Mysql索引
- Mysql5.6的索引下堆:指待匹配的列都在联合索引中,但是存在索引跳过(如index(a,b,c);where a='' and c='');此时对于where的条件匹配 判定会发生在存储引擎层面,而不是返回给Server层在进行条件判定,其目的是减少回表次数
- 索引优化三原则
- 尽量减少单行访问;因为从海量数据中取出一条,将浪费很多资源
- 尽量顺序访问
- 顺序访问保证了I/O的顺序性,避免了随机I/O
- 顺序访问之后将不再需要进行其余的排序操作
- 数据列尽量只返回我们需要的;让数据检索走覆盖索引,避免二级索引的回表操作
- 何为索引?
- 索引实际上类似于目录,它可以帮助我们跳过全文查找,快速的找到我们所需要的内容.索引最大的优点便是节省时间
- 但是实际上索引的建立是一种以空间换时间的方式,在物理存储上依然是存在索引文件的
- 索引记录了关系表的索引信息,有利于查询;但是如果频繁修改、删除、新增,则对性能的损耗也很严重;因为**不但需要维护关系表还需要维护索引 **
- MySQL的索引类型
- MySQL索引的建立与MySQL索引列的顺序很大的关系
- B+树索引:
- 为什么采用B+树索引?;B+树索引的优势是什么?
- B+树索引的使用场景
- 全键值、键值范围、键前缀(只适合最左前缀匹配)
- B+树索引的限制场景
- 聚合索引没有按照索引列的最左列开始查找;如index(id,name,age);如果使用age则索引失效
- 聚合索引索引列不能跳索引列;如index(id,name,age);如果使用id,age则索引失效
- 如果存在范围查询,则范围查询的列的右边列索引失效
- 自适应Hash索引:当某个值被频繁使用时,innodb会自动在内存创建一个Hash索引,进而加快查找
- 哈希索引:
- 哈希索引基于Hash表实现的,只有在精确匹配时才有效
- 哈希索引通过所有索引列来计算hash值,哈希索引十分的快
- 哈希索引的限制
- 只包含行指针、Hash值,所以依然需要读取行;但是行在内存中,性能损耗忽略不计
- Hash索引是通过索引的Hash值插入,因此Hash索引不能用于排序
- Hash索引通过全部索引列生成Hash值,因此不支持部分索引列查找
- Hash索引只支持等值比较;不支持任何的范围比较
- 空间数据索引(R-Tree)
- 全文索引
- 聚簇索引
- 聚簇索引并非是一种单独的索引类型,它更加像一种数据存储的方式
- 聚簇索引的主键值与行的信息是绑定在一起的;如果没有主键怎么办?
- 优先选择唯一非空列作为索引列
- 如果不存在唯一、非空列,则采用隐藏的索引列(6个字节),随行的新增而ID自增
- 聚簇索引的优势
- 主键与数据在一块,减少了I/O次数,极大提升了I/O密集行应用的性能
- 主键与数据在一块,不需要进行回表
- 什么是回表?
- 指首先获取主键ID,在根据主键ID来获取具体信息(涉及了两次查询)
- 聚簇索引的劣势
- 数据插入的速度依赖于插入的顺序;B+Tree需要排序
- 数据更新的代价很高,因为需要移动行
- 当行移动时,有可能会触发页分裂
- 当某一页的数据满时,如果发生新插入,会触发页分裂将一页分裂为两页;同时,后一列的聚簇索引必然大于前一页
- 当数据稀疏时、或者页分裂的过程导致数据不连续时,对性能影响很大
- 非聚簇索引(二级索引)
- 非聚簇索引需要两次索引查找(第一次查询主键ID,第二次根据主键ID查询对应的数据条目);使用自适应性Hash可以减少索引查询的次数
为什么聚簇索引的设计应该尽量避免随机?
- 假设聚簇索引是有序的,则对于数据的记录,当前数据条目应该追加在前一个条目的尾部
- 如果聚簇索引的设计是随机的,那么会存在以下问题:
- 如果当前的索引的写入目标页已经不在内存,此时会将磁盘数据载入内存,增加随机I/O,使性能降低
- 由于索引的随机性,会导致引擎进行大量的重排序,进而导致频繁的页分裂
- 大量的页分裂,会使业内碎片增加,使数据页碎片化严重,数据访问的性能降低
- 覆盖索引
- 如果一个索引包含了所需要查询的所有字段,则称该索引为覆盖索引;(换而言之,只扫描一个B+Tree树就能获得结果)
- 覆盖索引的优势
- 索引所占用的空间比数据行空间小;因此如果只读索引,则会极大减少数据访问
- 索引是依据一定顺序构建的,因此一般比随机访问的区间小很多;
- 如果说非聚簇索引能够覆盖查询,则能够避免对于主键索引的二次查询
