mysql - 优化50W-100W表中的SQL查询

Question

我收到这个查询:

SELECT
count( * ) AS AGGREGATE 
FROM
`report_ifind`
INNER JOIN `report_descriptions` ON `report_ifind`.`seq` = 
`report_descriptions`.`report_seq` 
AND `description` IS NOT NULL 
WHERE
EXISTS (
SELECT
1 
FROM
`report_types` 
WHERE
report_ifind.seq = report_types.report_seq 
AND `type_code` NOT IN ( '002', '001001', '001002', '001003', '099001002', 
'009001001', '003001', '003002', '003003', '004002' ) 
) 
AND `isvalid` = 1 
AND `report_ifind`.`publish_time` >= '2017-06-24'

本次查询共有三个表:

report_ifind
report_descriptions
report_types

那些表的索引:

**report_ifind**:
PRIMARY KEY (`id`),
UNIQUE KEY `idx_seq` (`seq`),
KEY `idx_research_id` (`researcher_id`),
KEY `idx_org_id` (`org_id`),
KEY `idx_publish_time` (`publish_time`) USING BTREE,
KEY `idx_sql_publish_time` (`publish_time`,`seq`) USING BTREE

**report_descriptions**:
PRIMARY KEY (`id`),
KEY `idx_report_seq` (`report_seq`)

**report_types**:
PRIMARY KEY (`id`),
KEY `idx_report_seq` (`report_seq`),
KEY `idx_type_code` (`type_code`)

我知道有些事情不好:

1、“desctiption is not null”速度慢

2、“WHERE EXIST ()”速度慢

3、“SELECT COUNT(*)”速度慢

问题是:如何改进？

解释输出: explain

Answer 1

对于 MySQL 5.6 或更高版本，如果可能，您应该使用 IN 而不是 EXISTS。在这种情况下:

... WHERE report_ifind.seq IN (
          SELECT report_seq FROM report_types 
          WHERE type_code NOT IN ('002', '001001', '001002', '001003', '099001002', 
                                  '009001001', '003001', '003002', '003003', '004002' ) 
    ) ... 

如果不知道您的 WHERE 条件的选择性，就不可能说这是否会使您受益。通常，要推荐如何改进查询，需要查看当前的查询计划(即 EXPLAIN 输出。)