高手都是如何做 Mysql 慢 SQL 优化网站首页 其他

高手都是如何做 Mysql 慢 SQL 优化

激流丶 2024-10-07 00:01:03

简介高手都是如何做 Mysql 慢 SQL 优化

tip：作为程序员一定学习编程之道，一定要对代码的编写有追求，不能实现就完事了。我们应该让自己写的代码更加优雅，即使这会费时费力。

?? 推荐：体系化学习Java（Java面试专题）

文章目录

1、如何定位慢 SQL 问题
2、如何针对慢 SQL 优化
3、建索引需要遵循什么原则
4、Mysql 索引的原理
- 4.1、索引介绍和原理
- 4.2、磁盘 IO 与预读
5、详解 B+ 树

在这里插入图片描述

1、如何定位慢 SQL 问题

定位慢SQL问题可以通过以下几种方法：

1.1、启用MySQL慢查询日志

在MySQL配置文件中开启慢查询日志功能，可以记录执行时间超过一定阈值的SQL语句。

# 开启MySQL慢查询日志可以通过以下步骤实现：

# 1. 编辑MySQL配置文件my.cnf（或my.ini），添加以下配置项：
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
# 其中， `slow_query_log` 表示是否开启慢查询日志， `slow_query_log_file` 表示慢查询日志保存的文件路径， `long_query_time` 表示执行时间超过多少秒的SQL语句会被记录到慢查询日志中。

# 2. 重启MySQL服务，使配置生效。

# 3. 查看慢查询日志，可以使用以下命令：
sudo tail -f /var/log/mysql/mysql-slow.log
# 该命令可以实时查看MySQL慢查询日志，可以按Ctrl+C退出。

# 4. 在慢查询日志中查找慢SQL语句，可以根据执行时间和执行次数等信息进行排序和过滤，找出执行时间较长的SQL语句，进一步分析和优化。

# 注意：开启慢查询日志会对MySQL性能产生一定的影响，因此建议在调试和优化阶段开启，调试结束后关闭。

1.2、分析慢查询日志

使用MySQL提供的工具或第三方工具，对慢查询日志进行分析，可以查看执行时间较长的SQL语句以及执行时间、执行次数等相关信息。

分析MySQL慢查询日志的工具有很多，以下是一些常用的工具：

mysqldumpslow：这是MySQL自带的一个工具，可以用来解析慢查询日志文件，并按照执行时间、执行次数等排序，方便查找慢SQL语句。
pt-query-digest：这是Percona Toolkit中的一个工具，可以对慢查询日志进行分析，并生成报告，包括执行时间、执行次数、索引使用情况等信息，帮助定位慢SQL问题。可以使用以下命令安装：
sudo apt-get install percona-toolkit
MySQL Enterprise Monitor：这是MySQL官方提供的一款性能监控工具，可以实时监控MySQL的性能指标，包括慢查询、锁等问题，帮助发现和解决MySQL性能问题。
VividCortex：这是一款云端的MySQL性能监控工具，可以实时监控MySQL的性能指标，包括慢查询、锁等问题，提供可视化的报告和分析功能，帮助定位和解决MySQL性能问题。

具体哪种工具主要取决于具体需求和使用场景，可以根据实际情况选择合适的工具。

1.3、使用EXPLAIN分析查询计划

使用EXPLAIN语句可以查看MySQL执行SQL语句的查询计划，包括使用了哪些索引、表的连接方式等信息。可以通过分析查询计划，找出影响查询性能的因素。

EXPLAIN是MySQL的一个关键字，用于分析查询语句的执行计划，可以帮助我们优化查询性能。使用EXPLAIN需要在查询语句前加上EXPLAIN关键字，例如：

EXPLAIN SELECT * FROM table WHERE id = 1;

执行以上语句后，MySQL会返回一个执行计划，包括查询使用的索引、查询类型、扫描行数等信息，例如：

| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | table | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

其中，各列的含义如下：

id：查询的标识符，每个查询都有一个唯一的标识符。
select_type：查询的类型，包括SIMPLE（简单查询）、PRIMARY（主键查询）、UNION（联合查询）等。
table：查询的表名。
type：查询的类型，包括const（常量查询）、eq_ref（唯一索引查询）、ref（非唯一索引查询）、range（范围查询）等。
possible_keys：查询可能使用的索引。
key：查询实际使用的索引。
key_len：索引使用的长度。
ref：索引使用的列。
rows：扫描的行数。
Extra：额外的信息，例如Using index表示使用了索引优化查询。

通过分析EXPLAIN的结果，可以判断查询语句的性能瓶颈所在，例如是否使用了索引、是否存在全表扫描等问题，进而优化查询语句，提高查询性能。

1.4、使用性能分析工具

MySQL提供了一些性能分析工具，例如 MySQL Performance Schema 和 MySQL Enterprise Monitor 等，可以实时监控MySQL的性能指标，帮助定位慢SQL问题。

MySQL Performance Schema 是 MySQL 5.5.3 及以上版本中的一个特性，用于收集 MySQL 实例的性能数据，包括 SQL 语句执行时间、锁等待时间、IO 操作等等。启用 Performance Schema 可以帮助我们更好地了解 MySQL 实例的性能瓶颈，从而进行优化。而 MySQL Enterprise Monitor 是 MySQL 提供的一个监控和管理工具，需要下载安装的，这里我就不做介绍了，感兴趣的同学可以去下载安装下，我这里重点介绍下 MySQL Performance Schema。

以下是在 MySQL 5.7 中启用 Performance Schema 的步骤：

修改 my.cnf 配置文件，添加以下配置项：

[mysqld]
performance_schema=ON

重启 MySQL 服务，使配置生效。
连接到 MySQL 实例，使用以下命令检查 Performance Schema 是否启用：c

SHOW VARIABLES LIKE 'performance_schema';

如果返回值为 ON，则表示 Performance Schema 已经启用。

使用以下命令查看 Performance Schema 中的事件：

SELECT * FROM performance_schema.events_statements_summary_by_digest;

以上命令将返回 Performance Schema 中所有 SQL 语句的执行时间、执行次数、平均执行时间等信息。

注意：启用 Performance Schema 后会对 MySQL 实例的性能产生一定的影响，因此在生产环境中需要谨慎使用。可以根据实际情况选择启用或禁用 Performance Schema。

2、如何针对慢 SQL 优化

优化 SQL 语句的方法包括以下几个方面：

添加索引：索引是提高 SQL 查询效率的重要手段。通过添加索引，可以加快查询速度，减少全表扫描的时间。但是，索引也会占用额外的磁盘空间和内存，过多的索引会影响数据库性能。因此，在添加索引时需要根据实际情况进行分析和优化。
修改查询条件：通过修改查询条件，可以减少查询数据的数量，从而提高查询效率。例如，可以添加限制条件、修改排序方式、合并查询语句等。
分解查询语句：如果查询语句比较复杂，可以考虑将查询语句分解为多个简单的查询语句。通过分解查询语句，可以减少查询数据的数量，提高查询效率。
优化子查询：子查询是查询效率较低的一种查询方式。如果查询中包含子查询，可以考虑将子查询转化为连接查询或者其他方式。
避免使用通配符：通配符查询效率较低，应该尽量避免使用通配符查询。如果必须使用通配符查询，可以通过添加索引和限制查询范围等方式来提高查询效率。
使用优化器：MySQL 自带的优化器可以自动优化查询语句，提高查询效率。可以通过设置优化器参数来调整优化器的行为，从而达到优化查询效率的目的。

需要注意的是，在优化 SQL 语句时，应该根据实际情况进行分析和优化，避免过度优化和不必要的优化。同时，优化 SQL 语句需要进行测试和验证，确保优化后的 SQL 语句能够正确地执行，并且性能得到了提升。

其实在生产过程中，我们通过设置一个好的索引就能解决大多数慢 SQL，如果索引也优化不了了，那就只能业务上做拆分了，通过代码去优化，在大数据场景下冗余、物化视图、预查询、缓存等等手段也可以用于解决查询慢的问题。

3、建索引需要遵循什么原则

3.1、建索引需要遵循以下原则：

索引应该被建立在经常用于查询的列上。通过建立索引可以加快查询速度，但是过多的索引会影响数据库的性能，因此需要根据实际情况进行分析和优化。
索引应该被建立在唯一性较高的列上。例如，主键列和唯一性约束列都是适合建立索引的列。
索引应该被建立在数据量较大的表上。对于数据量比较小的表，建立索引的效果并不明显。
索引应该被建立在经常被用于连接的列上。例如，连接两个表的列应该被建立索引，可以加快连接操作的速度。
索引应该被建立在经常被用于排序和分组的列上。通过建立索引可以加快排序和分组操作的速度。
避免建立过多的索引。过多的索引会占用额外的磁盘空间和内存，影响数据库的性能。因此，需要根据实际情况进行分析和优化，避免建立过多的索引。
避免在大文本和二进制列上建立索引。这些列的数据量比较大，建立索引会占用大量的磁盘空间和内存，影响数据库的性能。

需要注意的是，索引不是建的越多越好，也不是什么字段建索引都能有很好的效果，而且在建立索引时，需要根据实际情况进行分析和优化，避免过度索引和不必要的索引。同时，需要定期对索引进行优化和维护，确保索引的有效性和性能。

3.2 最左前缀原则

要让 SQL 能命中索引，需要遵循最左前缀原则,最左前缀原则需要遵循以下情况：

查询条件必须包含索引的最左前缀列，才能利用索引进行查询。
查询条件中可以包含索引的前缀列，但不能包含索引的后缀列。
查询条件中可以包含索引的非前缀列，但是会导致索引失效，无法利用索引进行查询。

下面举例说明：
假设有一个表student，包含以下列：id, name, age, gender，其中id列为主键列，同时创建了一个联合索引idx_name_age_gender(name, age, gender)。

查询条件包含索引的最左前缀列
例如，查询name为’张三’的学生信息，可以使用如下SQL语句：

 SELECT * FROM student WHERE name = '张三';

该查询条件包含了索引的最左前缀列name，可以利用索引进行查询。

查询条件包含索引的前缀列，但不能包含索引的后缀列
例如，查询name为’张三’且age为18岁的学生信息，可以使用如下SQL语句：

SELECT * FROM student WHERE name = '张三' AND age = 18;

该查询条件包含了索引的前缀列name和age，可以利用索引进行查询。
但是，如果查询条件中只包含age列，不能利用索引进行查询，需要进行全表扫描。例如：

SELECT * FROM student WHERE age = 18;

查询条件中包含索引的非前缀列，导致索引失效
例如，查询gender为’男’的学生信息，可以使用如下SQL语句：

 SELECT * FROM student WHERE gender = '男';

该查询条件包含了索引的非前缀列gender，会导致索引失效，需要进行全表扫描。

3.3、哪些情况会破坏最左前缀原则

以下情况会破坏最左前缀原则：

查询条件中包含了索引列的后缀列，而不是前缀列。这种情况下，索引无法被利用，需要进行全表扫描。
查询条件中包含了索引列的非前缀列。这种情况下，索引会失效，需要进行全表扫描。
查询条件中使用了函数或表达式，导致无法利用索引。例如：

SELECT * FROM table WHERE YEAR(date_column) = 2021;

这种情况下，YEAR(date_column)是一个表达式，无法利用索引。

查询条件中使用了通配符（如%），导致无法利用索引。例如：

SELECT * FROM table WHERE name LIKE '张%';

这种情况下，LIKE '张%'中的通配符%会导致无法利用索引。

查询条件中使用了OR操作符，导致无法利用索引。例如：

SELECT * FROM table WHERE col1 = 'value1' OR col2 = 'value2';

这种情况下，OR操作符会导致无法利用索引。

总之，破坏最左前缀原则的情况主要是查询条件与索引的匹配度不高，导致无法利用索引进行查询。因此，在设计索引和查询时，需要注意遵循最左前缀原则，选择合适的索引列和查询条件，以充分利用索引提高查询效率。

4、Mysql 索引的原理

4.1、索引介绍和原理

MySQL索引是一种数据结构，用于提高查询效率。它通过将索引列的值与行的物理位置关联起来，可以快速地定位满足查询条件的行。MySQL支持多种类型的索引，包括B-tree索引、哈希索引、全文索引等。

1.B-tree索引是MySQL中最常用的索引类型。它是一种平衡树结构，可以快速地定位满足查询条件的行。B-tree索引的原理是将索引列的值按照一定的顺序存储在B-tree中，每个节点包含多个索引值和对应的物理位置指针，可以通过二分查找快速定位到目标行。B-tree索引不仅可以用于等值查询，还可以用于范围查询和排序等操作。

2.哈希索引是一种基于哈希表的索引类型。它将索引列的值通过哈希函数转换为哈希值，并将哈希值与对应的物理位置关联起来。哈希索引的查询效率非常高，但是它只支持等值查询，不支持范围查询和排序等操作。此外，哈希索引的缺点是哈希冲突较多时，查询效率会降低。

3.全文索引是一种用于全文搜索的索引类型。它可以对文本类型的列进行索引，并支持全文搜索、模糊搜索等操作。全文索引的原理是将文本分词后，将每个词作为索引值存储在倒排索引中，倒排索引记录了每个词在哪些行中出现过。查询时，可以通过倒排索引快速定位到包含目标词的行。

除了以上三种常见的索引类型，MySQL还支持空间索引、前缀索引、多列索引等类型的索引。索引的使用需要根据具体的业务场景和查询需求进行选择和设计，合理的索引可以大大提高查询效率，但是过多或不合理的索引也会降低写入性能和占用存储空间。

4.2、磁盘 IO 与预读

磁盘IO是指计算机从磁盘读取或写入数据的操作。由于磁盘的读写速度比内存慢很多，因此磁盘IO通常是系统性能的瓶颈之一。为了提高磁盘IO的效率，可以使用磁盘预读技术。

磁盘预读是指在读取一个数据块时，顺便将其后续的若干个数据块也读取到内存中，以便于后续的访问。这样可以减少磁盘IO的次数，提高数据访问的效率。磁盘预读一般分为两种方式：顺序预读和随机预读。

顺序预读是指在读取一个数据块时，将其后续的若干个数据块也顺序地读取到内存中。这种方式适用于顺序访问的场景，如扫描整个表或索引。在MySQL中，InnoDB存储引擎会自动进行顺序预读，以提高查询效率。

随机预读是指在读取一个数据块时，将其后续的若干个数据块也读取到内存中，但是读取的顺序是随机的。这种方式适用于随机访问的场景，如使用索引进行查询。在MySQL中，可以通过设置innodb_read_io_threads参数来控制随机预读的线程数。

虽然磁盘预读可以提高磁盘IO的效率，但是也会占用更多的内存资源。因此，在使用磁盘预读时需要根据具体的业务场景和硬件配置进行权衡，以达到最优的性能和资源利用率。

5、详解 B+ 树

5.1、什么是 B+ 树

在这里插入图片描述

B+树是一种常用的数据结构，广泛应用于数据库和文件系统等领域。它是一种多路搜索树，每个节点可以存储多个关键字和对应的数据指针。B+树具有以下特点：

多路搜索：B+树的每个节点可以存储多个关键字和对应的数据指针，因此可以减少树的高度，提高搜索效率。
平衡性：B+树是一种平衡树，所有叶子节点的深度相同，因此可以保证搜索效率的稳定性。
顺序访问：B+树的叶子节点组成一个有序链表，可以支持范围查询和排序等操作。
磁盘友好：B+树的节点大小通常和磁盘块大小相同，因此可以减少磁盘IO次数，提高磁盘访问效率。

B+树的结构和操作可以分为以下几个部分：

根节点：B+树的根节点是一个特殊的节点，通常包含少量的关键字和指向子节点的指针。
叶子节点：B+树的叶子节点存储数据指针和关键字，通常按照关键字的大小顺序组成一个有序链表。
内部节点：B+树的内部节点存储关键字和指向子节点的指针，通常按照关键字的大小顺序组织。
插入操作：B+树的插入操作通常从根节点开始，沿着关键字的大小顺序找到合适的叶子节点，将新的关键字和数据指针插入到叶子节点中，并根据需要调整树的结构。
删除操作：B+树的删除操作通常从根节点开始，沿着关键字的大小顺序找到合适的叶子节点，删除指定的关键字和数据指针，并根据需要调整树的结构。
查找操作：B+树的查找操作通常从根节点开始，沿着关键字的大小顺序找到合适的叶子节点，根据关键字查找对应的数据指针。
B+树是一种高效的数据结构，可以支持快速的插入、删除和查找操作，尤其适用于需要频繁访问磁盘的场景。在数据库和文件系统等领域，B+树已经成为一种常用的数据结构，广泛应用于各种系统中。