共计 4396 个字符，预计需要花费 11 分钟才能阅读完成。
慢查询日志 开启撒网模式
开启了 MySQL 慢查询日志之后，MySQL 会自动将执行时间超过指定秒数的 SQL 统统记录下来，这对于搜罗线上慢 SQL 有很大的帮助。
SHOW VARIABLES LIKE ‘slow%’
以我刚安装的 mysql5.7 为例 查询结果是这样子的：
slow_launch_time：表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加
slow_query_log：是否开启慢查询日志 ON 开启，OFF 关闭 默认没有开启
slow_query_log_file：日志保存路径
SHOW VARIABLES LIKE ‘long%’
long_query_time：达到多少秒的 sql 就记录日志
客户端可以用 set 设置变量的方式让慢查询开启，但是个人不推荐，因为真实操作起来会有一些问题，比如说，重启 MySQL 后就失效了，或者是开启了慢查询，我又去改变量值，它就不生效了。
编辑 MySQL 的配置文件：
vim /etc/my.cnf
加入如下三行：
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/localhost-CentOS-slow.log
long_query_time=3
我这里设置的是 3 秒
重启 MySQL
systemctl restart mysqld;
服务器开一个监控：
tail -f /var/lib/mysql/localhost-centos-slow.log
客户端走一条 SQL：
SELECT SLEEP(3)
此时发现 sql 已经被记录到日志里了。（有时候不一定，我看到很多博客讲的是超过指定秒数，但我实验得出的结果是达到指定秒数）
EXPLAIN 点对点分析你
explain 是一个神奇的命令，可以查看 sql 的具体的执行计划。
以一条联查 sql 为例：
SELECT a.id,a.cn_name,a.role_id,r.name
FROM tb_usr_admins a
INNER JOIN tb_base_roles r ON r.id=a.role_id
WHERE a.cn_name=” 接单人员 ”
查询结果是：
加上 explain 命令来执行：
EXPLAIN
SELECT a.id,a.cn_name,a.role_id,r.name
FROM tb_usr_admins a
INNER JOIN tb_base_roles r ON r.id=a.role_id
WHERE a.cn_name=” 接单人员 ”
查询结果是：
这就是这条 SQL 的执行计划，下面来说明一下这个执行计划怎么看
id：代表优先级 id 值越大，越先执行，id 值相同，从上往下执行。（比如示例的这条 sql 的执行计划，就是先执行第一行，再执行第二行）
select_type：表示 select 类型 取值如下
simple 简单表 即不使用表连接或者子查询
primary 包含 union 或者子查询的主查询 即外层的查询
union UNION 中的第二个或者后面的查询语句
subquery 一般子查询中的子查询被标记为 subquery，也就是位于 select 列表中的查询
derived 派生表 该临时表是从子查询派生出来的
等等
type：表示 MySQL 在表中查找数据的方式，或者叫访问类型，以下对于 type 取值的说明 从上往下性能由最差到最好
all: 全表扫描，MySQL 遍历全表来找到匹配的行
index：索引全扫描，MySQL 遍历挣个索引来查询匹配的行
range：索引范围扫描，常见于 <、<=、>、>=、between 等操作符
ref：使用非唯一索引或唯一索引的前缀扫描，返回匹配的单行数据
eq_ref：类似 ref，区别就在于使用的索引是唯一索引，简单来说，就是多表连接中使用 primary key 或者 unique index 作为关联条件。
const/system：单表中最多有一个匹配行，查询起来非常迅速，常见于根据 primary key 或者唯一索引 unique index 进行的单表查询
null：mysql 不用访问表或者索引，直接就能够得到查询的结果，例如 select 1+2 as result。
possible_keys：表示查询时可能使用的索引
key：表示实际使用的索引
key_len：使用到索引字段的长度
rows：扫描数量
Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息，常用取值如下：
Using index：直接访问索引就取到了数据，高性能的表现。
Using where：直接在主键索引上过滤数据，必带 where 子句，而且用不上索引
Using index condition：先条件过滤索引，再查数据，
Using filesort：使用了外部文件排序 只要见到这个 就要优化掉
Using temporary：创建了临时表来处理查询 只要见到这个 也要尽量优化掉
优化争议无数的 count()
统计列与统计行？
COUNT() 是一个特殊的函数，有两种不同的作用，它可以统计某个列值的数量，也可以统计行数。
在统计列值的时候要求列值是非空的，也就是不统计 null。
当我们统计行的时候，常见的是 COUNT(*)，这种情况下，通配符 * 并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数
解密 MyiSAM 的‘快’
这是一个容易产生误解的事情：MyiSAM 的 count() 函数总是非常快。
不过它是有前提条件的，条件是没有任何 where 条件的 count(*) 才非常快，因为此时无须实际的去计算表的行数，mysql 可以利用存储引擎的特性直接获得这个值，如果 mysql 知道某列不可能有 null 值，那么 mysql 内部会将 count(列) 表达式优化为 count(*)。
当统计带有 where 条件的查询，那么 mysql 的 count() 和其他存储引擎就没有什么不同了。
COUNT(1)、COUNT(*)、COUNT(列)
（先提前申明，本人是在 innodb 库里做的实验。）
1.count(1) 和 count(*) 直接就是统计主键，他们两个的效率是一样的。如果删除主键，他们都走全表扫描。
2. 如果 count(列) 中的字段是索引的话，count(列) 和 count(*) 一样快，否则 count(列) 走全表扫描。
优化 order by 语句
MySQL 的排序方式
优化 order by 语句就不得不了解 mysql 的排序方式。
1. 第一种通过有序索引返回数据，这种方式的 extra 显示为 Using Index, 不需要额外的排序，操作效率较高。
2. 第二种是对返回的数据进行排序，也就是通常看到的 Using filesort，filesort 是通过相应的排序算法，将数据放在 sort_buffer_size 系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。
filesort 的优化
了解了 MySQL 排序的方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where 条件和 order by 使用相同的索引。
1. 创建合适的索引减少 filesort 的出现。
2. 查询时尽量只使用必要的字段，select 具体字段的名称，而不是 select * 选择所有字段，这样可以减少排序区的使用，提高 SQL 性能。
优化 group by 语句
为什么 order by 后面不能跟 group by ?
事实上，MySQL 在所有的 group by 后面隐式的加了 order by，也就是说 group by 语句的结果会默认进行排序。
如果你要在 order by 后面加 group by，那结果执行的 SQL 是不是这样：select * from tb order by … group by … order by …？这不是搞笑吗？
禁止排序
既然知道问题了，那么就容易优化了，如果查询包括 group by 但又不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以指定 order by null 禁止排序。
例如：
select * from tb group by name order by null;
优化 limit 分页
一个非常常见又非常头痛的场景：‘limit 1000,20’。
这时 MySQL 需要查询 1020 条记录然后只返回最后 20 条，前面的 1000 条都将被抛弃，这样的代价非常高。如果所有页面的访问频率都相同，那么这样的查询平均需要访问半个表的数据。
第一种思路 在索引上分页
在索引上完成分页操作，最后根据主键关联回原表查询所需要的其他列的内容。
例如：
SELECT * FROM tb_user LIMIT 1000,10
可以优化成这样：
SELECT * FROM tb_user u
INNER JOIN (SELECT id FROM tb_user LIMIT 1000,10) AS b ON b.id=u.id
第二种思路 将 limit 转换成位置查询
这种思路需要加一个参数来辅助，标记分页的开始位置：
SELECT * FROM tb_user WHERE id > 1000 LIMIT 10
优化子查询
子查询，也就是查询中有查询，常见的是 where 后面跟一个括号里面又是一条查询 sql
尽可能的使用 join 关联查询来代替子查询。
当然 这不是绝对的，比如某些非常简单的子查询就比关联查询效率高，事实效果如何还要看执行计划。
只能说大部分的子查询都可以优化成 Join 关联查询。
改变执行计划
提高索引优先级
use index 可以让 MySQL 去参考指定的索引，但是无法强制 MySQL 去使用这个索引，当 MySQL 觉得这个索引效率太差，它宁愿去走全表扫描。。。
SELECT * FROM tb_user USE INDEX (user_name)
注意：必须是索引，不能是普通字段，（亲测主键也不行）。
忽略索引
ignore index 可以让 MySQL 忽略一个索引
SELECT * FROM tb_user IGNORE INDEX (user_name) WHERE user_name=” 张学友 ”
强制使用索引
force index 使用了 force index 之后 尽管效率非常低，MySQL 也会照你的话去执行
SELECT * FROM tb_user FORCE INDEX (user_name) WHERE user_name=” 张学友 ”
个人分享
查看执行计划时建议依次观察以下几个要点：
1.SQL 内部的执行顺序。
2. 查看 select 的查询类型。
3. 实际有没有使用索引。
4.Extra 描述信息
PS: 一定要养成查看执行计划的习惯，这个习惯非常重要。
：
