Checklist系列:MySQL自检五十五问，万字整理，推荐收藏

发布时间：2024年01月23日

🚀最近也打算整理一波已经学过的知识，名字已经想好了就叫《CheckList》系列，以后需要用到的时候也可以拿出来看。问题来源于网上常见的面试题，问题的答案多以官网为主，每个问题我都贴了链接，觉得我写的不清楚的地方可以点击链接查看原文。

另外如果有什么问题这上面没有，可以评论，私信，提交issue等等的各种方式，只要我看到都会观看的，满一定程度会出续集。

点赞👍收藏🌟支持一波呗~~~~~~

PS：提交issue地址：https://github.com/Nortyr/monk_note/issues

基础

1. 什么是MySQL？

MySQL是开源的关系型数据库，以下是MySQL的一些关键特性

关系型数据库：基于关系模型的数据库系统，行列结构
支持SQL：SQL是结构化查询语言(Structured Query Language)
开源：开源地址:https://github.com/mysql
支持事务：支持事务处理，保证了事务的原子性、一致性、隔离性和持久性（ACID）
主从复制：支持主从复制
支持多种存储引擎：MySQL支持多种存储引擎，例如InnoDB、MyISAM等。

2. 数据库三大范式

第一范式（1NF）：表中的字段有原子性，不可以拆分。每个字段的值都只能是单一值。
第二范式（2NF）：表中的属性都依赖于主键。
第三范式（3NF）：任何非主属性不依赖于其他非主属性

范式的目的是减少冗余，会造成一些问题

查询效率下降：增加关联查询和子查询，影响sql执行效率
更新复杂度和性能降低：维护多张表，还要考虑一定的关联关系。
所以，一般是反范式。

3. MySQL基础架构

精简版本：

官网版本：

官网链接：https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html

4. SQL语句的执行过程

大致情况如上图

与MySQL建立连接
检查是否开启缓存，如果开启并且命中直接返回
有分析器，进行词法分析，这一步分析是否合法
由优化器生成执行计划。这一步查看是否可以根据索引优化
由执行器执行SQL语句，交给存储引擎执行

5. CHAR 和 VARCHAR 的区别是什么？

CHAR:
- 固定长度不足由空格填充
- 范围：0~255
- 可以减少磁盘碎片
- 存取快
VHARCHAR:
- 可变长度，会花1~2字节存储实际长度
- 范围：0~65532
- 存取慢

官网链接：https://dev.mysql.com/doc/refman/8.0/en/char.html

6. BLOB和TEXT有什么区别？

BLOB:
- 二进制存储
- 没有字符集
TEXT:
- 字符存储
- 有字符集

官网链接：https://dev.mysql.com/doc/refman/8.0/en/blob.html

7. DATETIME 和 TIMESTAMP 的区别是什么？

DATETIME:
- 范围：1000-01-01 00:00:00~9999-12-31 23:59:59
- 时间格式：yyyy-MM-dd HH:mm:ss
- 时区：存储当地时区
TIMESTAMP:
- 范围：1970-01-01 00:00:01UTC~2038-01-19 03:14:07UTC
- 时间格式：yyyy-MM-dd HH:mm:ss
- 时区：存储UTC（世界时间）

官网链接：https://dev.mysql.com/doc/refman/8.0/en/datetime.html

8. count(1)、count(*) 与 count(列名) 的区别？

count(*):
- 检索到null任然会计算在内
- 8.0.13以后有单独优化
count(expr):
- 例如count(列名)在内，不会计算null
count(1):
- 等同于count(*)没有性能差异
- 对于MyISAM有差异

官网链接：https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count

9. 常见的存储引擎有哪些？MySQL默认使用的是什么

存储引擎：MySQL定义了对于数据CRUD操作标准规范（接口）。存储引擎就是对应的实现，负责与文件系统进行交互
比较常见的存储引擎

	InnoDB	MyISAM
事务	Yes	NO
MVCC	Yes	NO
外键	Yes	No
聚簇索引	Yes	no
锁最小粒度	行锁	表锁
清空方式	逐行	重建

官网链接：https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

10. MySQL自增主键用完了会怎么样？

当AUTO_INCREMENT整数列用完值时，得到的值就是最大的值，后续INSERT操作将返回重复键错误。

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

11 UNION与UNION ALL的区别？

union：将结果集合并到同一个结果集中，删除重复数据，显示写法为UNION DISTINCT
union all：不会删除重复数据

官网链接：https://dev.mysql.com/doc/refman/8.0/en/union.html

12. drop、delete与truncate的区别？

drop
- 属于DDL
- 会删除表级表相关的索引，触发器
delete
- 属于DML
- 可选子句中的条件WHERE标识要删除的行。如果没有WHERE 子句，则所有行都将被删除。
truncate
- 属于DDL
- 会删除并重新创建表，比删除行快

官网链接：

https://dev.mysql.com/doc/refman/8.0/en/drop-tablespace.html
https://dev.mysql.com/doc/refman/8.0/en/delete.html
https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html

事务

1. 什么是数据库事务

数据库事务：
- 数据库一个或者多个操作组成的逻辑单位
目的（解决的问题）：
- 失败了可以恢复到正常的状态，即使失败了仍可以恢复到一致性状态
- 多个client在并发访问到数据库的时候，针对多个client访问提供隔离避免彼此之间互相干扰

官网链接：https://zh.wikipedia.org/wiki/%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BA%8B%E5%8A%A1

2. acid特性？

ACID 模型是一组数据库设计原则。上面提到了事务的需要解决2个问题。而ACID模型是是解决这个问题的基本条件。InnoDB遵循ACID模型，因此数据不会被损坏，结果也不会因软件崩溃和硬件故障等异常情况而失真。

A(atomicity):事务中的所有操作要么全部成功完成，要么全部失败回滚，不会出现中间状态。
C(consistency):事务执行后，系统应保持一致状态。如果事务违反了数据库的完整性约束，它将被回滚，以确保系统的一致性。
I(isolation):隔离性确保多个并发事务之间互相不受影响，即一个事务的执行不应影响其他事务的执行。
D(durability):一旦事务提交，其结果应该是永久性的，即使在系统故障或重启后也能够保持。

官网链接：https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

3. 并发事务带来了哪些问题?

脏读：读到另一个事务未提交的数据
- 官网链接：https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_dirty_read
不可重复读：两次查询内，返回了不同的数据。主要指修改和删除。快照的版本不一致导致的
- 官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
幻读：特指第二次读取到第一次没有返回的行
- 官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html

4. 事务隔离级别

读未提交(READ UNCOMMITTED):事务可以看到其他事务“尚未提交”的修改。会有脏读的问题
读已提交(READ COMMITTED):每次读取会读取最新快照，会读取到其他事务已经提交的记录。可以避免脏读发生
可重复度(REPEATABLE READ)：第一次读取会生成快照，在不生成新的快照的前提下，多次读取是一致的
串行化(SERIALIZABLE)：Select隐式转化为SELECT … FOR SHARE
相当于使用锁来解决事务建的问题

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolevel_serializable

5. 不可重复读和幻读有什么区别？

不可重复读：当在事务过程中，一行被检索两次并且该行中的值在两次读取之间不同时，就会发生不可重复读取。
幻读：当在事务过程中执行两个相同的查询并且第二个查询返回的行集合与第一个查询不同时，就会发生幻读。

例子：

用户 A 运行相同的查询两次。
在这期间，用户 B 运行事务并提交。
不可重复读：用户A第二次查询到的A行的值不同。
幻读：查询中的所有行前后都有相同的值，但正在选择不同的行（因为 B 删除或插入了一些行）。示例：select sum(x) from table;如果已添加或删除行，即使受影响的行本身没有被更新，也会返回不同的结果。

参考链接：https://stackoverflow.com/questions/11043712/non-repeatable-read-vs-phantom-read

6. 不同隔离级别下可能会发生的问题？

隔离级别	脏读	不可重复读	幻读
读未提交(READ UNCOMMITTED)	?	?	?
读已提交(READ COMMITTED)	?	?	?
可重复度(REPEATABLE READ)	?	?	?
串行化(SERIALIZABLE)	?	?	?

7. MySql如何解决RR级别下的幻读

RR级别下解决了大部分的幻读的问题。但是还是会有幻读问题，下面是一点例子

锁解决幻读

事务A	事务B

通过加锁阻塞B事务，这样就肯定不会有幻读了

MVCC解决幻读

事务A	事务B

事务提交后查询：

MVCC中的幻读场景

事务A	事务B

PS:update test set name='kak' where id >120替换成锁也有一样的效果

原因
官网解释如下：

译文

快照适用于事务内的select语句，不一定适用于DML语句。

其他事务修改或删除并提交，其他事务的修改或删除可能影响到那些行，这些行对于该事务可见

上面
update test set name='kak' where id >120
和
update test set name='kak' where id <120 查询到的结果不同证明了这一点

锁和cud操作会生成最新的ReadView快照

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE 
UPDATE
DELETE
INSERT

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

8. 各个事务隔离级别都是如何实现读取的？

读未提交(READ UNCOMMITTED):直接读取最新数据
读已提交(READ COMMITTED):每次读取生成一个ReadView
可重复度(REPEATABLE READ)：第一次读取会生成ReadView
串行化(SERIALIZABLE)：读写锁的方式

9.MVCC是如何实现的？

MVCC(Multi Version Concurrency Control),多版本并发控制。由多个模块共同实现。

UNDOLOG：InnoDB使用UNDOLOG进行回滚，也使用这些日志构成版本链。

UNDOLOG(新增):仅在回滚时用，提交后可以立即丢弃。
UNDOLOG(修改):还会用于版本连，不存在事务使用到的时候才会丢弃

每一行记录中有DB_TRX_ID(事务id)，DB_ROLL_PTR(回滚指针)，DB_ROW_ID(记录id)
ReadView，下面贴了部分源码。主要是由几个字段来的，以下解释来自于注释

m_low_limit_id:读取不应看到任何 trx id >= m_low_limit_id 的值。换句话说，这就是“高水位线”。
m_up_limit_id: 读取应该看到 trx id <= m_up_limit_id 的值。换句话说，这就是低水位线”。
m_creator_trx_id：创建事务的事务id
m_ids：当前快照还活跃的读写事务
m_low_limit_no：小于这个值的undolog 可以不用再看了，他们已经可以被回收了

大致逻辑：
先简单的概述下这四个

m_low_limit_id：高水位，高于这个id的是看不见的
m_up_limit_id：低水位，低于这个id的当前事务是可以看见的
m_creator_trx_id：当前事务id
m_ids：活跃的读写事务id

接下来就是比较事务id了

DB_TRX_ID < m_up_limit_id 的可以直接看
DB_TRX_ID > m_low_limit_id 看不到，要按版本链找
m_up_limit_id < DB_TRX_ID < m_low_limit_id 要在 m_ids 逐一比较
3.1 如果不在，直接返回
3.2 如果在，按版本连向上找

下面画图举个栗子

这个是ReadView源码

private:
    // Disable copying
    ReadView(const ReadView&);
    ReadView& operator=(const ReadView&);

private:
    /** The read should not see any transaction with trx id >= this
    value. In other words, this is the "high water mark". */
    /** 读取不应看到任何 trx id >= m_low_limit_id 的值。换句话说，这就是“高水位线”。*/
    trx_id_t    m_low_limit_id;

    /** The read should see all trx ids which are strictly
    smaller (<) than this value.  In other words, this is the
    low water mark". */
    /** 读取应该看到 trx id <= m_up_limit_id 的值。换句话说，这就是低水位线”。*/
    trx_id_t    m_up_limit_id;

    /** trx id of creating transaction, set to TRX_ID_MAX for free
    views. */
    
    /** 创建事务的事务id，TRX_ID_MAX是空闲的视图 */
    trx_id_t    m_creator_trx_id;

    /** Set of RW transactions that was active when this snapshot
    was taken */
    /** 当前快照还活跃的读写事务  */
    ids_t       m_ids;

    /** The view does not need to see the undo logs for transactions
    whose transaction number is strictly smaller (<) than this value:
    they can be removed in purge if not needed by other views */
    /** 小于这个值的undolog 可以个栗子不用再看了，他们已经可以被回收了*/
    trx_id_t    m_low_limit_no;

    /** AC-NL-RO transaction view that has been "closed". */
    bool        m_closed;

    typedef UT_LIST_NODE_T(ReadView) node_t;

    /** List of read views in trx_sys */
    byte        pad1[64 - sizeof(node_t)];
    node_t      m_view_list;
};

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html

索引

1. MySQL中索引类型?

大类划分：
- 聚簇索引：id和数据在一起
- 非聚簇索引：id和数据不在一起
类型划分：
- 全文索引(FULLTEXT):针对于长文本的索引
- 普通索引(NORAML):最常用的就是这个
- 唯一索引(UNIQUE):不允许重复，有唯一性约束，可以有null。
- 空间索引(SPATIAL):针对于空间字段。
- 主键索引(PRIMARY):主键的索引，不能有null。
数据结构划分：
- B+树索引:多叉树，二分查找
- Hash索引:hash函数映射，等值过滤
其他:
- 前缀索引:创建索引的时候指定长度（仅限字符串）
- 组合索引:指定多列

官网链接：https://dev.mysql.com/doc/refman/8.0/en/create-index.html

2. 什么是聚簇索引什么是非聚簇索引?

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据行。
非聚簇索引：将数据存储与索引分开，叶子结点包指向数据行。
聚簇索引：

数据结构大致如图所示（实际还有槽（Solt）,不影响理解就没画）

非聚簇索引：

数据结构大致如图所示

3. 什么是回表?

上面应该看到了非举出索引的结构比如说

select id,name,work,create_time from fanhua where work= '至真园'

那就是搜work索引，查询到主键id=1。
由于需要4个字段，这里不满足，需要到聚簇索引中继续寻找
再聚簇索引中找到，再返回

4. 什么是索引覆盖、索引下推?

索引覆盖

select id,work from fanhua where work= '至真园'

这两个字段，work索引中都涵盖了，就不会回表了，直接返回。

索引下推(Index Condition Pushdown (ICP) )

索引下推是避免全表遍历的一种查询优化，由MySQL判断这些where条件，交给存储引擎判断，如果满足，直接筛选部分条件

explain 的时候 Extra列如果有Using index condition就代表使用了索引下推

索引下推的开关控制

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

官网链接:https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

5. 什么是最左前缀匹配？

假如有这个联合索引

ALTER TABLE `test`.`test` 
ADD INDEX `union_idx`(`work`, `create_time`);

那么他的结构如下

还记得前面说到b+树实际上是二分查找的一种吗

二分查找，最最最基本要求，是要能确定答案在你的左半边，还是在右半边，这类，连续的记录行，那么有序是一种相对简单高效的方式，这类多列索引，怎么确定顺序呢，就是按照你创建的方式进行排序（从左到右）

select work,create_time from test order by work asc,create_time asc

后面的列只是辅助最左列的排名，如果查询不带最左列，就无法使用这个联合索引。

一般生产中，捆绑了这两个条件查询的我们才创建联合索引

6. 为什么InnoDB使用B+树?

我感觉用一下的提问方式比较好一点

什么是B+树？为什么使用B+树而不使用其他数据结构？或者B+树相对于其他数据结构的优点是什么

7. 什么是B+树？

B+树
- 是对于B树的升级
- 是多叉排序树，每个节点可以有多个孩子
- 叶子节点存放数据
- 非叶子节点可以看成索引
- 二分查找

8. 什么是B树

B树
- 多叉排序树
- 叶子节点和非叶子节点都存放数据
- 二分查找

9. 为什么使用B+树而不使用其他数据结构？

非叶子节点存放索引，索引占用的空间小，可以指向更多的页，同等量的数据情况下，层级比B树更低，查询也就更快
叶子节点双向链表，更方便范围查询，由于Hash索引
数据都在叶子节点，插入删除操作的时候，IO操作更少

10. 索引失效的场景?

联合索引不满足最左匹配
索引列使用了运算或函数

like’%xxxx%’

涉及到类型转换

涉及到or操作

涉及到in操作

受到索引覆盖和命中数据量的影响

涉及到not in 操作

涉及到is not null操作

涉及到<> / != 操作

测试数据脚本

CREATE TABLE `monk` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`) USING BTREE,
  KEY `age_idx` (`age`) USING BTREE
) ENGINE=InnoDB  CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DELIMITER //
CREATE PROCEDURE insert_data()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 10000 DO
        INSERT INTO monk (name, age, email, city)
        VALUES (
            CONCAT('User', i),
            FLOOR(RAND() * 100) + 1,
            CONCAT('user', i, '@example.com'),
            'City'        
        );
        
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_data();

11. 索引是越多越好吗？

不是，索引也有很多弊端

占用存储空间
插入，更新，删除操作，索引也会更新维护，提高写入成本

12.讲讲执行计划，如何理解其中各个字段的含义？

id:序号
select_type:类型
- SIMPLE：简单查询
- PRIMARY：最外层Select
- UNION:UNION 中的第二个或后面的 SELECT 语句
- DEPENDENT UNION:UNION 中的第二个或后面的 SELECT 语句，依赖于外部查询
- UNION RESULT:UNION结果
- SUBQUERY:子查询中的第一个 SELECT
- DEPENDENT SUBQUERY:子查询中的第一个 SELECT，依赖于外部查询
- DERIVED:派生表(FROM中的子查询)
- DEPENDENT DERIVED:派生表依赖于另一个表
- MATERIALIZED:物化子查询
- UNCACHEABLE SUBQUERY:无法缓存结果且必须针对外部查询的每一行重新计算结果的子查询
- UNCACHEABLE UNION:UNION 中属于不可缓存子查询的第二个或后续选择（请参阅 UNCACHEABLE SUBQUERY）
table:表
partitions:查询将匹配记录的分区。
type:连接类型
- system:该表只有一行
- const:该表最多有一个匹配行，该行在查询开始时读取。
- eq_ref:对于来自前面表的每一行，在当前表中只能找到一行。
- ref:对于来自前面表的每一行，在此表的索引中可以匹配到多行。
- fulltext:全文索引
- ref_or_null:和null类似，增加了null值的比较。
```
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
```
- index_merge:索引合并优化。使用了两个以上的索引，最后取交集或者并集
- unique_subquery:unique_subquery只是一个索引查找函数，完全替代子查询以提高效率。
```
value IN (SELECT primary_key FROM single_table WHERE some_expr)
```
- index_subquery:此连接类型类似于 unique_subquery。它取代了 IN 子查询，但它适用于以下形式的子查询中的非唯一索引
```
value IN (SELECT key_column FROM single_table WHERE some_expr)
```
- range: 索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
```
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
```
- index:扫描索引树，出现这个只有两种情况
  - 索引覆盖
  - 全表扫描，使用索引进行读取，以按照索引的顺序查找数据行。在查询执行计划的"Extra"列中，没有显示"Uses index"。
possible_keys:mysql可以从中选择的索引（选择项）
key:MySQL实际决定使用的索引
key_len:索引长度
ref:显示哪些列或常量与键列中指定的索引进行比较，以从表中选择行。
rows:MySQL认为要查询的行数
filtered:过滤列指示按表条件过滤的表行的估计百分比。
Extra:此列包含有关 MySQL 如何解析查询的附加信息。(全部的可以点击官网链接，这里就列一些比较常见的)
- Using filesort:MySQL 必须执行额外的操作来找出如何按排序顺序检索行。
- Using index:覆盖索引
- Using index condition:索引下推
- Using index for group-by:使用索引优化Group by或distinct且无需额外磁盘访问
- Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join) (JSON property: using_join_buffer):多表连接使用缓冲区优化
  - Block Nested Loop:块嵌套循环，优化表连接
  - Batched Key Access:批量键访问算法进行连接
  - hash join：hash连接算法进行连接。>=8.0.18版本可用
- Using where:表示使用了where子句进行过滤或筛选
- Using intersect(…)Using union(…)Using sort_union(…):索引合并

官网链接：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

13. 慢SQL如何进行优化

我个人日常大致是按照以下九个步骤

查看是否是深分页。
- 避免深分页
- 如果主键单调增，可以带上主键
- 如果使用的mybatis,分页语句单独写
如果是统计类，是否可以存放进缓存。
- 比如读取前一天的数据，不实时查询
explain：查看排查索引失效/走错索引的情况。
- 主要看和预期索引是否一致即可
分析索引失效情况
- 以上情况排查（一般情况不存在）
分析索引走错情况
- 最多的情况就是这个使用,使用use index,ignore index,force index
- union优化，比较骚，适当用
- 适当的sql优化
驱动表，被驱动表的数据量分析
- 可以简单的理解为双层for循环，最外层越小越好
排查各个条件对应索引的数据区分度
分析数据命中情况
- 根据业务来
无法优化情况，尝试其他方案
- 冷热数据
- 合理的分表
- 更换数据库

14. 什么是索引合并？

降多个范围扫描的行合并，适用于单表而不是多表，合并可以产生交集，并集或者交集并集

extra中出现以下信息表示使用了索引合并，
Using intersect(…)Using union(…)Using sort_union(…)

注意：

索引合并不适用于全文索引
可以关闭，SET optimizer_switch=‘index_merge_union=off,index_merge_sort_union=off’

以下是索引合并的案例

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
 WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
 AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
 WHERE t1.key1 = 1
 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

官网链接：https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html

15. 创建索引有什么注意点？

考虑使用场景。使用比较频繁的字段一般都有索引。（重要）
考虑区分度。一般是区分度比较高的才加，但是某个特定场景区分度高也算
多个列经常一起查询，考虑组合索引
避免创建过多的索引
索引字段避免过长

16. 深分页如何优化？

前文中已经提到了。这里更详细的说明下

单纯优化查询速度的角度

大于一定长度的直接拒绝，不是爬虫就是搞事情
带上主键id

limit 10000,10
id> 111111111 limit 10

子查询优化

select id ,name,age,email from monk where age = 10 limit 50, 10

--修改为这个
SELECT
	m.id,NAME,age,email 
FROM
	monk m
	INNER JOIN ( SELECT id FROM monk WHERE age = 10 LIMIT 50, 10 ) AS t1 ON m.id = t1.id

正常如果mybatis插件，一般会有2次查询，我在如何优雅的实现一个Mybatis插件里面提到了，可以走自定义的查询
如果不需要分页信息，也可以PageHelper.startPage(pageNum, pageSize, false);
换成其他数据库

如果不需要优化查询速度，单纯降低负载来说

切到从/备库

Innodb

1. mysql有binlog，redolog，undolog？为什么不做简化？

这三个日志各自承担的职责是不同的，解决不同的问题

binlog
- Binlog记录所有的DDL和DML语句(除了数据查询语句SELECT、SHOW等)，以 Event的形式记录，同时记录语句执行时间。
- 主要作用为：主从复制，数据恢复
- 归属于mysql Server层
- 日志格式:
  - STATEMENT:记录sql语句
    - 本地数据不同可能导致执行效果不一致
  - ROW:基于行记录
    - 记录每个记录具体被修改的信息
  - MIXED:混合格式记录
    - 上面两种模式的综合体
- 官网链接:https://dev.mysql.com/doc/refman/8.0/en/binary-log.html
undolog
- undo log归属于innodb，
- 它主要用于回滚及MVCC的实现，保证事务的原子性
- 官网链接:https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html
redolog
- redolog归属于innodb，记录了对于页做了哪些改动
- 它主要通再崩溃恢复的时候，纠正不完整写入的数据，保证了持久性
- 官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html

2. redolog如何保证一致性，以及如何崩溃恢复的

大致介绍：redolog只要在事务执行后涉及到的页的修改，都会记录在redolog中
大致运行逻辑：
- 生成的每组的redolog都有唯一LSN值
- 系统会记录一个checkpoint_lsn全局变量，表示已经刷入到磁盘的脏页的最大lsn。
- 当bufferpool中的页有CUD操作的时候，除了加入flush链表外，还会记录初始的lsn值对应oldest_modification字段。PS:flush链表也是根据这个字段排序
- 每一次页的修改，都会记录最新的lsn值对应newest_modification字段
- 然后正常redolog刷入磁盘，脏页刷入磁盘
崩溃恢复：
- checkpoint_lsn之前的，就不管了，因为脏页都刷入磁盘了
- 查找磁盘存储redolog的block种，哪个没有写满，确定重点
- 解析redolog内容，组成hashMap,key是对应页的信息，value就是redolog链表
- 页中newest_modification这个字段记录了最新修改的LSN值，根据这个比对，还有哪些变动没有改动到，逐一进行恢复
刷盘策略：由innodb_flush_log_at_trx_commit控制刷入磁盘的策略。
- 设置为0时，每秒将日志写入并刷新到磁盘一次。
- 设置为1时，每次事务提交时都会将日志写入并刷新到磁盘。
- 设置为2时，日志会在每次事务提交后写入，并每秒刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。

3. undolog如何保证原子性？

当InnoDB引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到undolog里
undolog结构体里面有roll_pointer和trx_id 这个前文也提到了，形成版本链。
当事务执行rollback就找到对应的undolog进行相反操作即可

4. 一个更新语句的执行过程

server层向存储引擎获取数据。如果数据不在buffer pool中，会先将数据页加载到buffer pool中再返回
记录undolog
server层获取到数据，调用存储引擎修改接口，执行修改
更新数据到buffer pool。数据页设置成脏页，添加到flush链表中
写入redolog并写进redolog buffer
执行提交（两阶段提交）
写入redolog(prepare状态|一阶段)
记录binlog，并刷入磁盘（二阶段）
写入redolog(commit状态|二阶段)。

5. 什么是两阶段提交？

前文中提到了binlog和redolog都是用于崩溃恢复，那么为什么要两阶段提交呢？

是为了保证redolog和binlog的一致性

binlog记录缺失代表从库缺失。
redolog记录缺失代表主库缺失

单次提交，无论先后顺序怎么样，在极端情况下，都有可能后写入的造成缺失，最终都是主从不同步。
binlog 先写就会主库存在丢失数据的情况
binlog后写就会造成从库丢失数据的情况

6. 两阶段提交如何保证一致性的？

提交阶段如果发生崩溃

也就如下两种情况种极端情况

一阶段崩溃

redolog和binlog状态不一致，直接回滚

二阶段崩溃

比较binlog和redolog中的xid，不一致，回滚，一致，提交

参考博客：https://zhuanlan.zhihu.com/p/343449447

锁

1. mysql有哪些锁？

共享锁(S)：可以共享S锁
排它锁(X)：无法共享任何锁
意向锁：表级锁，表示要给行加锁。获取行锁之前要先获取意向锁（告诉别人，这张表有行锁）
- 意向共享锁（IS）：表示打算给表中的行加共享锁
  - SELECT ... FOR SHARE
- 意向排它锁（IX）：表示打算给表中的行加排它锁
  - SELECT ... FOR UPDATE

	X	IX	S	IS
X	互斥	互斥	互斥	互斥
IX	互斥	兼容	互斥	兼容
S	互斥	互斥	兼容	兼容
IS	互斥	兼容	兼容	兼容

行锁（Record Locks）:行锁锁的是索引记录，如果没有索引，就锁住隐式主键id
间隙锁（Gap Locks）:锁的是记录与记录之间的间隙
- 它会跨越单个或多个索引
- 唯一索引中等值查询不会锁住间隙
- 同一个间隙上允许出现X锁和S锁并存的情况
- RC级别中，对于索引的扫描，间隙锁是禁用的

比如下面一张表，主键id分别是 99,101,109
那间隙锁可能存在的地方是(-∞,99)(99,101)(101,109)(109,+∞)

行键锁（next-key locks）:是行锁和间隙锁的组合，锁的是间隙到当前记录为止
- 再RR级别下，InnoDB使用行键锁，扫描索引，预防幻读

那间隙锁可能存在的地方是(-∞,99](99,101](101,109](109,+∞)

插入意向锁（Insert Intention Locks）
- 间隙锁的一种
自增锁（AUTO-INC Locks）
- 表级别所，有事务插入，其他事务必须等待
空间索引的谓词锁（Predicate Locks for Spatial Indexes）
- 空间数据相关（>_>完全没见过）

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-locks

2. 遇到过死锁吗？如何解决的

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may involve com.iflytek.hmreader.order.dao.UBookShelfMapper.batchInsertOrUpdate-Inline
  ### The error occurred while setting parameters

类似于发现以上日志

导致死锁的原因：
多个事务同事访问相同的资源。但是访问顺序不同，出现了需要的锁在对方的手上，形成了互斥。和java之类的死锁产生条件一直

解决方案：

让事务尽可能的小（减少数据量）：代码优化
减少锁的数量：事务隔离级别改成RC
减少锁定时长：排查修改操作中的子查询
固定访问顺序：例如在每个事务中select… for update
开启死锁检测：mysql会自动回滚其中一个事务

innodb_lock_wait_timeout
innodb_deadlock_detect

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html

3. RR级别下的锁和RC级别下的锁

RR级别下

等值查询—普通查询

普通查询没有加锁的情况

等值查询—for update

此种情况下会添加【意向排它锁，行锁】

等值查询—for update边界情况

< 最小值【间隙锁】

最大值【行键锁】由于后面为”正无穷“所以LOCK_DATA为supremum pseudo-record

范围查询—for update
会加【行键锁】并且范围为(15,16](16,17](17,18](19,20](19,20)
lock in share mode

和上面一致，就不重复写了，排它锁换成共享锁即可

RC级别

for update
都执行了，但是只会添加行锁
lock in share mode

脚本

set global transaction isolation level read committed
set session transaction isolation level read committed;

set global transaction isolation level Repeatable Read
set session transaction isolation level Repeatable Read;
select @@transaction_isolation;


begin 

select id from monk where id = 100;
select id from monk where id = 100 for UPDATE;


select id from monk where id = 9999999 for UPDATE  ; -- >最大id
select id from monk where id = 0 for update; -- <最小id 

select id from monk where id >15 and id<20 for update;


select id from monk where id = 100;
select id from monk where id = 100 LOCK IN SHARE MODE;


select id from monk where id = 9999999 LOCK IN SHARE MODE; -- >最大id
select id from monk where id = 0 LOCK IN SHARE MODE; -- <最小id 

select id from monk where id >15 and id<20 LOCK IN SHARE MODE;


SELECT ENGINE_TRANSACTION_ID as Trx_Id, 
              OBJECT_NAME as `Table`, 
              INDEX_NAME as `Index`, 
              LOCK_DATA as Data, 
              LOCK_MODE as Mode, 
              LOCK_STATUS as Status, 
              LOCK_TYPE as Type 
        FROM performance_schema.data_locks;
ROLLBACK
commit

4. mysqlRR级别中的加锁原则

锁的范围是左开右闭。
如果是唯一非空索引的等值查询，Next-Key Lock 会退化成 Record Lock。
普通索引上的等值查询，向后遍历时，最后一个不满足等值条件的时候，Next-Key Lock 会退化成 Gap Lock。

上面一个问题中就是锁退化很好的例子

5. 说一说mysql中的乐观锁和悲观锁

几乎所有的悲观锁都是依靠排它锁实现的，不光在mysql中
下面就是一个悲观锁的实现方式

begin ;
select id,name ,age from monk where id =100 for update;
update monk set age=27 where id =100;
commit;

乐观锁基本都是cas的实现方式,compare and set的值一般是某个状态
下面是乐观锁的实现方式

update monk set age=27 where id =100 and age=39;

HA

1. MySQL主从复制过程是什么

大致流程如下图所示

从节点开启主从复制后会创建2个线程：IO线程和SQL线程
从节点的IO线程与主服务器创建链接，主节点创建Binary log dump线程，用于发送bin log
从服务器的IO线程会告诉dump线程从什么位置开始接收
然后当binlog有变动，从节点主动拉取变动部分（官网说的）

IO线程接收到变动保存到relaylog中
sql线程读取relaylog中的内容，写入本地（sql线程受到replica_parallel_workers控制）

参见官网：https://dev.mysql.com/doc/refman/8.0/en/replication-threads.html

2. binlog格式

日志格式:
- STATEMENT:记录sql语句
  - 本地数据不同可能导致执行效果不一致
- ROW:基于行记录
  - 记录每个记录具体被修改的信息
- MIXED:混合格式记录
  - 上面两种模式的综合体
    我这正在用的canal就要求row格式
    官网链接：https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

3. 你们是怎么分库的

分库主要解决如下几个问题

并发量大
连接数不够
cpu负载高

垂直拆分
按照业务拆分，当某个场景负载高的时候，可以平滑的迁移到其他数据库实例中从而降低负载

水平拆分
按照某个维度，整个数据库整体拆分，比如时间维度，地区维度等等

4. 你们是怎么分表的

垂直分表
由于业务拆分，一个表几十个字段了，可能把一些不活跃的字段拆分到别的表去，这样数据页能存放更多的数据，提升查询效率。
又或者业务就是庞杂，拆分的。
水平分表
主要是解决数据量大的问题，数据量大导致的最直接就是一堆慢sql，理论上要根据慢sql来优化，这个要设计到多个指标。

像我们主表1亿+，还能坚挺，每一天都提心吊胆。

5. 分表算法有哪些

取模

大致逻辑：
- id%128

关键词划分

大致逻辑
- 按照月份，按照年份按，照用户id

hash取模

大致逻辑
- 选取某几个列组合然后hash，然后取模

范围划分

多少到多少分到一张表中

一致性hash

大致逻辑
- hash后放入hash环中，多少到多少访问某个节点，这样拓展只要某个节点的数据迁移

6. 分库分表知道哪些框架和方案吗

服务端处理
- Sharding-JDBC：增强版本jdbc
- TDDL
数据库代理
- Sharding-Proxy
- Mycat

7. 分库分表会带来什么问题呢？

分布式事务的问题

这个是重中之重，多个库如何保证事务的一致性就是个问题

分页，排序等等都失效了

如果分的不多，可以再内存中进行数据处理。
如果业务场景比较复杂，上面这种就不大合适了

一般是下面两种思路

保证我数据查询条件能分页就行了
比如，买家我想查询我的订单，卖家我想查询我的订单。

按照卖家和买家冗余两份>_>

那就数据聚合到一起

走es等三方数据库

统计的问题

统计也是开发中比较常见的场景，如果分库分表影响了统计。

我能想到比较简单的方法，同步到clickhouse之类的列式数据库里面

id问题

常见的解决算法是以下2种算法，都可以保证生成的id不重复

雪花算法:https://github.com/twitter-archive/snowflake/releases/tag/snowflake-2010

时间+机器id+序号理论上一台机器的极限是1秒4096个，多布置机器可以防止重复问题

优点
- 生成id是有序的
- 几乎不会重复
- 不会消耗额外资源
缺点
- 只能用69年（都退休了🐶不是问题）
- 时钟回拨问题（人为问题，润秒）
  - 报错
  - 修改算法添加时钟位置

leaf算法:https://github.com/Meituan-Dianping/Leaf
号段的形式，一次读取一批
优点：
- 自增id之类的迁移方便
- 不会有重复
- id有序
缺点：
- 依赖数据库
- id不够随机

参考资料
《MySQL 是怎样运行的：从根儿上理解 MySQL》
https://dev.mysql.com/doc/refman/8.0/en/preface.html
https://stackoverflow.com/questions/11043712/non-repeatable-read-vs-phantom-read
https://zhuanlan.zhihu.com/p/343449447

文章来源:https://blog.csdn.net/goligu/article/details/135788989
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！