如何避免写出慢SQL？

背景

关于如何避免写出慢SQL，我发现自己对这方面的认识比较浅薄，生产环境中，慢SQL一旦产生，其代价还是比较大的，就比如数据库宕机，这是我们一定要去避免发生的现象。所以，一定要结合数据库，结合业务数据量去学习如何避免慢SQL，而不浅浅地只拘泥在理论层面。

定量认识慢SQL

作为一个合格的程序员，我们需要对数据库的能力，有一个定量的认识。影响 MySQL 处理能力的因素很多，比如：服务器的配置、数据库中的数据量大小、MySQL 的一些参数配置、数据库的繁忙程度等等。但是，通常情况下，这些因素对于MySQL性能和处理能力影响范围，大概在几倍的性能差距。所以，我们不需要精确的性能数据，只要掌握一个大致的量级，就足够指导我们的开发工作了。
1、有经验数据表明，一般一台 MySQL 服务器，平均每秒钟执行的 SQL 数量在几百左右，就已经是非常繁忙了，即使看起来 CPU 利用率和磁盘繁忙程度没那么高，也需要考虑给数据库减负，做些优化工作。 其实有实验数据测过，低端的服务器每秒可以执行的简单SQL达到几千条，高端的服务器每秒可以执行的简单SQL是几万条。考虑到正常业务系统中运行的SQL，总不能都是简单的SQL，大部分都是比较复杂的，与此同时结合实际的生产环境，这块的TPS还得大打折扣。
2、粗略估计数据量的多少。
另外一个重要的定量指标是，到底多慢的 SQL 才算慢 SQL。这里面这个“慢”，衡量的单位本来是执行时长，但是时长这个东西，我们在编写 SQL 的时候并不容易去衡量。那我们可以用执行 SQL 查询时，需要遍历的数据行数替代时间作为衡量标准，因为查询的执行时长基本上是和遍历的数据行数正相关的。你在编写一条查询语句的时候，可以依据你要查询数据表的数据总量，估算一下这条查询大致需要遍历多少行数据。如果遍历行数在百万以内的，只要不是每秒钟都要执行几十上百次的频繁查询，可以认为是安全的。遍历数据行数在几百万的，查询时间最少也要几秒钟，你就要仔细考虑有没有优化的办法。遍历行数达到千万量级和以上的，我只能告诉你，这种查询就不应该出现在你的系统中。当然我们这里说的都是在线交易系统，离线分析类系统另说。
遍历行数在千万左右，是 MySQL 查询的一个坎儿。MySQL中单个表数据量，也要尽量控制在一千万条以下，最多不要超过二三千万这个量级。原因也很好理解，对一个千万级别的
表执行查询，加上几个 WHERE 条件过滤一下，符合条件的数据最多可能在几十万或者百万量级，这还可以接受。但如果再和其他的表做一个联合查询，遍历的数据量很可能就超过
千万级别了。所以，每个表的数据量最好小于千万级别。
如果数据库中的数据量就是很多，而且查询业务逻辑就需要遍历大量数据怎么办？

使用索引避免全表扫描

如果业务本身体量就很大，就是有源源不断的数据，这个时候数据量很大，就可以考虑数据库的设计亮点-索引。使用索引避免全表扫描，即使数据库的量有千万条，我扫描的时候也只是扫了一小部分，查询效率就不受限于数据量了。总的来说，使用索引可以有效地减少执行查询时遍历数据的行数，提高查询性能。
绝大多数情况下，我们编写的查询语句，都应该使用索引，避免去遍历整张表，也就是通常说的，避免全表扫描。在每次开发新功能，需要给数据库增加一个新的查询时，都要评估
一下，是不是有索引可以支撑新的查询语句，如果有必要的话，需要新建索引来支持新增的查询。
但是，增加索引付出的代价是，会降低数据插入、删除和更新的性能。这个也很好理解，增加了索引，在数据变化的时候，不仅要变更数据表里的数据，还要去变更每个索引。所以，
对于更新频繁并且对更新性能要求较高的表，可以尽量少建索引。而对于查询较多更新较少的表，可以根据查询的业务逻辑，适当多建一些索引。
所以，其实，避免写出慢SQL，就这点套路，分析执行计划，看是否正常走了索引。从而分析索引有没有正常生效，这个时候我们需要对索引的实现原理——B+树有深刻的理解。如果SQL写的没问题，索引也正常生效了，是由于数据量过大导致的慢SQL，那就让每个表的数据量变小，即分表；实在不行，再去分库。当然了，这也上升到了服务器资源问题了。办法总比困难多，只有平时开发的时候按照这些思路去分析，总能避免写出慢SQL。

分析执行计划

分析过程很简单，不要被执行计划唬住了，就是在SQL语句前加一个explain关键字，点一下执行。该关键字会为我们分析出SQL执行的过程。
mysql> explain select * from student where id = 1

---

id: 1
select_type: SIMPLE
table: subject
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL

---

总的来说，explian会告诉我们一些信息：
1、本次 SQL 实际走的什么索引，索引如何被使用，看ref。
2、本次 SQL是一个什么样的查询，是简单的查询，还是主键查询，或者是包含子查询等。看select_type。
3、type字段是一个重点关注的字段，表示访问情况，通常用来衡量 SQL 的查询效率。其值的查询效率从最好到最差分别为：

NULL ：表示 MySQL 能够在优化器优化阶段分解查询语句，在执行阶段不用再访问表或索引。
system ：表中只有一行记录的情况，很少出现。相当于是const的特例。
const： 表示表中最多只有一行匹配记录。比较适用于包含唯一索引的情况，比如用户表中，根据某一个身份证查询，查到的用户是唯一的，所以只有一行记录。
eq_ref：表示主键索引或唯一索引的所有部分被连接使用 ，且最多只会返回一条符合条件的记录。与 const 类型非常相似，唯一的区别是 eq_ef 通常出现在联表的情况下，而 const 通常出现在单表情况下。** 这边唯一索引的所有部分指的是：唯一索引所在的表中的每一行记录都会被扫描到！**
ref ：表示使用了非唯一索引扫描，会返回匹配某个单独值的所有行。与 const 非常类似，只不过 ref 会匹配到多个记录，而 const 则只会匹配到单个记录。
ref_or_null : 类似ref，但是可以搜索值为NULL的行。
index_merge : 表示使用了索引合并的优化方法。索引合并指的是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并。
range ：范围扫描，检索给定范围的行。一般就是在where 语句中出现 between、<>、in 等的范围查询
index ： index 表示只遍历索引树，且只从索引树中获取数据。比如select查询的字段都是索引字段。
ALL ： 全表扫描

4、mysql估计要读取并检测的行数，可看rows字段。
5、table 字段表示数据来自哪张表。
6、possible_keys 字段显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用。
7、key：实际使用的索引。key_len：索引字段的长度。

哪些情况索引没生效？

这个真是老生常谈，其实就一句话。对于B+树上的原始索引数据，如果对原始数据做一些操作，诸如数值隐式类型转换操作，取非操作，like模糊操作，使用函数计算，各种表达式的计算等等都会使得索引失效。 因为操作以后的数据在B+树上并找不到，也就无法按索引查到想要的数据。
联合索引没有按照最左前缀原则也会使得索引失效，这个主要取决于联合索引B+树的一个按序存放数据的设计，查询的时候会按序对比。如果不符合最左前缀原则，那对比的时候，发现按照给定的数据找不到，只好进行全表扫描了。
一些特别需要记得索引失效的情况还有：
1、使用or关键字的时候，如果 A or B, A 和 B 之间有一个没有加索引，会导致有索引的字段索引失效！
2、使用order by 进行排序的时候，要记得排序的顺序和索引的顺序是一致的。

SELECT * FROM customers ORDER BY last_name DESC;

上述SQL语句中，使用order by 关键字针对last_name进行倒序排序。但索引last_name是按照升序（ASC）存储的，而我们尝试以降序（DESC）进行排序。虽然数据库可以通过排序结果的逆向扫描来实现降序排序，但这可能会导致索引失效，因为索引无法直接按照所需的顺序匹配。
为了避免索引失效，需要确保选择的排序列与索引列匹配，并尽量避免在排序中使用函数、表达式或非索引列。如果需要使用函数或表达式进行排序，可以考虑创建适当的函数索引来优化查询性能。
3、优化器选错了索引情况
举例说明，假设有一个 orders 表，其中包含了订单的不同属性，包括订单号 order_number（主键）、客户ID customer_id、订单日期 order_date 等属性。我们要查询特定客户在某个时间范围内的订单数量。

SELECT COUNT(*) FROM orders
WHERE customer_id = 12345 AND order_date BETWEEN '2022-01-01' AND '2022-12-31';

如果在 orders 表上存在 customer_id 列和 order_date 列的索引，优化器应该选择同时使用这两个索引进行查询。但是，优化器有可能错误地选择了仅使用其中一个索引。
例如，优化器可能选择了只使用 customer_id 索引，然后在结果集中进一步过滤日期范围。这将导致优化器扫描大量不符合日期过滤条件的记录，导致性能下降。
为了解决这个问题，我们可以引导优化器选择正确的索引，通过使用查询提示（query hint）或重构查询语句来指定优化器应选择的索引：

SELECT COUNT(*) FROM orders USE INDEX (customer_id, order_date)
WHERE customer_id = 12345 AND order_date BETWEEN '2022-01-01' AND '2022-12-31';

在这个改进后的查询中，我们使用了 USE INDEX 查询提示来告诉优化器显式使用了包含 customer_id 和 order_date 列的联合索引。
通过引导优化器选择正确的索引，我们可以提高查询性能，避免优化器选择错误的索引导致性能下降的问题。需要注意的是，引导优化器的行为需要基于实际情况进行评估，并确保所选的索引在大多数查询场景下都是最优的选择。