聚集索引、辅助索引、覆盖索引、联合索引, 最左前缀原则, 索引失效情况

一. 聚集索引（Clustered Index）
聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。
聚集索引的叶子节点称为数据页，每个数据页通过一个双向链表来进行链接，而且数据页按照主键的顺序进行排列。

二. 辅助索引
辅助索引(二级索引)：叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。

三. 覆盖索引
当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。

四. 联合索引
联合索引是指对表上的多个列进行索引。

联合索引也是一棵B+树，其键值数量大于等于2。键值都是排序的，通过叶子节点可以逻辑上顺序的读出所有数据。数据（1,1）（1,2）（2,1）（2,4）（3,1）（3,2）是按照（a，b）先比较a再比较b的顺序排列。

基于上面的结构，对于以下查询显然是可以使用（a，b）这个联合索引的：

select * from table where a=xxx and b=xxx ;

select * from table where a=xxx;

但是对于下面的sql是不能使用这个联合索引的，因为叶子节点的b值，1,2,1,4,1,2显然不是排序的。

select * from table where b=xxx

联合索引的第二个好处是对第二个键值已经做了排序
当执行

select * from buy_log where user_id = 2；

时，优化器会选择key(userid)；但是当执行以下sql：

select * from buy_log where user_id = 2 order by buy_date desc;

时，优化器会选择key(userid, buy_date)，因为buy_date是在userid排序的基础上做的排序。

五. 最左前缀原则
mysql 建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

如果有一个 2 列的索引 (col1, col2)，则已经对 (col1)、(col1, col2) 上建立了索引；
如果有一个 3 列索引 (col1, col2, col3)，则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引；
很容易从上面的联合索引看出这个原则的来源

六.注意

1. 范围查询
   mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引

2. like 语句的索引问题
   如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀
   在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描

3. 不要在列上进行运算
   如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描
   例如 select * from user where YEAR(birthday) < 1990
   可以改造成 select * from users where birthday <’1990-01-01′

4. 索引不会包含有 NULL 值的列
   只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL

5. 尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录

6. 覆盖索引的好处
   如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需读表，极大减少数据访问量

七. 索引失效情况
在数据库表中，使用索引可以提高查询速度。但是索引并不是对所有的查询操作都会生效的。比如以下几种情况，将导致索引失效：

1、如果查询条件用or，必须or条件中的每个列都加上索引，否则无效。

2、对于复合索引（又叫多列索引或者联合索引），如果查询的列不使用复合索引的第一部分，则不使用索引。例如，tb表的复合索引为（key1，key2，key3），则查询select * from tb where key2=1 and key2>5将不会使用索引。

3、如果like是以%开头的，则该列上的索引不会被使用。例如：select * from tb where key1 like “%a”; 即使key1列上建立了索引，该查询也不会使用索引。

4、存在索引列的数据类型隐形转换，则不使用索引。例如：列key1的数据类型为字符串，select * from tb where key1=1; 将不会使用索引。要想使用索引，必须将字符串用引号印起来。

5、where字句里对索引列有数学运算或者使用函数，则不使用索引