mysql索引不能命中

在很多时候我们使用索引去搜索的时候，分析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%等情况的不会使用索引，测试结果确实也用到了索引，索引骚小孩也不太清楚那种情况到底有没有用到索引，还希望大家多多实验，多多补充。