MySQL数据库索引相关知识

定义

索引时帮助MySQL高效获取数据的数据结构。
简单说：排好序的快速查找数据结构

重点

• 局部性原理：指处理器在访问某些数据时短时间内存在重复访问，某些数据或者位置访问的概率极大，大多数时间只访问_局部_的数据。推荐阅读:局部性原理——各类优化的基石.
• 树的深度为磁盘io次数
• 一个节点存储16K（innodb的datapagesize默认16KB）数据。一次io需要去页的整数倍数倍。计算机读取数据的时候，最小交换单位为一页，最多可以读出4KB或者8KB。
• 如果没有主动设置主键，就会选一个不包含NULL的第一个唯一索引列作为主键列，并把它用作一个聚集索引。如果没有这样的索引就会使用行号生成一个聚集索引，把它当做主键，这个行号6bytes，自增。可以用select _rowid from table来查询。
• 索引预先加载到内存中，加载时是分页加载的。

存储原理

B-Tree

叶子几点存储16K数据，因为一个节点存储数据，导致存储的节点数变小，所以使用B+tree

B+Tree

MyISAM

主键索引	二级索引
非聚集索引（索引与数据分离），采用叶子节点存储数据地址的方式存储	与主键索引存储模式相同

InnoDB

主键索引	二级索引
聚集索引（索引与数据结合），采用叶子节点存储数据方式存储	叶子节点存储主键索引

主键使用自增整形主键

• 整形占的字节数少，8BK
• 比较时整形比较快
• 自增直接在叶子节点添加不需要太多调整

联合索引

多列索引组合，按照字段先后顺序进行排序。

原则

那些情况应当创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引
5. where里用不到的字段不创建
6. 单键、组合索引偏向组合
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（order by、 group by）

不适合见索引

1. 表记录太少
2. 经常crud表
3. 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。某字段重复内容多，建索引没效果