对于类似的查询，带有索引的 MySQL 选择性能会降低

Question

我有一个架构

applicants - id, max_res_id, max_visa_id
applicant_files - id, applicatid, fileid, filetype
files - id, name, filetype

申请人 -

CREATE TABLE `applicants` (
  `id` char(36) NOT NULL,
  `max_res_id` char(36) NOT NULL,
  `max_visa_id` char(36) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_res_id` (`max_res_id`) USING BTREE,
  KEY `idx_visa_id` (`max_visa_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

申请人文件

CREATE TABLE `applicant_files` (
  `id` char(36) CHARACTER SET latin1 NOT NULL,
  `applicantid` char(36) CHARACTER SET latin1 DEFAULT NULL,
  `fileid` char(36) CHARACTER SET latin1 DEFAULT NULL,
  `filetype` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `q_applicantfile_fileid` (`fileid`),
  KEY `u_applicantfile_applid` (`applicantid`),
  KEY `idx_filetype` (`filetype`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

文件

CREATE TABLE `files` (
  `id` char(36) NOT NULL,
  `filetype` tinyint(1) NOT NULL,
  `name` text,
  PRIMARY KEY (`id`),
  KEY `idx_filetype` (`filetype`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

max_res_id, max_visa_id 指 applicant_files 的“id”fileid 指的是文件的“id”

现在我有 2 个不同的查询 -

select  f.id as resumeId, f.name as resumeName, f.date_entered as resumeDate,
        a.id as applId
    from  oepl_applicants a
    inner join  applicant_files af
              ON ( a.id in ('id1', 'id2')
              and  a.id = af.applicantid
              and  a.max_res_id = af.id
              and  af.filetype = 1
              and  a.max_res_id != ''
              and  a.max_res_id is not null )
    inner join  files f
              ON ( af.fileid = f.id
              and  f.filetype = 1 ) 
select  f.id as visaId, f.name as visaName, f.date_entered as visaDate,
        a.id as applId
    from  oepl_applicants a
    inner join  applicant_files af 
              ON ( a.id in ('id1', 'id2')
              and  a.id = af.applicantid
              and  a.max_visa_id = af.id
              and  af.filetype = 2
              and  a.max_visa_id != ''
              and  a.max_visa_id is not null )
    inner join  files f 
              ON ( af.fileid = f.id
              and  f.filetype = 2 )

对于 200 个 id(id1、id2、...id200)，第一个查询将在 2 秒内返回结果，而第二个查询将在 30 秒内返回结果。

这里可能出了什么问题？

这两个查询的唯一区别是文件类型不同，并且连接位于两个不同的列上。PS - 与 max_res_id 中的值相比，max_visa_id 中的许多值都是 null(空)

Answer 1

感谢您的CREATE TABLEs .

加入latin1与 utf8使索引的使用无效!

虽然在这种情况下并不重要，但请将“过滤”子句移至 WHERE子句，并仅保留描述 ON 中的表如何相关的子句。条款。例如，在第一个查询中:

    inner join  applicant_files af
              ON   a.id = af.applicantid
              and  a.max_res_id = af.id
    inner join  files f  ON  af.fileid = f.id
            WHERE  a.id in ('id1', 'id2')
              and  f.filetype = 1
              and  af.filetype = 1
              and  a.max_res_id != ''
              and  a.max_res_id is not null

优化器将决定查看表的顺序。从“过滤”子句中，它希望看到这些:

a:        INDEX(max_res_id, id)
af and f: INDEX(filetype)  -- but see note below

然后优化器将查看是否容易到达“下一个”表。这些可能是有益的。 (我注意到您已经有 (id) 。)

af:  INDEX(applicantid, filetype)  -- in either order

请运行EXPLAIN SELECT查看优化器选择的顺序，以及选择的索引来访问每个后续表。

char(36)闻起来像 UUID 或 GUID。很高兴你做了它们CHARACTER SET latin1而不是utf8 。但由于随机性，这些字段对于索引来说非常糟糕。请参阅my blog 。如果可能的话切换到MEDIUMINT UNSIGNED AUTO_INCREMENT尽管这会涉及大量代码和架构更改。

是 filetype 的两个实例吗？多余的？也就是说，您需要检查两个表的文件类型吗？这是很多额外的工作。

为了帮助初始查询，我们需要了解 filetype 值的分布。是1比 2 更常见(或更不常见) ？行是否带有 1 (或 2 )聚集在表格的开头(或结尾)附近？

table 有多大？如果 innodb_buffer_pool_size 的值是多少？你有多少内存？

以下部分或全部可能会共同导致您的性能不佳:

• UUID 的随机性。
• 表的大小超出了 buffer_pool 的容纳范围。
• 没有足够的 RAM 来增大 buffer_pool。
• buffer_pool 太大(相对于 RAM)以至于正在发生交换。

如果这些注释无法提供足够的速度，我会建议重组查询以延迟从 f 获取数据。 :

SELECT f..., x...
    FROM (
        SELECT ... FROM applicants AS a
                   JOIN applicant_files AS af ON ...
                 WHERE ...
         ) AS x
    JOIN files AS f  ON x.fileid = f.id
    WHERE f.filetype = 1

警告:“此处显示的模式是缩小版。” -- 由于您的缩小，我推荐的内容可能不够!