Question

我们有一个 MySQL 表格,

CREATE TABLE `my_data` (
  `auto_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_ts` timestamp NOT NULL DEFAULT  0000-00-00 00:00:00 ,
  `data_txt` varchar(256) CHARACTER SET utf8 NOT NULL,
  `issued_ts` timestamp NULL DEFAULT NULL,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`auto_id`),
  KEY `account_issued_idx` (`account_id`,`issued_ts`),
  KEY `account_issued_created_idx` (`account_id`,`issued_ts`,`created_ts`),
  KEY `account_created_idx` (`account_id`,`created_ts`),
  KEY `issued_idx` (`issued_ts`)
) ENGINE=InnoDB;

表格中有大约900M行,其中1个账户_ID占这些行的65%以上。我被要求为创建的和发行的取决于账户_id的账号填写不同日期范围的查询,该账号似乎对自动递增键有1:1的功能依赖性。

典型的查询会是这样的 :

SELECT * 
FROM my_data 
WHERE account_id = 1 AND 
      created_ts > TIMESTAMP( 2012-01-01 ) AND 
      created_ts <= TIMESTAMP( 2012-01-21 ) 
ORDER BY created_ts DESC LIMIT 100;

查询的一则解释显示:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: range
possible_keys: account_issued_idx, account_issued_created_idx, account_created_idx,
      key: account_issued_created_idx
  key_len: 8
      ref: NULL
     rows: 365314721
    Extra: Using where

问题在于查询需要的时间太长, 最终被杀死。我让它运行过几次, 并且它把数据库主机拖下水, 因为 OS (Linux) 的交换空间用完了。

我反复研究过这个问题,并试图将查询分解为与气候无关的小问题、强迫指数、使用明确的SELECT条款和限制日期范围窗口,但结果是一样的:业绩不佳(太慢)和对东道主过于征税(总是死亡)。

我的问题是:

能否制定一个查询, 将数据切成日期范围, 并可以接受实时调用? ( & lt; 1s)
为了得到要求我完成的表演,我是否缺少或可能有所帮助的优化?

欢迎任何其他建议、暗示或想法。

谢谢谢谢

Answer 1

Mysql似乎对这个查询使用错误的索引,

SELECT * 
FROM my_data FORCE INDEX (`account_created_idx`)
WHERE account_id = 1 AND 
      created_ts > TIMESTAMP( 2012-01-01 ) AND 
      created_ts <= TIMESTAMP( 2012-01-21 ) 
ORDER BY created_ts DESC LIMIT 100;

Answer 2

这个问题年复一年地持续了,但有一个很好的答案。

您斗争的关键在于您的单词“ 它们” 重要列被删除。 < / em> 在您做 < code> SELECT *..... ORDER by X DESC Limit N < / code > 中, 没有任何微不足道的列。因为整个结果必须被拾取和打乱。当您在复杂的表格中要求所有列时, 有很多数据。

您对 WHERE 条款有一个好的索引。如果其中没有写出 DESC 条款, 也会对 ORDER by 条款有好处。

您想要的是一个延迟加入。从只检索您需要的行的 ID 开始。

        SELECT auto_id
          FROM my_data
         WHERE account_id = 1 AND 
              created_ts > TIMESTAMP( 2012-01-01 ) AND 
              created_ts <= TIMESTAMP( 2012-01-21 ) 
     ORDER BY created_ts DESC
        LIMIT 100

这将为您提供您需要的列的 < code> auto_ id 值列表。要命令此列表, MySql 只需打乱 id 和时间戳值。它会少处理 LOTS 数据。

然后您将您主表格上的 ID 列表 JOIN 并抓取结果。

SELECT a.*
  FROM my_data a
  JOIN (
             SELECT auto_id
               FROM my_data
              WHERE account_id = 1 AND 
                    created_ts > TIMESTAMP( 2012-01-01 ) AND 
                    created_ts <= TIMESTAMP( 2012-01-21 ) 
           ORDER BY created_ts DESC
              LIMIT 100
       ) b ON a.auto_id = b.auto_id
 ORDER BY a.created_ts DESC

试试这个,也许能节省你很多时间

如果您知道 a sisteri 自动_id 和已创建的 ts 都在增加单色酮, 那么您就可以做得更好。您的子密钥可以包含

      ORDER BY auto_id DESC
         LIMIT 100

这将减少你进一步洗牌所需的数据。

Pro tip: 避免生产系统中出现 SELECT {/code> ; 而是列出您实际需要的列。原因很多。

Answer 3

Try MariaDB (or MySQL 5.6), as their Optimizer can do it faster. I am using it for some months, and for some queries like yours it s 1000% faster.

You need Index Condition Pushdown: http://kb.askmonty.org/en/index-condition-pushdown/

Answer 4

在比较中不使用函数。计算时间戳并使用计算值,否则您无法使用索引比较创建的_ts,它是一个字段,从结果框中过滤百万行

Answer 5

不清楚为什么 MySQL 使用( 显然) 并非最佳索引。除了强制使用索引, 您还可以尝试使用 < code> EXPLAIN 计划来修改这个变量 :

SELECT * 
FROM my_data 
WHERE account_id = 1 AND 
      created_ts > TIMESTAMP( 2012-01-01 ) AND 
      created_ts <= TIMESTAMP( 2012-01-21 ) 
ORDER BY account_id
       , created_ts DESC 
LIMIT 100;

友情链接