创建高性能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的信息
索引无法覆盖的两个可能原因:
- 在查询中查出所有的列数据,没有哪一个索引覆盖了所有的列;
- 在查询中执行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
用不到的地方:
- 两种不同排序方向
SELECT * FROM students WHERE name = 'popo' ORDER BY sex desc, age asc.
- 不在索引中的列
SELECT * FROM students WHERE name = 'wen' ORDER BY sex, college ;
- 无法组成最左前缀匹配
SELECT * FROM students WHERE name = 'wen' ORDER BY age ;
- 查询在索引第一列上是范围条件
explain SELECT * FROM students WHERE name > 'popo' ORDER BY sex, age;
参考
- 《高性能MySQL》
- 《MySQL技术内幕:InnoDB存储引擎》