专栏首页moon聊技术mysql┃多个角度说明sql优化,让你吊打面试官!

mysql┃多个角度说明sql优化,让你吊打面试官!


目录

  • 目录
  • 前言
  • 正文
    • 1.表结构优化●
    • 1.1拆分字段
    • 1.2字段类型的选择
    • 1.3字段类型大小的限制
    • 1.4合理的增加冗余字段
    • 1.5新建字段一定要有默认值
    • 2.索引方面●
    • 2.1索引字段的选择
    • 2.2利用好mysql支持的索引下推,覆盖索引等功能
    • 2.3唯一索引和普通索引的选择
    • 3.查询语句方面●
    • 3.1避免索引失效
    • 3.2合理的书写where条件字段顺序
    • 3.3小表驱动大表
    • 3.4可以使用force index()防止优化器选错索引
    • 4.分库分表●
  • 结语

前言

mysql的优化是我们经常都会提到的一个话题,也是重中之重,在很多大厂中会有专门的DBA来做这件事情,甚至更过分的是连应届生的招聘岗位要求上都写了需要懂一点sql优化,最近moon一直在写关于mysql的文章,包括之前写的索引相关,其实也都是为了这篇文章做个铺垫,所以你懂了吗,今天我将从表结构、索引、查询语句、分库分表这四个维度来和大家聊聊,在工作中,怎么进行sql优化?

正文

1.表结构优化●

优化sql最基本的条件时要有一张表,那么我们怎么通过一张表来达到sql语句优化的目的呢?

1.1拆分字段

我们给出一个场景,想象自己是一家包子铺老板,每天都要结账,于是肯定会有一张账户余额表,来记录包子铺的总资产

CREATE TABLE `accout_balance` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `account` varchar(64) NOT NULL DEFAULT '' COMMENT '账户',
  `balance` decimal(16,2) DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

可是后来包子铺的生意做的越来越好,老板陆陆续续开了几百家店,后来居然做成了全国连锁店。

老板很开心,但是他发现了一个问题,由于生意太火爆,所以每时每刻都会有人结账,而且他们的系统越来越卡了,这是为什么?

我们来分析下:

每时每刻都会有人结账,结账后会修改accout_balance(账户余额表)的balance(余额)字段,所以这张表是一张热表,而每一次修改都会开启一个事务(update语句就相当于一个事务),所以在高并发的情况下,问题显而易见,针对同一行数据一个事务必须要等上另一个事务执行完成之后才能执行自己的更新语句,所以越来越慢。(行锁)

那么我们怎么针对这种情况来优化呢?moon的思路是控制并发度。

我们目前的情况是几百家分店都会操作这同一行记录,那么我们就可以把这一行记录分成多行,也就是说,把账户的余额分成N份,这样每次增加的时候选择其中的一条记录增加,冲突的概率也变成了之前的N分之一。

1.2字段类型的选择

这种优化应该是比较常见的,我们就长话短说。

比如针对UUID这种数据我们可以直接使用char(36)来作为该字段的类型,或者说在表示boolean这种数据格式的时候,我们就可以直接使用tinyint(2)作为我们的字段类型,在我们提前可预知字段的大小的时候,最好在类型上直接限制,避免浪费存储空间占用。

1.3字段类型大小的限制

这点在我们公司的sql建表规范上就会明确写到。

我这里简单的举个例子,比如varchar,要使用varchar(255),这里会有几点考量:

.255刚好会消耗一个字节的存储单元,但是256会导致消耗两个字节的存储单元。(这个针对UTF-8编码)

.如果你要在varchar上建立索引,255会是一个完全索引,而266以上只能用到最左前缀(MySQL的每个单表中所创建的索引长度是有限制的,且对不同存储引擎下的表有不同的限制。myisam表,单列索引,最大长度不能超过1000 bytes,否则会报警,但是创建成功,最终创建的是前缀索引。innodb表,单列索引,超过 767 bytes的,给出warning,最终索引创建成功,取前缀索引(取前 255 字符)),最左前缀的弊端就是无法用到mysql提供的覆盖索引的加速功能了。

.此外在onlineddl的时候,255以下可以用inplace的方式,256需要rebuild。(Inplace方式:这是原生MySQL 5.5,以及innodb_plugin中提供的创建索引的方式。所谓Inplace,也就是索引创建在原表上直接进行,不会拷贝临时表。相对于Copy Table方式,这是一个进步。Inplace方式创建索引,创建过程中,原表同样可读的,但是不可写。)

1.4合理的增加冗余字段

在我们刚开始学习mysql的时候,就会了解到数据库的三范式,而在实际的使用过程中,为了性能,我们也可以抛弃数据库的三范式

moon在之前的公司就有这样的问题,一条sql语句要连5张表,正常一个查询下来可能要1分多钟,所以这条sql太重了,而在moon的细心观察下发现,其中两张表都只用到了其中一个字段,然后我就和DBA商量下将这两个字段冗余到了其它的两个表中(业务有关联),结果这条sql语句的执行时间就变成了十几秒。

1.5新建字段一定要有默认值

好处如下

1.节省空间。

大体看上去,好像设置可以为空的时候更节省空间,但实际上,他比NOT NULL要多占用一个bit的空间,用来判断该字段是否为空。

2.索引失效索引分裂

引用到null,索引会失效。还看到一个说法:空更新到非空时,如果空间不足,有可能会引起索引分裂。

3.减少因空值出现的计算错误等

count()在遇到null值时,这条记录不会计算在内。

2.索引方面●

2.1索引字段的选择

一般情况下,可以通过慢查询日志选择出一些热sql语句,给select条件后以及where条件后的字段加索引

2.2利用好mysql支持的索引下推,覆盖索引等功能

select a from user where  b = 5;

此时给a和b字段增加索引,这样可以利用mysql的覆盖索引加速的功能,省去了回表的过程。

select a from user where c = 5 and  d > 5;

此时给c和d字段增加索引,也可以在判断的时候也能利用到索引下推的功能,也就是说mysql在判断c=5后,发现d也是索引,会直接找到d判断d>5,如果不给d增加索引此时也是需要回表的。

其次对于组合索引: (a,b)这种索引一旦建立,就不需要再给a建立索引了,mysql的最左前缀原则支持组合索引或者字符串类型的索引最左N个单位的索引建立。反之,如果你此刻建立的是(a,b)索引,但是你的业务却还需要一个b的单独索引,那么就可以考虑给b单独新建索引了。如果现在你的表中只有a索引,但是业务需求需要(a,b)索引,一定要记得,先增加索引,然后再建立索引,不然可能会导致服务挂掉。moon有个朋友的同事,在新增索引的时候,选择了先删除,后增加,这样就导致的在删除后到新增前的这段空白期,出现了很多慢查询sql,同时请求量有很大,业务无法在短时间内处理完,只能慢慢等待,最后导致服务挂掉。

2.3唯一索引和普通索引的选择

如果我们能在业务意义上保证某个字段是唯一的,并且这张表又是一个经常写入数据的表,那么这里moon推荐你用普通索引,而不是唯一索引,原因如下: .在读取数据的时候,普通索引在查到满足第一个条件的记录后,会继续查找下一个记录,直到第一个不满足条件的记录。而唯一索引,查找到第一个满足条件的记录时,就直接停止了。这样看来其实唯一索引更好,但是实际观察来看,这种性能的差异微乎其微,况且我们还可以在查询语句上用limit 1来限制。重点是第二点。 .在更新过程中,普通索引的更新因为不用考虑唯一性,会将这次更新操作直接写入change buffer中,之后会定期或者再次访问到这个数据页的时候持久化到磁盘当中。而唯一索引的更新不能用change bufer,原因是要在表中判断是否已经有该条记录,所以会有一个将数据页读入内存的IO操作,而IO操作又是很消耗资源的。

3.查询语句方面●

3.1避免索引失效

.最佳左前缀法则(带头索引不能死,中间索引不能断

.不要在索引上做任何操作(计算、函数、自动/手动类型转换),不然会导致索引失效而转向全表扫描

.不能继续使用索引中范围条件(bettween、<、>、in等)右边的列,如:

select a from user where c > 5 and b = 4;

.索引字段上使用(!= 或者 < >)判断时,会导致索引失效而转向全表扫描

.索引字段上使用 is null / is not null 判断时,会导致索引失效而转向全表扫描。

.索引字段使用like以通配符开头(‘%字符串’)时,会导致索引失效而转向全表扫描,也是最左前缀原则。

.索引字段是字符串,但查询时不加单引号,会导致索引失效而转向全表扫描

.索引字段使用 or 时,会导致索引失效而转向全表扫描

3.2合理的书写where条件字段顺序

这里其实也是最左前缀原则。在一些后需维护开发工作中,可以观察表中的联合索引,当你新写的sql有where条件时,尽量在where条件的书写顺序按照联合索引的顺序

3.3小表驱动大表

join查询在有索引条件下,驱动表有索引不会使用到索引,被驱动表建立索引会使用到索引

MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与Join,则再通过前两个表的Join结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复。所以,小表驱动大表所建立的连接次数也远比大表驱动小表所建立的连接次数要小的多

可以通过EXPLAIN分析来判断在sql中谁是驱动表,EXPLAIN语句分析出来的第一行的表即是驱动表。

3.4可以使用force index()防止优化器选错索引

在我们确定要使用某个索引的时候可以使用force index()强制只用某个索引,避免在某些情况下优化器选择错误导致查询效率降低。

优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。

优化器会结合是否使用临时表、是否排序、扫描行数等因素进行综合判断。

当然是用force index 也是有弊端的,如果你的索引发生了变化,而你的sql语句没有即使更改,那么这里就会报错。

4.分库分表●

在以上你能做到优化的极致条件下,由于数据量很大,可能还是会面临着慢查询的情况出现,那么这时候我们就要考虑分库分表了。

moon这里简单的和大家举个例子:

一家做客服系统的公司,业务量很大,客户很多,每天可能有上千万的数据量,如果你将这些数据都放在一张表里面,毫无疑问,会死的很惨。这时候我们可以考虑和业务相关的方式来进行分表,比如说你有10000家客户,你可以每一百家客户放在一张表上,这样平均下来一天该表可能只能几十万条数据,这样是可以接受的。但是时间久了,你会发现之前的数据都是没有用的,客户关心的都是最新产生的数据,那么我们就可以分库,将这些客户不关心的数据放在这个冷库中,以提高线上热数据的查询效率。

结语

mysql优化的路还很长,当然以上这也不是全部的优化方案,但是会基本覆盖所有你在日常开发中能用到的优化小技巧,对于一般的面试官来说,足以吊打他了,但是我要提醒你的是,面对DBA,还是可以尽量乖一点,嗯,言至于此~

本文分享自微信公众号 - moon聊技术(onetraveller_llxz),作者:moon聊技术

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

原始发表时间:2020-11-03

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 搞懂这些SQL优化技巧,面试横着走

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL专栏目录 | 点击这里

    陈哈哈
  • MySQL - SQL优化干货总结(吐血版)

    BATJTMD等大厂的面试难度越来越高,但无论从大厂还是到小公司,一直未变的一个重点就是对SQL优化经验的考察。一提到数据库,先“说一说你对SQL优化的见解吧?...

    陈哈哈
  • 一文搞懂MySQL体系架构!!

    作者个人研发的在高并发场景下,提供的简单、稳定、可扩展的延迟消息队列框架,具有精准的定时任务和延迟队列处理功能。自开源半年多以来,已成功为十几家中小型企业提供了...

    冰河
  • 搞清楚这 10 几个后端面试问题,工作稳了!

    面试过程是一个由浅入深的过程,面试官先给求职者抛出一个相对简单的问题,然后通过一环套一环的追问深入考察求职者对知识点的理解掌握程度。

    JAVA日知录
  • 干掉 Navicat!这款官方客户端到底行不行?

    无论是开发、还是运维,选择一个好的工具,不仅能用的舒服,还能在很大程度上提高我们的工作生产效率。对于数据库的管理工具,在这之前我们也介绍过很多:介绍一款免费好用...

    民工哥
  • 不能用 + 拼接字符串? 这次我要吊打面试官!

    好久没维护《吊打面试官》系列了,今天再来一篇,这次真的要吊打了,哈哈!(看往期吊打系列请在后台回复:吊打,我会陆续更新……)

    Java技术栈
  • 《吊打面试官》系列-Redis终章_凛冬将至 FPX_新王登基

    Redis在互联网技术存储方面使用如此广泛,几乎所有的后端技术面试官都要在Redis的使用和原理方面对小伙伴们进行360°的刁难。作为一个在互联网公司面一次拿一...

    敖丙
  • TiDB 团队:一群无法抑制内心技术骚动的人 | PingCAP 招聘季

    简单来说,TiDB 是一个分布式高可用且能够水平扩展的关系型数据库,这个数据库的内核包含三个组件,其中的 SQL 层组件的名字也叫做 TiDB。这个组件负责所有...

    PingCAP
  • 可以拿来吊打面试官的 SQL Join (三)

    我在这个系列中,所分享的知识,力求逻辑严谨,实战辅证。但一如所有的文章一样,读者需要自己思考,是否正确无误,是否可以拿来直接作用于生产环境。对于没有理解透彻,就...

    Lenis
  • 如何防止MySQL数据库升级后性能下降|Vol 15

    首先来说MySQL升级后性能下降,在我从事MySQL DBA这10多年中也遇到几次,而且排查难度比较大。这里给大家提供一个MySQL升级管管理方案供大家参考。内...

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

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

    纯洁的微笑
  • 上课一不小心讲了年入百万起的项目MySQL10|#14

    在乙方工作的学生,总会遇到特别稀奇古怪的问题。由于从事数据库培训及咨询服务,有时也给学生担任起二级技术支持的作用(response slow), 前...

    wubx
  • 大数据和云计算技术周报(第167期)

    本文内容讲解了关于网易云音乐基于Flink实时数仓实践,包括实时数仓版本的演进过程,具体实现和最佳实践。

    大数据和云计算技术
  • 《吊打面试官》系列-Redis哨兵、持久化、主从、手撕LRU

    Redis在互联网技术存储方面使用如此广泛,几乎所有的后端技术面试官都要在Redis的使用和原理方面对小伙伴们进行360°的刁难。作为一个在互联网公司面一次拿一...

    Java3y
  • 专业解决 MySQL 查询速度慢与性能差!

    https://segmentfault.com/a/1190000013672421

    Java技术栈
  • 「mysql优化专题」主从复制面试宝典!面试官都没你懂得多!(11)

    主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库一般是准实时的业务数据库。

    java进阶架构师
  • 硬核推荐!Mac电脑必备软件/工具!

    昨天晚上下班之后,突然想整理一下自己离不开的哪些 Mac 软件,想着以后有个记录也方便自己日后查阅。之前我也整理过,不过都不是很全面,就是碰到一两个觉得还不错的...

    Guide哥
  • SELECT * 效率低,面试官:为什么

    陈小哈:“SELECT * 它好像比写指定列名多一次全表查询吧,还多查了一些无用的字段。”

    Java小咖秀
  • 为什么大家都说 SELECT * 效率低

    来源:blog.csdn.net/qq_39390545/article/details/106766965

    java进阶架构师

扫码关注云+社区

领取腾讯云代金券