索引科普
1.索引是存储在磁盘中的,不是存储在内存中的,索引一定会持久化磁盘
2.我们的电脑持久化存储采用磁盘,磁盘有磁道,磁道有磁头,磁头的移动是进行读取的过程,磁头移动快慢就是读取的快慢
3.由1968年P.Denning研究发现,对程序执行有以下特性
- 局部性原理
程序和数据访问都有聚集成群的倾向,在一个时间段内仅使用其中一小部分(空间局部性)
最近访问的程序代码和数据,再次访问可能性很大(时间局部性)
- 磁盘预读(为页(page)的整数倍)
页是存储器的逻辑块,操作系统往往将主存和磁盘存储分割为连续的块,这个块通常为固定大小
1968年P.Denning研究了程序执行的局部性(principle of locality),对于程序局部性原理进行研究还有Knuth(分析的一组Fortran程序)、Tanenbaum(分析操作系统的过程)、Huck(分析通用科学计算的程序)。
4.什么是索引?
预先知识
mysql整体架构
-
索引是帮助MySQL的进行优化的,在架构图的优化器中
-
存储引擎:不同的存放位置,不同的文件格式
InnoDB: 磁盘 MyISAM:磁盘 memory:内存
MyISAM:默认表类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。不是事务安全的,而且不支持外键,如果执行大量的select,insert MyISAM比较适合。
InnoDB:支持事务安全的引擎,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。
聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据 InnoDB
非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行 MyISAM
聚簇索引
- .frm 存放表结构
- .ibd 存放数据文件和索引文件(在innodb下通常看不到这个文件,被整合到了ibdata1)
- .opt 是存数据库信息
我们使用innodb存储,所有都通过表空间进行存储,创建表必然生成.frm和.ibd文件,
设置不整合到ibdata1,使用分文件存储
set global innodb_file_per_table=on;
非聚簇索引
索引和数据单独一个文件:MyISAM
-
.frm:存放表结构
-
.MYI 存放索引数据
-
.MYD 存放实际数据
什么是索引:
-
索引是帮助MySQL高效获取数据的数据结构
-
索引存储在文件系统中
-
索引的文件存储形式和存储引擎有关
-
索引的文件结构
- hash
哈希表可以完成索引存储,每次在添加索引要计算指定的hash值,取模运算计算下标
适合场景:
等值查询
无序查询
hash表在使用过程中,需要将数据全部加载到内存中,比较耗费内存,不是很合适
- 树
多叉树->二叉树->avl树->红黑树->B-树->B+树
红黑树是基于avl树的,损失部分查询性能,提升插入性能,在红黑树中最低子树和最高子树之差小于2倍即可,在插入过程中,不需要进行N次旋转,而且加入变色特性,来满足插入和查询性能的平衡
B树:
1.所有键值分布在整棵树中
2.搜索可能在非叶子结点结束,在关键字全集内做一次查找性能逼近二分查找
3.每个节点最多有m个子树
4.根节点至少2个子树
5.分支节点至少m/2个子树
6.所有叶子节点都在同一层,每个节点最多有m-1个key,都以升序排列
B+树:
解决B树深度过深问题。
注意:
1.InnoDB B+Tree 叶子节点直接放置数据,如果没有主键,那么会选择唯一键,如果没有唯一键,那么会生成一个6位的row_id作为主键
2.如果创建的索引键是其他字段,那么叶子节点中存储的是主键,再通过主键进行找到相应记录,叫做回表
5.索引的分类
mysql索引的5种类型:主键索引、唯一索引、普通索引、全文索引、组合索引
- 主键索引
主键是一种唯一索引,但它必须指定为PRIMARY KEY,每个表只有一个主键
- 唯一索引
索引列值只出现一次,必须唯一,值可以空(回表,不覆盖)
- 普通索引(建议这个,不用2次)
基本索引,值可以空,没有唯一性限制(回表,会覆盖索引)
- 全文索引,MyISAM支持,Innodb在5.6之后支持(一般不会用到,公司在做搜索引擎,一般使用es、solr、Lucene)目前Lucene不用了,solr在老牌公司用,更多选择es
全文索引类型是FULLTEXT,全文索引可以在varchar、char、text类型的列上创建
- 组合索引
多列组合成一个索引
最左匹配,先去匹配左边的
一个查name,age,另一个查age
可以优化为age,name的索引
是否回表 | 是否覆盖 | |
---|---|---|
主键索引 | 没有回表 | 不覆盖 |
唯一索引 | 没有回表 | 不覆盖 |
普通索引 | 回表 | 覆盖 |
执行计划:explain select * from xxx where xxx=xxx
6.MyISAM和InnoDB区别
MyISAM不支持事务,行锁,外键,适合大量select操作