title
第八篇：MySQL ！索引、SQL调优、事务、B+树、分

MySQL 那点破事！索引、SQL调优、事务、B+树、分

作者：Tom哥
公众号：微观技术
博客：https://offercome.cn
人生理念：知道的越多，不知道的越多，努力去学

数据库三大范式？

答案

第一范式：数据库中的字段具有原子性，不可再拆分，并且是单一职责
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。例如订单表里，存储了商品信息（商品价格、商品类型），那就需要把商品ID和订单ID作为联合主键，才满足第二范式。
第三范式：建立在第一，第二范式的基础上，确保每列都和主键列直接相关，而不是间接相关不存在其他表的非主键信息

但是在我们的日常开发当中，并不是所有的表一定要满足三大范式，有时候冗余几个字段可以少关联几张表，带来的查询效率的提升有可能是质变的

什么是内连接、左连接、右连接？

答案：
MySQL 的连接分为内连接和外连接。外连接通常有左连接和右连接。

inner join ：内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
left join ：两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
right join ：两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

UNION 与 UNION ALL的区别？

答案：

UNION ALL，不会合并重复的记录行
效率 UNION ALL 高于 UNION

MyISAM 和 InnoDB 的区别？

答案：
1. 存储结构：每个MyISAM在磁盘上存储成三个文件；InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
2. 事务支持：MyISAM不提供事务支持；InnoDB提供事务支持事务，具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全特性。
3 最小锁粒度：MyISAM只支持表级锁，更新时会锁住整张表，导致其它查询和更新都会被阻塞。InnoDB支持行级锁。
4. 索引类型：MyISAM的索引为聚簇索引，数据结构是B树；InnoDB的索引是非聚簇索引，数据结构是B+树。
5. 主键必需：MyISAM允许没有任何索引和主键的表存在；InnoDB如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
6. 表的具体行数：MyISAM保存了表的总行数，如果select count() from table;会直接取出该值; InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，但是在加了where条件后，MyISAM和InnoDB处理的方式都一样。
7. 外键支持：MyISAM不支持外键；InnoDB支持外键

SQL 约束有哪几种？

答案：

NOT NULL：用于控制字段的内容一定不能为空（NULL）。
UNIQUE：控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY：也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY：用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK：用于控制字段的值范围

varchar 和 char 有什么区别？

答案：
char：

char表示定长字符串，长度是固定的；
如果插入数据的长度小于char的固定长度时，则用空格填充；
因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
对于char来说，最多能存放的字符个数为255，和编码无关

varchar：

varchar表示可变长字符串，长度是可变的；
插入的数据是多长，就按照多长来存储；
varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
对于varchar来说，最多能存放的字符个数为65532

日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些

blob 和 text 有什么区别？

答案：

blob：用于存储二进制数据，而text用于存储大字符串。
blob：没有字符集，text有一个字符集，并且根据字符集的校对规则对值进行排序和比较

DATETIME 和 TIMESTAMP 的区别？

答案：
相同点：

两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS
两个数据类型都包含「日期」和「时间」部分。
两个数据类型都可以存储微秒的小数秒（秒后6位小数秒）

区别：

日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC 到 2038-01-09 03:14:07.999999 UTC
存储空间：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

锁类型有哪些？

答案：
1、基于锁的属性分类

独占锁：又称排它锁、X锁、写锁。它会阻塞其他的写锁和读锁。只要有事务对数据上加了独占锁，其他事务就不能再加其他任何锁了，只有获取排他锁的事务是可以对数据进行读取和修改。
共享锁：又称读锁、S锁。S锁与S锁兼容，可以同时放置。通过lock in share mode实现，这时候只能读不能写。

2、基于锁的粒度分类

行锁(Record Lock)：只锁记录，特定几行记录。
间隙锁(Gap Lock)：只锁间隙，前开后开区间(a,b)，对索引的间隙加锁，防止其他事务插入数据。
临键锁(Next-Key Lock)：同时锁住记录和间隙，前开后闭区间(a,b]。
插入意图锁(Insert Intention Lock)：插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。

⾏锁⼜可以分为乐观锁和悲观锁，悲观锁可以通过for update实现；乐观锁则通过版本号实现。

InnoDB 中的行锁实现?

答案：
1、Record Lock 记录锁
记录锁就是直接锁定某行记录。当我们使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时，此时就会直接将这条记录锁定。例如select * from t where id =6 for update;就会将id=6的记录锁定。
2、Gap Lock 间隙锁
间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填入数据的部分，是一个左开右开空间。
间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就会将(1,6)区间锁定。
3、Next-key Lock 临键锁
临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。
临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。
当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。注意，临键锁锁住的区间会包含最后一个record的右边的临键区间。例如select * from t where id > 5 and id <= 7 for update;会锁住(4,7]、(7,+∞)。mysql默认行锁类型就是临键锁(Next-Key Locks)。当使用唯一性索引，等值查询匹配到一条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化成间隙锁。

间隙锁(Gap Locks)和临键锁(Next-Key Locks)都是用来解决幻读问题的。在已提交读（READ COMMITTED）隔离级别下，间隙锁(Gap Locks)和临键锁(Next-Key Locks)都会失效！

上面是行锁的三种实现算法，除此之外，在行上还存在插入意向锁。
4、Insert Intention Lock 插入意向锁
一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了意向锁，如果有的话，插入操作需要等待，直到拥有 gap锁的那个事务提交。
但是事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。这种类型的锁命名为 Insert Intention Locks ，也就是插入意向锁。
假如我们有个T1事务，给(1,6)区间加上了意向锁，现在有个T2事务，要插入一个数据，id为4，它会获取一个（1,6）区间的插入意向锁，又有个T3事务，想要插入一个数据，id为3，它也会获取一个（1,6）区间的插入意向锁，但是，这两个插入意向锁锁不会互斥。

什么是间隙锁？

答案：
间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

当我们用范围条件而不是相等条件索引数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的 Next-Key 锁

产生间隙锁的条件：RR事务隔离级别下

举个列子：
假如现在user表有⼏条记录

当我们执⾏：

begin;
    select * from user where age=20 for update;
    
begin;
    insert into user(age) values(10); #成功
    insert into user(age) values(11); #失败
    insert into user(age) values(20); #失败
    insert into user(age) values(21); #失败
    insert into user(age) values(30); #失败

只有10可以插⼊成功，那么因为表的间隙mysql⾃动帮我们⽣成了区间(左开右闭)

(negative infinity，10],(10,20],(20,30],(30,positive infinity)

由于20 存在记录，所以(10,20]，(20,30]区间都被锁定了⽆法插⼊、删除。

如果查询21呢？
由于 21 不存在，就会根据21定位到(20,30)的区间(都是开区间)。 :::info 注意：唯⼀索引，查询条件命中了具体的行记录，只会加行锁，不会加间隙锁。如果是普通索引，查询条件命中了具体的行记录，会加间隙锁。 ::: 参考地址：https://zhuanlan.zhihu.com/p/48269420

如何避免死锁？

答案：
死锁的四个必要条件：1、互斥 2、请求与保持 3、环路等待 4、不可剥夺。

合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
调整业务逻辑 SQL 执行顺序，避免 update/delete 长时间持有锁的 SQL 在事务前面。
避免大事务，将大事务拆成多个小事务
以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
尽量用主键/索引去查找记录
优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，避免多个表join，将复杂 SQL 分解为多个简单的 SQL。

如何排查死锁问题？

答案：

查看死锁日志， show engine innodb status;
找出死锁 sql
分析 sql 加锁情况
模拟死锁案发
分析死锁日志
分析死锁结果

索引为什么采用B+树，而不用 B- 树，红黑树？

答案：
提升查询速度，首先要减少磁盘IO次数，也就是要降低树的高度。

平衡二叉树、红黑树，都属于二叉树。时间复杂度为O(n)，当表的数据量上千万时，树的深度很深，mysql读取时消耗大量 IO。另外，InnoDB引擎采用页为单位读取，每个节点一页，但是二叉树每个节点储存一个关键词，导致空间浪费。
B-树，非叶子节点也存储数据，占用较多空间，导致每个节点的指针少很多，无形增加了树的深度。
B+树，数据都存储在叶子节点，非叶子节点只存储健值+指针，索引树更加扁平，三层深度可以支持千万级表存储。同时叶子节点之间通过链表关联，范围查找更快。
更多内容，参考 mysql 一棵 B+ 树能存多少条数据？

Hash 索引和 B+ 树索引区别是什么？

答案：

B+ 树可以进行范围查询，Hash 索引不能。
B+ 树支持联合索引的最左侧原则，Hash 索引不支持。
B+ 树支持 order by 排序，Hash 索引不支持。
Hash 索引在等值查询上比 B+ 树效率更高。
B+ 树使用 like 进行模糊查询的时候，like 后面（比如 % 开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

索引的优缺点？

答案：
1、优点

大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

2、缺点

时间方面：创建索引和维护索引要耗费时间，对表中的数据增加、删除和修改，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

索引类型有哪些？

答案：

普通索引：一个索引只包含一个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
聚簇索引：也称为主键索引，是一种数据存储方式。B+Tree结构，非叶子节点包含健值和指针，叶子节点包含索引列和行数据。一张表只能有一个聚簇索引。
非聚簇索引：不是聚簇索引，就是非聚簇索引。叶子节点只是存索引列和主键id。如果sql还要返回除了索引列的其他字段信息，需要回表，第一次索引一般是顺序IO，回表的操作属于随机IO。回表的次数越多，性能越差。此时我们推荐覆盖索引

聚簇索引与非聚簇索引区别？

答案：
在 InnoDB 里，索引 B+ Tree的叶子节点存储了整行数据，叫做主键索引，也称为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。
而索引B+ Tree的叶子节点存储了主键的值，叫做非主键索引，也称为非聚簇索引、二级索引。
聚簇索引与非聚簇索引的区别：

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键
对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描。
通常情况下，主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可
注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

非聚簇索引一定回表查询吗？

答案：
不一定，这涉及到查询语句所要求的字段是否全部命中索引。
如果全部命中了索引，那么就不必再进行回表查询。一个索引包含所有需要查询的字段值，被称之为"覆盖索引"。

普通索引和唯一索引该怎么选择?

答案：

查询
- 当普通索引为条件时查询到数据会一直扫描，直到碰到下一个记录不满足条件为止
- 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。 :::info 但是这个造成的性能的差异是很小的，因为mysql每一次从磁盘上加载一个页的数据大小是16kb，在内存中查询数据是很快的，所有不会产生很大的数据的差异。 :::

什么是覆盖索引和回表？

答案：
1、覆盖索引，指的是在一次查询中，一个索引包含所有需要查询的字段的值，可能是返回值或where条件

select buyer_id from order where money>100

假如我们创建了一个(money，buyer_id) 的联合索引，索引的叶子节点包含了buyer_id 信息，则不会再回表查询。
2、回表，指查询时一些字段值拿不到，需要到主键索引B+树再查一次。

什么是索引下推优化？

答案：
索引条件下推优化（Index Condition Pushdown (ICP) ）是 MySQL 5.6 添加的，用于优化数据查询。

不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL Server，MySQL Server进行过滤条件的判断。
当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合MySQL Server传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于name使用了范围查询，根据最左匹配原则：
不使用 ICP，引擎层查找到name like '张%'的数据，再由 Server 层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age。
但是，使用了索引下推优化，把 where 条件放到引擎层执行，直接根据name like '张%' and age=10的条件进行过滤，减少了回表的次数。 :::info 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。 :::

最左前缀原则？

答案：
即最左优先，在检索数据时从联合索引的最左边开始匹配，直到遇到范围查询（如：> 、< 、between、like等）
例子：where a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)组合索引，d是用不到索引的；如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

如何判断SQL语句有没有用到索引？

答案：
通过explain 命令

id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id ，如explain select * from s1 where id = (select id from s1 where name = 'egon1'); 第一个select的id是1，第二个select的id是 2。有时候会出现两个select，但是id却都是1，这是因为优化器把子查询变成了连接查询。
select_type：select关键字对应的那个查询的类型，如：SIMPLE、PRIMARY、SUBQUERY、DEPENDENT、SNION
table：查询对应的表名
type： type 字段比较重要，它提供了判断查询是否高效的重要依据依据。通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等。如const (主键索引或者唯一二级索引进行等值匹配的情况下)，ref(普通的二级索引列与常量进行等值匹配)，index(扫描全表索引的覆盖索引) 通常来说, 不同的 type 类型的性能如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描，相同查询条件下，速度最慢。而 index 类型的查询虽然不是全表扫描，但是它扫描了所有的索引，因此比 ALL 类型的稍快.
possible_key：查询中可能用到的索引 (可以把用不到的删掉，降低优化器的优化时间) 。
key：当前查询真正使用到的索引。
filtered：查询器预测满足下一次查询条件的百分比。
rows ：估算 SQL 要查找到结果集需要扫描读取的数据行数， rows 越少越好。
extra：表示额外信息，如：Using where、Start temporary、End temporary、Using temporary等。

更多内容：MySQL的explain，你真的会用吗？

线上SQL的调优经验？

答案：

1、slow_query_log 日志中收集到的慢 SQL ，结合 explain 分析是否命中索引。
2、减少索引扫描行数，有针对性的优化慢 SQL。
3、建立联合索引，由于联合索引的每个叶子节点包含检索字段的信息，按最左前缀原则匹配后，再按其它条件过滤，减少回表的数据量。
4、还可以使用虚拟列和联合索引来提升复杂查询的执行效率。

如何做索引优化？

答案：

分页优化。比如电梯直达，limit 100000,10 先查找起始的主键id，再通过id>#{value}往后取10条
尽量使用覆盖索引，索引的叶节点中已经包含要查询的字段，减少回表查询
SQL优化（索引优化、小表驱动大表、虚拟列、适当增加冗余字段减少连表查询、联合索引、排序优化、慢日志 Explain 分析执行计划）。
设计优化（避免使用NULL、用简单数据类型如int、减少 text 类型）。
1. 如果数据量太大，可以采用分库分表
硬件优化（使用SSD 减少 I/O 时间、足够大的网络带宽、尽量大的内存）
可以使用force index()，防止优化器选错索引

索引失效的场景?

答案：

查询条件包含 or，可能导致索引失效
如果字段类型是字符串，where时一定用引号括起来，否则会因为隐式类型转换，索引失效
like 通配符可能导致索引失效。
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
在索引列上使用mysql的内置函数，索引失效。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
索引字段上使用is null， is not null，可能导致索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
MySQL优化器估计使用全表扫描要比使用索引快，则不使用索引

SQL 慢查询原因？

答案：

没有用到索引
- 比如函数导致的索引失效，或者本身就没有加索引
表数据量太大
- 考虑分库分表
优化器选错了索引
- 考虑使用 force index 强制走索引

SQL 查询偶尔慢会是什么原因?

答案：

1. 数据库在刷新脏页
- 比如 redo log 写满了，内存不够用了释放内存如果是脏页也需要刷，mysql 正常空闲状态刷脏页
2. 没有拿到锁

为什么推荐使用自增id 作为主键？

答案：
1、自增id是连续的，插入过程也是顺序的，总是插入在最后，减少了页分裂，有效减少数据的移动。所以尽量不要使用字符串（如：UUID）作为主键。如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动，导致页分裂维护成本较高
2、普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会导致普通索引的存储空间较大

事务特性？

答案：ACID。

原子性。一个事务中的操作要么全部成功，要么全部失败。
持久性。永久保存在数据库中。
一致性。事务前后数据的完整性必须保持一致，总是从一个一致性的状态转换到另一个一致性的状态
隔离性。当多个事务同时触发时，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

如何实现分布式事务？

答案：

本地消息表
流水任务，最终一致性，前提是接口要支持幂等性
事务消息
二阶段提交
三阶段提交
TCC
最大努力通知
Seata 框架
更多内容，参考如何解决分布式事务

事务隔离级别？

答案：

读未提交: 即能够读取到没有被提交的数据
读提交: 即能够读取到那些已经提交的数据
可重复读: 可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的
可串行化: 最高事务隔离级别，不管多少事务，都是依次按序一个一个执行

脏读
- 脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读
不可重复读
- 对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的。
幻读
- 幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的这就叫幻读

为什么不要使用长事务?

答案：

并发情况下，数据库连接池容易被撑爆
容易造成大量的阻塞和锁超时
- 长事务还占用锁资源，也可能拖垮整个库
执行时间长，容易造成主从延迟
回滚所需要的时间比较长
- 事务越长整个时间段内的事务也就越多
undo log 日志越来越大
- 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致占用大量存储空间。

主从复制的流程？

答案：

master主库，有数据更新，将此次更新的事件类型写入到主库的binlog文件中
主库会创建log dump 线程通知slave有数据更新
slave，向master节点的 log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的Relay log 中继日志中
slave 再开启一个SQL 线程读取Relay log事件，并在本地执行redo操作。将发生在主库的事件在本地重新执行一遍，从而保证主从数据同步

什么是主从延迟？

答案：
指一个写入SQL操作在主库执行完后，将数据完整同步到从库会有一个时间差，称之为主从延迟。计算公式：

主库生成一条写入SQL的binlog，里面会有一个时间字段，记录写入的时间戳 t1
binlog 同步到从库后，一旦开始执行，取当前时间 t2
t2-t1，就是延迟时间

注意：不同服务器要保持时钟一致

主从延迟排查方法？

答案：
通过 show slave status 命令输出的Seconds_Behind_Master参数的值来判断

为零：表示主从复制良好
正值：表示主从已经出现延时，数字越大，表示从库延迟越严重

主从延迟如何解决？

答案：

看业务的接受程度。对于一些对延迟很敏感的业务，建议强制走主库查询
可以考虑引入缓存，更新主库后同步写入缓存，保证缓存的及时性
提升从库的机器配置，提高从库 binlog 的同步效率
缩短主、从库的网络距离，减少 binlog 的网络传输时间
避免让数据库进行各种大量运算
一主多从，每个从库都启一个线程从主库同步 binlog，导致主库压力过大，可以采用canal 增量订阅&消费组件，缓解主库压力。
因为数据库必须要等到事务完成之后才会写入binlog，所以减少大事务的执行，尽量控制数量，分批执行。
5.6 版本之前，从库是单线程复制，当遇到执行慢的sql时，就会阻塞后面的同步。5.7 版本后支持多线程复制，可以在从服务上设置slave_parallel_workers为一个大于0的数，然后把slave_parallel_type参数设置为LOGICAL_CLOCK
为从库增加浮动IP，并通过脚本检测从库的延迟，延迟大于指定阈值时，将浮动IP切换至Master库，追平后再切换回从库。

如果数据量太大怎么办？

答案：
mysql表的数据量一般控制在千万级别，如果再大的话，就要考虑分库分表。除了分表外，列举了面对海量数据业务的一些常见优化手段

缓存加速
读写分离
垂直拆分
分库分表
冷热数据分离
ES助力复杂搜索
NoSQL
NewSQL
更多内容，参考海量数据业务有哪些优化手段？

分表后ID如何保证全局唯一呢？

答案：
分库分表后，多张表共用一套全局id，原来单表主键自增方式满足不了要求。我们需要重新设计一套id生成器。特点：全局唯一、高性能、高可用、方便接入。

UUID
数据库自增ID
数据库的号段模式，每个业务定义起始值、步长，一次拉取多个id号码
基于Redis，通过incr命令实现ID的原子性自增。
雪花算法（Snowflake）
市面的一些开源框架，如：百度（uid-generator），美团（Leaf），滴滴（Tinyid）等

分表后可能遇到的哪些问题？

答案：
分表后，与单表的最大区别是有分表键sharding_key，用来路由具体的物理表，以电商为例，有买家和卖家两个维度，以buyer_id路由，无法满足卖家的需求，反之同样道理。如何解决？

分买家库和卖家库，将买家库做为写库，保存完整的数据关系。同时将数据异构同步一份到卖家库，卖家库可以只存储seller_id，order_id，buyer_id 等几个简单关系字段即可，以seller_id作为分表键
多线程扫描，分段查找，然后再聚合结果
另外也可以存到ES中，支持多维度复杂搜索

一条查询语句是怎么执行的?

答案：

通过连接器跟客户端建立连接
通过查询缓存查询之前是否有查询过该 sql
- 有则直接返回结果
- 没有则执行第三步
通过分析器分析该 sql 的语义是否正确，包括格式，表等等
通过优化器优化该语句，比如选择索引，join 表的连接顺序
验证权限，验证是否有该表的查询权限
- 没有则返回无权限的错误
- 有则执行第六步
通过执行器调用存储引擎执行该 sql，然后返回执行结果

一条更新语句是怎么执行的?

答案：
用以下语句来举例，c 字段无索引，id 为主键索引

update T set c=c+1 where id=2;

执行器先找引擎取 id=2 这一行。id 是主键，引擎直接用树搜索找到这一行
- 如果 id=2 这一行所在的数据页本来就在内存中，就直接返回给执行器
- 不在内存中，需要先从磁盘读入内存，然后再返回
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据
引擎将这行新数据更新到内存中，同时将这个更新操作**记录到 redo log 里面（prepare 状态），**然后告知执行器执行完成了，随时可以提交事务
执行器生成这个操作的 binlog，并把 binlog 写入磁盘
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

Innodb 事务为什么要两阶段提交?

答案

**情况一：**一阶段提交之后崩溃了，即写入 redo log，处于 prepare 状态的时候崩溃了，此时：
由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。
**情况二：**假设写完 binlog 之后崩溃了，此时：
redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时候，首先检查 binlog 中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。
**情况三：**假设 redolog 处于 commit 状态的时候崩溃了，那么重启后的处理方案同情况二。
由此可见，两阶段提交能够确保数据的一致性。

WAl 是什么，有什么优点?

答案：
WAL 就是 Write-Ahead Logging，所有的修改都先被写入到日志中，然后再写磁盘，用于保证数据操作的原子性和持久性。
优势:

读和写可以完全地并发执行，不会互相阻塞
先写入 log 中，磁盘写入从随机写变为顺序写，降低了 client 端的延迟。由于顺序写入大概率是在一个磁盘块内，这样产生的 io 次数也大大降低
写入日志当数据库崩溃的时候，可以使用日志来恢复磁盘数据

redo log 是做什么的?

答案：
redolog 是 InnoDB 存储引擎所特有的一种日志，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
可以做数据恢复并且提供 crash-safe 能力
当有增删改相关的操作时，会先记录到 Innodb 中，并修改缓存页中的数据，等到 mysql 闲下来的时候才会真正的将 redolog 中的数据写入到磁盘当中。

redo log 怎么记录日志?

答案：

InnoDB 的 redo log 是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。
所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生内存抖动现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷盘了。

undo log 是做什么的?

答案：
undo log 是 InnoDB 存储引擎的日志，用于保证数据的原子性，保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修改之前的数据，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）。
主要作用

事务回滚
实现多版本控制 (MVCC)

binlog 是做什么的?

答案：
binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。
MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。
binlog 有三种格式，各有优缺点：

statement：基于 SQL 语句的模式，某些语句和函数如 UUID、 LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。
row：基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。
mixed：混合模式，根据语句来选用是 statement 还是 row 模式

redo log 和 binlog 的区别是什么?

答案：

redolog 是 Innodb 独有的日志，而 binlog 是 server 层的，所有的存储引擎都有使用到
redolog 记录了具体的数值，对某个页做了什么修改，binlog 记录的操作内容
binlog 大小达到上限或者 flush log 会生成一个新的文件，而 redolog 有固定大小只能循环利用
binlog 日志没有 crash-safe 的能力，只能用于归档。而 redo log 有 crash-safe 能力。

relaylog 是做什么的?

答案：
relaylog 是中继日志，在主从同步的时候使用到，它是一个临时的日志文件，用于存储从master节点同步过来的binlog日志内容。

master 主节点的 binlog 传到 slave 从节点后，被写入 relay log 里，从节点的 SQL 线程从 relay log 里读取日志然后应用到 slave 从节点本地，从而使从服务器和主服务器的数据保持一致。

什么是 MVCC ?

答案：
多版本并发控制，是现代数据库(包括 MySQL、Oracle、PostgreSQL 等)引擎实现中常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能。
在 MVCC 协议下，每个读操作会看到一个一致性的快照，这个快照是基于整个库的，并且可以实现非阻塞的读，用于支持读提交和可重复读隔离级别的实现。
MVCC 允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务 ID，在同一个时间点，不同的事务看到的数据是不同的，这个修改的数据是记录在 undo log 中。

MVCC 实现原理?

答案：对于 InnoDB ，聚簇索引记录中包含 3 个隐藏的列：

ROW ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树。
事务 ID：记录最后一次修改该记录的事务 ID。
回滚指针：指向这条记录的上一个版本。

如图，首先 insert 语句向表 t1 中插入了一条数据，a 字段为 1，b 字段为 1， ROW ID 也为 1 ，事务 ID假设为 1，回滚指针假设为 null。当执行 update t1 set b=666 where a=1 时，大致步骤如下：

数据库会先对满足 a=1 的行加排他锁；
然后将原记录复制到 undo 表空间中；
修改 b 字段的值为 666，修改事务 ID 为 2；
并通过隐藏的回滚指针指向 undo log 中的历史记录；
事务提交，释放前面对满足 a=1 的行所加的排他锁。

MVCC 实现原理：
InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。
MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性。通过 MVCC，保证了事务 ACID 中的 I（隔离性）特性。

删除表数据，表的大小却没有变?

答案：
使用 delete 删除数据时，对应的数据行并没有真正的删除，是逻辑删除，InnoDB 仅仅是将其标记成可复用的状态，所以表空间不会变小

buffer pool 是做什么的?

答案：
buffer pool 是一块内存区域，为了提高数据库的性能，当数据库操作数据的时候，把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool 里面的数据，数据库的增删改查都是在 buffer pool 上进行
buffer pool 里面缓存的数据内容也是一个个数据页。
其中有三大双向链表:

free 链表
- 用于帮助我们找到空闲的缓存页
flush 链表
- 用于找到脏缓存页，也就是需要刷盘的缓存页
lru 链表
- 用来淘汰不常被访问的缓存页，分为热数据区和冷数据区，冷数据区主要存放那些不常被用到的数据

MySQL怎么存储emoji😊?

答案：
MySQL可以直接使用字符串存储emoji。
但是需要注意的，utf8 编码是不行的，MySQL中的utf8是阉割版的 utf8，它最多只用 3 个字节存储字符，所以存储不了表情。那该怎么办？
需要使用utf8mb4编码。

drop、delete 与 truncate 的区别？

答案：
三者都表示删除，但是三者有一些差别：

|

| delete | truncate | drop | | --- | --- | --- | --- | | 类型 | 属于DML | 属于DDL | 属于DDL | | 回滚 | 可回滚 | 不可回滚 | 不可回滚 | | 删除内容 | 表结构还在，删除表的全部或者一部分数据行 | 表结构还在，删除表中的所有数据 | 从数据库中删除表，所有数据行，索引和权限也会被删除 | | 删除速度 | 删除速度慢，需要逐行删除 | 删除速度快 | 删除速度最快 |

在不再需要一张表的时候，用drop；在想删除部分数据的时候，用delete；在保留表而删除所有数据的时候用truncate。

一棵B+树能存储多少条数据？

答案：
假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的单元(键值+指针)，代表有 1170 个指针。
假如一行数据的大小约为1K字节，那么按 16K / 1K = 16，可以计算出一页大约能存放16条数据
树深度为 2 的时候，能存放 1170 * 16 = 18720 条这样的数据记录。
同理，高度为3的B+树可以存放的行数 = 1170 * 1170 * 16 = 21902400
在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。
所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

数据库 cpu 飙升的话，如何排查？

答案：

使用 top 命令观察，确定是 MySQL 导致还是其他原因。
如果是 MySQL 导致的，show processlist，查看 session 情况，确定是不是有消耗资源的 sql 在运行。
找出消耗高的 sql，看看执行计划是否准确，索引是否缺失，数据量是否太大。

如何处理：

kill 掉这些线程 (同时观察 cpu 使用率是否下降)
进行相应的调整 (比如说加索引、改 sql、改内存参数)
重新跑这些 SQL。

其他情况：
也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的新 session 连接进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

Files

MySQL 那点破事.md

Latest commit

History