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

10月前作者：躺柒分类：Toy博客阅读(62) 违法举报

这篇具有很好参考价值的文章主要介绍了读高性能MySQL（第4版）笔记09_创建高性能索引（下）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

读高性能MySQL（第4版）笔记09_创建高性能索引（下）文章来源地址https://www.toymoban.com/news/detail-709771.html

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

到了这里，关于读高性能MySQL（第4版）笔记09_创建高性能索引（下）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：

领支付宝红包赞助服务器费用

读高性能MySQL（第4版）笔记10_查询性能优化（上）

4.11.1.1. 在存储引擎层完成的 4.11.2.1. 直接从索引中过滤不需要的记录并返回命中的结 4.11.2.2. 在MySQL服务器层完成的，但无须再回表查询记录 4.11.3.1. 在MySQL服务器层完成 4.11.3.2. 需要先从数据表中读出记录然后过滤 4.13.2.1. 使用单独的汇总表 5.5.1.1. 定期清除大量数据时，

2024年02月08日
浏览(61)
读高性能MySQL（第4版）笔记12_查询性能优化（下）

2.3.1.1. 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行 2.3.1.2. 即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次传输排序的成本非常高 2.3.2.1. 先

2024年02月08日
浏览(49)
读高性能MySQL（第4版）笔记11_查询性能优化（中）

2024年02月08日
浏览(58)
读高性能MySQL（第4版）笔记18_扩展MySQL

4.2.2.1. 增加更多应用节点可以扩展服务用户请求的客户端数 4.2.2.2. 最终会被单源数据库主机的能力所限制，该数据库主机将要负责响应所有的读取请求 4.2.2.3. 高CPU使用率意味着服务器正花费所有的时间处理查询 4.2.2.4. CPU的使用率越高，查询的延迟也会越长 6.9.1.1. 负载均

2024年02月08日
浏览(52)
读高性能MySQL（第4版）笔记03_监控

7.1.1.1. 200响应代码 7.1.2.1. 202已接受 10.3.2.1. 连接的线程数（threads_connected）很高，但运行的线程数（threads_running）仍然很低 10.3.3.1. 连接的线程数（threads_connected）和运行的线程数（threads_running）都处于高值并持续增加 10.5.1.1. 数据库工程师不断努力的目标之一

2024年02月12日
浏览(37)
读高性能MySQL（第4版）笔记01_MySQL架构（上）

1.2.2.1. 存储过程 1.2.2.2. 触发器 1.2.2.3. 视图 3.3.2.1. 共享锁（shared lock） 3.3.2.2. 资源上的读锁是共享的，或者说是相互不阻塞的 3.3.3.1. 排他锁（exclusive lock） 3.3.3.2. 写锁则是排他的，也就是说，一个写锁既会阻塞读锁也会阻塞其他的写锁 3.3.3.3. 只有这样才能确保在特定的

2024年02月13日
浏览(45)
读高性能MySQL（第4版）笔记02_MySQL架构（下）

2.6.4.1. 失败的事务可能导致不一致的结果，因为某些部分可以回滚，而其他部分不能回滚 5.1.1.1. 在表的.ibd文件中 5.1.1.2. 减少了I/O，非常高效 5.2.1.1. 分区定义 5.2.1.2. 表定义 5.2.1.3. 存储程序定义 5.2.1.4. 字符集 5.2.1.5. 排序信息 5.2.2.1. 每个表的.ibd和.frm文件被替换为已经

2024年02月12日
浏览(55)
读高性能MySQL（第4版）笔记17_复制（下）

1.3.1.1. 安全补丁 1.3.1.2. 内核更新 1.3.1.3. 一些配置选项更改后需要重新启动才能生效 1.3.2.1. 确定将哪个副本切换为新的源 1.3.2.1.1. 一个包含所有数据的副本 1.3.2.2. 检查延时，确保延时在秒级别 1.3.2.3. 通过设置super_read_only停止数据写入源服务器 1.3.2.4. 等待副本与目标完

2024年02月08日
浏览(47)
读高性能MySQL（第4版）笔记16_复制（上）

4.1.1.1. 通过记录所有在源端执行的数据变更语句来实现的 4.1.1.2. 简单且紧凑 4.1.1.3. 一条更新了大量数据的SQL语句，在二进制日志中可能仅仅需要几十字节存储 4.1.1.4. “不确定性”的SQL语句问题 4.1.1.4.1. 如果在源和副本上，记录的排序不同，这条SQL语句在源和副本上删除

2024年02月08日
浏览(40)
读高性能MySQL（第4版）笔记14_备份与恢复（中）

7.3.6.1. 消除了底层数据存储引擎的差异 7.3.7.1. 如果MySQL在内存中的数据还没有损坏，当不能得到一个正常的裸文件备份时，或许可以得到一个可以信赖的逻辑备份 7.4.1.1. 某些场景下比数据库文件本身更大 7.4.2.1. 浮点表示的问题、软件Bug等都会导致问题 7.4.3.1. MySQL中导出数

2024年02月08日
浏览(33)