Mysql聚簇索引和二级索引到底有何不同

问题

• 聚簇索引存储结构是如何的？二级索引存储结构又如何？
• 什么是覆盖索引？
• 怎么判断能不能用到索引？

准备

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `author` varchar(20) DEFAULT NULL,
  `published` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_author_name` (`author`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT book VALUES (1,'book1','author1','2018-01-01'),(2,'book2','author1','2018-01-01'),(3,'book3','author2','2018-01-01'),(4,'book4','author2','2019-01-01');

什么是聚簇索引和二级索引

book表中，我们创建了3个索引，id 主键索引， idx_name 和 idx_author_name 2个非主键索引。从定义来讲，主键索引就是聚簇索引，而非主键索引就是二级索引。一般情况下所有都是B+TREE结构的，除非特别指定所有为HASH结构。InnoDB 的表必定是有一个主键索引（聚簇索引）的，即使不指定某个字段为主键，表结构中也会一个row_id字段来充当聚簇索引。上面聚簇索引的叶子节点存储结构类似于：

而二级索引叶子节点 idx_name 的存储结构类似于：

二级联合索引idx_author_name 的存储结构类似于：

可以知道聚簇索引叶子节点存储了所有字段信息。 二级索引叶子节点存储的是索引字段和主键字段。

上面就是聚簇索引和二级索引的区别，他们叶子节点存储的信息量不同。就因为这点差异，导致了查询效率的差异。

例如我们要查id = 1的数据信息，和查书名为book1的数据信息。

select * from book where id = 1; //语句1
select * from book where name = 'book1'; //语句2

语句1 用到了聚簇索引，语句2 用到了idx_name 索引。语句1 执行过程为从聚簇索引中查找id为1的数据，并取出所有字段信息。语句2 执行过程为从idx_name 中查找name为book1的数据，并取出id信息，由于idx_name索引中并没有存储其他字段的信息，所以为了拿到其他数据，必须拿着查到的id信息 到聚簇索引中再次根据id查找。这个过程称为回表。可以看出使用二级索引进行查找 要比使用聚簇索引查找多了一步回表动作。那么查询效率二级索引一定不如聚簇索引吗？

规避回表

现在，我们想知道author1 有哪些书？

select id,author,name from book where author = 'author1' //语句3
select * from book where author='author1' //语句4

语句3用到了idx_author_name 索引，但是执行过程中这条select语句不需要进行回表。分析上面的页20结构，我们就知道idx_author_name 索引已经包含了author,name,id 这三个字段的数据，而select语句查询也只需要这三个字段。语句4 由于使用 * ，需要查询到所有字段信息，所以还是需要进行回表操作。性能语句3高于语句4。这种只需要使用到二级索引的查询，不需要进行回表操作的方式称为覆盖索引。

相信大家看到过有一条Mysql 规范是 避免写select *这种查询方式。这就是其中原因之一。

到底能不能用到索引

select * from book where name='book1' and author = 'author1'; //语句5
select * from book where name in ('book1') or author in ('author1');//语句6
select * from book where published = '2018-01-02' and author = 'author2'; //语句7
select * from book where published = '2018-01-02' or author = 'author2'; //语句8
select * from book where published = '2018-01-02' and author like '%author2'; //语句9

建议大小先自己想下，给出答案然后看下去。

语句5，可能使用到 idx_author_name 索引，也可能使用到idx_name 索引，执行的时候查询优化器会分别计算2种索引的效率，使用性能高的进行查询。

语句6， 既有in 又有or。 可能使用idx_author_name，也可能使用idx_name,但最终没有用到任何索引，进行全表扫描。

语句7， 可能用到idx_author_name 索引，最终使用idx_author_name进行查询

语句8， 可能用到idx_author_name 索引，最终使用全表扫描查询。

语句9， 没有可用索引，最终使用全表扫描。

由于查询优化器会对各种可能的查询方式与全表扫描的方式进行对比，选取效率高的方式进行最终的查询。

判断索引有没有可能被使用到，我的方法是画出索引的结构，然后根据搜索条件进行对比。比如上面的语句5， 只有熟练脑子中可以立刻反映出主键索引，idx_name 和idx_author_name 的结构，接下去看到查询语句中有name 和 author 两个字段。根据idx_name 中就是以name 为key 的，所以能用到。而对于联合索引idx_author_name，按照索引定义时的顺序，从左往右匹配（最左匹配原则），定义时的顺序为author,name。所以这个语句5可以用到idx_author_name 索引。 联合索引中字段顺序至关重要。

select * from book where name = 'book1' and published = '2018-01-02' 

//可以用到idx_name 索引，但是用不到idx_author_name，虽然idx_author_name索引中也包含了name字段，idx_author_name能被使用到的条件是搜索字段中必须先包含auhtor字段。

语句6为什么用不到索引？首先or 的语法我们知道这个查询的意思是 查找书名为book1 的数据 或者查找作者为author1 的数据。虽然查找书名为book1 时可以用到idx_name ,查找作者为author1时可以用到idx_author_name， 但是优化器发现需要同时用到2个二级索引并回表，还不如直接全表扫描返回来的效率高。所以就直接选择了全表扫描的方式。但是并不是用or 就一定会导致用不到索引。比如我们查询书名'book1’或者'book2'的数据时。

select * from book where name = 'book1' or name = 'book2';
select * from book where name in ('book1','book2');

这两种方式都是可以用到索引的，而且性能相同。

语句9 使用like 模糊匹配。 由于使用是‘%author2’ 前缀模糊匹配，导致idx_author_name 无法使用，如果使用'author2%'进行匹配则可以用到idx_author_name索引。

简化查询条件必杀秘技

把与索引不相关的字段查询条件替换为True例如语句7，8，9，中published 任何索引都没有用到这个字段那么进行替换:

select * from book where True and author = 'author2'; //语句7等价select * from book where author = 'author2';
select * from book where True or author = 'author2'; //语句8等价select * from book where True 
select * from book where True and author like '%author2'; //语句9等价select * from book where author like '%author2'

等价后的语句相信大家都能很快的说出用到了什么索引。

对于简单的查询可能体现不出这个秘技的价值，我们来看下面的语句：

SELECT
	* 
FROM
	book 
WHERE
	(
		(
			`name` = 'book1' 
			AND author = 'author2' 
			OR published = '2018-01-02' 
		) 
		OR ( `name` = 'book2' AND published = '2018-01-02' ) 
	) 
	AND ( author = 'author2' OR published = '2019-02-02' )

看到这种语句是不是一下子头就疼了，其实运用我们的秘技转换:

SELECT
	* 
FROM
	book 
WHERE
	(
		(
			`name` = 'book1' 
			AND author = 'author2' 
			OR True
		) 
		OR ( `name` = 'book2' AND True ) 
	) 
	AND ( author = 'author2' OR True )

再次简化

SELECT
	* 
FROM
	book 
WHERE
	(
		True
		OR ( `name` = 'book2') 
	) 
	AND  True 

再简化

SELECT * FROM book where True and True

简化后的语句可以看到就是一个全表查询了。