MySQL 优化

SQL 优化是提高 MySQL 数据库性能和查询效率的过程。下面是一些常见的 SQL 优化技巧：

1. 优化表结构：合理的表结构设计有助于提高性能。特别是，列是否具有正确的数据类型，每个表是否具有符合业务需求的列？例如，执行频繁更新的业务通常表比较多，列很少，而分析大量数据的业务通常表比较少，列很多。
2. 编写高效的查询：编写高效的 SQL 是优化查询性能的关键。避免使用不必要的 SELECT * 查询，只选择需要的列。合理使用 WHERE、JOIN、GROUP BY 和 ORDER BY 等子句，以尽量减少数据库的负担。
3. 适当的冗余字段：冗余字段可以提高查询性能，但必须考虑数据一致。冗余字段应遵循：不是频繁修改的字段；不是唯一索引的字段；不是 varchar 超长字段，更不能是 text 字段。这种方式也被称作反范式。
4. 使用合适的索引：索引是提高查询效率的关键。确保表中的字段经常用于搜索并在这些列上创建索引。特别是，在使用外键进行连表查询时，索引尤其重要。
5. 避免全表扫描：全表扫描会对性能产生严重影响，特别是对大表来说。尽量避免在查询中使用不带索引的列，以免触发全表扫描。
6. 使用 EXPLAIN 分析查询计划：MySQL 提供了 EXPLAIN 关键字，可以帮助分析查询语句的执行计划。通过查看执行计划，可以了解查询是如何使用索引和执行的顺序，从而找出可能存在的性能问题。
7. 分析慢查询：通过 MySQL 的慢查询日志或者其他监控工具，找出执行时间较长的 SQL 查询语句。分析这些慢查询的执行计划、索引使用情况等信息，确定可能存在的性能问题。
8. 缓存重复查询结果：对于一些相对静态的查询结果，可以考虑使用缓存技术，如 Redis，将查询结果缓存起来，减少数据库的访问次数。

使用合适的索引

提高 SELECT 子句的查询性能的最佳方式是在一个或多个列上创建索引。索引就像是指向表行的指针，使查询可以快速确定哪些行匹配 WHERE 子句中的查询条件，并检索这些行的其他列值。

但是，需要注意的是，不必要的索引反而会降低性能。索引增加了插入、更新和删除的成本，因为每个索引都必须更新。必须找到正确的平衡点，以使用最佳的索引集实现快速查询。

索引的创建应遵循以下原则：

• 因为每个 InnoDB 表都有一个主键（不管你是否主动创建它），所以要为每个表指定一组主键列，这些列用于最重要和最关键的查询。

• 不要指定太多或太长的列为主键，因为这些列值在每个辅助索引中都是重复的。当索引包含不必要的数据时，读取这些数据的 I/O 和缓存这些数据的内存会降低服务器的性能和可伸缩性。

• 不要为每个列创建单独的二级索引而应该创建联合索引，因为每个查询只能使用一个索引。在很少使用的列或只有少数不同值的列上建立索引可能对查询都没有任何帮助。而对于联合索引，如果索引包含结果集所需的所有列（称为覆盖索引），则查询可能完全能够避免读取表数据。

• 只有经常作为查询条件的列才需要创建索引，如果在在查询条件中很少使用的列创建了索引，反而会降低性能，索引增加了插入、更新和删除的成本，因为每个索引都必须更新。

• 在用于连接（JOIN）的列上创建索引，这些列主要是外键，可以加快连表查询的速度。

• 在需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。

• 在需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

• 对于数据量很少或者重复值多的列不应该创建索引。

• 对于 text, image 和 bit 数据类型的列不应该创建索引。

更多规范可以参考阿里巴巴 Java 开发手册 。

索引失效场景

1. 最左前缀原则：

   如果在（col1, col2, col3）上有一个三列索引，则在（col1）、（col1, col2）和（col1, col2, col3）能命中索引。

   如果列没有在索引的最左边形成前缀，MySQL 就不能命中索引。假设你有如下所示的 SELECT 语句：

   SELECT * FROM tbl_name WHERE col1=val1;
   SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
   
   SELECT * FROM tbl_name WHERE col2=val2;
   SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
   

   如果在（col1, col2, col3）上存在索引，则只有前两个查询能命中该索引。第三和第四个查询确实涉及到了索引列，但不能命中索引，因为（col2）和（col2, col3）不是（col1, col2, col3）的最左前缀。

2. 条件中含有计算、函数、类型转换：

   以下语句都会使索引失效：

   SELECT * FROM tbl_name WHERE age + 1 ='1';
   SELECT * FROM tbl_name WHERE date(create_time)='20190101';
   SELECT * FROM tbl_name WHERE col1='1';    -- col1 为 int 类型
   

3. Like 以 % 开头：

   使用双 % 号的查询条件。如：col1 like '%123%'，会是索引失效，为使索引生效，可以只添加后置 %，如：a like '123%'。

4. 范围查询右侧的索引失效：

   如果在（col1, col2, col3）上有一个三列索引，并进行了以下查询：col1 > 5 AND col2 = 1 AND col3 = 4，这种情况下，虽然 col1 可以命中缓存，但是 col2 和 col3 缓存会失效。如果要让所有缓存生效，可以将需要进行范围查找的列放在联合索引的右侧。

5. NOT IN 使索引失效：

   NOT IN 通常会使索引失效，使用 NOT EXISTS 来替换 NOT IN。

查询执行计划

在执行查询之前，MySQL 优化器会选择执行最有效查询的操作集，这称为“查询执行计划”，也称为 EXPLAIN 计划。我们的目标是识别 EXPLAIN 计划中表明查询优化得很好的方面，并学习 SQL 语法和索引技术，以便在遇到一些低效操作时改进计划。

EXPLAIN 语句提供了 MySQL 如何执行语句的信息。在 EXPLAIN 的帮助下，我们可以看到应该在哪里向表添加索引，以便通过使用索引查找行来加快语句的执行速度。

EXPLAIN 可以与 SELECT, DELETE, INSERT, REPLACE 和 UPDATE语句一起工作。

EXPLAIN 输出格式

EXPLAIN 的每个输出行提供关于一个表的信息：

id

SELECT 的序号，用来标识整个查询中 SELELCT 语句的序号。

select_type

SELECT 的 类型，可以是下表中所示的类型。

table

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

• <unionM,N> : id 值为 M 和 N 的行的并集。
• <derivedN> : 该行引用 id 值为 N 的行的派生表结果，例如，派生表可能来自 FROM 子句中的子查询。

type

连接类型。性能从最优到最差排序：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

• const：表中最多只有一行匹配的记录，当使用主键或唯一索引的字段作为查询条件时 type 为 const：

  SELECT * FROM tbl_name WHERE primary_key=1;
  
  SELECT * FROM tbl_name
    WHERE primary_key_part1=1 AND primary_key_part2=2;
  

• eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。除了 system 和 const 类型之外，这是最好的连接类型。当使用主键或唯一索引的字段作为连表条件时 type 为 eq_ref。

  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;
  

• ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。

  SELECT * FROM ref_table WHERE key_column=expr;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;
  

• ref_or_null：这种连接类型类似于ref，但是MySQL会对包含NULL值的行进行额外的搜索。这种连接类型优化最常用于解析子查询。

  SELECT * FROM ref_table
    WHERE key_column=expr OR key_column IS NULL;
  

• index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。

• range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。

  SELECT * FROM tbl_name
    WHERE key_column = 10;
  
  SELECT * FROM tbl_name
    WHERE key_column BETWEEN 10 and 20;
  
  SELECT * FROM tbl_name
    WHERE key_column IN (10,20,30);
  
  SELECT * FROM tbl_name
    WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
  

• index：除了扫描索引树之外，索引连接类型与ALL相同。如果是覆盖索引，则只扫描索引树。仅索引扫描通常比 ALL 更快，因为索引的大小通常小于表数据的大小。

• ALL：全表扫描。

possible_keys

表示 MySQL 执行查询时可能会用到的索引。如果该列为 NULL，则没有相关索引。在这种情况下，你应该检查 WHERE 子句来检查它是否引用了适合建立索引的列，从而提高查询的性能。如果是，则创建一个适当的索引，并再次使用EXPLAIN检查查询。

key

表示 MySQL 实际使用到的索引。要强制 MySQL 使用或忽略在 possible_keys 列中列出的索引，可以在查询中使用 FORCE INDEX, USE INDEX, 或者 IGNORE INDEX。

key_len

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。

rows

rows 列表示根据表统计信息及选用情况，大致估算出执行查询要检查的行数。对于 InnoDB 表，这个数字是一个估计值，可能并不总是准确的。

Extra

关于 MySQL 如何解析查询的附加信息。通过这些信息，可以更准确的理解 MySQL 是如何执行查询的。常见的值如下：

• const row not found：对于SELECT…FROM tbl_name，表为空。

• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。

• Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

• Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。

• Using temporary：为了解析这个查询，MySQL需要创建一个临时表来保存结果。常见于 GROUP BY 和 ORDER BY 子句。

• Using index condition：表示查询优化器选择使用了索引条件下推这个特性。

基于 Spring Boot 2.7.12、MyBatis-Plus、Spring Security 等主流技术栈构建的后台管理系统。
后台：https://gitee.com/linjiabin100/pi-admin.git
前端：https://gitee.com/linjiabin100/pi-admin-web.git