Mysql索引原理及索引优化
sql语句的实质行为就是从磁盘上的文件中读取数据,数据在磁盘上存储是随机IO,效率较低,所以我们需要“索引”B+tree
数据存储时是以页为单位存储的
为什么不用HashTable、二叉树?
HashTable的存储原理是:HashTable的底层也是数组,根据一定算法将key转化成下标,存储至数组中,查询是直接根据key算出下标从数据中拿到数据,K-V的结果不适合存储复杂数据结构的数据,比如一条数据中有多个字段需要存储,另外HashTable的存储顺序是随机的,不能查询大于、小于,只适合精确查找。
完全对称二叉树为什么不合适?
因为二叉树存储是有序的,完全对称二叉树可以做大于、小于的运算,但是在查找过程中要回去查找父节点中储存的元素,不利于检索
B+tree
B+tree也是有序的,在存储过程中是将所有非叶子节点的数据冗余一份到叶子节点中,在查找过程中只关注同级节点就可以了,所以B+tree的有点就在于区间查询,另外B+tree中一个节点存储了多个元素,有助于节省磁盘IO次数。
聚合索引:新建表是默认分配1页资源,索引与数据存在一起,插入数据达到本页最大限制时(默认16k),将第一页复制一份,并开辟一页新的资源,原第一页修改为目录页,以此类推,B+tree形成 ,建表时的第一页目录通常会被缓存,数据量越大,缓存越多。所以建表时务必指定主键索引,如果没有指定,也没有建其他唯一索引,mysql会自动补全自增隐藏列row_id为主键索引,但此列不可见,不支持查询,作用很小。
那可能有人有这样的疑问,如果我的数据量很大,是不是树就会层级很高,检索起来会不会很慢,那我们简单算一下:
叶子节点:数据 16k/1k(单条数据1k举例)
非叶子节点:主键id+指针
一个主键id我们认为它占用8b空间,指针源码上写的占6b空间,根节点缓存到内存里,我们可以理解为树高为2级可以存储的索引数据量为:
16k/14b*16=18724左右
树高为3级时
1170*1170*16=21907740左右
所以建议mysql分表存储的参考数据就是2000w左右,因为树高在临近这个临界点会增加,检索效率会直线下降,单条数据容量可能有区别,数值为近似量。
主键索引建议采用小内存、自增形式:
数据是累积到一定数值后分页的,但是如果非自增,随着数据增长,各数据页、目录页中数据会有补全、变动,那么会增加IO、降低效率
辅助索引:
辅助索引(非聚合索引)存储中存储了索引列和主键的对应关系,根据索引列查到主键后再去根据主键查询数据,由此可见主键索引的重要性,辅助索引其实就是针对该列单独进行排序,便于查找。
那么联合索引是怎么工作的呢?其实同理,就是两列内容进行一个排序,便于查询,最左后缀原则,如果单独查询组合索引的第二、三列索引效果近乎没有,因为没有优先按照右侧列排序,同理 like XX%是可以走索引的,%XX则不行。
InnoDB支持事务