设置慢查日志
1.开启‘记录未使用Index’日志
set global logs_queries_not_using_indexes=on;
2.测试阶段先设置查询时间长long_query_time=0。
set global long_query_time=0;
3.设置慢查询状态为on
set global slow_query_log=on;
4.对已有数据库进行查询
use IoT_platform;
select * from t_project_industry;
5.查看慢查日志所在位置
show variables like 'slow%';
+---------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/iZ0q3u7byob2pwZ-slow.log |
+---------------------+-----------------------------------------+
可以看到第三行为日志所在位置
6.退出MySQL,查看慢查日志
tail -50 /var/lib/mysql/iZ0q3u7byob2pwZ-slow.log
# Time: 2018-02-06T06:08:09.473367Z
# User@Host: root[root] @ localhost [] Id: 14280326
# Query_time: 0.000255 Lock_time: 0.000108 Rows_sent: 10 Rows_examined: 10
SET timestamp=1517897289;
select * from t_project_industry;
# Time: 2018-02-06T06:08:33.793089Z
# User@Host: root[root] @ localhost [] Id: 14280326
# Query_time: 0.001980 Lock_time: 0.000189 Rows_sent: 1 Rows_examined: 1018
SET timestamp=1517897313;
show variables like 'long%';
# Time: 2018-02-06T06:08:37.725966Z
# User@Host: root[root] @ localhost [] Id: 14280326
# Query_time: 0.000007 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1018
SET timestamp=1517897317;
# administrator command: Quit;
可以看到查询的SQL被记录下来,并且最后的退出mysql都也被记录。其记录格式为时间,操作用户,查询时间和SQL。
7.最后把long_query_time设置为一个合理的时间(1s)。
set global long_query_time=1;
最后,可以通过mysqldumpslow和pt-query-digest来分析日志,具体可参考其他博文。
MySQL慢查询分析mysqldumpslow
pt-query-digest用法
SQL 优化
MAX() 聚集函数的优化
可以看一下执行计划
mysql> explain select max(data_value) from t_data;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | t_data | NULL | ALL | NULL | NULL | NULL | NULL | 3553331 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可以看到执行max()需要全表扫描(type 是all),需要扫描三百万行,显然效率非常非常低。
那么我们就可以通过建立索引来尝试优化此操作。
create index idx_data_value on t_data(data_value);
mysql> explain select max(data_value) from t_data;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
这样呢,也就是可以大大优化执行效率,不需要再对表进行全部扫描了。
小结:MAX()可以通过建立覆盖索引来优化查询
子查询优化
通常情况下,将子查询优化成为join查询。但是要注意采用join查询可能会造成数据重复,记得使用distinct关键字进行去重操作。
例:原始SQL
select *
from p
where p.id in (
select q.id
from q
where q.name = 'xxx'
);
优化成join查询
select * from p
join q
on p.id = q.id
where q.name = 'xxx';
GroupBy优化
主旨:尽量将操作放在内层查询,减少外层查询的复杂度。
例:原始SQL
select id,name,COUNT(*)
from p
inner join q(q.id)
group by p.id;
优化成为
select p.id,name
from p
inner join (
select q.id,COUNT(*)
from q
group by q.id
) using(q.id);
Limit优化
使用Limit查询,有时会使用到OrderBy,这样就可能造成使用FileSort,从而造成大量IO问题。
例:原始SQL:对name进行排序
select * from p
order by p.name
limit 50,5;
下面展示执行计划
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| 1 | SIMPLE | t_device_info | NULL | ALL | NULL | NULL | NULL | NULL | 65531 | 100.00 | Using filesort |
+----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
可以看到需要扫描全表,并且还使用了FileSort。
优化步骤1:使用主键来排序
优点:不再使用文件排序。 缺点:随着页数的增加,IO会越来越慢。优化步骤2:在步骤1的基础上,记录上一次主键扫描位置。
select id,name from p where p.id >= 60 and p.id <=65 order by p.id limit 1,5;
优点:扫描行数固定 。 缺点:主键必须连续且顺序重点内容递增
建立索引
- 在where从句中,order by从句,group by从句,on从句中出现的列。
- 索引子段越小越好,IO效率会更高。
- 建立联合索引时,离散程度大的列放在前面。(一般为外键)
数据库结构优化
原则:
1. 尽量使用可以存下自己数据的最小的数据类型。(例如时间可以用int来存,需要两个MySQL函数 FROM_UNIXTIME()和UNIX_TIMESTAMP())
2. 使用简单数据类型,int比varchar等类型简单。
3. 尽可能使用not null字段。
4. 尽量不用text类型,费用不可时最好考虑分表。
垂直拆分
旨在为过宽的表(列数过多)进行拆分
原则:
1. 把不常用的字段单独放在一个表中
2. 把大字段单独放在一个表中
3. 把经常一起使用的字段放在一起
水平拆分
旨在单表数据量过多,及时建立了完美的索引,但是效率依旧不高,此时需要将表进行水平拆分。
拆分方法:
1. 按照id进行hash运算,如果要拆分5个表则用mod(id, 5) 取出0 - 4 这5个值
2. 针对不同的hashId,将数据存入不同的表中
存在的问题:
1. 跨分区数据查询复杂
2. 统计和后台报表麻烦
系统配置优化
操作系统配置优化
编辑/etc/sysctl.conf文件,修改有关网络方面的配置
# 减少断开连接时,资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
# 增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
此外,如果系统数据库表很多,势必在打开文件数目时有所限制。因此可以调整一下打开文件数目。
打开文件/etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535