MySQL 索引提示:USE INDEX
时间:2022-12-23
MySQL 查询优化器是 MySQL 数据库服务器的一个组件,它为 SQL 语句制定最佳执行计划。 MySQL 优化器通常根据索引基数进行决策。 有时候,虽然你创建了索引,但是你的 SQL 语句却不一定使用索引。 这是因为 MySQL 查询优化器的做出了它认为的更优的选择。
MySQL 允许您使用 USE INDEX
语句建议查询优化器去使用指定的命名索引。
但是, MySQL 查询优化器依然有可能不适用您建议的索引。 如果您想 MySQL 必须使用您指定的索引,请使用 FORCE INDEX
子句。
在 EXPLAIN
显示查询优化器使用错误索引的情况下, USE INDEX
很有用。
MySQL USE INDEX 语法
要使用 MySQL USE INDEX
进行索引提示,请遵循以下语法:
SELECT column_list
FROM table_name
USE INDEX (index_list)
WHERE condition;
说明:
- 请将
USE INDEX
子句放在FROM
子句之后。 - 如果 MySQL 查询优化器要使用索引,则必须使用索引列表
index_list
中的一个索引。
请注意,查询优化器不一定会使用您建议的命名索引。
MySQL USE INDEX 实例
我们将使用来自示例数据库中的 customer
表进行演示。
看一下 actor
表的定义:
DESC customer;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO | PRI | NULL | auto_increment |
| store_id | tinyint unsigned | NO | MUL | NULL | |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | MUL | NULL | |
| email | varchar(50) | YES | | NULL | |
| address_id | smallint unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| create_date | datetime | NO | | NULL | |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
9 rows in set (0.00 sec)
让我们创建两个索引,
CREATE INDEX idx_last_name
ON customer (last_name);
CREATE INDEX idx_last_name_first_name
ON customer (last_name, first_name);
看一下 customer
表的中的索引:
SHOW INDEXES FROM customer;
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customer | 0 | PRIMARY | 1 | customer_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_store_id | 1 | store_id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_fk_address_id | 1 | address_id | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name | 1 | last_name | A | 598 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name_first_name | 1 | last_name | A | 598 | NULL | NULL | | BTREE | | | YES | NULL |
| customer | 1 | idx_last_name_first_name | 2 | first_name | A | 599 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.00 sec)
通过 EXPLAIN
语句查看以下查找姓氏为 BARBEE
的语句的执行计划:
EXPLAIN
SELECT *
FROM customer
WHERE last_name = 'BARBEE';
+----+-------------+----------+------------+------+----------------------------------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+----------------------------------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | customer | NULL | ref | idx_last_name,idx_last_name_first_name | idx_last_name | 182 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+----------------------------------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
从输出可以发现, MySQL 查询优化器选择使用 idx_last_name
索引。
如果您认为使用 idx_last_name_first_name
更好,则使用 USE INDEX
指定它,如下:
EXPLAIN
SELECT *
FROM customer
USE INDEX (idx_last_name_first_name)
WHERE last_name = 'BARBEE';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | customer | NULL | ref | idx_last_name_first_name | idx_last_name_first_name | 182 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
请注意,这仅用于演示目的,但不是最佳选择。
从输出可以发现, MySQL 查询优化器选择使用 idx_last_name_first_name
索引。
结论
本文讨论了 MySQL USE INDEX
索引提示的基本用法。 USE INDEX
与 FORCE INDEX
是不同的:
USE INDEX
告诉 MySQL 用列表中的其中一个索引去做本次查询,但是 MySQL 不一定会用。FORCE INDEX
强制 MySQL 使用一个特定的索引。
特别声明:本站部分内容收集于互联网是出于更直观传递信息的目的。该内容版权归原作者所有,并不代表本站赞同其观点和对其真实性负责。如该内容涉及任何第三方合法权利,请及时与824310991@qq.com联系,我们会及时反馈并处理完毕。