数据库索引实例

最新推荐文章于 2024-05-13 02:55:12 发布

weixin_34125592

最新推荐文章于 2024-05-13 02:55:12 发布

阅读量1.1k

点赞数

文章标签：数据库运维

原文链接：http://www.cnblogs.com/klb561/p/10666460.html

版权

索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B 树)中，使 SQL Server 可以快速有效地查找与键值关联的行。

表或视图可以包含以下类型的索引:

* 聚集
o 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。
o 只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。
* 非聚集
o 非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。
o 从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。
o 您可以向非聚集索引的叶级添加非键列以跳过现有的索引键限制(900 字节和 16 键列)，并执行完整范围内的索引查询。

聚集索引和非聚集索引都可以是唯一的。这意味着任何两行都不能有相同的索引键值。另外，索引也可以不是唯一的，即多行可以共享同一键值。

每当修改了表数据后，都会自动维护表或视图的索引。

索引和约束

对表列定义了 PRIMARY KEY 约束和 UNIQUE 约束时，会自动创建索引。例如，如果创建了表并将一个特定列标识为主键，则数据库引擎自动对该列创建 PRIMARY KEY 约束和索引。有关详细信息，请参阅创建索引(数据库引擎)。

二、索引有什么用

与书中的索引一样，数据库中的索引使您可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。

设计良好的索引可以减少磁盘 I/O 操作，并且消耗的系统资源也较少，从而可以提高查询性能。对于包含 SELECT、UPDATE、DELETE 或 MERGE 语句的各种查询，索引会很有用。例如，在 AdventureWorks 数据库中执行的查询 SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250。执行此查询时，查询优化器评估可用于检索数据的每个方法，然后选择最有效的方法。可能采用的方法包括扫描表和扫描一个或多个索引(如果有)。

扫描表时，查询优化器读取表中的所有行，并提取满足查询条件的行。扫描表会有许多磁盘 I/O 操作，并占用大量资源。但是，如果查询的结果集是占表中较高百分比的行，扫描表会是最为有效的方法。

查询优化器使用索引时，搜索索引键列，查找到查询所需行的存储位置，然后从该位置提取匹配行。通常，搜索索引比搜索表要快很多，因为索引与表不同，一般每行包含的列非常少，且行遵循排序顺序。

查询优化器在执行查询时通常会选择最有效的方法。但如果没有索引，则查询优化器必须扫描表。您的任务是设计并创建最适合您的环境的索引，以便查询优化器可以从多个有效的索引中选择。SQL Server 提供的数据库引擎优化顾问可以帮助分析数据库环境并选择适当的索引。

三、索引怎么用

索引其实关键目的是为了加快检索速度而建立的，所以，怎么用索引是数据库系统本身的事情，作为数据库设计或使用者，设计并创建好索引然后体验加上索引后的查询变快的感觉就行了。所以，索引怎么用就变为了“怎么创建合适的索引”，以下说明这个问题:

索引设计不佳和缺少索引是提高数据库和应用程序性能的主要障碍。设计高效的索引对于获得良好的数据库和应用程序性能极为重要。为数据库及其工作负荷选择正确的索引是一项需要在查询速度与更新所需开销之间取得平衡的复杂任务。如果索引较窄，或者说索引关键字中只有很少的几列，则需要的磁盘空间和维护开销都较少。而另一方面，宽索引可覆盖更多的查询。您可能需要试验若干不同的设计，才能找到最有效的索引。可以添加、修改和删除索引而不影响数据库架构或应用程序设计。因此，应试验多个不同的索引而无需犹豫。

SQL Server 中的查询优化器可在大多数情况下可靠地选择最高效的索引。总体索引设计策略应为查询优化器提供可供选择的多个索引，并依赖查询优化器做出正确的决定。这在多种情况下可减少分析时间并获得良好的性能。若要查看查询优化器对特定查询使用的索引，请在 SQL Server Management Studio 中的“查询”菜单上选择“包括实际的执行计划”。

不要总是将索引的使用等同于良好的性能，或者将良好的性能等同于索引的高效使用。如果只要使用索引就能获得最佳性能，那查询优化器的工作就简单了。但事实上，不正确的索引选择并不能获得最佳性能。因此，查询优化器的任务是只在索引或索引组合能提高性能时才选择它，而在索引检索有碍性能时则避免使用它。

建议的索引设计策略包括以下任务:

1. 了解数据库本身的特征。例如，它是频繁修改数据的联机事务处理 (OLTP) 数据库，还是主要包含只读数据的决策支持系统 (DSS) 或数据仓库 (OLAP) 数据库?
2. 了解最常用的查询的特征。例如，了解到最常用的查询联接两个或多个表将有助于决定要使用的最佳索引类型。
3. 了解查询中使用的列的特征。例如，某个索引对于含有整数数据类型同时还是唯一的或非空的列是理想索引。筛选索引适用于具有定义完善的数据子集的列。
4. 确定哪些索引选项可在创建或维护索引时提高性能。例如，对现有某个大型表创建聚集索引将会受益于 ONLINE 索引选项。ONLINE 选项允许在创建索引或重新生成索引时继续对基础数据执行并发活动。
5. 确定索引的最佳存储位置。非聚集索引可以与基础表存储在同一个文件组中，也可以存储在不同的文件组中。索引的存储位置可通过提高磁盘 I/O 性能来提高查询性能。例如，将非聚集索引存储在表文件组所在磁盘以外的某个磁盘上的一个文件组中可以提高性能，因为可以同时读取多个磁盘。
或者，聚集索引和非聚集索引也可以使用跨越多个文件组的分区方案。在维护整个集合的完整性时，使用分区可以快速而有效地访问或管理数据子集，从而使大型表或索引更易于管理。有关详细信息，请参阅已分区表和已分区索引。在考虑分区时，应确定是否应对齐索引，即，是按实质上与表相同的方式进行分区，还是单独分区。

# 设计索引。
索引设计是一项关键任务。索引设计包括确定要使用的列，选择索引类型(例如聚集或非聚集)，选择适当的索引选项，以及确定文件组或分区方案布置。

# 确定最佳的创建方法。按照以下方法创建索引:

* 使用 CREATE TABLE 或 ALTER TABLE 对列定义 PRIMARY KEY 或 UNIQUE 约束
SQL Server 数据库引擎自动创建唯一索引来强制 PRIMARY KEY 或 UNIQUE 约束的唯一性要求。默认情况下，创建的唯一聚集索引可以强制 PRIMARY KEY 约束，除非表中已存在聚集索引或指定了唯一的非聚集索引。默认情况下，创建的唯一非聚集索引可以强制 UNIQUE 约束，除非已明确指定唯一的聚集索引且表中不存在聚集索引。
还可以指定索引选项和索引位置、文件组或分区方案。
创建为 PRIMARY KEY 或 UNIQUE 约束的一部分的索引将自动给定与约束名称相同的名称。

* 使用 CREATE INDEX 语句或 SQL Server Management Studio 对象资源管理器中的“新建索引”对话框创建独立于约束的索引
必须指定索引的名称、表以及应用该索引的列。还可以指定索引选项和索引位置、文件组或分区方案。默认情况下，如果未指定聚集或唯一选项，将创建非聚集的非唯一索引。若要创建筛选索引，请使用可选的 WHERE 子句。

# 创建索引。
要考虑的一个重要因素是对空表还是对包含数据的表创建索引。对空表创建索引在创建索引时不会对性能产生任何影响，而向表中添加数据时，会对性能产生影响。
对大型表创建索引时应仔细计划，这样才不会影响数据库性能。对大型表创建索引的首选方法是先创建聚集索引，然后创建任何非聚集索引。在对现有表创建索引时，请考虑将 ONLINE 选项设置为 ON。该选项设置为 ON 时，将不持有长期表锁以继续对基础表的查询或更新。

简单的创建索引，可采用如下语句:
CREATE INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (VendorID, VendorName);
GO

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。

表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引

大多数情况下索引能大幅度提高查询效率，但：

数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本
更多的索引意味着也需要更多的空间（一本100页的书，却有50页目录？）
过小的表，建索引可能会更慢哦：）（读个2页的宣传手册，你还先去找目录？）

索引的字段类型问题

text类型，也可建索引（需指定长度）
myisam存储引擎索引键长度综合不能超过1000字节
用来筛选的值尽量保持和索引列同样的数据类型

like 不能用索引？

尽量减少like，但不是绝对不可用，”xxxx%” 是可以用到索引的，

想象一下，你在看一本成语词典，目录是按成语拼音顺序建立，查询需求是，你想找以 “一”字开头的成语（”一%“），和你想找包含一字的成语（“%一%”）
除了like，以下操作符也可用到索引：

<，<=，=，>，>=，BETWEEN，IN

<>，not in ，！=则不行

什么样的字段不适合建索引？

一般来说，列的值唯一性太小（如性别，类型什么的），不适合建索引（怎样叫太小？一半说来，同值的数据超过表的百分之15，那就没必要建索引了）
太长的列，可以选择只建立部分索引，（如：只取前十位做索引）
更新非常频繁的数据不适宜建索引（怎样叫非常？意会）

一次查询能用多个索引吗?

不能

多列查询该如何建索引?

一次查询只能用到一个索引，所以首先枪毙 a，b各建索引方案

a还是b？谁的区分度更高（同值的最少），建谁！

当然，联合索引也是个不错的方案，ab，还是ba，则同上，区分度高者，在前

联合索引的问题?

where a = “xxx” 可以使用 AB 联合索引
where b = “xxx” 则不可（再想象一下，这是书的目录？）

所以，大多数情况下，有AB索引了，就可以不用在去建一个A索引了

哪些常见情况不能用索引?

like “%xxx”
not in ，！=
对列进行函数运算的情况（如 where md5(password) = “xxxx”）
WHERE index=1 OR A=10
存了数值的字符串类型字段（如手机号），查询时记得不要丢掉值的引号，否则无法用到该字段相关索引，反之则没关系
也即

select * from test where mobile = 13711112222;

可是无法用到mobile字段的索引的哦（如果mobile是char 或 varchar类型的话）

btw，千万不要尝试用int来存手机号（为什么？自己想！要不自己试试）

覆盖索引(Covering Indexes)拥有更高效率

索引包含了所需的全部值的话，就只select 他们，换言之，只select 需要用到的字段，如无必要，可尽量避免select *

NULL 的问题

NULL会导致索引形同虚设，所以在设计表结构时应避免NULL 的存在（用其他方式表达你想表达的NULL，比如 -1？）

如何查看索引信息，如何分析是否正确用到索引?

show index from tablename;
explain select ……;

1.创建表并插入数据

在Sql Server2008中创建测试数据库Test，接着创建数据库表并插入数据，sql代码如下：

USE Test
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'emp_pay')
   DROP TABLE emp_pay
GO
USE Test
IF EXISTS (SELECT name FROM sys.indexes 
      WHERE name = 'employeeID_ind')
   DROP INDEX emp_pay.employeeID_ind
GO
USE Test
GO
CREATE TABLE emp_pay
(
 employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
   VALUES (1, 500, .10)
INSERT emp_pay 
   VALUES (2, 1000, .05)
INSERT emp_pay 
   VALUES (6, 800, .07)
INSERT emp_pay
   VALUES (5, 1500, .03)
INSERT emp_pay
   VALUES (9, 750, .06)

执行完上述sql代码以后我们会发现在Test数据库中多出了一张emp_pay表，数据库表的内容如下图所示：

2.无索引查找

从上图我们可以看出数据库中存储的数据排列顺序与我们插入的先后顺序一致。接下来我们查询employeeID=5的字段，执行如下sql代码：

USE Test
SELECT * FROM emp_pay where employeeID=5

在SQL SERVER MANAGEMENT STUDIO中我们点击“显示估计的查询计划”，会出现如下图所示的查询计划图：

其中表扫描的内容为：

3.创建索引

接下来我们为上述表添加聚集唯一索引，代码如下：

SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
   ON emp_pay (employeeID)
GO

在执行完上述创建索引的代码以后，我们再次查询emp_pay的数据内容，如下图所示：

从上图我们可以发现数据内容已经按照employeeID进行了排序。

我们继续执行前面关于employeeID=5的查询，点击“显示估计的执行计划”，出现如下图所示内容：

聚集索引查找的内容为：

总结：

当我们为数据库表中的某一个字段创建索引，并且在查询语句中where子句中用到这样一个字段，那么查询效率会有所提高，我们上述实验因为数据量的关系查询效率提高不明显。

补充

我们上面添加的索引是唯一聚集索引，因此当插入的数据在employeeID字段出现重复时会报错。假如我们在创建索引之前数据字段出现重复，那么就不能创建唯一索引。

创建索引以后的排序（PS:2012-5-28）

执行如下sql语句

update emp_pay set employeeID=7 where employeeID=1;

然后再次执行全表查询，我们发现查询结果如下所示：

只要我们更新了employeeID，那么最后的更新结果都会按照employeeID的值进行升序排序。这是因为我们在employeeID上创建了索引的缘故。

删除索引（PS:2012-6-4）

我们可以通过sql server management studio这个工具删除索引，也可以通过sql语句进行索引的删除，假设我们要求删除在前面创建的索引employeeID_ind，那么sql语句如下代码所示：

DROP INDEX employeeID_ind ON emp_pay;

转载于:https://www.cnblogs.com/klb561/p/10666460.html

weixin_34125592

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库索引实例

索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B 树)中，使 SQL Server 可以快速有效地查找与键值关联的行。表或视图可以包含以下类型的索引: * 聚集 o 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。 o...
复制链接

扫一扫