微网站成功案例,做哪种网站浏览量比较高,wordpress获取照片信息,2017网站开发语言文章目录一. 命名规范二. 库表基础规范三. 字段规范四. 索引规范五. SQL设计①.正规化表设计原则②.SQL设计③.“三少原则”六. 行为规范一. 命名规范
1.库名、表名、字段名必须使用小写字母#xff0c;并采用下划线分割 (1)MySQL有配置参数lower_case_table_names1#xff…
文章目录一. 命名规范二. 库表基础规范三. 字段规范四. 索引规范五. SQL设计①.正规化表设计原则②.SQL设计③.“三少原则”六. 行为规范一. 命名规范
1.库名、表名、字段名必须使用小写字母并采用下划线分割 (1)MySQL有配置参数lower_case_table_names1即库表名以小写存储大小写不敏感。如果是0则库表名以实际情况存储大小写敏感如果是2以实际情况存储但以小写比较。 (2)如果大小写混合使用可能存在abcAbcABC等多个表共存容易导致混乱。 (3)字段名显示区分大小写但实际使⽤时不区分即不可以建立两个名字一样但大小写不一样的字段。 (4)为了统一规范 库名、表名、字段名使用小写字母。 2.库名以 d 开头表名以 t 开头字段名以 f_ 开头 (1)比如表 t_crm_relation中间的 crm 代表业务模块名 (2)视图以view_开头事件以event_开头触发器以trig_开头存储过程以proc_开头函数以func_开头 (3)普通索引以idx_col1_col2命名唯一索引以uk_col1_col2命名可去掉f_公共部分。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time) 3.库名、表名、字段名禁止超过32个字符需见名知意 库名、表名、字段名支持最多64个字符但为了统一规范、易于辨识以及减少传输量禁止超过32个字符 4.临时库、表名须以tmp加日期为后缀 如 t_crm_relation_tmp0425。备份表也类似形如 _bak20160425 。 5.按日期时间分表须符合_YYYY[MM][DD]格式 这也是为将来有可能分表做准备的比如t_crm_ec_record_201403但像 t_crm_contact_at201506就打破了这种规范。 不具有时间特性的直接以 t_tbname_001 这样的方式命名。
二. 库表基础规范
1.使用Innodb存储引擎 5.5版本开始mysql默认存储引擎就是InnoDB5.7版本开始系统表都放弃MyISAM了。 2.表字符集统一使用UTF8 (1)UTF8字符集存储汉字占用3个字节存储英文字符占用一个字节 (2)校对字符集使用默认的 utf8_general_ci (3)连接的客户端也使用utf8建立连接时指定charset或SET NAMES UTF8;。对于已经在项目中长期使用latin1的救不了了 (4)如果遇到EMOJ等表情符号的存储需求可申请使用UTF8MB4字符集 3.所有表都要添加注释 (1)尽量给字段也添加注释 (2)类status型需指明主要值的含义如”0-离线1-在线” 4.控制单表字段数量 (1)单表字段数上限30左右再多的话考虑垂直分表一是冷热数据分离二是大字段分离三是常在一起做条件和返回列的不分离。 (2)表字段控制少而精可以提高IO效率内存缓存更多有效数据从而提高响应速度和并发能力后续 alter table 也更快。 5.所有表都必须要显式指定主键 (1)主键尽量采用自增方式InnoDB表实际是一棵索引组织表顺序存储可以提高存取效率充分利用磁盘空间。还有对一些复杂查询可能需要自连接来优化时需要用到。 (2)需要全局唯一主键时使用外部发号器ticket server建设中 (3)如果没有主键或唯一索引update/delete是通过所有字段来定位操作的行相当于每行就是一次全表扫描 (4)少数情况可以使用联合唯一主键需与DBA协商 6.不强制使用外键参考 即使2个表的字段有明确的外键参考关系也不使用 FOREIGN KEY 因为新纪录会去主键表做校验影响性能。 7.适度使用存储过程、视图禁止使用触发器、事件 (1)存储过程procedure虽然可以简化业务端代码在传统企业写复杂逻辑时可能会用到而在互联网企业变更是很频繁的在分库分表的情况下要升级一个存储过程相当麻烦。又因为它是不记录log的所以也不方便debug性能问题。如果使用过程一定考虑如果执行失败的情况。 (2)使用视图一定程度上也是为了降低代码里SQL的复杂度但有时候为了视图的通用性会损失性能比如返回不必要的字段。 (3)触发器trigger也是同样但也不应该通过它去约束数据的强一致性mysql只支持“基于行的触发”也就是说触发器始终是针对一条记录的而不是针对整个sql语句的如果变更的数据集非常大的话效率会很低。掩盖一条sql背后的工作一旦出现问题将是灾难性的但又很难快速分析和定位。再者需要ddl时无法使用pt-osc工具。放在transaction执行。 (4)事件event也是一种偷懒的表现目前已经遇到数次由于定时任务执行失败影响业务的情况而且mysql无法对它做失败预警。建立专门的 job scheduler 平台。 a.单表数据量控制在5000w以内 b.数据库中不允许存储明文密码
三. 字段规范
1.char、varchar、text等字符串类型定义 (1)对于长度基本固定的列如果该列恰好更新又特别频繁适合char (2)varchar虽然存储变长字符串但不可太小也不可太大。UTF8最多能存21844个汉字或65532个英文 (3)varbinary(M)保存的是二进制字符串它保存的是字节而不是字符所以没有字符集的概念M长度0-255字节。只用于排序或比较时大小写敏感的类型不包括密码存储 (4)TEXT类型与VARCHAR都类似存储可变长度最大限制也是2^16但是它20bytes以后的内容是在数据页以外的空间存储row_formatdynamic对它的使用需要多一次寻址没有默认值。 一般用于存放容量平均都很大、操作没有其它字段那样频繁的值。 网上部分文章说要避免使用text和blob要知道如果纯用varchar可能会导致行溢出效果差不多但因为每行占用字节数过多会导致buffer_pool能缓存的数据行、页下降。另外text和blob上面一般不会去建索引而是利用sphinx之类的第三方全文搜索引擎如果确实要创建前缀索引那就会影响性能。凡事看具体场景。 另外尽可能把text/blob拆到另一个表中 (5)BLOB可以看出varbinary的扩展版本内容以二进制字符串存储无字符集区分大小写有一种经常提但不用的场景不要在数据库里存储图片。 2.int、tinyint、decimal等数字类型定义 (1)使用tinyint来代替 enum和boolean ENUM类型在需要修改或增加枚举值时需要在线DDL成本较高ENUM列值如果含有数字类型可能会引起默认值混淆 tinyint使用1个字节一般用于status,type,flag的列 (2)建议使用 UNSIGNED 存储非负数值 相比不使用 unsigned可以扩大一倍使用数值范围 (3)int使用固定4个字节存储int(11)与int(4)只是显示宽度的区别 (4)使用Decimal 代替float/double存储精确浮点数 对于货币、金额这样的类型使用decimal如 decimal(9,2)。float默认只能能精确到6位有效数字 3.timestamp与datetime选择 (1)datetime 和 timestamp类型所占的存储空间不同前者8个字节后者4个字节这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07 。所以 TIMESTAMP 支持的范围比 DATATIME 要小。 (2)timestamp可以在insert/update行时自动更新时间字段如 f_set_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP但一个表只能有一个这样的定义。 (3)timestamp显示与时区有关内部总是以 UTC 毫秒 来存的。还受到严格模式的限制 (4)优先使用timestampdatetime也没问题 (5)where条件里不要对时间列上使用时间函数 4.建议字段都定义为NOT NULL (1)如果是索引字段一定要定义为not null 。因为null值会影响cordinate统计影响优化器对索引的选择 (2)如果不能保证insert时一定有值过来定义时使用default ‘’ 或 0 5.同一意义的字段定义必须相同 比如不同表中都有 f_user_id 字段那么它的类型、字段长度要设计成一样
四. 索引规范
1.任何新的select,update,delete上线都要先explain看索引使用情况 尽量避免extra列出现Using File SortUsing Temporaryrows超过1000的要谨慎上线。 explain解读 (1)typeALL, index, range, ref, eq_ref, const, system, NULL从左到右性能从差到好 (2)possible_keys指出MySQL能使用哪个索引在表中找到记录查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询使用 (3)key表示MySQL实际决定使用的键索引 如果没有选择索引键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX (4)ref表示选择 key 列上的索引哪些列或常量被用于查找索引列上的值 (5)rows根据表统计信息及索引选用情况估算的找到所需的记录所需要读取的行数 (6)Extra a.Using temporary表示MySQL需要使用临时表来存储结果集常见于排序和分组查询 b.Using filesortMySQL中无法利用索引完成的排序操作称为“文件排序” 1.索引个数限制 (1)索引是双刃剑会增加维护负担增大IO压力索引占用空间是成倍增加的 (2)单张表的索引数量控制在5个以内或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引需要经过DBA评估。 2.避免冗余索引 (1.)InnoDB表是一棵索引组织表主键是和数据放在一起的聚集索引普通索引最终指向的是主键地址所以把主键做最后一列是多余的。如f_crm_id作为主键联合索引(f_user_id,f_crm_id)上的f_crm_id就完全多余 (2)(a,b,c)、(a,b)后者为冗余索引。可以利用前缀索引来达到加速目的减轻维护负担 3.没有特殊要求使用自增id作为主键 (1.)主键是一种聚集索引顺序写入。组合唯一索引作为主键的话是随机写入适合写少读多的表 (2)主键不允许更新 4.索引尽量建在选择性高的列上 (1)不在低基数列上建立索引例如性别、类型。但有一种情况idx_feedbackid_type (f_feedback_id,f_type)如果经常用 f_type1 比较而且能过滤掉90%行那这个组合索引就值得创建。有时候同样的查询语句由于条件取值不同导致使用不同的索引也是这个道理。 (2)索引选择性计算方法基数 ÷ 数据行数 Selectivity Cardinality / Total Rows select count(distinct col1)/count(*) from tbname越接近1说明col1上使用索引的过滤效果越好 (3)走索引扫描行数超过30%时改全表扫描 5.最左前缀原则 (1)mysql使用联合索引时从左向右匹配遇到断开或者范围查询时无法用到后续的索引列 比如索引idx_c1_c2_c3 (c1,c2,c3)相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引where条件包含上面三种情况的字段比较则可以用到索引但像 where c1a and c3c 只能用到c1列的索引像 c2b and c3c等情况就完全用不到这个索引 (2)遇到范围查询(、、between、like)也会停止索引匹配比如 c1a and c2 2 and c3c只有c1,c2列上的比较能用到索引(c1,c2,c3)排列的索引才可能会都用上 (3)where条件里面字段的顺序与索引顺序无关mysql优化器会自动调整顺序 6.前缀索引 (1)对超过30个字符长度的列创建索引时考虑使用前缀索引如 idx_cs_guid2 (f_cs_guid(26))表示截取前26个字符做索引既可以提高查找效率也可以节省空间 (2)前缀索引也有它的缺点是如果在该列上 ORDER BY 或 GROUP BY 时无法使用索引也不能把它们用作覆盖索引(Covering Index) (3)如果在varbinary或blob这种以二进制存储的列上建立前缀索引要考虑字符集括号里表示的是字节数 7.合理使用覆盖索引减少IO INNODB存储引擎中secondary index(非主键索引又称为辅助索引、二级索引)没有直接存储行地址而是存储主键值。 如果用户需要查询secondary index中所不包含的数据列则需要先通过secondary index查找到主键值然后再通过主键查询到其他数据列因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据列从而避免回表进行二次查找节省IO因此效率较高。 例如SELECT emailuid FROM user_email WHERE uidxx如果uid不是主键适当时候可以将索引添加为index(uidemail)以获得性能提升。 8.尽量不要在频繁更新的列上创建索引 如不在定义了 ON UPDATE CURRENT_STAMP 的列上创建索引维护成本太高好在mysql有insert buffer会合并索引的插入
五. SQL设计
①.正规化表设计原则
零状态形式所需要的所有字段组成的表未经过任何处理。 第一级正规化形式处理原则1.消除每个表格中重复的组2.为每套相关的数据建立一个独立的表格3.使用一个主键来标识每套相关的数据。 第二级正规化形式处理原则1.为应用在多条记录的字段建立独立的表格2.通过一个foreign key来关联这些表格的值。 第三级正规化形式处理原则1.消除不依赖于该键的字段。 第四个正规化形式处理原则1.在一个多对多的关系中独立的实体不能存放在同一个表格中。由于它仅应用于多对多的关系因此大多数的开发者可以忽略这条规定。不过在某些情况下它是非常实用的。 第五级正规化形式处理原则1.原来的表格必须可以通过由它分离出去的表格重新构建使用这个规定的好处是你可以确保不会在分离的表格中引入多余的列所有你创建的表格结构都与它们的实际需要一样大。应用这条规定是一个好习惯不过除非你要处理一个非常大型的数据否则你将不需要用到它。
②.SQL设计
1.杜绝直接 SELECT * 读取全部字段 即使需要所有字段减少网络带宽消耗能有效利用覆盖索引表结构变更对程序基本无影响 2.能确定返回结果只有一条时使用 limit 1 在保证数据不会有误的前提下能确定结果集数量时多使用limit尽快的返回结果。 3.小心隐式类型转换 (1)转换规则 a. 两个参数至少有一个是 NULL 时比较的结果也是 NULL例外是使用 对两个 NULL 做比较时会返回 1这两种情况都不需要做类型转换 b. 两个参数都是字符串会按照字符串来比较不做类型转换 c. 两个参数都是整数按照整数来比较不做类型转换 d. 十六进制的值和非数字做比较时会被当做二进制串 e. 有一个参数是 TIMESTAMP 或 DATETIME并且另外一个参数是常量常量会被转换为 timestamp f. 有一个参数是 decimal 类型如果另外一个参数是 decimal 或者整数会将整数转换为 decimal 后进行比较如果另外一个参数是浮点数则会把 decimal 转换为浮点数进行比较 g. 所有其他情况下两个参数都会被转换为浮点数再进行比较。 (2)如果一个索引建立在string类型上如果这个字段和一个int类型的值比较符合第 g 条。如f_phone定义的类型是varchar但where使用f_phone in (098890)两个参数都会被当成成浮点型。发生这个隐式转换并不是最糟的最糟的是string转换后的floatmysql无法使用索引这才导致了性能问题。如果是 f_user_id ‘1234567’ 的情况符合第 b 条,直接把数字当字符串比较。 4.禁止在where条件列上使用函数 (1)会导致索引失效如lower(email)f_qq % 4。可放到右边的常量上计算 (2)返回小结果集不是很大的情况下可以对返回列使用函数简化程序开发 5.使用like模糊匹配%不要放首位 会导致索引失效有这种搜索需求是考虑其它方案如sphinx全文搜索 6.涉及到复杂sql时务必先参考已有索引设计先explain (1)简单SQL拆分不以代码处理复杂为由。 (2)比如 OR 条件 f_phone‘10000’ or f_mobile‘10000’两个字段各自有索引但只能用到其中一个。可以拆分成2个sql或者union all。 (3)先explain的好处是可以为了利用索引增加更多查询限制条件 7.使用join时where条件尽量使用充分利用同一表上的索引 (1)如 select t1.a,t2.b * from t1,t2 and t1.at2.a and t1.b123 and t2.c 4 如果t1.c与t2.c字段相同那么t1上的索引(b,c)就只用到b了。此时如果把where条件中的t2.c4改成t1.c4那么可以用到完整的索引 (2)这种情况可能会在字段冗余设计反范式时出现 (3)正确选取inner join和left join 8.少用子查询改用join 小于5.6版本时子查询效率很低不像Oracle那样先计算子查询后外层查询。5.6版本开始得到优化 9.考虑使用union all少使用union注意考虑去重 (1)union all不去重而少了排序操作速度相对比union要快如果没有去重的需求优先使用union all (2)如果UNION结果中有使用limit在2个子SQL可能有许多返回值的情况下各自加上limit。如果还有order by请找DBA。 10.IN的内容尽量不超过200个 超过500个值使用批量的方式否则一次执行会影响数据库的并发能力因为单SQL只能且一直占用单CPU而且可能导致主从复制延迟 11.拒绝大事务 比如在一个事务里进行多个select多个update如果是高频事务会严重影响MySQL并发能力因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。 12.避免使用is null, is not null这样的比较 13.order by … limit 这种查询更多的是通过索引去优化但order by的字段有讲究比如主键id与f_time都是顺序递增那就可以考虑order by id而非 f_time 。 14.c1 a order by c2 与上面不同的是order by之前有个范围查询由前面的内容可知用不到类似(c1,c2)的索引但是可以利用(c2,c1)索引。另外还可以改写成join的方式实现。 15.分页优化 建议使用合理的分页方式以提高分页效率大页情况下不使用跳跃式分页 假如有类似下面分页语句: SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10; 这种分页方式会导致大量的io因为MySQL使用的是提前读取策略。 推荐分页方式 SELECT FROM table1 WHERE ftime last_time ORDER BY ftime DESC LIMIT 10 即传入上一次分页的界值 SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 1000010) as t2 ON t1.idt2.id 16.count计数 (1)首先count()、count(1)、count(col1)是有区别的count()表示整个结果集有多少条记录count(1)表示结果集里以primary key统计数量绝大多数情况下count()与count(1)效果一样的但count(col1)表示的是结果集里 col1 列 NOT null 的记录数。优先采用count() (2)大数据量count是消耗资源的操作甚至会拖慢整个库查询性能问题无法解决的应从产品设计上进行重构。例如当频繁需要count的查询考虑使用汇总表 (3)遇到distinct的情况group by方式可能效率更高。 17.delete,update语句改成select再explain select最多导致数据库慢写操作才是锁表的罪魁祸首 18.减少与数据库交互的次数尽量采用批量SQL语句 (1)INSERT … ON DUPLICATE KEY UPDATE …插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值则执行旧行UPDATE如果不重复则直接插入影响1行。 (2)REPLACE INTO类似但它是冲突时删除旧行。INSERT IGNORE相反保留旧行丢弃要插入的新行。 (3)INSERT INTO VALUES(),(),()合并插入。 19.杜绝危险SQL (1)去掉where 11 这样无意义或恒真的条件如果遇到update/delete或遭到sql注入就恐怖了 (2)SQL中不允许出现DDL语句。一般也不给予create/alter这类权限但阿里云RDS只区分读写用户
③.“三少原则”
防止数据库设计打补丁的方法是“三少原则” (1)一个数据库中表的个数越少越好。只有表的个数少了才能说明系统的E–R图少而精去掉了重复的多余的实体形成了对客观世界的高度抽象进行了系统的数据集成防止了打补丁式的设计
(2)一个表中组合主键的字段个数越少越好。因为主键的作用一是建主键索引二是做为子表的外键所以组合主键的字段个数少了不仅节省了运行时间而且节省了索引存储空间
(3)一个表中的字段个数越少越好。只有字段的个数少了才能说明在系统中不存在数据重复且很少有数据冗余更重要的是督促读者学会“列变行”这样就防止了将子表中的字段拉入到主表中去在主表中留下许多空余的字段。所谓“列变行”就是将主表中的一部分内容拉出去另外单独建一个子表。这个方法很简单有的人就是不习惯、不采纳、不执行。
数据库设计的实用原则是在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概念综合观点不能孤立某一个原则。该原则是相对的不是绝对的。“三多”原则肯定是错误的。试想若覆盖系统同样的功能一百个实体(共一千个属性)的E–R图肯定比二百个实体(共二千个属性)的E–R图要好得多。
六. 行为规范
(1)不允许在DBA不知情的情况下导现网数据 (2)大批量更新如修复数据避开高峰期并通知DBA。直接执行sql的由运维或DBA同事操作 (3)及时处理已下线业务的SQL (4)复杂sql上线审核因为目前还没有SQL审查机制复杂sql如多表join,count,group by主动上报DBA评估。 (5)重要项目的数据库方案选型和设计必须提前通知DBA参与
