Checklist系列:MySQL自检五十五问，万字整理，推荐收藏-淘娜娜副业社

基础1. 什么是MySQL？

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

2. 数据库三大范式

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

3. MySQL基础架构

精简版本：

官网版本：

官网链接:/doc//…

4. SQL语句的执行过程

大致情况如上图

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

官网链接：/doc//…

6. BLOB和TEXT有什么区别？

官网链接：/doc//…

7. 和的区别是什么？

官网链接：/doc//…

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

官网链接：/doc//…

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

事务

Yes

MVCC

Yes

外键

Yes

聚簇索引

Yes

锁最小粒度

行锁

表锁

清空方式

逐行

重建

官网链接地址：/doc//…

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

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

官网链接：/doc//…

11 UNION与UNION ALL的区别？

官网链接：/doc//…

12. drop、与的区别？

官网链接：

事务1. 什么是数据库事务

官网链接：/wiki/%E6%95…

2. acid特性？

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

官网链接：/doc//…

3. 并发事务带来了哪些问题?4. 事务隔离级别

官网链接：/doc//…

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

例子：

链接：//1…

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

隔离级别

脏读

不可重复读

幻读

读未提交(READ )

✅

读已提交(READ )

❎

✅

可重复度( READ)

❎

✅

串行化()

❎

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

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

锁解决幻读

事务A

事务B

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

MVCC解决幻读

事务A

事务B

MVCC中的幻读场景

事务A

事务B

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


    快照适用于事务内的select语句，不一定适用于DML语句。
    其他事务修改或删除并提交，其他事务的修改或删除可能影响到那些行，这些行对于该事务可见

上面 test set name='kak' where id >120 和 test set name='kak' where id

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

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

官网链接：/doc//…

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

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

：使用进行回滚，也使用这些日志构成版本链。每一行记录中有(事务id)，(回滚指针)，(记录id)，下面贴了部分源码。主要是由几个字段来的，以下解释来自于注释

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

接下来就是比较事务id了

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

下面画图举个栗子

这个是源码

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;
};

官网链接：/doc//…

索引1. MySQL中索引类型?

官网链接：/doc//…

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索引中都涵盖了，就不会回表了，直接返回。

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

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

索引下推的开关控制

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

官网链接:/doc//…

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. 为什么使用B+树?

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

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

7. 什么是B+树？

8. 什么是B树9. 为什么使用B+树而不使用其他数据结构？非叶子节点存放索引，索引占用的空间小，可以指向更多的页，同等量的数据情况下，层级比B树更低，查询也就更快叶子节点双向链表，更方便范围查询，由于Hash索引数据都在叶子节点，插入删除操作的时候，IO操作更少10. 索引失效的场景?

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

测试数据脚本

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.讲讲执行计划，如何理解其中各个字段的含义？

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

value IN (SELECT primary_key FROM single_table WHERE some_expr)

value IN (SELECT key_column FROM single_table WHERE some_expr)

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);

:mysql可以从中选择的索引（选择项）key:MySQL实际决定使用的索引:索引长度ref:显示哪些列或常量与键列中指定的索引进行比较，以从表中选择行。rows:MySQL认为要查询的行数:过滤列指示按表条件过滤的表行的估计百分比。Extra:此列包含有关 MySQL 如何解析查询的附加信息。(全部的可以点击官网链接，这里就列一些比较常见的) Using :MySQL 必须执行额外的操作来找出如何按排序顺序检索行。 Using index:覆盖索引 Using index :索引下推 Using index for group-by:使用索引优化Group by或且无需额外磁盘访问 Using join (Block Loop), Using join ( Key ), Using join (hash join) (JSON : ):多表连接使用缓冲区优化 Block Loop:块嵌套循环，优化表连接 Key :批量键访问算法进行连接 hash join：hash连接算法进行连接。>=8.0.18版本可用 Using where:表示使用了where子句进行过滤或筛选 Using (…)Using union(…)Using (…):索引合并

官网链接：/doc//…

13. 慢SQL如何进行优化

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

14. 什么是索引合并？

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

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

注意：

以下是索引合并的案例

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);

官网链接：/doc//…

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

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

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

切到从/备库. mysql有，，？为什么不做简化？

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

. 如何保证一致性，以及如何崩溃恢复的3. 如何保证原子性？4. 一个更新语句的执行过程层向存储引擎获取数据。如果数据不在 pool中，会先将数据页加载到 pool中再返回记录层获取到数据，调用存储引擎修改接口，执行修改更新数据到 pool。数据页设置成脏页，添加到flush链表中写入并写进执行提交（两阶段提交）写入(状态|一阶段)记录，并刷入磁盘（二阶段）写入(状态|二阶段)。5. 什么是两阶段提交？

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

是为了保证和的一致性

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

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

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

提交阶段如果发生崩溃

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

一阶段崩溃

和状态不一致，直接回滚

二阶段崩溃

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

参考博客：/p/

锁1. mysql有哪些锁？

互斥

兼容

互斥

兼容

互斥

兼容

互斥

兼容

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

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

官网链接：/doc//…

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减少锁定时长：排查修改操作中的子查询固定访问顺序：例如在每个事务中… for 开启死锁检测：mysql会自动回滚其中一个事务

官网链接：/doc//…

3. RR级别下的锁和RC级别下的锁等值查询—普通查询

普通查询没有加锁的情况

等值查询—for

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

等值查询—for 边界情况

小于最小值【间隙锁】

大于最大值【行键锁】由于后面为”正无穷“所以为 –

范围查询—for 会加【行键锁】并且范围为(15,16](16,17](17,18](19,20](19,20)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; -- 15 and id最大id
select id from monk where id = 0 LOCK IN SHARE MODE; -- 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. 级别中的加锁原则

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

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的实现方式, and set的值一般是某个状态下面是乐观锁的实现方式

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

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

大致流程如下图所示

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

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

参见官网：/doc//…

2. 格式3. 你们是怎么分库的

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

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

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

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

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

5. 分表算法有哪些取模关键词划分hash取模范围划分一致性hash6. 分库分表知道哪些框架和方案吗7. 分库分表会带来什么问题呢？分布式事务的问题

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

分页，排序等等都失效了

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

一般是下面两种思路

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

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

那就数据聚合到一起

走es等三方数据库

统计的问题

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

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

id问题

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

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

———END———
限时特惠： 本站每日持续更新海量各大内部创业教程，永久会员只需109元，全站资源免费下载点击查看详情
站长微信： nanadh666

声明：1、本内容转载于网络，版权归原作者所有！2、本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。3、本内容若侵犯到你的版权利益，请联系我们，会尽快给予删除处理！

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

站长简介

最新实战项目

小红书引流私域旅游资料售卖，复制粘贴，三分钟一篇图文，日入1000+，小白终身可做

剪映电脑版实战教程，0基础打造短视频剪辑高手，详解功能、工具与实操案例

短视频制作与运营，从0到10万粉丝变现方法，剪映特效与热门视频制作教程

【高端精品】外面收费1988的咔咔一通剪视频批量处理，合并，裁剪，转图片，视频去重等功能【剪辑助手+详细教程】

主播直播特训营：抖音直播间运营知识+开播准备+流量考核，轻松上手

同城店铺短视频引流变现：掌握抖音平台规则，打造爆款内容，实现流量变现

小红书实战运营课：从开通店铺到直播话术，打造爆款笔记与高效直播

AI工具PPT制作教程：AI工具助力PPT制作，高质量PPT一分钟搞定

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

相关文章

站长简介

最新实战项目