教你如何优化SQL,干货满满

目录

  1. MySQL组件
  2. 存储引擎与索引
  3. SQL 分析
  4. SQL 优化(案例+处理思路)
  5. 索引设计

1 MySQL组件

1.1 MySQL整体架构

MySQL内部组件结构图.png

客户端:如navicat,sql server 等等。

Service层:从 Service 层和引擎层才是属于 MySQL Server 部分的组件。连接器、查询缓存(很鸡肋,MySQL8移除了该组件)、词法分析器、优化器、执行器。

引擎层:真正执行 SQL,对数据进行读写的地方。

1.2 组件

通过整体架构图可以知道 MySQL 里面重要的组件有哪些,它们执行的顺序是怎样的。现在我们来看下它们具体的职责。

1.2.1 连接器

接收客户端连接,进行认证授权的组件。

1.2.2 词法分析器

处理流程如下图


分析器工作流程图.png
  1. 词法分析
    首先扫描文本,再识别 词元/token(每个词元代表一个基本的语法单元,如关键字、标识符、操作符、常量、字符串等)。生成有序的词元流(token stream)给到语法分析器

  2. 语法分析 + 分析机
    语法分析器会对词元流进行进一步的检查:

    • 语法分析
      检查代码是否符合编程语言的语法规则,也就是是否遵循正确的语法结构。如果源代码包含语法错误,语法分析器会发现并报告错误,通常以编译错误的形式呈现。
    • 语义分析
      语义分析阶段关注的是源代码的含义和语义规则。它确保代码在语法上正确的基础上也是有意义的,不会违反编程语言的语义规则。语义分析通常更加高级和复杂,因为它需要考虑语言的语义含义。
  3. 构建抽象语法树(Abstract Syntax Tree,AST
    SQL 语句的分析分为词法分析与语法分析,mysql 的词法分析由 MySQLLex(MySQL自己实现的)完成,语法分析由 Bison 生成。关于语法树可以参考:https://en.wikipedia.org/wiki/LR_parser。除了 Bison 外,Java 当中也有开源的词法结构分析工具例如 Antlr4,ANTLR 从语法生成一个解析器,可以构建和遍历解析树,可以在 IDEA 工具当中安装插件 antlr v4 grammar plugin。

1.2.3 优化器

分析 SQL,生成执行计划

优化器会对我们的 SQL 进行分析,看预计使用哪些索引,SQL 的执行顺序如何,实际会使用哪些索引(没有真的执行SQL),使用索引的情况等等。其实这些无非就是我们 explain 返回的执行计划那些信息。

1.2.4 执行器

执行器承担的任务相对加多,需要分场说明:

  • 查询计划优化
    这个场景通常配合优化器一起执行,优化器生成执行计划后,执行器负责执行计划。
  • 查询执行
    执行器负责执行SELECT查询语句,它会将查询语句发送到数据库引擎中的查询处理器,然后将查询结果返回给用户或应用程序。这包括从表中检索数据、应用筛选条件、排序和分组等操作,以满足用户的查询需求。
  • 插入、更新和删除操作
    执行器负责执行 INSERT、UPDATE 和 DELETE 等数据修改操作。它将这些操作请求传递给数据库引擎中的数据修改处理器,以便在数据库中插入新数据、更新现有数据或删除数据。
  • 事务管理
    执行器管理数据库事务的开始、提交和回滚。事务是一组SQL操作的逻辑单元,可以确保数据的一致性和完整性。执行器负责启动新事务、提交已完成的事务以及在必要时回滚事务。
  • 并发控制
    数据库可能会同时接收多个查询和事务请求,执行器需要进行并发控制,以确保多个事务之间的数据访问不会互相干扰。这包括锁管理、隔离级别的实现等。
  • 错误处理
    执行器负责处理SQL执行过程中可能出现的错误,它会将错误信息返回给用户或应用程序,以便进行故障排除和处理。

2 存储引擎与索引

存储引擎也是 MySQL 的重要组件,但是它会涉及到索引。而索引则属于数据结构,并且重要程度并不比组件低。因此,这里需要拆出来单独写。

存储引擎的作用是什么呢?读写磁盘,实现数据持久化

SHOW ENGINES;

MySQL 支持很多的存储引擎,我们可以通过上面的 SQL 查询得到,下图是笔者的查询结果


存储引擎的种类.png

这里主要介绍 MyISAM 和 InnoDB。

2.1 MyISAM

使用 MyISAM 的表会生成三个文件,具体如下图


MyISAM的表保存数据对应的文件.png

*.frm : 存放表结构数据
*.MYD : 存放表记录
*.MYI : 存放索引

MyISAM 的表的主键索引和非主键索引都是非聚簇索引

非聚簇索引指的是叶子节点存放 ID(主键),不存放记录信息。

非聚簇索引数据结构如下图(这用了主键索引做示例)


MyISAM索引图.png

优点:在高频读,低频写的场景下,读的性能十分好。
缺点:1、不支持事务;2、不支持行锁,支持表锁,所以多并发写数据场景下,性能差。

2.2 InnoDB

我们最常用的存储引擎就是 InnoDB

我们先看下在操作系统中,使用 InnoDB 会生成什么文件,请看下图


InnoDB的表保存数据对应的文件.png

*.frm:数据表结构相关信息存储的文件
*.idb:索引和数据存储的文件

聚簇索引就是索引和和非索引字段放在一起,也就是一行完整的记录,并且一个表最多只能有一个聚簇索引

2.2.1 主键索引

使用 InnoDB 的表有且仅有一个聚簇索引。下面以主键索引为例(我的user表就只有三个字段)

InnoDB聚簇索引图.png

2.2.1.1 尽量使用自增整型主键

大部分有名的互联网公司的 DBA 都建议 InnoDB 表使用自增整型主键

  1. 为什么要用主键?
    如果表没有主键,那么MySQL会在该表内找一个具有唯一性的字段作为主键并构建主键索引;如果找不到具有唯一性的字段,就会创建一个符合主键要求的隐藏列,使用它作为主键并构建主键索引。因此,无论如何都是需要主键的,那么不如自己明确主键。
  2. 为什么大厂都建议主键尽量用自增整型?
    这和构建主键索引有关系。
    • 整型
      1. 比较大小,整型更简单
      2. 整型占用空间小,一个是节约空间,另外一个就是一次IO可以读取更多数据
    • 自增
      B+Tree 要求叶子节点是有序的,所以当我们主键是单调递增时,直接链尾插入就好,节约了查询的时间,而且后面插入的数据一定再前面插入的数据后面的话,MySQL 还可以使用顺序IO提高性能。

2.2.2 二级索引

二级索引是非聚集的,主要是为了节约空间。二级索引是先通过过滤条件找到主键,通过主键回表得到结果集。

InnoDB二级索引图.png

2.2.3 联合索引(复合索引)

假如现在我有个用户表,用户表有4个字段:username、telephone、age、sex。
我们可以建两种类型的联合索引:联合主键,普通的联合索引。

2.2.3.1 联合主键

username、sex 构建成联合主键

联合主键索引.png

先根据名字转成的ascii码进行排序,如果 ascii 码一样,那么再根据性别的 ascii 码大小比较排序。

2.2.3.2 普通的联合索引

这个和上面的没差太多,只是 data 存的是主键,需要回表查找。

2.2.4 分表与索引

我们常说数据量太大了,要分表。阿里也给出了需要分表的一些参考值,推荐单表数据量超过 500W 或者表超过了 10G 时,应该分表。但是,这是阿里工程师们根据自己接触阿里内部的各种业务场景得出来的经验值!针对业务情况的不同,索引设置的不同,那么分表的阈值自然也就不同。

方法论
一个业务一般是有自己的预期收益,这个预期的收益是可以估算出我们技术设计里面表所需要的数据量的基本值。通过了解,这个业务的后续发展和使用预想,我们可以相应地去看这个数据量的增量,看是稳定后不会有太多增量,还是预想发展好会持续有增量的。然后我们就可以得出我们需不需要分表,要分多少张表了的结论了。

案例

  • 业务背景
    业务上需要用主播直播中的高光时刻的画面给主播做推荐。高光时刻不用管,这是大数据+AI得出的结论,我们只是根据它们的要求查出对应时间段的静图/动图。我们真正需要做的是存一段时间直播流推过来的图片而已。
  • 具体数据
    平均每日开播主播数量:8000+
    平均开播时长:2H左右
    直播流记录截图频率:5s/帧
  • 图库表字段
    id(8B)+uid(8B)+live_id(8B)+pic_url(1B * 100)+create_time(8B)
  • 需要查询的时间范围是7天,也就是说超过7天可归档
还有 85% 的精彩内容
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
支付 ¥88.88 继续阅读
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 203,098评论 5 476
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,213评论 2 380
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 149,960评论 0 336
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,519评论 1 273
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,512评论 5 364
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,533评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,914评论 3 395
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,574评论 0 256
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,804评论 1 296
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,563评论 2 319
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,644评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,350评论 4 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,933评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,908评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,146评论 1 259
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,847评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,361评论 2 342

推荐阅读更多精彩内容