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

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

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

原子性
- 要么全副实现，要么齐全不起作用
一致性
- 多个事务对同一个数据读取的后果是雷同的
隔离性
- 各并发事务之间数据库是独立的
持久性
- 数据的扭转是长久的

脏读
- 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 给定主机

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

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

Delete
- 可回滚
- 表构造还在，删除表的全副或者一部分数据行
- 删除速度慢，须要逐行删除
Truncate
- 不可回滚
- 表构造还在，删除表中的所有数据
- 删除速度快
Drop
- 不可回滚
- 从数据库中删除表，所有的数据行，索引和权限也会被删除
- 删除速度最快
不再须要一张表的时候，用 drop；在想删除局部数据行时候，用 delete；在保留表而删除所有数据的时候用 truncate。

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

可被压缩，存储空间较小
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+ 树
- 外部节点只寄存键，一次性读入内存中能够查找的关键字也就越多，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

查看语句的执行打算
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 与原来的大小不确定，会造成十分多的数据插入，数据挪动
没有主键，InnoDB 会抉择一个惟一键来作为聚簇索引，如果没有惟一键，会生成一个隐式的主键。

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

在 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

将字段很多的表分解成多个表
减少两头表
- 须要常常联结查问的表
减少冗余字段
- 表的规范化水平越高，表和表之间的关系越多，须要连贯查问的状况也就越多，性能也就越差。

看看外面跑的 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 十分的长，除占用大量存储空间外，最次要的问题是在索引上，在建设索引和基于索引进行查问时都存在性能问题。
  - 跨分片的排序分页
    - 排序字段非分片字段
      - 在不同的分片节点中将数据进行排序并返回，并将不同分片返回的后果集进行汇总和再次排序

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

数据分布：随便开始或进行复制，并在不同地理位置散布数据备份
负载平衡：升高单个服务器的压力
高可用和故障切换：帮忙应用程序防止单点失败
降级测试：能够用更高版本的 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 层决定数据源
  - 能够反对事务.

写在后面

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

mysql

事务

四大个性(ACID)

读

四个隔离级别

锁

粒度

类别

锁

基础知识

三大范式

权限表

binlog

数据类型

关键字

in 和 exists

UNION 与 UNION ALL

drop、delete 与 truncate

引擎

Innodb 引擎

MyIASM 引擎

索引

优缺点

应用场景

类型

数据结构

创立准则

创立形式

百万级别删除

B 树和 B + 树的区别

聚簇索引与非聚簇索引

联结索引

其余

视图

存储过程

触发器

关联查问

穿插连贯(笛卡尔积)

内连贯

左连贯

右连贯

全表连贯

优化

explain

大表数据查问

超大分页

分页

慢查问日志

自增 ID 还是 UUID

定义为 not null

SQL 语句优化

优化 WHERE 子句

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

优化长难的查问语句

化特定类型的查问语句

优化关联查问

优化子查问

数据库优化

构造优化

cpu 飙升

大表优化

主从复制

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

作用

解决问题

流程

解决方案