MySQL 数据库开发注意事项

2018-03-18 0 By admin

核心规则

1、尽量不要在数据库中做运算

尽可能简单应用MySQL;不要在数据库中做运算,将复杂运算移到程序端。
如md5()\Order by Rand()

2、控制单表数据量

纯INT不超过1000W;含CHAR不超过500W
合理分表不超载;USERID\DATE\AREA
建议单库不超过300表。

3、优化表字段配置

1、表字段数少而精
IO高效、全表遍历、表修复快、提高并发、修改表格式快
2、单表1G体积,500W行评估
顺序读1G文件需要N表
单行不超过200Byte
单表不超过50个纯INT字段
单表不超过20个CHAR(10)字段
单表字段数控制在20-50个

4、平衡范式与冗余

效率优先、提升性能
适当牺牲范式、加入冗余
平衡代码复杂度的增加

5、拒绝3B

数据库并发处理非线性增长
拒绝:Big SQL\Big Transaction \Big Batch

字段类

1、考虑数值字段占用的存储空间
2、数字字段比字符串有优势
3、优先使用ENUM或SET
4、避免使用NULL字段:很难进行查询优化\NULL列加索引需要额外空间\含NULL复合索引无效
5、少用TEXT\BLOB字段:
TEXT字段类型处理性能远低于varchar:强制生成硬盘临时表\浪费更多空间\varchar的存储空间不要小觑
将TEXT/BLOB字段拆分到独立表中
5、不要在数据库中存储图片

索引类

1、合理添加索引
提高查询速度、降低更新速度、综合评估数据密度和数据分布、最好不超过字段数的20%;
2、字符字段设置前缀索引,设置区分度。
3、不要在索引中做运算
4、尽量不用外键;高并发容易死锁、由程序保证约束

补充:

索引的区分度,主要是衡量索引值不相同的程度,区分度越大,越有利于索引的查询。
设想一下,对于sex列,列值只有male和female,那么也就是说列中绝大多数值都是重复的,那么用此索引进行row的查找其实意义并不大。所以这样的列建索引的意义并不大。
对于列值比较长的列,我们往往不能将整个列做索引,因为这样会导致索引过大,降低索引效率。我们需要取列值的前缀进行索引,那么索引前缀的大小选择就需要计算区分度。
索引的区分度计算主要计算是通过 不重复的索引值/数据表的总记录数。区分度越高,索引查询时会让mysql在查询时过滤掉更多的行。值越接近1,证明区分度越高。

SQL语句

1、建议多个小SQL:一个SQL只有一个CPU运算、高并发下小SQL执行快、小SQL缓存命中率高
2、短小事务:减少锁资源的占用、使用短事务代替长事务
3、减少SP\TRIG\FUNC:减少存储过程、触发器、函数
4、减少select*:更多的CPU、内存、IO、网络宽带
5、改写OR为IN()
6、改写OR为UNION
7、避免反向查询:NOT\!=\NOT EXISTS\NOT IN\NOT LIKE…
8、避免%前缀模糊查询:不能使用索引,全表扫描
9、少用Count(*):count(*)!=count(col)
10、Limit高效分页:偏移量越大则越慢
11、使用UNION ALL 而非UNION
12、分解联接保证高并发
13、group by 去除排序:order by null
14、同数据类型进行比较

开发管理

隔离线上线下
不在程序端对数据库显式加锁
统一字符串编码