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_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索引最左匹配原则的详细介绍，希望能对您有所帮助。