时间戳

首页 > 解决方案 > 了解如何设计 MySQL 索引以获得良好的性能

mysql - 了解如何设计 MySQL 索引以获得良好的性能

问题描述

通过反复试验，我已经为这个查询找到了一个很好的索引，但我真的很想了解为什么只有这个索引有帮助，以及如何避免下次必须重复测试。

日志表的 InnoDB 表结构是： 日志表结构

这是我的查询 - 它正在查找在日志中具有一种操作但没有另一种操作的所有用户。它还限制在某些值org和某个日期范围内。

SELECT DISTINCT USER AS 'Dormant Users'
FROM db.log
WHERE `action` = @a1
  AND `org` = @orgid
  AND `logdate` >= @startdate
  AND USER NOT IN (SELECT DISTINCT USER
             FROM db.log
            WHERE `action` = @a2
              AND `org` = @orgid
              AND `logdate` >= @startdate)
;

在没有索引的情况下，这大约需要 21 秒，并EXPLAIN显示如下：

没有索引的解释

所以，我认为在 , 和 上建立一个索引org可能logdate会action有所帮助。确实如此——如果我以精确的顺​​序在这些列上创建索引，查询时间会减少到大约 0.3 秒，EXPLAIN现在的输出是：

用良好的索引解释

但是，如果我更改索引中列的顺序，或者甚至只是添加另一个不相关的索引（比如在user列上），则查询大约需要 2 秒。

那么，我如何理解甚至设计索引以根据该查询表现良好，并避免添加另一个索引并损害性能的相当退化的情况？或者这只是一个测试案例，看看什么有效？

标签： mysql

解决方案

我的答案不是答案，因为它不是关于如何设置索引，而是如何编写查询以提高效率。

NOT IN如果子查询不是小表，请避免使用：

SELECT DISTINCT l1.USER AS 'Dormant Users'
FROM db.log l1
WHERE `action` = @a1
  AND `org` = @orgid
  AND `logdate` >= @startdate
  AND NOT EXISTS  (SELECT 1
             FROM db.log l2
            WHERE l1.`user` = l2.`user`
              AND l1.`org` = l2.`org`
              AND l2.`action` = @a2
              AND l2.`logdate` >= @startdate)
;

编辑：我删除了解释链接，因为它不是我想的那样。我只是一个熟练的开发人员而不是 DBA。因此，我优化了很多查询，而且我总是得到NOT EXISTS比NOT IN当量高时更好的结果。但我无法争论内部原因（我想这取决于 RDBMS）

推荐阅读