代码之家 › 专栏 › 技术社区 › parchambeau

优化MYSQL排名mod查询

mod optimization sql mysql

parchambeau · 技术社区 · 7 年前

我尝试过的一件事是删除内部查询中的“ORDER BY”,但这实际上并没有改善它的计时,也没有清除我选择的一些额外的不需要的列,这些列提高了时间,但没有使它“足够快”。

SELECT unix_date, price FROM
(SELECT @row := @row +1 as row_num, unix_date, price 
FROM (SELECT @row:=0, unix_date, price FROM 
price_data WHERE created_date >= '2017-03-26 00:00:00' AND created_date 
<= '2017-06-26 23:59:59' AND currency= 'USD' ORDER BY unix_date DESC) 
AS p) AS d
WHERE MOD(row_num, 288) = 1;

| Table              | Non_unique | Key_name     | Seq_in_index | 
Column_name  | Collation | Cardinality | Sub_part | Packed | Null | 
Index_type | Comment | Index_comment |
+--------------------+------------+--------------+--------------+------
--------+-----------+-------------+----------+--------+------+---------
---+---------+---------------+
| price_data |          0 | PRIMARY      |            1 | 
unix_date    | A         |      200002 |     NULL | NULL   |      | 
BTREE      |         |               |
| price_data |          0 | PRIMARY      |            2 | 
currency     | A         |      200002 |     NULL | NULL   |      | 
BTREE      |         |               |
| price_data |          1 | created_date |            1 | 
created_date | A         |      200002 |     NULL | NULL   |      | 
BTREE      |         |               |
| price_data |          1 | price        |            1 | price        
| A         |      200002 |     NULL | NULL   | YES  | BTREE      |         
|               |
+--------------------+------------+--------------+--------------+------
--------+-----------+-------------+----------+--------+------+---------
---+---------+--

根据建议,我添加了创建表:

  CREATE TABLE `price_data` (
  `created_date` datetime NOT NULL,
  `unix_date` int(11) NOT NULL,
  `currency` varchar(255) NOT NULL DEFAULT '',
  `price` decimal(10,6) DEFAULT NULL,
  PRIMARY KEY (`unix_date`,`currency`),
  KEY `created_date` (`created_date`),
  KEY `price` (`price`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

如果您能就如何提高查询速度提出任何建议,我们将不胜感激。

编辑:问题是否可能与此查询的最终位置类似,实际上是根据row_num的“伪列”来评估信息,该列是从以前的内部查询派生的,因此本身没有索引?那么,当WHERE进行计算时,它不是以普通索引列的速度进行计算的?

1 回复 | 直到 7 年前

Bill Karwin 7 年前

将该条件应用于unix\u date,而不是create\U date,以使其使用主键索引。

您必须使用派生表子查询,但不必使用两个嵌套级别的子查询。

SELECT row_num, unix_date, price
FROM (
    SELECT @row := @row + 1 AS row_num, unix_date, price
    FROM (SELECT @row := 0) AS _init
    CROSS JOIN price_data
    WHERE currency = 'USD' 
     AND unix_date BETWEEN UNIX_TIMESTAMP('2017-03-26 00:00:00') 
                       AND UNIX_TIMESTAMP('2017-06-26 23:59:59')
    ORDER BY unix_timestamp DESC
) AS t
WHERE MOD(row_num, 288) = 1

use EXPLAIN

你可能也喜欢我的演讲 How to Design Indexes, Really https://www.youtube.com/watch?v=ELR7-RdU9XU

MySQL 8.0应该有窗口功能,所以明年找个时间看看吧。