从程序员的角度深入理解MySQL

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 前言作为一名工作了多·年的程序猿,今天我将站在程序员的角度以MySQL为例探索数据库的奥秘!数据库基本原理 我对DB的理解第一,数据库的组成:存储 + 实例不必多说,数据当然需要存储;存储了还不够,显然需要提供程序对存储的操作进行封装,对外提供增删改查的API,即实例。

前言

作为一名工作了多·年的程序猿,今天我将站在程序员的角度以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。

从程序员的角度深入理解MySQL

 

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:

从程序员的角度深入理解MySQL

 

注意复合索引(age,address)

从程序员的角度深入理解MySQL

 

符合最左前缀匹配

从程序员的角度深入理解MySQL

 

复合索引失效

OK,到这里,准备结束了,查询容易,优化不易,且写且珍惜!

为什么某些人会一直比你优秀,是因为他本身就很优秀还一直在持续努力变得更优秀,而你是不是还在满足于现状内心在窃喜! 关注我,私信回复我“666"或者“Java架构"获取免费的Java架构学习资料(里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)合理利用自己每一分每一秒的时间来学习提升自己,不要再用"没有时间“来掩饰自己思想上的懒惰!趁年轻,使劲拼,给未来的自己一个交代!

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
SQL 关系型数据库 MySQL
【MySQL入门到精通-黑马程序员】MySQL基础篇-函数
SELECT 函数(参数); 注:SUBSTRING(str,start,len);中str的索引从1开始,即start最小为1。
74 0
【MySQL入门到精通-黑马程序员】MySQL基础篇-函数
|
存储 关系型数据库 MySQL
【MySQL入门到精通-黑马程序员】MySQL基础篇-约束
e.g.:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
61 0
|
SQL 关系型数据库 MySQL
【MySQL入门到精通-黑马程序员】MySQL基础篇-DCL
DCL英文全称是Data Control Language(数据控制语言),用来管理数据库用户、控制数据库的访问权限。
61 0
|
SQL 关系型数据库 MySQL
【MySQL入门到精通-黑马程序员】MySQL基础篇-DML
DML(Data Manipulation Language,数据操作语句),用来对数据库中表的数据记录进行增删改操作。
72 0
|
SQL 关系型数据库 MySQL
【MySQL入门到精通-黑马程序员】MySQL基础篇-SQL概述及DDL
(精度指数的所有位数;标度指小数点后数的位数) 字符串类型
107 0
|
6月前
|
关系型数据库 MySQL 程序员
老程序员分享:MYSQL外键(ForeignKey)的使用
老程序员分享:MYSQL外键(ForeignKey)的使用
64 0
|
7月前
|
关系型数据库 MySQL 大数据
程序员小sister的烦恼_快速上手大数据ETL神器Kettle(xls导入mysql)
程序员小sister的烦恼_快速上手大数据ETL神器Kettle(xls导入mysql)
110 0
|
SQL 关系型数据库 MySQL
【MySQL入门到精通-黑马程序员】MySQL基础篇-DQL
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记录。 查询关键字:SELECT
84 0
|
存储 SQL 关系型数据库
【MySQL入门到精通-黑马程序员】MySQL基础篇-概述及MySQL环境配置(2)
• 关系型数据库(RDBMS):建立在关系模型基础上,由多张相互连接的二维表 1. 使用表存储数据,格式统一,便于维护。 a. 使用SQL语言操作,标准统一,使用方便。
80 0
|
存储 关系型数据库 MySQL
【MySQL入门到精通-黑马程序员】MySQL基础篇-概述及MySQL环境配置(1)
数据库(DataBase(DB)):存储数据的仓库,数据是有组织的进行存储。 数据库管理系统(DataBase Management System(DBMS)):操纵和管理数据库的大型软件。
100 0