sqlcount优化(SQL优化教程之in与range查询)
sqlcount优化
SQL优化教程之in与range查询前言
《高性能MySQL》里面提及用in这种方式可以有效的替代一定的range查询,提升查询效率, 因为在一条索引里面,range字段后面的部分是不生效的(ps.需要考虑 ICP) 。MySQL优化器将in这种方式转化成 n*m 种组合进行查询,最终将返回值合并,有点类似union但是更高效。
MySQL在 IN() 组合条件过多的时候会发生很多问题。查询优化可能需要花很多时间,并消耗大量内存。新版本MySQL在组合数超过一定的数量就不进行计划评估了,这可能导致MySQL不能很好的利用索引。
这里的 一定数 在MySQL5.6.5以及以后的版本中是由eq_range_index_lie_limit这个参数控制 。默认设置是10,一直到5.7以后的版本默认修改为200,当然可以手动设置的。5.6手册说明如下:
The eq_range_index_lie_limit system variable enables you to configure the number of values at which the optimizer switches from one row estimation strategy to the other. To disable use of statistics and always use index lies, set eq_range_index_lie_limit to 0. To permit use of index lies for comparisons of up to N equality ranges, set eq_range_index_lie_limit to N + 1. eq_range_index_lie_limit is available as of MySQL 5.6.5. Before 5.6.5, the optimizer uses index lies, which is equivalent to eq_range_index_lie_limit=0.
换言之,
eq_range_index_lie_limit = 0 只能使用index lie
0 < eq_range_index_lie_limit <= N 使用index statistics
eq_range_index_lie_limit > N 只能使用index lie
在MySQL5.7版本中将默认值从10修改成200目的是为了尽可能的保证范围等值运算(IN())执行计划尽量精准,因为IN()list的数量很多时候都是超过10的。
在MySQL的官方手册上有这么一句话:
the optimizer can estimate the row count for each range using lies into the index or index statistics.
大意:
优化器预估每个范围段--如"a IN (10, 20, 30)" 视为等值比较, 括3个范围段实则简化为3个单值,分别是10,20,30--中包括的元组数,用范围段来表示是因为 MySQL 的"range"扫描方式多数做的是范围扫描,此处单值可视为范围段的特例;
估计方法有2种:
- lie到index中即利用索引完成元组数的估算,简称index lie;
- index statistics:使用索引的统计数值,进行估算;
对比这两种方式
- index lie: 速度慢,但能得到精确的值(MySQL的实现是数索引对应的索引项个数,所以精确)
- index statistics: 速度快,但得到的值未必精确
简单说,**选项 eq_range_index_lie_limit 的值设定了 IN列表中的条件个数上线,超过设定值时,会将执行计划从 index lie 变成 index statistics **。
为什么要区分这2种方式呢?
- 查询优化器会使用代价估算模型计算每个计划的代价,选择其中代价最小的
- 单表扫描时,需要计算代价;所以单表的索引扫描也需要计算代价
- 单表的计算公式通常是: 代价 = 元组数 * IO平均值
- 所以不管是哪种扫描方式,都需要计算元组数
- 当遇到“a IN (10, 20, 30)”这样的表达式的时候,发现a列存在索引,则需要看这个索引可以扫描到的元组数由多少而计算其索引扫描代价,所以就用到了本文提到的“index lie”、“index statistics”这2种方式。
讨论主题
- range查询与索引使用
- eq_range_index_lie_limit的说明
range查询与索引使用
SQL如下:
|
SELECT * FROM pre_forum_post WHERE tid=7932552 AND invisible IN ( '0' , '-2' ) ORDER BY dateline DESC LIMIT 10; |
索引如下:
|
PRIMARY (tid,position), pid(pid), fid(tid), displayorder(tid,invisible,dateline) first (tid, first ) new_auth(authorid,invisible,tid) idx_dt(dateline) mul_test(tid,invisible,dateline,pid) |
看下执行计划:
|
root@localhost 16:08:27 [ultrax]> explain SELECT * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN ( '0' , '-2' ) -> ORDER BY dateline DESC LIMIT 10; + ----+-------------+----------------+-------+-------------------------------------------+--------------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ----+-------------+----------------+-------+-------------------------------------------+--------------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | pre_forum_post | range | PRIMARY ,displayorder, first ,mul_test,idx_1 | displayorder | 4 | NULL | 54 | Using index condition; Using filesort | + ----+-------------+----------------+-------+-------------------------------------------+--------------+---------+------+------+---------------------------------------+ 1 row in set (0.00 sec) |
MySQL优化器认为这是一个range查询,那么(tid,invisible,dateline)这条索引中,dateline字段肯定用不上了,也就是说这个SQL最后的排序肯定会生成一个临时结果集,然后再结果集里面完成排序,而不是直接在索引中直接完成排序动作,于是我们尝试增加了一条索引。
|
root@localhost 16:09:06 [ultrax]> alter table pre_forum_post add index idx_1 (tid,dateline); Query OK, 20374596 rows affected, 0 warning (600.23 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost 16:20:22 [ultrax]> explain SELECT * FROM pre_forum_post force index (idx_1) WHERE tid=7932552 AND `invisible` IN( '0' , '-2' ) ORDER BY dateline DESC LIMIT 10; +----+-------------+----------------+------+---------------+-------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+------+---------------+-------+---------+-------+--------+-------------+ | 1 | SIMPLE | pre_forum_post | ref | idx_1 | idx_1 | 3 | const | 120646 | Using where | +----+-------------+----------------+------+---------------+-------+---------+-------+--------+-------------+ 1 row in set (0.00 sec) root@localhost 16:22:06 [ultrax]> SELECT sql_no_cache * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN( '0' , '-2' ) ORDER BY dateline DESC LIMIT 10; ... 10 rows in set (0.40 sec) root@localhost 16:23:55 [ultrax]> SELECT sql_no_cache * FROM pre_forum_post force index (idx_1) WHERE tid=7932552 AND `invisible` IN( '0' , '-2' ) ORDER BY dateline DESC LIMIT 10; ... 10 rows in set (0.00 sec) |
实验证明效果是极好的,其实不难理解,上面我们就说了in()在MySQL优化器里面是以多种组合方式来检索数据的,如果加了一个排序或者分组那势必只能在临时结果集上操作,也就是说索引里面即使包含了排序或者分组的字段依然是没用的。唯一不满的是MySQL优化器的选择依然不够靠谱。
总结下:在MySQL查询里面使用in(),除了要注意in()list的数量以及eq_range_index_lie_limit的值以外(具体见下),还要注意如果SQL包含排序/分组/去重等等就需要注意索引的使用。
eq_range_index_lie_limit的说明
还是上面的案例,为什么idx_1无法直接使用?需要使用hint强制只用这个索引呢?这里我们首先看下eq_range_index_lie_limit的值。
|
root@localhost 22:38:05 [ultrax]> show variables like 'eq_range_index_lie_limit' ; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | eq_range_index_lie_limit | 2 | +---------------------------+-------+ 1 row in set (0.00 sec) |
根据我们上面说的这种情况0 < eq_range_index_lie_limit <= N使用index statistics,那么接下来我们用OPTIMIZER_TRACE来一看究竟。
|
{ "index" : "displayorder" , "ranges" : [ "7932552 <= tid <= 7932552 AND -2 <= invisible <= -2" , "7932552 <= tid <= 7932552 AND 0 <= invisible <= 0" ], "index_lies_for_eq_ranges" : false , "rowid_ordered" : false , "using_mrr" : false , "index_only" : false , "rows" : 54, "cost" : 66.81, "chosen" : true } // index lie为 false ,最终chosen是 true ... { "index" : "idx_1" , "ranges" : [ "7932552 <= tid <= 7932552" ], "index_lies_for_eq_ranges" : true , "rowid_ordered" : false , "using_mrr" : false , "index_only" : false , "rows" : 120646, "cost" : 144776, "chosen" : false , "cause" : "cost" } |
我们可以看到displayorder索引的cost是66.81,而idx_1的cost是120646,而最终MySQL优化器选择了displayorder这条索引。那么如果我们把eq_range_index_lie_limit设置>N是不是应该就会使用index lie计算方式,得到更准确的执行计划呢?
|
root@localhost 22:52:52 [ultrax]> set eq_range_index_lie_limit = 3; Query OK, 0 rows affected (0.00 sec) root@localhost 22:55:38 [ultrax]> explain SELECT * FROM pre_forum_post WHERE tid=7932552 AND `invisible` IN( '0' , '-2' ) ORDER BY dateline DESC LIMIT 10; +----+-------------+----------------+------+-------------------------------------------+-------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+------+-------------------------------------------+-------+---------+-------+--------+-------------+ | 1 | SIMPLE | pre_forum_post | ref | PRIMARY,displayorder,first,mul_test,idx_1 | idx_1 | 3 | const | 120646 | Using where | +----+-------------+----------------+------+-------------------------------------------+-------+---------+-------+--------+-------------+ 1 row in set (0.00 sec) |
optimize_trace结果如下
|
{ "index" : "displayorder" , "ranges" : [ "7932552 <= tid <= 7932552 AND -2 <= invisible <= -2" , "7932552 <= tid <= 7932552 AND 0 <= invisible <= 0" ], "index_lies_for_eq_ranges" : true , "rowid_ordered" : false , "using_mrr" : false , "index_only" : false , "rows" : 188193, "cost" : 225834, "chosen" : true } ... { "index" : "idx_1" , "ranges" : [ "7932552 <= tid <= 7932552" ], "index_lies_for_eq_ranges" : true , "rowid_ordered" : false , "using_mrr" : false , "index_only" : false , "rows" : 120646, "cost" : 144776, "chosen" : true } ... "cost_for_plan" : 144775, "rows_for_plan" : 120646, "chosen" : true |
在备选索引选择中两条索引都被选择,在最后的逻辑优化中选在了代价最小的索引也就是idx_1 以上就是在等值范围查询中eq_range_index_lie_limit的值怎么影响MySQL优化器计算开销,从而影响索引的选择。另外我们可以通过profiling来看看优化器的统计耗时:
index lie
|
+----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000048 | | checking permissions | 0.000004 | | Opening tables | 0.000015 | | init | 0.000044 | | System lock | 0.000009 | | optimizing | 0.000014 | | statistics | 0.032089 | | preparing | 0.000022 | | Sorting result | 0.000003 | | executing | 0.000003 | | Sending data | 0.000101 | | end | 0.000004 | | query end | 0.000002 | | closing tables | 0.000009 | | freeing items | 0.000013 | | cleaning up | 0.000012 | +----------------------+----------+ |
index statistics
|
+----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000045 | | checking permissions | 0.000003 | | Opening tables | 0.000014 | | init | 0.000040 | | System lock | 0.000008 | | optimizing | 0.000014 | | statistics | 0.000086 | | preparing | 0.000016 | | Sorting result | 0.000002 | | executing | 0.000002 | | Sending data | 0.000016 | | Creating sort index | 0.412123 | | end | 0.000012 | | query end | 0.000004 | | closing tables | 0.000013 | | freeing items | 0.000023 | | cleaning up | 0.000015 | +----------------------+----------+ |
可以看到当eq_range_index_lie_limit加大使用index lie时,优化器统计耗时明显比ndex statistics方式来的长,但最终它使用了作出了更合理的执行计划。统计耗时0.032089s vs .000086s,但是SQL执行耗时却是约0.03s vs 0.41s。
附:
如何使用optimize_trace
|
set optimizer_trace= 'enabled=on' ; select * from information_schema.optimizer_traceG |
注:optimizer_trace建议只在session模式下开启调试即可
如何使用profile
|
set profiling= ON ; 执行sql; show profiles; show profile for query 2; show profile block io,cpu for query 2; |
另外还可以看到memory,swaps,context switches,source 等信息
参考资料
[1]MySQL SQL优化系列之 in与range 查询
http://www.zzvips.com/article/147157.html
[2]MySQL物理查询优化技术---index lie辨析
http://blog.163.com/li_hx/blog/static/18399141320147521735442/
到此这篇关于SQL优化教程之in与range查询的文章就介绍到这了,更多相关SQL优化之in与range查询内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://mp.weixin.qq.com/s/CjA5g0ex4_84ts2FuXzxfg
- mysql查询性能优化详解(实例讲解MySQL 慢查询)
- sql优化100个技巧(52条SQL语句教你性能优化)
- mysql多表连接优化(浅谈Mysql多表连接查询的执行细节)
- 从宝塔面板找网站会员数据(BT宝塔面板高并发大流量网站优化方案分享)
- mysql优化使用方法(详解Mysql函数调用优化)
- dedecms缩略图优化教程(织梦DedeCMS V5.7缩略图站内上传修正)
- dedecms怎么加入模板(dedecms 列表页标题修改图文方法利于优化)
- web前端字体和图标(web字体加载方案优化小结)
- mysql 高效分页(MySQL 分页查询的优化技巧)
- dedecms循环栏目(dedecms 栏目列表分页链接优化)
- web图片优化方法(网站性能延迟加载图像的五种技巧小结)
- 常见的mysql优化策略(MySQL pt-slave-restart工具的使用简介)
- nginx优化安全设置(nginx优化的六点方法)
- react性能优化是哪个周期(React 并发功能体验前端的并发模式)
- css如何设置简写代码(10个CSS简写/优化技巧整理)
- php系统化框架教程(PHP+swoole+linux实现系统监控和性能优化操作示例)
- 老弄堂里的市井味道(老弄堂里的市井味道)
- 小米音乐可绑定QQ音乐, QQ音乐 真的会消失在小米的设备上吗(小米音乐可绑定QQ音乐)
- 小米Watch S1评测 或许能成为小米冲击高端可穿戴设备的里程碑(小米WatchS1评测或许能成为小米冲击高端可穿戴设备的里程碑)
- 手机QQ与小米路由器在一起 明天揭晓,敬请期待(手机QQ与小米路由器在一起)
- 小米音乐与 QQ 音乐合作,便捷迁移会员(小米音乐与QQ音乐合作)
- 小米推出米兔儿童电话手表奥特曼版,799 元,支持微信 QQ(小米推出米兔儿童电话手表奥特曼版)
热门推荐
- pyqt5加载chrome浏览器(PyQt5内嵌浏览器注入JavaScript脚本实现自动化操作的代码实例)
- docker端口配置(docker 无法释放端口的解决方案)
- docker镜像大是否会导致启动慢(docker下载镜像太慢的解决方案)
- kubernetes exec使用的接口(kubernetes YAML文件的使用)
- 了解mysql数据自动备份(MySQL数据备份方法的选择与思考)
- mysqlint后面的4是什么意思(MySQL中intn后面的n到底代表的是什么意思)
- pythonselenium设置(解决Python selenium get页面很慢时的问题)
- laravel提交数据时间(laravel实现查询最后执行的一条sql语句的方法)
- python中生成字符串序列(python实现字符串加密 生成唯一固定长度字符串)
- python识别验证码教程(Python3.5 + sklearn利用SVM自动识别字母验证码方法示例)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9