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

善知者

1. 索引

1.1. 键（key）

1.2. 存储引擎用于快速找到记录的一种数据结构

1.3. 当表中的数据量越来越大时，索引对性能的影响愈发重要

1.4. 在数据量较小且负载较低时，缺少合适的索引对性能的影响可能还不明显

1.5. 索引优化是对查询性能优化最有效的手段

1.6. 索引能够轻易将查询性能提高几个数量级

1.7. “最优”的索引有时比一个“好的”索引性能要好两个数量级

1.8. 创建一个真正“最优”的索引经常需要重写查询

1.9. 可以包含一列或多列的值

1.10. 包含多列，那么列的顺序也十分重要

1.10.1. MySQL只能有效地使用索引的最左前缀列

1.11. 在精妙和复杂的索引面前，无论ORM工具多么精巧，都不要对其抱太大希望

1.12. 即使是查询优化技术专家也很难兼顾到各种情况，更别说ORM了

2. 索引的类型

2.1. 在MySQL中，索引是在存储引擎层而不是服务器层实现的

2.2. 不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引

2.3. 在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求

2.4. B-tree索引

2.4.1. 使用B-tree数据结构来存储数据

2.4.2. 意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同

2.4.2.1. 按照索引列中的数据大小顺序存储的

2.4.3. 适用于全键值、键值范围或键前缀查找

2.4.3.1. 键前缀查找只适用于根据最左前缀的查找

2.4.4. 能够加快数据访问的速度

2.4.4.1. 在查询某些条件的数据时，存储引擎不再需要进行全表扫描

2.4.4.2. 通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限

2.4.4.3. 最终存储引擎要么找到对应的值，要么该记录不存在

2.4.5. NDB集群存储引擎虽然依然使用了BTREE标识，但在其内部实际上使用了T-tree结构存储这种索引

2.4.6. InnoDB则使用的是B+tree

2.5. 自适应哈希索引

2.5.1. 当InnoDB发现某些索引值被非常频繁地被访问时，它会在原有的B-tree索引之上，在内存中再构建一个哈希索引

2.5.2. 让B-tree索引也具备了一些哈希索引的优势实现非常快速的哈希查找

2.5.3. 完全自动化的，用户无法进行控制或者配置

2.5.4. 可以通过参数彻底关闭自适应哈希索引这个特性

2.6. 全文索引

2.6.1. FULLTEXT

2.6.2. 查找的是文本中的关键词，而不是直接比较索引中的值

2.6.3. 全文索引和其他几类索引的匹配方式完全不一样

2.6.4. 全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配

2.6.5. 在相同的列上同时创建全文索引和基于值的B-tree索引并不会有冲突

2.6.6. 全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作

3. 索引优点

3.1. 可以让服务器快速地定位到表的指定位置

3.2. 索引大大减少了服务器需要扫描的数据量

3.3. 索引可以帮助服务器避免排序和临时表

3.4. 索引可以将随机I/O变为顺序I/O

4. 高性能的索引策略

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

4.2. 索引的选择性

4.2.1. 不重复的索引值（也称为基数，cardinality）和数据表的记录总数（＃T）的比值，范围从1/＃T到1之间

4.2.2. 索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行

4.2.3. 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

4.3. 前缀索引

4.3.1. 一种能使索引更小、更快的有效办法

4.3.2. 有时候为了提升索引的性能，同时也节省索引空间，可以只对字段的前一部分字符进行索引

4.3.3. 对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL并不支持对这些列的完整内容进行索引

4.3.4. 缺点

4.3.4.1. 会降低索引的选择性

4.3.4.2. MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描

4.3.5. 既要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）

4.3.6. 计算合适的前缀长度的办法就是计算完整列的选择性，并使前缀的选择性接近完整列的选择性

4.3.7. 只看平均选择性是不够的，还有例外的情况，需要考虑最坏情况下的选择性

4.3.8. 常见的场景是针对很长的十六进制唯一ID使用前缀索引

4.4. 多列索引

4.4.1. 一个常见的错误就是，为每列创建独立的索引，或者按照错误的顺序创建多列索引

4.4.2. 在多列上独立地创建多个单列索引，在大部分情况下并不能提高MySQL的查询性能

4.4.3. 用UNION改写查询，往往是最好的办法

4.5. 选择合适的索引列顺序

4.5.1. 正确的顺序依赖于使用该索引的查询语句

4.5.1.1. 还需要考虑如何更好地满足排序和分组操作的需要

4.5.2. 索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列

4.5.3. 索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求

4.5.4. 当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的

4.5.5. 经验法则考虑的是全局基数和选择性，而不是某个具体查询

4.5.6. 性能不只依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关

4.5.7. 经验法则和推论在多数情况下是有用的，但要注意，不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能

5. 聚簇索引

5.1. 并不是一种单独的索引类型，而是一种数据存储方式

5.2. InnoDB的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行

5.3. 聚簇表示数据行和相邻的键值紧凑地存储在一起

5.4. 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引

5.5. 如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替

5.6. 如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引

5.7. 优点

5.7.1. 以把相互关联的数据保存在一起

5.7.2. 数据访问更快

5.7.2.1. 从聚簇索引中获取数据通常比在非聚簇索引中查找要快

5.7.3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

5.7.4. 聚簇数据最大限度地提高了I/O密集型应用的性能

5.7.4.1. 如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了

5.8. 缺点

5.8.1. 插入速度严重依赖于插入顺序

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

5.8.2.1. 会强制InnoDB将每个被更新的行移动到新的位置

5.8.3. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂（page split）的问题

5.8.4. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

5.9. 最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用

5.10. 从性能的角度考虑，使用UUID作为聚簇索引会很糟糕

5.10.1. 主键字段更长

5.10.2. 占用的空间也更大

5.10.2.1. 页分裂和碎片

5.11. 对于高并发的工作负载，在InnoDB中按主键顺序插入可能会造成明显的写入竞争

5.11.1. 主键的上界会成为“热点”

5.11.2. 所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争

5.12. AUTO_INCREMENT锁机制

5.12.1. 可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置

6. 二级索引

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

6.2. 二级索引访问需要两次索引查找，而不是一次


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