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;