百万数据查询优化
在网上看到一篇“百万数据查询优化”的文章，具体如下。
想问一下各位高手，
1）这文章靠不靠谱？
2）文章中多处出现“否则将引擎放弃使用索引而进行全表扫描”，请问出现这句话的那些建议是不是对的？
3）如果2)是对的，那些建议是靠死记硬背还是说有其他方法能记住？比如“尽量避免在 where 子句中对字段进行 null 值判断”
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%'
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，
否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
11.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
12.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，
如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
18.避免频繁创建和删除临时表，以减少系统表资源的消耗。
19.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，
以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
20.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
21.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
22.尽量避免大事务操作，提高系统并发能力。
23.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。</pre>
[解决办法]
首先我认为这些文章是很靠谱的，你可以从他文章的评论中推测到，如果评论当中有多个人在骂文章的水，那说明文章可能是水的，但这是少数。现在网上看到的关于计算机技术性的文章，怕就怕过时，真实性倒没那么担忧。
很少有人能对数据库进行如此大量的测试并把真实结果发布出来共享，所以在此篇帖子中，有高手的回答那很可能也是他在别处看到了同样的知识，但他自己也没测试过。
那么多知识点确实也不容易记，但咱或许能先记住几条容易记的，然后在设计数据库的过程中，可以反复多看几遍这样的文章。不是吗
[解决办法]
--我也是新手，但是我最烦记这些条条框框的了
--很多东西需要自己切实地去验证一下（当然有N多的前提了，比如数据量，数据库版本等等）
--对于那些结论，一般我是习惯性地验证下，这里有一点提示，测试环境下数据量不要太少了
--拿来三条五条数据做测试，也说明不了问题
--以第四条为例
--真的用不到索引吗，看测试结论吧
CREATE TABLE t_1
(
ID INT,
NAME VARCHAR(50),
NUMBER VARCHAR(10),
ADDRESS VARCHAR(200),
REMARK VARCHAR(200)
)
DECLARE @i int
set @i=0
while @i<100000
begin
insert into t_1 values (@i,NEWID(),@i,NEWID(),NEWID())
set @i=@i+1
end
create unique index index_id on t_1(number)
set statistics profile on
select * from t_1 where NUMBER = '100' or NUMBER = '8889'
USE [tempdb]
GO
CREATE TABLE [dbo].[Table_Z](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CLN] [nchar](10) NULL
) ON [PRIMARY]
GO
/****** Object: Index [NonClusteredIndex-20130502-112321] Script Date: 5/2/2013 11:25:49 AM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20130502-112321] ON [dbo].[Table_Z]
([ID] ASC)
GO
/****** Object: Index [NonClusteredIndex-20130502-112333] Script Date: 5/2/2013 11:25:49 AM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20130502-112333] ON [dbo].[Table_Z]
( [CLN] ASC )
GO
INSERT INTO [dbo].[Table_Z] VALUES(NULL)
GO 1000000
UPDATE [dbo].[Table_Z] SET [CLN]= CAST( [ID] AS VARCHAR)
GO
INSERT INTO [dbo].[Table_Z] VALUES(NULL)
从计划看，很明显，对[CLN]建的INDEX对性能是毫无作用的。
2），错误的建议。
SELECT * FROM [dbo].[Table_Z] WHERE [CLN] is null
从计划看，很明显，有效的用到了index seek+Rid LookUp
3), 错误的建议。
SELECT * FROM [dbo].[Table_Z] WHERE [ID] >999990
SELECT * FROM [dbo].[Table_Z] WHERE [ID] <100
从计划看，很明显，有效的用到了index seek+Rid LookUp
4),错误的建议。
select id from [dbo].[Table_Z] where [ID]=10 or [ID]=20
从计划看，很明显，有效的用到了index seek
5），错误的建议。
select id from [Table_Z] where [ID] in(1,2,3)
从计划看，很明显，有效的用到了index seek
6），不一定，
use AdventureWorks
go
create index ms_idx on Person.Address (city)
go
select * from Person.Address where city like '%Abingdon%'
明显用到index scan+ clustered seek, 在很多情况下，这明显比全表扫描性能高多了。
7， 基本同意，但说法不准确。
首先num/2=100 ，num=100*2可能会不同的结果集 ，其次从计划上看是index scan+ Rid lookup，可以看到产生的不是全部扫描，但是比num=100*2确实性能差很多
8),基本同意,但是具体情况具体分析,
如果你对substring(name,1,3)建计算列索引的话,可以避免全表扫描,当然直接like 'abc%'的话,可以避免另外建索引
9),同上
10),不同意
后半句有问题,次序没有关系,QO会自己调控顺序。
11），不同意
两种写法都是为了创建表，从性能跟资源消耗来看，不觉得有什么本质的区别。
12），不同意
1,逻辑上来说是不同的，也就是说可能会返回不同的结果集，所以不可相互代替。
2,抛开逻辑从性能上说吧,exists跟IN哪个好? 不一定，具体情况具体分析。举个例子，下面的表，如果t2的ID绝不分都为NULL，那in的性能绝对比exists好
SELECT t1.*
FROM [dbo].[Table_1] t1
WHERE t1.id IN (SELECT id FROM t2)
GO
13），错误的建议。
上面的例子明显可以看到使用了index seek 而不是全表扫描。
CREATE TABLE [dbo].[Table_3](
[SEX] [nchar](10) NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20130502-140644] ON [dbo].[Table_3]
(
[SEX] ASC
)
INSERT INTO [dbo].[Table_3] VALUES('M')
GO 100000
INSERT INTO [dbo].[Table_3] VALUES('F')
GO 100000
SELECT * FROM [dbo].[Table_3] where Sex='F'
14）错误的建议
update [dbo].[Table_Z] set [CLN]='ABC' WHERE Id=12345
上面的例子，如果没有为Id建索引，那会哦引起全表扫描，而建了索引后用了index seek,性能明显是数量级的提升。
15）不同意
把ID类型从int 改为char(4)，estimated CPU cost基本都是一样的。
Select * from [dbo].[Table_Z] where Id =1234
16)不同意，
虽然varchar 代替 char 能省空间，但是读取的时候，char在行中的位置都是固定的，而读取varchar类型的字段的时候需要
读取column begin位置，还有end位置，这些都是耗时的，到底哪个性能好 不得而知。
17）基本不同意。
具体问题具体分析，所以确实需要到所有的字段，为什么不可以用 select * from ？
18）基本不同意，
该用临时表的时候还是需要用的，特别是多个地方重复用到的结果集。
19）不同意。
至少我目前没有找到明显的证据表面它们在log上的明显差别。
后面的几点，也是一句话，具体情况具体分析，该表扫描的时候还是要表扫描的，因为这或许是最佳的方式。
