sql基础（2）

索引：

索引是一种特殊的目录，分为聚集索引（cluster index） 和 非聚集索引

比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序

聚集索引所在的列：1. 最好不要频繁变动和更新 2. “既不能绝大多数都相同，又不能只有极少数相同” 

SQL server 默认是在主键上建立聚集索引的

通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自增1的。此时，如果我们将这个列设为主键，SQL server会将此列默认为聚集索引，这个时候 id为聚集索引是浪费的，因为我们不会在实践中用ID查询

实例：在进入员工管理系统后台，首先全表扫描

（1）仅在主键上建立聚集索引，并且不划分时间段：

1.Select gid,fariqi,neibuyonghu,title from tgongwen

用时：128470毫秒（即：128秒）

如果数据量大， 我们完全可以在进入首页后，只查询近三个月来的

2）在主键上建立聚集索引，在fariq上建立非聚集索引：

1.select gid,P_date,neibuyonghu,title from Tgongwen

2.where P_date> dateadd(day,-90,getdate())

用时：53763毫秒（54秒）

非聚集索引用时过长，将聚集索引放在日期列上 显著提高查询速度

（3）将聚合索引建立在日期列（fariqi）上：

1.select gid,P_date,neibuyonghu,title from Tgongwen

2.where P_date> dateadd(day,-90,getdate())

用时：2423毫秒（2秒）

创建聚集索引语句：字段名是P_date

 

create CLUSTERED INDEX 索引名称 ON 表名(字段名)

复合聚集索引：

 

create CLUSTERED INDEX 索引名称 ON 表名(字段名1, 字段名2)

仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的

1.（2）select gid,P_date,name,title from Tgongwen where P_date>''2004-5-5'' and name=''办公室''

查询速度：2516毫秒

1.（3）select gid,P_date,name,title from Tgongwen where name=''办公室''

查询速度：60280毫秒

速度：聚合索引>非聚合索引的主键

order by排序: 聚合索引>主键

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

用时：12936

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

用时：18843

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

key 和 index 的区别

Note that “primary” is called PRIMARY KEY not INDEX.
    KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity …)
    INDEX is something on the physical level, helps improve access time for table operations.
    Behind every PK there is (usually) unique index created (automatically).

SQL where语句的自动优化：

SQL server 中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间。

工作原理：

先用query transformer 把query转换成cost更低的语句

由此看出，尽量不要用 OR， 会扫描全表两次

estimator: 计算execution plan 需要的cost

影响因素：

1. 选择性，选择的行数/总行数，选择的行数越少，选择性越高，不同的操作符有默认值 或者 动态统计信息，=默认值低于>，因为=返回的行数少

2. cardinality（基数） 由每一个操作返回的行数决定， 优化器基于一组复杂的公式来确定每个操作的基数

SELECT first_name, last_name
FROM   employees
WHERE  salary='10200';

在这个表中，employees总共107，根据当前数据统计信息，薪水列中 不同的值 的数目为58，107/58 = 1.84，基数为2

 

计划生成器通过尝试不同的访问路径，联接方法和联接顺序来探索查询块的各种计划。优化器会计算不同plan的cost，选出最低cost

The plan generator explores various plans for a query block by trying out different access paths, join methods, and join orders.

参考：https://www.cnblogs.com/hyd1213126/p/5828937.html

https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm#TGSQL218