mysql （三）查询优化

最新推荐文章于 2023-07-27 11:30:00 发布

他大舅m

最新推荐文章于 2023-07-27 11:30:00 发布

阅读量175

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/weixin_40475396/article/details/105377130

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一：主导表(驱动表)选择

说明：在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语句的操作：传送门

他大舅m

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
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 AN...
复制链接

扫一扫

专栏目录