索引
索引相当于书的目录:查询一张表的时候有两种检索方式:
第一种方式:全表扫描
第二种方式:根据索引检索
索引提高效率的原因
原理是缩小的扫描范围。
如果表中数据经常被修改,这样就不适合添加索引,因为一旦添加则索引需要重新排序。
什么时候考虑添加索引:
- 数据量庞大。
- 经常在where子句中。
- 该字段很少的增删改操作
主键和具有unique字段的自动会添加索引
索引什么时候会失效
模糊查询时且 第一个通配符为%时 索引会失效
例如: select * from user where name = ‘%三’
索引数据结构
- 二叉树(效率低,可能会退化成链表)
- 红黑树(树的高度仍然太高,每一层的递进就是一次磁盘的I/O查询,十分浪费时间,为了树的高度可控,需要用到B树)
- 哈希(根据哈希表的特性,查找单个非常快,但是致命伤是不支持范围查找,所以导致在工作中几乎用不到hash索引)
- B树:
红黑树下改进,每个节点开辟更大的空间,查询时将整个空间放入内存当中,一次IO查询到的索引更多,内存的读取速度相对于一次磁盘读取速度来说等于忽略不计。
1、叶节点具有相同的深度,叶子节点的指针为空
2、所有索引元素不重复
- B+树(B树的变种)
1、非叶子节点不储存data,只储存索引(冗余),可以存储更多的索引(因为非叶子节点不需要存储data数据,所以一个节点中可以存储更多的索引) MYSQL默认B树一个节点可以存放16K的数据。
2、叶子节点包含所有索引字段,且索引从左往右有序。
3、叶子节点用指针连接,提高区间访问的性能。(还能更快速的支持范围查找,如果没有双向指针连接,则从15查到18后需要回到上一个节点才能找到20和30.)
4、(默认指针内存存放6个字节)假如一个三层B+树占满了可以存放约2000万个索引。
聚集索引(聚簇索引)
- innodb引擎为聚集索引,表存储两个文件, frm表结构文件, ibd文件(索引文件与数据文件的结合)
innodb主键索引实现
叶子节点中储存主键索引和其对应的行数据
innodb非主键索引实现
叶子节点存储索引所在行的主键索引
非聚集索引
- Myisam使用的是非聚簇索引,表储存三个文件,frm表结构文件,myd数据文件,myi索引文件(索引文件和数据文件是分离的)
myisam存储引擎索引实现
叶子节点存放索引和地址,
查找的步骤: 在B+树中找到索引—>再找到其对应的数据地址—>去到MYD数据文件中找地址所指示的行数据(此步骤也称为回表)。
InnoDB表必须建主键,并且推荐使用整型自增的主键。
原因:
- 如果不自己建主键,则数据库会自动逐列查找不重复的列并为其添加唯一索引,从而来构建B+树,若不存在,则生成一个隐藏的唯一索引列来维护B+树。
- 使用整型在索引大小比对的时候速度更快,并且存储空间更小。uuid是字符串(空间占用大,大小比对时效率还低)
- 使用自增是为了,在添加节点时维护B+树索引时更有效率,若随机插入数据会导致B+树插入节点后还要节点分裂再做树的平衡,十分影响效率。