当前位置：首页 > news >正文

索引（二）

news 来源：原创 2024/4/26 2:33:50

(四)其他书上没有的索引使用经验总结

　　1、用聚合索引比用不是聚合索引的主键速度快

　　下面是实例语句：(都是提取25万条数据)

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen

　　where fariqi='2004-9-16'

　　使用时间：3326毫秒

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

　　使用时间：4470毫秒

　　这里，用聚合索引比用不是聚合索引的主键速度快了近1/4。

　　2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

　　用时：12936

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

　　用时：18843

　　这里，用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话，如10万以上，则二者的速度差别不明显。

　　3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen

　　where fariqi>'2004-1-1'

　　用时：6343毫秒(提取100万条)

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen

　　where fariqi>'2004-6-6'

　　用时：3170毫秒(提取50万条)

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen

　　where fariqi='2004-9-16'

　　用时：3326毫秒(和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的)

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen

　　where fariqi>'2004-1-1' and fariqi<'2004-6-6'

　　用时：3280毫秒

4 、日期列不会因为有分秒的输入而减慢查询速度

　　下面的例子中，共有100万条数据，2004年1月1日以后的数据有50万条，但只有两个不同的日期，日期精确到日;之前有数据50万条，有5000个不同的日期，日期精确到秒。

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen

　　where fariqi>'2004-1-1' order by fariqi

　　用时：6390毫秒

　　select gid,fariqi,neibuyonghu,reader,title from Tgongwen

　　where fariqi<'2004-1-1' order by fariqi

　　用时：6453毫秒

　　(五)其他注意事项

　　“水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。过多的索引甚至会导致索引碎片。

　　索引是从数据库中获取数据的最高效方式之一。95%的数据库性能问题都可以采用索引技术得到解决。

　　1. 不要索引常用的小型表

　　不要为小型数据表设置任何键，假如它们经常有插入和删除操作就更别这样作了。对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。

　　2. 不要把社会保障号码(SSN)或***号码(ID)选作键

　　永远都不要使用 SSN 或 ID 作为数据库的键。除了隐私原因以外，SSN 或 ID 需要手工输入。永远不要使用手工输入的键作为主键，因为一旦你输入错误，你唯一能做的就是删除整个记录然后从头开始。

　　3. 不要用用户的键

　　在确定采用什么字段作为表的键的时候，可一定要小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。这样做会迫使你采取以下两个措施：

　　4. 不要索引 memo/notes 字段和不要索引大型文本字段(许多字符)

　　这样做会让你的索引占据大量的数据库空间

　　5. 使用系统生成的主键

　　假如你总是在设计数据库的时候采用系统生成的键作为主键，那么你实际控制了数据库的索引完整性。这样，数据库和非人工机制就有效地控制了对存储数据中每一行的访问。

　　采用系统生成键作为主键还有一个优点：当你拥有一致的键结构时，找到逻辑缺陷很容易。

二、改善SQL语句

　　很多人不知道SQL语句在SQL SERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。比如：

　　select * from table1 where name='zhangsan' and tID > 10000

　　和执行:

　　select * from table1 where tID > 10000 and name='zhangsan'

　　一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了;而前一句则要先从全表中查找看有几个name='zhangsan'的，而后再根据限制条件条件tID>10000来提出查询结果。

　　事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。

　　虽然查询优化器可以根据where子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。

　　在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数(SARG)，那么就称之为可优化的，并且可以利用索引快速获得所需数据。

　　SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下：

　　列名操作符 <常数或变量>

　　或

　　<常数或变量> 操作符列名

　　列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：

　　Name=’张三’

　　价格>5000

　　5000<价格

　　Name=’张三’ and 价格>5000