mysql索引详解

分类

按数据结构分类：b+ tree索引、Hash索引、Full-text索引
按物理存储分类：聚簇索引(主键索引)、二级索引(辅助索引)
按字段特性分类：主键索引、唯一索引、普通索引、前缀索引
按字段个数分类：单列索引、联合索引

按数据结构分类

索引类型	InnDB引擎	MyISAM引擎	Memory引擎
B+Tree 索引	Yes	Yes	Yes
HASH索引	NO	NO	Yes
Full-Text	Yes	Yes	NO

InnoDB是Mysql默认存储引擎，B+Tree也是使用最多的引擎
索引列选择：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

B+ Tree

B+Tree 是一种多叉树，叶子节点存放数据，非叶子节点存放索引，叶子节点间用双向链表链接。

查询过程

select*from product where id=5;

将5与跟节点点索引数据(1,10,20)比较，找到在1-10之间，继续向第二层索引数据找
在第二层索引数据(1,4,7)中进行查找，找到在4-7之间，继续向第三层索引查找
直到找到索引值为5的行数据

B+Tree对于千万级数据也只需要3-4层高度就能满足，相比于B树和二叉树来说，最大的优势在于查询效率很高，因为在数据量很大的情况下，查询一个数据的磁盘I/O依然维持在3-4次。

二级索引查询数据的过程

主键搜音的B+Tree和二级索引的B+Tree的区别：

主键索引的B+Tree的叶子节点存放的实际数据，所有完整的用户记录都存放在主键索引的B+Tree的叶子节点里
二级索引的B+Tree的叶子节点存放的是主键值，而不是实际数据。

例如执行查询语句：


select * from product where product_no = '0002';

先在二级索引查询，获得主键值，再去主键索引查询行数据。这个过程叫回表，需要查询两个B+Tree才能查到数据。

但是在二级索引就能获得结果的过程叫「覆盖索引」，只需要查询一个B+Tree
例如：


select id from product where product_no = '0002';

为什么选择b+Tree

B+Tree与B Tree
B+Tree与二叉树
二叉树儿子节点只有2个，搜索复杂度比B+Tree高很多
B+Tree与hash
Hash只适合做等值查询，不适合做范围查询

按物理存储分类

聚簇索引、二级索引，上面已经讨论区别。

按字段特性分类

主键索引

建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列不允许空值

唯一索引

建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

普通索引

建立在普通字段上的索引，即不要求字段为主键，也不要求是UNIQUE。

前缀索引

对字符类型字段前几个字符建立索引，而不是整个字段上建立索引。

按字段个数分类

单列索引、联合索引

建立在单列上的索引称为单列索引
建立在多列上的索引称为联合索引

联合索引

联合索引将多个字段作为索引，先按照第一个key比较，在第一个key相同的情况下，再根据第二个字段比较，以此类推。
因此，联合索引存在最左匹配原则，如果在使用时不遵循最左匹配原则就会导致索引失效。

联合索引范围查询：
并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，可能部分字段没有用到联合索引。
这种情况发生在范围查询。范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。
联合索引为(a,b)
例一：


select * from tb where a>1 and b=2;

a>1会走索引，但是在a>1条件的二级索引范围中，b的字段是无序的，所以b=2是不会走索引的。

例二：


select * from tb where a>=1 and b=2;

a>=1会走索引，虽然在a>1的范围中，b是无序的，但是在a=1的范围中，b是有序的。所以在查找a>=1 and b=2时，会从a=1 and b=2这条记录开始查询，会减少一些查询范围，a和b都走了索引。

例三：


select * from tb where a between 2 and 8 and b=2;

a和b都走了联合索引

例四：


select * from tb where name like 'j%' and age=22;

与例二相同，字符串会以字段的值排序，所以都会走联合索引。

总结：
综上所示，联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配，前面我也用了四个例子说明了。

索引下推

在 MySQL 5.6 之前，只能从 ID2 （主键值）开始一个个回表，到「主键索引」上找出数据行，再对比 b 字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化。

索引区分度

建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的sql使用到。

区分度时某个字段不同值的个数除以表的总行数。

联合索引进行排序


select * from order where status=1 order by create by create_time asc

对于这个查询语句可以对status和cerate_time 建立一个联合索引，在status筛选完之后就已经排好了create_time 排好了序，提高了查询效率。

什么时候需要创建索引

索引缺点：

需要占用物理空间，数量越大，占用空间越大
创建索引和维护索引要耗时间，数据量越大，耗费时间越多
降低增删改的效率，每次增删改都会进行动态维护。

需要索引

字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

不需要索引

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

优化索引

前缀索引优化；
覆盖索引优化；
主键索引最好是自增的；
防止索引失效；

前缀索引优化

以某个字段的前几个字符建立索引，减少索引字段的大小
缺点：

order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；

覆盖索引优化

将需要的字段建立成联合索引，可以直接从二级索引中查询到记录，避免回表操作，减少I/O操作。

主键索引

使用自增主键，可以让新记录按顺序添加，不用移动之前有的数据。而使用非自增主键，会导致页分裂，可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

索引最好设置为NOT NULL

第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中 1 字节空间存储 NULL 值列表

防止索引失效

索引失效：

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。