问题：

MySQL-优化查询以使用临时查询和使用filesort删除

司徒瀚

2023-03-14

SELECT SQL_NO_CACHE
 `Table1`.`recordID`
FROM
  `Table1`
  LEFT JOIN `Table3` ON `Table3`.`table1RecordID`=`Table1`.`recordID`
WHERE
  (`Table3`.`status` = '3' OR `Table3`.`status` = '4')  AND
  (`Table1`.`groupName` = 'Sample Name')
GROUP BY `Table3`.`recordID` ASC, `Table1`.`recordID` ASC;

+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+
| id | select_type | table  |  type  |        possible_keys         |   key   | key_len |             ref              | rows  |                    Extra                     |
+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | Table3 | ALL    | fk_packageID,regStatus,pkgID | NULL    | NULL    | NULL                         | 11322 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Table1 | eq_ref | PRIMARY,groupName            | PRIMARY | 4       | testDb.Table3.table1RecordID |     1 | Using where                                  |
+----+-------------+--------+--------+------------------------------+---------+---------+------------------------------+-------+----------------------------------------------+

如果我通过“Table1.RecordIdASC”删除组的第二部分，但当我这样做时，数据是不正确的。它为什么要这样做，我如何修复它，并且仍然按照表1分组，除了表3之外，还要先按表1分组。

先进的谢谢！

我有时间接受完整的查询，并将表拉到一个通用表单，以便在没有客户端数据的情况下发布。我能够将模式添加到sqlfiddle中，但如果没有我使用的数据，结果可能会有所不同，而且由于字符的限制，我甚至无法将表前的100行（共7行）放入sqlfiddle中。所以我做了一堆桌子，并在Dropbox上分享。

https://www.dropbox.com/s/9fgu626996utpar/stackoverflow-21291707_test_db_schema_and_data.sql

SELECT
  `t1`.`name` AS `Object1.Name`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=68,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_68`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=67,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_67`,
  GROUP_CONCAT(DISTINCT
  IF(`t5`.`questionID`=66,
    IF(`t6`.`writeInRequired` = 1,
      CONCAT(
        `t6`.`value`,
        ':', `t5`.`writeInResponse`
      ),
      `t6`.`value`
    ),
    NULL
  ) SEPARATOR ', ') AS `Object3.Response_66`,
  `t7`.`firstName` AS `Object8.FirstName`,
  `t7`.`lastName` AS `Object8.LastName`,
  `t7`.`email` AS `Object8.Email`,
  `t1`.`recordID` AS `Object1.PackageID`,
  `t3`.`recordID` AS `Object5.RegistrationID`
FROM
  `Table1` t1
  LEFT JOIN `Table2` t2 ON `t1`.`recordID`=`t2`.`table1RecordID`
  LEFT JOIN `Table3` t3 ON `t3`.`table1RecordID`=`t1`.`recordID`
  LEFT JOIN `Table4` t4 ON `t4`.`table3RecordID`=`t3`.`recordID` AND `t4`.`type` = 1
  LEFT JOIN `Table5` t5 ON `t5`.`objectID`=`t3`.`recordID` AND `t5`.`objectType`='Type2'
  LEFT JOIN `Table6` t6 ON `t6`.`recordID`=`t5`.`table6RecordID`
  JOIN `Table7` t7 ON `t7`.`recordID`=`t4`.`table7RecordID`
WHERE
  `t3`.`status` IN ('3','4')
GROUP BY
  `Object5.RegistrationID` ASC,
  `Object1.PackageID` ASC

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t7
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11627
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
         type: ref
possible_keys: idx_table7RecordID,idx_table3RecordID
          key: idx_table7RecordID
      key_len: 5
          ref: testDb.t7.recordID
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
         type: eq_ref
possible_keys: PRIMARY,table1RecordID_status,idx_status,idx_table1RecordID
          key: PRIMARY
      key_len: 4
          ref: testDb.t4.table3RecordID
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
         type: ref
possible_keys: compositeIDs
          key: compositeIDs
      key_len: 773
          ref: const,testDb.t3.recordID
         rows: 5
     filtered: 100.00
        Extra:
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: t6
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: testDb.t5.table6RecordID
         rows: 1
     filtered: 100.00
        Extra:
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: testDb.t3.table1RecordID
         rows: 1
     filtered: 100.00
        Extra:
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: idx_table1RecordID
          key: idx_table1RecordID
      key_len: 5
          ref: testDb.t1.recordID
         rows: 85
     filtered: 100.00
        Extra: Using index
7 rows in set, 1 warning (0.13 sec)

+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+
| id | select_type | table |  type  |                        possible_keys                        |        key         | key_len |           ref            | rows  | filtered |              Extra              |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE      | t7    | ALL    | PRIMARY                                                     | NULL               | NULL    | NULL                     | 11627 | 100.00   | Using temporary; Using filesort |
|  1 | SIMPLE      | t4    | ref    | idx_table7RecordID,idx_table3RecordID                       | idx_table7RecordID | 5       | testDb.t7.recordID       |     1 | 100.00   | Using where                     |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,table1RecordID_status,idx_status,idx_table1RecordID | PRIMARY            | 4       | testDb.t4.table3RecordID |     1 | 100.00   | Using where                     |
|  1 | SIMPLE      | t5    | ref    | compositeIDs                                                | compositeIDs       | 773     | const,testDb.t3.recordID |     5 | 100.00   |                                 |
|  1 | SIMPLE      | t6    | eq_ref | PRIMARY                                                     | PRIMARY            | 4       | testDb.t5.table6RecordID |     1 | 100.00   |                                 |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY                                                     | PRIMARY            | 4       | testDb.t3.table1RecordID |     1 | 100.00   |                                 |
|  1 | SIMPLE      | t2    | ref    | idx_table1RecordID                                          | idx_table1RecordID | 5       | testDb.t1.recordID       |    85 | 100.00   | Using index                     |
+----+-------------+-------+--------+-------------------------------------------------------------+--------------------+---------+--------------------------+-------+----------+---------------------------------+

再次提前致谢！

删除select子句，只选择第一个表的recordID。这个查询仍然产生与上面相同的解释。

SELECT
  `t1`.`recordID`
FROM
  `Table1` t1
  LEFT JOIN `Table2` t2 ON `t1`.`recordID`=`t2`.`table1RecordID`
  LEFT JOIN `Table3` t3 ON `t3`.`table1RecordID`=`t1`.`recordID`
  LEFT JOIN `Table4` t4 ON `t4`.`table3RecordID`=`t3`.`recordID` AND `t4`.`type` = 1
  LEFT JOIN `Table5` t5 ON `t5`.`objectID`=`t3`.`recordID` AND `t5`.`objectType`='Type2'
  LEFT JOIN `Table6` t6 ON `t6`.`recordID`=`t5`.`table6RecordID`
  JOIN `Table7` t7 ON `t7`.`recordID`=`t4`.`table7RecordID`
WHERE
  `t3`.`status` IN ('3','4')
GROUP BY
  `t3`.`recordID` ASC,
  `t1`.`recordID` ASC;

共有1个答案

侯令雪

2023-03-14

关于您的查询的一些注释：

不需要左外部联接。您正在使用where子句撤消外部联接。
不需要同时按两个字段分组，table1。recordid就足够了。
比一堆与或的比较更易读。

下面是结果查询：

SELECT SQL_NO_CACHE t1.`recordID`
FROM `Table1` t1 JOIN
     `Table3` t3
     ON t3.`table1RecordID` = t1.`recordID`
WHERE t3.`status` in ('3', '4')  AND
      t1.`groupName` = 'Sample Name'
GROUP BY t1.`recordID` ASC;

这样表现更好吗？

如果不是，让我们考虑一下查询在做什么。它正试图根据t3中的内容从t1中筛选出记录。假设table1.recordid是唯一的，那么这将起作用：

SELECT SQL_NO_CACHE t1.`recordID`
FROM `Table1` t1 
WHERE t1.`groupName` = 'Sample Name' and
      exists (select 1
              from Table3 t3
              where t3.`status` in ('3', '4') and
                    t1.RecordId = t3.RecordId
             );

为了获得最佳性能，您应该在表3(RecordId，status)上有一个索引。

类似资料：

使用filters优化查询

使用filters优化查询 ElasticSearch支持多种不同类型的查询方式，这一点大家应该都已熟知。但是在选择哪个文档应该匹配成功，哪个文档应该呈现给用户这一需求上，查询并不是唯一的选择。ElasticSearch 查询DSL允许用户使用的绝大多数查询都会有各自的标识，这些查询也以嵌套到如下的查询类型中： constant_score filterd custom_filters_score
MySQL查询优化-内部查询

问题内容：这就是整个查询… 如果… 和… 有明显的理由吗？正在服用？扩展说明问题答案：您可以始终使用EXPLAIN或EXPLAIN EXTENDED 来查看MySql对查询所做的操作您也可以用稍微不同的方式编写查询，是否尝试过以下方法？看看效果如何会很有趣。我希望它会更快，因为目前，我认为MySql将为您拥有的每个节目运行内部查询1（这样一个查询将运行多次。联接应该更有效。）如果希
Mysql使用索引实现查询优化

本文向大家介绍Mysql使用索引实现查询优化，包括了Mysql使用索引实现查询优化的使用技巧和注意事项，需要的朋友参考一下索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。 1.索引的优点假设你拥有三个未索引的表t1、t2和t3，每个表都分
使用MySQL查询或javascript优化数据

我在mysql中有这些数据。我想调整数据并将其发送到json使用节点js / expressjs与每日，每月，每年的总和。首先我尝试这个查询。这个查询运行良好。但我认为这是浪费。因为反复查询代码数量。输出：我可以用一个查询获取所有数据吗？最后，我想用Expresjs发送这种形式的数据。或此表单我尝试过mysql查询，sequelize，洛达什。但我找不到正确的方法。
MySQL，使用联接删除查询

问题内容：有人可以帮助我了解此查询的问题吗：问题答案：之所以无法使用，是因为MySQL不按照您尝试的方式在delete语句中支持连接语法。使用： …或使用EXISTS：
使用多个左联接优化MySQL查询

问题内容：我有一个NewsStories表格，剩下一些相关表格。每个新闻故事可以具有多个图像，类别和地址。因此查询实质上是：通常每个故事有一些图像和地址，以及1或2个类别。NewsStories表包含大约10,000条文章。问题在于性能相当慢（大约15-20秒，尽管它的确变化很大，有时甚至低至5秒）。我想知道是否有更好的方法来组织查询以加快查询速度（我对SQL还是很陌生）。尤其是，给定故

MySQL-优化查询以使用临时查询和使用filesort删除

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档