Java面试专题-MySQL篇优化01-05
MySQL
flowchart TD
A[优化]
B[定位慢查询]
C[SQL执行计划]
D[索引]
E[SQL优化经验]
F[存储引擎<br>索引底层数据结构<br>聚簇和非聚簇索引<br>索引创建原则<br>索引失效场景]
A --> B
A --> C
A --> D
A --> E
D --> F
flowchart TD
A[其他面试题]
B[事务相关]
C[主从同步原理]
D[分库分表]
E[事务特性<br>隔离级别<br>MVCC]
A --> B
A --> C
A --> D
B --> E
一、优化
1.如何定位慢查询
-
在MySQL中，如何定位慢查询？
- 聚合查询
- 多表查询
- 表数据量过大查询
- 深度分页查询
表象：页面加载过慢、接口压测响应时间过长（超过1秒）
方案一：开源工具
- 调试工具：Arthas
- 运维工具：Prometheus、Skywalking
方案二：MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。
（默认为开启）如果要开启慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
配置完毕后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log。
总结
- 如何定位慢查询?
-
介绍一下当时产生问题的场景(我们当时的一个接口测试的时候非常的慢,压测的结果大概5秒钟)
-
我们系统中当时采用了运维工具(Skywalking),可以监测出哪个接口,最终因为是sql的问题
-
在mysql中开启了慢日志查询,我们设置的值就是2秒,一旦sql执行超过2秒就会记录到日志中(调试阶段)
2.SQL语句执行得很慢，如何分析？
可以用EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息
语法：
- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
展示这条SQL执行的情况
-
possible_keys：当前sql可能会使用到的索引
-
key：当前sql实际命中的索引 —— 通过它们两个查看是否可能会命中索引
-
key_len：索引占用的大小 —— 通过它们两个查看是否可能会命中索引
-
Extra：额外的优化建议
Extra 含义 Using where; Using Index 查找使用了索引,需要的数据都在索引列中能找到,不需要回表查询数据 Using index condition 查找使用了索引,但是需要回表查询数据 -
type：这条sql的连接的类型,性能由好到差为NULL（没有使用到表，很少见）、system、const、eq_ref、ref、range、index、all
-
system：查询系统中的表（查询MySQL中内置的表，也很少用到）
-
const：根据主键查询（现在这个就是）
-
eq_ref：主键索引查询或唯一索引查询
-
ref：索引查询
-
range：范围查询
-
index：索引树扫描
-
all：全盘扫描
要是到后面这两个，就得优化了
-
总结
- 那这个SQL语句执行很慢,如何分析呢?
可以采用MySQL自带的分析工具 EXPLAIN
- 通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)
- 通过type字段查看sql是否有进一步的优化空间,是否存在全索引扫描或全盘扫描
- 通过extra建议判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修复
3.索引概念及索引底层数据结构
-
了解过索引吗？（什么是索引）
索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外,数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
-
索引的底层数据结构了解过吗？
B+树
为什么用B+树？
数据结构对比
MySQL默认使用的索引底层数据结构是B+树。在聊B+树之前，我们先聊聊二叉树和B树
以一棵最大度数（max-degree）为5（5阶）的b-tree为例,那这个B树每个节点最多存储4个key。
B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
非叶子节点只存储指针不存储数据
- 磁盘读写代价B+树更低；
- 查询效率B+树更加稳定；
- B+树便于扫库和区间查询。
总结
-
了解过索引吗?(什么是索引)
-
索引(index)是帮助MySQL高效获取数据的数据结构(有序)
-
提高数据检索的效率,降低数据库的IO成本(不需要全表扫描)
-
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
-
索引的底层数据结构了解过嘛?
MySQL的InnoDB引擎采用的B+树的数据结构来存储索引
- 阶数更多,路径更短（他是一个矮胖树，寻找的层级比较短，性能比较高）
- 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据
- B+树便于扫库和区间查询，叶子节点是一个双向链表
4.聚簇索引和非聚簇索引、回表查询
-
什么是聚簇索引什么是非聚簇索引?
-
什么是聚集索引什么是二级索引(非聚集索引)
-
什么是回表?
聚簇索引和非聚簇索引
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引（Clustered Index） | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引（Secondary Index） | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则：
-
如果存在主键,主键索引就是聚集索引。
-
如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
-
如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
回表查询
select * from user where name = 'Arm';
先通过二级索引拿到对应的主键值，再通过主键值到聚集索引中找到整行的数据
总结
-
什么是聚簇索引什么是非聚簇索引?
-
聚簇索引(聚集索引)：数据与索引放到一块，B+树的叶子节点保存了整行数据,有且只有一个
-
非聚簇索引(二级索引)：数据与索引分开存储，B+树的叶子节点保存对应的主键,可以有多个
-
-
知道什么是回表查询嘛?
- 通过二级索引找到对应的主键值,到聚集索引中查找整行数据,这个过程就是回表查询
