第五章 创建高性能索引

2019-02-14  本文已影响2人  李逍遥JK

1 索引基础

索引（KEY）是存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能。
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两个列的索引，和创建一个只包含一个列的索引是大不相同的

1.1 索引的类型

索引有很多类型，可以为不同场景提供更好的性能。不同引擎的索引的工作方式也不一样。
B-Tree索引
它使用B-Tree数据结构来存储数据。意味着所有的值都是按顺序存储的，并且每一个子页到根的距离都相同。B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如查出所有I到K开头的名字
请注意，索引对多个值进行排序的依据是CREATE TABLE 语句中定义索引时的顺序。例如，当姓名一样时，根据出生日期来排序。
可以使用B-Tree索引查询的类型:全键值、键值范围或键前缀查找（仅适用于左前缀）。前面所述的索引对一下的类型有效：

1. 全值匹配

全值匹配是指和索引中所有的列进行匹配。如前面的姓名和出生年月

2. 匹配最左前缀

前面提到的索引可用于查找所有姓为 Allen 的人，即只使用索引的第一列

3. 匹配列前缀

也可以用来匹配某一列的开头部分

4. 匹配范围值

前面提到的索引可以用查找姓在Allen和Barraymore之间的人。

5. 精确匹配某一列并范围匹配另外一列

第一列last_name全匹配，第二列firest_name范围匹配。查找Allen 名字是字母K开头的人

6. 只访问索引的查询

B-Tree支持 至访问索引的查询，即查询只需要访问索引，而无需访问数据行。（覆盖索引）
因为索引树中的节点是有序的，所以除了按值查找之外，索引用于查询中的ORDER BY操作。

下面是关于B-Tree索引的限制：

哈希索引
哈希索引基于哈希表实现，只有精确匹配到所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同的键值行计算出来的哈希吗不同。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针
因为索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而哈希也有限制：

空间数据索引(R-Tree)
MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree不同，这类索引无需前缀查询。空间索引会从所有的维度来索引数据。查询时，可以有效地使用任意的维度来组合查询。必须使用GIS相关的函数来维护数据。一般不推荐这个特性。平时一般用PostgreSQL的PostGIS

全文索引
全文索引是一种特殊的类型索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。他有许多需要注意的细节，如停用词、词干和复数、布尔搜索。全文搜索更类似于搜索引擎做的事情，而不是简单的where条件匹配
在相同列上创建全文索引和基于值的B-Tree不会有冲突，全文索引适用于MATCH AGAINST操作

2 索引的优点

  1. 索引大大减少了服务器需要扫描的数据量
  2. 索引可以帮助服务器避免排序和临时表
  3. 索引可以将随机I/O变为顺序I/O

3 高性能索引策略

3.1. 独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引。独立的列：是指索引列不能是表达式的一部分，也不能是函数的参数
例如，下面的这个查询无法使用actor_id列的索引：

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

我们应该养成简化WHERE条件习惯，始终将索引列单独放在比较符号的一侧
下面是一个常见的错误

mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TODAYS(date_col) <= 10;

3.2. 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是提到过的模拟哈希索引。但是有时候不够，因此我们可以索引开始的部分字符，这样可以大大节约索引空间，提高索引效率。但是也会降低索引的选择性（不重复的索引值和表记录（#T）的比值），索引的选择性越高则查询效率越高。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR列，必须使用前缀索引。因为MySQL不允许这么长的长度
诀窍在于选择足够长的前缀保证较高的选择行，同时又不能太长。前缀应该足够长，以使得前缀所有的选择行接近于索引整个列。换句话说，前缀的基数应该接近于完整列的基数
创建前缀索引

mysql> ALTER TABLE sakila.city_demo ADD KEY(city(7));

前缀索引是一种能使索引更小、更快的有效办法，缺点是MySQL无法使用前缀索引做 ORDER BY 和 GROUP BY, 也无法使用前缀索引做覆盖扫描

3.3. 多列索引

在多个列上建立索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫索引合并的策略，一定是可以使用表上的多个单列索引来定位制定的行。
索引合并策略有时候是一种优化的结果，但实际上更多的时候说明了表上的索引建得很糟糕

3.4. 选择合适的索引列顺序(适用于B-Tree索引）

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以，索引可以按照升序或者降序进行扫描，以满足精确符合顺序的ORDER BY , GROUP BY 和 DISTINCT等句子的查询需求

mysql> SELECT * FROM payment WHERE staff_id  = 2 AND customer_id = 584;

是应该创建(staff_id, customer_id)索引还是颠倒一下顺序。可以跑个查询来确定在这个表中值的分布情况，并且确定那个列的选择性最高，看看各个WHERE条件的分支对于的数据基数有多大

mysql> SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment \G
SUM（staff_id = 2) ： 7992
SUM（customer_id = 584) ： 70

根据前面的法则，应该将索引列customer_id放到前面，因为对应的customer_id数量更小。
我们再来看看对于这个customer_id的条件值，对应的staff_id的选择型如何。

mysql> SELECT SUM(staff_id = 2) FROM payment WHERE customer_id  = 584 \G
SUM(staff_id = 2) : 17

这样的优化以来具体的查询值，如果按照上述的办法优化。可能对于其他的条件查询不公平，服务器的整体性能变得更糟。或者其他的查询变得不如预期
如果是从pr-query-digest这样的工具报告中提取最差查询。那么再按上面的方法选定索引的顺序是非常高效的。如果没有列斯的查询来运行，那么还是按照经验法则来做。因为经验法则考虑的是全局基数和选择性

mysql> SELECT 
       COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,
       COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,
       COUNT(*) 
       FROM payment \G
*******************************1. row *************************************
staff_id_selectivity:  0.001
customer_id_selectity: 0.0373
COUNT(*):              16049

customer_id的选择性更高，所以答案是将其作为索引的第一列

mysql> ALTER TABLE payment ADD KEY(customer_id, staff_id);

当使用前缀索引的时候如果某些条件数值比正常的高。这个时候就可能有问题，比如表中符合group_id=103的条件的数量很多，所以不要假设平均的情况下的性能也能代表特殊情况下的性能，特殊情况可能摧毁整个性能

最后还应该考虑WHERE子句中的排序、分组和范围条件等其他因素。

3.5. 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行
当表有聚簇索引时，它的数据行实际存放在索引的叶子页（leaf page)中。术语聚簇，表示数据行和相邻的键紧凑的存储在一起。因为无法把数据行放在两个地方，索引一个表只能有一个聚簇索引（覆盖索引可以模拟多个聚簇索引的情况）
InnoDB中将通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远
聚集索引的优点：

缺点：

在InnoDB表中按主键顺序插入行
在InnoDB表中按主键顺序插入行
如果这个在使用InnoDB的表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据是按顺序写入，对于主键做关联操作的性能也会更好
使用UUID主键插入花费的时间更长，而且索引占用的空间也更大。主要是由于主键字段更长，另外一方面是由于页分裂和碎片导致的。另外主要缺点如下：

顺序的主键什么时候会造成更坏的结果
因为高并发工作的负载，在InnoDB中按主键顺序插入可能造成更明显的争用。可能导致间隙锁竞争。另一个机制可能是AUTO INCREAMENT锁机制；如果遇到这个问题，则可能需要重新设计表或者应用，或者更改innodb_autoinc_lock_mode参数，可能对这种场景会工作的更好

3.6. 覆盖索引

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

有时候无法实现覆盖索引，主要有两个原因

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

MySQL可以有两种方式生成排序的结果：通过排序操作；或者按照索引顺序扫描；如果EXPLAIN出来的type值为index,则说明MySQL使用了索引扫描来做排序
扫描索引本身是很快的，但如果不能覆盖全部的列，那就不得不扫描每一行记录都查询一次对应的行。这基本上是随机I/O,因此按照索引顺序读取数据的速度通常比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时
只有当索引列的顺序和ORDER BY 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）一样时，才能用索引对结果做排序。如果查询关联多张表，则只有当ORDER BY子句引用的字段全部是第一个表时，才能使用索引做排序。
即使ORDER BY不满足索引的最做前缀的要求，也可以用于排序查询，当满足索引的第一列被指定为常数的情况。

KEY( rental_date,  inventory_id, customer_id)
mysql> SELECT rental_id, staff_id FROM sakila.rental 
       WHERE rental_date = '2005-05-24' ORDER BY inventory_id, customer_id \G

下面的查询都没有问题

... WHERE rental_date = '2015-05-25' ORDER BY inventory_id DESC

因为第一列常量，和第二列组在一起形成了最左前缀
下面的这个查询也没有问题，因为ORDEY BY 使用的的两列就是索引的最左前缀

... WHERE rental_date > '2015-08-21' ORDER BY rental_date, inventory_id

下面是一些不能使用索引做排序的查询；

... WHERE rental_date = '2005-05-05' 
ORDER BY inventory_id DESC, customer_id ASC;

... WHERE rental_date = '2005-05-05' ORDER BY inventory_id, staff_id.

... WHERE rental_date = '2012-09-09' ORDER BY customer_id;

... WHERE rental_date > '2019-01-09' ORDER BY  inventory_id , customer_id

... WHERE rental_date = '2008-09-09' 
AND 
inventory_id IN (1, 2) ORDER BY customer_id

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

MyISAM使用压缩来减少索引的大小，让更多的索引放入内存中，这在某种情况下能够极大的提高性能
压缩方式: 先完全保存索引块中的第一个值，然后将其值和第一个值进行比较得到相同的前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块第一个值是'preform'第二个是’preformance'，那么第二个值的前缀压缩后类似‘7，ance'这种形式。
对于CPU密集型应用，因为扫描需要随机查找，压缩引擎使得MyISAM在索引上要慢好几倍。
压缩索引需要在CPU内存资源与磁盘之间取舍。压缩索引只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多

3.9. 冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立刻删除。
冗余索引和重复索引也有一些不同，如果创建了索引（A，B)再创建索引B就是冗余索引。因为（A，B）也可以当（A）来用（只针对B-Tree索引）。但是如果创建（B，A) 或者（B），则不是冗余索引。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引。
当冗余重复索引太多时，建议进行删除
建议使用Percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更

3.10. 未使用的索引

未使用的索引是累赘，考虑删除

3.11. 索引和锁

索引可以让锁锁定更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量，但这个只当InnoDB在存储引擎层能够过滤掉所有不需要的行的时候才有效。如果索引无法过滤所有的行，在InnoDB检索到数据返回给服务层之后，MySQL服务器才能应用WHERE子句。这是已经无法避免锁定行了：InnoDB已经锁住了这些行，到适合的时候才释放。在5.1版本中，InnoDB可以在服务器过滤掉行之后才释放锁。
关于InnoDB、索引和锁有一些很少有人知到的细节：InnoDB在二级索引上使用共享（读）锁，但是访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或 非锁定查询要慢很多

4 索引案例学习

参加《高性能MySQL》相应章节

4.1 支持多种过滤条件

4.2 更新索引统计信息

4.3 优化排序

5 维护索引和表

即使用正确的类型创建了表并加上合适的索引，工作也没有结束：还需要维护表和索引来确保他们的工作。维护主要有三个目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

5.1 找到并修复损坏的表

表损坏（corruption）是很糟糕的事情。假如遇到了古怪的问题，或者一些莫须有的错误可以尝试运行CHECK TABLE。CHECK TABLE 通常能够找出大多数的表和索引的错误。可以通过REPAIR TABLE 命令来修复损坏的表。
如果InnoDB的表发生了损坏，那么一定是发生了严重的错误，需要立刻调查。InnoDB一般不会出现损坏。除非是硬件，或者是磁盘的问题导致了损坏。
如果遇到损坏，最重要的是找出什么导致了损坏，而不是简单的修复。否则很有可能还会不断的损坏。

5.2 更新索引统计信息

MySQL的优化查询器会通过两个API来了解存储引擎的索引值的分布信息，已决定如何使用索引。第一个是records_in_range()，通过向存储引擎传入两个边界值获取这个范围的大概记录有多少条。MyISAM返回精确值。InnodDB返回大概值
第二个API 是 info()，该接口反回各种类型的数据，包括索引的基数（每个键有多少条记录）
每种存储引擎实现索引统计信息的方式不同，所以需要进行ANALYZE TABLE的频率也因引擎的不同而不同，每次运行的成本也不同

5.3 减少索引和数据的碎片

B-Tree索引可能会碎片化，以降低查询的效率。碎片化的索引可能会以很差或无序的方式存在磁盘上，根据设计，B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在物理分布上是顺序紧密的，那么查询的性能就会更好。
碎片一般分为三种：
行碎片
-这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一条记录，碎片也会导致性能下降
行间碎片
指逻辑上的顺序的页，或者行在磁盘上不是顺序存储的。对全表扫描和聚簇扫描有很大影响
剩余空间碎片
剩余空间碎片是指数据页中有大量的剩余空间。这会导致服务器读取大量不需要的碎片，从而造成浪费

6. 总结

1.单行访问是很慢的。特别是在机械硬盘中。如果服务器从存储中读取一个数据块只是为了获取其中的一行。那么就浪费了许多工作。自最好读取的块中包含尽可能多所需要的行。使用索引可能创建位置引用以提升效率
2.按顺序访问数据是很快的

总的来说，编写查询的时候能够选择合适的索引避免单行查找、尽可能的使用数控原生顺序从而避免额外的顺序操作，并尽可能使用覆盖查询
理解索引是如何工作的非常重要,应该根据这些理解来创造最合适的索引，而不是盲目根据经验法则或者推论
如何判断索引合理：按照相应时间对查询进行分析，找出耗时查询，分析schema,SQL和索引结构，判断是否扫描太多的行，是否做了多余的排序或者临时表，是否使用随机I/O访问数据，或者是有太多回表查询那些不再索引中的列的操作
如果一个查询无法从所有可能的索引中获益，则应该看看是否创建一个更适合的索引来提升性能。如果不行，那么看看是否能够重写查询，将其转化成一个能高效利用现有索引或者新创建索引的查询
如果根据基于响应时间的分析不能找到有问题的查询。是否有问题呢
。一般来说不可能，对于诊断时抓不到得到查询，那就不是问题。但是可能随着时间的推移。如果还是找到那些索引不是和的查询，提前优化，则可以使用pt-query-digest的查询审查review功能，并分析器EXPLAIN出来的执行计划

上一篇下一篇

猜你喜欢

热点阅读