MySQL

一、优化

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。

总结

• 如何定位慢查询?

1. 介绍一下当时产生问题的场景(我们当时的一个接口测试的时候非常的慢,压测的结果大概5秒钟)

2. 我们系统中当时采用了运维工具(Skywalking),可以监测出哪个接口,最终因为是sql的问题

3. 在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树

B-Tree，B树是一种多叉路衡查找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。

以一棵最大度数（max-degree）为5（5阶）的b-tree为例,那这个B树每个节点最多存储4个key。

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

非叶子节点只存储指针不存储数据

B树与B+树对比：

1. 磁盘读写代价B+树更低；
2. 查询效率B+树更加稳定；
3. 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+树的叶子节点保存对应的主键,可以有多个

• 知道什么是回表查询嘛?

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