深入MySQL--MySQL行业军规系列-3-

三、表设计规范
11.单实例表数目必须小于1000
解读:我们从MySQL 5.7及8.0官方提供的操作手册中可以看到如下内容:

MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.

MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.


从上述结论可以看到虽然MySQL在数据库数量和数据表数量上没有做明确限制,最终和文件系统等其他机制相关.
但在业务架构设计时,考虑到业务性能和MySQL数据库性能问题,我们还是应该将 MySQL中单实例中的表数量控制在1000以下.

12.单表列数目必须小于30

解读:
同理:我们可以查找到相关的单表字段数相关定义:

MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors:
  • The maximum row size for a table constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. See Row Size Limits.
  • The storage requirements of individual columns constrain the number of columns that fit within a given maximum row size. Storage requirements for some data types depend on factors such as storage engine, storage format, and character set. See Section 11.7, “Data Type Storage Requirements”.
  • Storage engines may impose additional restrictions that limit table column count. For example, InnoDB has a limit of 1017 columns per table. See Section 14.23, “InnoDB Limits”. For information about other storage engines, see Chapter 15, Alternative Storage Engines.
  • Each table has an .frm file that contains the table definition. The definition affects the content of this file in ways that may affect the number of columns permitted in the table. See Limits Imposed by .frm File Structure.



从上述内容能看到MySQL中关于单表字段数量的限制.
但从业务上来讲,还是应该将单表字段数量控制在30内.

13.表必须有主键,例如自增主键
解读:
1.主键递增,数据行写入可以提高插入性能,可以避免频繁的页分裂及页合并操作,减少表碎片,提升空间和内存的使用
2.主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率
3.无主键的表删除,在row模式的主从架构,会导致备库夯住

14.禁止使用外键,如果需要外键完整性约束,需要业务应用进行控制
解读:外键会导致表与表之间耦合,update与delete操作都会涉及相关联的表,十分影响sql 的性能,甚至会造成死锁。高并发情况下容易造成数据库性能,大数据高并发业务场景数据库使用以性能优先





标签: 暂无标签
lee

写了 24 篇文章,拥有财富 103,被 4 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

成为第一个吐槽的人

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈