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

MYSQL对新索引数据的查询速度异常慢

performance sql mysql

Myles · 技术社区 · 6 年前

我有以下问题:

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

运行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

在ALTER语句运行之后是否有索引生成?是否有某种方法可以检查索引是否正确执行?

两个表的行计数:

生产:

位置数据:13070888

试验数据:

位置数据:594780

如有任何想法或建议,我们将不胜感激。提前谢谢!

2 回复 | 直到 6 年前

ScaisEdge 6 年前

只是个建议

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切片的聚合结果

希望这有用

Rick James 6 年前

(添加到@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

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

如果 id 是 PRIMARY KEY 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 因为它击中了两个表,也没有任何办法避免排序。

EXPLAIN SELECT ... 如果你需要进一步讨论的话。 SHOW CREATE TABLE