MySQL优化注意事项

Posted on

在操作数据库的时候,需要我们对mysql进行优化处理,本篇讲讲有优化的注意事项。

第一点,硬件太老
硬件我们这里主要从CPU、内存、磁盘三个方面来说下,还有一些因素比如网卡,机房网络等因为文章篇幅关系,就不一一介绍了,以后还有机会可以聊。
首先我们来看下MySQL对CPU的利用特点:
5.1可以利用4个核,5.5可以利用到24个核,5.6可以利用到64个核
比如MySQL5.6能用到48个CORE以上,跑得好的,64个CORE都能用到(48CORE-64CORE之间,官方公布48个CORE,我实际测试能跑到64个CORE)。
MySQL 5.6 可以用到48 core+
*MySQL 5.1 前最多可以用到4个核 **
现在一般的生产环境服务器,都是32CORE以上。
所以我这里都推荐大家尽量得去用MySQL5.5或MySQL5.6, 除非你们公司的服务器一直用的很老旧的服务器,只有4个核,或1个核。
因为5.1以前(5.0一样)都是在内部代码里写死了,是基于innobase的存储引擎,数据库对硬件的利用率较差。 后面演进为了InnoDB引擎后,好了很多。
每个连接一个是一个线程(非thread pool),每个query只能使用到一个核
另外,在MySQL每个query只能用到一个CPU。
Oracle里面用并行SQL,并行查询,这类功能在MySQL里是不存在的。
无执行计划缓存(无SQL执行计划预编译)
其次,MySQL内部没有SQL预编译。因此不存在像Oracle内存结构里的library cache(库缓存)这类结构体。所以,MySQL只有硬解析,不存在什么软解析,更不存在什么软软解析。
MySQL随着连接数上升会出现性能下降
这个也是MySQL的一个硬伤,但是随着MySQL的版本演进,还是出现了很多解决方法。
比如:官方推出的thread pool(线程池),简称TP。就是为了解决并发连接数过高的问题,不过这属于MySQL额外的组件,官方的TP是需要额外花钱购买的。
另外,国内有个叫楼方鑫的,开发了一个OneSQL的中间件,也是解决类似问题的。
有Result缓存,但比较鸡肋
MySQL里也有类似Oracle里的结果缓存,叫Query Cache,但属于比较鸡肋的功能,很少使用。
因为大部分实际的生产环境都是OLTP系统,存在频繁的更新修改操作,这个Query Cache用在数据频繁更新修改的环境里,会使MySQL的性能严重下降,因此,一般很少使用。
现在用MySQL,基本都是用InnoDB存储引擎,以前的MyISAM这些引擎也用得很少了。(什么是存储引擎?这个不知道的话,你可以gg了)
InnoDB引擎是完全没有必要去开启这个Query Cache的,因为本身就是一个事务型的存储引擎,用InnoDB就是用它的事务处理能力,肯定会发生频繁的数据更新和修改嘛。
再次来看下MySQL对内存利用特点
64位操作系统的服务器可利用内存((2^64-1)/1024/1024/1024)G
在高速并发环境,基本是靠内存缓存来减少对磁盘的IO冲击
通常内存按实际数据的15%-20%规划,如果特别热的数据,需要考虑更大的比例来缓存数据
这15%-20%的数据我们通常又叫做热数据。(这也是通常的一个经验值)
比如你评估出你这台MySQL数据总量大概在500G左右,那MySQL要给到的内存可能就是75G(5000.15),那你可能需要一台128G左右内存的服务器。
另外有些业务还会存在特别热、大量热的数据(大大超出15%-20%这个区间,也是有可能的),比如:QQ农场。
相信大家都玩过以前那种偷菜的游戏,QQ农场,开心农场之类的。(还有订票的12306网站)。
这类业务在我们业界里面都是属于关注度很高的,这类业务的特点,数据热的时候,基本100%都是热数据,比如:QQ农场大家玩的时候,每天都上来玩的,每隔一会儿就上来偷把菜,很多人半夜起来上厕所起来都要偷一把菜。
所以这类业务的MySQL数据库,内存配备还得加高。 15-20%还不够。
总结:****一般的业务15%-20%来规划热数据,比如:用户中心,订单之类的常见业务。另外一些特殊点的业务,具体情况具体分析。
可以根据Query响应时间来做指导分配
我们在做这种大型在线架构-大型数据库规划设计的时候,
SQL查询的响应时间也是一个非常重要的指标。
在这种大型系统里面,要承载数百万甚至千万级别用户同时在线进行业务,SQL查询(query)的响应时间是必须去严格把控,必须把你这套系统的Query响应时间控制在多少时间以内。
比如我们的核心库,我就要求Query的响应时间(平均响应)在30ms以下。超过30ms,我们就认为这个数据库可能达到承载极限,需要对这个数据库进行扩容了。
另外,要对这个Query响应时间进行长期的指标监控。
这个是核心库,如果另外一些不太重要的辅助库,比如放日志的库,或者说一些性能要求本身不是太高的库,我们可以放宽点这个Query响应时间,放宽到1秒或2秒内。
根据业务的重要等级程度来定这个Query响应时间的阀值。
这是一个很重要的指导思想,根据Query响应时间来规划你的性能容量。
容量分两种:性能容量和空间容量。 空间容量很简单,就是放多少SIZE数据,几个T。
性能容量是更重要的,决定能否接住你的业务压力和承载。
大家要记住:你如果要抗的业务是百万级别的活跃用户,不是几百个用户的话,性能才是王道,性能上满足业务的需求才是最重要的。
你功能再牛B,产品再好,性能抗不了,其他都是扯淡,几百W人可能在几秒钟内就把你的整个系统和项目都搞挂掉,然后你们公司就抓瞎了。
苦心经营的用户也会大量流失,损失就惨重了。
性能是基础。性能能抗住,整个架构才有意义。性能抗不住,后面去考虑什么高可用,这些都没用。
MySQL对磁盘的利用特点
Binlog,redo log ,undo log顺序IO
MySQL的IO类型多种多样。
binlog,redolog,undolog,这些都是顺序IO写。
这一类东西没太多必要放到SSD上,顺序写在传统机械盘上也是很快的,放到SSD上有点暴殄天物,而且SSD存在写损耗和写寿命的问题,没必要放到SSD上。放到传统的SAS盘上就够用了。没必要放SSD。
SSD用来放datafile。因为datafile上发生的IO大部分是随机IO,SSD跑随机IO是非常有优势的。SSD固态盘+传统盘SAS盘一起混合存储。另外,备份盘也不要用SSD。
Datafile随机IO和顺序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,底层故障的几率也就增加了。
所以大家要明白,一个好的数据库项目设计,是小而美,精而简的。另外,数据库也只是整体项目的一部分,像Trigger,存储过程这些能实现的,在整体项目里面肯定也可以用应用程序代码来完成。
所以,我们用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优化的注意事项作出了讲解,更多相关知识请关注php中文网。

相关推荐:

Discuz!X/数据库 DB:: 函数操作方法

ThinkPHP框架String类详解

JS基础-Math数组Date

以上就是MySQL优化注意事项的详细内容,更多请关注php中文网其它相关文章!

该文章由WP-AutoPost插件自动采集发布

原文地址:http://www.php.cn/php-weizijiaocheng-397372.html

Leave a Reply

Your email address will not be published. Required fields are marked *