mysql优化

内容从底层到应用层的结构来描述，最后简单介绍了下监控和架构
ps: mysql系统5.6

硬件/OS系统

1.优化硬件:更好的cpu更好的内存以及SSD等
2.调整内核最大文件数

查看最大文件数: sysctl fs.file-max
临时修改: sysctl -w fs.file-max=6553500
永久生效则: echo "fs.file-max=6553500" >>/etc/sysctl.conf

3.优化网络:

• DNS配置 尽量使用skip-name-resolve来减少因解析带来的不必要麻烦、
• 检查网络的ping 丢包率
• 通过优化/etc/sysctl.cnf 中的网络参数，提升性能，以下便是参考值

net.ipv4.ip_local_port_range = 1024 65535  改变本地的端口范围
net.ipv4.tcp_max_syn_backlog = 4096   允许更多的连接进入队列
net.ipv4.tcp_fin_timeout = 30    对于只在本地使用的数据库服务器，可以缩短tcp保持状态的超时时间、默认1分钟

数据库系统配置

1. 并发性能
   max_connections:
   MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值
   thread_concurrency: 并发数
   线程状态:
   SHOW STATUS like '%thread%';
   Threads_cached 缓存的 通过thread_cache_size设置
   Threads_connected 当前连接数
   Threads_created 已经创建的数量
   Connections:历史连接数
   未命中 = Treads_created/Connections
   如果未命中率比较高的话，可以提高thread_cache_size,增加并发性能

2. back_log
   MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到 max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过 back_log，将不被授予连接资源。
   back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。

3. open_files_limit:
   MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个,同时还受到系统内核设置的影响

4. qcache,维护复杂，在多cpu的场合下性能没有优势，同时8.0之后官方貌似不再支持，可以通过memcached或者redis等来做缓存

5. sync_binlog
   控制binlog同步到硬盘的频率。

• 设置为0时，由文件系统控制同步或者缓存满了后才同步，性能最好，但可能会丢失数据；
• 设置为1时，同步每一条事务，性能最差，但不会丢失数据；
• 设置为N时，表示每N条同步一次，同样，可能会丢失N条数据

5. innod引擎

• innodb-open-files:
  innodb_open_files的大小对InnoDB效率的影响比较小。但是在InnoDBcrash的情况下，innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。
• innodb_buffer_pool_size
  专用的数据库服务器下需要设置成物理内存的80%大小。不要设置太大，以免因此与操作系统进行分页竞争。注意，在32位系统中，每个进程被限制在2-3.5G，因此不要设置太高。
• innodb_buffer_pool_instances
  当innodb_buffer_pool_size设置超过1G的时候，可以通过这个变量来切分innodb_buffer,每个分片都有自己的lru算法，通过hash
  算法分片，从而提高并发性能。为了达到最好的性能，innodb_buffer_pool_size最好设置为GB((2^30)的整数倍
• innodb_log_file_size
  在一个日志组中，每个log的大小。结合innodb_buffer_pool_size设置其大小，25%-100%。避免不需要的刷新。注意：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。一般取256M可以兼顾性能和recovery的速度。
  上限为每个日志上限大小为4G.一般控制在几个Log文件相加大小在2G以内为佳。具体情况还需要看你的事务大小，数据大小为依据。
• innodb_log_files_in_group
  有多少个innodb_log,这个是用来写redo log的，这几个文件相当于ring队列.一般设置为2-3，写操作频繁的话，可以稍微大些，减少IO操作
• innodb_flush_log_at_trx_commit
  innodb引擎ib_logfile的刷新方式
  • 设置为0:表示每隔一秒把log buffer刷到文件系统中(log file)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去, mysql崩溃会丢掉最后一秒内的事务；
  • 设置为1(默认为1),在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步,不会丢失数据；
  • 设置为2,表示在每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。

注: 大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把 sync_binlog 配置成 1. 为了追求高并发，提升性能，可以设置为 100 或直接用 0.
而和 innodb_flush_log_at_trx_commit 一样，对于支付服务这样的应用，还是比较推荐 sync_binlog = 1

sql语句调优

语句调优的种类太多太多了，需要具体情况具体分析，

大致思路

通过slow_query_log找到耗时的语句，再通过explain调优, 还可以通过 profiling 查看详细的时间耗费,另外GUI工具通常也会给出执行时间
profile 功能默认是关闭的，需要开启，用完后记得关闭
SET profiling=1;
SHOW profiles; // 所有的
SHOW profile FOR QUERY 175; // 某个特定的query的性能
下面详细解释 explain

explain 解释

explain select * from ads_task_play_log;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------+

执行结果重要字段解释:

select_type: select 语句类型

• SIMPLE:简单SELECT(不使用UNION或子查询)
• PRIMARY:最外面的SELECT
• UNION:UNION中的第二个或后面的SELECT语句
• DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
• UNION RESULT:UNION 的结果
• SUBQUERY:子查询中的第一个SELECT
• DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
• DERIVED:导出表的SELECT(FROM子句的子查询)

possible_keys: 用的那个索引

• key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL
• key_len:使用索引的长度

type: The join type(联接类型),几种常见的,效率从高到低

• const:常数查找，如:主键，唯一索引，会很快，因为它们只读取一次!
• eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
• ref:基于连接的查找
• range:基于索引的范围查找
• index:基于索引的扫描。该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
• ALL:对于每个来自于先前的表的行组合,进行完整的表扫描

extra: 而外信息，非常重要

当extra中如果出现Using temporary或者Using filesort时，这说明我们的sql语句就需要进行优化了。
对于Using temporary，当查询涉及多张表时，需要将查询结果放入第三张临时表中来存放。这样势必会降低我们的查询效率;
对于Using filesort,当查询需要排序同时又没有用到索引的时候，会用filesort来排序(全表扫)，当然会比索引要慢，但不一定是决定性因素，可以通过order by null来关闭排序，做对比测试.
filesort的优化:
1.走索引(给需要order by 或者group by 的添加组合索引)
2.优化配置,走更高效的索引算法.主要的配置有max_length_for_sort_data和sort_buffer_size
3.去掉不必要的返回字

简单的调优

查看表的详细信息:

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',  
  CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows',  
  CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size',  
  CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size',  
  CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'  
FROM information_schema.TABLES WHERE table_schema LIKE 'table_name';  

1. 索引优化

• 尽量用索引数据。可以通过explain查看优化.
• 一个表的索引最好不要超过7个，太多的话，会影响写入性能，如果更新不频繁的表，可以适当的增加索引
• 对于组合(多个字段)索引，越是离散的字段越是要靠前, 查询顺序要和联合索引顺序保持一致
• 无论是组合索引还是单个列的索引，尽量不要选择那些唯一性很低的字段,比如说，在只有两个值0和1的字段上建立索引没有多大意义

2. 数据量优化

• 尽可能只选取需要的数据
• 限制结果集，通过分页处理，获取数据

3. 尽量不要超过两个表的联表操作
4. 不要用存储过程，不好维护和移植

监控

• 硬件: cpu和内存(通过telgraf或者zabbix等监控系统处理)

• 数据库:
  日志系统
  log_output 控制log文件输出位置:table,还是file,还是不输出.

  • 慢日志
    通过long_query_time设置时间阈值，超过这个阈值同时slow_query_log又是开启的话，会写入到log中.
    相关配置变量:
    slow_query_log 用来控制是否开启
    slow_query_log_file 慢日志保存位置
    log_queries_not_using_indexes 是否记录没有使用索引的查询
    log_throttle_queries_not_using_indexes 每分钟记录没有使用索引查询的最大条数
    min_examined_row_limit: 查询结果小于这个行数的，则不记入日志
    格式化慢日志:
    mysqldumpslow -s at,al /var/lib/mysql/suse11b-slow.log
  • general log
    全量log,记录所有的log而不仅仅是查询log, 不用的时候要关闭或者定时删除，会消耗很多资源
    general_log 是否开启general_log
    general_log_file 日志输出位置
  • 错误日志
    log_error 变量查看文件路径
    5.7后，可以通过 log_error_verbosity控制错误级别
  • QPS和TPS(这两个目前没什么用)
    QPS:
    每隔一段时间获取一次queries值，通过差值求均值,当qps超过1W，需要小心了(为什么呢?)
    TSP:
    事务总数 = Com_commit + Com_rollback ,同样也是每隔一段时间获取一个值，再求均值。超过4k，就要小心了(同样也不知道为什么这定)
  • 死锁:
    查询是否锁表:
    show OPEN TABLES where In_use > 0;
    查询进程:
    show processlist / show full processlist
    查看正在锁的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    查看等待锁的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    查看 innodb 死锁日志:
    SHOW ENGINE INNODB STATUS;
    死锁日志上半部分说明了事务1在等待什么锁
  • 追踪长时间事务

SELECT 
    trx.trx_id
    ,trx.trx_started
    ,trx.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX AS trx
INNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl 
    ON trx.trx_mysql_thread_id = pl.id
WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 59 SECOND
  AND pl.user <> 'system_user';

架构优化

1. 通过redis或memcached添加缓存，减少数据库的调用
2. 分库分表
3. 读写分离
4. 热备份和双活等