关于MySQL的索引之最左前缀优化详解

关于MySQL的索引最左前缀优化，在这里为大家详细讲解一下。

什么是索引最左前缀优化？

MySQL的索引最左前缀优化指的是当一个组合索引被查询时，只有最左边的索引被使用了，其他索引（当然是在此左侧的索引）则未被使用。

何时使用？

当你有多列，同时要使用这些列作为查询条件时，你可能需要用到组合索引。此时，你可以通过对这些列的升序或降序排列创建一个组合索引。在查询时，可以使用组合索引 covering。这种方式可以大大提高查询性能。

怎么使用？

在创建组合索引时，应该将索引的列以最左前缀的顺序进行排列。这样做有什么好处呢？其实就是可以最大化利用索引的提供效率。

举例说明

示例1：有表A，有以下两个索引，分别为组合索引和普通索引

CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  `type` varchar(255) DEFAULT '',
  `value` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_name_type` (`name`,`type`),
  KEY `idx_value` (`value`)
) ENGINE=InnoDB;

现在我们做查询：

SELECT id FROM A WHERE name='xxx' AND type='yyy';

建议创建如上述组合索引“idx_name_type”，它可以大大提高执行效率，同时这个组合索引可以满足等值查询、范围查询和排序功能。

示例2：还是上面的表A，现在我们想要查询“name”为"xxx"或"value"大于100的所有记录

SELECT id FROM A WHERE name='xxx' OR value>100;

这个查询我们可以使用普通索引“idx_value”， 做到“value”这个列的索引扫描，这个案例我们就不需要对“name”列的索引进行扫描了。

示例3：还是上面的表A，现在我们想要查询“name”为"xxx"或"value"大于100的所有记录，但我们对“name”列还想要排序

SELECT id FROM A WHERE name='xxx' OR value>100 ORDER BY name DESC;

如果我们仍然直接使用“idx_value”普通索引的话，那么我们在这个查询中用到的“name DESC”排序就会由MySQL 强制我们执行文件排序或者使用temp表来保存中间计算的结果。这样的做法会很耗费资源，极大降低查询效率！因此，在这个查询中，建议我们使用组合索引“idx_name_type”。

综上所述，我们在对MySQL的组合索引做优化时，应该借助索引最左前缀原则，尽可能从组合索引的最左侧开始匹配，才能最大化利用索引的优势。