MySQL性能优化
大约 6 分钟
慢查询日志
MySQL 的慢查询日志功能默认是关闭的,需要手动开启。
-- 查询是否开启了慢查询,默认是关闭
show variables like '%slow_query%';
-- slow_query_log:是否开启慢查询日志,1为开启,0为关闭。
-- slow-query-log-file:MySQL数据库慢查询日志存储路径。
-- 慢查询阈值,当查询时间多于设定的阈值时,记录日志,【单位为秒】
show variables like '%long_query_time%';
-- 临时开启慢查询功能重启MySQL的话将失效
set global slow_query_log = ON;
set global long_query_time = 1;
-- 永久开启慢查询功能
vi /etc/my.cnf 中配置如下
[mysqld]
slow_query_log=ON
long_query_time=1
慢查询日志的工具
mysqldumpslow 是 MySQL 自带的慢查询日志工具
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
- -s:是表示按照何种方式排序
al 平均锁定时间
ar 平均返回记录时间
at 平均查询时间(默认)
c 计数
l 锁定时间
r 返回记录
t 查询时间 - -t:是 top n 的意思,即为返回前面多少条的数据
- -g:后边可以写一个正则匹配模式,大小写不敏感的
查看执行计划
mysql> EXPLAIN SELECT t1.* FROM ( SELECT t.name, t.address FROM tuser t WHERE age = ( SELECT age FROM tuser WHERE id = 1 ) ) t1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | t | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
| 3 | SUBQUERY | tuser | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
-- id
SELECT查询的标识符。每个SELECT都会自动分配一个唯一的标识符。
id相同,执行顺序由上到下,id越大,优先级越高。
-- select_type(重要)
单位查询的查询类型,比如:普通查询、联合查询(union、union all)、子查询等复杂查询。
- primary:一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type
- union:union连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表select_type都是union
- dependent union:与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响
- union result:包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为null
- subquery:除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
- dependent subquery:与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
- derived:from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select
-- table
如果查询使用了别名,那么这里显示的是别名
如果不涉及对数据表的操作,那么这显示为null
如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。
如果是尖括号括起来的<union M,N>,与类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。
-- partitions
使用的哪些分区(对于非分区表值为null)。
-- type(重要)
显示的是单位查询的连接类型或者理解为访问类型,访问性能依次从好到差:
system > const eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > ALL
- 除了all之外,其他的type都可以使用到索引
- 除了index_merge之外,其他的type只可以用到一个索引
- 最少要使用到range级别
-- possible_keys
此次查询中可能选用的索引,一个或多个
-- key
此次查询中确切使用到的索引.
-- key_len
key_len越小 索引效果越好。
-- ref
如果是使用的常数等值查询,这里会显示const
如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段
如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
-- rows
显示此查询一共扫描了多少行. 这个是一个估计值.
-- filtered
表示此查询条件所过滤的数据的百分比
-- extra(重要)
profile 分析语句
是 MySQL 自带的一种 query 诊断分析工具,通过它可以分析出一条 SQL 语句的硬件性能瓶颈在什么地方。
默认的情况下,MYSQL 的该功能没有打开,需要自己手动启动。
-- 查看是否开启了Profile功能,两个都行。
select @@profiling;
show variables like '%profil%';
-- 开启profile功能,1是开启、0是关闭
set profiling=1;
-- 显示当前会话的资源使用情况
show profiles;
show profile;
-- 查看`show profiles`结果中第二条语句的执行情况
show profile for query 2;
-- 指定资源类型查询
show profile cpu,swaps for query 2;
系统参数优化
缓冲区优化 设置
innodb_buffer_pool_size
建议为总内存大小的 3/4 或者 4/5。-- 查看innodb_buffer_pool_size的使用情况 show global status like 'innodb_buffer_pool_pages_%'; -- Innodb_buffer_pool_pages_free为0,则表示buffer_pool已经被用光了。
降低磁盘写入次数
- 对于生产环境来说,很多日志是不需要开启的,比如:通用查询日志、慢查询日志、错误日志
- 使用足够大的写入缓存 innodb_log_file_size,推荐设置为 0.25 * innodb_buffer_pool_size
- 设置合适的 innodb_flush_log_at_trx_commit,和日志落盘有关系。
MySQL 数据库配置优化
innodb_buffer_pool_size
缓冲池字节大小推荐值为物理内存的 50%~80%。innodb_flush_log_at_trx_commit=1
来控制 redo log 刷新到磁盘的策略。sync_binlog=1
每提交 1 次事务同步写到磁盘中,可以设置为 n。innodb_max_dirty_pages_pct=30
脏页占 innodb_buffer_pool_size 的比例时,触发刷脏页到磁盘。 推荐值为 25%~50%。innodb_io_capacity=200
后台进程最大 IO 性能指标。默认 200,如果 SSD,调整为 5000~20000innodb_data_file_path
指定 innodb 共享表空间文件的大小。long_qurey_time=0.3
慢查询日志的阈值设置,单位秒。binlog_format=row
mysql 复制的形式,row 为 MySQL8.0 的默认形式。max_connections=200
调高该参数则应降低 interactive_timeout、wait_timeout 的值。innodb_log_file_size
过大,实例恢复时间长;过小,造成日志切换频繁。general_log=0
全量日志建议关闭。默认关闭。
操作系统优化
内核参数优化
cat > /etc/sysctl.conf << EOF # 增加监听队列上限: net.core.somaxconn = 65535 net.core.netdev_max_backlog = 65535 net.ipv4.tcp_max_syn_backlog = 65535 # 加快TCP连接的回收: net.ipv4.tcp_fin_timeout = 10 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 # TCP连接接收和发送缓冲区大小的默认值和最大值: net.core.wmem_default = 87380 net.core.wmem_max = 16777216 net.core.rmem_default = 87380 net.core.rmem_max = 16777216 # 减少失效连接所占用的TCP资源的数量,加快资源回收的效率: net.ipv4.tcp_keepalive_time = 120 net.ipv4.tcp_keepalive_intvl = 30 net.ipv4.tcp_keepalive_probes = 3 # 单个共享内存段的最大值: kernel.shmmax = 4294967295 # 控制换出运行时内存的相对权重: vm.swappiness = 0 EOF
增加资源限制
# 配置将可打开的文件数量增加到65535个,以保证可以打开足够多的文件句柄。重启系统才能生效。 cat > /etc/security/limit.conf << EOF # *:表示对所有用户有效 # soft:表示当前系统生效的设置(soft不能大于hard ) # hard:表明系统中所能设定的最大值 # nofile:表示所限制的资源是打开文件的最大数目 # 65535:限制的数量 * soft nofile 65535 * hard nofile 65535 EOF
磁盘调度策略
硬件优化