mysql开启慢查询

在mysql的shell模式下执行

mysql> show variables like '%log%';

配置开启慢查询，

slow_query_log=ON

慢查询时间为2秒，

long_query_time=2

将没有使用索引的语句记录到慢查询日志，

log_queries_not_using_indexes=ON

查看慢查询日志保存位置

show variables like 'slow%'

查看慢查询日志，文件的后50行（218.11.132.34作为例子）

tail -50 /var/lib/mysql/localhost-slow.log

慢查询日志的存储格式

Time:140606 12:30:17
执行SQL的主机信息
User@Host: root[root] @ localhost []
SQL的执行信息
Query_time: 0.0000031 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SQL执行时间
SET timestamp=1402029017;

• show variables like 'slow_query_log'
• 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

mysqldumpslow分析慢查询日志（分析前3条日志，在centos shell 模式下，通过管道符 | more 查看）

mysqldumpslow -t 3 /var/lib/mysql/localhost-slow.log | more

如何通过慢查询日志

1. 查询次数多且每次查询占用时间长的SQL
2. IO大的SQL
3. 未命中索引的SQL

SQL如何选择合适的列建立索引？

1. 在where从句，group by 从句，order by从句，on 从句中出现的列
2. 索引字段越小越好
3. 离散度大的列放到联合索引的前面
4. 唯一值越多，离散度越好，通过 select count(distinct keyword)

数据库结构优化

选择合适的数据类型
数据类型的选择，重点在于合适二字，如何确定选择的数据类型合适？

1. 使用可以存下你的数据的最小的数据类型
2. 使用简单的数据类型，int要比varchar类型在MySQL处理上简单
3. 尽可能的使用not null 定义字段
4. 尽量少用text类型，非用不可时最好考虑分表

使用int来存储日期时间，利用from_unixtime()，unix_timestamp()两个函数来进行转换
create table test(id int auto_increment not null,timestr int, primary key(id));
insert into test(timestr) values(unix_timestamp('2014-06-01 13:12:00'));
select from_unixtime(timestr) from test;

使用bigint来存储ip地址，利用inet_aton()，inet_ntoa()两个函数来进行转换
create table sessions(id int auto_increment not null, ipaddress bigint, primary key(id));
insert into sessions(ipaddress) values(inet_aton('192.168.0.1'));
select inet_ntoa(ipaddress) from sessions;

表的垂直拆分

把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行：

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

表的水平拆分

为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完成一致的。
常用的水平拆分方法为：

1. 对id进行hash运算，如果要拆分成5个表则使用mod(id, 5)取出0-4个值
2. 针对不同的hashID把数据存到不同的表中

挑战：

1. 跨分区表进行数据查询
2. 统计及后台报表操作