MySQL经典面试题-史上面试题思维导图2022最新版

写在前面

🍁个人主页：HNUJSY
✨本期专栏：《史上最全经典面试题总结》欢迎订阅学习~
📌Xmind文件获取：GitHub 持续更新中，别忘了 star 喔~

「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层决定数据源
    • 可以支持事务.

这篇好文章是转载于：学新通技术网