29、Red Hat Linux Web与FTP服务器搭建指南
2025/12/17 6:22:24
1.定位慢SQL
慢查询日志：开启 MySQL 慢查询日志，再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，找出问题的根源。
服务监控：可以在业务的基建中加入对慢 SQL 的监控，常见的方案有字节码插桩、连接池扩展、ORM 框架过程，对服务运行中的慢SQL 进行监控和告警。
找到对应的慢 SQL 后，使用 EXPLAIN 命令查看 MySQL 是如何执行 SQL 语句的，这会帮助我们找到问题的根源。
开启MySQL的慢查询日志：
https://blog.csdn.net/m0_74090098/article/details/137639660
2.SQL优化方式
2.1.避免不必要的列
如尽量避免select*
2.2.分页优化
数据量巨大时，传统的Limit和offset导致性能不好，数据库扫描需要offset+limit数量的行。
2.2.1.延迟关联
延迟关联适用于需要从多个表中获取数据且主表行数较多的情况。
它首先从索引表中检索出需要的行ID，然后再根据这些ID去关联其他的表获取详细信息。
2.2.2.书签
书签方法通过记住上一次查询返回的最后一行的某个值，然后下一次查询从这个值开始，避免了扫描大量不需要的行。
2.3.索引优化
2.3.1.覆盖索引
使用非主键索引查询数据时需要回表，但如果索引的叶节点中已经包含要查询的字段，那就不会再回表查询了，这就叫覆盖索引。
2.3.2.避免使用!或<>操纵符
会导致无法使用索引而进行全表扫描。
2.3.3.适当使用前缀索引
适当使用前缀索引可以降低索引空间占用，提高索引查询效率。
前缀索引是指在创建索引时，只对列的前部分字符（而非整个列）进行索引，通常用于 字符串类型的字段，以节省空间并提高查询效率。
创建前缀索引：
CREATE TABLE users ( id INT PRIMARY KEY, email VARCHAR(255), INDEX(email(10)) --对email字段的前10个字符创建索引 ); |
在已有表上添加前缀索引：
ALTER TABLE users ADD INDEX email_prefix_index (email(10));-- 对 email 字段的前 10 个字符创建索引 |
2.3.4.避免列上使用函数
在where子句中直接对列使用函数会导致索引失效，因为数据库需要对每行的列应用函数后再进行比较，无法直接利用索引。
2.3.5.正确使用联合索引
正确地使用联合索引可以极大地提高查询性能，联合索引的创建应遵循最左前缀原则，即索引的顺序应根据列在查询中的使用频率和重要性来安排。
2.4.Join优化
2.4.1.优化子查询
子查询，特别是在 select 列表和 where 子句中的子查询，往往会导致性能问题，因为它们可能会为每一行外层查询执行一次子查询。
例：
SELECT o.order_id, o.amount, (SELECT SUM(amount) FROM orders WHERE customer_id = o.customer_id) AS total_amount FROM orders o; |
※ 对于每一行 orders 表中的记录，都会执行一次内层的子查询 (SELECT SUM(amount) FROM orders WHERE customer_id = o.customer_id)。
2.4.2.小表驱动大表
在执行 JOIN 操作时，应尽量让行数较少的表（小表）驱动行数较多的表（大表），这样可以减少查询过程中需要处理的数据量。
例：
SELECT e.name, d.department_name FROM employees e JOINdepartments dONe.department_id = d.id; |
employees 表包含的行较少，数据库会首先扫描 employees 表中的所有数据。
对于每一行 employees 表中的数据，数据库会去 departments 表中查找对应的部门信息。由于 departments 表是大表，数据库对它的扫描次数被减少了，因为它只会根据 employees 表中的 department_id 来进行筛选。
2.4.3.适当增加冗余字段
在某些情况下，通过在表中适当增加冗余字段来避免 JOIN 操作，可以提高查询效率，尤其是在高频查询的场景下。
2.4.4.避免join太多表
因为 join 太多表会降低查询的速度，返回的数据量也会变得非常大，不利于后续的处理。
如果业务逻辑允许，可以考虑将复杂的 JOIN 查询分解成多个简单查询，然后在应用层组合这些查询的结果。
2.4.5.利用索引扫描做排序
设计索引时充分考虑排序的需求，这样之后按照索引顺序扫描得到自然有序的结果。
2.5.排序优化(?)
利用索引扫描做排序。
2.6.UNION优化
条件下推是指将 where、limit 等子句下推到 union 的各个子查询中，以便优化器可以充分利用这些条件进行优化。通过将查询条件下推到UNION的每个分支中，每个分支查询都只处理满足条件的数据，减少了不必要的数据合并和过滤。
例：
SELECT id, name, salary FROM employees WHERE salary > 5000 UNION ALL SELECT id, name, salary FROM contractors WHERE salary > 5000; |
UNION ALL的作用是将多个查询结果合并为一个结果集，并保留所有重复行，不进行去重。
LIMIT用于限制SQL查询返回的结果行数，常用于分页或只取前几条记录。
3.查看explain
EXPLAIN的作用是显示SQL查询的执行计划，用于分析查询的优化方式和性能瓶颈。
使用方法：在select语句前加explain关键字即可。
①id 列：查询的标识符。
②select_type 列：查询的类型。常见的类型有：
SIMPLE：简单查询，不包含子查询或者 UNION 查询。
PRIMARY：查询中如果包含子查询，则最外层查询被标记为 PRIMARY。
SUBQUERY：子查询。
DERIVED：派生表的 SELECT，FROM 子句的子查询。
③table 列：查的哪个表。
④type 列：表示 MySQL 在表中找到所需行的方式，性能从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。
system，表只有一行，一般是系统表，往往不需要进行磁盘 IO，速度非常快
const、eq_ref、ref：这些类型表示 MySQL 可以使用索引来查找单个行，其中 const 是最优的，表示查询最多返回一行。
range：只检索给定范围的行，使用索引来检索。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。
index：遍历索引树读取。
ALL：全表扫描，效率最低。
⑤possible_keys 列：可能会用到的索引，但并不一定实际被使用。
⑥key 列：实际使用的索引。如果为 NULL，则没有使用索引。
⑦key_len 列：MySQL 决定使用的索引长度（以字节为单位）。当表有多个索引可用时， key_len 字段可以帮助识别哪个索引最有效。通常情况下，更短的 key_len 意味着数据库在比较键值时需要处理更少的数据。
⑧ref 列：用于与索引列比较的值来源。
const：表示常量，这个值是在查询中被固定的。例如在 WHERE column = 'value'中。
一个或多个列的名称，通常在 JOIN 操作中，表示 JOIN 条件依赖的字段。
NULL，表示没有使用索引，或者查询使用的是全表扫描。
⑨rows 列：估算查到结果集需要扫描的数据行数，原则上 rows 越少越好。
⑩Extra 列：附加信息。
Using index：表示只利用了索引。
Using where：表示使用了 WHERE 过滤。
Using temporary ：表示使用了临时表来存储中间结果。