MySQL调优之索引一 （前缀、覆盖索引）

1.什么是前缀索引？

 假如某个字段的类型是字符串，并设置这个字段加索引，索引就会很长，导致索引变大且慢，一般情况下可以用这个字段的部分字符串，

 这样大大的减少索引的空间，从而提高索引效率，但这会降低索引的选择性。

什么是索引的选择性？

  定义：是指不重复的索引值和数据表记录总数的比值，范围从1/T 到1之间。

  索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

什么时候用前缀索引？

blob,text,varchar 类型的列，必须使用前缀索引。

选择足够长的前缀来保证字段较高的选择性，但又不能太长

计算完整列的选择性：

select count(distinct left(city,3))/count(*) as sel3 from city;

计算完成之后可以创建前缀索引 

alter table city add key(city(7));

优缺点

前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。

 

2.什么是覆盖索引？

1. 如果一个索引包含所有需要查询的字段的值，称为覆盖索引。即 select name from user where name="123";
2. 不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
3. 不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory 不支持覆盖索引。

优点：

     1.索引条目比数据行小，如果只读索引，mysql查询效率会更快

     2.索引是按照列值的顺序存储的，所以对于IO密集型的范围查询会比较随机IO少得多

     3. 因为InnoDB 是聚簇索引，覆盖索引对InnDB 表特别有用

实战

1、当发起一个被索引覆盖的查询时，在explain的extra列可以看到using index的信息，此时就使用了覆盖索引

 explain select store_id,film_id from inventory\G

2、在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以进一步的进行优化，可以使用innodb的二级索引来覆盖查询。

例如：actor使用innodb存储引擎，并在last_name字段又二级索引，虽然该索引的列不包括主键actor_id，但也能够用于对actor_id做覆盖查询

mysql> explain select actor_id,last_name from actor where last_name='HOPPER'\G