DQL

DDL（Data Definition Language）：CREATE、DROP、ALTER

DML（Data Manipulation Language）：INSERT、DELETE、UPDATE

DQL（Data Query Language）：SELECT

DCL（Data Control Language）：GRANT、REVOKE

DQL 语句执行顺序：

1
FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT -> SELECT

DCL

用户管理

1）查询用户

1
SELECT * FROM mysql.user;

2）创建用户

Host 默认值为 “%”，通过用户名@主机名来唯一标识用户

1
CREATE USER "用户名"@"主机名" IDENTIFIED BY "密码"

3）修改密码

1
ALTER USER  "用户名"@"主机名" IDENTIFIED WITH mysql_native_password BY "新密码"

4）删除用户

1
DROP USER "用户名"@"主机名"

权限控制

1）查看权限

权限包括：ALL, ALL PRIVILEGES、SELECT、INSERT、UPDATE、DELETE、ALTER、DROP、CREATE

1
SHOW GRANTS FOR "用户名"@"主机名"

2）授予权限

数据库名和表名可用 * 通配符

1
GRANT 权限列表 ON 数据库名.表名 TO "用户名"@"主机名"

3）撤销权限

1
REVOKE  权限列表 ON 数据库名.表名 FROM "用户名"@"主机名"

eg）：

1
2
CREATE USER "zhangsan" IDENTIFIED BY "1234";
SELECT * FROM mysql.user;
Host User …
localhost root …
localhost mysql.session …
localhost mysql.sys …
% zhangsan … 
1
SHOW GRANTS FOR "root"@"localhost";
Grants for root@localhost
GRANT ALL PRIVILEGES ON . TO ‘root‘@’localhost’ WITH GRANT OPTION
GRANT PROXY ON ‘‘@’’ TO ‘root‘@’localhost’ WITH GRANT OPTION 
1
SHOW GRANTS FOR "zhangsan";
Grants for zhangsan@%
GRANT USAGE ON . TO ‘zhangsan‘@’%’ 
1
2
GRANT ALL ON 'test'.'good' TO "zhangsan";
SHOW GRANTS FOR "zhangsan";
Grants for zhangsan@%
GRANT USAGE ON . TO ‘zhangsan‘@’%’
GRANT ALL PRIVILEGES ON `test`.`good` TO ‘zhangsan‘@’%’ 
1
2
REVOKE DROP, CREATE ON test.good FROM zhangsan;
SHOW GRANTS FOR "zhangsan";
Grants for zhangsan@%
GRANT USAGE ON . TO ‘zhangsan‘@’%’
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `test`.`good` TO ‘zhangsan‘@’%’

函数

字符串函数

CONCAT(s1, s2, ... ,sn)、LOWER(str)、UPPER(str)、TRIM(str)

LPAD(oldstr, length, pad)、RPAD(oldstr, length, pad)：用字符串 pad 对 oldstr 进行左（右）填充，达到 length 长度

eg：

1
2
SELECT LPAD("hello",10,"123")；# 12312hello
SELECT RPAD("hello",10,"123")；# hello12312

SUBSTRING(str, start, [lenth])：str 从第 start 位起长度为 length 的子串，length 缺省时则直到最后一位

eg：

1
2
SELECT SUBSTRING("1234567", 3); # 34567
SELECT SUBSTRING("1234567", 3, 4); # 3456

数值函数

CEIL(x)、FLOOR(x)：向上、下取整
MOD(x, y)：x 模 y
RAND()：[0, 1) 的随机浮点数，可将固定种子作为参数传入
ROUND(x, [y])：x 四舍五入并保留 y 位小数，y 默认为 0

日期函数

CURDATE()、CURTIME()、NOW()

YEAR(date)、MONTH(date)、DAY(date)

DATE_ADD(date, INTERVAL expr type)：向日期添加指定的时间间隔，其中 type 为 SECOND、DAY 等单位

DATEDIFF(date1, date2)：date1 减去 date2 的天数

eg：

1
2
SELECT CURDATE(), CURTIME(), NOW(), YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT DATE_ADD("2023-01-01 11:00:00", INTERVAL 20 SECOND), DATEDIFF("2023-02-01", "2023-01-01");
CURDATE() CURTIME() NOW() YEAR(NOW()) MONTH(NOW()) DAY(NOW())
2023-01-01 01:22:34 2023-01-01 01:22:34 2023 1 1
DATE_ADD(“2023-01-01 11:00:00”, INTERVAL 20 SECOND) DATEDIFF(“2023-02-01”, “2023-01-01”);
2023-01-01 11:00:20 31

流程函数

IF(value, t, f)：if value==true, return t, else return f

IFNULL(value1, value2)：if value1 is null, return value2, else return value1

CASE WHEN value1 THEN res1 WHEN value2 THEN res2 WHEN value3 THEN res3 ... ELSE [defaultRes] END：if value1==true, return res1; else if value2==true, return res2; else if value3==true, return res3; else return defaultRes

CASE exp WHEN value1 THEN res1 WHEN value2 THEN res2 WHEN value3 THEN res3 ... ELSE [defaultRes] END：if value1==exp, return res1; else if value2==exp, return res2; else if value3==exp, return res3; else return defaultRes

eg：

1
2
3
4
insert into `score` (`id`, `name`, `math`) values('1','zhangsan','50');
insert into `score` (`id`, `name`, `math`) values('2','lisi','70');
insert into `score` (`id`, `name`, `math`) values('3','wangwu','99');
SELECT id, name, (CASE WHEN math>=80 THEN "良好" WHEN math>=60 THEN "及格" ELSE "不及格" END) "数学成绩" FROM score;
id name 数学成绩
1 zhangsan 不及格
2 lisi 及格
3 wangwu 良好

外键约束的 UPDATE 和 DELETE 行为

NO ACTION、RESTRICT：直接禁止

CASCADE：级联更新/删除

SET NULL：置空

SET DEFAULT：置默认值（Innodb 不支持）

eg：

1
ALTER TABLE tb_emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES tb_dept(id) ON UPDATE SET NULL ON DELETE CASCADE;

自连接

使用表别名

eg：

1
SELECT a.name "员工", b.name "上司" FROM  emp a, emp b WHERE a.managerid = b.id;

UNION 联合查询

联合查询的多张表的列数、字段类型需一致，否则报错。

UNION ALL 不会去重；UNION 会去重。

eg：

1
2
3
SELECT id, name FROM emp where salary>1000
UNION ALL
SELECT id, name FROM emp where age>30;

子查询

又称为嵌套查询。根据子查询的结果不同，分为四类。

标量子查询

子查询结果为单个值，作为一个常量使用。

列子查询

子查询结果为一列。同一字段的多个值，类似“同一集合”的概念。常用的操作符：IN 、NOT IN 、ANY、ALL

eg：

查询比财务部所有员工的工资都高的员工信息

1
2
3
4
5
SELECT * FROM emp WHERE salary > ALL(
	SELECT salary FROM emp WHERE dept_id = (
        SELECT id FROM dept WHERE name = "财务部"
    )
);

行子查询

子查询结果为一行，类似“一个对象”的概念，子查询的字段数必须和外层条件字段数相同。

eg：

查询与一号员工同名同姓的员工信息

1
2
3
SELECT * FROM emp WHERE (first_name, last_name) = (
	SELECT first_name, last_name FROM emp WHERE id = 1
);

表子查询

子查询结果为多行多列。常用操作符：IN

eg:

查询与张三、李四的职位和薪资相同的员工

1
2
3
SELECT * FROM emp WHERE (job,salary) IN (
    SELECT job, salary FROM emp WHERE name="zhangsan" or name="lisi"
);

TRANSACTION

特性

原子性（事务是不可分割的工作单位）、一致性（事务按照预期生效，必须使数据库从一个一致性状态变换到另外一个一致性状态）、隔离性（多个并发事务之间要相互隔离）、持久性（事务一旦被提交，其对数据库中数据的改变就是永久性的）

原理

原子性、一致性、持久化，由 InnoDB 中的 redo log 和 undo log 来保证。

而持久性是通过数据库的锁和 MVCC 保证。

redo log

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

WAL（Write-Ahead Logging）：当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在 redo log buffer 中。在事务提交时，会将 redo log buffer 中的数据刷新到 redo log file 中，由于是日志文件顺序写，效率高于每次都将 Buffer Pool 中的脏页刷新到磁盘。 一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，就可借助 redo log 进行恢复，保证了事务的持久性。而如果脏页成功刷新到磁盘或者涉及到的数据已经落盘，此时 redo log 就可以删除了，所以存在的两个 redo log 文件是循环写的。

undo log

回滚日志，用于记录数据被修改前的信息，可用于提供回滚（保证事务的原子性）和 MVCC（多版本并发控制）。当执行rollback 时，可以从 undo log 中的逻辑记录读取到对应相反的内容并进行回滚。

undo log 在事务执行时产生，事务提交时，并不会立即删除，因为这些日志可能还用于 MVCC。

undo log 采用段的方式进行管理和记录，存放在 rollback segment 回滚段中，包含 1024个 undo log segment。

MVCC

MVCC

隔离

脏读：一个事务读到另一个事务还未提交的数据。

不可重复读：一个事务先后读取同一条记录（期间其他事务对该记录进行了更新并提交）时读取的数据不同。

幻读：一个事务先后读取同一条记录，在再次读取这些数据时，发现有一些原本不存在的行出现了（期间其他事务进行了添加）。

1
2
SELECT @@TRANSACTION_ISOLATION;
SET [范围] TRANSACTION ISOLATION LEVEL [级别];

范围包括：SESSION 和 GLOBAL，分别表示当前连接和之后新获取的连接。

隔离级别 是否存在脏读 是否存在不可重复读 是否存在幻读
READ UNCOMMITTED √ √ √
READ COMMITTED × √ √
REPEATABLE READ（默认） × × √
SERIALIZABLE × × ×

eg1：

1
2
3
4
5
6
7
8
9
10
11
SELECT @@autocommit;
SET autocommit = 0; # 关闭自动提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT @@TRANSACTION_ISOLATION;

START TRANSACTION;
UPDATE account SET money = money - 1 WHERE name = "zhangsan";
UPDATE account SET money = money + 1 WHERE id = "lisi";
# 虽然当前事务未提交，但此时在其他会话中查询 zhangsan 和 lisi 的数据，会发现读取到的数据已经发生了改变，即查询到了未提交的事务的数据
ROLLBACK;
# 此时在其他会话中查询 zhangsan 和 lisi 的数据，会发现读取到的数据变回了修改前的状态

eg2：

1
2
3
4
5
6
7
8
9
10
11
SELECT @@autocommit;
SET autocommit = 0; # 关闭自动提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 
SELECT @@TRANSACTION_ISOLATION;

START TRANSACTION;
UPDATE account SET money = money - 1 WHERE name = "zhangsan";
UPDATE account SET money = money + 1 WHERE id = "lisi";
# 此时在其他会话中查询 zhangsan 和 lisi 的数据，会发现读取到的数据未发生改变，即未出现脏读问题
COMMIT;
# 此时在其他会话中查询 zhangsan 和 lisi 的数据，会发现读取到的数据发生了改变，即出现了不可重复读的问题，读取到其他事务已提交的数据

eg3：

1
2
3
4
5
6
7
8
9
10
11
SELECT @@autocommit;
SET autocommit = 0; # 关闭自动提交
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; # 也可以不设置，因为默认隔离等级就是 REPEATABLE READ
SELECT @@TRANSACTION_ISOLATION;

START TRANSACTION;
UPDATE account SET money = money - 1 WHERE name = "zhangsan";
UPDATE account SET money = money + 1 WHERE id = "lisi";
# 此时在其他会话中查询 zhangsan 和 lisi 的数据，会发现读取到的数据未发生改变，即未出现脏读问题
COMMIT;
# 此时在其他会话中查询 zhangsan 和 lisi 的数据，发现读取到的数据仍未发生改变，即未出现不可重复读的问题，只有其他绘画结束事务，才会看到变化

存储引擎

存储引擎就是存储数据、建立索引、查询/更新数据等技术的实现方式，存储引擎是基于表的，而不是基于库的，因此存储引擎也被称为表类型，可在创建表时手动指定。

InnoDB

DML 操作遵循 ACID 模型，支持事务；行级锁，提高并发访问性能；支持外键约束，保证数据的完整性和正确性。每张表都会对应一个 ibd 表空间文件，存储该表的表结构、数据和索引。

[InnoDB 存储引擎](# InnoDB 引擎)逻辑结构的最高层是表空间（tablespace），表空间由数据段、索引段、回滚段等多个段（segment）组成，一个段包含多个区（extent），每个区大小为 1 M，包括 64 个连续的页（page），页作为组成区的最小单元和 InnoDB 存储引擎磁盘管理的最小单元，每个页为 16 KB，为保证页的连续性，InnoDB 存诸引擎每次从磁盘申请 4-5 个区。

MyISAM

不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快。每张表对应三个文件：xxx.sdi 存储表结构信息；xxx.MYD 存储数据 ；xxx.MYI 存储索引。

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择MyISAM 是非常合适的。

索引概念

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 叶子节点单向链表的基础上，增加一个指向相邻叶子节点的链表指针，变成了双向链表；提高区间访问的性能，利于排序。

常见索引类型

PRIMARY KEY（主键索引）、UNIQUE（唯一索引）、INDEX（普通索引）、FULLTEXT（全文索引，适合于执行全文搜索查询，如在大量文本中搜索一个或多个单词）、组合索引

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引；否则 InnoDB 自动生成一个 rowid 作为隐藏的聚集索引。

1
2
3
4
5
ALTER TABLE 'table_name' ADD PRIMARY KEY ('column');
ALTER TABLE 'table_name' ADD UNIQUE ('column');
ALTER TABLE 'table name' ADD INDEX index_name('column');
ALTER TABLE 'table_name' ADD FULLTEXT ('column');
ALTER TABLE 'table name' ADD INDEX index_name('column1', 'column2', 'column3');

InnoDB 引擎选择使用 B+tree 索引结构的原因

1。相对于二叉树，层级更少，搜索效率高；
2．对于 B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
3．相对 Hash 索引，B+tree 支持范围匹配及排序操作。

聚簇索引和非聚簇索引

聚簇索引：必须有，且只有一个，索引结构的叶子节点保存数据。并不是一种单独的索引类型，而是一种数据存储方式，是一种抽象概念，在具体实现的时候，需要区分不同的引擎实现细节有所不同。InnoDB中只有主键索引才能是聚簇索引。

非聚簇索引：叶子节点没有存储数据行，那么就是非聚簇索引。myisam 采用非聚簇索

二级索引：又称作辅助索引，均属于非聚簇索引。索引结构的叶子节点关联的是对应的主键。

image-20240214003747952

回表查询：先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据。如执行 SELECT * FROM user WHERE name="Arm" 时（id 为主键，name 字段创建有索引），先根据 name=’Arm’ 到 name 字段的二级索引中进行匹配查我到对应的主键值 10，根据主键值到聚集索引中查找 10 对应的记录，最终找到 10 对应的行数据返回。SELECT * FROM user WHERE id=10 直接走聚集索引直接返回数据，因此执行性能更高。

语句性能分析

执行频率

SHOW [session | global] STATUS 可以查看服务器状态信息，其中 session 是查看当前会话，global 是查看全局数据。

1
SHOW STATUS LIKE "Com\_______";

通过以上命令查出的 Com_select、Com_update、Com_insert、Com_delete 是不同类型 SQL 语句的执行频次，知道当前数据库是以增删改为主，还是以查询为主，如果是以增删改为主，可以考虑不对其进行索引的优化。

慢查询日志

慢查询日志记录所有执行时间超过指定参数（long_query_time，单位：秒，默认 10）的所有 SQL 语句到日志文件中。

在 MySQL 的配置文件 my.ini 或 my.cnf 中配置：

1
2
3
4
5
6
# 开启慢查询日志记录
slow_query_log=1
# 阈值
long_query_time=2
# 指定慢查询日志的保存位置
slow_query_log_file = /path/to/file.log

Profile 详情

1
2
3
4
5
6
# 开启
SET PROFILING=1;
# 查看每一条 SQL 执行的耗时基本情况
SHOW PROFILES;
# 查看指定 query_id 的 SQL 语句各个阶段的耗时情况
SHOW PROFILE CPU FOR QUERY 30;
Query_ID Duration Query
28 0.00004775 SET profiling_history_size = 15
29 0.00119900 SHOW STATUS
30 0.00007450 SELECT @@PROFILING
31 0.00097500 SHOW STATUS
32 0.00021425 select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 30 group by state order by `duration (summed) in sec` desc
Status Duration CPU_user CPU_system
starting 0.000019 0.000000 0.000000
… … … …

EXPLAIN/DESC

在查询语句前加上 EXPLAIN 或 DESC 关键字，得到如下结果

列名 描述
id SELECT 查询的序列号，表示查询中执行 SELECT 子句或者是操作表的顺序（id 相同，执行顺序从上到下；id 不同，值越大，越先执行）
select_type 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE 之后包含了子查询）等
table 表名
partitions 匹配的分区信息
type 性能由好到差的连接类型为 NULL（不访问表，如 SELECT 1）、system（表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，而 InnoDB 即使一条数据也是all）、const（主键或唯一索引，如 WHERE id = 1）、eq_ref（在连接查询的时候，被驱动表是通过主键或者唯一的二级索引等值匹配的方式进行访问的）、ref（非唯一性索引，如 WHERE age = 18）、range（范围匹配非唯一性索引，如 WHERE age > 18）、index（需要扫描全部的索引记录）、all（全表扫描）
possible_keys 可能使用到的索引，一个或多个
key 实际使用的索引
key_len 索引字段的最大可能长度
ref 当使用索引列等值查询的时候，与索引列进行等值匹配的对象信息
rows 预估需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息 
1
2
# 假定 emp 表中，有主键索引（id）、唯一索引（name）、普通索引（age）
SELECT * FROM emp;
id workno name gender age idcard workaddress entrydate
1 NULL zs NULL 10 NULL NULL NULL
2 NULL ls NULL 30 NULL NULL NULL
3 NULL ww NULL 50 NULL NULL NULL 
1
2
# 全表扫描，type 为 ALL
EXPLAIN SELECT name FROM emp;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE emp NULL ALL NULL NULL NULL NULL 3 100.00 NULL 
1
2
# where 条件中没有用到索引, 但是要取出的列 id 是索引包含的列, 所以只要全扫描 id 索引即可, 直接使用索引树查找数据.，type 为 index
EXPLAIN SELECT id FROM emp;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE emp NULL index NULL age 2 NULL 3 100.00 Using index 
1
2
# 常见于 '<', '<=', '>', '>=', 'between' 等操作符，因为 age 是索引, 所以只要查找索引的某个范围即可, 通过索引找到具体的数据，type 为 range
EXPLAIN SELECT workno FROM emp WHERE age < 40; # 若为 age <100，实际上相当于全扫描 age 索引，此时 type 为 index
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE emp NULL range age age 2 NULL 2 100.00 Using index condition 
1
2
# 使用非唯一性索引，返回匹配某个单独值的记录行，type 为 ref
EXPLAIN SELECT workno FROM emp WHERE age = 40;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE emp NULL ref age age 2 const 1 100.00 NULL 
1
2
# 在联表查询中使用 primary key 或者 unique key 作为关联条件，type 为 eq_ref
EXPLAIN SELECT e1.workno FROM emp e1 LEFT JOIN emp e2 ON e1.id = e2.id;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE e1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
1 SIMPLE e2 NULL eq_ref PRIMARY PRIMARY 4 test.e1.id 1 100.00 Using index 
1
2
3
# 当 MySQL 对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将唯一性索引置于 where 列表中, MySQL 就能将该查询转换为一个常量，type 为 const
# system 是 const 类型的特例
EXPLAIN SELECT workno FROM emp WHERE name = "ls";
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE emp NULL const name name 33 const 1 100.00 NULL 
1
2
# 不访问表或者索引就直接能到结果，type 为 NULL
EXPLAIN SELECT workno FROM emp WHERE name = "unknown";
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table

索引使用

联合索引的 B+ 树中的键值是排好序的。不过，这里指的排好序，其实是相对的，举个例子，有 (a, b, c) 联合索引，a 首先是排序好的，而 b 列是在 a 列排序的基础上做的排序，同样的 c 是在 a,b 有序的基础上做的排序

img

最左前缀法则

联合索引，以最左边的为起点任何连续的索引都能匹配上。

MySQL 最终只会选择一个索引，因此在业务场景中，如果存在多查询条件，考虑针对于查询字段建立索引时，建议建立联合索引而非单列索引。

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE t1 ADD INDEX idx_test('c1', 'c2', 'c3');

SELECT * FROM t1 WHERE c1="3"; #使用索引
SELECT * FROM t1 WHERE c2="4"; #未使用索引
SELECT * FROM t1 WHERE c3="2"; #未使用索引
SELECT * FROM t1 WHERE c1="2" AND c2="3"; #使用索引
SELECT * FROM t1 WHERE c2="2" AND c1="3"; #使用索引
SELECT * FROM t1 WHERE c1="2" AND c3="4";  #使用索引 c1
SELECT * FROM t1 WHERE c3="2" AND c1="4";  #使用索引 c1
SELECT * FROM t1 WHERE c1="2" AND c2="3" AND c3="4"; #使用索引
SELECT * FROM t1 WHERE c2="3" AND c3="4";  #未使用索引

范围查询

联合索引，出现范围查询 <、>，LIKE，BETWEEN，范围查询右侧的列索引失效。

1
2
SELECT * FROM t1 WHERE c1< 3 AND c2 = 2; #使用索引 c1
# 假定如下结果，则需要回表 4 次（1，2，3，4）
id c1 c2
1 1 1
2 1 2
3 2 1
4 2 2
5 3 2

索引下推（INDEX CONDITION PUSHDOWN，ICP） ：MySQL 5.6 发布后针对扫描二级索引的一项优化改进。通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数，数据库在取出索引的同时，会根据 where 条件直接过滤掉不满足条件的记录，减少回表次数。ICP 适用于 MYISAM 和 INNODB。

如上例子，由于 ICP，只需要回表 2 次（2，4）。

索引失效

  • 索引列进行运算操作会导致索引失效。

    1
    SELECT * FROM t1 WHERE SUBSTRING(c1, 2, 3) = "1"; # 失效

  • 索引列字符串不加单引号时，数据库存在隐式类型转换，索引会失效。

    1
    2
    SELECT * FROM t1 WHERE c1 = "1";
    SELECT * FROM t1 WHERE c1 = 1; # 失效

  • 在 LIKE 模糊查询中，在关键字后面加 %，索引生效，而在关键字前加 %，索引失效。

    1
    2
    SELECT * FROM t1 WHERE c1 LIKE "1%"; 
    SELECT * FROM t1 WHERE c1 LIKE "%1%"; # 失效

  • 用 OR 分割开的条件，即使 OR 前的条件中的列有索引，后面的列中没有索引时，涉及的索引都不会被用到。

    1
    SELECT * FROM t1 WHERE c1 = "1" OR c99 = "9";  # 失效

  • 与数据库的数据分布有关。查询时 MySQL 会评估，走索引快，还是全表扫描快，如果全表扫描更快，则放弃索引走全表扫描。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快。

    1
    2
    # 假设数据库中绝大多数数据的 c1 字段都是 "1"
    SELECT * FROM t1 WHERE c1 = "1"; # 失效

SQL 提示

在 SQL 语句中加入一些人为提示来优化操作，例如当某查询存在多个可用的索引时，可指定使用的索引。

USE INDEX 建议使用某索引（非强制）；IGNORE INDEX 忽略指定的索引；FORCE INDEX 强制使用某索引。

1
2
# 假设存在两个索引 idx_t1_c1 和 idx_t1_c1_c2
SELECT * FROM t1 FORCE INDEX(idx_t1_c1) WHERE c1="1";

覆盖索引

查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。应尽量使用覆盖索引，而不是 SELECT *，否则极易造成回表查询。

1
2
3
EXPLAIN SELECT id,c1 FROM t1 WHERE c1="1" AND c2="2";
EXPLAIN SELECT id,c1,c2,c3 FROM t1 WHERE c1="1" AND c2="2";
EXPLAIN SELECT id,c1,c99 FROM t1 WHERE c1="1" AND c2="2";

输出的 Extra，前面两条 SQL 的结果为 Using where；Using Index，表明查找使用了索引，但是需要的数据都在索引列中及叶子节点下挂的主键 id 能找到，所以不需要回表查询数据；而后面 SQL 的结果为：Using index condition 说明查找使用了索引，但是需要回表查询数据、

eg：

对如下语句：select id,c1,c2 from tb t1 where c1="1"; 。若只针对 c1 建索引，会造成回表查询，而若对 c1 和 c2 建联合索引，则不会出现回表查询。

前缀索引

当字段类型为字符串类型（varchar，text，longtext 等）时，会让索引变得很大，查询时浪费大量的磁盘IO。此时可以只将字符串的一部分前缀建立索引，节约索引空间，从而提高索引效率。

选择性：不重复的索引值 (基数) 和数据表的记录总数的比值。索引选择性越高则查询效率越高，唯一索引的选择性是 1，性能最好的。

1
2
3
CREATE INDEX idx_c1_5 on t1(c1(5));
SELECT COUNT(DISTINCT c1) / COUNT(*) FROM tb; 
SELECT COUNT(DISTINCT SUBSTRING(c1, 1, 5)) / COUNT(*) FROM tb;

使用原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储 NULL 值，在创建表时使用 NOT NULL 约束。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。

SQL 优化

INSERT/DELETE

一次性往数据库表中插入多条记录时，若简单调用多次 INSERT 语句会增加服务器的负荷，因为执行每一次 SQL，服务器要同样对 SQL 进行分析、优化等操作。

可使用 LOAD 将文本文件中的数据批量插入。

1
2
3
4
# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
# 设置全局参数 localinfile 为 1，开启从本地加载文件导入数据的开关
set global local_infile=1;
1
LOAD DATA LOCAL INFILE '/path/to/file.suffix' INTO TABLE tb1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

主键顺序插入性能要高于乱序插入，原理如下：

在 InnoDB 引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table, IOT)。数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K，如果插入的数据行 row 在该页存储不下时，会存储到下一个页中，页与页之间通过指针连接。

当数据按主键顺序插入时，一页满了，则会开辟一个新页：

image-20240225000722725

而当数据按主键乱序插入时，由于索引结构的叶子节点是有顺序的，如图，此时要插入主键为 50 对应的数据，应该放在主键为 47 的数据的后面：

image-20240225000948868

但是 47 所在的 1# 页，已经写满，此时会开辟一个新的页 3#，但是并不直接将 50 存入 3# 页，而是会将 1# 页后一半的数据，移动到 3# 页，然后在 3# 页，插入 50，并重新设置链表指针，这种现象称作页分裂。

image-20240225001307584

对数据进行删除时，实际上数据记录并没有被物理删除，只是被标记（flaged）为删除并且它的空间允许被其他记录声明使用。当页中删除的记录达到阈值 MERGE_THRESHOLD（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）尝试将两个页合并以优化空间使用，称为页合并。

image-20240225001740105

ORDER BY

MySQL 的 ORDER BY 排序有两种：

Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

1
2
3
4
5
6
7
8
9
10
11
# MySQL8.0
EXPLAIN SELECT id, c1, c2 FROM tb1 ORDER BY c1, c2; # Extra 显示为 Using filesort
CREATE INDEX idx_tb1_c1_c2_aa on tb1(c1, c2);
EXPLAIN SELECT id, c1, c2 FROM tb1 ORDER BY c1; # Extra 显示为 Using index
EXPLAIN SELECT id, c1, c2 FROM tb1 ORDER BY c1, c2; # Extra 显示为 Using index
EXPLAIN SELECT id, c1, c2 FROM tb1 ORDER BY c1 DESC, c2 DESC; # Extra 显示为 Backward index scan; Using index
EXPLAIN SELECT id, c1, c2 FROM tb1 ORDER BY c2, c1; # Extra 显示为 Using index; Using filesort
EXPLAIN SELECT id, c1, c2 FROM tb1 ORDER BY c1 DESC, c2; # Extra 显示为 Using index; Using filesort
EXPLAIN SELECT id, c1, c2 FROM tb1 ORDER BY c1, c2 DESC; # Extra 显示为 Using index; Using filesort
CREATE INDEX idx_tb1_c1_c2_ad on tb1(c1 ASC, c2 DESC);
EXPLAIN SELECT id, c1, c2 FROM tb1 ORDER BY c1, c2 DESC; # Extra 显示为 Using index

如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认 256k）。

GROUP BY

GROUP BY 的三种处理方式：

  • 松散索引扫描（Loose Index Scan）：利用 Innodb 基于 B+ 树的 IOT 有序原理，无需扫描满足条件的所有索引键即可返回结果，具体来看，当查询初始化后，首先查询第一条索引记录，然后查询下一条 prefix 不同的记录，直到最后一条为止。使用条件：1、只能用于单表查询；2、GROUP BY 中只能包含索引的 prefix；3、聚合函数只支持MIN() 和 MAX()，并且指向同一列，同时这列紧跟在 group list 之后；4、不能是前缀索引；5、查询中存在的除 GROUP BY 指定的列以外的其他部分必须为常量
  • 紧凑索引扫描（Tight Index Scan）：需要扫描满足条件的所有索引键才能返回结果。
  • 临时表（Temporary table）：建立临时表。

在 MYSQL8.0 之前，GROUP BY 默认会依据字段进行隐式排序；在语义相同，有索引的情况下，GROUP BY 和 DISTINCT 效率相同；在语义相同，无索引的情况下，GROUP BY 相比 DISTINCT 在 MySQL8.0 之前执行效率更低；更推荐使用GROUP BY。

1
2
3
4
# MySQL5.7
EXPLAIN SELECT c1 FROM t1 GROUP BY c1; # 松散索引扫描，Extra 为 Using index for group-by; 
EXPLAIN SELECT c1, SUM(c2) FROM t1 GROUP BY c1; # Extra 为 Using index
EXPLAIN SELECT c1,c99 FROM t1 GROUP BY c1, c99;  # Extra 为 Using temporary; Using filesort

LIMIT

LIMIT m, n 的原理是，服务器从存储引擎取出 m+n 条数据，然后丢弃掉前 m 条数据，只保留最后的 n 条。因此当 m 较大时，效率低。

优化方法：

1、给出大致范围，增加确定范围的条件，避免 offset。

2、通过覆盖索引加子查询形式进行优化。

eg：

1
2
3
4
SELECT * FROM t1 ORDER BY id LIMIT 100000, 5;
# 优化
SELECT * FROM t1 WHERE id > 100000 ORDER BY id LIMIT 5;
SELECT * FROM t1 a, (SELECT id FROM t1 ORDER BY id LIMIT 100000, 5) b WHERE a.id=b.id;

COUNT

对于 MyISAM 引擎，会把表的总行数存在了磁盘上（存放在 information_schema 库中的 PARTITIONS 表中），在不加 where 条件时，执行 COUNT(*) 和 COUNT(主键) 和 COUNBT(常量) 时会直接返回这个总数，因此效率很高，但是在加 where 限定语句的时候 MySQL 需要对全表进行检索从而得出 count 的总数。

对于 InnoDB 引擎，支持事务，默认的隔离级别是 Repeatable Read，COUNT(*) 和 COUNT(主键) 和 COUNBT(常量) 不加 where 条件时，优化器会找到最小的那棵索引树进行遍历全表扫描，加 where 条件时，需要走主键索引筛选出值后再统计。COUNT(非主键) 只统计非主键字段值不为 NULL 的总数。

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

源码中 count(expr)，当 expr = * 时，当做数字 0 处理，因此 count(*) 转换成 count(0)

UPDATE

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，执行更新的条件应该有索引，并且该索引不能失效，否则会从行锁升级为表锁，并发性能降低。

1
2
UPDATE t1 SET c1="2" WHERE c1="1"; # 行锁
UPDATE t1 SET c1="2" WHERE c99="1"; # 表锁

View

视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。分为可更新视图（通过视图更新时，转换到基本表来更新）和不可更新视图（包括 DISTINCT、GROUP BY、HAVING、UNION、UNION ALL、聚合函数或其他的一些概念、所有使用了 TEMPTABLE 算法的视图都不可以更新）

一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。

当使用 WITH [CACADED/LOCAL] CHECK OPTION 子句创建视图时，MySQL 会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义，默认为 CASCADED，即会级联检查基于视图创建的视图，LOCAL 则只检查当前视图。

1
2
3
4
CREATE OR REPLACE VIEW t1_v_1 AS SELECT id, c1 FROM t1 WHERE c1 < 3 WITH CHECK OPTION;
INSERT INTO t1_v_1 VALUES(10,2); # 成功，插入到基表 t1
# INSERT INTO t1_v_1 VALUES(11,3); # 报错，插入失败
SELECT * FROM t1_v_1;

变量

系统变量

服务器提供。分为全局变量（GLOBAL）、会话变量（SESSION），默认 SESSION。

1
2
3
4
5
6
SHOW [SESSION/GLOBAL] VARIABLES; # 查看所有系统变量
SHOW [SESSION/GLOBAL] VARIABLES LIKE '------';
SELECT @@[SESSION/GLOBAL] 变量名; # 查看指定系统变量
# 设置变量
SET [SESSION/GLOBAL] 变量名 = 值;
SET @@[SESSION/GLOBAL]变量名 = 值;

用户定义变量

作用域为当前连接。

1
2
3
4
5
# 赋值
SET @变量名 = 表达式;
SET @变量名 := 表达式;
SELECT @变量名 := 表达式; -- 使用 此方式赋值，注意是 :=
SELECT 表达式 INTO 变量名 FROM 表; -- 将查询到的值赋给变量

局部变量

访问之前，需要 DECLARE 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 BEGIN-END 块。

1
2
3
4
5
6
7
8
DECLARE 变量名 变量类型 [DEFAULT 默认值];
# eg：
CREATE PROCEDURE pr1()
BEGIN
	DECLARE stu_count INT DEFAULT 0;
	SELECT COUNT(*) INTO stu_count FROM student;
	SELECT stu_count;
END;

TRIGGER

在 INSERT/UPDATE/DELETE 之前或之后，触发执行触发器中定义的 SQL 语句集合，可用于确保数据完整性、日志记录、数据校验等，使用 OLD 和 NEW 来引用发生变化的。

1
2
3
4
5
6
CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
BEGIN
	触发器执行的语句块;
END;

触发器很消耗资源，可读性差，应尽量少使用

锁

按照锁的粒度分，分为全局锁（锁定数据库中的所有表）、表级锁、行级锁。

全局锁

对整个数据库实例加锁，加锁后整个实例就处于只读状态。其典型的使用场景是做全库的逻辑备份。

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的 DDL、 DML 全部都处于阻塞状态，但可以执行 DQL 语句。

1
2
3
4
FLUSH TABLES WITH READ LOCK;
-- InnoDB 引擎可使用参数 --single-transaction 参数来完成不加锁的一致性数据备份
mysqldump -uroot -p1234 test > test.sql; # 数据备份
UNLOCK TABLES;

表级锁

表级锁分为以下三类：

  • 表锁
  • 元数据锁（meta data lock, MDL）
  • 意向锁（Intent Lock，IL）

表锁

  • 表共享读锁（Read Lock）：只读，阻塞其他事务的写操作。

  • 表独占写锁（Write Lock）：阻塞其他事务的读和写操作。

1
2
3
LOCK TABLES 表名 READ/WRITE;
-- 客户端断开连接也会释放锁
UNLOCK TABLES;
1
2
3
4
LOCK TABLES t1 READ;
SELECT * FROM t1; # ok
INSERT INTO t1 VALUES(7,1,1,1,1); -- 报错： Table 't1' was locked with a READ lock and can't be updated
UNLOCK TABLES;

元数据锁

MySQL5.5 中引入了 MDL，MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL 锁主要作用是维护表元数据的数据一致性，当表上有活动事务的时候，不可以对元数据进行写入操作。

当执行 SELECT、INSERT、UPDATE、DELETE 等语句时，添加的是元数据共享锁（SHARED READ／ SHARED WRITE），这两种 MDL 之间兼容。
当执行 ALTER TABLE 时，添加的是 EXCLUSIVE 锁，与其他的 MDL 排斥。

通过如下 SQL 查询数据库中的 MDL 情况：

1
SELECT object_type,object_schema,object_name,lock_type,lock_duration FROM performance schema.metadata_locks;

意向锁

不与行级锁冲突的表级锁。加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。

有了意向锁后，执行 DML 操作对涉及的行加行锁时，同时也会对该表加上意向锁，其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁。事务提交后，意向共享锁、 意向排他锁， 都会自动释放。

  • 意向共享锁/读锁（Intention Shared lock，IS Lock）：当事务想要获得一张表中某几行的读锁（行级读锁）时，InnoDB 存储引擎会自动地先获取该表的意向读锁（表级锁），申请成功后才能继续申请某行记录的行级读锁。添加行级共享锁时自动添加 。与表锁共享锁（read lock）兼容，与表锁排他锁（write lock）互斥。
  • 意向排他锁/写锁（Intention eXclusive lock，IX Lock）：当事务想要获得一张表中某几行的写锁（行级写锁）时，InnoDB 存储引擎会自动地先获取该表的意向写锁（表级锁），添加行级排他锁时自动添加。与表锁共享锁（read lock）及表锁排他锁（write lock）都互斥，意向锁之间不会互斥。

通过如下 SQL 查询数据库中的 IL 情况：

1
SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM performance_schema.data_locks;

eg：

事务 A 准备获取第 6 行的排他锁：事务 A 获取了 users 表上的意向排他锁。事务 A 获取了 id 为 6 的数据行上的排他锁。

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
SELECT * FROM users WHERE id = 6 FOR UPDATE;

事务 B 想要获取 users 表的共享锁：事务 B 检测到事务 A 持有 users 表的意向排他锁，事务 B 对 users 表的加锁请求被阻塞（排斥）。

1
LOCK TABLES users READ; -- 阻塞

事务 C 想获取 users 表中第 5 行的排他锁：事务 C 申请 users 表的意向排他锁，事务 C 检测到事务 A 持有 users 表的意向排他锁，因为意向锁之间并不互斥，所以事务 C 也获取到了 users 表的意向排他锁。因为 id 为 5 的数据行上不存在任何排他锁，最终事务 C 成功获取到了该数据行上的排他锁。

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
SELECT * FROM users WHERE id = 5 FOR UPDATE;

行级锁

应用在 InnoDB 引擎中，行锁是通过对索引上的索引项加锁来实现的。

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，使用 Next-Key Lock 进行搜索和索引扫描，以防止幻读。

  1. 针对唯一索引上的等值查询，对已存在的记录进行等值匹配时，将会自动优化为行锁
  2. 针对唯一索引上的等值查询，给不存在的记录加锁时，优化为间隙锁
  3. 针对非唯一普通索引上的等值查询，由于可能存在多个符合条件的记录，沿 B+ 树叶子节点双向链表向右遍历最后一个值不满足查询需求时，临键锁退化为间隙锁
  4. 针对唯一索引上的范围查询，会访问到不满足条件的第一个值为止，给这个值和正无穷都加上临键锁
  5. InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁。

行级锁包括：

  • 行锁（Record Lock）：锁定单个行记录，防止其他事务对改行进行 UPDATE、DELETE，在 RC、RR 隔离级别下都支持。
  • 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行INSERT，产生幻读。在 RR 隔离级别下都支持。
  • 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap。 在 RR 隔离级别下支持。

行锁

共享锁（S）：允许该事务和其他事务读取该行，阻止其他事务获得相同数据的排它锁，

排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据的共享锁和排他锁。

普通 SELECT 语句执行时，不加锁。INSERT、UPDATE、DELETE 类型的 SQL 语句执行时，自动加排他锁；SELECT … FOR UPDATE 执行时，即手动在 SELECT 之后加 FOR UPDATE 的语句执行时，加排他锁。

SELECT … LOCK IN SHARE MODE 执行时，即手动在 SELECT 之后加 LOCK IN SHARE MODE 的语句执行时，加共享锁。

查看行锁的语句和查看意向锁的语句一样：

1
SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM performance_schema.data_locks;

eg1：

针对唯一索引上的等值查询，对已存在的记录进行等值匹配时，将会自动优化为行锁

事务 A 获取第 99 行的共享锁

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
SELECT * FROM t1 WHERE id=99 LOCK IN SHARE MODE;

事务 B 获取第 99 行的又一共享锁成功，与此时存在事务 A 在第 99 行的共享锁兼容。COMMIT 后释放该锁

1
2
3
4
SET autocommit = 0; -- 关闭自动提交
BEGIN;
SELECT * FROM t1 WHERE id=99 LOCK IN SHARE MODE;
COMMIT;

事务 C 尝试获取第 99 行的排他锁时阻塞，因为此时存在事务 A 在第 99 行的共享锁，与排他锁互斥

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
UPDATE t1 SET c1=66 WHERE id=99;  -- 阻塞

当事务 A 释放共享锁后，事务 C 获取到排他锁

1
COMMIT;

事务 D 尝试获取第 99 行的共享锁和排他锁时都会被阻塞，因为此时存在事务 C 在第 99 行的排他锁，与共享锁和排他锁互斥

1
2
3
4
SET autocommit = 0; -- 关闭自动提交
BEGIN;
SELECT * FROM t1 WHERE id=99 LOCK IN SHARE MODE;  -- 阻塞
UPDATE t1 SET c1=66 WHERE id=99;  -- 阻塞

eg2：

c99 字段没有索引

事务 A 尝试获取第 99 行的排他锁，由于 c99 字段没有索引，升级为表锁

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
UPDATE t1 SET c1=66 WHERE c99=99;

事务 B 尝试获取第 98 行的排他锁，阻塞

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
UPDATE t1 SET c1=66 WHERE c99=88;  -- 阻塞

间隙锁和临键锁

间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

eg：

针对唯一索引上的等值查询，给不存在的记录加锁时，优化为间隙锁

数据如下，id 为主键：

1
SELECT * FROM t1;
id c1
1 11
5 55
9 99

事务 A 给不存在的第 7 行记录加锁，间隙锁锁住了 5 到 9 之间的间隙

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
UPDATE t1 SET c1=66 WHERE id=7;

此时查看锁信息如下

object_schema object_name index_name lock_type lock_mode lock_data
test t1 NULL TABLE IX（意向锁） NULL
test t1 PRIMARY RECORD X,GAP（间隙锁） 9

事务 B 尝试在 5 和 9 之间插入 8，由于之前的间隙锁的存在，阻塞住

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
INSERT INTO t1 VALUES(8,88); -- 阻塞

eg2：

针对非唯一普通索引上的等值查询，向右遍历时最后一个值不满足查询需求时，Next-Key Lock 退化为 Gap Lock。

数据如下，c2 为普通索引：

id c1 c2
1 11 1
3 55 3
7 99 7

事务 A 

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
SELECT * FROM t1 WHERE c2=3 LOCK IN SHARE MODE;

此时查看锁信息如下

object_schema object_name index_name lock_type lock_mode lock_data
test t1 NULL TABLE IS（意向锁） NULL
test t1 idx_t1_c2 RECORD S（临键锁） 3,3
test t1 PRIMARY RECORD S,REC_NOT_GAP（行锁） 3
test t1 idx_t1_c2 RECORD S,GAP（间隙锁） 7,7

eg3：

针对唯一索引上的范围查询，会访问到不满足条件的第一个值为止，给这个值和正无穷都加上临键锁

数据如下，c1 为主键

id c1
1 11
3 55
7 99

事务 A

1
2
3
SET autocommit = 0; -- 关闭自动提交
BEGIN;
SELECT * FROM t1 WHERE c1>=3 LOCK IN SHARE MODE;

此时查看锁信息如下

object_schema object_name index_name lock_type lock_mode lock_data
test t1 NULL TABLE IS（意向锁） NULL
test t1 PRIMARY RECORD S,REC_NOT_GAP（行锁） 3
test t1 PRIMARY RECORD S（临键锁） supremum pseudo-record
test t1 PRIMARY RECORD S（临键锁） 7

InnoDB 引擎

表空间（idb 文件）是 InnoDB 存储引擎逻辑结构的最高层，如果用户启用了参数 innodb_file_per_table（8.0 版本中默认开启），则每张表都会有一个表空间（xxx.ibd），一个 mysql 实例可以对应多个表空间，用于存储记录、索引等数据。

段（Segment），分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollbacks egment），数据段就是 B+ 树的叶子节点，索引段即为 B+ 树的非叶子节点。

区（Extent），表空间的单元结构，每个区的大小为 1M。默认情况下，一个区中一共有 64 个连续的页。

页（Page），是 InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16K。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

行（Row），InnoDB 存储引擎数据是按行进行存放的。

img

内存结构

InnoDB 的内存结构主要分为四大块：Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。

在专用服务器上，通常将多达 80% 的物理内存分配给缓冲池。

Buffer Pool

缓冲池，缓存磁盘上经常操作的真实数据。

缓冲池以 Page 为单位，底层采用链表数据结构管理 Page。根据状态，将 Page 分为三种类型：

  • free page：空闲页，未被使用
  • clean page：干净页，数据未被修改过
  • dirty page：脏页，数据被修改过，其中数据与磁盘的数据产生了不一致

Change Buffer

MySQL5.5 引入新特性，原 Inset Buffer 的加强版。

更改缓冲区，针对于非唯一二级索引页（存在大量随机 IO），在执行 DML 语句时，如果待修改的数据页还未缓存到 Buffer Pool 中，此时不会立刻操作磁盘，而是将数据变更存在更改缓冲区中，在未来数据被读取时，再将数据合并恢复到 Buffer Pool 中，之后将合并后的数据刷新到磁盘中。

Adaptive Hash Index

哈希索引在进行等值匹配时，一般性能高于 B+树，因为哈希索引一般只需要一次IO，但哈希索引不适合做范围查询、模糊匹配等。InnoDB 存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下哈希索引可以提升速度则建立哈希索引，称之为自适应哈希索引，

无需人工干预，系统根据情况自动完成。

参数：adaptive_hash_index，默认为 ON。

Log Buffer

日志缓冲区，用来保存要写入到磁盘中的日志数据（redo log 、undo log），默认大小为 16MB，定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 IO。

参数：
innodb_log_buffersize：缓冲区大小
innodb_flush_log at_trx_commit：日志刷新到磁盘时机，取值可选 0（每秒写入磁盘刷新），1（每次事务提交，默认值），2（每次事务提交，且每秒写入刷新）

磁盘结构

System Tablespace

系统表空间是 MySQL 中用于存储系统表和特殊表的默认表空间，包含了一些重要的系统表，通常存储在名为 ibdata1 的共享文件中。

  • mysql.user：存储了 MySQL 中的用户和权限信息。
  • mysql.db：存储了所有数据库的信息。
  • mysql.host：存储了允许连接到 MySQL 服务器的主机信息。
  • mysql.tables_priv：存储了表级别的权限信息。
  • mysql.columns_priv：存储了列级别的权限信息。
  • 其他系统表，用于存储 MySQL 服务器的配置和元数据信息。

File-Per-Table Tablespace

如果开启了 innodb_file_per_table 开关（默认打开），则每个表的文件表空间包含单个表的数据和索引（ibd 文件），并存储在文件系统上的单个数据文件中。

General Tablespaces

通用表空间允许将多个 InnoDB 表存储在一个或多个共享表空间文件中，而不是每个表都有自己的独立表空间文件（如 File-Per-Table Tablespace ）中。这些共享表空间文件可以在运行时动态添加或删除新的表。

eg：

首先创建通用表空间：

1
CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;

创建表时指定表空间为通用表空间：

1
CREATE TABLE XXX ... TABLESPACE ts_name;

Undo Tablespace

撤销表空间，MySQL 实例在初始化时会自动创建两个默认的 undo 表空间（初始大小 16M 的文件 undo_001 和 undo_002），用于存储 undo log。

Temporary Tablespace

InnoDB 使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据。

Doublewrite Buffer Files

用于提高数据保护和恢复机制，通过在写入实际数据文件之前将数据先写入到双写缓冲区文件，来减少数据损坏和页级别的IO 不一致性的风险。

双写缓冲区文件（ib_16384_0.dblwr 和 ib_16384_1.dblwr）的大小由配置参数 innodb_doublewrite_buffer_size 控制，默认值为 1MB，双写缓冲区文件在重启 MySQL 时会自动应用并删除。

会增加写操作的 IO 负载，因为每个写操作都需要写入两次。为了减少双写带来的性能影响，可以考虑将双写缓冲区文件放置在快速的存储介质上，如 SSD。

Redo Log

MySQL 中用于实现事务的持久性和恢复的关键组件，所有的修改操作都会先写入到 Redo Log，然后异步地刷新到磁盘上的数据文件。这样即使在写操作还未刷新到磁盘上的数据文件时发生崩溃，通过 Redo Log 的回放可以重新执行未完成的事务，确保数据的持久性。

Redo Log 由两个文件组成，通常为 ib_logfile0 和 ib_logfile1。大小由配置参数 innodb_log_file_size 控制，默认情况下为48 MB。

后台线程

将缓冲池中的数据适时刷新到磁盘中。

  • Master Thread：核心后台线程，主要负责将缓冲池中的数据一步刷新到磁盘，保证数据的一致性，它的工作包括：脏页的刷新、并插入缓冲、undo 页的回收等等
  • IO Thread：负责处理异步 IO 请求的回调，包括四种：4 个负责读操作的 Read thread、4 个负责写操作的 Write thread、1 个负责将日志缓冲区刷新到磁盘的 Log thread、1 个负责将写缓冲区内容刷新到磁盘的 Insert buffer thread
  • Purge Thread：主要用于回收事务已经提交了的 undo log
  • Page Cleaner Thread：协助 Master Thread 刷新脏页到磁盘的线程，减轻 Master Thread 的工作压力，减少阻塞

MVCC

当前读

每次对行数据进行读取时，加共享锁。此时就不允许修改，但是允许其他事务读取，所以每次都可以读到最新的数据。
每次对行数据进行修改时，加排他锁，不允许其他事务读取和修改，这种情况下其他事务读取的数据也一定是最新的数据。
每次对范围行数据进行读取的时候，对这个范围加一个范围共享锁。
每次对范围行数据进行修改的时候，读这个范围加一个范围排它锁。

具体来看，select … lock in share mode（共享锁），select … for update、update、insert、delete（排他锁
）都是一种当前读。

快照读

简单的 SELECT（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

  • Read Committed：每次 SELECT，都生成一个快照读　
  • Repeatable Read：开启事务后第一个 SELECT 语句才是快照读的地方，而不是一开启事务就快照读
  • Serializable：快照读退化为当前读，每次读取都会加锁

MVCC 的具体实现

Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL 实现 MVCC 提供了一个非阻塞读功能。MVCC 的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

隐藏字段

InnoDB 会自动添加三个隐藏字段：

字段名 含义
DB_TRX_ID 最近修改事务 ID，记录插入这条记录或最后一次修改该记录的事务 ID
DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本，用于配合 undo log，指向上一个版本
DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。如果表有主键，则不会添加该隐藏字段

不同事务或相同事务对同一条记录进行修改，会导致该记录的 undo log 生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。记录的 DB_ROLL_PTR 指向链表头部。

Readview

ReadView（读视图）是快照读 SOL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

不同的隔离级别，生成 ReadView 的时机不同，：

  • READ COMMITTED：在事务中每一次执行快照读时生成 ReadView。
  • REPEATABLE READ：仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView。

包含四个核心字段：

字段 含义
m_ids 当前活跃的事务 ID 集合
min_trx_id 最小活跃事务 ID
max_trx_id 预分配事务 ID，当前最大事务 ID+1
creator_trx_id ReadView 创建者的事务 ID

在 ReadView 中规定了版本链数据的访问规则，trx_id 代表当前 undo log 版本链对应事务 ID：

  1. trx_id == creator_trx_id。可以访问该版本，说明数据是在当前事务更改的。
  2. trx_id < min_trx_id。可以访问该版本，说明数据已经提交。
  3. trx_id > max_trx_id。不可以访问该版本，说明该数据相关的事务是在该 ReadView 生成后再开启的。
  4. min_trx_id <= trx_id <= max_trx_id。如果 trx_id 不在 m_ids 中，可以访问该版本，说明数据已经提交。