有时有索引但并不一定被优化器选择使用,那么什么时候不使用索引呢?通常的场景大致有:
- 1、 以%开头的like查询,b-tree索引不起作用
解决方案:
①、先投影主键索引覆盖扫描进行模糊查询,然后内连接,理论上比目前这种直接全表扫描更快。具体可参看后面的样例说明。
②、子查询 提供方案不为绝对,具体看实际情况。 - 2、数据类型出现隐式转换的时候也不会使用索引。比如字符串使用检索条件值没加引号,那该列建立的索引不会生效。
- 3、复合索引的情况下,加入查询条件不包含索引列最左边部分,即不满足最左原则leftmost,是不会使用复合索引的。
- 4、MYSQL估计使用索引比全表扫描更慢,则不适用索引了。
- 5、用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到
感兴趣可以具体看下面样例,针对每点都有说明
- 1、 以%开头的like查询,b-tree索引不起作用,一般推荐使用fulltext全文索引来解决类似全文检索问题。或者考虑利用innodb表时聚簇表特点,采取一种轻量级的解决方式:通常但不绝对,索引比表小,扫描索引比扫描表快的多,innodb表上二级索引idx_last_name实际上存储字段last_name还有主键actor_id,理想的访问方式先扫描二级索引idx_last_name获取满足条件last_name like '%NI%'的主键actor_id列表,然后根据主键回表检索记录,这样避免全表扫描导致的大亮io请求。eg:
mysql> explain select * from actor where last_name like '%NI%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: Using where
1 row in set (0.02 sec)
解决方案1:试试先投影查询然后内连接 ,这种也叫谓词下推
mysql> explain select * from (select actor_id from actor where last_name like '%NI%') a,actor b where a.actor_id=b.actor_id\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: a.actor_id
rows: 1
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: actor
type: index
possible_keys: NULL
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 200
Extra: Using where; Using index
3 rows in set (0.00 sec)
可以看出 执行计划是通过内层查询的using index (代表索引覆盖扫描),之后通过主键join操作,去actor表获取最终查询结果,理论上比直接全表扫描更快。
- 2 数据类型存在隐式转换的不会使用索引。特别是当列类型是字符串,那么作为检索条件时,值一定要用引号引起来。否则即便有索引,mysql也不会引用到,mysql默认把输入的常量值进行转换以后才进行检索。比如actor.last_name,eg:
explain select * from actor where last_name=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: idx_actor_last_name
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: Using where
1 row in set (0.09 sec)
加上引号后,可以看到使用了索引。
explain select * from actor where last_name='1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)
- 3、复合索引不满足最左原则,不会使用该复合索引
explain select * from payment where amount=3.98 and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.06 sec)
4 评估使用所以比全表扫描更慢,则不使用索引。eg:
mysql> update film_text set title=concat('S',title);
Query OK, 1000 rows affected (0.35 sec)
Rows matched: 1000 Changed: 1000 Warnings: 0
mysql> explain select * from film_text where title like 'S%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_text
type: ALL
possible_keys: idx_title_desc_part,idx_title_description
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.02 sec)
mysql5.6版本中,可以透过trace清晰看到优化器选择的过程,全表扫描table scan需要访问的记录rows 为1000,代价cost计算为233.53;
“table_scan:{
"rows":1000,
"cost":233.53
}”/*table_scan*/
面对idx_title_desc_part索引过滤条件时,优化器预估需要返回1998条记录,访问代价为1198.6,远高于全表扫描的代价,索引优化器倾向于选择全表扫描。
更换查询的值为一个选择率更高的值,就能发现优化器更倾向于选择索引扫描。
mysql> explain select *from film_text where title like 'SW'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_text
type: range
possible_keys: idx_title_desc_part,idx_title_description
key: idx_title_desc_part
key_len: 32
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.02 sec)
通过trace能够得知,title like 'SW%'优化器预估需要返回66条记录,代价cost为80.21,远小于全表扫描的代价,所以优化器倾向于选择索引扫描,所以查询的时候,筛选性越高越容易使用到索引,筛选性越低越不容易使用索引。
- 5 、用or分割开的条件,不区分在or的前后,如果or的一边条件中的列有索引,另一边的列中没有索引,涉及到的索引均不会被用到,eg:
explain select * from payment where customer_id=203 or amount=3.96\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: idx_fk_customer_id
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
因为or后面的条件列没有索引,所以查询走的全表扫描,在存在全表扫描的情况下,没必要多一次索引扫描增加I/O访问,一次全表扫描过滤条件就足够了。