关键词

MySQL索引最左匹配原则实例详解

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_idorder_noorder_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_noorder_status都是非主键列,当MySQL在使用索引时,会先使用索引键对应的列进行查找,再根据order_noorder_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,包含了agegenderusername三个字段。

现在我们需要查询所有年龄为18岁,性别为的用户的用户名和密码,可以使用如下SQL语句进行查询:

SELECT username, password FROM users WHERE age=18 AND gender='Male';

这个查询语句可以借助索引idx_users_multi,首先按照age字段找到所有年龄为18的用户记录,然后再在这些记录中根据gender字段完成最后的筛选,再根据这些记录中的usernamepassword字段返回查询结果。

需要注意的是,即使usernamepassword字段在索引中排在gender之后,也可以被用于查询。这是因为在idx_users_multi索引中,usernamepassword是非主键列,当MySQL在使用索引时,会先使用索引键对应的列进行查找,再根据usernamepassword进行后续筛选。

以上就是关于MySQL索引最左匹配原则的详细介绍,希望能对您有所帮助。

本文链接:http://task.lmcjl.com/news/16297.html

展开阅读全文