时间戳

首页 > 解决方案 > 检索最接近输入日期的日期

mysql - 检索最接近输入日期的日期

问题描述

我有以下功能：

DELIMITER $$
DROP FUNCTION IF EXISTS f_prevpricedate;
CREATE FUNCTION f_prevpricedate (id CHAR(8), startdate DATE)
RETURNS DATE

BEGIN

DECLARE prevpricedate DATE;

SELECT MAX(f.p_date) INTO prevpricedate
FROM fp_v2_fp_basic_prices AS f 
WHERE f.fsym_id = id AND f.p_date<startdate;

RETURN prevpricedate; 

END$$

这基本上只是将最接近的日期（前一个日期）返回到输入日期。但是它运行得非常慢，因为桌子很大。

有人知道如何优化吗？

标签： mysql

解决方案

首先，检查explain select ...说什么。

是否已编fp_v2_fp_basic_prices.fsym_id入fp_v2_fp_basic_prices.p_date索引？索引允许数据库快速匹配和比较行，而无需查看所有行。如果没有索引，则必须比较表中的每一行。

例如...

mysql> select count(*) from foo;                                                                                                                              
+----------+
| count(*) |
+----------+
|        3 |
+----------+

mysql> explain select max(this) from foo where this < 42;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | foo   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

这表明查询正在执行全表扫描。它没有使用键（索引），查询的类型是ALL，它认为它必须查看表中的所有 3 行。

mysql> alter table foo add index foo_this (this);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select max(this) from foo where this < 42;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

这是在添加索引之后。Select tables optimized away告诉我们优化器已经发现它可以使用索引来优化整个查询。

在您的情况下，您正在搜索两列，fsym_id并且p_date. MySQL 将在查询中对每个表只使用一个索引。因此，即使您有一个索引fsym_id并且上面的索引p_date也只会使用一个。为了使这个查询执行得很好，你需要在一个索引中。

alter table fp_v2_fp_basic_prices add index(p_date, fsym_id);

这将适用于仅使用的p_date查询以及同时使用p_date+的查询fsym_id。因此，您不需要在 just 上建立索引p_date。但它不包括仅使用fsym_id. 有关更多详细信息，请参阅此答案。

也可以看看：

推荐阅读