为什么你的MySQL跑得很慢？(2)

发布时间：2021-01-04 22:07 所属栏目：53 来源：网络整理

导读：顺序IO永远是更快的.在数据库设计里,决定你是不是牛B的DBA或牛B的架构师,就是看你能否把一个业务尽可能设计为顺序IO,同时减少随机IO.举个例子：一个好友关系的业务,设计的时候希望一个query以顺序IO把好友关系就拿

顺序IO永远是更快的.在数据库设计里,决定你是不是牛B的DBA或牛B的架构师,就是看你能否把一个业务尽可能设计为顺序IO,同时减少随机IO.举个例子：一个好友关系的业务,设计的时候希望一个query以顺序IO把好友关系就拿出来,那么怎么设计呢?

那在MySQL的InnoDB里面,我们可以利用InnoDB的一个特性：聚集索引表.(类似Oracle的IOT).

利用这个特性,可以让用户的好友数据尽可能的聚集在一个page里或多个相邻的page里.那读的时候一个顺序读IO就能搞定了,性能大大提高.

好友关系表结构如下(前提表是InnoDB引擎)：

owner_id ? ?friend_id(好友id)

上面这样的两个字段做一个主键,InnoDB的主键就是聚集索引,那读取这两个字段肯定顺序IO就能搞定.

以前有什么数据库设计的书上,总说到,每个表上必须添加一个自增的主键的规范,其实规范死的,应对是活的,我上面举例的好友关系?就没有用自增的主键,而是具有业务属性读取又频繁的两个业务字段作主键,反而性能更好.

因此,大家学习,不要去死记这些书上的什么规范和章程,而是应该真正学懂一个东西的原理,比如学好InnoDB的内部原理,然后在实际工作中,有原理的支撑,用原理去举一反三.

InnoDB的原理是很大的一块知识,需要日积月累的学习.大家可以多留意我的公众号,陆续会有InnoDB的一些文章推出来.

OLTP业务更多的需要随机IO

可以利用内存做缓存,从而减少随机IO

OLAP业务更多需要顺序IO

内存缓存作用不大

MySQL5.6之前是不支持修改page的,默认就是16K.

MySQL5.6以后可以改了,这个参数是innodb_page_size,但是MySQL5.6也只能修改为8K或4K,不能调大,直到MySQL5.7以上才可以改大为32K或64K.

对OLAP系统来说,更大的page,对性能的提升会有所帮助,因为OLAP系统都是比较大的查询,扫描的数据很多.

第二点：数据库设计不好

比如用了很多的数据库特性,像Trigger,?分区,非常多的存储过程、函数等等.

我们经常说什么,小而美,意思就是简单才是最好的.你把数据库的所有功能都用上了,数据库的性能自然就会被拖慢,可能碰到的BUG,底层故障的几率也就增加了.

所以大家要明白,一个好的数据库项目设计,是小而美,精而简的.另外,数据库也只是整体项目的一部分,存储过程这些能实现的,在整体项目里面肯定也可以用应用程序代码来完成.

所以,我们用MySQL,就是用它厉害的地方,比如：表、索引、事务这些,而不是要它所有的功能都得用上.

另外有一点,在MySQL5.6之前,生产环境的主库里面是不允许使用子查询的.

MySQL5.6之前子查询的性能特别差.(语法上是支持的,但SQL性能非常差).

比如大家现在如果是用Oracle,想把Oracle迁移到MySQL上的话,建议大家用MySQL5.6版本,MySQL5.6对子查询的支持和性能上都做了较大的改善.

MySQL5.6跑子查询的性能会大大提高.

第三点：程序写太烂

这个估计当过DBA的同学应该都是有体会的,中小型的公司,程序员水平参差不齐.

特别是碰到很多刚入行的程序员(刚毕业的),更有可能,这些刚入行的程序员手里还接了一些进度非常赶的需求.?那这种环境下开发出来的程序,想不烂都很难了.

当然,这也不怪我们的程序员,不能怪罪他们.

造成我上述现象的原因,主要还是国内的开发环境,也没办法,开发需求迫切(产品天天催活),程序员忙于赶工(长期加班),只能忙与实现业务程序,根本没时间去优化程序.

当然,这种环境下,对于我们DBA来说就是机会了.程序员写出来的烂SQL,复杂SQL,造成系统缓慢甚至崩溃,然后我们DBA出马,对这些烂SQL,慢SQL进行优化改造后,系统恢复正常,并日趋稳定. 这也是很有成就,并且也会受到同事和领导尊重的一件事.

同时,DBA们也可以加强对程序员的培训,加强他们快速写出好SQL的能力.让他们花较少的时间,也能写出性能比较好,更得顺畅的SQL语句.?这样,也可以给DBA减轻负担.

我本人就比较喜欢跟程序员讲培训,一来大家交流技术,都有收获,二来搞好关系,工作上有什么事以后需要协商的也好聊.这比请他们吃饭强.

我们针对程序写得太烂,主要有下面几个解决方向：

要让应用使用数据库连接池,特别是像基于JAVA开发的大型高并发应用里,一定要使用连接池.

使用连接池的好处：就是可以限制应用的连接数,另外,不用再额外地去创建每个连接,MySQL创建连接的开销也是较大的,因为创建一个新连接相当于MySQL创建了一个thread.

刚才我也提到,MySQL随着连接数上升会出现性能下降.

有写过程序代码的同学,应该也知道,在我们一般的PC笔记本上(一般4CORE),你创建400个thread,每个thread就干1+1+1+1+..简单活,再sleep下,你看看你的PC电脑卡还是不卡.你会发现你PC电脑的CPU都快跑满了.你要敢创建600个thread,那你的机器就快等着重启吧.这就是因为thread的开销,把CPU已经占满了.

复杂的SQL语句

这个刚才也说了,程序员写的SQL,一般都问题多多,他们毕竟太忙了,不会去考虑这个SQL的性能和运行情况.在一些情况下,程序员拼接的SQL,直接可以把整个系统干跨掉.

我举个简单例子：我们一个应用对数据库创建了10个连接(最大连接数=10),这10个连接?每个连接都同时跑相同的一条复杂SQL,执行这个复杂SQL至少要10分钟,那这10个连接?在10分钟以内都只能执行这个复杂?SQL,其他后面的SQL全得堵着.

造成10分钟大部分应用不可用了,对吧.而且有可能引起雪崩,造成系统崩溃.

复杂SQL的优化,也是DBA很重要的一个活,需要通过监控的手段找出这些复杂SQL、慢SQL、烂SQL,然后给出优化建议到程序员(DBA要进行性能对比测试),让程序员改造下代码,才能让系统真正畅快并行地跑起来,像不堵车的高速公路一样.

那有人会问了,我们公司的程序员就是牛B,打死不改SQL代码,弄死了也不去优化,无法沟通.那我们该怎么办呢?

我们还是有办法的,我们还可以构建一个专用的从库(Slave库)来处理,你换个库查询,总可以了吧.

比如举我们公司的例子,我们的后台出报表的系统,就是连的从库查询,不给连主库.

无效逻辑

全表扫描

比如：update?t?set?a?=?a?+?1?;?忘加where条件了.

以你要想你的系统能支撑百万级别的用户在线,那还得加入SQL审核系统(SQL?Review),杜绝无效逻辑的SQL,和这类全表扫描的SQL.

SQL经过DBA审核通过后,才能发布上线.

另外,这种大的update SQL应该分批更新,把大的SQL任务拆成小的任务来跑.在MySQL里面来说,这是要特别注意的.

为什么要分批更新呢?

原因1.?上面说的,MySQL的一个query只能用到一个CORE.SQL事务太大,复杂度太高需要很久才能运行出来,容易造成拥堵.

原因2.?线上环境,MySQL一般都是Master/Slave架构,如果Master发生100W行的大更新事务,很可能造成SLAVE卡在那里,因为SLAVE是单线程结构,造成同步延迟.

MySQL写SQL,干成小事务SQL,快速执行,快速提交.让每个query完成得更快,让连接更快地释放出来.

最后,回到我们的标题,为什么你会觉得MySQL跑得很慢,相信你应该多少有了点答案.

文/?黄稚禹

文章来自DBAplus社群

（编辑：ASP站长网）