Mysql innodb 查询返回缓慢结果

Question

我有以下查询，该查询需要时间。Mytable 类型是 innodb 并且在字段 tsh_id 上有主键我还在 transaction_Id 字段上添加了索引

以下是我的数据库存储过程中的实现。

 DECLARE lv_timestamp DATETIME(3);
    SET @lv_timestamp = NOW(3);

    IF(mycondition) then    
        SET @lv_Duration :=( SELECT UNIX_TIMESTAMP (@lv_timestamp)  - UNIX_TIMESTAMP ( `changedon` ) 
        FROM  `MyTable`         
        WHERE transaction_Id = _transaction_Id        
        ORDER BY tsh_id DESC
        LIMIT 1)        
    End if;

请提出任何改进建议

编辑:

解释查询说

"select_type":"SIMPLE",
"table":"MyTable",
"type":"ref",
"possible_keys":"IX_MyTable_Transaction",
"key":"IX_MyTable_Transaction",
"key_len":"98",
"ref":"const",
"rows":1,
"Extra":"Using where"

Answer 1

1. 确保您在 MyTable.transaction_Id 上有索引
2. 确保您拥有 innodb_buffer_pool_size在 MySQL 配置中设置一个合适的值