SQL优化

SQL优化（默认Mysql）

---

---

前言

SQL优化是一个非常庞大的话题。进行SQL优化需要先定位到慢SQL，再进行SQL执行效率的分析

---

提示：以下是本篇文章正文内容，下面案例仅供参考

一、SQL执行效率分析

假设请求接口时需要很长的时间才能返回结果，很可能接口中大部分逻辑的处理时间都使用在了SQL查询上

1，找到慢SQL

可以根据慢查询日志查找慢SQL，执行完的SQL都会在日志中
在mysql中，默认会将执行时间超过10s的SQL写到慢查询日志中，这个时间可以用long_query_time参数自行调整
开启慢查询（默认关闭）：set global slow_query_log = on
假设超过3s就是慢SQL：set global long_query_time = 3，也可以设置为0，记录所有语句
如果想要查看还未执行完成的慢SQL，可以使用show processlist

2，分析慢SQL

explain是分析慢SQL最常用最方便的手段
EXPLAIN select * from union_test where name = 'asd'

关键字段：
selectType: 简单查询还是复杂查询
type：表连接类型，用于判断SQL执行效率
possible_keys：可能用到的索引
key：实际用到的索引
key_len：用到的索引的长度，联合索引时，可以借此判断使用了多少个索引
rows：扫描的行
extra：额外信息，排序时会有该信息。如Using filesort，使用了文件排序，数据少时从内存排序，较大时从磁盘排序

type比较核心值：
system：理论中最好的情况，比如表里一条数据
const：根据主键或唯一索引查询，只能找到一条
eq-ref：有表连接的情况，根据主键和唯一索引查询
ref：根据普通索引查询
range：根据索引范围查询
index：索引文件全扫描，需要优化，效率比all好一点
all：全表扫描

二、SQL优化

1，对where字段做函数操作，不会走索引。比如 select * form test where date(create_time) = ‘2023-01-01 10’
原因：索引树中存放的是具体的值，如果拿函数操作之后的值去匹配，可能匹配不到，或者匹配到错误的值。因此放弃走索引，全表扫描
优化后：select * form test where create_time >= ‘2023-01-01 10:00:00’ and create_time < ‘2023-01-01 11:00:00’
2，隐士转换不会走索引，比如存储字段为varchar类型，但是查询时根据数字查询，虽然也可能查到，但是数据库做了数据转换，不会走索引（相当于做了函数操作）
3，模糊匹配通配符放在前面不走索引
4，查询范围太大不走索引：mysql优化器会根据检索比例判断是否走索引，比如一次查询数据过大时，不走索引。可以适当降低查询范围
5，对查询字段做计算不走索引。例如：select * from test where qty - 1 = 100
应优化成 select * from test where qty = 100 + 1
6，使用联合索引时必须遵循最左原则，联合主键则不需要。
6.1，联合索引（a, b, c）：select * from test where a = ‘a’ and b = ‘b’ and c = ‘c’
即使b使用了范围查询，联合索引仍然全部生效
6.2，select * from test where a = ‘a’ and b = ‘b’ order by c，仍然使用全部的联合索引
6.3，select * from test where a = ‘a’ and b in () order by c，使用a和b的联合索引，c不会被用到
6.4，联合索引使用or连接则不走索引
7，排序时最好使用索引字段，利用有序索引返回有序数据。如果是多个字段，可以配置联合索引，使用最左原则，如果使用fileSort排序，可以适当分配内存排序的空间
8，使用范围查询后在排序，无法使用索引排序，联合索引a, b, c：
select * from test where a > 100 order by b，where查询时会使用索引a，但不会用b索引排序，需要额外排序
9，对索引进行混合排序不走索引，比如联合索引a, b，order by a asc, b desc无法使用索引排序
10，根据索引字段a进行or查询：where a = ‘a’ or a = ‘a1’，会使用索引，但效率从ref降为range
11，关联查询时，使用索引字段关联，并且遵循小表驱动大表
12，count()，从mysql 5.7.18之后，count()遍历二级索引数据统计数量，优于统计主键。innoDB会临时去计算，myisam在没有where的情况下会快于innoBD。count(*)与count(1)没区别，如果经常需要统计，可以使用redis，或者建立计数表
13，如果使用IS NULL 或者IS NOT NULL索引会不生效

其实在使用mybatis的xml进行批量新增时，效率并不高，一次插入十几条时没有影响，但数据量较大时，可以使用sqlSessionFactory开始批量执行，在一个sqlSession中批量提交SQL

---

总结

根据索引查询的本质，其实就是根据主键查询。
在构建出的二级索引树中，叶子节点上存储的是主键，根据索引找到主键，通常情况我们需要查询出很多字段的数据，而主键索引树叶子节点存储的是整行数据，找到主键后再去主键索引树获取整行数据，继而返回查询结果，这就是回表
使用联合索引查询如果不回表，应该就是最好的查询效率，因为二级索引树比主键索引树要小