MYSQL优化（八）

MYSQL慢日志
show variables like ‘slow_query_log’;查看是否开启慢日志
show variables like ‘%log%’;查看所有日志开启情况
set global log_queries_not_using_indexes=on;开启记录使用索引的查询
show variables like ‘long_query_time’;查询时间以上的访问记录在日志中
set global slow_query_log=on;//开启慢日志

慢日志内容
1，执行SQL的主机信息
2，SQL的执行信息
3，SQL执行时间
4，SQL的内容

慢日志分析工具
1，mysqldumpslow -h帮助命令 SQL安装自带 +日志文件目录 | more
2，pt-query-digest slow-log > slow_log_report 输出到文件
pt-query-digest show.log -review \ h=127.0.0.1,D=test,p=root.P=3306,u=root,t=query_review \
–create-reviewtable \ –review-history t=hostname_show 输出到数据库表
关注优化的SQL
1.查询次数多且每次查询占用时间长的SQL
通常为pt-query-digest分析的前几个查询
2.IO大的SQL，数据库的主要瓶颈就在于IO
注意pt-query-digest分析中的Rows examine项。扫描行数多，占用io大
3.未命中索引的SQL
注意pt-query-digest分析中Rows examine（扫描行数）和Rows send（发送行数）的对比
如果这个比值比较大，说明索引命中率不高

explain查询SQL的执行计划判断查询效率
Max()优化CREATE INDEX 创建索引
COUNT()优化会忽略NULL值需要加 COUNT(‘a’ = ‘1’ OR NULL)

子查询优化->join链接的方式
若是一对多关系使用DISTINCT去重复

GROUP BY 优化利用子查询过滤条件后分组之后再内链接

LIMIT分页优化
1，使用主键ORDER BY操作
2，记录上次返回的主键，在下次查询时使用主键过滤（此列必须数据连续的id，通过where限定查询范围）

索引优化
选择合适的索引列
1.在where，group by，order by，on从句中出现的列
2.索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )
3.离散度大得列放在联合索引前面
select count(distinct customer_id), count(distinct staff_id) from payment;数值越大离散度越高，优先在前面
冗余索引重复索引查看工具pt-duplicate-key-checker
删除不用索引pt-index-usage -uroot -p ” mysql-slow.log 大量慢日志分析

选择合适的数据类型
1.使用可存下数据的最小的数据类型
2.使用简单地数据类型，Int要比varchar类型在mysql处理上更简单
3.尽可能使用not null定义字段，这是由innodb的特性决定的，因为非not null的数据可能需要一些额外的字段进行存储，这样就会增加一些IO。可以对非null的字段设置一个默认值
4.尽量少用text，非用不可最好分表，将text字段存放到另一张表中，在需要的时候再使用联合查询，这样可提高查询主表的效率
例子1、用Int存储日期时间
from_unixtime()可将Int类型的时间戳转换为时间格式
select from_unixtime(1392178320); 输出为 2014-02-12 12:12:00
unix_timestamp()可将时间格式转换为Int类型
select unix_timestamp(‘2014-02-12 12:12:00’); 输出为1392178320
例子2
存储IP地址——bigInt
利用inet_aton(),inet_ntoa()转换
select inet_aton(‘192.169.1.1’); 输出为3232301313
select inet_ntoa(3232301313); 输出为192.169.1.1

表结构优化
1，范式化
避免冗余数据，方便多表单独增删改差（类别等）
2，反范式化
使用冗余减少多表链接，加快查询效率
3，垂直拆分
把不常用的字段单独存放到一个表中
把大字段独立存放到一个表中
把经常一起使用的字段放到一起
4，水平拆分
一个表中的数据太多,分多表结构不变

MYSQL配置文件
my.ini
1，内存大小 innodb_buffer_pool_size >= 1024 MB(75%)
2，缓冲池个数 innodb_buffer_pool_instances = 1
3，缓冲大小 innodb_log_buffer_size 日志每秒刷新不用太大
4，数据刷新时间默认1，建议2性能，安全1 innodb_flush_log_at_trx_commit = 1
5，读写IO进程数 innodb_read_io_threads = 4 innodb_write_io_threads = 4
6，独立表空间 innodb_file_per_table = ON
7，刷新表统计信息 innodb_stats_on_metadata = OFF
第三方配置工具
https://tools.percona.com/wizard