网站设计网页设计公司,设计师素材网站e,南京设计公司前十名,订房网站开发select * from table where id in (....)这样的查询#xff0c;是走范围索引还是走等值索引#xff1f;select * from table where key_part1 in (....) and key_part2XX;这样的查询#xff0c;第二部分还走不走索引#xff1f;测试目的#xff0c;想知道#xff0c;MYSQ…select * from table where id in (....)这样的查询是走范围索引还是走等值索引select * from table where key_part1 in (....) and key_part2XX;这样的查询第二部分还走不走索引测试目的想知道MYSQL对IN LIST是如何选择执行计划的在单字段索引和复合索引中mysql 5.1.40os:rhel 5.4engineinnodbinnodb_file_per_table# 先来创建测试环境create table index_test ( id int auto_increment , col1 int ,col2 varchar(200) ,content varchar(500),primary key (id) ,key col1 (col1) ) engineinnodb default charsetlatin1;# repeat insert operation 12 timesinsert into index_test (col1,col2) select rownum:rownum1,column_name from information_schema.COLUMNS c , (select rownum:0 ) id limit 500 ;# 测试1先测对主键的IN操作# 测试用例reset query cache; --清空QUERY_CAHCEshow 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你应该多跑几次以去掉物理读 root127.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)# 从执行计划看走的是范围条件但我们看看实际情况 # 注意为了减少篇幅我把各个查询的结果给删减了。select * from index_test where id in (2,10);RESULTs 2 rowsLIO 4select * from index_test where id in (2,1000);RESULTs 2 rowsLIO 4select * from index_test where id in (2,10,100);RESULTs 3 rowsLIO 6select * from index_test where id in (2,10,1000,2000);RESULTs 4 rowsLIO 8select * from index_test where id in (2,10,1000,2000,9000);RESULTs 5 rowsLIO 10### 在这里看到逻辑读根据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 ;root127.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)select * from index_test where col1 in (100,101);RESULTs 24 rowsLIO 86select * from index_test where col1 in (100,500);RESULTs 24 rowsLIO 86select * from index_test where col1 in (100,500,300);RESULTs 36 rowsLIO 139select * from index_test where col1 in (100,500,300,400);RESULTs 48 rowsLIO 172分析 这个结果与测试1的结果是一样的# 测试3对复合索引的前列IN操作alter table index_test drop index col1 ,add index col1col2(col1,col2) ;update index_test set contentconcat(col2,col3,col1) ;主要是测一下索引的第一个字段用IN后优化器还会不会使用第二个字段来进行索引搜索root127.0.0.1 : test 18:41:38 explain select content from index_test where col1 in (100,500,300,400) and col2aaaa;-----------------------------------------------------------------------------------------------| 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 col2aaaa;RESULTs 0 rowsLIO 24select content from index_test where col1 in (100,500,300,400) and col2aaaa;RESULTs 0 rowsLIO 24分析#我们发现两个查询的逻辑读是一样其实这已经表明优化器用上了索引的第二个字段在索引搜索部分就完成了对COL2的过滤总结优化器对in list是转成“or” 的“多个等值”查询来处理的并没有转成范围查询
