mysql使用规范

Mysql使用规范
 

1.流程规范

2.存储引擎

3.数据库字符集

4.命名规范

5.表设计规范

6.索引设计规范

7.常见SQL使用规范
 

存储引擎
使用InnoDB存储引擎

InnoDB存储引擎是MySQL默认存储引擎，支持事务和行级锁，并发性能更好，CPU及内存缓存页优化使得资源利用率更高
 

数据库字符集
使用utf8字符集

如果有字段需要存储emoji表情之类的，则需要将字段或表设置成utf8mb4 (现在各业务线的设置有可能不同，不强制要求)
 

命名规范
1.库名、表名、字段名：小写字母，下划线风格，禁止数字开头，禁止两个下划线中间只出现数字，字段命名禁止复数名词

正例：getter_ admin，task_ config，level3_ name 反例：GetterAdmin，taskConfig，level_ 3_name。命名中不允许单独出现MYSQL数据库中的保留字。如desc、range、match、delayed、select、where等，请参考MySQL官方保留字。
2.索引命名格式为: 索引类型-字段名(缩写)

普通索引名idx-xxx
唯一索引名uniq-xxx
 

表设计规范
1.数据表、数据字段必须加入中文注释:

便于识别表和字段的用途
2.如果业务中无唯一自增列作为主键，建议显式指定一个无业务用途的自增unsigned int型主键

主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
3.单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表

如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表
4.把字段定义为NOT NULL并且提供默认值

tinyint/int/bigint 类型默认值：0
char/varchar 类型默认值：’ ’
date 类型默认值：‘0000-00-00’
time 类型默认值：‘00:00:00’
timestamp 类型默认值：‘0000-00-00 00:00:00’ 或 ‘CURRENT_TIMESTAMP’
5.禁止使用TEXT、BLOB类型，可以做垂直拆分到子表中

会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能
6.存储货币用decimal或整数类型，禁止使用float和double

float和double在存储的时候，存在精度损失的问题，为了效率考虑建议使用整数类型，用’分’作为单位来存储。
7.枚举类型禁止使用ENUM，可使用TINYINT代替

增加新的ENUM值要做DDL操作
ENUM的内部实际存储就是整数
8.表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint( 1表示是，0表示否)

比如判断一个优惠券是否使用可以定义为: is_used
9.使用varchar(20)存储手机号

涉及到区号或者国家代号，可能出现+-()
手机号不会做数学运算
varchar可以支持模糊查询，例如：like“138%”
10.禁止使用外键，如果有外键完整性约束，需要应用程序控制

外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql的性能，甚至会造成死锁。高并发情况下容易造成数据库性能下降。
11.表必须有create_ at和update_at两个字段，并指定timestamp类型

create_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;或者
finished_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
update_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
12.小数类型用decimal，禁止使用float和double

float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。
13.设计表字段能用数字类型就千万别用字符类型，比如存IP地址，用int，别用varchar

14.mysql单库最大表数量200， 单表字段数量最大值50

 

索引设计规范
1.单表索引建议控制在5个以内

索引过多会增加存储开销和增删改的开销
2.禁止在更新十分频繁、区分度不高的属性上建立索引

更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
3.尽量使用组合索引，建立组合索引时必须把区分度高的字段放在前面，字段数不允许超过5个

能够更加有效的过滤数据，索引上字段超过5个时，实际已经起不到有效过滤数据的作用了
4.在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
5.假设有一个联合索引（A，B，C，D）就相当于也有（A，B，C），（A，B），（A）这几个索引

索引的最左前缀匹配原则
6.经常与其他表进行连接的表，在连接字段上应该建立索引

7.索引不会包含有NULL值的列（若组合索引包含NULL值的列则整个组合索引无效）

 

常见SQL使用规范
1.禁止负向查询

负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
2.禁止以%开头的模糊查询

%开头的模糊查询，会导致全表扫描
3.用IN查询来替换OR

低效: SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
高效: SELECT * FROM t WHERE LOC_ID IN (10,20,30);
4.EXPLAIN SELECT 查询用来跟踪查看效果

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。
5.当只要一行数据时使用 LIMIT 1

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。在这种情况下，加上 LIMIT 1 可以增加性能。这样一来，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
6.InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现的特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

7.禁止使用"*"返回所有： select “*” from table ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
8.提高GROUP BY语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果，但第二个明显就快了许多

低效: SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB =’PRESIDENT’ OR JOB =’MANAGER’
高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB =’PRESIDENT’ OR JOB =’MANAGER’ GROUP BY JOB
9.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num < 10 or num > 20 这条SQL可以这样优化:
select id from t where num < 10 union all select id from t where num > 20
10.很多时候用 exists 代替 in 是一个好的选择

低效: select num from a where num in(select num from b)
高效: select num from a where exists(select 1 from b where num=a.num)
11.尽量避免SQL中出现运算，例如select (a+5) from A，让DB功能单一化，逻辑判断尽量放在程序端控制

12.避免使用count(*)，会扫描全表，1000W以上至少几秒，作为替代方案，考虑使用nosql例如redis，memcached存下来，但是要定时校对。还有一个办法，直接做一个表存下来，每次增加或者减少都在这个表做update增减。如果不想每次update增减，建议用唯一索引列查询。

13.分页时：Select a from A limit 10000,10; 这种大偏移量下效率非常低。可以考虑如下几个方案：

select a from A WHERE id>=xxxx limit 11;(将上一页的最大值通过where id> 进行预处理，然后分页)
select a from A WHERE id >= ( select id from A limit 10000,1 ) limit 10;
select a from A inner join (select id from A limit 10000,10) using (id) ;
14.禁止在我们的项目中使用存储过程，触发器, event

15.禁止在代码中使用orm的方式执行join操作