【java面试】MySQL篇

一、总体结构

二、优化

（一）定位慢查询

问：在mysql如何进行慢查询？

出现的情况：

• 聚合查询
• 多表查询
• 表数据量过大查询深度分页查询

具体现象:页面加载过慢、接口压测响应时间过长(超过1s)

1.1 开源工具

调试工具: Arthas
运维工具: Prometheus .Skywalking

1.2Mysql自带的慢日志查询

慢查询日志记录了所有执行时间超过指定参数(long _query_time，单位:秒，默认10秒）的所有SQL语句的日志如果要开启慢查询日志，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysgql/localhost-slow.log。

1.3 总结

（二）定位后优化

2.1 优化

问：这个SQL语句执行很慢，你是如何分析（优化）的呢？

可以采用EXPLAIN或者DESC命令获取 MySQL如何执行SELECT语句的信息。

2.2 总结

（三）索引

3.1 索引

问：了解过索引吗（什么是索引）？

索引 (index)是帮助MysQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树)，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
以二分查找为例：

3.2 索引底层数据结构——B+树

索引的底层结构是什么？ B+树

①二叉树：时间复杂度不太稳定

②红黑树：虽然保持了平衡，但是本质上也是二叉树，每个结点只有两个分支，查找效率不高

③B树：B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数(max-degree)为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key。变成了矮胖树，解决了层级过高查找效率过低的问题，但是B树效率仍没有B+树优秀

④B+树：B+ Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。非叶子结点只存储指针不存储数据，只有最底层叶子结点才会存储数据，非叶子节点的作用是导航找到数据。

• 磁盘读写代价B+树更低;
• 查询效率B+树更加稳定;
• B+树便于扫库和区间查询

3.3 总结

（四）聚簇索引、非聚簇索引

问：什么是聚簇索引？什么是非聚簇索引（二级索引）？（什么是回表查询？）

4.1 聚簇索引、非聚簇索引

讲解视频：聚簇索引和非聚簇索引的 区别

4.2 回表查询

以上面为姓名列添加索引的二级索引为例，查询“name = “Arm””,由于给name字段添加了索引，那么现在会走二级索引，找到10，但我们需要查找的是全部信息select * ,通过查询到的主键ID10到聚簇索引中区查找，最终找到所有信息。

4.3 总结

（五）覆盖索引

5.1 覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
例如：在以下第二个例子中，通过“name = “Arm””可以直接查询 到id，并直接返回id,name。在第三个例子中gender不可以一次查询直接找到，而是需要回表查询。

5.3 MYSQL超大分页

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
分页查询耗时对比：

因为，当在进行分页查询时，如果执行limit 9000000,10，此时需要MySQL排序前9000010记录，仅仅返回9000000 - 9000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路:一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

视频讲解：MYSQL深度分页如何优化？

①先根据Id排序（只查询id,减少回表），返回10条索引——>覆盖索引
②在和之前的表做关联，做一个等价查询（通过id走主键索引，只查需要的10条）
通过该过程办法，极大的优化了查询效率。

5.3 总结

（六）索引创建原则

6.1 原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据（增加用户体验)
2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

6.2 总结

（七）索引失效

7.1 索引失效的情况

1. 违背最左前缀法则
   如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:（a,b,c的联合索引包含：a，ab，abc这三种情况）
   表的情况：
   
   遵循最左前缀法则的查询：
   失效的情况：
   
   符合最左法则，但是跳跃了中间某一列，那么只能查询到符合的：
   

2. 范围查询右边的列，不能使用索引
   

3. 索引列上进行运算操作，索引也会失效
   

4. 字符串不加单引号，导致索引失效
   
   由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换造成索引失效。

5. 模糊查询，有可能导致索引失效：以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
   

7.2 总结

（八）优化经验

问： 谈一谈对sql优化的经验

8.1 表的设计优化

表的设计优化 参考:阿里开发手册《嵩山版》

1. 比如设置合适的数值(tinyint int bigint)，要根据实际情况选择
2. 比如设置合适的字符串类型(char和varchar) char定长效率高，varchar可变长度，效率稍低

8.2 SQL语句优化

1. SELECT语句务必指明字段名称（避免直接使用select * )
2. SQL语句要避免造成索引失效的写法
3. 尽量用union all代替union union会多一次过滤，效率低
   
   union all 会将两次查询的结果直接组合起来，不会删除重复的部分，union过滤重复部分。
4. 避免在where子句中对字段进行表达式操作
5. Join优化能用innerjoin 就不用left join right join，如必须使用一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join或right join，不会重新调整顺序

以该循环为例子，只需要进行三次小循环（三次连接数据库的操作）后再每次连接中执行其中操作即可。

8.3 主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。

8.4 总结

三、其他面试

（一）事务

1.1 事务特性

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

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

1.2 并发事务

问：并发事务带来哪些问题?怎么解决这些问题呢?MySQL的默认隔离级别是?
并发事务问题: 脏读、不可重复读、幻读
解决方案：隔离
隔离级别: 读未提交、读已提交、可重复读、串行化

在解决了不可重复读的基础上（事务回滚了）

1.3 解决并发事务问题——隔离

1.4 undo Log 和redo log

缓冲池(buffer pool):主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存)，以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
数据页(page):是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。页中存储的是行数据

操作时，为提高效率会首先操作内存结构中的缓冲池，操作结束后会将信息同步到磁盘中（还未同步的称为脏页），但是同步过程中会出现宕机的现象，导致无法同步，内存中数据无法保存太久，最后会消失，无法做到持久化。

1.4.1 重做日志

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file) ,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。

1.4.2 回滚日志 undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样，它是逻辑日志。
可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，
当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
undo log 可以实现事务的一致性和原子性

1.5 MVCC

问：事务的隔离性是如何保证的？
锁:排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁)
mvcc:多版本并发控制

全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突
MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

1.5.1 记录中的隐藏字段

1.5.2 undo log

• 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
• 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即册除。
• 而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部爆最早的旧记录。

1.5.3 readView

ReadView(读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的) id。当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select … lock in share mode(共享锁)，select … for update、update、insert.
delete(排他锁)都是一种当前读。
快照读
简单的select (不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed:每次select，都生成一个快照读。
Repeatable Read:开启事务后第一个select语句才是快照读的地方。
v

（二）主从同步原理

MySQL主从复制的核心就是二进制日志
二进制日志（BINLOG)记录了所有的DDL(数据定义语言）语句和DML(数据操纵语言）语句
但不包括数据查询(SELECT、SHQW）语句。

（三）分库分表

只有一个从库不够用，分库分表分担了访问压力。
分库分表的时机:

• 项目业务数据逐渐增多，或业务发展比较迅速单表的数据量达1000W或20G以后
• 优化已解决不了性能问题（主从读写分离、查询索引…)
• IO瓶颈（磁盘IO、网络lO)、CP心瓶颈（聚合查询、连接数太多)
  
  

1、垂直拆分

1.1 垂直分库

以表为依据，根据业务将不同表拆分到不同库中。

特点:

1. 按业务对数据分级管理、维护、监控、扩展
2. 在高并发下，提高磁盘IO和数据量连接数

1.2 垂直分表

以字段为依据，根据字段属性将不同字段拆分到不同表中。
拆分规则：把不常用的字段单独放在一张表，把text,blob等大字段拆分出来放在附表中

特点:

1. 冷热数据分离(经常访问的就是热数据，不常访问的就是冷数据)
2. 减少IO过渡争抢，两表互不影响

2、 水平拆分

2.1 水平分库

将一个库的数据拆分到多个库中。表结构、字段属性都是一模一样的，但是把表分在了很多个不同的地方（库）。

路由规则:

• 根据id节点取模
• 按id也就是范围路由，节点1(1-100万),节点2(100万-200万)

特点:

1. 解决了单库大数量，高并发的性能瓶颈问题
2. 提高了系统的稳定性和可用性

2.2 水平分表

将一个表的数据拆分到多个表中，同样的字段属性一模一样，但是数据存储不同。

特点:

1. 优化单一表数据量过大而产生的性能问题;
2. 避免IO争抢并减少锁表的几率;

3、分库分表新问题及其解决方案

分库之后的问题:

• 分布式事务一致性问题
• 跨节点关联查询
• 跨节点分页、排序函数
• 主键避重

解决方案：采用中间件

4、总结