MySQL知识梳理

编码

字符集

ASCII
GB2312
GBK
UTF-8

比较规则

存储引擎

InnoDB

简介

将数据划分为若干个页，以 页 作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。
当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为 行溢出 。

表结构

表空间 被划分为许多连续的 组 ，每个组由256个 区（extent） 组成，每个区默认由64个 页 组成，每页又包含了诸多 行 记录。

每个组最开始的几个页面类型是固定的。

页

1. InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做 数据页（索引页）。
2. 一个数据页可以被大致划分为7个部分，分别是：
   File Header ：表示页的一些通用信息，占固定的38字节。
   Page Header ：表示数据页专有的一些信息，占固定的56个字节。
   Infimum + Supremum ：两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的 26 个字节。
   User Records ：真实存储我们插入的记录的部分，大小不固定。
   Free Space ：页中尚未使用的部分，大小不确定。
   Page Directory ：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插 入的记录越多，这个部分占用的空间越多。
   File Trailer ：用于检验页是否完整的部分，占用固定的8个字节。
3. 每个记录的头信息中都有一个 next_record 属性，从而使页中的所有记录串联成一个 单链表 。
4. InnoDB 会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个 槽 ，存放在 Page Directory 中，所以在一个页中根据主键查找记录是非常快的，分为两步:
   通过二分法确定该记录所在的槽。
   通过记录的next_record属性遍历该槽所在的组中的各个记录。
5. 每个数据页的 File Header 部分都有上一个和下一个页的编号，所以所有的数据页会组成一个 双链表 。
   
6. 为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的 LSN 值，如果首部和尾部的校验和和 LSN 值校验不成功的话，就说明同步过程出现了问题。

行格式

Compact（紧凑 [kəmˈpækt]）

Redundant（冗余 [rɪˈdʌndənt]）

Dynamic（动态 [daɪˈnæmɪk]）和 Compressed（压缩 [kəmˈprest]）
类似于Compact行格式 。

区别：在处理行溢出数据时，Compact会在记录的真实数据处存储字符串的前768个字节，Dynamic和Compressed是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外，Compressed行格式会采用压缩算法对页面进行压缩。

索引

每个索引都对应一棵 B+树 。

索引的类型

聚簇索引
使用主键进行排序，且叶子节点存储的是完整的数据记录。

二级索引（辅助索引）
使用索引列进行排序，叶子节点存储的并不是完整的数据记录，而只是 索引列+主键 这两列的值。想要获取完整的数据记录时，需要进行 回表 操作（根据主键值去聚簇索引中再查找一遍完整的数据）。

由于非主键索引的列值可能存在重复的，所以二级索引的非叶子节点记录的内容实际上是由三个部分构成的：

• 索引列的值（橘色）
• 主键值（蓝色）
• 页号（绿色）
  

联合索引
以多个列的大小作为排序规则，也就是同时为多个列建立索引。

索引的创建

• 只为用于搜索、排序或分组的列创建索引
• 为重复数据少的列创建索引
• 索引列的类型尽量小，节省空间
• 可以只对字符串值的前缀建立索引，格式：字段名(前缀长度)，例如 name(10)
• 只有索引列在比较表达式中单独出现才可以适用索引
• 为了尽可能少的让 聚簇索引 发生页面分裂和记录移位的情况，建议让主键拥有
  AUTO_INCREMENT 属性
• 定位并删除表中的重复和冗余索引
• 尽量使用 覆盖索引 进行查询，避免回表带来的性能损耗

索引的使用

以person_info表为例进行后续说明。

CREATE TABLE person_info(
        id INT 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, birthday, phone_number)
);

全值匹配
搜索条件中的列和索引列一致或包含全部索引中的列。

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

搜索条件中列的顺序对查询的执行过程不影响。

【True】SELECT * FROM person_info WHERE birthday = '1990-09-27' AND name = 'Ashburn' AND phone_number = '15123983239';

最左匹配
搜索条件中的列包含联合索引中从最左边开始的列。

# 匹配name + birthday
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';
# 匹配name
SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239';
# 未匹配
SELECT * FROM person_info WHERE birthday = '1990-09-27';

前缀匹配

【True】SELECT * FROM person_info WHERE name LIKE 'As%';
【False】SELECT * FROM person_info WHERE name LIKE '%As%';

如果某一列需要进行后缀匹配时，可以考虑将该列的值逆序进行存储。

范围匹配
对多个列同时进行范围查找时，只有对索引最左边的列进行范围查找的时候才能用到索引。

# 匹配name
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
# 匹配name
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

如果左边的列是精确查找，则右边的列可以进行范围查找。

# 匹配name + birthday
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

单表的访问方法

const
通过主键或者唯一二级索引列与常数等值比较来定位一条记录

# 主键
SELECT * FROM single_table WHERE id = 1438;
# 二级索引只对应一条记录
SELECT * FROM single_table WHERE key2 = 3841;

ref
通过索引列进行等值比较后可能匹配到多条连续的记录

SELECT * FROM single_demo WHERE key1 = 'abc';

ref_or_null
不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为 NULL 的记录也找出来

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

range
索引列匹配某个或某些范围的值

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

index
可以直接通过遍历二级索引得到查询结果，而无需回表

# key_part1, key_part2, key_part3的值全部包含在二级索引的叶子结点中
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

all
全表扫描

多表的连接方式

内连接
外连接：左连接、右连接

事务

事务的特性

原子性（Atomicity）
要么全做、要么不做

隔离型（Isolation）
事务之间互不影响

一致性（Consistency）
以转账为例，参与转账的账户的总的余额是不变的

持久性（Durability）
数据库操作所修改的数据都应该在 磁盘上保留下来

事务的语法

开启事务
以下两种语法均可：
BEGIN [WORK];
START TRANSACTION;

提交事务
COMMIT [WORK];

手动回滚事务
ROLLBACK [WORK];

保存点
SAVEPOINT 保存点名称;

当我们想回滚到某个保存点时，可以使用下边这个语句：
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

删除某个保存点：
RELEASE SAVEPOINT 保存点名称;

redo log

undo log

事务隔离级别

脏读
读到了不存在的数据

不可重复读
之前读到的数据发生了变化（没有了或被修改了）

幻读
读到了之前未读到的数据

MVCC

多版本并发控制

MyISAM

数据和索引会分开记录，所有的索引（包括主键索引）都是二级索引。

数据文件

表中的记录 按照记录的插入顺序 单独存储在一个文件中，称之为 数据文件 。

索引文件

在索引的叶子节点中存储的不是完整的数据记录，而是 索引列值 + 行号 。需要先通过索引找到对应的行号，再通过行号去找对应的记录（一定会进行回表操作）。

函数

where 和 having 的区别：

• where 在 group by 前， having 在 group by 之后
• 聚合函数（avg、sum、max、min、count），不能作为条件放在 where 之后，但可以放在 having 之后

<=>运算符相当于封装了= 和 is ，既可以判断 非NULL值，也可以用来判断NULL值。