一、索引的声明与使用
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)。
表中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的优化机制,因此如果
业务可以接受
,从性能角度出发建议优先考虑非唯一索引。但是如果"业务可能无法确保"的情况下,怎么处理呢?
- 首先,
业务正确性优先
。我们的前提是“业务代码已经保证不会写入重复数据”的情况下,讨论性能问题。如果业务不能保证,或者业务就是要求数据库来做约束,那么没得选,必须创建唯一索引。这种情况下,本节的意义在于,如果碰上了大量插入数据慢、内存命中率低的时候,给你多提供一个排查思路。 - 然后,在一些“
归档库
”的场景,你是可以考虑使用唯一索引的。比如,线上数据只需要保留半年,然后历史数据保存在归档库。这时候,归档数据已经是确保没有唯一键冲突了。要提高归档效率,可以考虑把表里面的唯一索引改成普通索引。