用了这么多年MySql，这些好习惯你用过哪些

一：新建表和字段建议：

1.所有数据表和字段要有清晰的注释，字段说明

　　说明：不管是创建者还是其他开发或者后续维护者都能清楚知道数据表和字段定义的含义

 

2.表名、字段名使用小写字母或数字，禁止出现数字开头

　　说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写，为了避免出现不必要的麻烦，统一使用小写

 

3.每个列都设置为not null（如果列为BLOB/TEXT类型的，则这个列不能设置为NOT NULL)，且定义默认值

　　说明：3.1：NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回非空行的结果集

　　3.2：使用 concat 函数拼接时，首先要对各个字段进行非 NULL 判断，否则只要任何一个字段为空都会造成拼接的结果为 NULL

　　3.3：当用count函数进行统计时，NULL 列不会计入统计

　　3.4：因为NULL的列使得索引，索引统计和值比较都更复杂，可为NULL的列会使用更多的存储空间，在mysql里也需要特殊处理，当可为NULL的列被索引时，每个索引记录需要一个额外的字节，如果计划在列上建索引，应该避免将列设计为NULL。

 

4.每个表有自增列id且为主键，使用无符号类型unsigned，不作业务逻辑使用

　　说明：4.1：避免存储负值，且扩大了表示范围

　　4.2：如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面

 

5.表达是否概念的列，使用is_xxx的方式命名，数据类型使用unsigned tinyint（1表示是，0表示否）

　　例如：is_valid，1.表示有效 0.表示无效

 

6.禁止使用mysql保留字，例如desc，range，match，action，add，alter，all..等等

　　更多保留字可以参考mysql官方文档说明：https://dev.mysql.com/doc/refman/5.7/en/keywords.html

 

7.varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率

 

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

 

9.命令规范：主键索引名以pk_字段名；唯一索引名uk_字段名；普通索引名idx_字段名；临时表则以tmp为前缀

 

10.小数类型为decimal，禁止使用float和double

　　说明：float和double的存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储

 

11.字段允许适当冗余，以提高查询性能，冗余字段应遵循：1）不是频繁修改的字段。2）不是varchar超长字段，更不能是text字段。

 

12.InnoDB和MyISAM存储引擎表，索引类型选择BTREE；MEMORY表可以根据需要选择HASH或者BTREE类型索引。

 

13.在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面，这样可以更高效检索数据

 

14.合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度

 

对象	年龄区间	类型	字节	表示范围
人	150之内	unsigned tinyint	1	无符号：0-255
龟	数百岁	unsigned smallint	2	无符号：0-65535
恐龙化石	数千年	unsigned int	4	无符号：0-42.9亿
太阳	约50亿年	unsigned bigint	8	无符号：0-约10的19次方

 

二：增删改查好习惯

 

1.SELECT语句指定具体字段名称，禁止写成*，select *会将不该读的数据也从MySQL里读出来，造成不必要的带宽压力

 

2.分页查询，当limit起点较高时，可先用过滤条件进行过滤。如select f1,f2,f3 from table1 limit 20000,20;优化为: select f1,f2,f3 from table1 where id>20000 limit 20

 

3.where条件里等号左右字段类型一致，否则无法利用索引

 

4.在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表

 

5.插入列列表与值列表个数相同，上面二者的个数需要相同，如果没有指定列列表，则值列表长度要与表列数相同。

 

6.更新删除影响行数不要太大，如果太大，进行细粒度拆分

 

7.更新，删除语句记得随手写好where条件(你想删库吗？哈哈)

 

欢迎大家补充，一起建立更优雅的数据规范