【八股文】MySQL

1.char 和 varchar的区别

• char是定长的，varchar是可变的字符串
• char适合存长度差不多的或者较短的，例如手机号，身份证，MD4加密算法。varchar用来存备注信息，用户昵称等不确定长度的信息。

2.Decimal、double和float的区别

Decimal可以设计精确到小数点后多少位，float和double都有精度问题，可能损失精度。类比于Java中的实现

3. MySQL的存储引擎

• InnoDB: 支持行级锁（默认）和表级锁，支持事务。支持外键（但一般不使用外键，而是在业务代码中使用逻辑外键），支持数据库异常崩溃后的安全恢复。支持MVCC。支持聚簇索引，索引和数据是在一起的，查询速率高，但是插入和删除的速率很低（因为，索引底层是B+树，插入和删除会影响树的结构，会调整树）。
• MyISAM:支持表级锁，不支持事务，在并发时如果使用表级锁会造成阻塞，性能也就下降了。不支持外键。不支持聚簇索引，数据和索引是分开的，在查询较少的情况下速率更高。
  适用场景：
• InnoDB：适合需要事务处理、高并发写操作、数据完整性和恢复能力的场景，如金融交易、电子商务等。
• MyISAM：适合读取密集型应用，尤其是那些数据变化不大，以select查询为主的场景，如日志分析、报表生成等。

4.MySQL索引

• 使用索引可以大大提高数据的检索速度
• 创建唯一索引，可以保证数据表中的每一行的唯一性
• 缺点：占用空间，由于给表加了索引，要存储索引，就要用到B+树，B+树这种数据结构，在查询的时候很快，但是增删改时，索引也会跟着修改，降低了SQL的执行率、
• 适用场景：多读少写的大表情况下
• 索引的底层结构：B+树
  • B树，所有节点都要存key和data
  • B+树，只有叶子节点存储key和data，其他节点存储key，这样有更多的空间来创建更多的叶子节点（索引位置），同时为了能够查询一个区间，B+树用了有序双向链表，将所有叶子节点连接起来了。
• 索引类型：
  • 按照维度分为：主键索引，唯一索引，联合索引，全文索引
  • 按数据结构分为：B树索引，哈希索引和全文索引
• 什么是聚簇索引，非聚簇索引（二级索引）？
  • 聚簇索引是将数据存储和索引放到一块，索引结构的叶子节点保存了行数据。一张表中只有一个聚簇索引
  • 非聚簇索引（二级索引），将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。一张表可以存在多个。
• 聚簇索引（每个叶子节点存储的是一行数据）选取规则
  • 如果存在主键，主键索引就是聚簇索引
  • 如果不存在，将选用第一个unique索引作为聚簇索引
  • 如果都没有，则InnoDB会自动生成一个rowid作为隐藏的聚簇索引
• 什么是回表查询
  • 回表查询就是如果查询的不是主键，那么要通过二级索引查询到主键值，再从聚簇索引中通过主键值拿到改行数据
• 什么是覆盖索引
  • 覆盖索引是一种特殊的联合索引，覆盖索引包含要查询的所有列作为联合索引。简单来说不需要回表查询，要查询的列都在覆盖索引中能够找到。
  • 场景：超大分页处理：可以通过覆盖索引来解决（通过覆盖索引 + 子查询进行优化）。先通过主键索引查询主键，然后将查询到的主键与主表一起查询。例如
    • 优化前：select * from stu limit 9000000,10
    • 优化后：select * from stu s, (select id from stu order by id limit 9000000, 10) a where s.id = a.id
• 索引创建原则有哪些？
  • 针对数据量较大，且查询比较频繁的表建立索引（单表超过10万数据）
  • 当前字段为查询，排序和分组时
  • 选择区分度高的列作为索引（学号，id，手机号等）
  • 某个字符串类型的字段，字段很长，可以建立前缀索引
  • 尽量使用联合索引，节省存储空间，避免回表
  • 控制索引的数量，如果索引多，增删改会影响效率，并且存储索引会占用一定的空间
  • 如果索引列不能存储NULL，要用NOT NULL约束
• 索引失效的情况
  • 如果进行了类型转换就会造成索引失效。
    • 原因：本来索引的类型是整数类型，然后转换成字符串类型，在B+树里查找认为两个不相等
  • 模糊查询like可能导致索引失效，如果%在前面肯定会失效
    • 原因：因为查询的时候满足最左前缀法则，如果是%abc，索引不知道从哪个位置开始往下找，而abc%，索引会先去找abc的前缀串
  • 违反最左前缀法则，也就是在使用联合索引时，没有用左边的索引
    • 原因：联合索引是按照一定的顺序构建的，如果查询条件没有按照这个顺序从最左侧列开始使用，数据库优化器可能无法确定如何有效地使用这个索引。
  • 范围查询右边的列，不能使用索引
    • 原因：因为索引的结构是基于左侧列的排序，右侧列的信息是附加在左侧列的基础上的。
• 在索引上使用运算操作
  - 原因：例如SELECT * FROM products WHERE price + 10 > 100;price是索引，并且存在B+树中，而price可能不存在B+树中。

5.MySQL事务

• 事务是一组操作的集合，是一个不可分割的工作单位，要么全执行要么全都不执行。在Java中有现有的注解@Transactional
• 事务有ACID特性：
  • 原子性A（Atomicity）：事务是最小的执行单位，不允许被分割。
  • 一致性C（Consistency）：事务执行前后，数据保持一致
  • 隔离性I （Isolation）：一个用户的事务不会被其他事务干扰
  • 持久性D（Durability）：事务一旦被提交，对数据库中的数据改变是持久的。AID保证了C也保证了。AID是手段，C是目的。
  • 案例：当我们转账的过程中，A用户向B用户转1000，A减少1000，B增加1000，这个操作要么全成功要么全失败（原子性），数据库的数据必须一致（一致性），这个过程中其他事务不能干扰（隔离性），数据永远存储到数据库中（持久性）。
• 事务失效问题：
  • 事务方法非public修饰
    • 原因：由于Spring的事务是基于AOP的方法结合动态代理来实现的。因此事务方法一定要是public的，这样才能便于被Spring做事务的代理和增强。
  • 非事务方法调用事务方法
    • 原因：非事务方法调用事务方法，其实隐含了一个this.的前缀。也就是相当于调用了原来的Service中的普通方法，而非Spring代理对象的代理方法。事务肯定就失效了。
  • 事务方法的异常被捕获了
    • 原因：当事务方法调用非事务方法，非事务方法中捕获了异常，如果出现异常不会往外抛出，而当Spring事务管理感知到异常才会进行回滚，如果捕获了，则Spring无法感知到事务异常的发生，就不会回滚，事务就失效了
  • 事务异常类型不对
    • 原因：Spring的事务管理默认感知的异常类型是RuntimeException，当事务方法内部抛出了一个IOException时，不会被Spring捕获，因此就不会触发事务回滚，事务就失效了
  • 事务传播行为不对
    • 原因：当事务方法（A）调用另外一个事务方法（B）时，使用了 @Transactional(propagation = Propagation.REQUIRES_NEW)注解，就会创建一个新的事物，成为子事物，而该事物不会合并到调用的事物方法，当A发生异常时，B事务不会回滚，所以事务就失效了。
  • 没有被Spring管理
    • 原因：Service类没有加@Service注解，因此就没有被Spring管理

6.并发事务带来了什么问题

• 脏读：一个事务读取数据并对其进行修改，其他事物能够看到被修改后的数据，即使当前事务未提交。第一个事务没有提交，如果改回来，第二个事务就读取到了脏数据。
• 丢失修改：两个事务都在读数据，第一个事务修改了该数据，第二个事务也修改了数据，第一个事务修改的结果会丢失。（两个事务同时修改表的同一行数据）
• 不可重复读：两个事务同时读该数据，在第一个事务没结束的时候，第二个事务修改了数据，第一个事务读了多次，会出现不一样的结果。（总的数据行没有多，只是值变了）
  • 幻读：和不可重复读类似，两个事务同时读数据，其中一个事务开始插入一些数据，另外一事务在查询过程中会发现多了几条数据。（幻读是多了几条数据）

7.并发事务的控制有哪些？

锁：悲观控制，主要是通过读写锁来控制的

• 共享锁：也叫读锁，事务在读取记录时获取共享锁，允许多个事务获取
• 排他锁：也叫写锁，事务在修改记录时获取排它锁，不允许多个事务获取
• MVCC：乐观控制，MySQL的InnoDB存储引擎下RC，RR（读已提交和可重复读）且快照读下基于MVCC做数据的多版本并发控制。
  • MVCC（Multi-Version Concurrency Control）：多版本并发控制，维护一个数据的多个版本，使得读写操作没有冲突。
  • 隐藏字段
    • DB_TRX_ID：最近修改事务ID，指修改或插入这条记录，事务ID要加1
    • DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，配合undo log使用
    • DB_ROW_ID 隐藏主键，如果该表没有指定主键，将会生成该隐藏字段
  • undo log回滚日志
    • 记录update和delete的反操作，操作完成不会马上删除，MVCC版本访问也需要，不会立即被删除
    • undo log 版本链：不同事物或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，聊表的头部是最新的旧记录，链表尾部是最早的旧记录。
    • 当前读：读取记录的最新版本，并且其他事物不能修改该版本记录，会进行加锁。
    • 快照读：简单的select就是快照读，读取的是记录数据的可见版本，有可能是历史记录，非阻塞读。
      • RC（读已提交）：每次select，都生成一个快照读（每次读的视图都不同）
      • RR （可重复读）：开启事务后的第一个select语句才是快照读的地方。（有锁的只能读第一次select）
  • readview：是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务ID。

8.数据库的隔离级别

• 读未提交：允许读取没有提交的数据变更，可能会引起脏读、幻读和不可重复读。
• 读已提交：允许读取并发实物已经提交的数据，可以阻止脏读，但也可能发生幻读和不可重复读。
• 可重复读（InnoDB默认）：对同一字段的多次读取结果是一致的，除非数据被本身事务自己锁修改，可以阻止脏读和不可重复读，但还可以发生幻读。
• 串行化：服从ACID的隔离级别，每个事务逐个执行，其他事务不能干扰，隔离级别最高。都可以防止，基于锁和MVCC实现的。

9.MySQL怎么存储图片

• 通过云服务器的OSS（对象存储服务）技术，这需要提供访问凭证和指定的存储桶和文件路径。将上传的图片存储到OSS后，OSS返回一个唯一的URL地址，用于访问。

10.SQL优化

• 大表怎么优化，CRUD慢

  • 限定查询数据范围，务必要加where，比如查询一个月内的历史订单
  • 读写分离：将数据库拆分方案，主库负责写，从库负责读
  • 缓存：对更新少的数据可以考虑使用Redis缓存
  • 分库分表：例如分表，比如经常查询某张表的某些字段，可以将其拆分为两张表，常用的为一张表。
    • 场景：
      • 项目业务数据主键增多，单表的数据量达到1000或20以上
      • 优化已经解决不了性能问题（主从读写分离，查询索引）
      • IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询，连接数太多）
    • 分库分表策略
      • 垂直分库分表
        • 垂直分库，将相同业务的表分到一个数据库里
        • 垂直分表，将常用的字段单独放到一张表中
        • 特点：冷热数据分离，减少IO过度争抢，两张表互不影响
      • 水平分库分表
        • 水平分库：将一个库里的数据拆分到多个库中（按照id取模，或者范围路由）
        • 水平分表：类似于水平分表
        • 优化单个表或单个库的数据量过大而产生的性能问题，避免IO争抢并减少锁表的几率
      • 新的问题：（水平分库）
        • 分布式事务一致性问题
        • 跨节点关联查询
        • 跨节点分页，排序函数
        • 主键避重
      • 中间件：（解决水平分库出现的问题）
        • sharding-sphere
        • mycat

• 如何定位慢查询

  • 表示形式：页面加载过慢，接口压测响应时间长（超过1s）
  • 场景：
    • 聚合查询
    • 多表查询
    • 表数据量过大查询
    • 深度分页查询
  • 解决办法
    • 开源工具，Arthas（调试工具），Prometheus，Skywalking（运维工具）
    • mysql自带慢日志（/etc/my.conf）
      • slow_query_log = 1; //开启慢查询日志
      • long_query_time = 2; // 记录SQL语句超过2秒的命令

• 如何优化慢查询，如何分析

  • 通过查询语句前加explain或desc可以查询到该语句的详细信息。
    • 通过key和key_len检查是否命中索引。
    • type是这条语句的连接类型，由好到差：NULL，system（系统表），const（主键查询），eq_ref（唯一或主键），ref（索引），range（范围查询），index（索引树扫描），all（全盘扫描）。
    • extra建议判断，是否出现回表，如果出现了，可以尝试添加索引或修改返回字段。

• 表的设计优化（参考阿里开发手册《嵩山版》）
  - 选择合适的数值类型（tinyint，int bigint）
  - char定长效率高，varchar可变长度，效率低

• sql语句优化

  • 指定字段名称
  • 尽量使用union all 代替 union，union会多一次过滤，效率低
  • 避免在where子句中对字段进行表达式操作
  • join优化，能用innerjoin就不用left join和right join。内连接会对两个表进行优化，优先把小表放外面，大表放里面

• 主从复制，读写分离

  • 如果数据库的读比写更多时，为避免写操作而影响性能，可以采用读写分离架构
  • 用一个主库负责写操作，而分库负责读操作，写操作完成后将主库同步到分库中
  • 同步原理：二进制日志，记录了所有的DDL（修改和删除表字段）和DML（添加、修改和删除数据）
    

11.其他问题

• undo log 和 redo log的区别
  • 缓冲池：主内存中的一个区域，用于存储磁盘上经常操作的真实数据，在执行ACID操作时，先操作缓冲池数据，以一定的频率刷新磁盘，从而减少磁盘IO，加快处理速度。
  • 数据页：是InnoDB存储引擎磁盘管理的最小单位，每页的大小默认为16kb，页中存储的是行数据
  • 场景：当数据库宕机时，内存中的数据被清空，而操作后的数据没有同步到数据里，违背了一致性
  • redo log：记录事务提交时的物理修改，保证了事务的持久性。（物理日志）
  • undo log：回滚日志，记录数据被修改前的信息，作用包括：提供回滚和MVCC多版本并发控制（逻辑日志）
    • delete一条操作时，undo log会记录一条相反的insert操作
    • update时会记录一条相反的update操作，当执行rollback时，可以从undo log 中读取到相应的内容进行回滚
    • 这保证了事务的原子性和一致性