从程序员的角度深入理解MySQL
前言
作为一名工作了多·年的程序猿,今天我将站在程序员的角度以MySQL为例探索数据库的奥秘!
数据库基本原理
我对DB的理解
第一,数据库的组成:存储 + 实例
不必多说,数据当然需要存储;存储了还不够,显然需要提供程序对存储的操作进行封装,对外提供增删改查的API,即实例。
一个存储,可以对应多个实例,这将提高这个存储的负载能力以及高可用;多个存储可以分布在不同的机房、地域,将实现容灾。
第二,按Block or Page读取数据
用大腿想也知道,数据库不可能按行读取数据(Why? ^_^)。实质上,数据库,如Oracle/MySQL,都是基于固定大小(比如16K)的物理块(Block or Page,我这里就不区分统一称为Block)来实现调度和管理的。要知道Block是数据库的概念,如何对应到文件系统呢?显然需要指出“这个Block的地址在哪里”,当查找到地址后,读取固定大小的数据就相当于完成了Block的读取了。
数据库很聪明的,它不会仅仅只读取需要读取的Block,它还会替我们把附近的Block块都读取加载至内存。实际上,这是为了减少IO次数,提高命中率。事实上,一个Block块的附近Block也是热点数据,这种处理方式很有必要!
第三,磁盘IO是数据库的性能瓶颈
毫无疑问,数据在磁盘上,少不了磁盘IO。什么磁头旋转,定位磁道,寻址的过程,就不说了,我们是程序员,也管不了这些。但是这个过程确实是非常耗时的,和内存读取不是一个数量级,所以后来出现了很多方式来减少IO,提升数据库性能。
比如,增加内存,让数据库把数据更多的加载至内存。内存虽好,但也不能滥用,为什么这么说呢?假设数据库中有100G数据,如果都加载至内存,也就说数据库要管理100G磁盘数据+100G内存数据,你说累不累?(数据库要处理磁盘和内存的映射关系,数据的同步,还要对内存数据进行清理,如果涉及数据库事务,又是一系列复杂操作......)不过这里需要指出的是,为了加快内存查找速度,数据库一般对内存进行HASH存放。
比如,利用索引,索引相比内存,是一个性价比非常高的东西,后文详细介绍MySQL的索引原理。
比如,利用性能更好的磁盘...(和咱们就没关系呢)
第四,提出一些问题思考下:
为什么我们说利用delete删除一个表的数据较trancate一个表要慢?
【一个按行查找删除,多费劲;一个基于Block的体系结构删除】
为什么我们说要小表驱动大表?
【小表驱动大表会快?什么鬼?M*N和N*M不是一样的么?有鬼的地方,就有索引!】
探索MySQL索引背后的原理
对于绝大数的应用系统,读写比例在10:1,甚至100:1,而且insert/update很难出现性能问题,遇到最多的,最棘手的就是select了,select优化是重中之重,显然少不了索引!
说起MySQL的索引,我们会冒出很多这些东西:BTree索引/B+Tree索引/Hash索引/聚集索引/非聚集索引...这么多,晕头!
索引到底是什么,想解决什么问题?
老生常谈了,官网说MySQL索引是一种数据结构,索引的目的就是为了提高查询效率。
说白了,不使用索引的话,磁盘IO次数比较多!要想减少磁盘IO次数,怎么办?
我们想通过不断缩小想要获取的数据的范围来筛选出最终想要的结果,把每次查找数据的磁盘IO次数控制在一个很小的数量级,最好是常数数量级。
为了应对上述问题,B+Tree索引出来了!
Hello,B+Tree
在MySQL中,不同存储引擎对索引的实现方式是不同的,这里将重点分析MyISAM和Innodb。
MyISAM引擎的B+Tree索引结构
我们知道对于MyISAM引擎而言,数据文件和索引文件是分离的。从图中也可以看出,通过索引查找到后,就得到了数据的物理地址,然后根据地址定位数据文件中的记录即可。这种方式也叫"非聚集索引"。
而对于Innodb引擎而言,数据文件本身是索引文件!通俗点说,叶子节点上,MyISAM存储的是记录的物理地址,而Innodb上存储的是数据内容,这种方式即"聚集索引"。
另外一点需要注意的是,对于Innodb而言,主键索引中叶子节点存储的是数据内容,而普通索引的叶子节点中存储的是主键值!也就是说,对于Innodb的普通索引字段查找,先通过普通索引的B+Tree查找到主键后,然后通过主键索引的B+Tree进行查找。从这里你可以看出,对于Innodb而言,主键的建立非常重要!
而对于MyISAM而言,主键索引和普通索引仅仅的区别在于主键只需要查找到一条记录即可停止,而普通索引允许重复,找到一条记录后需要继续查找,在结构上没有区别,如上图所示。
深入B+Tree
提几个问题:
为什么B+Tree把真实的数据放到叶子节点,而不是内层节点?
为什么我们说索引字段要尽可能短,最好是单调递增的?
为什么复合索引存在最左匹配原则?
范围查询(>,<,between,like)对最左匹配有什么影响?
关于B+Tree的一些数学理论,咱们就不玩了,至少一点可以肯定的是:数据表的数据量N=F(树的高度h,每个Block存储的索引的个数m)。在N一定的情况下,索引字段越小,那么m会越大,这意味着h将越小!树越低,当然查找的更快!
如果内层节点存放真实的数据,显然m会变小,树将变高。
在实际应用中,我们应该尽可能采用单调递增的字段作为主键,一方面不会使得索引的数据结构变大,减小了索引占用的空间;另一方面也不会频繁的分裂B+Tree,使得效率下降。
比如复合索引(name,age,sex),B+Tree会优先比较name来确定下一步的搜索方向。如果突然来了个(age,sex),根本上就无从下手。这也是符合常理的,对于一本书,我们说“找到第几章第几节的XXX”,从没有听说过“找到第几节的XXX”!这是复合索引的重要特性,即最左匹配特性。
假设存在复合索引(name,age,sex),我们在进行select的时候,并没有按照这个顺序进行,而是sex = 'man' and name = 'zfz' and age = 27,是否会使用索引呢?数据库是很聪明的,在SQL优化的时候,会自动帮助我们调整!但是如果缺失了复合索引的第一列,数据库也将无能为力呢。
对于最左匹配,MySQL会一直向右匹配直到遇到范围查询就停止匹配。什么意思?比如复合索引(name,age,sex),对于name = 'zhangfengzhe' and age > 26 and sex = 'man',实际上只利用到了复合索引的name列。
想利用索引,就得“干净”
什么叫“干净”?就是不要让索引参与计算!比如在索引上应用函数,很可能导致索引失效。为什么呢?
其实不用想,B+Tree上存储的是数据,要比较的话,需要把所有的数据都应用上函数,显然成本太大。
想建立索引,看看区分度
索引虽然物美价廉,但是也别乱来。count(distinct col) / count(*)可以算一下col的区分度,显然对于主键而言,就是1。区分度太低的话,可以考虑下,是否还有必要建立索引呢?
Hash索引
这里并不是要深入分析Hash索引,而是要说明一下Hash的思想真是无处不在!
在MySQL的Memory存储引擎中,存在hash函数,给一个key,通过hash函数进行计算得到地址,所以通常情况下,hash索引查找,会非常快,O(1)的速度。但是也存在hash冲突,和HashMap一样,通过单链表的形式解决。
思考下,hash索引是否支持范围查询呢?
显然是不支持的,它只能给一个KEY去查找。就如同HashMap一样,查找key包含"zhangfengzhe"的,会很快么?
SQL优化神器:explain
SQL优化的场景很多,网上的技巧也很多,完全记不住!
要想彻底解决这个问题,我想只有把索引背后的数据结构和原理做适当的理解,遇到书写SQL或者SQL慢查询的时候,我们有基础去分析,再利用好explain工具去验证,就应该问题不大呢。
explain查询的结果,可以告诉你哪些索引正在被使用,表是如何被扫描的等等。这里我将演示个Demo。
数据表student:
注意复合索引(age,address)
符合最左前缀匹配
复合索引失效
OK,到这里,准备结束了,查询容易,优化不易,且写且珍惜!
为什么某些人会一直比你优秀,是因为他本身就很优秀还一直在持续努力变得更优秀,而你是不是还在满足于现状内心在窃喜! 关注我,私信回复我“666"或者“Java架构"获取免费的Java架构学习资料(里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)合理利用自己每一分每一秒的时间来学习提升自己,不要再用"没有时间“来掩饰自己思想上的懒惰!趁年轻,使劲拼,给未来的自己一个交代!
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
Tomcat中的类是怎么被一步步加载的?
了解Tomcat的类加载机制,原来一切是这么的简单。 一、类加载 在JVM中并不是一次性把所有的文件都加载到,而是一步一步的,按照需要来加载。 比如JVM启动时,会通过不同的类加载器加载不同的类。当用户在自己的代码中,需要某些额外的类时,再通过加载机制加载到JVM中,并且存放一段时间,便于频繁使用。 因此使用哪种类加载器、在什么位置加载类都是JVM中重要的知识。 二、JVM类加载 JVM类加载采用:父类委托机制,如下图所示: JVM中包括集中类加载器: BootStrapClassLoader 引导类加载器 ExtClassLoader 扩展类加载器 AppClassLoader 应用类加载器 CustomClassLoader 用户自定义类加载器 他们的区别上面也都有说明。需要注意的是,不同的类加载器加载的类是不同的,因此如果用户加载器1加载的某个类,其他用户并不能够使用。 当JVM运行过程中,用户需要加载某些类时,会按照下面的步骤(父类委托机制): 用户自己的类加载器,把加载请求传给父加载器,父加载器再传给其父加载器,一直到加载器树的顶层。 最顶层的类加载器首先针对其特定的位置加载...
- 下一篇
史上最全的MySQL高性能优化实战总结!
1.1 前言 MySQL对于很多Linux从业者而言,是一个非常棘手的问题,多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行MySQL的优化之前必须要了解的就是MySQL的查询过程,很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。 今天给大家体验MySQL的优化实战,助你高薪之路顺畅。 图 - MySQL查询过程 1.2 优化的哲学 优化有风险,涉足需谨慎 1.2.1 优化可能带来的问题 1.2.2 优化的需求 1.2.3 优化由谁参与 在进行数据库优化时,应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等,业务相关人员共同参与。 1.3 优化思路 1.3.1 优化什么 在数据库优化上有两个主要方面:即安全与性能。 1.3.2 优化的范围有哪些 存储、主机和操作系统方面: 应用程序方面: 数据库优化方面: 说明:不管是在,设计系统,定位问题还是优化,都可以按照这个顺序执行。 1.3.3 优化维度 数据库优化维度有四个: 硬件、系统配置、数据库表结构、SQL及索引 优...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- SpringBoot2初体验,简单认识spring boot2并且搭建基础工程
- CentOS7,CentOS8安装Elasticsearch6.8.6
- SpringBoot2整合MyBatis,连接MySql数据库做增删改查操作
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- MySQL8.0.19开启GTID主从同步CentOS8
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- SpringBoot2全家桶,快速入门学习开发网站教程
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- CentOS8编译安装MySQL8.0.19