旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

mysql innodb索引道理的细致引见(代码示例)【MySQL教程】,索引,存储过程,查询优化,数据库,mysql

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:44评论:0


导读:本篇文章给人人带来的内容是关于mysqlinnodb索引道理的细致引见(代码示例),有肯定的参考价值,有须要的朋侪能够参考一下,愿望对你有所协助。群集索引(clustered...

本篇文章给人人带来的内容是关于mysql innodb索引道理的细致引见(代码示例),有肯定的参考价值,有须要的朋侪能够参考一下,愿望对你有所协助。

群集索引(clustered index)

innodb存储引擎表是索引组织表,表中数据依据主键递次寄存。其群集索引就是依据每张表的主键递次组织一颗B+树,其叶子结点中寄存的就是整张表的行纪录数据,这些叶子节点成为数据页。(相干引荐:MySQL教程)

群集索引的存储并非物理上一连的,而是逻辑上一连的,叶子结点间依据主键递次排序,经由过程双向链表衔接。多半情况下,查询优化器倾向于采纳群集索引,因为群集索引能在叶子结点直接找到数据,而且因为定义了数据的逻辑递次,能迥殊快的接见针对局限值的查询。

群集索引的这个特征决议了索引组织表中的数据也是索引的一部分。因为内外的数据只能依据一颗B+树排序,因而一张表只能有一个聚簇索引。

在Innodb中,聚簇索引默许就是主键索引。假如没有主键,则依据以下划定规矩来建聚簇索引:

  • 没有主键时,会用一个非空而且唯一的索引列做为主键,成为此表的聚簇索引;
  • 假如没有如许的索引,InnoDB会隐式定义一个主键来作为聚簇索引。

因为主键运用了聚簇索引,假如主键是自增id,那末对应的数据也会相邻地寄存在磁盘上,写入机能较高。假如是uuid等字符串情势,频仍的插进去会使innodb频仍地挪动磁盘块,写入机能就比较低了。

B+树(多路均衡查找树)

我们知道了innodb引擎索引运用了B+树构造,那末为何不是其他范例树构造,比方二叉树呢?

计算机在存储数据的时刻,有最小存储单位,这就比如人民币流畅最小单位是分一样。文件体系的最小单位是块,一个块的大小是4k(这个值依据体系差别而且可设置),InnoDB存储引擎也有本身的最小贮存单位—页(Page),一个页的大小是16K(这个值也是可设置的)。

文件体系中一个文件大小只要1个字节,但不得不占磁盘上4KB的空间。同理,innodb的一切数据文件的大小一直都是16384(16k)的整数倍。

所以在MySQL中,寄存索引的一个块节点占16k,mysql每次IO操纵会应用体系的预读才能一次加载16K。如许,假如这一个节点只放1个索引值是异常糟蹋的,因为一次IO只能猎取一个索引值,所以不能运用二叉树。

B+树是多路查找树,一个节点能放n个值,n = 16K / 每一个索引值的大小。
比方索引字段大小1Kb,这时刻每一个节点能放的索引值理论上是16个,这类情况下,二叉树一次IO只能加载一个索引值,而B+树则能加载16个。

B+树的路数为n+1,n是每一个节点存在的值数目,比方每一个节点寄存16个值,那末这棵树就是17路。

从这里也能看出,B+树节点可存储多个值,所以B+树索引并不能找到一个给定键值的详细行。B+树只能找到寄存数据行的详细页,然后把页读入到内存中,再在内存中查找指定的数据。

附:B树和B+树的区分在于,B+树的非叶子结点只包括导航信息,不包括现实的值,一切的叶子结点和相连的节点运用链表相连,便于区间查找和遍历。

辅佐索引

也称为非群集索引,其叶子节点不包括行纪录的悉数数据,叶子结点除了包括键值之外,每一个叶子结点中的索引行还包括一个书签,该书签就是响应行的群集索引键。

以下图能够示意辅佐索引和群集索引的关联(图片源自收集,看也许意义即可):

当经由过程辅佐索引来寻觅数据时,innodb存储引擎会经由过程辅佐索引叶子节点取得只想主键索引的主键,既然后再经由过程主键索引找到完全的行纪录。

比方在一棵高度为3的辅佐索引树中查找数据,那须要对这颗辅佐索引树举行3次IO找到指定主键,假如群集索引树的高度一样为3,那末还须要对群集索引树举行3次查找,终究找到一个完全的行数据地点的页,因而一共须要6次IO接见来获得终究的数据页。

竖立的索引,如团结索引、唯一索引等,都属于非聚簇索引。

团结索引

团结索引是指对表上的多个列举行索引。团结索引也是一颗B+树,差别的是团结索引的键值数目不是1,而是大于即是2。

比方有user表,字段为id,age,name,现发明以下两条sql运用频次最多:

Select * from user where age = ? ;
Select * from user where age = ? and name = ?;

这时刻不须要为age和name零丁建两个索引,只须要建以下一个团结索引即可:

create index idx_age_name on user(age, name)

团结索引的另一个优点已对第二个键值举行了排序处置惩罚,有时刻能够防止多一次的排序操纵。

掩盖索引

掩盖索引,即从辅佐索引中就可以够获得查询所须要的一切字段值,而不须要查询群集索引中的纪录。掩盖索引的优点是辅佐索引不包括整行纪录的一切信息,故其大小要远小于群集索引,因而能够削减大批的IO操纵。

比方上面有团结索引(age,name),假如以下:

select age,name from user where age=?

就可以运用掩盖索引了。

掩盖索引的另一个优点是关于统计题目,比方:

select count(*) from user

innodb存储引擎并不会挑选经由过程查询群集索引来举行统计。因为user表上另有辅佐索引,而辅佐索引远小于群集索引,挑选辅佐索引能够削减IO操纵。

注意事项

  • 索引只建适宜的,不建过剩的
因为每当增删数据时,B+树都要举行调解,假如竖立多个索引,多个B+树都要举行调解,而树越多、构造越巨大,这个调解越是耗时耗资本。假如削减了这些不必要的索引,磁盘的运用率能够会大大下降。
  • 索引列的数据长度能少则少。

索引数据长度越小,每一个块中存储的索引数目越多,一次IO猎取的值更多。

  • 婚配列前缀可用到索引 like 9999%,like %9999%、like %9999用不到索引;
  • Where 前提中in和or能够运用索引, not in 和 <>操纵没法运用索引;

假如是not in或<>,面临B+树,引擎基础不知道应当从哪一个节点入手。

  • 婚配局限值,order by 也可用到索引;
  • 多用指定列查询,只返回本身想到的数据列,罕用select *;

不须要查询无用字段,而且不运用*能够还会掷中掩盖索引哦;

  • 团结索引中假如不是依据索引最左列开始查找,没法运用索引;

最左婚配准绳;

  • 团结索引中准确婚配最左前线并局限婚配别的一列能够用到索引;
  • 团结索引中假如查询中有某个列的局限查询,则其右侧的一切列都没法运用索

以上就是mysql innodb索引道理的细致引见(代码示例)的细致内容,更多请关注ki4网别的相干文章!

标签:索引存储过程查询优化数据库mysql


欢迎 发表评论: