菜单

mysql索引 b+树

2019年3月21日 - MySQL

壹 、B+树基本概念

原因就是为了减少磁盘io次数,因为b+树所有最终的子节点都能在叶子节点里找见,
所以非叶子节点只需要存`索引范围和指向下一级索引(或者叶子节点)的地址` 就行了,
不需要存整行的数据,所以占用空间非常小,直到找到叶子节点才加载进来整行的数据。

B树非叶子节点也会存数据,所以不适合mysql(以后研究下mongo为啥用b树 再补充)

  B+树的言语定义比较复杂,一句话来说是为磁盘存取设计的平衡二叉树

B+树适合当作数据库的底蕴结构,完全是因为总结机的内部存款和储蓄器-机械硬盘两层存款和储蓄结构。内部存款和储蓄器能够形成快捷的自由访问(随机走访即给出任意1个地点,供给回到那几个地址存款和储蓄的数额)然则体积较小。而硬盘的即兴走访要经过机械动作(1磁头移动
2盘片转动),访问作用比内部存款和储蓄器低多少个数据级,可是硬盘容积较大。典型的数据水库蓄水容量量大大超过可用内部存款和储蓄器大小,那就决定了在B+树中搜寻一条数据很只怕要依靠一遍磁盘IO操作来成功。如下图所示:平时向下读取1个节点的动作恐怕会是一遍磁盘IO操作,不过非叶节点日常会在上马阶段载入内部存款和储蓄器以加快访问速度。同时为狠抓在节点间横向遍历速度,真实数据库中大概会将图铁蓝灰的CPU总结/内部存款和储蓄器读取优化成二叉搜索树(InnoDB中的page
directory机制)。

图片 1

图片 2

  网上经典图,古铜黑p1 p2
p3代表指针,杏黄的象征磁盘,里面富含数据项,第3层17,35,p1就意味着小于17的,p2就表示17-35里头的,p3就象征大于35的,可是需求留意的是,第一层才是开诚布公的数目,1七 、35都不是潜心贯注数据,只是用来划分数据的!

image

二 、为啥使用B+树

开诚相见数据库中的B+树应该是那么些扁平的,能够透过向表中各种插入丰裕数量的法子来验证InnoDB中的B+树到底有多扁平。大家通过如下图的CREATE语句建立二个唯有大致字段的测试表,然后不断丰硕数据来填充那一个表。通过下图的统计数据(来源见参考文献1)能够分析出多少个直观的下结论,那多少个结论宏观的变现了数据库里B+树的标准。

  B+树有啥样便宜大家非要使用它吧?那就先要来探视mysql的目录

1
每种叶子节点存款和储蓄了468行数据,每一个非叶子节点存储了大体上1200个键值,那是一棵平衡的1200路寻找树!

 

2
对于1个22.1G体积的表,也只供给中度为3的B+树就能储存了,那一个容积大约能满意众多采用的急需了。尽管把中度叠加到4,则B+树的仓储容量霎时增大到25.9T之巨!

  2.1mysql索引

3
对于二个22.1G容量的表,B+树的万丈是3,假使要把非叶节点全体加载到内部存款和储蓄器也只供给不难18.8M的内部存款和储蓄器(怎么样得出的那个结论?因为对于高度为2的树,120一个叶子节点也只需求18.8M空中,而22.1G从良表的可观是3,非叶节点120几个。同时我们假若叶子节点的尺寸是高于非叶节点的,因为叶子节点存款和储蓄了行数据而非叶节点唯有键和少量数量。),只利用那样少的内部存款和储蓄器就足以确定保证只要求三次磁盘IO操作就招来出所需的数码,功效是相当之高的。

    试想一下在mysql中有200万条数据,在没有树立目录的情事下,会整整进展围观读取,那个时间费用是尤其害怕的,而对此大型一点的网站来说,达到那么些数据量很不难,不容许那样去设计

图片 3

    在大家创建数量库表的时候,大家都明白三个事物叫做主键,一般来讲数据库会自动在主键上创设索引,那称为主键索引,来探视索引的分类吧

image

    a.主键索引:int优于varchar

    b.普通索引(INDEX):最核心的目录,没有限制,加快查找

    c.唯一索引(UNUQUE):听名字就领悟,供给全体类的值是绝无仅有的,不过允许有空值

    d.组合索引:

1 CREATE INDEX name_age_address_Index ON `student`(`name`, `age`, `address`);

    在此地实在包括多少个目录,说到组合索引,一定要讲最左前缀原则

 


    最左前缀原则:

      我们以往创制了索引x,y,z,Index:(x,y,z),只会走x,xy,xyz的查询,例如:

1 select * from table where x='1'
2 select * from table where x='1' and b='1'
3 select * from table where x='1' and b='1' and c='1'

      假设是x,z,就只会走x,注意一种奇特别情报形,select * from table
where x=’1′ and y>’1′ and
z=’1’,那里只会走xy,因为在经历xy的筛选后,z不可能保障是板上钉钉的,可索引是铁板钉钉的,因而不会走z


 

    e.全文索引(FULLTEXT):用于搜索内容十分长的小说之类的很好用,假使创立普通的目录,在碰着like=’%xxx%’那种意况索引会失效

1 ALTER TABLE tablename ADD FULLTEXT(col1, col2)
2 SLECT * FROM tablename WHERE MATCH(col1, col2) AGAINST(‘x′, ‘y′, ‘z′)

    那样就能够将col1和col2里面富含x,y,z的记录整个取出来了

    

    索引的删减:DO大切诺基P INDEX IndexName ON `TableName`

  

    索引的得失:

      ① 、在数据量尤其巨大的时候,建立目录有助于大家加强查询效能

      贰 、在操作表的时候,维护索引会扩张额外开支

      三 、不泛滥使用索引,创造多了目录文件会暴涨十分的快

 

  2.2B+树的长处

    打探上边的模子后,试想一下,200W条数据,如果没有树立目录,会全体开始展览扫描,B+树仅仅用三层构造能够象征上百万的数额,只必要一回I/O!这提高是真的皇皇啊!

    因为B+树是平衡二叉树,在时时刻刻的加码多少的时候,为了保证平衡也许要求做大批量的拆分操作,因而提供了旋转的效益,不知道旋转提议去补一下树的基础知识

    B+树插入动画(来自https://www.cnblogs.com/vincently/p/4526560.html)

图片 4

三 、索引优化

  壹 、最好左前缀原则

  ② 、不要在目录的列上做操作

  三 、like会使索引失效变成全表扫描

  四 、字符串不加单引号会促成索引败北

  五 、减少使用select *

图片 5

  参照那里,写的很好 
 https://www.cnblogs.com/zhaobingqing/p/7071331.html

 

总结:

  sql语句怎么用,没有明确必须怎么查,对于数据量小,有时候不须求新创建目录,依据早晚的莫过于情状来考虑

    

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图