创建高性能索引

70 阅读21分钟

创建高性能索引

索引是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能,除此之外,本章还将讨论索引其他一些方面有用的属性。

索引基础

MySQL中的索引,类似与一本书的“索引”部分:如果想在一本书中找到某个特定主题,一般会先看书的“索引”,找到对应的页码。(MySQL存储引擎也是先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行)

索引的类型

B-Tree索引

以下面表为例:

create table A(
    `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '姓名',
    `age` int(10) not null default 0 COMMENT '年龄',
    PRIMARY KEY (`id`),
    KEY `idx_abc` (`name`,`age`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMENT = '索引测试表';

对于表中的每一行数据,索引中包含了name、age列的值,下图展示了索引是如何组织数据的存储的。

请注意:索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看一下最后两个条目,两个人名字都一样,则根据他们的年龄来排列顺序。

可以使用B+Tree索引的查询类型:B+Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

1676021419125.png

对于以下查询会导致索引失效:

select * from A where age = 12;

1666073884454.png

根据上图sql执行计划可知,查询语句未遵循索引最左匹配规则,会导致索引失效。

哈希索引

哈希索引基于哈希表实现,只有精准匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。

在MySQL中,只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型,Memory引擎同时也支持B+Tree索引。值得一提的是,Memory引擎是支持非唯一哈希索引的,这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中

假设有如下表:

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

假设有如下查询:

select lname from testhash where fname='Peter';

MySQL先计算'Peter'的哈希值,并使用该值寻找对应的记录指针。因为f('Peter')=8784,所以MySQL在索引中查找8784,可以找到指向第3行的指针,最后一步是比较第三行的值是否为'Peter',以确保就是要查找的行。

因为索引自身只需存储对应的哈希值,所以索引的结构十分紧凑,这也让哈希索引查找的速度非常快。然而,哈希索引也有它的限制:

  • 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取航。
  • 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。
  • 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
  • 哈希索引只支持等值比较查询,包括=、IN()、<=>(注意<>个<=>是不同的操作)。也不支持任何范围查询。例如where price > 100。
  • 访问哈希索引的数据非常快,除非有很多哈希冲突。当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行进行比较,知道找到所有符合条件的行。
  • 如果哈希冲突很多的话,一些索引维护操作的代价也会很高。例如,如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引,那么当从表中删除一行时,存储引擎要遍历对应哈希值的链表中的每一行,找到并删除对应行的引用,冲突越多,代价越大。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时,它会在内存中基于B+Tree索引之上再创建一个哈希索引,这样就让B+Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。

创建自定义哈希索引。

(总体思路是为某一列的哈希值创建索引,然后查询的时候使用该哈希索引列,并且带上索引前的值)

如果存储引擎不支持哈希索引,则可以模拟像InnoDB一样创建哈希索引,这可以享受一些哈希索引的便利,例如只需要很小的索引就可以为超长的键创建索引。

思路很简单:在B+Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事,因为还是使用B+Tree进行查找,但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的where子句中手动指定使用哈希函数。

例如:

有一个实例需要存储大量的URL,并需要根据URL进行搜索查找。如果使用B+Tree来存储URL,存储的内容就会很大,因为URL本身都很长。正常情况下会有如下查询:

select id from url where url = "http://www.mysql.com";

若删除原来URL列上的索引,而新增一个被索引的url_crc列,使用CRC32做哈希,就可以使用下面的方式查询:

select id from url where url="http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com")

这样做的性能会非常高,因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查询。即使有多个记录相同的索引值,查找仍然很快,只需要根据哈希值做快速的整数比较就能找到索引条目,然后一一比较返回对应的行。

这样实现的缺陷是需要维护哈希值。可以手动维护,也可以使用触发器实现。不建议使用SHA1()和MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串,会浪费大量空间,比较时也会更慢。SHA1()和MD5()是强加密函数,设计目标是最大限度消除冲突,但这里并不需要这样高的要求。简单哈希函数的冲突在一个可以接收的范围,同时又能够提供更好的性能。

如果数据表非常大,CRC32()会出现大量的哈希冲突,则可以考虑自己实现一个简单的64位哈希函数。这个自定义函数要返回整数,而不是字符串。一个简单的办法可以使用MD5()函数返回值的一部分来作为自定义哈希函数。如:select conv(right(MD5('http:/www.mysql.com/'),16),16,1…) as hash64;

处理哈希冲突。当使用哈希索引进行查询的时候,必须在where子句中包含常量值:select id from url where url_crc=CRC32("www.mysql.com") and url = "www.mysql.com";

全文索引

全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节,如停用词、词干和负数、布尔搜索等。全文索引更类似于搜索引擎做的事情,而不是简单的where条件匹配。

在相同的列上同时创建全文索引和基于值的B+Tree索引不会有冲突,全文索引适用于match against操作,而不是普通的where条件操作。

索引的优点

索引可以让服务器快速定位到表的指定位置。但是这并不是索引的唯一作用,到目前为止可以看到,根据创建索引的数据结构不同,索引也有一些其他的附加作用。

最常见的B+Tree索引,按照顺序存储数据,所以MySQL可以用来做order by和group by操作。因为数据是有序的,所以B+Tree也就会将相关的列值都存储在一起。最后因为索引中存储了实际的列值,所以某些查询只使用索引就能够完成全部查询。据此特性,总结下来索引有如下三个优点:

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

三星系统:索引将相关的记录放到一起则获得一星;如果索引中的数据顺序和查找中的排序顺序一致则获得二星;如果索引中的列包含了查询中需要的全部列则获得“三星”

高性能的索引策略

独立的列

指的是,查询中的索引列必须是独立的,不能在索引列上使用**“计算”、“函数”**

前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得很大且慢。通常有以下解决办法:

  • 自定义哈希索引(但有时候还是不够)

  • 前缀索引(索引列开始的部分字符,这样可以大大节约索引空间,从而提高索引效率)。但这样也会降低索引的选择性。

    • 索引的选择性:指的是不重复的索引值和数据表的记录总数的比值。通常索引选择性越好,性能也是最好。

    一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能。诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(一遍节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引整个列。换句话说,前缀的“基数”应该接近于完整列的“基数”。

    • **缺点:**前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有其缺点:MySQL无法使用前缀索引做order by和group by,也无法使用前缀索引做覆盖扫描。

    例子:一个场景是针对很长的十六进制唯一ID使用前缀索引。采用长度为8的前缀索引通常能显著地提升性能,并且这种方法对上次应用完全透明。

    创建前缀索引语句:
    alter table city add key (city(7));
    
  • 后缀索引:有时候后缀也有用途(例如:找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引,但是可以把字符串反转后存储,并基于此建立前缀索引。

多列索引

通常指的是联合索引,而不是为每个列创建独立的索引。联合索引列的顺序要创建对

选择合适的索引列顺序

最容易引起我们困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好地满足排序和分组的需要

对于如何选择索引的列顺序有一个经验法则:当不需要考虑排序和分组时,将选择性最高的列放到索引最前列,在某些场景可能有帮助,但通常不如避免随机IO和排序那么重要,考虑问题需要更全面(不同场景需要不同解决方案)

聚簇索引

聚簇索引:即按照每张表的主键构造一颗B+Tree,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分;

一般建表会自增主键做聚簇索引,没有的话,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键作为聚簇索引。

我们日常工作中,根据实际情况自己添加的索引都是辅助索引,辅助索引就是一个为了找主键索引的二级索引,先找到主键索引再通过主键索引找到数据

聚簇索引有以下特点:

  • 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。

  • InnoDB的聚簇索引实际上在同一个结构中【保存了B+Tree索引和数据行】。

  • 一个表中只能有一个聚簇索引(不过,覆盖索引可以模拟多个聚簇索引的情况)

  • 数据行实际上存放在索引的叶子页中。

1676293353845.png

  • 聚簇索引优点:
    • 相关数据保存在一起(减少I/O次数)
    • 数据访问更快(聚簇索引将索引和数据保存在同一个B+Tree叶子节点中,因此从聚簇索引汇总获取数据通常比在非聚簇索引中查找要快,我理解的是减少回表操作
    • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
  • 聚簇索引缺点:
    • 聚簇索引最大限度提高了I/O密集型应用的性能,但如果数据去u俺不都放在内存中,则访问的顺序就没那么重要了,聚簇索引也就没什么优势了。
    • 插入速度严重依赖于插入顺序。(按照主键插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据,那么在加载完后最好使用optimize table命令重新组织一下表)
    • 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题。(插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作。)
    • 聚餐索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
  • 注意:强烈建议指定的主键索引应该为AUTO_INCREMENT自增列,这样可以保证数据行是按顺序写入,对于主键做关联操作的性能也会更好。最好避免随机的(不连续且值的分布范围非常大)的聚簇索引,特别是I/O密集型的应用。例如使用uuid作为聚簇索引则会很糟糕:它使得聚簇索引的插入变得完全随机,这是最坏的情况,使得数据没有任何聚集特性。
  • 使用随机的聚簇索引缺点
    • **导致大量的随机I/O。**写入的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被加载到缓存中,InnoDB在插入之前不得不先找到并从磁盘读取目标到内存中。
    • 写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便为新的行分配空间。
    • **数据会有很多碎片。**由于频繁地页分裂,页会变得稀疏并被不规则地填充。

覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具,**能够极大地提高性能,无需回表,**具有以下优点:

  • 索引条目通常远小于数据行大小,所以如果只需要读取索引,那MySQL就会极大地减少数据访问量。(对缓存负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上;对I/O密集型的应用也有帮助,因为索引比数据更小,更容易全部放入内存中)
  • 因为索引是按照列值顺序存储的(至少在单个页内是如此),所以对I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。
  • **一些存储引擎如MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。**这可能会导致严重的性能问题,尤其是那些系统调用占了数据访问中的最大开销的场景。
  • 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免回表操作。

1676363743541.png

例子,优化前的索引及查询:

select * from products where actor = 'SEAN CARREY' and title like '%APOLLO%';
索引列为actor和title

以上索引无法覆盖该查询,原因如下:

  • 没有任何索引能够覆盖这个查询。
  • MySQL不能再索引中做最左前缀匹配的like比较。

解决以上问题办法:

修改索引列(actor,title,prod_id)
修改查询语句:
select * from products
	join (
    	select prod_id
        from products
        where actor = 'SEAN CARREY' and title like '%APOLLO%'
    ) as t1 on (t1.prod_id = products.prod_id);

**我们把这种方式叫做延迟关联,因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引,在FROM子句的子查询中找到匹配的prod_id,然后根据这些prod_id值在外层查询匹配获取需要的所有列。**虽然无法使用索引覆盖整个查询,但总比完全无法利用覆盖索引的好。

1676366282703.png

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果:通过排序操作;或者按索引顺序扫描;如果explain出来的type列的值为“index”,则说明MySQL使用了索引扫描来做排序(不是Extra列的“Using index”)

扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条记录就都回表查询一次对应的行。这基本上都是随机I/O,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢,尤其是在I/O密集型的工作负载时。

只有当索引的列顺序和order by子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一个表时,才能使用索引做排序order by子句和查找型查询的限制是一样的需要满足索引的最左前缀的要求;否则,MySQL都需要执行排序操作,而无法利用索引排序。

有一种情况下order by子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。

如下语句:

索引:(rental_date,inventory_id,customer_id)
查询语句:select rental_id,staff_id from sakila.rental where rental_date = '2005-05-25' order by inventory_id,customer_id;

上面语句即使order by子句不满足索引的最左前缀的要求,也可以用于查询排序,这是因为所有的第一列被指定为一个常数。

压缩(前缀压缩)索引

冗余和重复索引

注意避免冗余和重复索引,如创建了(A,B),再创建(A)就是冗余索引,

未使用得索引

索引和锁

索引可以让查询锁定更少的行。锁定更少的行有两个好处:①锁定更少的行意味者更小的开销;②锁定超过需要的行会增加锁争用并减少并发性。

**InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行,那么在InnoDB检索到数据并返回给服务器层以后,MySQL服务器才能应用where子句。**这时已经无法避免锁定行了:InnoDB已经锁住了这些行,到适当时候才释放。在MySQL5.1和更新的版本中,InnoDB可以在服务器过滤掉行后就释放锁,但是在早期的MySQL版本中,InnoDB只有在事务提交后才能释放锁。

例子:

set autocommit=0;
begin;
select actor_id from sakila.actor where actor_id < 5 and actor_id <> 1 for update;

这条查询仅仅会返回24之间的行,但是实际上获取了14之间的行的排它锁。

InnoDB会锁住第1行,这是因为MySQL为该查询选择的执行计划是索引范围扫描:

1676377446212.png

换句话说,底层存储引擎的操作是”从索引的开头开始获取满足条件actor_id < 5的记录“,服务器并没有告诉InnoDB可以过滤第1行的where条件。注意到explain的Extra列出现了”Using where“,这表示MySQL服务器将存储引擎返回行以后再应用where过滤条件。

下面第二个查询就能证明第1行确实已经被锁定,尽管第一个查询的结果中并没有这个第1行。保持第一个连接打开,然后开启第二个连接并执行如下查询:

set autocommit=0;
begin;
select actor_id from sakila.actor where actor_id = 1 for update;

这个查询将会挂起,知道第一个事物释放第1行的锁。

就像这个例子显示的,即使使用了索引,InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话问题可能会更糟糕,MySQL会做全表扫描并锁住所有的行,而不管是不是需要。

索引案例学习

支持多种过滤条件

主要讲述的是,例如sex列,如果很多查询中用到,还是考虑在创建不同组合索引的时候将sex带上。这里有以下两个理由:

  • 第一点,几乎所有的查询都会用到sex列。
  • 第二点,如果查询不限制性别,那么可以通过在查询条件中新增AND sex in('m','f')来让MySQL选择该索引。这样写并不会过滤任何行,和没有这个条件时返回的结果相同。但是必须加上这个列的条件,MySQL才能够 匹配索引的最左前缀。但是如果In()列表太长,这样就不行了

优化索引和优化查询要平衡

如果想尽可能重用索引而不是建立大量的组合索引,可以使用前面提到的in()技巧来避免同时需要(sex,country,age)、(sex,country,region,age)和(sex,country,region,city,age)的索引

避免多个范围查询

explain的输出很难区分范围查询**,between和列表值查询in(),因为explain中的type都是range**,访问效率不同,前者索引不能用了,后者却可以。

返回查询列放在后面,因为会导致返回列后面的索引列失效

优化排序

深度分页优化:

SELECCT FROM  profiles WHERE sex=’M’ ORDER BY rating limit 100000,10;

随着偏移量的增加,MySQL需要花费大量的时间来扫描需要丢弃的数据。优化这类索引的另一个比较好的策略是使用延迟关联,通过使用覆盖索引查询返回需要的主键,再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢弃的行数。优化后的语句如下:

SELECT FROM profiles INNER JOIN (

               SELECT id FROM profiles

               WHERE x.sex = ‘M’ ORDER BY rating 10000,10

    ) AS x on (x.id = profiles.id);