Savage

G

0%

MySQL优化方案

发表于 分类于
总结一下MySQL优化的技巧

MySQL优化方案

1.定位慢SQL

接口定位

最简单也是最常用的方案，通过浏览器查看请求速度慢的接口，根据接口定位到SQL。但是接口请求速度会受到网络等影响，可能会出现定位不准确的情况。

慢查询日志

通过设定查询时间的阈值配合MySQL慢查询日志可以很精准的定位到慢SQL，相关内容可以查看MySQL日志 | Savage (gaoxuefeng.xyz)

但是慢查询日志在查询结束以后才记录，所以当SQL执行中执行效率出现问题的时候，慢查询日志并不能定位问题SQL

processlist

show processlist命令可以查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

Untitled

下面是查询结果中各项参数的含义：

  • id ：用户登录mysql时，系统分配的”connection_id”，可以使用函数connection_id()查看
  • user：显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
  • host：显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
  • db：显示这个进程目前连接的是哪个数据库
  • command：显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等、
  • time：显示这个状态持续的时间，单位是秒
  • state：显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
  • info：显示这个sql语句，是判断问题语句的一个重要依据

2.EXPLAIN 分析执行计划

1
explain select * from users where id = 1;

常见优化流程：explain命令会获取到SQL的执行信息，查看possible_keys 获取可能使用的索引， 查看key 参数获取实际查询的索引，rows 参数查看扫描的行数，通过这这三个参数的反馈信息不断调整索引，优化SQL。其他参数信息可以为我们提供优化SQL的思路和方案。

对于在乎效率的sql，type 参数结果不要为ALL，最好是ref 之前的值。

Untitled

  • **id**：查询的标识符，通常是一个数字，每个查询都会有一个唯一的标识符。id越大查询优先级越高，越先执行，id相同从上到下顺序执行。
  • **select_type**：查询类型，表示查询的类型，比如 SIMPLE（简单查询）、PRIMARY KEY（基于主键的查询）等。
    • select_type值类型：
      • SIMPLE：表示查询不包含 UNION 查询或子查询等复杂语句。
      • PRIMARY：表示查询为最外层的查询，也称为主查询。
      • UNION：表示查询为 UNION 查询的一部分。
      • SUBQUERY：表示查询为子查询。
      • DEPENDENT SUBQUERY：表示查询为依赖于外部查询结果的子查询。
      • DERIVED：表示查询的结果集来自 FROM 子句中的子查询，MySQL 会将这个子查询的执行结果放在一个临时表中，然后再执行主查询。
      • UNION RESULT：表示查询为 UNION 查询的结果集合并操作。
      • DEPENDENT UNION：表示查询为依赖于外部查询结果的 UNION 查询。
      • DEPENDENT UNION RESULT：表示查询为依赖于外部查询结果的 UNION 查询的结果集合并操作。
  • **table**：查询涉及到的表名。
  • **partitions**：查询涉及到的分区名，如果表没有被分区，则该列为 NULL。
  • **type**：访问类型，表示 MySQL 选择了哪种访问方式来处理查询，比如 ALL（全表扫描）、index（索引扫描）等。
    • type值类型（效率依次降低）：
      • system：这是最快的访问类型，仅有一行数据（系统表）。
      • const：使用唯一索引或主键来检索单个值时，采用 const 访问类型，只返回一行数据。
      • eq_ref：对于每个索引键，在联接中只有一条匹配记录，通常在使用主键或唯一索引的等值连接时出现。
      • ref：使用非唯一索引或主键的等值操作进行查询，返回多个匹配的行。
      • range：使用索引范围的查询，例如使用 “>” 或 “<” 进行范围查询。
      • index：全索引扫描，遍历整个索引树，通常比 ALL 快，但仍需要扫描索引树。
      • all：全表扫描，遍历整个表，是最慢的访问类型。
  • **possible_keys**：可能使用的索引，表示 MySQL 可以使用哪些索引来处理查询。
  • **key**：实际使用的索引，表示 MySQL 实际上使用了哪个索引来处理查询。
  • **key_len**：索引长度，表示 MySQL 使用的索引长度，索引长度与表字段数据结构和表字段长度有关，在不影响精度的前提下越小越好。
  • **ref**：引用的表和列，表示 MySQL 在对查询结果进行排序、分组或者连接操作时使用的参考表和列。
    • ref值类型
      • const：表示查询使用了常量条件进行等值匹配，通常与唯一索引或主键相关联。
      • column_name：表示查询使用了非唯一索引进行等值匹配，参考的表和列为 column_name。
      • func(column_name)：表示查询使用了函数操作，并参考了 column_name 列。
      • NULL：表示查询没有使用任何引用的表和列。
      • table_name.column_name：表示查询使用了连接操作，并参考了 table_name 表和 column_name 列。
  • **rows**：扫描的行数，表示 MySQL 执行查询时需要扫描的行数。
  • **filtered**：过滤的行数百分比，表示 MySQL 执行查询后，查询结果经过过滤后还剩下的行数占总行数的百分比。
  • e**xtra**：额外信息，表示 MySQL 在执行查询时需要做的额外操作，比如 Using filesort（需要对结果进行排序操作）、Using temporary（需要创建临时表）等。
    • extra值类型
      • Using index：表示查询使用了覆盖索引，即查询只通过索引就能获取所需的数据，无需进一步访问表。
      • Using where：表示在查询过程中执行了 WHERE 条件过滤操作。
      • Using temporary：表示查询需要创建临时表来处理中间结果。
      • Using filesort：表示查询需要对结果进行排序操作，可能会使用临时文件进行排序。
      • Using join buffer：表示查询使用了连接缓存来处理连接操作。
      • Impossible where：表示查询的 WHERE 条件始终为假，因此不会检索任何行。
      • Select tables optimized away：表示查询的结果可以直接从索引中获得，而无需访问实际的表。
      • Distinct：表示查询使用了 DISTINCT 关键字去除重复行。
      • Full scan on NULL key：表示查询使用了索引，但索引的值都为 NULL，因此需要进行全表扫描。
      • Full join：表示在连接操作中使用了完全连接（CROSS JOIN）。
      • Loose index scan：表示使用了宽松索引扫描，在某些情况下可能会比较慢。
      • FirstMatch(subquery)：表示在子查询中找到第一个匹配项后停止执行。
      • Uncacheable subquery：表示子查询的结果无法被缓存，每次执行都需要重新计算。

3.PROFILE 分析SQL性能

MySQL 的 SHOW PROFILES 语句可以用于查看最近执行的一些查询语句的性能信息，包括查询的执行时间、CPU 时间、磁盘 IO 等。它可以帮助我们识别性能瓶颈，并优化查询语句。

SHOW PROFILES 语句的语法如下：

1
SHOW PROFILES [LIMIT N];

通过have_profileing 参数可以查看MYSQL是否支持profile：

Untitled

默认是关闭的，可以通过set语句开启profiling

1
set profiling=1; //开启profiling 开关；

通过 show profiles 可以看到执行的sql

1
SHOW PROFILES;

Untitled

通过show profile for query query_id 可以查看详细的性能信息

1
SHOW PROFILE FOR QUERY 1;

Untitled

TIP ：
Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

Untitled

4.索引的使用

复合索引和最左前缀规则

在MYSQL中，最左前缀指的是复合索引的一个特性，在查询条件中使用的索引列必须按照索引定义的顺序从左到右连续使用，不能跳过中间的列。

例如，假设有一个复合索引 (A, B, C)，那么以下查询可以充分利用索引：

  • WHERE A = 1
  • WHERE A = 1 AND B = 2
  • WHERE A = 1 AND B = 2 AND C = 3

但以下查询无法完全利用索引，因为不满足最左前缀的要求：

  • WHERE B = 2 （没有使用 A 列）
  • WHERE C = 3 （没有使用 A 和 B 列）

在explain中的key_len 参数会显示使用到索引的长度

  • WHERE A = 1 （只有A索引的长度）
  • WHERE A = 1 AND B = 2（A的长度+B的长度）
  • WHERE A = 1 AND B = 2 AND C = 3（A的长度+B的长度+C的长度）

通过下图可以看到key_len的使用情况

Untitled

索引失效情况

  • 数据类型不匹配（字符串不加单引号），通过下图可以发现不加单引号，只有id的索引生效

    Untitled

  • 在索引列上进行函数操作也会导致索引失效（计算、转换、截取等）。在下图中可以看到只走了ID的索引

    Untitled

  • 使用NOT 条件可能会导致索引失效，因为它需要扫描整个数据集

    Untitled

  • 使用OR条件可能会导致索引失效，MySQL 无法有效地使用索引是因为 OR 条件的性质，它要求将不同的条件组合在一起，导致查询优化器难以选择合适的索引来加速查询。测试发现使用or语句在InnoDB中无法使用索引。

    Untitled

  • 使用%开头的LIKE模糊查询，索引失效。仅是尾部模糊匹配，索引不会失效。（因为 B-tree 索引是按照从左到右的顺序建立的，所以后缀%查询可以使用索引的前缀部分匹配，快速定位符合条件的记录。）

    Untitled

  • 使用IN通常会走索引，NOT IN 不走索引

  • is NULL ， is NOT NULL 有时索引失效

5.SQL语句优化

大批量插入数据

使用load导入数据时，可以通过设置提高导入的效率。在一些小型数据集上，INSERT 和LOAD DATA 可能性能相当，还是要根据具体情况来使用。

  • 让数据按照主键顺序排列，可以提高插入速度
  • 导入时关闭唯一性校验：在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验
  • 手动提交事物：如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交

以下是一些导致 LOAD DATA 比 INSERT 更快的原因：

  1. 批量操作：LOAD DATA 一次性加载整个文件或数据集，而 INSERT 需要逐条插入每一条记录。批量操作减少了与数据库服务器的交互次数，提高了效率。
  2. 索引和约束：INSERT 语句在插入每一条记录时都需要检查索引和约束，以确保数据的完整性。而 LOAD DATA 在加载数据时可以临时禁用索引和约束，然后再重新启用它们，从而节省了额外的检查和验证步骤。
  3. 内部优化：MySQL 数据库在处理 LOAD DATA 命令时使用了一些内部优化技巧，例如并行加载、内存缓冲等，以提高加载速度。

优化insert

  • 使用一条insert into 插入多个数据，这样可以减少与数据库服务器的交互次数，提高插入性能

    1
    insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

  • 在事务中进行数据插入

  • 数据有序插入

优化order by

使用order by 会对返回的数据进行排序，排序的信息在explain分析的extra值类型中可以看到。

如果查询的结果不全在索引中，则需要回表通过ID查询出所有需要的信息，这也是不推荐使用SELECT *查询的一个理由。几乎不可能对所有字段添加索引，所以使用SELECT * extra中的信息会是Using filesort 。

Untitled

如果只查询需要的字段，并且这个字段在索引中，则不需要回表查询，直接返回索引中的字段信息。extra 参数是Using index

Untitled

如果需要查询多个字段信息，只要该字段在索引上，并且符合复合索引的查询规则（最左前缀），就可以通过索引直接返回结果

Untitled

Filesort的优化

filesort 排序算法是在无法使用索引或优化查询时使用的排序算法，Filesort排序算法可以使用一次扫描或者二次扫描来实现排序操作。

  • 一次扫描（One pass sort）是指MySQL只需要扫描一遍排序文件就能完成排序操作的方式。一次扫描通常需要使用到多个内存缓冲区，在内存中进行排序操作，然后将排序好的数据写入磁盘，最后读取排序好的数据返回给客户端。一次扫描相对于二次扫描来说，需要更多的内存空间，但是它的性能更高，因为它只需要扫描一遍数据。
  • 二次扫描（Two-pass sort）是指MySQL需要扫描两遍排序文件才能完成排序操作的方式。第一遍扫描用于将数据拆分成多个块，每个块都可以放入内存中，并对每个块进行排序操作。第二遍扫描用于将已排序的块合并成一个有序的结果集。相比于一次扫描，二次扫描需要更少的内存，但是需要扫描两次数据。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

优化group by

group by 语句实际上和order by 语句一样也会进行排序操作，在排序后对结果进行分组操作。通过order by null 可以禁止排序，提升效率。

Untitled

使用order by null，则不需要进行filesort排序

Untitled

优化嵌套查询

子查询虽然可以完成复杂的查询逻辑，但是子查询会创建临时表，查询结束后还需要删除临时表，查询效率会慢。可以使用join替代子查询，

使用SQL索引提示

索引提示会影响MYSQL优化起在查询时的索引选择，合理的使用查询会更快，使用不当可能会让优化器错过最优索引。

  • FORCE index：FORCE INDEX 关键字用于强制 MySQL 使用指定的索引来执行查询，而不考虑优化器可能选择的其他索引。例如，假设有一个表名为 users，它具有一个名为 email_index 的索引。您可以使用以下语法来强制 MySQL 使用该索引：

    1
    SELECT * FROM users FORCE INDEX (email_index) WHERE email = 'example@example.com';

  • IGNORE index：IGNORE INDEX 关键字用于指示 MySQL 忽略指定的索引，即不使用该索引来执行查询。例如，假设有一个表名为 orders，它具有一个名为 status_index 的索引。您可以使用以下语法来忽略该索引：

    1
    SELECT * FROM orders IGNORE INDEX (status_index) WHERE status = 'completed';

6.总结

优化SQL流程

  1. 定位慢SQL
  2. explain分析
  3. 调整表索引
  4. 调整SQL和查询结构

对于一个SQL做好这些应该已经能够满足日常的使用了，如果要优化一些热点接口，单纯的数据库已经无法满足，这时候就应该结合实际业务，设计特殊的架构、搭配其他中间件来完成。

如果觉得我的文章对您有用，赏我一包辣条吧！您的支持将鼓励我继续创作！也可以加我微信一起交流学习，折腾点有意思事情。