高性能MySQL--高性能的索引策略_正确地创建和使用索引是实现高性能查询的基础-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_33300026/article/details/103958189

正确地创建和使用索引是实现高性能查询的基础。

1.独立的列

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

select * from user where id+1 > 5;类似的查询不能正确地使用索引。

2.前缀索引

通常在待查询的长字符串上截取部分前缀字符串来作为索引列，这样可以大大节约索引空间，从而提高索引效率。但是这样也会大大降低索引的选择性。

索引的选择性

是指不重复的索引值和数据表的记录总数（N）的比值，比值在1/N到1之间。索引的选择性越高，则查询效率越高，因为选择性高的索引可以让MySQL在查找是过滤掉更多的行。

通常来说，对于BLOB,TXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完全长度。

如何选择足够长的前缀索引以保证较高的选择性？通过使用计算公共前缀的总数/数据表格总数来计算。例如：

select coun(DISTINCT LEFT(city,3))/COUNT(*) AS sel3,
 coun(DISTINCT LEFT(city,4))/COUNT(*) AS sel4,
 coun(DISTINCT LEFT(city,5))/COUNT(*) AS sel5,
 coun(DISTINCT LEFT(city,6))/COUNT(*) AS sel6,
 coun(DISTINCT LEFT(city,7))/COUNT(*) AS sel7
 from city_demo;

根据前缀列的选择性来判断我们需要的前缀字符串长度。

如何创建前缀索引？

Alert table city_demo ADD KEY (city(7)); #7是我们通过比表完整列的选择性得出来的一个相对具有更好选择性的前缀字符串长度

3.多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。多列索引遵循最左原则。

4.合适的索引列顺序

例如：

SELECT * FROM payment where staff_id = 2 and customer_id = 548;

此时如果建立staff_id和customer_id的联合索引，应该由staff_id在前还是customer_id在前呢？

首先分别查询where条件后各个分支的数据基数：

Select count(*) from payment where staff_id = 2; #7992
select count(*) from payment where customer_id = 548; #30

根据前面的经验法则来看应该将customer_id放到前面，因为对应条件的值customer_id数量更少，能过滤掉更多的数据，但是这样做非常依赖选定的具体值，可能对其它的查询非常不公平，所以我们采用经验法则来做，因为经验法则考虑的是全局基数和选择性，而不依赖某个具体的查询：

SELECT COUNT(DISTINCT(STAFF_ID))/COUNT(*) AS STAFF_ID_SELECTIVITY,
COUNT(DISTINCT(CUSTOMER_ID))/COUNT(*) AS CUSTOMER_ID_SELECTIVITY,
COUNT(*) FROM PAYMENT;


#STAFF_ID_SELECTIVITY:0.0001
#CUSTOMER_ID_SELECTIVITY:0.0373
#COUNT(*):16049

可以看出customer_id具有更高的选择性，因此添加索引列：

alert table payment ADD key(CUSTOMER_ID,STAFF_ID);

5.聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

一个表只有一个聚簇索引，因为无法同时把数据行存放在两个不同的地方。由于是存储引擎实现索引，所以不是所有的存储引擎都支持聚簇索引。但是这里讨论的原理对所有支持聚簇索引的存储引擎都适用。

InnoDB默认通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

聚簇索引的优点：

1.可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID将用户的所有邮件都聚集在一页，这样能能快的从磁盘读取用户的全部邮件。

2.数据访问更快。索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。

3.使用覆盖索引扫描的查询可以直接使用叶节点上的主键值

缺点：

1.当数据全部存储在内存中时，聚簇索引将没有任何优势。

2.插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。如果不是按照这种方式插入，可以使用OPTIMIZE TABLE命令重新组织一下表。

3.更新聚簇索引列的代价很高。

4.插入新行，或者主键更新会导致数据行移动，可能面临页分裂的问题。

5.聚簇索引可能会导致全表扫描变慢。

6.二级索引（非聚簇索引）可能比想象的要更大，因为二级索引的叶节点包含了主键列、

7.二级索引速度更慢。两次查找，而不是一次。

聚簇索引

非聚簇索引

在InnoDB表中按主键顺序插入行

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

6.覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们称之为“覆盖索引”。

覆盖索引的优点：

1.索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部方到内存之中。（这对MyISAM尤其正确，因为MyISAM能压缩索引以变得更小）

2.因为索引是按照列值顺序存储的（至少在单个页面内如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。

3.一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统的调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。

4.由于InnoDB的聚簇索引，覆盖索引对于InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等都不能存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

tips：当发起一个覆盖索引查询时，执行计划的Extra列可以看到“Using index”信息。

1,Extra中的为Using index的情况
　　　　where筛选列是索引的前导列 &&查询列被索引覆盖 && where筛选条件是一个基于索引前导列的查询，意味着通过索引超找就能直接找到符合条件的数据，并且无须回表

2,Extra中的为空的情况
　　　　查询列存在未被索引覆盖&&where筛选列是索引的前导列，意味着通过索引超找并且通过“回表”来找到未被索引覆盖的字段，

3,Extra中的为Using where Using index：
　　出现Using where Using index意味着是通过索引扫描（或者表扫描）来实现sql语句执行的，即便是索引前导列的索引范围查找也有一点范围扫描的动作，不管是前非索引前导列引起的，还是非索引列查询引起的

7.使用索引扫描来做排序

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

如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上就是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查询型查询的限制是一样的：需要满足索引的最左前缀的要求，否则无法利用索引排序。

有一种情况下ORDER BY子句可以不满足索引的最左前缀要求，就是前导列为常量的时候。

8.压缩（前缀压缩）索引

MyISAM压缩每个索引块的方法是，首先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似于“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是逆序扫描------例如ORDER BY DESC-----就不是很好了。所有在块中查找某一行的操作平均都要扫描半个索引块。

对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡，压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对于某些查询带来的好处会比成本多很多。即压缩索引适合IO密集型应用，不太适合CPU密集型应用。