Mysql学习总结(77)——温故Mysql数据库开发核心原则与规范

一、核心原则1、尽量不在数据库做运算俗话说:别让脚趾头想事情,那是脑瓜子的职责。作为数据库开发人员,我们应该让数据库多做她所擅长的事情。尽量不在数据库做运算,复杂运算移到程序端CPU,尽可能简单应用MYSQL。举例:在mysql中尽量不要使用如:md5()、OrderbyRand()等这类运算函数2、尽量控制单表数据量大家都知道单表数据量过大后会影响数据查询效率,严重情况下会导致整个库都卡住。一般...

Mysql学习总结(77)——温故Mysql数据库开发核心原则与规范
一、核心原则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)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复合索引无效。举例:

`a` char(32) DEFAULT NULL 【不推荐】`b` int(10) NOT NULL 【不推荐】`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、谨慎合理添加索引

添加索引是为了改善查询,添加索引会减慢更新,索引不是越多越好,能不加的索引尽量不加(综合评估数据密度和数据分布,最好不超过字段数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 。字符字段必须建前缀索引,例如:

create table P(`pinyin` varchar(100) DEFAULT NULL COMMENT '小区拼音',KEY `idx_pinyin` (`pinyin`(8)),) ENGINE=InnoDB
3、不在索引列做运算

在索引列计算会导致:无法使用索引、全表扫描。如下:

BAD SAMPLE:select * from tableWHERE to_days(current_date) – to_days(date_col) <= 10GOOD SAMPLE:select * from tableWHERE date_col >= DATE_SUB('2011-10-22',INTERVAL 10 DAY);
4、自增列或全局ID做INNODB主键

对主键建立聚簇索引,二级索引存储主键值,主键不应更新修改,按自增顺序插入值,忌用字符串做主键,聚簇索引分裂,推荐用独立于业务的AUTO_INCREMENT列或全局ID生成器做代理主键,若不指定主键,InnoDB会用唯一且非空值索引代替。

5、尽量不用外键

线上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 `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`)) 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;
四、SQL类原则1、SQL语句尽可能简单

在开发过程中,我们尽量要保持SQL语句的简单性,我们对比一下大SQL和多个简单SQL。传统设计思想 BUG MySQL NOT,一条SQL只能在一个CPU运算,5000 QPS的高并发中,1秒大SQL意味着?可能一条大SQL就把整个数据库堵死,拒绝大SQL,拆解成多条简单SQL,简单SQL缓存命中率更高,减少锁表时间,特别是MyISAM,用上多CPU。

2、保持事务(连接)短小

事务/连接使用原则:即开即用,用完即关。与事务无关操作都放到事务外面,减少锁资源的占用,不破坏一致性前提下,使用多个短事务代替长事务。如:1)发帖时的图片上传等待。2)大量的sleep连接。3.尽可能避免使用SP/TRIG/FUNC。线上OLTP系统中,我们应当:尽可能少用存储过程、尽可能少用触发器,减少使用MySQL函数对结果进行处理,将上述这些事情都交给客户端程序负责。

4、尽量不用SELECT *

用SELECT * 时,将会更多的消耗CPU、内存、IO以及网络带宽。我们在写查询语句时,应当尽量不用SELECT * ,只取需要的数据列。更安全的设计,减少表变化带来的影响,为使用covering index提供可能性。Select/JOIN 减少硬盘临时表生成,特别是有TEXT/BLOB时。

5、改写OR为IN()

同一字段,将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') #(推荐)
6、改写OR为UNION

不同字段,将or改为union。减少对不同字段进行 "or" 查询,Merge index往往很弱智,如果有足够信心:set global optimizer_switch='index_merge=off';

Select * from opp WHERE phone='010-88886666' or cellPhone='13800138000'; #不推荐Select * from opp WHERE phone='010-88886666' union Select * from opp WHERE cellPhone='13800138000'; # 推荐
7、避免负向查询和%前缀模糊查询

在实际开发中,我们要尽量避免负向查询,那什么是负向查询呢,主要有以下:NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。同时,我们还要避免%前缀模糊查询,因为这样会使用B Tree,同时会造成使用不了索引,并且会导致全表扫描,性能和效率可想而知。

8、减少COUNT(*)

在开发中我们经常会使用COUNT(*),殊不知这种用法会造成大量的资源浪费,因为COUNT(*)资源开销大,所以我们能不用尽量少用。对于计数类统计,实时统计:用memcache,双向更新,凌晨跑基准。非实时统计:尽量用单独统计表,定期重算。

9、LIMIT高效分页
传统分页:Select * from table limit 10000,10;LIMIT原理:Limit 10000,10偏移量越大则越慢推荐分页: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…) ;具体需要根据实际的场景分析并重组索引
10、用UNION ALL 而非UNION

如果无需对结果进行去重,仅仅是对多表进行联合查询并展示,则用UNION ALL,因为UNION有去重开销。如下例子:

MySQL>SELECT * FROM detail20091128 UNION ALLSELECT * FROM detail20110427 UNION ALLSELECT * FROM detail20110426 UNION ALLSELECT * FROM detail20110425 UNION ALLSELECT * FROM detail20110424 UNION ALLSELECT * FROM detail20110423;
11、分解联接保证高并发

高并发DB不建议进行两个表以上的JOIN。适当分解联接保证高并发:可缓存大量早期数据,使用了多个MyISAM表,对大表的小ID IN(),联接引用同一个表多次。

原SQL:MySQL> Select * from tag JOIN tag_post on tag_post.tag_id=tag.id JOIN poston 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)
12、GROUP BY 去除排序

使用GROUP BY可以实现分组和自动排序。无需排序:Order by NULL;特定排序:Group by DESC/ASC。

13、同数据类型的列值比较

原则:数字对数字,字符对字符。数值列与字符类型比较:同时转换为双精度进行比对。字符列与数值类型比较:字符列整列转数值,不会使用索引查询。

14、Load data 导数据

批量数据快导入:成批装载比单行装载更快,不需要每次刷新缓存。无索引时装载比索引装载更快。Insert values ,values,values 减少索引刷新。Load data比insert快约20倍,尽量不用INSERT ... SELECT,一个是有延迟,另外就是会同步出错。

15、打散大批量更新

大批量更新尽量凌晨操作,避开高峰,凌晨不限制,白天上线默认为100条/秒(特殊再议)。

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;
16、Know Every SQL

作为DBA乃至数据库开发人员,我们必须对数据库的每条SQL都非常了解,常见的命令有:SHOW PROFILE、MYSQLsla、MySQLdumpslow、explain、Show Slow Log、Show Processlist、SHOW QUERY_RESPONSE_TIME(Percona)等

五、约定类原则1、隔离线上线下

构建数据库的生态环境,确保开发无线上库操作权限。原则:线上连线上,线下连线下。生产数据用pro库,预生产环境用pre库,测试用test库,开发用dev库。

2、禁止未经DBA确认的子查询

大部分情况优化较差,特别WHERE中使用IN id的子查询,一般可用JOIN改写。如下例子:

MySQL> select * from table1 where id in (select id from table2);MySQL> insert into table1 (select * from table2); //可能导致复制异常
3、永远不在程序端显式加锁

外部锁对数据库丌可控,高幵发时是灾难,极难调试和排查,对于类似并发扣款等一致性问题,我们采用事务来处理,Commit前进行二次校验冲突。

4、统一字符集为UTF85、统一命名规范
源文地址:https://www.guoxiongfei.cn/csdn/7955.html