MySQL
MySQL 是我们日常开发中用到的最多的关系型数据库,该篇总结 MySQL 的常用知识点。
目录

查询优化

优化之EXPLAIN

使用 EXPLAIN 可以帮助分析自己写的 SQL 语句,看看我们是否用到了索引。

按以下两个 SQL 新建两张表

1
CREATE TABLE `demo` (
2
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
3
`name` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo name',
4
`author` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo author',
5
PRIMARY KEY (`ID`),
6
KEY `IX_name` (`name`)
7
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
8
9
CREATE TABLE `demo_details` (
10
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
11
`demoId` int(11) unsigned NOT NULL COMMENT 'demo id',
12
`url` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo author',
13
PRIMARY KEY (`ID`),
14
KEY `FIX_demoId_ID` (`demoId`),
15
CONSTRAINT `demo_details_ibfk_1` FOREIGN KEY (`demoId`) REFERENCES `demo` (`ID`)
16
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Copied!

具体用法

1
mysql> EXPLAIN SELECT * FROM demo WHERE ID = 1\G
2
3
执行结果:
4
5
id: 1
6
select_type: SIMPLE
7
table: demo
8
partitions: NULL
9
type: const
10
possible_keys: PRIMARY
11
key: PRIMARY
12
key_len: 4
13
ref: const
14
rows: 1
15
filtered: 100.00
16
Extra: NULL
17
1 row in set, 1 warning (0.00 sec)
Copied!
以上执行结果各行表示的含义:
  • id: SELECT 查询的标识符,每个 SELECT 都会自动分配一个唯一的标识符
  • select_type: SELECT 查询的类型
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行,这个是一个估计值
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息

select_type

select_type 表示了查询的类型, 它的常用取值有:
  • SIMPLE: 表示此查询不包含 UNION 查询或子查询
  • PRIMARY: 表示此查询是最外层的查询
  • UNION: 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION: UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT: UNION 的结果
  • SUBQUERY: 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果
最常见的应该是 SIMPLE,当我们的查询 SQL 里面没有 UNION 查询或者子查询的时候,那么通常就是 SIMPLE 类型。

type

type 字段比较重要,它提供了判断查询是否高效的重要依据。通过 type 字段,我们可以判断此次查询是全表扫描,还是索引扫描等。
type 常用取值有:
  • system
    表中只有一条数据,这个类型是特殊的 const 类型。
  • const
    针对主键或唯一索引的等值查询扫描,最多只返回一行数据,const 查询速度非常快,因为它仅仅读取一次即可。
  • eq_ref
    此类型通常出现在多表的 join 查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果,并且查询的比较操作通常是 =,查询效率较高。 demo 如下:
    1
    mysql>EXPLAIN select * from demo,demo_details where demo.ID = demo_details.demoId\G;
    2
    *************************** 1. row ***************************
    3
    id: 1
    4
    select_type: SIMPLE
    5
    table: demo_details
    6
    partitions: NULL
    7
    type: ALL
    8
    possible_keys: FIX_demoId_ID
    9
    key: NULL
    10
    key_len: NULL
    11
    ref: NULL
    12
    rows: 1
    13
    filtered: 100.00
    14
    Extra: NULL
    15
    *************************** 2. row ***************************
    16
    id: 1
    17
    select_type: SIMPLE
    18
    table: demo
    19
    partitions: NULL
    20
    type: eq_ref
    21
    possible_keys: PRIMARY
    22
    key: PRIMARY
    23
    key_len: 4
    24
    ref: springdemo.demo_details.demoId
    25
    rows: 1
    26
    filtered: 100.00
    27
    Extra: NULL
    28
    2 rows in set, 1 warning (0.00 sec)
    Copied!
  • ref
    此类型通常出现在多表的 join 查询,针对于非唯一或非主键索引,或者是使用了最左前缀规则索引的查询。
    1
    mysql> EXPLAIN select * from demo where name='tommy'\G;
    2
    *************************** 1. row ***************************
    3
    id: 1
    4
    select_type: SIMPLE
    5
    table: demo
    6
    partitions: NULL
    7
    type: ref
    8
    possible_keys: IX_name
    9
    key: IX_name
    10
    key_len: 258
    11
    ref: const
    12
    rows: 1
    13
    filtered: 100.00
    14
    Extra: NULL
    15
    1 row in set, 1 warning (0.00 sec)
    Copied!
  • range
    表示使用索引范围查询,通过索引字段范围获取表中部分数据记录,这个类型通常出现在 =、 <>、 >、 >=、 <、 <=、 IS NULL、 <=>、 BETWEEN、 IN 操作中。 当 type 是 range 时,那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个。 demo 如下:
    1
    mysql> EXPLAIN select * from demo where id between 1 and 2\G;
    2
    *************************** 1. row ***************************
    3
    id: 1
    4
    select_type: SIMPLE
    5
    table: demo
    6
    partitions: NULL
    7
    type: range
    8
    possible_keys: PRIMARY
    9
    key: PRIMARY
    10
    key_len: 4
    11
    ref: NULL
    12
    rows: 2
    13
    filtered: 100.00
    14
    Extra: Using where
    15
    1 row in set, 1 warning (0.00 sec)
    Copied!
  • index
    表示全索引扫描(full index scan),和 ALL 类型类似,只不过 ALL 类型是全表扫描,而 index 类型则仅仅扫描所有的索引,而不扫描数据。 index 类型通常出现在:所要查询的数据直接在索引树中就可以获取到,而不需要扫描数据。当是这种情况时,Extra 字段 会显示 Using index。 demo 如下:
    1
    mysql> EXPLAIN select name from demo\G;
    2
    *************************** 1. row ***************************
    3
    id: 1
    4
    select_type: SIMPLE
    5
    table: demo
    6
    partitions: NULL
    7
    type: index
    8
    possible_keys: NULL
    9
    key: IX_name
    10
    key_len: 258
    11
    ref: NULL
    12
    rows: 2
    13
    filtered: 100.00
    14
    Extra: Using index
    15
    1 row in set, 1 warning (0.00 sec)
    Copied!
  • ALL
    表示全表扫描,这个类型的查询是性能最差的查询之一。通常来说,我们的查询不应该出现 ALL 类型的查询,因为这样的查询在数据量大的情况下,对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询,那么一般来说可以对相应的字段添加索引来避免。 demo 如下:
    1
    mysql> EXPLAIN select * from demo\G;
    2
    *************************** 1. row ***************************
    3
    id: 1
    4
    select_type: SIMPLE
    5
    table: demo
    6
    partitions: NULL
    7
    type: ALL
    8
    possible_keys: NULL
    9
    key: NULL
    10
    key_len: NULL
    11
    ref: NULL
    12
    rows: 2
    13
    filtered: 100.00
    14
    Extra: NULL
    15
    1 row in set, 1 warning (0.00 sec)
    Copied!

type 类型的性能比较

通常来说, 不同的 type 类型的性能关系如下: ALL < index < range < index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描,因此在相同的查询条件下,它是速度最慢的。 而 index 类型的查询虽然不是全表扫描,但是它扫描了所有的索引,因此比 ALL 类型的稍快。 后面的几种类型都是利用了索引来查询数据,因此可以过滤部分或大部分数据,因此查询效率就比较高了。

possible_keys

possible_key 表示 MySQL 在查询时,可能使用到的索引。即使有些索引出现在 possible_key 中,但是并不表示此索引一定会被 MySQL 使用到。MySQL 在查询时具体使用到那些索引,与 key 和你写的 SQL 有关。

key

此字段表示 MySQL 在当前查询时所真正会使用到的索引。

key_len

表示查询优化器使用了索引的字节数。这个字段可以评估组合索引是否完全被使用,或只有最左部分字段被使用到。 key_len 的计算规则如下:
  • 字符串
    • char(n):n 字节长度
    • varchar(n):如果是 utf8 编码,则是 3n + 2字节;如果是 utf8mb4 编码,则是 4n + 2 字节。
  • 数值类型
    • TINYINT: 1字节
    • SMALLINT: 2字节
    • MEDIUMINT: 3字节
    • INT: 4字节
    • BIGINT: 8字节
  • 时间类型
    • DATE:3字节
    • TIMESTAMP:4字节
    • DATETIME:8字节
  • 字段属性 NULL 属性 占用一个字节。如果一个字段是 NOT NULL 的,则没有此属性。

rows

rows 也是一个重要的字段。MySQL 查询优化器根据统计信息,估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏,原则上 rows 越少越好。

Extra

EXPLAIN 中的很多额外的信息会在 Extra 字段显示,常见的有以下几种内容:
  • Using filesort
    当 Extra 中有 Using filesort 时,表示 MySQL 需额外的排序操作,不能通过索引顺序达到排序效果。一般有 Using filesort,都建议优化去掉,因为这样的查询 CPU 资源消耗大。 demo 如下:
    1
    mysql> EXPLAIN select * from demo order by name\G;
    2
    *************************** 1. row ***************************
    3
    id: 1
    4
    select_type: SIMPLE
    5
    table: demo
    6
    partitions: NULL
    7
    type: ALL
    8
    possible_keys: NULL
    9
    key: NULL
    10
    key_len: NULL
    11
    ref: NULL
    12
    rows: 2
    13
    filtered: 100.00
    14
    Extra: Using filesort
    15
    1 row in set, 1 warning (0.00 sec)
    Copied!
  • Using index
    覆盖索引扫描,表示查询在索引树中就可查找所需数据,不用扫描表数据文件,往往说明性能不错。 demo 如下:
    1
    mysql> EXPLAIN select * from demo order by id desc\G;
    2
    *************************** 1. row ***************************
    3
    id: 1
    4
    select_type: SIMPLE
    5
    table: demo
    6
    partitions: NULL
    7
    type: index
    8
    possible_keys: NULL
    9
    key: PRIMARY
    10
    key_len: 4
    11
    ref: NULL
    12
    rows: 2
    13
    filtered: 100.00
    14
    Extra: Backward index scan
    15
    1 row in set, 1 warning (0.00 sec)
    Copied!
  • Using temporary
    查询有使用临时表,一般出现于排序,分组和多表 join 的情况,查询效率不高,建议优化。
  • Using where
    列数据是从仅仅使用了索引中的信息而没有读取实际行的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示 MySQL 服务器将在存储引擎检索行后再进行过滤。

优化数据访问

减少返回数据的数量

  • 只返回必要的列:最好不要使用 SELECT * 语句。
  • 只返回必要的行:使用 LIMIT 语句来限制返回的数据。

减少服务器端扫描的行数

  • 最有效的方式是使用索引来覆盖查询。
  • 缓存重复查询的数据:使用缓存可以避免在数据库中进行查询,特别在要查询的数据经常被重复查询时,缓存带来的查询性能提升将会是非常明显的。

重构查询方式

切分大查询

一个大查询如果一次性执行的话,可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
1
DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 12 MONTH);
2
rows_affected = 0
3
do {
4
rows_affected = do_query(
5
"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 12 MONTH) LIMIT 5000")
6
} while rows_affected > 0
Copied!

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询,然后在应用程序中进行关联,这样做的好处有:
  • 让缓存更高效。对于连接查询,如果其中一个表发生变化,那么整个查询缓存就无法使用。而分解后的多个查询,即使其中一个表发生变化,对其它表的查询缓存依然可以使用。
  • 分解成多个单表查询,这些单表查询的缓存结果更可能被其它查询使用到,从而减少冗余记录的查询。
  • 减少锁竞争。(select * for update)
  • 在应用层进行连接,可以更容易对数据库进行拆分,从而更容易做到高性能和可伸缩。

MySQL事物隔离级别与锁总结

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性;锁冲突也是影响数据库并发访问性能的一个重要因素。锁是 MySQL 在服务器层和存储引擎层的并发控制。

MySQL 事物隔离级别

说到 MySQL 的锁,先来了解一下 MySQL 的事物隔离级别。

事务的四个重要特性 --- ACID 特性

  • 原子性(Atomicity)
    事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。
  • 一致性(Consistency)
    指事务将数据库从一种状态转变为另一种一致的的状态。事务开始前和结束后,数据库的完整性约束没有被破坏。
  • 隔离性(Isolation)
    要求每个读写事务的对象对其他事务的操作对象能互相分离,即该事务提交前对其他事务不可见。也可以理解为多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行结果。这指的是在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自完整的数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。 Tips:MySQL 通过锁机制来保证事务的隔离性
  • 持久性(Durability) 事务一旦提交,则其结果就是永久性的。即使发生宕机的故障,数据库也能将数据恢复,也就是说事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。这只是从事务本身的角度来保证,排除 RDBMS(关系型数据库管理系统,例如 Oracle、MySQL 等)本身发生的故障。Tips:MySQL 使用 redo log 来保证事务的持久性

事务的四种隔离级别

在数据库操作中,为了有效保证并发读取数据的正确性,提出的事务隔离级别。我们的数据库锁,也是为了构建这些隔离级别存在的。
隔离级别
脏读(Dirty Read)
不可重复读(NonRepeatable Read)
幻读(Phantom Read)
未提交读(Read Uncommitted)
可能
可能
可能
已提交读(Read Committed)
不可能
可能
可能
可重复读(Repeated Read)
不可能
不可能
可能
可串行化(Serializable)
不可能
不可能
不可能
  • 未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据。
  • 已提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。
  • 可重复读(Repeated Read):在同一个事务内的查询都是事务开始时刻一致的,InnoDB 默认级别。在 SQL 标准中,该隔离级别消除了不可重复读,但是还存在幻读。
  • 可串行化(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞。
通过上述描述可以看出,Read Uncommitted 这种级别,数据库一般都不会用,而且任何操作都不会加锁。

MySQL 事务级别详解

以下信息都是针对 MySQL 8.0 版本进行测试。
新建一张测试表 demo,SQL 如下:
1
CREATE TABLE `demo` (
2
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
3
`name` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo name',
4
`author` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo author',
5
PRIMARY KEY (`ID`),
6
KEY `IX_name` (`name`)
7
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Copied!
查看 MySQL 当前事务级别:select @@session.transaction_isolation;
未提交读(Read Uncommitted)
该隔离级别的事务会读到其它未提交事务的数据,此现象也称之为脏读
准备数据:
1
SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
2
3
insert into demo(name, author) values('name1', 'tommy');
Copied!
  • 准备两个终端,MySQL 终端 1 和 MySQL 终端 2,再准备一张测试表 demo,写入一条测试数据并调整隔离级别为 Read Uncommitted,任意一个终端执行即可。
  • 登录 MySQL 终端 1,开启一个事务,将 ID 为 1 对应的 name1 的记录更新为 name2。
    begin; update demo set name = 'name2' where id = 1; select * from demo; -- 此时看到一条 name 为 name2 的记录
  • 登录 MySQL 终端 2,开启一个事务后查看表中的数据。
    use demo; begin; select * from demo; -- 此时看到一条 name 为 name2 的记录
最后一步读取到了 MySQL 终端 1 中未提交的事务(没有 commit 提交动作),即产生了脏读,大部分业务场景都不允许脏读出现,但是此隔离级别下数据库的并发是最好的。由于会出现脏读,所以这种隔离级别一般数据库都不会使用。
已提交读(Read Committed)
一个事务可以读取另一个已提交的事务,多次读取会造成不一样的结果,此现象称为不可重复读问题,Oracle 和 SQL Server 的默认隔离级别,但是不是 MySQL 的默认隔离级别。
准备数据:
1
SET @@session.transaction_isolation = 'READ-COMMITTED';
2
3
insert into demo(name, author) values('name1', 'tommy');
Copied!
  • 准备两个终端,MySQL 终端 1 和 MySQL 终端 2,再准备一张测试表 demo,写入一条测试数据并调整隔离级别为 Read Committed,任意一个终端执行即可。
  • 登录 MySQL 终端 1,开启一个事务,将 ID 为 1 对应的 name1 的记录更新为 name2。
    begin; update demo set name = 'name2' where id = 1; select * from demo; -- 此时看到一条 name 为 name2 的记录
  • 登录 MySQL 终端 2,开启一个事务后查看表中的数据。
    use demo; begin; select * from demo; -- 此时看到一条 name 为 name1 的记录
  • 切换 MySQL 终端 1,提交事务。
    commit;
  • 切换 MySQL 终端 2。
    select * from test; -- 此时看到一条 name 为 name2 的记录
MySQL 终端 2 在开启了一个事务之后,在第一次读取 demo 表(此时 MySQL 终端 1 的事务还未提交)时 name 的值为 'name1',在第二次读取 demo 表(此时 MySQL 终端 1 的事务已经提交)时 name 列的值 'name1' 已经变为 'name2',说明在此隔离级别下只能读取到已提交的事务。
可重复读(Repeated Read)
该隔离级别是 MySQL 默认的隔离级别,在同一个事务里,select 的结果是事务开始时时间点的状态,因此,同样的 select 操作读到的结果会是一致的,但是,会有幻读现象。MySQL 的 InnoDB 引擎可以通过 next-key locks(行锁) 机制来避免幻读。使用行锁来避免幻读会在后续锁的介绍中进行解释。
准备数据:
1
SET @@session.transaction_isolation = 'REPEATABLE-READ';
Copied!
  • 准备两个终端,MySQL 终端 1 和 MySQL 终端 2,再准备一张测试表 demo,写入一条测试数据并调整隔离级别为 Repeated Read,任意一个终端执行即可。
  • 登录 MySQL 终端 1,开启一个事务。
    begin; select * from demo; -- 无记录
  • 登录 MySQL 终端 2,开启一个事务后查看表中的数据。
    begin; select * from demo; -- 无记录
  • 切换 MySQL 终端 1,提交事务。
    insert into demo(id, name, author) values(1, 'name1', 'tommy'); commit;
  • 切换 MySQL 终端 2。
    select * from demo; --此时查询还是无记录
    以上可以证明,在该隔离级别下已经读取不到别的已提交的事务,如果想看到 MySQL 终端 1 提交的事务,在 MySQL 终端 2 将当前事务提交后再次查询就可以读取到 MySQL 终端 1 提交的事务。我们接着实验,看看在该隔离级别下是否会存在别的问题。
  • 此时接着在 MySQL 终端 2 插入一条数据。
    insert into demo(id, name, author) values(1, 'name1', 'tommy'); Duplicate entry '1' for key 'PRIMARY',主键冲突。
    这时你肯定会有疑问,明明在上一步没有数据,为什么在这里会报错呢?其实这就是该隔离级别下可能产生的问题,MySQL 称之为幻读。注意我在这里强调的是 MySQL 数据库,Oracle 数据库对于幻读的定义可能有所不同。
可串行化(Serializable)
在该隔离级别下事务都是串行顺序执行的,MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁,从而避免了脏读、不可重读复读和幻读问题。
准备数据:
1
SET @@session.transaction_isolation = 'SERIALIZABLE';
Copied!
  • 准备两个终端,MySQL 终端 1 和 MySQL 终端 2,再准备一张测试表 demo,写入一条测试数据并调整隔离级别为 Serializable,任意一个终端执行即可。
  • 登录 MySQL 终端 1,开启一个事务,并写入一条数据。
    begin; insert into demo(id, name, author) values(1, 'name1', 'tommy');
  • 登录 MySQL 终端 2,开启一个事务。
    begin; select * from test; -- 此时会一直卡住
  • 立马切换到 MySQL 终端 1,提交事务。
    commit;
一旦事务提交,MySQL 终端 2 会立马返回 ID 为 1 的记录,否则会一直卡住,直到超时,其中超时参数是由 innodb_lock_wait_timeout 控制。该隔离级别的数据库并发能力最弱,因为每条 select 语句都会加锁。

锁机制

InnoDB 实现了两种类型的行级锁: 共享锁(也称为 S 锁):允许事务读取一行数据。 可以使用 SQL 语句 select * from tableName where … lock in share mode; 手动加 S 锁。
独占锁(也称为 X 锁):允许事务删除或更新一行数据。 可以使用 SQL 语句 select * from tableName where … for update; 手动加 X 锁。
S 锁和 S 锁是兼容的,X 锁和其它锁都不兼容,举个例子,事务 T1 获取了一个行 r1 的 S 锁,另外事务 T2 可以立即获得行 r1 的 S 锁,此时 T1 和 T2 共同获得行 r1 的 S 锁,此种情况称为锁兼容,但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁,则必须等待 T1 对行 r 锁的释放,此种情况也成为锁冲突。
为了实现多粒度的锁机制,InnoDB 还有两种内部使用的意向锁,由 InnoDB 自动添加,且都是表级别的锁。
意向共享锁(IS):事务即将给表中的各个行设置共享锁,事务给数据行加 S 锁前必须获得该表的 IS 锁。 意向排他锁(IX):事务即将给表中的各个行设置排他锁,事务给数据行加 X 锁前必须获得该表 IX 锁。
意向锁解决的问题:如果下一个事务试图在该表级别上应用共享和排它锁,则会受到由第一个任务控制的表级别意向锁的阻塞。下一个事务在其锁定该表前不必检查各个页或行锁,而只需检查该表上的意向锁。

表级意向锁和行级锁的兼容互斥性

锁类型
X
IX
S
IS
X
互斥
互斥
互斥
互斥
IX
互斥
兼容
互斥
兼容
S
互斥
互斥
兼容
兼容
IS
互斥
兼容
兼容
兼容

锁粒度

不同粒度锁的比较

MylSAM 表锁

MylSAM 表级锁模式

MylSAM 加表锁方法

死锁案例分析

  • show variables like 'innodb_deadlock_detect';
  • show status like 'table_locks%';
  • show status like 'innodb_row_lock%';
  • show engine innodb status;

主从复制

  • 数据分布
  • 负载平衡(Load Balancing)
  • 备份
  • 高可用性(High Availability)和容错

相关命令

  • show status 查看整个 mysql 状态,这个命令也可以看到 Seconds_Behind_Master。
  • show master status 查看主库信息
  • show slave status 查看从库信息
    查看参数 Seconds_Behind_Master 来看主从是否延迟。
    • 0:该值为零,是我们极为渴望看到的情况,表示主从复制良好,可以认为lag不存在。
    • 正值:表示主从已经出现延时,数字越大表示从库落后主库越多。
    • 负值:几乎很少见,我只是听一些资深的DBA说见过,其实,这是一个BUG值,该参数是不支持负值的,也就是不应该出现。
  • show processlist 查看数据库线程列表信息

原理

基于语句的复制

在 MySQL5.0 及之前的版本只支持基于语句的复制。基于语句复制的模式下,主库会记录那些造成数据更改的事件,当备库读取并重放这些事件时,备库只是把主库上执行过的 SQL 再执行一遍。
优点
  • 实现简单。
  • 二进制日志里的事件更加的紧凑。(全部是需要执行的 SQL 语句)
缺点
  • 执行语句的时间不同。(机器的 CPU 和内存可能很不一样)
  • 还有一些动态数据,比如 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,还有一些使用函数的语句,比如包含 CURRENT_USER 的语句。
  • 更新是串行的。(需要考虑锁带来的性能消耗)

基于行的复制

在 MySQL5.1 开始支持基于行的复制,这种方式会将实际的数据记录到二进制日志中。
优点
  • 可以正确的复制每一行。(不存在基于语句的复制出现的那种问题)
  • 可以更高效地复制数据。(备库不用重放 MySQL 的事件,这个也是针对具体的 SQL,有的 SQL 可以提高效率,有的确会降低效率。)
    1
    insert into tab1 (col1, col2, sum_col3) select col1, col2, sum(col1, col2) from tab2 group by col1, col2; # 基于行的复制只需要把插入结果记录下来
    2
    3
    update tab3 set col1 = 0; // 基于行的复制就要在二进制中记录全表的数据
    Copied!
  • 有利于数据的恢复
缺点
  • 无法判断数据库做了什么,因为不知道执行的 SQL。
  • 针对上述全表数据更新的时候,效率会很低。

复制文件

  • mysql-bin.index
    该文件是 MySQL 用来识别具体的二进制 binlog 文件;该文件记录磁盘上 binlog 文件。
  • mysql-relay-bin.index
    中继日志的索引文件。跟 mysql-bin.index 作用类似。
  • master.info
    保存备库连接到主库所需要的信息,格式为纯文本。这个文件以文本的方式记录了复制用户的密码。故要注意该文件的权限。
  • relay-log.info
    包含当前备库复制的二进制日志和中继日志的坐标(及备库复制到主库的具体位置)。

发送复制事件到其它的备库

当设置 log_slave_updates 时,你可以让 slave 扮演其它 slave 的 master。此时,slave 把 SQL 线程执行的事件写进行自己的二进制日志(binary log),然后,它的 slave 可以获取这些事件并执行它。
Could not load image

复制过滤器

复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤:在 master 上过滤二进制日志中的事件;在 slave 上过滤中继日志中的事件。
Could not load image

读写分离

基于主从复制架构,简单来说,就搞一个主库,挂多个从库,然后我们就单单只是写主库,然后主库会自动把数据给同步到从库上去。 读写分离可以提高系统的效率,特别是对于写少读多的系统,使用读写分离可以大大提高系统的效率。这也是从库会有多个的原因,读的时候可以做负载均衡(可以通过主健或者用户 id 等 hash 的方式,也可以使用 Round Robin 轮询算法;负载均衡算法有很多种,这里就不一一列举),让读请求分布到不同的从库上,提高读请求的效率。

持久化数据分析

数据InnoDB到磁盘需要经过
  • InnoDB buffer pool, Redo log buffer。这个是InnoDB应用系统本身的缓冲。
  • page cache /Buffer cache(可通过o_direct绕过)。这个是vfs层的缓冲。
  • Inode cache/directory buffer。这个也是vfs层的缓冲。需要通过O_SYNC或者fsync()来刷新。
  • Write-Back buffer。(可设置存储控制器参数绕过)
  • Disk on-broad buffer。(可通过设置磁盘控制器参数绕过)
Last modified 2yr ago