随笔分类 - SQL反模式
《SQL反模式》阅读体会与心得
摘要:什么是“反模式” 反模式是一种试图解决问题的方法,但通常会同时引发别的问题。 反模式分类 (1)逻辑数据库设计反模式 在开始编码之前,需要决定数据库中存储什么信息以及最佳的数据组织方式和内在关联方式。 这包含了如何设计数据库的表、字段和关系。 (2)物理数据库设计反模式 在确定了需要存储哪些数据之后
阅读全文
摘要:目标:整理数据,使不连续的主键Id数据记录变的连续。 反模式:填充断档的数据空缺。 1、不按照顺序分配编号 在插入新行时,通过遍历表,找到的第一个未分配的主键编号分配给新行,来代替原来自动分配的伪主键机制。 使用Select Max(Id) + 1 这种查询语句,会出现并发访问的问题。 2、为现有数
阅读全文
摘要:目标:编写SQL动态查询,防止SQL注入 通常所说的“SQL动态查询”是指将程序中的变量和基本SQL语句拼接成一个完整的查询语句。 反模式:将未经验证的输入作为代码执行 当向SQL查询的字符串中插入别的内容,而这些被插入的内容以你不希望的方式修改了查询语法时,SQL注入就成功了。 传统的SQL注入案
阅读全文
摘要:目标:恢复或重置密码 反模式:使用明文存储密码 1、存储密码 使用明文存储密码或者在网络上传递密码是不安全的。 如果攻击者截取到你用来插入(或者修改)密码的sql语句,就可以获得密码。 黑客获取密码的方式有很多种: (1)在客户端和服务器端数据库交互的网络线路上接货数据包。比如使用Wireshark
阅读全文
摘要:目标:减少输入 反模式:捷径会让你迷失方向 使用通配符和未命名的列能够达到减少输入的目的,但是这个习惯会带来一些危害。 1、破坏代码重构:增加一列后,使用隐式的Insert插入语句报错; 2、查询中使用通配符,影响性能和扩展性。大量数据在网络之间传输,可能会造成阻塞。 如何识别反模式:当出现以下情况
阅读全文
摘要:目标:减少SQL查询数据,避免使用一条SQL语句解决复杂问题 反模式:视图使用一步操作,单个SQL语句解决复杂问题 使用一个查询来获得所有结果的最常见后果就是产生了一个笛卡尔积。导致查询性能降低。 如何识别反模式:当出现以下情况时,可能是反模式 1、为什么我的求和、技术返回的结果异常地大? 2、我一
阅读全文
摘要:目标:全文搜索 使用SQL搜索关键字,同时保证快速和精确,依旧是相当地困难。 SQL的一个基本原理(以及SQL所继承的关系原理)就是一列中的单个数据是原子性的。 反模式:模式匹配 使用Like 或者正则表达式。 缺点:(1)无法使用索引,进行全表遍历,非常耗时,性能极低。 (2)有时候会返回医疗之外
阅读全文
摘要:目标:随机排序,使用高效的SQL语句查询获取随机数据样本。 反模式:使用RAND()随机函数 SELECT * FROM Employees AS e ORDER BY RAND() Limit 1 缺点:无法利用索引,每次选择的时候都不同且不可预测。进行全表遍历,性能极差。 如何识别反模式:当出现
阅读全文
摘要:目标:查询得到每组的max(或者min等其他聚合函数)值,并且得到这个行的其他字段 反模式:引用非分组列 单值规则:跟在Select之后的选择列表中的每一列,对于每个分组来说都必须返回且仅返回一直值。 select ProductId,Max(DateReported) as Latest from
阅读全文
摘要:目标:辨别并使用Null值 反模式:将Null值作为普通的值,反之亦然 1、在表达式中使用Null: Null值与空字符串是不一样的,Null值参与任何的加、减、乘、除等其他运算,结果都是Null; Null值与False也不同。And、Or和Not三个bool操作如果设计Null,结果很迷惑。 2
阅读全文
摘要:目标:优化性能 改善性能最好的技术就是在数据库中合理地使用索引。 索引也是数据结构,它能使数据库将指定列中的某个值快速定位在相应的行。 反模式:无规划的使用索引 1、不使用索引或索引不足 2、使用了太多的索引或一些无效的索引 (1)大多数数据库会自动地位主键建立索引,因此额外再定义一个索引就是冗余。
阅读全文
摘要:目标:存储图片或其他多媒体大文件 反模式:图片存储在数据库外的文件系统中,数据库表中存储文件的对应的路径和名称。 缺点: 1、文件不支持Delete操作。使用SQL语句删除一条记录时,对应的文件不会被删除,需要使用额外的程序来操作。 2、文件不支持事务隔离 3、文件不支持回滚操作 4、文件不支持数据
阅读全文
摘要:目标:限定列的有效值,将一列的有效字段值约束在一个固定的集合中。类似于数据字典。 反模式:在列定义上指定可选值 1、 对某一列定义一个检查约束项,这个约束不允许往列中插入或者更新任何会导致约束失败的值: create table Bugs(status varchar(20) check(statu
阅读全文
摘要:目标:使用小数取代整数 反模式:使用Float类型 根据IEEE754标识,float类型使用二进制格式编码实数数据。 缺点:(1)舍入的必要性: 并不是所有的十进制中描述的信息都能使用二进制存储,处于一些必要的因素, 浮点数通常是舍入到了一个非常接近的值。 举例:select rate from
阅读全文
摘要:目标:支持可扩展性。优化数据库的结构来提升查询的性能以及支持表的平滑扩展。 反模式:克隆表与克隆列 1、将一张很长的表拆分成多张较小的表,使用表中某一个特定的数据字段来给这些拆分出来的表命名。 2、将一个列拆分成多个之列,使用别的列中的不同值给拆分出来的列命名。 为了达到减少每张表记录数的目的,你不
阅读全文
摘要:目标:存储多值属性 反模式:创建多个列。比如一个人具有多个电话号码、座机号码、手机号码等。 1、查询:多个列的话,查询时可能不得不用IN,或者多个OR; 2、添加、删除时确保唯一性、判断是否有值:这些都很麻烦; 3、字段的列数无法确定具体数量。 如何识别反模式:当出现以下情况时,可能是反模式 1、应
阅读全文
摘要:目标:引用多个父表 反模式:使用多用途外键。这种设计也叫做多态关联,或者杂乱关联。 多态关联和EAV有着相似的特征:元数据对象的名字是存储在字符串中的。 在多态关联中,父表的名字是存储在Issue_Type单独一列中,有时候这样的设计被称作:混合数据与原数据。 查询示例: select * from
阅读全文
摘要:目标:支持可变属性 反模式:使用泛型属性表。这种设计成为实体-属性-值(EAV),也可叫做开放架构、名-值对。 优点:通过增加一张额外的表,可以有以下好处 (1)表中的列很少; (2)新增属性时,不需要新增列。不会影响现有表的结构; (3)存储的字段内容不会为空值。 缺点:(1)查询语句变得更加复杂
阅读全文
摘要:目标:简化数据库架构 一些开发人员不推荐使用引用完整性约束,可能不使用外键的原因有一下几点: 1、数据更新有可能和约束冲突; 2、当前的数据库设计如此灵活,以至于不支持引用完整性约束; 3、数据库为外键建立的索引会影响性能; 4、当前使用的数据库不支持外键。比如MySQL的MyISAM存储引擎,或者
阅读全文
摘要:2014-10-11 在树形结构中,实例被称为节点。每个节点都有多个子节点与一个父节点。 最上层的节点叫做根(root)节点,它没有父节点。 最底层的没有子节点的节点叫做叶(leaf)。 中间的节点简单地称为非叶节点(nonleaf)。 目标:分成存储于查询,比如:系统字典、组织机构、省份区域等树形
阅读全文