索引:是帮助MySQL高效获取数据的排好序的数据结构。
mysql 索引的底层数据结构:
- 二叉树:如果是规律性数据,比如1,2,3.....等数据,存储容易成线性结构,数据规模太大之后,查询太慢。
- 红黑树(hashap的底层数据结构):存在自平衡性问题,虽然不会出现单边增长,但是在数据量太大的时候,数据树的高度是不可控的,向下检索很慢。
- hash:通过hashcode通过位置指针多次定位,某些情况下也是很快的。但是没法实现范围检索,比如id>10,这个条件检索就没法使用hasdcode来查询。
- B-Tree:在红黑树的基础上,每个节点可以存储多个数据。横向增加数据个数,这样,就从解决了纵向数据树太高问题。B-Tree有个很重要的属性度(degree):每个节点最多可以存储多少个数据。比如定义一个B-Tree的某个节点的度为4。最大存储容量是15/16。有人会说,那么我们直接定义度=数据个数不就可以了么,这样就只有一层,不就更快了么。这里有要说到cpu的I/O操作了,java程序操作数据的过程:java程序 》 cpu 》内存(如果内存中没有数据)》磁盘。而内存与磁盘数据交互的时候是有限制的,一般单位是“页”,一页=4k。也就是说一个节点最多能存储4k的数据,如果太多的话I/O操作还是会变慢。我们看到B-Tree每个节点都存储的是key+data的形式,有时候数据库表的一行数据会有很多列,就是说data会大,这样会不会影响到B-Tree的效率呢。这是有人提出能不能把data去掉,所以就有了B-Tree的升级版B+Tree。
- B+Tree:在B-Tree的基础上在非叶子节点上只存放key这样就大大节省了空间。而叶子节点是存放key+data,并且key是按照从左自右递增的链表形式,通过next指正就可以很快的找到需要的数据。
mysql的存储引擎又分为MyISAM和innodb :
MyISAM:在mysql的安装目录data下,可以看到主要文件有1、表结构文件。2、数据结构文件。3、index索引文件。也就是说MyISAM的数据结构和索引结构文件是分开的(非聚集)。
MyISAM索引中,叶子节点存储的data是文件地址指针, 而数据结构中也是有一个地址的。也就是在索引出来文件地址之后,还需要通过文件地址在从数据结构中尽心一次索引,也就是要进行两次搜索。
InnoDB:在mysql的安装目录data下,只有两个文件,一个是表结构文件,一个是数据结构和索引文件。(聚集)
InnoDB的主键和非主键索引的B+Tree结构是不一样的。
主键:非叶子节点存储的是主键id,叶子节点存储的是每一行的数据。
非主键:非叶子节点存储的我们自己所设置的索引键,如果不是整数,就按ASC码排序,叶子节点存储的data是主键id。
InnoDB的主键id为什么要设置成自增整数,而不用UUID/UNID。原因是因为自增主键在非叶子节点添加的时候,总是往右新增就可以了,方便插入。如果用UUID/UNID作为主键,非叶子节点插入数据是还需要开辟新的页。影响效率。
mysql 联合索引:数据库中的多个列组成一个索引。但是要注意:
1、需要加索引的字段,要在where条件中
2、数据量少的字段不需要加索引
3、如果where条件中是OR关系,加索引不起作用
4、符合最左原则:联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。
结论:MyISAM的查询速度相比较InnoDB的查询速度要慢,因为MyISAM是进行了两次查询。