mysql性能调优

目的

• 避免数据库连接超时timeout而导致的5xx错误
• 由于慢查询造成页面无法加载
• 由于阻塞造成数据无法提交

调优思路：

1. 数据库表结构优化
2. SQL和索引优化
3. 系统和Mysql配置优化
4. 硬件设备

一、系统和Mysql配置优化：

操作系统配置优化：

网络方面，/etc/sysctl.conf

#增加tcp支持的队列数net.ipv4.tcp_max_syn_backlog = 65535#减少断开连接时：资源回收net.ipv4.tcp_max_tw_buckets = 8000net.ipv4.tcp_tw_reuse = 1net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_fin_timeout = 10

ulimit -n 打开文件数的限制，/etc/security/limits.conf* soft nofile 65535* hard nofile 65535

mysql本身优化：

所有这篇文章提到的配置都属于 [mysqld]
1. 基本配置
你需要经常察看以下3个配置项
请确定你使用了正确的单位。例如，innodb_buffer_pool_size的单位是MB而max_connection是没有单位的。

innodb_buffer_pool_size#innodb缓冲池大小：。缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证大多数读取操作时使用的是内存而不是硬盘,如果之后innodb表，推荐为总内存的75%innodb_log_file_sizeredo#日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就把它这是成4G。max_connections#你需要比默认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

2. InnoDB配置

innodb_buffer_pool_instances # 指定缓冲池分成多少份。增加查询并发性innodb_file_per_table# 控制innodb每个表使用独立的表空间，默认off，所有的表都会建立在共享表空间,建议设为on，每个表使用独立的表空间，增加并发读写效率。innodb_flush_log_at_trx_commit# 数据库多长时间把变更刷新到磁盘,默认1，可取0，1，2，一般建议2，如果数据安全性要求高则使用1innodb_flush_method # 决定了数据和日志写入硬盘的方式。如果你有硬件RAID控制器，并且其独立缓存采用write-back机制，并有着电池断电保护，那么应该设置配置为O_DIRECT；否则，大多数情况下应将其设为fdatasync（默认值）。innodb_log_buffer_size # 日志缓冲区，最短一秒钟进行刷新，一般不会太大innodb_read_io_threadsinnodb_write_io_threads # 决定innodb读写的IO进程数，默认4innodb_stats_on_metadata# 决定mysql什么情况下会刷新innodb表的统计信息。可设为off，人为去刷新

3. 其他设置

skip-locking#取消文件系统的外部锁，减少出错几率增强稳定性skip-name-resolve#关闭mysql的dns反查功能。这样速度就快了。不过，这样的话就不能在MySQL的授权表中使用主机名了而只能用ip格式。因此在添加这项设置到一个已有系统中必须格外小心wait_timeout=10 #终止空闲时间超过10秒的链接，避免长连接max_connect_errors=10 #10次连接失败就锁定，使用flush hosts 解锁，或mysqladmin flush-hosts解锁query_cache_size #（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用，设置query_cache_size = 0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果你已经为你的应用启用了query cache并且还没有发现任何问题，query cache可能对你有用。这时如果你想停用它，那就得小心了。log_bin：#如果你想让数据库服务器充当主节点的备份节点，那么开启二进制日志是必须的。如果这么做了之后，还别忘了设置server_id为一个唯一的值。就算只有一个服务器，如果你想做基于时间点的数据恢复，这（开启二进制日志）也是很有用的：从你最近的备份中恢复（全量备份），并应用二进制日志中的修改（增量备份）。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽，你可以用 PURGE BINARY LOGS 来清除旧文件，或者设置 expire_logs_days 来指定过多少天日志将被自动清除。记录二进制日志不是没有开销的，所以如果你在一个非主节点的复制节点上不需要它的话，那么建议关闭这个选项。

第三方工具调整：percona 配置向导
https://tools.percona.com/wizard

二、SQL和索引优化

select version()；  --查询mysql版本:show status         --看系统的资源show processlist    --显示系统中正在运行的所有进程。show warnings       --查看最近一个sql语句产生的错误警告，看其他的需要看.err日志show errors--查看数据库设置：    show VARIABLES；    --查看某一参数：例：查询慢查询设置        show variables like '%slow%'; --参数查询：pseudo_thread_id；open_files_limit；max_connectionsshow variables like '%open_files_limit%'; show variables like '%max_connections%'; show variables like '%slow%'; show variables like 'long_query_time';

1. 慢日志

--慢日志：    set global log_slow_queries = on;               # 开启慢日志    set global long_query_time =0.0;                # 设置时间.精确的毫秒    set global log_queries_not_using_indexes = on;  # 设置无索引的查询  --mysqldumpslow命令/path/mysqldumpslow -s c -t 10 /database/mysql/slow-log--  这会输出记录次数最多的10条SQL语句，其中：--  -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；--  -t, 是top n的意思，即为返回前面多少条的数据；--  -g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如

--  /path/mysqldumpslow -s r -t 10 /database/mysql/slow-log--  得到返回记录集最多的10个查询。--  /path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log--  得到按照时间排序的前10条里面含有左连接的查询语句。

1. 查询次数多切每次查询占用时间长的SQL
2. IO大的SQL
   通常为pt-query-digest分析的前几个查询
3. 未命中索引的SQL

2. 如何分析sql查询
explain #查询sql的执行计划。各列的含义：

table：# 关于哪个表的type： # 这列很重要，显示连接使用了那种类型、有无使用索引。从做好到最差为const，eq_reg，ref，range，index，ALLpossible_keys：# 显示可能应用在这张表中的索引，如果为空，没有可能的索引key： # 实际使用的索引，如果为NULL，则没有使用索引key_len：# 使用索引的长度，在不损失精确度的情况下，长度越短越好。ref：# 显示索引的那一列被使用了，如果可能的话，是一个常数。rows：# mysql任务必须检查的用来返回请求数据的行数extra：# 列需要注意的返回值：    Using filesort：看到它，查询就需要优化了，mysql需要进行额外的步骤来发现如何对返回的列进行排序    Using temporary： 看到它，查询就需要优化了，mysql需要创建临时表来存储结果，通常发生在对不同的列进行order by上，而不是group by上

3. 优化

count（）和max（）的优化方法：max（）：加索引。count（*）和count（列），count（列）是不会计算null值的。子查询优化：优化为连接的方法查询。主要一对多的关系是否与数据的重复。使用distinct 进行去重group by的优化：利用子查询limit的优化：时常伴随order by，大多会造成大量io。1-使用主键或者索引列进行order by操作。2-记录上次返回的主键，在下次查询时使用主键过滤（要求主键顺序排序。）where id > 55 and id <=50 order by id limit 1,5

索引优化：如何选择在合适的列上建立索引：
1. 在 where 从句，group by 从句，order by 从句，on 从句中出现的列
2. 索引字段越小越好
3. 多列联合索引，哪列在前面离散度大的列放到联合索引的前面，唯一值越多离散度越大
select count (distinct customer_id)

索引的维护及优化–重复及冗余索引
重复索引：相同的列以相同的顺序建立的同类型的索引

use information_schema;select a.table_schema as '数据名' \,a.table_name as '表名' \,a.index_name as '索引1' \,b.index_name as '索引2' \,a.column_name as '重复列名' \from statistics a join statistics b on \a.table_schema=b.table_schema and a.table_name=b.table_name \and a.seq_in_index=b.seq_in_index and a.column_name=b.column_name \where a.seq_in_index =1 and a.index_name<>b.index_name;

使用 pt-duplicate-key-checker 工具检测重复及冗余索引

pt-duplicate-key-checker -uroot -p '' -h 127.0.0.1

冗余索引：多个索引的前缀相同，或者在联合索引中包含了主键的索引。
由于业务变更，不使用的索引。
通过慢查询和pt-index-usage -uroot -p ” mysql-slow.log 分析，其他数据库有统计不使用索引，mysql没有

三、数据库表结构优化

选择合适的数据库类型

1. 存下数据的最小数据类型
2. 使用简单的数据类型
3. 尽可能使用not null定义字段
4. 尽量少使用text类型，非用不可时最好考虑分表

表的范式化和反范式化
要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖–冗余

反范式化：适当的增加冗余，以达到优化查询的目的，空间换取时间。

数据库结构优化
表的垂直拆分：（宽度）

1. 把不常用的字段单独放到一个表中
2. 把大字段独立放到一个表中
3. 把经常使用的字段放到一起

表的水平拆分：（数据量）

1. 前后台分开，前台使用拆分表，后台使用汇总表

四、硬件设备

硬件优化：
cpu：mysql有一些工作只能使用到单核cpu
mysql5.5 使用的服务器不要超过32核
Disk IO
一般建议数据库使用RAID1+0 、 更好的比如ssd卡等

---

转载请务必保留此出处：http://blog.csdn.net/fgf00/article/details/51820252