show status
或 show session status
⬆查看当前MySQL服务器连接的会话状态变量信息;
show global status
⬆查看全局状态变量;
flush status
⬆初始化当前会话状态变量
show variables
⬆查看全局系统变量、会话系统变量和静态变量等;
MySQL 缓存:
按缓存读写功能不同划分
- Cache 缓存 (加速读)
- Buffer 缓存 (缓冲写)
按生存周期长短划分
- 全局缓存 例如二进制日志 binlog_cache_size
- 会话缓存 例如结果集缓存 net_buffer_size
- 临时缓存 例如select语句中包含的派生表生成的内存临时表
按存储引擎实现划分
- MySQL 缓存
- MyISAM 缓存
- InnoDB 缓存
超时
show variables like '%timeout%'
⬆查看超时相关变量配置
连接超时
- connect_timeout 建立连接超时
- wait_timeout 保持睡眠状态太长,超时
- interactive_timeout 交互模式下(cmd)保持睡眠状态太长,超时
- net_write_timeout 默认60秒 写超时
- net_read_timeout 默认30秒 读超时
InnoDB 锁等待超时
- innodb_lock_wait_timeout 默认50秒 设置行级锁锁等待时间,超时触发导致行级锁锁等待的SQL语句回滚(若希望整个事务回滚,启动MySQL时开启 innodb_rollback_on_timeout 参数)
- innodb_rollback_on_timeout 默认OFF 回滚上一条导致行级锁锁等待的SQL语句, 设置为ON则回滚整个事务
元数据锁超时 metadata locks
- lock_wait_timeout 默认值1年 31536000 取值范围[1, 31536000]
复制连接超时
- slave_net_timeout 默认3600秒 MySQL主从复制时,从拉取主二进制日志失败后,等待该设置的时间后,再重连主获取数据。 设置为30秒,减少网络问题导致的数据同步延迟。
MyISAM 表的延迟插入超时
- delayed_insert_timeout
MySQL 连接的优化
连接参数
show variables like '%connect%'
⬆查询MySQL服务的连接参数信息
- max_connections 设置最大的并发连接数,拥有SUPER权限的用户可以在连接数达到最大时依然能建立链接。
- max_user_connections 设置指定的MySQL账号的最大并发连接数,设置为0表示不限制
- max_connect_errors 某主机连接到MySQL服务器失败次数过多,超过该值,服务器会拒绝该主机的连接,除非执行 flush hosts
- init_connect 客户机连接服务器时,会先执行 init_connect 参数内设置的SQL语句。SUPER权限的用户连接不会执行这些SQL语句
连接状态
show status like '%connections%'
⬆查看当前实例连接MySQL服务的状态信息
- Connections Mysq服务从启动到现在尝试连接的请求数(包括不能成功建立的连接请求)
- max_used_connections 表示MySQL服务从启动到现在,同一时刻并行连接的最大值。如果 max_used_connections 和 max_connections 相同, 则说明 max_connections 设置过低或者服务器负载上限。
- connection_errors_max_connections 由于MySQL服务器已经达到 max_connections 的上限,连接被拒绝的次数。如果该值过大,则说明 max_connections 设置过低或者服务器负载上限。
连接线程参数
show variables like 'thread%'
⬆查看MySQL连接线程参数信息
- thread_cache_size 表示当前可用的MySQL连接池大小
- thread_concurrency 针对Solaris系统设置为CPU核心数的2倍
- thread_handling 默认为 one-thread-per-connection,值为 no-threads 只能提供一个连接线程
- thread_stack 默认 192KB, 配置连接线程分配的内存大小用于保存每个连接线程的信息
连接状态信息
show status like 'Thread%'
⬆查看连接线程的状态信息
- Threads_cached 当前线程池的线程数
- Threads_connected 当前连接数
- Threads_created 连接线程创建数,该值过大会扩充连接池大小
- Threads_running 不在睡眠状态的连接线程数量
连接池的连接命中率 = (Connections - Threads_created)/ connections * 100%
该值较低时,需要增加 thread_cache_size。
连接请求堆栈
show variables like 'back_log'
⬆查询堆栈中的连接请求(因连接数过大而被塞入)
连接异常
show status like 'Aborted%'
⬆查看连接异常的状态信息
- Aborted_clients MySQL客户机被异常关闭的次数。例如发送的SQL语句过长或者select语句执行结果太大,超过 max_allowed_packet 参数值,或者 wait_timeout、 interactive_timeout ( max_allowed_packet 默认 1M)
- Aborted_connects 试图连接到MySQL服务器而失败的连接次数,该次数过大可能有网络问题。错误的账户名密码或者无效的数据库都会使得该值递增。
其他
show status like 'Slow%'
⬆查看其他链接状态
- Slow_launch_threads 记录创建时间超过 slow_launch_time 的线程数,如果该值过大,可能是服务器过载。 (默认情况下, slow_launch_time 为 2秒)
show status like 'Connection_error%'
⬆查看连接错误的状态统计信息
缓存的优化
show variables like 'host_cache_size'
⬆查询主机名缓存大小
show variables like 'stored_program_cache'
⬆查看MySQL为每个会话提供的存储程序缓存个数上限
show variables like 'innodb_ft_cache_size'
⬆查询InnoDB 全文索引缓存的大小
查询缓存 Query Cache
show variables like '%query_cache%'
⬆查询有关查询缓存的参数设置
- have_query_cache 是否支持查询缓存 YES NO
- query_cache_type 0(OFF) 关闭,1(ON)先到查询缓存中查找,除非
- select 语句中包含 sql_no_cache, 2(DEMOND)不使用查询缓存,除非 select 语句中包含 sql_cache
- query_cache_size 查询缓存的大小
- query_cache_limit 如果 select 语句的结果集大小超过了该值,将不会被添加进查询缓存
- query_cache_min_res_unit 查询缓存是以块为单位分配内存空间,结果集大于该值就会多申请一块,如此反复。合适的值不仅可以减少内存分配操作的次数,还可以减少内存碎片
- query_cache_wlock_invalidate 用于设置行级排他锁与查询缓存之间的关系,默认 0 (false),表示施加行级排他锁时,该表的所有查询缓存依然有效。如果设置为1(true),表示施加行级排他锁时,该表的所有查询缓存将失效。
查询缓存的命中率
set global query_cache_size = 102760448
⬆开启缓存查询,将其内存大小设置为98M
show status like 'Qcache%'
⬆获取当前实例的查询缓存状态,从而可以计算出当前缓存查询的命中率,继而确定 query_cache_size 的设置是否合理
- Qcache_free_memory 当前可用内存
- Qcache_lowmen_prunes 因查询缓存已满而溢出、删除的查询结果个数。该值过大表示需要增加查询缓存大小
- Qcache_hits 使用查询缓存的次数,若该值过小,则考虑是否应该开启查询缓存
- Qcache_total_blocks 查询缓存的总块数
- Qcache_free_blocks 处于空闲的块数(碎片数量)如果该值较大,意味着查询缓存中碎片较多,表明查询结果集比较小,此时可以减少 query_cache_min_res_unit。使用 flush query cache 对碎片进行整理。(reset query cache 会移除查询缓存中的结果集)
- Qcache_inserts 表示此前总共缓存过多少条 select语句的结果集
- Qcache_not_cached 表示没有进入查询缓存的 select语句的个数
- Qcache_queries_in_cache 表示查询缓存中缓存中多少条 select 语句的结果集
结果集缓存
select 语句的结果集会暂存在结果集缓存中,结果集缓存的初始大小由 net_buffer_size 定义(默认16KB),如果 select语句的结果集大小超过初始大小,则会自动扩容,但不会超过 max_allowed_packet 的参数值。select 语句执行成功后,结果集缓存空间会“瘦身”到初始大小。
优化表结构
- 尽量将字段定义为 NOT NULL
- 考虑使用 enum、 set等复合数据类型
- 尽量不存文件、视频等二进制数据
- 数值型字段的比较比字符串效率高很多
SQL语句优化
了解 SQL 的执行频率
show status like 'queries'
⬆执行的 SQL 语句的数量,不统计 com_ping、com_statistics
show global status like 'Com_%'
⬆查看MySQL服务执行各种SQL语句的数量
- com_select
- com_insert 批量插入只记一次
- com_update
- com_delete
可以通过上面的信息了解当前应用偏向于 OLTP 还是 OLAP。
- com_commit
- com_rollback
可以通过上面信息,了解到rollback从而推断程序中存在某些问题。
数据处理状态信息
show global status like 'handler%'
⬆执行次数查询
转载请注明来源,欢迎对文章中的引用来源进行考证,欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论,也可以邮件至 [email protected]