mysql性能优化
sql语句优化
如何发现有问题的sql？
开启mysql慢查询
show variables like 'slow_query_log'
set global slow_query_log_file='/var/mysql/mysql_log/mysql-slow.log'
set global log_queries_not_using_index=on;
set global long_query_time=1
MySQL慢查日志分析工具之mysqldumpslow
mysqldumpslow 参数 日志文件路径
MySQL慢查日志分析工具之pt-query-digest
输出到文件：
pt-query-digest slow-log > slow_log.report
输出到数据库：
pt-query-digest slow-log -review h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review --create-reviewtable --review-history t=hostname_slow
如何通过慢查日志发现有问题的SQL
查询次数多且每次耗时长的sql
通常为前几个查询sql
IO大的sql
pt-query-digest中Rows examine项
未命中索引的sql
注意pt-query-digest中Rows examine和Row Send的对比，Rows examine远远大于Row Send的sql。
通过explain查询和分析SQL的执行计划
explain sql语句
explain select id,username from student;
table：对应的表
type：最好到最差的连接类型const、eq_reg、ref（常见于连接查询中）、range、index和ALL（表扫描）
possible_keys：显示可能应用在这张表中的索引，如果为空，则表示没有可用的索引。
key：实际使用的索引，为空是表示没有用到索引
key_len：索引的长度。（越小越好）
ref：显示索引的哪一列被使用了，如果可能的话是一个常数。
rows：返回的行数
extra列返回值需要注意：
Using filesort：看到这个的话查询就需要优化了。
Using temporary：看到这个的话查询就需要优化了。一般会出现在group by和order by中。
Count()和Max()的优化
select max(pay_date) from order;
max：可以创建一个索引进行操作
count：
count(*)和count(id)的区别：*会统计所有行数，id只统计列不为NULL的行数
同时查出2015年和2016年电影的数量
select count(release_year='2016' OR NULL) as 2016电影数量',count(release_year='2015' OR NULL) as2016电影数量' from film;
子查询的优化
通常情况下，一般将子查询优化为join的方式进行查询。但是优化时需要注意的是关联键是否存在一对多的关系，要注意重复数据
如果优化成join的方式出现数据重复句，可以使用distinct的方式进行去重处理。
group by的优化
优化前的写法：
eplain select actor.first_name, actor.last_name, c.cnt from sakila.actor inner join sakila.film_actor using(actor_id) group by file_actor.actor_id;
extra：using temporary; using filesort
group by 优化后的写法：
eplain select actor.first_name, actor.last_name, c.cnt from sakila.actor inner join ( select actor_id, count(*) as cnt from sakila.film_actor group by actor_id ) as c using(actor_id);
extra：using index
这样可以减少io等。
Limit查询的优化
limit常用于分页处理，同时会伴随order by从句的使用。因此在大多时候会使用filesort的方式进行，从而导致大量的IO
explain select id, name, sex from student order by name;
extra：using filesort
优化步骤1：使用索引键或者主键进行order by排序
explain select id, name, sex from student order by id limit 10, 5;
优化步骤2：使用过滤条件（避免数据量过大时扫描记录数过多的情况）
explain select id, name, sex from student where id > 10000 and id < 100005 order by id limit 1, 5;
索引优化
如何选择合适的列建立索引
1、where从句、order by从句、group by从句、on从句出现的列
2、索引字段越小越好
3、离散度（唯一值越多离散度越大）大的列放在联合索引的前面
索引优化SQL的方法
重复索引，如主键为id，又声明了unique(id)的索引
冗余索引，索引里面包好了主键。由于innodb中默认索引都包含了主键，因此没有必要在索引里面再次包含索引。
使用pt-duplicate-key-checker工具进行重复及冗余索引的检查
pt-duplicate-key-checker -uroot -ppassword -h 127.0.0.1
索引维护的方法
对于已经不再使用的索引进行删除
pt-index-usage -uroot -p'password' mysql-slow.log
数据库结构优化
选择合适的数据类型
1、使用可以存下数据的最小数据类型
2、使用简单的数据类型。int要比varchar处理上简单
3、尽可能使用not null属性
4、尽量少用text类型，非用不可时可以考虑分表
用int来存储日期时间，使用FROM_UNIXTIME和UNIX_TIMESTAMP进行转换。
使用bigint来存储IP地址，inet_aton()，inet_ntoa()来进行转换。
表的范式化优化
表的反范式化优化
以空间换取时间的方式
如果完全按照三范式的设计会导致关联表过多等一系列问题。
表的垂直拆分
解决表字段过多的问题
拆分原则：
把不常用的字段放在一个表中
把大字段独立放在一个表中
把经常使用的字段放在一起
表的水平拆分
解决表数据量的问题，拆分后表结构是一样的。
存在问题：跨分区表查询、统计及后台报表操作
系统优化
数据库系统配置优化
参考：http://www.cnblogs.com/rwxwsblog/p/5785376.html
MySQL配置文件优化
参考：http://www.cnblogs.com/rwxwsblog/p/5792962.html
第三方配置工具使用
Percon Configuration Wizard
https://tools.percona.com/wizard
服务器硬件优化
参考：http://www.cnblogs.com/rwxwsblog/p/5785376.html
本文转自秋楓博客园博客，原文链接：http://www.cnblogs.com/rwxwsblog/p/5808716.html，如需转载请自行联系原作者
