Mysql关联查询优化器理解
来源:互联网 发布:surge for mac 配置 编辑:程序博客网 时间:2024/04/30 10:35
原文地址是http://www.jb51.net/article/66999.htm
问题
通过「SHOW FULL PROCESSLIST」语句很容易就能查到问题SQL,如下:
SELECT
post.*
FROM
post
INNER
JOIN
post_tag
ON
post.id = post_tag.post_id
WHERE
post.status = 1
AND
post_tag.tag_id = 123
ORDER
BY
post.created
DESC
LIMIT 100
说明:因为post和tag是多对多的关系,所以存在一个关联表post_tag。
试着用EXPLAIN查询一下SQL执行计划(篇幅所限,结果有删减):
+
----------+---------+-------+-----------------------------+
|
table
|
key
|
rows
| Extra |
+
----------+---------+-------+-----------------------------+
| post_tag | tag_id | 71220 | Using
where
; Using filesort |
| post |
PRIMARY
| 1 | Using
where
|
+
----------+---------+-------+-----------------------------+
下面给出优化后的SQL,唯一的变化就是把连接方式改成了「STRAIGHT_JOIN」:
SELECT
post.*
FROM
post
STRAIGHT_JOIN post_tag
ON
post.id = post_tag.post_id
WHERE
post.status = 1
AND
post_tag.tag_id = 123
ORDER
BY
post.created
DESC
LIMIT 100
试着用EXPLAIN查询一下SQL执行计划(篇幅所限,结果有删减):
+
----------+----------------+--------+-------------+
|
table
|
key
|
rows
| Extra |
+
----------+----------------+--------+-------------+
| post | status_created | 119340 | Using
where
|
| post_tag | post_id | 1 | Using
where
|
+
----------+----------------+--------+-------------+
对比优化前后两次EXPLAIN的结果来看,优化后的SQL虽然「rows」更大了,但是没有了「Using filesort」,综合来看,性能依然得到了提升。
解释
对第一条SQL而言,为什么MySQL优化器选择了一个耗时的执行方案?对第二条SQL而言,为什么把连接方式改成STRAIGHT_JOIN之后就提升了性能?
这一切还得从MySQL对多表连接的处理方式说起,首先要确定以谁为驱动表,也就是说以哪个表为基准,在处理此类问题时,MySQL优化器采用了简单粗暴的解决方法:哪个表的结果集小,就以哪个表为驱动表,通常这都是最佳选择。
说明:在EXPLAIN结果中,第一行出现的表就是驱动表。
继续post连接post_tag的例子,MySQL优化器有如下两个选择,分别是:
- 以post为驱动表,通过status_created索引过滤,结果集119340行
- 以post_tag为驱动表,通过tag_id索引过滤,结果集71220行
显而易见,post_tag过滤的结果集更小,所以MySQL优化器选择它作为驱动表,可悲催的是我们还需要以post表中的created字段来排序,也就是说排序字段不在驱动表里,于是乎不可避免的出现了「Using filesort」,从而导致慢查询。
知道了来龙去脉,优化起来就容易了。头等大事是务必保证排序字段在驱动表中,所以必须以post是驱动表,于是乎「STRAIGHT_JOIN」就成了答案,它强制了连接顺序。
…
不过我总觉得「STRAIGHT_JOIN」这种非标准的语法属于奇技淫巧的范畴,能不用尽量不用,毕竟多数情况下,MySQL优化器都能做出正确的选择。
- Mysql关联查询优化器理解
- MySQL关联查询优化实例
- Mysql分页&关联查询优化
- mysql 查询优化理解
- 【mysql】关联子查询的一种优化
- 【mysql】关联子查询的一种优化
- 记录 mysql 查询优化器的一点理解
- 理解大数据量MySQL查询优化
- MySQL 查询优化器
- MySQL查询优化器
- Mysql查询优化器
- Mysql查询优化器
- MYSQL查询优化器
- MySQL SQL优化:关联子查询的局限性
- MySQL查询优化之查询优化器
- Mysql关联查询
- mysql关联查询原理
- mysql关联查询
- AE学习日记之MapControl与PageLayoutControl图层变化同步
- 实时视频应用之QoS关键技术分析
- 单例模式之懒汉与饿汉式
- 1.4
- 四则运算
- Mysql关联查询优化器理解
- Building your own private LoRa network
- Redis 基本类型介绍与基础操作指令
- ssh 中报错:org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration
- 手动生成XMl
- 第四周项目6-多项式求和
- xcoj1226-捕鼠
- 神经网络简介
- 冒泡排序法