Fork me on GitHub
王翔的技术博客

高性能MySQL 第五章 创建高性能的索引

索引是存储引擎快速找到记录的一种数据结构。

索引对于良好的性能非常关键,应该是对查询性能优化最有效的手段了。

索引基础

索引类似于一本书的目录,需要找到某个内容,在目录中找到对应的页数,翻到该页即可。

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

比如下面的查询:

1
SELECT * FROM ACTOR WHERE ACTOR_ID = 5;

如果在 ACTOR_ID 列上有索引,则MySQL将使用该索引找到 ACTOR_ID 为5的行。

索引可以包含一个或多个列的值。

如果索引包含多个列,那么列的顺序十分重要。因为MySQL只能高效的使用索引的最左前缀列。

索引的方式

索引有很多种方式,可以为不同的场景提供更好的性能。

在MySQL中,索引是在存储引擎层而不是服务器层实现的。并没有统一的索引标准。

创建索引

1
2
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type]
on tbl_nam(index_col_name,……)

删除索引

1
DROP INDEX index_name ON tbl_name

其中,创建索引时 INDEX 前面的就是索引类型,USING 后面就是指定索引方式

PS: 这里的索引类型与索引方式的概念与《高性能MySQL》中的不太一样,高性能MySQL中将BTree索引、Hash索引、全文索引都归入索引类型,我觉得不好理解。

B-Tree索引

一般谈论索引时,如果没有特别指明类型,那多半说的就是 B-Tree 索引,它使用 B-Tree 数据结构存储数据。

B-Tree索引

B-Tree索引能够加快访问数据的速度,因为存储引擎不需要全表扫描获取数据。

B-Tree对索引列是顺序存储的,所以很适合查找范围数据

可以使用B-Tree索引的查询类型

查询类型 说明
全值匹配 指的是和索引中的所有列进行匹配
匹配最左前缀 只使用多列索引中的第一列
匹配列前缀 匹配第一列的开头部分
匹配范围值 匹配第一列的某个范围
精确匹配某一列并范围匹配另一列 第一列相等,第二列范围匹配
只访问索引的查询 查询只需要访问索引,无需访问数据行

因为索引树中的节点是有序的,除了按值查找之外,索引还可用于查询中的ORDER BY操作。

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

  1. 如果不是从(多列)索引的最左列开始查找,则无法使用索引。

  2. 不能跳过索引中的列,不能查找符合第一列和第三列的数据。如果不指名第二列,则只能使用索引的第一列。

3.如果查询中有某个列的范围查找,那么其右边的所有列都无法使用索引优化查找。

由上可知,多列索引中,索引列的顺序是多么的重要。

Hash索引

哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。

在MySQL存储引擎中,只有Memmory引擎支持哈希索引,这也是Memmory引擎默认的索引方式,Memmory也支持B-Tree索引。

优点:查询速度很快。

缺点:

1.哈希索引只包含哈希值和行指针,不存储字段值,不能使用索引中的值避免读取行。

2.哈希索引数据不是顺序存储,无法用于排序。

3.哈希索引不支持部分索引匹配查找,因为哈希索引使用全部列计算哈希值。

4.哈希索引只支持等值比较查询,=、IN、<=>,也不支持任何范围查询。

5.当存在很多哈希冲突时,查询效率会低。

6.当存在很多哈希冲突时,一些索引维护操作的代价也会很高。

RTree索引 (空间数据索引)

MyISAM表支持空间索引,可以用作地理数据存储。

索引的类型

PRIMAR索引

创建表的主键时自动创建,是一种UNIQUE索引。

NORMAL索引

默认的索引类型。

UNIQUE索引

唯一索引,不可重复。

FULLTEXT索引

全文搜索的索引,一般用于大段的文章。一两行的数据,还是适合用默认索引。

SPATIAL索引

空间索引,建立在空间数据类型(如point和genomtry等)列上的索引。

多个单列索引 VS 多列索引:

多个单列索引与单个多列索引的查询效果不同,因为:

执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引


索引的优点

1.索引大大减少了服务器需要扫描的数据量。

2.索引可以帮助服务器避免排序和临时表

3.索引可以将随机IO变成顺序IO


高性能的索引策略

独立的列

前缀索引和索引选择性

多列索引

选择合适的索引列顺序

聚簇索引

覆盖索引

使用索引扫描来做排序

压缩(前缀压缩)索引

冗余和重复索引

未使用的索引

索引和锁


索引案例学习


维护索引和表


坚持原创技术分享,您的支持将鼓励我继续创作!