MySQL优化之索引优化（六）

圆之蓝调
66 阅读2分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第15天，点击查看活动详情

前言

上篇我们学习了MySQL中的数据库优化之索引优化。有兴趣的小伙伴可以阅读(MySQL优化之索引优化（五）)。
下面我们继续学习MySQL中的数据库优化之索引优化。

给字符串添加索引

创建一张教师表

CREATE TABLE teacher(
    ID BIGINT UNSIGNED PRIMARY KEY,
    email VAHCHAR(64)
)engine = innodb;

如果想查询使用哪个email的老师，需要使用如下语句：

SELECT *
FROM teacher
WHERE email = `xxxs.com`;

如果email这个字段上没有索引，那么这个语句就只能做全表扫描。

前缀索引

MySQL支持前缀索引。默认的，如果想创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。下面创建两种索引。

ALTER TABLE teacher ADD INDEX index1(email);
ALTER TABLE teacher ADD INDEX index2(email(3));

如果使用的是index1（即email整个字符串的索引结构），执行顺序如下：

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

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是index2（即email(3)的索引结构），执行顺序如下：

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

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

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

今天先学习到这里，明天继续。

avatar
文章
阅读
粉丝