MySQL
MySQL 是我们日常开发中用到的最多的关系型数据库,该篇总 结 MySQL 的常用知识点。
目录
使用 EXPLAIN 可以帮助分析自己写的 SQL 语句,看看我们是否用到了索引。
CREATE TABLE `demo` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo name',
`author` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo author',
PRIMARY KEY (`ID`),
KEY `IX_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `demo_details` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`demoId` int(11) unsigned NOT NULL COMMENT 'demo id',
`url` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo author',
PRIMARY KEY (`ID`),
KEY `FIX_demoId_ID` (`demoId`),
CONSTRAINT `demo_details_ibfk_1` FOREIGN KEY (`demoId`) REFERENCES `demo` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
mysql> EXPLAIN SELECT * FROM demo WHERE ID = 1\G
执行结果:
id: 1
select_type: SIMPLE
table: demo
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
以上执行结果各行表示的含义:
- id: SELECT 查询的标识符,每个 SELECT 都会自动分配一个唯一的标识符
- select_type: SELECT 查询的类型
- table: 查询的是哪个表
- partitions: 匹配的分区
- type: join 类型
- possible_keys: 此次查询中可能选用的索引
- key: 此次查询中确切使用到的索引
- ref: 哪个字段或常数与 key 一起被使用
- rows: 显示此查询一共扫描了多少行,这个是一个估计值
- filtered: 表示此查询条件所过滤的数据的百分比
- extra: 额外的信息
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
常用取值有:- system表中只有一条数据,这个类型是特殊的 const 类型。
- const针对主键或唯一索引的等值查询扫描,最多只返回一行数据,const 查询速度非常快,因为它仅仅读取一次即可。
- eq_ref此类型通常出现在多表的 join 查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果,并且查询的比较操作通常是 =,查询效率较高。 demo 如下:mysql>EXPLAIN select * from demo,demo_details where demo.ID = demo_details.demoId\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: demo_detailspartitions: NULLtype: ALLpossible_keys: FIX_demoId_IDkey: NULLkey_len: NULLref: NULLrows: 1filtered: 100.00Extra: NULL*************************** 2. row ***************************id: 1select_type: SIMPLEtable: demopartitions: NULLtype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: springdemo.demo_details.demoIdrows: 1filtered: 100.00Extra: NULL2 rows in set, 1 warning (0.00 sec)
- ref此类型通常出现在多表的 join 查询,针对于非唯一或非主键索引,或者是使用了最左前缀规则索引的查询。mysql> EXPLAIN select * from demo where name='tommy'\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: demopartitions: NULLtype: refpossible_keys: IX_namekey: IX_namekey_len: 258ref: constrows: 1filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
- range表示使用索引范围查询,通过索引字段范围获取表中部分数据记录,这个类型通常出现在 =、 <>、 >、 >=、 <、 <=、 IS NULL、 <=>、 BETWEEN、 IN 操作中。 当 type 是 range 时,那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个。 demo 如下:mysql> EXPLAIN select * from demo where id between 1 and 2\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: demopartitions: NULLtype: rangepossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: NULLrows: 2filtered: 100.00Extra: Using where1 row in set, 1 warning (0.00 sec)
- index表示全索引扫描(full index scan),和 ALL 类型类似,只不过 ALL 类型是全表扫描,而 index 类型则仅仅扫描所有的索引,而不扫描数据。 index 类型通常出现在:所要查询的数据直接在索引树中就可以获取到,而不需要扫描数据。当是这种情况时,Extra 字段 会显示 Using index。 demo 如下:mysql> EXPLAIN select name from demo\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: demopartitions: NULLtype: indexpossible_keys: NULLkey: IX_namekey_len: 258ref: NULLrows: 2filtered: 100.00Extra: Using index1 row in set, 1 warning (0.00 sec)
- ALL表示全表扫描,这个类型的查询是性能最差的查询之一。通常来说,我们的查询不应该出现 ALL 类型的查询,因为这样的查询在数据量大的情况下,对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询,那么一般来说可以对相应的字段添加索引来避免。 demo 如下:mysql> EXPLAIN select * from demo\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: demopartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 2filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range < index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描,因此在相同的查询条件下,它是速度最慢的。 而 index 类型的查询虽然不是全表扫描,但是它扫描了所有的索引,因此比 ALL 类型的稍快。 后面的几种类型都是利用了索引来查询数据,因此可以过滤部分或大部分数据,因此查询效率就比较高了。
possible_key
表示 MySQL 在查询时,可能使用到的索引。即使有些索引出现在 possible_key 中,但是并不表示此索引一定会被 MySQL 使用到。MySQL 在查询时具体使用到那些索引,与 key 和你写的 SQL 有关。此字段表示 MySQL 在当前查询时所真正会使用到的索引。
表示查询优化器使用了索引的字节数。这个字段可以评估组合索引是否完全被使用,或只有最左部分字段被使用到。 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 也是一个重要的字段。MySQL 查询优化器根据统计信息,估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏,原则上 rows 越少越好。
EXPLAIN 中的很多额外的信息会在 Extra 字段显示,常见的有以下几种内容:
- Using filesort当 Extra 中有 Using filesort 时,表示 MySQL 需额外的排序操作,不能通过索引顺序达到排序效果。一般有 Using filesort,都建议优化去掉,因为这样的查询 CPU 资源消耗大。 demo 如下:mysql> EXPLAIN select * from demo order by name\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: demopartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 2filtered: 100.00Extra: Using filesort1 row in set, 1 warning (0.00 sec)
- Using index覆盖索引扫描,表示查询在索引树中就可查找所需数据,不用扫描表数据文件,往往说明性能不错。 demo 如下:mysql> EXPLAIN select * from demo order by id desc\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: demopartitions: NULLtype: indexpossible_keys: NULLkey: PRIMARYkey_len: 4ref: NULLrows: 2filtered: 100.00Extra: Backward index scan1 row in set, 1 warning (0.00 sec)
- Using temporary查询有使用临时表,一般出现于排序,分组和多表 join 的情况,查询效率不高,建议优化。
- Using where列数据是从仅仅使用了索引中的信息而没有读取实际行的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示 MySQL 服务器将在存储引擎检索行后再进行过滤。
- 只返回必要的列:最好不要使用 SELECT * 语句。
- 只返回必要的行:使用 LIMIT 语句来限制返回的数据。
- 最有效的方式是使用索引来覆盖查询。
- 缓存重复查询的数据:使用缓存可以避免在数据库中进行查询,特别在要查询的数据经常被重复查询时,缓存带来的查询性能提升将会是非常明显的。
一个大查询如果一次性执行的话,可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 12 MONTH);
rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 12 MONTH) LIMIT 5000")
} while rows_affected > 0
将一个大连接查询分解成对每一个表进行一次单表查询,然后在应用程序中进行关联,这样做的好处有:
- 让缓存更高效。对于连接查询,如果其中一个表发生变化,那么整个查询缓存就无法使用。而分解后的多个查询,即使其中一个表发生变化,对其它表的查询缓存依然可以使用。
- 分解成多个单表查询,这些单表查询的缓存结果更可能被其它查询使用到,从而减少冗余记录的查询。
- 减少锁竞争。(select * for update)
- 在应用层进行连接,可以更容易对数据库进行拆分,从而更容易做到高性能和可伸缩。
锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性;锁冲突也是影响数据库并发访问性能的一个重要因素。锁是 MySQL 在服务器层和存储引擎层的并发控制。
说到 MySQL 的锁,先来了解一下 MySQL 的事物隔离级别。
- 原子性(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 8.0 版本进行测试。
新建一张测试表 demo,SQL 如下:
CREATE TABLE `demo` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo name',
`author` varchar(64) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'demo author',
PRIMARY KEY (`ID`),
KEY `IX_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
查看 MySQL 当前事务级别
:select @@session.transaction_isolation;未提交读(Read Uncommitted)
该隔离级别的事务会读到其它未提交事务的数据,此现象也称之为脏读。
准备数据:
SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
insert into demo(name, author) values('name1', 'tommy');
- 准备两个终端,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 的默认隔离级别。
准备数据:
SET @@session.transaction_isolation = 'READ-COMMITTED';
insert into demo(name, author) values('name1', 'tommy');
- 准备两个终端,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(行锁) 机制来避免幻读。使用行锁来避免幻读会在后续锁的介绍中进行解释。
准备数据:
SET @@session.transaction_isolation = 'REPEATABLE-READ';
- 准备两个终端,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 引擎会给读操作隐式加一把读共享锁,从而避免了脏读、不可重读复读和幻读问题。
准备数据:
SET @@session.transaction_isolation = 'SERIALIZABLE';
- 准备两个终端,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 | 互斥 | 兼容 | 兼容 | 兼容 |
- 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 可以提高效率,有的确会降低效率。)insert into tab1 (col1, col2, sum_col3) select col1, col2, sum(col1, col2) from tab2 group by col1, col2; # 基于行的复制只需要把插入结果记录下来update tab3 set col1 = 0; // 基于行的复制就要在二进制中记录全表的数据
- 有利于数据的恢复
缺点
- 无法判断数据库做了什么,因为不知道执行的 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 可以获取这些事件并执行它。

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

基于主从复制架构,简单来说,就搞一个主库,挂多个从库,然后我们就单单只是写主库,然后主库会自动把数据给同步到从库上去。 读写分离可以提高系统的效率,特别是对于写少读多的系统,使用读写分离可以大大提高系统的效率。这也是从库会有多个的原因,读的时候可以做负载均衡(可以通过主健或者用户 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 3yr ago