php抓取网页数据插入数据库(PM需遵守的数据库设计与创建规范)

　　php抓取网页数据插入数据库(PM需遵守的数据库设计与创建规范)

　　1.一般

　　为规范公司各种环境下数据库系统的设计和创建过程，提高数据库系统的开发效率，保证数据库系统的性能和安全稳定运行，特制定本规范。

　　该规范涉及 PM、RD、SQ 和 DBA 四个角色。数据库系统设计和创建过程中各个角色的工作内容会按照【高风险】、【强制】、【推荐】三个级别进行标注，按照优先级从高到低。

　　对于不符合【高风险】和【强制】两个级别的工作内容，DBA将强制其回电并要求修改。

　　2.适用范围

　　本规范定义的数据库系统包括部署在传统数据中心（物理机如Cisco UCS）和云（公有云、私有云）中的所有数据库实例。相关数据库实例参考数据库开发和创建的相关规范。

　　3.PM 应遵循的数据库设计和创建规范

　　本规范旨在帮助或指导 RD、QA、OP 等技术人员设计和开发适合在线业务的数据库。规范数据库变更及处理流程、数据库表设计、SQL编写等，为公司业务系统稳定健康运行提供保障。

　　3.1 Mysql设计开发规范

　　MySQL数据库与Oracle、SQL Server等商业数据库相比，在内核上各有优劣。我们在使用MySQL数据库进行设计开发时，需要遵循一定的规范，扬长避短。

　　3.1.1 命名约定

　　数据库对象命名规则的范围是管理平台设计和开发中涉及的表。由其他*敏*感*词*内。

　　表、列、序列、过程、函数等数据库对象应按照以下规则命名：

　　【强制】名称必须使用有意义的中英文缩写，以字母开头，不能超过32个字符。

　　【强制】名称必须使用小写字母，

　　【必选】数据库对象名由以下部分组成：范围、类型、名称实体，下划线“_”用于连接各个单词。

　　【必填】各个数据库对象的范围和类型的具体含义和取值，请参考各个数据库对象的命名规则。

　　数据库对象命名规则

　　【强制】数据库对象的名称不允许是数据库的保留字和关键字。

　　SELECT id, name, from, age FROM user not allowed

　　请参阅保留字和关键字的链接

　　选项/变量参考3.1.2 库规范

　　【强制】创建数据库时必须明确指定字符集，字符集只能是utf8或utf8mb4。创建数据库 SQL 示例：

　　创建数据库 db1 默认字符集 utf8；

　　3.1.3 表结构规范

　　【强制】创建表时，必须明确指定字符集为utf8或utf8mb4。

　　【强制】创建表时，必须显式指定表存储引擎类型。如果没有特殊要求，永远都是 InnoDB。当需要使用 InnoDB 以外的存储引擎时，必须经过 DBA 的审核才能在生产环境中使用。因为Innodb表支持事务、行锁、崩溃恢复、MVCC等关系型数据库的重要特性，所以是业界使用最多的MySQL存储引擎。这是大多数其他存储引擎所不具备的，因此 InnoDB 是首选。

　　【强制】表格必须有注释，数据字典必须从一开始就维护。类状态类型需要指定主值的含义，如“0-离线，1-在线”

　　【强制】中间表用于存放中间结果集，名称必须以tmp_开头。备份表用于备份或拍摄源表的快照，名称必须以 bak_ 开头。定期清理中间表和备份表。

　　【强制】对于超过100W行的大表，alter table必须经过DBA审核，并在业务低峰期执行。因为alter table会产生表锁，在此期间阻塞所有对表的写可能对业务有很大的影响。

　　【建议】关于建表时的主键：（1)强制主键为id，类型为int或bigint，为auto_increment；（2)标识的字段表中每一行的主题不应该设置为主键，建议设置其他字段如user_id、order_id等，并建立唯一键索引。因为如果设置为主键和主键值随机插入，会导致innodb内部分页和大量随机I/O，性能下降。

　　【建议】核心表（如用户表、钱相关表）必须要有行数据的创建时间字段create_time和最后更新时间字段update_time，方便排查问题。

　　【建议】建议将表中的blob、text等大字段垂直拆分到其他表中，仅在需要读取这些对象时才选择。

　　【建议】反范式设计：经常需要join查询的字段的冗余副本放在其他表中。比如user_account、user_login_log等表中user_name属性是多余的，减少了join查询。

　　3.1.4 字段说明

　　【建议】表中所有字段必须为NOT NULL属性，商家可根据需要定义DEFAULT值。因为使用NULL值会导致每行额外存储空间、容易出错的数据迁移、聚合函数计算结果偏差等问题。如果不保证插入时一定有值，定义时使用默认值''或0。

　　使用 NUMBER 类型时，必须指定长度。准确度和密度取自NUMBER，保证数据一致性

　　表中字段的命名长度不应超过 30 个字节

　　【推荐】表中自增列（auto_increment属性）推荐使用bigint类型。因为unsigned int的存储范围是-2147483648~2147483647（约21亿），溢出后会报错。

　　【推荐】对于业务中选择性较少的状态、类型等字段，建议使用tinytint或smallint，以节省存储空间。

　　【建议】业务中IP地址字段建议使用int类型，不建议使用char(15)。因为int只占4个字节，可以使用以下函数相互转换, 而 char(15) 至少占用 15 个字节。一旦表数据行数达到 1 亿，就需要使用更多的 1.1G 存储空间。

　　SQL: 选择 inet_aton('192.168.2.12'); 选择 inet_ntoa(3232236044);

　　PHP: ip2long('192.168.2.12'); long2ip(3530427185);

　　【推荐】不推荐使用enum和set。因为它们浪费空间，而且枚举值是硬编码的，不方便更改。建议使用 Tinyint 或 smallint。

　　【推荐】不推荐blob、text等类型。它们都浪费硬盘和内存空间。加载表数据时，会将大字段读入内存，浪费内存空间，影响系统性能。是否真的需要这么大的字段，建议与PM和RD沟通。在 Innodb 中，当一行记录超过 8098 字节时，将选择记录中最长的字段，并将其中的 768 字节放在原创页中，其余字段将放在溢出页中。不幸的是，在紧凑行格式中，原创页面和溢出页面都被加载了。

　　【建议】存钱的字段，推荐使用int，终端乘以100除以100访问。因为 int 占用 4 个字节，double 占用 8 个字节，所以浪费空间。

　　【建议】尽量使用varchar存储文本数据。因为varchar是变长存储，所以比char节省空间。MySQL服务器层规定一行中的所有文本最多可以存储65535字节，所以utf8字符集中最多可以存储21844个字符，会自动转换为mediumtext字段。而text最多可以存储21844个utf8字符集，mediumtext最多可以存储2^24/3个字符，longtext最多可以存储2^32个字符。一般推荐使用varchar类型，字符数不要超过2700。