索引

索引优化

mysql优化之使用索引优化查询

1.匹配全值(match the full value)

对索引中所有列都指定具体值,即对索引中的所有列都有等值匹配的条件。

#设置组合索引(rental_date,inventory_id,customer_id)为唯一索引。 EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25 17:22:10'  AND inventory_id=373  AND customer_id=343;

2.匹配值的范围查询(match a range of values)

对索引值进行范围查找。

#设置索引idx_fk_customer_id(customer_id) EXPLAIN SELECT * FROM rental WHERE customer_id >= 373 AND customer_id < 400;

范围查询

3.匹配最左前缀(match a leftmost prefix)

仅仅使用索引中的最左边列进行查询。比如组合索引(col1,col2,col3)能够被col1,col1+col2,col1+col2+col3的等值查询利用到的。

#创建索引idx_payment_date(payment_date,amount,last_update); EXPLAIN SELECT * FROM payment WHERE payment_date='2006-02-14 15:16:03' AND last_update = '2006-02-15 22:12:32';

匹配最左前缀

从结果可以看出利用了索引,但又row为182行,所有只使用了部分索引。

EXPLAIN SELECT * FROM payment WHERE amount=3.98 AND last_update='2006-02-15';

匹配最左前缀-2

从结果看出,这次查询没有利用索引,进行了全表查找。

4.仅对索引查询进行查询(index only query)

当查询列都在索引字段中。即select中的列都在索引中。

EXPLAIN SELECT last_update FROM payment WHERE payment_date='2005-08-19 21:21:47' AND amount=4.99;

不用回表查找

extra部分Using index,说明不需要通过索引回表,Using index就是平时说的覆盖索引扫描(即找到索引,就找到了要查询的结果,不用再回表查找了)。

5.匹配列前缀(match a column prefix)

仅仅使用索引的第一列,并且只包含索引第1列的开头部分进行查找。

#创建索引idx_title_desc_part(title(10),description(20)); EXPLAIN SELECT title FROM film_text WHERE title LIKE 'AFRICAN%';

匹配前缀索引

6.索引部分等值匹配,部分范围匹配

EXPLAIN SELECT inventory_id FROM rental WHERE rental_date='2006-02-14 15:16:03' AND customer_id >= 300 AND customer_id <=400;

这里写图片描述

type=ref,说明使用了索引。

7.列名是索引,column_name is null,使用索引

EXPLAIN SELECT * FROM payment WHERE rental_id IS NULL;

null

索引存在但不能使用索引的典型场景

1.以%开头的like查询

EXPLAIN SELECT * FROM actor WHERE last_name LIKE '%NI%';

这里写图片描述

#优化 EXPLAIN SELECT * FROM (SELECT actor_id FROM actor WHERE last_name LIKE '%NI%')a,actor b WHERE a.actor_id=b.actor_id;

这里写图片描述

2.数据类型出现隐式转化,不会使用索引

EXPLAIN SELECT * FROM actor WHERE last_name=1;

这里写图片描述

#使用索引 EXPLAIN SELECT * FROM actor WHERE last_name='1';

这里写图片描述

3.组合索引,不满足最左原则,不使用符合索引

EXPLAIN SELECT * FROM payment WHERE amount=3.98 AND last_update='2006-02-15 22:12:32';

这里写图片描述

4.估计使用索引比全表扫描还慢,则不要使用索引

如查询以“S”开头的标题的电影,返回记录比例比较大,mysql预估索引扫描还不如全表扫描。

EXPLAIN SELECT * FROM film_text WHERE title LIKE 'S%';

这里写图片描述

5.用or分割条件,若or前后只要有一个列没有索引,就都不会用索引

EXPLAIN SELECT * FROM payment WHERE customer_id=203 OR amount=3.96;

这里写图片描述

作者:林湾村龙猫

链接:https://www.jianshu.com/p/e3d6a7a83f39

來源:简书

简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。

吉ICP备16008059号

Back to top