MySQL 索引 聚簇索引 非聚簇索引 解析

1、索引

基本概述：索引是数据库管理系统中一个有序的数据结构，协助快速查询更新数据库表中的数据

索引类型：普通索引，唯一索引(唯一)，主键索引(唯一非空)，全文索引(解决like效率低的索引)

数据结构：二叉查找树，查找效率受到其深度的影响，极端情况下会行程单链表的结构，查询效率慢，之后演变成平衡二叉查找树，通过左右旋的操作使得左右子树的深度相差不大，平衡二叉树每个节点如果只存储一条数据大大浪费空间，所以最终变为多路平衡查找树，即所谓的B Tree,MySql底层的数据结构是加强版的B TREE即：B+Tree

1.1 B TREE结构图

1.2 B+TREE结构图

B+TREE一路能够存储多少条数据 

一个节点16k，大约1024*16个字节，引用+键值大约16个字节，所以一个节点可以分1024路，叶子节点存储数据，一条数据约1k，可以存储16条数据，所以一路存储的数据共1024*1024*16=16777216，大约一千多万条数据，节省了与磁盘的io操作，树的深度也就在2-3之间

 b+tree的特点

1、b tree能解决的问题，b+tree都能解决

2、扫库扫表能力更强

3、磁盘读写能力更强

4、排序能力更强

5、效率更加稳定：因为数据都是存储在叶子节点上，保证查询数据，每次的io效率是稳定的

2、索引方式

1、B+TREE

2、Hash：采取键值对模式，时间复杂度永远是0(1)查询快，缺点是无序的，只能等值查询，向> <这种范围查询是搞不定的 ;同时也会出现hash碰撞的问题

InnoDb主键索引，其他索引(辅助索引) ：Innodb是以主键索来组织数据存放的

如果没有定义主键索引的话，它会找一个不包含null值得唯一字段作为主键索引

如果没有这种字段，它会选择隐含得rowid作为主键索引

2.1 B树和B+树的区别

3、聚簇索引、非聚簇索引

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；

　　一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。

　　我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据

3.1 聚簇索引 

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，聚簇索引的叶子节点称为数据页，所以数据也是索引表的一部分，每张表只能拥有一个聚簇索引；

innodb通过主键聚集数据，如果没有定义主键，为选择非空唯一的索引代替，如果没有这样的索引，innodb会隐式定义一个rowID代替主键进行聚集数据

优点：

1. 数据访问更快，因为聚簇索引将索引和数据保存在同一个结构中，因此聚簇索引中获取数据比非聚簇索引更快
2. .聚簇索引对于主键的排序查找和范围查找速度非常快

缺点：

1. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
3. 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

3.2 非聚簇索引（辅助索引） 

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

　　Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

　　辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引

总结

区分聚簇索引和非聚簇索引主要的一点就是明白数据和索引是否在一起，如果数据和索引在一起的话就是局促索引，针对innondb来说，主键索引就是聚簇索引，但是针对myisam来说，主键索引是非聚簇索引，因为我们知道，myisam存储引擎，myi文件存储索引，myd存储数据，索引和数据区分开来的，所以是非聚簇索引

索引的适用原则

离散度越高建索引越好 

联合索引的话满足最左匹配原则

覆盖索引，指查询具体的字段索引数据，不需要再走主键索引，犹如上图的辅助索引，只不过不需要再查询一遍主键索引拉

1、在用于where判断order排序和join的on字段上创建索引

2、索引的个数不要过多：索引创建过多消耗空间

3、区分度低的字段不要建立索引，比如性别

4、频繁更新的值，不要作为主键，因为会发生数据结构的调整

5、联合索引把离散度高的字段放在前面

6、创建了联合索引之后，不需要再创建单列的索引

什么时候用不到索引？

1、索引列上适用函数计算，replace，sum,count

2、字符串不加引号，出现隐式转换 id字段是字符串类型，sql --》where id=1不走索引，where id ='2' 走索引

3、like条件前面带%。 where name like 'wang%'走索引 where name like '%wang' 不走索引

4、负向查询能用到索引吗 <> != not in？ where name <> 'wang'走索引