【无标题】

MySQL基础架构

[外链图片转存中…(img-I4QiGQUK-1649062565741)]

连接器

执行连接时会到权限表中查权限；连接完成后若没后续动作则处于空闲状态（可以使用show processlist看到），默认8小时没动静就会断开

数据库中尽量使用长连接，但因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的，所以在执行完一个比较大的操作后可以定期断开长连接再重连或通过mysql_reset_connection重新初始化连接资源

分析器

分析器主要是用来分析 SQL 语句是来干嘛的，分为两步：
词法分析：一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步
语法分析：主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法

优化器

优化器的作用就是它认为的最优的执行方案去执行,比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

查询语句

执行流程：
1、先检查该语句是否有权限，如果没有权限，直接返回错误信息
2、通过分析器进行词法分析，提取 sql 语句的关键元素，然后判断这个 sql 语句是否有语法错误
3、优化器根据自己的优化算法进行选择执行效率最好的一个方案
4、开始执行时进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

日志

redo log是InnoDB特有，binlog是Server层实现的
redo log是循环写的。空间固定会用完；binlog是追加写入，不会覆盖以前的日志
redo log能safe-crash,binlog不能

更新语句

1、执行器先从内存中找到数据页，若没有从磁盘读入内存再返回
2、执行器拿到引擎给的行数据，再调用引擎接口写入新的行数据
3、引擎将这行新数据更新到内存中，同时将更新操作记录到redo log中，redo log处于prepare状态，告知执行器随时可以提交事务
4、执行器生成此操作的binlog并写入磁盘
5、执行器调用引擎的提交事务接口，把redo log改为commit状态，更新完成

两阶段提交

当误操作或者需要扩容的时候（多搭建一些悲苦增加系统读能力时），用全量备份和应用binlog实现，两阶段提交保证主从数据库一致

几个重要的概念:

1.函数依赖：A–>B，如果通过A属性（属性组）的值，可以确定唯一的B属性的值，则称B依赖于A

例如：学号---->姓名    （学号、课程名称 的属性组）-->  分数

2.完全函数依赖：A–>B 如果A是一个属性组，则B属性值的确定需要依赖A属性组的中所有的属性值

例如：（学号、课程名称）--> 分数

3.部分函数依赖： A–>B 如果A是一个属性组，则B属性值的确定只需要依赖A属性组的中某一些的属性值（第二范式就是消除这个）

例如：（学号 、课程名称）--> 姓名

4.传递函数依赖：A – >B , B – >C 如果通过A属性（属性组）的值，可以确定唯一的B属性的值，再通过B属性（属性组）的值，可以唯一确定C属性的值，那么称C传递依赖于A(第三范式就是消除这个)

例如： 学号 --> 系名  ，系名 --> 系主任

5.码 ：如果在一张表中，一个属性或属性组，被其他所有的属性（非主属性）所完全函数依赖，则称这个属性（属性组）为该表的码。（上面的表，学号和课程名称所构成的属性组就是码）

例如： 该表中码为 （学号、课程名称）

6.主属性： 码中所有属性
7.非主属性： 除码之外的所有属性

事务的四大特性

1.原子性:事务是最小的执行单位，要么全部完成要么完全不起作用
2.一致性:执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的
3.隔离性:并发访问数据库时，一个用户的事务不被其他事务干扰，各并发事务之间数据库独立
4.持久性:一个事务被提交后，对数据库中数据的改变是持久的

并发事务的问题

1、脏读:当一个事务正访问数据并对数据进行了修改，此时修改还未提交到数据库而另一个事务也访问了这个数据，此时这个数据就是脏数据
2、丢失修改:在一个事务读取一个数据时，另一个事务也访问了该数据，则第一个事务中修改了这个数据后，第二份事务也修改了这个数据。此时第一个事务修改就被丢失
3、不可重复读:指在一个事务内多次读同一个数据，此时另一个事务修改了该数据，导致第一个事务两次读到的数据不一致
4、幻读:指在一个事务内多次读同一个数据，此时另一个事务增加或者删除了该数据，导致第一个事务两次读到的数据不一致

事务隔离级别

READ-UNCOMMITTED(读未提交):最低隔离级别，允许读取尚未提交的数据变更
READ-COMMITTED(读已提交):允许读取并发事务已经提交的数据，可以阻止脏读
REPEATABLE-READ(可重复读):对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己修改，可以阻止脏读和不可重复读
SERIALIZABLE(可串行化):最高的隔离级别，所以事务依次逐个执行，事物之间完全不能产生干扰

InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ,可通过SELECT @@transaction_isolation查看
这里需要注意InnoDB存储引擎在这种隔离级别下使用的Next-Key Lock锁算法，可以避免幻读产生，因此达到了SQL标准的SERIALIZABLE隔离级别
因为隔离级别越低，事务请求的锁越少，因此大部分数据库系统隔离级别都是READ-COMMITTED，但InnoDB存储引擎默认的隔离级别不会有任何性能损失，在分布式事务下一般用到SERIALIZABLE隔离级别

InnoDB基本存储结构

表空间

由于表与文件的耦合度容易制约数据库使用的便利性，于是在文件与表之间加了一层表空间，向上对接表，向下对接文件；分为系统表空间和独立表空间（5.6.6后默认开启）

独立表空间在创建表时，自动创建"表名.ibd"

Extent（区/簇）

一个磁盘或文件的容量不便管理，因此把文件划分成一个个大小相等的存储块，即页
根据局部性原理，为提高读操作的性能，将相临的数据尽可能在物理上也存储在相邻的页中，因此引入了Extent用来分配连续存储空间和管理区内存储空间状态

Segment(段)

为了管理区的使用情况以及提供空间存储状态，将具有相关性区的存储空间状态的管理信息称为段实体，段实体所管理的区总和为段

因为索引数据与业务行数据分别具有不同的数据结构，所以叶子节点存储在索引段，非叶子节点存储在数据段

Page(页)

一张表对应一个聚集索引，聚集索引元数据中指定了root page的页号；page不仅用来存储业务相关数据，也存储了extent和segment信息

页分为页头、页身和页尾
页头指明当前页号、类型和所属表空间，页身用来存储数据，页尾用于数据校验

页身

页身分为表空间首页头信息区和业务数据区
FSP HEADER:表空间入口页，保存了表空间信息、段链表指针（Inode Page）和空闲碎片区链表指针（不属于段属于表空间）
XDES Entry:一个Extent的实体，包含了段id、碎片区链表中下一个节点指针
XDES页：存储XDES Entry的页，通过这个页号和Entry位置偏移量计算256个XDES Entry在磁盘上的位置，因此XDES Entry无需记录Extent的地址

User Records

next_record字段指的是下一条记录的数据内容的起使部分，使得每个数据页中的记录又可以组成一个单向链表，而链表的顺序是按照主键大小排列，此时Infumum和Supremum相当于记录链表的哨兵节点

Page Directory

为了提高查找效率，将整个链表进行分组，将分组内最后一条记录（即组内最大记录）的地址存放在一个Slot（槽）中，各Slot根据其所指向的记录按从大到小的顺序在页目录中排列
因此在通过主键查找某条记录的时候，可以在Page Directory中使用二分法快速定位到对应的Slot槽，也就定位到了分组，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

B树

特点：
1、所有节点关键字按递增次序排列，遵循左小右大原则
2、所有叶子节点均在同一层，叶子节点包含了关键字、关键字记录的指针以及子节点的指针
3、这种方式把树的节点关键字增多，树的层级比原来的二叉树少了，减少数据查找的次数和复杂度

问题：
B树不管叶子节点还是非叶子节点，都会保存数据，导致能保存的指针数量变少，指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低

B+树

特点：
1、非叶子节点不保存关键字记录的指针，只进行数据索引，使得非叶子节点所能保存的关键字大大增加
2、叶子节点保存了父节点的所有关键字记录的指针，因此所有数据地址必须要到叶子节点才能获取到
3、叶子节点关键字从小到大排序，左边结尾数据会保存右边节点开始数据的指针，构成了有序链表

因此对比B树，B+树的层级更少，查询速度更稳定以及全节点遍历更快，天然具备排序功能，

在InnoDB中B+树节点与Page页是一一对应的，如果聚集索引使用自增的主键，那么数据是以追加的方式存储在每一页中；如果聚簇索引使用的是uuid,容易导致page页分离，类似向数组中插入数据一样先进行移动

索引

索引的本质是一种排好序的数据结构，类似于字典中的目录，有Hash索引和二叉树索引

只有当索引帮助存储引擎快速查到记录带来的好处大于其带来的额外工作，才是有效的。对于小表来说全表扫描更高效，对于中大型表索引非常有效，对于特大型表需要分区。

Hash索引(Mysql Innodb引擎不支持)

Hash索引适合精确查找，不适合范围查找，因为存储引擎都会为每一行计算一个hash码，hash码彼此之间没有规律的被分到不同的桶中，因此只能进行全值匹配的查询

B-Tree索引：

根节点槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找；通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，叶子节点的指针指向被索引的数据而不是其他的节点页

有效的查询：全值匹配，最左前缀，列前缀，范围值，只访问索引

索引页

若数据页过多则对应的主键目录就会很大，因此需要用到索引页

主键索引

MySQL会将里面的记录拆分到不同的索引页中，索引页中记录的是每页数据页的页号和该数据页中最小的主键记录，即最小主键和数据页从主键目录演变成了索引页，而维护索引页的索引页是在真正存储记录和数据页的索引页的上一层，最终构成了一个B+树,像这种索引页+数据页组成的B+树就是聚簇索引
聚簇索引是MySQL基于主键索引结构创建的
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CXUedSfS-1649062565743)(https://cdn.jsdelivr.net/gh/geek-cy/img/MySQL/v2-e79c5057bed36c40ac34e265678eb38c_b.jpg)]

非主键索引

对于非主键索引原理是一样的，建立多少个索引，MySQL就会维护多少个B+树，因此索引不能建太多，占用空间
每个数据页中的记录存放的实际是索引字段和主键字段，其他字段是不存的

非主键索引维护过程：对于非主键索引（一般都是联合索引），在维护 B+ 树的时候，会根据联合索引的字段依次去判断，假设联合索引为：name + address + age，那么 MySQL 在维护该索引的 B+ 树的时候，首先会根据 name 进行排序，name 相同的话会根据第二个 address 排序，如果 address 也一样，那么就会根据 age 去排序，如果 age 也一样，那么就会根据主键字段值去排序，且对于非主键索引，MySQL 在维护 B+ 树的时候，仅仅是维护索引字段和主键字段

回表

若select 后面的查询字段仅仅查询带有索引的字段，B+树就能查询到想要的结果了
但若select * from table，此时B+树的数据页获取不到其他属性值，这种情况下就需要回表查询了。
MySQL会根据定位到的某条记录中的id再次进行聚簇索引查询，因为聚簇索引中数据页的记录是一条完整的记录，这个过程就叫回表

因此回表的含义是:根据非主键索引查询到的结果并没有查找的字段值，此时就需要再次根据主键从聚簇索引的根节点开始查找，这样再次查找到的记录才是完成的

最左前缀原则

如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到，如User表的name和city加联合索引

select * from user where name=xx and city=xx; // 可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
select * from user where city==xx and name==xx;// 会自动优化为匹配联合索引的顺序
											   //因此可以命中索引

注意Order by子句也遵循次规则
注意避免冗余索引，查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

索引分类

0、查看索引
show index/keys from table_name;

1、添加PRIMARY KEY（主键索引）

ALTER TABLE table_name ADD PRIMARY KEY ( column )

2、添加UNIQUE(唯一索引)
ALTER TABLE table_name ADD UNIQUE ( column )

唯一索引控制该列不能有相同值

3、添加INDEX(普通索引)
普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column = ）或排序条件（ORDER BY column）中

ALTER TABLE table_name ADD INDEX index_name ( column )

4、添加FULLTEXT(全文索引)
ALTER TABLE table_name ADD FULLTEXT ( column)

通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题，比如
select * from 表名 where 标题 like ‘%xxx%’ or 内容 like ‘%xxx%’ or 作者 like ‘%xxx%’
查找效率低下，而全文索引使得关键词搜索更加高效，索引程序从数据库读取数据，对需要索引的内容进行分词，对分好词的一个个词条加入索引文件

5、添加多列索引（复合索引）
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

6、删除索引
DROP INDEX column on table_name

索引优化

索引的三星原则
1.索引将相关的记录放到一起，则获得一星
2.如果索引中的数据顺序和查找中的排列顺序一致则获得二星
3.如果索引中的列包含了查询中的需要的全部列则获得三星

有时如果无法设计出一个“三星”索引，那么不如忽略掉WHERE 子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引

前缀索引

索引更小，更快但无法做order by和group by,无法覆盖扫描
后缀索引类似，把字符串反转后存储

使用前缀索引会回表

索引的选择性是不重复的索引值/记录总数，越高则查找时过滤的行也越多，例如唯一索引的选择性就是1

select count(*) as cnt,city from city_demo GROUP BY city ORDER BY cnt desc limit 10
select count(*) as cnt,left(city,7) as pref from city_demo GROUP BY pref ORDER BY cnt desc limit 10
select count(DISTINCT left(city,1)) / count(*) as sel3 from city_demo

索引合并

可通过参数optimizer_switch来关闭索引合并功能，在EXPLAIN中看到索引合并有可能是查询和表结构还待优化

在多个列上建立独立的单列索引大部分情况下不能提高MySQL的查询性能

当出现服务器对多个索引做相交操作（通常有多个AND条件），通常意味着需要一个包含所有相关列的多个索引，而不是独立的单列索引
当服务器需要对多个索引做联合操作（通常有多个OR条件），通常需要耗费大量的cpu和内存资源在算法的缓冲，排序和合并的操作上。特别是当其中有些索引的选择性不高。需要合并扫描返回大量数据的时候

索引列顺序

经验法则：当不需要考虑排序和分组时，把选择性最高的列放在前面；但性能不只依赖于选择性，也和值的分布有关，需要根据运行频率最高的查询调整索引列的顺序
但在WHERE子句中的排序、分组和范围条件等其他因素可能对查询的性能有非常大的影响

聚簇索引

InnoDB特有
聚簇索引的每个叶子节点包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列；因此聚簇索引就像是整张表;使用主键值当作指向行的指针可以减少二级索引的维护

优点是：1、将相关数据保存在一起，根据ID聚集数据，中需要从磁盘读取少数数据页就能获得全部信息
2、聚簇索引将索引和数据保存在同一个B-Tree中，查找更快
3、使用覆盖索引扫描的查询可直接使用页节点中的主键值

缺点：1、提高IO密集型应用的性能，对内存中的数据提升有限
2、插入速度严重依赖于插入顺序；若不是按照主键顺序加载数据，则最好使用OPTIMIZE TABLE重新组织表
3、更新聚簇索引列代价较高
4、插入新行或主键被更新需要移动行的时候容易导致页分裂导致表占用更多空间
5、容易导致全包扫描更慢，尤其是页分裂造成数据存储不连续
6、二级索引（非聚簇索引）的叶子节点包含了引用行的主键列，空间会更大

覆盖索引

若索引包含所有需要查询的字段的值，称之为覆盖索引；无需回表查询

索引失效

1、联合索引的最左前缀原则
本质上来说，联合索引也是一个B+树，和单值索引不同的是，联合索引的键值对是大于1个,此时只有第一个字段是有序的，而当第一个字段相等的情况下，第二个字段才有序即其他字段是相对有序。因此当最左索引不在的时候，其他索引无法确定顺序

2、范围查询右边失效

select * from testTable where a>1 and b=2

理由同上，b有序的前提是a是确定的值，此时也无法确定b的顺序

3、like索引失效

where name like "a%" // 前缀、查询以"a"开头的数据，如abc
where name like "%a%" // 中缀、查询包含"a"的数据,如cab
where name like "%a" // 后缀、查询以"a"结尾的数据，如cba

注意只有前缀能用到索引，因为B+树的索引顺序是按照首字母大小进行排序

4、当where后条件为null时不能使用索引
mysql 官方建议是把索引字段设置为 not null

5、索引列参与表达式计算或作为函数的参数

唯一索引和普通索引选择问题

当需要更新一个数据页时，若数据页在内存中则直接更新，否则会将更新操作换成到change buffer中，在下次查询需要访问这个数据页的时候执行操作merge或者后台线程定期merge或者数据库正常关闭也会merge;change buffer在内存中有拷贝也会被写入到磁盘上

对于唯一索引来说，需要先判断表中是否存在这个数据，会先将数据页读入内存，因此不会使用到change buffer

对于写多读少的业务来说，change buffer收益就比较大；但若更新后马上做查询，这样反而增加了change buffer的维护代价，则起到了反作用

flush

redo log在内存中总会找机会更新到磁盘，即flush
flush的条件：
1、redo log满了，系统会停止所有更新操作，把checkpoint向前推进
2、系统内存不足，需要淘汰一些数据页，若淘汰的脏页就会写到磁盘
3、MySQL空闲的时候
4、MySQL正常关闭

存储引擎

MySQL5.5之前默认的存储引擎是MyISAM，虽然性能极佳且提供了大量特性，包括全文索引、压缩、空间函数等，但不支持事务和行级锁，因此崩溃后无法安全恢复。在读密集的情况下可以考虑使用

MyISAM使用前缀压缩技术使得索引更小，InnoDB按照原数据格式存储
MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行

两者对比：

存储引擎	MyISAM	InnoDB
锁	表级锁	行级锁（默认）和表级锁
事务	不支持事务	支持事务
外键	不支持	支持
多版本控制（MVCC）	不支持	支持

MVCC相关

乐观锁与悲观锁

乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现
乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程），比如行锁，表锁等，读锁，写锁等，像Java中synchronized和ReentrantLock等独占锁

乐观锁缺点：

1、ABA问题，不过在JDk1.5后AtomicStampedReference 类其中的compareAndSet 方法能够首先检查当前引用是否等于预期引用以及当前标志是否等于预期标志，如果全部相等，则以原子方式将该引用和该标志的值设置为给定的更新值
2、循环时间长开销大，若自旋CAS长时间不成功会给CPU带来更大的执行开销
3、只能保证一个共享变量的原子操作,CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无效。但是从 JDK 1.5开始，提供了AtomicReference类来保证引用对象之间的原子性，你可以把多个变量放在一个对象里来进行 CAS 操作.所以我们可以使用锁或者利用AtomicReference类把多个共享变量合并成一个共享变量来操作。

使用场景

多读场景可用乐观锁，省去了锁的开销，加大了系统的整个吞吐量
多写场景可用悲观锁，避免上层应用会不断的进行retry从而降低性能

实现方式

1、版本号机制
一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

2、CAS算法
不使用锁的情况下实现多线程之间的变量同步所以也叫非阻塞同步（Non-blocking Synchronization）。
CAS算法涉及到三个操作数:需要读写的内存值 V,进行比较的值 A,拟写入的新值 B
仅当V的值等于A时，CAS通过原子方式用新值B来更新V值，否则不会执行任何操作（比较和替换是一个原子操作）。一般情况下是一个自旋操作，即不断的重试。

锁机制与锁算法

表级锁：MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗少且加锁快不会出现死锁。因锁定粒度最大，触发锁冲突的概率最高，并发度最低
适用场景：1、事务更新大表中的大部分数据 2、事务比较复杂，使用行级锁容易引起死锁

行级锁：MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加索。能大大减少数据库操作的冲突。因锁定粒度最小，并发度搞，但加锁的开销大，加锁慢，会出现死锁
行级锁包括三种：

• Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项
• Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
• Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

innodb对于行的查询使用next-key lock，当查询的索引含有唯一属性时，降级为record lock
有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

explain分析

在MySQL中，我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序

1、select_type表示select的类型

SIMPLE：简单表，不使用表连接或子查询
PRIMARY：主查询，即外层的查询
UNION：UNION中的第二个或者后面的查询语句
SUBQUERY：子查询中的第一个

性能由高到底
Simple -> Primary -> Subquery -> Derived -> Union -> Union result

2、table:输出结果集的表（表别名）

3、type:表示MySQL在表中找到所需行的方式，或者叫访问类型。

常见访问类型如下，从上到下，性能由差到最好：
ALL：全表扫描

• 一般是没有where条件或者where条件没有使用索引的查询语句

index：索引全扫描

• 一般是查询的字段都有索引的查询语句

range：索引范围扫描

• 索引范围扫描，常用于<、<=、>、>=、between等操作，注意这种情况下比较的字段是需要加索引的，如果没有索引，则MySQL会进行全表扫描

ref：非唯一索引扫描或唯一索引的前缀扫描

• ref类型还经常会出现在join操作中：表关联查询时必定会有一张表进行全表扫描，此表一定是几张表中记录行数最少的表，然后再通过非唯一索引寻找其他关联表中的匹配行，以此达到表关联时扫描行数最少。

eq_ref：唯一索引扫描

• 一般出现在多表连接时使用primary key或者unique index作为关联条件

const/system：单表最多有一个匹配行，查询起来非常迅速，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理

NULL：不用扫描表或索引，直接就能够得到结果

4、possible_keys: 表示查询可能使用的索引

5、key: 实际使用的索引

6、key_len: 使用索引字段的长度

7、ref: 使用哪个列或常数与key一起从表中选择行

8、rows: 扫描行的数量

9、filtered: 存储引擎返回的数据在server层过滤后记录数量的比例(百分比)即返回结果的行数占需读取行数的百分比

10、Extra: 执行情况的说明和描述

包含不适合在其他列中显示但是对执行计划非常重要的
额外信息最主要的有一下三种：
Using Index：表示索引覆盖，不会回表查询
Using Where：表示进行了回表查询
Using Index Condition：表示进行了ICP优化
Using Flesort：表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果

读写分离

一般情况下，都会选择一主多从，主数据库负责写，其他数据库负责读，主库和从库之间进行数据同步，保证库中数据正确性。但也会带来一些问题

主从同步延迟

解决方法：
1、强制将读请求路由由主库处理

• 若从库数据过期了，就直接从主库读取，这种方案会增加主库压力，如使用Sharding-JDBC可以将最新数据的读请求交给主库处理

2、延迟读取

• 既然主从同步存在延迟，那就延迟之后读取。对于一些对数据比较敏感的场景，你可以在完成写请求之后，避免立即进行请求操作。比如你支付成功之后，跳转到一个支付成功的页面，当你点击返回之后才返回自己的账户

实现步骤

1、部署多台数据库，选择一台作为主数据库，其他台作为从数据库
2、保证主数据库和从数据库之间的数据实时同步，即主从复制
3、系统将写请求交给主数据库处理，读请求交给从数据库处理

主从复制原理

1、主库将数据库中数据变化写入binlog
2、从库连接主库
3、从库创建一个IO线程向主库请求更新的binlog
4、主库创建一个binlog dump线程发送binlog,从库中Io线程负责接收
5、从库的IO线程将接收的binlog写入到relay log中
6、从库的SQL线程读取relay log同步数据本地（即执行一遍SQL）

总结：MySQL 主从复制是依赖于 binlog 。另外，常见的一些同步 MySQL 数据到其他数据源的工具（比如 canal）的底层一般也是依赖 binlog 。

分库分表

读写分离主要应对的是数据库读并发，但没有解决数据库存储问题，因此为了解决一张表数据量过大的存储压力，引入了分库分布表

分库就是将数据库中的数据分散到不同的数据库上，如将数据库中的用户表和用户订单表分别放在两个不同的数据库或者是由于用户表数据量太大，对用户表进行了水平切分，将切分后的2张用户表分别放在两个不同的数据库

使用场景

单表数据达到千万级别以上，数据库读写速度比较缓慢（分表）
数据库中数据占用的空间越来越大，备份时间越来越长（分库）
应用并发量太大（分库）

分库分表的问题

1、join操作：同一个数据库中表分布在不同数据库中，导致无法使用join，此时需要手动进行数据封装,在一个数据库中查询到一个数据后，再根据做个数据去另一个数据库找对应的数据
2、事务问题：同一个数据库中的表分布在了不同的数据库中，若单个操作设计多个数据库，那么数据库自带的事务便无法满足要求
3、分布式id:分库后，数据库的自增主键无法保证生成的主键唯一，需要引入分布式id

锁

分为全局锁、表级锁和行锁

全局锁

Flush tables with read lock

执行此命令会使整个库处于只读状态，可用于作全库的逻辑备份即备份过程中整个库完全处于只读状态

当在主库上备份，则备份期间不能执行更新；在从库上备份，备份期间不能执行主库同步的binlog导致主从延迟

表级锁

表级锁分为表锁和元数据锁（MDL）

lock tables 表名 read/write

表锁会禁止所有线程操作

MDL在访问一个表时会自动加上，当一个表做增删改查时加MDL读锁，当对表结构变更时加DML写锁；因此长事务会一直占着MDL锁，

• 读锁之间不互斥即可以有多个线程同时对一张表增删改查
• 读写锁之间、写锁之间互斥，保证变更表结构操作安全性

参考链接：https://www.zhihu.com/search?type=content&q=Mysql%E7%B4%A2%E5%BC%95
https://xiaozhuanlan.com/topic/4912306758#sectionmysql-2
参考自《JavaGuide面试突击版》