innodb索引

为什么用索引：

1 大大减少服务器扫描的数据量

2 索引帮助服务器避免排序和临时表

3 顺序读耗时远远小于随机IO耗时

索引仅仅适用于中到大型的表，起到很好的效果

对于小表，全表扫描更高效

对于极大的表，建立索引的代价显著上升，采用分区技术更好的解决问题。进行分库分表。

 

b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”（提高磁盘效率）；

b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定，不会有时快，有时慢；

对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历，且b+树范围查找为顺序读，性能极其高

 

B+树：

1 全值匹配

2 匹配最左前缀

3 匹配列前缀

4 匹配范围值

5 全值匹配某一列并范围匹配另一列

6 覆盖索引避免回表

 

限制：

组合索引  必须满足最左前缀原则，带头大哥不能死，中间兄弟不能丢，前有范围查询，后不能用索引

 

哈希索引：

1  无法用于排序

2  不支持索引部分匹配查找

3  只支持全值匹配

4  哈希冲突较高，影响性能

 

1 查询列必须为独立的列

     1 不能是表达式的一部分

     2 不能是函数的参数

2  前缀索引 索引选择性

索引列为字符串且很长时使用前缀索引，使用多长的前缀需要兼顾

A：前缀的长度尽可能短

B：索引的选择性尽可能高（1/n ~1）

选择性：不重复的记录数目/总记录数目

计算整个列的选择性：SELECT COUNT(DISTINCT 字段)/COUNT(*) FROM 表;

计算前缀的选择性：SELECT COUNT(DISTINCT LEFT(字段,前缀长度))/COUNT(*) FROM 表;

前缀的选择性接近整个列的选择性即可

 

前缀索引优点: 1节约空间  2 索引更快  缺点：不支持排序以及覆盖索引

 

后缀索引：身份证号前6位辨识度不高，反转后存储建立前缀索引

 

多列索引

 

如何选择索引列的顺序：

1  将选择性最高的列放到索引最前列。

2  根据运行频率最高的查询调整索引列的顺序，并兼顾索引的选择性

 

聚簇索引

 

聚簇：数据行和相邻的键值紧凑地存储在一起

innodb 在主键索引中保存了索引和数据行

 

优点:

1  把相关数据保存在一起：实现电子邮件，根据用户id聚集数据，磁盘顺序读获取该用户所有邮件

2  索引与数据在一个BTree中，从聚簇索引中查询数据比非聚簇索引中快（避免回表）

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

 

缺点：

1  更新聚簇索引代价高，强制innodb将每个被更新的行移动到新的位置。

2  插入新记录，可能会导致页分裂问题

3  行稀疏或页分裂时导致全表扫描变慢

4  二级索引未能使用覆盖索引的时候会导致回表，两次索引查找。因为二级索引叶子节点存储的是主键值而非记录的物理地址，需要再去主键索引检索该记录数据。