如何优化一条MySQL查询

概览

1、合理建立索引。在合适的字段上建立索引，例如在where和order by命令上涉及的列建立索引。可以为经常查询的字段、排序字段和关联查询字段创建索引，但不能滥用索引。索引的过多、过少或者不恰当都会影响查询效率。
2、索引优化。防止不走索引，或者走错索引
3、分析是否是偶发问题，如果是因为资源占用导致的慢查询好像也没办法优化
4、适时清理数据。数据库中的数据越多，查询速度就越慢。因此，在数据库设计时，需要考虑定期清理历史数据。同时，对于暂时不用的数据，应该及时清理，以免影响查询效率。
5、避免全表扫描。全表扫描是数据库查询速度最慢的方式之一。避免全表扫描的方法主要有两种：一是通过索引查找到需要查询的数据；二是添加过滤条件，缩小查询结果集的范围。
6、优化查询语句。在设计数据库查询语句时，应该尽量简洁明了。同时，避免使用重复或者多余的语句，尽可能减少数据库的负载。另外，也可以根据查询场景使用不同的查询方式，例如利用 JOIN 代替两个以上的 SELECT 语句、使用 UNION ALL 代替 UNION 、使用 LIMIT 控制结果集大小等等。
7、使用缓存。对于一些查询结果经常不变的数据，可以利用缓存技术，将数据缓存在缓存服务器上，从而减少数据库查询的次数，提高查询效率。Redis？

1、索引失效

1.1 对索引使用左模糊匹配

// name 字段为二级索引
select * from t_user where name like '%林';

使用like %xx等以百分号开头的模糊匹配会使索引失效。因为索引使用的是B+树，相当于对字段进行了排序，然后构造成B+树。我们在二叉搜索树查询数据时是根据查询值与当前节点的大小来决定是往左子树还是右子树去找。如果是%开头，说明在根节点就已经不知道往哪找了，%相当于通配符，无法判断大小。

1.2 对索引使用函数

// name 为二级索引
select * from t_user where length(name)=6;

不难理解，对name建立的索引跟name的长度有啥关系。
从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。
举个例子，我通过下面这条语句，对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。

alter table t_user add key idx_name_length ((length(name)));

此时再查询就可以使用索引了。

1.3 对索引进行表达式计算

select * from t_user where id + 1 = 10;

这其实类似于对索引使用了函数，函数的功能是对索引的值加1。如果改写成where id = 10-1就能够使用索引了。

1.4 对索引隐式类型转换

两种情况：
如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，会走全表扫描。
如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描。
直接说答案：MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。
然后我们思考为什么？这两种情况分别是：

select * from t_user where id(字符串) = query(数字)
select * from t_user where id(数字) = query(字符串)

根据结论”MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。“，第一句，会把索引id的字符串转为数字，第二句会把查询的字符串转为数字。说到这里，其实就清晰明了了。我们可以用两种角度看这个问题，我们在字符串上建立了索引，但是查询时却要把字符串改成数字，然而我们对这个数字又没有相应的索引，就只能全表扫描了。第二种则是只对查询的内容改变了类型。两者比较，可以看出来，前者是改变全表的类型来匹配查询的内容，后者则是改变了查询类型来匹配全表。
从另一个角度看，这里索引失效是对索引使用了函数。

select * from t_user where CAST(phone AS signed int) = 1300000001;

1.5 联合索引非最左匹配

如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1;
where a=1 and b=2 and c=3;
where a=1 and b=2;

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。
但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2;
where c=3;
where b=2 and c=3;

也就是说，顺序无所谓，因为优化器会帮我们重排，但是重排之后对顺序要符合最左匹配。如果根本没有a，那么第一个字段就走不成索引，后面的就不用说了。就跟like %xx一样。
有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？
这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。
MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。
从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。
索引下推的大概原理是：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

1.6 WHERE 子句中的 OR

// id 是主键，age 是普通列
select * from t_user where id = 1 or age = 18;

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。这也不难理解，or表示前后只要有一个为真就行，在查询没有索引的那一项时，不得不对全表进行扫描，别的项有索引又怎样，帮不到啊。换个思路，极端情况，假设有索引那一项根本不存在查询结果，就相当于只对没有索引的那一项进行查询，既然都没有索引了，那肯定索引失效。

2、数据库选错索引

select id from product where id > 1  and name like 'i%';

product 表就有主键索引（id）和普通索引（name）。这条查询语句的结果既可以使用主键索引，也可以使用普通索引，但是执行的效率会不同。这时，就需要优化器来决定使用哪个索引了。
很显然这条查询语句是覆盖索引，直接在二级索引就能查找到结果（因为二级索引的 B+ 树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查询主键索引的 B+ 树的成本会比查询二级索引的 B+ 的成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引（不用回表）。
上面解释了多条索引的选择问题，下面探讨索引是如何选择的。

// c有索引
select * from t where 100 < c and c < 100000

系统在执行这条语句的时候，会进行预测：究竟是走 c 索引IO次数少，还是直接扫描全表IO次数少（考虑索引的回表问题，走索引不一定是一定好的）
如果是扫描全表的话，那么扫描的次数就是这个表的总行数
如果走索引 c 的话，我们通过索引 c 找到主键之后，还得再通过主键索引来找我们整行的数据
走索引需要回表，相当于增加了IO次数。而且，我们也不知道符合 100 < c and c < 10000 这个条件的数据有多少行，如果这个表全部数据都符合，那就意味着，走 c 索引不仅需要扫描全表，同时还得每行数据走两次索引，此时还不如一开始就扫描全表。
系统通过索引的区分度来进行选择，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高，唯一索引的区分度是1。我们也把区分度称之为基数，即区分度越高，基数越大。所以基数越大，意味着符合 100 < c and c < 10000 这个条件的行数越少。一个索引的基数越大，意味着走索引查询越有优势。
索引的区分度等于count(distinct 具体的列) / count( * )，表示字段不重复的比例
系统当然是不会遍历全部来获得一个索引的基数的，代价太大了，索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。由于是采样，所以可能存在失误
结论：由于统计的失误，导致系统没有走索引，而是走了全表扫描，而这，也是导致我们 SQL 语句执行的很慢的原因。
这里我声明一下，系统判断是否走索引，扫描行数的预测其实只是原因之一，这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。
我们可以通过强制走索引的方式来查询，例如

select * from t force index(a) where c < 100 and c < 100000;

我们也可以通过

show index from t;

来查询索引的基数和实际是否符合，如果和实际很不符合的话，我们可以重新来统计索引的基数，可以用这条命令

analyze table t;

来重新统计分析。
索引区分度低除了可能导致选错索引，也会直接导致查询慢。例如，网页全路径的url链接做索引，一眼看过去全都是同一个域名，如果前缀索引的长度建得不够长（也就是只索引了前面相同的部分，后面的没上索引？），那这走索引跟走全表扫描似的，正确做法是尽量让索引的区分度更高，比如域名去掉，只拿后面URI部分去做索引。

3、偶尔查询很慢

上面讨论的索引失效，或者选错索引导致的慢查询是每次都能复现的情况，除了上述情况，还可能出现有时候查询快，有时候查询慢的情况。出现这种偶尔查询很慢的情况就像有时候电脑啥也没干，但是很卡。因为后台有程序在大量占用资源。

3.1 数据库在刷新脏页

当我们要往数据库插入一条数据、或者要更新一条数据的时候数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，再通过 redo log 里的日记把最新的数据同步到磁盘中去。
不过，redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。

3.2 拿不到锁

执行的涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了。
判断是否真的在等待锁，我们可以用 show processlist这个命令来查看当前的状态

3.2 连接数太小

正常情况下，客户端与server层如果只有一条连接，那么在执行sql查询之后，只能阻塞等待结果返回，如果有大量查询同时并发请求，那么后面的请求都需要等待前面的请求执行完成后，才能开始执行。如果我们能多建几条连接，那么请求就可以并发执行，后面的连接就不用等那么久了。但是如果超过了连接数限制，还是会出现等待的问题。注意，连接数过小的问题，受数据库和客户端两侧同时限制。

4、使用连接（JOIN）来代替子查询(Sub-Queries)

子查询可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)

如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT * FROM customerinfo LEFT JOIN salesinfo 
ON customerinfo.CustomerID=salesinfo.CustomerID 
WHERE salesinfo.CustomerID ISNULL

连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

5、使用联合(UNION)来代替手动创建的临时表

UNION就是对查询的所有行取并集，去除重复行。UNION 关键字不会查询重复的记录，但重复的记录并不是简简单单的“看上去重复”或“主键重复”，记录中只要有一个字段不一致，UNION 就会老老实实的将他们都查询出来。
UNION它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要相同。下面的例子就演示了一个使用UNION的查询。

SELECT Name,Phone FROM client UNION 
SELECT Name,BirthDate FROM author UNION 
SELECT Name,Supplier FROM product

参考

https://xiaolincoding.com/mysql/index/index_lose.html
https://xiaolincoding.com/mysql/base/how_select.html
https://segmentfault.com/a/1190000042027861
https://juejin.cn/post/7206654718675451961
https://www.cnblogs.com/LoveMyBottle/p/10768878.html
https://www.php.cn/mysql-tutorials-488182.html