MySQL(五)|《千万级大数据查询优化》第二篇:查询性能优化(1)

MySQL优化一般是需要索引优化、查询优化、库表结构优化三驾马车齐头并进。
本章节开始讲查询优化。

一、为什么查询速度会慢

可以把查询当作一个任务,它由一系列子任务组成,每个子任务都会消耗一定的时间。如果要优化查询,实际上是优化其子任务,要么消除其中一些子任务,要么减少子任务的执行次数,要么让子任务运行得更快。
MySQL在执行查询的时候有哪些子任务,这个是有一定的方法进行剖析的,具体方法下回单独拿一个章节来分析。
通常来说,查询的生命周期大致可以按照顺序来看:从客户端,到服务端,然后在服务器上进行解析,生成执行计划,执行,并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段,这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序、分组等。
在完成这些任务的时候,查询需要在不同的地方花费时间,包括网络,CPU计算,生成统计信息和执行计划、锁等待(互斥等待)等操作,尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。
在每一个消耗大量时间的查询案例中,都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

一、首选要优化数据访问

查询性能底下最基本的原因是访问的数据太多。所以,对于低效的查询,一般通过两个步骤来分析:

  1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也可能是访问了太多的列。
  2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

1.1、是否向数据库请求了不需要的数据

在访问数据库时,应该只请求需要的行和列,请求多余的行和列会消耗MySQL服务器的CPU和内存资源,并增加网络开销。
1、在处理分页时,应该使用LIMIT限制MySQL只返回需要的数据,而不是向应用程序返回全部数据后,再由应用程序过滤不需要的行。
2、多表关联时,或获取单表数据时,尽量避免不加思考地使用SELECT *
3、当一些数据被多次使用时可以考虑将数据缓存起来,避免每次使用都要到MySQL查询。

1.2、MySQL是否在扫描额外的记录,应该让MySQL使用最合适的方式查询数据

对于MySQL,最简单的衡量查询开销有三个指标:响应时间、扫描的行数和返回的行数。这里主要考虑提高扫描的方式,即查询数据的方式。
查询数据的方式有全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。这些查询方式,速度从慢到快,扫描的行数也是从多到少。可以通过EXPLAIN语句中的type列反应查询采用的是哪种方式。
通常可以通过添加合适的索引改善查询数据的方式,使其尽可能减少扫描的数据行,加快查询速度。
例如,当发现查询需要扫描大量的数据行但只返回少数的行,那么可以考虑使用覆盖索引,即把所有需要用到的列都放到索引中。这样存储引擎无须回表获取对应行就可以返回结果了。

二、重构查询的方法

设计查询的时候需要考虑是否需要把一个复杂的查询分成多个简单的查询。在我的印象中,曾经无数次听到一个经验法则:可以在数据库中做的事不要放在应用程序中,数据库比我们想象的要厉害的多。这个经验法则是在华夏基金使用Oracle编写SQL时一位Oracle牛人告诉我的,后来我把它使用到MySQL上,真是吃尽苦头。
当然这其中的原因有Oracle和MySQL原本就不是一样的处理逻辑,并且现在的网络通信、查询解析和优化的代价并没有以前那么高啦。再次说明,经验法则有在某种特定笼子里才有效。
分解复杂的查询:
可以将一个大查询切分成多个小查询执行,每个小查询只完成整个查询任务的一小部分,每次只返回一小部分结果。
删除旧的数据是一个很好的例子。
如果只用一条语句一次性执行一个大的删除操作,则可能需要一次锁住很多数据,占满整个事务日志,耗尽系统资源、阻塞很多小的但重要的查询。将一个大的删除操作分解成多个较小的删除操作可以将服务器上原本一次性的压力分散到多次操作上,尽可能小地影响MySQL性能,减少删除时锁的等待时间,同时也减少了MySQL主从复制的延迟。这个方法我一直在用。
另一个例子是分解关联查询,即对每个要关联的表进行单表查询,然后将结果在应用程序中进行关联。我在之前一家公司和一位在阿里待过很多年的同事一起编码时,他就是这么干的。后来我在心中默默地鄙视着他,因为我心里有这么一个经验法则(可以在数据库中做的事不要放在应用程序中,数据库比我们想象的要厉害的多),并且我在行动上也是保持能用一个SQL解决的事绝对不会用两个SQL。这么做当然处理经验法则的原因之外还有一个原因是:获取数据的逻辑尽量与业务代码分离,这样以后在切换数据库时也很方便。实际上是这样吗?未必啊。那次的无知让我吃尽苦头啊,后来因为SQL的性能问题再把我写的大部分SQL进行分解。
用分解关联查询的方式重构查询有如下的优势:

  1. 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
  2. 将查询分解后,执行单个查询可以减少锁的竞争。
  3. 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
  4. 查询本身效率也可能会有所提升。
  5. 可以减少冗余记录的查询。在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会减少网络和内存的消耗。
  6. 更进一步,这样做相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

三、查询执行的基础

当向MySQL发送一个请求之后,MySQL到底做了些什么?如下图所示:

查询执行路径
  1. 客户端发送一条查询给服务器。
  2. 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进行下一阶段。
  3. 服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。
  4. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

上述的每一步都比想象的复杂。我们在下一章节来进行分析。

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

推荐阅读更多精彩内容