MySQL索引

创建高性能MySQL索引

工作半年有余，越来越觉得基础知识的重要性。今年上半年给自己立个flag：读完《高性能MySQL》和《DDIA》两本书。当然，读完并不是主要目的，还是要有所想有所思的。趁着今天读完索引这一章节，总结下。

MySQL索引有哪些？他们的优缺点又有哪些呢？

B树索引

B树的B代表的含义是Balance（平衡）而非Binary（二分），它是由二叉树 —> 平衡二叉树 —> 平衡多叉树 —> B树不断进化而来，B树适合于磁盘查询等外部操作，多叉树查找降低了B树的高度，大多数数据在 2-4层的高度就可以搞定，需要注意的是，B树查找的是页的数据，而不是具体某一行的数据。 B树叶子节点采用链表数据结构将页与页之间链接起来，方便了数据的范围查询。

怎样的查询可以使用到B树索引呢？我们以一张表来做例子，一探究竟，表结构定义如下：

create table `student` (
    `id` int(11) not null default 0 comment '学号',
    `name` varchar(20) not null default '' '姓名',
    `grade` tinyint not null default 0 comment '年级',
    `class` tinyint not null default 0 comment '班级',
    primary key (`id`),
    key ix_name(`name`),
    key ix_grage_class_name(`grade`, `class`, `name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='学生表'

• 等值匹配

select * from student where id = 1

select * from student where name = 'popo'

select * from student where grade = 1 and class = 1 and name = 'popo'

• 最左前缀匹配

select * from student where grade = 1

select * from student where grade = 1 and class = 1

select * from student where grade = 1 and class = 1 and name like '%p%'

• 列前缀匹配（后面的列不可以用索引了）

select * from student where name like 'pop%'

• 范围匹配查询（后面的列就不支持索引了）

select * from student where grade > 5

• 只访问索引的查询，基本就是覆盖索引了，不需要访问数据行

select name from student where grade = 1 and class = 1

• 排序

select name from student where grade = 1 and class = 1 order by name

上面介绍的基本覆盖了B树的使用范围。那么B树索引的限制又在哪呢？

例如：

select * from student where class = 1 and name = 'popo'

还有就是不能跳过联合索引的某一列

select * from student where grade = 3 and name = 'popo'

查询中某列带有范围查询的话，该列右面的所有列都无法使用索引了。

select * from student where grade = 2 and class > 2 and name = 'popo'

class列后的name列不支持索引查找。

Hash索引

Hash索引基本和我们使用的编程语言中数据结构——map一样，基于Hash表实现，采用链地址法解决Hash冲突。每一行数据，Hash索引都会计算索引列的哈希码，将哈希码和其所对应的每行数据的指针存到hash表中。在MySQL中，只有Memory支持哈希索引，已《高性能MySQL》中为例：

create table testhash (
    fname varchar(50) not null,
    lname varchar(50) not null,
    key using hash(fname)
)engine=memory;

表中的数据如下：

fname	lname
arjen	lentz
baron	schwarz
peter	zaitsev
vadim	tkachenke

每个索引返回的值假设如下：

f('arjen') = 2323
f('baron') = 7437
f('peter') = 8784
f('vadim') = 2458

那么hash索引的数据e结果如下：

槽（slot）	值（value）
2323	指向第1行指针
2458	指向第4行指针
8784	指向第3行指针
7437	指向第2行指针

hash索引查询速度非常快，当需要查询某个fname的行数据的时候，仅需要计算该值对应的hash码，并从hash表中拿到行指针就可以了。

hash索引虽快，可不要贪杯哦，它的限制还挺多的：

• 从它的存储结构上看，hash表中仅仅存储了hash值和对应的航结构，那么每次查询时都需要无法避免的要读取整行数据；
• 不支持排序；
• 不支持范围查询；
• 不支持部分索引列查询。

上面hash索引的限制直接导致hash索引只适用于一些特殊的场景。

自适应hash索引，在某些场景下（经常查询某索引列的情况，索引值使用非常频繁），MySQL会在内存中B树索引基础之上建立起自适应Hash，加快查询速度，我们是没办法控制的（逃）。

创建自定义hash索引，MySQL中只有Memory支持Hash索引，那如果其他存储引擎想使用Hash索引，则可以在表里新建一个
存在列的Hash列。

create table `student` (
    `id` int not null,
    `name` varchar(50) not null,
    `hash_name_val` int not null,
    index ix_hash_name_val (`hash_name_val`)
)engine=innodb;

hash_name列中存储了name的hash值，在查询name='popo’的id时，可以这样查询，假设hasn(‘popo’) = 4632746327，加name的查询条件是避免hash冲突查询出多个值.

select id from student where hash_name_val = 4632746327 and name = 'popo'

高性能的索引策略

Where查询语句中不可以出现表达式

如果在sql查询时，where查询条件中的列不是独立（出现表达式）的列的话，该索引会失效，假设score在student表中是一个表示学生成绩的索引列，如果是以如下方式查询时，则不会走索引：

select * from student where score + 10 < 100.

前缀索引和索引选择性

当一个很长字符串列需要索引时，这个索引通常会很占空间，而且查询速度会比较慢。我们通常需要采用前缀索引的方式进行索引，这种索引方式是选择字符串的前n个字符作为索引，这样可以节省索引空间，加快查询速度。

如何选择前n个字符呢？这前n个字符是否可以降低索引的选择性，提升查询效率呢？索引选择性指的是，不重复的索引值占所有数据的比值，索引选择性越大，代表索引构建的越好。有两种方法可以进行选择

• 选择最常见的值和索引前缀一一对比，观察n的哪一个取值和常见值相似；

select count(*) as c, city from city group by city 

select count(*) as c, left(city, 3) as city group by city

• 计算列的完整度和索引前缀的完整度，观察n的哪一个取值和列的完整度相近.

select count(distinct city) / count(*) from city

select count(distinct left(city, 3)) / count(*) from city

前缀索引会让索引空间变得更小，但是MySQL无法利用前缀索引进行Order By和Group By操作，也没办法使用覆盖索引。

多列索引

多列索引的顺序至关重要，一个通用的经典法则：将选择性最高的列放在前面。索引适用于最左匹配原则。

聚簇索引

聚簇索引是一种将行数据和相邻的键值放在一起的索引，由于一份表中只有一份数据，索引一个表中只有一个聚簇索引。由于是存储引擎负责索引的，所以不同的存储引擎的聚簇索引实现不太一样。

InnoDB和MyISAM索引对比

MyISAM存储引擎引擎中，数据文件和索引文件是分离的，不论是主键索引，还是普通索引，其叶子节点指向的嗾使数据文件的行号指针。

InnoDB支持索引文件和数据文件是放在一起的，当索引是主键索引时，其叶子节点存储的是具体的行信息（包括主键值、事务ID、MVCC回滚指针以及剩余列），当索引时普通索引时，其叶子节点存储的是主键索引。所以，当进行普通索引查询时，通常要先查一次普通索引查出主键，再在主键索引中查出具体值得信息。需要进行2次查询。

在InnoDB表中插入数据需要注意的是：主键顺序保持递增，最简单的方法是采用AUTO_INCREMENT，最好避免随机插入。主要是因为:

• 顺序插入每条记录都存储在上一条记录之后，当页达到最大空间时，新的记录会分配到下一个新的页。
• 随机插入通常需要查询要插入的记录需要存储在那个页，该页可能已刷到磁盘或者未读入缓存，需要再次从磁盘中读取，导致大量随机io；
• 频繁的做页分裂操作，数据总会有碎片。

一般情况下，主键会作为聚簇索引，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB内部会隐式定义一个主键来作为主键。

覆盖索引

在上述的聚簇索引中我们了解到，普通索引叶子节点存储了主键值，一般情况下回导致二次查询索引。但是如果在查找普通索引时，该索引已经包含了我们所要查找的全部数据，那我们就不需要在进行二次查询了，这就是覆盖索引。

当发起一个被索引覆盖的查询时，在EXPLAIN的EXTRA列可以看到USING INDEX的信息

索引无法覆盖的两个可能原因：

1. 在查询中查出所有的列数据，没有哪一个索引覆盖了所有的列；
2. 在查询中执行LIKE操作

利用索引扫描做排序

MySQL有两种方式可以生成有序的结果：通过排序操作和按照索引顺序扫描。如果EXPLAIN出来的type列的值为"index",那么说明了MySQL使用了索引扫描来排序。

只有当索引的列顺序和Order by字句的顺序完全一样，并且所有列的排序方向都一样（要么都是ASC，要么都是DESC），MySQL才能使用索引进行排序。Order By子句和查找型查询的限制是一样的：需要满足索引的最左匹配要求。有一种情况可以不用满足：前导列是常量的时候。

以下列举一些可以使用到索引排序和不可以使用到的排序。表结构如下：

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL DEFAULT '',
  `sex` tinyint(4) NOT NULL DEFAULT 0,
  `age` tinyint(4) NOT NULL DEFAULT 0,
  `college` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `name_sex_age_idx` (`name`,`sex`,`age`),
  KEY `sex_idx` (`sex`),
  KEY `age_idx` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

可以使用到索引排序：

## 前导列是常量的时候
SELECT * FROM students WHERE name = 'popo' ORDER BY sex, age.


SELECT * FROM studnet ORDER BY name, sex, age

用不到的地方：

1. 两种不同排序方向

SELECT * FROM students WHERE name = 'popo' ORDER BY sex desc, age asc.

2. 不在索引中的列

SELECT * FROM students WHERE name = 'wen' ORDER BY sex, college ;

3. 无法组成最左前缀匹配

SELECT * FROM students WHERE name = 'wen' ORDER BY age ;

4. 查询在索引第一列上是范围条件

explain SELECT * FROM students WHERE name > 'popo' ORDER BY sex, age;

参考

1. 《高性能MySQL》
2. 《MySQL技术内幕：InnoDB存储引擎》