一、以%开头的like查询时，索引失效
MySQL > explain select * from statistic_sales_data_detail_2018_08 where shop_code like '%33%';
+----+-------------+-------------------------------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | statistic_sales_data_detail_2018_08 | ALL | NULL | NULL | NULL | NULL | 12305004 | Using where |
+----+-------------+-------------------------------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.01 sec)MySQL > explain select * from statistic_sales_data_detail_2018_08 where shop_code like '33%';
+----+-------------+-------------------------------------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | statistic_sales_data_detail_2018_08 | range | idx_shop_code_tikect_time | idx_shop_code_tikect_time | 202 | NULL | 1 | Using index condition |
+----+-------------+-------------------------------------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
二、数据类型数显隐式转换的，索引失效
MySQL > explain select * from statistic_sales_data_detail_2018_08 where shop_code = 33;
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | statistic_sales_data_detail_2018_08 | ALL | idx_shop_code_tikect_time | NULL | NULL | NULL | 12305004 | Using where |
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)MySQL > explain select * from statistic_sales_data_detail_2018_08 where shop_code = '33';
+----+-------------+-------------------------------------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | statistic_sales_data_detail_2018_08 | ref | idx_shop_code_tikect_time | idx_shop_code_tikect_time | 202 | const | 1 | Using index condition |
+----+-------------+-------------------------------------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
三、复合索引查询条件不符合最左匹配原则，索引失效
四、“ != ” 会造成索引失效
MySQL > explain select * from statistic_sales_data_detail_2018_08 where shop_code != '3443';
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | statistic_sales_data_detail_2018_08 | ALL | idx_shop_code_tikect_time | NULL | NULL | NULL | 12305004 | Using where |
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)
五、where条件中“ or ” 连接的条件如果有没使用到索引的，则会造成所用索引失效
MySQL > explain select * from statistic_sales_data_detail_2018_08 where shop_code = '3443' or sale_shop_code = '33';
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | statistic_sales_data_detail_2018_08 | ALL | idx_shop_code_tikect_time | NULL | NULL | NULL | 12305004 | Using where |
+----+-------------+-------------------------------------+------+---------------------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)
六、如果mysql估计使用索引的时间比全表扫描的时间大，则会放弃索引