MYSQL8优化

概述

• 从设计上优化
• 从查询上优化
• 从索引上优化
• 从存储上优化

查看SQL执行频率

参数	解释
Com_select	执行select操作的次数，一次查询只累加1
Com_insert	执行INSERT操作的次数，对于批量插入的INSERT操作，值累加一次
Com_update	执行UPDATE操作的次数
Com_delete	执行DELETE操作的次数
Innodb_rows_read	select查询返回的次数
Innodb_rows_inserted	执行INSERT操作插入的行数
Innodb_rows_updated	执行UPDATE操作更新的行数
Innodb_rows_deleted	执行DELETE操作删除的行数
Connections	试图连接MYSQL服务器的次数
Uptime	服务器工作时间
Slow_queries	慢查询的次数

查看当前会话统计结果

-- 查看当前会话SQL执行类型的统计结果
-- 7个_
show session status like 'Com_____'

查看数据库自上次启动至今统计结果

-- 查看全局(自上次数据库启动至今)执行类型的统计信息
-- 7个_
show global status like 'Com_____'

查看针对INnodb引擎的统计结果

-- 查看针对INnodb引擎的统计结果
show status like 'Innodb_rows_%'

show processlist定位低效率执行SQL

show processlist可以动态的查询到正在执行中的sql语句。

慢SQL查询

通过慢查询日志定位那些执行效率较低的SQL语句。
https://blog.csdn.net/omaidb/article/details/129360788

显示正在运行的连接和查询

该命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
该命令会向服务器发送一个额外的查询，如果您的数据库服务器非常繁忙或者有很多连接，那么执行这个命令可能需要很长时间。

-- 显示当前正在运行的所有连接和它们所执行的查询
show processlist;

explain分析执行计划

https://blog.csdn.net/omaidb/article/details/120040657

show profiles分析sql执行耗时

开启profile

-- 查看当前mysql只是否支持profile
select @@have_profiling;

-- 查看profile是否开启
show variables like 'profiling';

-- 临时开启profile
set profiling=1;

profiles查看sql语句执行的耗时细节

-- 查看sql语句执行的耗时细节
show profiles;

profile查看sql执行线程的状态和耗时

通过show profile for query 查询id;语句，可以查看到sql执行过程中每个线程的状态和消耗的时间；

-- 查看第8条sql语句的耗时细节
show profile for query 20;

show profile分析SQL资源明细类型消耗

资源明细类型	解释
all	所有资源
cpu	cpu资源
memory	内存
block io	io
centext switch	连接转换
page faults	分页

-- 查看指定查询语句的资源消耗
SHOW PROFILE CPU, MEMORY, BLOCK IO FOR QUERY 133;
-- SHOW PROFILE CPU, MEMORY, BLOCK IO FOR QUERY 133;

profile结果字段解读

字段	含义
Status	SQL语句执行的状态
Duration	SQL执行过程中每一个步骤的耗时
CPU_user	当前用户占用的CPU
CPU_system	系统占用的CPU

trace分析优化器执行计划

-- 查看trace分析优化器是否开启
-- 查看trace最大能够使用的内存大小
show variables like 'optimizer_trace%';

-- 查看是否配置json格式
show variables like 'end_markers_in%';

optimizer_trace_max_mem_size参数的单位是字节(bytes)。

-- 打开trace分析优化器，设置格式为json
set optimizer_trace='enabled=on',end_markers_in_json=on;

-- 设置trace最大能够使用的内存大小，单位是字节（bytes)
set optimizer_trace_max_mem_size=1000000;

检查information_schema.optimizer_trace

检查information_schema.optimizer_trace就可以知道mysql是如何执行的。

-- 检查infromation_schema.optimizer_trace
select * from information_schema.optimizer_trace\G;

疑问：为什么sql会自动选择b计划

mysql8内置了mysql优化器，会自动优化执行策略。

MYSQL数据索引优化

https://blog.csdn.net/omaidb/article/details/130292101

大批量插入数据优化

0.开启允许从本地导入数据

-- 开启local_infile
set global local_infile=1;

-- 查看local_infile是否开启
show global variables LIKE 'local_infile';

-- 加载数据
load data local infile '/data/sql_data/sql2.log' into table tb_user fields terminated by ',' lines terminated by '\n';

1.主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，可将可以利用这点，来提高导入数据的效率

2.关闭唯一性校验

-- 查看唯一性校验
show variables LIKE 'unique_checks';

-- 关闭唯一性校验
set unique_checks=0;

-- 查看是否允许客户端可以使用LOAD DATA LOCAL INFILE语句将本地文件加载到MySQL服务器
show global variables LIKE 'local_infile';

3.优化插入语句

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句。
这种方式将大大的缩减客户端与数据库之间的连接，关闭等消耗。使得效率比分开执行的单个insert语句快。

4.在事务中进行数据插入

插入多条数据后一次性提交。

优化order by语句

不要用select *；
把*改为索引字段，就会使用索引。
如果*这里包含非索引字段,就要额外排序,效率低。

orde by后面的多个排序字段的排序顺序尽量相同。
orde by后面的多个排序字段顺序尽量和组合索引字段的顺序一致。

数据准备

两种排序方式

数据准备

Filesort的优化

MySQL 8 不再支持 max_length_for_sort_data 系统变量，该变量在 MySQL 5.7 中已被废弃。这是因为在 MySQL 8 中，排序和分组操作默认使用字典排序(collation)而不是二进制排序(binary sorting)。

-- 查看可用于排序的最大数据长度
show variables LIKE 'max_length_for_sort_data';

-- 查看排序操作所使用的缓冲区大小
show variables LIKE 'sort_buffer_size';

优化子查询

通常情况下，SQL多表连接查询（Join）比子查询效率高。这是因为在使用子查询时，需要执行多个查询语句，而在使用关联查询时只需要执行单个查询语句。
连接查询(Join)不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。



性能由高到低的连接类型为：
system > const > eq_ref > ref > range > index > ALL

优化limit查询

优化前的结果：

优化后的结果：

SQL优化总结

• 合理设计数据库架构：要考虑哪些列需要建立索引，合理的数据库设计可以减少冗余，提高性能。
• 优化查询语句：使用正确的索引、避免全表扫描、使用 UNION ALL 替代 UNION、减少不必要的连接等。
• 使用分页优化：当需要查询大量数据并分页显示时，可以通过 LIMIT 和 OFFSET 来进行优化。
• 避免使用 SELECT *：只选择需要的列而不是全部列，可以提高性能。
• 使用合适的数据类型：使用小的数据类型来存储数据可以减少磁盘空间、提高查询速度。
• 使用分区表：可以将大型表分成小的片段，以减少查询时间。
• 使用缓存：使用缓存可以减少查询数据库的次数，提高性能。
• 定期优化表：定期进行表优化，可以减少磁盘碎片，提高性能。
• 避免使用子查询：尽量避免使用子查询，因为它们会引起额外的开销。
• 减少 JOIN 操作次数：JOIN 操作需要消耗大量 CPU 和内存资源，所以应该尽可能减少 JOIN 操作的次数。
• 使用索引：使用索引可以加快查询速度，但也要注意不要滥用索引，否则会导致性能下降。
• 使用 EXPLAIN 进行查询计划分析：使用 EXPLAIN 可以查看 MySQL 如何执行查询，从而找到查询效率低下的原因。