[笔记]从根儿上理解 MySQL

Scroll Down

最近在跟着 掘金小册:MySQL 是怎样运行的:从根儿上理解 MySQL 重温Mysql,写的很好,再次结合自己理解,做下笔记,希望没有侵权。

1.启动选项

defaults-extra-filedefaults-file的区别:使用defaults-extra-file可以指定额外的配置文件搜索路径(也就是说那些固定的配置文件路径也会被搜索)。而defaults-file代表只在指定的路径下搜索。

2.设置系统变量

SET [GLOBAL|SESSION] 系统变量名 = 值;  
<=等价于=>  
SET [@@(GLOBAL|SESSION).]var_name = XXX;  
# 例如:
SET SESSION default_storage_engine = MyISAM;  
SET @@SESSION.default_storage_engine = MyISAM;  
SET default_storage_engine = MyISAM;

# 设置系统变量时,默认的作用范围就是 SESSION。
SET 系统变量名 = 值  
<=等价于=>  
SET SESSION 系统变量名 = 值

# 同理,SHOW VARIABLES 语句默认查看的系统变量的作用范围也是 SESSION。例如:
SHOW SESSION VARIABLES LIKE 'default_storage_engine';  

如果某个客户端改变了某个系统变量在GLOBAL作用范围的值,并不会影响该系统变量在当前已经连接的客户端作用范围为SESSION的值,只会影响后续连入的客户端在作用范围为SESSION的值。

特殊的:

  • 有的变量只有SESSION 作用域,比如 insert_id。
  • 有的变量只有GLOBAL 作用域,比如 max_connections。
  • 还有的系统变量是只读的,并不能设置值,比如 version。
  • 状态变量:用来显示mysql运行的状况,只能由mysql服务自己来设置。

3.字符集

  • utf8mb3:阉割过的utf8字符集,只使用1~3个字节表示字符。(MySql中 utf8 是 utf8mb3 的别名)
  • utf8mb4:正宗的utf8字符集,使用1~4个字节表示字符。
  • 如果创建或修改时没有显式的指定字符集和比较规则,则该默认用的字符集和比较规则
  • 如果创建或修改时没有显式的指定字符集和比较规则,则该默认用的字符集和比较规则
  • 如果创建或修改时没有显式的指定字符集和比较规则,则该默认用服务器的字符集和比较规则

字符集和比较规则是互相有联系的:

  • 只修改字符集,则比较规则将变为修改后的字符集默认的比较规则。
  • 只修改比较规则,则字符集将变为修改后的比较规则对应的字符集。

在转换列的字符集时需要注意,如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。
比方说原先列使用的字符集是utf8,列中存储了一些汉字,现在把列的字符集转换为ascii的话就会出错,因为ascii字符集并不能表示汉字字符。

从客户端发送sql请求到mysql服务返回结果,这个过程中伴随着多次字符集的转换。

我们通常都把 character_set_client(服务器解码请求时使用的字符集) 、character_set_connection(服务器处理请求时会把请求字符串从charactersetclient转为charactersetconnection)、character_set_results(服务器向客户端返回数据时使用的字符集)这三个系统变量设置成和客户端使用的字符集一致的情况,这样减少了很多无谓的字符集转换。

MySQL提供了一条非常简便的语句:

SET NAMES 字符集名;  
<=等价于=>  
SET character_set_client = 字符集名;  
SET character_set_connection = 字符集名;  
SET character_set_results = 字符集名;  

或者在配置文件中指定:

[client]
default-character-set=utf8  

如果你在使用 MySQL 或 MariaDB,不要使用用“utf8”编码,改用“utf8mb4”。这里(https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4)提供了一个指南用于将现有数据库的字符编码从“utf8”转成“utf8mb4”。

4.InnoDB 记录结构

InnoDB表对主键的生成策略:
优先使用用户自定义主键作为主键,如果用户没有定义主键,则选取一个Unique键作为主键,如果表中连Unique键都没有定义的话,则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

VARCHAR(M) 最多能存储的数据

VARCHAR(M)类型的列最多可以占用65535个字节,MySQL对一条记录占用的最大存储空间是有限制的,除了BLOB或者TEXT类型的列之外,其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过65535个字节。

这个65535个字节除了列本身的数据之外,还包括一些其他的数据(storage overhead),比如说我们为了存储一个VARCHAR(M)类型的列,其实需要占用 3 部分存储空间:

  • 真实数据
  • 真实数据占用字节的长度
  • NULL值标识,如果该列有NOT NULL属性则可以没有这部分存储空间

如果该VARCHAR类型的列没有NOT NULL属性,那最多只能存储65532个字节的数据,因为真实数据的长度可能占用2个字节,NULL值标识需要占用1个字节;如果VARCHAR类型的列有NOT NULL属性,那最多只能存储65533个字节的数据,因为真实数据的长度可能占用2个字节,不需要NULL值标识。

VARCHAR(M)中M最大取值

如果VARCHAR(M)类型的列使用的不是 ASCII 字符集(一个字符就代表一个字节),那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下:

  • gbk字符集表示一个字符最多需要2个字节
    那在该字符集下,M的最大取值就是 32766(也就是:65532/2),也就是说最多能存储32766个字符;
  • utf8字符集表示一个字符最多需要3个字节
    那在该字符集下,M的最大取值就是 21844(也就是:65532/3),就是说最多能存储21844个字符。

上述所言在列的值允许为NULL的情况下,gbk字符集下M的最大取值就是32766,utf8字符集下M的最大取值就是21844,这都是在表中只有一个字段的情况下说的,一定要记住一个行中的所有列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过65535个字节

行溢出数据

对于Compact和Reduntant行格式来说,如果某一列中的数据非常多的话,在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址(MySQL是以页为基本单位来管理存储空间的,记录会被分配到某个页中存储,而InnoDB一个页的大小一般是 16KB,也就是16384字节),然后把剩下的数据存放到其他页中,这个过程也叫做行溢出,存储超出768字节的那些页面也被称为溢出页

不只是 VARCHAR(M) 类型的列,其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生行溢出。

行溢出的临界点

MySQL中规定一个页中至少存放两行记录,至于为什么这么规定我们之后再说,现在看一下这个规定造成的影响。

先分析一下页中的空间都是如何利用的:

  • 每个页除了存放我们的记录以外,也需要存储一些额外的信息。
    乱七八糟的额外信息加起来需要136个字节的空间(现在只要知道这个数字就好了),其他的空间都可以被用来存储记录。
  • 每个记录需要的额外信息是27字节。
    这27个字节包括下边这些部分:
    • 2个字节用于存储真实数据的长度
    • 1个字节用于存储列是否是NULL值
    • 5个字节大小的头信息
    • 6个字节的rowid列
    • 6个字节的transactionid列
    • 7个字节的roll_pointer列

其实重点就:不用关注这个临界点是什么,只要知道如果我们向一个行中存储了很大的数据时,可能发生行溢出的现象。

Dynamic和Compressed行格式

MySQL 5.7 默认的行格式就是Dynamic,这俩行格式和Compact行格式挺像,只不过在处理行溢出数据时有点儿分歧,它们不会在记录的真实数据处存储字段真实数据的前768个字节,而是把所有的字节都存储到其他页面中,只在记录的真实数据处存储其他页面的地址。

Compressed行格式和Dynamic不同的一点是,Compressed行格式会采用压缩算法对页面进行压缩,以节省空间。

5.InnoDB 数据页(索引页)结构

记录头信息的秘密

delete_mask 属性:

这个属性标记着当前记录是否被删除,占用1个二进制位,值为0的时候代表记录并没有被删除,为1的时候代表记录被删除掉了。

next_record 属性:

不论我们怎么对页中的记录做增删改操作,InnoDB 始终会维护一条记录的单链表,链表中的各个节点是按照主键值由小到大的顺序连接起来的(通过next_record指向)。

InnoDB 会自动给每个页里边加两记录,由于这两个记录并不是我们自己插入的,所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录(Infimum记录),一个代表最大记录(Supremum记录)。最小记录的下一条记录就本页中主键值最小的用户记录,而本页中主键值最大的用户记录的下一条记录就是最大记录。

next_record属性会指向下一条记录的 记录头信息和真实数据之间的位置(这个位置刚刚好,向左读取就是记录头信息[逆序存储],向右读取就是真实数据)。

InnoDB并不会因为新记录的插入而为它申请新的存储空间,而是直接复用了原来被删除记录的存储空间(如果有的话)。

当数据页中存在多条被删除掉的记录时,这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表,以备之后重用这部分存储空间。

各个数据页可以组成一个双向链表。

6.B+树索引

B+树的叶子节点存储的是完整的用户记录(存储了所有列的值(包括隐藏列))。

B树和B+树的区别:B+树的非叶子结点只包含导航信息,不包含实际的值,所有的叶子结点和相连的节点使用链表相连,便于区间查找和遍历。 优点:

  • 由于B+树在内部节点上不包含数据信息,因此在内存页中能够存放更多的key。
    数据存放的更加紧密,具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
  • B+树的叶子结点都是相连的,因此对整棵树的遍历只需要一次线性遍历叶子结点即可。
    而且由于数据顺序排列并且相连,所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻,所以缓存命中性没有B+树好。

不论是存放用户记录的数据页,还是存放目录项记录的数据页,我们都把它们存放到B+树这个数据结构中了,所以我们也称这些数据页为节点。我们的实际用户记录其实都存放在B+树的最底层的节点上,这些节点也被称为叶子节点叶节点,其余用来存放目录项的节点称为非叶子节点或者内节点(目录项记录着主键+页号的搭配),其中B+树最上边的那个节点也称为根节点

一般情况下,我们用到的B+树都不会超过4层。

聚簇索引

  • 1.使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
    • 页内的记录是按照主键的大小顺序排成一个单向链表。
    • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
  • 2.B+树的叶子节点存储的是完整的用户记录。

具有这两种特性的B+树称为聚簇索引,所有完整的用户记录都存放在这个聚簇索引的叶子节点处。InnoDB存储引擎会自动的为我们创建聚簇索引。

另外,在InnoDB存储引擎中,聚簇索引就是数据的存储方式(所有的用户记录都存储在了叶子节点),也就是所谓的索引即数据,数据即索引

二级索引

二级索引的B+树只能确定我们要查找记录的主键值,所以如果我们想根据索引列的值查找到完整的用户记录的话,仍然需要到聚簇索引中再查一遍,这个过程也被称为回表(如果像主键一样设计会太占用空间)。也就是根据索引列的值查询一条完整的用户记录需要使用到2棵B+树!!!

二级索引的B+树的叶子节点存储的并不是完整的用户记录,而只是索引列+主键这两个列的值。目录项记录中不再是主键+页号的搭配,而变成了索引列+页号的搭配。

这种按照索引列建立的B+树需要一次回表操作才可以定位到完整的用户记录,所以这种B+树也被称为二级索引(英文名secondary index),或者辅助索引

联合索引

此外,还可以对多个列建立联合索引(1棵B+树)。

InnoDB的B+树索引的注意事项

  • 根页面万年不动窝

    一个B+树索引的根节点自诞生之日起,便不会再移动。这样只要我们对某个表建立一个索引,那么它的根节点的页号便会被记录到某个地方,然后凡是InnoDB存储引擎需要用到这个索引的时候,都会从那个固定的地方取出根节点的页号,从而来访问这个索引。

  • 内节点中目录项记录的唯一性

  • 一个页面最少存储2条记录

MyISAM中的索引方案

MyISAM的索引方案虽然也使用树形结构,但是却将索引和数据分开存储:

  • 将表中的记录按照记录的插入顺序单独存储在一个文件中,称之为数据文件(不划分数据页)。
  • 把索引信息另外存储到另一个称为索引文件的文件中。
  • MyISAM会单独为表的主键创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是主键值 + 行号的组合。
    也就是先通过索引找到对应的行号,再通过行号去找对应的记录!
    这一点和InnoDB是完全不相同的,在InnoDB存储引擎中,我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录,而在MyISAM中却需要进行一次回表操作,意味着MyISAM中建立的索引相当于全部都是二级索引
  • 也可以对其它的列分别建立索引或者建立联合索引,原理和InnoDB中的索引差不多,不过在叶子节点处存储的是相应的列 + 行号。这些索引也全部都是二级索引。

也就是所谓的:索引是索引、数据是数据

InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去建立B+树索引,但是如果我们想为其他的列建立索引就需要我们显式的去指明(如果自动为每个列都建立索引,会很费性能和存储空间)。

7.B+树索引的使用

B+树索引适用于下边这些情况:

  • 全值匹配
  • 匹配左边的列
  • 匹配范围值
  • 精确匹配某一列并范围匹配另外一列
  • 用于排序
  • 用于分组

索引的代价

  • 空间上的代价

这个是显而易见的,每建立一个索引都为要它建立一棵B+树,每一棵B+树的每一个节点都是一个数据页,一个页默认会占用16KB的存储空间,一棵很大的B+树会由许多数据页组成。。

  • 时间上的代价

每次对表中的数据进行增、删、改操作时,都需要去修改各个B+树索引。而且我们讲过,B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录,还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。
而增、删、改操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行一些记录移位,页面分裂、页面回收啥的操作来维护好节点和记录的排序。如果我们建了许多索引,每个索引对应的B+树都要进行相关的维护操作。

所以说,一个表上索引建的越多,就会占用越多的存储空间,在增删改记录的时候性能就越差。
阿里巴巴 Java 开发手册中,建议单张表索引不超过5个

联合索引查询注意事项

  • 如果我们想使用联合索引中尽可能多的列,搜索条件中的各个列必须是联合索引中从最左边连续的列。

  • 在使用联合索引进行范围查找的时候需要注意,如果对多个列同时进行范围查找的话,只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引。

  • 对于同一个联合索引来说,虽然对多个列都进行范围查找时只能用到最左边那个索引列,但是如果左边的列是精确查找,则右边的列可以进行范围查找。

联合索引排序注意事项

在MySQL中,把在内存中或者磁盘上进行排序的方式统称为文件排序(filesort),一般就比较慢了,但是如果 ORDER BY 子句里使用到了我们的索引列,就有可能省去在内存或文件中排序的步骤。

  • ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出。
  • 使用联合索引的各个排序列的排序顺序必须是一致的(不能ASC、DESC混用)。
  • 匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引左边列的值为常量,也可以使用后边的列进行排序。

无法使用索引进行排序的几种情况

  • ASC、DESC混用
  • WHERE子句中出现非排序使用到的索引列
  • 排序列包含非同一个(联合)索引的列
  • 排序列使用了复杂的表达式

用于分组

和使用B+树索引进行排序是一个道理,分组列的顺序也需要和索引列的顺序一致,也可以只使用索引列中左边的列进行分组。

回表的代价

查询时需要回表的记录越多,使用二级索引的性能就越低,甚至让某些查询宁愿使用全表扫描也不使用二级索引

回表操作其实是一个随机IO,比较耗时!

查询优化器会事先对表中的记录计算一些统计数据,然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数,需要回表的记录数越多,就越倾向于使用全表扫描,反之倾向于使用二级索引 + 回表的方式。
当然优化器做的分析工作不仅仅是这么简单,但是大致上是个这个过程。一般情况下,限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询,因为回表的记录越少,性能提升就越高。

覆盖索引

只需要用到索引的查询方式可以省去回表操作带来的性能损耗,是为索引覆盖

如何挑选索引

  • 1.只为用于搜索、排序或分组的列创建索引
  • 2.考虑列的基数(可选择性): 最好为那些列的基数大的列建立索引,为基数太小列的建立索引效果可能不好。

列的基数指的是某一列中不重复数据的个数。在记录行数一定的情况下,列的基数越大,该列中的值越分散,列的基数越小,该列中的值越集中。

  • 3.索引列的类型尽量小(该类型表示的数据范围的大小)

    • 数据类型越小,在查询时进行的比较操作越快
    • 数据类型越小,索引占用的存储空间就越少。
      在一个数据页内就可以放下更多的记录,从而减少磁盘I/O带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。

      这个建议对于表的主键来说更加适用,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键使用更小的数据类型,也就意味着节省更多的存储空间和更高效的I/O。

  • 4.索引字符串值的前缀 (字符串类型前缀索引)

索引列前缀对排序的影响:
因为二级(前缀)索引中不包含完整的索引列信息,所以无法对前N个字符相同,后边的字符不同的记录进行排序,也就是使用索引列前缀的方式无法支持使用索引排序,只能使用文件排序。

  • 5.让索引列在比较表达式中单独出现:
    如果索引列在比较表达式中不是以单独列的形式出现,而是以某个表达式,或者函数调用形式出现的话,是用不到索引的。
  • 6.主键插入顺序
    如果想尽量避免(页面分裂和记录移位)这样无谓的性能损耗,最好让插入的记录的主键值依次递增,这样就不会发生这样的性能损耗了。
  • 7.冗余和重复索引
    维护冗余/重复索引索引只会增加额外的维护成本,并不会对搜索有什么好处,应该避免。
  • 8.尽量使用覆盖索引进行查询,避免回表带来的性能损耗。

8.MySQL 的数据目录

表在文件系统中的表示

InnoDB和MyISAM这两种存储引擎都在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件,文件名形如:表名.frm

InnoDB是如何存储表数据的

系统表空间(system tablespace):
可以对应文件系统上一个或多个实际的文件,默认情况下,InnoDB会在数据目录下创建一个名为ibdata1,这个文件就是对应的系统表空间在文件系统上的表示。而且这个文件是所谓的自扩展文件,也就是当不够用的时候它会自动扩展文件大小。

需要注意的一点是,在一个MySQL服务器中,系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中,我们表中的数据都会被默认存储到这个系统表空间

独立表空间(file-per-table tablespace):
MySQL5.6.6以及之后的版本中,InnoDB并不会默认的把各个表的数据存储到系统表空间中,而是为每一个表建立一个独立表空间,也就是说我们创建了多少个表,就有多少个独立表空间。
使用独立表空间来存储表数据的话,会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件,文件名和表名相同,只不过添加了一个.ibd的扩展名而已,形如:表名.ibd

MyISAM是如何存储表数据的

在MyISAM中的索引全部都是二级索引,该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件索引文件
而且和InnoDB不同的是,MyISAM并没有什么所谓的表空间一说,表数据都存放到对应的数据库子目录下

新建一个表会创建三个文件:

表名.frm # 表结构文件
表名.MYD # 数据文件
表名.MYI # 索引文件

视图在文件系统中的表示

MySQL 中视图的本质就是查询语句的别名,所以在存储视图的时候是不需要存储真实的数据的,只需要把它的结构存储起来就行了。和表一样,描述视图结构的文件也会被存储到所属数据库对应的子目录下边,只会存储一个视图名.frm的文件。

文件系统对数据库的影响

  • 数据库名称和表名称不得超过文件系统所允许的最大长度
  • 特殊字符的问题

为了避免因为数据库名和表名出现某些特殊字符而造成文件系统不支持的情况,MySQL会把数据库名表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+编码值的形式作为文件名。比如:ghost@002dblog ==> ghost@002dblog

  • 数据文件大小受文件系统最大长度(大小)限制

MySQL系统数据库简介

  • mysql

这个数据库是核心,它存储了MySQL的用户账户和权限信息,一些存储过程、事件的定义信息,一些运行过程中产生的日志信息,一些帮助信息以及时区信息等。

  • information_schema

这个数据库保存着MySQL服务器维护的所有其他数据库的信息,比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等。这些信息并不是真实的用户数据,而是一些描述性信息,有时候也称之为元数据

  • performance_schema

这个数据库里主要保存MySQL服务器运行过程中的一些状态信息,算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句,在执行过程的每个阶段都花费了多长时间,内存的使用情况等等信息。

  • sys

这个数据库主要是通过视图的形式把information_schemaperformance_schema结合起来,让程序员可以更方便的了解MySQL服务器的一些性能信息

9.访问方法/访问类型

MySQL 查询的执行方式大致分为下边两种:

  • 使用全表扫描进行查询

  • 使用索引进行查询

    • 1.针对主键或唯一二级索引的等值查询
    • 2.针对普通二级索引的等值查询
    • 3.针对索引列的范围查询
    • 4.直接扫描整个索引

MySQL 执行查询语句的方式又称之为访问方法或者访问类型

const

通过主键或者唯一二级索引列与常数的等值比较来定位一条记录的访问方法定义为:const,意思是常数级别的,代价是可以忽略不计的。

如果主键或者唯一二级索引是由个列构成的话,索引中的每一个列都需要与常数进行等值比较,这个 const 访问方法才有效(这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录)。

唯一二级索引列并不限制 NULL 值的数量,所以唯一二级索引使用key IS NULL时,有可能访问到多条记录。但规定它的访问方法也是 const。

ref

普通的二级索引列与常数进行等值比较来定位一条记录的访问方法定义为:ref

对于普通二级索引而言,通过索引列进行等值比较之后可能匹配到多条连续的记录,又由于需要回表,所以效率比const差一丢丢(二级索引等值比较时匹配的记录数较少时的效率还是很高的)。

特殊情况:

  • 二级索引列值为NULL的情况 对于普通二级索引使用key IS NULL时,使用的方法是ref
  • 对于包含多个索引列的二级索引来说,只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法。

但是如果最左边的连续索引列并不全部是等值比较的话,访问方法就不能称为ref

refornull

对于普通二级索引使用key = 'xxx' or key IS NULL时,使用的方法是ref_or_null

先分别从 key 索引对应的 B+ 树中找出key IS NULLkey1 = 'xxx'的两个连续的记录范围,然后根据这些二级索引记录中的id(主键)值再回表查找完整的记录。

range

利用索引进行范围匹配(匹配某个或某些范围的值)的访问方法称之为:range

可以使聚簇索引,也可以是二级索引。
所谓范围,也就是区间。索引列等值匹配(比如 IN)的情况称之为单点区间,>,>=,<,<= 等这种称为连续范围区间

index

假设有一个联合索引KEY idx_key_part(key_part1, key_part2, key_part3),对于列key_part2并不是联合索引idx_key_part最左索引列,那么对列key_part2的查询就无法使用ref或者range访问方法来查询。

但是如果满足了下列两个条件:

  • 1.查询列表中只出现包含在(联合)索引中的列。
  • 2.搜索条件中只出现包含在(联合)索引中的列。

例如:

SELECT key_part1, key_part2, key_part3 FROM table WHERE key_part2 = 'xxx';  

也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = 'xxx'这个条件是否成立,把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。

由于二级索引记录比聚簇索记录小的多(聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列,而二级索引记录只需要存放索引列和主键),而且这个过程也不用进行回表操作,所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多。

这种采用遍历二级索引记录的执行方式(查询方法)称之为:index

all

顾名思义,即全表扫描,对于 InnoDB 引擎来说也就是直接扫描聚簇索引。这种使用全表扫描执行查询的方式称之为:all

注意事项

A.重温 二级索引 + 回表

一般情况下只能利用单个二级索引执行查询。(特殊情况可以看后文:索引合并index merge

优化器一般会根据表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少,选择那个扫描行数较少的条件到对应的二级索引中查询。然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根据其余的WHERE条件过滤记录。

一般来说,等值查找比范围查找需要扫描的行数更少(也就是ref的访问方法一般比range好,但这也不总是一定的,也可能采用ref访问方法的那个索引列的值为特定值的行数特别多)。

需要注意的是,我们说一般情况下执行一个查询只会用到单个二级索引,不过还是有特殊情况的。

B.明确 range 访问方法使用的范围区间

其实对于B+树索引来说,只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=、<>或者LIKE操作符连接起来,就可以产生一个所谓的区间

LIKE操作符比较特殊,只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引。
IN操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样的,也就是说会产生多个单点区间。

一个使用到索引的搜索条件和没有使用到索引的搜索条件使用OR连接起来后是无法使用该索引的。

索引合并

使用到多个索引来完成一次查询的执行方法称之为:index merge

Intersection 合并

Intersection直译就是交集,指某个查询可以使用多个二级索引,将从多个二级索引中查询到的结果取交集。MySQL在某些特定的情况下才可能会使用到Intersection索引合并:

  • 情况1:二级索引列是等值匹配的情况,对于联合索引来说,在联合索引中的每个列都必须等值匹配,不能出现只出现匹配部分列的情况。比如:
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';  

因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

  • 情况2:主键列可以是范围匹配,因为二级索引的记录中都带有主键值,可以直接匹配,无需回表。比如:
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';  

当然,查询条件符合了这些情况也不一定就会采用Intersection索引合并(必要非充分)。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多,导致回表开销太大,而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

按照有序的主键值去回表取记录有个专有名词儿,叫:Rowid Ordered Retrieval,简称ROR

Union合并

Intersection交集的意思,适用于使用不同索引的搜索条件之间使用AND连接起来的情况;
Union并集的意思,适用于使用不同索引的搜索条件之间使用OR连接起来的情况。
Intersection索引合并类似,MySQL在某些特定的情况下才可能会使用到Union索引合并:

  • 情况一:二级索引列是等值匹配的情况,对于联合索引来说,在联合索引中的每个列都必须等值匹配,不能出现只匹配部分列的情况。比如:
SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c');  
  • 情况二:主键列可以是范围匹配,比如:
SELECT * FROM single_table WHERE id > 100 OR key1 = 'a';  
  • 情况三:使用Intersection索引合并的搜索条件,就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集,比如:
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');  

当然,查询条件符合了这些情况也不一定就会采用Union索引合并,也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少,通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

Sort-Union合并

Union索引合并的使用条件太苛刻,必须保证各个二级索引列在进行等值匹配的条件下才可能被用到,比方说下边这个查询就无法使用到Union索引合并:

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'  

可以先按照二级索引记录的主键值进行排序,之后按照Union索引合并方式执行的方式称之为:Sort-Union索引合并,很显然,这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

为啥有Sort-Union索引合并,就没有Sort-Intersection索引合并么?是的,的确没有Sort-Intersection索引合并。
Sort-Union的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较,这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高;
Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多,导致回表开销太大,合并后可以明显降低回表开销。
但是如果加入Sort-Intersection后,就需要为大量的二级索引记录按照主键值进行排序,这个成本可能比回表查询都高了,所以也就没有引入Sort-Intersection这个玩意儿。

10.连接的原理

在两表连接查询中,驱动表(第一个需要查询的表)只需要访问一次,被驱动表可能被访问多次。

对于LEFT JOIN类型的连接来说,把放在左边的表称之为外表或者驱动表,右边的表称之为内表或者被驱动表RIGHT JOIN 类推。

内连接和外连接的根本区别就是:在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表,两表连接产生的笛卡尔积肯定是一样的。
而对于内连接来说,由于凡是不符合ON子句或WHERE子句中的条件的记录都会被过滤掉,其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去。
所以对于内连接来说:驱动表和被驱动表是可以互换的,并不会影响最后的查询结果。
但是对于外连接来说:由于驱动表中的记录即使在被驱动表中找不到符合ON子句连接条件的记录,也会加入结果集。所以此时驱动表和被驱动表的关系就很重要了,也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换

嵌套循环连接(Nested-Loop Join)

多表关联的过程就像是一个嵌套的循环,所以这种驱动表只访问一次,但被驱动表却可能被多次访问,访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为:嵌套循环连接(Nested-Loop Join),这是最简单,也是最笨拙的一种连接查询算法。

使用索引加快连接速度

在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为:eq_ref

有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列,而这些列都是某个索引的一部分,这种情况下即使不能使用eq_refrefref_or_null或者range这些访问方法执行对被驱动表的查询的话,也可以使用索引扫描,也就是index的访问方法来查询被驱动表。
所以我们建议在真实工作中最好不要使用*作为查询列表,最好把真实用到的列作为查询列表。

基于块的嵌套循环连接(Block Nested-Loop Join)

join buffer就是执行连接查询前申请的一块固定大小的内存,先把若干条驱动表结果集中的记录装在这个join buffer中,然后开始扫描被驱动表,每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配,因为匹配的过程都是在内存中完成的,所以这样可以显著减少被驱动表的I/O代价。

最好的情况是join buffer足够大,能容纳驱动表结果集中的所有记录,这样只需要访问一次被驱动表就可以完成连接操作了。这种加入了join buffer的嵌套循环连接算法称之为:基于块的嵌套连接(Block Nested-Loop Join)算法。

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置,默认大小为262144字节(也就是256KB),最小可以设置为128字节。

当然,对于优化被驱动表的查询来说,最好是为被驱动表加上效率高的索引,如果实在不能使用索引,并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

另外需要注意的是,驱动表的记录并不是所有列都会被放到join buffer中,只有查询列表中的列和过滤条件中的列才会被放到join buffer中,所以再次提醒我们,最好不要把*作为查询列表,只需要把我们关心的列放到查询列表就好了,这样还可以在join buffer中放置更多的记录!

11.基于成本的优化

什么是成本

  • I/O成本:
    我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引都存储到磁盘上的,当我们想查询表中的记录时,需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本

  • CPU成本:
    读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本

对于InnoDB存储引擎来说,页是磁盘和内存之间交互的基本单位,MySQL 规定读取一个页面花费的成本默认是1.0,读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为:成本常数

不管读取记录时需不需要检测是否满足搜索条件,其成本都算是0.2

12.基于规则的优化

MySQL 会依据一些规则,竭尽全力的把很糟糕的语句转换成某种可以比较高效执行的形式,这个过程也可以被称作:查询重写

条件化简

  • 移除不必要的括号
  • 常量传递(constant_propagation)(AND 才可以):
    a = 5 AND b > a --> a = 5 AND b > 5
  • 等值传递(equality_propagation):
    a = b and b = c and c = 5 --> a = 5 and b = 5 and c = 5
  • 移除没用的条件(trivialconditionremoval):
    对于一些明显永远为TRUE或者FALSE的表达式,优化器会移除掉它们
  • 表达式计算:
    在查询开始执行之前,如果表达式中只包含常量的话,它的值会被先计算出来。但是如果某个列并不是以单独的形式作为表达式的操作数时,比如出现在函数中,出现在某个更复杂表达式中时优化器不会对其做简化。
  • HAVING子句和WHERE子句的合并
  • 常量表检测:

外连接消除

外连接和内连接的本质区别就是:对于外连接的驱动表的记录来说,如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录,那么该记录仍然会被加入到结果集中,对应的被驱动表记录的各个字段使用 NULL 值填充;而内连接的驱动表的记录如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录,那么该记录会被舍弃。

把在外连接查询中,指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件称之为:空值拒绝(英文名:reject-NULL)。在被驱动表的WHERE子句符合空值拒绝的条件后,外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本,选出成本最低的那种连接顺序来执行查询。例如:

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;  
<==>  
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;  

子查询优化

1.按返回的结果集区分子查询可分为:

  • 标量子查询:只返回一个单一值的子查询
  • 行子查询:返回一条记录的子查询(包含多个列)
  • 列子查询:查询出一个列的数据(包含多条记录)
  • 表子查询:子查询的结果既包含很多条记录,又包含很多个列

2.按与外层查询关系来区分子查询可分为:

  • 不相关子查询:子查询可以单独运行出结果,而不依赖于外层查询的值
  • 相关子查询:子查询的执行需要依赖于外层查询的值

3.子查询在布尔表达式中的使用:

  • 子查询可以使用=、>、<、>=、<=、<>、!=、<=>作为布尔表达式的操作符
  • [NOT] IN/ANY/SOME/ALL也可以用于子查询
  • EXISTS/NOT EXISTS子查询

4.子查询语法注意事项:

  • 子查询必须用小括号扩起来
  • SELECT子句中的子查询必须是标量子查询
  • 在想要得到标量子查询或者行子查询,但又不能保证子查询的结果集只有一条记录时,应该使用LIMIT 1语句来限制记录数量
  • 对于[NOT] IN/ANY/SOME/ALL子查询来说,子查询中不允许LIMIT语句

因此:这种子查询中的ORDER BY、DISTINCT、GROUP BY这些语句也就是多余的了,优化器会自动干掉。

  • 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询
DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1);

ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause  

5.标量子查询、行子查询的执行方式:

对于包含不相关的标量子查询或者行子查询的查询语句来说,MySQL会分别独立的执行外层查询和子查询,就当作两个单表查询就好了。

6.IN子查询优化:

不直接将不相关子查询的结果集当作外层查询的参数,而是将该结果集写入一个临时表里:

  • 该临时表的列就是子查询结果集中的列。
  • 写入临时表的记录会被去重
  • 一般情况下子查询结果集不会大的离谱,所以会为它建立基于内存的使用Memory存储引擎的临时表,而且会为该表建立哈希索引

如果子查询的结果集非常大,超过了系统变量tmp_table_size或者max_heap_table_size,临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录,索引类型也对应转变为B+树索引。

MySQL 把这个将子查询结果集中的记录保存到临时表的过程称之为物化(英文名:Materialize)。
正因为物化表中的记录都建立了索引(基于内存的物化表有哈希索引,基于磁盘的有B+树索引),通过索引执行 IN 语句判断某个操作数在不在子查询结果集中变得非常快,从而提升了子查询语句的性能。

条件满足时物化表可以转连接。

7.将子查询转换为semi-join
semi-join只是在 MySQL 内部采用的一种执行子查询的方式,MySQL 并没有提供面向用户的semi-join语法。

SELECT * FROM s1  
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

==>

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

只有符合下边这些条件的子查询才可以被转换为semi-join

  • 该子查询必须是和IN语句组成的布尔表达式,并且在外层查询的WHERE或者ON子句中出现。
  • 外层查询也可以有其他的搜索条件,只不过和IN子查询的搜索条件必须使用AND连接起来。
  • 该子查询必须是一个单一的查询,不能是由若干查询由UNION连接起来的形式。
  • 该子查询不能包含GROUP BY或者HAVING语句或者聚集函数。

例如:

SELECT ... FROM outer_tables  
    WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

或者这样的形式也可以:

SELECT ... FROM outer_tables  
    WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...

不适用于semi-join的情况:

  • 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来
  • 使用NOT IN而不是IN的情况
  • 在SELECT子句中的IN子查询的情况
  • 子查询中包含GROUP BY、HAVING或者聚集函数的情况
  • 子查询中包含UNION的情况

13.Explain 详解

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划,这个执行计划展示了接下来具体执行查询的方式。
MySQL 提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划。

EXPLAIN 输出的各个列:

  • id: 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
  • select_type: SELECT关键字对应的那个查询的类型
  • table: 表名
  • partitions: 匹配的分区信息
  • type: 针对单表的访问方法
  • possible_keys: 可能用到的索引
  • key: 实际上使用的索引
  • key_len: 实际使用到的索引长度
  • ref: 当使用索引列等值查询时,与索引列进行等值匹配的对象信息
  • rows: 预估的需要读取的记录条数
  • filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
  • Extra: 一些额外的信息

执行计划输出中各列详解

table

EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法,该条记录的table列代表着该表的表名

id

查询语句中每出现一个SELECT关键字,设计 MySQL 的大叔就会为它分配一个唯一的id值。这个 id 值就是 EXPLAIN 语句的第一个列。

连接查询的执行计划中,每个表都会对应一条记录,这些记录的 id 列的值是相同的,出现在前边的表表示驱动表,出现在后边的表表示被驱动表

对于包含子查询的查询语句来说,就可能涉及多个 SELECT 关键字,所以在包含子查询的查询语句的执行计划中,每个SELECT关键字都会对应一个唯一的id值

查询优化器可能对涉及子查询的查询语句进行重写,从而转换为连接查询,从 EXPLAIN 中可以分析出来。

使用UNION语法时,EXPLAIN 后最后一行会出现 id 为NULL的结果,表明这个是临时表,是为了合并两(多)个查询的结果集而创建的(为了将结果集合起来并去重)。而UNION ALL就不需要为最终的结果集进行去重(也不需要临时表)。

select_type

每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性,只要知道了某个小查询的select_type属性,就知道了这个小查询在整个大查询中扮演了一个什么角色。

  • SIMPLE:查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型。
  • PRIMARY:对于包含UNION、UNION ALL或者子查询的大查询来说,它是由几个小查询组成的,其中最左边的那个查询的select_type值就是PRIMARY
  • UNION:对于包含UNION或者UNION ALL的大查询来说,它是由几个小查询组成的,其中除了最左边的那个小查询以外,其余的小查询的select_type值就是UNION
  • UNION RESULT:选择使用临时表来完成 UNION 查询的去重工作,针对该临时表的查询的select_type就是UNION RESULT
  • SUBQUERY:如果包含子查询的查询语句不能够转为对应的semi-join的形式,并且该子查询是不相关子查询,并且查询优化器决定采用将该子查询物化的方案来执行该子查询时,该子查询的第一个 SELECT 关键字代表的那个查询的select_type就是SUBQUERY(由于SUBQUERY的子查询会被物化,所以只需要执行一遍)。
  • DEPENDENT SUBQUERY:如果包含子查询的查询语句不能够转为对应的semi-join的形式,并且该子查询是相关子查询,则该子查询的第一个 SELECT 关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY(DEPENDENT SUBQUERY 的查询可能会被执行多次)。
  • DEPENDENT UNION:在包含UNION或者UNION ALL的大查询中,如果各个小查询都依赖于外层查询的话,那除了最左边的那个小查询之外,其余的小查询的select_type的值就是DEPENDENT UNION
  • DERIVED:对于采用物化的方式执行的包含派生表的查询,该派生表对应的子查询的select_type就是DERIVED
  • MATERIALIZED:当查询优化器在执行包含子查询的语句时,选择将子查询物化之后与外层查询进行连接查询时,该子查询对应的select_type属性就是MATERIALIZED
  • UNCACHEABLE SUBQUERY
  • UNCACHEABLE UNION

partitions

一般情况下我们的查询语句的执行计划的partitions列的值都是NULL

type

前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法,其中的type列就表明了这个访问方法是什么。

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

  • system:当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的,比如MyISAM、Memory,那么对该表的访问方法就是system
  • const:根据主键或者唯一二级索引列与常数进行等值匹配时,对单表的访问方法就是const
  • eq_ref:在连接查询时,如果被驱动表是通过主键或者唯一二级索引等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话,所有的索引列都必须进行等值比较),则对该被驱动表的访问方法就是eq_ref
  • ref:当通过普通的二级索引列与常量进行等值匹配时来查询某个表,那么对该表的访问方法就可能是ref
  • fulltext:全文索引。
  • ref_or_null:当对普通二级索引进行等值匹配查询,该索引列的值也可以是NULL值时,那么对该表的访问方法就可能是ref_or_null
  • index_merge:一般情况下对于某个表的查询只能使用到一个索引,但在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询。
  • unique_subquery:类似于两表连接中被驱动表的eq_ref访问方法,unique_subquery是针对在一些包含IN子查询的查询语句中,如果查询优化器决定将IN子查询转换为EXISTS子查询,而且子查询可以使用到主键进行等值匹配的话,那么该子查询执行计划的type列的值就是unique_subquery
  • index_subqueryindex_subqueryunique_subquery类似,只不过访问子查询中的表时使用的是普通的索引。
  • range:如果使用索引获取某些范围区间的记录,那么就可能使用到range访问方法。
  • index:当我们可以使用索引覆盖,但需要扫描全部的索引记录时,该表的访问方法就是index
  • ALL:全表扫描。

possible_keys 和 key

possible_keys列表示在某个查询语句中,对某个表执行单表查询时可能用到的索引有哪些,key列表示实际用到的索引有哪些

有一点特别的:就是在使用index访问方法来查询某个表时,possible_keys列是空的,而key列展示的是实际使用到的索引。

possible_keys列中的值并不是越多越好,可能使用的索引越多,查询优化器计算查询成本时就得花费更长时间,所以如果可以的话,尽量删除那些用不到的索引。

key_len

表示当优化器决定使用某个索引执行查询时,该索引记录的最大长度,它是由这三个部分构成的:

  • 1.对于使用固定长度类型的索引列来说,它实际占用的存储空间的最大长度就是该固定值,对于指定字符集的变长类型的索引列来说,比如某个索引列的类型是VARCHAR(100),使用的字符集是utf8,那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。
  • 2.如果该索引列可以存储NULL值,则key_len比不可以存储NULL值时多1个字节。
  • 3.对于变长字段来说,都会有2个字节的空间来存储该变长列的