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、字段类型为字符串时需要注意的
javascript函数式编程指南
如果存储的字符串长度⼏乎相等，则应该使⽤ 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、尽量做到冷热数据分离，减⼩表的宽度
mysql无法连接到服务器表越宽，把表装进内存缓冲池时所占⽤的内存也就越⼤，也会消耗更多的 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、建⽴组合索引时，区分度最⾼的放在最左边
linux查看文件夹内文件数量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)
body是什么中文意思
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、⼤批量操作分批执⾏
⼤批量修改数据，会造成表中⼤量数据⾏被锁定，从⽽造成⼤量的阻塞。
长时间的阻塞会占满数据库所有的可⽤连接，使⽣产环境中的其他应⽤⽆法连接到数据库。
因此⼀定要注意⼤批量写操作⼀定要分批执⾏。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。