MySQL 面试题 - 参考回答

​MySQL 面试题-参考回答.pdf

​

MySQL 优化

如何定位慢查询

​​

场景：聚合查询 多表查询 表数据量过大查询 深度分页查询

定位慢查询

1 使用调试工具：Arthas 运维工具：Prometheus，Skywalking

2 MySQL 自带的慢日志

MySQL 默认未开启慢查询日志，需要在配置文件中手动开启

日志在/var/lib/mysql/localhost-slow.log 中查看

#开启慢日志查询开关
slow_query_log=1
#超出该时间为慢查询
long_query_time=2

SQL 语句的分析​​

对于聚合查询，多表查询，表数据过大查询都可以通过分析 SQL 执行计划来分析 SQL 执行慢的原因

-添加EXPLAIN或DESC都可以分析
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

​​

字段

possible_key 当前 sql 可能使用到的索引

key 当前 sql 实际命中的索引

key_len 索引占用的大小

extra 额外的优化建议，如果是 Using index condition 的话索引有优化的空间

​​

type sql 连接的类型 性能由好到坏 NULL(查询没涉及到表）,system（查询的是 mysql 内置的表)，const(根据主键查询），eq_ref（主键索引查询或唯一索引查询，只能返回一条数据）,ref（索引查询，返回多条数据）,range（范围查询）,index（遍历全索引树）,all（全盘扫描）

type 类型如果是 index 或 all，这个语句需要优化

什么是索引

​​

概念

索引是帮助 MySQL 高效获取数据的数据结构，特定查找算法的数据结构（B+ 树）

底层数据结构

B+ 树 Mysql 的默认存储引擎 InnoDB 使用 B+ 树实现

B+ 树与 B 树对比：​​

1 B+ 树磁盘读写代价低 2 查询效率 B+ 树更稳定（查询每个数据路径大小都一致）

3 B+ 树便于扫库和区间查询

4 B+ 树，非叶节点不包含该关键字对应记录的存储地址，可以使一个磁盘块包含更多关键字，使 B+ 树的阶更大，树更矮，读磁盘次数少，查找快

什么是聚簇索引，什么是非聚簇索引（二级索引又称为非聚集索引）

​​

概念

1 聚集索引（​必须有，有且只有一个​）

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

聚集索引的选取规则：

存在主键，主键索引就是聚集索引

不存在主键，使用第一个 UNIQUE 索引作为聚集索引

如果表中没有主键，或者没有合适的唯一索引，InnoDB 会自动生成一个 rowid 作为隐藏的聚簇索引

2 二级索引（​可以存在多个​）（单独给字段创建的索引大部分为二级索引）

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以有多个

​​

3 回表查询

​​

‍

什么是覆盖索引

​​

**概念：**查询使用到了索引，并且需要返回的列，在该索引中已经可以全部找到

​​

覆盖索引可以用于解决 MySQL 超大分页问题

​​

使用覆盖索引加子查询优化超大分页问题

select * 
from tb_sku t ,
  (Select id from tb_sku order by limit 9000000,10) a
where t.id=a.id;

索引的创建索引有哪些

1 针对数据量较大，且查询比较频繁的表建立索引

2 针对常作为查询条件，排序，分组操作的字段建立索引

3 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引

5 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

6 要控制索引的数量，索引并不是越多越好，索引越多，维护索引结构的代价也越大，会影响增删改的效率

7 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束，当优化器知道每列是否包含 NULL 值时，可以更好确定哪个索引最有效用于查询

‍

索引失效问题

1 违反最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则，查询从索引的最左列开始，并且不跳过索引中的列，匹配最左前缀法则，如果跳过的话可能会导致索引失效，如果符合最左原则，但是出现跳跃某一列，只有最左列索引生效

2 范围查询右边的列不能使用索引，如果使用，索引会失效

​​

3 不要在索引列上进行运算操作，否则会失效

​​

4 字符串不加单引号，造成索引失效

​​

5 以 % 开头的 like 模糊查询，索引失效，如果仅仅是尾部模糊匹配，索引不会失效，头部模糊匹配，索引会失效

​​

对 SQL 优化的经验

表设计的优化

我们项目主要使用的是阿里卡法手册（嵩山版）

1.设置合适的数值（tinyint，int，bigint），根据实际情况选择

2.设置合适的字符串类型（char，varchar）char 效率高，varchar 可变长度，效率低

索引优化（参考索引创建原则和索引失效）

SQL 语句优化

​​

1.SELECT 语句务必指名字段名称（避免使用 select*） --尽量使用覆盖索引，避免回表查询

2.SQL 语句要避免造成索引失效的写法

3.尽量使用 union all 代替 union union 会多一次过滤，效率低

4.避免在 where 子句对字段进行表达式操作（在 where 中对表达式操作可能导致 SQL 索引失效）

5.Join 优化 能用 innerjoin 就不用 left join right join，如必须使用要以小表为驱动，内连接会对两个表进行优化，优先把小表放外边，大表放里边。 innerjoin 默认会把小表放外边，大表放里面，而 left join 和 right join，已经人为确定好表顺序

主从复制，读写分离优化

如果数据库的使用场景读多写少时，为了避免写操作造成的性能影响，可以采用读写分离的架构，主库进行写，从库进行读

​​

分库分表（后续讲）

MySQL 其他面试题

事务的特性是什么

概念：

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

特性：（ACID)

原子性（Atomicity） 事务是不可分割的最小操作单元，要么同时成功，要么同时失败

一致性（Consistency） 事务完成时，所有数据要保持一致状态

隔离性（Isolation） 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境运行

持久性（Durability） 事务一旦提交或回滚，它对数据库的改变是永久的

并发事务带来的问题？怎么解决这些问题？MySQL 的默认隔离级别是？

​​

并发事务问题

1 脏读 一个事务读到了另一个事务没有提交的数据

​​

2 不可重复读

一个事务先后读取同一个记录，两次读取的数据不同

​​

3 幻读（在解决了不可重复读条件下发生的）

一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据存在了，出现幻读

​​

解决并发事务的问题

对事务进行隔离

事务隔离级别越往下级别越高，数据越安全，性能越差

​​

undolog 和 redo log 的区别

​​

概念：

缓冲池（buffer pool）：缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据，缓冲池中没有，从磁盘中加载，并以一定频率刷新到磁盘，从而减少 IO，加快处理速度

数据页: 是 InnoDB 存储引擎磁盘管理的最小单位，每个页默认大小为 16KB 后缀名为.ibd

redo log

​​

重做日志，记录的是事务提交时对物理页的物理修改，实现事务的持久化

日志文件由两部分组成：重做日志缓冲（redo log buffer）和重做日志文件（redo log file），前者在内存中，后者在磁盘中，redo log 日志文件在磁盘中有两份是循环进行写的

​​

undo log（解决事务的一致性和原子性）

回滚日志，用于记录数据被修改前的信息，作用是：提供回滚和 MVCC（多版本并发控制），undo log 记录的是逻辑日志（是一条一条的指令）

当执行 delete，update，insert 时，会在 log 中记录一条相反的指令，方便回滚

执行 rollback 时，可以从 undo log 中的逻辑记录中读取到内容进行回滚

事务的隔离性如何保证（排他锁和 MVCC 来共同保证）

锁：排他锁（如果一个事务获取了一个数据行的排他锁，其他事务就不能获取该行的其他锁，包括排他锁）

MVCC （Multi-Version Concurrency Control，多版本并发控制） 维护一个数据的多个版本，使读写操作没有冲突

1.MVCC 依赖于隐藏字段，undo log 日志，readView

DB_TRX_ID 事务 ID，一个事务在开启时，会默认分配一个 ID，是自增的唯一的

​​

2.undo log 版本链

​​

3.readView

ReadView 是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id

MySQL 的主从同步原理

​​

​​

核心是二进制日志，二进制日志（BINLOG）中记录了所有的 DDL（数据定义语言）和 DML（数据操纵语言）语句，但不包括数据查询语言（SELECT ,SHOW)

​​

MySQL 中的分库分表问题

​​

主从解决的是访问的压力 分库分表解决的是存储的压力

1 分库分表的时机

业务数据逐渐增多，业务发展比较迅速 （例如单表的数据量达到 1000w 或 20G）

优化已经解决不了性能问题，数据增多情况下

IO 瓶颈（磁盘 IO，网络 IO），CPU 瓶颈（表太多情况下聚合查询会占用大量 CPU 用户太多连接数太多，也会占用大量 CPU）

​​

2 分库分表的策略

垂直拆分

垂直分库 以表为依据，根据业务将不同表拆分到不同的库中（例如不同的微服务对应不同的表）

​​

特点：

1 按照业务对数据分级管理，维护，监控，扩展

2 在高并发下，提高磁盘 IO 和数据量连接数

垂直分表

拆分规则： 1 把不常用的字段单独拆分成一张表 2 把 text，blob 大文本拆分成一张表

特点：

1 冷热数据分离，展示数据时，只展示基本信息，只有用户感兴趣才会点进去展示描述信息

2 提高 IO 的效率，不用经常性传输大数据

​​

水平拆分

水平分库

将一个库的数据拆分到多个库中（解决的是一个库装不下的问题）

可以通过一些算法将查找的索引路由到某一台数据库中

特点：

1 解决了单库大数量，高并发的性能瓶颈问题

2 提高了系统的稳定性和可用性

​​

水平分表

​​

将一个表中的数据拆分到多个表中（这些表可以位于一个库中）

同样查找某一条数据时，通过算法路由到指定数据库

特点：

1 优化单一表数据量过大产生的性能问题

2 避免 IO 争抢减少锁表的几率，减少阻塞的几率

‍

分库出现的问题**：**

1 分布式事务一致性问题（一个服务操作多个库，涉及到同时提交或者回滚）

2 跨结点关联查询 3 跨节点分页，排序函数 4 主键避重

‍

解决此问题的技术：使用分库分表的中间件 sharding-sphere 或 Mycat

​​

‍