在阅读«高性能MySQL»时，开始就被灌输了一个索引很重要的概念。最近在读第五章，“创建高性能的索引”，用于记录。

索引基础

理解索引最简单的办法就查字典，小时候查汉语大词典就是最典型的索引思想。通过索引可以将查询的效率提高几个数量级。

类型

B-Tree索引

在讨论数据库索引时，如果没有特别指明类型，多半是B-Tree索引，准确来说是B+Tree索引。 (B-Tree树和B+Tree树区别，参考如下 b树和b+树区别 )

B-Tree索引通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。B-Tree索引能够加快访问数据的速度，存储引擎不需要进行全盘扫描来获取需要的数据，仅仅从索引的根结点开始进行搜索即可。

B-Tree对索引列是顺序组织存储的，很适合查找范围数据。例如在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找很方便。

B-Tree索引适用于全键值，键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找。

考虑如下表

create table People(
    last_name varchar(50) not null,
    first_name varchar(50) not null,
    dob date not null,
    gender enum('m', 'f') not null,
    key(last_name, first_name, dob)
)

索引包含了last_name, first_name, dob

  • 全职匹配 查找(Cuba Allen, 1960-01-01)的人
  • 匹配最左前缀 查找姓为Allen的人
  • 匹配列前缀 查找所有以J开头的姓的人
  • 匹配范围值 查找姓在Allen和Barrymore之间的人
  • 精确匹配某列并范围匹配另一列 查找姓为Allen并且名字是字母K开头的人
  • 只访问索引的查询

下面是B-Tree索引的限制。

  • 不从索引的最左列查找，则无法适用索引。
  • 不能跳过索引中的列。
  • 如果查询有某个列的范围查询，则右边所有列无法适用索引优化查询。 例如 where last_name = ‘Smith’ AND first_name LIKE ‘%J’ AND dob = ‘1976-12-23’ 查询只能适用索引的前两列。

哈希索引

基于哈希表实现，优点是查找非常快。

在 MySQL 中只有 Memory 引擎显式支持哈希索引。

InnoDB 引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B-Tree 索引之上再创建一个哈希索引，这样就让 B-Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

限制：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能影响并不明显；无法用于分组与排序；只支持精确查找，无法用于部分查找和范围查找；如果哈希冲突很多，查找速度会变得很慢。

空间树索引

MyISAM表支持空间索引，可以用作地理数据存储，这类索引无须前缀查询。

全文索引

一种特殊类型的索引，查找文本中的关键字，而不是直接蹩脚索引中的值。

索引的优点

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

高性能的索引

独立的列

select actor_id from sakila.actor where actor.id + 1 = 5 
//MySQL无法自动解析这个方程式，所以无法使用索引。应该将索引列单独的放在比较符号的一侧

前缀索引和索引选择性

索引的选择性: 不重复的索引值(基数)和数据表的记录总数(#T)的比值，范围从1/#T 到 1之间，选择性越高则查询效率越高。

选择足够昌的前缀以保证较高的选择性，同时又不能太长，前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

多列索引

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

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫做"索引合并"的策略，一定程度上可以适用表上的多个单列索引来定位制定的行。

选择合适的索引列顺序

正确的索引列顺序依赖于适用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。

在一个多列B-Tree索引中，索引列的顺序意味者索引首先按照最左列进行排序。 让选择性最强的索引列放在前面，例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

聚簇索引

聚簇索引并不是一种单独的索引类型，二十一种数据存储方式。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中，因为无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚簇索引。如下

叶子页包含了行的全部数据，但是节点页只包含了索引列，这里索引的是整数值。

优点:

  • 把相关数据保存在一起
  • 数据访问更快
  • 适用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点:

  • 聚簇数据最大限度提高了I/O密集型应用性能，但如果数据全都放在内存中，访问顺序则不重要，聚簇索引也就失去了优势。
  • 插入速度严重依赖于插入顺序
  • 更新聚簇索引列的代价很高
  • 导致全表扫描变慢

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为"覆盖索引"。

使用索引扫描来排序

MySQL又两种方式生成有序的结果: 1.通过排序操作 2.按索引顺序扫描。

压缩索引

MyISAM适用前缀索引来减少索引的大小，从而让更多的索引可以放入内存中。默认只压缩字符串，通过参数设置可以对整数做出压缩。

冗余和重复索引

MySQL允许在相同列上创建多个索引，MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个的进行考虑，这会影响性能。

未适用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，这样的索引建议删除。