最近在复习MySQL的一些理论知识,并打算根据复习到的理论知识重构我本科那个完整项目的数据库。在这个过程中，我会记录下一些印象深刻的知识点。

本文想总结的，主要是SQL查询的优化问题。

SQL性能问题的来源

可以总结出，SQL的性能问题有可能来自下面的这些因素：

  1. 查询的复杂性：

    查询中的复杂操作（例如多表连接、子查询、聚合函数等）可能需要更多的计算和资源，从而影响性能。较为简单的查询通常会更快执行。

  2. 表的大小：

    如果查询命中的表非常大，例如具有数百万行或更多行的表，查询的执行时间可能会增加。在大型表上执行复杂操作可能会导致性能下降。

  3. 连接操作：

    如果查询涉及多个表的连接操作，并且连接的结果集行数很大，查询的性能可能会受到影响。连接操作需要比简单的检索操作更多的计算和资源。

  4. 聚合操作：

    聚合操作（例如SUM、COUNT、AVG等）需要组合多行数据来生成结果，这可能需要更多的计算和资源。

  5. 数据库服务器负载：

    如果数据库服务器上同时运行的查询较多，特别是资源密集型查询，数据库的整体性能可能会受到影响。其他用户运行的查询可能会占用数据库服务器的资源，导致查询运行缓慢。

解决方案

在开发中，可以结合下面的这些细节来注意对SQL查询的优化。

  1. 避免过多的JOIN

    JOIN 消除是实现高效查询计划的众多技术之一。可以将单个查询拆分为多个单独的查询，这些查询稍后可以连接起来，从而删除不必要的连接、子查询、表等。

  2. 避免使用SELECT DISTINCT

    SQL DISTINCT 运算符用于仅选择列的唯一值，从而消除重复值。

    但是，这可能需要该工具处理大量数据，因此会使查询运行缓慢。通常，建议避免使用 SELECT DISTINCT，而只是执行 SELECT 语句但指定列。

  3. 使用SELECT+字段，而不是SELECT

    在大型数据库的情况下，不建议检索所有数据，因为这会在查询大量数据时占用更多资源。

  4. 使用索引优化查询

    1. 最左前缀匹配原则：

      在MySQL中建立联合索引时，最左前缀匹配原则指的是在检索数据时从联合索引的最左边开始匹配。

    2. 选择区分度高的列作为索引。

      应该使用经常用来查询的、区分度高的字段作为索引字段。

    3. 索引列不参与计算

      索引创建的过程是维护一棵B+树的过程。尽可能不对索引字段进行修改。

    4. 避免在同一列上使用多种类型的索引。

      应该避免在同一列上使用多种类型的索引，例如UNIQUE INDEX和PRIMARY KEY。

  5. 使用EXPLAIN进行分析

    MySQL 提供了 EXPLAIN 和 DESCRIBE 语句来分析查询语句（DESCRIBE 的使用方法与 EXPLAIN 语句是一样的，并且分析结果也一样）。

    下面是EXPLAIN语法的基本格式：

    1
    2
    EXPLAIN SELECT * FROM table_name WHERE conditions;

    其得到的结果可以包括下面的内容：

    1. id：查询的唯一标识符，如果查询包含子查询，则会有多个id。
    2. select_type：查询的类型，包括SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
    3. table：查询涉及的表名。
    4. partitions：查询涉及的分区名。
    5. type：访问表的方式，常见的类型有ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。
    6. possible_keys：可能使用的索引。
    7. key：实际使用的索引。
    8. key_len：索引使用的长度。
    9. ref：与索引比较的列或常数。
    10. rows：估计需要扫描的行数。
    11. filtered：通过条件过滤后的行占比。
    12. Extra：额外的信息，如使用临时表、使用文件排序等。

  6. 考虑分库分表