【小宅按】 前言这些原则都是经历过实战总结而成每一条原则背后都是血淋淋的教训这些原则主要是针对资料库开发人员,在开发过程中务必注意总是在灾难发生后,才想起容灾的重要性;总是在吃过亏以后,才记得曾有人提醒过。
这些原则都是经历过实战总结而成
每一条原则背后都是血淋淋的教训
这些原则主要是针对资料库开发人员,在开发过程中务必注意
总是在灾难发生后,才想起容灾的重要性;
总是在吃过亏以后,才记得曾有人提醒过。
俗话说:别让脚趾头想事情,那是脑瓜子的职责
作为资料库开发人员,我们应该让资料库多做她所擅长的事情:
举例:
在mysql中尽量不要使用如:md5()、Order by Rand()等这类运算函数
大家都知道单表数据量过大后会影响数据查询效率,严重情况下会导致整个库都卡住
一般情况下,按照一年内单表数据量预估:
同时要尽量做好合理的分表,使单表数据量不超载,常见的分表策略有:
分区表的适用场景主要有:
① 表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据;
② 分区表的数据更易维护,可以对独立的分区进行独立的操作;
③ 分区表的数据可以分布在不同的机器上,从而高效使用资源;
④ 可以使用分区表来避免某些特殊的瓶颈;
⑤ 可以备份和恢复独立的分区。
但是使用分区表同样有一些限制,在使用的时候需要注意:
① 一个表最多只能有 1024 个分区;
② 5.1版本中,分区表表达式必须是整数, 5.5可以使用列分区;
③ 分区栏位中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来;
④ 分区表中无法使用外键约束;
⑤ 需要对现有表的结构进行修改;
⑥ 所有分区都必须使用相同的存储引擎;
⑦ 分区函数中可以使用的函数和表达式会有一些限制;
⑧ 某些存储引擎不支持分区;
⑨ 对于 MyISAM 的分区表,不能使用 load index into cache;
⑩ 对于 MyISAM 表,使用分区表时需要打开更多的文件描述符。
单表的栏位数量也不能太多,根据业务场景进行优化调整,尽量调整表栏位数少而精,这样有以下好处:
那究竟单表多少栏位合适呢?
按照单表1G体积,500W行数据量进行评估:
==>建议单表栏位数上限控制在20~50个
资料库表结构的设计也讲究平衡,以往我们经常说要严格遵循三大范式,所以先来说说什么是范式:
第一范式:单个栏位不可再分。唯一性。
第二范式:不存在非主属性只依赖部分主键。消除不完全依赖。
第三范式:消除传递依赖。
用一句话来总结范式和冗余:
冗余是以存储换取性能,
范式是以性能换取存储。
所以,一般在实际工作中冗余更受欢迎一些。
模型设计时,这两方面的具体的权衡,首先要以企业提供的计算能力和存储资源为基础。
其次,一般互联网行业中都根据Kimball模式实施数据仓库,建模也是以任务驱动的,因此冗余和范式的权衡符合任务需要。
例如,一份指标数据,必须在早上8点之前处理完成,但计算的时间窗口又很小,要尽可能减少指标的计算耗时,这时在计算过程中要尽可能减少多表关联,模型设计时需要做更多的冗余。
资料库的并发就像城市交通,呈非线性增长
这就要求我们在做资料库开发的时候一定要注意高并发下的瓶颈,防止因高并发造成资料库瘫痪。
这里的拒绝3B是指:
三类数值类型:
以几个常见的例子来进行说明:
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类型
数字型VS字元串型索引有更多优势:
举例:用无符号INT存储IP,而非CHAR(15)
INT UNSIGNED
可以用INET_ATON()和INET_NTOA()来实现IP字元串和数值之间的转换
对于一些枚举型数据,我们推荐优先使用ENUM或SET,这样的场景适合:
1)字元串型
2)可能值已知且有限
存储方面:
1)ENUM占用1位元组,转为数值运算
2)SET视节点定,最多占用8位元组
3)比较时需要加『单引号(即使是数值)
`sex` enum(F,M) COMMENT 性别;
`c1` enum(0,1,2,3) COMMENT 审核;
为什么在资料库表栏位设计的时候尽量都加上NOT NULL DEFAULT ,这里面不得不说用NULL栏位的弊端:
1)`a` char(32) DEFAULT NULL 【不推荐】
2)`b` int(10) NOT NULL 【不推荐】
3)`c` int(10) NOT NULL DEFAULT 0 【推荐】
TEXT类型处理性能远低于VARCHAR
尽量不用TEXT/BLOB数据类型
如果业务需要必须用,建议拆分到单独的表
CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, data TEXT NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB;
先上图:
可见,如果将图片全部存在资料库,将使得资料库体积变大,会造成读写速度变慢。
图片存资料库的弊端:
推荐处理办法:资料库中保存图片路径
按照年月日生成路径。具体是按照年月日还是按照年月去生成路径,根据自己需要(不一定是按照日期去生成)。
理解为什么要分散到多个文件夹中去才是关键,涉及到一个原理就明白了:
操作系统对单个目录的文件数量是有限制的。当文件数量很多的时候。从目录中获取文件的速度就会越来越慢。所以为了保持速度,才要按照固定规则去分散到多个目录中去。
图片分散到磁碟路径中去。资料库栏位中保存的是类似于这样子的」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,http://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,给网站提供分流和就近访问)上去。这样子北京用户访问的时候,实际上图片就是就近获取。不需要很长距离的传输。
自己用一个域名http://img.xxx.com来载入图片。这个域名解析到北京的云服务上去。
做法:资料库中保存的是」 images/2012/09/25/1343287394783.jpg」,
这些图片实际上不存储在web伺服器上。上传到北京的cdn伺服器上去。
我从资料库取出来,直接」http://img.xxx.com/」+」 images/2012/09/25/1343287394783.jpg」
比如如果还有多个,就命名http://img1.xx.com、http://img2.xx.com
反正可以随便。所以如果把域名直接保存进去。就显得很麻烦了。迁移麻烦。
举例:不要给「性别」列创建索引
理论文章会告诉你值重复率高的栏位不适合建索引。不要说性别栏位只有两个值,网友亲测,一个栏位使用拼音首字母做值,共有26种可能,加上索引后,百万加的数据量,使用索引的速度比不使用索引要慢!
为什么性别不适合建索引呢?因为你访问索引需要付出额外的IO开销,你从索引中拿到的只是地址,要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据,那么利用索引迅速定位,访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据,就比如性别栏位,那你相对需要访问50万次索引,再访问50万次表,加起来的开销并不会比直接对表进行一次完整扫描小。
区分度:
字元栏位必须建前缀索引,例如:
`pinyin` varchar(100) DEFAULT NULL COMMENT 小区拼音, KEY `idx_pinyin` (`pinyin`(8)), ) ENGINE=InnoDB
原因有两点:
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);
线上OLTP系统尽量不用外键:
建议由程序保证约束
比如我们原来建表语句是这样的:
CREATE TABLE `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主键, `user_name` varchar(50) NOT NULL DEFAULT COMMENT 用户名, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主键, `total_price` decimal(10,2) NOT NULL DEFAULT 0.00, `user_id` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `for_indx_user_id` (`user_id`), CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
不使用外键约束后:
CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主键, `total_price` decimal(10,2) NOT NULL DEFAULT 0.00, `user_id` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
不适用外键约束后,为了加快查询我们通常会给不建立外键约束的栏位添加一个索引。
CREATE TABLE `order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主键, `total_price` decimal(10,2) NOT NULL DEFAULT 0.00, `user_id` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
实际开发中,一般不会建立外键约束。
1.SQL语句尽可能简单
在开发过程中,我们尽量要保持SQL语句的简单性,我们对比一下大SQL和多个简单SQL
拒绝大SQL,拆解成多条简单SQL
1)发帖时的图片上传等待
2)大量的sleep连接
线上OLTP系统中,我们应当:
将上述这些事情都交给客户端程序负责
用SELECT * 时,将会更多的消耗CPU、内存、IO以及网路带宽
我们在写查询语句时,应当尽量不用SELECT * ,只取需要的数据列:
不推荐:
SELECT * FROM tag WHERE id = 999148
推荐:
SELECT keyword FROM tag WHERE id = 999148
同一栏位,将or改写为in()
OR效率:O(n)
IN效率:O(Log n)
当n很大时,OR会慢很多
注意控制IN的个数,建议n小于200
Select * from opp WHERE phone=12347856 or phone=42242233
Select * from opp WHERE phone in (12347856 , 42242233)
不同栏位,将or改为union
Select * from opp WHERE phone=010-88886666 or cellPhone=13800138000;
Select * from opp WHERE phone=010-88886666 union Select * from opp WHERE cellPhone=13800138000;
在实际开发中,我们要尽量避免负向查询,那什么是负向查询呢,主要有以下:
NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等
同时,我们还要避免%前缀模糊查询,因为这样会使用B+ Tree,同时会造成使用不了索引,并且会导致全表扫描,性能和效率可想而知
在开发中我们经常会使用COUNT(*),殊不知这种用法会造成大量的资源浪费,因为COUNT(*)资源开销大,所以我们能不用尽量少用
对于计数类统计,我们推荐:
来对比一下COUNT(*)和其他几个COUNT吧:
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT 公司的id, `sale_id` int(10) unsigned DEFAULT NULL,
结论:
COUNT(*)=COUNT(1)
COUNT(0)=COUNT(1)
COUNT(1)=COUNT(100)
COUNT(*)!=COUNT(col)
传统分页:
Select * from table limit 10000,10;
LIMIT原理:
推荐分页:
Select * from table WHERE id>=23423 limit 11; #10+1 (每页10条) select * from table WHERE id>=23434 limit 11;
分页方式二:
Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;
分页方式三:
SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ;
分页方式四:
#先使用程序获取ID: select id from table limit 10000,10; #再用in获取ID对应的记录 Select * from table WHERE id in (123,456…) ;
具体需要根据实际的场景分析并重组索引
示例:
如果无需对结果进行去重,仅仅是对多表进行联合查询并展示,则用UNION ALL,因为UNION有去重开销
MySQL>SELECT * FROM detail20091128 UNION ALL SELECT * FROM detail20110427 UNION ALL SELECT * FROM detail20110426 UNION ALL SELECT * FROM detail20110425 UNION ALL SELECT * FROM detail20110424 UNION ALL SELECT * FROM detail20110423;
高并发DB不建议进行两个表以上的JOIN
适当分解联接保证高并发:
原SQL:
MySQL> Select * from tag JOIN tag_post on tag_post.tag_id=tag.id JOIN post on tag_post.post_id=post.id WHERE tag.tag=『二手玩具』;
分解SQL:
MySQL> Select * from tag WHERE tag=『二手玩具』; MySQL> Select * from tag_post WHERE tag_id=1321; MySQL> Select * from post WHERE post.id in (123,456,314,141)
使用GROUP BY可以实现分组和自动排序
无需排序:Order by NULL
特定排序:Group by DESC/ASC
原则:数字对数字,字元对字元
数值列与字元类型比较:同时转换为双精度进行比对
字元列与数值类型比较:字元列整列转数值,不会使用索引查询
栏位:`remark` varchar(50) NOT NULL COMMENT 备注,默认为空,
MySQL>SELECT `id`, `gift_code` FROM gift WHERE `deal_id` = 640 AND remark=115127; 1 row in set (0.14 sec)
MySQL>SELECT `id`, `gift_code` FROM pool_gift WHERE `deal_id` = 640 AND remark=115127; 1 row in set (0.005 sec)
批量数据快导入:
尽量不用INSERT ... SELECT,一个是有延迟,另外就是会同步出错
update post set tag=1 WHERE id in (1,2,3); sleep 0.01; update post set tag=1 WHERE id in (4,5,6); sleep 0.01; ……
作为DBA乃至资料库开发人员,我们必须对资料库的每条SQL都非常了解,常见的命令有:
构建资料库的生态环境,确保开发无线上库操作许可权
原则:线上连线上,线下连线下
MySQL> select * from table1 where id in (select id from table2); MySQL> insert into table1 (select * from table2); //可能导致复制异常
对于类似并发扣款等一致性问题,我们采用事务来处理,Commit前进行二次校验冲突
1)库表等名称统一用小写
2)索引命名默认为「idx_栏位名"
3)库名用缩写,尽量在2~7个字母
DataSharing ==> ds
4)注意避免用保留字命名
以上所有坑,建议资料库开发人员都要铭记于心。
更多精彩内容,请滑至顶部点击右上角关注小宅哦~
来源:华为云社区原创 作者:真爱无敌