高性能mysql笔记——第五章

创建高性能的索引

索引(在MySQL中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构。

索引基础

在MySQL中,存储引擎先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。

索引的类型

  • B-Tree索引(InnoDB使用的是B+ Tree)

    存储引擎以不同方式使用B-Tree索引,性能也各有不同。例如,myisam使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储。再如myisam索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行。

    B-Tree通常意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同。

  • 哈希索引

  • 空间数据索引(R-Tree)

  • 全文索引

索引的优点

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

高性能的索引策略

独立的列

索引列不能是表达式的一部分,也不能是函数的参数。比如:

select id from user where id + 1 = 5

前缀索引和索引选择性

多列索引

选择合适的索引列顺序

  • 将选择性最高的列放到索引最前列?

    当不需要考虑排序和分组时,将选择性最高的列放在前面通常是好的。这时候索引只用于优化where条件的查找。

聚簇索引

数据行实际上存放在索引的叶子页中,InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC(多版本控制)的回滚指针以及所有的剩余列。如果主键是一个列前缀索引,InnoDB也会包含完整的主键列和剩下的其他列。

InnoDB二级索引的叶子节点中存储的不是“行指针”,而是主键值,并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间,换来的好处是,InnoDB在移动行时无须更新二级索引中的这个“指针”。