面试笔记——MySQL（优化篇：定位慢查询、SQL执行计划、索引、SQL优化）

发布于：2024-03-28 ⋅ 阅读:(17) ⋅ 点赞:(0)

定位慢查询

在MySQL应用中，慢查询 通常指的是执行时间超过一定阈值的查询语句。这个阈值通常由管理员或开发人员根据具体情况设置，一般是以毫秒为单位。慢查询可能会影响系统性能和用户体验，因此需要及时识别和优化。
表象： 页面加载过慢、接口压测响应时间过长（超过1s）
以下情景可能会导致慢查询：

  • 聚合查询
  • 多表查询
  • 表数据量过大查询
  • 深度分页查询
定位慢查询的方法

方案一：工具
调试工具：Arthas；
运维工具：Prometheus、Skywalking；
方案二：MySQL自带慢日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关，值为0表示不开启慢日志查询
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log，如图：
在这里插入图片描述
注意：使用慢日志会损失一部分性能，因此一般是在调试阶段开启慢日志的功能，在生产环境下关闭。

SQL执行计划

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

# 直接在select语句前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

在这里插入图片描述

  • possible_keys 当前sql可能会使用到的索引
  • key 当前sql实际命中的索引
  • key_len 索引占用的大小（ps：可以通过key和key_len查看是否可能会命中索引。）
  • Extra 额外的优化建议，比如某个索引在使用的过程中是否使用了回表，下图中展示了两个参考信息：
    在这里插入图片描述
  • type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all
    • system：表示当前语句查询的表是MySQL系统中内置的表，（不常用）；
    • const：根据主键查询，（使用频率高）；
    • eq_ref：主键索引查询或唯一索引查询，只能返回一条数据；
    • ref：索引查询，查询结果可能是多条数据；
    • range：范围查询，（应用中的最低要求）；
    • index：索引树扫描；
    • all：全盘扫描；
    • 若type的值是index或all，则说明此条SQL语句需要优化。

索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库还维持着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种结构就是索引。下图展示了以二叉树为索引，查找年龄为20的结点过程：
在这里插入图片描述
B-Tree（B树） 是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key：
在这里插入图片描述

B+Tree 是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。如图所示：
在这里插入图片描述
在B+树中，非叶子结点只存储指针不存储数据，数据存储在叶子节点中。

B树与B+树对比:

  1. 磁盘读写代价B+树更低（因为非叶子结点不存储任何的数据，它们只用存储指针，相对来说存储压力更低）；
  2. 查询效率B+树更加稳定（查找时，最终都是从叶节点获取数据，因此效率比较稳定）；
  3. B+树便于扫库和区间查询（叶节点之间通过双向指针进行连接，在进行范围查询时更加方便）。

索引的基本概念问题

  1. 什么是索引
    • 索引（index）是帮助MySQL高效获取数据的数据结构(有序)
    • 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
    • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
  2. 索引的底层数据结构是什么？
    • MySQL的InnoDB引擎采用的B+树的数据结构来存储索引
    • 阶数更多，路径更短
    • 磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据
    • B+树便于扫库和区间查询，叶子节点是一个双向链表
聚集索引与二级索引（非聚集索引）
分类 含义 特点
聚集索引(Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有,而且只有一个
二级索引(Secondary Index) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

如下图所示，注意——聚集索引的叶节点存储的是整行数据，二级索引的叶节点存储的是数据的主键值，在：
在这里插入图片描述

回表查询
回表查询：通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表。如图所示：
在这里插入图片描述
回表查询可能会影响查询性能，因为它需要额外的IO操作和访问磁盘，尤其是在大型表或者索引列宽度较大的情况下。

覆盖索引

覆盖索引 是指一个索引包含了查询语句所需的所有列，从而使得查询可以直接从索引中获取所需的数据，而无需回表查询。覆盖索引通常用于查询语句的列与索引列完全匹配的情况。如图：
在这里插入图片描述
查询语句：select * from tb_user where id = 2 ;
在这里插入图片描述
查询语句：select id, name from tb_user where name = ‘Arm’ ;
在这里插入图片描述
查询语句：select id,name,gender from tb_user where name = ‘Arm’ ;
在这里插入图片描述
使用覆盖索引处理MySQL超大分页：
在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比：
在这里插入图片描述
因为，当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大 。

解决方案：覆盖索引 + 子查询

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

在执行select id from tb_sku order by id limit 9000000,10时，可以使用覆盖索引，性能更高；然后与之前的表做等价查询。

创建索引的原则

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

索引失效

索引失效指的是数据库查询执行时，无法有效利用现有索引进行查询优化，从而导致性能下降或无法利用索引的情况。

以下情况会导致索引失效：

  1. 违反最左前缀法则

    • 最左前缀法则（或最左前缀匹配）指的是当一个查询包含了多列的复合索引时，查询条件查询从索引的最左前列开始，并且不跳过索引中的列。

    • 举例说明，假设有一个复合索引 (col1, col2, col3)，那么以下查询可以利用该索引进行查询优化：

      • WHERE col1 = 'value'
      • WHERE col1 = 'value' AND col2 = 'value2'
      • WHERE col1 = 'value' AND col2 = 'value2' AND col3 = 'value3'

      例如，当前有表tb_seller，它的索引情况为：
      在这里插入图片描述
      情况一——违法最左前缀法则 ， 索引失效：
      在这里插入图片描述
      情况二——如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效（图中只有status索引生效）：
      在这里插入图片描述

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

    同上例，当status使用范围查询时，address是失效的： 在这里插入图片描述
    查询结果与只用name和status索引的效果一样：
    在这里插入图片描述

  3. 不要在索引列上进行运算操作， 索引将失效

    同例1，若对name进行了运算操作后，索引就失效了：
    在这里插入图片描述

  4. 字符串不加单引号，造成索引失效
    同例1，第2条查询语句未加单引号， MySQL的查询优化器，会自动的进行类型转换，造成索引失效：
    在这里插入图片描述

  5. 以%开头的Like模糊查询，索引失效。 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
    同例1， 在这里插入图片描述

SQL优化

表的设计优化

关于类型的选择——需要根据存储的内容来选择合适的类型，如：

  • 设置合适的数值（tinyint int bigint），
  • 设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低
索引优化

参考上文——索引优化创建原则和索引失效

SQL语句优化
  • SELECT语句务必指明字段名称（避免直接使用select * ，从而避免回表查询）
  • SQL语句要避免造成索引失效的写法
  • 尽量用union all代替union union会多一次过滤，效率低
  • 避免在where子句中对字段进行表达式操作
  • Join优化：能用inner join 就不用left join， right join，如必须使用 一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序
    • 以小表为驱动：在执行连接操作时，应该将较小的表作为驱动表（驱动表是在连接操作中被选择的第一个表），这样可以减少处理的数据量，提高查询性能。
    • 内连接的优化：内连接会对两个表进行优化，因为它只返回两个表中匹配的行，而不需要额外的处理。所以，如果使用 INNER JOIN，推荐将较小的表放在外层，因为外层的表会优先进行匹配。
    • left join 或 right join 不会重新调整顺序：LEFT JOIN 和 RIGHT JOIN 会按照查询中指定的顺序进行操作，并不会重新调整表的顺序。因此，如果使用 LEFT JOIN 或 RIGHT JOIN，需要明确考虑表的顺序，以确保查询结果符合预期。
主从复制、读写分离

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

分库分表

当数据量很大（例如超过五百万）的时候，可以采取分库分表优化。
见面试笔记——MySQL（主从同步原理、分库分表）。

本文含有隐藏内容，请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布