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%'
执行次数查询
网友评论