MySQL的设计规范与性能优化经验

前言

最近在设计一套实验室数据管理系统，背后用MySQL来支撑，所以这里引用一些命名上的规范，做笔记！

sql表及字段命名规范

• 表名用单数，用户表：用user不是users
• 小写。标识符应该全部用小写字母来书写，使用first_name，不是"First_Name"或者“FirstName”。
• 数据类型不是名称。避免使用仅为数据类型的名字（如text或timestamp）。
• 强调单独的单词。由多个单词组成的对象名称应该用下划线分隔，例如使用word_count或team_member_id，而不是wordcount或wordCount。
• 完整的单词，而不是缩写。例如使用middle_name，不是mid_nm。
• 使用常用缩写。对于几个长词而言，缩写词比词本身更为常见，比如i18n和l10n，这时使用缩写。

表列数和行大小的限制

列计数限制

MySQL对于每个表具有4096个列的硬限制，但给定表的有效最大值可能较少。精确的列限制取决于几个因素：

• 表的最大行大小限制列的数量（可能是大小），因为所有列的总长度不能超过此大小。请参阅行大小限制
• 单个列的存储要求限制了适合给定最大行大小的列数。某些数据类型的存储要求取决于存储引擎，存储格式和字符集等因素。请参见数据类型存储要求
• 存储引擎可能会施加限制表列计数的额外限制。例如， InnoDB每个表的限制为1017列。请参见对InnoDB表的限制有关其他存储引擎的信息，请参见替代存储引擎
• 每个表都有一个.frm包含表定义的文件。该定义以可能影响表中允许的列数的方式影响此文件的内容。请参见.frm文件结构引起的限制

行大小限制

给定表格的最大行大小由以下几个因素决定：

• MySQL表的内部表示形式的最大行大小限制为65,535字节，即使存储引擎能够支持较大的行。 BLOB并且 TEXT列只向行大小限制贡献9到12个字节，因为它们的内容与行的其余部分分开存储。
• InnoDB 适用于数据库页面本地存储的数据 的表的最大行大小略小于4KB，8KB，16KB和32KB innodb_page_size 设置的一半页面 。例如，对于默认的16KB InnoDB页面大小，最大行大小略小于8KB 。对于64KB页面，最大行大小略小于16KB。请参见 对InnoDB表的限制
• 如果包含可变长度列的InnoDB 行超过最大行大小，请InnoDB为外部页外存储选择可变长度列，直到该行适合InnoDB 行大小限制。存储在页外的可变长度列的本地存储的数据量按行格式而不同。有关更多信息，请参见 InnoDB行存储和行格式
• 不同的存储格式使用不同数量的页眉和预告片数据，这会影响可用于行的存储量。