Mysql索引原理深入剖析

Mysql索引原理深入剖析

1.     索引是一种数据结构,能够提高数据的检索速度。

栗子:从如下数据中找出所有为2的数据:1,3,2,5,7,9,2,5,6?

无索引:由于数据是没有顺序的就只能通过顺序查找的方式一个一个的查找比对。

有索引:会先将数据排序,排序后为1,2,2,3,5,5,6,7,9,这个时候就不用顺序查找了,顺序查找效率也不高,这个时候我们就可以使用比较高效的二分法查找了,所以速度一定比顺序查找快。

2.     结合上面例子可以引出索引的特点:排好序,快速查找,数据结构(mysql里面的索引index_type有btree,hash等,这些都可以理解成不同的数据结构,如btree就和数据结构中二叉树非常类似)

3.     Btree数据结构特点:

由上图可以基本上了解到建立索引的好处了,就是查找快,以前要查询7次才能查询到对应的所有数据,现在只需要查找二叉树层数(n)对应次数就能查到数据,使用索引后只需要查询3次就能查取到结果。

4.     二叉树层级特点:

第一层:2^0=1

第二层:2^1=2

第三层:2^2=4

第n层:2^(n-1)

当n=32:2^31=21亿(无符号)

5.     Hash数据结构特点:

如Memory内存数据表存储引擎就是使用的hash索引,而myisan和innode也用到了hash索引,但是它们二个是伪hash,是自己实现。优点:查找高效,理论上面只需要查找一次。缺点:会产生地址冲突,如果地址冲突比较多的话,索引的维护就比较麻烦,代价很高。

6.     索引本身存储也是存放到文件中的,占据硬盘资源。

7.     Hash索引局限:

     1)hash索引只能用于等值比较,也就是范围查找用不上索引。

    等值比较包含=,<=>,in()。

    2)无法使用索引最左前缀列原则

    create index idx_id_name(id,name)

                 where id=1 这个时候是不走hash索引的。

8.     Mysql如何管理数据库文件的?

    .frm文件:存储数据表的框架结构,文件名与表名相同,每个表对应一个同名frm文件,与操作系统和存储引擎无关,即不管MySQL运行在何种操作系统上,使用何种存储引擎,都有这个文件。

    除了必有的.frm文件,根据MySQL所使用的存储引擎的不同(MySQL常用的两个存储引擎是MyISAM和InnoDB),存储引擎会创建各自不同的数据库文件。

    1)MyISAM数据库表文件:.MYD文件:即MY Data,表数据文件 .MYI文件:即MY Index,索引文件 .log文件:日志文件。

    2) InnoDB数据库表文件:.ibd文件:InnoDB引擎开启了独立表空间产生的存放该表的数据和索引的文件。

8.     聚集索引和非聚集索引原理(数据的物理存储)

非聚集索引:如MyISAM中.MYD (MYData,存储数据)和.MYI (MYIndex,存储索引)是存放在不同的文件中的,数据文件和索引文件可以放置在不同的目录,平均分布io,获得更快的速度。这样分开存的方式就叫非聚集索引。

而InnoDB是存放在一个.ibd文件中的,这是聚集索引。

参考博客:

https://www.cnblogs.com/olinux/p/5217186.html

https://www.cnblogs.com/zlcxbb/p/5757245.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java技术学习之道

JAVA设计模式之单例模式

15730
来自专栏技术碎碎念

Mybatis插入数据后返回主键id

20830
来自专栏岑玉海

sqoop 常用命令整理(一)

  这些内容是从sqoop的官网整理出来的,是1.4.3版本的Document,如果有错误,希望大家指正。       1.使用sqoop导入数据   sqoo...

44860
来自专栏精讲JAVA

Java面试之数据库面试题

触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可...

16740
来自专栏python成长之路

(细节)My SQL中主键为0和主键自排约束的关系

19840
来自专栏PHP实战技术

这些Mysql基础设计思路以及优化思路我都给你总结好了

4、btree索引,就是用树形结构存储在磁盘上,其中操作是用2分发,找一个中间点,然后把大比这个大的分在一边,小的放在一边,然后当你查询的时候,从数字头开始,大...

11920
来自专栏程序员的SOD蜜

抽象SQL查询:SQL-MAP技术的使用

什么是参数化查询?我们来看百科对此的定义和示例: 一,定义 --------------------------------------------------...

333100
来自专栏NetCore

Sql语句收藏

1、 查询列表,按照In的排序进行排序 select *, (select count(0) from [picture] where album_id=[al...

217100
来自专栏我爱编程

Day24访问数据库

使用SQLite SQLite是一种嵌入式数据库,它的数据库就是一个文件。由于SQLite本身是C写的,而且体积很小,所以,经常被集成到各种应用程序中,甚至在i...

39040
来自专栏PHP在线

介绍mysql中replace方法

今天在编程的时候,学习了replace into的用法,真的很好用,是insert into的增强版。在向表中插入数据时,我们经常会遇到这样的情况:1、首先判断...

33740

扫码关注云+社区

领取腾讯云代金券