关于mysql:MySQL经典面试题总结史上最全面试题思维导图总结2022最新版

写在后面

📌Xmind 文件获取：GitHub 继续更新中，别忘了 star 喔~

@TOC
「Java 学习 + 面试指南」思维导图，计算机自学指南，包含 Java 根底、JVM、数据库、mysql、redis、计算机网络、算法、数据结构、操作系统等，后盾技术栈 / 架构师之路 / 全栈开发社区，阿里，腾讯，百度，美团，头条等春招 / 秋招 / 校招 / 面试

思维导图（png 格局可下载放大）

mysql

事务

四大个性(ACID)

• 原子性

  • 要么全副实现，要么齐全不起作用

• 一致性

  • 多个事务对同一个数据读取的后果是雷同的

• 隔离性

  • 各并发事务之间数据库是独立的

• 持久性

  • 数据的扭转是长久的

读

• 脏读

  • A 读，B 读，A 回滚，B 不正确

• 不可反复读

  • A 读 1，B 写 2，A 读 2

• 幻读

  • A 读 2 条，B 删了 1 条，A 读 1 条

四个隔离级别

• READ-UNCOMMITTED(读取未提交)

  • 容许读取尚未提交的数据

    • 3

• READ-COMMITTED(读取已提交)

  • 容许读取并发事务曾经提交

    • 阻止脏读

• REPEATABLE-READ(可反复读)

  • 屡次读取后果都是统一

    • 阻止脏读和不可反复读

• SERIALIZABLE(可串行化)

  • 顺次一一执行
• 隔离机制的实现基于锁机制和并发调度
  （MVVC（多版本并发管制），通过保留批改的旧版本信息）
• Mysql 默认采纳的 REPEATABLE_READ 隔离级别，分布式事务 SERIALIZABLE(可串行化)
  Oracle 默认采纳的 READ_COMMITTED 隔离级别

锁

• Read Uncommitted

  • 不须要加共享锁，不会跟被批改的数据上的排他锁抵触

• Read Committed

  • 加共享锁，语句执行完当前开释共享锁

• Repeatable Read

  • 须要加共享锁，必须期待事务执行结束当前才开释共享锁

• SERIALIZABLE

  • 锁定整个范畴的键，并始终持有锁，直到事务实现

粒度

• 表级锁

  • 开销小，加锁快
  • 锁定粒度大，收回锁抵触的概率最高，并发度最低
  • MYISAM 与 INNODB

• 行级锁

  • 开销大，加锁慢
  • 锁定粒度最小，产生锁抵触的概率最低，并发度也最高
  • INNODB

• 页级锁

  • 开销和加锁工夫界于表锁和行锁之间
  • 一次锁定相邻的一组记录
  • 并发度个别

类别

• 共享锁
  读锁

  • 能够同时加上多个

• 排他锁
  写锁

  • 只能够加一个
  • 其余的排他锁，共享锁都相斥

锁

• 乐观锁

  • 数据库中的锁机制
  • 多写的场景下

• 乐观锁

  • 应用版本号机制或 CAS 算法实现
  • 写比拟少的状况下（多读场景）

基础知识

三大范式

• 第一范式 列不可再分
• 第二范式 非主键齐全依赖主键，不能局部依赖
• 第三范式 非主键只依赖主键，不依赖非主键

权限表

• user 用户账号信息，全局
• db 账号各数据库的操作权限
• table_priv 表级操作权限
• column_priv 列级操作权限
• host 给定主机

binlog

• statement 批改数据的 sql：缩小日志量、解决 io，需保留上下文，函数之类无奈被复制
• row 记录每一行的改变：全部记下来，信息多日志量大
• mixed 一般用 statement，无奈应用用 row
• MySQL 主从复制，Master 把它的二进制日志传递给 slaves 来达到 master-slave 数据统一的目标
• 数据恢复：通过应用 mysqlbinlog 工具来使复原数据

数据类型

• 整数类型

  • TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，别离示意 1 字节、2 字节、3 字节、4 字节、8 字节整数
  • 加上 UNSIGNED 示意无符号
  • 指定长度：INT(11)只影响显示字符，须要和 UNSIGNED ZEROFILL 才有意义

  • int(20)

    • 显示字符的长度。20 示意最大显示宽度为 20，但仍占 4 字节存储，存储范畴不变，int(1)和 int(20)存储和计算均一样
    • 不影响外部存储，只是影响带 zerofill 定义的 int 时，后面补多少个 0，易于报表展现

• 实数类型

  • DECIMAL 能够用于存储比 BIGINT 还大的整型，能存储准确的小数。
  • 而 FLOAT 和 DOUBLE 是有取值范畴的，并反对应用规范的浮点进行近似计算。
  • 计算时 FLOAT 和 DOUBLE 相比 DECIMAL 效率更高一些，DECIMAL 你能够了解成是用字符串进行解决。
  • FLOAT 类型 4 字节，DOUBLE 类型 8 字节

• 字符串类型

  • VARCHAR

    • VARCHAR 用于存储可变长字符串，它比定长类型更节俭空间。
    • VARCHAR 应用额定 1 或 2 个字节存储字符串长度。列长度小于 255 字节时，应用 1 字节示意，否则应用 2 字节示意。
    • VARCHAR 存储的内容超出设置的长度时，内容会被截断。

  • CHAR

    • CHAR 是定长的，依据定义的字符串长度调配足够的空间。
    • CHAR 会依据须要应用空格进行填充不便比拟。
    • CHAR 适宜存储很短的字符串，或者所有值都靠近同一个长度。
    • CHAR 存储的内容超出设置的长度时，内容同样会被截断。
    • 长度固定，所以存取速度要比 varchar 快很多，甚至能快 50%
      长度固定，所以会占据多余的空间，是空间换工夫的做法

  • 综合

    • 对于常常变更的数据来说，CHAR 比 VARCHAR 更好，因为 CHAR 不容易产生碎片。
    • 对于十分短的列，CHAR 比 VARCHAR 在存储空间上更有效率。
    • 应用时要留神只调配须要的空间，更长的列排序时会耗费更多内存。
    • 尽量避免应用 TEXT/BLOB 类型，查问时会应用长期表，导致重大的性能开销。
    • 性能角度（char 更快）和节俭磁盘空间角度（varchar 更小

    • chart(10)和 varchar(10)
      示意存储数据的大小，即示意存储多少个字符

      • char(10)示意存储定长的 10 个字符，有余 10 个就用空格补齐，占用更多的存储空间
      • varchar(10)示意存储 10 个变长的字符，存储多少个就是多少个
        空格也按一个字符存储，这一点是和 char(10)的空格不同的，char(10)的空格示意占位不算一个字符
    • 明码散列，盐，用户身份证号等固定长度的字符串应该应用 char 而不是 varchar 来存储，这样能够节俭空间且进步检索效率。

• 日期和工夫类型

  • 尽量应用 timestamp，空间效率高于 datetime，
  • 用整数保留工夫戳通常不不便解决。
  • 如果须要存储微秒，能够应用 bigint 存储。

关键字

in 和 exists

• in

  • 表面和内表作 hash 连贯

• exists

  • 对表面作 loop 循环，每次 loop 循环再对内表进行查问。
• 两个表大小相当，那么用 in 和 exists 差异不大
• 子查问表大的用 exists，子查问表小的用 in
• not in，那么内表面都进行全表扫描，没有用到索引
  not exists 的子查问仍然能用到表上的索引

UNION 与 UNION ALL

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

drop、delete 与 truncate

• Delete

  • 可回滚
  • 表构造还在，删除表的全副或者一部分数据行
  • 删除速度慢，须要逐行删除

• Truncate

  • 不可回滚
  • 表构造还在，删除表中的所有数据
  • 删除速度快

• Drop

  • 不可回滚
  • 从数据库中删除表，所有的数据行，索引和权限也会被删除
  • 删除速度最快
• 不再须要一张表的时候，用 drop；在想删除局部数据行时候，用 delete；在保留表而删除所有数据的时候用 truncate。

引擎

Innodb 引擎

• ACID 事务的反对
• 行级锁，并发量高
• 外键的束缚
• INSERT、UPDATE、DELETE
• B+ 树索引，Innodb 是索引组织表，聚簇索引，主键索引叶子节点存储着行数据，非主键索引的叶子节点存储的是主键和其余带索引的列数据
• 哈希索引
• 按主键大小有序插入
• 更多的内存和存储，会在主内存中建设其专用的缓冲池用于高速缓冲数据和索引

MyIASM 引擎

• 可被压缩，存储空间较小
• SELECT，读写插入为主
• B+ 树索引，myisam 是堆表，非聚簇索引，叶子节点存储的是行数据地址，再寻址一次
• 全文索引
• 按记录插入程序保留
• 以文件的模式存储，在跨平台的数据转移中会很不便

索引

优缺点

• 长处

  • 放慢检索速度
  • 应用优化暗藏器，进步零碎的性能

• 毛病

  • 创立索引和保护索引要消耗工夫
  • 动静的保护，会升高增 / 改 / 删的执行效率
  • 占物理空间

应用场景

• where
• order by 没有用内部排序，有只有取出索引表某个范畴内的索引对应的数据

• explain select……

  • possible_keys 可能
  • key 理论用到

• 索引笼罩

  • 间接在索引表中查问而不会拜访原始数据（否则只有有一个字段没有建设索引就会做全表扫描）

类型

• 主键索引: 不容许反复，不容许为 NULL，一个表一个
• 惟一索引: 不容许反复，容许为 NULL 值，一个表可多个
• 一般索引: 没有唯一性的限度，容许为 NULL 值
• 全文索引

数据结构

• b+ 树

  • 不仅能够被用在 =,>,>=,<,<= 和 between 这些比拟操作符上，而且还能够用于 like 操作符
  • 非终端结点能够看成是索引局部
  • 叶子结点中蕴含了全副关键字的信息，及指向含这些关键字记录的指针
    数据对象的插入和删除仅在叶节点上进行
  • 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点
  • 人造反对范畴，有序
  • 防止不了回表查问数据，聚簇索引、笼罩索引间接通过索引

• hash

  • 只能用于对等比拟
  • 等值查问更快

创立准则

• 最左前缀匹配准则
  始终向右匹配直到遇到范畴查问
• 较频繁作为查问条件
• 更新频繁字段不适宜
• 不能无效辨别数据的列不适宜
• 有外键的数据列肯定要
• 尽量的扩大索引，不要新建索引

创立形式

• CREATE TABLE
• ALTER TABLE
• CREATE INDEX 不能创立 PRIMARY KEY 索引
• 删除索引 alter table 表名 drop KEY 索引名

百万级别删除

• 产生额定的对索引文件的操作，会升高增 / 改 / 删的执行效率
• 能够先删除索引
• 删除其中无用数据
• 删除实现后从新创立索引

B 树和 B + 树的区别

• B 树

  • 外部节点同时存储键和值，进步热点数据的查问效率

• B+ 树

  • 外部节点只寄存键，一次性读入内存中能够查找的关键字也就越多，IO 读写次数升高；
  • 由一条链相连，范畴查问；
  • 必须走一条从根节点到叶节点的路查问效率相当

聚簇索引与非聚簇索引

• 聚簇索引

  • 数据存储与索引放到了一块

• 非聚簇索引

  • 将数据存储于索引离开构造，回表查问

• 在 InnoDB 中

  • 只有主键索引是聚簇索引
  • 如果没有主键，则筛选一个惟一键建设聚簇索引。
  • 如果没有惟一键，则隐式的生成一个键来建设聚簇索引。
  • 在聚簇索引之上创立的索引称之为辅助索引，总是须要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、惟一索引，辅助索引叶子节点存储的不再是行的物理地位，而是主键值
  • 非聚簇索引不肯定会回表查问，全副命中了索引不用

联结索引

• 想要命中索引，须要依照建设索引时的字段程序挨个应用
• 将查问需要频繁或者字段选择性高的列放在后面

其余

视图

• 由根本表 (实表) 产生的表(虚表)
• 建设和删除不影响根本表
• 更新 (增加，删除和批改) 间接影响根本表
• 来自多个根本表时，不容许增加和删除数据

• 毛病

  • 性能
  • 批改限度

存储过程

预编译的 SQL 语句

• 长处

  • 预编译过的，执行效率高
  • 间接寄存于数据库中，缩小网络通讯
  • 安全性高，有肯定权限
  • 能够重复使用

• 毛病

  • 调试麻烦
  • 移植问题
  • 从新编译问题
  • 用户需要的减少会导致数据结构的变动

触发器

一段代码，当触发某个事件时，主动执行这些代码

关联查问

穿插连贯(笛卡尔积)

• select r.,s. from r,s

内连贯

• select r.,s. from r inner join s on r.c=s.c

左连贯

• select r.,s. from r left join s on r.c=s.c

右连贯

• select r.,s. from r right join s on r.c=s.c

全表连贯

mysql 不反对

• select r.,s. from r full join s on r.c=s.c
• SELECT FROM A LEFT JOIN B ON A.id=B.id UNION SELECT FROM A RIGHT JOIN B ON A.id=B.id

优化

explain

• 查看语句的执行打算

• id

  • 示意一个查问中各个子查问的执行程序
    越大优先级越高，越先被执行

• type

  • ALL 扫描全表数据
  • index 遍历索引
    索引物理文件全扫描，速度十分慢
  • range 索引范畴查找
  • ref 应用非惟一索引查找数据
  • consts 主键或者惟一索引

• possible_keys

  • 可能应用的索引

• key

  • 在查问中理论应用的索引

大表数据查问

• 优化 shema、sql 语句 + 索引
• 第二加缓存，memcached, redis
• 主从复制，读写拆散
• 垂直拆分，依据你模块的耦合度，将一个大的零碎分为多个小的零碎，也就是分布式系统
• 程度切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要抉择一个正当的 sharding key, 为了有好的查问效率，表构造也要改变，做肯定的冗余，利用也要改，sql 中尽量带 sharding key，将数据定位到限定的表下来查，而不是扫描全副的表；

超大分页

• MySQL 并不是跳过 offset 行，而是取 offset+ N 行，而后返回放弃前 offset 行，返回 N 行，那当 offset 特地大的时候，效率就十分的低下

• 先疾速定位须要获取的 id 段，而后再关联

  • SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20) b where a.id=b.id

分页

• mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
• mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
• mysql> SELECT * FROM table LIMIT 5; // 检索前 5 个记录行

慢查问日志

• 用于记录执行工夫超过某个临界值的 SQL 日志
• 配置项：slow_query_log

• 优化

  • 是否 load 了额定的数据

    • 查问了多余的行并且摈弃掉了，可能是加载了许多后果中并不需要的列
  • 应用索引的状况

  • 数据量是否太大

    • 横向或者纵向的分表

自增 ID 还是 UUID

• 应用自增 ID，不要应用 UUID
• 自增 ID，那么只须要一直向后排列即可
• UUID，因为到来的 ID 与原来的大小不确定，会造成十分多的数据插入，数据挪动
• 没有主键，InnoDB 会抉择一个惟一键来作为聚簇索引，如果没有惟一键，会生成一个隐式的主键。

定义为 not null

• null 值会占用更多的字节，且会在程序中造成很多与预期不符的状况。

SQL 语句优化

优化 WHERE 子句

• 在 where 及 order by 波及的列上建设索引
• 防止在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃应用索引而进行全表扫描
• 防止在 where 子句中应用!= 或 <> 操作符
• 防止在 where 子句中应用 or 来连贯条件
• in 和 not in 也要慎用
• 防止在 where 子句中应用参数
• 防止在 where 子句中对字段进行表达式操作
• 防止在 where 子句中对字段进行函数操作

优化查问过程中的数据拜访

• 查问不须要的数据

  • 应用 limit 解决

• 多表关联返回全部列

  • 指定列名

• 总是返回全部列

  • 防止应用 SELECT *

• 反复查问雷同的数据

  • 能够缓存数据

优化长难的查问语句

• 应用尽可能小的查问是好的
• 将一个大的查问分为多个小的雷同的查问
• 合成关联查问

化特定类型的查问语句

• count(*)会疏忽所有的列，不要应用 count(列名)
• 减少汇总表
• 应用缓存

优化关联查问

• ON 或者 USING 子句中是否有索引
• GROUP BY 和 ORDER BY 只有一个表中的列

优化子查问

• 优化 GROUP BY 和 DISTINCT，应用索引来优化，是最无效的优化办法
• 不须要 ORDER BY，进行 GROUP BY 时加 ORDER BY NUL

数据库优化

构造优化

• 将字段很多的表分解成多个表

• 减少两头表

  • 须要常常联结查问的表

• 减少冗余字段

  • 表的规范化水平越高，表和表之间的关系越多，须要连贯查问的状况也就越多，性能也就越差。

cpu 飙升

• 看看外面跑的 session 状况，是不是有耗费资源的 sql 在运行

  • 找出耗费高的 sql，看看执行打算是否精确，index 是否缺失，或者切实是数据量太大造成

    • kill 掉这些线程

      • 进行相应的调整 (比如说加索引、改 sql、改内存参数) 之后，再从新跑这些 SQL

• 每个 sql 耗费资源并不多，然而忽然之间，有大量的 session 连进来导致 cpu 飙升

  • 剖析为何连接数会激增，再做出相应的调整，比如说限度连接数等

大表优化

• 限定数据的范畴

• 读 / 写拆散

  • 主库负责写，从库负责读

• 缓存

  • 应用 MySQL 的缓存，另外对重量级、更新少的数据能够思考应用利用级别的缓存

• 分库分表

  • 垂直分区

    • 数据表列的拆分；某些列罕用，另外一些列不罕用
    • 使得行数据变小，在查问时缩小读取的 Block 数，缩小 I / O 次数
    • 会呈现冗余，须要治理冗余列，并会引起 Join 操作；对于应用层来说，逻辑算法减少开发成本

  • 程度分区

    • 数据表行的拆分，程度拆分最好分库

    • 长处

      • 升高在查问时须要读的数据和索引的页数，同时也升高了索引的层数，进步查问次数
      • 反对十分大的数据量存储，利用端革新也少

    • 毛病

      • 分片事务难以解决，跨界点 Join 性能较差，逻辑简单
      • 通常查问时须要多个表名，查问所有数据都需 UNION 操作

  • 数据库分片

    • 客户端代理

      • 分片逻辑在利用端，封装在 jar 包中，通过批改或者封装 JDBC 层来实现

    • 中间件代理

      • 在利用和数据两头加了一个代理层。分片逻辑对立保护在中间件服务中。

  • 问题

    • 事务反对

      • 数据库自身的分布式事务管理
      • 应用程序去帮助管制

    • 跨库 join

      • 分两次查问实现。在第一次查问的后果集中找出关联数据的 id, 依据这些 id 发动第二次申请失去关联数据。

    • 跨节点的 count,order by,group by 以及聚合函数问题

      • 与解决跨节点 join 问题的相似，别离在各个节点上失去后果后在应用程序端进行合并
      • 能够并行执行，因而很多时候它的速度要比繁多大表快很多

    • ID 问题

      • 自生成的 ID 无奈保障在全局上是惟一的
      • 在插入数据之前须要先取得 ID, 以便进行 SQL 路由，常见的主键生成策略

      • UUID

        • UUID 十分的长，除占用大量存储空间外，最次要的问题是在索引上，在建设索引和基于索引进行查问时都存在性能问题。

    • 跨分片的排序分页

      • 排序字段非分片字段

        • 在不同的分片节点中将数据进行排序并返回，并将不同分片返回的后果集进行汇总和再次排序

主从复制

通过二进制日志（BINLOG）传输到从数据库上，而后将这些日志从新执行（重做）；保持一致

作用

• 主数据库呈现问题，能够切换到从数据库。
• 能够进行数据库层面的读写拆散。
• 能够在从数据库上进行日常备份。

解决问题

• 数据分布：随便开始或进行复制，并在不同地理位置散布数据备份
• 负载平衡：升高单个服务器的压力
• 高可用和故障切换：帮忙应用程序防止单点失败
• 降级测试：能够用更高版本的 MySQL 作为从库

流程

• 主：binlog 线程——记录下所有扭转了数据库数据的语句，放进 master 上的 binlog 中；
• 从：io 线程——在应用 start slave 之后，负责从 master 上拉取 binlog 内容，放进本人的 relay log 中；
• 从：sql 执行线程——执行 relay log 中的语句；

解决方案

• 应用 mysql-proxy 代理

  • 升高性能，不反对事务

• 应用 AbstractRoutingDataSource+aop+annotation 在 dao 层决定数据源

  • mybatis

    • 通过 mybatis plugin 拦挡 sql 语句，所有的 insert/update/delete 都拜访 master 库，所有的 select 都拜访 salve
      还须要重写一下 DataSourceTransactionManager，将 read-only 的事务扔进读库，其余的有读有写的扔进写库

  • 应用 AbstractRoutingDataSource+aop+annotation 在 service 层决定数据源

    • 能够反对事务.