高性能MySQL-创建高性能索引

什么是索引

MySQL并没有统一的索引标准，不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

索引是存储引擎用于快速找到记录的一种数据结构，为数据库检索提供良好的性能。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。

索引的优点

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

索引的类型

索引的实现多种多样，在这里我们从数据结构的角度进行分类介绍下常见的两种索引类型。

B-Tree索引

书中使用“B-Tree”是因为 MySQL 在一些语句中也使用该关键字。不过底层的存储引擎也可能使用不同的存储结构，比如 InnoDB 使用的是 B+Tree。

实现原理

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) );

主要支持的查询方式

• 全值匹配
• 匹配最左前缀
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询

B-Tree 的限制

• 如果不是按照索引的最左列开始查找的，则无法使用索引
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找