MYSQL8优化-Toy模板网

这篇具有很好参考价值的文章主要介绍了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