mysql怎么设置预留字段_开发中 MySQL 规范

一、建表规范

1、数据库名、表名、字段名必须使用小写字母或数字，并且禁止以数字开头

示例：goods_category、agent_operate_201812_log

2、数据库名、表名、字段名要做到见名知意

示例：goods_category，不能 gc

3、配置表建议以 xx_config 形式命名

示例：shop_payment_config

4、日志表建议以 xx_log 形式命名

示例：system_log

5、临时表建议以 temp_xx 形式命名

示例：temp_order_info_export

6、创建时间使用 create_time，更新时间使用 update_time

类型使用 int(11) unsigned

7、字段类型为字符串时需要注意的

如果存储的字符串长度几乎相等，则应该使用 char 定长字符串类型。

如果长度超过5000个字符，则应该将字段类型定义为 text，并独立出来一张表，用主键对应，避免影响其他字段的索引效率。

8、字段允许适当冗余，以提高查询性能，但必须考虑数据一致

9、单表行数超过 500 万行 或者 单表容量超过 2GB 时，才推荐进行分库分表

10、当存储的字段为小数时，数据类型设置为 decimal，禁止使用 float 和 double

在存储的时候，float 和 double 存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。

如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。

存储商品价格时，统一转为分，存储类型应为整型 int。

11、没有特殊要求的情况下，所有的数据表必须使用 Innodb 存储引擎

Innodb 支持事务，支持行级锁，拥有更好的并发性能和恢复性。

12、数据库和数据表的字符集统一使用 utf8，需要存储 emoji 表情的使用 utf8mb4

13、所有数据表和字段必须写 comment 注释说明

有条件尽量建立数据字典。

14、尽量做到冷热数据分离，减小表的宽度

表越宽，把表装进内存缓冲池时所占用的内存也就越大，也会消耗更多的 IO。

15、禁止在数据表中建立预留字段

预留字段的命名很难做到见名识意，并且无法选择合适的类型，而且对预留字段修改时，会对整张表进行锁定。

16、禁止在数据库中存储图片、文件等大的二进制数据

文件很大时，IO 将会很耗时，也会占用很多带宽，影响响应速度。

建议图片、视频、大文件统一存储在文件存储空间，比如阿里云、腾讯云的对象存储空间和文件存储空间，数据库中只记录文件地址。

17、设置合适的字符存储长度

对象

年龄区间

类型

字节

表示范围

人

150岁以内

unsigned tinyint

1

无符号值 0~255

乌龟

数百岁

unsigned smallint

2

无符号值 0~65535

恐龙化石

数千万年

unsigned int

4

无符号值 0~约42.9亿

太阳

约50亿年

unsigned bigint

8

无符号值 0~约10^19

18、条件允许，就将字符串转换成数字类型存储

比如存储ip时，使用 ip2long 和 long2ip

19、避免使用 enum 类型存储字段

enum 类型的 orderby 操作效率低。

20、建议把所有列定义为 not null

索引 null 列需要额外的空间来保存，要占用更多空间。进行比较时和计算时要对 null 值进行特别处理。

21、禁止在开发环境、测试环境直接连接生产环境数据库

二、索引规范

1、业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

唯一索引影响 insert 的速度可以忽略不计，但会明显提高查询速度。

另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然会有脏数据产生。

2、禁止3个表以上join。需要join的字段，数据类型必须一致，当多表关联时，保证被关联的字段有索引

3、限制每张表上的索引数量，尽量不超过5个

索引增加查询效率的同时，也会降低插入和更新的效率，甚至有时会降低查询效率。

mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成一个最佳的执行计划。

如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，进而降低查询性能。

4、在 varchar 字段上建立索引时，必须指定索引长度

没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。一般对字符串数据，长度为20的索引，区分度就会高达 90%。

可以使用 count(distinct left (列名,索引长度)) / count(*) 来确定区分度。

5、页面搜索严禁左模糊或全模糊，如果需要，请使用搜索引擎解决

索引文件具有最左匹配特性，如果左边的值未确定，则无法使用此索引。

6、如果有order by 的场景，请注意利用索引的有序性

正例：where a=5 and b=10 order by c; 索引 a_b_c 生效

反例：where a>10 order by b 索引中有范围查找，索引 a_b 不生效

7、使用延迟关联或者子查询优化超多分页场景

MySQL 并不是跳过 offset 行，而是取 offset + n 行。

当 offset 特别大时，效率将会非常低，要么控制返回的总页数，要么对超过特定阀值的页数进行 SQL 改写。

正例：先快速定位需要获取的 id 段，然后再关联。

SELECT a.*FROM表1a,(selectidfrom表1where条件LIMIT100000,20)bwherea.id=b.id

实例对比：

selecta.*fromagent_admin a,(selectagent_admin_idfromagent_adminwhereadmin_id=11400limit1000,5)bwherea.agent_admin_id=b.agent_admin_id

0.017s

SELECT*fromagent_adminwhereadmin_id=22240limit1000,5

0.023S

8、建立组合索引时，区分度最高的放在最左边

9、哪些字段最好建索引

(1)经常出现在 where 从句的字段

(2)包含在 order by，group by、distinct 中的字段

10、避免建立重复索引和冗余索引

建立冗余索引，ui增加查询优化器生成执行计划的时间

// 重复索引示例

primary key(id)

index(id)

unique key(id)// 冗余索引示例

index(a,b,c)

index(a,b)

index(a)

11、创建索引时尽量避免如下误解

(1)宁滥勿缺：认为一个查询就需要建立一个索引

(2)宁缺毋滥：任务索引会消耗空间、严重拖慢更新和新增速度

(3)抵制唯一索引：认为业务的唯一性一律需要在应用层通过“先查后插”的方式解决

三、SQL 开发规范

1、不要使用 count(列名) 或 count(常量) 来替代 count(*)

count(*) 是 SQL92 定义的标准统计行数的语法，count(*) 会统计值为NULL的行，而count(列名) 不会统计此列值为 null 的行。

2、在代码中写分页查询逻辑时，如果 count 为 0 ，应直接返回结果，避免继续执行后面的程序再返回结果

3、禁止使用存储过程

存储过程难以调试和扩展，新人接手麻烦，可移植性差。

4、禁止使用外键与级联，一切外键概念必须在应用层解决

以学生和成绩的关系为例，学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的 student_id,同时触发成绩表中的student_id更新，即为级联更新。

外键与级联更新适用于单机低并发，不适合分布式、高并发集群；

级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

5、建议使用预编译语句进行数据库操作

尽量使用框架自带的查询构造器，其底层均封装了预编译处理。如果特殊情况使用不了框架的查询构造器，也要手动预编译查询。

预编译语句可以重复使用优化查询器生成的执行计划，减少 SQL编译 所需要的时间，还可以解决动态 SQL 所带来的的 SQL 注入问题。

6、避免数据类型的隐式转换

selectuser_name,agefromadminwhereadmin_id='11140';

7、禁止使用 select *，必须指定要查询的具体字段

(1)无法使用覆盖索引

注：覆盖索引的含义是 select 的数据列只从索引中就能够取得，不必读取数据行，换句话说查询列已经被所建的索引覆盖。

(2)消耗更多的 cpu 和 IO 以及网络带宽资源。

8、避免使用子查询，可以把子查询优化成join查询

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

注：通常子查询在in子句中，并且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。

9、避免使用JOIN关联太多的表

对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。

在 Mysql 中，对于同一个 SQL 多关联一个表，就会多分配一个关联缓存，在一个SQL 中，关联的表越多，所占用的内存就越大。

如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成服务器内存溢出的情况，进而影响到服务器数据库性能的稳定性。

MySQL 最多允许关联61个表，建议不超过5个

10、减少和数据库的交互操作

合并多个相同的操作到一起，可以提高处理效率。比如批量更新时，将语句处理合并后，在提交到 MySQL 中进行处理，这样效率会更高。

一定要避免在循环中执行 SQL。

11、对同一列进行 or 判断时，使用 in 代替 or

举例：

selectuser_name,agefromadminwherecityin(1024,1028);

in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。但需要注意的是，in 的值不要超过500个。

12、禁止在 SQL 语句中进行函数转换和计算

将数据取出来再在程序中进行处理，比如格式化时间和转换ip时。

13、在明显不会有重复值时使用 UNINON ALL，而不是 UNION

UNION 会把两个结果集的所有数据放到临时表，再进行去重操作

UNINON ALL不会再对结果集进行去重操作

14、拆分复杂的大 SQL 为多个小 SQL

SQL 拆分后可以通过并行执行来提高处理效率。

15、大批量操作分批执行

大批量修改数据，会造成表中大量数据行被锁定，从而造成大量的阻塞。

长时间的阻塞会占满数据库所有的可用连接，使生产环境中的其他应用无法连接到数据库。

因此一定要注意大批量写操作一定要分批执行。