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 如何执行查询,从而找到查询效率低下的原因。