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%'

执行次数查询