like失效
like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。表 city 字段设置有索引。
mysql> EXPLAIN SELECT * FROM `t` WHERE `city` LIKE '%州' LIMIT 0,1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 6980 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `city` LIKE '杭%' LIMIT 0,1000;
+----+-------------+-------+------------+-------+-----------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t | NULL | range | city | city | 66 | NULL | 4000 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------+------+---------+------+------+----------+-----------------------+or失效
or语句前后没有同时使用索引。当or左右查询字段只有一个是索引该索引失效，如果被联合索引包含索引也失效，只有当or左右查询字段均为索引时，才会生效。
mysql> show index from t;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t | 0 | PRIMARY | 1 | id | A | 6980 | NULL | NULL | | BTREE | | |
| t | 1 | city | 1 | city | A | 4 | NULL | NULL | | BTREE | | |
| t | 1 | city_user | 1 | city | A | 4 | NULL | NULL | | BTREE | | |
| t | 1 | city_user | 2 | name | A | 6980 | NULL | NULL | | BTREE | | |
| t | 1 | name | 1 | name | A | 6980 | NULL | NULL | | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `city` = '杭州' OR `age` = '57' LIMIT 0,1000;
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | city,city_user | NULL | NULL | NULL | 6980 | 19.00 | Using where |
+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `city` = '杭州' OR `name` = 'hksVkR' LIMIT 0,1000;
+----+-------------+-------+------------+-------------+---------------------+-----------+---------+------+------+----------+-------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------------+-----------+---------+------+------+----------+-------------------------------------+
| 1 | SIMPLE | t | NULL | index_merge | city,city_user,name | city,name | 66,66 | NULL | 4001 | 100.00 | Using union(city,name); Using where |
+----+-------------+-------+------------+-------------+---------------------+-----------+---------+------+------+----------+-------------------------------------+组合索引失效
组合索引，不是使用第一列索引，索引失效。
mysql> show index from t;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t | 0 | PRIMARY | 1 | id | A | 6980 | NULL | NULL | | BTREE | | |
| t | 1 | city_user_age | 1 | city | A | 4 | NULL | NULL | | BTREE | | |
| t | 1 | city_user_age | 2 | name | A | 6980 | NULL | NULL | | BTREE | | |
| t | 1 | city_user_age | 3 | age | A | 6980 | NULL | NULL | | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `city` = '杭州' LIMIT 0,1000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | city_user_age | city_user_age | 66 | const | 4000 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `city` = '杭州' AND `name` = 'hksVkR' LIMIT 0,1000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | city_user_age | city_user_age | 132 | const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `city` = '杭州' AND `age` = 57 LIMIT 0,1000;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t | NULL | ref | city_user_age | city_user_age | 66 | const | 4000 | 10.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `name` = 'hksVkR' LIMIT 0,1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 6980 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+数据类型失效
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t | 0 | PRIMARY | 1 | id | A | 6980 | NULL | NULL | | BTREE | | |
| t | 1 | name | 1 | name | A | 6980 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `name` = '1234' LIMIT 0,1000;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | name | name | 66 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
mysql> EXPLAIN SELECT * FROM `t` WHERE `name` = 1234 LIMIT 0,1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | name | NULL | NULL | NULL | 6980 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+NULL查询失效(谣言)
在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效。当查询量达到总表的30%以上时，索引就会失效。
mysql> show index from population;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| population | 0 | PRIMARY | 1 | id | A | 5526 | NULL | NULL | | BTREE | | |
| population | 1 | addr | 1 | addr | A | 4977 | NULL | NULL | YES | BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> EXPLAIN SELECT * FROM `population` WHERE `addr` IS NULL LIMIT 0,1000;
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | population | NULL | ref | addr | addr | 515 | const | 573 | 100.00 | Using index condition |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
mysql> EXPLAIN SELECT * FROM `population` WHERE `addr` IS NOT NULL LIMIT 0,1000;
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | population | NULL | range | addr | addr | 515 | NULL | 5127 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+不等于失效(谣言)
mysql> show index from population;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| population | 0 | PRIMARY | 1 | id | A | 5526 | NULL | NULL | | BTREE | | |
| population | 1 | addr | 1 | addr | A | 4977 | NULL | NULL | YES | BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> EXPLAIN SELECT * FROM `population` WHERE `addr` != 'hBkNJ2' LIMIT 0,1000;
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | population | NULL | range | addr | addr | 515 | NULL | 5126 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+使用函数失效
对索引字段进行计算操作、字段上使用函数。
mysql> show index from population;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| population | 0 | PRIMARY | 1 | id | A | 5526 | NULL | NULL | | BTREE | | |
| population | 1 | addr | 1 | addr | A | 5128 | NULL | NULL | YES | BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> EXPLAIN SELECT * FROM `laravel`.`population` WHERE LEFT(`addr`,3) = '98E' LIMIT 0,1000;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | population | NULL | ALL | NULL | NULL | NULL | NULL | 5700 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+