MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序
MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序
What's Index ?
索引就是帮助RDBMS高效获取数据的数据结构。
索引可以让我们避免一行一行进行全表扫描。它的价值就是可以帮助你对数据进行快速定位。
索引分类
按照功能逻辑来分
- 普通索引
INDEX(col_name)或者key index_name(col_name)没有任何约束，一张表可以有多个普通索引 - 唯一索引
UNIQUE INDEX index_name(col_name)在不同索引上增加了唯一约束，允许多个ull值 - 主键索引
PRIMARY KEY (col_name)主键约束=UNIQUE+NOT NULL，一张表只能有一个主键索引---该性质由主键索引的物理实现方式决定---数据在文件中只能按照一种顺序进行存储 - 外键索引
CONSTRAINT foreign_key_name FOREIGN KEY(col_name) REFREENCES table_name(col2_name) - 全文索引
FULLTEXT INDEX index_name(col_name)...)ENGINE=MyISAM;MySQL自带的全文索引仅支持英文，一般我们使用专门的全文搜索引擎Elasticsearch或者solr。
按照物理实现方式来分（也是按照叶子结点的内容来分）索引模型见此
- 聚集索引（InnnoDB主键索引/ clustered index），叶子节点存放的是整行数据。一张表只能有一个聚集（InnnoDB主键）索引。查询时，尽量选用主键进行查询，这样可以减少回表次数，提高效率
- 非聚集索引（InnnoDB非主键索引/二级索引/辅助索引/second index）,叶子节点存放的是主键的值，为了找到数据，它单独维护了一个索引表（树），先在索引表（树）中查询主键值，再到主键索引上查找对应的真实数据，这个过程称为回表
- 区别：
- 聚集索引叶子节点存储整行数据，非聚集索引存储的是主键值---是数据位置，非聚集索引不会影响数据表的物理存储顺序。（而聚集索引决定物理存储结构）
- 一张表只能有一个聚集索引，但可以有多个非聚集索引
- 主键索引的查询效率高，但对数据的增删改的效率比非主键索引低
但是请格外注意：主键索引不一定是聚集索引
因为聚集索引决定了数据库物理存储结构，而主键只决定数据库逻辑组织方式
在InnoDB中，主键是一个聚集索引，但当一个表没有主键或者没有索引，innodb也会有对应的处理规则。而在MyISAM引擎中，主键不是聚集索引。一般按照innodb与机具集索引的关系进行讨论。
按照字段个数来分
- 单一索引
- 联合（组合）索引
最左前缀原则
创建联合索引时我们要注意联合索引的字段顺序
因为B+树的结构，所以有最左前缀原则/最左匹配原则，意思就是，对于联合索引，只要查询条件与联合索引从左到右部分字段顺序相匹配，该次查询就可以利用联合索引进行加速。
例如现有联合索引（x,y,z）
如果查询条件是WHERE x=1 AND y=2 AND z=3；那么就是对该联合索引的完全匹配。
如果查询条件是WHERE x=1 AND y=2；那么就是利用了该联合索引（符合最左匹配原则）
当然，WHRER x=1;可以以利用该联合索引
如果是WHERE y=2 AND z=3；或者 WHERE z=3；等，就无法利用联合索引（x,y,z）
如何安排联合索引的字段顺序
-
原则一：精简索引数量
如果联合索引的顺序可以让我们少维护一个索引，那么这个联合索引顺序就该被优先考虑
比如说现在你有两个字段
那么只需要建立（a,b）+（b）两个索引即可。而不是（a）+（b）+（a+b）
因为最左前缀原则给予了联合索引复用能力
当然，如果仅有根据字段a来进行索引的要求，b字段的单一索引可以不创建。
如无必要，勿增实体 -
原则二：精简索引占用的空间
那么在a，b两个字段，中我们应该选用哪个字段来做单独的索引（b）呢
这时候就需要考虑空间了，一般将字段长度比较小的当做这里的单一索引（b）。
比如说有name和age两个字段，name字段比age大，如果我们使用（age,name）+（name）的形式，所占用的空间就比（name，age）+（age）的要大
覆盖索引
用自己的话总结，覆盖索引就是不需要回表的查询
翻译过来就是， 要的查询结果就是主键值且查询条件就是非主键索引字段，即在索引树上的查询结果就是我们需要的结果。
索引覆盖可以显著减少树的搜索次数，显著提升查询性能。是常用的性能优化手段。
例如，现有主键id，有已经建立了普通索引的字段col(int)，我们查询SELECT id from test where col between 6 and 8;
执行该语句时，引擎内部在索引树上读取了三条记录，但因为对于MySQL的Server层来说，该语句就是拿到了符合条件的两个记录，因此MySQL认为扫描行数是2
索引下推
MySQL5.6版本引入索引下推，就是在只能利用部分联合索引时，对剩余联合索引的字段（不符和最左匹配原则的字段）进行先判断，先过滤，通过先过滤来减少回表的次数。
例如有索引（name，age），然后执行SELECT * FORM student WHERE name like '高%' and age=21;
5.6后，MySQL就会先利用联合索引找出所有姓高的名字，然后仅对age=21的主键值进行回表，而不是找出所有姓高的同学之后，对他们的所有主键值都进行回表。
