Ammar's Blog
微信号：aicjaish
首页 标签 分类 归档 留言 关于
MySQL进阶
|Java数据库
字数总计: 9.7k|阅读时长: 35 分钟

Mysql的结构图

逻辑结构

系统层

  • 连接器(Connectors)
  • 系统管理工具 Management Services & Utillities
  • 连接池

业务层

  • SQL Interface
    • 接收 SQL DML DDL
  • 解析器 Parser
    • select * from t1
    • 词法分析 分词 语法树
    • 语法分析 ：符合 SQL 的语法，SQL 92(标准语法) limit MYSQL 自己的语法
  • 查询优化器(Optimizer)
    • mysql 认为你写的 SQL 不够完美
    • 优化：索引，只使用最优索引，
    • 多表关联：
    • where 从左到右 mysql 从右到左 Oracel，过滤力度最大先执行。通过 explain 分析谁的过滤力度比较大。
  • 查询缓存(Cache 和 buffer)
    • 把查询结果存起来， SQL -> hash 后的值 唯一 则表示缓存中有。
    • 当数据库的数据发生改变时，新增、删除、更新后清除缓存。mysql8 以后就没有了。

存储引擎(Pluggable Storage Engines)

以表为单位，默认 InnoDB，InnoDB/Memory/MyISAM

存储引擎 说明
MyISAM 高速引擎，拥有较高的插入，查询速度，但不支持事务、不支持行锁、支持3种不同的存储格式。包括静态型、动态型和压缩型。
InnoDB 5.5版本后MySQL的默认数据库，支持事务和行级锁定，事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全，比 MyISAM 处理速度稍慢。
Memory 内存存储引擎，拥有极高的插入，更新和查询效率，但是会占用和数据量成正比的存储空间，只在内存上保存数据，意味着数据可能会丢失。

Memory 的使用场景：存储报表，临时表时 用到 memory。

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

一条 sql 语句的执行流程


连接模块校验连接是否正确，用户模块校验连接用户是否有表操作权限，访问控制模块校验用户是否有命令执行权限。

物理结构

MySQL 通过文件存储数据和索引的。
MySQL 从物理上分为日志文件和数据索引文件
日志文件采用顺序 IO 方式存储、数据文件采用随机 IO 方式存储。

日志文件

错误日志 Error Log

默认开启，且 5.5.7 以后无法关闭，记录运行过程中遇到的所有严重的错误信息，一级 MySQL 每次启动和关闭的详细信息。

mysql8.0.2 中文件夹为：/usr/local/mysql/data/mysqld.local.err

二进制日志 bin log

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

通用查询日志 general query log

啥都记录，耗性能，生产中不建议开启。

慢查询日志 slow_query_log

1
2
3
4
5
6
7
8
9
10
11
Show variables like '%slow_query%'; -- 可以用这个查询所有的变量

//第一步
set global log_output='TABLE'; -- 开启慢日志,纪录到 mysql.slow_log 表
set global long_query_time=2; -- 设置超过2秒的查询为慢查询
set global slow_query_log='ON';-- 打开慢日志记录

//第二步 运行一下比较慢的功能,执行下面的语句
select convert(sql_text using utf8) sql_text from mysql.slow_log -- 查询慢sql的 日志
//第三步 记得关上日志
set global slow_query_log='OFF'; -- 如果不用了记得关上日志

下面是清除日志的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SET GLOBAL slow_query_log = 'OFF';

ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;

CREATE TABLE `mysql`.`slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumblob NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

SET GLOBAL slow_query_log = 'ON';
DROP TABLE mysql.slow_log_drop;

重做日志 redo log、回滚日志 undo log 中继日志 relay log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name                          | Value                                  |
+----------------------------------------+----------------------------------------+
| log_bin                                | ON                                     |
| log_bin_basename                       | /usr/local/mysql/data/binlog           |
| log_bin_index                          | /usr/local/mysql/data/binlog.index     |
| log_bin_trust_function_creators        | OFF                                    |
| log_bin_use_v1_row_events              | OFF                                    |
| log_error                              | /usr/local/mysql/data/mysqld.local.err |
| log_error_services                     | log_filter_internal; log_sink_internal |
| log_error_suppression_list             |                                        |
| log_error_verbosity                    | 2                                      |
| log_output                             | FILE                                   |
| log_queries_not_using_indexes          | OFF                                    |
| log_raw                                | OFF                                    |
| log_slave_updates                      | ON                                     |
| log_slow_admin_statements              | OFF                                    |
| log_slow_extra                         | OFF                                    |
| log_slow_slave_statements              | OFF                                    |
| log_statements_unsafe_for_binlog       | ON                                     |
| log_throttle_queries_not_using_indexes | 0                                      |
| log_timestamps                         | UTC                                    |
+----------------------------------------+----------------------------------------+

数据文件

InnoDB 数据文件

1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE '%datadir%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
  • .frm 文件：存放与表相关的数据信息，包括表结构的定义信息
  • .ibd 使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件
  • ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。

MyIsam 数据文件

  • .frm 主要存放表结构定义信息
  • .myd文件 存储表数据信息
  • .myi文件 主要存储表数据文件中任何索引的数据树

MySQL 索引

优势：提高数据检索效率，降低IO成本。通过索引列队数据进行排序，降低CPU消耗。
劣势：占据磁盘空间，降低更新表的效率。

索引的存储结构 B B+ TREE

  • 不同的存储引擎，会使用不同的索引
  • MyISAM 和 InnoDB 存储引擎：只支持 B+ TREE 索引，默认使用BTREE，不能更换
  • Memory/Heap 存储引擎：支持HASH和BTREE索引

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

B树和B+树最大的区别在于非叶子节点是否会存储数据。
B树是非叶子节点和叶子节点都会存储数据。B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，这些数据都是有指针指向的，也就是有顺序的。

非聚集索引 MyISAM

聚集索引 InnoDB

回表和索引覆盖

如果查询条件为主键，则只需要扫描一次 B+ 树即可通过聚集索引定位到要查找的行记录数据。
如果查询条件为普通索引，需要扫描两次 B+ 树，第一次扫描通过普通索引定位到聚集索引的值，然后第二次扫描通过聚集索引的值定位到要查找的行记录数据。这种就是回表查询。它的性能较扫描一遍索引树更低。
索引覆盖： 只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。
如何实现索引覆盖？

常见的方法：将被查询的字段，建立到联合索引里去。

索引使用场景

需要创建索引：

  1. 主键自动创建唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引
  4. 查询中排序的字段，应该创建索引 B + tree 有顺序
  5. 覆盖索引 好处是不需要回表。组合索引达到覆盖索引
  6. 统计或者分组字段，应该创建索引

不需要创建索引：

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

组合索引

由多个字段组成的索引，组合索引

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

由3个字段创建的组合索引。组合索引的结构：

组合索引的使用顺序就是创建顺序，一棵树上有多个字段。
优势：省空间、容易形成覆盖索引(不回表查询)
当使用 like 常量% 使用索引， like %常量 不使用索引
从左到右匹配直到遇到范围查询 > < between 索引失效。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
ALTER TABLE t1 ADD INDEX idx_a_b_c_D(a, b, c, d);

show index from t1;

+----------+----------+--------+------+------------+---------+---------------+ 
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | 
Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 
+-------+----------+----------+--------+------+------------+---------+
| t1 | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | | 
| t1 | 1 | idx_a_b_c_d | 1 | a | A | 1 | NULL | NULL | YES | BTREE | | | 
| t1 | 1 | idx_a_b_c_d | 2 | b | A | 1 | NULL | NULL | YES | BTREE | | | 
| t1 | 1 | idx_a_b_c_d | 3 | c | A | 1 | NULL | NULL | YES | BTREE | | | 
| t1 | 1 | idx_a_b_c_d | 4 | d | A | 1 | NULL | NULL | YES | BTREE | | | 
+-------+------------+-------------+--------------+-------------+-----------+

-- 上面的表表示一个索引，idx_a_b_c_d，上面有4个列，顺序为 abcd。

explain select * from t1 where a=1 and b=1 and c=1 and d=1 ;

+----+-------------+-------+------+---------------+-------------+---------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+-------+------+---------------+-------------+---------+
| 1 | SIMPLE | t1 | ref | idx_a_b_c_d | idx_a_b_c_d | 20 | const,const,const,const | 1 | Using index | 
+----+-------------+-------+------+---------------+-------------+---------+

只要 type = ref 就表示使用了索引。

索引失效

explain 出来的信息有10列，分别是
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

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
-- 用户表
CREATE TABLE tuser(
id INT PRIMARY KEY,
loginname VARCHAR(100),
name VARCHAR(100),
age INT,
sex char(1),
dep INT,
address varchar(100)
);

-- 部门表
CREATE TABLE tdep(
id INT PRIMARY KEY,
name VARCHAR(100)
);

-- 地址表
CREATE TABLE taddr(
	id INT PRIMARY KEY,
	addr VARCHAR(100)
);

-- 创建普通索引
ALTER TABLE tuser ADD INDEX idx_dep(dep);
-- 创建唯一索引
ALTER TABLE tuser ADD UNIQUE INDEX idx_loginname(loginname);

-- 创建组合索引
ALTER TABLE tuser ADD INDEX idx_name_age_sex(name, age, sex);

-- 创建全文索引
ALTER TABLE taddr ADD FULLTEXT ft_addr(addr);

id

每个 select 语句都会自动分配一个唯一标识符。
表示查询中操作表的顺序，id越大，优先级越高。

1
2
3
4
5
6
7
explain select (select name from tuser) from tuser ;
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | tuser | NULL       | index | NULL          | idx_dep          | 5       | NULL |    1 |   100.00 | Using index |
|  2 | SUBQUERY    | tuser | NULL       | index | NULL          | idx_name_age_sex | 312     | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+

select_type

表明查询类型。

simple 简单类型

1
2
3
4
5
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tuser | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

primary&subquery 主从类型

一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只 有一个
除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

1
2
3
4
5
6
7
explain select (select name from tuser) from tuser ;
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | tuser | NULL       | index | NULL          | idx_dep          | 5       | NULL |    1 |   100.00 | Using index |
|  2 | SUBQUERY    | tuser | NULL       | index | NULL          | idx_name_age_sex | 312     | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+

DEPENDENT SUBQUERY 主从有字段关联

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

1
2
3
4
5
6
7
explain select id,name,(select name from tdep a where a.id=b.dep) from tuser b;
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | PRIMARY            | b     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    1 |   100.00 | NULL  |
|  2 | DEPENDENT SUBQUERY | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | javaweb.b.dep |    1 |   100.00 | NULL  |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+

union 集合

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

1
2
3
4
5
6
7
8
explain select * from tuser where sex='1' union select * from tuser where sex='2';
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|    1 | PRIMARY      | tuser      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where     |
|    2 | UNION        | tuser      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

dependent union 这个查询受外部查询的影响

union result 包含 union 的结果集

因为其不需要参与查询，所以 id 字段为 null

derived 派生表

只出现在一种情况， from 后面带 select

1
2
3
4
5
6
7
explain select * from (select * from tuser where sex='1') b;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL |
|  2 | DERIVED      | tuser | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

type

好->差：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 表中只有一行数据或者是空表。
  • const 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库 也叫做唯一索引扫描
  • eq_ref **主键关联或者唯一性索引关联(=号两边都要建立索引)**，此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查 询的比较操作通常是 ‘=’, 查询效率较高。
  • ref 针对非唯一性索引，使用等值（=）非主键查询。或者是使用了最左前缀规则索引的查询。
  • fulltext 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代 价，优先选择使用全文索引
1
2
3
4
5
6
explain select * from taddr where match(addr) against('bei');
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type     | possible_keys | key     | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | taddr | NULL       | fulltext | ft_addr       | ft_addr | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
  • fulltext 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代 价，优先选择使用全文索引
1
explain select * from taddr where match(addr) against('bei');
  • range 范围查询、前缀索引
  • index 索引覆盖，不回表。
1
2
3
4
5
6
7
explain select loginname from tuser;
type = index

explain select loginname, name from tuser;
type = all

-- name 和 loginname 不在同一棵索引树上，说明需要回表查询。
  • ALL

全表扫描数据文件，然后再 server 层进行过滤，返回符合要求的记录。如果有索引，则会在存储引擎层 storage engines 层中去过滤。

小 tips

所以在查询时，一定不要写 selct * from tuser，肯定需要回表。
如果需求是 select * ，select * from tuser order by id; 全表扫描，+ order by id ， type = index 了。

索引不光是走查询用的，还可以减少回表，减少索引扫描。

组合索引

在创建组合索引时，会从创建组合索引的第一个字段开始找，如果检索时发现组合索引第一个字段都没有，则直接检索后面的字段查询时不会用到索引。type = ALL

possible_keys & key & key_len

  • possible_keys: 此次查询可能选用的索引，一个或多个
  • key: 使用的索引
  • key_len: 看组合索引的使用情况，索引长度
1
2
3
4
5
6
explain select * from tuser WHERE id=1 and loginname='zhy' and name = 'zhaoyun';
+----+-------------+-------+------------+-------+----------------------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys                          | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tuser | NULL       | const | PRIMARY,idx_loginname,idx_name_age_sex | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+----------------------------------------+---------+---------+-------+------+----------+-------+

extra

额外信息

  • NULL :效率最高

distinct(效率低)

会生成一张原表的临时表，然后一个一个比对。如果一定要用的话，需要在条件查询完后使用

using filesort

排序时无法使用索引，常见于 order by，group by 语句。MySQL 中无法利用索引排序的操作称为 文件排序。

using index

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

  • 表示相应的 select 查询中使用到了 覆盖索引(Covering Index)，效率不错。
  • 如果同时出现 using where，说明索引被用来执行查找索引键值。

using where

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

  • 查询条件无索引
  • 索引失效

查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后 server 层根据检查条件进行过滤再返回真正符合的数据。 5.6.x之后，支持ICP特性(查询条件下推到存储引擎层)，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就减少了存储引擎扫描的记录数量。 extra 显示 using index condition

索引失效分析

  1. 全值匹配：组合所有所有条件都有，效率最高
  2. 最左前缀法则：组合索引，带头索引不能没有，中间索引页不能断，如果中间索引中断，则只有带头的索引生效，其他索引失效。
  3. 不在索引上做计算
  4. 范围条件查询后面的条件 索引失效
  5. 减少 select * 尽量使用索引覆盖
  6. 索引字段上不要使用 不等 != 或 <>
  7. 主键索引字段上不能判断 null
  8. 索引字段使用 like 不以通配符开头，如果一定要用 like ‘%字符串%’，则 select (组合索引，索引覆盖) 提高查询性能
  9. 索引字段字符串不能省略单引号
  10. 索引字段不要使用 or

Mysql锁

表锁

由 MySQL SQL layer 层实现

1
2
show status like 'table%'; 
-- 查询表使用锁的状态

Table Read Lock 表共享读

1
lock table 表名 read, 表名2 read, 其他;

Table Write Lock 表独占写

1
2
3
lock table 表名 write, 表名2 write, 其他;
show open tables; 查看表锁情况
unlock tables; 删除表锁
1
2
3
4
5
6
7
8
session1: lock table mylock read; -- 给mylock表加读锁 
session1: select * from mylock; -- 可以查询 
session1：select * from tdep; --不能访问非锁定表 
session2：select * from mylock; -- 可以查询 没有锁 
session2：update mylock set name='x' where id=2; -- 修改阻塞,自动加行写锁 
session1：unlock tables; -- 释放表锁 
session2：Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执行完成 
session1：select * from tdep; --可以访问

元数据锁

MDL (metaDataLock) 元数据：表结构
在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结 构变更操作的时候，加 MDL 写锁。

1
2
3
4
5
6
7
8
session1: begin;--开启事务 
select * from mylock;--加MDL读锁 

session2: alter table mylock add f int; -- 修改阻塞 
session1：commit; --提交事务 或者 rollback 释放读锁 
session2：Query OK, 0 rows affected (38.67 sec) --修改完成 

Records: 0 Duplicates: 0 Warnings: 0

行级锁

InnoDB 存储引擎实现，分三种

  • 记录锁(Record Locks): 锁定索引中一条记录
  • 间隙锁(Gap Locks): 锁定记录前、记录中、记录后的行
  • Next-Key 锁：记录锁 + 间隙锁

共享读锁

允许一个事务读一行，阻止其他事务获得相同数据集的排他锁。

1
select * from table_name where ... LOCK IN SHARE MODE -- 共享读锁 手动添加

排他写锁

允许获得排他写锁的事务更新数据，组织其他事务获得相同数据集的共享读锁和排他写锁。

  1. DML会自动加 对于 UPDATE、DELETE、和INSERT语句，InnDB会自动给涉及数据加排他锁
  2. 手动加
1
select * from table_name where ... FOR UPDATE

行锁演示

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过 索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

行读锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查看行锁状态
show STATUS like 'innodb_row_lock%';

session1: begin; --开启事务未提交 
select * from mylock where ID = 1 lock in share mode; -- 手动加id=1的行锁,使用索引 

session2：update mylock set name='y' where id=2; -- 未锁定该行可以修改 
session2：update mylock set name='y' where id=1; -- 锁定该行修改阻塞 
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 锁定超时 

session1: commit; --提交事务 或者 rollback 释放读锁 
session2：update mylock set name='y' where id=1; --修改成功 

Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

where ID=1 lock in share mode; --手动加id=1的行读

-- 注：使用索引加行锁 ，未锁定的行可以访问
1
2
3
4
5
6
7
8
9
10
session1: begin;--开启事务未提交 --手动加name='c'的行读锁,未使用索引 
select * from mylock where name='c' lock in share mode; 

session2：update mylock set name='y' where id=2; -- 修改阻塞 未用索引行锁升级为表锁 
session1: commit; --提交事务 或者 rollback 释放读锁 
session2：update mylock set name='y' where id=2; --修改成功 

Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

-- 注：未使用索引行锁升级为表锁

行写锁

1
2
3
4
5
6
7
8
9
10
11
12
session1: begin;--开启事务未提交

--手动加id=1的行写锁, 
select * from mylock where id=1 for update;

session2：select * from mylock where id=2 ; -- 可以访问 
session2: select * from mylock where id=1 ; -- 可以读 不加锁 
session2: select * from mylock where id=1 lock in share mode ; -- 加读锁 被阻塞 
session1：commit; -- 提交事务 或者 rollback 释放写锁 
session2：执行成功

主键索引产生记录锁

间隙锁

非主键索引产生间隙锁，主键范围产生间隙锁。

死锁

事务

  • Atomicity(原子性):构成事务的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行
  • Consistency(一致性):数据库在事务执行前后状态必须是稳定的或一致的
  • Isolation(隔离性):事务之间不会相互影响 由锁机制和 MVCC(多版本并发控制) 机制来实现:(读不加锁、读写不冲突)
  • Durability(持久性):事务执行成功后必须全部写入磁盘。

BEGIN 或 START TRANSACTION；显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK ，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的 所有修改称为永久性的；
ROLLBACK 有可以使用 ROLLBACK WORK`，不过二者是等价的。回滚会结束用户的事务，并撤销正在 进行的所有未提交的修改；

InnoDB 架构图


InnoDB 存储引擎由内存池，后台线程池和磁盘文件三部分组成。

为什么会有缓存(insert buffer) 存在?

InnoDB 会直将内存中的数据写入磁盘，就是执行 I/O 操作，此操作比较耗时，在执行sql时，MySQL程序在线程A中将内存中进行数据修改，把在内存中修改后的数据页生成一个缓存页。然后用另一个线程B做 I/O 操作。这是一个非常典型的生产者消费者模型。一条线程读，一条线程写。

Page 是 InnoDB 存储的最基本结构，也是 InnoDB 磁盘管理的最小单位。

插入缓冲(Insert Buffer)

复杂：主键排序、索引、树、插入算法。所以需要单独使用线程

自适应哈希(Adaptive Hash Index)

hash 结构， key value。

InnoDB 会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率

lock info 锁信息

行锁、表锁

Data Dictionary 数据字典信息

元数据信息，表结构、数据库名或表名。字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容。

Redo log Buffer 重做日志缓冲

Redo log: ib_logfile0 ib_logfile1 默认为8M innodb_log_buffer_size 控制大小

Redo log 重做日志：
数据以所及I/O的形式存储，性能较低。利用日志存储，日志是顺序I/O，性能就高了，所以要存储数据，则先存储数据的日志，一旦内存崩了，可以从日志找数据。

数据一旦被存入 Redo log 中就认为存储成功了。保证了数据的可靠性。预写机制

Buffer Pool 先将数据存入 Redo log 中，存入后就认为成功了，然后再择时将数据存入文件系统。

重做日志落盘机制

Force Log at Commit 实现事务的持久性，即当事务提交时，必须先将事务的所有日志写入到重做日志文件进行持久化，然后事务的提交操作才算完成。为了确保每次日志都写入到重做日志文件，每次重做日志后，必须调用操作系统的 fsync 方法，将缓冲文件写入磁盘。

可以通过参数 innodb_flush_log_at_trx_commit 参数来配置

该参数默认值为1，表示事务提交必须进行一次 fsync 操作，1最安全。 0和2，虽然性能较高但是丧失事务的一致性。

检查点 checkPoint

表示脏页写入到磁盘的时机，检查点也意味着脏页的写入。

sharp checkpoint

完全检查点：当数据库正常关闭时，会触发。把所有脏页都写入到磁盘上。

fuzzy checkpoint

正常使用时，模糊检查点，部分页写入磁盘。

master thread checkpoint:定时刷新，以每秒或者每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的

flush_Iru_list checkpoint: 读取 Iru(Least Recently Used) list，找到脏页，写入磁盘。最近最少使用的数据写入磁盘。

async/sync flush checkpoint: Redo log Buffer 快满时(16M的两个文件)，会批量的触发数据页回写，这个事件触发的时候又分为异步和同步，不可覆盖的 redolog 占 log file的比值 75% -> 异步 90% -> 同步。

dirty page too much checkpoint: 默认是脏页占比 75%的时候就会触发落盘。

Double Write 双写

该机制带来了数据的可靠性。

Double Write 由两部分组成，一部分是内存中的 double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。

对缓冲池的脏页刷新时，不直接写磁盘，而是通过 memcpy 函数将脏页先复制到内存中的 double write buffer 区域，之后通过 double write buffer 在分两次，每次 1M 顺序写入共享表空间的物理磁盘，然后马上调用 fsync 同步磁盘，避免操作系统缓冲写带来的问题。

完成 double write 页的写入后，再将 double write buffer 中的页写入各个表空间文件中。如果操作系统在将页写入磁盘的过程发生了崩溃，恢复过程中， InnoDB 存储引擎可以从共享表空间中的 double write 中找到该页的副本，复制到表空间文件中，再应用 redolog

UndoLog

数据库崩溃重启后需要从 redo log 中把未落盘的脏页数据恢复出来，重新写入磁盘，保证用户的数据不丢失。在崩溃恢复中还需要回滚没有提交的事务。回滚操作需要 undo log 的支持，undo log 的完整性和可靠性需要 redo log 保证。崩溃恢复先做 redo 恢复数据，然后做 undo 回滚。

在事务进行的过程中，undo log 记录了每个操作前的状态。下图说明数据和回滚日志的逻辑存储结构

undo log 存放在数据库内部的特殊的段，称为回滚段，在共享表空间中。redo log 是物理日志，记录数据库页的物理修改操作，所以 undo log 的写入也会产生 redo log，也就是 undo log 也需要持久性的保护。数据库事务的整个流程如下：

事务进行过程中，每次sql语句执行，都会记录 undo log 和 redo log，然后更新数据形成脏页，然后 redo log 落盘，undo log 和脏页按照 checkpoint 进行落盘，落盘后相应的 redo log 就可以删除了。事务还未 COMMIT，如果发生崩溃，则首先检查 checkpoint 记录，使用相应的 redo log 进行数据和 undo log 的恢复，然后查看 undo log 的状态发现事务尚未提交，然后就使用 undo log 进行事务回滚。事务执行 COMMIT 操作时，会将本事务相关的所有 redo log 都进行落盘，只有所有 redo log 落盘成功，才算 COMMIT 成功。然后内存中的数据脏页继续按照 checkpoint 进行落盘。如果此时发生了崩溃，则只使用redo log恢复数据。

事务的隔离级别

原子性，持久性和一致性主要是通过redo log、undo log 和 Force Log at Commit 机制机制来完成 的。redo log用于在崩溃时恢复数据，undo log 用于对事务的影响进行撤销，也可以用于多版本控制。而 Force Log at Commit 机制保证事务提交后 redo log 日志都已经持久化。

事务的隔离性由多版本控制机制和锁实现。

四大隔离级别

事务隔离级别

  1. Read uncommited 任何情况无法保证
  2. Read committed RC 读已提交，可避免脏读
  3. Repeatable read RR 可避免脏读、不可重复读的发生
    • InnoDB 的 RR 还可以解决幻读，主要原因是 Next-Key 锁，只有 RR 才能使用 Next-Key锁
  4. Serializable 串行化：可避免脏读、不可重复读、幻读的发生。 MVCC 降级为 Locking-Base CC
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
--创建账户表并初始化数据 
create table tacount(
id int , 
aname varchar(100),
acount int , 
primary key(id)); 
alter table tacount add index idx_name(aname); 
insert into tacount values(1,'a',1000); 
insert into tacount values(2,'b',1000);

--设置隔离级读未提交（read-uncommitted） 
set session transaction isolation level read uncommitted;

--session 1 
start transaction ; 
select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+

--session 2 
start transaction; 
update tacount set acount=1100 where aname='b';

--session 1 
select * from tacount where aname='b'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 2 | b | 1100 | 
+----+-------+--------+

--设置隔离级别为串行化（serializable） 死锁演示 
set session transaction isolation level serializable;

--session 1 
start transaction;
select * from tacount where aname='a';

--session 2 
start transaction ;
update tacount set acount=900 where aname='b';

-- session 1 
select * from tacount where aname='b';

-- session 2 
update tacount set acount=1100 where aname='a'; 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

MVCC 机制

使用 MVCC 机制 RR 隔离级别下的演示：

MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数 据的前一个或者前几个历史版本，保证了ACID中的I特性（隔离性)。

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
-- 显示当前隔离级别为 REPEATABLE-READ 
select @@tx_isolation;

-- MySQL默认隔离级别

-- session 1
start transaction ; 
select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+

-- session 2 
start transaction; update tacount set acount=1100 where aname='a';

-- session 1 
select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+

-- session 2 提交事务 
commit;

-- session 1 显示在session 1 事务开始时的数据 
select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+
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
38
39
-- 设置事务隔离级别为REPEATABLE-COMMITTED

-- 读已提交

-- session 1 
set session transaction isolation level read committed; 
start transaction ; 
select * from tacount where aname='a'; 
+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+

-- session 2 
set session transaction isolation level read committed; 
start transaction; 
update tacount set acount=1100 where aname='a';

-- session 1 
select * from tacount where aname='a'; 

+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1000 | 
+----+-------+--------+

-- session 2 提交事务 
commit;

-- session 1 显示最新事务提交后的数据 
select * from tacount where aname='a'; 

+----+-------+--------+ 
| id | aname | acount | 
+----+-------+--------+ 
| 1 | a | 1100 | 
+----+-------+--------+

MVCC 的实现

在 MVCC 并发控制中，读操作可以分成两类：快照读与当前读
Multiversion concurrency control

快照读：简单的 select 操作，属于快照读，不加读锁，读历史版本
当前读：特殊的操作，插入、更新、删除操作，属于当前读，需要加锁，加行写锁，读当前版本

一致性非锁定读(consistent nonlocking read)

是指 InnoDB 存储引擎通过多版本控制 MVCC 读取当前数据库中行数据的方式
如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。相反的 InnoDB 会去读取行的一个最新可见的快照。

如上图所示，当回话B提交事务后，回话A再次运行 select * from test where id = 1，在不同的事务隔离级别下结构就不一样了。

MVCC 在 mysql 中的实现依赖 undo log 与 read view

Undo Log

InnoDB 行记录有三个隐藏字段，分别是 rowid、事务号db_trx_id和回滚指针 db_roll_ptr，其中 db_trx_id 表示最近修改的事务的 id，db_roll_ptr 指向回滚段中的 undo log

根据行为，undo log 分为 insert undo log 和 update undo log

insert undo log，rollback 直接删除，不需要进行 purge 操作。

delete/update undo log 不能再事务提交的时候进行删除/更改，而是将事务提交时放入 history list 上，等待purge线程进行最后的操作。

purge线程会在没有其他人读该条数据时进行执行。

初始状态

当事务2使用 update 修改该行数据时，首先使用排他锁 锁定该行，将当前行的值复制到 undo log 中，再真正的修改当前行的值，最后填写事务 id，使用回滚指针指向 undo log 中修改前的行

事务链表

MySQL 中的事务在开始到提交这段过程中，都会保存到 trx_sys 的事务链表中，

ct-trx -> trx11 -> trx9 -> trx9 -> trx5 -> trx3 这是一个基本链表

事务链表存储的是未提交的事务，事务一旦提交，则会从事务链表中摘除。

RR 隔离级别下，每个事务开始的时候，会将当前系统的所有活跃事务拷贝到一个列表中 read view
RC 隔离级别下，每个语句开始的时候，会将当前系统中的所有活跃事务(最新版本)拷贝到一个列表中 read view

1
show engine innodb status -- 事务列表

ReadView

当前事务(读)能读哪个历史版本?

Read View 是事务开启时 当前所有事务的集合，存储了当前 Read View 中最大事务 ID 及 最小事务 ID

1
ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3;

ct-trx 表示当前事务的id，对应上面的read_view数据结构如下。

1
2
3
4
read_view->creator_trx_id = ct-trx; 
read_view->up_limit_id = trx3; 低水位 
read_view->low_limit_id = trx11; 高水位 
read_view->trx_ids = [trx11, trx9, trx6, trx5, trx3];

low_limit_id 当前活跃的事务的最大id，此id之前的的数据都没有提交。这些数据在 RR 下都不可见

up_limit_id 当前活跃事务的最小id，如果 row 的 db_trx_id < up_limit_id，事务已提交，数据可见。

ReadView 不同隔离级别的实现方式：

  • read-commited
1
2
3
4
if (trx->isolation_level <= TRX_ISO_READ_COMMITTED && trx->global_read_view)
{
	read_view_close_for_mysql(trx);
}

每次语句执行过程中，都关闭 read_view，重新再函数中创建当前的一份 read_view，这样就会将所有当前的事务获取到，可以获取到最新的提交。

  • repeatable read:

在 RR 级别下，创建事务 trx 结构时，会生成当前的 global read view，一直维持到事务结束，每一次查询都不会重新创建 read view，因此导致在事务中无法读到最新的提交。

Mysql 性能分析和优化

SQL 锁分析

RC

  • id 主键，只需要将主键 id 的记录加上 X 锁
  • id 唯一索引，先在索引中找到符合记录，取出主键，然后去主键索引中查找记录，找到加 X 锁
  • id 非唯一索引，找到匹配的记录，在记录上加 X 锁
  • id 无索引，全表扫描做过滤，直接加表锁。

RR

  • id 主键，同 RC
  • id 唯一索引，同 RC
  • id 非唯一索引，在索引中加入间隙锁(GAP)，同时在表中字段加入 X 锁，可以排除幻读的发生

  • id 无索引 + RR，X锁 + 间隙锁

复杂 SQL 分析

RR 下，针对一个复杂的 SQL，首先需要提取 where 条件
Index Key 确定的范围，需要加上 GAP 锁
Index Filter 过滤条件，视 MySQL 版本是否支持 ICP，支持则不满足 Index Filter 的记录不加 X锁，不支持则加

  • Table Filter 过滤条件，无论是否满足，都需要加 X 锁。

利用慢查询日志查询耗时比较长的 sql

1
2
3
4
5
6
7
8
9
cd /var/lib/mysql
# slow-log.log 就是慢查询日志，需要开启慢查询日志

vim /etc/my.cnf

# 找到 slow_query_log=ON
slow_query_log=ON
long_query_time=3 # 超过3秒算慢查询
slow_query_log_file=/var/lib/mysql/slow-log.log

mysqldumpslow

按照时间排序前10条里面含有左连接的查询语句

1
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

常用参数说明

参数 说明
-s: 表示按何种方式排序
c 访问计数
l 锁定时间
r 返回记录
t 查询时间
al 平均锁定时间
ar 平均返回记录数
at 平均查询时间
-t 是 top n 的意思，即为返回前面多少条数据
-g 后面可以写一个正则匹配模式，大小写不敏感

percana-toolkit

percona-toolkit 工具

1
2
3
4
5
6
wget https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz

tar -xf percona-toolkit-3.0.11_x86_64.tar.gz cd percona-toolkit-3.0.11
perl Makefile.PL
make
make install

服务器层面优化

减少 IO 次数，扩大 buffer pool

buffer pool 默认是 128M，扩大 buffer pool 理论上可以为 内存 的 3/4 或者 4/5，将数据保存在内存中，从内存读取数据。如何确定 innodb_buffer_pool_size 足够大？

1
2
3
4
5
6
7
8
9
10
show global status like 'innodb_buffer_pool_pages_%';
+----------------------------------+-------+
| Variable_name                    | Value |
| Innodb_buffer_pool_pages_data    | 8190  |
| Innodb_buffer_pool_pages_dirty   | 0     |
| Innodb_buffer_pool_pages_flushed | 12646 |
| Innodb_buffer_pool_pages_free    | 0     |   -- 0表示已经被用光了
| Innodb_buffer_pool_pages_misc    | 1     |
| Innodb_buffer_pool_pages_total   | 8191  |
+----------------------------------+-------+

修改 my.cnf
innodb_bufer_pool_size = 750M

降低磁盘写入次数

  • redo log 大则落盘次数少，innodb_log_file_size 设置成 innodb_buffer_pool_size * 0.25
  • 通用查询日志、慢查询日志可以不开 bin-log 开(防止删库)
  • 写 redo log 策略(一般不用)

提高磁盘读写速度 SSD

SQL 设计优化

根据业务需求。

  1. 针对统计分析功能，报表等实时性不高的业务，设计中间表，或者设计定时器，将每日需要统计的数据插入中间表中，再做汇总。
  2. 为减少关联查询，创建合理的冗余字段，考虑数据库的三范式和查询性能的取舍，创建冗余字段需要注意数据一致性问题
  3. 如果字段太多的大表，考虑拆表
  4. 表中经常不使用的字段或者存储数据比较多的字段，考虑拆表，比如商品表中会存储商品介绍，此时可以将商品字少字段单独拆解到另一个表中，使用商品 di 关联
  5. 每张表都要有一个主键，主键类型最好是 int，建议自增主键(不考虑分布式)

SQL 语句优化

  1. where，组合索引，索引下推(非选择行，不加锁)，索引覆盖不会表
  2. 对排序 分组 on 两边字段加索引
  3. 不要使用 *
  4. Limit 截断表，可用于停止全表扫描，适当使用可以提升速度
  5. 不使用 MySQL 内置的函数，因为内置函数不会建立查询缓存

大厂面试题

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

说说 MySQL 优化之道

UndoLog 和 RedoLog 的区别和联系

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

索引失效的场景有哪些

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

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

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

文章作者: Ammar
文章链接: http://lizhaoloveit.cn/2020/06/20/MySQL%E8%BF%9B%E9%98%B6/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Ammar's Blog！
数据库
打赏
  • 微信
  • 支付宝
评论
繁