索引笔记（一）

现在的软件系统建设中数据库还是实际上的系统核心，所以对于系统性能的优化也是以优化数据库为主的。而在数据库优化时索引的建立和使用情况尤为重要。上几天在写一个统计用的sql语句时忽然觉得自己对索引的了解很泛泛，所以就萌生了重温索引想法。笔记首先提到了表的存储和读取的简单原理，这有助与后面对索引的了解。然后是索引的作用和相关的一些语法；相关的注意点；基本策略和优化策略；优化的基本原则；特殊形式的索引等等。
这只是一篇我自己重温数据库索引的记录笔记，希望对于需要的人能有所帮助。当然有人会讲用了SOA，分布式计算，云计算后数据库就不那麽重要了，但基于这些感念的系统（特别在中国）好像还很少。绝大多数的系统（包括已建设的和建设中的）还是已数据库为核心的。
一：行、 表与文件
表是sql理解的概念，但不是操作系统理解的概念。在探索索引的工作原理之前，理解这个概念相当重要
行被存储在文件中。某些sql产品会为表创建一个单独的文件。而在其他产品中多个表可共享一个文件，有时一个表的行可分布在多个文件中，甚至这些文件存储在多个硬盘上。
每个文件被分为多个数据页，或简称为页。在数据页中可能会有一些“空隙”，这些“空隙”不是由增加行的操作引起的，而是由删除行的操作引起的。在添加新航时，sql会自动的在最后一个数据页的最后一行之后存储这一行，如果该数据页已满，则在文件中添加一个空的数据页，而在删除时，sql不会自动的填充“空隙”，因为如果每次都去填充“空隙”，那么sql必须在添加行后找到空隙，且对于较大的表，这是很费时的。
数据页的大小取决与操作系统和sql产品本身，常见的大小有2kB，4KB，8KB，32KB。假设players表中行的长度大约为90字节，这意味着4KB的一个数据页大约有45行。
意识到数据页总是形成I/O的单元很重要，如果操作系统自硬盘检索数据，则可逐页完成。象Unix和Windows这样的操作系统并不会自硬盘检索2字节，相反，它们会收集存储这2字节的数据页。因此，数据库服务器可要求操作系统从文件中检索一个数据页，而不是一行。
从表中检索一行有两个步骤：
1. 从硬盘中收集记录行所在的数据页
2. 找到要检索的行

二：索引作用：
1. 索引用于优化select语句处理
2. 在select语句中不会显示的引用索引，sql的语法不允许这样做
3. 在语句处理过程中，数据库本身决定是否使用一个已有的索引
4. 索引可以在任何时候建立或删除
5. 在更新、插入、删除时数据库会维护索引，这意味select语句处理时间短了，但更新、插入、删除语句的处理时间加长了
6. 索引也是数据库对象

建立语法： create index index_Name on table (col_Name)

一种特殊的索引：唯一索引
作用：
1. 数据库也使用唯一索引来优化处理。
2. 保证某个特殊的列或几个列的组合不包含重复的值
建立方法：create unique index index_Name on table (col_Name)

3. 复合索引：create index index_name on table (col_name1, col_name2,….. ) 用于创建复合索引的各列要位于同一个表

4. 集群索引且唯一：create unique clustered index index_name on table (col_name) 索引将确保行以排序的方式记录在硬盘上（mySql不支持集群索引）

索引可以在任何时候建立，不必在创建表时创建所有的索引。对其中包含重复值的列不能创建索引。
也可以使用Alter table 语句建立索引
例：Alter table teams add index teams_index using Btree (Division)

5. 删除索引：drop index index_name ;

三：涉及到索引的一些注释：
1）如果表中的值被更新，或增加或删除了行，那么sql会自动地更新索引。因此，索引树总会与表的内容保持一致。
2）索引可以建在唯一列上，如ID，也可以建在非唯一列上，如name，如果建在非唯一列上，这样做的结果是，叶数据页中的一个值指向多行-----出现该值的每一行一个指针
3）在一个表上可以定义许多索引，但因为集群索引会影响存储行的方式，所以每个表仅可以包含一个集群索引。
4）索引也可以定义在值的组合上，这用索引叫复合索引。节点中的每个值然后成为多个值的连接。叶数据页会指向其中出现值合并的行

四：重要提示：
1）索引的节点就象表中的行一样存储在文件中。因此，索引会占用物理存储空间
2）对表的更新会导致索引的更新。在必须更新索引时，sql会尽力填充节点间的“空隙”，以尽快完成处理，但是，索引会变的太满，从而需要增加新的节点。这会导致索引的重新组织，这是非常耗时的。

五：select语句的处理步骤：
sql会选择最有效的策略来处理每条语句。这种分析由sql内的一个称为“优化程序”的模块来执行（语句的分析也称为查询优化）。优化程序为每条语句定义了许多可选策略。它会基于象期望的执行时间、行数以及存在的索引，评估哪个策略可能是最有效率的（在没有索引的情况下，这会是基本策略）。sql然后会根据其选择的策略来执行语句。
示例1：给出关于运动员44的所有信息（假设存在一个定义在playerno列上的索引）
Select *
From players
Where playerno = 44

基本策略：
Result := [];
For Each P in players Do
If P.Playerno = 44 Then
Reslut :+ P;
EndFor;

优化策略为：
Result := [];
For Each P in Players where Playerno = 44 Do
Result :+ P;
EndFor;

示例2：给出运动员号小于10且住在Stratford的运动员的号码和城镇，并对结果按运动员号排序
Select playerno,town
Form players
Where playerno <10
And town='Stratford'
Order by playerno

基本策略：
Resut := [];
For Each P in Players Do
If (P.Playerno<10)
And (P.Town = 'Stratford') Then
Result :+ P;
EndFor;

优化策略：
Result := [];
For Each P in players where playerno < 10 Do
If(P.town = 'Stratford')
Result :+ P;
EndFor;

示例3：给出与运动员44所住的城镇相同的运动员的名和姓
Select name, initials
From players
Where town = (select town
from players
where playerno = 44)

基本策略：
Result := [];
For Each P in players Do
Helper:= false;
For Each P44 in Players Do
if(P44.town = P.town)
And (P44.playerno =44) Then
Hepler := true;
EndFor;
If Hepler = true Then
Result :+ P;
EndFor;

优化策略：
Result := [];
Find P44 in players where playerno=44;
For Each P in players where town=P44.town Do
Result :+ P;
Endfor;

索引和主键：
如果在create table 语句内包含了一个主键或交换键 ，那么许多sql产品（包括mySql）会自动的创建一个唯一索引。索引的名称有sql产品本身确定。

------待续