MySQL优化

设置慢查日志

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. 优化步骤1：使用主键来排序
   优点：不再使用文件排序。 缺点：随着页数的增加，IO会越来越慢。

2. 优化步骤2：在步骤1的基础上，记录上一次主键扫描位置。

   select id,name
   from p
   where p.id >= 60 and p.id <=65
   order by p.id
   limit 1,5;

   优点：扫描行数固定 。 缺点：主键必须连续且顺序重点内容递增

---

建立索引

1. 在where从句中，order by从句，group by从句，on从句中出现的列。
2. 索引子段越小越好，IO效率会更高。
3. 建立联合索引时，离散程度大的列放在前面。（一般为外键）

---

数据库结构优化

原则：
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

---