MySQL数据库开发的五大原则

大家好，我是一安，以下内容来自一线的实战经验，主要针对DBA和后端开发人员

核心原则

尽量不在数据库做运算

作为数据库开发人员，我们应该让数据库多做它所擅长的事情：

尽量不在数据库做运算
复杂运算移到程序端CPU
尽可能简单应用MYSQL

举例：

在mysql中尽量不要使用如：md5()、Order by Rand()等这类运算函数

尽量控制单表数据量

大家都知道单表数据量过大后会影响数据查询效率，严重情况下会导致整个库都卡住

一般情况下，按照一年内单表数据量预估：

纯INT不超过1000W
含CHAR不超过500W

同时要尽量做好合理的分表，使单表数据量不超载，常见的分表策略有：

通过USERID来分表（根据ID区间分表）：在金融行业应用较多，用户量大、用户特征明显
按DATE分表（按天、周、月分表）：在电信行业应用非常多，如用户上网记录表、用户短信表、话单表等
按AREA分表（省、市、区分表）
其他

分区表的适用场景主要有：

表非常大，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据；
分区表的数据更易维护，可以对独立的分区进行独立的操作；
分区表的数据可以分布在不同的机器上，从而高效使用资源；
可以使用分区表来避免某些特殊的瓶颈；
可以备份和恢复独立的分区。

但是使用分区表同样有一些限制，在使用的时候需要注意：

一个表最多只能有 1024 个分区；
5.1版本中，分区表表达式必须是整数， 5.5可以使用列分区；
分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来；
分区表中无法使用外键约束；
需要对现有表的结构进行修改；
所有分区都必须使用相同的存储引擎；
分区函数中可以使用的函数和表达式会有一些限制；
某些存储引擎不支持分区；
对于 MyISAM 的分区表，不能使用 load index into cache；
对于 MyISAM 表，使用分区表时需要打开更多的文件描述符。

尽量控制表字段数量

单表的字段数量也不能太多，根据业务场景进行优化调整，尽量调整表字段数少而精，这样有以下好处：

IO高效
全表遍历
表修复快
提高并发
alter table更快

那究竟单表多少字段合适呢？

按照单表1G体积，500W行数据量进行评估：

单行不超过200Byte
单表不超50个纯INT字段
单表不超20个CHAR(10)字段

建议单表字段数上限控制在20~50个

平衡范式与冗余

数据库表结构的设计也讲究平衡，以往我们经常说要严格遵循三大范式，所以先来说说什么是范式：

第一范式：单个字段不可再分。唯一性。

第二范式：不存在非主属性只依赖部分主键。消除不完全依赖。

第三范式：消除传递依赖。

用一句话来总结范式和冗余：

冗余是以存储换取性能，

范式是以性能换取存储。

所以，一般在实际工作中冗余更受欢迎一些。

模型设计时，这两方面的具体的权衡，首先要以企业提供的计算能力和存储资源为基础。

其次，一般互联网行业中都根据Kimball模式实施数据仓库，建模也是以任务驱动的，因此冗余和范式的权衡符合任务需要。

例如，一份指标数据，必须在早上8点之前处理完成，但计算的时间窗口又很小，要尽可能减少指标的计算耗时，这时在计算过程中要尽可能减少多表关联，模型设计时需要做更多的冗余。

拒绝3B

数据库的并发就像城市交通，呈非线性增长

这就要求我们在做数据库开发的时候一定要注意高并发下的瓶颈，防止因高并发造成数据库瘫痪。

这里的拒绝3B是指：

大SQL（BIG SQL）：要减少
大事务（BIG Transaction）
大批量（BIG Batch）

字段类原则

用好数值字段类型

三类数值类型：

整型：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类型

将字符转化为数字

数字型VS字符串型索引有更多优势：

更高效
查询更快
占用空间更小

举例：用无符号INT存储IPINT UNSIGNED，而非CHAR(15)

可以用INET_ATON()和INET_NTOA()来实现IP字符串和数值之间的转换

优先使用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 ‘审核’；

避免使用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 【推荐】

少用并拆分TEXT/BLOB

TEXT类型处理性能远低于VARCHAR

强制生成硬盘临时表
浪费更多空间
VARCHAR(65535)==>64K(注意UTF-8)

尽量不用TEXT/BLOB数据类型

如果业务需要必须用，建议拆分到单独的表

不在数据库里存图片

可见，如果将图片全部存在数据库，将使得数据库体积变大，会造成读写速度变慢。

图片存数据库的弊端：

对数据库的读/写的速度永远都赶不上文件系统处理的速度
数据库备份变的巨大，越来越耗时间
对文件的访问需要穿越你的应用层和数据库层

推荐处理办法：数据库中保存图片路径

按照年月日生成路径。具体是按照年月日还是按照年月去生成路径，根据自己需要(不一定是按照日期去生成)。

理解为什么要分散到多个文件夹中去才是关键，涉及到一个原理就明白了：

操作系统对单个目录的文件数量是有限制的。当文件数量很多的时候。从目录中获取文件的速度就会越来越慢。所以为了保持速度，才要按照固定规则去分散到多个目录中去。

图片分散到磁盘路径中去。数据库字段中保存的是类似于这样子的”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

反正可以随便。所以如果把域名直接保存进去。就显得很麻烦了。迁移麻烦。

索引类原则

谨慎合理添加索引

添加索引是为了改善查询
添加索引会减慢更新
索引不是越多越好
能不加的索引尽量不加（综合评估数据密度和数据分布，最好不超过字段数20%）
结合核心SQL有限考虑覆盖索引

举例：不要给“性别”列创建索引

理论文章会告诉你值重复率高的字段不适合建索引。不要说性别字段只有两个值，网友亲测，一个字段使用拼音首字母做值，共有26种可能，加上索引后，百万加的数据量，使用索引的速度比不使用索引要慢！

为什么性别不适合建索引呢？因为你访问索引需要付出额外的IO开销，你从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小

字符字段必须建前缀索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

不在索引列做运算

原因有两点：

1）会导致无法使用索引
2）会导致全表扫描

自增列或全局ID做INNODB主键

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

尽量不用外键

线上OLTP系统尽量不用外键：

外键可节省开发量
有额外开销
逐行操作
可“到达”其他表，意味着锁
高并发时容易死锁

建议由程序保证约束

SQL类原则

SQL语句尽可能简单

在开发过程中，我们尽量要保持SQL语句的简单性，大SQL可以拆分多个简单SQL

简单SQL缓存命中率更高
减少锁表时间，特别是MyISAM
用上多CPU

保持事务（连接）短小

事务/连接使用原则：即开即用，用完即关
与事务无关操作都放到事务外面，减少锁资源的占用
不破坏一致性前提下，使用多个短事务代替长事务

尽可能避免使用SP/TRIG/FUNC

线上OLTP系统中，我们应当：

尽可能少用存储过程
尽可能少用触发器
减少使用MySQL函数对结果进行处理

将上述这些事情都交给客户端程序负责

尽量不用SELECT *

用SELECT * 时，将会更多的消耗CPU、内存、IO以及网络带宽

我们在写查询语句时，应当尽量不用SELECT * ,只取需要的数据列：

更安全的设计：减少表变化带来的影响
为使用covering index提供可能性
Select/JOIN 减少硬盘临时表生成，特别是有TEXT/BLOB时

改写OR为IN()

同一字段，将or改写为in()

OR效率：O(n)

IN效率：O(Log n）

当n很大时，OR会慢很多

注意控制IN的个数，建议n小于200

改写OR为UNION

不同字段，将or改为union

减少对不同字段进行 “or” 查询

Merge index往往很弱智

如果有足够信心：set global optimizer_switch=’index_merge=off’;

避免负向查询和%前缀模糊查询

在实际开发中，我们要尽量避免负向查询，那什么是负向查询呢，主要有以下：

NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

同时，我们还要避免%前缀模糊查询，会造成使用不了索引导致全表扫描，性能和效率可想而知

LIMIT高效分页

传统分页：

Select * from table limit 10000,10;

推荐分页：

Select * from table WHERE id>=23423 limit 11; 
#10+1 (每页10条)
select * from table WHERE id>=23434 limit 11;

用UNION ALL 而非UNION

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

分解联接保证高并发

高并发DB不建议进行两个表以上的JOIN，可以分解SQL

GROUP BY 去除排序

使用GROUP BY可以实现分组和自动排序，无需排序排序：order by null

同数据类型的列值比较

原则：数字对数字，字符对字符

数值列与字符类型比较：同时转换为双精度进行比对

字符列与数值类型比较：字符列整列转数值，不会使用索引查询

Load data 导数据

批量数据快导入：

成批装载比单行装载更快，不需要每次刷新缓存
无索引时装载比索引装载更快
Insert values ,values，values 减少索引刷新
Load data比insert快约20倍

尽量不用INSERT … SELECT，一个是有延迟，另外就是会同步出错

约定类原则

隔离线上线下

构建数据库的生态环境，确保开发无线上库操作权限

原则：线上连线上，线下连线下

生产数据用pro库
预生产环境用pre库
测试用test库
开发用dev库

禁止未经DBA确认的子查询

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

永远不在程序端显式加锁

外部锁对数据库不可控
高幵发时是灾难
极难调试和排查

对于类似并发扣款等一致性问题，我们采用事务来处理，Commit前进行二次校验冲突

统一字符集为UTF8

统一命名规范

1）库表等名称统一用小写

2）索引命名默认为“idx_字段名”

3）库名用缩写，尽量在2~7个字母

4）注意避免用保留字命名

致谢：https://blog.csdn.net/devcloud/article/details/100173405/

号外！号外！

如果这篇文章对你有所帮助，或者有所启发的话，帮忙点赞、在看、转发、收藏，你的支持就是我坚持下去的最大动力！

JVM 内存布局详解，图文并茂，写得太好了！

一文带你详解Mysql底层索引机制

你对JVM垃圾回收机制了解多少

原文始发于微信公众号（一安未来）：MySQL数据库开发的五大原则

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/44705.html