读高性能MySQL（第4版）笔记09_创建高性能索引（下）

利佰兴

1. 覆盖索引

1.1. 设计优秀的索引应该考虑到整个查询，而不单是WHERE条件部分

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

1.3. 只有B-tree索引可以用于覆盖索引

1.4. 如果查询只需要扫描索引而无须回表

1.4.1. 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量

1.4.2. 覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中

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

1.4.4. 由于InnoDB的聚簇索引的特点，覆盖索引对InnoDB表特别有用

1.4.4.1. InnoDB的二级索引在叶子节点中保存了记录的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询

1.5. 在索引中满足查询的成本一般比查询记录本身要小得多

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

2.1. 生成有序的结果

2.1.1. 通过排序操作

2.1.2. 按索引顺序扫描

2.2. 如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录都回表查询一次对应的记录

2.2.1. 基本上都是随机I/O

2.2.2. 按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的应用负载上

2.3. 只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序

2.4. 如果前导列为常量的时候，ORDER BY子句中的列也可以不满足索引的最左前缀的要求

2.5. 如果在WHERE子句或者JOIN子句中将这些列指定为了常量，就可以“填补”索引字段的间隙了

2.6. 使用索引做排序的另一个最重要的场景是，查询语句中同时有ORDERBY和LIMIT子句的情况

3. 重复索引

3.1. 指在相同的列上按照相同顺序创建的相同类型的索引

3.2. MySQL允许在相同列上创建多个相同的索引

3.2.1. MySQL会抛出一个警告，但是并不会阻止你这么做

3.2.2. MySQL需要单独维护重复的索引，优化器在优化查询的时候也需要逐个地进行评估，这会影响性能，同时也浪费磁盘空间

4. 冗余索引

4.1. 如果创建了索引（A，B），再创建索引（A）就是冗余索引

4.1.1. 索引（A，B）也可以当作索引（A）来使用

4.1.2. 前一个索引的前缀索引

4.1.3. 这种冗余只是对B-tree索引来说的

4.2. 如果再创建索引（B，A），则不是冗余索引，索引（B）也不是，因为B不是索引（A，B）的最左前缀列

4.3. 将一个索引扩展为（A，ID），其中ID是主键，因为主键列已经包含在二级索引中了，所以这也是冗余的

4.4. 冗余索引通常发生在为表添加新索引的时候

4.5. 大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引

4.6. 出于性能方面的考虑也需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能

4.7. 索引越多，插入的速度越慢

4.7.1. 增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后达到了内存瓶颈的时候

5. 未使用的索引

5.1. 一些服务器永远不用的索引

5.2. 这样的索引完全是累赘，建议删除

5.3. 找到未使用索引的最好办法就是使用系统数据库performance_schema和sys

5.4. 在sys数据库中，在table_io_waits_summary_by_index_usage视图中可以非常简单地知道哪些索引从来没有被使用过

6. 解决冗余索引和重复索引的方法

6.1. 删除这些索引就可以了

6.2. 针对INFORMATION_SCHEMA表编写各种复杂的查询来识别这类索引

6.3. Percona工具箱中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复索引

6.4. 使用Percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更

6.5. 使用MySQL 8.0的不可见索引特性，而不是直接删除索引

6.5.1. 可以通过ALTER TABLE语句，改变索引的一个标志位，使得优化器在确定执行计划时，忽略该索引

6.5.2. 如果你发现计划删除的索引依旧有非常重要的作用，可以直接把索引改成可见，而不需要重新构建该索引

7. 维护索引和表

7.1. 找到并修复损坏的表

7.1.1. 对于数据表来说，最糟糕的情况就是表被损坏了

7.1.2. 损坏的索引会导致查询返回错误的结果或者出现莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃

7.1.3. 可以尝试运行CHECK TABLE来检查是否发生了表损坏

7.1.4. 可以使用REPAIR TABLE命令来修复损坏的表

7.1.5. 如果是InnoDB存储引擎的表发生了损坏，那么一定是发生了严重的错误，需要立刻调查一下原因

7.1.5.1. 常见的类似错误通常是由于尝试使用rsync备份InnoDB导致的

7.1.6. 如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不只是简单地修复，否则很有可能还会不断地出现数据损坏的情况

7.2. 维护准确的索引统计信息

7.2.1. MySQL的优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行

7.2.2. 可以使用SHOW INDEX FROM命令来查看索引的基数（cardinality）

7.3. 减少索引和数据的碎片

7.3.1. B-tree索引可能会产生碎片化，这会降低查询的效率

7.3.2. 碎片化的索引可能会以很差或者无序的方式存储在磁盘上

7.3.3. 如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好

7.3.3.1. 否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多

7.3.3.2. 对于索引覆盖扫描，这一点会表现得更加明显

7.3.4. 行碎片（Row fragmentation）

7.3.4.1. 数据行被存储在多个地方的多个片段中

7.3.4.2. 即使查询只从索引中访问一行记录，行碎片也会导致性能下降

7.3.5. 行间碎片（Intra-row fragmentation）

7.3.5.1. 指逻辑上顺序的页或者行，在磁盘上不是顺序存储的

7.3.5.2. 对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益

7.3.6. 剩余空间碎片（Free space fragmentation）

7.3.6.1. 指数据页中有大量的空余空间

7.3.6.2. 导致服务器读取大量不需要的数据，从而造成浪费

7.3.6.3. 可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据

7.3.6.4. 对多数存储引擎都是有效的

8. 原则

8.1. 单行访问是很慢的，特别是在机械硬盘中存储

8.1.1. 尽可能选择合适的索引以避免单行查找

8.1.2. SSD的随机I/O要快很多，不过这一点仍然成立

8.2. 按顺序访问范围数据是很快的

8.2.1. 顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对于机械硬盘）

8.2.2. 如果服务器能够按需顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了

8.3. 索引覆盖查询是很快的

8.3.1. 如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行

8.4. 建议按响应时间来对查询进行分析

8.4.1. 如果一个查询无法从所有可能的索引中获益，则应该看看是否可以创建一个更合适的索引来提升性能


来源:https://www.cnblogs.com/lying7/p/17699932.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！