mysql - 哎呀!扫描了 40 亿行。这是正确的吗？

Question

我对这个很困惑。根据 EXPLAIN，我觉得没问题，但 MYSQL 慢速日志报告的结果截然不同:

Count         : 21  (8.50%)
Time          : 174 s total, 8.285714 s avg, 6 s to 16 s max  (7.87%)
95% of Time   : 142 s total, 7.473684 s avg, 6 s to 10 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
95% of Lock   : 0 total, 0 avg, 0 to 0 max
Rows sent     : 34 avg, 1 to 42 max  (0.05%)
Rows examined : 2.63G avg, 8.13M to 4.22G max  (99.97%)
Database      : 
Users         : 
hub@localhost 127.0.0.1 : 100.00% (21) of query, 98.38% (243) of all users

现在，如果我对此查询执行 EXPLAIN:

mysql> explain SELECT t.* FROM teamproject t WHERE    t.company_id= 3494 AND t.template=0 AND t.id IN (SELECT DISTINCT a.targetId FROM acl a WHERE a.targetId=t.id and a.targetType='hub.app.model.TeamProject' AND a.company_id = 3494 AND a.role_id=4 AND a.permission_id=4  UNION  SELECT DISTINCT a.targetId FROM acl a WHERE a.targetId=t.id AND a.targetType='hub.app.model.TeamProject' AND a.company_id = 3494 AND a.role_id=-1 and a.user_id= 20929 ) ORDER BY t.name ASC limit 500 \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t
         type: ref
possible_keys: FK2EF9161C3D5FD8EF
          key: FK2EF9161C3D5FD8EF
      key_len: 8
          ref: const
         rows: 93
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: a
         type: ref
possible_keys: company_id,FK1788A3D5FD8EF,FK1788AA16C7AE5,IDX1788AA27C8AE1,IDX1788AA27C8AE2,IDX1788AA27C8AE4,IDX1788AA27C8AE5,IDX1788AA27C8999
          key: company_id
      key_len: 18
          ref: const,const
         rows: 1
        Extra: Using where; Using index; Using temporary
*************************** 3. row ***************************
           id: 3
  select_type: DEPENDENT UNION
        table: a
         type: ref
possible_keys: company_id,FK1788A3D5FD8EF,IDX1788AA27C8AE1,IDX1788AA27C8AE2,IDX1788AA27C8AE4,IDX1788AA27C8AE5,IDX1788AA27C8AE6,IDX1788AA27C8999
          key: IDX1788AA27C8AE1
      key_len: 9
          ref: func
         rows: 5
        Extra: Using where; Using temporary
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: 
4 rows in set (0.01 sec)

就我所见，它看起来还不错，但我遗漏了一些东西，我就是看不到它。有什么想法吗？

更新好的，这是使用 g.d.d.c sql 的 EXPLAIN(看起来好多了):

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t
         type: ref
possible_keys: FK2EF9161C3D5FD8EF
          key: FK2EF9161C3D5FD8EF
      key_len: 8
          ref: const
         rows: 93
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: a
         type: index_subquery
possible_keys: company_id,FK1788A3D5FD8EF,FK1788AA16C7AE5,IDX1788AA27C8AE1,IDX1788AA27C8AE2,IDX1788AA27C8AE4,IDX1788AA27C8AE5,IDX1788AA27C8AE6,IDX1788AA27C8999
          key: IDX1788AA27C8AE1
      key_len: 9
          ref: func
         rows: 3
        Extra: Using where

acl 上的索引是:

CREATE TABLE `acl` (
  `id` bigint(20) NOT NULL auto_increment,
  `company_id` bigint(20) default NULL,
  `group_id` bigint(20) default NULL,
  `permission_id` bigint(20) default NULL,
  `role_id` bigint(20) default NULL,
  `targetId` bigint(20) default NULL,
  `targetType` varchar(255) default NULL,
  `user_id` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `company_id` (`company_id`,`role_id`,`user_id`,`group_id`,`targetId`,`targetType`,`permission_id`),
  KEY `FK1788A3D5FD8EF` (`company_id`),
  KEY `FK1788AA16C7AE5` (`permission_id`),
  KEY `IDX1788AA27C8AE1` (`targetId`),
  KEY `IDX1788AA27C8AE2` (`targetType`),
  KEY `IDX1788AA27C8AE3` (`group_id`),
  KEY `IDX1788AA27C8AE4` (`role_id`),
  KEY `IDX1788AA27C8AE5` (`company_id`),
  KEY `IDX1788AA27C8AE6` (`user_id`),
  KEY `IDX1788AA27C8999` (`role_id`,`company_id`,`targetId`,`targetType`),
  CONSTRAINT `FK1788A3D5FD8EF` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `FK1788AA16C7AE5` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=576233 DEFAULT CHARSET=utf8 

Answer 1

我相信，首先，您可以重新格式化 where 子句以避免需要 UNION。这可能会删除大量已检查的行。接下来，acl 中索引了哪些列？

SELECT 
  t.* 
FROM 
  teamproject t 
WHERE    
  t.company_id= 3494 
AND 
  t.template=0 
AND 
  t.id IN (
    SELECT DISTINCT 
      a.targetId 
    FROM 
      acl a 
    WHERE 
      a.targetId=t.id 
    and 
      a.targetType='hub.app.model.TeamProject' 
    AND 
      a.company_id = 3494
    AND 
      ((a.role_id=4 
        AND 
       a.permission_id=4)
        OR
       (a.role_id=-1 
        and 
       a.user_id= 20929))
  ) 
ORDER BY 
  t.name ASC limit 500