mysql sql优化方法(MySQL SQL优化教程之in和range查询)
mysql sql优化方法
MySQL SQL优化教程之in和range查询首先我们来说下in()这种方式的查询。在《高性能MySQL》里面提及用in这种方式可以有效的替代一定的range查询,提升查询效率,因为在一条索引里面,range字段后面的部分是不生效的。使用in这种方式其实MySQL优化器是转化成了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.
也就是说:
1. eq_range_index_lie_limit = 0 只能使用index lie
2. 0 < eq_range_index_lie_limit <= N 使用index statistics
3. eq_range_index_lie_limit > N 只能使用index lie
index lie与index statistics是MySQL优化器对开销代价的估算方法,前者统计速度慢但是能得到精准的值,后者统计速度快但是数据未必精准。
the optimizer can estimate the row count for each range using lies into the index or index statistics.
在MySQL5.7版本中将默认值从10修改成200目的是为了尽可能的保证范围等值运算(IN())执行计划尽量精准,因为IN()list的数量很多时候都是超过10的。
说在前面
今天文章的主题有两个:
- 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; |
索引如下:
|
+ ----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | + ----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | pre_forum_post | 0 | PRIMARY | 1 | tid | A | NULL | NULL | NULL | | BTREE | | | | pre_forum_post | 0 | PRIMARY | 2 | position | A | 25521392 | NULL | NULL | | BTREE | | | | pre_forum_post | 0 | pid | 1 | pid | A | 25521392 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | fid | 1 | fid | A | 1490 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | displayorder | 1 | tid | A | 880048 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | displayorder | 2 | invisible | A | 945236 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | displayorder | 3 | dateline | A | 25521392 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | first | 1 | tid | A | 880048 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | first | 2 | first | A | 1215304 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | new_auth | 1 | authorid | A | 1963184 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | new_auth | 2 | invisible | A | 1963184 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | new_auth | 3 | tid | A | 12760696 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | idx_dt | 1 | dateline | A | 25521392 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | mul_test | 1 | tid | A | 880048 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | mul_test | 2 | invisible | A | 945236 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | mul_test | 3 | dateline | A | 25521392 | NULL | NULL | | BTREE | | | | pre_forum_post | 1 | mul_test | 4 | pid | A | 25521392 | NULL | NULL | | BTREE | | | + ----------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
看下执行计划:
|
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_trace\G // 注:optimizer_trace建议只在session模式下开启调试即可 |
参考资料
http://dev.mysql.com/doc/refman/5.6/en/range-optimization.html
http://imysql.com/2014/08/05/a-fake-bug-with-eq-range-index-lie-limit.shtml
http://blog.163.com/li_hx/blog/static/18399141320147521735442/
到此这篇关于MySQL SQL优化教程之in和range查询的文章就介绍到这了,更多相关MySQL SQL优化之in和range查询内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:http://myrock.github.io/2014/09/24/in-and-range/
- mysql时间存储如何选择(MySQL如何使用时间作为判断条件)
- 常用的mysql查询语句(MySQL查询重写插件的使用)
- navicat for mysql连接测试失败(Navicat连接虚拟机mysql常见错误问题及解决方法)
- mysqldecimal类型数据转换(mysql decimal数据类型转换的实现)
- mysql各种锁详解(MySQL 查看事务和锁情况的常用语句分享)
- mysql的count知识(MySQL中MTR的概念)
- mysql分组函数入门(详解MySQL 数据分组)
- mysqlexplain解析(Mysql explain用法与结果深入分析)
- mysql统计函数详解(浅析MySQL的基数统计)
- mysql子查询用法(MySQL 子查询和分组查询)
- mysql数据类型及用法(MySQL数据库重命名的快速且安全方法3种)
- 如何查找MySQL中查询慢的SQL语句
- mysql数据库使用规则(mysql数据库基本语法及操作大全)
- mysql索引原理和优化(Mysql 索引该如何设计与优化)
- mysql的默认数据库例子在哪里(修改MySQL8.0 默认的数据目录快捷操作无配置)
- 最新版mysql 安装教程(新手如何安装Mysql亲测有效)
- 学好汉语拼音,从娃娃绕口令抓起,平时还是要多练 收藏好(从娃娃绕口令抓起)
- 仙女们的私藏鲜法大PK 鲜香切块牛肉(仙女们的私藏鲜法大PK)
- 天热没胃口 这道菜开胃又下饭,2个小技巧新手一学就会(这道菜开胃又下饭)
- 指天椒紫苏爆炒牛肉(指天椒紫苏爆炒牛肉)
- 谷雨前,吃牛羊肉别忘了吃河鲜,除湿还清热,加紫苏一炒特解馋(吃牛羊肉别忘了吃河鲜)
- 紫苏牛肉锅里滚一滚,香的鼻子都要掉了(紫苏牛肉锅里滚一滚)
热门推荐
- sqlserver如何设置定时备份(SQL Server使用脚本实现自动备份的思路详解)
- php设计模式如何实现(php模式设计之观察者模式应用实例分析)
- win7 ftp服务器怎么搭建(win7下利用IIS搭建FTP服务器)
- 盘点CSS Selectors Level4中新增的选择器(盘点CSS Selectors Level4中新增的选择器)
- python提取字符串中的正则表达式(python3正则提取字符串里的中文实例)
- nginx配置root后的路径打不开(详解nginx.conf 中 root 目录设置问题)
- phpstudy使用教程学习(phpStudy 2016 使用教程详解支持PHP7)
- label for属性
- thinkphp5框架怎么设置(thinkphp5框架调用其它控制器方法 实现自定义跳转界面功能示例)
- 异步调用中HttpContext.Current为null解决方法