mysql 索引类型
本文学习整理自《高性能MYSQL》
索引
索引(在mysql中也叫做键key)是存储引擎用于快速找到记录的一种数据结构。
索引的类型
mysql支持的索引类型
B-Tree索引
使用B-Tree数据结构来存储数据。大多数Mysql引擎都支持这种索引。Archive引擎是特例,5.1前不支持任何索引,5.1后才支持单个自增列(AUTO_INCREMENT)的索引。
实际上很多存储引擎使用的是B+Tree,即每一个叶子节点包括指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。
B+Tree上的索引的结构
BTree索引能加快数据访问的速度,因为存储引擎不需要再根据全表进行扫描获取数据,而是从索引的根节点(图中未画出)开始搜索。根节点的槽中存放了指向子节点的指针,通过比较节点的值(是子节点中值的上下限)和要查找的值进入下一层子节点。最终要么数据不存在,要么找到对应的记录。
叶子节点的指针指向的是被索引的数据,而不是其他的节点页。树的深度和表的大小直接相关。
CREATE TABLE Peaple(
last_name varchar(64) not null,
first_name varchar(64) not null,
birth date not null,
gender enum('m','f') not null,
key(last_name, first_name, birth)
);
索引是lastname,firstname,和birth
BTree索引适用于全键值,键值范围或键前缀查找(左前缀),对下面的查询有效
- 全值匹配:和索引中所有列匹配。比如查询lastname为aa,firstname为bb,和birth为cc的人
- 匹配最左前缀:只使用索引的第一列。比如查询lastname为aa
- 匹配范围值:模糊匹配索引第一列。比如查询lastname以A开头的人
- 精确匹配某一列并范围匹配另外一列:即第一列全匹配,第二列范围匹配。比如查询lastname为aa,firstname以B开头的人
- 只访问索引的查询:BTree通常可以支持“只访问索引的查询”,即查询只需要访问索引,而无需访问数据行。——覆盖索引(后面介绍)。
BTree索引失效情况:
- 不是从最左列开始查找,无法使用索引。比如想查询birth为cc的人,不走索引
- 不能跳过索引中的列,比如查询lastname为aa,和birth为cc的人,则birth的索引不生效
- 如果查询中有某列的范围查询,则右边的列的索引都失效。比如查询where last_name=‘aa’ and first_name like ‘%B’ and birth = ‘2020-01-01’,birth索引不生效。
哈希索引
Mysql中只有Memory引擎显式的支持哈希索引。这也是Memory引擎表的默认索引类型,其也支持BTree索引。
哈希索引的限制:(其实想想哈希表的原理就可以知道其受限制的原因)
- 无法排序,因为哈希索引数据不是按照索引值顺序存储
- 不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值
- 只支持等值比较,不支持任何范围查询
- 哈希冲突时,需要遍历链表
- 哈希冲突多的话,一些索引维护操作的代价也会很高。
InnoDB引擎有一个特殊功能叫“自适应哈希索引”。当InnoDB注意到某些索引值被引用的很频繁时,它会在内存上基于BTree索引在创建一个哈希索引。
创建自定义哈希索引
可以创建一个伪哈希索引,虽然还是用BTree来进行查找,但是它用的是哈希值而不是值本身进行索引查找。
例如要存储大量url,并需要根据url进行搜索查找。如果使用BTree来存储url,存储的内容会很大,因为url很长。
可以删除原理url列上的索引,新增一个被索引的url_crc列,使用CRC32做哈希。查询sql为
// 索引是url时
select id form url where url = 'http://www.mysql.com';
// 伪哈希索引url_crc
select id form url where url_crc = CRC32('http://www.mysql.com') and url = 'http://www.mysql.com';
但是需要维护url_crc值。可以手动维护,也可以使用触发器实现。
// 创建表
CREATE TABLE pseudohash (
id int unsigned not null auto_increment,
url varchar(255) not null,
url_crc int unsigned not null default 0,
PRIMARY KEY(ID)
);
// 创建触发器
// 设置语句结束符号为双斜杠//
DELIMITER //
CREATE TRIGGER crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW
BEGIN
SET NEW.url_crc=CRC32(NEW.url);
END;
//
CREATE TRIGGER crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW
BEGIN
SET NEW.url_crc=CRC32(NEW.url);
END;
//
DELIMITER ;
经过测试,触发器均可自动计算哈希值,不过当哈希冲突时,必须带上url的值进行查找才能精确匹配。
空间数据索引(R-Tree)
MyISAM表支持空间索引,可以作为地理数据存储,这类索引无需前缀查询,空间索引会从所有维度来索引数据。查询时,可以有效使用任意维度组合查询。MySQL中的GIS相关的函数如MBRCONTAINS等维护数据。但MySQL的GIS支持不太完善,一般使用PostgreSQL的PostGIS。
全文索引
查找的是文本中的关键词,而不是直接比较索引中的值。
索引优点
- 索引大大减少服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机I/O变为顺序I/O(随机IO就是读写的内容分散在磁盘的不同位置,需要来回查找所以效率低;顺序IO就是读写的内容集中存储在磁盘的一块,从前到后依次读取,免去了查找的过程,所以效率高。)
对于数据特大的表,建立索引和使用索引的代价随之增长,可能需要分区分表来存储数据。
如果数据量特别多,可以建立一个元数据信息表,用来查询需要用到的某些特性。(Infobright)
高性能索引策略
独立的列
查询中的列不是独立的(索引列不能是表达式的一部分,也不能是函数的参数),则MySQL不会使用索引
前缀索引和索引选择性
索引选择性:不重复的索引值(基数)和数据表的记录总数(#T)的比值,范围从1/#T到1之间。选择性越高查询效率越高。
前缀索引需要计算下选择性,通过比较取多长的前缀较为合适。
// 取city值的前3位,计算下不重复的数据占总数据的比例
select count(distinct LEFT(city,3))/count(*) from sakila.city_demo;
通过多次计算,选出合适的前缀索引的位数
// 创建前缀索引
ALTER TABLE sakila.city_demo ADD KEY (city(7));
缺点:无法使用order by 和 group by,也无法使用前缀索引做覆盖扫描
场景:
- 针对很长的十六进制唯一ID使用前缀索引。
- 后缀索引:找到某个域名的所有电子邮件地址,可以把字符串反转后存储,基于此键前缀索引,通过触发器维护索引。
多列索引
对每列都建立索引不一定是高效的。explain的extra列可以看到是否有索引合并。有索引合并可能意味着表上的索引建立的很糟糕:
- 当需要对多个索引做相交操作时(有多个and),意味着需要一个包含所有相关列的的多列索引,而不是多个独立的单列索引
- 当需要对多个索引做联合操作时(有多个or),通常需要耗费大量的CPU和内存资源在算法缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回的大量数据的时候
- 优化器不会把这些计算到“查询成本”中,这回导致查询的成本被低估。
选择合适的索引列顺序
根据计算选择性,选择性更高的作为索引的第一列。
聚簇索引
聚簇索引不是一种单独的索引类型,而是一种数据存储的方式。具体细节依赖于其实现方式,InnoDB的聚簇索引实际上在同一个结构中保存了BTree索引和数据行。
当表有聚簇索引时,数据行实际上存放在索引的叶子页中。一个表侄女有一个聚簇索引。
如下图,叶子也包含了行的全部数据,但节点页值包含了索引列
InnoDB通过主键聚集数据。如果没有定义主键,会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。
聚簇索引可能会对性能有帮助,也可能会导致严重的性能问题。尤其是将表的存储引擎从InnoDB改为其他引擎或者反过来切换时,需要评估聚簇索引。
优点:
- 可以把相关数据保存在一起。
- 数据访问更快
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点:
- 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,对于InnoDB表,我们一般都会定义一个自增的ID列为主键。如果不是按照主键顺序加载数据,那么加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
- 更新聚簇索引列的代价很高
- 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动时,可能有“页分裂”问题。当行的主键值要求必须将这一行插入到某个已满的页中,存储引擎会将该页分裂成两个页面来容纳该行,这是一次页分裂操作,会导致表占用更多磁盘空间
- 二级索引(非聚簇索引)可能比想象的更大,因为在二级索引(又叫辅助索引)的叶子节点宝行列引用行的主键列。访问需要两次索引查询,而不是一次。二级索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值去聚簇索引中查找对应的行。
InnoDB和MyISAM如何存放表的抽象图。InnoDB是聚簇索引,叶子节点包含整条数据的信息,二级索引存储的是主键值。MyISAM是非聚簇索引,无论是主键索引还是二级索引,都是指向行号。
按顺序的主键和按uuid随机生成主键相比,按顺序的主键插入的效率更高,索引也更小。但对于高并发工作负载,在InnoDB中按主键顺序插入可能会造成明星的争用,并发插入可能导致间隙锁竞争。
覆盖索引
覆盖索引:一个索引包含(或者说覆盖)所需要的查询的字段的值。
覆盖索引能极大地提高性能:
- 索引条目通常远小于数据行大小,如果只需要读取索引,那么Mysql就会极大减少数据访问量。
- 由于索引是按列值顺序存储的,对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少很多。
- 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值,所以二级主键能够覆盖查询,可以避免对主键索引的二次查询。
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列中的值,而哈希索引、空间索引和全文索引都不存储索引列的值。所以Mysql只能使用BTree索引做覆盖索引。
通过explain的extra列可以看到“Using index”的信息,代表是索引覆盖查询。
使用索引扫描做排序
如果explain的type列是“index”,说明mysql使用了索引扫描做排序。
- 当索引的列的顺序和order by子句的顺序完全一致,并且所有的列的排序放下都一样,mysql才能使用索引来对结果排序。
- 当查询需要关联多张表,只有当order by子句引用的字段全部为第一个表,此案用索引做排序
- order by子句需要满足索引的最左前缀要求。有一种特殊情况,是前导列为常量时,where或join中对这些列指定了常量。比如索引是(rental_date,inventory_id,customer_id),当查询语句为
select rental_id from sakila.rental where rental_date = '2005-05-26' order by inventory_id,customer_id;
压缩(前缀压缩)索引
MyISAM使用前缀压缩来减少索引大小,默认只压缩字符串。
对于CPU密集型应用,因为扫描需要随机查找,压缩索引使得MyISAM在索引查找上慢好几倍,倒序扫描就更慢了。压缩索引需要在CPU内存资源和磁盘之前做权衡。
冗余和重复索引
mysql允许在相同列上创建多个索引。
重复索引是指在相同的列上按照相同的顺序创建相同类型的索引,应避免这样创建重复索引,发现后立即移除。
冗余索引是比如创建了索引(A,B),再创建索引(A)就是冗余索引。
表中的索引越多,可能会提升查询的效率,但会导致insert,update,delete操作的速度变慢。
总结
在选择索引和编写利用索引的查询时,有三个原则:
- 单行访问是很慢的。如果服务器从存储中读取一个数据块只是为了获取其中一行,那么就浪费了很多工作。最好的读取的块中能包含尽可能多的所需要的的行,使用索引可以创建位置引用以提升效率
- 按顺序访问范围数据是很快的。
- 索引覆盖查询是很快的。如果一个索引包含了查询所需要的所有列,那么存储引擎就不需要再回表查找行。这样避免了大量的单行访问。