mysql 不用in mysql不使用索引情况

explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。简单讲，它的作用就是分析查询性能。

explain关键字的使用方法很简单，就是把它放在select查询语句的前面

具体参照：

MySQL的Explain关键字查看是否使用索引

1) 如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如，如果列key均匀分布在1和100之间，下面的查询使用索引就不是很好：select * from table_name where key>1 and key<90;

2) 如果使用MEMORY/HEAP表，并且where条件中不使用“=”进行索引列，那么不会用到索引，head表只有在“=”的条件下才会使用索引

MEMORY/HEAP 表相关介绍请参照：mysql数据库引擎HEAP（MEMORY）的使用，内存表，临时表的用法

3) 用or分隔开的条件，如果or条件中的一个列有索引，其他的列没有索引，那么涉及到的索引都不会被用到，例如：select * from table_name where key1='a' or key2='b';如果在key1上有索引而在key2上没有索引，则该查询也不会走索引。

4) 复合索引，如果索引列不是复合索引的第一部分，则不使用索引（即不符合最左前缀），例如，复合索引为(key1,key2),则查询select * from table_name where key2='b';将不会使用索引。

CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> select * from countrylanguage where CountryCode ='ABW';
+-------------+------------+------------+------------+
| CountryCode | Language   | IsOfficial | Percentage |
+-------------+------------+------------+------------+
| ABW         | Dutch      | T          |        5.3 |
| ABW         | English    | F          |        9.5 |
| ABW         | Papiamento | F          |       76.7 |
| ABW         | Spanish    | F          |        7.4 |
+-------------+------------+------------+------------+
4 rows in set

mysql> explain select * from countrylanguage where CountryCode ='ABW';
+----+-------------+-----------------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table           | partitions | type | possible_keys       | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | countrylanguage | NULL       | ref  | PRIMARY,CountryCode | PRIMARY | 3       | const |    4 |      100 | NULL  |
+----+-------------+-----------------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
1 row in set

mysql> explain select * from countrylanguage where Language ='Dutch';
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | countrylanguage | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  984 |       10 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

mysql>

5) 如果like是以‘%’开始的，则该列上的索引不会被使用。例如select * from table_name where key1 like '%a'；该查询即使key1上存在索引，也不会被使用。

CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> select * from country where Code like '%AB';
+------+-------+-----------+----------------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
| Code | Name  | Continent | Region         | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP  | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+-------+-----------+----------------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
| GAB  | Gabon | Africa    | Central Africa |      267668 |      1960 |    1226000 |           50.1 | 5493 |   5279 | Le Gabon  | Republic       | Omar Bongo  |     902 | GA    |
+------+-------+-----------+----------------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
1 row in set

mysql> EXPLAIN select * from country where Code like '%AB';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | country | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  239 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

mysql> EXPLAIN select * from country where Code like 'AB%';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | country | NULL       | range | PRIMARY       | PRIMARY | 3       | NULL |    1 |      100 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set

6) 如果列为字符串，则where条件中必须将字符常量值加引号，否则即使该列上存在索引，也不会被使用。例如,select * from table_name where key1=1;如果key1列保存的是字符串，即使key1上有索引，也不会被使用。

7) mysql会对sql语句做优化， in 后面的条件不超过一定数量仍然会使用索引。mysql 会根据索引长度和in后面条件数量判断是否使用索引。另外，如果是in后面是子查询，则不会使用索引。

解决MySQL中IN子查询会导致无法使用索引问题