索引提示与计划验证¶
v4.2.5 的表索引提示使用 FROM table_name USE INDEX(index_name)、FORCE INDEX(index_name) 或 IGNORE INDEX(index_name)。提示名称与适用范围由实现检查;USE 与 FORCE 不得组合。注释式 /*+ INDEX(...) */、/*+ ORDERED */ 不应被描述为已经控制计划。
可复现示例¶
DROP DATABASE IF EXISTS kernel_plan_518;
CREATE DATABASE kernel_plan_518;
USE kernel_plan_518;
CREATE TABLE customers(id INT PRIMARY KEY, region VARCHAR(10));
CREATE TABLE orders(id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2));
CREATE INDEX idx_customer ON orders(customer_id);
INSERT INTO customers VALUES(1,'east'),(2,'west');
INSERT INTO orders VALUES(10,1,10.00),(11,1,20.00),(12,2,5.00);
EXPLAIN SELECT id FROM orders WHERE customer_id=1;
EXPLAIN SELECT id FROM orders FORCE INDEX(idx_customer) WHERE customer_id=1;
SELECT c.region,SUM(o.amount) AS total FROM customers c JOIN orders o ON c.id=o.customer_id GROUP BY c.region ORDER BY c.region;
DROP DATABASE kernel_plan_518;
该示例显式创建索引,并统一表名。EXPLAIN 能解析只是第一步,还应比较访问路径和最终结果。参数 hint、表索引 hint 与 SQL 重写映射是不同接口;不要凭通用数据库语法推断支持。详见规划机制。