mysql创建表字段长度范围_MySQL数据表设计规范
数据命名规范所有数据库对象名称必须使⽤⼩写字母并⽤下划线分割。
所有数据库对象名称禁⽌使⽤ MySQL 保留关键字(如果表名中包含关键字查询时，需要将其⽤单引号括起来)。
数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符。
临时库表必须以 tmp为前缀并以⽇期为后缀，备份表必须以 bak 为前缀并以⽇期 (时间戳) 为后缀。
所有存储相同数据的列名和列类型必须⼀致(⼀般作为关联列，如果查询时关联列类型不⼀致会⾃动进⾏数据类型隐式转换，会造成列上的索引失效，导致查询效率降低)。
数据库基本设计规范
1、所有表必须使⽤ InnoDB 存储引擎没有特殊要求(即 InnoDB ⽆法满⾜的功能如：列存储，存储空间数据等)的情况下，所有表必须使⽤InnoDB 存储引擎 MySQL 5.5 之前默认使⽤ Myisam，5.6 以后默认的为 InnoDBInnoDB ⽀持事务，⽀持⾏级锁，更好的恢复性，⾼并发下性能更好。
2、数据库和表的字符集统⼀使⽤ UTF8MB4兼容性更好，统⼀字符集可以避免由于字符集转换产⽣的乱码，不同的字符集进⾏⽐较前需要进⾏转换会造成索引失效。
3、所有表和字段都需要添加注释使⽤ comment 从句添加表和列的备注 从⼀开始就进⾏数据字典的维护。
4、尽量控制单表数据量的⼤⼩，建议控制在 500 万以内500 万并不是 MySQL 数据库的限制，过⼤会造成修改表结构、备份、恢复都会有很⼤的问题，可以⽤历史数据归档(应⽤于⽇志数据)，分库分表(应⽤于业务数据)等⼿段来控制数据量⼤⼩。
5、谨慎使⽤ MySQL 分区表分区表在物理上表现为多个⽂件，在逻辑上表现为⼀个表 谨慎选择分区键，跨分区查询效率可能更低 建议采⽤物理分表的⽅式管理⼤数据。
6、尽量做到冷热数据分离，减⼩表的宽度MySQL 限制每个表最多存储 4096 列，并且每⼀⾏数据的⼤⼩不能超过 65535 字节 减少磁盘 IO，保证热数据的内存缓存命中率(表越宽，把表装载进内存缓冲池时所占⽤的内存也就越⼤，也会消耗更多的 IO) 更有效的利⽤缓存，避免读⼊⽆⽤的冷数据 经常⼀起使⽤的列放到⼀个表中(避免更多的关联操作)
7、禁⽌在表中建⽴预留字段预留字段的命名很难做到见名识义 预留字段⽆法确认存储的数据类型，所以⽆法选择合适的类型 对预留字段类型的修改，会对表进⾏锁定
8、禁⽌在数据库中存储图⽚，⽂件等⼤的⼆进制数据通常⽂件很⼤，会短时间内造成数据量快速增长，数据库进⾏数据库读取时，通常会进⾏⼤量的随机 IO 操作，⽂件很⼤时，IO 操作很耗时 通常存储于⽂件服务器，数据库只存储⽂件地址信息。。
9、禁⽌在线上做数据库压⼒测试
10、禁⽌从开发环境，测试环境直接连接⽣成环境数据库
数据库字段设计规范
1. 优先选择符合存储需要的最⼩的数据类型
原因列的字段越⼤，建⽴索引时所需要的空间也就越⼤，这样⼀页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多， 索引的性能也就越差
⽅法1、将字符串转换成数字类型存储，如：将 IP 地址转换成整形数据MySQL 提供了两个⽅法来处理 IP 地址
inet_aton 把 ip 转为⽆符号整型 (4-8 位)
inet_ntoa 把整型的 ip 转为地址 插⼊数据前，先⽤ inet_aton 把 IP 地址转为整型，可以节省空间。显⽰数据时，使⽤ inet_ntoa 把整型的 IP 地址转为地址显⽰即可。
2、对于⾮负型的数据(如⾃增 ID、整型 IP)来说，要优先使⽤⽆符号整型来存储，因为⽆符号相对于有符号可以多出⼀倍的存储空间。
SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295
VARCHAR (N) 中的 N 代表的是字符数，⽽不是字节数。使⽤ UTF8 存储 255 个汉字 Varchar (255)=765 个字节。过⼤的长度会消耗更多的内存
2. 避免使⽤ TEXT、BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
MySQL 内存临时表不⽀持 TEXT、BLOB 这样的⼤数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使⽤内存临时表，必须使⽤磁盘临时表进⾏。
⽽且对于这种数据，MySQL 还是要进⾏⼆次查询，会使 SQL 性能变得很差，但是不是说⼀定不能使⽤这样的数据类型。
如果⼀定要使⽤，建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，查询时⼀定不要使⽤ select * ⽽只需要取出必要的列，不需要TEXT 列的数据时不要对该列进⾏查询。
TEXT 或 BLOB 类型只能使⽤前缀索引
因为 MySQL 对索引字段长度是有限制的，所以 TEXT 类型只能使⽤前缀索引，并且 TEXT 列上是不能有默认值的。
3. 避免使⽤ ENUM 类型修改 ENUM 值需要使⽤ ALTER 语句
ENUM 类型的 ORDER BY 操作效率低，需要额外操作
禁⽌使⽤数值作为 ENUM 的枚举值
4. 尽可能把所有列定义为 NOT NULL
原因索引 NULL 列需要额外的空间来保存，所以要占⽤更多的空间。
进⾏⽐较和计算时要对 NULL 值做特别的处理。
5. 使⽤ TIMESTAMP(4 个字节)或 DATETIME 类型(8 个字节)存储时间TIMESTAMP 存储的时间范围 19
70-01-01 00:00:01 ~ 2038-01-19-03:14:07。
TIMESTAMP 占⽤ 4 字节和 INT 相同，但⽐ INT 可读性⾼，超出 TIMESTAMP 取值范围的使⽤ DATETIME 类型存储。
经常会有⼈⽤字符串存储⽇期型的数据(不正确的做法)：
缺点 1：⽆法⽤⽇期函数进⾏计算和⽐较。
缺点 2：⽤字符串存储⽇期要占⽤更多的空间。
6. 同财务相关的⾦额类数据必须使⽤ decimal 类型⾮精准浮点：float，double
精准浮点：decimal
Decimal 类型为精准浮点数，在计算时不会丢失精度。占⽤空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且⼩数点要占⽤⼀个字节。可⽤于存储⽐ bigint 更⼤的整型数据。
索引设计规范
1. 限制每张表上的索引数量，建议单张表索引不超过 5 个索引并不是越多越好！索引可以提⾼效率同样
也可以降低效率；索引可以增加查询效率，但同样也会降低插⼊和更新的效率，甚⾄有些情况下会降低查询效率。
因为 MySQL 优化器在选择如何优化查询时，会根据统⼀信息，对每⼀个可以⽤到的索引来进⾏评估，以⽣成出⼀个最好的执⾏计划，如果同时有很多个索引都可以⽤于查询，就会增加 MySQL 优化器⽣成执⾏计划的时间，同样会降低查询性能
2. 禁⽌给表中的每⼀列都建⽴单独的索引5.6 版本之前，⼀个 SQL 只能使⽤到⼀个表中的⼀个索引，5.6 以后，虽然有了合并索引的优化⽅式，但是还是远远没有使⽤⼀个联合索引的查询⽅式好
3. 每个 InnoDB 表必须有个主键InnoDB 是⼀种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有⼀种 InnoDB 是按照主键索引的顺序来组织表的。
不要使⽤更新频繁的列作为主键，不适⽤多列主键(相当于联合索引) 不要使⽤ UUID、MD5、HASH、字符串列作为主键(⽆法保证数据的顺序增长)。主键建议使⽤⾃增 ID 值。
常见索引列建议出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列。
包含在 ORDER BY、GROUP BY、DISTINCT 中的字段。
并不要将符合 1 和 2 中的字段的列都建⽴⼀个索引，通常将 1、2 中的字段建⽴联合索引效果更好。
多表 JOIN 的关联列。
如何选择索引列的顺序
建⽴索引的⽬的是：希望通过索引进⾏数据查，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读⼊的数据也就越少。区分度最⾼的放在联合索引的最左侧(区分度 = 列中不同值的数量 / 列的总⾏数)。
尽量把字段长度⼩的列放在联合索引的最左侧(因为字段长度越⼩，⼀页能存储的数据量越⼤，IO 性能也就越好)。
使⽤最频繁的列放到联合索引的左侧(这样可以⽐较少的建⽴⼀些索引)。
避免建⽴冗余索引和重复索引因为这样会增加查询优化器⽣成执⾏计划的时间。
重复索引⽰例：primary key (id)、index (id)、unique index (id)
冗余索引⽰例：index (a,b,c)、index (a,b)、index (a)
优先考虑覆盖索引对于频繁的查询优先考虑使⽤覆盖索引。
覆盖索引就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引
覆盖索引的好处：避免 InnoDB 表进⾏索引的⼆次查询
InnoDB 是以聚集索引的顺序来存储的，对于 InnoDB 来说，⼆级索引在叶⼦节点中所保存的是⾏的主键信息，如果是⽤⼆级索引查询数据的话，在查到相应的键值后，还要通过主键进⾏⼆次查询才能获取我们真实所需要的数据。⽽在覆盖索引中，⼆级索引的键值中可以获取所有的数据，避免了对主键的⼆次查询 ，减少了 IO 操作，提升了查询效率。
可以把随机 IO 变成顺序 IO 加快查询效率
由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查来说，对⽐随机从磁盘读取每⼀⾏的数据 IO 要少的多，因此利⽤覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查的顺序 IO。
索引 SET 规范尽量避免使⽤外键约束。
不建议使⽤外键约束(foreign key)，但⼀定要在表与表之间的关联键上建⽴索引。
外键可⽤于保证数据的参照完整性，但建议在业务端实现。
外键会影响⽗表和⼦表的写操作从⽽降低性能。
数据库 SQL 开发规范建议使⽤预编译语句进⾏数据库操作预编译语句可以重复使⽤这些计划，减少 SQL 编译所需要的时间，还可以解决动态 SQL 所带来的 SQL 注⼊的问题 只传参数，⽐传递 SQL 语句更⾼效 相同语句可以⼀次解析，多次使⽤，提⾼处理效率。避免数据类型的隐式转换隐式转换会导致索引失效。如：
select name,phone from customer where id = '111';
3. 充分利⽤表上已经存在的索引避免使⽤双 % 号的查询条件。
如 a like '%123%'，(如果⽆前置 %，只有后置 %，是可以⽤到列上的索引的)
⼀个 SQL 只能利⽤到复合索引中的⼀列进⾏范围查询
varchar2最大长度如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被⽤到，在定义联合索引时，如果 a 列要⽤到范围查的话，就要把 a 列放到联合索引的右侧。
使⽤ left join 或 not exists 来优化 not in 操作， 因为 not in 也通常会使⽤索引失效。
4. 数据库设计时，应该要对以后扩展进⾏考虑
5. 程序连接不同的数据库使⽤不同的账号，禁⽌跨库查询为数据库迁移和分库分表留出余地
降低业务耦合度
避免权限过⼤⽽产⽣的安全风险
6. 禁⽌使⽤ SELECT * 必须使⽤ SELECT 查询消耗更多的 CPU 和 IO 以⽹络带宽资源
⽆法使⽤覆盖索引
可减少表结构变更带来的影响
7. 禁⽌使⽤不含字段列表的 INSERT 语句
如：
insert into values ('a','b','c');
应使⽤：
insert into t(c1,c2,c3) values ('a','b','c');
8. 避免使⽤⼦查询，可以把⼦查询优化为 JOIN 操作通常⼦查询在 in ⼦句中，且⼦查询中为简单 SQL (不包含 union、group by、order by、limit 从句) 时，才可以把⼦查询转化为关联查询进⾏优化。
⼦查询性能差的原因：⼦查询的结果集⽆法使⽤索引，通常⼦查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到⼀定的影响。
特别是对于返回结果集⽐较⼤的⼦查询，其对查询性能的影响也就越⼤。
由于⼦查询会产⽣⼤量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产⽣⼤量的慢查询。
9. 避免使⽤ JOIN 关联太多的表对于 MySQL 来说，是存在关联缓存的，缓存的⼤⼩可以由 join_buffer_size 参数进⾏设置。
在 MySQL 中，对于同⼀个 SQL 多关联(join)⼀个表，就会多分配⼀个关联缓存，如果在⼀个 SQL 中关联的表越多，所占⽤的内存也就越⼤。
如果程序中⼤量的使⽤了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。
同时对于关联操作来说，会产⽣临时表操作，影响查询效率 MySQL 最多允许关联 61 个表，建议不超过 5 个。
10. 减少同数据库的交互次数数据库更适合处理批量操作 合并多个相同的操作到⼀起，可以提⾼处理效率
11. 对应同⼀列进⾏ or 判断时，使⽤ in 代替 orIn 的值不要超过 500 个， in 操作可以更有效的利⽤索引，or ⼤多数情况下很少能利⽤到索引。
12. 禁⽌使⽤ order by rand () 进⾏随机排序会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机⽣成的值进⾏排序，并且可能会对每⼀⾏都⽣成⼀个随机值，如果满⾜条件的数据集⾮常⼤，就会消耗⼤量的 CPU 和 IO 及内存资源。
推荐在程序中获取⼀个随机值，然后从数据库中获取数据的⽅式。
13. WHERE 从句中禁⽌对列进⾏函数转换和计算对列进⾏函数转换或计算时会导致⽆法使⽤索引。
不推荐
where date(create_time)='20190101'
推荐
where create_time >= '20190101' and create_time < '20190102'
14. 在明显不会有重复值时使⽤ UNION ALL ⽽不是 UNIONUNION 会把两个结果集的所有数据放到临时表中后再进⾏去重操作。
UNION ALL 不会再对结果集进⾏去重操作。
15. 拆分复杂的⼤ SQL 为多个⼩ SQL⼤ SQL：逻辑上⽐较复杂，需要占⽤⼤量 CPU 进⾏计算的 SQL 。
MySQL：⼀个 SQL 只能使⽤⼀个 CPU 进⾏计算。
SQL 拆分后可以通过并⾏执⾏来提⾼处理效率。
数据库操作⾏为规范
1. 超 100 万⾏的批量写(UPDATE、DELETE、INSERT)操作，要分批多次进⾏操作⼤批量操作可能会造成严重的主从延迟
主从环境中，⼤批量操作可能会造成严重的主从延迟，⼤批量的写操作⼀般都需要执⾏⼀定长的时间，⽽只有当主库上执⾏完成后，才会在其他从库上执⾏，所以会造成主库与从库长时间的延迟情况
Binlog ⽇志为 row 格式时会产⽣⼤量的⽇志
⼤批量写操作会产⽣⼤量⽇志，特别是对于 row 格式⼆进制数据⽽⾔，由于在 row 格式中会记录每⼀⾏数据的修改，我们⼀次修改的数据越多，产⽣的⽇志量也就会越多，⽇志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的⼀个原因。
避免产⽣⼤事务操作⼤批量修改数据，⼀定是在⼀个事务中进⾏的，这就会造成表中⼤批量数据进⾏锁定，从⽽导致⼤量的阻塞，阻塞会对MySQL 的性能产⽣⾮常⼤的影响。
特别是长时间的阻塞会占满所有数据库的可⽤连接，这会使⽣产环境中的其他应⽤⽆法连接到数据库，因此⼀定要注意⼤批量写操作要进⾏分批。
2. 对于⼤表使⽤ pt-online-schema-change 修改表结构避免⼤表修改产⽣的主从延迟
避免在对表字段进⾏修改时进⾏锁表
对⼤表数据结构的修改⼀定要谨慎，会造成严重的锁表操作，尤其是⽣产环境，是不能容忍的。
pt-online-schema-change 它会⾸先建⽴⼀个与原表结构相同的新表，并且在新表上进⾏表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加⼀些触发器。
把原表中新增的数据也复制到新表中，在⾏所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉，把原来⼀个 DDL 操作，分解成多个⼩的批次进⾏。
3. 禁⽌为程序使⽤的账号赋予 super 权限当达到最⼤连接数限制时，还运⾏ 1 个 有 super 权限的⽤户连接 super 权限只能留给 DBA 处理问题的账号使⽤。
4. 对于程序连接数据库账号，遵循权限最⼩原则程序使⽤数据库账号只能在⼀个 DB 下使⽤，不准跨库 程序使⽤的账号原则上不准有 drop 权限。
以上内容希望帮助到⼤家，很多PHPer在进阶的时候总会遇到⼀些问题和瓶颈，业务代码写多了没有⽅向感，不知道该从那⾥⼊⼿去提升，对此我整理了⼀些资料，包括但不限于：分布式架构、⾼可扩展、⾼性能、⾼并发、服务器性能调优、
TP6，laravel，YII2，Redis，Swoole、Swoft、Kafka、Mysql优化、shell脚本、Docker、微服务、Nginx等多个知识点⾼级进阶⼲货需要的可以免费分享给⼤家，需要点击PHP进阶架构师视频资料、⾯试⽂档免费获取d ocs.qq
或 者关注咱们下⾯的知乎专栏PHP7进阶架构师z huanlan.zhihu

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