运维首页 > 数据运维 > MySQL索引优化策略

MySQL索引优化策略

2024年 3月 8日 25.0k 0

这篇文章我们继续来聊聊MySQL索引，有关索引优化策略方面的问题，有兴趣的小伙伴可以点击链接看我的上一篇文章~

MySQL索引：B+树原理揭秘与索引优缺点分析

一、索引优化策略

1、联合索引

在我们实际工作中，最容易让我们引起困惑的便是在建立联合索引时，索引字段的顺序问题，正确的字段顺序依赖于使用该索引的查询，并且需要同时考虑如何才能够更好地满足排序和分组的需求。

我们知道，在联合索引的B+树中，字段按照最左列往右进行排序，即从第一列开始排序，在第一列相同的情况下，按照第二列进行排序，如此往复。索引可以按照升序或者降序进行扫描，以满足符合列顺序的ORDER BY、GROUP BY以及DISTINCT等子句查询。因此，在实际业务中，联合索引的列顺序对于性能方面来说至关重要。

对于联合索引列顺序的选择有一个经验法则：将选择性最高的列放在联合索引最前列。简单来说，就是能够最快地过滤出需要的行的查询字段放在联合索引的最前列。

什么是选择性？索引的选择性指的是不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行，唯一索引的选择性为1，这是最好的索引选择性，性能也是最好的。

举个例子，如下查询语句：

SELECT * FROM log WHERE staff_id = 2 AND customer_id = 3;

对于上述查询，我们应该建立联合索引（staff_id, customer_id）呢？还是建立联合索引（staff_id, customer_id）呢？

或许我们可以通过下面的查询，看看各个WHERE条件的分支对应的数据基数有多大，从而确定这个表数据的分布情况，并确定哪个列的选择性更高。

SELECT SUM(staff_id = 2), SUM(customer_id = 3) FROM log;

# 查询结果
SUM(staff_id = 2)：8000
SUM(customer_id = 3)：100

根据查询结果以及前面的经验法则，我们应该将customer_id放到联合索引的前面，因为对应条件值的customer_id查询数量更小。

使用上述方法，查询的结果非常依赖于选定的具体条件值，如果按照上述方法去判断，可能会对其他一些条件值的查询不公平，其他某些查询变得不符合预期，甚至导致性能变得更糟糕。

因此，对于经验法则来说，考虑的是全局基数和选择性，而不是具体的某个查询。我们可以使用如下查询：

SELECT
	COUNT(*),
	COUNT(DISTINCT staff_id) / COUNT(*) as staff_id_selectivity,
	COUNT(DISTINCT customer_id) / COUNT(*) as customer_id_selectivity
FROM log
# 查询结果
COUNT(*)：18000
staff_id_selectivity：0.0001
customer_selectivity：0.0373

由结果可以看出，customer_id字段的选择性更高，因此将其作为联合索引的第一列：

ALTER TABLE log ADD KEY(customer_id, staff_id);

通过上述的案例，我们可以看出经验法则在多数情况下时有用的，但是通常不如避免随机IO以及排序那么重要，我们实际在考虑问题是需要更加的全面，不同的场景下会有不同的选择。

如果说不需要考虑排序与分组，将选择性最高的列放在联合索引前面通常是很好的，这个时候索引只用于优化WHERE条件的查询，这样设计的索引能够快速过滤出需要的行，另外对于WHERE子句中只使用了索引前面列（联合索引符合最左前缀索引）的查询来说性能也更好。

当然，我们也不能一概而论的说只依赖于整体的全局基础，也需要和查询条件的具体值分布有关，可能需要根据那些运行频率高的查询来调整索引列的顺序。

总而言之，无论是具体的查询场景、还是根据经验法则，都需要我们根据实际的场景来做出最合理的判断与选择，一定要记住除了WHERE条件外，还有排序、分组、范围条件等其他因素。

2、覆盖索引

通常来说，在创建索引时，往往会根据查询的WHERE条件来创建合适的索引，但是这只不过是索引优化的一个方面而已，设计优秀的索引往往不单单只能够考虑WHERE条件部分，应该考虑到整个查询。

在MySQL中，索引不仅可以优化查询，还可以从索引中直接获取到列数据。当我们的查询能够直接从索引树的叶子节点中获取需要的列数据时，这时候我们就不必再拿着主键回到聚簇索引查找需要的列数据，即避免的回表这一操作。当然，覆盖索引只能用于MySQL的B+树索引。

如果一个索引包含（或者说覆盖）所有我们需要查询的字段值时，这时候我们称之为“覆盖索引”。

由于覆盖索引可以减少树的搜索次数，避免回表，能够显著提升查询性能，那么覆盖索引的好处有哪些呢？

  • 索引条目通常来说远小于数据行大小，如果说只需要读取索引树的话，那么MySQL就会极大的减少数据访问量。这对于缓存的负载来说非常的重要，避免了大部分时间花费在数据拷贝上，且索引比数据更小因而更容易放入内存中。
  • 索引是按照列值顺序存储，所以对于I/O密集型的范围查询来说会比随机从磁盘读取一行数据的I/O要少得多。我们知道索引存储的是索引列+主键字段（InnoDB），而索引是通过索引列来进行排序的，因此紧随其后的主键值在索引中其实并不是按顺序存储的，由于主键值的随机排列导致根据主键值回表到聚簇索引树实际上是随机读I/O，因此性能上会比较的差，而覆盖索引则完美的避免的这一回表的随机I/O读。
  • 对于一些存储引擎来说，例如MyISAM在内存中只缓存索引，数据则依赖于操作系统缓存，因此在访问完整数据需要进行一次系统调用，可能会产生一定的性能问题，尤其是系统调用占了数据访问中最大开销的场景，而覆盖索引这可以避免这种情况的发生。

当一条查询语句使用了覆盖索引时，在EXPLAIN的Extra列可以看到Using index的信息。

image.png

5、索引下推

MySQL5.6引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

举个例子，例如有如下数据库表：

CREATE TABLE `tuser` (
    `id` int(11) NOT NULL,
    `id_card` varchar(32) DEFAULT NULL,
    `name` varchar(32) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    `ismale` tinyint(1) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `id_card` (`id_card`),
    KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

在上述表结构定义中，我们定义了一个联合索引name_age

image.png

当执行如下SQL语句时：

select * from tuser where name like '张%' and age=10 and ismale=1;

在 MySQL 5.6 之前，只能从符合条件的第一条记录开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

image.png

image.png

上述两个执行过程中，第一个执行过程再无索引下推的情况下，在联合索引中不会判断age是否符合查询条件，而是直接进行回表操作，第二个执行过程中，由于索引下推的存在，会先判断联合索引记录中的age是否符合条件，当不符合条件时该记录则无需回表，否则需要进行回表。

通过使用索引下推，在某些情况下，可以减少回表次数，提高效率。

6、前缀索引

有时候所需要索引很长的字符列，会让索引变得大且慢。一个策略是通过模拟哈希索引来进行字符串索引创建，但有时候这种做是不够的。

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

通过定义前缀索引，这样可以大大节约索引的空间，从而提高索引效率，但同时也会降低索引的选择性（索引的选择性指的是不重复的索引值和数据表的记录总数的比值）。

一般情况下，我们在合理的定义某个列的前缀索引，其选择性也同样足够高，足以满足查询性能。选择足够长的前缀可以保证较高的选择性，使其选择性接近于索引的整个列，同时又不能太长，以便节约空间。

举个例子，来看看前缀索引：

假设我们在字段email创建索引:

# 创建的 index1 索引里面，包含了每个记录的整个字符串
alter table SUser add index index1(email);

# 创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。
alter table SUser add index index2(email(6));

image.png

image.png

由于email(6)这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs ），所以占用的空间会更小，这就是使用前缀索引的优势。

当执行如下SQL语句时：

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是index1（即email整个字符串的索引结构）

  • 从index1索引树找到满足索引值是 'zhangssxyz@xxx.com' 的这条记录，取得 ID2 的值；
  • 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
  • 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足
    email='zhangssxyz@xxx.com' 的条件了，循环结束。

如果使用的是index2（即email(6)索引结构）

  • 从index2索引树找到满足索引值是'zhangs'的记录，找到的第一个是 ID1 ；
  • 到主键上查到主键值是ID1的行，判断出email的值不是 'zhangssxyz@xxx.com'，这行记录丢弃；
  • 取index2上刚刚查到的位置的下一条记录，发现仍然是'zhangs'，取出 ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
  • 重复上一步，直到在index2上取到的值不是'zhangs'时，循环结束。

使用前缀索引可以减少占用的存储空间，但是，这同时带来的损失是，可能会增加额外的记录扫描次数，这是因为使用前缀索引会导致索引值的区分度不高，导致额外的扫描次数。

如果上述定义index2时，使用的email(7)而不是email(6)，即取email字段的前 7 个字节来构建索引的话，即满足前缀'zhangs'的记录只有一个，也能够直接查到ID2，只扫描一行就结束了。

因此使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

如何确定前缀索引的长度

当要给字符串创建前缀索引时，有什么方法能够确定应该使用多长的前缀呢？

在建立索引时关注的是区分度(选择性)，区分度越高越好。因为区分度越高，意味着重复的键值越少。

因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

通过下边的语句，算出这个列上有多少个不同的值：

select count(distinct email) as L from SUser;

依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

select
    count(distinct left(email,4)）as L4,
    count(distinct left(email,5)）as L5,
    count(distinct left(email,6)）as L6,
    count(distinct left(email,7)）as L7,
from SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5% 。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为6。

前缀索引对覆盖索引的影响

若执行如下 sql 语句：

select id,email from SUser where email='zhangssxyz@xxx.com';

上述sql语句中，只要求返回id和email字段，如果使用email整个字段 作为索引，则第一条语句可以使用覆盖索引，在索引中查询到结果即可直接返回，如果使用email的前缀索引 ，就不得不回到主键索引再去判断email字段的值。

因此，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是在选择是否使用前缀索引时需要考需要考虑的一个因素。

前缀的区分度不够好的情况时，如何创建索引？

比如对身份证字段建立索引，在同一地区下身份证的前几位是相同的，因此有可能需要创建长度为12以上的前缀索引，才能够满足区分度要求。

但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

为了解决这个问题，可以采取如下的方法：

  • 倒序存储

由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足够的区分度。

select field_list from t where id_card = reverse('input_id_card_string');
  • hash字段

可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。

由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以查询语句 where 部分要判断 id_card 的值是否精确相同。

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string

这样，索引的长度变成了 4 个字节，比原来小了很多。

7、普通索引与唯一索引

在不同的业务场景下，应该选择普通索引，还是唯一索引？

查询过程

我们知道，查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，然后可以认为数据页内部通过二分法来定位记录。

假设作为索引的字段不重复，则由查询过程可知：

  • 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录；
  • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索；

InnoDB的数据是按数据页为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存；因为引擎是按页读写的，当找到记录时，它所在的数据页就都在内存里了。

对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。因此两种查询过程所带来的性能差距微乎其微。

当然，如果这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些，但是，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。

所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

更新过程

为了更好的说明普通索引和唯一索引对更新语句性能的影响，我们需要先了解change buffer。

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

change buffer实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge。

  • 除了访问这个数据页会触发merge外，系统有后台线程会定期merge。
  • 在数据库正常关闭（shutdown）的过程中，也会执行merge操作。
什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。

比如，要插入某条记录，就要先判断现在表中是否已经存在该记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

两个索引的更新过程

如果要在这张表中插入一个新记录的话， InnoDB的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中

  • 对于唯一索引来说，找到对应的插入的位置，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，找到对应的插入的位置，插入这个值，语句执行结束。

第二种情况是，这个记录要更新的目标页不在内存中

  • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

  • 对于普通索引来说，则是将更新记录在 change buffer ，语句执行就结束了。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。

change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景

使用change buffer对更新过程的加速作用，change buffer只限于用在普通索引的场景下，而不适用于唯一索引。

普通索引的所有场景，使用change buffer都可以起到加速作用吗？

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前， change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了 change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

change buffer主要节省的则是随机读磁盘的 IO 消耗。

如何选择？

普通索引和唯一索引应该怎么选择？

其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议你尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer。而在其他情况下，change buffer都能提升更新性能。

在实际使用中，普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。

8、自增主键

InnoDB创建主键索引默认为聚簇索引，数据被存放在了B+Tree的叶子节点上，也就是说，同一个叶子节点内的各个数据是按主键顺序存放的。因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，即避免了页分裂的操作，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

因此，在使用InnoDB存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键。

另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

同时，在为某个列创建索引时，需要考虑该列中不重复的个数占全部记录数的比例。如果比例太低，则说明该列包含过多重复值，那么在通过二级索引+回表的方式执行查询时，就有可能执行太多次回表操作。

并且，索引列的类型尽量小，类型大小指的就是该类型占用的存储空间的大小。因为数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存放更多的记录，磁盘I/O带来的性能损耗也就越小（一次页面I/O可以将更多的记录加载到内存中），读写效率也就越高。

大白菜程序猿
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

大白菜程序猿

这个人很懒，什么都没有留下～

文章8480 评论0