MySQL优化示例 1：关联查询

示例（优化前）

SELECT
    COUNT(*) AS count,
    dl.province_name
FROM
    device d
    JOIN user_device_rel udr ON d.device_id = udr.device_id
    JOIN device_location_from_app dl ON d.device_id = dl.device_id
WHERE
    d.category_id != 1
    AND province_name IS NOT NULL 
    AND province_name != '' 
GROUP BY
    dl.province_name 
ORDER BY
    count DESC;

示例（优化方法1，MySQL5.7）

优化思路

• 提前过滤数据：使用子查询在连接表之前先对数据进行筛选，减少参与连接的数据量。
• 索引优化：为参与 JOIN 和 WHERE条件的列创建合适的索引，加快查询速度。

SELECT
    COUNT(*) AS count,
    dl.province_name
FROM
    (SELECT device_id FROM device WHERE category_id != 1) d
    JOIN user_device_rel udr ON d.device_id = udr.device_id
    JOIN (SELECT device_id, province_name FROM device_location WHERE province_name IS NOT NULL AND province_name != '') dl ON d.device_id = dl.device_id
GROUP BY
    dl.province_name
ORDER BY
    count DESC;

示例（优化方法1，MySQL8.0及以上）

优化思路

• 提前过滤数据：使用 WITH 子句（CTE，公共表表达式）在连接表之前先对数据进行筛选，减少参与连接的数据量。
• 索引优化：为参与 JOIN 和 WHERE条件的列创建合适的索引，加快查询速度。

版本要求说明

从 MySQL 8.0 版本开始，MySQL 正式支持公共表表达式（CTE），这意味着你可以使用 WITH 关键字来创建 CTE。而在 MySQL 8.0 之前的版本，比如 MySQL 5.x 系列，是不支持 WITH 语法的。

WITH filtered_device AS (
   SELECT device_id
    FROM device
    WHERE category_id != 1
),
filtered_location AS (
    SELECT device_id, province_name
    FROM device_location
    WHERE province_name IS NOT NULL AND province_name != ''
)
SELECT
    COUNT(*) AS count,
    dl.province_name
FROM
    filtered_device d
    JOIN user_device_rel udr ON d.device_id = udr.device_id
    JOIN filtered_location dl ON d.device_id = dl.device_id
GROUP BY
    dl.province_name
ORDER BY
    count DESC;

知识点

在 SQL 中，WITH 关键字用于创建公共表表达式（Common Table Expressions，CTE）。CTE 是一种临时命名的结果集，它存在于单个 SQL 语句的执行范围内，可在该语句中被多次引用。下面为你详细解析 WITH 关键字及其使用场景和优势。

基本语法

WITH cte_name (column1, column2, ...) AS (
    -- CTE 的查询语句
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- 主查询语句，使用 CTE
SELECT *
FROM cte_name
WHERE another_condition;

• cte_name：CTE 的名称，用于在主查询中引用该临时结果集。
• (column1, column2, …)：可选参数，用于指定 CTE 结果集中列的名称。如果省略，将使用子查询中列的名称。
• AS：用于分隔 CTE 名称和子查询。
• (SELECT …)：CTE的子查询，用于生成临时结果集。