MySQL 5.7 性能调优

前言

最近在使用new relic监控发现有个sql update花了30s, 于是开启了数据库优化的路径…

优化原则

数据库性能优化有两个层面: 数据库本身层面和硬件层面. 两个层面的优化原则各不相同

数据库本身优化原则:

表结构是否合理? 更新较多的应用表设计时需要设置多表但是较少的列. 查询较多的应用应该设置较少的表但是列较多.
索引是否设置合理?
存储引擎是否选择合理
列的类型是否选择合理, 较少的磁盘需求意味着较少的I/O.
并发控制的锁策略是否合理?
用于缓存的内存是否设置合理?

硬件优化原则:

SQL语句优化

优化SQL时主要需要考虑的问题有:

是否在where语句后面的列是否加了索引, mysql5.7是会给外键自动加索引的,但是在使用join或外键查询时还是最好explain一下,看看执行计划.
分步调试查询的每一部分.
对于大表, 尽量减少全表查询
ANALYZE TABLE, 尽量保持statistics table统计表更新, 让优化器使用这些统计信息去构建更高效的执行计划.
需要学习调优技巧, 如索引技巧, 各种存储引擎的参数配置.

开启慢sql查询日志

mysql可以开启慢sql查询的log, 只要sql执行超过long_query_time时间, 同时影响超过min_examined_row_limit行数, 这个慢sql就会被记录.

MySQL [(none)]> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |  //对于文件精细度是毫秒, 对于表精细度是秒
+-----------------+-----------+
MySQL [(none)]> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+

默认情况下, admin的管理查询语句是不会被记录在log, 但是可以被log_slow_admin_statements和log_queries_not_using_indexes修改配置.
慢log记录的执行时间是从获取锁的时间开始了, 直到运行完语句然后释放所有锁.所以获取锁的执行时间是不会被记录.
默认情况下, 慢查询log会记录所有使用非索引查询的慢语句, 这可能会导致log变得很大, 所以可以设置log_queries_not_using_indexes忽略这些使用非索引查询的语句. 或者使用log_throttle_queries_not_using_indexes来限制每分钟记录多少条非索引慢查询记录. mysql server使用如下控制参数顺序来控制一个查询语句是否写入慢查询log:

是否是管理sql语句, 或者开启了log_slow_admin_statements
long_query_time必须被满足,或者log_queries_not_using_indexes被使能, 非索引查询语句也会被写入log
满足min_examined_row_limit行数
那些满足log_throttle_queries_not_using_indexes的查询
log_timestamps控制了写入慢查询log文件的时区, 但是不会影响通用查询log和写入数据库的log.
在cache中满足慢语句的sql是不会写入log, 默认情况下复制的从机也不会记录log.
收集完log后, 可以使用mysqldumpslow 来查看.
所以为了开启慢查询log, 在my.cnf设置如下代码:
1
2
3
4
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 5

增加最大客户端连接数

max_connections指定允许的同时连接客户端, 默认是151(实际最大连接数会+1, 多出来的是给admin使用). 改变这个值会影响mysqld的文件描述符的需求. 如果所需的fd不能满足,server会自动减少这个值.所以一味增大连接数但是不改变table_open_cache可能不能提升性能, 同时还需要确保table_open_cache的值能被操作系统满足.
关于mysql如何打开和关闭表 为了提升性能, 每一个client session会单独打开一个table. table_open_cache和max_connections系统变量决定了最大数量文件可以被打开. 比如如果有200个并发连接, 每个连接最大的表join是N, 那么起码应该设置table_open_cache为200*N + 额外的一定数量. 那么什么时候mysql会关闭不使用的表呢? 会有如下情况表会被关闭:

cache满了, 新的线程想要打开新的table但是不在cache中
当前cache中条目超过了table_open_cache设置, 不再被线程使用table将会被关闭.

使用flush的命名. 可以是flush table/mysqladmin flushtables/mysqladmin refresh.
如果cache满了, mysql怎么分配的table cache呢? 这时候会分配临时cache, 具体是怎么做的还未知.
查询当前表打开的状态, open_tables和opened_tables, 如果opened_tables > table_open_cache应该考虑增大table_open_cache.

MySQL [(none)]> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 1352  |
| Opened_tables | 1457  |
+---------------+-------+
MySQL [(none)]> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |  
+------------------+-------+

增加线程缓存

mysql可以缓存线程, 用于下次用户连接. thread_cache_size默认是-1, 通过8 + (max_connections / 100)计算出. 如果threads_created/connections过大, 可以考虑增大thread_cache_size.

通用和结构化系统变量

mysql支持一种结构化的变量类型, 用于控制key cache的参数. 结构化的系统变量有两个特点:

它的值由一些系统的参数组成
每一个结构化变量类型都可能有多个实例, 每个实例有不同的名称和不同的资源.
一个结构化的key cache变量有如下组件组成, 这些参数可能和MyISAM密切相关:
key_buffer_size
key_cache_block_size
key_cache_division_limit
key_cache_age_threshold