MySQL
Last updated
Was this helpful?
Last updated
Was this helpful?
MySQL 是我们日常开发中用到的最多的关系型数据库，该篇总结 MySQL 的常用知识点。
目录
使用 EXPLAIN 可以帮助分析自己写的 SQL 语句，看看我们是否用到了索引。
以上执行结果各行表示的含义：
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 如下：
ref
此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询。
range
表示使用索引范围查询，通过索引字段范围获取表中部分数据记录，这个类型通常出现在 =、 <>、 >、 >=、 <、 <=、 IS NULL、 <=>、 BETWEEN、 IN 操作中。 当 type 是 range 时，那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个。 demo 如下：
index
表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。 index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。 demo 如下：
ALL
表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说，我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询，那么一般来说可以对相应的字段添加索引来避免。 demo 如下：
通常来说, 不同的 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 如下：
Using index
覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。 demo 如下：
Using temporary
查询有使用临时表，一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化。
Using where
列数据是从仅仅使用了索引中的信息而没有读取实际行的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示 MySQL 服务器将在存储引擎检索行后再进行过滤。
只返回必要的列：最好不要使用 SELECT * 语句。
只返回必要的行：使用 LIMIT 语句来限制返回的数据。
最有效的方式是使用索引来覆盖查询。
缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。
一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：
让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
减少锁竞争。（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 如下：
查看 MySQL 当前事务级别：select @@session.transaction_isolation;
未提交读（Read Uncommitted）
该隔离级别的事务会读到其它未提交事务的数据，此现象也称之为脏读。
准备数据：
准备两个终端，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 的默认隔离级别。
准备数据：
准备两个终端，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（行锁） 机制来避免幻读。使用行锁来避免幻读会在后续锁的介绍中进行解释。
准备数据：
准备两个终端，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 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。
准备数据：
准备两个终端，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 可以提高效率，有的确会降低效率。）
有利于数据的恢复
缺点
无法判断数据库做了什么，因为不知道执行的 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。(可通过设置磁盘控制器参数绕过)
