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

正确创建和使用索引策略是实现高性能查询的基础，本文总结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 customidfrom 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 t1join (select prodid from reyment where staffid=2 and customer like '%11') t2 on t1.prodid = t2.prodid

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