这篇文章上次修改于 1509 天前，可能其部分内容已经发生变化，如有疑问可询问作者。

如何知道慢查询

开启慢查询记录

第一种方法

第一种方法是永久设置，重启数据库服务也不受影响。

找到数据库配置文件

笔者用的 Mariadb 默认路径为 /etc/mysql/mariadb.conf.d/50-server.cnf

修改或添加如下几项配置文件

# 打开慢查询记录开关
slow_query_log         = ON
# 打开慢查询记录的位置
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
# 执行时间几秒才会被定义为慢查询
long_query_time        = 5

第二种方法

第二种方法是临时设置，重启数据库服务就失效了。

执行 SQL 语句

set global slow_query_log=1;

如何查看慢查询记录

# 执行 SQL 语句查看慢查询日志的存放位置。
show variables like 'slow_query%';

找到目录打开文件，里面会有慢查询的 SQL 语句。

如何优化慢查询

控制变量关闭 QueryCache

我们需要关闭 QueryCache，保证 SQL 语句每次都是调用数据库引擎取执行查询过程的，而不是直接走缓存，影响我们的对比结果。

第一种方法

执行 SQL 语句

set global query_cache_size=0;
set global query_cache_type=0;

第二种方法

修改或添加如下几项配置文件

# vi my.cnf 
query_cache_type=0 
query_cache_size=0

索引优化

分析索引使用情况

可以使用 explain 来分析 SQL 语句

# eg: 
EXPLAIN SELECT * FROM student WHERE id > 2;

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型。
- SIMPLE – 查询是一个简单的查询，没有任何子查询或数据集合。
- PRIMARY – 表示此查询是最外层的查询。
- DERIVED – 表示此查询是 FROM 子句中子查询的一部分。
- SUBQUERY – 表示此查询是子查询中的第一个。
- DEPENDENT SUBQUERY – 依赖于外部查询的子查询。
- UNCACHEABLE SUBQUERY – 不可缓存的子查询（在某些条件下，查询可缓存）。
- UNION – 查询是 UNION 的第二个或随后的查询。
- DEPENDENT UNION – 与 UNION 一样，出现在 UNION 或 UNION ALL 语句中，但是这个查询要受到外部查询的影响。
- UNION RESULT – 查询是 UNION 的结果。
table: 查询的是哪个表。
partitions: 匹配的分区。
type: 连接类型（以下类型说明性能从好到差）
- system – 该表只有零行或一行
- const – 该表只有一个匹配的行被索引。这是最快的连接类型，因为该表只需要读取一次，并且在连接其他表时可以将列的值视为常量。
- eq_ref – 联接使用索引的所有部分，索引为「主键索引」或「非空的唯一索引」。这是第二种可能的最佳联接类型。
- ref – 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询。
- fulltext – 联接使用表的 FULLTEXT 索引。
- ref_or_null – 与 ref 相同，但也包含具有该列的 null 值的行。
- index_merge – 联接使用索引列表来生成结果集。EXPLAIN 的输出的键列将包含使用的键。
- unique_subquery – IN 子查询仅从表中返回一个结果，并使用主键。
- index_subquery – 与 unique_subquery 相同，但返回多个结果行。
- range – 使用索引查找特定范围内的匹配行，通常是在使用诸如 BETWEEN、in、>、> = 等运算符将键列与常量进行比较时。
- index – 扫描整个索引树以找到匹配的行。他只会扫描索引，而不会扫描数据。
- all – 扫描整个表以查找联接的匹配行。这是最差的联接类型，通常表明表上缺少适当的索引，也是最需要优化的类型。
possible_keys: 此次查询中可能选用的索引。
key: 此次查询中确切使用到的索引。
key_len: 表示查询优化器使用了索引的字节数。
ref: 哪个字段或常数与 key 一起被使用。
rows: 显示此查询一共扫描了多少行. 这个是一个估计值。
filtered: 表示此查询条件所过滤的数据的百分比。
extra: 额外的信息。
- Using index - 直接访问索引就能够获取到所需要的数据，不需要通过索引回表。
- Using where - 表示数据库服务从存储引擎获取数据后在直接进行 where 过滤。
- Using temporary - 表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询。
- Using filesort - MySQL 中无法利用索引完成的排序。
- Using index condition - 优先去过滤索引，过滤完索引后找到所有符合索引条件的数据行。如果WHERE 还存在子句，再随后去回表过滤这些子句数据行（Mysql 5.6 添加）。
- Using index & Using where - 直接访问索引就能够获取到所需要的数据，直接在数据库服务层过滤数据。

ps: innoDB 数据库引擎默认有一个「主键的索引」索引名称为「PRIMARY」

SQL 语句如何强制不使用索引

来个新知识点，可以让我们方便对比使用索引和不使用索引的情况。而不用去频繁删除和创建索引。

# 忽略使用某索引
select * from T ignore index(index_name);
# 强制使用某索引
select * from T force index(index_name);

全局搜索索引优化对比

我的测试环境

我的树莓派 Mysql 测试环境

建立索引

alter table user add index phone_index(phone);
alter table user drop index phone_index;

查询

select * from user where phone = '15505876918';
explain select * from user where phone = '15505876918';

查询时间 32ms 使用索引 phone_index ,扫描行数 1，Using index condition

select * from user ignore index(phone_index) where phone = '15505876918';
explain select * from user ignore index(phone_index) where phone = '15505876918';

查询时间 16589ms 没有使用索引,扫描行数 4239304，Using where

小结

查询建立相应的索引，能大幅度的加快我们的查询速度，但是不建议单表超过五个索引。防止数据插入速度过慢。

过滤性不好的索引查询优化比对

建立索引

alter table user add index height_weight_index(height,weight);
alter table user drop index height_weight_index;

查询

# 使用索引
select * from user where height > 170 and weight > 70 limit 50 offset 10000;
explain select * from user where height > 170 and weight > 70 limit 50 offset 10000;

查询时间 867ms 使用索引 height_weight_index ,扫描行数 2119652，Using index condition

# 不使用索引
select * from user ignore index(height_weight_index) where height > 170 and weight > 70 limit 50 offset 10000;
explain select * from user ignore index(height_weight_index) where height > 170 and weight > 70 limit 50 offset 10000;

查询时间 137ms 没有使用索引，扫描行数 2119652，Using where

小结

接下来就有疑问了为什么使用索引比不使用索引还慢这么多。我们来分析一下。

使用索引
首先你知道索引是个 B+ 树，B+ 树有很多叶子，每一片叶子节点上数据量大小是磁盘的一页。所以搜索过程就是根据 B+ 树一个叶子一个叶子的查找，然后找到数据聚集的一些叶子，再取具体的数据。
不使用索引
全表从第一条开始循环对比。

那么为什么我们这个例子使用索引还这么慢呢？其实原因很简单，我们搜索条件太宽了，并且索引数据重复率也很高不利于查询，不用索引可能只需要几次磁盘 IO 就能满足我们的查询条件把数据返回，但是索引要根据叶子一层一层的查询经多多次磁盘 IO 才凑到数据。

大家可以动手试一试，比如把身高条件改为 185，这样索引搜索就会比不使用索引快得多。

# 978 ms
select * from user where height > 185 and weight > 100 limit 50 offset 10000;

# 1302 ms
select * from user ignore index(height_weight_index) where height > 185 and weight > 100 limit 50 offset 10000;

所以总结一点，创建索引不是万能的，还是要考虑索引的过滤性。如果过滤性太差倒不如不建立索引。

全局搜索排序分页查询优化对比

建立索引

alter table user add index name_birthday_index(name,birthday);
alter table user drop index name_birthday_index;

查询

select * from user where name like '融%' ORDER BY birthday limit 100,20;
explain select * from user where name like '融%' ORDER BY birthday limit 100,20;

查询时间 649ms 使用索引 name_birthday_index ，扫描行数 13382，Using index condition; Using filesort

看到这是不是觉得挺正常的应该没有什么需要优化的。但这次查询是随查询随回表拿数据，那么能不能更快的，我们现在 SQL 语句执行是 Using index condition; Using filesort 的方式，那么我能能不能改成 Using index; 呢？接下来我们试一试。

select id from user where name like '融%' ORDER BY birthday limit 100,20;
explain select id from user where name like '融%' ORDER BY birthday limit 100,20;

查询时间 64ms 使用索引 name_birthday_index ，扫描行数 13382，Using where; Using index; Using filesort

这次我们直接根据索引查询，没有用到索引之外的其他数据，所以不用「回表」，索引查询速度也会极快。

所以查询时能不使用 select * 就不用，用多少数据查多少。如果索引的数据能够满足业务，就不要附加其他数据。

虽然说我们查询出来的数据条数是对的，但是毕竟只有 ID ，我们怎么才能拿到其他数据呢？我想了个方案我们可以使用 ID 进行子查询，因为 ID 这个索引过滤性极好，我们又只需要 20 条数据。所以理论速度应该是极快的，我们来试一试。

select * from user where id in(select t.id from (select id from user where name like '融%' ORDER BY birthday limit 100,20) as t);
explain select * from user where id in(select t.id from (select id from user where name like '融%' ORDER BY birthday limit 100,20) as t);

查询时间 76ms 使用索引 name_birthday_index ，扫描行数 13382,查询说明太复杂了，我前面 Explain 已经说的很清楚了，大家自己分析吧！

另一种写法

select * from user join(select id from user where name like '融%' ORDER BY birthday limit 100,20) as t on user.id = t.id;
explain select * from user join(select id from user where name like '融%' ORDER BY birthday limit 100,20) as t on user.id = t.id;

查询时间 75ms 使用索引 name_birthday_index ，扫描行数 13382。

小结

根据 Explain 的分析结果，我们一定要考虑能不能用最快的方式来完成我们的业务，甚至是在微微调整业务实现逻辑的条件下给数据库减压。

全局查询不分页的搜索情况

建立索引

alter table user add index name_birthday_index(name,birthday);
alter table user drop index name_birthday_index;

查询

#SQL1
select * from user where name like '融%' ORDER BY birthday;
explain select * from user where name like '融%' ORDER BY birthday;
# 查询时间 5613ms 使用索引 name_birthday_index

#SQL2
select id from user where name like '融%' ORDER BY birthday;
explain select id from user where name like '融%' ORDER BY birthday ;
# 查询时间 461ms 使用索引 name_birthday_index

#SQL3
select * from user where id in(select t.id from (select id from user where name like '融%' ORDER BY birthday) as t);
explain select * from user where id in(select t.id from (select id from user where name like '融%' ORDER BY birthday) as t);
# 查询时间 3636ms 使用索引 name_birthday_index

以上三种情况查询数据量都是一样的 7368 行，我们可以看到 SQL1 运行时间最长，SQL2 只查询了 ID 查询时间最快，SQL3 虽然是我们所说的经过优化的写法但是因为数据量太大，所以速度也不是特别快。

小结

我们查询的时候查询数据量尽量不要太大，能分页就分页。

虚拟列

有时候我们有很多困境

比如想快速查询某一天出生的人，想查询姓或社区之类。
没有什么好办法。

Mysql 5.7 中加入了虚拟列

用法

alter table 表名 add column 虚拟列名 列数据类型 GENERATED ALWAYS AS (数据处理 SQL 语句) VIRTUAL;
# eg:
alter table user add name_first varchar(2) generated always as (left(name,1)) virtual;

创建完虚拟列就可以跟我们正常的列一样使用，该建索引建索引，都不影响。

建立索引

alter table user add index name_first_birthday_index(name_first,birthday);
alter table user drop index name_first_birthday_index;

查询

select * from user where id in(select t.id from (select id from user where name like '融%' ORDER BY birthday) as t);
explain select * from user where id in(select t.id from (select id from user where name like '融%' ORDER BY birthday) as t);

查询时间 3636ms 使用索引 name_birthday_index

select * from user where id in(select t.id from (select id from user where name_first = '融' ORDER BY birthday) as t);
explain select * from user where id in(select t.id from (select id from user where name_first = '融' ORDER BY birthday) as t);

查询时间 1602ms 使用索引 name_first_birthday_index

这次对比就很说明问题了，使用虚拟列加索引实现我们的业务流程比模糊查询的速度将近提升了一倍。

小结

如果有一些模糊查询可以使用虚拟列来解决就优先使用虚拟列来解决。

总结

优化思路

首先使用 Explain 去分析语句运行情况。
该语句能不能用索引去加快查询。
该语句能不能用过滤性好的索引去查询。
该语句能不能用唯一所索引去实现查询。
是不是可以减少查询数据量拆成几个子查询，去加快速度。

不要钻牛角尖

当然以上优化肯定不是十全十美的，我们要考虑的是核心业务、高频业务能不能快速的查询，其他业务可以再不影响核心的情况下进行优化提速。

如果数据库没有了优化的可能性，那么也可以适当考虑业务的调整或业务实现方式的调整来加速查询。

举个例子，我要查询 APP 热门榜单排名第 1000 页的数据。结果查起来太慢了，我就要优化它。其实优化他没有意义了这么冷的数据，非要窜牛角尖的优化没有必要，一般情况热门榜单这种查十页二十页也就差不多了。如果非要有这么变态的查询可以换一个思路，用户提交查询后，告诉用户需要一定时间查询后续有结果了通知他，在数据库不忙碌或在不影响核心业务的情况下慢慢查询，将结果推送给用户。

意见参考

在业务中条件查询需要建立相关索引，否则就是全表扫描。「不建议创建过多的索引」
考虑业务查询时建立的索引过滤性一定要好，否则扫描行数过多还是会慢。
如果单一索引过滤性达不到要求，可以考虑使用联合索引。
一定要注意数据的查询量，不建议太大。
需要模糊时，查询过滤性也要非常好，可以使用 MySQL 5.7 引入的虚拟列并创建联合索引来把模糊查询改为非模糊查询。

MySQL 5.5 及以下：联合索引模糊查询时，根据联合索引列的顺序从左到右。先查询联合索引树上查找符合第一个索引查询条件的 ID ，然后「回表」去主键索引树去判断其他索引查询条件是否匹配，匹配就会获取表中其他列的数据。这样查询效率就会变慢。
MySQL 5.6 及以上：联合索引模糊查询时，会查询联合索引树上查找符合查询条件的 ID ，然后「回表」去主键索引获取表中其他列的数据。从而根据联合索引优化查询效率。

需要注意一点

如果使用 like "%name%" 这种搜索是不会使用索引的。

名次解释

回表：去主键索引树去查询。

MySQL 如何优化慢查询

如何知道慢查询

开启慢查询记录

第一种方法

第二种方法

如何查看慢查询记录

如何优化慢查询

控制变量关闭 QueryCache

第一种方法

第二种方法

索引优化

分析索引使用情况

SQL 语句如何强制不使用索引

全局搜索索引优化对比

我的测试环境

建立索引

查询

小结

过滤性不好的索引查询优化比对

建立索引

查询

小结

全局搜索排序分页查询优化对比

建立索引

查询

小结

全局查询不分页的搜索情况

建立索引

查询

小结

虚拟列

用法

建立索引

查询

小结

总结

优化思路

不要钻牛角尖

意见参考

名次解释

没有评论