mysql系列-第1章-从跟儿上理解MYSQL笔记而已
InnoDB
TCP/IP
真实环境中,数据库服务器进程和客户端进程可能运行在不同的主机中,它们之间必须通过网络来进行通讯。MySQL
采用TCP
作为服务器和客户端之间的网络通信协议。在网络环境下,每台计算机都有一个唯一的IP地址
,如果某个进程有需要采用TCP
协议进行网络通信方面的需求,可以向操作系统申请一个端口号
,这是一个整数值,它的取值范围是0~65535
。这样在网络中的其他进程就可以通过IP地址 + 端口号
的方式来与这个进程连接,这样进程之间就可以通过网络进行通信了。
一些重要的字符集
ASCII
字符集
共收录128个字符,包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符,所以可以使用1个字节来进行编码,我们看一些字符的编码方式:
1 | 'L' -> 01001100(十六进制:0x4C,十进制:76) |
ISO 8859-1
字符集
共收录256个字符,是在ASCII
字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母),也可以使用1个字节来进行编码。这个字符集也有一个别名latin1
。
GB2312
字符集
收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个,其他文字符号682个。同时这种字符集又兼容ASCII
字符集,所以在编码方式上显得有些奇怪:
- 如果该字符在
ASCII
字符集中,则采用1字节编码。 - 否则采用2字节编码。
这种表示一个字符需要的字节数可能不同的编码方式称为变长编码方式
。比方说字符串'爱u'
,其中'爱'
需要用2个字节进行编码,编码后的十六进制表示为0xB0AE
,'u'
需要用1个字节进行编码,编码后的十六进制表示为0x75
,所以拼合起来就是0xB0AE75
。
GBK
字符集
GBK
字符集只是在收录字符范围上对GB2312
字符集作了扩充,编码方式上兼容GB2312
。
utf8
字符集
收录地球上能想到的所有字符,而且还在不断扩充。这种字符集兼容ASCII
字符集,采用变长编码方式,编码一个字符需要使用1~4个字节,比方说这样:
1 | 'L' -> 01001100(十六进制:0x4C) |
对于同一个字符,不同字符集也可能有不同的编码方式。比如对于汉字'我'
来说,ASCII
字符集中根本没有收录这个字符,utf8
和gb2312
字符集对汉字我
的编码方式如下:
1 | utf8编码:111001101000100010010001 (3个字节,十六进制表示是:0xE68891) |
- InnoDB为了不同的目的而设计了不同类型的页,我们把用于存放记录的页叫做
数据页
。 - 一个数据页可以被大致划分为7个部分,分别是
File Header
,表示页的一些通用信息,占固定的38字节。Page Header
,表示数据页专有的一些信息,占固定的56个字节。Infimum + Supremum
,两个虚拟的伪记录,分别表示页中的最小和最大记录,占固定的26
个字节。User Records
:真实存储我们插入的记录的部分,大小不固定。Free Space
:页中尚未使用的部分,大小不确定。Page Directory
:页中的某些记录相对位置,也就是各个槽在页面中的地址偏移量,大小不固定,插入的记录越多,这个部分占用的空间越多。File Trailer
:用于检验页是否完整的部分,占用固定的8个字节。
- 每个记录的头信息中都有一个
next_record
属性,从而使页中的所有记录串联成一个单链表
。 InnoDB
会把页中的记录划分为若干个组,每个组的最后一个记录的地址偏移量作为一个槽
,存放在Page Directory
中,所以在一个页中根据主键查找记录是非常快的,分为两步:- 通过二分法确定该记录所在的槽。
- 通过记录的next_record属性遍历该槽所在的组中的各个记录。
- 每个数据页的
File Header
部分都有上一个和下一个页的编号,所以所有的数据页会组成一个双链表
。 - 为保证从内存中同步到磁盘的页的完整性,在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的
LSN
值,如果首部和尾部的校验和和LSN
值校验不成功的话,就说明同步过程出现了问题。
B+树索引
- 如果
B+
树只有1层,也就是只有1个用于存放用户记录的节点,最多能存放100
条记录。 - 如果
B+
树有2层,最多能存放1000×100=100000
条记录。 - 如果
B+
树有3层,最多能存放1000×1000×100=100000000
条记录。 - 如果
B+
树有4层,最多能存放1000×1000×1000×100=100000000000
条记录。哇咔咔~这么多的记录!!!
你的表里能存放100000000000
条记录么?所以一般情况下,我们用到的B+
树都不会超过4层,那我们通过主键值去查找某条记录最多只需要做4个页面内的查找(查找3个目录项页和一个用户记录页),又因为在每个页面内有所谓的Page Directory
(页目录),所以在页面内也可以通过二分法实现快速定位记录
聚簇索引
使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
- 页内的记录是按照主键的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
B+
树的叶子节点存储的是完整的用户记录。所谓完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。
我们把具有这两种特性的B+
树称为聚簇索引
,所有完整的用户记录都存放在这个聚簇索引
的叶子节点处。
在InnoDB
存储引擎中,聚簇索引
就是数据的存储方式(所有的用户记录都存储在了叶子节点
),也就是所谓的索引即数据,数据即索引。
二级索引
上边介绍的聚簇索引
只能在搜索条件是主键值时才能发挥作用,因为B+
树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢?难道只能从头到尾沿着链表依次遍历记录么?
不,我们可以多建几棵B+
树,不同的B+
树中的数据采用不同的排序规则。比方说我们用c2
列的大小作为数据页、页中记录的排序规则,再建一棵B+
树,效果如下图所示:
这个B+
树与上边介绍的聚簇索引有几处不同:
- 使用记录
c2
列的大小进行记录和页的排序,这包括三个方面的含义:- 页内的记录是按照
c2
列的大小顺序排成一个单向链表。 - 各个存放用户记录的页也是根据页中记录的
c2
列大小顺序排成一个双向链表。 - 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的
c2
列大小顺序排成一个双向链表。
- 页内的记录是按照
B+
树的叶子节点存储的并不是完整的用户记录,而只是c2列+主键
这两个列的值。- 目录项记录中不再是
主键+页号
的搭配,而变成了c2列+页号
的搭配。
所以如果我们现在想通过c2
列的值查找某些记录的话就可以使用我们刚刚建好的这个B+
树了。以查找c2
列的值为4
的记录为例,查找过程如下:
确定
目录项记录
页根据
根页面
,也就是页44
,可以快速定位到目录项记录
所在的页为页42
(因为2 < 4 < 9
)。通过
目录项记录
页确定用户记录真实所在的页。在
页42
中可以快速定位到实际存储用户记录的页,但是由于c2
列并没有唯一性约束,所以c2
列值为4
的记录可能分布在多个数据页中,又因为2 < 4 ≤ 4
,所以确定实际存储用户记录的页在页34
和页35
中。在真实存储用户记录的页中定位到具体的记录。
到
页34
和页35
中定位到具体的记录。但是这个
B+
树的叶子节点中的记录只存储了c2
和c1
(也就是主键
)两个列,所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。
看到步骤4的操作了么?我们根据这个以c2
列大小排序的B+
树只能确定我们要查找记录的主键值,所以如果我们想根据c2
列的值查找到完整的用户记录的话,仍然需要到聚簇索引
中再查一遍,这个过程也被称为回表
。也就是根据c2
列的值查询一条完整的用户记录需要使用到2
棵B+
树!!!
为什么我们还需要一次回表
操作呢?直接把完整的用户记录放到叶子节点
不就好了么?你说的对,如果把完整的用户记录放到叶子节点
是可以不用回表
,但是太占地方了呀~相当于每建立一棵B+
树都需要把所有的用户记录再都拷贝一遍,这就有点太浪费存储空间了。因为这种按照非主键列
建立的B+
树需要一次回表
操作才可以定位到完整的用户记录,所以这种B+
树也被称为二级索引
(英文名secondary index
),或者辅助索引
。由于我们使用的是c2
列的大小作为B+
树的排序规则,所以我们也称这个B+
树为为c2列建立的索引。
B+
树的形成过程是这样的:
- 每当为某个表创建一个
B+
树索引(聚簇索引不是人为创建的,默认就有)的时候,都会为这个索引创建一个根节点
页面。最开始表中没有数据的时候,每个B+
树索引对应的根节点
中既没有用户记录,也没有目录项记录。 - 随后向表中插入用户记录时,先把用户记录存储到这个
根节点
中。 - 当
根节点
中的可用空间用完时继续插入记录,此时会将根节点
中的所有记录复制到一个新分配的页,比如页a
中,然后对这个新页进行页分裂
的操作,得到另一个新页,比如页b
。这时新插入的记录根据键值(也就是聚簇索引中的主键值,二级索引中对应的索引列的值)的大小就会被分配到页a
或者页b
中,而根节点
便升级为存储目录项记录的页。
这个过程需要大家特别注意的是:一个B+树索引的根节点自诞生之日起,便不会再移动。这样只要我们对某个表建立一个索引,那么它的根节点
的页号便会被记录到某个地方,然后凡是InnoDB
存储引擎需要用到这个索引的时候,都会从那个固定的地方取出根节点
的页号,从而来访问这个索引。
B+树索引的使用
- 每个索引都对应一棵
B+
树,B+
树分为好多层,最下边一层是叶子节点,其余的是内节点。所有用户记录
都存储在B+
树的叶子节点,所有目录项记录
都存储在内节点。 InnoDB
存储引擎会自动为主键(如果没有它会自动帮我们添加)建立聚簇索引
,聚簇索引的叶子节点包含完整的用户记录。- 我们可以为自己感兴趣的列建立
二级索引
,二级索引
的叶子节点包含的用户记录由索引列 + 主键
组成,所以如果想通过二级索引
来查找完整的用户记录的话,需要通过回表
操作,也就是在通过二级索引
找到主键值之后再到聚簇索引
中查找完整的用户记录。 B+
树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表,而且每个页内的记录(不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引
的话,则页面和记录先按照联合索引
前边的列排序,如果该列值相同,再按照联合索引
后边的列排序。- 通过索引查找记录是从
B+
树的根节点开始,一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory
(页目录),所以在这些页面中的查找非常快。
索引的代价
空间上的代价
这个是显而易见的,每建立一个索引都要为它建立一棵
B+
树,每一棵B+
树的每一个节点都是一个数据页,一个页默认会占用16KB
的存储空间,一棵很大的B+
树由许多数据页组成,那可是很大的一片存储空间呢。时间上的代价
每次对表中的数据进行增、删、改操作时,都需要去修改各个
B+
树索引。而且我们讲过,B+
树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录,还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行一些记录移位,页面分裂、页面回收啥的操作来维护好节点和记录的排序。
回表的代价
idx_name_birthday_phone_number
索引为例,看下边这个查询:
1 | SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow'; |
在使用idx_name_birthday_phone_number
索引进行查询时大致可以分为这两个步骤:
- 从索引
idx_name_birthday_phone_number
对应的B+
树中取出name
值在Asa
~Barlow
之间的用户记录。 - 由于索引
idx_name_birthday_phone_number
对应的B+
树用户记录中只包含name
、birthday
、phone_number
、id
这4个字段,而查询列表是*
,意味着要查询表中所有字段,也就是还要包括country
字段。这时需要把从上一步中获取到的每一条记录的id
字段都到聚簇索引对应的B+
树中找到完整的用户记录,也就是我们通常所说的回表
,然后把完整的用户记录返回给查询用户。
由于索引idx_name_birthday_phone_number
对应的B+
树中的记录首先会按照name
列的值进行排序,所以值在Asa
~Barlow
之间的记录在磁盘中的存储是相连的,集中分布在一个或几个数据页中,我们可以很快的把这些连着的记录从磁盘中读出来,这种读取方式我们也可以称为顺序I/O
。根据第1步中获取到的记录的id
字段的值可能并不相连,而在聚簇索引中记录是根据id
(也就是主键)的顺序排列的,所以根据这些并不连续的id
值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中,这样读取完整的用户记录可能要访问更多的数据页,这种读取方式我们也可以称为随机I/O
。一般情况下,顺序I/O比随机I/O的性能高很多,所以步骤1的执行可能很快,而步骤2就慢一些。所以这个使用索引idx_name_birthday_phone_number
的查询有这么两个特点:
- 会使用到两个
B+
树索引,一个二级索引,一个聚簇索引。 - 访问二级索引使用
顺序I/O
,访问聚簇索引使用随机I/O
。
需要回表的记录越多,使用二级索引的性能就越低,甚至让某些查询宁愿使用全表扫描也不使用二级索引
。比方说name
值在Asa
~Barlow
之间的用户记录数量占全部记录数量90%以上,那么如果使用idx_name_birthday_phone_number
索引的话,有90%多的id
值需要回表,这不是吃力不讨好么,还不如直接去扫描聚簇索引(也就是全表扫描)。
那什么时候采用全表扫描的方式,什么时候使用采用二级索引 + 回表
的方式去执行查询呢?这个就是传说中的查询优化器做的工作,查询优化器会事先对表中的记录计算一些统计数据,然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数,需要回表的记录数越多,就越倾向于使用全表扫描,反之倾向于使用二级索引 + 回表
的方式。当然优化器做的分析工作不仅仅是这么简单,但是大致上是个这个过程。一般情况下,限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表
的方式进行查询,因为回表的记录越少,性能提升就越高,比方说上边的查询可以改写成这样:
1 | SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10; |
覆盖索引
为了彻底告别回表
操作带来的性能损耗,我们建议:最好在查询列表里只包含索引列,比如这样:
1 | SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow' |
因为我们只查询name
, birthday
, phone_number
这三个索引列的值,所以在通过idx_name_birthday_phone_number
索引得到结果后就不必到聚簇索引
中再查找记录的剩余列,也就是country
列的值了,这样就省去了回表
操作带来的性能损耗。我们把这种只需要用到索引的查询方式称为索引覆盖
。排序操作也优先使用覆盖索引
的方式进行查询,比方说这个查询:
1 | SELECT name, birthday, phone_number FROM person_info ORDER BY name, birthday, phone_number; |
虽然这个查询中没有LIMIT
子句,但是采用了覆盖索引
,所以查询优化器就会直接使用idx_name_birthday_phone_number
索引进行排序而不需要回表操作了。
当然,如果业务需要查询出索引以外的列,那还是以保证业务需求为重。但是我们很不鼓励用*
号作为查询列表,最好把我们需要查询的列依次标明。
只为用于搜索、排序或分组的列创建索引
也就是说,只为出现在WHERE
子句中的列、连接子句中的连接列,或者出现在ORDER BY
或GROUP BY
子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了:
1 | SELECT birthday, country FROM person_name WHERE name = 'Ashburn'; |
像查询列表中的birthday
、country
这两个列就不需要建立索引,我们只需要为出现在WHERE
子句中的name
列创建索引就可以了。
考虑列的基数
列的基数
指的是某一列中不重复数据的个数,比方说某个列包含值2, 5, 8, 2, 5, 8, 2, 5, 8
,虽然有9
条记录,但该列的基数却是3
。也就是说,在记录行数一定的情况下,列的基数越大,该列中的值越分散,列的基数越小,该列中的值越集中。这个列的基数
指标非常重要,直接影响我们是否能有效的利用索引。假设某个列的基数为1
,也就是所有记录在该列中的值都一样,那为该列建立索引是没有用的,因为所有值都一样就无法排序,无法进行快速查找了~ 而且如果某个建立了二级索引的列的重复值特别多,那么使用这个二级索引查出的记录还可能要做回表操作,这样性能损耗就更大了。所以结论就是:最好为那些列的基数大的列建立索引,为基数太小列的建立索引效果可能不好。
索引列的类型尽量小
我们在定义表结构的时候要显式的指定列的类型,以整数类型为例,有TINYINT
、MEDIUMINT
、INT
、BIGINT
这么几种,它们占用的存储空间依次递增,我们这里所说的类型大小
指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增,如果我们想要对某个整数列建立索引的话,在表示的整数范围允许的情况下,尽量让索引列使用较小的类型,比如我们能使用INT
就不要使用BIGINT
,能使用MEDIUMINT
就不要使用INT
~ 这是因为:
- 数据类型越小,在查询时进行的比较操作越快(这是CPU层次的东东)
- 数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录,从而减少磁盘
I/O
带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。
这个建议对于表的主键来说更加适用,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键适用更小的数据类型,也就意味着节省更多的存储空间和更高效的I/O
。
索引字符串值的前缀
我们知道一个字符串其实是由若干个字符组成,如果我们在MySQL
中使用utf8
字符集去存储字符串的话,编码一个字符需要占用1~3
个字节。假设我们的字符串很长,那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时,那就意味着在对应的B+
树中有这么两个问题:
B+
树索引中的记录需要把该列的完整字符串存储起来,而且字符串越长,在索引中占用的存储空间越大。- 如果
B+
树索引中索引列存储的字符串很长,那在做字符串比较时会占用更多的时间。
我们前边儿说过索引列的字符串前缀其实也是排好序的,所以索引的设计者提出了个方案 — 只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置,但是能定位到相应前缀所在的位置,然后根据前缀相同的记录的主键值回表查询完整的字符串值,再对比就好了。这样只在B+
树中存储字符串的前几个字符的编码,既节约空间,又减少了字符串的比较时间,还大概能解决排序的问题,何乐而不为,比方说我们在建表语句中只对name
列的前10个字符进行索引可以这么写:
1 | CREATE TABLE person_info( |
name(10)
就表示在建立的B+
树索引中只保留记录的前10
个字符的编码,这种只索引字符串值的前缀的策略是我们非常鼓励的,尤其是在字符串类型能存储的字符比较多的时候。
主键插入顺序
我们知道,对于一个使用InnoDB
存储引擎的表来说,在我们没有显式的创建索引时,表中的数据实际上都是存储在聚簇索引
的叶子节点的。而记录又是存储在数据页中的,数据页和记录又是按照记录主键值从小到大的顺序进行排序,所以如果我们插入的记录的主键值是依次增大的话,那我们每插满一个数据页就换到下一个数据页继续插,而如果我们插入的主键值忽大忽小的话,这就比较麻烦了,假设某个数据页存储的记录已经满了,它存储的主键值在1~100
之间:如果此时再插入一条主键值为9
的记录, 可这个数据页已经满了啊,再插进来咋办呢?我们需要把当前页面分裂成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着:性能损耗!所以如果我们想尽量避免这样无谓的性能损耗,最好让插入的记录的主键值依次递增,这样就不会发生这样的性能损耗了。所以我们建议:让主键具有AUTO_INCREMENT
,让存储引擎自己为表生成主键,而不是我们手动插入
B+
树索引在空间和时间上都有代价,所以没事儿别瞎建索引。B+
树索引适用于下边这些情况:- 全值匹配
- 匹配左边的列
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
- 用于排序
- 用于分组
- 在使用索引时需要注意下边这些事项:
- 只为用于搜索、排序或分组的列创建索引
- 为列的基数大的列创建索引
- 索引列的类型尽量小
- 可以只对字符串值的前缀建立索引
- 只有索引列在比较表达式中单独出现才可以适用索引
- 为了尽可能少的让
聚簇索引
发生页面分裂和记录移位的情况,建议让主键拥有AUTO_INCREMENT
属性。 - 定位并删除表中的重复和冗余索引
- 尽量使用
覆盖索引
进行查询,避免回表
带来的性能损耗。