MySQL架构篇

大厂面试问题

存储引擎的InnoDB与MyISAM的区别，优缺点，使用场景？

说说MySQL 优化之道？

UndoLog和RedoLog的区别和联系？

MySQL索引的数据结构是什么，及为什么使用这种数据结构？

索引失效的场景有哪些？

什么是死锁和死锁的排查和解决？

RC和RR的实现原理及区别和使用场景？

分库与分表带来的分布式困境与应对之策？

。。。。。

逻辑架构

架构逻辑图

image-20200321212236626

MySQL将数据存储到硬盘中，MySQL就是如何操作硬盘上的文件

客户端是提出需求的，SQL层是对SQL请求进行一步一步处理，而可插拔式存储引擎层是用来将数据从文件系统中存储或者查询的

SQL Interface

接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户

Parser

进行语法解析和词法解析，首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理

Optiomizer

优化索引选择，对SQL命令按照标准流程进行优化分析

InnoDB是默认的存储引擎

存储引擎（Pluggable Storage Engines）

以表为单位，每张表在建立的时候都会默认使用存储引擎，默认使用InnoDB，如果想使用其他存储引擎，则在建表的时候就声明

1
creat table xxx() engine=InnoDB/Memory/MyISAM

MySQL的存储引擎是针对表进行指定的（engine=InnoDB\myisam）

存储引擎 说明
MyISAM 高速引擎，拥有较高的插入，查询速度，但不支持事务、不支持行锁、支持3种不同的存储格式。包括静态型、动态型和压缩型
InnoDB 5.5版本后MySQL的默认数据库，支持事务和行级锁定，事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全，比MyISAM处理速度稍慢、支持外键（FOREIGN KEY）
ISAM MyISAM的前身，MySQL5.0以后不再默认安装
MRG_MyISAM（MERGE） 将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory 内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
Falcon 一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者
Archive 将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作
CSV CSV 存储引擎是基于CSV 格式文件存储数据(应用于跨平台的数据交换)

xtraDB存储引擎是由Percona公司提供的存储引擎，该公司还出品了Percona Server这个产品，它是基于MySQL开源代码进行修改之后的产品，阿里对于Percona Server服务器进行修改，衍生了自己的数据库（alisql）

InnoDB和MyISAM存储引擎区别

InnoDB MyISAM
存储文件 .frm 表定义文件
.ibd 数据文件和索引文件		 .frm 表定义文件
.myd 数据文件
.myi 索引文件
锁 表锁、行锁 表锁
事务 支持 不支持
CRDU 读、写 读多
count 扫表 专门存储的地方（加where也扫表）
索引结构 B+ Tree B+ Tree
外键 支持 不支持

只有行锁才支持事务

存储引擎的选型

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，不需要持久保存，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎

简版执行流程图

image-20200322084952595

详细执行流程图

image-20200322090911369

物理架构

物理架构更多的看文件系统

  • MySQL是通过文件系统对数据和索引进行存储的
  • MySQL从物理结构上可以分为日志文件和数据索引文件
  • MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下
  • 日志文件采用顺序IO方式存储、数据文件采用随机IO方式存储

硬盘运行原理

image-20200322100912780

页、块、扇区之间的关系和区别

页：内存以页这个单位去进行IO读取，一般大小为4K，在MySQL中可以通过Innodb_page_size设置大小，一般设置为16K，一页=2^n块

块：操作系统以块这个逻辑单位去操作磁盘，常见为4K，一块=2^n扇区

扇区：磁盘以扇区这个物理最小磁盘单位去存储数据，常见为512Byte

页大小查看：getconf PAGE_SIZE，常见为4K

磁盘块大小查看：stat /boot/|grep “IO Block”，常见为4K

扇区大小查看：fdisk -l，常见为512Byte

指针默认长度为6bit，如果key为bigint的话，为8bit，那么一个索引的话为8+6 = 14bit

索引节点：是页的倍数，一个索引节点，可以存储N多页的数据，MySQL可以设置大小

数据文件

1
SHOW VARIABLES LIKE '%datadir%';

InnoDB数据文件

.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息

.ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件

ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件

MyISAM数据文件

.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息

.myd文件：主要用来存储表数据信息

.myi文件：主要用来存储表数据文件中任何索引的数据树

日志文件

错误日志（errorlog）

默认是开启的，而且从5.5.7以后无法关闭错误日志，错误日志记录了运行过程中遇到的所有严重的错误信息,以及MySQL每次启动和关闭的详细信息

二进制日志（bin log）

记录数据变化

binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。生产中开启

数据备份、恢复、主从都和二进制日志有关

通用查询日志（general query log）

什么都记录，耗性能生产中，不开启

慢查询日志（slow query log）

SQL调优，定位慢的select

默认是关闭的

需要通过以下设置进行开启

1
2
3
4
5
6
#开启慢查询日志
slow_query_log=ON
#慢查询的阈值
long_query_time=3
#日志记录文件如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
slow_query_log_file=file_name

记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句

重做日志（redo log）

回滚日志（undo log）

中继日志（relay log）

看日志开启情况

1
show variables like 'log_%';

在my.cnf配置这些文件的信息

MySQL索引篇

索引介绍

索引是什么

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别

索引的优势和劣势

优势

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。– 检索
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。–排序
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一
些。
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
where 索引列在存储引擎层处理索引下推ICP
覆盖索引select 字段字段是索引

劣势：
索引会占据磁盘空间
索引虽然会提高查询效率，但是会降低更新表的效率**。比如每次对表进行增删改操作，
MySQL不仅要保存数据，还有保存或者更新对应的索引文件

索引的分类

在高性能mysql中

MySQL里的索引类型主要有以下几种

1. B-Tree索引

最常见的索引类型，基于B-Tree数据结构。B-Tree的基本思想是，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）。但是当索引多列时，列的顺序特别重要，需要格外注意。InnoDB和MyISAM都支持B-Tree索引。InnoDB用的是一个变种B+Tree，而MyISAM为了节省空间对索引进行了压缩，从而牺牲了性能。

2. Hash索引

基于hash表。所以这种索引只支持精确查找，不支持范围查找，不支持排序。这意味着范围查找或ORDER BY都要依赖server层的额外工作。目前只有Memory引擎支持显式的hash索引（但是它的hash是nonunique的，冲突太多时也会影响查找性能）。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引。

例子

1
2
3
4
5
CREATE TABLE testhash (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
) ENGINE =MEMORY;

3. Spatial (R-Tree)（空间）索引

只有MyISAM引擎支持，并且支持的不好。可以忽略。

4. Full-text索引（全文索引）

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。Full-text索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的WHERE语句的参数匹配。你可以对某列分别进行full-text索引和B-Tree索引，两者互不冲突。Full-text索引配合MATCH AGAINST操作使用，而不是一般的WHERE语句加LIKE。

在一般的教学中

普通索引

MySQL中最基本的索引类型，没有什么限制，允许定义索引的列中插入重复值和空值，纯粹就是为了查询数据更快一些

唯一索引

索引列中的值必须是唯一的，但是允许空值

主键索引

一种特殊的唯一索引，内容不能重复，不允许有空值

单列索引

单列，一个表可以多个单值索引， age ，name

组合索引

在表中的多个字段组合上创建的索引，遵循最左前缀原则

全文索引

全文索引只能在MyISAM存储中才能使用，而且只能在char、varchar、text 类型字段上才能使用

空间索引

位图索引Oracle

索引的使用

创建索引

单列索引之普通索引

1
2
CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;

单列索引之唯一索引

1
2
CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);

单列索引之全文索引

1
2
CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)

组合索引

1
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

注意：如果一个字段是primary key，则该字段默认就是主键索引

删除索引

1
DROP INDEX index_name ON table

查看索引

1
SHOW INDEX FROM table_name

索引原理分析

索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

看一个例子

image-20200323151704457

如图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)O(log2n)的复杂度内获取到相应数据。
虽然这是一个货真价实的索引，但是实际的数据库系统几乎没有使用二叉查找树或其进化品种红黑树（red-black tree）实现的，原因会在下文介绍

索引的存储结构

  • 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
  • MyISAM和InnoDB存储引擎：只支持B+ TREE索引，也就是说默认使用BTREE，不能够更换
  • MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B-Tree和B+Tree

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，在本文的下一节会结合存储器原理及计算机存取原理讨论为什么B-Tree和B+Tree在被如此广泛用于索引，这一节先单纯从数据结构角度描述它们

数据结构示例网站

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B-Tree（B树）

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的，data为数据记录除key外的数据

B树图示

B树是为了磁盘或其它存储设备而设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。多叉平衡

image-20200322104229571

  • B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数
  • 如果是三层树结构—支撑的数据可以达到20G，如果是四层树结构—支撑的数据可以达到几十T

那么B-Tree是满足下列条件的数据结构

d为大于1的一个正整数，称为B-Tree的度。
h为一个正整数，称为B-Tree的高度。
每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为
null 。
所有叶节点具有相同的深度，等于树高h。
key和指针互相间隔，节点两端是指针。
一个节点中的key从左到右非递减排列。
所有节点组成树结构。

B+Tree（B+树）

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构

与B-Tree相比，B+Tree有以下不同点

每个节点的指针上限为2d而不是2d+1

内节点不存储data，只存储key

叶子节点不存储指针

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关，将在下面讨论

B和B+的区别

B树和B+树的最大区别在于非叶子节点是否存储数据的问题

B树是非叶子节点和叶子节点都会存储数据

B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的

###

聚集索引和非聚集索引

data中存储的是表数据还是磁盘地址（指针），这就区分为是聚集索引还是非聚集索引

聚集索引（InnoDB）

将表数据和索引数据都存到一个主键索引上

主键索引

主键索引必须要有（MyISAM可以没有）

如果有主键（最好是int），则直接使用

如果没有主键，就会使用唯一索引建立聚集索引

如果没有唯一索引，则MySQL默认创建唯一隐藏列，由该列去创建key作为主键索引

数据文件和索引文件都保存在*.ibd中

image-20200322105025807

辅助索引（次要索引）

不存表数据也不存地址值，只存主键索引的主键值（key）

如果是非主键查询，则需要搜索两次索引树，一次是辅助索引树，一次是主键索引树，最终取出来数据

非聚集索引（MyISAM）

表数据和索引数据没有存储到一起，主索引和次要索引，都是存储表数据的磁盘地址

主键索引

数据文件存储在*.myd中

索引文件存储在*.myi中

查询只要使用到索引，就会找数据文件中的记录

image-20200322104854128

辅助索引（次要索引）

image-20200322104951887

补充

  • 创建索引的时候，只要不是主键就会创建次要索引，比如说普通的列就是次要索引，次要索引直到主键是哪一个。主键索引是自动创建，主键索引是每张表必有的，除此之外，其他的索引都需要通过命令才能够创建

  • 只要是组合索引（除了联合主键），都是次要索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
什么是数据表的复合主键
所谓的复合主键 就是指你表的主键含有一个以上的字段组成
比如
create table test
(
   name varchar(19),
   id number,
   value varchar(10),
   primary key (name,id)
)

上面的name和id字段组合起来就是你test表的复合主键
它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性
一般情况下，主键的字段长度和字段数目要越少越好

联合主键
什么是联合主键？
(主键原则上是唯一的，别被唯一值所困扰。)
顾名思义就是多个主键联合形成一个主键组合
一个简单的例子
主键A跟主键B组成联合主键
主键A跟主键B的数据可以完全相同(困扰吧，没关系)，联合就在于主键A跟主键B形成的联合主键是唯一的。
下例主键A数据是1，主键B数据也是1，联合主键其实是11，这个11是唯一值，绝对不充许再出现11这个唯一值。(这就是多对多关系)
主键A数据 主键B数据
1　　　　　　1
2　　　　　　2
3　　　　　　3
主键A与主键B的联合主键值最多也就是
11
12
13
21
22
23
31
32
33

  • 一个组合索引有多个列，会创建一个索引树

  • B+树的叶子节点中的数据，都是有序的，其实是一个链表

联合索引的存储结构

联合索引的如何存储与如何排序

首先，表T1有字段a,b,c,d,e这5个列，其中a是主键，除e为varchar其余为int类型，并创建了一个联合索引
idx_t1_bcd(b,c,d)，然后b、c、d三列作为联合索引（组合索引），下面是假设的表数据以及对其联合索引在B+树上的结构图的改进。PS：基于InnoDB存储引擎

image-20200323173547370

bcd联合索引在B+树上的结构图

image-20200323182743277

联合索引的查找方式

当我们的SQL语言可以应用到索引的时候，比如select * from T1 where b = 12 and c = 14 and d = 3;
也就是T1表中a列为4的这条记录。存储引擎首先从根节点（一般常驻内存）开始查找，第一个索引的第一个索引列为1,12大于1，第二个索引的第一个索引列为56,12小于56，于是从这俩索引的中间读到下一个节点的磁盘文件地址，从磁盘上Load这个节点，通常伴随一次磁盘IO，然后在内存里去查找。当Load叶子节点的第二个节点时又是一次磁盘IO，比较第一个元素，b=12,c=14,d=3完全符合，于是找到该索引下的data元素即ID值，再从主键索引树上找到最终数据

image-20200323193109689

最左前缀匹配原则

之所以会有最左前缀匹配原则和联合索引的索引构建方式及存储结构是有关系的

首先我们创建的idx_t1_bcd(b,c,d)索引，相当于创建了(b)、（b、c）（b、c、d）三个索引，看完下面你就知道为什么相当于创建了三个索引

我们看，联合索引是首先使用多列索引的第一列构建的索引树，用上面idx_t1_bcd(b,c,d)的例子就是优先使用b列构建，当b列值相等时再以c列排序，若c列的值也相等则以d列排序。我们可以取出索引树的叶子节点看一下

image-20200323193246991

索引的第一列也就是b列可以说是从左到右单调递增的，但我们看c列和d列并没有这个特性，它们只能在b列值相等的情况下这个小范围内递增，如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素

由于联合索引是上述那样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如（c,d）、(c）、(d)是无法应用缓存的，以及跨列也是无法完全用到索引如(b,d)，只会用到b列索引

索引使用场景

哪些情况需要创建索引

  1. 主键自动建立唯一索引

  2. 频繁作为查询条件的字段应该创建索引，就是where后的字段

  3. 多表关联查询中，关联字段应该创建索引on两边都要创建索引

1
select * from user left join order on user.id = order.userid

  1. 查询中排序的字段，应该创建索引，B + tree 有顺序

  2. 覆盖索引好处是？不需要回表组合索引（回表的意思是回主键里，即primary key的表数据查询）

    但是如果加了一个字段sex，但是sex不是组合所引，此时就需要回表查询

1
2
3
user表中有一个组合索引（name,age）
select * from user ------- 全表扫描，没有使用索引
select name,age from user ---- 使用到了索引，索引覆盖，不需要回表

6、统计或者分组字段，应该创建索引

哪些情况不需要创建索引

  1. 表记录太少，因为索引是要有存储的开销
  2. 频繁更新，相应的索引也需要更新，索引是要维护
  3. 查询字段使用频率不高

为什么使用组合索引

推荐使用组合索引

由多个字段组成的索引使用顺序就是创建的顺序

1
ALTER TABLE 'table_name' ADD INDEX index_name(col1,col2,col3);

组合索引的好处

  1. 建一个索引树，相当于创建了多棵索引树，比如说创建了组合索引（name,age）就相当于创建了以name为key的索引树和以name、age创建的索引树，节省了空间

    1
    2
    select * from user where name = 'zhangsan' and age=18;
    select * from user where name = 'zhangsan';

​ 以上两个查询都可以使用一个组合suoy

  1. 可以优化排序

    比如说创建了组合索引（name,age）

    1
    2
    select * from user order by name,age;--可以使用索引
    select * from user order by name;--不能使用索引

  2. 可以使用覆盖索引优化查询

    比如说创建了组合索引（name,age）

    1
    2
    3
    select name,age from user;--可以使用索引
    select name from user;--不能使用索引
    select name,age,sex from user;--不能使用索引

组合索引的使用

组合索引的列不一定全部被使用到

遵循最左前缀原则：对于组合索引的使用，需要按照组合索引的创建顺序，从左往右依次匹配

能匹配一个就可以使用组合所引，如果中间断开，则后续的列使用不到，如果使用<、>、between、like

1、前缀索引

like 常量% 使用索引，like %常量不使用索引

2、最左前缀

从左向右匹配直到遇到范围查询> < between ，索引失效

最左前缀的原理

最左前缀就是从左边开始找索引树的叶子节点（key）

注意：不管一张表有几个索引，在查询的时候最多只能使用一个索引，除了index-merge这种情况

索引失效

查看执行计划

执行计划是在优化器中产生的

1
2
3
4
5
6
7
8
9
10
11
12
13
create table tuser(
id int primary key,
name varchar(100),
age int,
  sex char(1),
address varchar(100)
);

alter table tuser add index idx_name_age(name(100),age);
alter table tuser add index idx_sex(sex(1));

insert into tuser(id,name,age,sex,address) values (1,'zhangsan',20,'0','致真
大厦');

介绍

MySQL 提供了一个EXPLAIN 命令, 它可以对SELECT 语句的执行计划进行分析, 并输出SELECT 执行的详细信息, 以供开发人员针对性优化

使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看

可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单, 在SELECT 语句前加上explain 就可以了, 例如

image-20200323082851057

参数说明

EXPLAIN 命令的输出内容大致如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from user where id = 2\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
      table: user_info
  partitions: NULL
        type: const
possible_keys: PRIMARY
key: PRIMARY
    key_len: 8
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

各列的含义如下

id: SELECT 查询的标识符，每个SELECT 都会自动分配一个唯一的标识符

select_type: SELECT 查询的类型

table: 查询的是哪个表

partitions: 匹配的分区

type: join 类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引

ref: 哪个字段或常数与key 一起被使用

rows: 显示此查询一共扫描了多少行， 这个是一个估计值

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

id

每个单位查询的SELECT语句都会自动分配的一个唯一标识符，表示查询中操作表的顺序

有四种情况：

  • id相同：执行顺序由上到下
  • id不同：如果是子查询，id号会自增，id越大，优先级越高
  • id相同的与不同的可以同时存在
  • id列为null的就表示这是一个结果集，不需要使用它来进行查询

select_type（重要）

单位查询的查询类型，比如：普通查询、联合查询(union、union all)、子查询等复杂查询

simple

表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个

image-20200323083803751

primary

一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个

image-20200323083901192

union

union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union

image-20200323083946210

dependent union

image-20200323084636404

union result

包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

image-20200323084723857

subquery

除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

image-20200323084810285

dependent subquery

与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

image-20200323084847272

derived

from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

image-20200323084924049

table

显示的单位查询的表名，有如下几种情况

  • 如果查询使用了别名，那么这里显示的是别名
  • 如果不涉及对数据表的操作，那么这显示为null
  • 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于
    这个查询产生
  • 如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查
    询的id为M,N的结果集

type（重要）

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差

1
2
3
4
5
6
7
8
9
10
11
12
system
const
eq_ref
ref
fulltext
ref_or_null
unique_subquery
index_subquery
range
index_merge
index
ALL

注意事项

  • 除了all之外，其他的type都可以使用到索引
  • 除了index_merge之外，其他的type只可以用到一个索引
  • 最少要使用到range级别

system

表中只有一行数据或者是空表

image-20200323092745378

const（重要）

使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描

image-20200323092827404

eq_ref（重要）

前提

  • 多表关联

  • 等值连接

  • 等值连接的两个表的列是唯一索引列或者主键索引列

此类型通常出现在多表的join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是= ， 查询效率较高

image-20200323092918584

ref（重要）

前提

  • 多表关联

  • 等值连接

  • 等值连接的两个表的列是非唯一索引列

针对非唯一性索引，使用等值（=）查询，或者是使用了最左前缀规则索引的查询

  1. 组合索引

image-20200323101726437

  1. 非唯一索引

image-20200323101755965

fulltext

全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

ref_or_null

与ref方法类似，只是增加了null值的比较。实际用的不多

unique_subquery

用于where中的in形式子查询，子查询返回不重复值唯一值

index_subquery

用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重

range（重要）

索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中

image-20200323101931475

index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range

index（重要）

select结果列中使用到了索引，type会显示为index

全部索引扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询

image-20200323102955346

all（重要）

这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录，注意全表扫描和全索引扫描

image-20200323103029833

image-20200323103047602

possible_keys

此次查询中可能选用的索引，一个或多个

key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个

key_len

key_len

ref

如果是使用的常数等值查询，这里会显示const

如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段

如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB里面使用了MVCC并发机制）

extra（重要）

这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有

using filesort（重要）

说明排序的列没有使用到索引

  • 排序时无法使用到索引时，就会出现这个，常见于order by和group by语句中

  • 说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取

  • MySQL中无法利用索引完成的排序操作称为“文件排序”

1
2
3
4
5
6
7
8
9
mysql> explain select * from tuser order by address;
+----+-------------+-------+------+---------------+------+---------+------+--
----+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra         |
+----+-------------+-------+------+---------------+------+---------+------+--
----+----------------+
| 1 | SIMPLE     | tuser | ALL | NULL         | NULL | NULL   | NULL |  2 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--
----+----------------+

排序是数据库中的一个基本功能，MySQL也不例外。用户通过Order by语句即能达到将指定的结果集排序的目的，其实不仅仅是Order by语句，Group by语句，Distinct语句都会隐含使用排序。我们首先会简单介绍SQL如何利用索引避免排序代价，然后会介绍MySQL实现排序的内部原理，并介绍与排序相关的参数

1排序优化与索引使用

为了优化SQL语句的排序性能，最好的情况是避免排序，合理利用索引是一个不错的方法。因为索引本身也是有序的，如果在需要排序的字段上面建立了合适的索引，那么就可以跳过排序的过程，提高SQL的查询速度

下面我通过一些典型的SQL来说明哪些SQL可以利用索引减少排序，哪些SQL不能

假设t1表存在索引key1(key_part1,key_part2)，key2(key2)

a.可以利用索引避免排序的SQL

1
2
3
4
5
SELECT * FROM t1 ORDER BY key_part1,key_part2;
SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
SELECT * FROM t1 WHERE key_part1=constant1 AND key_part2>constant2 ORDER BY
key_part2;

b.不能利用索引避免排序的SQL

1
2
3
4
5
6
7
8
-- 排序字段在多个索引中，无法使用索引排序
SELECT * FROM t1 ORDER BY key_part1,key_part2, key2;
-- 排序键顺序与索引中列顺序不一致，无法使用索引排序
SELECT * FROM t1 ORDER BY key_part2, key_part1;
-- 升降序不一致，无法使用索引排序
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
-- key_part1是范围查询，key_part2无法使用索引排序
SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;

排序实现的算法

对于不能利用索引避免排序的SQL，数据库不得不自己实现排序功能以满足用户需求，此时SQL的执行计划中会出现“Using filesort”，这里需要注意的是filesort并不意味着就是文件排序，其实也有可能是内存排序，这个主要由sort_buffer_size参数与结果集大小确定。MySQL内部实现排序主要有3种方式，常规排序，优化排序和优先队列排序，主要涉及3种排序算法：快速排序、归并排序和堆排序。假设表结构和SQL语句如下

1
2
3
4
5
6
7
8
9
CREATE TABLE t1(
id int,
  col1 varchar(64),
col2 varchar(64),
col3 varchar(64),
PRIMARY KEY(id),key(col1,col2)
);

SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

a.常规排序

(1)从表t1中获取满足WHERE条件的记录

(2)对于每条记录，将记录的主键+排序键(id,col2)取出放入sort buffer

(3)如果sort buffer可以存放所有满足条件的(id,col2)对，则进行排序；否则sort buffer满后，进行排序并固化到临时文件中。(排序算法采用的是快速排序算法)

(4)若排序中产生了临时文件，需要利用归并排序算法，保证临时文件中记录是有序的

(5)循环执行上述过程，直到所有满足条件的记录全部参与排序

(6)扫描排好序的(id,col2)对，并利用id去捞取SELECT需要返回的列(col1,col2,col3)

(7)将获取的结果集返回给用户

从上述流程来看，是否使用文件排序主要看sort buffer是否能容下需要排序的(id,col2)对，这个buffer的大小由sort_buffer_size参数控制。此外一次排序需要两次IO，一次是捞(id,col2),第二次是捞(col1,col2,col3)，由于返回的结果集是按col2排序，因此id是乱序的，通过乱序的id去捞(col1,col2,col3)时会产生大量的随机IO。对于第二次MySQL本身一个优化，即在捞之前首先将id排序，并放入缓冲区，这个缓存区大小由参数read_rnd_buffer_size控制，然后有序去捞记录，将随机IO转为顺序IO

b.优化排序

常规排序方式除了排序本身，还需要额外两次IO。优化的排序方式相对于常规排序，减少了第二次IO。主要区别在于，放入sort buffer不是(id,col2),而是(col1,col2,col3)。由于sort buffer中包含了查询需要的所有字段，因此排序完成后可以直接返回，无需二次捞数据。这种方式的代价在于，同样大小的sortbuffer，能存放(col1,col2,col3)数目要小于(id,col2)，如果sort buffer不够大，可能导致需要写临时文件，造成额外的IO。当然MySQL提供了参数max_length_for_sort_data，只有当排序元组小于max_length_for_sort_data时，才能利用优化排序方式，否则只能用常规排序方式

c.优先队列排序

为了得到最终的排序结果，无论怎样，我们都需要将所有满足条件的记录进行排序才能返回。那么相对于优化排序方式，是否还有优化空间呢？5.6版本针对Order by limit M，N语句，在空间层面做了优化，加入了一种新的排序方式–优先队列，这种方式采用堆排序实现。堆排序算法特征正好可以解limitM，N 这类排序的问题，虽然仍然需要所有元素参与排序，但是只需要M+N个元组的sort buffer空间即可，对于M，N很小的场景，基本不会因为sort buffer不够而导致需要临时文件进行归并排序的问题。对于升序，采用大顶堆，最终堆中的元素组成了最小的N个元素，对于降序，采用小顶堆，最终堆中的元素组成了最大的N的元素

using index（重要）

查询时不需要回表查询，直接通过索引就可以获取查询的数据

  • 表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错
  • 如果同时出现Using Where ，说明索引被用来执行查找索引键值
  • 如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作
1
2
3
4
5
6
7
8
9
10
mysql> explain select name,age,sex from tuser ;
+----+-------------+-------+-------+---------------+------------------+------
---+------+------+-------------+
| id | select_type | table | type | possible_keys | key |key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------------+------
---+------+------+-------------+
|  1 | SIMPLE | tuser | index | NULL | idx_name_age_sex | 312 | NULL | 2 | Using index |
+----+-------------+-------+-------+---------------+------------------+------
---+------+------+-------------+
全值匹配覆盖索引

using index condition（重要）

说明用到了索引下推

ICP只能针对二级索引进行使用。不需要针对主键索引

MySQL5.6之前，查询的时候，只会根据Index Key去存储引擎层，确定索引的范围，然后将该范围内的记录取出来，返回到Sql Server层，SqlServer层，再根据where中其他的过滤条件进行过滤。有也就是说IndexFilter和TableFilter都是在SqlServer层进行过滤的

介绍

Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用WHERE子句中的其他条件去过滤这些数据行

因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。所以ICP（Index Condition Pushdown，索引下推）其实就是实现了index filter技术，将原来的在server层进行的table filter中可以进index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数

where条件分类

要想深入理解ICP 技术，必须先理解数据库是如何处理where 中的条件的
对where 中过滤条件的处理，根据索引使用情况分成了三种：index key、index filter、table filter，这三个都不是必须要有的

1. index key

用于确定SQL查询在索引中的连续范围(起始范围+结束范围)的查询条件，被称之为Index Key。由于一个范围，至少包含一个起始与一个终止，因此Index Key也被拆分为Index First Key和Index Last Key，分别用于定位索引查找的起始，以及索引查询的终止条件。也就是说根据索引来确定扫描的范围。

2. index filter

在使用index key 确定了起始范围和介绍范围之后，在此范围之内，还有一些记录不符合where 条件，如果这些条件可以使用索引进行过滤，那么就是index filter。也就是说用索引来进行where条件过滤

3. table filter

where 中的条件不能使用索引进行处理的，只能访问table，进行条件过滤

也就是说各种各样的where 条件，在进行处理时，分成了上面三种情况，一种条件会使用索引确定扫描的范围，一种条件可以在索引中进行过滤，一种必须回表进行过滤

如何确定哪些where条件分别是index key,index filter,table filter？

在MySQL5.6 之前，并不区分Index Filter与Table Filter，统统将Index First Key与Index LastKey范围内的索引记录，回表读取完整记录，然后返回给MySQL Server层进行过滤

而在MySQL 5.6（包含）之后，Index Filter与Table Filter分离，Index Filter下降到InnoDB的索引层面进行过滤，减少了回表与返回MySQL Server层的记录交互开销，提高了SQL的执行效率

所以所谓的ICP 技术，其实就是index filter技术而已。只不过因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。所以ICP其实就是实现了index filter技术，将原来的在server层进行的table filter中可以进行index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter

不使用ICP扫描的过程

storage层

只将满足index key条件的索引记录对应的整行记录取出，返回给server层

server 层

对返回的数据，使用后面的where条件过滤，直至返回最后一行

image-20200323135440442

image-20200323135607708

使用ICP扫描的过程

storage层

首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤

将满足的index filter条件的索引记录才去回表取出整行记录返回server层

不满足index filter条件的索引记录丢弃，不回表，也不会返回server层

server 层

对返回的数据，使用table filter条件做最后的过滤

image-20200323135808025

image-20200323135838274

使用前后的成本差别

  • 使用ICP前，存储层多返回了需要被index filter过滤掉的整行记录

  • 使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本

ICP 例子

例子1

官方文档给出了一个例子

1
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

上面例子中的lastername like '%etrunia%'和address like '%Main Street%'本来是无法使用复合索引index(zipcode, lastername, firstname)进行过滤的，但是因为有了ICP技术，所以他们可以在index filter
阶段使用索引进行过滤，无需回表进行table filter

例子2

role_goods 表上有组合索引index(roleId,status,number)，下面的select语句，因为“索引最左前缀原则”，只能使用到组合索引的roleId 部分，但是因为ICP 技术的存在，现在number 条件过滤也可以在index filter 阶段完成了，无需像以前一样需要进行table filer了

1
2
3
4
5
6
7
8
9
10
mysql> explain select * from role_goods where roleId=100000001 and number=1;
+----+-------------+------------+------+---------------+----------+---------
+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len |ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------
+-------+------+-----------------------+
|  1 | SIMPLE | role_goods | ref | roleId_2 | roleId_2 | 9 |const | 14 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------
+-------+------+-----------------------+
1 row in set (0.01 sec)

案例解析

可以看到key_len = 9, 因为roleId 是big int 类型，所以key_len = 8 + 1 = 9; 所以在index key 阶段中，并没有使用到组合索引index(roleId,status,number) 中的number 字段(因为中间有一个status字段没有出现在where 条件中)，但是“Using index condition” 却说明使用到了ICP技术，显然是number =1条件过滤使用到了ICP技术

ICP的使用条件

  • 只能用于二级索引(secondary index)

  • explain显示的执行计划中type值（join 类型）为range、ref、eq_ref或者ref_or_null

  • 且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)

  • 对于InnnoDB表，ICP仅用于二级索引。（ICP的目的是减少全行读取的次数，从而减少IO操作），对于innodb聚集索引，完整的记录已被读入到innodb缓冲区，在这种情况下，ICP不会减少io

  • ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）

using temporary

  • 表示使用了临时表存储中间结果
  • MySQL在对查询结果order by和group by时使用临时表
  • 临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来

distinct

在select部分使用了distinct关键字（索引字段）

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select distinct a.id from tuser a,tdep b where a.dep=b.id;
+----+-------------+-------+--------+--------------------------------------
----------+---------+---------+------------+------+--------------------------
-----------------+
| id | select_type | table | type| possible_keys  | key | key_len | ref| rows | Extra |
+----+-------------+-------+--------+--------------------------------------
----------+---------+---------+------------+------+--------------------------
-----------------+
| 1 | SIMPLE | a | index |PRIMARY,idx_loginname,idx_name_age_sex,idx_dep | idx_dep | 5   | NULL |2 | Using where; Using index; Using temporary |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4| kkb2.a.dep |1 | Using index; Distinct|
+----+-------------+-------+--------+--------------------------------------
----------+---------+---------+------------+------+--------------------------
-----------------+

using where（重要）

表示存储引擎层返回的记录并不是所有的都满足查询条件，需要在server层进行过滤

数据在存储引擎层没有过滤干净，需要在SQL server层继续过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
--查询条件无索引
mysql> explain select * from tuser where address='beijing';
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
|  1 | SIMPLE | tuser | ALL | NULL | NULL | NULL| NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
--索引失效
mysql> explain select * from tuser where age=1;
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
|  1 | SIMPLE | tuser | ALL | NULL | NULL | NULL   | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------
+------+-------------+
mysql> explain select * from tuser where id in(1,2);
+----+-------------+-------+-------+---------------+---------+---------+---
---+------+-------------+
| id | select_type | table | type | possible_keys | key| key_len |ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+---
---+------+-------------+
|  1 | SIMPLE| tuser | range | PRIMARY | PRIMARY | 4 |NULL |  2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+---
---+------+-------------+
  • 查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量
  • extra列显示using index condition
1
2
3
4
5
6
7
8
9
mysql> explain select * from tuser where name='asd';
+----+-------------+-------+------+------------------+------------------+----
-----+-------+------+-----------------------+
| id | select_type | table | type | possible_keys| key |key_len | ref   | rows | Extra  |
+----+-------------+-------+------+------------------+------------------+----
-----+-------+------+-----------------------+
|  1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 303| const |1 | Using index condition |
+----+-------------+-------+------+------------------+------------------+----
-----+-------+------+-----------------------+

索引失效分析

1
2
3
4
5
6
7
8
9
10
11
12
13
 NAME VARCHAR (24) NOT NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
 
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',24,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

全值匹配我最爱

1
2
3
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

image-20200323210732160

最佳左前缀法则

带头索引不能死，中间索引不能断

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

1
2
3
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

错误示例

带头索引死

image-20200323211334458

image-20200323211410258

不在索引列上做计算

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

1
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

image-20200323211643978

范围条件右边的列失效

不能继续使用索引中范围条件（between、<、>、in等）右边的列

范围条件能够使用，但是范围条件右边的索引不能使用

尽量使用覆盖索引

尽量使用覆盖索引（索引列和查询列一致），减少select *

image-20200323212442545

索引字段上不要使用不等

索引字段上使用（!=或<>）判断时，会导致索引失效而转向全表扫描

image-20200323214550547

索引字段上不要判断null

索引字段上使用is null/is not null判断使，会导致索引失效而转向全表扫描

image-20200323214851581

索引字段使用like不以通配符开头

索引字段使用like以通配符开头（’%字符串’）时，会导致索引失效而转向全表扫描

image-20200323215836756

由结果可知，like以通配符结束相当于范围查找，索引不会失效。与范围条件（bettween、<、>、in等）不同的是，不会导致右边的索引失效

面试题：解决like '%字符串%'时索引失效问题的方法？在select后使用覆盖索引可以解决

image-20200323222622993

索引字段字符串要加单引号

索引字段是字符串，但是查询时候不加单引号，会导致索引失效而转向全表扫描

image-20200324075557462

索引字段不要使用or

索引字段使用or时，会导致索引失效而转向全表扫描

image-20200324080400651

总结

假设index(a,b,c)

Where语句 索引是否被使用
where a = 3 Y,使用到a
where a = 3 and b = 5 Y,使用到a，b
where a = 3 and b = 5 and c = 4 Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N
where a = 3 and c = 5 使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5 使用到a和b， c不能用在范围之后，b断了
where a = 3 and b like ‘kk%’ and c = 4 Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4 Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4 Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4 Y,使用到a,b,c

优化总结口诀

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写*(星)；

不等空值还有OR，索引影响要注意；

VAR引号不可丢， SQL优化有诀窍。

本文标题:

文章作者:爱吃鱼的呆先生

发布时间:2020-03-21, 20:47:14

最后更新:2020-03-25, 09:58:42

原始链接:http://yoursite.com/2020/03/21/8MySQL/2MySQL/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. MySQL架构篇
    1. 1.1. 大厂面试问题
    2. 1.2. 逻辑架构
      1. 1.2.1. 架构逻辑图
      2. 1.2.2. 存储引擎（Pluggable Storage Engines）
        1. 1.2.2.1. InnoDB和MyISAM存储引擎区别
      3. 1.2.3. 存储引擎的选型
      4. 1.2.4. 简版执行流程图
      5. 1.2.5. 详细执行流程图
    3. 1.3. 物理架构
      1. 1.3.1. 数据文件
        1. 1.3.1.1. InnoDB数据文件
        2. 1.3.1.2. MyISAM数据文件
      2. 1.3.2. 日志文件
        1. 1.3.2.1. 错误日志（errorlog）
        2. 1.3.2.2. 二进制日志（bin log）
        3. 1.3.2.3. 通用查询日志（general query log）
        4. 1.3.2.4. 慢查询日志（slow query log）
        5. 1.3.2.5. 重做日志（redo log）
        6. 1.3.2.6. 回滚日志（undo log）
        7. 1.3.2.7. 中继日志（relay log）
  2. 2. MySQL索引篇
    1. 2.1. 索引介绍
      1. 2.1.1. 索引是什么
    2. 2.2. 索引的优势和劣势
    3. 2.3. 索引的分类
      1. 2.3.1. 在高性能mysql中
      2. 2.3.2. 在一般的教学中
    4. 2.4. 索引的使用
      1. 2.4.0.1. 删除索引
      2. 2.4.0.2. 查看索引
  3. 2.5. 索引原理分析
    1. 2.5.1. 索引的本质
    2. 2.5.2. 索引的存储结构
    3. 2.5.3. B-Tree和B+Tree
      1. 2.5.3.1. B-Tree（B树）
      2. 2.5.3.2. B+Tree（B+树）
    4. 2.5.4. B和B+的区别
  4. 2.6. 聚集索引和非聚集索引
    1. 2.6.1. 聚集索引（InnoDB）
      1. 2.6.1.1. 主键索引
      2. 2.6.1.2. 辅助索引（次要索引）
    2. 2.6.2. 非聚集索引（MyISAM）
      1. 2.6.2.1. 主键索引
      2. 2.6.2.2. 辅助索引（次要索引）
    3. 2.6.3. 补充
  5. 2.7. 联合索引的存储结构
  6. 2.8. 联合索引的查找方式
  7. 2.9. 最左前缀匹配原则
  8. 2.10. 索引使用场景
    1. 2.10.1. 哪些情况需要创建索引
    2. 2.10.2. 哪些情况不需要创建索引
    3. 2.10.3. 为什么使用组合索引
    4. 2.10.4. 组合索引的使用
  9. 2.11. 索引失效
    1. 2.11.1. 查看执行计划
    2. 2.11.2. 介绍
    3. 2.11.3. 参数说明
    4. 2.11.4. id
    5. 2.11.5. select_type（重要）
      1. 2.11.5.1. simple
      2. 2.11.5.2. primary
      3. 2.11.5.3. union
      4. 2.11.5.4. dependent union
      5. 2.11.5.5. union result
      6. 2.11.5.6. subquery
      7. 2.11.5.7. dependent subquery
      8. 2.11.5.8. derived
    6. 2.11.6. table
    7. 2.11.7. type（重要）
      1. 2.11.7.1. 注意事项
      2. 2.11.7.2. system
      3. 2.11.7.3. const（重要）
      4. 2.11.7.4. eq_ref（重要）
      5. 2.11.7.5. ref（重要）
      6. 2.11.7.6. fulltext
      7. 2.11.7.7. ref_or_null
      8. 2.11.7.8. unique_subquery
      9. 2.11.7.9. index_subquery
      10. 2.11.7.10. range（重要）
      11. 2.11.7.11. index_merge
      12. 2.11.7.12. index（重要）
      13. 2.11.7.13. all（重要）
    8. 2.11.8. possible_keys
    9. 2.11.9. key
    10. 2.11.10. key_len
    11. 2.11.11. ref
    12. 2.11.12. rows
    13. 2.11.13. extra（重要）
      1. 2.11.13.1. using filesort（重要）
      2. 2.11.13.2. using index（重要）
      3. 2.11.13.3. using index condition（重要）
        1. 2.11.13.3.1. 介绍
        2. 2.11.13.3.2. where条件分类
        3. 2.11.13.3.3. 不使用ICP扫描的过程
        4. 2.11.13.3.4. 使用ICP扫描的过程
        5. 2.11.13.3.5. 使用前后的成本差别
        6. 2.11.13.3.6. ICP 例子
      4. 2.11.13.4. using temporary
      5. 2.11.13.5. distinct
      6. 2.11.13.6. using where（重要）
  10. 2.12. 索引失效分析
    1. 2.12.1. 全值匹配我最爱
    2. 2.12.2. 最佳左前缀法则
      1. 2.12.2.1. 错误示例
    3. 2.12.3. 不在索引列上做计算
    4. 2.12.4. 范围条件右边的列失效
    5. 2.12.5. 尽量使用覆盖索引
    6. 2.12.6. 索引字段上不要使用不等
    7. 2.12.7. 索引字段上不要判断null
    8. 2.12.8. 索引字段使用like不以通配符开头
    9. 2.12.9. 索引字段字符串要加单引号
    10. 2.12.10. 索引字段不要使用or
    11. 2.12.11. 总结
|