上一篇我们从索引的存储结构分析，说到了B Tree索引、Hash索引、FULLTEXT全文索引。这一期，我们在从其他层次学习洗索引。
一、前期回顾：
从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引以及R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从键值划分：主键、辅助
从数据存储以及索引逻辑关系划分：聚集索引、非聚集索引
二、索引的分类
2.1、普通索引
普通索引既我们平时常用的索引，默认使用的是BTree。
ALTER TABLE `user`ADD INDEX `index_userNm` (`username`) USING BTREE COMMENT '用户名索引';-- 或者ALTER TABLE `user`ADD INDEX `index_userNm` (`username`);
2.2、唯一索引
与普通索引类似，不同的点在于：索引的值必须唯一，允许有空值。换句话说 某种程度上说的就是表中索引锁标记的唯一列。
ALTER TABLE `user` ADD UNIQUE INDEX `index_un_id`(`id`);
主键索引
与唯一索引的区别就是索引标记的列不允许有空值，换句话说就是主键列上加的索引。
ALTER TABLE `user` ADD PRIMARY KEY (`id`);
2.3、复合索引
又名联合索引，也是我们比较常用的索引类型之一。在数据表的2列或>2列共同组成的索引。而上面我们说的普通索引、唯一索引、主键索引又叫单一索引。顾名思义是指索引列只有一列，而用户可以在多个列上建立索引，这种索引就叫复合索引，也叫组合索引、联合索引，复合索引可以代替多个单一索引，相遇多个单一索引，复合索引所需的开销更小。
在这里复合索引还有个小小的区分：窄索引、宽索引
-
窄索引：指索引列为1-2列的索引
-
宽索引：指索引列超过2列的索引
索引设计原则：能用窄索引不用宽索引，因为窄索引往往比组合索引更有效
值得我们重点关注的是：
需要加索引的字段，要在where条件中。
数据量少的字段不需要加索引。最窄的字段放在键的左边。
如果where条件中是OR关系，必须所有的or条件都必须是独立索引，否则加索引不起作用。见：mysql关于or的索引问题
最左匹配原则。
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
eg:
假设某个表有一个联合索引（c1,c2,c3,c4）以下选项哪些字段使用了该索引：
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=? and c5=? order by c2,c3
E where c1=? and c2=? and c5=? order by c2,c3
接下来借用上面的例子，我们求证一下：
首先创建表：
CREATE TABLE t( c1 CHAR(1) not null, c2 CHAR(1) not null, c3 CHAR(1) not null, c4 CHAR(1) not null, c5 CHAR(1) not null)ENGINE myisam CHARSET UTF8;
创建索引：
alter table t add index c1234(c1,c2,c3,c4);
随机搞两条数据：
insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2')使用MySql Explain开始分析题目结果：
A 选项：where c1=x and c2=x and c4>x and c3=x
说明下：UTF-8 编码，一个索引的长度为3，如上图我们可以看到，c1,c2,c3,c4均使用到了该索引。
但是如果我们将查询条件调整下或者去掉一个，索引又是怎么使用的呢：
由上面4个图可以看出，我们分别调整了各个列的位置，使用>和使用>的位置，发现索引都是正常使用的，所以可以得出：
使用“<” / “>”并不影响索引的选择
where条件和创建索引的列保持一致时，条件的位置（顺序）并不影响索引的选择。
但是，如果我们对A组where条件做些调整呢？
当我们删除分别其中一个条件c1、c2、c3后，发现使用索引的个数也发生了变化。得出结论：
当使用复合索引时，没有使用第一个复合索引，不触发索引的使用
使用复核索引时，索引的选择与where的条件顺序和索引建立时的顺序是否一致存在关系，当顺序中断时，后续的索引列将不被选择。
如果我们对A组where条件做些调整呢？
当我们使用or，或者like时，通过上面4张图可以清醒的得出：
当使用or时，不会使用索引
当条件顺序与索引创建顺序一致，且使用 “like” / “like%”时，索引可以正常被选中
当条件顺序与索引创建顺序一致，且使用 “%like%”，索引无法被选中
当条件顺序与索引创建顺序不一致时，且第一个条件不是索引创建时的首列，则无法选中索引
B选项：where c1=x and c2=x and c4=x order by c3
通过上图可以发现，key_len长度说明c1，c2字段用到了该索引，Extra显示并没有使用临时表进行排序，说明排序是使用了索引的，但并没有计算在key_len值中，也没有起到连接c4的作用，说明索引到c3这里是断掉的。
排序其实是利用联合索引直接完成了的，即：使用了c1234联合索引，就已经使得c1下c2，c2下c3，c3下c4是有序的了，所以实际是排序利用了索引，c3字段并没有使用该索引。
C选项：where c1=x and c4= x group by c3,c2
通过上面两图，能看出使用group by 一般先生成临时文件，再进行排序，但是字段顺序为c2，c3时，并没有用临时表进行排序，而是利用索引排序好的；当group by字段为c3，c2时，由于与索引字段顺序不一致，所以分组和排序并没有利用到索引。
D选项：where c1=? and c5=? order by c2,c3
通过此图可以看出 order by 和group by 类似，字段顺序与索引一致时，会使用索引排序；字段顺序与索引不一致时，不使用索引。
E选项：where c1=? and c2=? and c5=? order by c2,c3
其实选项E的结果分析在上述ABCD的结果中都分析过了，这里只有c1，c2字段使用了该索引。
综上所述问题答案：
A:四个字段均使用了该索引
B:c1，c2字段使用了该索引
C:c1字段使用该索引
D:c1字段使用该索引
E:c1，c2字段使用了该索引
三、小结：
索引的最左原则（左前缀原则），如（c1,c2,c3,c4....cN）的联合索引，
where 条件按照索引建立的字段顺序来使用（不代表and条件必须按照顺序来写），
如果中间某列没有条件，或使用like会导致后面的列不能使用索引。
索引也能用于分组和排序，分组要先排序，在计算平均值等等。所以在分组和排序中，如果字段顺序可以按照索引的字段顺序，即可利用索引的有序特性。
不知不觉写到这又有 2.5k 多字了，本期我们就写到这里，下期我们继续
探讨索引。
…………………………………分割线……………………………
不积跬步，无以至千里；不积小流，无以成江海。
关注我，每天分享一些小知识点。分享自己的小心得，包含但不限于初、中、高级面试题呦！！！
我都墨迹这么半天了 ，你不点关注，不点赞，不收藏，还不转发，你想干啥！！！
网友评论