MySQL索引最左匹配原则是指当我们使用多列索引进行查询时,只有索引的最左边的列才能被用于索引扫描,即只有最左前缀匹配的列会被索引扫描,这是MySQL查询优化的一个重要原则。
具体来说,当使用多列索引进行查询时,MySQL只会使用最左边的列作为索引键进行查找,找到符合条件的最左前缀匹配的行,并返回这些行的主键值;然后在这些行中再进行二次查找,即对最左前缀匹配的所有行进行数据的筛选。这种二次查找也被称为“回表”,即通过主键再次查找具体的数据行。
下面我们通过两个例子来进一步理解最左匹配原则:
例1:
假设我们有如下一张订单表orders
,表结构如下:
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT NOT NULL,
order_no VARCHAR(50) NOT NULL,
order_status VARCHAR(20) NOT NULL,
order_amount DECIMAL(10,2) NOT NULL
);
CREATE INDEX idx_orders_multi ON orders(user_id, order_no, order_status);
可以看到我们在订单表上创建了一个联合索引idx_orders_multi
,包含了user_id
、order_no
和order_status
三个字段。
现在我们需要查询用户1001
的所有已完成订单的订单号,可以使用如下SQL语句进行查询:
SELECT order_no FROM orders WHERE user_id=1001 AND order_status='Completed';
这个查询语句可以借助索引idx_orders_multi
,首先按照user_id
字段找到所有用户为1001
的订单记录,然后再在这些记录中根据order_status
字段完成最后的筛选,返回符合条件的订单号。
需要注意的是,即使order_no
字段在索引中排在order_status
之后,也可以被用于查询。这是因为在idx_orders_multi
索引中,order_no
和order_status
都是非主键列,当MySQL在使用索引时,会先使用索引键对应的列进行查找,再根据order_no
和order_status
进行后续筛选。
例2:
现在我们有如下一张用户表users
,表结构如下:
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
age INT NOT NULL,
gender VARCHAR(10) NOT NULL
);
CREATE INDEX idx_users_multi ON users(age, gender, username);
可以看到我们在用户表上创建了一个联合索引idx_users_multi
,包含了age
、gender
和username
三个字段。
现在我们需要查询所有年龄为18
岁,性别为男
的用户的用户名和密码,可以使用如下SQL语句进行查询:
SELECT username, password FROM users WHERE age=18 AND gender='Male';
这个查询语句可以借助索引idx_users_multi
,首先按照age
字段找到所有年龄为18
的用户记录,然后再在这些记录中根据gender
字段完成最后的筛选,再根据这些记录中的username
和password
字段返回查询结果。
需要注意的是,即使username
和password
字段在索引中排在gender
之后,也可以被用于查询。这是因为在idx_users_multi
索引中,username
和password
是非主键列,当MySQL在使用索引时,会先使用索引键对应的列进行查找,再根据username
和password
进行后续筛选。
以上就是关于MySQL索引最左匹配原则的详细介绍,希望能对您有所帮助。
本文链接:http://task.lmcjl.com/news/16297.html