索引、B树和B+树

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

索引类型

一、主键索引

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

二、唯一索引

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

不能出现重复，可以为NULL，NULL不算为空

三、组合索引

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list，column_list2)

多个字端组合的唯一索引特点和唯一索引一致

最左原则：查询时，不使用最左的字段，无法命中索引

可以设置长度

四、普通索引

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

可以为NULL，可以重复

五、全文索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

索引的失效条件

1.like的查询以%开头，索引失效

2.条件中用or，即使其中有条件带索引（有条件不带索引），也不会使用索引查询

phonenum没有遵循最左原则

3.对于多列索引，不是使用的第一部分，则不会使用索引

4.索引参与到函数计算中

多表联查索引

多表联查也会触发索引，两边都有索引，会优化一个使用快的索引

删除索引

索引的分类和Mysql InnoDB底层内容实际保存方案

聚簇索引

找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。

非聚簇索引(二级索引)

索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

聚簇索引的优点

1.当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。

2.当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。

3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点

1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

4.采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

B树和B+树

B树

1.所有键值分布在整颗树中（索引值和具体data都在每个节点里）

2.任何一个关键字出现且只出现在一个结点中；

3.搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据）

4.在关键字全集内做一次查找,性能逼近二分查找；

B+树

1.所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)

2.为所有叶子结点增加了一个链指针

B+树的优势：

• 由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。

• B+树的叶子节点都是相连的，因此对整棵树的遍历只需要一次线性遍历叶子节点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

我们知道二叉查找树的时间复杂度是Ｏ(logN)，效率已经足够高。为什么出现B树和B+树呢？当大量数据存储在磁盘上，进行查询操作时，需要先将数据加载到内存中（磁盘IO操作），而数据并不能一次性全部加载到内存中，只能逐一加载每个磁盘页（对应树的一个节点），并且磁盘IO操作很慢，平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。为了减少磁盘IO的次数，就需要降低树的深度，那么就引出了B树和B+树：每个节点存储多个元素，采用多叉树结构。这样就提高了效率，比如数据库索引，就是存储在磁盘上，采用的就是B+树的数据结构。