mysql数据查询优化总结

mysql数据库条数较多，数据加载较慢

• mysql数据库表的优化方法：
  • mysql数据库加索引
    • 索引优化：
      • 选择合适的列（主键列、经常用于连接的外键列、经常用户查询或者过滤的列）建立索引
      • 避免过度索引（索引过多会增加存储开销，导致写入性能下降，每次插入、更新或者删除都需要维护索引）
      • 使用多列索引的时候，要注意SQL中的条件顺序
      • 自增索引和联合主键索引比较，其他参考：
        • 唯一性要求：联合主键索引要求联合字段的组合是唯一的；自增索引通常用于创建主键，但不要求唯一性。
        • 数据查询需求：如果经常按照组合字段查询数据，联合索引更有效率；但是如果频繁使用其他条件，自增更好。
        • 性能：自增索引通畅鼻联合主键索引更快，它减少了索引文件的大小，并且在插入新纪录时不需要重新排序
        • 索引维度：联合主键索需要更多的维度，尤其在插入新纪录或者更新记录的时候；
  • 如果需要频繁执行更新和删除操作，还应该考虑事务的隔离级别和索引的维护成本。Todo
    • MySQL事务隔离级别和实现原理
  • 读写分离一种常见的数据库架构优化策略，它可以显著提高数据库系统的性能和容量：
    • 主数据库Master：数据库用于处理写入操作（INSERT、UPDATE、DELETE）以及实时数据的维护，它负责数据的写入和变更。主数据库的性能通常需要较高，因为它需要处理写操作的压力。
    • 从数据库Slave：从数据库用于处理读取操作（SELECT），它从主数据库中复制数据，并提供读取服务。从数据库的性能通常不需要像主数据库那样高，因为它主要用于读取操作。
    • eg：读写分离，并将部分写操作换成到Redis：适用于需要处理大量读取操作、希望提高读取性能、并且可以容忍一定的数据延迟和一致性问题的应用程序。不适用于对数据一致性要求非常高的场景。
      1. 设置主从复制： 首先，您需要设置主从复制。这可以通过数据库软件（例如 MySQL、PostgreSQL）的配置来实现。主数据库将数据同步到从数据库，以确保从数据库具有与主数据库相同的数据。
      2. 读写分离负载均衡： 实现读写分离后，需要一个负载均衡器来将读取请求分发到从数据库，而写入请求仍然发送到主数据库。这有助于平衡流量并提高整体性能。
      3. 同步写入到 Redis： 对于部分需要频繁更新的数据，您可以将写入请求发送到 Redis 缓存，而不是直接写入主数据库。Redis 提供了快速的读写能力，适用于需要高速写入的场景。定期将 Redis 中的数据同步到主数据库，以确保数据的一致性（分钟级）。
      4. 定期同步和备份： 定期执行主从数据库之间的同步，以确保数据一致性。此外，定期备份主数据库和从数据库的数据以应对灾难性故障。
      5. 监控和故障处理： 实施监控和警报系统，以实时监测主从数据库和 Redis 缓存的性能和可用性。对于故障情况，需要有相应的恢复策略。
  • 表的大文本字段分离出来，成为独立的新表。大文本字段，可以使用NOSQL数据库
  • 查询优化：
    • 避免全表扫描，count(*)等获取表中的记录数，可以考虑单独计数列
    • 字查询优化：
    • 分页查询： 如果查询结果集很大，不要一次性获取所有数据。而是使用分页查询，只获取需要的数据页
  • 建立分区表（例如天级别更新）：分区表允许您将大型表分割成更小、更易管理的分区（partition），每个分区都可以单独处理，这有助于提高查询性能、数据管理和维护的效率。分区表在某些场景下特别有用，例如，处理大量历史数据、按时间范围查询数据等。
    1. 分区类型： MySQL 支持不同类型的分区，包括 RANGE、LIST、HASH 和 KEY 分区。您可以根据需求选择合适的分区类型。每种分区类型都有其独特的用例和语法。
    2. 分区键（Partition Key）： 分区表的创建需要指定一个分区键，该键用于定义如何将数据分割成分区。分区键可以是表中的一个列，通常是根据查询和数据分布的需求选择的列，例如时间戳列。
    3. 例如：
       1. RANGE 分区：根据时间戳将数据表分成不同的时间范围分区。
       2. HASH 分区：根据某个列的哈希值将数据分布到不同的分区。
       3. LIST 分区：根据列的值列表将数据分成不同的分区。

CREATE TABLE my_partitioned_table (
    id INT,
    event_date DATE
) PARTITION BY RANGE (YEAR(event_date)) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2005),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

CREATE TABLE my_hash_partitioned_table (
    id INT,
    name VARCHAR(255)
) PARTITION BY HASH(id) PARTITIONS 4;


CREATE TABLE my_list_partitioned_table (
    id INT,
    category VARCHAR(255)
) PARTITION BY LIST (category) (
    PARTITION p_default VALUES IN ('unknown'),
    PARTITION p_books VALUES IN ('fiction', 'non-fiction'),
    PARTITION p_movies VALUES IN ('action', 'comedy', 'drama'),
    PARTITION p_others VALUES IN (DEFAULT)
);

注意：MySQL 中的分区表在定义分区键时，必须确保分区键列包含在表的主键（Primary Key）或唯一键（Unique Key）中，为了确保分区表的数据唯一性和正确性。如果不将分区键列包含在主键或唯一键中，可能会导致数据分布不正确，从而产生错误或数据冗余。

CREATE TABLE my_partitioned_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE
) PARTITION BY RANGE (YEAR(event_date)) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2005),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
// 主键列 id 包含在分区表的分区键 event_date 中，确保了每个分区内的数据是唯一的。

如果表每天都有增量数据，希望实现自动更新分区：

-- 假设今天是2023-09-16，你要添加新的分区
ALTER TABLE my_daily_data
ADD PARTITION (
    PARTITION p20230916 VALUES LESS THAN ('2023-09-17')
);

删除历史分区及数据

-- 删除名为 p20230915 的历史分区及其数据
ALTER TABLE my_daily_data
DROP PARTITION p20230915;

条件顺序的查询举例：假设我们有一个包含以下列的数据库表：

1. user_id - 用户ID
2. username - 用户名
3. email - 电子邮件地址
4. created_at - 创建时间

假设我们希望查询具有特定用户名和创建时间范围的用户。

情景1： 我们创建了一个多列索引，包括username和created_at列，索引的顺序是username在前，created_at在后。

sqlCopy codeCREATE INDEX idx_username_createdat ON users(username, created_at);

如果我们执行以下查询：

sqlCopy codeSELECT * FROM users WHERE username = 'john' AND created_at >= '2023-01-01';

在这种情况下，由于查询条件中username在前，数据库查询优化器可以有效地使用多列索引来过滤具有特定用户名和创建时间范围的用户，因为username是查询条件中更频繁的列。

情景2： 如果我们创建了相同的多列索引，但索引的顺序是created_at在前，username在后。

sqlCopy codeCREATE INDEX idx_createdat_username ON users(created_at, username);

然后执行相同的查询：

sqlCopy codeSELECT * FROM users WHERE username = 'john' AND created_at >= '2023-01-01';

在这种情况下，虽然索引仍然包括我们需要的两列，但由于查询条件中created_at在前，数据库查询优化器可能不会有效地使用索引来执行此查询，因为created_at列的选择性较低，它不是最频繁的过滤条件。

因此，在设计多列索引时，要考虑查询的常见条件和顺序，以确保索引的顺序和查询条件的顺序相匹配，以获得最佳性能。数据库查询优化器会根据情况选择最佳索引来执行查询，但正确的索引设计可以帮助优化器更容易地做出正确的决策。

我正在参与2023腾讯技术创作特训营第二期有奖征文，瓜分万元奖池和键盘手表