mysql性能优化总结(一)

一.什么决定双11大促的成败?

场景:原数据库架构为1M15S,M服务器性能很好,64核512G内存,IO采用fushion IO,比一般磁盘的读写能力高很多.

缺点:

　　1.只有一个主库,没有主从切换中间件,每次如果主库挂了,需要运维人员手动进行切换,然后同步给其他从库,耗时高(超过半小时不可用)

　　2.双十一凌晨2点半数据库磁盘IO达到峰值,造成恐慌,担心性能为急剧下降.经检查,发现是数据库远程备份定时任务在跑,总结:最好不要在主库上进行数据库备份,大型活动前取消这类计划.

 

对数据库性能影响比较大的因素:

　　(1)sql性能

　　(2)网卡流量

　　(3)服务器硬件

　　(4)磁盘IO

　　(5)大表

　　(6)大事务

 

数据库面临的风险:

　　(1)超高的qps和tps

　　　　根据经验,数据库性能80%都是由慢查询导致的.大多数据数据库问题都可以通过sql性能优化来解决.

　　　　当前的mysql不支持单sql语句多cpu并发计算,一条sql只能由一个cpu去执行.10ms的sql和100ms的sql性能直接相差10倍.

　　(2)大量的并发和超高的cpu使用率

　　　　大量并发:数据库连接被占满.当并发数大于最大连接数时,出现500错误,对用户体验造成影响.

　　　　　　PS:并发数和连接数的区别:并发是指同时请求mysql的数量,连接数是指mysql的连接数,通常连接数远大于并发数,因为大部分的连接都是出于sleep状态.类似于http长连接.

　　　　　　数据库最大连接数:max_connections,默认值为100.所以需要把这个参数改的大一些.

　　　　超高的cpu使用率:可能导致cpu资源耗尽而宕机

　　(3)磁盘

　　　　1.IO性能突然下降

　　　　　　常发生在热数据量远远大于服务器可用内存的情况下(使用更快的磁盘设备来解决.比如fashion io,SSD,更好的raid卡)

　　　　2.其他大量损耗磁盘性能的计划任务(调整计划任务,做好磁盘维护)

　　(4)网卡流量(1000Mb/8≈100MB)

　　　　流量高峰期,千兆网卡多次被跑满,其他请求会出现无法连接数据库.　

　　　　解决办法:

　　　　　　1.减少从数据库的数量　　　

　　　　　　2.进行分级缓存,避免大量的请求突然对数据库形成冲击

　　　　　　3.避免使用SELECT * 进行查询,查询出没有必要的列会浪费网络流量

　　　　　　4.分离业务网络和服务器网络,避免主从同步或者备份等作业影响网络性能.

 

什么是大表:

　　1.记录行数巨大,单表达到千万行

　　　　根据场景灵活应用,比如日志表,只有insert操作和少量的select操作,几乎没有update操作和delete操作,这样的表就算超过了千万行,也不会对业务产生什么影响.实际工作中有遇到过,日志表的行数已经超过10个亿了,一直很稳定.但是在增加列的时候,会非常痛苦(为什　么?)

　　2.数据文件巨大,表数据文件超过10G

 

大表对查询的影响:

　　1.慢查询:大表意味着很难在一定的时间内,过滤出所需要的的数据

　　　　案例:此超过10E行的日志表,之前一直无人问津,直到有一天,运营人员提出需要查询表中订单来源(order_from,1公司网站 2京东 3淘宝 4微信)的字段,在大促中此条慢sql直接把数据库搞死了.

　　2.对DDL操作的影响

　　　　(1)建立索引需要花费很长的时间

　　　　　　MYSQL版本<5.5,建立索引会锁表

　　　　　　MYSQL版本>=5.5,不会锁表,但是会引起主从延迟

　　　　(2)修改表结构需要长时间锁表

　　　　　　风险:

　　　　　　　　1.会造成长时间的主从延迟.假设主库修改需要480s,mysql5.6之前主从同步使用的是单线程,主库执行完之后才会同步到从库执行相同的操作,所以从库也至少需要480s来完成同步,也就造成了480s的主从延迟,这对于大多数业务来说都是不可接受的.

　　　　　　　　　5.6之后,支持多线程复制,但是也有一定的限制.

　　　　　　　　2.影响正常的数据库操作(锁表,其他操作被阻塞),导致数据库连接数猛增.超过最大连接数后,前台出现500错误

 

 如何解决大表问题:

　　1.分库分表

　　2.进行历史数据归档

　　　　难点:

　　　　　　　(1)归档是时间点的选择

　　　　　　　(2)如何进行归档操作(大表中删除大量数据,需要注意方式方法,否则轻则造成主从延迟,重则影响用户正常操作)

 

大事务对性能的影响

　　定义:运行时间较长,操作的数据比较多的事务

　　风险:

　　　　锁定太多的数据,造成大量的阻塞和锁超时.

　　　　回滚时所需时间比较长(有可能比执行事务所花费的时间更长)

　　　　执行时间长,容易造成主从延迟

　　解决方法:

　　　　1.避免一次性操作太多的数据

　　　　2.移出不必要在事务中的SELECT操作

 

影响性能的几个方面:

　　1.计算机硬件,如cpu,内存,磁盘io

　　2.操作系统,windows xp系统默认的并发数只有10个.服务器性能调优

　　3.mysql的存储引擎

　　　　MyISAM:不支持事务,表级锁

　　　　InnoDB:事务级存储引擎,完美支持行级锁,事务ACID特性.

 　　4.数据库参数配置

　　　　有些参数对数据库性能的影响是决定性的.

　　　　前面三个方面的影响加起来 < 数据库参数配置的影响 

　　5.数据库表结构设计和sql语句执行效率

　　　　慢查询是很多数据库性能低下的罪魁祸首.

　　　　很多慢查询是由于数据库表结构设计不合理导致的,这类问题一旦项目上线就很难再修改了.

　　　　重点是前期的优化设计及后期sql优化.

 

如何选择CPU?

　1.高的频率,更多的核心内核数.2者不可兼得时,WEB应用来说,内核数>频率.CPU密集型应用来说,由于mysql不支持多核并发计算同一条sql,所以频率也很重要.

　2.MYSQl版本也有影响,5.0之前的版本对于多核CPU的支持是很弱的,5.6,5.7之后做了很好的支持.

　3.注意不要出现64位的cpu使用32位的操作系统,大多由于误装,对性能限制很大.

 

 

如何选择内存?

　　1.数据库自带缓存策略,内存的大小直接影响数据库性能,特别是当热数据大小远远大于可用内存时.

　　　　　　MyIsam   

　　　　　　　　索引->内存　　

　　　　　　　　数据->OS

　　　　　　InnoDB

　　　　　　　　索引,数据->内存

　　2.内存并不是越大越好,当内存大小大于所有数据量大小,即所有的数据都已经被缓存在内存中,再增加内存就是没有意义的了.

　　3.内存的大小不仅对查询有效,对插入也一样有效.可以做到延缓插入,先缓存在内存中,然后批量插入,从而减少io操作次数,提高性能.

　　4.频率越高越好,选择主板能够支持的频率最高的内存条,然后单个内存条尽量的大,所有内存条型号保持完全一致.

 

 

磁盘的配置和选择

　　1.使用传统机器硬盘

　　　　最常见,使用最多,价格低,存储空间大,读写速度慢

　　　　如何选择?

　　　　　　1.存储容量

　　　　　　2.传输速度

　　　　　　3.访问时间

　　　　　　4.主轴转速(7500,15000)

　　　　　　5.物理尺寸(越小越快)

　　2.使用RAID增强传统机器硬盘的性能

　　　　定义:RAID是磁盘冗余队列的简称,吧多个容量较小的磁盘组成一组容量更大的磁盘,并提供数据冗余来保证数据完整性的技术

　　　　常见RAID组别:

　　　　　　(1)RAID 0:数据条带,串联,成本低,可以提高磁盘性能和吞吐量,没有提供冗余和错误修复.损坏率比单块磁盘更高,其中任何一块磁盘损坏都会造成数丢失.

　　　　　　(2)RAID 1:磁盘镜像,最大限度的保障可靠性和可修复性,磁盘利用率低(50%)

　　　　　　(3)RAID 5:分布式奇偶校验磁盘阵列,读快,写慢,磁盘利用率高,只需要额外一块磁盘.同时损坏两块以上则不可修复.

　　　　　　(4)RAID 10:先做RAID 1,在做RAID 0,读写性能好,恢复较RAID 5更简单,速度也更快. 

　　　　　　

　　3.使用固态存储SSD和PCIe卡

　　　　更好的随机读写性能

　　　　更好的支持并发

　　　　缺点:更容易损坏

　　　　SSD:使用SATA接口,同样可以使用RAID技术,由于带宽的限制,无法完全发挥闪存技术的性能

　　　　PCIe卡:无法使用SATA接口,需要特殊的驱动和配置,价格比SSD更贵,性能更好.Fusion-IO,会占用内存资源.

　　　　如果只有一块ssd,应该放在从服务器上,而不是主服务器上.

　　4.使用网络存储NAS和SAN

　　　　SAN和NAS是两种外部文件存储设备加载到服务器上的方法