MySql 函数不使用索引

Question

我有一个简单的函数，由一个 sql 查询组成

CREATE FUNCTION `GetProductIDFunc`( in_title char (14) )
        RETURNS bigint(20)
BEGIN
  declare out_id bigint;    

  select id into out_id from products where title = in_title limit 1;    
  RETURN out_id;
END

这个函数的执行时间需要5秒

select Benchmark(500 ,GetProductIdFunc('sample_product'));

普通查询的执行时间为0.001秒

select Benchmark(500,(select id from products where title = 'sample_product' limit 1));

“标题”字段已编入索引。为什么函数执行需要这么多时间，我该如何优化它？

编辑:执行计划

mysql> EXPLAIN EXTENDED select id from products where title = 'sample_product' limit 1;
+----+-------------+----------+-------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+-------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | products | const | Index_title   | Index_title | 14      | const |    1 |   100.00 | Using index |
+----+-------------+----------+-------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN select GetProductIdFunc('sample_product');
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Answer 1

这可能是字符集问题。如果函数使用与表列不同的字符集，尽管有索引，也会导致性能非常低。

运行 show create table products\G 以确定列的字符集。

运行 show variables like 'character_set%'; 以查看您的数据库的相关默认字符集。