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 表(来自 jos_sobipro_object AS o)实际上是查询的关键驱动元素.尝试在 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屋！