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时,循环结束。

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

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

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