索引的详解

MySQL索引详解

原创已于 2024-08-19 14:09:52 修改 · 1.7k 阅读

41 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2024-07-18 14:43:25 首次发布

一、索引的声明与使用

1、索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索等。

从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。

聚簇索引（聚集索引）：数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个
非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个
按照 作用字段个数进行划分，分成单列索引和联合索引。

不同的存储引擎支持的索引类型也不一样

InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash索引；
MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive ：不支持 B-tree、Hash、Full-text 等索引；

2、索引的创建

可以为已经存在的表设置隐藏索引，其语法形式如下：

#是否可见
CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

通过ALTER TABLE语句创建:

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

切换索引可见状态 已存在的索引可通过如下语句切换可见状态：

3、隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。
此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

#如果将index_cname索引切换成可见状态，通过explain查看执行计划，发现优化器选择了index_cname索引。

注意:
当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

通过设置隐藏索引的可见性可以查看索引对调优的帮助。

4、查看索引

（1.）SHOW INDEX FROM table_name 查看表中索引

（2.）使用EXPLAIN查看以字段invisible_column作为查询条件时的索引使用情况。

二、索引的设计原则

1、哪些情况适合创建索引

①字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

②频繁作为 WHERE 查询条件的字段
某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。
尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。

③经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。
如果待排序的列有多个，那么可以在这些列上建立组合索引。

④UPDATE、DELETE 的 WHERE 条件列
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。
如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

⑤DISTINCT 字段需要创建索引
有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。
比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行SQL 语句：
⑥多表 JOIN 连接操作时，创建索引注意事项
首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。⑦使用列的类型小的创建索引

⑦使用列的类型小的创建索引

⑧使用字符串前缀创建索引

#前缀长度
alter table shop add index(address(12));

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；
截取得少了，重复内容太多，字段的散列度(选择性)会降低。
怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度：

select count(distinct address) / count(*) from shop;

通过不同长度去计算，与全表的选择性对比：

公式：

count(distinct left(列名, 索引长度))/count(*)

引申另一个问题：索引列前缀对排序的影响

拓展：Alibaba《Java开发手册》

【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

⑨区分度高(散列性高)的列适合作为索引

数据相似性大的就不适合建立索引，如：男女性别

⑩使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。

①①在多个字段都要创建索引的情况下，联合索引优于单值索引

2、哪些情况不适合创建索引

①在where中使用不到的字段，不要设置索引

②数据量小的表最好不要使用索引
在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。

③有大量重复数据的列上不要建立索引
如，男女性别
当数据重复度大，比如高于 10% 的时候，也不需要对这个字段使用索引。

④避免对经常更新的表创建过多的索引

⑤不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

⑥删除不再使用或者很少使用的索引

⑦不要定义冗余或重复的索引
(1)冗余索引
举例：建表语句如下

CREATE TABLE person_info(
	 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	 name VARCHAR(100) NOT NULL,
	 birthday DATE NOT NULL,
	 phone_number CHAR(11) NOT NULL,
	 country varchar(100) NOT NULL,
	  PRIMARY KEY (id),
	  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
	  KEY idx_name (name(10))
);

通过idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

(2)重复索引
另一种情况，我们可能会对某个列重复建立索引，比方说这样：

CREATE TABLE repeat_index_demo (
	 col1 INT PRIMARY KEY,
	 col2 INT,
	 UNIQUE uk_idx_c1 (col1),
	 INDEX idx_c1 (col1)
);

col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免

三、性能查询

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。SHOW STATUS语句语法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：

• Connections：连接MySQL服务器的次数。
• Uptime：MySQL服务器的上线时间。
• Slow_queries：慢查询的次数。
• Innodb_rows_read：Select查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的行数
• Innodb_rows_deleted：执行DELETE操作删除的行数
• Com_select：查询操作的次数。
• Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作的次数。
• Com_delete：删除操作的次数。

1、统计SQL的查询成本：last_query_cost

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;

运行结果（1 条记录，运行时间为 0.042s ）

然后再看下查询上次执行sql的优化器的成本，实际上我们只需要检索一个页即可：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name  | Value  |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+

2、定位执行慢的 SQL：慢查询日志

1、开启慢查询日志参数

①开启slow_query_log

set global slow_query_log='ON';

四、分析查询语句：EXPLAIN

1、概述

MySQL 5.6.3以前只能 EXPLAIN SELECT；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE，DELETE
在5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令；想要显示filtered 需要使用explain extended命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

在这里插入图片描述

2、基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN，就像这样：

EXPLAIN SELECT 1;

EXPLAIN 语句输出的各个列的作用如下：

在这里插入图片描述

3、EXPLAIN各列作用

①table
不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

②id
在写的查询语句一般都以 SELECT 关键字开头，比较简单的查询语句里只有一个 SELECT 关键字，比如下边这个查询语句：

SELECT * FROM s1 WHERE key1 = 'a';

稍微复杂一点的连接查询中也只有一个 SELECT 关键字，比如：

SELECT * 
FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 
WHERE s1.common_field = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

在这里插入图片描述

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

在这里插入图片描述

小结:

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

4、select_type

在这里插入图片描述

具体分析如下：

EXPLAIN SELECT * FROM s1;

在这里插入图片描述

当然，连接查询也算是 SIMPLE 类型，比如：

 EXPLAIN SELECT * FROM s1 INNER JOIN s2;

在这里插入图片描述

PRIMARY

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

在这里插入图片描述

5、type

完整的访问方法如下：system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL。

小结：

结果值从最好到最坏依次是：system > const > eq_ref > ref> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL其中比较重要的几个提取出来（见上图中的蓝色）。
SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

五、索引失败的案例

1、全值匹配我最爱

全值的等值匹配

2、最佳左前缀法则

拓展：Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3、主键插入顺序

在这里插入图片描述

如果此时再插入一条主键值为 9的记录，那它插入的位置就如下图：

在这里插入图片描述

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。
所以我们建议：让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入，比如：

person_info 表：

CREATE TABLE person_info(
	 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	 name VARCHAR(100) NOT NULL,
	 birthday DATE NOT NULL,
	 phone_number CHAR(11) NOT NULL,
	 country varchar(100) NOT NULL,
	  PRIMARY KEY (id),
	  KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

4、计算、函数、类型转换(自动或手动)导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

5、类型转换导致索引失效

6、范围条件右边的列索引失效

ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_age;
ALTER TABLE student DROP INDEX idx_age_classid;
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
# student.classId>20的右侧的student.name = 'abc'的索引就会失效

create index idx_age_name_classid on student(age,name,classid);

将范围查询条件放置语句最后：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 ;
#直接交换sql语句的位置是没有用的，需要改变联合索引的位置

7、不等于(!= 或者<>)索引失效

当sql语句中有!=或者<>会出现索引失效的问题，尝试改写为等于，或采用覆盖索引

8、is null可以使用索引，is not null无法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

9、 like以通配符%开头索引失效

在这里插入图片描述

拓展：Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

10、OR 前后存在非索引的列，索引失效

让OR的前后条件都具备索引，如果缺少一个就会出现索引失效

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

在这里插入图片描述

#使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

在这里插入图片描述

11、数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

六、关联查询优化

1、数据准备

2、采用左外连接

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述

结论：type 有All

添加索引优化

ALTER TABLE book ADD INDEX Y ( card);  #【被驱动表】，可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述

可以看到第二行的 type 变为了 ref，rows 也变成了优化比较明显。这是由左连接特性决定的。
左外连接LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引。

ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

3、采用内连接

4、join语句原理

如果直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表，这样会影响我们分析SQL语句的执行过程。

所以，为了便于分析执行过程中的性能问题，我改用 straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。在这个语句里，t1 是驱动表，t2是被驱动表。

从表t1中读入一行数据 R；
从数据行R中，取出a字段到表t2里去查找；
取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
重复执行步骤1到3，直到表t1的末尾循环结束。

这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称NLJ

它对应的流程图如下所示：

在这里插入图片描述

在这个流程里：

对驱动表t1做了全表扫描，这个过程需要扫描100行；
而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
所以，整个执行流程，总扫描行数是200。

引申问题1：能不能使用join?

引申问题2：怎么选择驱动表？

比如：
N扩大1000倍的话，扫描行数就会扩大1000倍；而M扩大1000倍，扫描行数扩大不到10倍。

两个结论：

使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；
如果使用join语句的话，需要让小表做驱动表。

Simple Nested-Loop Join
Block Nested-Loop Join

在这里插入图片描述

执行流程图也就变成这样：

在这里插入图片描述

总结1：能不能使用xxx join语句？

总结2：如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？

总结3：什么叫作“小表”？

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

5、小结

保证被驱动表的JOIN字段已经创建了索引
需要JOIN 的字段，数据类型保持绝对一致。
LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
衍生表建不了索引

七、子查询优化

看看能不能将子查询优化成内外连接查询
MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。

原因：

① 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。
连接查询 不需要建立临时表，其 速度比子查询要快 ，如果查询中使用索引的话，性能就会更好。

结论：
尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

八、排序优化

1、排序优化

问题：
在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？
优化建议：

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。

九、GROUP BY优化

group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where效率高于having，能写在where限定的条件就不要写在having中
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

十、优先考虑覆盖索引

1、什么是覆盖索引？

直接通过二级索引对应的数据找到了查询结果，无需回表

**理解方式一：**索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
**理解方式二：**非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是，索引列+主键包含 SELECT 到 FROM之间查询的列 。

2、覆盖索引的利弊

好处：

避免Innodb表进行索引的二次查询（回表）
可以把随机IO变成顺序IO加快查询效率
弊端：
索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

十一、如何给字符串添加索引

有一张教师表，表定义如下：

create table teacher(
	ID bigint unsigned primary key,
	email varchar(64),
	...
)engine=innodb;

讲师要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

mysql> select col1, col2 from teacher where email='xxx';

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

1、前缀索引

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

mysql> alter table teacher add index index1(email);
#或
mysql> alter table teacher add index index2(email(6));

这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图。

在这里插入图片描述

以及

在这里插入图片描述

如果使用的是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索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

2、前缀索引对覆盖索引的影响
结论：

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

十二、普通索引 vs 唯一索引

从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引，假设字段 k 上的值都不重复。这个表的建表语句是：

这个表的建表语句是：

sqlmysql> create table test(
		id int primary key,
		k int not null,
		name varchar(16),
		index (k)
	)engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)。

1、查询过程
假设，执行查询的语句是 select id from test where k=5。

对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微 。

2、更新过程
为了说明普通索引和唯一索引对更新语句性能的影响这个问题，介绍一下changebuffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行changebuffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了访问这个数据页会触发merge外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

如果能够将更新操作先记录在change buffer， 减少读磁盘 ，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够 避免占用内存，提高内存利用率。
唯一索引的更新就不能使用change buffer ，实际上也只有普通索引可以使用。

如果要在这张表中插入一个新记录(4,400)的话，InnoDB的处理流程是怎样的？

3、change buffer的使用场景

普通索引和唯一索引应该怎么选择？其实，这两类索引在查询能力上是没差别的，主要考虑的是对 更新性能的影响。所以，建议你 尽量选择普通索引 。
在实际使用中会发现， 普通索引 和change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在其他情况下，change buffer都能提升更新性能。
由于唯一索引用不上change buffer的优化机制，因此如果 业务可以接受 ，从性能角度出发建议优先考虑非唯一索引。但是如果"业务可能无法确保"的情况下，怎么处理呢？

首先， 业务正确性优先 。我们的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。这种情况下，本节的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，给你多提供一个排查思路。
然后，在一些“ 归档库 ”的场景，你是可以考虑使用唯一索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。