MySQL性能优化：日志、查询分析与优化策略-CSDN博客

本文链接：https://blog.csdn.net/qq_42977003/article/details/127355859

文章目录

sql执行频率

# 查询全局的sql执行频率
show global status;
# 查询当前会话的sql执行频率
show session status;

show global status like '%Com_%'

慢查询日志

# 查看是否开启慢查询
show variables like 'slow_query_log'
# 开启慢查询需要在 /etc/my.cnf配置(window下是my.ini)
#慢查询日志
log_query_log=1
# mysql中设置
set global slow_query_log = 1
#查询超过多长时间记录：单位是S
long_query_time=1
# 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1
# 记录执行较慢的管理语句
log_slow_admin_statements=1

#慢sql日志文件存放位置
slow_query_log_file=D:\mysql\log.slow.log

profile

# 查看数据库是否支持profile
select @@have_profiling;
# 开启profile
set profiling = 1


show profiles;
# 查询某个query_id的每个阶段的耗时
show profile for query 111;
# 查看指定query_id的sql语句cpu的使用情况
show profile cpu for query 105;

explain

id:select查询序列号，id越大越先执行，如果一样按照先后顺序执行
type：连接类型，性能是null、system、const、eq_ref、ref、range、index、all
key_len：实际使用到的索引长度(字节数)，越短越好
filtered：某个表经过搜索条件过滤后再满足接下来条件记录的百分比，越大越好
possible_key：对某个表执行单表查询可能用到的索引
key：单表查询中实际用到的索引
rows：预估需要读取的记录条数
extra：别的额外的信息

插入数据

批量插入
手动提交事务
主键顺序插入

select @@local_infile;
set global local_infile = 1

load data local infile '/xxx/xx' into 表名

主键优化

页分裂
- 主键乱序插入会造成页分裂
页合并：当一个页中删除的数据操作50%
主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度
- 插入数据时，尽量选择顺序插入，使用auto_increment自增主键
- 尽量不要使用UUID做主键或者其它自然主键，如身份证号

order by优化

extra值为using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序都叫FileSort排序
extra值为using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

需要排序的字段，在创建索引的时候可以指定索引的升序和降序，多个字段排序时，需要遵循最左前缀法则、覆盖索引，如果不可避免filesort，可以设置缓存大小，默认大小是256k

create index name_age on user(age desc, name asc);

group by优化

在没有给group by 字段添加索引的情况下 extra的值using temporary

分组的字段需要添加索引，extra的值是using index，可以提交查询速度，需要满足最左前缀法则

limit优化

当数据量过大时，limit的执行速度会变慢，使用子查询和覆盖索引优化limit

# 子查询，早期sql版本可能用不了
explain select * from user where id in (select id from user order by id limit 1000,10)
# 多表查询
explain select t1.* from user t1, (select id from user order by id limit 1000,10) as t2 where t1.id = t2.id;