mysql性能调优化指南
Mysql 数据库本身参数优化：（my.ini）
Mysql 查询系统变量：
show variables like 变量名
：show variables like 's_ow_query_log%'
参数优化：
innodb_buffer_pool_size = Gb/MB //缓冲区大小一般为整机内存的70~80%
innodb_buffer_pool_instances=8 //内存缓冲池（将上面配置的内存分成多少份，高IO时，需要配置多个（一般值<=CPU的个数），让数据缓存在内存可并发操作）
max_connections = 2000 //最大连接数
max_user_connections=1000 //指定的mysql用户的最大连接数
innodb_log_buffer_size =64M //日志缓冲区大小,一般不用设置太大，能存下1秒钟操作的数据就行了。
innodb_flush_log_at_trx_commit （这个很关键）一般配置为2，取值0,1,2
0:数据操作时，直接写内存，并不同时写入磁盘
2:数据操作时，直接写内存，并不同时写入磁盘
1:就每个事务提交就会要刷新到磁盘后才算提交完成,这种情况是保证了事务的一致性,但性能会有很大的影响...
0与2的区别：
0：当mysql挂了之后，可能会损失前一秒的事务信息
2：当mysql挂了之后，如果系统文件系统没挂，不会有事务丢失
innodb_read_io_threads = 16 //读IO进程数
innodb_write_io_threads = 16 //写IO进程数
innodb file per table=（0|1） //每一个表是否使用独立的数据表空间，默认为OFF（使用共享表空间），一般建议配置为1。
0：关闭独立的表空间
1：打开独立表空间
说明（InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，
单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。
共享表空间在Insert操作上少有优势。其它都没独立表空间表现好）
innodb_stats_on_metadata=（） //是否动态关闭统计信息(一般关闭，找个时间手动刷新，或定时刷新)
innodb_purge_batch_size //---
innodb_purge_threads //清除操作是否使用单独的线程 1:是 0：否（不支持动态修改， 需要添加到 my.cnf 里修改，需要结合 innodb_purge_batch_size 参数来使用 ， 默认值是 20， 最
大可设置为 5000， 这个参数一般不用 调整， 使用 默认的即可。）
innodb_change_buffering //控制删除缓冲和既有插入缓冲功能， 默认是 all， 此参数支持动态设置
SET GLOBAL innodb_change_buffering = all;
innodb_spin_wait_delay=10 //控制CPU轮循间隔 （参数的值默认是 6， 可动态调整）
innodb_lock_wait_timeout=30 //修改锁的超时时间 （ 默认50 秒）
slow_query_log //慢查询日志监控开关
slow_query_log_file //慢查询监控日志文件
long_query_time | 10.000000 //慢查询时间设置 (单位：S)
--------------mysql 索引优化--------------
查看索引信息：show index from tablename;
执行计划：
explain sql语句：
mysql> explain select from test; 或：explain select from test \G;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 82 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
--字段解释：
输入 explain select * from customer; 后，出现一张表，个行的意思如下：
table－显示此行数据属于哪张表；
type－重要的一列，显示使用了何种连接，从好到差依次为const、eq_ref、ref、range、index、all，下面会详细说明；
type的描述：
system－表只有一行，这是const连接类型的特例；
const－表中一个记录的最大值能够匹配这个查询(索引可以是主键或唯一索引)。因为只有一行，这个值实际就是常数，因为mysql先读这个值，再把它当作常数对待；
eq_ref－从前面的表中，对每一个记录的联合都从表中读取一个记录。在查询使用索引为主键或唯一索引的全部时使用；
ref－只有使用了不是主键或唯一索引的部分时发生。对于前面表的每一行联合，全部记录都将从表中读出，这个连接类型严重依赖索引匹配记录的多少－越少越好；
range－使用索引返回一个范围中的行，比如使用>或<查找时发生；
index－这个连接类型对前面的表中的每一个记录联合进行完全扫描(比all好，因为索引一般小于表数据)；
all－这个连接类型多前面的表中的每一个记录联合进行完全扫描，这个比较糟糕，应该尽量避免。
possible_keys－可以应用在这张表中的索引，如果为null，则表示没有可用索引；
key－实际使用的索引，如为null，表示没有用到索引；
key_len－索引的长度，在不损失精确度的情况下，越短越好；
ref－显示索引的哪一列被使用了，如果可能的话，是个常数；
rows－返回请求数据的行数；
extra－关于mysql如何解析查询的额外信息，下面会详细说明。
extra行的描述：
distinct－mysql找到了域行联合匹配的行，就不再搜索了；
not exists－mysql优化了left join，一旦找到了匹配left join的行，就不再搜索了；
range checked for each－没找到理想的索引，一次对于从前面表中来的每一个行组合；
record(index map: #)－检查使用哪个索引，并用它从表中返回行，这是使用索引最慢的一种；
using filesort－看到这个就需要优化查询了，mysql需要额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行.
using index－列数据是从单使用了索引中的信息而没有读取实际行的表返回的，这发生在对表的全部的请求列都是同一个索引时；
using temporary－看到这个就需要优化查询了，mysql需要创建一个临时表来查询存储结果，这通常发生在多不同的列表进行order by时，而不是group by；
where used－使用了where从句来限制哪些行将与下一张表匹配或是返回给用户。如不想返回表中用的全部行，并连接类型是all或index，这就会发生，也可能是查询有问题。
SQL语句优化：注意SQL语句的书写规则，where条件，order by ,group by ,having , in ,like ,jion on,表顺序，聚合函数的使用，子查询等
索引优化：
1。是否有无重复索引
2。索引字段类型，顺序是否合理
3。是否有无用索引
4。索引利用率
表结构优化：
1.表的字段类型是否合理
2。数据是否冗余
3。根据业务规则建立合理的约束
4。建表时，尽量使字段值不为空(加not null约束)，索引列值尽量离散（不重复）
5。不常用的字段列可适当考虑折分表
6。数据量较大的表，有存储时间，IP地址数据时，转为int ,bigint
INT类型的时间数据转换：
UNIX_TIMESTAMP('2015-01-10 12:00:00') 转int（插入数据时）
FROM_UNIXTIME(时间字段) 取时间字段的值
IP地址数据操作转换：
INET_ATON :IP地址转bigint （inet_aton(192.168.1.1)）
INET_NTOA :BIGINT转IP地址（inet_ntoa(ip地址字段)）
---在mysql中int ,比varchar处理要简单，尽量少使用text类型
数据库主机参数优化：
网络：
net.ipv4.tcp_keepalive_time = 1200 表示当keepalive起用的时候，TCP发送keepalive消息的频度。缺省是2小时，改为20分钟。
net.ipv4.ip_local_port_range = 10000 65000 表示用于向外连接的端口范围,一般低位端口不要设置太低，有可能会用到其它程序固定的端口
net.ipv4.tcp_max_syn_backlog = 65535 表示SYN队列的长度，默认为1024，加大队列长度为65535，可以容纳更多等待连接的网络连接数。
net.ipv4.tcp_max_tw_buckets = 5000 示系统同时保持TIME_WAIT的最大数量，如果超过这个数字，TIME_WAIT将立刻被清除并打印警告信息。默 认为180000，避免被大量的timewait拖死。
net.ipv4.tcp_syncookies = 1 表示开启SYN Cookies
net.ipv4.tcp_tw_reuse=1 表示开启重用,允许将TIME-WAIT sockets重新用于新的TCP连接
net.ipv4.tcp_recycle=1 表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭；
net.ipv4.tcp_fin_timeout=10 修改系統默认的 TIMEOUT 时间。
limit.conf内核相关参数优化：
详见笔记：《limits.conf详解》
表的碎片整理：
查询：show table status from 库名 where name=‘表名’不加where条件，表示查询整个库每个表的状态
当data_free字段不为空时，表示有碎片
整理：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名
下面是mysql的配置文件实例（my.ini）：
[mysqld]
gdb
innodb_file_per_table = true
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 125G
innodb_buffer_pool_instances=8
innodb_log_file_size = 4G
innodb_log_files_in_group = 2
plugin options
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_io_capacity_max = 40000
not innodb options (fixed)
port = 3306
back_log = 50
max_connections = 2000
max_prepared_stmt_count=500000
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1000
query_cache_size = 0
query_cache_type = 0
ft_min_word_len = 4
thread_stack = 192K
tmp_table_size = 64M
server-id = 10
*** MyISAM Specific options
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
user=root
skip-grant-tables
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 4
max_connections = 2000
innodb_log_buffer_size = 64M
max_user_connections = 1000
innodb_flush_log_at_trx_commit = 2
innodb_write_io_threads = 16