mysql 创建完索引之后 ，查询反而变慢了，这是为什么？？？？？

我们先来看第一个阶段，MySQL慢的诊断思路，一般我们会从三个方向来做：

第一个方向是MySQL内部的观测

第二个方向是外部资源的观测

第三个方向是外部需求的改造

1.1 MySQL 内部观测

我们来看MySQL内部的观测，常用的观测手段是这样的，从上往下看，第一部分是Processlist，看一下哪个SQL压力不太正常，第二步是explain，解释一下它的执行计划，第三步我们要做Profilling，如果这个SQL能再执行一次的话, 就做一个Profilling，然后高级的DBA会直接动用performance_schema ，MySQL 5.7 以后直接动用sys_schema，sys_schema是一个视图，里面有便捷的各类信息，帮助大家来诊断性能。再高级一点，我们会动用innodb_metrics进行一个对引擎的诊断。

除了这些手段以外，大家还提出了一些乱七八糟的手段，我就不列在这了，这些是常规的一个MySQL的内部的状态观测的思路。除了这些以外，MySQL还陆陆续续提供了一些暴露自己状态的方案，但是这些方案并没有在实践中形成套路，原因是学习成本比较高。

1.2 外部资源观测

外部资源观测这部分，我引用了一篇文章，这篇文章的二维码我贴在上面了。这篇文章是国外的一个神写的，标题是：60秒的快速巡检，我们来看一下它在60秒之内对服务器到底做了一个什么样的巡检。一共十条命令，这是前五条，我们一条一条来看。

1.uptime，uptime告诉我们这个机器活了多久，以及它的平均的负载是多少。

2.dmesg -T | tail，告诉我们系统日志里边有没有什么报错。

3.vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap有没有使用。

4. mpstat -P ALL，告诉我们CPU压力在各个核上是不是均匀的。

5.pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。

我们来看一下后五条：

首先是iostat-xz 1，查看IO的问题，然后是free-m内存使用率，之后两个sar，按设备网卡设备的维度，看一下网络的消耗状态，以及总体看TCP的使用率和错误率是多少。最后一条命令top，看一下大概的进程和线程的问题。

这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源。

1.3 外部需求改造

第三个诊断思路是外部的需求改造，我在这里引用了一篇文档，这篇文档是MySQL的官方文档中的一章，这一章叫Examples of Common Queries，文档中介绍了常规的SQL怎么写, 给出了一些例子。文章的链接二维码在slide上。

我们来看一下它其中提到的一个例子。

它做的事情是从一个表里边去选取，这张表有三列，article、dealer、price，选取每个作者的最贵的商品列在结果集中，这是它的最原始的SQL，非常符合业务的写法，但是它是个关联子查询。

关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询，大家可以看到中间的子查询和外边的查询之间是没有关联性的。

第三步，会教大家直接把子查询拿掉，然后转成这样一个SQL，这个就叫业务改造，前后三个SQL的成本都不一样，把关联子查询拆掉的成本，拆掉以后SQL会跑得非常好，但这个SQL已经不能良好表义了，只有在诊断到SQL成本比较高的情况下才建议大家使用这种方式。

为什么它能够把一个关联子查询拆掉呢？

这背后的原理是关系代数，所有的SQL都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉。

上面的这篇文档是一个大学的教材，它从头教了关于代数和SQL之间的关系。然后一步步推导怎么去简化这句SQL。

第一，MySQL本身提供了很多命令来观察MySQL自身的各类状态，大家从上往下检一般能检到SQL的问题或者服务器的问题。

第二，从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就这几种，观测手法也就那么几种，我们把服务器的资源全部都观察一圈就可以了。

第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写SQL，这个成本会下降的非常快，这个是常规的MySQL慢的诊断思路。

MySQL的最佳是单表百万级,一旦上到千万级就慢了,只能分表,分表不行就集群或者换数据库吧。

1.SQL你基础不怎么好 ，sql中尽量少使用 select *

2.索引字段类型注意下

这两个优化好 一般速度不会很慢 ，再慢的话 看下你数据库服务器吧

1. 执行计划中明明有使用到索引，为什么执行还是这么慢？

2. 执行计划中显示扫描行数为 644，为什么 slow log 中显示 100 多万行？

a. 我们先看执行计划，选择的索引 “INDX_BIOM_ELOCK_TASK3(TASK_ID)”。结合 sql 来看，因为有 "ORDER BY TASK_ID DESC" 子句，排序通常很慢，如果使用了文件排序性能会更差，优化器选择这个索引避免了排序。

那为什么不选 possible_keys:INDX_BIOM_ELOCK_TASK 呢？原因也很简单，TASK_DATE 字段区分度太低了，走这个索引需要扫描的行数很大，而且还要进行额外的排序，优化器综合判断代价更大，所以就不选这个索引了。不过如果我们强制选择这个索引（用 force index 语法），会看到 SQL 执行速度更快少于 10s，那是因为优化器基于代价的原则并不等价于执行速度的快慢；

b. 再看执行计划中的 type:index，"index" 代表 “全索引扫描”，其实和全表扫描差不多，只是扫描的时候是按照索引次序进行而不是行，主要优点就是避免了排序，但是开销仍然非常大。

Extra:Using where 也意味着扫描完索引后还需要回表进行筛选。一般来说，得保证 type 至少达到 range 级别，最好能达到 ref。

在第 2 点中提到的“慢日志记录Rows_examined: 1161559，看起来是全表扫描”，这里更正为“全索引扫描”，扫描行数确实等于表的行数；

c. 关于执行计划中：“rows：644”，其实这个只是估算值，并不准确，我们分析慢 SQL 时判断准确的扫描行数应该以 slow log 中的 Rows_examined 为准。

4. 优化建议：添加组合索引 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)

优化过程：

TASK_DATE 字段存在索引，但是选择度很低，优化器不会走这个索引，建议后续可以删除这个索引：

select count(*),count(distinct TASK_DATE) from T_BIOMA_ELOCK_TASK+------------+---------------------------+| count(*) | count(distinct TASK_DATE) |+------------+---------------------------+| 1161559 | 223 |+------------+---------------------------+

在这个 sql 中 REL_DEVID 字段从命名上看选择度较高，通过下面 sql 来检验确实如此：

select count(*),count(distinct REL_DEVID) from T_BIOMA_ELOCK_TASK+----------+---------------------------+| count(*) | count(distinct REL_DEVID) |+----------+---------------------------+| 1161559 | 62235 |+----------+---------------------------+

由于有排序，所以得把 task_id 也加入到新建的索引中，REL_DEVID,task_id 组合选择度 100%：

select count(*),count(distinct REL_DEVID,task_id) from T_BIOMA_ELOCK_TASK+----------+-----------------------------------+| count(*) | count(distinct REL_DEVID,task_id) |+----------+-----------------------------------+| 1161559 | 1161559 |+----------+-----------------------------------+

在测试环境添加 REL_DEVID，TASK_ID 组合索引，测试 sql 性能：alter table T_BIOMA_ELOCK_TASK add index idx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)

添加索引后执行计划：

这里还要注意一点“隐式转换”：REL_DEVID 字段数据类型为 varchar，需要在 sql 中加引号：AND T.REL_DEVID = 000000025xxx >>AND T.REL_DEVID = '000000025xxx'

执行时间从 10s+ 降到 毫秒级别：

1 row in set (0.00 sec)

结论

一个典型的 order by 查询的优化，添加更合适的索引可以避免性能问题：执行计划使用索引并不意味着就能执行快。

---