MySQL优化小建议

时间:2021-08-22 23:25:27

背景

“那啥,你过来一下!”

“怎么了?我代码都单元测试了的,没出问题啊!”我一脸懵逼跑到运维大佬旁边。

“你看看!你看看!多少条报警,赶快优化一下!”

运维大佬短信列表里面50多条MySQL CPU 100%报警短信。再看看项目名称不就是我前几天刚发布的项目吗!?

我心底一沉,赶快赔上笑脸。“这个一定优化,马上优化!那个,能不能看下数据库监控日志...”

运维大佬又数落了我几句,然后调开了数据库监控日志。

那家伙...每秒300多的连接数,几乎快要封顶的全表扫描数,还有大红色CPU警报。。。

“那个,能不能看看nginx访问日志...我看下访问量...”我弱弱地说到。

运维大佬不情愿的跑了下下面的语句:

grep -c come access.log

come这个接口是其中一个请求量比较大的接口,结果是600多万。那个时候才中午,周末高峰期估计一天得有上千万吧,

我撇了撇嘴,心里想着这么高的请求量,当初那么抠门只给我一台低配数据库还好意思说,不过嘴上肯定是:“好好好,请求量不是很大,看来是数据库问题,我立刻去优化一下!”

“给它弄一个读写分离不就行了吗!?”这时另外一个运维大佬凑了过来,随意地挥了挥手。。。

你问我DBA去哪儿了?DBA当时有点忙,只说让我自己检查一下。。。

优化思路

我这个项目由于上线之前比较赶,所以前期并没有管数据库设计方面的一些问题,如今随着游戏接入,请求量剧增才暴露出来。(其实是前期加班加烦了懒得搞)

这个问题,并不需要增加数据库硬件配置和增加读写分离这种高端手段就能解决,我自个儿挖了多少坑,心里还是有点碧树的。

详细的MySQL优化步骤如下:

  • 检查数据表结构,改善不完善设计
  • 跑一遍主要业务,收集常用的数据库查询SQL
  • 分析查询SQL,适当拆分,添加索引等优化查询
  • 优化SQL的同时,优化代码逻辑
  • 添加本地缓存和redis缓存

这个项目是原生PHP写的,以上这些只能自己做了。

检查数据表结构

因为比较菜,回去看设计的表结构,真是惨不忍睹。

尽可能不要使用NULL值

因为建表的时候,如果不对创建的值设置默认值,MySQL都会设置默认为NULL。那么为啥用NULL不好呢?

  • NULL使得索引维护更加复杂,强烈建议对索引列设置NOT NULL
  • NOT IN!=等负向条件查询在有NULL值的情况下返回永远为空结果,查询容易出错
  • NULL列需要一个额外字节作为判断是否为NULL的标志位
  • 使用NULL时和该列其他的值可能不是同种类型,导致问题。(在不同的语言中表现不一样)
  • MySQL难以优化对可为NULL的列的查询

所以对于那些以前偷懒的字段,手动设置一个默认值吧,空字符串呀,0呀补上。

虽然这种方法对于MySQL的性能来说没有提升多少,但是这是一个好习惯,而且以小见大,不要忽略这些细节。

添加索引

对于经常查询的字段,请加上索引,有索引和没有索引的查询速度相差十倍甚至更多。

  • 一般来说,每张表都需要有一个主键id字段
  • 常用于查询的字段应该设置索引
  • varchar类型的字段,在建立索引的时候,最好指定长度
  • 查询有多个条件时,优先使用具有索引的条件
  • LIKE条件这样的模糊搜索对于字段索引是无效的,需要另外建立关键词索引来解决
  • 请尽量不要在数据库层面约束表和表之间的关系,这些表之间的依赖应该在代码层面去解决

当表和表之间有约束时,虽然增删查的SQL语句变简单了,但是带来的负面效果是插入等操作数据库都会去检查约束(虽然可以手动设置忽略约束),这样相当于把一些业务逻辑写到了数据库层,不便于维护。

优化表字段结构

数据库中那些可以用整形表示的数据就不要使用字符串类型,到底是用varchar还是char要看字段的可能值。

这种优化往往在数据库中有大量数据以后是不可行的,最好在数据库设计之前就设计好。

  • 对于那些可能值很有限的列,使用tinyint代替VARCHAR
    • 比如记录移动设备平台,只有两个值:android,ios,那么就可以使用0表示android,1表示ios,这种列一定要写好注释
    • 为什么不用ENUM呢?ENUM扩展困难,比如后来移动平台又增加了一个ipad,那岂不是懵逼了,而tinyint加个2就行,而且ENUM在代码里面处理起来特别奇怪,是当成整形呢还是字符串,各个语言不一样。
    • 这种方式,一定要在数据库注释或者代码里面写明各个值的含义
  • 对于那些定长字符串,可以使用char,比如邮编,总是5位
  • 对于那些长度未知的字符串,使用varchar
  • 不要滥用bigint,比如记录文章数目的表id字段,用int就行了,21亿篇文章上限够了
  • 适当打破数据库范式添加冗余字段,避免查询时的表连接

查询的时候,肯定int类型比varchar快,因为整数的比较直接调用底层运算器就可以实现,而字符串比较要逐个字符比较。

定长数据比变长数据查询快,因为比较定长数据与数据之间的偏移是固定的,很容易计算下一个数据的偏移。而变长数据则还需要多一步去查询下一个数据的偏移量。不过。定长数据可能会浪费更多的存储空间。

大表拆分

对于那些数据量可能近期会超过500W或者增长很快的表,一定要提前做好垂直分表或者水平分表,当数据量超过百万以后,查询速度会明显下降。

分库分表尽量在数据库设计初期敲定方案,否则后期会极大增加代码复杂性而且不易更改。

垂直分表是按照日期等外部变量进行分表,水平分表是按照表中的某些字段关系,使用hash映射等分表。

分库分表的前提条件是在执行查询语句之前,已经知道需要查询的数据可能会落在哪一个分库和哪一个分表中。

优化查询语句

这个才是很多系统数据库瓶颈的始作俑者。

  • 请尽量使用简单的查询,避免使用表链接
  • 请尽量避免全表扫描,会造成全表扫描的语句包括但不限于:
    • where子句条件恒真或为空
    • 使用LIKE
    • 使用不等操作符(<>、!=)
    • 查询含有is null的列
    • 在非索引列上使用or
  • 多条件查询时,请把简单查询条件或者索引列查询置于前面
  • 请尽量指定需要查询的列,不要偷懒使用select *
    • 如果不指定,一方面会返回多余的数据,占用宽带等
    • 另一方面MySQL执行查询的时候,没有字段时会先去查询表结构有哪些字段
  • 大写的查询关键字比小写快一点点
  • 使用子查询会创建临时表,会比链接(JOIN)和联合(UNION)稍慢
  • 在索引字段上查询尽量不要使用数据库函数,不便于缓存查询结果
  • 当只要一行数据时,请使用LIMIT 1,如果数据过多,请适当设定LIMIT,分页查询
  • 千万不要 ORDER BY RAND(),性能极低

上面是我总结的一些小tips,这些规则是死的,但是业务场景是活的,在实际使用的过程中,比如数据统计,可以适当牺牲性能换取便利。

添加缓存

使用redis等缓存,还有本地文件缓存等,可以极大地减少数据库查询次数。缓存这个东西,一定要分析自己系统的数据特点,适当选择。

  • 对于一些常用的数据,比如配置信息等,可以放在缓存中
  • 可以在本地缓存数据库的表结构
  • 缓存的数据一定要注意及时更新,还有设置有效期
  • 增加缓存务必会增加系统复杂性,一定要注意权衡

优化实例

下面举几个简单的优化查询例子。首先就是跑一下主要业务,把主要的查询语句打印到一个文件里面,然后分析这些语句。

补充一下,在查询语句前使用关键字explain可以查看查询执行的具体情况。

看下面的这个查询语句

select *
from link
where player_id='15298635' AND gameid='10389' AND appid='200'
AND action='open' AND creator='android_sdk' AND transport='{"name":"uusama","age":20}'

上面这条语句毛病挺多的

  • select * 没有指定查询列,这个表有20个字段,其实我用到的就几个
  • 查询列没有索引,造成全表扫描
  • 查询条件过于冗余,可以适当拆分
  • 只需要一条查询结果,但是没有限定查询结果大小

显然查询条件很多,而且很多列都是不定长的varchar类型,如果要建立索引,是不是要建立联合索引呢?

显然没有必要,索引的字段越多,MySQL维护的时候越复杂,对性能也会有损耗,像这样的SQL查询语句,我们在主要字段上建立索引即可。比如在player_id字段、gameid字段、appid字段上建立索引就够了。

这样的查询语句要结合具体的业务场景来进行分析,比如在我当前的系统中,我是期望上面的语句能够查询相同的参数下是否有记录。其实没必要使用这么多条件的查询。

我只需要使用下面的这条更简单的查询语句代替即可。

select id,player_id
from link
where player_id='15298635'

查询到的记录条数在100条以下,大部分就只用几十条记录,我完全可以在代码里面在把查询结果遍历一遍判断即可。这样不知道有多快呢!

再看下面的这个例子:

select *
from browser
where device_id='52' AND created>='1513735322' order by id desc

我只是想查一下这个表里面某个时间以后的数据。问题大了!

created字段是timestamp类型,这样用是不对的,而且没有限定行数,这条语句会把数据库所有的device_id='52'的数据搞出来。

还好device_id字段设置了索引,要不然必然会导致全表扫描。

修改后的查询如下:

select *
from browser
where device_id='52' AND created>='2018-03-27 00:00:00' order by id desc

我的项目中并没有使用复杂的像表连接和联合这样的查询,这类查询一定要谨慎使用,能够拆分的话尽量拆分。

记住下面的速度优先级,两两之间相差2个以上数量级

CPU运行速度 > 内存访问速度 > 磁盘io访问速度 > 网络请求速度

本文已同步到个人博客

MySQL优化小建议的更多相关文章

  1. extjs 优化小建议

    1 原文信息 原文标题: Sencha Con 2013: Ext JS Performance tips 原文地址: [http://edspencer.net/2013/07/19/sencha- ...

  2. MySQL数据库优化小建议

    背景 “那啥,你过来一下!” “怎么了?我代码都单元测试了的,没出问题啊!”我一脸懵逼跑到运维大佬旁边. “你看看!你看看!多少条报警,赶快优化一下!”运维大佬短信列表里面好多MySQL CPU 10 ...

  3. mysql优化小技巧

    对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引.主键索引.唯一索引unique.全文索引] c: 分表技术(水平 ...

  4. MySQL 优化小技巧

    碎片整理: mysql数据一开始是在磁盘上顺序存放的,如果数据表有频繁的update改动,那么数据就会形成很多碎片,拖慢速度和不利于索引: 优化碎片有两种方式: alter table user en ...

  5. redis优化小建议

    1.优化的一些小建议 1.尽量使用短的key 当然在精简的同时,不要为了key的"见名知意".对于value有些也可精简,比如性别使用0.1. 2.每个redis设置合理内存 每个 ...

  6. MySQL优化小方法

    一.查询优化 1.尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引: 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而 ...

  7. 六,mysql优化——小知识点

    1,选择适当的字段类型,特别是主键 选择字段的一般原则是保小不保大,能占用字节小的字段就不用大字段.比如主键,建议使用自增类型,这样节省空间,空间就是效率!按4个字节和按32个字节定位一条记录,谁快谁 ...

  8. MySQL优化小案例:key&lowbar;buffer&lowbar;size

    key_buffer_size是对MyISAM表性能影响最大的一个参数,下面一台以MyISAM为主要存储引擎服务器的配置: mysql> SHOW VARIABLES LIKE '%key_bu ...

  9. MySQL优化小案例:连接数

    错误代码:MySQL: ERROR 1040: Too many connections 经常会遇到这个错误,要么是业务增长,正常的访问量增多,要么是自己的max_connections设置的过小了 ...

随机推荐

  1. 电脑右键新建文本文档&lpar;txt&rpar;消失的解决办法

    其实只需要一个注册表就可以了 下载地址http://pan.baidu.com/s/1hr7r0fM 拿走不谢! 注册表的内容是这样的,你也可以新建一个文件把后缀名改成.reg然后把下面的内容copy ...

  2. 1319-n皇后问题

    描述 在n×n 格的棋盘上放置彼此不受攻击的n 个皇后.按照国际象棋的规则,皇后可以攻击与之处在同一行或同一列或同一斜线上的棋子.n后问题等价于在n×n格的棋盘上放置n个皇后,任何2 个皇后不放在同一 ...

  3. 【转】Could not write file XXX&bsol;&period;classpath解决

    原文网址:http://www.sjsjw.com/kf_other/article/323_11877_12218.asp 环境 MyEclipse 8.6 + Windows 7 Ultimate ...

  4. &lbrack;SDOI 2008&rsqb;仪仗队

    Description 作为体育委员,C君负责这次运动会仪仗队的训练.仪仗队是由学生组成的N * N的方阵,为了保证队伍在行进中整齐划一,C君会跟在仪仗队的左后方,根据其视线所及的学生人数来判断队伍是 ...

  5. 在controller中将timestamp类型的数据通过toString&lpar;&rpar;方法变成字符串

    然后在miniui里面将dateFormat="yyyy-MM-dd",变成想要的格式.

  6. Java基础知识点总结

    前言 本文主要是我之前复习Java基础原理过程中写的Java基础知识点总结.Java的知识点其实非常多,并且有些知识点比较难以理解,有时候我们自以为理解了某些内容,其实可能只是停留在表面上,没有理解其 ...

  7. 2018 vue前端面试题

    1.active-class是哪个组件的属性?嵌套路由怎么定义?答:vue-router模块的router-link组件. 2.怎么定义vue-router的动态路由?怎么获取传过来的动态参数? 答: ...

  8. &lbrack;Winform&rsqb;js与webbrowser交互

    摘要 目前项目中采用的方式是内嵌浏览器的方式,打开本地或者互联网上的h5页面.在开发之前做了一下调研.目前常用的在C#封装的浏览器内核中,Chromium 内核封装有Xilium.Cefglue.Ce ...

  9. pycaffe编译

    环境:ubuntu14.04  python2.7  caffe已经成功编译 1,首先确保pip已经安装 sudo apt-get install python-pip 2,在caffe-master ...

  10. Framework连接oracle数据库以及Cognos服务器出现错误

     1:Framework连接oracle数据库时出现下面错误信息 环境: win2008R2 cognos10.2.1, 服务器上已经安装oracle11.2 content manager连接的也是 ...