SQL反模式（四）
by snoopyxdy
at 2013-02-19 13:49:00
original http://snoopyxdy.blog.163.com/blog/static/601174402013119102751228
案例5、乱用索引
当我们通过猜测来选择索引时，不可避免的会犯一些错误，对何时使用索引的误解可能会导致如下的问题：
我们先运行如下程序，发现没有找到数据，于是请教了另外一位同事，同事告诉我说要给NULL增加引号就可以了，果然增加了单引号数据查询出来就正确了：
假设我们使用-1作为默认值，于是我们插入记录
对于一个评论，我们可以查询他的bug和request属性：
跟在select之后的选择列表中的每一项，对于每个分组来说都必须仅返回一个值，这就称为单值规则。MAX()表达式也能够保证每组都返回单一的值，然后下面的这段sql就违反了单值规则：
一个给定的product_id可能有很多不同的bug_id所以数据库没办法在查询结果中表示所有的bug_id。一般数据库会在这样的sql语句抛出一个错误，但是mysql和sqlite这方面不同。在mysql中返回值是这一组第一个记录，而sqlite是最后一个，当然如果分组的product_id都是对应一样的bug_id，在mysql中我们倒是可以利用这个技巧
当我们通过猜测来选择索引时，不可避免的会犯一些错误，对何时使用索引的误解可能会导致如下的问题：
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的记录：
于是需求又来了，我们要查找tag3是NULL的数据，但是这次无论我们怎么修改sql语句都查不出来了
下面是另外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的比较表
令人痛恨的NULL，我痛恨NULL！
NULL会导致程序出错，使用默认值就可以了，但是无论使用何种默认值都需要程序做特殊处理。
我们将assigned_to和hours这一列设置为NOT NULL,
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值。
案例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列来区分，看上去是这样的：
最高效的解决方法似乎可以套用面向对象中的多态和继承，我们去除issue_type这个列，这样创建4张表：
各个数据表的创建语句：
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，我们可以找到它的评论：
其他警示：
1、并不是素有表都必需要有id这一列的
2、数据表的水平分区和垂直分区
3、float类型可能带来错误
4、使用数据库BLOB类型存储文件有时候比存在系统更靠谱
5、切勿乱世用索引
6、正确判断NULL值
7、使用GROUP BY避免违反单值规则
比如如下sql语句：
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、不要有伪键洁癖，因为有时候自然增长的主键可能就是有断档的
结束了~