mysql使用规范;mysql的性能优化

相关阅读：数据库设计的规范化和标准化

一、基础规范

强制类规范

1. 创建表的存储引擎必须是InnoDB。

支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

2. 每个表必须显式的指定一个主键。

3. 不允许使用联合主键。

4. 不允许使用外键。

5. 不允许存在和主键重复的索引。

6. 自增长字段必须是主键或唯一索引。

7. 不允许在数据库中存储诸如图片、大文件，影像之类的二进制数据。

(1)禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径

8. 不允许使用TEXT类型字段

9. 建表时不允许显式的指定除了utf8之外的其他字符集。

10. 对于所有声明为NOT NULL的字段，必须显式指定默认值。

11. 必须包含时间戳字段DataChange_LastTime，定义默认值为CURRENT_TIMESTAMP和on update CURRENT_TIMESTAMP，并添加索引。

12.不要使用系统或者常见的名称作为表名 如order

13.表字符集默认使用utf8，必要时候使用utf8mb4

(1)通用，无乱码风险，汉字3字节，英文1字节

(2)utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它

14.禁止使用存储过程，视图，触发器，Event

(1)对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层

(2)调试，排错，迁移都比较困难，扩展性较差

15.禁止使用负向查询NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描

16.禁止在线上环境做数据库压力测试

测试，开发，线上数据库环境必须隔离

二、命名规范

1.库名，表名，列名必须用小写，采用下划线分隔，不要使用复数，不超过32个字符，禁止拼音英文混用

abc，Abc，ABC都是给自己埋坑

2.库名，表名，列名必须见名知义，长度不要超过32字符

tmp，wushan谁TM知道这些库是干嘛的

3.库备份必须以bak为前缀，以日期为后缀

从库必须以-s为后缀 备库必须以-ss为后缀

4.只允许使用内网域名，而不是ip连接数据库

5.线上环境、开发环境、测试环境数据库内网域名遵循命名规范

6.表名以tb_开头为实体表，以tr_开头为关系表，以th_开头为历史表等

7.唯一索引名以uk_字段名；普通索引名则以 idx_字段名

三、表设计类规范

1.单实例mysql表数目必须小于2000(根据项目需求)

2.单表列数目必须小于30

3.表必须有主键，推荐使用UNSIGNED整数为主键

(1)主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用

(2)主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率

(3)无主键的表删除，在row模式的主从架构，会导致备库夯住

4.禁止使用外键，如果有外键完整性约束，需要应用程序控制

外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库锁死，大数据高并发业务场景数据库使用以性能优先

建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据

四、列字段设计规范

1.根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节

2.根据业务区分使用char/varchar

(1)字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高

(2)字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间

3.根据业务区分使用datetime/timestamp

解读：前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime

必须把字段定义为NOT NULL并设默认值

(1)NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化

(2)NULL需要更多的存储空间

(3)NULL只能采用IS NULL或者IS NOT NULL，而不能采用=、in、<、<>、!=、not in这些操作符号。

使用INT UNSIGNED存储IPv4，不要用char(15)

4.使用varchar(20)存储手机号或varchar(32)，不要使用整数

原因:

(1)牵扯到国家代号，可能出现+/-/()等字符，例如+86

(2)手机号不会用来做数学运算

(3)varchar可以模糊查询，例如like ‘138%’

5.使用TINYINT来代替ENUM

ENUM增加新值要进行DDL操作

ENUM的内部实际存储就是整数

5.禁止使用TEXT、BLOB类型

会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

6.禁止使用小数存储国币

使用“分”作为单位，这样数据库里就是整数了

7.字段boolean使用unsigned tinyint（ 1 表示是，0表示否）

8.非负数使用unsigned，多值使用枚举，固定成长度使用char，不固定使用varchar 长度2的倍数(长度不要超过 5000，超过使用text，并单独新建一张表存储)

9.实体表必备字段 id，create_time，modify_time

10.业务上具有唯一性的字段，即使是组合字段，也必须建成唯一索引。

五、索引类规范

1.单表索引最好控制在3个以内，不要超过5个

2.单索引字段数不允许超过3个

3.禁止在更新十分频繁、区分度不高的属性上建立索引

4.不要新增索引，尽量使用组合索引（注意组合索引的最左原则，区分度最高的在最左边）

5.不要在索引列上使用数学运算和函数运算

6.唯一索引使用uniq_[字段名]来命名

7.非唯一索引使用idx_[字段名]来命名

8.单张表索引数量建议控制在5个以内

(1)互联网高并发业务，太多索引会影响写性能

(2)生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引

(3)异常复杂的查询需求，可以选择ES等更为适合的方式存储

9.组合索引字段数不建议超过5个

解读：如果5个字段还不能极大缩小row范围，八成是设计有问题

理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a，b，c)，相当于建立了(a)， (a，b)， (a，b，c)

10.不建议在频繁更新的字段上建立索引

非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引

解读：踩过因为JOIN字段类型不一致，而导致全表扫描的坑么？

六、建议类规范

1.建议使用自增长字段作为主键。

2.尽量不要在数据库里做运算。

3.对较长的字符类型，如果需要索引，则建立前缀索引。

4.尽量不要做‘%’前缀模糊查询，如 like '%name'。

5.不建议在数据库存放日志

6.建议将字段都定义为not null，并且提供默认值。

7.选用能满足需求的最小类型。

8.避免使用保留字命名DB对象

9.对表和字段都添加备注说明

10.连接MySQL不要设置成autocommit=0。

11.批量insert语句最好采用bulk insert的方法，如insert into table(xxx) values (xxx)，(xxx)。

12.update/delete尽量根据主键进行操作。

13. 尽量减少count()的使用，尤其是用来频繁获取全表记录数。

14.使用group by时，如无排序的需求，建议加order by null。

15.Join中使用的关联字段使用统一数据类型。

七、SQL使用类规范

1. 禁止使用子查询。

2. 禁止使用select *，必须指定需要的字段，需要显示说明列属性。

(1)读取不需要的列会增加CPU、IO、NET消耗

(2)指定字段能有效利用索引覆盖

(3)使用SELECT *容易在增加或者删除字段后出现程序BUG；指定字段查询，在表结构变更时，能保证对应用程序无影响

3.禁止使用INSERT INTO tb_xxx VALUES(xxx)，必须显示指定插入的列属性

容易在增加或者删除字段后出现程序BUG；指定属性在指定字段插入，在表结构变更时，能保证对应用程序无影响

4.禁止使用属性隐式转换

SELECT uid FROM tb_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引

5.不要在where后的筛选字段上做运算(使用函数或者表达式)；

SELECT uid FROM tb_user WHERE from_unixtime(day)>=‘2017-02-15’ 会导致全表扫描

正确的写法是：SELECT uid FROM tb_user WHERE day>= unix_timestamp(‘2017-02-15 00:00:00’)

原因:会导致不能命中索引，全表扫描

6.禁止负向查询，以及%开头的模糊查询

负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描

%开头的模糊查询，会导致全表扫描

7.单条查询语句中，不允许出现多于一次的join。禁止大表使用JOIN查询和子查询，禁止大表使用子查询。超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

对数据库性能影响较大的数据库特性较少使用，解放数据库CPU，把复杂逻辑计算放到服务层

8.禁止使用OR条件，必须改为IN查询(IN的值必须少于50个)，缩小查询范围

9.应用程序必须捕获SQL异常，并有相应处理

10.禁止在主库上执行sum，count等复杂的耗时统计分析语句

11. update/delete只能单表操作，不允许多表关联，不允许用子查询，且一定要带where条件。

12. insert语句要显式指定插入的列名，且不允许使用insert .... select的形式。

13. 不允许使用存储过程、存储函数、触发器和视图。

八、行为规范

1.禁止使用应用程序配置文件内的帐号手工访问线上数据库

2.禁止非DBA对线上数据库进行写操作，修改线上数据需要提交工单，由DBA执行，提交的SQL语句必须经过测试

3.分配非DBA以只读帐号，必须通过VPN+跳板机访问授权的从库

4.开发、测试、线上环境隔离