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

前言

作为一名工作了4年的程序猿,今天我将站在程序员的角度以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,到这里,准备结束了,查询容易,优化不易,且写且珍惜!

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 206,723评论 6 481
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 88,485评论 2 382
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 152,998评论 0 344
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 55,323评论 1 279
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 64,355评论 5 374
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,079评论 1 285
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,389评论 3 400
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,019评论 0 259
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 43,519评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,971评论 2 325
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,100评论 1 333
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,738评论 4 324
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,293评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,289评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,517评论 1 262
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,547评论 2 354
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,834评论 2 345

推荐阅读更多精彩内容