Mysql优化器对in list的处置
发布时间：2022-03-24 03:00:48 所属栏目：MySql教程 来源：互联网
导读：select * from table where id in (....) 这样的查询，是走范围索引还是走等值索引？ select * from table where key_part1 in (....) and key_part2=XX; 这样的查询，第二部分还走不走索引？ 测试目的，想知道，MYSQL对IN LIST是如何选择执行计划的；在单字
|
select * from table where id in (....) 这样的查询，是走范围索引还是走等值索引？ select * from table where key_part1 in (....) and key_part2='XX'; 这样的查询，第二部分还走不走索引？ 测试目的，想知道，MYSQL对IN LIST是如何选择执行计划的；在单字段索引和复合索引中； mysql 5.1.40 os:rhel 5.4 engine=innodb innodb_file_per_table # 测试1：先测对主键的IN操作； # 测试用例： reset query cache; --清空QUERY_CAHCE show status like 'Innodb_buffer_pool_read_requests' ; --用来查询逻辑读 select * from index_test where id in (2,10,1000,2000,9000); show status like 'Innodb_buffer_pool_read_requests' ; --与前面的结果相减，就得到SQL执行所带来的逻辑读 ； 为了逻辑读的准确性， 对同一个SQL你应该多跑几次，以去掉物理读 ； root@127.0.0.1 : test 16:02:16> explain select * from index_test where id in (2,10,1000,2000); +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | index_test | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) ### 在这里看到，逻辑读根据IN LIST里KEY的数量成线性增加，而没有根据KEY值的大小变化，所以我们判断，对主键的IN操作，其实都转成了OR操作。 # 测试2：对非主键的IN操作； # 测试用例： reset query cache; show status like 'Innodb_buffer_pool_read_requests' ; select * from index_test where col1 in (100,500,300,400); show status like 'Innodb_buffer_pool_read_requests' ; root@127.0.0.1 : test 16:06:33> explain select * from index_test where col1 in (100,200); +----+-------------+------------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | index_test | range | col1 | col1 | 5 | NULL | 24 | Using where | +----+-------------+------------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) 分析： 这个结果与测试1的结果是一样的； # 测试3：对复合索引的前列IN操作； alter table index_test drop index col1 ,add index col1col2(col1,col2) ; update index_test set content=concat(col2,col3,col1) ; 主要是测一下，索引的第一个字段用IN后，优化器还会不会使用第二个字段来进行索引搜索； root@127.0.0.1 : test 18:41:38> explain select content from index_test where col1 in (100,500,300,400) and col2='aaaa'; +----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | index_test | range | col1col2 | col1col2 | 208 | NULL | 4 | Using where | +----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+ 1 row in set (0.00 sec) select count(*) from index_test where col1 in (100,500,300,400) and col2='aaaa'; RESULTs： 0 rows LIO ： 24 select content from index_test where col1 in (100,500,300,400) and col2='aaaa'; RESULTs： 0 rows LIO ： 24 分析： #我们发现，两个查询的逻辑读是一样，其实这已经表明优化器用上了索引的第二个字段，在索引搜索部分就完成了对COL2的过滤。  （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
