系统优化怎么做-数据库优化
前言
目前大部分公司的数据库都是MySQL,虽然现在NoSQL数据库比如mongo, hbase越来越流行了,但传统的MySQL依然是业界用得最多。本文是以MySQL为例。
数据库
数据库是唯一在应用系统中的单点资源,对于数据库的资源的使用要特别小心。有如下几点注意点
- 数据库作为数据存储的地方,不应该把宝贵的资源用于数据的转换或统计操作,SQL中不使用一些字符转换等操作。
- 数据库连接资源宝贵,外围系统按需分配使用
- 数据库不怕高qps的小查询,但害怕慢查询,因此请消灭慢查询。
- 索引不是越多越好,维护索引资源也耗费数据库运算资源,数据库运算能力宝贵程度大于存储
- 如果是主从架构,主机器与从机器的网络带宽及稳定性要保证 不在数据库中存储图片、文件等大数据
- 禁止在线上做数据库压力测试
- 禁止从测试、开发环境直连线上数据库
- 不在业务高峰期批量更新、查询数据库
- 不在MySQL数据库中存放业务逻辑,写储存过程及触发器等
- 禁止在主库上执行后台管理和统计报表类的功能查询,都放到从库
硬件
- 磁盘
MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以,通常认为磁盘I/O是制约MySQL性能的最大因素之一,推荐使用RAID-0+1磁盘阵列。
- CPU
推荐使用至少4U以上的服务器来专门做数据库服务器,基本上是越多越好
- 内存
服务器内存建议不要小于4GB。基本上是越大越好
系统配置
MySQL配置在my.conf,影响新能的几个关键配置属性
- 使用INNODB存储引擎 5.5以后的默认引擘,支持事务,行级锁,更好的恢复性,高并发下性能更好,对多核,大内存,ssd等硬件支持更好。
- 表字符集使用utf8mb4
使用utf8mb4字符集,如果是汉字,占3个字节,但ASCII码字符还是1个字节;统一,不会有转换产生乱码风险,并能解决符号表情乱码问题; - max_connections 最大连接(用户)数
- innodb_log_file_size
在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但是要注意到可能会增加恢复时间。设置为64-512MB,根据服务器大小而异 - Innodb_buffer_pool_pages_data 分配出去, 正在被使用页的数量
- Innodb_buffer_pool_pages_total 缓冲区总共的页面数 Innodb_page_size
编译的InnoDB页大小(默认16KB)
调优参考计算方法:
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
- val > 95% 则考虑增大 innodb_buffer_pool_size, 建议使用物理内存的75%
- val < 95% 则考虑减小 innodb_buffer_pool_size,
- 建议设置为:
Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)
数据库表结构
表结构的设计目标除了满足业务以外,尽量减少代码实现上的联表查询操作,因此在设计上可以适当有一些冗余字段的设计,减少数据库IO次数。
现在很流行的ElasticSearch等大数据存储宽表的概念也是这种思想的体现- 尽量避免使用分区表 MySQL的分区表实际性能不是很好。 拆分大字段和访问频率低的字段,分离冷热数据
- 采用合理的分库分表策略,推荐使用HASH进行分表,表名后缀使用十进制数,下标从0开始首次分表尽量多的分,避免二次分表,二次分表的难度和成本较高
- 单表字段数控制在20个以内
- 一条完整的建表语句中应包含必要的字段、主键、合理的索引(综合代码中所有的条件语句创建合理的索引,主键必须要有
索引设计
索引是一把双刃剑,它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间。
- 单张表中索引数量不超过5个
- 单个索引中的字段数不超过5个
- 对字符串使用前缀索引,前缀索引长度不超过10个字符;如果有一个CHAR(200)列,如果在前10个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10个字符进行索引能够节省大量索引空间,也可能会使查询更快
- 表必须有主键,不使用UUID、MD5、HASH作为主键,尽量不选择字符串列作为主键;主键建议选择自增id
- 创建复合索引时区分度较大的字段放在最前面
- 不在低区分度的字段上创建索引,如“性别”
- 避免冗余或重复索引
- 合理创建联合索引(避免冗余),index(a、b、c) 相当于index(a)、index(a、b)、index(a、、b、c)
- 索引不是越多越好,按实际需要进行创建
- 每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能
- 不在索引列进行数学运算和函数运算;
- 尽量不要使用外键 外键用来保护参照完整性,可在业务端实现,对父表和子表的操作会相互影响,降低可用性
- 不使用%前导的查询,如like“%xxx”,不使用反向查询,如not in / not like 无法使用索引,导致全表扫描 1. 全表扫描导致buffer pool利用降低
字段设计
- 尽可能不要使用TEXT、BLOB类型。删除这种值会在数据表中留下很大的"空洞",可以考虑把BLOB或TEXT列分离到单独的表中
- 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数。浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;浮点数的缺点是会引起精度问题
- 将字符转化为数字
- 使用TINYINT来代替ENUM类型
- 字段长度尽量按实际需要进行分配,不要随意分配一个很大的容量 VARCHAR(N),N表示的是字符数不是字节数,比如VARCHAR(255),可以最大可存储255个汉字,需要根据实际的宽度来选择N。VARCHAR(N),N尽可能小,因为6. 6.MySQL一个表中所有的VARCHAR字段最大长度是65535个字节,进行排序和创建临时表一类的内存操作时,会使用N的长度申请内存;
- 如果可能, 所有字段均定义为not null
- 使用UNSIGNED存储非负整数 同样的字节数,存储的数值范围更大。如tinyint有符号为-128-127,无符号为0-255
- 使用TIMESTAMP存储时间. 因为TIMESTAMP使用4字节,DATETIME使用8个字节,同时TIMESTAMP具有自动赋值以及自动更新的特性.
- 使用INT UNSIGNED存储IPV4
- 使用VARBINARY存储大小写敏感的变长字符串
- 禁止在数据库中存储明文密码
思考题
- 数据库有哪些高可用措施?
- 如果数据库挂了,怎么保证核心业务是可用的?
相关推荐
fangxiaoji 2020-04-10
jiaming 2020-01-16
tycoon 2015-03-02
upzxt 2017-03-18
nihuotuboliefu 2015-12-23
ningningmingming 2011-05-24
无忧老猪 2014-07-21
seacover 2011-03-08
86570791 2013-05-19
BearTang 2012-03-31
菜鸟上路CCLinux 2010-04-01
GodLong 2010-01-13
wodetian 2019-06-28
Loger 2019-06-28
拼命工作好好玩 2019-06-28
programmerv 2019-06-27
85407615 2018-07-28
thankYouLikeMe 2017-12-14