我要评论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;
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论