MySQL - 覆盖索引 - 范围查询

Question

现在，我有一个 SQL 查询，例如:

      SELECT
        SUM(dtraff_web) AS dtraff_web,
        SUM(dtraff_ftp) AS dtraff_ftp,
        SUM(dtraff_mail) AS dtraff_mail,
        SUM(dtraff_pop) AS dtraff_pop
      FROM domain_traffic WHERE `dtraff_time` BETWEEN
        UNIX_TIMESTAMP(((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH ))
        AND
        UNIX_TIMESTAMP((LAST_DAY(CURDATE()) + INTERVAL 1 DAY ))
      AND
        domain_id = ?

我添加了一个涵盖所有字段(覆盖索引)的索引，例如:

ALTER TABLE `domain_traffic` ADD INDEX `full_covering` (`domain_id`, `dtraff_time`, `dtraff_web`, `dtraff_ftp`, `dtraff_mail`, `dtraff_pop`) USING BTREE;

为了避免完全扫描，但在解释查询时我得到:'Using where;使用索引'

我会避免“使用何处”。涉及范围的时候可以吗？

当前的“解释”结果是:

mysql>           EXPLAIN SELECT SQL_NO_CACHE
    ->             SUM(dtraff_web) AS dtraff_web,
    ->             SUM(dtraff_ftp) AS dtraff_ftp,
    ->             SUM(dtraff_mail) AS dtraff_mail,
    ->             SUM(dtraff_pop) AS dtraff_pop
    ->           FROM domain_traffic WHERE `dtraff_time` BETWEEN
    ->             UNIX_TIMESTAMP(((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH ))
    ->             AND
    ->             UNIX_TIMESTAMP((LAST_DAY(CURDATE()) + INTERVAL 1 DAY ))
    ->           AND
    ->             domain_id = 1;
+----+-------------+----------------+------------+-------+----------------------------------+---------------+---------+------+------+----------+--------------------------+
| id | select_type | table          | partitions | type  | possible_keys                    | key           | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------------+------------+-------+----------------------------------+---------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | domain_traffic | NULL       | range | i_unique_timestamp,full_covering | full_covering | 12      | NULL |  959 |   100.00 | Using where; Using index |
+----+-------------+----------------+------------+-------+----------------------------------+---------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec)

mysql> 

表架构为:

mysql> show create table domain_traffic;
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| domain_traffic | CREATE TABLE `domain_traffic` (
  `dtraff_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int(10) unsigned NOT NULL,
  `dtraff_time` bigint(20) unsigned NOT NULL,
  `dtraff_web` bigint(20) unsigned DEFAULT '0',
  `dtraff_ftp` bigint(20) unsigned DEFAULT '0',
  `dtraff_mail` bigint(20) unsigned DEFAULT '0',
  `dtraff_pop` bigint(20) unsigned DEFAULT '0',
  PRIMARY KEY (`dtraff_id`),
  UNIQUE KEY `i_unique_timestamp` (`domain_id`,`dtraff_time`),
  KEY `full_covering` (`domain_id`,`dtraff_time`,`dtraff_web`,`dtraff_ftp`,`dtraff_mail`,`dtraff_pop`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5452211 DEFAULT CHARSET=latin1 |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql>

Answer 1

我并没有在“解释”中投入过多的精力。这是一个线索，但它仍然是巫术。两个相似查询的相同解释输出可以返回相同的解释输出，但性能不同(即将提供引用)。这意味着优化器规则。

下面的插入例程适用于任何想要从这里获取它的人。如果没有向测试表添加足够的大小，则索引不会用于查询。说明书上说当然是这样，只是说而已。因为有些人认为只要看起来应该使用索引就总是会使用索引(即:一个有 10 行的测试表不会使用索引)。

下面是一些鸡抓痕。希望对您有所帮助。

create schema nuxwin099a;
use nuxwin099a;

CREATE TABLE `domain_traffic` (
  `dtraff_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int(10) unsigned NOT NULL,
  `dtraff_time` bigint(20) unsigned NOT NULL,
  `dtraff_web` bigint(20) unsigned DEFAULT '0',
  `dtraff_ftp` bigint(20) unsigned DEFAULT '0',
  `dtraff_mail` bigint(20) unsigned DEFAULT '0',
  `dtraff_pop` bigint(20) unsigned DEFAULT '0',
  PRIMARY KEY (`dtraff_id`),
  UNIQUE KEY `i_unique_timestamp` (`domain_id`,`dtraff_time`),
  KEY `full_covering` (`domain_id`,`dtraff_time`,`dtraff_web`,`dtraff_ftp`,`dtraff_mail`,`dtraff_pop`) USING BTREE
) ENGINE=InnoDB;

插入随机行的存储过程:

drop procedure if exists insertMany;
DELIMITER $$
create procedure insertMany
(
    howMany int
)
BEGIN
    DECLARE soFar int default 0;
    WHILE soFar<howMany DO
        -- insert ignore is used because of `i_unique_timestamp`
        insert ignore domain_traffic (domain_id,dtraff_time,dtraff_web,dtraff_ftp,dtraff_mail,dtraff_pop) values
        (1,rand()*2000000+unix_timestamp(now())-3000000,1,2,3,4);

        set soFar=soFar+1;
        if soFar>500 then
            set soFar=howMany; -- hah, you can't trick me. I am not doing that many. RAND() is slow.
        end if;
    END WHILE;
END$$
DELIMITER ; 

测试存储过程:

call insertMany(1000);
-- select count(*) from domain_traffic;

.

select from_unixtime(dtraff_time) from domain_traffic;
select 60*60*24*30;
--  2.6M sec/month

1 469 124 890
1 469 125 020
1 469 125 042
select unix_timestamp(now()); -- seconds since epoch
select current_timestamp();

测试一些解释输出(3个条件):

Stub A:
WHERE `dtraff_time` BETWEEN 1 and 2 

Stub B:
WHERE `dtraff_time` BETWEEN 1400000000 and 1500000000 

Stub C:
WHERE `dtraff_time` BETWEEN UNIX_TIMESTAMP(((LAST_DAY(CURDATE()) + INTERVAL 1 DAY) - INTERVAL 1 MONTH )) and UNIX_TIMESTAMP((LAST_DAY(CURDATE()) + INTERVAL 1 DAY ))

EXPLAIN SELECT  
SUM(dtraff_web) AS dtraff_web, 
SUM(dtraff_ftp) AS dtraff_ftp, 
SUM(dtraff_mail) AS dtraff_mail, 
SUM(dtraff_pop) AS dtraff_pop 
FROM domain_traffic 
WHERE (fill in stubs A to C above, individually)  
AND domain_id = 1; 

Explain results, Stub A:
+----+-------------+----------------+-------+----------------------------------+--------------------+---------+------+------+-----------------------+
| id | select_type | table          | type  | possible_keys                    | key                | key_len | ref  | rows | Extra                 |
+----+-------------+----------------+-------+----------------------------------+--------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | domain_traffic | range | i_unique_timestamp,full_covering | i_unique_timestamp | 12      | NULL |    1 | Using index condition |
+----+-------------+----------------+-------+----------------------------------+--------------------+---------+------+------+-----------------------+

Explain results, Stub B:
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+
| id | select_type | table          | type  | possible_keys                    | key           | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | domain_traffic | range | i_unique_timestamp,full_covering | full_covering | 12      | NULL | 1284 | Using where; Using index |
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+

Explain results, Stub C:
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+
| id | select_type | table          | type  | possible_keys                    | key           | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | domain_traffic | range | i_unique_timestamp,full_covering | full_covering | 12      | NULL |  515 | Using where; Using index |
+----+-------------+----------------+-------+----------------------------------+---------------+---------+------+------+--------------------------+

清理:

drop schema nuxwin099a;

对我来说最重要的是性能，而不是专注于确切的解释输出。