MySQL 变量查询如何使用索引

MySQL 变量查询如何使用索引

1. 问题现象

在存储过程中，有通过变量进行数据查询，执行时间长，不符和预期，经过分析，发现是有一个变量查询的效率低，不走索引造成的。

在定义变量查询，不能使用索引。查询如下：

mysql> SET @bt_id = 'UojLOkCu';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bt_order t where t.bt_id=@bt_id and t.calc_date> date(now());


14430 rows in set (8.63 sec)

mysql>

耗时居然用了8.63 秒

表上的索引情况：

mysql> show index in bt_order ;
+----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bt_order |          1 | ind_bt_order_id_date |            1 | bt_id       | A         |       15082 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| bt_order |          1 | ind_bt_order_id_date |            2 | calc_date   | A         |      210672 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

如果不使用变量：

mysql> select * from bt_order t where t.bt_id='UojLOkCu' and t.calc_date> date(now());

14430 rows in set (0.24 sec)

才 0.24秒

2. 问题分析

（1）强制索引

查看执行计划：

mysql> explain select * from bt_order   where bt_id=@bt_id and calc_date> date(now());
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | bt_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6847015 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查询没有走索引！！
指定索引，强制索引：

mysql> explain
    -> select * from bt_order use index (ind_bt_order_id_date) where bt_id=@bt_id and calc_date> date(now());
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | bt_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6847015 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


mysql> explain
    -> select * from bt_order  force index (ind_bt_order_id_date) where bt_id=@bt_id and calc_date> date(now());
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | bt_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6847015 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

强制索引对应变量查询，没有使用索引，还是全表扫描。

对于变量，强制索引是没有用的吗？
用count（*） 的情况下，是自动走索引的！

mysql> explain select count(*) from bt_order   where bt_id=@bt_id and calc_date> date(now());
+----+-------------+----------+------------+-------+----------------------+----------------------+---------+------+---------+----------+----------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys        | key                  | key_len | ref  | rows    | filtered | Extra                                  |
+----+-------------+----------+------------+-------+----------------------+----------------------+---------+------+---------+----------+----------------------------------------+
|  1 | SIMPLE      | bt_order | NULL       | range | ind_bt_order_id_date | ind_bt_order_id_date | 39      | NULL | 2282110 |   100.00 | Using where; Using index for skip scan |
+----+-------------+----------+------------+-------+----------------------+----------------------+---------+------+---------+----------+----------------------------------------+
1 row in set, 1 warning (0.01 sec)

不用变量查询的执行计划：

mysql> explain
    -> select * from bt_order  where bt_id='UojLOkCu' and calc_date> date(now());
+----+-------------+----------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+----------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | bt_order | NULL       | range | ind_bt_order_id_date | ind_bt_order_id_date | 39      | NULL | 26960 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

用静态数值，走了索引！！！

（2）原因

原因：
在MySQL中，当使用用户定义的变量（如 @bt_id）在查询中时，MySQL的优化器可能无法有效地利用索引，尤其是当这些变量用于与索引列进行比较时。因为MySQL的查询优化器在查询准备阶段（即解析和生成执行计划时）不会将用户定义的变量的值考虑进去。因此，它无法确定变量在运行时的具体值，从而无法优化索引的使用。

• 查询优化器的限制：MySQL的查询优化器在查询准备阶段不展开用户定义的变量。它只能看到变量名，而不知道其实际值。
• 准备计划与实际执行的分离：MySQL的查询优化是在查询执行之前完成的，此时变量的值尚未确定。

唯一不能解释的是用 count(*)的时候，使用索引了，SELECT * 则没有。

原因：
在MySQL中，查询优化器会选择最有效的执行计划来执行查询。当使用COUNT(*)时，优化器通常会选择使用索引，因为在这种情况下，只需要统计满足条件的行数，不需要检索完整的行数据。而当使用SELECT *时，优化器可能选择全表扫描（ALL类型），因为它需要返回所有列的数据，如果索引不能覆盖所有列，则需要额外的工作来获取非索引列的数据。

• COUNT(*)查询：当使用COUNT(*)时，MySQL只需要统计满足条件的行数，而不需要读取每一行的所有列。如果有一个合适的索引，它可以快速跳过不符合条件的行，并只计数符合条件的行。这种情况下，即使索引不是覆盖索引（即索引中不包含查询所需的所有列），MySQL也可以有效地使用它来减少搜索范围。
• SELECT * 查询：当使用SELECT *时，MySQL需要返回每行的所有列。如果索引不是一个覆盖索引（即索引中没有包含查询所需的所有列），那么即使使用索引找到匹配的行，MySQL也需要进行回表操作（即回到主键索引或其他索引中去查找其他列的数据），这可能会导致性能下降。在这种情况下，优化器可能会认为全表扫描更有效率。

（3）解决

使用预处理语句（Prepared Statements）：
预处理语句允许你指定查询模板，并在执行时传入参数。MySQL能够更有效地优化这些查询，因为它们在执行前就已经知道了参数的类型和值。

SET @bt_id = 'UojLOkCu';
PREPARE stmt FROM 'SELECT * FROM bt_order WHERE bt_id=? AND calc_date > DATE(NOW())';  
EXECUTE stmt USING @bt_id;  
DEALLOCATE PREPARE stmt;

EXECUTE stmt USING @bt_id;
执行时间是 0.25秒
14430 rows in set (0.25 sec)

如果还是查询性能慢的话，用大招。
分析和优化索引：
使用ANALYZE TABLE和OPTIMIZE TABLE命令来更新统计信息，并优化表。

ANALYZE TABLE bt_order;
OPTIMIZE TABLE bt_order;