一、EXPLAIN 输出执行计划

在select语句前加上explain就可以了(MySQL 5.6开始，不仅仅支持select )

1) id

标识select，id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。

2)select_type

用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

select_常用的值：

• SIMPLE:简单 SELECT (不使用 UNION 或子查询)
• PRIMARY:最外面的 SELECT
• UNION:UNION 中的第二个和随后的 SELECT 语句
• DEPENDENT UNION: UNION 中的第二个或后面的 SELECT 语句,取决于外面的查询
• UNION RESULT: UNION 的结果
• SUBQUERY:子查询中的第一个 SELECT
• DEPENDENT SUBQUERY:子查询中的第一个 SELECT ,取决于外面的查询
• DERIVED:导出表(派生表)的 SELECT ( FROM 子句的子查询)
• UNCACHEABLE SUBQUERY：子查询中的第一个 select 语句，同时意味着 select 中的某些特性阻止结果被缓存于一个 Item_cache 中
• UNCACHEABLE UNION：满足此查询是 UNION 中的第二个或者随后的查询，同时意味着 select 中的某些特性阻止结果被缓存于一个 Item_cache 中

3） table列

• table列输出的行所引用的表;
• 显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为 null，如果显示为尖括号括起来的 <derived N>就表示这个是临时表;

4）type列

type列：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

• system ：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
• const ：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
• eq_ref ：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。使用这种索引，MySQL知道最多只返回一条符合条件的记录。
• ref ：它是一种索引访问（有时也叫做索引查找），它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。把它叫做ref是因为索引要跟某个参考值相比较。此类索引访问只有当使用非唯一性索引或者唯一索引的非唯一性前缀时才会发生。
• range ：范围扫描就是一个有限制的索引扫描，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
• index ：这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。它的主要优点是避免了排序；最大优点是要承担按索引次序读取整个表的开销。如果在EXTRA列中看到using index,说明MySQL正在使用覆盖索引，它比按索引次序全表扫描的开销要少的多。
• all ：全表扫描，将遍历全表以找到匹配的行

5） possible_keys 列

possible_keys 列：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

6） key列

key列：实际使用的索引。如果为NULL，则没有使用索引。

• 很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句 中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
• 查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

7） key_len列

key_len列：使用的索引的长度。在不损失精确性的情况下，长度越短越好。

留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

8） ref列

ref列：显示使用索引哪个列或常数与key一起从表中选择行。

9）rows列

rows列：这里是执行计划中估算的扫描行数，不是精确值。

10） Extra列

Extra列：关于MYSQL如何解析查询的额外信息。

• Using filesort：
  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
• Using temporary：
  使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
• Using index）：
  表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
• Using where ：
  表明使用了where过滤
• Using join buffer ：
  表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
• impossible where ：

where子句的值总是false，不能用来获取任何元组
> - select tables optimized away ：
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
> - distinct ：
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

二、慢查询

1）分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。

2）关于慢查询日志，主要涉及到下面几个参数：
- slow_query_log ：是否开启慢查询日志功能（必填）
- long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填）
- log-slow-queries ：慢查询日志文件（不可填），自动在 \data\ 创建一个 [hostname]-slow.log 文件
也就是说，只有满足以上三个条件，“慢查询功能”才可能正确开启或关闭。

3）两种设置方式
第一种以命令行的方式，设置参数，不需要重启 MySQL 服务，注意权限和参数作用域：

# slow_query_log 需要超级权限
mysql> set global slow_query_log = ON;
# 设置查询“超时”时间
mysql> set GLOBAL long_query_time = 1;

第二种以配置文件的方式设置，需要重启 MySQL 服务。在 my.ini 中的 [mysqld] 下添加：

slow_query_log = ON
long_query_time = 1

小结：生产环境请关闭“慢查询日志”功能，节约空间
命令方式 不需要重启 MySQL 服务，而配置文件方式 需要重启

4）慢查询性能问题在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：
a、索引没有设计好；
b、SQL 语句没写好；
c、MySQL 选错了索引。
第一种场景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。
第二种可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。
第三种应急方案就是给这个语句加上 force index。同样地，使用查询重写功能，给原来的语句加上 force index，也可以解决这个问题。

索引优化查看我的另一篇博文：https://blog.csdn.net/weixin_47061482/article/details/115183279

三、短连接风暴

1）第一种方法：先处理掉那些占着连接但是不工作的线程。

需要注意，在 show processlist 的结果里，踢掉显示为 sleep 的线程，可能是有损的。会话是 Sleep 状态。还要看事务具体状态，可以查 information_schema 库的 innodb_trx 表。

id=4 和 id=5 的两个会话都是 Sleep 状态，但是，trx_mysql_thread_id=4，表示 id=4 的线程还处在事务中。

因此，如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。

从服务端断开连接使用的是 kill connection + id 的命令， 一个客户端处于 sleep 状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

2）第二种方法：减少连接过程的消耗。

一种可能的做法，是让数据库跳过权限验证阶段。
跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。风险极高，不建议使用。在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。

四、count(*)

在不同的 MySQL 引擎中，count(*) 有不同的实现方式。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高==；而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

在执行 count(*) 操作的时候还是做了优化的。InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

show table status采用采样估算计算的行数TABLE_ROWS，官方文档说误差可能达到 40% 到 50%。如下，不准确不能替代count(*)

总结：MyISAM 表虽然 count(*) 很快，但是不支持事务；show table status 命令虽然返回很快，但是不准确；InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

• 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
• 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
• 对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。
• 但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以我建议你，尽量使用 count(*)。*

五、order by

排序这个动作，可能在内存中完成，也可能需要使用外部排序。

1）sort_buffer_size

sort_buffer_size就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成number_of_tmp_files 就是 0。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。

2）rowid 排序算法

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。Eg：city、name、age 这三个字段的定义总长度是 36，把 max_length_for_sort_data 设置为 16，新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id，最后遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

3）全字段排序

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

4）随机消息

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。
tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。
随机排序方法
Eg：取得这个表的主键 id 的最大值 M 和最小值 N;用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;取不小于 X 的第一个 ID 的行。这个方法效率很高，因为取 max(id) 和 min(id) 都是不需要扫描索引的，而第三步的 select 也可以用索引快速定位，可以认为就只扫描了 3 行。而如果你直接使用 order by rand()，这个语句需要 Using temporary 和 Using filesort，查询的执行代价往往是比较大的。所以，在设计的时候你要尽量避开这种写法。比较规范的用法就是：尽量将业务逻辑写在业务代码中，让数据库只做“读写数据”的事情。

六、查询慢

查询长时间不返回：一般碰到这种情况的话，大概率是表 t 被锁住了。接下来分析原因的时候，一般都是首先执行一下 show processlist 命令，看看当前语句处于什么状态。

1）等 MDL 锁

MySQL 5.7 版本修改了 MDL 的加锁策略，所以就不能复现这个场景了。找到谁持有 MDL 写锁，然后把它 kill 掉。

2）等 flush

出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。

3）等行锁

访问记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的 select 语句就会被堵住。

4）查询慢

select * from t where id=1 lock in share mode

带 lock in share mode 的 SQL 语句，是当前读。因此会直接读到 1000001 这个结果，所以速度很快；
而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。

七、能不能使用 join

如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。总是应该使用小表做驱动表。
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。