查询优化 - 掘金

相对于insert、update等DML操作，查询操作更加频繁，如果查询耗时长，会严重影响用户体验。

慢查询定位

慢查询参数

通过以下命令设置慢查询日志：

# 开启慢查询日志
SET global slow_query_log = ON
# 指定慢查询日志的文件名称
SET global slow_query_log_file = 'ABC-slow.log'
# 记录没有使用索引的sql语句
SET global log_queries_not_using = ON
# 设置阈值，单位：秒。执行时间超过阈值，则认为是慢sql
SET long_query_time = 10

查看慢查询日志

Time：日志记录时间
User@Host：执行的用户及主机
Query_time：执行时间，单位是秒（的确是秒，不用怀疑。）
Lock_time：锁表表示，单位是秒
Rows_sent：发送给请求方的记录数，结果数量
Rows_examined：语句扫描的记录条数
SET timestamp：语句执行的时间点
select * from dept：执行的sql语句

慢查询优化

索引与慢查询的关系

如何判断是否为慢查询
- MySQL判断一条语句是否为慢查询，主要依据是sql语句的执行时间，当执行时间超过long_query_time，那么就是慢查询语句。默认值为10s
如何判断是否使用索引
- 可以通过explain查看执行计划，如果key列的值为NULL，说明没有使用索引
使用了索引，查询速度一定会快吗？
- 答案是否，如果需要扫描整个索引树，速度一样慢。比如：
- ```
select * from t1 where id > 0
```
  虽然使用了主键索引，但是还是从叶子节点的最左边扫描整个索引树，查询速度依然很慢。

查询是否使用索引，只是表示一个sql的执行过程；而是否为慢查询，由执行时间决定。因此，它们俩没有必然的关系。

我们应该关心的是，使用索引是否能够减少查询扫描的行数，这与索引的过滤性相关。

索引优化

在我们看来，只要sql语句使用了索引，查询速度就会变快。这是因为索引树底层使用的是B+树数据结构，搜索时间复杂度可以达到O(logN)，且B+树相对于二叉树高度变小，物理页存放的数据元素更多，大大减少磁盘IO。所以，查询语句要尽量使用到索引，且该索引可以有效减少扫描行数。

优化点一：选择性高

索引的选择性是指，不重复的索引值和数据表的记录总数的比值，范围在0~1之间，选择性最好是唯一索引，也是性能最高的。选择性高的索引可以查询时有效过滤掉更多的行。因此，在创建索引时，优先考虑过滤性高的字段

优化点二：覆盖索引

如果查询的字段不在辅助索引树上，需要回表查询进行磁盘IO操作，这样比较耗费时间的。因此，对于经常查询的字段，可以按照实际情况，创建联合索引，把需要查询的字段都覆盖到。

优化点三：最左匹配原则

我们知道，联合索引在B+树上的存储结构是，每个节点元素都包含n个索引列，元素间都是先按照第1列排序，如果相同，则按照第2列，以此类推，依次排序。因此，我们要选择经常出现在where条件中字段作为主列，避免索引失效

注意点：索引失效

查询语句尽量使用索引，这样有利于提高查询速度。以下几种情况会让索引失效：

or关键字

除非所有查询字段都带有索引，否则不会使用索引

# 只有col1和col2都创建索引，查询语句才使用索引
select col from t1 where col1 = '123' or col2 < 10

没有使用联合索引的主列
模糊匹配，使用%开头
where中索引列有表达式运算或者函数，要分情况考虑
- 如果查询字段是索引，那么是会走索引，但是效率不高，因为遍历整个索引树
- 如果查询字段不是索引，则不会走索引，全表扫描
隐式转换，这个不一定不走索引

分页查询优化

问题

查询分页时，一般都是使用limit关键字

select * from table limit [offset] rows

不做实验，直接来结果。

偏移量（offset）保持不变，当rows超过某个阈值，rows越大，查询时间越长。

返回记录数（rows）保持不变，当offset超过某个阈值，offset越大，查询时间急剧增加

方案

优化方案包括使用覆盖索引，和利用子查询。

使用覆盖索引，避免回表，可减少检索时间

# 优化前
select * from t1 limit 1000, 100
# 优化后
select id from t1 limit 1000, 100

利用子查询

# 优化前
select * from t1 limit 1000, 100
# 优化后：先查询到第1000条数据的主键，再根据主键做范围查询
select * from t1 where id >= (select id from t1 limit 1000, 1) limit 100