有一个系统从Oracle迁移到MySQL,在Oracle中运行很快,在MySQL基本运行不出来(等了10分钟都无法运行出来),该加的索引都加了,最后用临时表解决问题。
SELECT COUNT(1)
FROM (SELECT a.vendor_id
FROM g_VENDOR a LEFT JOIN g_VENDOR_CONTACT_PERSON b ON a.vendor_id = b.vendor_id) a
LEFT JOIN (SELECT c.vendor_id,
d.vendor_classify_id,
d.vendor_classify_code,
d.vendor_classify_name
FROM g_vendor_classify_link c, ipb_vendor_classify d
WHERE c.vendor_classify_id = d.vendor_classify_id) e ON a.vendor_id =e.vendor_id
LEFT JOIN (SELECT g.object_id,
f.file_name,
f.file_type,
f.state,
f.update_time,
f.file_dir,
h.attachment_type
FROM g_attachment_object_relation g,
g_attachment f
LEFT JOIN g_attachment_extend h
ON f.attachment_id = h.attachment_id
WHERE f.attachment_id = g.attachment_id
AND f.state = 2
AND g.state = 1
AND f.job_type_code = 'g_Vendor_Attachment_File') h ON a.vendor_id = h.object_id;
+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+--------------------- +------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+--------------------- +------+----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5009 | NULL |
| 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 99 | a.vendor_id | 10 | NULL |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 2970 | Using where; Using join buffer (Block Nested Loop) |
| 4 | DERIVED | f | ALL | PRIMARY | NULL | NULL | NULL | 2970 | Using where |
| 4 | DERIVED | g | ref | idx2_g_attachment_object_rel | idx2_g_attachment_object_rel | 98 | f.attachment_id | 1 | Using where |
| 4 | DERIVED | h | ref | ind_tae_attachment_id | ind_tae_attachment_id | 99 | f.attachment_id | 1 | NULL |
| 3 | DERIVED | c | index | NULL | index_vendor_classify | 198 | NULL | 457 | Using where; Using index |
| 3 | DERIVED | d | eq_ref | PRIMARY | PRIMARY | 98 | c.vendor_classify_id | 1 | NULL |
| 2 | DERIVED | a | index | NULL | ind_sv_VENDOR_SOURCE | 5 | NULL | 131 | Using index |
| 2 | DERIVED | b | ref | ind_svcp_vendor_id | ind_svcp_vendor_id | 99 | a.vendor_id | 1 | Using index |
+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+------------------------------------+------+--------------------------------------+
改成如下方式,一共花费6s出来结果:
CREATE TEMPORARY TABLE tmp_g_VENDOR(vendor_id VARCHAR(32));
CREATE INDEX ind_tsv_vendor_id ON tmp_g_VENDOR(vendor_id);
INSERT INTO tmp_g_VENDOR SELECT a.vendor_id
FROM (SELECT a.vendor_id
FROM g_VENDOR a LEFT JOIN g_VENDOR_CONTACT_PERSON b ON a.vendor_id = b.vendor_id) a
LEFT JOIN (SELECT c.vendor_id,
d.vendor_classify_id,
d.vendor_classify_code,
d.vendor_classify_name
FROM g_vendor_classify_link c, ipb_vendor_classify d
WHERE c.vendor_classify_id = d.vendor_classify_id) e ON a.vendor_id = e.vendor_id;
CREATE TEMPORARY TABLE tmp_attachment_object(object_id VARCHAR(100));
CREATE INDEX ind_tao_object_id ON tmp_attachment_object(object_id);
INSERT INTO tmp_attachment_object SELECT g.object_id
FROM
g_attachment_object_relation g,
g_attachment f
LEFT JOIN
g_attachment_extend h
ON f.attachment_id = h.attachment_id
WHERE f.attachment_id = g.attachment_id
AND f.state = 2
AND g.state = 1
AND f.job_type_code = 'g_Vendor_Attachment_File';
SELECT COUNT(1) FROM tmp_g_VENDOR a LEFT JOIN tmp_attachment_object b ON a.vendor_id = b.object_id;
SELECT COUNT(1)
FROM (SELECT a.vendor_id
FROM g_VENDOR a LEFT JOIN g_VENDOR_CONTACT_PERSON b ON a.vendor_id = b.vendor_id) a
LEFT JOIN (SELECT c.vendor_id,
d.vendor_classify_id,
d.vendor_classify_code,
d.vendor_classify_name
FROM g_vendor_classify_link c, ipb_vendor_classify d
WHERE c.vendor_classify_id = d.vendor_classify_id) e ON a.vendor_id =e.vendor_id
LEFT JOIN (SELECT g.object_id,
f.file_name,
f.file_type,
f.state,
f.update_time,
f.file_dir,
h.attachment_type
FROM g_attachment_object_relation g,
g_attachment f
LEFT JOIN g_attachment_extend h
ON f.attachment_id = h.attachment_id
WHERE f.attachment_id = g.attachment_id
AND f.state = 2
AND g.state = 1
AND f.job_type_code = 'g_Vendor_Attachment_File') h ON a.vendor_id = h.object_id;
+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+--------------------- +------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+--------------------- +------+----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5009 | NULL |
| 1 | PRIMARY | <derived3> | ref | <auto_key0> | <auto_key0> | 99 | a.vendor_id | 10 | NULL |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 2970 | Using where; Using join buffer (Block Nested Loop) |
| 4 | DERIVED | f | ALL | PRIMARY | NULL | NULL | NULL | 2970 | Using where |
| 4 | DERIVED | g | ref | idx2_g_attachment_object_rel | idx2_g_attachment_object_rel | 98 | f.attachment_id | 1 | Using where |
| 4 | DERIVED | h | ref | ind_tae_attachment_id | ind_tae_attachment_id | 99 | f.attachment_id | 1 | NULL |
| 3 | DERIVED | c | index | NULL | index_vendor_classify | 198 | NULL | 457 | Using where; Using index |
| 3 | DERIVED | d | eq_ref | PRIMARY | PRIMARY | 98 | c.vendor_classify_id | 1 | NULL |
| 2 | DERIVED | a | index | NULL | ind_sv_VENDOR_SOURCE | 5 | NULL | 131 | Using index |
| 2 | DERIVED | b | ref | ind_svcp_vendor_id | ind_svcp_vendor_id | 99 | a.vendor_id | 1 | Using index |
+----+-------------+------------+--------+--------------------------------+--------------------------------+---------+------------------------------------+------+--------------------------------------+
改成如下方式,一共花费6s出来结果:
CREATE TEMPORARY TABLE tmp_g_VENDOR(vendor_id VARCHAR(32));
CREATE INDEX ind_tsv_vendor_id ON tmp_g_VENDOR(vendor_id);
INSERT INTO tmp_g_VENDOR SELECT a.vendor_id
FROM (SELECT a.vendor_id
FROM g_VENDOR a LEFT JOIN g_VENDOR_CONTACT_PERSON b ON a.vendor_id = b.vendor_id) a
LEFT JOIN (SELECT c.vendor_id,
d.vendor_classify_id,
d.vendor_classify_code,
d.vendor_classify_name
FROM g_vendor_classify_link c, ipb_vendor_classify d
WHERE c.vendor_classify_id = d.vendor_classify_id) e ON a.vendor_id = e.vendor_id;
CREATE TEMPORARY TABLE tmp_attachment_object(object_id VARCHAR(100));
CREATE INDEX ind_tao_object_id ON tmp_attachment_object(object_id);
INSERT INTO tmp_attachment_object SELECT g.object_id
FROM
g_attachment_object_relation g,
g_attachment f
LEFT JOIN
g_attachment_extend h
ON f.attachment_id = h.attachment_id
WHERE f.attachment_id = g.attachment_id
AND f.state = 2
AND g.state = 1
AND f.job_type_code = 'g_Vendor_Attachment_File';
SELECT COUNT(1) FROM tmp_g_VENDOR a LEFT JOIN tmp_attachment_object b ON a.vendor_id = b.object_id;