优化MySQL存储过程：从自定义二级索引获取行序号

受限于MySQL视图无法插入带序号的行、函数无法返回表的特性，我通过存储过程what_id(value)替代直接的select * from table where regular_column=value，实现根据二级列值获取行序号的需求。该存储过程会从主表非空记录中生成对应列的索引（按字母排序分配序号），同时记录原表ID；为处理大数据量，通过临时表截取索引值前两位生成二级索引，再用分组和max()记录分段位置。目前存储过程可正常返回结果，但耗时约300ms，远高于直接查询的90ms（PostgreSQL环境测试仅需70ms），尝试内联代码后耗时反而升至500ms，急需更高效简洁的实现方案。

1. 消除重复全表扫描 #

原存储过程两次全表扫描select_post（生成temp_indice和datos临时表各一次）是核心性能瓶颈。可一次性预计算排序后的全表数据存入临时表并建立索引，后续操作均基于此表：

CREATE TEMPORARY TABLE IF NOT EXISTS sorted_posts (
    id_sort INT PRIMARY KEY AUTO_INCREMENT,
    codigo VARCHAR(24),
    idposts INT,
    INDEX idx_codigo (codigo),
    INDEX idx_prefix (LEFT(codigo, 2))
) ENGINE=InnoDB;
TRUNCATE TABLE sorted_posts;
INSERT INTO sorted_posts (codigo, idposts)
SELECT codigo, idposts FROM select_post ORDER BY codigo;

2. 简化分段位置计算 #

无需单独创建temp_indice表，利用MySQL 8.0+支持的分组聚合，即可获取每个前缀的起止序号：

SELECT 
    LEFT(codigo, 2) AS indice,
    MIN(id_sort) AS ini,
    MAX(id_sort) AS fin
FROM sorted_posts
GROUP BY indice;

3. 直接定位目标记录 #

无需创建datos临时表，直接从预排序表中关联查询目标记录：

SELECT p.*
FROM posts p
JOIN sorted_posts sp ON p.idposts = sp.idposts
WHERE sp.codigo = _userinput
LIMIT 1;

4. 优化后的完整存储过程 #

CREATE PROCEDURE `what_id`(_userinput varchar(24))
BEGIN
    DECLARE _input_prefix VARCHAR(2);
    SET _input_prefix = UPPER(LEFT(_userinput, 2));

    -- 预生成排序后的临时表（首次调用或数据更新时填充）
    CREATE TEMPORARY TABLE IF NOT EXISTS sorted_posts (
        id_sort INT PRIMARY KEY AUTO_INCREMENT,
        codigo VARCHAR(24),
        idposts INT,
        INDEX idx_codigo (codigo),
        INDEX idx_prefix (LEFT(codigo, 2))
    ) ENGINE=InnoDB;

    -- 仅当临时表为空时插入数据，避免重复扫描
    IF NOT EXISTS (SELECT 1 FROM sorted_posts) THEN
        INSERT INTO sorted_posts (codigo, idposts)
        SELECT codigo, idposts FROM select_post ORDER BY codigo;
    END IF;

    -- 直接查询目标记录
    SELECT p.*
    FROM posts p
    JOIN sorted_posts sp ON p.idposts = sp.idposts
    WHERE sp.codigo = _userinput
    LIMIT 1;

    -- 若数据频繁更新，可在结束时删除临时表
    -- DROP TEMPORARY TABLE IF EXISTS sorted_posts;
END

5. 额外优化建议 #

• 若select_post数据更新不频繁，可将sorted_posts设为永久表，定期刷新数据，进一步降低调用耗时。
• 确保select_post表的codigo字段建有索引，posts表的idposts为主键。
• MySQL 8.0+环境下，可使用CTE（公共表表达式）替代临时表，简化逻辑。

CREATE PROCEDURE `what_id`(_userinput varchar(24)) 
BEGIN 
    declare _inputchars varchar(24); 
    declare _codigo varchar(24); 
    declare _aux int; 
    declare _ini int; 
    declare _fin int; 
    DROP TEMPORARY TABLE IF EXISTS temp_indice; 
    CREATE TEMPORARY TABLE temp_indice(id_indice INT PRIMARY KEY, indice varchar(2), fin smallint); 
    INSERT INTO temp_indice (SELECT @j:=@j + 1 AS id_indice, indice, fin FROM (SELECT substring(codigo, 1, 2) AS indice, max(id_sort) as fin FROM (SELECT @i:=@i + 1 AS id_sort, codigo, idposts FROM select_post,(select @i:=0, @j:=0) as temp) as temp GROUP BY indice) as temp); 
    set _inputchars = UPPER(substring(_userinput, 1, 2)); 
    SELECT id_indice, fin INTO _aux, _fin FROM temp_indice as temp WHERE indice=_inputchars; 
    set _aux = _aux-1; 
    set _ini := (SELECT fin FROM temp_indice as temp WHERE id_indice = _aux); 
    set _ini = _ini+1; 
    DROP TEMPORARY TABLE IF EXISTS datos; 
    CREATE TEMPORARY TABLE datos(id_sort INT, codigo varchar(24), idposts int); 
    INSERT INTO datos (SELECT * FROM (SELECT @i:=@i + 1 AS id_sort, codigo, idposts FROM select_post,(select @i:=0) as temp) as temp WHERE id_sort BETWEEN _ini and _fin); 
    set _codigo := (SELECT idposts FROM datos as temp WHERE codigo=_userinput); 
    SELECT * FROM posts WHERE idposts=_codigo limit 1; 
END

内容的提问来源于stack exchange，提问作者Pablo Rodriguez