建一个表
CREATE TABLE `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
在这10000条记录里面七上八下地分布了5条author_id = '1'的记录,只不过其他条件则不相同。
来看这条T-SQL:
SELECT * from article WHERE author_id = 1 AND title = '1' AND comments = '1'
首先考虑建MySQL单列索引:
在author_id列上建立索引。执行T-SQL时,MYSQL很快将目标锁定在了author_id = 1的5条记录上,取出来放到一中间结果集。在这个结果集里,先排除掉了title不等于1的记录,然后排除掉了comments不等于1的记录,最后筛选出唯一的符合条件的记录。
虽然在author_id上建立了索引,查询时mysql不用扫描 整张表,效率有所提高,但离我们的要求还有一定差距。同样的,在title和comments分别建立的MySQL单列索引的效率相似。(一张表建n个单列索引,最后只会有一个索引生效。)
为了进一步提高效率,考虑建立复合索引。就是将author_id,title,comments建到一个索引里:
ALTER TABLE article ADD INDEX author_id_title_comments(author_id,title,comments);
执行T-SQL时,MySQL无须扫描任何记录就可以找到唯一的记录。
有人要问了,如果分别再author_id,title,comments上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率一样吗?大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
以上解释了为什么复合索引比单列索引查询效率高。
单列索引和组合索引
索引分为单列索引(主键索引,唯一索引,普通索引)和组合索引。
单列索引:一个索引只包含一个列,一个表可以有多个单列索引。
唯一索引,索引列是唯一的,不重复。普通索引,索引列会出重复。
组合索引:一个组合索引包含两个或两个以上的列。组合索引本质上也是一颗B+树,只不过它的键值是大于等于2的。
聚集索引和辅助索引
接着说,对于Mysql的InnoDB 存储引擎来说,在绝大多数情况下使用 B+ 树建立索引,这是关系型数据库中查找最为常用和有效的索引,但是 B+ 树索引并不能找到一个给定键对应的具体值,它只能找到数据行对应的页,数据库把整个页读入到内存中,并在内存中查找具体的数据行。
而数据库中的 B+ 树索引可以分为聚集索引(clustered index)和辅助索引(secondary index),它们之间的最大区别就是,聚集索引中存放着一条行记录的全部信息,而辅助索引中只包含索引列和一个用于查找对应行记录的『书签』,在 InnoDB 中这个书签就是当前记录的主键。一个表只有一个聚集索引,但是可以创建多个辅助索引来提升性能。
对于InnoDB来说,默认创建的主键就是聚集索引,如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引,如果没有主键也没有合适的唯一索引,那么InnoDB内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增。
以上是两种不同的分类体系,比如聚集索引就是单列索引,如果创建单列的辅助索引那么就是单列索引,如果创建多列索引,那么就是辅助索引,也可以叫做是组合索引。
覆盖索引
就是说查询的字段就是建立索引的字段。select a from table where a = '1'
InnoDB支持覆盖索引,意思就是能从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录信息,所以大小远远小于聚集索引,这样可以减少大量IO操作。覆盖索引的另外一个好处是对于一些统计问题而言,接着我们继续看例子:
explain SELECT count(*) from article
可以看到,possible_keys为Null,但是实际执行的时候优化器却选择了辅助的单列所以id,而Extra的Using index就代表优化器进行了覆盖索引操作。
除此之外,对于(a,b,c)的复合索引,在通常情况下,只根据b进行查询的时候,一般情况下是不能使用组合索引的,这是最左前缀法则。但是如果是统计操作,并且是覆盖索引的,那么优化器会进行优化。
explain SELECT count(*) from article where title >1
可以看到,possible_keys仍然为Null,但是实际执行的时候优化器却选择了辅助的复合索引,即(author_id,title,comments)的组合索引,而Extra的Using index就代表了优化器进行了覆盖索引操作。
索引注意事项
全值匹配(覆盖索引)我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;(会出现没有带头大哥但是还是走了索引的情况,那就是覆盖索引)
索引列上少计算,范围之后((a,b,c)复合索引,b上有范围查询,c索引会失效)全失效;
LIKE百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用。
https://www.cnblogs.com/developer_chan/p/9223671.html