Mysql数据库_非常耗时的mysql语句-优快云博客

Mysql查询优化

什么是索引？

观察下面一组数字：

如果我想查找最后一个数字，那么我付出的最大查询成本是：查询10次，数据越多，查询代价越大。

如果我想查询某个范围的值，比如查找小于5的值，我需要从头到尾把每个值都需要对比一遍，最终挑出小于5的值。

如果我把上面这组数字变成如下图：有序的数据结构，这样就可以利用二分查找法

那么此时我查找某个具体的值付出的最大查询成本大概仅仅是3、4次。

如果我想查找某个范围的值，比如还是小于5，因为数据有序排列的，那么等我通过二分法确定5的位置时，那么它左边的值全部就是小于5的数据，立即就能拿到。

经过上面分析你得到什么启示：想使用某种快速查找的算法，这前提必须是建立在某种有规律的特定的数据结构之上的。而我们创建索引的过程，就是创建为了实现快速查找算法所必须的数据结构的过程。而在mysql中，想使用索引实现快速查找，你可以简单理解为：必须要求索引的数据是按顺序排列的。

使用索引和非索引的查询成本对比

整个stu表如下:

怎么查看分析查看查询成本，通过explain查看执行计划。

执行某条sql语句、不使用索引：

分析：type:all 说明mysql没有走索引，走了全表扫描，8条记录全部取出。

IO成本： 8条记录（全表）

算法成本：普通查找、必须一个个的去扫描对比。

使用索引：

分析：type:ref ref表示使用了普通索引。1条记录被取出。

IO成本： 1记录+1列索引

算法成本：二叉树查找法，通过有规律的数据结构，快速定位到某个数据，比全表扫描快。

由此可见：使用索引之后，在一般情况下，无论是IO成本还是计算查找成本都远低于全表扫描。

多列索引

需求：查出班级class_id为3且年龄小于25的人

Sql： select * from stu where age >20 and class_id=3;

可以把age建立index索引，查询计划如下：

IO 成本：4条记录

算法成本：当查找age>20走了索引。然后就把age>20的满足条件的记录都取了出去，然再按普通查找的方式扫描class_id=3

但是这仅仅够么，还不够完美：

为了尽可能的利用mysql的索引特性，我们可以建立一个多列索引。当mysql使用复合索引时，会先扫描完age列之后，然而不会再把满足age列条件的记录都取出来，而是再继续利用二叉树查找算法扫描class_id这个列，得到最终结果的索引项，取出索引中保存的地址，根据地址把表中的记录取出。

给age和 class_id建立复合索引：alter table stu add index fuhe (class_id,age);

查询后的执行计划如下：

分析：

IO成本：row为1，最终的IO成本变成了1

算法成本：相比上面只建立age单个索引，class_id=3这个条件的查询也利用上了索引，利用索引之后，提高了class_id=3的查找效率，最终又降低了最终从表取出数据的IO成本。

注意：

在当前查询语句中，建立复合索引的条件只能是(class_id,age)的顺序，不能是(age,class_id)这种顺序。因为这样的话第二列class_id索引会利用不上，最终还是走了age单个列的索引查询。

为什么？不是说复合索引中，只要使用了第一列索引，就会使用第二列索引么？

不是的，这是不对的。

试验：以这种(age,class_id)创建索引的顺序，执行下面语句，：

分析：key值为fuhe ，说明使用到了索引，看似也是使用到了索引，观察rows的值，是4，并不是1，这说明一个什么问题？其实真实的情况还是只是使用了复合索引中 age这个一单列的索引，并没有使用到class_id。

为什么？

因为你不了解复合索引的内部结构是怎样的？

比如：以该表的(class_id,age)复合索引为例,它内部结构简单说就是下面这样排列的：

mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个class_id字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的age字段进行排序。其实就相当于实现了类似sql语句中，走了 order by class_id age这样一种排序规则。

所以：第一个class_id字段索引是绝对有序的，而第二字段就是无序的了。我之前说过，想利用到索引，必须要求该列索引的数据必须是有规律的特定的数据结构，也就是在这里必须是有序的。而所以通常情况下，直接使用第二个age字段进行条件判断是用不到索引的，这就是所谓的mysql为什么要强调最左前缀原则的原因。

那么什么时候才能用到呢?

观察可知，当然是在class_id字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个class_id值为2 的age字段值是不是有序的呢。从上往下分别是15 16。
这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因，而且第一列索引必须是等值匹配。

多表查询的优化

需求：查出所有在读班级的学生

Sql: select * from stu where class_id in (select id from class);

执行计划如下：

分析：

Select_type中 DEPENDENT SUBQUERY代表这个表是子查询出来的，而且是相关子查询。

执行计划中，其实它并不是先执行in子查询语句找到id，然后再去到stu中去查复合id值的。

mysql会把in子查询转换成exists相关子查询，所以它实际等同于这条sql语句：select * from stu where exists(select id from class where stu.class_id=class.id );

而exists相关子查询的执行原理是: 循环取出外表的每一条记录与子查询中的表进行比较，比较的条件是stu.class_id=class.id 然后看外表的每条记录的class_id是否在内表的id字段存在，如果存在就行返回外表的这条记录。

是不是很类似join连接查询？

exists查询有什么弊端？
由exists执行原理可知，外表使用不了索引，必须全表扫描，因为是拿外表的数据到内表查。而且必须得使用外表的数据到内表中查（外表到里表中），顺序是固定死的。

如何优化？
建索引。但是由上面分析可知，要建索引只能在内表(class表)的id字段建，不能在外表的class_id上，因为外表是全表扫描，mysql利用不上。(当热这里class表的id字段因为是主键，已经是索引了，不用咱们创建)

这样优化够了吗？

引出了一个更细致的疑问：在双方两个表的字段上都建有索引时，到底是外表查内表的效率高，还是内表查外表的效率高？

该如何进一步优化？
把查询修改成inner join连接查询：select * from stu inner join class on stu.class_id=class.id; （但是仅此还不够，接着往下看）

为什么不用left join 和 right join？
这时候表之间的连接的顺序就被固定住了，比如左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。仍然不是最好的选择。

为什么使用inner join就可以？
inner join中的两张表，如： a inner join b，但实际执行的顺序是跟写法的顺序没有半毛钱关系的，最终执行也可能会是b连接a，顺序不是固定死的。如果on条件字段有索引的情况下，同样可以使用上索引。

那我们又怎么能知道a和b什么样的执行顺序效率更高？
答：你不知道，我也不知道。谁知道？mysql自己知道。让mysql自己去判断（查询优化器）。具体表的连接顺序和使用索引情况，mysql查询优化器会对每种情况做出成本评估，最终选择最优的那个做为执行计划。

在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高，如果两个表都建有索引的情况下，mysql同样会评估使用a表条件字段上的索引效率高还是b表的。

而我们要做的就是：把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。

Group by 和临时表的优化

先观察一个分组的sql语句的explain结果（在没有使用索引的情况下）：

extra结果: Using temporary; Using filesort 什么意思？表示查询使用了临时表、使用了排序。

为什么会产生这种情况？了解一下group by 执行原理：

1 首先mysql会把最终需要分组的结果集提取出来作为一个临时的表存放到内存空间。

2 对该临时表进行排序

3排序之后进行分组

alter table stu add index (class_id) 把class_id建立索引，利用索引：

extra结果:Using index 发现mysql直接走了索引覆盖

仅接着对max或者min进行测试：

extra结果: Using temporary; Using filesort 发现时使用了临时表、使用了排序。

为什么？

因为我们最终查询的age字段并没有在索引中，Mysql无法只通过class_id这个索引字段进行分组就能求出age这个字段的统计信息。它必须还得通过class_id这个索引上地址回去取出完整的记录。

那这样的话，岂不是多次一举，所以它不会使用索引，还不如直接把记录都取出来，使用临时表的方式进行统计。

怎么办？解决方案：

建立一个复合索引，把age也利用上。（class_id,age） class_id是索引的第一列，所以class_id是有序的数据结构，能被group by 利用上。如下图：

那么此时，group by 会直接该索引进行分组，然后对索引的age列直接统计就行了。

结果：使用上了索引，并没有产生临时表排序。

多表查询中出现的临时表现象

观察下面sql结果：

为什么会产生了临时表？

分析：mysql首先查了class表，然后再使用stu的class_id索引对stu表进行了join连接查询。这并没有什么问题，为什么此时产生了临时表呢？请注意我sql语句最后我利用age字段进行了排序: order by stu.age

mysql就把join后的结果作为临时表进行了排序。

在这里我们无法通过有效的索引来解决这个问题。

我们只能尽量保证让它只在内存中来进行这个过程，而不是在磁盘上。

什么叫在磁盘上操作，具体内容请往下看：磁盘临时表：

磁盘临时表

这个磁盘临时表跟上面讲的临时表是什么关系呢？

其实是同一个概念，无论是产生的条件还是解决的方案都是一样的。

只不过是：在mysql使用临时表的过程中，这个临时表在内存中放不下时，会自动的转换成磁盘临时表，把结果集放到磁盘上，一点一点的回读到内存中操作。这样的话，就会产生磁盘IO，那么此种的临时表效率会更加糟糕！mysql最终采用的是内存临时表还是磁盘临时表我们无从得知，我们仍然可以采用上面讲的索引方案避免临时表的产生。

但是，有时候情况并不是那么完美，就一定能用索引解决临时表的产生。而此时，我们应要尽量要避免磁盘临时表的产生，让它在内存中操作就好。

解决方案：

在我们的mysql中有两个参数为：

tmp_table_size (默认33.5M)

max_heap_table_size (默认16.7M)

mysql是否转化成为磁盘临时表的依据就是这两个参数，mysql会取这两个参数最小的那个作为依据，如果当前要操作的结果集超过了这个设定，就会自动转换成磁盘临时表，所以我们可以设置这两个参数，把它调大。一般初始会设置成百兆，当然根据实际情况。

怎么设置，在mysql中使用以下命令动态的改变：

set @@tmp_table_size=100*1024*1024;

set @@max_heap_table_size=100*1024*1024;

查看结果：

SELECT @@tmp_table_size;

SELECT @@max_heap_table_size;

然后不断的观察sql语句的执行时间是否有降低。

最后：一个真实的sql优化案例

这是阿里云上的某个项目的表查询，下面的sql语句，竟然耗时了1525秒

explain结果：

为了同学们便于快速观察该sql语句，我把它简化成如下sql语句：

select * from

a inner join b on a.uid=b.uid

inner join c on a.shopid=c.id

where a.code=0 and a.status like '%未发货%'

and b.auto_user=0 order by a.time;

我询问后所获得的一些信息如下：

a表一百六十万多条数据，数据量最大。

b表两千多条

c 表十五万条

解决思路：

由上面explain结果得知：查询a表时，虽然利用上time索引，解决了排序问题，但是并不高效，为什么？，观察type:index 说明mysql先把a表的time索引加载过来，然后倒着一个个的遍历索引项，说明：index类型表示，这个索引的查找并不是按特定的算法“跳着”查找某个索引项，而是一个个扫描索引项，然后取出表中的记录这个索引的查找并不是按然后一个个取出表中的记录。其实最终仍然走了全表的扫描，而且又加上刚开始time索引的读取成本。mysql中影响sql查询最大的障碍其实就是磁盘IO上的操作。而a表是最大的，有一百六十多万数据，这样的做法是万万不妥的。

我们解决问题的核心思路点是应该使用索引尽量减少a表数据读取的量。

观察语句得知，a表能利用上的索引有以下四个：

a.uid

a.code=0

a.status like ’%未发货%’

a.shopid

分析：

a.status是利用不上索引的,只有a.uid和a.code=0能做些文章。

到底选哪个呢？

继续询问得知：

code=0这个值在列中的重复度最高，大概占了90%，而uid值的重复度相对比code少很多，具体占比不详。

a.shopid和uid的重复度差不多，并不像code=0这么明显的高。