Mysql索引的运用-组合索引+跳跃条件
发布时间：2022-03-30 12:18:04 所属栏目：搜索优化 来源：互联网
导读：关于MYSQL组合索引的使用，官方对下面的例子的说法是可以使用索引： KEY（key_part1,key_part2,key_part3） select .... from table where key_part1=xxx and key_part3=yyy; 从MYSQL的执行计划看，确实也是使用索引; 但在实际的优化过程中，我们只是简单
|
关于MYSQL组合索引的使用，官方对下面的例子的说法是可以使用索引： KEY（key_part1,key_part2,key_part3） select .... from table where key_part1='xxx' and key_part3='yyy'; 从MYSQL的执行计划看，确实也是使用索引; 但在实际的优化过程中，我们只是简单的关注是否使用了这个索引是不够的。 [@more@] 我们需要关注的是: 对key_part3这个关键字过滤的时候，是否用到了索引？ 我们看到，查询的条件，对索引来说是跳跃的。 这对ORACLE来说并不是难事。SQL优化器会在索引里完成对time字段的过滤。 用HINT：/*+INDEX_SS(TABLE INDEX_NAME)*/ 可以来辅助。 但对MYSQL来说，你可能并不知道，是什么时候对time字段进行过滤的。 当然我们希望是通过索引来过滤TIME字段。这样最后回表的次数就会少一些。 在测试过程中，我们通过观察MYSQL的Innodb_buffer_pool_read_requests（逻辑读）变量的变化，来推测结果。 注意以下查询过程中，条件time的变化，以及变量Innodb_buffer_pool_read_requests的变化 #######开始第一次测试 show session status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | Innodb_buffer_pool_read_requests | 136566076 | +----------------------------------+-----------+ 1 row in set (0.02 sec) select count(distinct concat('c',content)),count(*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-02-01 00:00:00' ; +-------------------------------------+----------+ | count(distinct concat('c',content)) | count(*) | +-------------------------------------+----------+ | 35644 | 44397 | +-------------------------------------+----------+ 1 row in set (1.40 sec) show session status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | Innodb_buffer_pool_read_requests | 136742193 | +----------------------------------+-----------+ 1 row in set (0.02 sec) select 136742193-136566076 ; +---------------------+ | 136742193-136566076 | +---------------------+ | 176117 | +---------------------+ 1 row in set (0.00 sec) #######开始第二次测试 show session status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | Innodb_buffer_pool_read_requests | 136742194 | +----------------------------------+-----------+ 1 row in set (0.02 sec) select count(distinct concat('c',content)),count(*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-01-05 00:00:00' ; +-------------------------------------+----------+ | count(distinct concat('c',content)) | count(*) | +-------------------------------------+----------+ | 3679 | 4097 | +-------------------------------------+----------+ 1 row in set (0.74 sec) show session status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | Innodb_buffer_pool_read_requests | 136916032 | +----------------------------------+-----------+ 1 row in set (0.01 sec) select 136916032-136742194; +---------------------+ | 136916032-136742194 | +---------------------+ | 173838 | +---------------------+ 1 row in set (0.00 sec) #######开始第三次测试 show session status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | Innodb_buffer_pool_read_requests | 137086324 | +----------------------------------+-----------+ 1 row in set (0.02 sec) select count(*) from im_message_201001_11 where owner='huaniaoyuchong83' and time between '2010-01-01 00:00:00' and '2010-02-01 00:00:00' ; +----------+ | count(*) | +----------+ | 44397 | +----------+ 1 row in set (0.05 sec) show session status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | Innodb_buffer_pool_read_requests | 137092204 | +----------------------------------+-----------+ 1 row in set (0.01 sec) select 137092204-137086324 ; +---------------------+ | 137092204-137086324 | +---------------------+ | 5880 | +---------------------+ 1 row in set (0.00 sec) #######开始第四次测试 show session status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | Innodb_buffer_pool_read_requests | 137092205 | +----------------------------------+-----------+ 1 row in set (0.01 sec) select count(*) from im_message_201001_11 where owner='huaniaoyuchong83' ; +----------+ | count(*) | +----------+ | 44397 | +----------+ 1 row in set (0.04 sec) #######开始第五次测试 show session status like 'Innodb_buffer_pool_read_requests'; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | Innodb_buffer_pool_read_requests | 137098131 | +----------------------------------+-----------+ 1 row in set (0.02 sec) ####### 分析结果 前三次查询，从索引检索后需要回表： time 结果行数 逻辑读 30天 44397 176117 5天 4097 173838 1天 0 170290 后三次查询，从索引检索后不需要回表 time 结果行数 逻辑读 30天 44397 5880 无time条件 44397 5880 5天 4097 5880 对这样的索引，需要优化，可以。 调整索引顺序(`owner`,`time`,`other`)。 但是这仅仅是对一个SQL的优化。 你还要考虑到系统里还有很多其他类似的SQL需要用到这个索引。 所以在优化时，需要评估所有的SQL。 （编辑：洛阳站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐