Mysql索引:
索引根据什么来创建?
有三种情况,第一是,where子句后面的字段来创建
第二种情况根据多表都涉及到的字段进行创建,也就是外键的情况
第三种情况,经常出现在order by 或者group by子句后的字段也可以考虑使用索引
索引是在存储引擎实现的?
不同的存储引擎使用到的索引是不同的,像mysql默认就使用了innodb,那么索引就是b+树.
在mysql当中,每创建一个索引,那么就就会创建b+树,然后索引细分,又可分为主键索引和非主键索引,在主键索引的b+树中,key存放的是主键的值,而value存放的是除了主键外的所有其他的行数据。而非主键索引呢,key存放的是索引列,而value存放的是对应的主键值。
那么他们到底有什么区别呢,假如你是按照主键进行查询的话,那么就会使用主键索引,也就是查询主键对应的b+树即可。而如果是非主键索引,那么就先找到非主键索引对应的那棵树,找到对应的主键值,然后在根据主键值去查询主键索引对应的那个b+树,相当于查询了两遍b+树,然后我们把这个过程称为回表
如果数据库是innodb的话,那么使用索引,默认是B+tree类型。
什么是b树,为什么要使用他们,因为mysql的数据都是存储在磁盘当中,当我们要到磁盘进行相应的读取和写入数据的话,如果数据量比较大的情况下,频繁的访问磁盘进行读取会消耗比较多的时间,那么基于此,b树跟b+树就是用来做优化的,它能够高效的访问到磁盘当中的数据,因为他们允许一个节点有多个子结点,而不像红黑树或者其他二叉树那样,一个节点最多有两个节点,正因为如此,就保证了,在相同的节点个数的情况下,使用b+树木或者b树,树的高度就比较低,也就减少了磁盘的访问次数,磁盘i/o的时间也就比较短。这是他们存在的理由。
b树的机构:
1.某一个节点最多可以包含m个子结点,最少是一个。
2.子节点的关键字数量大于等于ceil(m/2)-1小于等于m-1
3.所有的叶子节点都在同一层。
b树的插入删除查询的详解:
1.先说查询把,先把要查询的元素与根元素进行比较,如果比根元素小,那么往左边查询,如果比根元素大,那么往右边查询,然后判定一下,这个要查询的元素,在对应的左边或者右边的关键字里面处于哪一个位置,如果处于中间位置,那么就往关键字的中间位置去查找,如果在关键字的左右两边,那么就对应的往左右两边进行查找,直到找到为止。
2.再说说插入,插入涉及到一个拆分的问题,也就是说假如一个树,它允许的最大的子结点的个数为5,那么就说明,一个节点,它能够放的关键字的个数最大就是4个,基于这个规则再去走下面的逻辑。
把那个元素放进去之后,此时,刚好五个元素,那么找到中间的那个元素,把它作为父节点,然后左边两个元素构成一个节点,右边两个元素构成一个节点。
3.再说说删除的逻辑把,首先呢需要知道这样一个规则,也就是说一个b树,假如它是一个5路查找树,那么它必须要求每一个节点的关键字的个数至少为ceil(m/2)-1,ceil代表向上取整。
然后如果删除的过程当中,发现某个节点的关键字小于了上面计算的这个值,那么就需要进行一个合并的操作,首先这个节点会往它的儿子那里拿,要么取左节点最大的那个元素,要么取右边最小的那个元素,然后如果说,我要删除的这个节点,它没有儿子节点,那么就需要往它的父节点那里拿(取父节点关键字的中间的那个)。
有可能从父节点那里拿了之后,父节点就不满足要求了,那么就会看看与父节点同级的兄弟节点有没有多余的关键字(也就是超出最低要求个数的关键字),如果有,那么就按照相同的逻辑,父节点从它的父节点那里借一个元素,然后兄弟节点的某一个元素往上移动(左兄弟的话,就移动最右边的那个元素,右兄弟的话,就移动最左边的那个元素)。当然有可能还有一种情况,就是发现父节点的兄弟节点并没有饱和,而是刚刚好,那么就不能借了,那么就要涉及到合并的操作(也就是说父节点所在的节点跟父节点的兄弟节点进行合并)。
B+树,非叶子节点,只存储key,不存储数据,叶子节点存储所有的key和value,所以当我们查询数据的时候,直接到叶子节点进行遍历即可,而不像b树那样需要遍历整个树来取得数据。而且b+树支持范围查询,因为叶子节点与叶子节点之间通过指针连接起来了。
普通索引(一个索引只包含单个列,表里面可以有多个单列索引)
唯一索引(索引列的值必须唯一,但是允许为空)
多列索引(一个索引包含多个列的情况)
主键索引(每一个表都有一个主键的话,那么就默认有一个主键索引)
全文索引
覆盖索引:要查询的字段,刚好存在于你的字段里面。
建立索引的原则:
最左匹配原则,mysql在建立索引的时候,需要考虑到这个原则,那么具体是什么意思呢?
也就是说假如我现在建立一个多列索引(a,b,c,d),有一个原则需要遵守,mysql会向右匹配,直到遇到了(>,<,between,like)的话,就会停止匹配,也就是说假如我现在有一个查询语句,select * from user where a=1 and b=2 and c>3 and d=4,那么d就用不到索引,因为mysql往右边扫描的过程当中,碰到了>这个符号。
尽量扩展索引,不要建立索引,
索引列最好不要参与函数的运算,不然的话,使用不到索引。