mysql 慢查询、表分区、数据库配置优化

建议实时更新mysql的版本，因为不同版本差距较大，效率上会有较大的提升。

1、慢查询日志

查询是否开启了慢查询日志，off为未开启，on为开启

show variables like 'slow_query_log';

查看没有使用索引的sql语句是否记录到了慢查询日志中

show variables like '%log%';

记录未使用索引的查询

set global log_queries_not_using_indexs=on;

查看超过多长时间的查询会被记录到慢查询日志中,默认为0，生产环境中不要设置成0

show variables like 'long_query_time';

把慢查询日志设置成开启状态

set  global slow_query_log=on;

查看慢查询日志记录在本地的位置

show variables like 'slow_query_log_file';

慢查询日志存储格式

执行SQL的主机信息

# User@Host: root[root] @ localhost []

SQL的执行信息

# Query_time: 0.0024 Lock_time:0.00 Rows_sent:0 Rows_examined:0

SQL执行时间

SET timestamp=1402389328;

SQL的内容

select .....

 

其他的一些操作

set global slow_query_log_file = 'home/mysql/sql_log/mysql-slow.log'

set global log_queries_not_using_indexes=on;

set global long_query_time=1

 

 

慢查询分析工具

1、mysqldumpslow

2、pt-query-digest

2、优化sql语句

count，max优化：注意count null值时不会计算在内

子查询优化：in用Join代替

limit：如果是单列作条件的要加上上一次查询到多少行，进行判断，效率会有提高

索引优化：离散程度大的列放到前面，查看冗余索引（工具：pt-duplicate-key-checker）,删除不用的索引（工具：pt-index-usage）

3、垂直、水平拆分表，前台、管理端两中查询方式，前台使用拆分后的表进行查询，后台使用汇总表查询

大表分区（百万级别、甚至千万级别的数据需要做的优化），提高查询效率

建表时

create table hash(
　　a int(11),
　　b datetime
　　)partition by hash (id)
　　partitions 4;

查看表分区信息：

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='tablename'; 

 

 

4、系统优化：

1、文件最大打开数

2、关闭软件防火墙，尽量使用硬件防火墙

5、mysql数据库优化

常用参数说明：

用于配置innodb的缓冲池如果系统只有数据库，推荐配置量为总内存的75%

innodb_buffer_pool_size

 

可以控制缓冲池的个数

innodb_buffer_pool_instances

 

log 缓冲的大小

innodb_log_buffer_size

 

数据库多少时间把更新刷新到磁盘，0：每秒刷新，1：每次提交刷新，2：每次提交到缓冲区，每秒刷新到磁盘，建议2

innodb_flush_log_at_trx_commint

 

读写数，默认4

innodb_read_io_threads

innodb_write_io_threads

 

控制innodb每一个表使用独立的表空间，默认off，建议on

innodb_file_per_table

 

什么情况下会刷新innodb表的统计信息，

innodb_stats_on_metadata

 

工具：

https://tools.percona.com/wizard

 

6、硬件优化

1、使用单核cpu

2、磁盘io优化，常用raid1+0

 

参考：

http://x125858805.iteye.com/blog/2068120