MySQL · 调优案例 · 千万级大表模糊查询

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

背景

模糊查询：使用 like %% 类 SQL

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
46
47
48
49
50
51
52
# 生产环境执行的SQL
SELECT '' AS batch_fail_id, '' AS 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 AS createDate, b.delete_status
FROM t_batch b
WHERE b.mtl_no LIKE '%0594A%' 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 AS createDate, f.delete_status
FROM t_batch_fail f
WHERE f.mtl_no LIKE '%0594A%' AND f.delete_status = 'N'
ORDER BY createDate DESC
LIMIT 0, 10

# 表数据量
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    |   10261904 | 4895.00MB | 8643.23MB  |
+------------+------------+-----------+------------+
1 row in set (0.00 sec)

+--------------+------------+-----------+------------+
| table_name   | table_rows | data_size | index_size |
+--------------+------------+-----------+------------+
| t_batch_fail |      43683 | 19.56MB   | 14.57MB    |
+--------------+------------+-----------+------------+
1 row in set (0.01 sec)

# 执行时间
10 rows in set (1 min 26.92 sec)

分析

Union All：对两个结果集进行并集操作，包括重复行。

执行计划：

1
2
3
4
5
6
7
+----+--------------+------------+------+-------------------+-------------------+---------+-------+---------+------------------------------------+
| id | select_type  | table      | type | possible_keys     | key               | key_len | ref   | rows    | Extra                              |
+----+--------------+------------+------+-------------------+-------------------+---------+-------+---------+------------------------------------+
|  1 | PRIMARY      | b          | ALL  | NULL              | NULL              | NULL    | NULL  | 9527271 | Using where                        |
|  2 | UNION        | f          | ref  | idx_delete_status | idx_delete_status | 9       | const |   20469 | Using index condition; Using where |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL              | NULL              | NULL    | NULL  |    NULL | Using temporary; Using filesort    |
+----+--------------+------------+------+-------------------+-------------------+---------+-------+---------+------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
# 添加索引
mysql> ALTER TABLE t_batch ADD INDEX idx_delete_status (delete_status);
Query OK, 0 rows affected (2 min 47.24 sec)

# 执行计划
+----+--------------+------------+------+-------------------+-------------------+---------+-------+---------+------------------------------------+
| id | select_type  | table      | type | possible_keys     | key               | key_len | ref   | rows    | Extra                              |
+----+--------------+------------+------+-------------------+-------------------+---------+-------+---------+------------------------------------+
|  1 | PRIMARY      | b          | ref  | idx_delete_status | idx_delete_status | 9       | const | 5130952 | Using index condition; Using where |
|  2 | UNION        | f          | ref  | idx_delete_status | idx_delete_status | 9       | const |   20469 | Using index condition; Using where |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL              | NULL              | NULL    | NULL  |    NULL | Using temporary; Using filesort    |
+----+--------------+------------+------+-------------------+-------------------+---------+-------+---------+------------------------------------+
3 rows in set (0.07 sec)

模糊查询方法对比：

1
2
3
4
5
6
7
8
① mtl_no like '%0594A%'
10 rows in set (1 min 26.92 sec)

② locate('0594A', mtl_no) > 0
10 rows in set (32.71 sec)

③ instr(mtl_no,'0594A') > 0
10 rows in set (32.72 sec)

结论：

  • 这三种方法都只能用全表扫描的方式进行查询，但 locate 和 instr 方法速度比 like 稍快。
  • like %% 此类模糊查询，推荐使用搜索引擎，比如 Elasticsearch。