Jamzy Wang

life is a struggle,be willing to do,be happy to bear~~~

MySQL数据库性能优化

2014-09-21 21:52

原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。

在实际开发中,除了service架构本身导致的性能瓶颈之外,数据库的性能瓶颈时最常见的系统问题了,作为非专业的DBA,数据库性能的优化对于多数RD来讲可能都不是那么的擅长,本文将对MySQL的通用优化方法做个简单的总结。

MySQL的优化可以分为好几个层面,有硬件层相关的优化(比如用SSD 替换机械硬盘)、系统层相关的优化、MySQL层相关的优化,其中MySQL层的优化又可以分为库表设计优化、查询优化、参数优化等等,本文将重点阐述常见的MySQL层的相关优化。

库表设计优化

数据库系统的性能不是优化出来的,更多的是设计出来的。没有固定的适合所有常见的方法,有一些基本的设计原则可供参考,通常,在做存储设计之前,需要考虑以下一些问题:

1
2
3
4
5
6
7
数据的容量,以及每条记录的预估大小;
数据字段的冷热;
哪些字段经常用作where查询;
哪些字段是经常更新的;
SQLselectupdatedeleteinsert的数据统计;
那些关联是必要的;
并发量大小

1. 选择合适的数据类型

1
2
3
4
5
6
1) 表字段定义时最好的策略:只分配真正需要的空间,在满足需求的条件下,数值类字段尽量使用占用空间小的类型
2) 使用UNSIGNED存储非负整数
3) 字符类字段,只分配所需要的空间长度
4) 字符类的字段,如果能转换为数值类存储,就用数值类代替,比如IP
5) 如果字段涉及索引,尽量使用NOT NULL
6) 少用并拆分TEXT/BLOB等大字段到单独的表

2. 总体设计上做到第三范式,适当冗余

平衡范式与冗余,没有绝对的对与错,可以适当牺牲范式,适度的冗余可以避免关联查询,减少join查询。加入冗余在细节上,但是冗余的字段一定要小。

3. 控制核心表的字段数量、单表数据量

减少数据量。除了减少字段长度外,可考虑数据压缩。比如MyISAM表的压缩操作,压缩后对磁盘利用率,IO缓存等都有明显的性能提升。读写分离、拆库拆表、增加从库数量

4. 选择适当的存储引擎

业务是偏重于数据存储,还是数据并发访问,不同的应用背景可能会使用不同的存储引擎。或者在系统运行一段时间后,根据数据库对select、insert、update、delete的统计信息来调整。推荐使用InnoDB引擎。

5. 建立合适的索引

索引可以增加访问速度,但是也会降低写入速度,同时索引很可能需要很大的存储空间。所以,在满足要求的情况下,索引越少越好。同时索引长度的也应该关注,尽量减少索引长度

Query优化

SQL编写规范:

1
2
3
4
5
6
7
8
9
SELECT语句只获取需要的字段,禁止使用select *
字段中的同一字段的or要转换为in形式,但是SQL语句中IN包含的值不应过多,尽量控制在200个以内
WHERE条件中传入的值必须使用与定义相的类型,避免MySQL进行隐式类型转化
尽量避免在SQL语句进行数学运算或者函数运算,避免将业务逻辑和DB耦合在一起
不使用ORDER BY RAND(),使用其他方法替换
禁止在线上库执行后台管理和统计类型功能的SQL,比如大表count(*)
尽量避免大SQL,可以拆解成多条简单的SQL
尽量通过索引消除掉filesort排序操作,禁止在多字段order by中升序不一致(可以将某字段的排序放到应用层实现)
尽量避免非操作,比如not in,<>,!>,!<,not exists,not like

MySQL服务器参数优化

Mysql参数调整优化Mysql有非常多的参数可调整配置,如何达到最适合当前业务场景,需不断进行调配测试。当前mysql初始化系统搭建的mysql实例,配置以通用为准,对于一些业务场景来说可能不是最优的配置。简单列举几个常用的参数调整。

  • query_cache_type

mysql 的query cache实现的不好,饱受吐槽的一个东西。因为一个表更新了,这个表的相关的所有的查询的query cache都会被请空。所以这个比较适合在写入较少,读取较多的场景下使用。对于关闭query cache是否有好处,最好还是线上实际调整后观察效果,可动态调整,调整后的效果也是很快可见。

  • innodb_flush_log_at_trx_commit

默认设置为1,每次事务提交都写入磁盘,在写入较大,io压力较大的情况下,调整这个参数效果非常明显,但相应的数据安全性也会降低,可根据实际业务情况配置

  • table_open_cache

表缓存数量大小,默认的是512,在并发比较高的集群中,其实是远不够的,如果open_tables_pt非常高,频繁的去关闭打开表的对系统消耗较大,就要考虑调大了。

  • innodb_max_dirty_pages_pct

innodb buffer pool脏页最大比,默认90,常预见的情况是刷脏页的时候系统整体压力非常大,io繁重,在写入比较繁重的情况下,如果有90%的脏页要刷盘就可能造成很大的压力,可是适当调小脏页比,减轻或避免一次性大量的脏页刷盘导致的压力问题。

还有很大一部分是cache size缓存大小类的参数,这类参数配置往往跟机器的物理内存大小相关。参数的优化调整往往比较难去找到到底调哪个参数有效果,需要不断进行调试观察,来确认调整哪个或者哪些参数对系统性能的提高有非常大的作用。

业务层面优化

所有数据库层面的调优,其实都是治标不治本的,业务才是数据库流量的来源,从源头上的优化其实效果才是最明显,也是事半功倍的效果的。业务层面的优化需要深入分析sql的请求特点,发现存在的问题,反推给业务端,从业务端来解决问题,因为很多问题业务端也是不知道的。

  • 读流量的优化

1) 一种情况是某类请求非常频繁,而且短时间内会有大量的重复请求,完全一样的sql同时发送到数据库。这种情况可以从业务端着手,去掉重复的查询。

2) 同类的sql请求,仅仅只是查询条件的不同,如select * from tabel where id=1 ,会有大量的请求,每个sql仅仅只是id不同的值而已。可以将请求进行合并:select * from table where id in (xx,xx),根据业务端和数据库端的实际情况,选择合理的合并条数。

3) 对于访问量特别大,更新又特别少的数据表,可以进行cache,减少数据库压力的同时,对于业务端请求的响应时间也可以得到很大的提升。

4) 大量某类查询,但大多数并没有返回结果给前端,在很大程度上这是程序逻辑的问题,我们希望的是来数据库的请求都是真实有效的查询请求,让业务端排查这类空结果请求,能收到很好的效果。

  • 写流量优化

1) 请求合并。跟读请求合并类似,insert,delete,update请求在数据库角度来讲都可以进行合并,可以很大程度减少数据库的压力,根据业务的实际情况进行优化。

2) 写入队列。消息队列的使用,在写入并发非常高的系统中,对缓解数据库的写入压力非常有效果,保证数据库的写入是平滑在可承受范围内的,而且写入速度是可控的。

3) 计数类的更新。最常见的问题就是某行记录非常热门,更新操作并发异常的高,导致大量的锁等待现象。计数类的操作不建议使用数据库来实现,可建议前端使用写缓存,定时计数更新等方式来实现。

Comments