在很多时候我们使用索引去搜索的时候,分析sql语句的时候,发现并没有使用索引,今天探讨一下这些情况。
先看一下表结构:
CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
`test` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `testindex` (`Population`),
KEY `nameindex` (`Name`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=304080 DEFAULT CHARSET=latin1
我们通过Population这个字段索引去经行验证,分析一下正常命中的索引:
explain select * from city where Population >10000;
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'range', 'testindex', 'testindex', '4', NULL, '4037', '100.00', 'Using index condition; Using MRR'
可以看到key那一列,正确用到了建立的testindex索引
索引不能中的情况
1.语句中包含or(如果or前后条件字段都建立了索引的情况能命中)
explain select * from city where Population >10000 or Population<200;
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'ALL', 'testindex', NULL, NULL, NULL, '302500', '51.33', 'Using where'
我们可以看到key那一列为null,没有用到索引
如果我们or前后的字段都有索引:
explain select * from city where Population >10000 or id>100;
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'index_merge', 'PRIMARY,testindex', 'testindex,PRIMARY', '4,4', NULL, '155287', '100.00', 'Using sort_union(testindex,PRIMARY); Using where'
可以看到key那一列用到了testindex和主键索引
2.语句中包含like并且以%开头
explain select * from city where name like '%k';
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'ALL', NULL, NULL, NULL, NULL, '302500', '11.11', 'Using where'
可以看到key那一列为null,没有用到索引
现在不以%开头看一下
explain select * from city where name like 'k%';
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, city, , range, nameindex, nameindex, 35, , 261, 100.00, Using index condition; Using MRR
可以看到用到了nameindex这个索引
3.字符串类型的字段一定要用引号引起来,否则不能命中索引
explain select * from city where name like 123;
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'ALL', 'nameindex', NULL, NULL, NULL, '302500', '11.11', 'Using where'
可以看到key的那一列为null,并没有用到索引
4.查询表中所有数据
explain select * from city;
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'ALL', NULL, NULL, NULL, NULL, '302500', '100.00', NULL
可以看到key那一列为null并没有用到索引
5.在索引列的条件左边做运算
explain select * from city where population-20 >20;
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'ALL', NULL, NULL, NULL, NULL, '302500', '100.00', 'Using where'
可以看到key那一列为null,所以没有用到索引
看一下在索引列右边做运算
explain select * from city where population >20-10;
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'range', 'testindex', 'testindex', '4', NULL, '4079', '100.00', 'Using index condition; Using MRR'
看到key那一列用到了testindex。
6.使用not in 或not exist
以not in 来演示
explain select * from city where population not in (10,20);
结果:
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'city', NULL, 'ALL', 'testindex', NULL, NULL, NULL, '302500', '51.35', 'Using where'
key列为空,没有用到索引
以上情况全为小编亲自测试过的,比如像网上说的那种查询的表数据大于30%等情况的不会使用索引,测试结果确实也用到了索引,索引骚小孩也不太清楚那种情况到底有没有用到索引,还希望大家多多实验,多多补充。