专栏首页纯洁的微笑关于SQL优化,你不能只是说自己只会语句的优化了

关于SQL优化,你不能只是说自己只会语句的优化了

每天早上七点三十,准时推送干货

文章有点长,请各位看官按下耐心,一定看下去,虽然数据库这块的内容很枯燥,但是一定得保证自己全部都掌握,才能拿到一个很好的Offer,不是么?

大部分人说的SQL优化

阿粉之前帮公司面试过一部分人,因为之前和老大一起面试,所以也看过不少人的简历,而简历上对于数据库这块内容,很多人都写的是这个样子的。

  • 熟练使用 MySQL,SQLServer,熟悉Oracle,熟悉SQL语句优化。

确实,如果你对数据库没有进行过深入的了解的时候,你也不敢往上面写,只能是去写关于SQL语句优化方面的,但是很多时候,在你写完这个SQL语句之后,你是压根不会去主动的去优化的,很多时候都是停在了,不出问题,我就不改,只要功能实现了,那么,一切万事大吉。

而本文,告诉你,不要只是会SQL语句优化。

SQL语句优化

我们在面试的时候,面试官看你写了SQL语句优化,有时候就会提问了,那你说说SQL语句优化都是在哪些方面的吧。

关于SQL语句优化,内容可就比较多了。

  • 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
  • 尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
  • 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。

。。。。。

类似这样的SQL语句优化还有很多很多,但是大家有没有注意到我在上面标注出来的内容,引擎? 相信大家之前肯定也都知道,但是没有做过多的研究,但是为了各位面试顺利,我们就来加紧分析这个吧。

MySQL体系

以下全文,全部按照MySQL来进行分析,分析引擎,我们先从MySQL分析吧。MySQL的体系结构图如下:

我们能从图中看出一点内容,比如说MySQL的组成部分。

  • 连接池组件
  • 管理服务和工具组件
  • SQL接口组件
  • 查询分析器组件
  • 优化器组件
  • 缓冲(Cache)组件
  • 插件式存储引擎
  • 物理文件

不得不说,这个插件式存储引擎总结的是极其精辟的呀,MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。

但是我们要注意一个最重要的,那就是存储引擎是基于表的,而不是数据库。

MySQL存储引擎

存储引擎是MySQL区别于其他数据库的一个最重要特性,每个存储引擎都有他们自己的特点,不同的特点,会在不同的场景下使用,虽然我们在开发中很多时候都是使用的一种,但是能够根据具体的应用建立不同存储引擎表,这才是最牛逼的吧。

那么我们看看MySQL都支持哪些存储引擎把,手动在自己的MySQL中输入查询语句 show engines,就会如下图所示。

大家看完是不是感觉还挺多,但是MySQL的9种存储引擎都是各自是各自的特点,然后根据需求的不同,我们在建表的时候可以选择一下,是不是发现了又一个带妹的好机会,那我们就开始分析一下吧。

1.FEDERATED存储引擎

在阿粉之前翻看书籍的时候就看到过这个,说这个引擎不是存放数据的引擎,而是一个指向远程MySQL数据库服务器的,那是什么意思呢,其实一句大白话:“我这里不存表结构文件和数据文件,我是在远程端存的”,这时候就有了个比较有意思的地方,如下图:

就像图上说的,FEDERATED存储引擎分成了2部分,一部分是本地服务,另外一部分就是远程服务,那么如果在你切换到这个引擎的时候,他在执行CRUD的时候,就会把执行操作的命令发到远程服务器,然后执行完了之后,在发回本地,然后从本地服务器中返回匹配的行即可。

在这里,阿粉不去着重的讲这个,因为重点是我们最常使用的那个,大家肯定都知道,那就是InnoDB存储引擎。

2.InnoDB存储引擎

InnoDB存储引擎这个一般也是大家在面试中经常的能和面试官侃侃而谈的一个,因为它是默认的数据库存储引擎。注意,这个默认是从MySQL的5.5.8开始

特点比较多,下面就开始分析一下都有哪些特点吧。

  • 支持事务。默认的事务隔离级别为可重复,这个我们经常用,所以肯定也都知道。
  • 支持外键,这个外键大家肯定也都清楚,有利有弊,毕竟外键的作用在那里放着(利:增加可读性,若出现宕机,最大限度的保证数据的一致性和完整性,弊:降低了表的查询速度,如果数据了过大,那么你插入数据库数据的时长可能是不增加外键的十倍)
  • 行锁设计,这样可以支持更高的并发,这也是为什么有时候面试官说你们上ES有点大材小用,因为MySQL自己也能处理那么多。
  • 使用多版本并发控制(MVCC)来获得高并发性,并且实现了SQL标准的4种隔离级别,默认为REPEATABLE READ级别。
  • 使用一种被称为next-key locking(有人称它为间隙锁)的策略来避免幻读(phantom)现象的产生
  • 数据存储采用了聚集(clustered)的方式,每张表的存储都是按主键的顺序进行存放。
  • InnoDB的索引结构和MySQL其他的存储引擎不同,聚簇索引对主键查询性能非常高,这时候就得有个限制要求,如果表上的索引较多,主键就尽可能的小。
  • InnoDB通过一些机制和工具都支持真正的热备份,也就是在线热备份。
  • 数据存储在表空间(tablespace)中,这个表空间实际上是由InnoDB管理的一个黑盒,由一系列的文件组成。
2.1InnoDB的体系架构

我们从上面的图中就能看到,InnoDB存储引擎有许多的内存块,可以认为这些内存块其实就相当于是一个大的内存池,就是线程池是类似的那种,

既然图中我们看到了后台的线程,那么我们也要说说这个后台线程是个什么?

InnoDB存储引擎是多线程的模型,因此其后台有多个不同的后台线程,负责处理不同的任务。

而这个后台线程也是分两个部分的,一个是核心线程,一个是IO线程。

  • 核心线程Master Thread
  • IO线程 IO Thread
  • 净化线程Purge Thread
  • 清理线程Page Cleaner Thread

核心线程

核心线程的作用就是将缓冲池中的数据异步的刷新到磁盘上,来保证数据的一致性。

IO线程

IO线程则很简单,主要就是用来IO请求的回调处理。

净化线程

主要作用就是事务提交之后回收已经使用并分配的undo页

清理线程

他的作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。

如果阿粉当时面试的时候能够把这些内容给面试官说上,相信工资肯定还能再多来点。

3.Memory 存储引擎

Memory 存储引擎实际上就是将表中的数据存放在内存中,如果数据库重启或发生崩溃,表中的数据都将消失。也就是说,如果你们的数据存储在Memory 存储引擎中,如果机房不慎掉电,那完了,之前存在里面的东西都没了,就和你用了个 rm -rf效果一样,不过一个是被动的,一个是主动的。

这种用的比较少,阿粉就不再多给大家介绍了,就说说它的特点得了,毕竟一个关机,直接就没了。

  • 不支持TEXT和BLOB类型,对于字符串类型的数据,只支持固定长度的行,VARCHAR会被自动存储为CHAR类型;
  • 速度非常快,只支持表锁,并发性能较差,并且不支持TEXT和BLOB列类型
  • 服务器一但出现宕机,所有数据全部消失
  • 存储变长字段(varchar)时是按照定常字段(char)的方式进行的,因此会浪费内存

4.MyISAM存储引擎

这个引擎特点非常明显,不支持事务,但是支持全文检索,要面向一些OLAP(联机分析处理)数据库应用.

5.BLACKHOLE存储引擎

这个引擎就像他的名字一样,肉包子打狗,有去无回,它的用途也比较简单

  • SQL 文件语法的验证
  • 用来查找与存储引擎自身不相关的性能瓶颈

6.CSV存储引擎

CSV 存储引擎实际上操作的就是一个标准的CSV 文件,而且他的特点就是不支持索引,也就说,不支持索引,那么效率必然会很低,这个相信很多人都不会选择去使用它。

针对这些引擎,阿粉就说,既然都了解了这么多的内容了,那肯定不能在继续说我们的SQL语句优化了,当你在面试的时候,就要有针对性的了。

假如说面试的时候,面试官问你:现在有一个功能,测试那边反馈过来的信息是,这个功能的响应时间超出了预期的值,你都从哪些方面入手来处理这个问题。

这个问题看似不是很难,但是可以根据这个问题延伸出很多很多的问题。

问题一:如果你说从SQL语句优化,入手,那么势必要去检查索引,而面试官下一个问题可能就是,为什么加了索引,速度就会变快。

问题二:如果你说服务器的配置,那么面试官可能心里就想,为了一个功能上的反馈,你让我去给服务器增加配置,代价太大。

如果你对索引还没有了解到很深层面的时候,这个时候你就可以把SQL语句优化,和看数据库该表的引擎是否可以进行修改,如果可以,那么你就可以开始把这些关于SQL引擎的优势往自己的方向走了。

文献参考

《高性能MySQL》

《MySQL技术内幕》

最后说两句(求关注)

最近大家应该发现微信公众号信息流改版了吧,再也不是按照时间顺序展示了。这就对阿粉这样的坚持的原创小号主,可以说非常打击,阅读量直线下降,正反馈持续减弱。

所以看完文章,哥哥姐姐们给阿粉来个在看吧,让阿粉拥有更加大的动力,写出更好的文章,拒绝白嫖,来点正反馈呗~。

如果想在第一时间收到阿粉的文章,不被公号的信息流影响,那么可以给Java极客技术设为一个星标

最后感谢各位的阅读,才疏学浅,难免存在纰漏,如果你发现错误的地方,留言告诉阿粉,阿粉这么宠你们,肯定会改的~

往期精彩回顾

MongoDB 最全攻略

社交软件已经到达瓶颈了吗?马克思携 ClubHouse 告诉你没有!

一文带你看懂 Redis BitArray 如何实现高性能的位操作

本文分享自微信公众号 - 纯洁的微笑(keeppuresmile)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-02-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 2015年系统架构师软考案例分析考点

    两者最大的区别是:状态图侧重于描述行为的结果,而活动图侧重描述行为的动作。其 次活动图可描述并发行为,而状态图不能。

    cwl_java
  • 一文了解Optimizer Trace

    MySQL的执行计划跟踪,一直是比较欠缺的能力。如Oracle中的10046、10053提供的trace执行计划能力,被很多Oracle DBA所称赞。确实在某...

    用户5548425
  • Hibernate与Mybatis的区别优缺点对比

    我是一名java开发人员,hibernate以及mybatis都有过学习,在java面试中也被提及问道过,在项目实践中也应用过,现在对hibernate和myb...

    Java周某人
  • 一条SQL语句在MySQL中如何执行的

    本篇文章会分析一个 sql 语句在 MySQL 中的执行流程,包括 sql 的查询在 MySQL 内部会怎么流转,sql 语句的更新是怎么完成的。

    帅地
  • .NET面试题系列[15] - LINQ:性能

    当你使用LINQ to SQL时,请使用工具(比如LINQPad)查看系统生成的SQL语句,这会帮你发现问题可能发生在何处。

    s055523
  • 如何写出更快的 SQL (db2)

    在数据库开发的初期,或者在系统刚上线的初期,由于数据量比较少,一些查询 SQL 语句、视图、存储过程编写等体会不出 SQL 语句各种写法的性能优劣,但是随着数据...

    matinal
  • 100% 展示 MySQL 语句执行的神器-Optimizer Trace

    在上一篇文章《用Explain 命令分析 MySQL 的 SQL 执行》中,我们讲解了 Explain 命令的详细使用。但是它只能展示 SQL 语句的执行计划,...

    程序员历小冰
  • 100% 展示 MySQL 语句执行的神器-Optimizer Trace

    在上一篇文章《用Explain 命令分析 MySQL 的 SQL 执行》中,我们讲解了 Explain 命令的详细使用。但是它只能展示 SQL 语句的执行计划,...

    程序员历小冰
  • Java面试中问及Hibernate与MyBatis的对比,在这里做一下总结

    我是一名java开发人员,hibernate以及mybatis都有过学习,在java面试中也被提及问道过,在项目实践中也应用过,现在对hibernate和myb...

    Java团长
  • Java面试中问及Hibernate与MyBatis的对比,在这里做一下总结

    我是一名java开发人员,hibernate以及mybatis都有过学习,在java面试中也被提及问道过,在项目实践中也应用过,现在对hibernate和myb...

    企鹅号小编
  • 通俗易懂讲解一条SQL是怎么执行的

    额~~不是我不说啊,因为细说起来,我可以细分为DML(Update、Insert、Delete),DDL(表结构修改),DCL(权限操作),DQL(Sele...

    Java3y
  • 一条查询SQL的执行过程

    需要从数据库检索某些符合要求的数据,我们很容易写出 Select A B C FROM T WHERE ID = XX 这样的SQL,那么当我们向数据库发送这...

    JAVA日知录
  • Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化

    本文提要 最近写的几篇文章都是关于数据层优化方面的,这几天也在想还有哪些地方可以优化改进,结合日志和项目代码发现,关于数据层的优化,还是有几个方面可以继续修改的...

    程序员十三
  • Apache Calcite 论文学习笔记

    特别声明:本文来源于掘金,“预留”发表的[Apache Calcite 论文学习笔记](https://juejin.im/post/5d2ed6a96fb9a...

    叁金
  • 【数据分析】关于学习SQL的五个常见问题?

    对从事数据工作的小伙伴来说,SQL几乎是必备技能,写得一手好SQL说明你是一个合格的‘取数民工’。

    黄博的机器学习圈子
  • Mysql面试题及千万级数据查询优化

    今天在说Mysql查询优化之前,我先说一个常见的面试题,并带着问题深入探讨研究。这样会让大家有更深入的理解。

    攻城狮的那点事
  • 我后悔在简历上写了“精通”…

    前言:众所周知,简历上“了解=听过名字;熟悉=知道是啥;熟练=用过;精通=做过东西”,我现在十分后悔在简历上写了“精通”二字…

    小灰
  • Mysql面试题及千万级数据查询优化

    今天在说Mysql查询优化之前,我先说一个常见的面试题,并带着问题深入探讨研究。这样会让大家有更深入的理解。

    JAVA葵花宝典
  • 自制小工具大大加速MySQL SQL语句优化(附源码)

    优化SQL,是DBA常见的工作之一。如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题。在日常的优化工作中,我发现有很多操作是在优化过程中必不可少的...

    宜信技术学院

扫码关注云+社区

领取腾讯云代金券