一、什么是索引?
索引是帮助Mysql高效的排列数据的一种数据结构
二、索引的数据结构
很多数据结构都可以实现索引:二叉树、红黑树、hash表、B-Tree、B+Tree等等。Mysql使用的是B+Tree数据结构。
为了降低树的高度,引入了B-Tree;为了提高索引的效率又引入了B+Tree。
为什么Mysql使用B+Tree当作索引呢?那么我们必须要了解B+Tree和B-Tree的数据结构的特点:
B-Tree:
1、叶子节点的深度都一样
2、数据和索引是存储在一起的
3、不同的值通过索引查询效率可能不一样
B+Tree:
1、所有的非叶子节点都是索引,数据全部存储在叶子节点上
2、不同的值通过索引查询的效率是一样的
3、所有的叶子节点形成一个双向链表,遍历数据时只需要从头遍历叶子节点即可
那么为什么选择B+Tree作为Mysql的索引呢?
1、B+Tree每次查询的效率都是一样的,便于优化
2、InnoDB是按页存储的,每页存储的大小为16kb。对于B+Tree来说非叶子节点都是索引,数据和索引是分开存储的,那么一页存储的索引个数就比B-Tree的多。索引个数多,那么查询的效率就更高
3、在进行数据遍历时也只需要遍历叶子节点就可以了,而B-Tree还需要进行遍历才可以
索引为什么就这么快呢?
我们知道,InnoDB存储引擎的页大小为16kb。加入我们存储一个id使用bigInt类型–>8byte,一个索引存储下一个索引的地址大概为6byte。那么一页可以存储16kb / (8+6) = 1170个索引。对于叶子节点,数据类型+data大概为1kb,那么一页可以存储16条数据。如果树的深度为3,那么可以存储的数据条数为:1170117016 = 21902400(千万级条)。如果把根节点和索引节点加载到内存当中,只需要一次I/O操作就可以得到目标数据,这是特别快的。这就是索引这么快的原因。减少了查询的个数。
Hash
对索引的key进行一次哈希运算就能得到数据存储的位置
很多时候Hash要比B+tree更高效
仅仅能满足“IN”、“=”的查询条件,其他的条件不能满足
hash冲突问题
例如:范围查找就不能实现。
MyISAM存储引擎的存储方式和查询数据的过程:
MyISAM是采用分区存储的方式维护表和表中的数据的
.frm表:存储表的结构的
.MYD表:存储表的数据的
.MYI表:存储表的索引信息的
非聚簇索引:MyISAM采用的是非聚簇索引的方式
MyISAM中查询一条记录的过程:
根据索引快速定位到叶子节点找那个存储的物理地址,这个物理地址就是对应数据在.MYD文件中的地址,然后根据物理地址到.MYD文件中快速找到数据。
无论是主键索引还是非主键索引,叶子节点记录的都是记录对应的物理的地址。通过物理地址再到.MYD文件中查询相对应的数据。
InnoDB的存储方式和查询过程
InnoDB是Mysql默认的存储引擎,它的索引和MyISAM还是有一点区别的。
InnoDB存储引擎维护表是靠两张表来维护的
.ibd文件:用于存储表的索引和数据
.frm文件:用于存储表的结构
索引的实现(聚簇索引):
对于InnoDB来说,数据的本身就是以B+Tree的形式来存储的。首先,InnoDB会根据设置的主键来构造B+Tree【如果没有设置主键则InnoDB会为这张表设置一个默认的主键root id用来存储数据】。所有的数据存储在叶子节点上面,这就是聚簇索引。
查询过程
当我们根据主键查询数据时,通过聚簇索引直接定位到叶子节点得到数据即可。
当我们使用辅助索引【非聚簇索引】时,根据索引定位到叶子节点,此时叶子节点中存储的是对应数据的id而不是数据信息。然后我们根据id再到聚簇索引中查询得到对应的数据。
我们思考几个问题,这也是面试中经常问的问题:
1、在InnoDB中,为什么强烈建议为每一张表设置一个主键?
InnoDB引擎,数据的存储本身就是以B+Tree的形式存储的。一开始InnoDB会根据主键生成一棵索引树。如果不设置主键的话,InnoDB会自动为我们生成一个默认的主键Root ID。但是Mysql的资源是很珍贵的,我们只希望它就做添加/查询的功能就可以了。所以我们手动设置id不要让Mysql有过多的资源消耗。
2、为什么推荐使用自增的整型主键?
首先整型的数据比较要比字符型啊什么的要快。再一个呢就是自增的主键,在维护索引的时候不会破坏原来已经达到平衡的节点。如果节点不够需要扩容的话只需要在后面追加一个节点再维护一下 节点的值就可以了,不去破坏已经达到平衡的节点。效率较高一点。
3、为什么非主键索引的叶子节点存储的是主键值?
两个原因:
1.节省空间:如果所有的索引的叶子节点都记录数据,那么会造成空间上的浪费。数据冗余
2.保持数据的一致性:对表进行写操作时,需要同时维护每张索引表的数据,可能会出现数据不一致的情况,那么如果辅助索引只存储id,那么只需要修改聚簇索引的数据信息即可。不会产生数据的不一致性。
联合索引的排序原理和底层存储结构?
对于联合主键。例如(id,age,gender)。首先会根据id进行排序,如果id相同就根据age进行排序,如果id和age都相同,最后才根据gender进行排序。
最左前缀原则:
面试题:为什么要有最左前缀原则?
对于联合索引,首先会根据最左边的索引字段进行排序,如果最左边的字段的值都相同再按照后一个索引字段进行排序,依次类推。索引在查询数据的时候也应该遵守最左前缀原则,否则无法走索引。
建立索引的原则:
1、索引不宜建太多,每个索引都对应着一颗索引树,Mysql需要维护这颗索引树。如果索引太多维护起来也相当麻烦。
2、尽量使用联合索引将多个需要建立索引的字段都创建在一个索引中
3、建立索引的字段的数据类型不宜过大。InnoDB的一页大小为16kb,如果索引字段数据类型太多,建立的索引个数就减少,那么索引的查询效率也就会降低
4、建立索引的字段的数据应该尽量的散列。例如性别字段,只有男和女两种数据,索引就无法发挥它的优势。所以自增的主键索引查询的效率就高就是这个原因
5、对于联合索引要满足最左前缀原则
6、在通过索引查询数据的时候尽量不适用一些函数或者in not in等,可能不会走索引
7、对于模糊查询:like “%xx”这种不会走索引