MySQL 连接优化问题

Question

我正在处理一些利用连接的 MySQL 语句。这些查询的性能似乎相当差，并且可以在查询运行期间降低性能。下面列出了我正在使用的几个查询示例。我是 MySQL JOIN 语句的新手，很好奇是否有人可以帮助我优化这些语句以获得更好的性能。

我们使用这两者来查询我们的票务系统数据库，以生成一些关于处理的票证类型、处理票据的位置等的报告。

SELECT * FROM tickets t 
LEFT Join customfieldvalues cv1 ON t.ticketid = cv1.typeid 
LEFT Join customfields cf1 ON cv1.customfieldid = cf1.customfieldid 
LEFT Join customfieldoptions co1 on cv1.fieldvalue = co1.customfieldoptionid 
WHERE t.dateline BETWEEN 1314853200 AND 1317445199 
Group by t.ticketid 
Order by t.Dateline asc;

这是从 9 月 1 日到 9 月 30 日的基本查询(未添加过滤)。运行时间 ~140 秒。如果去掉三个连接线，运行时间将减少到 ~0.01 秒。

SELECT * FROM tickets t 
LEFT Join customfieldvalues cv1 ON t.ticketid = cv1.typeid 
LEFT Join customfields cf1 ON cv1.customfieldid = cf1.customfieldid 
LEFT Join customfieldoptions co1 on cv1.fieldvalue = co1.customfieldoptionid 
LEFT Join customfieldvalues cv2 ON cv1.typeid = cv2.typeid
LEFT Join customfields cf2 ON cv2.customfieldid = cf2.customfieldid
LEFT Join customfieldoptions co2 on cv2.fieldvalue = co2.customfieldoptionid
WHERE t.dateline BETWEEN 1314853200 AND 1317445199 
AND cf1.title ='Customer Type'  AND co1.optionvalue = 'Staff' 
And cf2.title ='Building or Hall' AND co2.optionvalue like '%Stroupe%' 
Group by t.ticketid 
Order by t.Dateline asc;

此查询将是添加了 2 个过滤器的基本查询:客户类型(即员工)和位置建筑物或大厅(Stroupe)。使用与上述相同的时间范围，运行时间约为 0.1 秒。

==============================

编辑:这是列出的第一个查询的 EXPLAIN 命令的输出。

INSERT INTO `table_name` (`id`,`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`) VALUES (1,'SIMPLE','t','range','tickets7','tickets7','4',NULL,601,'Using where; Using temporary; Using filesort');
INSERT INTO `table_name` (`id`,`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`) VALUES (1,'SIMPLE','cv1','ALL',NULL,NULL,NULL,NULL,104679,'');
INSERT INTO `table_name` (`id`,`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`) VALUES (1,'SIMPLE','cf1','eq_ref','PRIMARY','PRIMARY','4','DB.cv1.customfieldid',1,'');
INSERT INTO `table_name` (`id`,`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`) VALUES (1,'SIMPLE','co1','eq_ref','PRIMARY','PRIMARY','4','DB.cv1.fieldvalue',1,'');

这是第二个查询的 EXPLAIN 输出。

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra1,SIMPLE,cf1,ref,"PRIMARY,title1",title1,767,const,1,"Using where; Using temporary; Using filesort"1,SIMPLE,co1,ref,"PRIMARY,optionvalue1",optionvalue1,767,const,1,"Using where"1,SIMPLE,cf2,ref,"PRIMARY,title1",title1,767,const,1,"使用哪里"1,SIMPLE,t,range,"PRIMARY,tickets7,tickets15,tickets16",tickets7,4,NULL,601,"Using where"1,SIMPLE,cv1,ref,customfieldvalues1,customfieldvalues1,8,"DB.cf1.customfieldid,DB.t.ticketid",1,"使用哪里"1,SIMPLE,cv2,ref,customfieldvalues1,customfieldvalues1,8,"DB.cf2.customfieldid,DB.t.ticketid",1,"使用哪里"1,SIMPLE,co2,eq_ref,PRIMARY,PRIMARY,4,DB.cv2.fieldvalue,1,"Using where"

Answer 1

错误的连接是你的left join + where clause = inner join，你需要重写查询。

你是说给我获取 cf1 中的所有行，即使它们不存在，也只获取标题为“客户类型”的行。

如果将左连接更改为内连接，它的性能会更高(尽管可能不会返回您想要的)