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)

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) 这两个条件只会存在一个。

# information_schema.tables
mysql> select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = 'mqm' and table_name='t_batch';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| t_batch    |    9527271 |
+------------+------------+
1 row in set (0.02 sec)

# count(*)
mysql> select count(*) from t_batch;
+----------+
| count(*) |
+----------+
| 10474287 |
+----------+
1 row in set (21.52 sec)

mysql> ANALYZE TABLE t_batch;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| mqm.t_batch | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (3.85 sec)

mysql> select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = 'mqm' and table_name='t_batch';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| t_batch    |   10261904 |
+------------+------------+
1 row in set (0.07 sec)

# 生产环境执行的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)

+----+--------------+------------+------+-------------------+-------------------+---------+-------+---------+------------------------------------+
| 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    |
+----+--------------+------------+------+-------------------+-------------------+---------+-------+---------+------------------------------------+

# 添加索引
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)

$ mysql -h 127.0.0.1 -P 4000 -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.25-TiDB-v3.0.0-rc.1-290-g21d2590ac MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| PERFORMANCE_SCHEMA |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)