创建高性能索引

ambitioncoder
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);
avatar
文章
阅读
粉丝