时间戳

首页 > 解决方案 > Where 子句订单性能，限制索引使用

mysql - Where 子句订单性能，限制索引使用

问题描述

我正在尝试对具有 1.25 亿行的表运行查询。数据与日期一起存储，我试图一次选择一个月的数据。我正在使用如下查询：

select id from stats where page regexp '...'  and timestamp between '2020-04-12' and '2020-05-12'

解释显示：

+----+-------------+--------------------+-------+---------------------------+-----------+---------+------+----------+------------------------------------+
| id | select_type | table              | type  | possible_keys             | key       | key_len | ref  | rows     | Extra                              |
+----+-------------+--------------------+-------+---------------------------+-----------+---------+------+----------+------------------------------------+
|  1 | SIMPLE      | stats | range | timestamp_video,timestamp | timestamp | 4       | NULL | 10257708 | Using index condition; Using where |
+----+-------------+--------------------+-------+---------------------------+-----------+---------+------+----------+------------------------------------+

在这里检查的行对我来说似乎很高：

select count(*) from stats where timestamp between '2020-04-12' and '2020-05-12';

返回：

+----------+
| count(*) |
+----------+
|  4840392 |
+----------+

数据库结构：

`page` text COLLATE utf8_unicode_ci NOT NULL,
`timestamp` date DEFAULT NULL,
KEY `timestamp_video` (`timestamp`,`video`),
KEY `timestamp` (`timestamp`)

该page列包含带有 +1000 个字符的条目。timestamp_video不需要索引，那么有没有办法告诉 MySQL 忽略该索引而只使用单个索引timestamp？

也许有一种方法可以使用子查询重写它，以便返回满足时间戳的行，然后返回匹配正则表达式的行？

查询当前执行时间超过 19516 秒。努力把它降到600以下。

更新

正则表达式示例，

它可以是 +12000 个字符长（有问题的字符串是12077），看起来像：

access=()

括号内有不同的 10 个字符长的字母数字字符串，由 . 分隔|。

部分完整示例：

page regexp 'access=(3slaug6h82|5qew9gd4tn|o7vr3e9tix|5coakhoymq|1axg2vf8qt|7uh9ptld4v|vpgaix9wm8|0klcvjbrm8|x19ozupcre|fo2tjd7cxn)'

page可能包含的样本值是：

www.example.com/page?param1=true&access=3slaug6h82&param3=false&user=1234

标签： mysqlquery-performancemysql-5.6

解决方案

使用FULLTEXT(page)：

SELECT ft FROM `desc_bug` 
    WHERE MATCH(ft) AGAINST('5qew9gd4tn|3slaug6h82|asdfasdfd' IN BOOLEAN MODE)\G

*************************** 1. row ***************************
ft: www.example.com/page?param1=true&access=3slaug6h82&param3=false&user=1234
1 row in set (0.00 sec)

注意：这只会检查奇数的“单词”；您可以添加正则表达式以验证它access=前面是否有：

    AND ft REGEXP 'access=(...)'

由于它应该首先使用 FT 索引，因此它应该比您最初的尝试快很多。

推荐阅读