MySQL优化案例(二）
MySQL优化案例(二）
这是学习笔记的第 1979 篇文章
近期收到慢日志监控报警，在慢日志平台查看，主要瓶颈在于几条创建临时表的SQL语句，占用了大量的临时空间，需要优化。
SQL语句为：
create temporary table `tem_expireClassID`
(
select distinct class_id
from dic_fsm_map_relation
where game_id = 1
and state = 0
and class_id not in (
SELECT distinct json_extract(fsm_info,'$.FSM.ClassID')
FROM dic_fsm_info
where state = 0
and json_extract(fsm_info,'$.FSM.ETime') > unix_timestamp(now())
)
order by class_id；两个表的数据量都在几千条，其实不算多，但是执行时间却差很多。
执行时间为150秒左右。
执行计划为：
+----+--------------------+----------------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+----------------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | PRIMARY | dic_fsm_map_relation | NULL | ALL | plat_id | NULL | NULL | NULL | 2403 | 1.00 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | dic_fsm_info | NULL | ALL | NULL | NULL | NULL | NULL | 1316 | 10.00 | Using where | +----+--------------------+----------------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 2 rows in set, 1 warning (0.00 sec)
系统层优化：
系统临时表空间占用150G左右
[root@hb30-dba-mysql-tgp-124-34 data]# ll total 157854040 -rw-r----- 1 mysql mysql 362 Apr 26 2018 ib_buffer_pool -rw-r----- 1 mysql mysql 2818572288 May 13 14:41 ibdata1 -rw-r----- 1 mysql mysql 158792155136 May 13 14:40 ibtmp1 drwxr-x--- 2 mysql mysql 4096 Apr 18 2018 infra drwxr-x--- 2 mysql mysql 4096 Apr 18 2018 mysql
经过系统优化和业务协调需要做MySQL实例重启，已重置为初始大小，设置阈值为10G。
SQL层优化
SQL语句的优化分析发现，基于json类型的解析差异和字符类型存在较大的性能差异，建议对json的子查询创建临时表。
测试步骤如下：
create table dic_fsm_info3 (classid varchar(30),etime varchar(30)); --可以根据业务特点创建索引
mysql> insert into dic_fsm_info3 select distinct json_extract(fsm_info,'$.FSM.ClassID') ,json_extract(fsm_info,'$.FSM.ETime') from tgp_db.dic_fsm_info where state=0;
Query OK, 334 rows affected (0.12 sec)
Records: 334 Duplicates: 0 Warnings: 0重新执行语句，执行时长优化只0.2秒左右。
select distinct class_id from tgp_db.dic_fsm_map_relation
where game_id = 1
and state = 0
and class_id not in (
SELECT distinct classid
FROM dic_fsm_info3
where etime > unix_timestamp(now())
)
| 2704 |
| 2705 |
| 2707 |
| 2715 |
+----------+
73 rows in set (0.23 sec)JSON类型的解析效率可以通过profile的对比方式来分析：
mysql> show profile cpu for query 1;
+--------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------+----------+----------+------------+
| Sending data | 0.047225 | NULL | NULL |
| executing | 0.000002 | NULL | NULL |
| Sending data | 0.047196 | NULL | NULL |
| executing | 0.000004 | NULL | NULL |而根据字符类型匹配，效率要高两个数量级。
+--------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------+----------+----------+------------+
| Sending data | 0.000128 | NULL | NULL |
| executing | 0.000001 | NULL | NULL |
| Sending data | 0.000126 | NULL | NULL |
| executing | 0.000001 | NULL | NULL |
后续对JSON类型的使用也需要注意以下。