MySQL优化策略（待完善）

MySQL语句基础优化策略：

第一、尽量使用索引

使用不到索引的场景有：

1. 使用了函数 或 表达式， 索引列参与了计算，索引：INDEX (col1)，查询：WHERE UPPER(col1) = ‘VALUE’（使用了函数，索引不会被使用）。
2. 查询条件不符合索引的最左前缀原则，索引：INDEX (col1, col2, col3)，查询：WHERE col2 = 1 AND col3 = 2（未使用 col1，索引不会被使用）。
3. 数据类型不匹配，索引：INDEX (col1)，col1 是整数类型。查询：WHERE col1 = ‘123’（字符串与整数不匹配，索引可能不会被使用）。
4. 使用了 OR 条件，索引：INDEX (col1)，查询：WHERE col1 = 1 OR col2 = 2（col2 没有索引，索引可能不会被使用）。
5. 对索引列使用了 NOT、<>、!= 等操作。
6. 对索引列使用了 LIKE 查询，并且通配符在开头（如 LIKE ‘%value’）。如果是LIKE ‘value%’，还是可以用到索引的。
7. 查询中使用了 JOIN，JOIN 的列没有索引，MySQL 可能不会使用索引。索引：INDEX (col1)，查询：SELECT * FROM table1 JOIN table2 ON table1.col2 = table2.col2（col2 没有索引，索引可能不会被使用）。
8. 索引选择性太低，索引：INDEX (gender)，gender 只有两个值（男/女）。查询：WHERE gender = ‘男’（索引选择性低，可能不会被使用）。
9. 查询返回大部分数据，或者表的数据很少，MySQL 可能会选择全表扫描而不是使用索引。
10. order by 或者group by 场景，索引：INDEX (col1)，INDEX (col2)，注意不是联合索引，SELECT * FROM example WHERE col1 = 10 ORDER BY col2;，只能用到一个索引col1，col2的排序不会用到索引。

第二、不使用select * ……这样的语句

老老实实将需要的字段写出来，如select name……这样的，除非是整张表的数据字段都要使用。

第三、索引覆盖

索引覆盖也是一种优化sql的策略。

指的是查询所需的所有数据都能够从索引中获取，而无需访问实际的数据表。这种情况被称为“覆盖索引”或“索引覆盖查询”。

例如：

SELECT name, email FROM users WHERE age > 30;

为了实现索引覆盖，可以创建一个复合索引：

CREATE INDEX idx_users_age_name_email ON users (age, name, email);

第四、永远不要讲or作为顶层条件。

有or的放在括号（）里，顶层条件必须用and。如：where a=1 and b=3 and … or ccc=33 … ， 这种是等于 where ( a=1 and b=3 and …) or ( ccc=33 … ) ,or 处理条件树的顶端，是绝对不行的。应该是where a=1 and b=3 and … (ccc=32 or ccc=33 … )。如果有where a=1 or a=2这样的，将or换成in。

第五、数据量大的表，禁止直接使用分页。

如：limit 100000,100。它是查询100000行之后的100条记录。它依然是扫描100000之后，再获取100行数据的，还有就是一般的语句都有where语句，比如select name form table_a where age=18 limit 100000,100。它会判断每条记录是不是where age=18，然后累计100000条之后，再取100条。
当然，如果没有where的条件查询语句，limit 100000,100是快一些（不过也快不了多少），但是日常的查询中应该不存在不要where语句的查询吧。
（如果非要使用分页，也有相应的优化方法，但是不能直接使用分页）

如果非要用，得要使用 > 号去做分页，例如寻找 id > 上一个分页末尾的数字，然后 limit 0,10。

第六、索引尽量重用，不要无限制建索引。

比如a b 需要建一个联合索引，a又需要建一个独立索引，那么a的独立索引是不需要建的，因为单独查a的话，也是会用的联合索引的
联合索引的匹配顺序是从前到后，比如a b c 建议联全索引，查a, ab, ac, abc 都会走索引，必须要有a；但bc， cab 等无a的字段就不会走索引。

第七、将一个大SQL拆分为多个小SQL。

第八、优化join

join的优化策略：

• 索引优化：确保join条件的连接字段有索引。
• 减少数据集大小：在JOIN之前，使用WHERE子句尽量减少数据集的大小。不过这种是临时表，需要具体看。
• 小表驱动大表：MySQL通常对小表进行全表扫描，对大表使用索引扫描。因此，将小表放在JOIN的左侧可能会更高效。
• 使用EXPLAIN分析查询：关注type和key字段：type字段显示JOIN类型（如ref、eq_ref等），key字段显示使用的索引。如果EXPLAIN显示使用了临时表或文件排序，Using temporary：表示查询使用了临时表。Using filesort：表示查询使用了文件排序（通常与临时表一起出现），考虑优化查询或索引。

分页和排序的优化策略

第一、排序优化。
MySQL中排序优化的核心点还是使用索引，使用索引的话排序的效率就会高一些。
MySQL支持两种排序方式：Filesort和Index。
Index的排序方式自然是效率高的排序了，如果出现Filesort的排序，那就是需要优化的排序了。
排序order by如果想要使用索引，其实是和where语句一样。
如下图：

当然，这时在MySQL中排序的情况下，其实我们还有一种选择，就是从数据库中取出数据后，在应用程序中进行排序（比如使用java的集合类）。
当MySQL中有相应的索引的时候，直接在数据库中排序好了传给应用程序（比如java）自然是最好的。但是如果数据库的负载很高，而且有很多一样的并发排序查询，而且也没有相应的索引的时候，在数据库中排序好了再传给应用程序就有些得不偿失了，这时候，把排序工作交给应用程序是最好的。
下面列举一些在数据库和应用程序中排序的场景。

在数据库中排序：

1、MySQL中已经存在这个排序的索引；
2、MySQL中数据量较大，而结果集需要其中很小的一个子集，比如1000000行数据，取TOP10；
3、复杂排序条件，比如按照几个字段进行排序，数据库就非常有优势了。
4、对于一次排序、多次调用的情况，比如统计聚合的情形，可以提供给不同的服务使用，那么在MySQL中排序是首选的。另外，对于数据深度挖掘，通常做法是在应用层做完排序等复杂操作，把结果存入MySQL即可，便于多次使用。

在应用程序中排序：

1、数据源不在MySQL中，存在硬盘、内存或者来自网络的请求等；
2、数据存在MySQL中，量不大，而且没有相应的索引，此时把数据取出来用PHP排序更快；
3、数据源来自于多个MySQL服务器，此时从多个MySQL中取出数据，然后在PHP中排序更快；

第二、分页优化

策略：起始ID定位法
一般查询大批量数据按照时间区间查询，比如一次查几分钟数据出来，按时间递增；或者如果主键是自动（或人为）递增的也可以按这个limit 0,N，第二次查询加一个条件 id>上次最后一个再limit 0,N，保证mysql每次只扫描N+行。
这样，像翻多少页都是一样快，因为它利用了时间这个锁引，但是不能满足的就是跳页。
例如：
表table_a中有1000万条数据，我要查700万条数据之后的10条。如果直接：

select name,age,sex from table_a limit 7000000,10

会需要很久的时间，
但如果我加上一个条件where id>7000000（假设id是自增的，从1开始，且是有主键索引的），变成：

select name,age,sex from table_a where id>7000000 limit 10

那么查询会非常快，因为有id上索引的帮助。

策略：索引覆盖+子查询
假如我们有这样的sql：

select name, age, gender
from person
order by createtime desc 
limit 1000000,10;

在以上 SQL 中，createtime 字段创建了索引，但查询效率依然很慢，因为它要取出 100w 完整的数据，并需要读取大量的索引页，和进行频繁的回表查询，所以执行效率会很低。

我们可以做以下优化：

SELECT p1.name, p1.age, p1.gender
FROM person p1
JOIN (
    SELECT id FROM person ORDER BY createtime desc LIMIT 1000000, 10
) AS p2 ON p1.id = p2.id;

优化后的 SQL 将不需要频繁回表查询了，因为子查询中只查询主键 ID，这时可以使用索引覆盖来实现。那么子查询就可以先查询出一小部分主键 ID，再进行查询，这样就可以大大提升查询的效率了。

分库分表的分页和排序怎么处理？

如果数据量很大，又是分库分表的，那么就难以处理分页和排序，如果加载到应用里数据量太大了，如果是数据库本身，又无法操作。

这时候只能使用其他的数据库了，例如hive、ck、starrocks等等其他大数据的数据库，有一定的延迟性，但是能够支持。

或者进行数据冗余，或者建立异构索引，等等，以空间换时间。

优化常用的SQL语句：

1、explain
例如：

explain select * from xuser order by xuser_id;

结果为：

type参数的分析：
type 字段表示 MySQL 在表中查找所需行的方式，从最优到最差依次为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1. system：表只有一行数据（系统表），是 const 类型的特例。
2. const：通过主键或唯一索引查找，最多返回一行数据。
3. eq_ref：在连接查询中，使用主键或唯一索引进行关联。
4. ref：使用非唯一索引查找，返回匹配某个单值的多行数据。
5. range：使用索引检索一个范围内的数据（如 BETWEEN、IN、> 等）。
6. index：全索引扫描，遍历整个索引树。
7. ALL：全表扫描，性能最差。

检查思路：

1. 如果 type 是 ALL，说明查询没有使用索引，需要检查是否有合适的索引。
2. 如果 type 是 ref 或 range，说明查询使用了索引，但需要进一步检查索引的选择性。有可能是索引的选择性较低，例如性别、年龄等等，索引的效果可能不明显。
3. 如果 type 是 eq_ref，说明连接查询使用了主键或唯一索引，性能较好。如果 type 是 ref，说明使用了非唯一索引，可能需要优化索引或查询条件。

Extra参数的分析：

后面的Extra就说明了排序使用了什么方法
可见Null（或者是Using index condition），就是正常的，下面Using filesort就是不正常的（需要优化）。

2、show full processlist

show full processlist

结果为：

这条语句就是后悔药，找出线上正在运行的有哪些SQL。
Time是已经执行的时长（没结束，结束就抓不到了）。
注意：
a、Time频繁大于0的客户端查询基本上都是有问题的，因为客户端触发，风险（并发）不可控。如果是服务器自己触发可视情况而定。
b、多次show出同一个SQL，那么这种SQL要重点关注和优化。

如果explain之后发现查询优化器选择错索引了

可以强制使用正确的索引
MySQL、Oracle支持通过提示（Hint）强制使用特定索引。

SELECT * FROM table_name USE INDEX (index_name) WHERE condition;

• 使用 FORCE INDEX 语法
• 使用 USE INDEX 语法（较温和的建议）
• 使用 IGNORE INDEX 排除不想要的索引

explain只是MySQL的执行计划，如果MySQL实际的执行和explain不相同要如何处理？

1. 如果是MySQL 8.0.18+的版本，可以使用：EXPLAIN ANALYZE 分析，提供实际执行的指标(执行时间、处理行数等)。
2. 索引选择错误时，可以通过 FORCE INDEX 强制指定索引。
3. JOIN顺序不合理，可以使用STRAIGHT_JOIN强制连接顺序。

什么是索引覆盖

索引覆盖（Covering Index）是一种数据库查询优化技术，指查询的所有字段都包含在索引中，因此数据库可以直接通过索引返回结果，而无需访问实际的数据行（即无需回表）。这种方式可以显著提高查询性能，减少I/O操作和CPU开销。

例如，索引是 INDEX (col1, col2, col3)， select col1,col2 from test where col1 = 1 and col2 = 2; 这种查询出的参数都是索引里包含的，就是索引覆盖，因为查询完索引，直接就获取到数据，无需回表查询其他参数。

但是如果是：select col1,col2,col4 from test where col1 = 1 and col2 = 2; 其中 col4 不在索引中，就没办法全部使用索引，就需要有一次回表查询。

什么是“回表”

回表是MySQL中与索引查询相关的一个概念，指的是在使用非聚簇索引（二级索引）进行查询时，数据库引擎需要通过索引找到主键值，然后再根据主键值回到聚簇索引（主键索引）中查找完整的数据行。这个过程称为回表。

在MySQL的InnoDB存储引擎中，数据存储是基于聚簇索引（Clustered Index）组织的。聚簇索引的叶子节点直接存储了完整的数据行，而非聚簇索引（Secondary Index）的叶子节点只存储了主键值和索引字段。

因此，当使用非聚簇索引查询时：

1. 数据库引擎首先通过非聚簇索引找到对应的主键值。
2. 然后根据主键值回到聚簇索引中查找完整的数据行。

这个过程就是回表。

所以回表的条件有两个：

1. 非聚簇索引
2. 没有索引覆盖

没有使用到索引会导致什么后果 / 大量的慢查询会导致什么后果

没有使用到索引会导致大量的慢查询，大量慢查询的话会导致：

1. MySQL数据库的CPU 飙升：会有全表扫描、复杂计算或未优化的JOIN。
2. MySQL数据库的磁盘 I/O 压力：大量慢查询可能频繁读取大表数据。
3. MySQL数据库的内存耗尽：排序（ORDER BY）、分组（GROUP BY）或临时表操作可能占用大量内存。
4. 连接池耗尽：慢查询长时间占用数据库连接，导致连接池被占满。
5. 锁竞争加剧：没有用到索引，因此加了很多表锁，而不是行锁。
6. 复制延迟：主从架构中，主库的慢查询会导致从库SQL线程回放延迟，影响读写分离。