带分区的 MYSQL 索引

Question

我们目前正在针对与分析相关的一个用例评估 mysql。

表模式是这样的

CREATE TABLE IF NOT EXISTS `analytics`(
  `date` DATE,
  `dimension1` BIGINT UNSIGNED,
  `dimension2` BIGINT UNSIGNED,
  `metrics1` BIGINT UNSIGNED,
  `metrics2` BIGINT UNSIGNED,
  INDEX `baseindex` (`dimension1`,`dt`)
);

由于大多数查询都围绕维度 1 和日期，我们认为组合索引是优化查询查找的最佳案例

考虑到这个表模式，解释查询返回以下结果

explain
select dimension2,dimension1
from analytics
where dimension1=1123 and dt between '2016-01-01' and '2016-01-30';

下面的查询返回

+----+-------------+-----------+------+---------------+-----------+---------+-------------+------+-----------------------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref         | rows | Extra                 |
+----+-------------+-----------+------+---------------+-----------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | analytics | ref  | baseindex     | baseindex | 13      | const,const |    1 | Using index condition |
+----+-------------+-----------+------+---------------+-----------+---------+-------------+------+-----------------------+

就我们得到索引正在启动的迹象而言，这看起来不错。

但是我们认为如果我们可以进一步优化它，因为我们的大部分查找将针对当前月份或基于月份的查找，我们认为日期分区将进一步提高性能。

该表后来被修改为按月添加分区

ALTER TABLE analytics
        PARTITION BY RANGE( TO_DAYS(`dt`))(
        PARTITION JAN2016 VALUES LESS THAN (TO_DAYS('2016-02-01')),
        PARTITION FEB2016 VALUES LESS THAN (TO_DAYS('2016-03-01')),
        PARTITION MAR2016 VALUES LESS THAN (TO_DAYS('2016-04-01')),
        PARTITION APR2016 VALUES LESS THAN (TO_DAYS('2016-05-01')),
        PARTITION MAY2016 VALUES LESS THAN (TO_DAYS('2016-06-01')),
        PARTITION JUN2016 VALUES LESS THAN (TO_DAYS('2016-07-01')),
        PARTITION JUL2016 VALUES LESS THAN (TO_DAYS('2016-08-01')),
        PARTITION AUG2016 VALUES LESS THAN (TO_DAYS('2016-09-01')),
        PARTITION SEPT2016 VALUES LESS THAN (TO_DAYS('2016-10-01')),
        PARTITION OCT2016 VALUES LESS THAN (TO_DAYS('2016-11-01')),
        PARTITION NOV2016 VALUES LESS THAN (TO_DAYS('2016-12-01')),
        PARTITION DEC2016 VALUES LESS THAN (TO_DAYS('2017-01-01'))
    );

分区就位后，相同的查询现在返回以下结果

| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | analytics | range | baseindex     | baseindex | 13      | NULL |    1 | Using where |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-------------+

现在“额外”列显示它切换到 where 而不是使用索引条件。

我们没有注意到任何性能提升或降低，所以想知道添加分区如何改变额外列内的值

Answer 1

评论太长了。

MySQL 对数据和索引进行分区。因此，查询的结果是查询正在访问一个较小的索引，该索引引用较少的数据页。

为什么您看不到性能提升？好吧，在较小的索引中查找行所节省的费用可以忽略不计(尽管冷启动的第一个查询可能会节省一些费用，因为必须将索引加载到内存中)。

我猜您正在寻找的数据相对较小——比如说，记录来自少数数据页。好吧，从分区中获取少量数据页与从整个表中获取少量数据页几乎是一回事。

这是否意味着分区没有用？一点也不。一方面，分区数据和索引比整个表小得多。因此，您可以节省服务器端的内存——这对于繁忙的服务器来说是一个巨大的胜利。

不过，总的来说，当您的查询没有完全使用索引时，分区会真正发挥作用。每个分区中较小的数据大小通常会使此类查询更有效率。