SQL Indexes（索引）

目录

Indexes

Using Clustered Indexes

Using Nonclustered Indexes

Declaring Indexes

Using Indexes

Finding Rows Without Indexes

Finding Rows in a Heap with a Nonclustered Index

Finding Rows in a Clustered Index

Finding Rows in a Clustered Index with a Nonclustered Index

总结：

Database Tuning

---

Indexes

• Index = data structure used to speed access to tuples of a relation, given values of one or more attributes.（用于给定一个或者多个属性后加速对于关系的访问）
• Could be a hash table, but in a DBMS it is always a balanced search tree with giant nodes (a full disk page) called a B+ tree.

Using Clustered Indexes

• Each Table Can Have Only One Clustered Index（每张表只能有一个聚焦索引）
• The Physical Row Order of the Table and the Order of Rows in the Index Are the Same（物理储存顺序与聚焦索引的顺序一样）
• Key Value Uniqueness Is Maintained Explicitly or Implicitly（键值的维护是显式或者隐式的）

Using Nonclustered Indexes

• Nonclustered Indexes Are the SQL Server Default（非聚焦索引是SQL_Server的默认索引类型）
• Existing Nonclustered Indexes Are Automatically Rebuilt When:

1. An existing clustered index is dropped
2. A clustered index is created
3. The DROP_EXISTING option is used to change which columns define the clustered index

以上情况会导致非聚焦索引会重建：

1. 已存在的聚焦索引被删除（会导致非聚焦索引储存的数据指针失效，所以需要重建）
2. 新的聚焦索引被创建
3. 修改了聚焦索引的列（可能会导致物理储存顺序重新排列）

总结来说的话就是，影响了物理储存顺序就可能会导致非聚焦索引的顺序失效

Declaring Indexes

No standard!

Typical syntax:

CREATE INDEX BeerInd ON
Beers(manf);
CREATE INDEX SellInd ON
Sells(bar, beer);

Using Indexes

• Given a value v, the index takes us to only those tuples that have v in the attribute(s) of the index.（通过给定的值v，能够快速找到包含v属性的元组）

• Example: use BeerInd and SellInd to find the prices of beers manufactured by Pete’s and sold by Joe.

SELECT price FROM Beers, Sells
WHERE manf = ’Pete’’s’ AND
Beers.name = Sells.beer AND
bar = ’Joe’’s Bar’;

• Use BeerInd to get all the beers made by Pete’s.

• Then use SellInd to get prices of those beers, with bar = ’Joe’’s Bar’

Finding Rows Without Indexes

Finding Rows in a Heap with a Nonclustered Index

Finding Rows in a Clustered Index

Finding Rows in a Clustered Index with a Nonclustered Index

总结：

• 非聚集索引的叶子结点存放的是聚集索引的关键字，聚集索引叶子结点存放的是数据本身
• 所以使用非聚焦索引查询数据时，应该是先找到非聚焦索引的叶子结点上的聚焦索引的关键字，然后通过这个关键字，从聚焦索引找到存放了数据的叶子结点

Database Tuning

• A major problem in making a database run fast is deciding which indexes to create.（一个主要的问题就是，要决定那些索引是需要被创建的）
• Pro: An index speeds up queries that can use it.（优点：能够提升查询的速度）
• Con: An index slows down all modifications on its relation because the index must be modified too.（缺点：是的关系上的修改效率降低，因为修改的同时索引也需要修改）