MySQL面试题

1.在Mysql中，如何定位慢查询？

• 1. 介绍业务场景，说明压测结果比较慢
• 2. 采用运维工具Skywalking，检测出哪个接口，最终因为sql问题
• 3. 在mysql中开启慢日志查询，设置为两秒，一旦sql执行超时就会记录到日志

2.一个SQL语句执行得很慢，如何分析？

• 通过key和key_len检查是否命中了索引
• 通过type字段查看sql是否有进一步的优化空间，是否存在全索引或全盘扫描
• 通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或者修改返回字段来修复

3.了解过索引吗，什么是索引？

• 索引（index）是帮助MySQL高效获取数据的数据结构（有序）
• 提高数据索引效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，减低CPU消耗

4.索引的底层数据结构了解过吗？

• MySQL的InnoDB引擎采用的B+树的数据结构来存储索引
• 1. 阶数更多，路径更短
• 2. 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
• 3. B+树便于扫库和区间查询，叶子节点是一个双向链表

5.B数和B+树的区别是什么？

• 在B树中非叶子节点和叶子节点都会存储数据，而B+树的所有数据都会出现在叶子节点上，在查询的时候，B+树查找效率更加稳定
• 在进行范围查询时，B+树效率更高，因为B+树都在叶子节点存储，并且叶子节点是一个双向链表

6.什么是聚簇索引？什么是非聚簇索引（二级索引）？

• 聚簇索引（聚集索引）：数据和索引放在一起，B+树的叶子节点保存了整行数据，有且只有一个
• 非聚簇索引（二级索引）：数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个

7.什么是回表查询？

• 通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

8.什么是覆盖索引？

• 覆盖索引：指查询使用了索引，返回的列，必须在索引中全部找到
• 1. 使用id查询，直接聚集索引查询，一次索引扫描，直接返回数据，性能高
• 2. 如果返回的列中没有创建索引，有可能触发回表查询，尽量避免使用select *

9.Mysql超大分页怎么处理？

• 数据量比较大的时，使用limit分页查询，并且需要对数据进行排序，效率非常低
• 解决方案：覆盖索引和子查询来解决
  • 1.先分页查询数据的id字段
  • 2.确定id之后，再用子查询来过滤
  • 3.只查询这个id列表中的数据接可以了

10.索引创建的原则有哪些？

• 1. 数据量大，且查询比较频繁的表
• 2. 常作为查询条件，排序，分组的字段
• 3. 尽量联合索引
• 4. 要控制索引的数量
• 5. 字段内容区分度高
• 6. 内容较长，使用前缀索引
• 7. 如果索引不能存储null值，请在创建表时使用not null约束它

复合索引 /多列索引 /联合索引 /组合索引，是在多个列上创建的索引，一个意思，不同叫法。

11.什么情况下索引会失效？

• 1. 违反最左前缀法则
• 2. 范围查询右边的列，不能使用索引
• 3. 不要在索引列上进行运算操作，索引将失效
• 4. 字符串不加单引号，造成索引失效（类型转换）
• 5. 以%开头的like模糊查询，索引失效

12.最左前缀法则是什么？

• 最左前缀法则：指的是查询从索引的最左前列开始，并且不跳过索引中的列
• 1. 匹配最左前缀法则，走索引
• 2. 违反最左前缀法则 ， 索引失效
• 3. 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效

13.谈谈你对SQL优化的经验？

• 1. 表的设计优化，数据类型的选择
• 2. 索引优化吗，索引的创建原则
• 3. sql语句优化，避免索引失效等
• 4. 主从复制，读写分离，不让数据的写入，影响读操作
• 5. 分库分表

14.创建表的时候是如何优化的？

• 这我们主要参考阿里开发手册《嵩山版》，比如在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint，int，bigint这些类型，要结合实际情况选择，如果是字符串类型，熬结合存储的类型来选择char和varchar或者text类型

15.使用索引的时候是如何优化的？

• 【参考索引创建原则，索引失效的场景】

16.平时对sql语句有什么优化？

• select务必指明字段名称，不能直接使用select*
• 注意sql语句避免索引失效的写法，
  • 如果是聚合查询，尽量用union all 代替union，union会多一次过滤，效率比较低。
  • 如果是表关联的话，尽量使用inner join，不要使用left join，right join，如必须使用尽可能使用小表驱动，降低对数据库连接次数

17.事务是什么？

• 事务是一组操作的集合，他是不可分割的工作单位
• 事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

18.事务的特性是什么？

• 1. 原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 2. 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致的状态
• 3. 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 4. 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

结合转账案例说明
ACID，分别指的是：原子性，一致性，隔离性，持久性；我举个例子，A向B转账500，转账成功，A扣除500，B增加500。原子性体现在要么都成功要么都失败。一致性体现在在转账过程中，数据要一致，A扣除了500，B必须增加500。隔离性体现在A向B转账，不能受其他事务干扰。持久性体现在，事务提交后要把数据持久化

19.并发事务带来哪些问题？怎么解决？

• 带来的问题：
  • 脏读：一个事务读到另一个事务还没提交的数据
  • 不可重复读：一个事务先后读到同一条记录，但两次读取的数据不同
  • 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”
• 解决方案：对事务进行隔离

20.MySQL的默认隔离级别是什么？

• read uncommitted 未提交读
• read committed 读已提交 解决：幻读
• repeatable read 可重复读 解决：不可重复读，幻读
• serializable 串行化 解决：脏读，不可重复读，幻读

21.undo log 和redo log的区别是什么？

• redo log：重做日志，记录的是数据页的物理变化，服务宕机可以用来同步数据
• undo log：回滚日志，记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
• redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

22.事务的隔离性怎么保证（解释一下MVCC）？

• MVCC：MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突
• 隐藏字段：
  • trx_id（事务id），记录每一次操作的事务id，是自增的
  • roll pointer（回接指针），指向上一个版本的事务版本
• undo log:
  • 回滚日志，存储老版本数据
  • 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过 roll pointer 指针形成一个链表
• readView 解决的是一个事务查询选择版本的问题
  • 根据 readView 的匹配规则和当前的一些事务id判断该访问那个版本的数据
  • 不同的隔离级别快照读是不一样的，最终的访问的结果不一样
    • RC：每一次执行快照读时生成 ReadView
    • RR：仅在事务中第一次执行快照读时生成 ReadView ，后续复用

23.主从同步原理是什么？

• 原理：MySQL主从复制的核心就是二进制日志binlog(DDL(数据定义语言)语句和DML(数据操纵语言)语句)
• 1. 主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中
• 2. 从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay Log
• 3. 从库重做中继日志中的事件，将改变反映它自己的数据

24.你们项目用过分库分表吗？

• 业务介绍（分库分表时机）：

  • 项目业务数据多，业务发展迅速，但表数据达到1000万或20G以后
  • 优化已经解决不了性能问题（主从读写分离，索引查询等）
  • IO瓶颈（磁盘IO、网络IO），CPU瓶颈（聚合查询，连接数太多）

• 拆分策略：

  • 水平分库：将一个库的数据拆分到多个库中
    • 解决海量数据存储和高并发问题
    • 提高系统稳定性和可用性
  • 水平分表：将一个表的数据拆分到多个表中
    • 优化单一表数据量过大产生的性能问题
    • 避免IO争抢并减少锁表的几率
  • 垂直分库：以表为依据，根据业务将不用表拆分到不同的库中
    • 按业务对数据分级管理，维护，监控，扩展
    • 在高并发下，提高磁盘IO和网络连接数
  • 垂直分表：根据字段属性将不同字段拆分到不同表中
    • 冷热数据分离
    • 减少IO过度争抢，多表互不影响