MySQL Query性能帮助，许多同一个表被联接 [英] MySQL Query performance help, many of the same table being joined

问题描述

我正在编写一个创建SQL查询的PHP脚本.该脚本和数据库用于Joomla CMS，特别是用于查询SOBIPro组件的表(以使用在该组件中输入的数据).但是，由于SOBI Pro表的处理方式，字段的每个实例都是表中自己的行，因此这意味着我要拉回的每个字段都包含一个单独的表实例.这似乎效率不高，实际上，在此搜索中它超时了.

I am writing a PHP script that creates an SQL query. This script and database is for the Joomla CMS, and specifically it's querying the SOBIPro component's tables (to use the data entered there in this component). However, due to the way that the SOBI Pro tables are handled, with each instance of a field being its own row in a table, this means including a separate instance of the table for every field I want to pull back. This doesn't seem very efficient, and in fact in this one search it times out.

SQL查询如下(这是在我的PHP代码生成之后):

The SQL query is as follows (this is after being generated by my PHP code):

    SELECT DISTINCT o.id AS entryid, o.parent AS parentID, name.baseData AS title,business.baseData AS business_data,
    contact_fn.baseData AS contact_fn_data ,contact_ln.baseData AS contact_ln_data ,position.baseData AS position_data,
    civic1.baseData AS civic1_data ,civic2.baseData AS civic2_data ,mailing.baseData AS mailing_data,
    community.baseData AS community_data ,municip.baseData AS municip_data ,county.baseData AS county_data,
    province.baseData AS province_data ,country.baseData AS country_data ,postal.baseData AS descr_data,
    phone.baseData AS phone_data ,tollfree.baseData AS tollfree_data ,fax.baseData AS fax_data,
    email.baseData AS email_data ,web.baseData AS web_data ,empTotal.baseData AS empTotal_data
    FROM jos_sobipro_object AS o
    INNER JOIN jos_sobipro_field_data AS name ON name.sid = o.id
    INNER JOIN jos_sobipro_relations AS r ON o.id = r.id
    LEFT JOIN jos_sobipro_field_data AS business ON business.sid = o.id AND business.fid = 36
    LEFT JOIN jos_sobipro_field_data AS contact_fn ON contact_fn.sid = o.id AND contact_fn.fid = 74
    LEFT JOIN jos_sobipro_field_data AS contact_ln ON contact_ln.sid = o.id AND contact_ln.fid = 75
    LEFT JOIN jos_sobipro_field_data AS position ON position.sid = o.id AND position.fid = 76
    LEFT JOIN jos_sobipro_field_data AS civic1 ON civic1.sid = o.id AND civic1.fid = 77
    LEFT JOIN jos_sobipro_field_data AS civic2 ON civic2.sid = o.id AND civic2.fid = 78
    LEFT JOIN jos_sobipro_field_data AS mailing ON mailing.sid = o.id AND mailing.fid = 79
    LEFT JOIN jos_sobipro_field_data AS community ON community.sid = o.id AND community.fid = 80
    LEFT JOIN jos_sobipro_field_data AS municip ON municip.sid = o.id AND municip.fid = 81
    LEFT JOIN jos_sobipro_field_data AS county ON county.sid = o.id AND county.fid = 82
    LEFT JOIN jos_sobipro_field_data AS province ON province.sid = o.id AND province.fid = 83
    LEFT JOIN jos_sobipro_field_data AS country ON country.sid = o.id AND country.fid = 84
    LEFT JOIN jos_sobipro_field_data AS postal ON postal.sid = o.id AND postal.fid = 85
    LEFT JOIN jos_sobipro_field_data AS phone ON phone.sid = o.id AND phone.fid = 86
    LEFT JOIN jos_sobipro_field_data AS tollfree ON tollfree.sid = o.id AND tollfree.fid = 87
    LEFT JOIN jos_sobipro_field_data AS fax ON fax.sid = o.id AND fax.fid = 88
    LEFT JOIN jos_sobipro_field_data AS email ON email.sid = o.id AND email.fid = 89
    LEFT JOIN jos_sobipro_field_data AS web ON web.sid = o.id AND web.fid = 90
    LEFT JOIN jos_sobipro_field_data AS empTotal ON empTotal.sid = o.id AND empTotal.fid = 106
    WHERE o.approved = 1 AND o.oType = 'entry' AND name.fid = 36 AND name.baseData <> ''
    AND name.section = 54 AND r.pid IN (415,418,425,431,458) AND (municip.baseData = "Municipality Name")
    ORDER BY name.baseData ASC

只要不涉及municip.baseData搜索，它似乎都可以快速运行，在这种情况下，即使目录中有15个条目，它也会失败.必须有一种更好的方法来设计此SQL代码，同时仍带回所有需要的字段.该查询通过AJAX调用，最终目录中将有2000多个条目.

It seems to work decently fast as long as the municip.baseData search isn't involved, in which case it flops even at 15 entries in the directory. There has to be a better way to get this SQL code designed, while still bringing back all of the fields needed. This query is called via AJAX, and eventually there will be 2000+ entries in the directory.

这是EXPLAIN输出，根据要求:

Here is the EXPLAIN output, as requested:

    id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
    1   SIMPLE  name    ref     PRIMARY     PRIMARY     8   const,const     15  Using where; Using temporary; Using filesort
    1   SIMPLE  municip     ref     PRIMARY     PRIMARY     4   const   9   Using where
    1   SIMPLE  o   eq_ref  PRIMARY,oType   PRIMARY     4   [[dbname]].municip.sid  1   Using where
    1   SIMPLE  county  ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  province    ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  country     ref     PRIMARY     PRIMARY     4   const   8   
    1   SIMPLE  postal  ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  business    ref     PRIMARY     PRIMARY     4   const   15  
    1   SIMPLE  contact_fn  ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  contact_ln  ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  position    ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  civic1  ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  civic2  ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  phone   ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  tollfree    ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  fax     ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  email   ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  mailing     ref     PRIMARY     PRIMARY     4   const   11  
    1   SIMPLE  community   ref     PRIMARY     PRIMARY     4   const   9   
    1   SIMPLE  web     ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  empTotal    ref     PRIMARY     PRIMARY     4   const   10  
    1   SIMPLE  r   ref     PRIMARY     PRIMARY     4   [[dbname]].name.sid     3   Using where; Using index; Distinct

推荐答案

很多时候，当您过度扩展了JOIN/JOIN/JOIN/etc时，SQL引擎会迷住自己，试图查找较小的结果设置并回退链接的效率较低.您的查询看起来不错.

Many times, when you have an overly extended JOIN/JOIN/JOIN/etc as you have, the SQL engine will get hung on itself trying to find small result sets and backfil the linking in a less efficient manner. Your query LOOKS good.

您的PRIMARY表(FROM jos_sobipro_object AS o)实际上是查询的KEY驱动元素.尝试在MySQL中添加特殊关键字"STRAIGHT_JOIN".

Your PRIMARY table (FROM jos_sobipro_object AS o) is really the KEY driving element to the query. Try adding the "STRAIGHT_JOIN" special keyword with MySQL..

SELECT STRAIGHT_JOIN DISTINCT ...其余查询...

SELECT STRAIGHT_JOIN DISTINCT ... rest of query ...

STRAIGHT_JOIN告诉优化器仅按照我在此处列出的顺序执行查询.然后它将更快地工作.知道第一个表是查询数据的主要对象.

STRAIGHT_JOIN tells the optimizer to just DO the query in the order I've listed here. Then it will work faster KNOWING the first table is the primary for querying the data.

话虽如此，但我并没有确切看到索引信息，我将特别在jos_sobipro_field_data上创建一个索引，以通过(SID，FID)获取查找"数据.

That said, and not exactly seeing index info, I would SPECIFICALLY have an index on jos_sobipro_field_data to GET the "lookup" data by (SID, FID).

我不得不对主表中的14+百万条记录的政府数据并连接到22+个查询表进行类似的处理. MySQL将在30多个小时后挂起.通过添加STRAIGHT_JOIN，查询在大约3个小时内完成了(这是它所做的预期).

I've had to do similar approach with govt data of 14+ million records in main table and joining to 22+ lookup tables. MySQL would hang after 30+ hours. By adding STRAIGHT_JOIN, the query finished in about 3 hours (as expected by what it was doing).

这篇关于MySQL Query性能帮助，许多同一个表被联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！