【数据库】MySQL与TiDB学习笔记

最流行的关系型数据库管理系统之一。在 WEB 应用方面，是最好的 RDBMS。

文件存储

MySQL的文件主要分为两大类，MySQl服务的文件和存储引擎的文件，主要分为三类：

日志文件：MySQL实例写入的各种日志文件。
表结构文件：存放表结构定义的文件，.frm后缀文件，与存储引擎无关。
存储引擎数据文件：存储引擎负责对表中的数据进行读取和写入，每个存储引擎以自己的方式来保存表中的数据，在不同的存储引擎中数据存放的方式一般不同。

日志文件

记录数据库操作信息和错误信息，主要包括错误日志，二进制日志， 查询日志， 慢查询日志， 中继日志等。

InnoBD相关的日志有：事务重做日志（redo log）和回滚日志（undo log）

查看命令：

1	`show variables like 'log_%';`

错误日志（errorlog）

用来记录 MySQL 服务器运行过程中遇到的所有严重错误信息以及MySQL每次启动和关闭的详细信息。比如，无法加载 MySQL 数据库的数据文件，或权限不正确等都会被记录在此，还有复制环境下，从服务器进程的信息也会被记录进错误日志。默认情况下，错误日志是开启的，从5.5.7开始无法关闭错误日志。

错误日志是存储在数据库的数据文件目录中，名称为 hostname.err，其中 hostname 为服务器主机名。在 MySQL 5.5.7 之前，数据库管理员可以删除很长时间之前的错误日志，以节省服务器上的硬盘空间， MySQL 5.5.7 之后，服务器将关闭此项功能，只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的，命令为：

mv hostname.err hostname.err.old mysqladmin flush-logs

默认的错误日志名称为 hostname.err，其中 hostname 为服务器主机名。

错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-error是定义是否启用错误日志功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。

# 可以直接定义为文件路径也可以为ON|OFF
log_error=/var/log/mysqld.log
# 定义警告信息，默认为开启状态，默认2， 关闭设置为0
log_warnings=2

log_warnings=0,不记录警告信息，只记录错误信息

log_warnings=1,记录警告信息，错误日志包含错误信息和警告信息

log_warnings>1,记录警告信息，访问时报错和访问拒接的连接错误信息也会被记录到错误日志中

MySQL 5.7.2之前默认为1， 5.7.2起默认值为2。

查询日志

查询日志在 MySQL 中被称为 general log(通用日志)，查询日志里面记录了数据库执行的所有命令。不管语句是否正确，都会被记录，具体原因如下:

insert 查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复肯定会报错；
update 时也会查询因为更新的时候很可能会更新某一块数据；
delete 查询，只删除符合条件的数据；

因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能。因此如果不是在调试环境下，是不建议开启查询日志功能的。

查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的 select 语句对应的数据是否能够被缓存，同时也可以帮助我们分析问题。所以，可以根据实际情况来决定是否开启查询日志。

查询日志模式是关闭的，可以通过以下命令开启查询日志：

set global generallog=1 set global logoutput='table';

general_log=1 为开启查询日志，0 为关闭查询日志，这个设置命令即时生效，不用重启 MySQL 服务器。

慢查询日志

慢查询会导致 CPU、IOPS、内存消耗过高，当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。使用如下命令记录当前数据库的慢查询语句：

# 开启慢查询日志
slowquerylog=ON
# 慢查询阈值，单位秒
long_query_time=10
# 慢查询日志文件名
# 没有指定，默认名为 hostname-slow.log hostname为主机名
# 需要指定，不是绝对路径
slow_query_files=file_name

使用命令 set global slowquerylog='ON' 开启慢查询日志，只是对当前数据库有效，如果 MySQL 数据库重启后就会失效。所以如果要永久生效，就要修改配置文件my.cnf，设置slowquerylog=ON 并重启 MySQL 服务器。

二进制日志（bin log）

通过以下命令来查询 binlog 是否开启：

show variables like 'log_%';

默认是关闭的。

1	`log-bin=OFF`

需要通过配置进行开启

1	`log-bin=mysql-bin`

其中mysql-bin是bin log日志文件的文件名前缀，bin log日志文件的完整名称：mysql-bin-000001.log

bin log是一个二进制文件，主要记录数据库所有的DDL语句和DML语句，但不包含SELECT、SHOW语句内容。DDL语句直接记录到bin log日志中，DML语句必须通过事务提交才能记录到bin log日志中。bin log 中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息。

bin log主要用于实现mysql主从复制、数据备份、数据恢复。

恢复（recovery）：某些数据的恢复需要二进制日志。比如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复；
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为 slave 或者 standby）与一台 MySQL 数据库（一般称为 master 或者 primary）进行实时同步；
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

除了上面介绍的几个作用外，binlog 对于事务存储引擎的崩溃恢复也有非常重要的作用，在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL 将采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内部的状态可能为 prepared（准备状态）和 commit（提交状态）两种，对于 prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考binlog，如果事务在 binlog 中存在，那么将其提交；如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

binlog 格式分为: STATEMENT、ROW 和 MIXED 三种：

STATEMENT 格式的 binlog 记录的是数据库上执行的原生 SQL 语句。这种格式的优点是简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。并且通过 mysqlbinlog 工具容易读懂其中的内容。缺点就是同一条 SQL 在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的 SQL。比如，使用 INSERT INTO TB1 VALUE(CUURENT_DATE())这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化，存储过程和触发器在使用基于语句的复制模式时也可能存在问题；另外一个问题就是基于语句的复制必须是串行化的，比如：InnoDB 的 next-key 锁等，并不是所有的存储引擎都支持基于语句的复制；
ROW 格式是从 MySQL 5.1 开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据，一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的 SQL 构造、触发器、存储过程等都能正确执行；它的缺点就是二进制日志可能会很大，而且不直观，所以，你不能使用 mysqlbinlog 来查看二进制日志，也无法通过看二进制日志判断当前执行到那一条 SQL 语句。现在对于 ROW 格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点，并且由于 ROW 格式记录行数据，所以可以基于这种模式做一些 DBA 工具，比如数据恢复，不同数据库之间数据同步等；
MIXED 也是 MySQL 默认使用的二进制日志记录方式，但 MIXED 格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到 UUID()、USER()、CURRENTUSER()、ROWCOUNT() 等无法确定的函数。

回滚日志(`undo log`)

用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log日志来实现回滚操作。undo log和redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。undo log默认存放在共享表空间中，在 mySQL 5.6 中，undo log 的存放位置还可以通过变量innodbundodirectory来自定义存放目录，默认值为“.”表示 datadir目录。

作用：

1	`保存事务发生前数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），即非锁定读`

内容：

1	`逻辑格式的体制。在执行undo的时候，仅仅是将数据从逻辑上恢复之事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log`

什么时候产生

1	`事务开始前，将当前的版本生成undo log. undo也会产生redo来保证undo log的可靠性`

什么时候释放

当事务提交之后，undo log不能立马被删掉，而是放入待清理的链表，由purge线程判断是否有其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间

对应物理文件：

1
2

mysql5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间默认的名称为ibdata,位于数据文件目录中。
mysql5.6之后，可以配置独立文件，完成数据库初始化后生效且不能改变undo log文件的个数，如果初始化数据库之前没有进行相关的配置，就无法配置独立的表空间。

重做日志(`redo log`)

为了最大程度的避免数据写入时，因为 IO 瓶颈造成的性能问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。

作用：

1 2	`确保事务的持久性。防止发生故障的时间，尚有脏页为写入磁盘。在重启mysql服务是，根据redo log进行重做，从而达到事务的持久性。`

内容：

1	`物理格式的日志，激励物理数据页面的修改信息，其redo log是顺序写入redo log file的物理文件中去的。`

什么时候产生

1	`事务开始之后就产生redo log. redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中便开始写入redo log文件中的。`

什么时候释放

1	`当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以冲用（被覆盖）。`

SQL语句的交互流程

说一下SQL语句的交互流程，包括客户端与服务端之间的交互以及服务内部的整体流程

-- id是主键
BEGIN
UPDATE user SET name='aa' WHERE id = 1 FOR UPDATE;
COMMIT;

连接器

当客户端登陆MySQL的时候，对身份认证和权限判断。

查询缓存

执行查询语句的时候，会先查询缓存(MySQL 8.0 版本后移除)。

分析器

假设在没有命中查询缓存的情况下，SQL请求就会来到分析器。分析器负责明确SQL要完成的功能，以及检查SQL的语法是否正确。

查询优化器

优化器会计算「IO 成本 + CPU」成本最小的那个索引来执行。优化器执行选出最优索引生成执行计划后，调用存储引擎接口被优化过的SQL语句。

存储引擎

执行 SQL 的动作是在存储引擎中完成的，数据是被存放在内存或者是磁盘中的。

执行器

执行器从准备更新一条数据到事务的提交的流程

首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中。
在数据被缓存到缓存池的同时，会写入 undo log 日志文件。
更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中。
完成以后就可以提交事务，在提交的同时会做以下三件事。
（第一件事）将redo log buffer中的数据刷入到 redo log 文件中。
（第二件事）将本次操作记录写入到 bin log文件中。
（第三件事）将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记。

索引原理及优化

索引介绍

什么是索引

索引是一种能帮助 MySQL 提高查询效率的数据结构。

索引优点和缺点

索引的优点如下：

快速访问数据表中的特定信息，提高检索速度。
创建唯一性索引，保证数据表中每一行数据的唯一性。
加速表与表之间的连接。
使用分组和排序进行数据检索时，可以显著减少查询中分组和排序的时间。

索引的缺点：

虽然提高了的查询速度，但却降低了更新表的速度，比如 update、insert，因为更新数据时，MySQL 不仅要更新数据，还要更新索引文件；
建立索引会占用磁盘文件的索引文件。

使用索引注意事项：

使用短索引，短索引不仅可以提高查询速度，更能节省磁盘空间和 I/O 操作；
索引列排序，MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的，因此数据库默认排序可以符合要求的情况下，不要进行排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引；
like 语句操作，一般情况下不鼓励使用 like 操作，如果非使用不可，注意 like "%aaa%" 不会使用索引，而like "aaa%"可以使用索引；
不要在列上进行运算；
不适用 NOT IN 和 <> 操作。

索引分类

MySQL 的索引有两种分类方式：逻辑分类和物理分类。按照逻辑分类，索引可分为：

单列索引

主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引；
普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

组合索引

在表中多个字段组合上创建的索引
组合索引的使用需要遵循最左前缀原则
一般情况建议组合索引代替单列索引

全文索引

让搜索关键词更高效的一种索引，只有在MyISAM引擎、InnoDB（5.6以后）才能使用，而且只能在CHAR, VARCHAR, TEXT类型才能使用。
优先级最高先执行不会执行其他索引
存储引擎决定只执行一个索引

空间索引

按照物理分类，索引可分为：

聚集索引
非聚集索引

索引使用

创建索引

各种索引的创建脚本如下：

-- 创建主键索引 
alter table t add primary key add (`id`); 
-- 创建唯一索引 
alter table t add unique (`username`); 
-- 创建普通索引 
alter table t add index index_name (`username`); 

-- 创建组合索引 
alter table t add index index_name (`username`, `age`); 

-- 创建全文索引 
alter table t add fulltext (`username`);

删除索引

1	`DROP INDEX index_name ON table;`

查看索引

1	`SHOW INDEX FROM tablename`

索引存储结构

InnoDB存储引擎逻辑结构

InnoDB存储引擎逻辑存储结构可分为五级：表空间、段、区、页、行。

索引存储结构

索引在存储引擎中实现，不同的存储引擎使用不同的索引

MyISAM和InnoDB存储引擎只支持B+TREE索引，即默认使用B+TREE,不能更换

MEMORY和HEAP存储引擎：执行HASH和BTREE

B树与B+树

B树与B+树的区别

非叶子结点是否存储数据

1 2	`B树叶子结点和非叶子结点都会存储数据 B+树只有叶子结点才会存储数据，而且存储的数据都在一行上，这些数据是由指针指向的，顺序的`

非聚集索引（MyISAM）

B+树叶子结点只存储数据行的指针。简单的说，数据跟索引不在一起。
非聚集索引包含的主键索引和辅助索引都会存储指针的值

主键索引

辅助索引（次要索引）

在MyISAM中主键索引与辅助索引在结构上没有区别，只是主键索引要求key是唯一的，辅助索引的key是可以重复的。

聚集索引（InnoDB）

主键索引的叶子结点都会存储数据，即数据和索引在一起
辅助索引只存储主键值
如果没有主键索引，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。

主键索引

InnoDB要求必须有主键（MyISAM可以没有）。如果没有，MySQL会自动选择一个可以唯一标识数据记录的列作为主键；如果没有，MySQL会自动为InnoDB生成一个隐含字段作为主键，类型为长整型。

辅助索引

聚集索引这种实现方式按照主键搜索非常高效，但是辅助索引需要查询两遍：辅助索引获得主键，然后主键到索引中检索获取记录。这也就是回表查询。

1
2

select * from user where name='Alice';  回表查询，检索两次 辅助索引——>主键索引——>数据
select id, name from user where name='Alice'; 不需要回表，辅助索引树上便可查到  覆盖索引（多用组合索引）

组合索引

哪些情况要创建索引

主键自动创建索引
频繁作为查询条件需要创建索引
多表关联查询中，关联字段应该创建索引 on 两边都需要创建索引
查询中排序字段，需要创建索引
频繁查找字短覆盖索引
查询中统计或者分组字段需要创建索引

哪些情况不需要创建索引

表记录太少
经常进行增删改查的表
频繁更新的字段
where条件里使用频率不高的字段

为什么使用组合索引

组合索引根据最左原则进行排序，为了节省索引空间和提升搜索性能，可以使用组合索引。

查询遵循最左前缀原则

前缀索引 where like a% 可以使用索引

where like %a 不能使用索引
从左向右匹配直到遇到范围查询 > < between like

建立索引（a, b, c, d）

where a=1 and b=3 and c>4 and d=2 到了c>4时听了，d=2用不到索引

可以通过键索引构建为（a, b, d, c）进行优化

索引失效与分析

执行计划

EXPALIN命令, 可以对select语句的执行计划进行分析

参数说明

1	`id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra`

id

每个select语句都会自动分配一个唯一标识
表示查询中操作表的顺序
- id相同：执行顺序由上而下
- id不同：如果是子查询，id会自增；id越大，优先级越高
- id相同的不同的同时存在
id列尾NULL表示这是个结果集，不需要用来进行查询

select_type（重要）

查询类型，主要用于区分普通查询、联合查询（union,union all）、子查询。

simple:不需要使用union操作或者包含子查询的简单select查询。有连接查询时，外层的查询也是simple，有且只有一个。
primary:需要union插座或者含有子查询，位于最外层的单位查询的select_type是primary

mysql> explain select (select name from user where name='xxxx') from user;
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | user  | NULL       | index | NULL          | index_com | 198     | NULL  |    9 |   100.00 | Using index |
|  2 | SUBQUERY    | user  | NULL       | ref   | index_com     | index_com | 99      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------------+
2 rows in set (0.00 sec)

subquery：除了from子句中包含的子查询外，其他地方出现的子查询都可能是subquery

mysql> explain select * from user where id=(select max(id) from user);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY     | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL                         |
|  2 | SUBQUERY    | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
2 rows in set (0.00 sec)

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

mysql> explain select id, name,(select name from dep d where d.dep=u.id) from user u;
+----+--------------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | u     | NULL       | index | NULL          | index_com | 198     | NULL |    9 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | d     | NULL       | ALL   | NULL          | NULL      | NULL    | NULL |    2 |    50.00 | Using where |
+----+--------------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
2 rows in set (0.00 sec)

union:union连接的两个查询，第一个是primary，除第一个外的，都是union

mysql> explain select * from user where name='xxxx' union select * from user where name='zhangsan';
+----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | user       | NULL       | ref  | index_com     | index_com | 99      | const |    1 |   100.00 | Using index     |
|  2 | UNION        | user       | NULL       | ref  | index_com     | index_com | 99      | const |    1 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL      | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------+
3 rows in set (0.00 sec)

dependent union:与union一样，出现union或者union all语句中，表示查询受外部表查询的影响
union result:包含union的结果集，id为NULL
Derived:from子句中出现的子查询，也叫做派生表

table

表名，查询中使用了别名，这里就显示别名

type（重要）

依次由好到差

1	`system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL`

除了ALL，其他类型都可以用到索引；

除了index_merge，其他type值可以用到一个索引

system：表中只有一行或者是空表
const（重要）：使用唯一索引或者主键索引

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

eq_ref（重要）：连接字段主键或者唯一索引

通常出现在多表的join查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询到额比较操作通常是“=”，效率更高

mysql> explain select a.id from user a left join dep b on a.dep=b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL       |    9 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.dep |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+

ref（重要）：针对非唯一索引，使用等值（=）查询非主键，或者使用了最左前缀原则索引的查询

mysql> explain select * from user where name='xxx';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_com     | index_com | 99      | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

mysql> explain select * from user where name='xxx' and age=2;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_com     | index_com | 99      | const |    2 |    11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set (0.01 sec)

fulltext：全文索引检索
ref_or_null：与ref类似，只是增加了null值的比较，实际上用的不多
unique_subquery：用于where中的in形式子查询，子查询返回不重复唯一值
index_subquery：用于in形式子查询使用到辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重
range（重要）：索引范围扫描，常见于>, <, is null, between, in, like等运算符的查询

mysql> explain select * from user where name='xxx' and age>3;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | index_com     | index_com | 104     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set (0.00 sec)

index_merge：表示使用两个以上的索引，最后去交集或者并集。常见and, or的条件使用不同的索引。官方排序这个在ref_or_null之后，但实际上由于要读取所有索引，性能大部分时间不如range
index（重要）：关键字：条件是出现在索引树中的节点。可能没有完全匹配索引

索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理，不需要读取文件查询，可以使用索引排序或者分组的查询。

mysql> explain select * from user order by id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    9 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set (0.00 sec)

ALL：全表扫描

possible_keys

此次查询中可能选用的索引，一个或多个

key

查询真正使用到的索引。select_type为index_merge时，这里可能会出现两个以上索引；其他情况，select_type只会出现一个

key_len

用于处理查询的索引长度。如果是单列索引，那就整个索引算进去；如果是多列索引，那么查询不一定是所有的列。
另外，key_len指极端where条件中用到的索引长度，而排序和分组苦短用到了索引，也不会计算到key_len中。
不是准确值

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)

int为4

ref

如果使用的是常数等值，这里回现实const
如果是链接查询，被驱动表的执行计划这里会显示驱动表的关联字段
如果是条件使用表达式或者函数，或者条件列发生内部隐式转换，这里可能会显示func

rows

这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确值，MyISAM是精确值，主要原因是InnoDB里面使用了MVCC并发机制）

Extra（重要）

这一列包含不适合其他列显示但非常重要的额外信息。

using temporary

表示使用了临时表
MySQL在对比查询结果order by 和group by
临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量（used_tmp_table, used_tmp_disk_table）才能看出来

mysql> explain select distinct a.id from user a,dep b where a.dep=b.id;
+----+-------------+-------+------------+-------+-------------------+---------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys     | key     | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+-------------------+---------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | b     | NULL       | index | PRIMARY           | PRIMARY | 4       | NULL |    2 |   100.00 | Using index; Using temporary                       |
|  1 | SIMPLE      | a     | NULL       | ALL   | PRIMARY,index_com | NULL    | NULL    | NULL |    9 |    11.11 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+-------------------+---------+---------+------+------+----------+----------------------------------------------------+
2 rows in set (0.03 sec)

no tables used

不带from子句的查询或者From dual查询（oracle操作）

使用not in()形式子查询或者not exists运算符的连接查询（反连接）

即，一般链接查询是先查询哪表，再查询外表；反连接是先查询外表，再查询内表。

mysql> explain select now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.00 sec)

using filesort(重要)

排序时无法使用到索引会出现这个，常见于order by 和group by语句中
说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取
MySQL中无法利用索引完成的排序操作称为“文件排序”

mysql> explain select * from user order by dep;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.00 sec)

using index(重要)

查询时不需要回表查询,直接通过索引就可以获取查询的数据

表示想醒的select查询中使用到了覆盖索引，避免访问表的数据行，效率不错
如果同时使用Using where，说明索引被用来查找索引键值
如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作

mysql> explain select name, age from user where name='xxx';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_com     | index_com | 99      | const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, (0.01 sec)

using where(重要)

表示存储引擎返回的记录并不是所有都满足查询条件，需要server层进行过滤。效率比较低

mysql> explain select * from user where age=23;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> explain select name, age from user where name='xxx' order by id;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                                    |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | index_com     | index_com | 99      | const |    2 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+------------------------------------------+
1 row in set (0.00 sec)

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

mysql> explain select * from user where name='xxx' and age>3;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | index_com     | index_com | 104     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name like 'xx%';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | index_com     | index_com | 99      | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set (0.00 sec)

firstmatch(tb_name)

5.6.x开始引入的优化子查询的新特性之一，常见于where子句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个。

loosescan(m..n)

5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个.

除此之外，还有很多查询数据字典库，执行计划过程中发现不可能存在结果的一些提示信息

filtered

使用explain extended时会出现这个。5.7之后的版本默认会有这个字段（不需要使用explain extended）。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录比例（100%不是具体记录数）。

锁原理及案例

锁基础

按照所里的粒度分类，MySQL主要包含三种类型（级别）的锁定机制：

全局锁：锁的是整个datebase，由MySQL的SQL layer层实现
表级锁：锁的是某个table，由MySQL的SQL layer层实现
行级锁：锁的是某行数据，也可能是行之间的间隙，由某些存储引擎实现，比如InnoDB

按照锁的功能可以分为：共享读锁和排它写锁

按照锁的实现方式分为：悲观锁和乐观锁

表级锁与行级锁区别

表级锁：开销小，加锁块；不会出现死锁；锁粒度大，发生冲突的概率高，并发度低
行级锁：开销大，加锁慢；会出现死锁；锁粒度小，发生锁冲突的概率低，并发度高

表级锁

表级锁有两种：

表锁
与数据锁（meta data lock, MDL）

查看锁状态

mysql>  
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 172   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 61    |
| Table_open_cache_misses    | 10    |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.05 sec)

表现形式：

表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

手动加锁

1	`lock table 表名称 read(write), 表名称2 read(write), 其他；`

查看表锁情况

1	`show open tables;`

删除表锁

1	`unlock tables;`

操作演示

-- 新建表
 
CREATE TABLE mylock (
	id INT(11) NOT NULL AUTO_INCREMENT,
	name VARCHAR(20) DEFAULT NULL,
	PRIMARY KEY (id)
);
INSERT INTO mylock VALUES(1, 'a');
INSERT INTO mylock VALUES(2, 'b');
INSERT INTO mylock VALUES(3, 'c');
INSERT INTO mylock VALUES(4, 'd');
INSERT INTO mylock VALUES(5, 'e');

-- session1
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from dept;
ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES
-- session2
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)
mysql> update mylock set name='bb' where id=2;  -- 修改阻塞，自动加上行写锁
-- session1
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
-- session2
Query OK, 1 row affected (1 min 35.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0        -- session1解锁后立刻执行
mysql> update mylock set name='b' where id=2;    
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- session1
mysql> select * from dept;                      -- session1可以访问其他表
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

-- session1
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from dept;
ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES
mysql> update mylock set name='b' where id=2;   -- session可以修改
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- session2
mysql> select * from mylock;                    -- 查询阻塞
-- session1
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
-- session2
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)

元数据锁(MDL)

MDL不需要显示使用，在访问一个表的时候会自动加上。**MDL的锁作用是保证写的正确性，可以想像一下：如果一个查询正在遍历表中的数据，而执行期间另一个线程对这个表结构做变更删除一行，那么查询县城拿到的结果便可能跟当前的表结构对应不上。

因此，在MySQ L5.5版本中引入MDL，当对一个表中做增删改查操作时，加MDL读锁；当要对表结构变更操作时，加MDL写锁。

读锁之间不互斥，因此可以有多个线程对一行表增删改查
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程同时要给一个表加字段，其中一个要等另一个执行完才能执行。

-- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
+----+------+
5 rows in set (0.00 sec)
-- session2
mysql> alter table mylock add dep int;        -- 阻塞
-- session1
mysql> commit;                                -- 释放锁
Query OK, 0 rows affected (0.00 sec)
-- session2
Query OK, 0 rows affected (12.80 sec)         -- 完成修改
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mylock;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| dep   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MySQL行级锁

基础概念

由存储引擎实现，利用存储引擎锁住索引项来实现。

InnoDB的行级锁，按照锁定范围来说，分为三种：

1
2
3

- 记录锁（Record locks）:锁定索引中一条记录
- 间隙锁（Gap Locks）:要么锁索引中间的值，要么锁住第一个索引记录前面的值或者最后一个索引后面的值。
- Next-key Locks:是索引记录上的记录锁在索引记录之前的间隙锁的组合

InnoDB的行级锁，按照功能分为两种：

1
2

- 共享锁（S）:允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
- 排它锁（S）:允许获得排它锁的事务更新数据，阻止其他事务获得相同数据集的共享读锁(不是读)和排它锁

对于UPDATE、DELETE、INSERT语句，InnoDB会自动给设计数据集加排它锁（X）;

对于普通SELECT语句，InnoDB不回家任何锁，事务可以通过以下语句显示给记录集加共享锁和排它锁。

手动添加共享锁(S)

1	`select * from table_name where ... lock in share mode;`

手动添加排它锁(X)

1	`select * from table_name where ... for update;`

InnoDB也实现了表级锁，也就是意向锁。意向锁是mysql内部使用的，不需要用户干预。

1
2

- 意向共享锁（IS）:事务打算给数据行加行共享锁，事务给一个数据行加行共享锁必须先去的该表的IS锁。
- 意向排它锁（IX）:事务打算给数据行加行排它锁，事务给一个数据行加行排它锁必须先去的该表的IX锁。

意向锁和行锁可以共存，意向锁的主要作用是为了【全表更新数据】是的性能提升。否则在全表更新数据时，需要先检索该表是否有某系记录上面有行锁。

	共享锁(S)	排它锁(X)	意向共享锁（IS）	意向排它锁（IX）
共享锁(S)	兼容	冲突	兼容	冲突
排它锁(X)	冲突	冲突	冲突	冲突
意向共享锁（IS）	兼容	冲突	兼容	兼容
意向排它锁（IX）	冲突	冲突	兼容	兼容

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

InnoDB锁使用行级锁争用状态查看命令

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |  -- 当前正在等待锁定的数量
| Innodb_row_lock_time          | 0     |  -- +从系统启动到现在锁定总时间长度
| Innodb_row_lock_time_avg      | 0     |  -- +每次等待锁话平均时间
| Innodb_row_lock_time_max      | 0     |  -- 从系统启动到现在等待最长那次时间
| Innodb_row_lock_waits         | 0     |  -- +从系统启动到现在总共等待的次数
+-------------------------------+-------+
5 rows in set (0.00 sec)

两阶段锁

传统的RDMS加锁原则就是2PL（Two-Phase Locking,两阶段锁）。相对而言，2PL比较容易理解：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。

1 2	`加锁阶段：只加锁，不放锁解锁阶段：只放锁，不加锁`

行读锁

-- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock where id=1 lock in share mode; -- 手动id=1加行读锁
+----+------+------+
| id | name | dep  |
+----+------+------+
|  1 | a    | NULL |
+----+------+------+
1 row in set (0.00 sec)
-- session2

mysql> update mylock set name='bb' where id=2;  -- 未锁定可以修改
mysql> update mylock set name='aa' where id=1;  -- 锁定不可修改
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction   -- 锁定超时
Query OK, 1 row affected (0.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 0
-- session1
mysql> commit;  -- 提交事务/rollback释放锁
Query OK, 0 rows affected (0.00 sec)
-- session2
Query OK, 1 row affected (12.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

行读锁升级为表锁

-- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mylock where name='a' lock in share mode; -- 手动name='a'非索引 行读锁升级表锁
+----+------+------+
| id | name | dep  |
+----+------+------+
|  1 | a    | NULL |
+----+------+------+
1 row in set (0.00 sec)
-- session2
mysql> update mylock set name='b' where id=2;  -- 阻塞 不可修改
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update mylock set name='a' where id=1;   -- 阻塞 不可修改
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- session1
mysql> commit;  -- 提交事务/rollback释放锁
Query OK, 0 rows affected (0.00 sec)
-- session2
mysql> update mylock set name='b' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update mylock set name='a' where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

行写锁

-- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock where id=1 for update;
-- session2
mysql> update mylock set name='bb' where id=2;  -- 未锁定可以修改
Query OK, 1 row affected (0.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from mylock where id=1;  -- select本身不加锁 可以访问
+----+------+------+
| id | name | dep  |
+----+------+------+
|  1 | a    | NULL |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from mylock where id=1 lock in share mode; -- 共享读锁不可获得
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 
mysql> update mylock set name='aa' where id=1;  -- 锁定不可修改
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 
-- session1
mysql> commit;  -- 提交事务/rollback释放锁
Query OK, 0 rows affected (0.00 sec)
-- session2
Query OK, 1 row affected (12.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

行写锁升级为表锁

-- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mylock where name='a' for update; -- 手动name='a'非索引 行读锁升级表锁
+----+------+------+
| id | name | dep  |
+----+------+------+
|  1 | a    | NULL |
+----+------+------+
1 row in set (0.00 sec)
-- session2
mysql> select * from mylock;
+----+------+------+
| id | name | dep  |
+----+------+------+
|  1 | a    | NULL |
|  2 | b    | NULL |
|  3 | c    | NULL |
|  4 | d    | NULL |
|  5 | e    | NULL |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select * from mylock where id=1;
+----+------+------+
| id | name | dep  |
+----+------+------+
|  1 | a    | NULL |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from mylock where id=1 lock in share mode; -- 阻塞 不可获得锁
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update mylock set name='b' where id=2;  -- 阻塞 不可修改
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update mylock set name='a' where id=1;   -- 阻塞 不可修改
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- session1
mysql> commit;  -- 提交事务/rollback释放锁
Query OK, 0 rows affected (0.00 sec)
-- session2
mysql> update mylock set name='b' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update mylock set name='a' where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

间隙锁

create table news(id int, number int, primary key(id), index idx_num(number));
insert into news values(1,2);
insert into news values(3,4);
insert into news values(6,5);
insert into news values(8,5);
insert into news values(10,5);
insert into news values(13,11);
mysql> select * from news;
+----+--------+
| id | number |  # id主键 索引 number
+----+--------+
|  1 |      2 |
|  3 |      4 |
|  6 |      5 |
|  8 |      5 |
| 10 |      5 |
| 13 |     11 |
+----+--------+
6 rows in set (0.01 sec)

-- session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from news where number=4 for update;
-- id在2、5之间和3-5之间且number2-6在均被锁住
+----+--------+
| id | number |
+----+--------+
|  3 |      4 |
+----+--------+
1 row in set (0.01 sec)
-- session2
mysql> start transaction;
mysql> insert into news values(2,4);  -- 阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into news values(2,2);  -- 阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into news values(4,4);  -- 阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into news values(4,5);  -- 阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into news values(5,7);
Query OK, 1 row affected (0.00 sec)
mysql> insert into news values(7,5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into news values(9,5);
Query OK, 1 row affected (0.01 sec)
mysql> insert into news values(11,5);
Query OK, 1 row affected (0.01 sec)

insert into news values(13,11);
mysql> select * from news;
mysql> select * from news;
+----+--------+
| id | number |
+----+--------+
|  1 |      2 |
|  3 |      4 |
|  6 |      5 |
|  8 |      5 |
| 10 |      5 |
| 13 |     11 |
+----+--------+
6 rows in set (0.01 sec)
-- session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from news where number=13 for update;
      （select * from news where id>1 and id<4 for update;）
mysql> start transaction;
mysql> insert into news values(11,5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into news values(12,11);
Query OK, 1 row affected (0.00 sec)
mysql> insert into news values(14,11);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into news values(15,12);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
检索条件number=13，想做取得最靠近的值11作为作区间，向右由于没有记录因此取得无穷大作为右区间，即session1间隙锁区间（11，无穷大）

死锁

两个session互相等待对方的释放资源之后，才能释放自己的资源

-- session1
mysql>begin;
Query OK, 0 rows affected (0.00 sec)
mysql>update mylock set name='m' where id=1;  -- 手动加行写锁 id=1,使用索引
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- session2
mysql>begin;
Query OK, 0 rows affected (0.00 sec)
mysql>update mylock set name='m' where id=2;  -- 手动加行写锁 id=2,使用索引
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- session1
mysql>update mylock set name='nn' where id=2; -- 加写锁被阻塞
-- session2
mysql>update mylock set name='nn' where id=1; -- 加写锁会死锁 不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

分库分表原理及实践

MyCat

核心概念

Schema：由它指定逻辑数据库（相当于MySQL的datebase数据库）
Table：逻辑表（相当于MySQL的table表）
DataNode：真正存储数据的物理节点
DateHost：存储节点所在的数据库主机（指定MySQL数据库的连接信息）
User：MyCat用户（类似于MySQL的用户，支持多用户）

主要解决问题

海量数据存储
查询优化

支持数据库

分片策略

MyCAT支持水平分片语垂直分片：

水平分片：一个表格的数据分割到多个节点上，按照行分割。
垂直分片：一个数据库中多个表A,B,C，A存储到节点1，B存储到节点2，C存储到节点3。

MyCAT通过定义表的分片规则来实现分片，没个表格可以捆绑一个分片规则，每个分片规则制定一个分片字段并绑定一个函数，来实现动态分片算法。

Schema：逻辑库，与MySQL的datebase数据对应，一个逻辑库中定义了所有包括的Table。
Table：表，即物理数据库中存储的某张表，与传统数据库不同，这里的表格需要声明其所存储的逻辑数据节点DataNode。可以指定表的分片规则
DataNode：MyCAT的逻辑数据节点，是存放Table的具体物理节点，也称之为分片节点，通过DateHost来关联到后段某个具体数据库上。
DateHost：定义某个物理库的访问地址，用于捆绑到DataNode上。

安装

# 下载
wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

# 解压
tar -zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz /weitrue/install

# 进入mycat目录
cd /weitrue/install/mycat

# 启动/关闭  进入mycat/bin下
./mycat start     # 启动
./mycat stop      # 关闭
./mycat restart   # 重启
./mycat status    # 状态

# 使用mysql客户端直接连接mycat服务,默认端口【8066】
mysql -uroot -p123456 -h127.0.0.1 -P8066

分片设置

配置schema.xml文件

schema.xml是Mycat中重要的配置文件之一，主要管理着MyCAT的逻辑库、表、分片规则、DataNode以及DateHost之间的映射关系。

<schema>定义Mycat实例中的逻辑库。
- <table>定义Mycat中的逻辑表。
<dataNode>定义了Mycat的数据节点，即数据分片。
<dataHost>定义了具体的数据库实例、读写分离配置和心跳语句。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
				<!-- 
						schema:逻辑库 name：逻辑库名 sqlMaxLimit 一次去多少条数据，如果超过limit
              table 逻辑表
                dataNode 数据节点 对应 dataNode标签名
                rule 分片规则 对应rule.xml
                subTables	字表		
                primaryKey 分片主键 可缓存
 				-->
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" randomDataNode="dn1">
                <!-- auto sharding by id (long) -->
                <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
                <table name="item" dataNode="dn1,dn2,dn3" rule="mod-long" primaryKey="ID"/>               
        </schema>
  
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"/> -->
        <dataNode name="dn1" dataHost="localhost1" database="db1" />
        <dataNode name="dn2" dataHost="localhost1" database="db2" />
        <dataNode name="dn3" dataHost="localhost1" database="db3" />
				<!-- 
						dataHost:数据主机
            balance 1:读写分离 0:读写不分离
            writeType 0:第一个writrHost写 1:随机writrHost写
            dbDriver 数据驱动 native: MySQL JDBC:Oracle SQLServer
            switchType	是否主动读 1:主从自动切换 -1:不切换 2:当从机延时超过slaveThreshold(毫秒级)值时切换为主读		
 				-->
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
                </writeHost>
        </dataHost>

`rule.xml`

rule.xml定义我们对表拆分涉及到的规则定义。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="mod-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">3</property>
        </function>
</mycat:rule>

`server.xml`

十大分片规则

连续分片

日期分片
- 按月
- 按日
- 按小时
范围约定：配置简单，即预先制定可能的id范围对应某个分片
- 优势：扩容无需迁移数据
- 缺点：热点数据，并发受限

离散分片

枚举法
求模法
字符串拆分hash法
固定分片hash法
一致性哈希（解决扩容问题）
编程指定

综合分片

通配取模
ASCii码求模通配

读写分离

建立在MySQL主从复制的基础上实现，必须搭建MySQL的主从复制。

<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"/> -->
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataNode name="dn2" dataHost="localhost1" database="db2" />
    <dataNode name="dn3" dataHost="localhost1" database="db3" />
<!-- 
		dataHost:数据主机
        balance 1:读写分离 0:读写不分离
        writeType 0:第一个writrHost写 1:随机writrHost写
        dbDriver 数据驱动 native: MySQL JDBC:Oracle SQLServer
        switchType	是否主动读 1:主从自动切换 -1:不切换 2:当从机延时超过slaveThreshold(毫秒级)值时切换为主读		
	-->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                      writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
            <heartbeat>show slave status</heartbeat>
            <!-- can have multi write hosts -->
            <writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
            </writeHost>
    </dataHost>

switchType="2"和slaveThreshold="100"意味着开启MySQL主从复制状态绑定的读写分离语切换机制。Mycat心跳机制通过检测show slave status中"Seconds_Behind_Master" "Slave_IO_Running" "Slave_SQL_Running"三个字段来确定当前主从的状态以及Seconds_Behind_Master主从复制延时。

MySQL分库分表

数据切分方案

垂直切分：根据业务模块进行切分，不同模块的表分到不同的库中。
水平切分：将一张大表按照一定切分规则，按照行切分到不同表或者不同库中。

水平切分原则

常用的主要分为：

按照ID取模：对ID取模，余数决定改行数据放到哪张表或者哪个库中。
按照日期：按照年月日，将数据切分到不同的表或者库中。
按照范围：可以对某一列按照范围进行切分不同的范围切分到不同的表或者库中。

切分原则

第一原则：能不切分尽量不要切分
第二原则：如果要切分一定要选择合适的规则，提前规划好
第三原则：数据切分尽量通过数据冗余或者表分组来降低跨库join的可能
第四原则：由于数据库中间件对数据join实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量少使用多表join

分库分表需要解决问题

分布式事务问题

分布式主键ID问题

跨库join问题

跨库count、group、orderby问题

Datebase

#Datebase #MySQL

【数据库】MySQL与TiDB学习笔记

https://weitrue.github.io/2024/06/06/mysql/

作者

Pony W

发布于

2024年6月6日

许可协议

【数据库】MySQL面试题下一篇

【数据库】MySQL与TiDB学习笔记

文件存储

日志文件

错误日志（errorlog）

查询日志

慢查询日志

二进制日志（bin log）

回滚日志(undo log)

重做日志(redo log)

SQL语句的交互流程

连接器

查询缓存

分析器

查询优化器

存储引擎

执行器

索引原理及优化

索引介绍

什么是索引

索引优点和缺点

索引分类

单列索引

组合索引

全文索引

空间索引

索引使用

创建索引

删除索引

查看索引

索引存储结构

InnoDB存储引擎逻辑结构

索引存储结构

B树与B+树

B树与B+树的区别

非聚集索引（MyISAM）

主键索引

辅助索引（次要索引）

聚集索引（InnoDB）

主键索引

辅助索引

组合索引

哪些情况要创建索引

哪些情况不需要创建索引

为什么使用组合索引

索引失效与分析

执行计划

参数说明

id

select_type（重要）

table

type（重要）

possible_keys

key

key_len

ref

rows

Extra（重要）

filtered

相关面试题

什么是索引？

索引分别有哪些优点和缺点？

索引有几种类型？分别如何创建？

能否给手机号的前 6 位创建索引？如何创建？

如何查询一张表的所有索引？

主索引和唯一索引有什么区别？

主索引和非主索引有什么区别？

辅助索引为什么只存主键？

在 InnDB 中主键索引为什么比普通索引的查询性能高？

唯一索引和普通索引哪个性能更好？

什么叫回表查询？

以下 SQL 有什么问题？该如何优化？

为什么 MySQL 官方建议使用自增主键作为表的主键？

自增主键有哪些优缺点?

MySQL 最多可以创建多少个索引列？

以下 like 查询会使用索引的是哪一个选项？为什么？

如何让 like %abc 走索引查询？

列值为 NULL 时，查询会使用到索引吗？

以下语句会走索引么？

MySQL 联合索引应该注意什么？

联合索引的作用是什么？

回滚日志(`undo log`)

重做日志(`redo log`)

`rule.xml`

`server.xml`