《高性能MySQL》夯实之路(三) 创建高性能的索引

《高性能MySQL》夯实之路(三) 创建高性能的索引

---

---

---

一.索引基础

1. 索引的类型：

• B-Tree索引
  1. InnoDB使用的实际是B+Tree，按原数据格式存储
  2. MyISAM使用前缀压缩技术使得索引更小
  3. 最左列原则
  4. 不仅可用于精准匹配查询，范围查询，还可以用于ORDER BY排序
  5. 限制与规则：
     • 必须从最左开始
     • 不能跳过中间的列
     • 如果索引查询中有一个列使用了范围查询，那么右边的列无法使用索引查询
• 哈希索引
  1. 所有的索引列都会计算出一个哈希码值，作为key
  2. Value为指向该索引所属行的指针
  3. 会发生哈希碰撞，生成链表
  4. 按照哈希码值排序，而不是按照索引值排序，所以无法应用于排序
  5. 速度很快，除非哈希冲突太多导致链表过长
  6. 适用于Memory引擎，也适用于InnoDB的自适应哈希(某些使用频率高的索引值)
• 空间数据索引·R-Tree·
  1. 会使用所有维度来查询
  2. 会使用GIS相关函数
• 全文索引：查找全文中的关键词

---

二. 索引的优点

• 大大减少服务器需要扫面的数据量
• 帮助服务器避免排序和临时表
• 将随机IO变成顺序IO

---

三.高性能的索引策略

1. 独立的列：索引不能是表达式的一部分，如actor_id + 1 = 5;

2. 前缀索引和索引的选择性

• 太长的数据，如BLOB，TEXT无法使用完整的数据作为索引，所以只能使用前缀索引
• 索引的选择性指：不重复的索引值(基数)/数据表的记录总数，选择性越高，查询效率越高。
• 使用前缀索引时，必须保持较高的选择性。

3. 多列索引

• 在explain中如果有索引合并Union或相交Intersection，应当优先考虑使用多列索引

4. 选择合适的索引顺序

• 不需要考虑排序或分组时，通常将选择性最高(基数/总数)的列放在最左

5. 聚簇索引

• 是一种数据存储方式，也叫做聚集索引
• 有聚簇索引时，叶子页同时存放索引和数据行。因为无法把数据行放在两个地方，所以一个表只能有一个聚簇索引
• 移动行时，会导致“页分裂”
• 使用二级索引需要回表
• MyISAM主键索引和二级索引结构上无区别，都是指向行地址
• InnoDB主键索引存储数据，为聚簇，二级没数据。如果使用UUID作为聚簇索引，会使得插入变得随机，而不是自增，查找也变成随机查找。

6. 覆盖索引

• 一个索引的叶子节点包含了所需要查询的数据，这样被称为覆盖索引

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

• 索引的列顺序与ORDER BY顺序一致时
• ORDER BY子句需要也满足最左前缀原则
• 当前导列为常量的时候，可以不满足最左原则

8. 压缩索引

• MyISAM通过前缀压缩来减少索引大小
• 完整保留第一个索引块，其他索引块只保留第一个剩下的后缀部分，如第一个为perform，第二个原为performance，但压缩后变为(7,ance)

9. 冗余和重复索引

• 重复索引应该避免
• 冗余索引有所不同，如果有索引(A,B)了，再创建A就是冗余索引，因为是前一个的最左前缀，但再创建(B,A)或B则不是。

---