flash网站制作教程 下载,南京市溧水区建设局网站,百度是不是只有在自己的网站发布才会被收录,wordpress 培训 主题第八章 多列属性目标#xff1a;存储多值属性 为一个bug设置多个标签反模式#xff1a;创建多个列#xff0c;为bugs创建tag1#xff0c;tag2#xff0c;tag3几个列保存标签。标签必须放于其中一个。1.查询数据#xff0c;比如搜索这三列#xff0c;可以使用in语句2.添…第八章 多列属性 目标存储多值属性 为一个bug设置多个标签 反模式创建多个列为bugs创建tag1tag2tag3几个列保存标签。标签必须放于其中一个。 1.查询数据比如搜索这三列可以使用in语句 2.添加和删除 update bugs set tag1nullif(tag1,perfor),tag2nullif(tag2,perfor),tag3nullif(tag3,perfor)这个能把值更新到其中为空的那一列如果都不为空则不作更新。 3.确保唯一性。无法确保三列的值不一样。 4.处理不断增长的值集。三列可能不够用如果在不断的增加列性能开销将越来越大而且sql查询更新将越来越复杂。 解决方案创建从属表 将具有同样意义的值存在同一列中。 第九章元数据分类 案例为一个客户表增加每年的收入情况每年的收入情况都存在单独的列中导致每一年就需要新增一个列存储该年的收入。 目标支持可扩展性 反模式 克隆表与克隆列 1.不断产生的新表 按照年份对bug表进行拆分拆成bugs_2008,bugs_2009等多张表然后按照需要需要修改对应的sql。 2.管理数据完整性 如果有数据被误写到其他表中则可能导致统计的一年的bugs数之类的数据不准确。没有任何办法自动对数据和相关表名做限制。但可以在每张表创建的时候使用check的约束。 3.同步数据 如果发现某条记录原来是在2009这个表中但时间弄错了需要修改为2008的这样订正数据就比较麻烦。需要好几条sql。 4.确保唯一性。 如果需要做数据迁移则需要保证记录的主键id值不会与目标表的主键记录冲突。而且对于那些只支持单表ID唯一的数据库产品实现这样的功能还需要定义一张额外的表存储产品主键的值。 5.跨表查询 如果需要查询所有的bugs数,则需要把每个表用union进行查询. 6.同步元数据.如果值在某个表增加一列,其他表没有增加,则联合查询不用使用*,需要列出所有列名. 7.管理引用完整性. 其他表就不能引用bugs的外键了,因为有多个bugs表. 8.标识元数据分裂列 如果有其他表保护bugx_fiexd_2008,bugx_fixed_2009则以后肯定需要增加bugx_fixed_2010. 解决方案 手工分割表的一个合理使用场景是归档数据。把没用的数据迁移到历史表中。 1.使用水平分区。MYSQL5.1所支持的分区特性在createtable时执行pritition by hash(year(date)reported)) partitions 4. 2.使用垂直分区。根据列来对表进行拆分。将一些BLOB或者TEXT字段拆分到其他表存储。 3.解决元数据分裂列。创建关联表。 别让数据繁衍元数据。 物理数据库设计反模式 第10章取整错误。 目标使用小数取代整数运算结果必须准确。 反模式使用float类型 无限循环小数无法使用存储表示。 在SQL中使用FLOAT类型放大查询结果差异比较大。无法使用比较操作必须使用近似相等查询但是阀值需要使用合适。 解决方案oracle的FLOAT类型表示的是精确值而BINARY_FLOAT则是非精确值。 使用NUMERIC类型。SQL的NUMERIC或者DECIMAL类型来代替FLOAT存储小数。 NUMERIC9,2) 精度刻度 这样仍然无法存储无限精度的数据。 尽可能不要使用浮点数。 第11章每日新花样 需要给称呼列加入约束指定这些候选值 目标限定列的有效值 希望数据库能够拒绝无效值的输入 反模式在列定义上指定可选值。很多数据库设计人员习惯在定义列的时候指定所有可选的有效数据。 create table bugs(status varchar(20) check(status in(new,in,fixed)). mysql也支持用ENUM关键词来约束。但是mysql存储的是序数而非字符串。 1.中间的是哪个 无法获得status列中值的枚举列表如果使用distinct来查询bugs表但是刚开始没数据查询的结果为空。如果使用INFORMATION_SHEMA系统视图则还需要解决解决格式。 2.添加新口味。添加或者删除一个候选值。没有什么语法支持从ENUM或者check约束中添加或者删除一个值。只能用一个新的集合重新定义这一列。一些数据库只有在表为空表是才能改变某一列的数据。那么就需要先将数据导出改变之后再导入。 3.老的口味永不消失。旧的值无法删除。 4.可一致性地下。check约束域和UDT在各种数据库支持形式不同意。ENUM是mysql特有的特性。 解决方案在数据中指定值通过创建一张检查表bug_status定义status列中出现的候选值然后定义一个外键约束。 1.查询候选值集合。直接查询检查表。 2.更新检查表中的数据。插入更新操作很方便。 3.支持废弃数据。可以通过在bug_status表增加一列来表示是否已经弃用。 4.良好的可移植性。 在验证固定集合的候选值时使用元数据。在验证可变集合的候选值时使用数据。 第12章幽灵文件 只保存数据库文件没有保存数据库中保存的文件路径对应的数据库外的文件。 目标存储图片或其他多媒体大文件。 反模式假设必须使用文件系统可以使用BLOB字段存储文件,或者只在数据库存储文件路径。 1.文件不支持DELETE 垃圾回收问题。如果图片在数据库之外删除某条记录之后无法自动将对应文件删除。 2.文件不支持事务隔离。数据库事务在提交之前所有改变对外都不可见。但是数据库之外的文件改变则立刻体现到外界。 3.文件不支持回滚操作。数据库可以回滚但是文件系统无法回滚。 4.文件不支持数据库备份工具。 5.文件不支持SQL的访问权限设置。 6.文件不是SQL数据类型。无法验证文件路径是否正确。 解决方案在需要时使用BLOB类型。 MYSQL MEDIUMBLOB16M oracleLONGRAW 2GB MYSQL有load_file()用来读取一个文件存储到BLOB列 存储在数据库之外的数据不由数据库管理。 第13章 乱用索引 目标优化性能 反模式无规划的使用索引 1.无索引 2.索引过多 不需使用的索引无法获得任何好处只有开销。 3.索引也无能为力 常犯的错误是进行一个无法使用索引的查询 解决方案所有不重复的值的记录和总计数条数之比越低索引的效率就越低。 1.测量 ORACLE:TKProf mysql:慢查询日志 2.解释 查询执行计划 3.挑选 索引覆盖 4.测试 5.优化 索引预载入mysql使用 load index into cache语句。 6.重建更新或者删除导致索引修改需要定期对索引进行维护。mysqlanalyze table or optimize table oracle:alter index rebuild 了解你的数据了解你的查询请求然后MENTOR你的索引。 查询反模式 第14章对未知的恐惧。 目标辨别悬空值 SQL支持一个特殊的空值NULL。 增加记录时使用NULL代替那些还不确定的值。 一个给定的列如果没有合适的值可以使用NULL代替。 当传入参数无效时一个函数的返回值也可以是NULL。 在外联结查询中NULL被用来当做未匹配的列的占位符。 反模式将NULL作为普通的值反之亦然。 1.在表达式中使用NULL。 如果某个字段为NULL表达式结果也是NULL。 2.搜索允许为空的列 select * from bugs where aggin_to123 或者select * from bugs where not(assin_to123)都不会返回这列为null的值。 而且查询null或者非null是不能用where assin_toNULL或者assin_toNULL。使用is null 3.在查询参数中使用NULL 不能在查询参数assin_to?传入NULL值 4.避免上述问题使用默认值来代替NULL按时查询计算时仍然需要制定默认值 解决方案将NULL视为特殊值 1.在标量表达式中使用NULL 表达式中一个值为NULL则结果就为NULL。 2.在布尔表达式中使用NULL。 3.检索NULL值。SQL-99中额外定义了一个比较断言 IS DISTINCT FROM 4.声明NOT NULL列。 5.动态默认值。使用COALESCE函数返回一个非NULL的参数。 使用NULL来表示任意类型的悬空值。 第15章模棱两可的分组 目标获取每组的最大值 反模式 引用非分组列 1.单值规则 一个分组只能返回单一的值 2.我想要的查询 如果分组后通过max获得的有两列的值是一样的那么就无法返回哪条记录的其他列。不能使用max和min两个聚合函数定位到不同的记录。 解决方案 无歧义的使用列 1.只查询功能依赖的列 2.使用关联子查询 3.使用衍生表 4.使用join 5.对额外的列使用聚合函数 6.连接同组所有值 mysql使用GROUP_CONCAT()函数将这一组中所有的值连在一起。 遵循单值规则避免获得模棱两可的查询结果。 第16章随机选择 设计一个随机广告展示的查询 目标获得样坏死记录 反模式随机排序 select * from bugs order by rand() limit 1; 使用rand简单但是无法利用索引因为没有索引会基于随机函数返回的值导致一次全表排序。 解决方案没有具体的顺序。 1.从1到最大值之间随机选择 select b1.* from bugs as b1 join(select(ceil(rand() *(select max(bug_id) from bugs)) as rand_id) as b2 on(b1.bug_idb2.bug_id); 2.选择下一个最大值。 select b1.* from bugs as b1 join(select(ceil(rand() *(select max(bug_id) from bugs)) as rand_id) as b2 where b1.bug_idb2.bug_id order by b1.bug_id limit 1; 3.获得所有键值随机选择一个。 程序选择一个查询两次 4.使用偏移量选择随机行。 5.专有解决方案。SQL server 使用tablesample函数。 oracle使用sample函数。 有些查询是无法优化的换种方式试试看。 第17章可怜人的搜索引擎 目标全文检索 反模式模糊匹配断言 SQL提供了模式匹配断言来比较字符串最常用的就是like语句。还有REGEXP正则表达式匹配。 不过缺点当然就是性能问题了。 解决方案使用正确的工具 1.数据库扩展 mysql能够对char,varchar,text定义一个全文索引,使用match进行全文查询.oracle使用context支持,然后通过contains()操作符搜索.sqlserver和postgreSQL也有对全文索引的支持. 2.第三方搜索引擎:Sphinx search lucene 你不必使用SQL来解决所有的问题. 第18章:意大利苗条查询 目标:减少sql查询数量 反模式:使用一部操作解决复杂问题 1.副作用 查询多少bug已经修复多少bug还打开。select p.product_id,count(f.bug_id) as count_fixed,counto.bug_id) as count_open from bugsproduct p left outer join bugs f on(p.bug_idf.bug_id and f.statusfixed) left outer join bugx o on(p.bug_ido.bug_id and o.statusopen) where p.product_id1 group by p.product 这条sql查询出来count_fixed和count_open都是84而实际上12个fixed7个open刚好84是12*7 这种查询是有问题。 解决方案分而治之 1.分两条sql来查询 。select p.product_id,count(f.bug_id) as count_fixe from bugsproduct p left outer join bugs f on(p.bug_idf.bug_id and f.statusfixed) where p.product_id1 group by p.product 。select p.product_id,counto.bug_id) as count_open from bugsproduct p left outer join bugx o on(p.bug_ido.bug_id and o.statusopen) where p.product_id1 group by p.product 2.寻找union标记 根据单个结果集再使用union all合并。 尽管SQL支持用一行代码解决复杂的问题但也别做不切实际的事情。 第19章隐式的列 联合查询如果两个表有列名一样的列则会只取其中一个。 目标减少输入 可以使用*获取所有列 反模式捷径会让你迷失方向 1.破坏代码重构比如增加一列之后原来insert没有指定列名的则现在会报错少一列的值了。 select也一样如果删除一列后应用代码获取列的可能也会出错。 2.隐藏的开销 获取一些没用的列会增加网络开销和性能。 解决方案明确列出列名 随便拿但是拿了就必须吃掉。 应用程序开发反模式 第20章 明文密码 目标恢复和重置密码 现在一般都是使用邮箱让用户恢复和重置密码 反模式使用明文存储密码 1.存储密码 sql被劫持 2.验证密码 3.在email中发送密码email有可能被劫持 解决方案先哈希后存储 mysql扩展支持SHA2函数返回256位的哈希串 哈希暴力破解可以先加密后再进行哈希。 在SQL中隐藏密码在程序中生成哈希串之后在sql中直接使用哈希串。 重置密码而非恢复密码。 如果密码对你可读那么对于攻击者也是如此。 第21章SQL注入 目标编写SQL动态查询 反模式将未经验证的输入作为代码执行 解决方案不相信任何人 1.过滤输入内容。 2.参数化动态内容 3.给动态输入的值加引号 4.将用户与代码隔离 5.找个可靠的人来帮你审查代码 让用户输入内容但永远别让用户输入代码。 第22章伪键洁癖 目标整理数据 反模式填充角落 1.不按照顺序分配编号 2.为现有行重新编号 3.制造数据差异 重新主键不是一个好习惯 解决方案1.定义行号。2.使用GUID 太长随机的需要16字节 将伪键当做行的唯一性标识但他们不是行号。 第23章非礼勿视 目标写更少的代码 反模式无米之炊 忽略数据库API的返回值将程序代码跟SQL混在一起 1.没有诊断的诊断 在多条sql顺序执行过程中最好对结果进行诊断保证错误能够快速定位。 2.字里行间 花费大量时间调试生成sql字符串的代码 解决方案优雅的从错误中恢复 1.保持节奏 检查数据库API的返回状态和异常。 2.回溯你的脚步。sql语句记录输出调试。 发现并解决代码中的问题已经很苦难了就别再盲目的干了。 第24章外交豁免权 目标最佳实践 使用版本控制工具管理源代码编写单元测试脚本编写文档代码注释。 反模式将SQL视为二等公民。转载于:https://www.cnblogs.com/zhwj184/archive/2012/07/22/3119630.html
