一:主导表(驱动表)选择
说明:在EXPLAIN结果中,第一行出现的表就是驱动表。
首先MySQL优化器要确定以谁为驱动表,也就是说以哪个表为基准,在处理此类问题时,MySQL优化器采用了简单粗暴的解决方法:哪个表的结果集小,就以哪个表为驱动表,当然MySQL优化器实际的处理方式会复杂许多,具体可以参考:MySQL优化器如何选择索引和JOIN顺序。
大家可能会遇到类似下面的问题:原本运行良好的查询语句,过了一段时间后,可能会突然变得很糟糕。一个很大可能的原因就是数据分布情况发生了变化,从而导致MySQL优化器对驱动表的选择发生了变化,进而出现索引失效的情况,所以没事最好多查查,关注一下这些情况
优化前:
SELECT * FROM news n0_ inner join news_translations n1_ ON n0_.id = n1_.translatable_id
inner join channels_news c3_ ON n0_.id = c3_.news_id
WHERE
(
(
n0_.unpublished_at IS NOT NULL AND
(
CURRENT_TIMESTAMP >= n0_.published_at AND CURRENT_TIMESTAMP < n0_.unpublished_at
)
) OR
(
CURRENT_TIMESTAMP >= n0_.published_at AND n0_.unpublished_at IS NULL
)
)
AND (n0_.status = 1 AND n0_.content_type_id = 1)
AND n0_.id NOT IN (510466, 510433, 24, 11, 10, 9, 4)
AND n0_.home_position_id IS NULL
AND n1_.locale = 'zh_CN'
AND c3_.channel_id = 1
ORDER BY n0_.published_at DESC
LIMIT 5 ;
优化前explain:
+-------+--------+-------------------------------+--------+-----------------------------------------------------------+
| table | type | key | rows | Extra |
+-------+--------+-------------------------------+--------+-----------------------------------------------------------+
| c3_ | ref | IDX_87B9249E72F5A1AA | 161590 | Using where; Using index; Using temporary; Using filesort |
| n0_ | eq_ref | PRIMARY | 1 | Using where |
| n1_ | ref | UNIQ_20FDB3302C2AC5D34180C698 | 1 | Using where |
+-------+--------+-------------------------------+--------+-----------------------------------------------------------+
优化后
SELECT * FROM news n0_ STRAIGHT_JOIN news_translations n1_ ON n0_.id = n1_.translatable_id
STRAIGHT_JOIN channels_news c3_ ON n0_.id = c3_.news_id
WHERE
(
(
n0_.unpublished_at IS NOT NULL AND
(
CURRENT_TIMESTAMP >= n0_.published_at AND CURRENT_TIMESTAMP < n0_.unpublished_at
)
) OR
(
CURRENT_TIMESTAMP >= n0_.published_at AND n0_.unpublished_at IS NULL
)
)
AND (n0_.status = 1 AND n0_.content_type_id = 1)
AND n0_.id NOT IN (510466, 510433, 24, 11, 10, 9, 4)
AND n0_.home_position_id IS NULL
AND n1_.locale = 'zh_CN'
AND c3_.channel_id = 1
ORDER BY n0_.published_at DESC
LIMIT 5 ;
优化后explain:
+-------+--------+-------------------------------+--------+--------------------------+
| table | type | key | rows | Extra |
+-------+--------+-------------------------------+--------+--------------------------+
| n0_ | range | IDX_published_at | 255440 | Using where |
| n1_ | ref | UNIQ_20FDB3302C2AC5D34180C698 | 1 | Using where |
| c3_ | eq_ref | PRIMARY | 1 | Using where; Using index |
+-------+--------+-------------------------------+--------+--------------------------+
优化前后的变化有四点:1、不再Using temporary和Using filesort;2、表的查询顺寻变了;3、查询扫描的rows增加了;4、查询时间由5s降到了0.02s。
优化前后出现的四点变化,性能显著提升,需要从mysql的关联的连接处理说起。
以下参考《高性能MySQL》
1)优化前的sql语句以channels_news为第一个关联表,找到161590条记录;2)优化后的sql语句以news表为第一关联表,找到255440条记录,比第一条sql语句查找多了9W多条。因此,优化前的sql语句的关联顺序是MySQL优化器的选择,可以让查询进行更小的嵌套循环和回溯操作。MySQL通过选择合适的关联顺序来让查询执行的成本尽可能低,重新定义关联的顺序是优化器很重要的一部分功能。不过有时候,优化器给出的并不是最优的关联顺序。这时可以使用STRAIGHT_JOIN关键字重写查询,让优化器按照你认为的最优关联顺序执行。
从优化后的explain分析结果看出,news是驱动表,结果以news表的published_at字段进行排序,所以用上了索引,避免了Using temporary和Using filesort,自然而然的,查询时间也降下来了。正如前面说的,mysql的优化器通过粗暴的小表驱动大表来选择连接的顺序,第一条sql语句扫描了161590行,第二条sql语句扫描了255440行,优化后的sql语句扫描的行数增加了。
结案陈词:造成这次sql语句查询耗时5s的原因是,sql语句order by的字段不在mysql的优化器选在驱动表上,所以导致这次关联查询排序字段上的索引没有被使用。因此,通过使用STRAIGHT_JOIN来强制制定关联查询的表顺序,以达到优化的目的。但是,有时候我们人为地指定顺序不一定比mysql的优化引擎准确,所以在使用STRAIGHT_JOIN的时候三思而后行。三、关于Mysql优化的一些建议:传送门
三、关于sum和count的用法:传送门
四、关于联合索引那些事(最左侧匹配原则):传送门
从一道有趣的题目开始分析:
假设某个表有一个联合索引(c1,c2,c3,c4)以下选项哪些字段使用了该索引:
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=? and c5=? order by c2,c3
E where c1=? and c2=? and c5=? order by c2,c3
下面我们开始:
首先创建表:
CREATE TABLE t(
c1 CHAR(1) not null,
c2 CHAR(1) not null,
c3 CHAR(1) not null,
c4 CHAR(1) not null,
c5 CHAR(1) not null
)ENGINE myisam CHARSET UTF8;
有c1到c5 5个字段,特别说明一下 字段类型都是定长char(1)类型,并且非空,字符集是utf8(与计算索引使用字节数有关)
创建索引:
alter table t add index c1234(c1,c2,c3,c4);
插入2条数据:insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2')
使用MySql Explain开始分析题目结果:
A选项:
结果可以看出,c1,c2,c3,c4均使用到了该索引,而我们对A结果稍作更改:
将c2条件去掉后:
根据索引最左原则,c2字段没有使用索引,c2之后的字段都不能使用索引。下面2图我们对比下索引最左原则:
上图结果显示直接使用c3是全表查询,无法使用该索引的,所以c3字段使用索引的前提是c1,c2两字段均使用了索引。
即是索引的最左原则(左前缀原则)。
B选项:
key_len长度说明c1,c2字段用到了该索引,Extra显示并没有使用临时表进行排序,说明排序是使用了索引的,但并没有计算在key_len值中,也没有起到连接c4的作用,说明索引到c3这里是断掉的。
排序其实是利用联合索引直接完成了的,即:使用了c1234联合索引,就已经使得c1下c2,c2下c3,c3下c4是有序的了,所以实际是排序利用了索引,c3字段并没有使用该索引。(这段写的时候总感觉有点别扭,不知道我理解的对不对,还有待更深层次的研究)
C选项:
使用group by 一般先生成临时文件,再进行排序,但是字段顺序为c2,c3时,并没有用临时表进行排序,而是利用索引排序好的;当group by字段为c3,c2时,由于与索引字段顺序不一致,所以分组和排序并没有利用到索引。
由key_len长度确定,只有c1一个字段使用了索引。
D选项:
order by 和group by 类似,字段顺序与索引一致时,会使用索引排序;字段顺序与索引不一致时,不使用索引。
由key_len长度确定,只有c1一个字段使用了索引。
E选项:
其实选项E的结果分析在上述ABCD的结果中都分析过了,这里只有c1,c2字段使用了该索引。
综上所述问题答案:
A:四个字段均使用了该索引
B:c1,c2字段使用了该索引
C:c1字段使用该索引
D:c1字段使用该索引
E:c1,c2字段使用了该索引
总结:
索引的最左原则(左前缀原则),如(c1,c2,c3,c4....cN)的联合索引,where 条件按照索引建立的字段顺序来使用(不代表and条件必须按照顺序来写),如果中间某列没有条件,或使用like会导致后面的列不能使用索引。
索引也能用于分组和排序,分组要先排序,在计算平均值等等。所以在分组和排序中,如果字段顺序可以按照索引的字段顺序,即可利用索引的有序特性。
五:mysql对json语句的操作:传送门