sql—对新索引数据的mysql查询异常缓慢

col17t5w 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(245)

我有以下问题：

SELECT DISTINCT
        CONCAT(COALESCE(location.google_id, ''),
                '-',
                COALESCE(locationData.resolution, ''),
                '-',
                COALESCE(locationData.time_slice, '')) AS google_id
    FROM
        LocationData AS locationData
            JOIN
        Location AS location ON location.id = locationData.location_id

    WHERE
        location.company_google_id = 5679037876797440
            AND location.google_id IN (4679055472328704, 6414382784315392, 5747093579759616)
            AND locationData.resolution = 8
            AND locationData.time_slice >= ((SELECT max(s.time_slice) FROM LocationData as s WHERE s.location_id = location.id ORDER BY s.time_slice ASC) - 255)
            AND location.active = TRUE
    ORDER BY location.google_id ASC , locationData.time_slice ASC
    LIMIT 0 , 101

我在where和order by子句中的所有列上都有索引，并且为（locationdata.time\u slice，locationdata.location\u id）添加了一个复合索引
运行explain会给您带来一些挑战（这里的格式化带来了一些挑战，所以希望它能很好地显示出来）：

id | select_type        | table        | type  | possible_keys                              | key                |  key_len | ref                | rows | Extra
    1 | PRIMARY            | location     | range | PRIMARY,google_id_UNIQUE                   | google_id_UNIQUE   | 8        | NULL               |    3 | Using index condition; Using where; Using temporary; Using filesort
    1 | PRIMARY            | locationData | ref   | max_time_slice_idx,max_time_slice_idx_desc | max_time_slice_idx | 5        | index2.location.id |  301 | Using where
    2 | DEPENDENT SUBQUERY | s            | ref   | max_time_slice_idx,max_time_slice_idx_desc | max_time_slice_idx | 5        | index2.location.id |  301 | Using index

我知道依赖子查询的速度很慢，我很乐意接受获得类似行为的建议，但是我看到这个查询运行大约需要92秒，这与我在将新的复合索引添加到生产中之前运行的测试数据相差大约4个数量级。
在alter语句运行之后是否有索引生成？是否有某种方法可以检查索引是否正确执行？
两个表的行计数：
生产：
地点：6814
位置数据：13070888
试验数据：
位置：626
位置数据：594780
如有任何想法或建议，我们将不胜感激。提前谢谢！

sql mysql performance

来源：https://stackoverflow.com/questions/52767796/unexpectedly-slow-mysql-query-on-newly-indexed-data

2条答案

按热度按时间

1zmg4dgp1#

只是个建议
可以使用内部联接避免子选择

SELECT DISTINCT
    CONCAT(COALESCE(location.google_id, ''),
            '-',
            COALESCE(locationData.resolution, ''),
            '-',
            COALESCE(locationData.time_slice, '')) AS google_id
FROM LocationData AS locationData
INNER JOIN Location AS location ON location.id = locationData.location_id
INNER JOIN (
            SELECT s.location_id, max(s.time_slice)  -255 my_max_time_slice
            FROM LocationData as s
            GROUP BY s.location_id
        ) t on t.location_id = Location.id

WHERE
    location.company_google_id = 5679037876797440
        AND location.google_id IN (4679055472328704, 6414382784315392, 5747093579759616)
        AND locationData.resolution = 8
        AND locationData.time_slice >= t.my_max_time_slice
        AND location.active = TRUE
ORDER BY location.google_id ASC , locationData.time_slice ASC
LIMIT 0 , 101

通过这种方式，您应该避免对每个id重复子查询，只使用一个查询来构建max\u time\u切片的聚合结果
希望这有用

赞(0）回复(0）举报 2021-06-18

rqenqsqc2#

（添加到@scaisedge…）的建议中）

WHERE   l.company_google_id = 5679037876797440
    AND l.google_id IN (4679055472328704, 6414382784315392, 5747093579759616)
    AND ld.resolution = 8
    AND ld.time_slice >= t.my_max_time_slice
    AND l.active = TRUE
ORDER BY l.google_id ASC , ld.time_slice ASC

假设需要首先运行子查询，则最佳索引为(mysql的旧版本就是这样。）

LocationData: (location_id, time_slice)  -- in this order, for the subquery
locationData: (time_slice, resolution, location_id)  -- for JOIN

如果 id 是 PRIMARY KEY 的 location ，不需要额外的索引。
对于较新的版本，可以具体化子查询并构建合适的索引。在这种情况下，可能会从 location :

location: (company_google_id, active,  -- in either order
           google_id)                  -- last
locationData:  (location_id, time_slice)  -- in this order (for subquery)
locationData:  (location_id, resolution   -- in either order (for JOIN)
                time_slice)               -- last

没有办法优化 ORDER BY 因为它击中了两个表，也没有任何办法避免排序。
sugges您添加所有这些索引，然后 EXPLAIN SELECT ... 如果你需要进一步讨论的话。 SHOW CREATE TABLE 也很方便。

赞(0）回复(0）举报 2021-06-18

我来回答

sql—对新索引数据的mysql查询异常缓慢

2条答案

相关问题

热门标签

最新问答