本文整理自网络,侵删。
前言
这些原则都是经历过实战总结而成
每一条原则背后都是血淋淋的教训
这些原则主要是针对数据库开发人员,在开发过程中务必注意
一、核心原则
1.尽量不在数据库做运算
俗话说:别让脚趾头想事情,那是脑瓜子的职责
作为数据库开发人员,我们应该让数据库多做她所擅长的事情:
- 尽量不在数据库做运算
- 复杂运算移到程序端CPU
- 尽可能简单应用MYSQL
举例:
在mysql中尽量不要使用如:md5()、Order by Rand()等这类运算函数
2.尽量控制单表数据量
大家都知道单表数据量过大后会影响数据查询效率,严重情况下会导致整个库都卡住
一般情况下,按照一年内单表数据量预估:
- 纯INT不超过1000W
- 含CHAR不超过500W
同时要尽量做好合理的分表,使单表数据量不超载,常见的分表策略有:
- 通过USERID来分表(根据ID区间分表):在金融行业应用较多,用户量大、用户特征明显
- 按DATE分表(按天、周、月分表):在电信行业应用非常多,如用户上网记录表、用户短信表、话单表等
- 按AREA分表(省、市、区分表)
- 其他
分区表的适用场景主要有:
① 表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据;
② 分区表的数据更易维护,可以对独立的分区进行独立的操作;
③ 分区表的数据可以分布在不同的机器上,从而高效使用资源;
④ 可以使用分区表来避免某些特殊的瓶颈;
⑤ 可以备份和恢复独立的分区。
但是使用分区表同样有一些限制,在使用的时候需要注意:
① 一个表最多只能有 1024 个分区;
② 5.1版本中,分区表表达式必须是整数, 5.5可以使用列分区;
③ 分区字段中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来;
④ 分区表中无法使用外键约束;
⑤ 需要对现有表的结构进行修改;
⑥ 所有分区都必须使用相同的存储引擎;
⑦ 分区函数中可以使用的函数和表达式会有一些限制;
⑧ 某些存储引擎不支持分区;
⑨ 对于 MyISAM 的分区表,不能使用 load index into cache;
⑩ 对于 MyISAM 表,使用分区表时需要打开更多的文件描述符。
3.尽量控制表字段数量
单表的字段数量也不能太多,根据业务场景进行优化调整,尽量调整表字段数少而精,这样有以下好处:
- IO高效
- 全表遍历
- 表修复快
- 提高并发
- alter table更快
那究竟单表多少字段合适呢?
按照单表1G体积,500W行数据量进行评估:
- 顺序读1G文件需N秒
- 单行不超过200Byte
- 单表不超50个纯INT字段
- 单表不超20个CHAR(10)字段
==>建议单表字段数上限控制在20~50个
4.平衡范式与冗余
数据库表结构的设计也讲究平衡,以往我们经常说要严格遵循三大范式,所以先来说说什么是范式:
第一范式:单个字段不可再分。唯一性。
第二范式:不存在非主属性只依赖部分主键。消除不完全依赖。
第三范式:消除传递依赖。
用一句话来总结范式和冗余:
冗余是以存储换取性能,
范式是以性能换取存储。
所以,一般在实际工作中冗余更受欢迎一些。
模型设计时,这两方面的具体的权衡,首先要以企业提供的计算能力和存储资源为基础。
其次,一般互联网行业中都根据Kimball模式实施数据仓库,建模也是以任务驱动的,因此冗余和范式的权衡符合任务需要。
例如,一份指标数据,必须在早上8点之前处理完成,但计算的时间窗口又很小,要尽可能减少指标的计算耗时,这时在计算过程中要尽可能减少多表关联,模型设计时需要做更多的冗余。
5.拒绝3B
数据库的并发就像城市交通,呈非线性增长
这就要求我们在做数据库开发的时候一定要注意高并发下的瓶颈,防止因高并发造成数据库瘫痪。
这里的拒绝3B是指:
- 大SQL(BIG SQL):要减少
- 大事务(BIG Transaction)
- 大批量(BIG Batch)
二、字段类原则
1.用好数值字段类型
三类数值类型:
- 整型:TINYINT(1Byte)、TINYINT(1Byte)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B)
- 浮点型:FLOAT(4B)、DOUBLE(8B)
- DECIMAL(M,D)
以几个常见的例子来进行说明:
1)INT(1) VS INT(11)
很多人都分不清INT(1)和INT(11)的区别,想必大家也很好奇吧,其实1和11其实只是显示长度的却别而已,也就是不管int(x)x的值是什么值,存储数字的取值范围还是int本身数据类型的取值范围,x只是数据显示的长度而已。
2)BIGINT AUTO_INCREMENT
大家都知道,有符号int最大可以支持到约22亿,远远大于我们的需求和MySQL单表所能支持的性能上限。对于OLTP应用来说,单表的规模一般要保持在千万级别,不会达到22亿上限。如果要加大预留量,可以把主键改为改为无符号int,上限为42亿,这个预留量已经是非常的充足了。
使用bigint,会占用更大的磁盘和内存空间,内存空间毕竟有限,无效的占用会导致更多的数据换入换出,额外增加了IO的压力,对性能是不利的。
因此推荐自增主键使用int unsigned类型,但不建议使用bigint。
3)DECIMAL(N,0)
当采用DECIMAL数据类型的时候,一般小数位数不会是0,如果小数位数设置为0,那建议使用INT类型
2.将字符转化为数字
数字型VS字符串型索引有更多优势:
- 更高效
- 查询更快
- 占用空间更小
举例:用无符号INT存储IP,而非CHAR(15)
INT UNSIGNED
可以用INET_ATON()和INET_NTOA()来实现IP字符串和数值之间的转换
3.优先使用ENUM或SET
对于一些枚举型数据,我们推荐优先使用ENUM或SET,这样的场景适合:
1)字符串型
2)可能值已知且有限
存储方面:
1)ENUM占用1字节,转为数值运算
2)SET视节点定,最多占用8字节
3)比较时需要加‘单引号(即使是数值)
举例:
`sex` enum('F','M') COMMENT '性别';
`c1` enum('0','1','2','3') COMMENT '审核';
4.避免使用NULL字段
为什么在数据库表字段设计的时候尽量都加上NOT NULL DEFAULT '',这里面不得不说用NULL字段的弊端:
很难进行查询优化
NULL列加索引,需要额外空间
含NULL复合索引无效
举例:
1)`a` char(32) DEFAULT NULL 【不推荐】
2)`b` int(10) NOT NULL 【不推荐】
3)`c` int(10) NOT NULL DEFAULT 0 【推荐】
5.少用并拆分TEXT/BLOB
TEXT类型处理性能远低于VARCHAR
- 强制生成硬盘临时表
- 浪费更多空间
- VARCHAR(65535)==>64K(注意UTF-8)
尽量不用TEXT/BLOB数据类型
如果业务需要必须用,建议拆分到单独的表
举例:
CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, data TEXT NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB;
6.不在数据库里存图片
先上图:
可见,如果将图片全部存在数据库,将使得数据库体积变大,会造成读写速度变慢。
图片存数据库的弊端:
- 对数据库的读/写的速度永远都赶不上文件系统处理的速度
- 数据库备份变的巨大,越来越耗时间
- 对文件的访问需要穿越你的应用层和数据库层
★推荐处理办法:数据库中保存图片路径
按照年月日生成路径。具体是按照年月日还是按照年月去生成路径,根据自己需要(不一定是按照日期去生成)。
理解为什么要分散到多个文件夹中去才是关键,涉及到一个原理就明白了:
操作系统对单个目录的文件数量是有限制的。当文件数量很多的时候。从目录中获取文件的速度就会越来越慢。所以为了保持速度,才要按照固定规则去分散到多个目录中去。
图片分散到磁盘路径中去。数据库字段中保存的是类似于这样子的”images/2012/09/25/ 1343287394783.jpg”
原来上传的图片文件名称会重新命名保存,比如按照时间戳来生成,1343287394783. jpg。这样子是为了避免文件名重复,多个人往同一个目录上传图片的时候会出现。
反正用什么样的规则命名图片,只要做到图片名称的唯一性即可。
比如网站的并发访问量大,目录的生成分得月细越好。比如精确到小时,一个小时都可以是一个文件夹。同时0.001秒有两个用户同时在上传图片(因为那么就会往同一个小时文件夹里面存图片)。因为时间戳是精确到秒的。为了做到图片名称唯一性而不至于覆盖,生成可以在在时间戳后面继续加毫秒微秒等。总结的规律是,并发访问量越大。就越精确就好了。
题外话:
1)为什么保存的磁盘路径,是”images/2012/09/25/1343287394783.jpg”,而不是” /images/2012/09/25/ 1343287394783.jpg”(最前面带有斜杠)
在页面中需要取出图片路径展示图片的时候,如果是相对路径,则可以使用”./”+”images/2012/09/25/1343287394783.jpg”进行组装。
如果需要单独的域名(比如做cdn加速的时候)域名,img1.xxx.com,img2.xxx.com这样的域名,
直接组装 “http://img1.xxx.com/”+”images/2012/09/25/1343287394783.jpg”
2)为什么保存的磁盘路径,是”images/2012/09/25/1343287394783.jpg”,而不是“http://www.xxx.com/images/2012/09/25/1343287394783.jpg"
这里其实涉及到CDN的知识,具体CDN的知识在此不多展开,简而言之:
cdn服务:对于静态内容是非常适合的。所以像商品图片,随着访问量大了后,租用cdn服务,只需要把图片上传到他们的服务器上去。
例子:北京访问长沙服务器,距离太远。我完全可以把商品图片,放到北京的云服务(我觉得现在提供给网站使用的云存储其实就是cdn,给网站提供分流和就近访问)上去。这样子北京用户访问的时候,实际上图片就是就近获取。不需要很长距离的传输。
自己用一个域名img.xxx.com来载入图片。这个域名解析到北京的云服务上去。
做法:数据库中保存的是” images/2012/09/25/1343287394783.jpg”,
这些图片实际上不存储在web服务器上。上传到北京的cdn服务器上去。
我从数据库取出来,直接”img.xxx.com/”+” images/2012/09/25/1343287394783.jpg”
比如如果还有多个,就命名img1.xx.com、img2.xx.com
反正可以随便。所以如果把域名直接保存进去。就显得很麻烦了。迁移麻烦。
三、索引类原则
1.谨慎合理添加索引
- 添加索引是为了改善查询
- 添加索引会减慢更新
- 索引不是越多越好
- 能不加的索引尽量不加(综合评估数据密度和数据分布,最好不超过字段数20%)
- 结合核心SQL有限考虑覆盖索引
举例:不要给“性别”列创建索引
理论文章会告诉你值重复率高的字段不适合建索引。不要说性别字段只有两个值,网友亲测,一个字段使用拼音首字母做值,共有26种可能,加上索引后,百万加的数据量,使用索引的速度比不使用索引要慢!
为什么性别不适合建索引呢?因为你访问索引需要付出额外的IO开销,你从索引中拿到的只是地址,要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据,那么利用索引迅速定位,访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据,就比如性别字段,那你相对需要访问50万次索引,再访问50万次表,加起来的开销并不会比直接对表进行一次完整扫描小。
2.字符字段必须建前缀索引
区分度:
单字母区分度:26
4字母区分度:26*26*26*26 = 456,976
5字母区分度:26*26*26*26*26 = 11,881,376
6字母区分度:26*26*26*26*26*26 = 308,915,776
字符字段必须建前缀索引,例如:
`pinyin` varchar(100) DEFAULT NULL COMMENT '小区拼音', KEY `idx_pinyin` (`pinyin`(8)), ) ENGINE=InnoDB
3.不在索引列做运算
原因有两点:
1)会导致无法使用索引
2)会导致全表扫描
举例:
BAD SAMPLE:
select * from table WHERE to_days(current_date) – to_days(date_col) <= 10
GOOD SAMPLE:
select * from table WHERE date_col >= DATE_SUB('2011-10-22',INTERVAL 10 DAY);
4.自增列或全局ID做INNODB主键
- 对主键建立聚簇索引
- 二级索引存储主键值
- 主键不应更新修改
- 按自增顺序插入值
- 忌用字符串做主键
- 聚簇索引分裂
- 推荐用独立于业务的AUTO_INCREMENT列或全局ID生成器做代理主键
- 若不指定主键,InnoDB会用唯一且非空值索引代替
5.尽量不用外键
- 线上OLTP系统尽量不用外键:
- 外键可节省开发量
- 有额外开销
- 逐行操作
- 可“到达”其他表,意味着锁
- 高并发时容易死锁
建议由程序保证约束
相关阅读 >>
c#实现定义一套中间sql可以跨库执行的sql语句(案例详解)
更多相关阅读请进入《sql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。