MySQL · 调优案例 · HMQM

发表于 | 更新于 | 分类于 | 阅读次数：

问题现象

原因分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
mysql> select table_name,table_rows,CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size from information_schema.tables where TABLE_SCHEMA = 'mqm' and table_name='t_batch';
+------------+------------+-----------+------------+
| table_name | table_rows | data_size | index_size |
+------------+------------+-----------+------------+
| t_batch    |   10289197 | 4895.00MB | 7578.23MB  |
+------------+------------+-----------+------------+
1 row in set (0.03 sec)

DROP INDEX idx_group
Analyze table t_batch;

+------------+------------+-----------+------------+
| table_name | table_rows | data_size | index_size |
+------------+------------+-----------+------------+
| t_batch    |   10657643 | 4895.00MB | 1230.93MB  |
+------------+------------+-----------+------------+
1 row in set (0.00 sec)

+------------+------------+-----------+------------+
| table_name | table_rows | data_size | index_size |
+------------+------------+-----------+------------+
| t_batch    |    9852520 | 4895.00MB | 2863.00MB  |
+------------+------------+-----------+------------+
1 row in set (0.04 sec)

mysql> ALTER TABLE t_batch ADD INDEX idx_check_style_id(check_style_id);
Query OK, 0 rows affected (2 min 27.34 sec)

mysql> ALTER TABLE t_batch ADD INDEX idx_check_status_id(check_status_id);
Query OK, 0 rows affected (1 min 0.12 sec)

mysql> ALTER TABLE t_batch ADD INDEX idx_iqc_leader_id(iqc_leader_id);
Query OK, 0 rows affected (1 min 14.52 sec)

mysql> ALTER TABLE t_batch ADD INDEX idx_create_date(create_date);
Query OK, 0 rows affected (53.03 sec)

mysql> ALTER TABLE t_batch ADD INDEX idx_IQC_manager_id(IQC_manager_id);
Query OK, 0 rows affected (1 min 11.37 sec)

mysql> ALTER TABLE t_batch ADD INDEX idx_syb_id(syb_id);
Query OK, 0 rows affected (1 min 3.97 sec)

mysql> ALTER TABLE t_batch ADD INDEX idx_group(check_style_id, check_status_id, delete_status);
Query OK, 0 rows affected (2 min 3.08 sec)

测试数据库

1
2
实例：10.138.22.192:3306/mqm
账号：hmqm/hmqm
1
2
3
4
5
6
7
8
+----+-------------+---------+-------------+----------------------------------------------------------+---------------------------------------+---------+------+------+---------------------------------------------------------------------+
| id | select_type | table   | type        | possible_keys                                            | key                                   | key_len | ref  | rows | Extra                                                               |
+----+-------------+---------+-------------+----------------------------------------------------------+---------------------------------------+---------+------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t_batch | index_merge | idx_check_style_id,idx_check_status_id,idx_iqc_leader_id | idx_check_status_id,idx_iqc_leader_id | 5,63    | NULL | 4955 | Using intersect(idx_check_status_id,idx_iqc_leader_id); Using where |
+----+-------------+---------+-------------+----------------------------------------------------------+---------------------------------------+---------+------+------+---------------------------------------------------------------------+

type: index_merge: 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
extra: using intersect，表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
explain select '' batch_fail_id,'' uuid_Fail,b.send_goods_No,b.test_type,b.order_no,b.row_no,b.bu,b.mtl_no,b.mtl_name,b.mtl_group,b.mtl_group_name,b.materiel_category_id,b.materiel_category_name,b.pattern_amount,b.plan_pattern_amount,b.goods_unit,b.plan_send_day,b.fact_send_Day,b.stock_Day,b.supply_code,b.supply_name,b.supply_check_level,b.sap_mark_id,b.sap_mark_name,b.syb_id,b.syb_name,b.check_style_id,b.check_style_name,b.check_type_id,b.check_type_name,b.QC_STATUS,b.QC_STATUS_INSTR_DATE,b.QC_NO,b.factory_code,b.goods_sort,b.price_unit,b.stock_price,b.LICHA,b.BSART,b.LGORT,b.plan_Date,b.TEST_CONCLUSION,b.IQC_manager_id,b.IQC_manager_name,b.iqc_leader_id,b.iqc_leader_name,b.test_complete_day,b.test_start_day,b.new_or_old,b.check_status_id,b.is_appeal,b.is_start_check,b.standard_type,b.check_reason,b.is_stop,b.check_time,b.instance_id,b.create_date createDate,b.delete_status
from t_batch b
where b.mtl_no like CONCAT('%470A%') 
and b.syb_id in (18,306,323,17,483,15,519)
and b.lgort like CONCAT('%J191023878681%')
and b.delete_status = 'N'
UNION ALL
select f.batch_fail_id,f.uuid,f.send_goods_No,f.test_type,f.order_no,f.row_no,f.bu,f.mtl_no,f.mtl_name,f.mtl_group,f.mtl_group_name,f.materiel_category_id,f.materiel_category_name,f.pattern_amount,f.plan_pattern_amount,f.goods_unit,f.plan_send_day,f.fact_send_Day,f.stock_Day,f.supply_code,f.supply_name,f.supply_check_level,f.sap_mark_id,f.sap_mark_name,f.syb_id,f.syb_name,f.check_style_id,f.check_style_name,f.check_type_id,f.check_type_name,f.QC_STATUS,f.QC_STATUS_INSTR_DATE,f.QC_NO,f.factory_code,f.goods_sort,f.price_unit,f.stock_price,f.LICHA,f.BSART,f.LGORT,f.plan_Date,f.TEST_CONCLUSION,f.IQC_manager_id,f.IQC_manager_name,f.iqc_leader_id,f.iqc_leader_name,f.test_complete_day,f.test_start_day,f.new_or_old,f.check_status_id,f.is_appeal,f.is_start_check,f.standard_type,f.check_reason,f.is_stop,f.check_time,f.instance_id,f.create_date createDate,f.delete_status
from t_batch_fail f
where f.mtl_no like CONCAT('%470A%')
and f.syb_id in (18,306,323,17,483,15,519)
and f.lgort like CONCAT('%J191023878681%') 
and f.delete_status = 'N'
order by createDate DESC
limit 0,10

// 查询时 limit 0,10
// 导出时 limit 0,5000
// 外检查询入场数据，常用的参数：物料号mtl_no截取后几位，看单号lgort，系统自动获取外检的事业部信息syb_id,有时候是一个事业部，有时候是多个事业部遍历。
and b.syb_id = 18    
and b.syb_id in (18,306,323,17,483,15,519) 这两个条件只会存在一个。