sql反模式分析2

第八章 多列属性

         目标：存储多值属性  为一个bug设置多个标签

         反模式：创建多个列，为bugs创建tag1，tag2，tag3几个列保存标签。标签必须放于其中一个。

                 1.查询数据，比如搜索这三列，可以使用in语句

                 2.添加和删除  update bugs set tag1=nullif(tag1,'perfor'),tag2=nullif(tag2,'perfor'),tag3=nullif(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存储小数。 NUMERIC（9,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 MEDIUMBLOB：16M oracle：LONGRAW 2GB

                   MYSQL有load_file()用来读取一个文件存储到BLOB列

          存储在数据库之外的数据不由数据库管理。

    第13章： 乱用索引

          目标：优化性能

          反模式：无规划的使用索引

                  1.无索引

                  2.索引过多  不需使用的索引无法获得任何好处，只有开销。

                  3.索引也无能为力 常犯的错误是进行一个无法使用索引的查询

          解决方案：所有不重复的值的记录和总计数条数之比越低，索引的效率就越低。

                 1.测量  ORACLE:TKProf mysql:慢查询日志

                 2.解释  查询执行计划

                 3.挑选   索引覆盖

                 4.测试 

                 5.优化  索引预载入：mysql使用 load index into cache语句。

                 6.重建：更新或者删除导致索引修改，需要定期对索引进行维护。mysql：analyze 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_to=123 或者select * from bugs where not(assin_to=123)都不会返回这列为null的值。

                                  而且查询null或者非null是不能用where assin_to=NULL或者assin_to<>NULL。使用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_id=b2.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_id>=b2.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,count（o.bug_id) as count_open from bugsproduct p left outer join bugs f on(p.bug_id=f.bug_id and f.status='fixed') left outer join bugx o on(p.bug_id=o.bug_id and o.status='open') where p.product_id=1 group by p.product

                    这条sql查询出来count_fixed和count_open都是84，而实际上12个fixed，7个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_id=f.bug_id and f.status='fixed') where p.product_id=1 group by p.product 。select p.product_id,count（o.bug_id) as count_open from bugsproduct p left outer join bugx o on(p.bug_id=o.bug_id and o.status='open') where p.product_id=1 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视为二等公民。