MySQL5.6 运用临时表做SQL优化
发布时间：2022-09-19 12:53:44 所属栏目：MySql教程 来源：
导读： 有一个系统从Oracle迁移到MySQL，在Oracle中运行很快MySQL 临时表，在MySQL基本运行不出来(等了10分钟都无法运行出来),该加的索引都加了，最后用临时表解决问题。
SELECT COUNT(1)
FROM (SELECT
SELECT COUNT(1)
FROM (SELECT
|
有一个系统从Oracle迁移到MySQL，在Oracle中运行很快MySQL 临时表，在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 | | ALL | NULL | NULL | NULL | NULL | 5009 | NULL | | 1 | PRIMARY | | ref | | | 99 | a.vendor_id | 10 | NULL | | 1 | PRIMARY | | 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; （编辑：海洋资讯信息网_我爱站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐