mysql数据库开发规范【推荐】

这篇文章主要介绍了MySQL数据库开发规范，包括命名规范、库表基础规范等方面的内容。这些规范有助于确保数据库的设计清晰、合理，并且易于维护。对于正在从事或即将从事数据库开发的朋友来说，这篇文章具有很高的参考价值。

一、命名规范

在数据库开发中，命名规范是非常重要的。本文提出的命名规范包括了库名、表名、字段名的命名规则。

1. 库名、表名、字段名必须使用小写字母，并采用下划线分割。这样做的好处在于，MySQL数据库对大小写敏感，使用小写字母可以避免因大小写不一致导致的问题。采用下划线分割可以提高名称的可读性。

2. 库名以“d”开头，表名以“t”开头，字段名以“f_”开头。这样的命名方式有助于快速识别数据库中的不同类型。视图以“view_”开头，事件以“event_”开头，触发器以“trig_”开头等。普通索引和唯一索引的命名也给出了具体的格式要求。这样的规范可以使数据库结构更加清晰。

3. 库名、表名、字段名的长度不能超过32个字符。这样可以确保名称简洁明了，减少传输量，并方便识别。临时库和表的命名方式也给出了具体规定。按日期时间分表的格式也给出了明确要求。这些规范有助于确保数据库的长期维护和管理。

二、库表基础规范

除了命名规范外，库表的基础规范也非常重要。本文介绍了以下几个方面的规范：存储引擎的选择、字符集的选择以及表的设计原则等。

1. 使用InnoDB存储引擎。InnoDB是MySQL默认的存储引擎，它支持事务处理，具有更好的性能和扩展性。在开发过程中应选择InnoDB作为存储引擎。还提到了MySQL版本升级后系统表的变化趋势。这也提醒我们在开发过程中要关注数据库的升级和更新情况。

2. 表字符集统一使用UTF-8字符集进行存储数据，这对于支持多语言环境和特殊字符非常重要。同时提到了UTF-8字符集和UTF-8MB4字符集的选择原则以及处理emoji等表情符号的需求时应该申请使用UTF-8MB4字符集的理由与条件要求 。在实际项目中尽量避免出现不同表或同一表中字符集不一致的情况，造成不必要的麻烦和潜在风险 。校对字符集使用默认的 utf8_general_ci 。连接的客户端也使用utf8 ，建立连接时指定charset或SET NAMES UTF8 。这样可以确保数据的正确性和一致性 。同时提醒开发者遇到不同客户端环境连接时字符集转换的问题也要妥善处理 。为开发者提供关于不同编码方式的选型建议和解决策略 。使得数据在不同的环境和平台间传递保持准确性 。统一规范的编码设置能极大减少数据乱码的风险 。对于已经存在的项目中的编码问题也给出了相应的处理建议 。同时也提到了字符集转换过程中可能遇到的问题和风险 ，提醒开发者注意防范和解决 。此外还提到了对于已经存在的项目中的编码问题处理建议 。在实际开发中对于特殊业务场景中的字符集选择需要根据具体业务需求来评估确定并妥善处理可能出现的编码问题 。 对于使用中需要注意的陷阱进行规避与预防避免出现编码混乱数据不一致等情况的发生影响业务的正常运营和数据的可靠性 。除了这些基本规范外 ，还需要注意字段注释的重要性以及单表字段数量的控制 。字段注释可以方便后续维护和开发 ，单表字段数量的控制可以提高IO效率和内存缓存的效率 ，从而提高响应速度和并发能力 。此外还提到了表结构设计的原则包括冷热数据分离大字段分离以及常在一起做条件和返回列的不分离等原则来指导开发者进行合理的表结构设计以满足业务需求和性能要求 。总之 ，合理的数据库开发规范是保证数据库稳定运行和提高开发效率的基础 。在实际开发中应严格遵守这些规范以确保数据库的安全性和稳定性 。希望这些规范能对从事数据库开发的朋友有所帮助 。关于数据库设计规范和字段定义的指导原则

一、主键设计原则

在数据库设计中，所有表都必须显式指定主键。主键的选择应遵循以下原则：

1. 主键应尽量采用自增方式。对于InnoDB表，由于其索引组织表的特性，顺序存储可以提高存取效率，并有效利用磁盘空间。在某些复杂查询需要自连接时，自增主键会起到优化作用。

2. 当需要全局唯一主键时，可以考虑使用外部发号器（如ticket server）。目前我们正在建设中。

3. 如果没有指定主键或唯一索引，更新和删除操作将会对每一行进行全表扫描，这将大大降低效率。

4. 在某些特定情况下，可以考虑使用联合唯一主键，但在决定前需与数据库管理员（DBA）协商。

二、关于外键、存储过程、视图、触发器、事件的使用规范

1. 不强制使用外键。即使两个表之间存在明确的外键关系，也不应使用FOREIGN KEY约束。新的记录会到主键表进行校验，这可能会影响性能。

2. 适度使用存储过程和视图。存储过程可以简化业务端代码，但在互联网企业中，由于变更频繁和分库分表的实施，升级存储过程可能会很麻烦。存储过程不应忽略执行失败的情况。视图可以在一定程度上降低SQL的复杂度，但有时为了通用性可能会损失性能（例如返回不必要的字段）。

3. 禁止在数据库中使用触发器和事件。触发器是基于行的触发，针对一条记录而不是整个SQL语句。如果变更的数据集非常大，触发器的效率会很低，并且可能难以分析和定位问题。当需要进行数据定义语言（DDL）操作时，无法使用像pt-osc这样的工具，应在事务中执行。事件是一种偷懒的表现，定时任务执行失败可能会影响业务，并且MySQL无法对其进行失败预警。建议建立专门的job scheduler平台。

三、字段定义规范

关于字段类型的选择和使用规范：

1. 对于长度基本固定的列，如果更新特别频繁且恰好适合char类型的特点，可以选择char类型。

2. varchar类型用于存储变长字符串，但长度不宜过小或过大。UTF8编码最多可以存储21844个汉字或65532个英文字符。

3. varbinary类型用于存储二进制字符串，它按字节存储而不考虑字符集，主要用于排序或比较大小写敏感的数据，不适合存储密码等敏感信息。

4. text类型与varchar类似，用于存储可变长度的数据。由于其特性，当使用text类型时可能需要考虑额外的寻址操作。在某些场景下，为了避免行溢出，可能需要将text或blob字段拆分到另一个表中。

5. blob类型可以看作是varbinary的扩展版本，用于存储二进制数据，无字符集区分大小写。一种常见的应用场景是存储图片等二进制文件，但实际上并不建议在数据库中直接存储图片等大数据文件。在需要全文搜索的情况下，可以考虑使用像Sphinx这样的第三方搜索引擎。如果需要创建索引（如前缀索引），可能会影响性能，需要根据具体场景来决定是否使用。

以上是关于数据库设计和字段定义的规范和建议。在实际应用中需要根据具体情况进行权衡和选择，以达到最佳的性能和效率。数字类型定义与数据库字段规范

一、数字类型的选择与应用

在数据库设计中，选择合适的数字类型对于数据的存储和查询效率至关重要。

1. 使用tinyint替代enum和boolean

ENUM类型在修改或增加枚举值时需要进行在线DDL操作，成本较高。如果ENUM列值包含数字，可能引起默认值混淆。而tinyint仅使用1个字节，适用于存储status、type、flag等列数据。

2. 建议使用UNSIGNED存储非负数值

采用UNSIGNED可以扩大一倍的使用数值范围，对于存储非负数据尤为适用。

3. int类型固定使用4个字节存储，int(11)与int(4)的区别仅在于显示宽度

在设计数据库时，不必过于关注int后的数字，其实际存储大小固定为4个字节，不同显示宽度的设定不会影响存储需求。

4. 使用Decimal代替float/double存储精确浮点数

对于需要精确计算的场景，如货币、金额等，推荐使用decimal类型，如decimal(9,2)。而float类型默认只能精确到6位有效数字，不适用于高精度需求。

二、时间戳与日期时间类型的选择

在数据库设计中，时间戳与日期时间类型的选择关系到数据存储和查询的效率。

1. datetime和timestamp类型的存储空间及时间范围不同

datetime类型占用8个字节，能表示的时间范围为1000-01-01 00:00:00到9999-12-31 23:59:59。而timestamp类型占用4个字节，时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07。根据实际需求选择合适的时间类型。

在设计表结构时，可以利用timestamp的这一特性，通过设定字段属性实现时间的自动更新。

3. timestamp类型显示与时区有关，受到严格模式的限制

在使用timestamp类型时，需考虑其时区特性及严格模式的限制。

三、字段定义规范

在数据库设计中，字段的定义关乎数据完整性、查询效率及系统性能。

1. 建议字段都定义为NOT NULL

2. 同一意义的字段定义必须相同

不同表中的同一字段，如f_user_id，其类型、字段长度应设计成一致，以确保数据的一致性和查询效率。

四、索引规范与使用

索引是提升数据库查询效率的关键。任何新的select、update、delete操作上线前，都应通过explain命令检查索引的使用情况。

1. 避免extra列出现"Using File Sort"、"Using Temporary"，以及谨慎处理rows超过1000的情况。

深入了解MySQL索引：如何强制使用或忽略索引，以及更多关键概念

在MySQL查询中，我们有时需要强制或忽略某些索引以提高查询效率。通过使用FORCE INDEX、USE INDEX或IGNORE INDEX，我们可以影响MySQL如何选择使用或忽略possible_keys列中的索引。

当我们谈论MySQL的查询优化时，ref列是一个重要指标。它显示了哪些列或常量被用于查找索引列上的值，从而选择适当的key列索引。

而rows列则根据表统计信息和索引选择情况，估算找到所需记录所需读取的行数。这为我们提供了一个关于查询效率的粗略估计。

深入了解Extra列中的信息也是至关重要的。例如，Using temporary表示MySQL需要使用临时表来存储结果集，这在排序和分组查询中尤为常见。而Using filesort则意味着MySQL中无法利用索引完成的排序操作。

关于索引的使用，有几个关键原则我们需要遵循：

1. 索引策略：虽然索引能加速查询，但也会增加维护负担和IO压力。单张表的索引数量应控制在5个以内，或不超过表字段个数的20%。若需要在多个字段上单独使用索引，需经过数据库管理员(DBA)的评估。

2. 避免冗余索引：InnoDB表使用索引组织表，主键和数据放在一起。普通索引最终指向的是主键地址。如果主键已经是一列，那么额外的联合索引中的重复字段就是多余的。例如，如果f_crm_id是主键，那么联合索引(f_user_id, f_crm_id)中的f_crm_id就是多余的。我们应该利用前缀索引来加速查询并减轻维护负担。

3. 主键选择：如果没有特殊要求，最好使用自增ID作为主键。这是因为主键是一种聚集索引，顺序写入。如果使用组合唯一索引作为主键，则是随机写入，更适合写操作较少的表。需要注意的是，主键是不允许更新的。

4. 索引的选择性：我们应该尽量在选择性高的列上建立索引。选择性是指一个列中不同值的数量与总行数之比。如果一个列的选择性接近1，那么在该列上使用索引的过滤效果就很好。当走索引扫描的行数超过30%时，考虑改为全表扫描。

5. 最左前缀原则：在使用联合索引时，MySQL会从左到右进行匹配。遇到断开或范围查询时，无法利用后续的索引列。例如，对于索引idx_c1_c2_c3 (c1, c2, c3)，只有当where条件包含特定的字段比较时才能充分利用索引。遇到范围查询时，只有部分索引会被使用。where条件中的字段顺序与索引顺序无关，MySQL优化器会自动调整顺序。

6. 前缀索引的使用：对于超过30个字符长度的列，可以考虑使用前缀索引来提高查找效率并节省空间。前缀索引在ORDER BY或GROUP BY时可能无法使用，也不能作为覆盖索引。

7. 合理使用覆盖索引减少IO：在InnoDB存储引擎中，非主键索引（又称为辅助索引或二级索引）不直接存储行地址，而是存储主键值。合理利用覆盖索引可以减少IO操作，提高查询效率。

理解并合理运用MySQL的索引机制是提高数据库性能的关键。通过遵循上述原则和建议，我们可以更有效地使用和管理MySQL索引，从而提高查询效率并优化数据库性能。当查询需求涉及到secondary index中没有的数据列时，首先需要利用secondary index查找到主键值，然后再通过主键检索其他数据列，这一过程需要两次查询。覆盖索引能够在单次查询中提供所有需要的数据列，避免了回表进行二次查找的麻烦，从而显著提高了IO效率。

例如，在查询语句SELECT email，uid FROM user_email WHERE uid=中，如果uid不是主键，我们可以通过添加索引来提升查询性能。适当的索引策略是将经常一起查询的列组合在一起，如index(uid，email)。

在SQL设计中，应避免直接SELECT读取全部字段，以减少网络带宽的消耗，并有效利用覆盖索引。即使表结构发生变化，这种设计对程序的影响也会降到最低。

当确定查询结果只有一条记录时，应使用limit 1来优化查询。在保证数据准确的前提下，如果能够确定结果集的数量，使用limit可以更快地返回结果。

在SQL查询中需要小心隐式类型转换。转换规则包括：两个参数中至少有一个是NULL时，比较的结果也是NULL；两个参数都是字符串或都是整数时，会按照相应的类型进行比较，不需要转换。在其他情况下，两个参数可能会被转换为浮点数进行比较。需要注意的是，如果一个索引是建立在string类型上，而查询条件中使用了int类型的值，可能会发生隐式转换，导致性能问题。

应禁止在where条件列上使用函数，这会导致索引失效。如果需要在SQL中使用函数，可以考虑将函数应用到返回列上，而不是条件列。

在使用like进行模糊匹配时，应将通配符%放在搜索词的末尾。否则，可能会导致索引失效。在这种情况下，可以考虑使用全文搜索工具如sphinx来优化搜索。

对于复杂的SQL查询，应先参考已有的索引设计，并使用explain来分析查询计划。通过简单的SQL拆分或者以union all的方式组合多个查询，可以更好地利用索引。先使用explain的好处在于，可以根据索引的情况添加更多的查询限制条件，优化查询性能。

当我们在处理数据库查询时，优化策略至关重要。以下是一些针对数据库查询优化的重要见解和建议：

当处理数据库中的where条件时，考虑更改某些条件以提高查询效率。例如，将where条件中的t2.c=4更改为t1.c=4，可以更好地利用索引。这样的改变可能源于数据库字段冗余设计的情况，即反范式设计。在这种情况下，应仔细选择join的类型，确保使用最适合的inner join或left join。

在编写SQL查询时，尽量减少子查询的使用，转而使用join操作。在MySQL 5.6版本之前，子查询的效率相对较低。但从该版本开始，经过优化，使用join通常更为高效。当需要联合多个查询结果时，考虑使用union all而不是union。因为union all不会进行重复项的检查，速度更快。但要注意，在使用union all时，确保结果集中不包含重复数据。

在处理包含大量值的IN列表时，应考虑使用批量处理的方式。一次性执行大量值可能会影响数据库的并发能力。建议IN列表中的值不超过200个。超过此数量时，应考虑分批处理。

对于大型事务，应谨慎处理。在一个事务中进行多个select和update操作可能会严重影响MySQL的并发能力。事务持有的锁等资源只有在事务完成时才会释放。在高频事务场景下，应尽量减少事务的大小。但也要权衡数据写入的一致性。

深入理解数据库操作：从SELECT到行为规范

我们常常听到关于数据库操作中的DELETE和UPDATE语句可能导致性能问题，但实际上，SELECT查询在某些情况下也可能成为瓶颈。今天，我们将深入数据库操作的一些重要方面，并通过SELECT语句进行解释。

让我们理解一个常见的误区。很多人认为SELECT查询仅仅是简单的读取操作，不会导致数据库变慢。但实际上，当面对大量数据时，不恰当的SELECT查询也可能导致性能下降。而写操作，如DELETE和UPDATE，虽然它们确实可能引起锁表问题，但合理的优化和规划同样可以使其运行得更高效。

高效操作的前提是杜绝危险SQL。我们应避免使用无意义或恒真的条件，如"where 1=1"，在update/delete或遭遇SQL注入时，这可能会带来严重问题。SQL中不应包含DDL语句，一般也不应给予create/alter等权限。

在行为规范方面，我们需要注意以下几点：不允许在DBA不知情的情况下导出现网数据；大批量更新或修复数据时，应避开高峰期并通知DBA；及时处理已下线的业务SQL；复杂SQL上线前需要审核，对于多表join、count、group by等复杂操作，应主动上报DBA进行评估；重要项目的数据库方案选型和设计必须提前通知DBA参与。

以上内容是我们对数据库操作的一些理解和规范，希望对大家有所帮助。在进行数据库操作时，合理的规划和优化至关重要。感谢大家的阅读和支持，让我们共同提高数据库操作的效率和安全性。

以上内容仅作为参考，实际操作中还需根据实际情况进行调整和优化。对于数据库操作，我们还需不断学习和更高效的方法和技巧。通过合理的规划和优化，我们可以更好地利用数据库资源，提高系统的性能和稳定性。