MySQL : Using EXPLAIN keyword to know about indexing.(特定用例)

Question

这是我的表结构:

CREATE TABLE `channel_play_times_bar_chart` (
  `playing_date` datetime NOT NULL,
  `channel_report_tag` varchar(50) NOT NULL,
  `country_code` varchar(50) NOT NULL,
  `device_report_tag` int(11) NOT NULL,
  `greater_than_30_minutes` decimal(10,0) NOT NULL,
  `15_to_30_minutes` decimal(10,0) NOT NULL,
  `0-15_minutes` decimal(10,0) NOT NULL,
  PRIMARY KEY (`country_code`,`device_report_tag`,`channel_report_tag`,`playing_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

当我运行以下查询时:

EXPLAIN EXTENDED 
SELECT 
  channel_report_tag,
  SUM(`greater_than_30_minutes`) AS '>30 minutes',
  SUM(`15_to_30_minutes`) AS '15-30 Minutes',
  SUM(`0-15_minutes`) AS '0-15 Minutes' 
FROM
  channel_play_times_bar_chart USE INDEX (ABCDE) 
WHERE country_code = 'US' 
  AND device_report_tag = 14 
  AND channel_report_tag = 'DUNYA NEWS' 
  AND playing_date BETWEEN '2016-09-01' 
  AND '2016-09-13' 
GROUP BY channel_report_tag 
ORDER BY SUM(`greater_than_30_minutes`) DESC 
LIMIT 10 

这是我得到的输出(在另一个选项卡中打开它):

索引定义为:

CREATE INDEX ABCDE 
ON channel_play_times_bar_chart (
  `country_code`,
  `device_report_tag`,
  `channel_report_tag`,
  `playing_date`,
  `greater_than_30_minutes`
)

我在这里有点困惑； key 列显示 ABCDE 被用作 index ，而 ref 列显示 NULL。这是什么意思 ？索引是否实际被使用？如果不是，我做错了什么？

Answer 1

它正在使用您在 create index 中显示的 key , 即 ABCDE .如果你做了一个就好了

show create table channel_play_times_bar_chart

然后一次全部显示出来。该键可能对您没有多大用处，因为它复制了您相当广泛的主键已经为您提供的大部分内容。

一旦查询使用到复合键的第 3 段的键，它就会以 WHERE 恢复。范围在 playing_date在该组合中找到 8 行。

备注EXPLAIN是一个估计值。

此外，我会重新考虑您的 PRIMARY KEY 的策略( PK ) 想法，特别是考虑到您决定通过创建 ABCDE 来或多或少地欺骗它.这意味着您正在维护两个索引，而第二个索引几乎没有任何收获(您将一列添加到二级索引 ABCDE )。

PK它相当宽(我相信是 118 字节)。它规定了物理顺序。如果在构建事物的整个过程中都使用这个想法，那么这个想法很容易成为一个糟糕的想法。通过 UPDATE 对数据所做的更改影响 PK 中的列强制重新调整表的物理顺序。这个事实很好地说明了为什么 id INT AUTO_INCREMENT PRIMARY KEY通常用作最佳实践用例，因为它永远不会经历重新洗牌并且很薄(4 字节)。

键的宽度及其与引用(其他)表(在外键约束中)的策略会影响键大小和查找性能。宽键可以显着减慢该过程。

这并不是说您不应该在这些列上设置键，例如您的二级索引 ABCDE .但总的来说，这对于 PK 不是一个好主意。 .

请注意，可以认为 ABCDE永远不会给你任何 PK 的好处，因为范围查询在接近尾声时停止使用它，即 WHERE一旦达到日期，就会超出范围。只是一个想法。

文章 Using EXPLAIN to Write Better MySQL Queries 是一篇不错的读物，而且相当简短。 .