注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

snoopyxdy的博客

https://github.com/DoubleSpout

 
 
 

日志

 
 

SQL反模式(四)  

2013-02-19 13:49:00|  分类: node |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
案例5、乱用索引
当我们通过猜测来选择索引时,不可避免的会犯一些错误,对何时使用索引的误解可能会导致如下的问题:
1、不适用索引或者索引不足
2、使用了太多的索引或者使用了一些无效的索引
3、执行一些让索引无能为力的查询

数据库在保持索引同步的时候会有额外的开销,我们每次使用INSERT、UPDATE或者DELETE时,数据库就不得不更新索引的数据结构。我们已经习惯的将额外的开销视作浪费,一些开发人员的结论是不适用索引,但是索引能够给你带来的好处是更多的。

CREATE TALBE Bugs(
   bug_id     SERIAL PRIMARY KEY
   date_report     DATE NOT NULL
   summary     VARCHAR(80) NOT NULL
   status     VACHAR(10) NOT NULL
   hours     NUMBERIC(9,2)
   INDEX(bug_id)   --1
   INDEX(summary)   --2
   INDEX(hours)   --3
   INDEX(bug_id, date_report, status)  --4
)
bug_id:大多数数据库都会自动为主键建立索引,一般再定义一个索引就是一个冗余操作
summary:对于长字符串,比如VARCHAR(80)这种类型的索引要比更为紧凑的数据类型大很多。同样你也不大会对长字符串进行全匹配查找。
hours:这是另外一个你不太可能按照特定值搜索的列
bug_id, date-report, status 组合索引是一个不错的选择,索引顺序也很重要

我们建立如下索引,然后试着使用索引:
CREATE INDEX TelephoneBook ON Accounts(last_name, first_name)
下面的这些查询都无法享受索引
1、SELECT * FROM Accounts ORDER BY first_name, last_name
我们在上面先创建了last_name再创建了first_name顺序的联合索引,所以索引帮不上忙
2、SELECT * FROM Bugs WHERE  MONTH(date_reported) = 4
即使为date_reported列创建了索引,这个索引时无法帮你按照月份查询的。
3、SELECT * FROM Bugs WHERE description LIKE ‘%crash%’
由于这个查询断言的匹配子串可能出现在该字段的任何部分,因此即使经过排序的索引结构也帮不上忙。

分离率是衡量数据库索引的一个重要指标,它表示一张表中所有不重复的值的数量和总记录条数之比。
SELECT COUNT(DISTINCT status) / COUNT(status) AS selectivity FROM Bugs;
分离率越低说明改列的索引效率越低。

案例6、NULL!
不可避免的,数据库中总会有一些字段是没有值的,不管插入一个不完整的行,还是有些列可以合法的拥有一些无效值。SQL支持一个特殊的空值,我们所熟悉的NULL。


我们如下这张表,记录了bug数和tag标签,现在我们查找tag3不为NULL的记录:
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
我们先运行如下程序,发现没有找到数据,于是请教了另外一位同事,同事告诉我说要给NULL增加引号就可以了,果然增加了单引号数据查询出来就正确了:
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 

SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 
于是需求又来了,我们要查找tag3是NULL的数据,但是这次无论我们怎么修改sql语句都查不出来了
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 
下面是另外2个直接和NULL比较可能出现的错误:

1、SELECT hours + 10 FROM Bugs;
如果hours默认值NULL,则NULL + 10还是NULL,所以这句话程序可能会出错
2、SELECT* FROM Bugs hours <> 10 
如果hours是NULL,则NULL <> 10 也不是true,所以程序可能漏掉某些数据

下面是 NULL的比较表

SQL反模式(四) - snoopyxdy - snoopyxdy的博客


令人痛恨的NULL,我痛恨NULL!
NULL会导致程序出错,使用默认值就可以了,但是无论使用何种默认值都需要程序做特殊处理。
我们将assigned_to和hours这一列设置为NOT NULL,
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
假设我们使用-1作为默认值,于是我们插入记录
INSERT TO Bugs (assigned_to, hours) VALUES(-1,-1) 
但是当-1在执行SUM或者AVG的时候会导致计算结果错误,所以必须在计算时使用另外一个判断去除这些数据,这些额外的工作都是要避免使用NULL所带来的
再来看assigned_to列,这是一个指向Accounts表的外键,表示将bug指派给某某某,但是当一个bug录入进系统还没指派给任何人去处理的时候,这个非NULL的值应该是谁呢?任何一个非NULL的值都必须指向Accounts表中的一条记录,因此你不得不在accounts表中插入一条记录,表示”没有此人“或”未指派“,然后让它作为默认值,这个帐号看上去非常可笑。
当你声明一列为NOT NULL时,也就是说这一列必须有一个值,因为每个bug都是由某个人报告的,但是一个bug暂时没有指派给任何人,悬空值应该为NULL。


下列可能用到NULL值的情况
1、一个在职员工的离职时间,不能确定,所以只能设置为NULL
2、一辆完全靠电池驱动的汽车,他的燃油消耗就是NULL
3、在外联查询中,NULL被用来当做匹配的列的占位符

大多数SQL将NULL当做一个特殊值,不同于0,false或者空字符串等,其实sql有一个IS NULL断言可以帮助我们判断数据是否NULL值。
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 

案例7、表继承
有这样的需求,我们需要开发一个评论系统,评论有2种类型,bug和featureRequest,一个给定的评论只能是一种类型,但是我用如下代码似乎是无效的:
CREATE TABALE Comments(
comment_id SERIAL PRIMARY KEY,
bug_id BIGINT UNSIGNED NOT NULL,
author_id      BIGINT NOT NULL,
comment_date       DATETIME NOT NULL,
comment       TEXT NOT NULL,
FOREIGN KEY  (author_id)  REFERNCES  Accounts(account_id),
FOREIGN KEY  (issue_id)  REFERNCES  bugs(issue_id) OR FeatureRequest(issue_id)
)
我们不能不明确的引用外键,那么我们可以使用2个明确的外键,分别指向不同的外表,然后通过type列来区分,看上去是这样的:
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 
最高效的解决方法似乎可以套用面向对象中的多态和继承,我们去除issue_type这个列,这样创建4张表:
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 
各个数据表的创建语句:
CREATE TABLE Issues(
issue_id   SERIAL PRIMARY KEY
)

CREATE TABLE Bugs(
issue_id BIGINT UNSIGNED PRIMARY KEY
FOREIGN KEY  (issue_id)  REFERNCES  Issues(issue_id) 
... //BUGS表的其他列
)

CREATE TABLE FeatureRequsets(
issue_id BIGINT UNSIGNED PRIMARY KEY
FOREIGN KEY  (issue_id)  REFERNCES  Issues(issue_id) 
... //Feature表的其他列
)

CREATE TABLE Comments(
comment_id    SERIAL PRIMARY KEY,
issue_id BIGINT UNSIGNED NOT NULL,
author     BIGINT UNSIGNED NOT NULL,
comment_date   DATETIME,
comment   TEXT,
FOREIGN KEY  (issue_id)  REFERNCES  Issues(issue_id) ,
FOREIGN KEY  (author)  REFERNCES  Accounts(account_id) 
)

注意bugs和featureRequest表的主键也同时是外键,它们引用了Issues表所维护的代理主键,而不用自己创建它们,更重要的这样可以保证数据的完整性。
对于一个指定的bug,我们可以找到它的评论:
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
对于一个评论,我们可以查询他的bug和request属性:
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 

其他警示:
1、并不是素有表都必需要有id这一列的
2、数据表的水平分区和垂直分区
3、float类型可能带来错误
4、使用数据库BLOB类型存储文件有时候比存在系统更靠谱
5、切勿乱世用索引
6、正确判断NULL值
7、使用GROUP BY避免违反单值规则
比如如下sql语句:
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 跟在select之后的选择列表中的每一项,对于每个分组来说都必须仅返回一个值,这就称为单值规则。MAX()表达式也能够保证每组都返回单一的值,然后下面的这段sql就违反了单值规则:
SQL反模式(四) - snoopyxdy - snoopyxdy的博客
 一个给定的product_id可能有很多不同的bug_id所以数据库没办法在查询结果中表示所有的bug_id。一般数据库会在这样的sql语句抛出一个错误,但是mysql和sqlite这方面不同。在mysql中返回值是这一组第一个记录,而sqlite是最后一个,当然如果分组的product_id都是对应一样的bug_id,在mysql中我们倒是可以利用这个技巧

8、高效的获取sql的随机数,避免产生性能瓶颈
SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;
以上这段sql是流行的获取数据库表中随即一条记录的解决方案,但是弱点也很鲜明。使用了RAND()是无法利用索引的,而且当数据库表持续增长后速度回越来越慢。
如果是连续的,我们可以利用程序来生成随机数
也可以先count数据库长度,然后使用偏移量来获取随机值

9、全文检索尽量不要使用like,用第三方库解决
10、不要老是想着用一个sql语句做所有事情,有时候分几步反而更高效简单
11、不要使用select * from db,通配符*可能会隐藏很多bug和潜在的错误,而且对于网络传输也存在浪费
12、一般不要明文保存用户名密码在数据库中
13、注意sql注入,记住一点,让用户输入内容,别让用户输入代码
14、不要有伪键洁癖,因为有时候自然增长的主键可能就是有断档的

结束了~

  评论这张
 
阅读(744)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016