一、什么是索引
我们知道,mysql数据库中表的数据都是存在磁盘上的,而我们在平常进行sql查询时,通常都是读取磁盘上的数据到内存中。在这个过程中最耗时的操作就是磁盘I/O。所谓的mysql优化 查询优化等最本质的优化原则就是减少磁盘I/O次数,从而提高数据库查询的效率。而索引就是帮助我们解决这个问题的一种方式,什么是索引?
索引就是帮助Mysql高效获取磁盘数据的一种排好序的数据结构。
既然索引是一种数据结构,那么到底什么样的数据结构可以作为索引呢?一般常用的数据结构有一下几种:
- 二叉树
- 红黑树(平衡二叉树)
- Hash
- B-Tree
- B+Tree(本文重点)
下面依次说明这几种数据结构是否适合作为Mysql索引。
二叉树:这种数据结构不做过多的详细说明(不清楚的可以从网上查一下),就是右边节点的数据大于左边接点的数据的顺序。但它并不适合作为Mysql的索引,原因如下。
1.数据按照顺序插入时,二叉树就演化成了一个链表,链表的查询效率并不高。
2.当数据量达到几百万甚至几千万时,树的高度会过高,从而遍历的次数会增多,而每次遍历一个节点就是一次磁盘I/O.
红黑树:这种数据结构又叫平衡二叉树,也是一种二叉树,不过它解决了二叉树的一个缺点,在顺序插入时,它可以通过左旋或者右旋的方式避免演化成链表。但是红黑树也不是Mysql数据索引的合适选择,原因如下。
1.数量过多时,树高不可控,会变的很高甚至达到几千几万的高度,查询效率会很低。
2.左旋和右旋操作还是比较耗时操作的。
Hash:一般是数组+链表结合的一种结构,通过hash(index)计算定位数据在数组中的位置,这种数据结构在某些场景是可以作为Mysql的索引的,而Mysql也确实将hash作为索引的一个选项,不过一般不常用,工作原理:通过计算索引字段的值,定位到数组上的位置,并保存。
优点:
1.只需要一次hash计算就能定位到位置。
2.某些场景(没有或者很少的hash冲突,且仅“=”和“in操作时”)效率比索引树要高,只需一次hash计算就可以了 无论是查询数据还是插入索引数据都很快
缺点:
1.受数据结构的影响,这种索引结构只支持“=”和“in”操作,不支持like,范围查找等操作。
2.需要解决hash冲突(一般是链表法)
总结:因为hash这种数据结构的约束性太大,一般在工作中不太常用。
B-Tree:这种数据结构也是一种树形结构,而Mysql的常用索引结构B+Tree(我们本文研究的重点结构)是B-Tree的一种优化。先来认识一下这种数据结构
特点:
1.叶子节点具有相同的深度,且叶子节点的指针为空
2.所有节点的元素不重复
3.节点从左到右依次递增且每个节点存储索引值和data数据
因为这种结构在单个节点上可以存储多个索引元素,从而可以避免像二叉树那样树高过高的情况,因此比较适合Mysql的数据库索引,当然Mysql对这中数据结构进行了一种优化 :B+Tree。
二、B+Tree索引结构
B+Tree是B-Tree的演进,作为Mysql中最常用的一种数据结构,它有如下特点,先看图:
特点:
1.非叶子节点不存储data数据,只存储索引的冗余,可以存放更多的索引
2.从左到右依次递增
3.叶子节点包含所有的索引元素,且存储data数据
4.叶子节点使用指针连接,这使得进行范围查找时提高查找效率。
与B-Tree的不同点:
1.B-Tree每个节点都会保存data数据,B+Tree只在叶子节点保存data数据
2.B-Tree叶子节点之间并没有指针连接,范围查询是需要多次遍历索引树。B+Tree叶子节点之间有指针连接 范围查询时不用多次遍历索引树,只需要获取右边节点数据即可。
3.B-Tree所有节点元素不重复,B+Tree子节点都有父节点元素的冗余。
针对于这种B+Tree索引结构,Mysql有两种实现方式(引擎):MyISAM和InnoDB。
1.首先我们要知道两种存储引擎都是形容表的
2.MyISAM实现的索引是非聚集索引
3.InnoDB引擎实现的索引是聚集索引(Mysql比较常用的存储引擎)
注意:聚集索引是索引和表数据存储在一起的索引,非聚集索引就是索引和数据不存储在一起的索引。
MyISAM引擎实现的索引(非聚集索引):
例如:我有一个表 t_test_myisam 使用MyISAM引擎
那么它在磁盘上存储的结构是怎么样的?
它会生成三个文件:
.frm:是记录表结构的文件
.MYI:就是我们创建的索引的存储文件
.MYD:存储表中的数据,索引文件中保存的地址指针就是指向此文件中的数据。
因此MyISAM实现的是一个非聚集索引 大概情况如下:
叶子节点存储的data不是表中的其他列的值,而是指向索引所在行的磁盘地址。
InnoDB引擎实现的索引
例如表t_test_innodb使用InnoDB引擎
那么它在磁盘上存储的结构如下:
.frm:是记录表结构的文件
.ibd:保存的是索引和表数据,是一个使用B+Tree组织的索引和表数据文件
特点:
1.主键索引的叶子节点存储的是索引所在行的其他列字段的值(是一个聚集索引)
2. 二级索引的叶子节点存储的是主键值,然后根据主键值查询主键索引树这就是回表。
3.表数据本事就是使用B+Tree组织的一个索引+数据的索引文件(.ibd文件)
4.叶子节点包含了所有的索引和表数据的值
针对以上索引结构做以下说明:
1.在建表时 必须制定主键。因为你不指定主键的话,mysql会从其它值不重复的字段中找一个作为主键,如果值都有重复的,则会帮忙建立一个隐藏的主键,为了提高效率 我们不应该把这件事麻烦mysql。应该自己完成。
2.主键最好是整形的。因为整形的数据在索引树中,比较大小时速度很快,如果是varchar类型的还要转化为ascall码进行比较,相比效率不高。
3.主键最好自增。因为在索引树中插入的数据,不是自增有序的话,比例插入节点中间,可能会导致节点的分裂和父节点的变化等,而自增的主键插入只需要在节点右面在插入索引元素即可 减少了节点的分裂处理。
4.为什么二级索引叶子节点保存的是主键的值?为什么不保存索引列所在行的其他列的值?
我的理解是这样的:
第一:如果二级索引也保存表的其他列数据,那么如果索引有多个 则就需要保存三分表数据 这比较占磁盘空间
第二: 在新插入数据记录时,需要维护多个索引树的叶子节点的数据,这样会产生数据不一致的风险,比如插入一天数据,维护主键索引的叶子节点后,还要去维护二级索引的叶子节点,其中要有一个因为服务等原因失败了 就会有数据不一致的风险。但是只需要维护一个主键索引一份 就不会有这个问题。
小知识:到此我们差不多把mysql的索引结构已经清晰的了解了,那么问题来了,一张表中存储多少数据合适?
我们知道索引是树形的,磁盘I/O次数等同于树的高度,那么假设我们树高是3的话。
一般数据库数据存储是以页为单位的,每页大小16k左右。
假设主键索引中主键使用Bigint类型占8B,指针占6B则总共占 8+6=14B
那么
联合索引: 假设有联合索引如下
KEY 'idx_name_age_position' ('name','age','position')USING BTREE
1.联合索引遵循最左列原则,根据联合索引的迅速 从左到右的一次进行排序,先根据name字段排序,name相同的在根据age字段排序 在name和age相同情况下在按照position排序
2.sql语句查询 也遵循最左列原则
where name="Bill" and age = "20" and position="dev"; 会使用联合索引进行查询
where name="Bill" and age = "20"; 会使用name和age联合索引中的两个索引字段
where age = "20" and position="dev"; 不会使用索引,因为在联合索引中name为确定的情况下,age和position的顺序是不确定的,无法使用索引进行定位。
where name="Bill" and position="dev"; 会使用联合索引中的name的索引字段,在没有确定age的情况下,position的顺序是不确定的 所以 无法使用position索引字段。
总结:
1.Mysql索引为B+Tree树形结构
2.MyISAM引擎实现非聚集索引,索引树中叶子节点存储的是索引所在行的磁盘地址;InnoDB引擎实现的是聚集索引(针对主键索引),叶子节点存储的是索引元素+表数据。二级索引叶子节点存储的是索引元素+主键值
3.数据查询效率优化===》减少磁盘I/O===>减少索引树的高度。