MySQL数据SQL优化中，索引不被使用的典型场景总结
有些时候虽然有索引，但是不被优化器选择使用，下面是开发过程中遇到的不能使用索引的几种情况：
1.以%开头的like查询不能够利用B-tree索引，执行计划中key的值为NULL表示没有使用索引。
mysql> explain select * from actor where last_name like '%NI%'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 200 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.42 sec)
数据库中InnoDB引擎默认使用B-Tree索引，再B-Tree索引结构中，以%开头的查询自然就法利用索引了。
一般都推荐使用全文索引（Full-text)来解决类似的全文检索问题。（这里没有给出全文索引的解决方案，可以自己查一下）
除此之外大家还可以利用InnoDB的表都是聚族表的特点，采取一种轻量级别的解决方式：
一般情况下，索引都会比表小，扫描索引比扫描表更快（特殊情况下，索引会比表更大）。
在InnoDB表上二级索引idx_last_name实际上存储字段last_name和主键actor_id,那么理想的访问方式应该是先扫描二级索引idx_last_name获得满足条件 last_name like '%NI%'的主键actor_id列表，之后根据主键回表去检索记录，这样就避免了全表扫描演员表actor产生的大量IO请求。
mysql> explain select * from (select actor_id from actor where last_name like '%NI%') a,actor b where a.actor_id = b.actor_id; +----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+ | 1 | SIMPLE | actor | NULL | index | PRIMARY | idx_actor_last_name | 137 | NULL | 200 | 11.11 | Using where; Using index | | 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.actor.actor_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------------------+---------+-----------------------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.00 sec)
通过执行计划中可以看到，内层查询的Using index 代表索引覆盖扫描，之后通过主键join操作去演员表actor中获取最终查询结果，理论上比直接扫描全表扫描更快一些。
2.数据类型出现隐式转换的时候也不会使用索引
特别是在当列类型是字符串时，那么一定记得在where条件中把字符常量的值用引号引起来，否则即便这个列上有索引，MYSQL也不会使用，因为MYSQL默认把常量值进行转换后才进行检索。
例如：演员表actor中姓氏字段last_name是字符型的，但是SQL语句的条件值1是一个数值类型，因此即便存在索引idx_last_name,MYSQL也不能正确使用索引，而是进行全表扫描。
mysql> explain select * from actor where last_name =1; +----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | actor | NULL | ALL | idx_actor_last_name | NULL | NULL | NULL | 200 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+ 1 row in set, 3 warnings (0.08 sec)
加上引号之后，再执行一次，就发先使用上索引了。
mysql> explain select * from actor where last_name ='1'; +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | actor | NULL | ref | idx_actor_last_name | idx_actor_last_name | 137 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.10 sec)
3.复合索引查询条件不符合最左匹配原则，是不会使用复合索引的。
最左匹配原则是要满足复合索引最左边的字段，如果查询条件不是第一个复合索引的第一个字段，则不符合最左匹配原则。
mysql> explain select * from payment where amount =3.98 and last_update ='2006-02-15 22:12:32'; +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | payment | NULL | ALL | NULL | NULL | NULL | NULL | 16086 | 1.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.04 sec)
4.如果MySQL估计使用索引比全表扫描慢，则不使用索引。
例如:查询以“S”开头的标题的电影，需要返回的记录比例较大，MySQL就预估索引扫描还不如全表扫描更快：
mysql> update film_text set title =concat('s',title);
Query OK, 1000 rows affected (3.47 sec)
Rows matched: 1000 Changed: 1000 Warnings: 0
mysql> explain select * from film_text where title like 's%';
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film_text | NULL | ALL | idx_title_description | NULL | NULL | NULL | 1000 | 11.11 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
在MySQL 5.6版本中，能够通过Trace 清晰地看到优化器的选择过程，对用时少的进行选择。
**5.用or分割开的条件，如果or前的条件中有列索引，而后面的列中没有索引，那么涉及的索引都不会被用到。**
mysql> explain select * from payment where customer_id =203 or amount =3.96;
+----+-------------+---------+------------+------+--------------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | payment | NULL | ALL | idx_fk_customer_id | NULL | NULL | NULL | 16086 | 10.15 | Using where |
+----+-------------+---------+------------+------+--------------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多做一次索引扫描增加I/O访问，一次全表扫过滤条件就足够了。
当or前后两个条件都有索引时，是会用到索引的。
mysql> explain select * from payment where customer_id= 203 or amount =3.96; +----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+ | 1 | SIMPLE | payment | NULL | index_merge | idx_fk_customer_id,idx_amount | idx_fk_customer_id,idx_amount | 2,3 | NULL | 21 | 100.00 | Using union(idx_fk_customer_id,idx_amount); Using where | +----+-------------+---------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------------------------+ 1 row in set, 1 warning (0.05 sec)
索引的总结：如果在查询条件中，有字段进行全表扫描，那么索引也就不会被使用。条件中使用复合索引时，要遵守复合索引的使用规则，不然索引也不会被使用。
相关推荐
西瓜皮儿的皮儿 2020-09-07
lifeison 2020-08-18
cyhgogogo 2020-08-18
minerk 2020-08-15
webliyang 2020-08-15
Jaystrong 2020-06-16
ribavnu 2020-11-16
liuweiq 2020-09-09
silencehgt 2020-09-07
sunnyxuebuhui 2020-09-07
houdaxiami 2020-08-15
抱抱熊 2020-08-15
adsadadaddadasda 2020-07-19
ztyzly00 2020-07-18
sofast 2020-07-08
明月清风精进不止 2020-07-05
bluetears 2020-07-05
URML 2020-07-04
minggehenhao 2020-07-04
minggehenhao 2020-06-21