MySQL优化五-高性能的8个索引策略

正确创建和使用索引策略是实现高性能查询的基础，本文总结7个索引策略。

一、独立的列

独立的列是指索引不能是表达式的一部分，也不能是函数的参数。

mysql>select id from actor where id+1 = 5;    //错误

mysql>select id from actor where id = 5-1;    //正确

二、前缀索引和索引的特性

有时索引的很长的字符列，会让索引变得很慢且大，一个策略是使用模拟的哈希索引MySQL优化四-MySQL Innodb 自定义Hash索引_一个高效工作的家伙的博客-CSDN博客

但有时候这样做还不够，需使用前缀索引。

 前缀索引是指，将字符列的前N位，作为索引列，而不是索引整个字符。比如

mysql> alter table city_demo add KEY ( city(7) )

前缀索引是一种能使用索引更小，更快的办法，但同时也有一个缺点，mysql无法用前缀索引做order by 和 group by ，也无法使用前缀索引做覆盖扫描。

同时还出现一个问题，选择前几位作为前缀索引，选太多则索引变大，选太少则选择性太低，同一索引的结果数据太多。

根据以上地市的例子，使用前3，前4，前5。。。前7位分别计算其选择性。

mysql> select count(distinct left(city,3))/count(*) as sel3,
    count(distinct left(city,4))/count(*) as sel4,
    count(distinct left(city,5))/count(*) as sel5,
    count(distinct left(city,6))/count(*) as sel6,
    count(distinct left(city,7))/count(*) as sel7,
    from city_demo ;

当前缀长度到7的时候，再增加前缀长度，其选择性提升的幅度很小了。此时可以将长度为7，作为前缀索引的字符长度。

三、多列索引

多列索引是指where子句，有两个及以上的查询列，需要将两列作为一个索引，以不是对每列单独作索引。

mysql> select id,city from city_demo where id=1 and city='XX'

此时将合并索引

mysql> alter table city_demo add KEY ( id,city )

四、选择合适的索引顺序

在一个多列索引中，索引列的顺序意味着，索引按照先左列进度排序，其次是第二列等，所以多列索引的顺序很重要。有一个经验法则是，将选择性最高的列，放在索引最前列。

比如：

mysql> select * from payment where staffid=2 and customerid=584

 合并索引的顺序，应该是staffid在前，还是customerid在前面呢。

mysql> select sum(staffid=2),sum(cusotmer=584) from paument

********************************
sum(staffid=2) : 7992
sum(cusomerid=584) : 30

根据前面的经验法则，应将索引customerid放在前面，先找到customerid=584的30条记录，再从中找到staffid=2的记录，比先找到staffid=2的7992条记录，再从中找到customerid=584的记录，会快很多。

但这种判断，有时也许会有偏差，特别对于随机分布的数据来说，更是这样。所以需要用另一种评估方式。

myql> select count(distinct staffid>/count(*) as staffid,
        count(distinct customid>/count(*) as customid
        from payment;
************************************
staffid : 0.0001,
customid: 0.0373

从上结果可知，customerid的选择性更高。所以将其作为索引列的第一列。

mysql> alter table payment add KEY( customerid , staffid )

五、聚簇索引

• 聚簇索引：
  • 将数据存储和索引放到了一块，找到了索引也就找到了数据
  • 一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
• 非聚簇索引：
  • 将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。
  • MyISAM通过key_buffer把索引先缓存到了内存中，当需要访问数据时(通过索引访问数据)，在内存中直接查找索引，然后通过索引找到磁盘相应数据。这也就是为什么索引不在key buffer命中时，速度慢的原因。

聚集索引⼀个表只能有⼀个，⽽⾮聚集索引⼀个表可以存在多个。聚集索引存储记录是物理上连续存在，⽽⾮聚集索引是逻辑上的连续，物理存储并不连续。

澄清一个概念：

• InnoDB中，在聚集索引上创建的索引叫做辅助索引
• 辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引、辅助引擎叶子节点存储的不再是行的物理位置，而是主键值。

一句话总结： innoDB中，每个表都有一个聚簇索引，如果设置了主键，它就是聚簇索引，一般要求字段自增。

六、覆盖索引

 覆盖索引是指，一个索引包含所有需要查询的字段值。比如payment表有一个多列索引（staffid，customerid），如果只需访问这两列，就可使用这个索引作为覆盖索引。

mysql> select staffid，customerid from reyment 
*******以上是覆盖索引

以下情况，无法用到覆盖索引：

mysql> select * from reyment where staffid=2 and customer like '%11'

1、查询选择了所有列，或选择了非索引的列。

2、覆盖索引无法执行like操作，索引只能作左前缀的like匹配；对于开头是%通配符的like查询，只有作全表扫描。

可以用另一种办法解决，叫延迟满足，即延迟了对列的访问。 将覆盖索引扩展至三个（staffid，customerid，prodid），查询语句重构如下：

mysql> select * from reyment t1
    join (
        select prodid from reyment where staffid=2 and customer like '%11'
    ) t2 on t1.prodid = t2.prodid

 性能是否提升，取决于子句 select prodid from reyment where staffid=2 ，是否筛选掉大部分行，如果是，就可以这么优化。

七、使用索引扫描来做排序

mysql有两种方式生成有序的结果：通过排序操作，或者按索引顺序扫描。如果explain出来的type列的值为“index”，则说明使用了索引扫描来排序。

比如创建一个多列索引，包含以及顺序是：customerid , staffid ，inventoryid，以下测试均以此例。

mysql> alter table payment add KEY( customerid , staffid ，inventoryid )

以下条件满足索引扫描：

1、只有索引列的顺序和order by子句的顺序一致，且所有列的排序方向一致；

mysql> order by customerid , staffid ，inventoryid  
*****顺序一致，使用索引排序
mysql> order by inventoryid , customerid , staffid  
*****顺序不一致，不使用索引排序

2、多表关联，只有当order by 子句引用的字段，全部是第一个表时；

mysql>select * from payment t1 , rental t2 where t1.staffid = t2.staffid
 order by t1.customerid , t1.staffid ，t1.inventoryid  

3、满足索引的最左前缀的要求；

mysql>..... where customerid = 33034 order by staffid desc
mysql>..... where customerid = 33034 order by staffid , inventoryid
mysql>..... where customerid > 33034 order by customerid，staffid 

 以下不能使用索引排序：

1、使用不同的排序方向，但索引列都是正序排序：

mysql>.... order by customerid asc , staffid desc

 2、order by 引用一个不在索引的列：

mysql>.... order by customerid , rental_date
******rental_date不在索引列

 3、where和order by的列，无法组合成索引的最左前缀：

mysql>.....where customerid =33034 order by inventoryid
******索引列是customerid , staffid ，inventoryid，缺少了staffid列，导致无法走索引排序

 4、索引的第一列是范围条件：

mysql>.....where customerid > 33034 order by staffid，inventoryid
*********where 和 order by 组合列中，第一列是范围条件，不走索引扫描

mysql>.....where customerid > 33034 order by customerid ，staffid，inventoryid
*********order by就有走索引扫描

 5、多个等于条件，对于排序来说，也是一种范围条件：

mysql>.....where customerid = 33034 and staffid in (1,2) order by inventoryid

 八、冗余索引和重复索引

重复索引是指在相同的列上，按相同的顺序创建相的的索引，应避免创建重复索引。冗余索引

mysql> create table test (
    ID int not null PRIMARY KEY,
    UNIQUE(ID),
    INDEX(ID),
)

 冗余索引是指创建了（A，B）索引，再创建（A）；或者将一个索引扩展为（A，ID），其中 ID是主键，就是冗余索引。