定位需要优化的SQL,及如何优化SQL

手机
2025-09-16 16:21:02

查看执行频率（看哪一类操作较多） show global status like 'Com_______'; 慢查询日志是否支持，以及日志文件的位置 show variables like 'slow_query%'; show profiles; 在SQL优化时帮助我们了解时间消耗在哪里查看是否支持profile select @@have_profiling; 查看是否开启profile select @@profiling; #开启profile set profiling =1; select * from message; select count(*) from message; select * from message where status=1; 查看SQL的耗时情况 show profiles; #查看某个Query_ID的SQL各个阶段的执行时间 show profile for query 116; 查看各个阶段cpu的使用情况 show profile cpu for query 137;

explain执行计划

1.id select 查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下；id不同，值越大，越先执行)

2.type 表示连接类型，性能由好到差的连接类型为

null 不查询任何表比如 select 'A'system 一般是访问系统表const 一般是主键和唯一索引eq_ref ref 一般是非唯一性索引range index 用到了索引但遍历了整个索引树all 全表扫描

3.possible_key 可能应用在这张表上的索引(一个或者多个)

4.Key 实际使用的索引，如果为NULL 则没有使用索引

5.key_len 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际长度，在不损失精度性的前提下，长度越短越好

6.rows MySQL认为必须要执行查询的行数，在Innodb引擎的表中，是一个估计值，可能并不总是准确的。

7.filtered 表示返回结果的行数占需读取行数的百分比，值越大越好

主键优化

以下几点均为在满足业务需求的情况下考虑

1. 满足业务需求的情况下，尽量降低主键的长度较短的主键可以减少存储空间的消耗，从而减少数据库的 I/O 操作，提高查询和插入的性能。索引结构的大小直接影响查询性能，较小的主键意味着索引可以更高效地存储和访问。 2. 插入数据时，尽量选择顺序插入，避免页分裂出现数据库使用 B-tree 结构存储索引，当插入数据时，随机插入会导致页分裂（详情可去搜索下），这不仅增加了 I/O 操作，也会影响性能。顺序插入可以保持 B-tree 的平衡，减少页的分裂。 3. 尽量不使用 UUID 作为主键或其他自然主键如身份证号 UUID 通常是 128 位的字符串，作为主键使用时，会导致索引变得庞大，影响查询和插入性能。自然主键如身份证号的唯一性可能会受到业务变更的影响，同时其长度较长，导致存储和处理效率低下。 4. 业务操作时，避免对主键的修改主键是唯一标识一条记录的字段，修改主键不仅会影响数据完整性，还可能导致外键关系失效、增加数据更新的复杂性。对主键的修改会导致相关索引的重建，增加了 I/O 操作和性能损耗。总结

以上优化原则旨在提高数据库的性能和可维护性。在设计主键时，考虑长度、插入顺序、选择合适的主键类型和避免主键修改可以有效提升数据库的整体效率。合理的主键设计是数据库性能优化的重要组成部分。

ORDER BY优化

1.Using filesort:通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer完成排序操作，所有不是通过索引直接返回排序结果的排序都是 fileSort排序 2.Using Index 通过有序索引扫描直接返回数据不需要额外排序效率高

1. 根据排序字段建立合适的索引，多字段排序时遵循最左前缀法则

解释：

为了优化 ORDER BY 的性能，最佳实践是为排序字段创建索引。当需要对多个字段进行排序时，应确保索引的第一个字段是查询中最左侧的排序字段（即遵循最左前缀法则）。

示例：假设有一个如下索引，包含字段 age 和 phone(不指定排序方式默认为升序)

CREATE INDEX idx_age_phone ON orders (age, phone);

SELECT * FROM orders ORDER BY age;

执行计划

keyextraidx_age_phoneUsing Index

SELECT * FROM orders ORDER BY age,phone;

执行计划

keyextraidx_age_phoneUsing Index

SELECT * FROM orders ORDER BY phone，age;

执行计划

keyextraidx_age_phoneUsing Index ; Using fileSort

违背了最左前缀法则

2. 尽量使用覆盖索引

解释：

覆盖索引是指索引中包含了查询所需的所有列，这样数据库可以仅通过索引而不需要访问表数据，从而提高查询效率。使用覆盖索引可以减少磁盘 I/O，因为只需要读取索引而不需要读取实际的数据行。

示例：假设我们查询 orders 表，获取字段 order_id 和 order_date，如：

SELECT order_id, order_date FROM orders ORDER BY customer_id asc, order_date asc;

优化建议：创建覆盖索引，包括所需的所有字段：

CREATE INDEX idx_covering ON orders (customer_id, order_date);

3. 多字段排序，一个升序一个降序时注意联合索引的创建规则（ASC/DESC）

解释：

当进行多字段排序，并且字段的排序方向不一致（如一个升序，一个降序）时，联合索引的创建需要特别注意。创建联合索引时，字段的排序方向（ASC/DESC）应与实际查询的 ORDER BY 语句保持一致，以确保能有效利用索引。

示例：假设有一个如下索引，包含字段 age 和 phone(不指定排序方式默认为升序)

CREATE INDEX idx_age_phone ON orders (age , phone);

SELECT * FROM orders ORDER BY age,phone;

执行计划

keyextraidx_age_phoneUsing Index

SELECT * FROM orders ORDER BY age desc,phone desc;

执行计划

keyextraidx_age_phoneBackward index scan(反向扫描索引),Using Index

SELECT * FROM orders ORDER BY age asc,phone desc;

执行计划

keyextraidx_age_phoneUsing Index ; Using fileSort

优化

CREATE INDEX idx_age_phone ON orders (age , phone desc);

4. 不可避免fileSort时

不可避免fileSort 且有大量数据排序时，适当增加排序缓冲区大小（ sort_buffer_size 默认未256K ）

GROUP BY优化

1.可以使用索引提高效率但仍需满足最左前缀法则示例：假设有一个如下索引，包含字段 profession ,age, status(不指定排序方式默认为升序)

create index idx_prf_age_status on tb_user(profession,age,status) select prfession ,count(*) form tb_user group by profession

执行计划

keyextraidx_prf_age_statusUsing Index

select age,count(*) form tb_user group by age

执行计划

keyextraidx_prf_age_statusUsing Index ;Using temporary

select prfession ,age,count(*) form tb_user group by prfession , age

执行计划

keyextraidx_prf_age_statusUsing Index

select ,age,count(*) form tb_user where prfession ='软件工程' group by prfession , age

执行计划

keyextraidx_prf_age_statusUsing Index

limit优化童话覆盖索引+子查询方式进行优化

原SQL

select * from tb_sku limit 9000000,10

优化后SQL

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

错误SQL

select s.* from tb_sku where id in (select id from tb_sku order by id limit 9000000,10 ) # 此SQL 会出现 This version of MySQL doesn't yer support 'limit IN/ALL/ANY/SOME subquery'

count优化

暂无较好优化方案，但有以下几点可以注意

count 是一个聚合函数对于返回的结果集一行一行的判断如何 count 的参数不是 null 则累计+1 如果为null 则不加，最后返回累计值

1.count(*)

InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务蹭饭直接按行进行累加

2.count(主键)

InnoDB引擎会遍历整张表，把每一行的id都取出来，返回给服务层，服务层拿到数据后，直接按行进行累加（主键不可能为null）

3.count(字段)

无 not null 约束 InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层拿到数据后，根据是否为null 进行计数

有 not null 约束 InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加

4.count(1) . InnoDB引擎会遍历整张表，但不取值，服务层对于返回的每一行放一个数字 1 进去直接按行进行累加

update优化