索引分析和优化
上一篇 /
下一篇 2008-11-05 16:09:38
/ 个人分类:数据库
(1)什么是索引?
索引用来快速地寻找那些具有特定值的记录,SQL索引都以B-树的形式保存。如果没有索引,执行查询时SQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,SQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。
(2)索引的类型
普通索引
这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
创建索引,例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) ); |
唯一性索引这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:
创建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表)
); |
主键主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
单列索引与多列索引
索引可以是单列索引,也可以是多列索引
最左前缀
多列索引还有另外一个优点,它通过称为最左前缀(Leftmost Prefixing)的概念体现出来。继续考虑前面的例子,现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,SQL将使用fname_lname_age索引:
firstname,lastname,age
firstname,lastname
firstname |
从另一方面理解,它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)这些列组合上的索引。下面这些不能够使用这个fname_lname_age索引(按最左原则,第一个是firstname):
lastname,age
lastname
age |
(3)
选择索引列
在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列。
SELECT people.age, ##不使用索引 town.name ##不使用索引 FROM people LEFT JOIN town ON
people.townid=town.townid ##考虑使用索引 WHERE firstname='Mike' ##考虑使用索引 AND
lastname='Sullivan' ##考虑使用索引 |
那么,我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?差不多如此,但并不完全。我们还必须考虑到对列进行比较的操作符类型。SQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如,“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”这个查询将使用索引,但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引
索引的缺点
首先,索引要占用磁盘空间。通常情况下,这个问题不是很突出。但是,如果你创建每一种可能列组合的索引,索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表,索引文件的大小可能达到操作系统允许的最大文件限制。
第二,对于需要写入数据的操作,比如DELETE、UPDATE以及INSERT操作,索引会降低它们的速度。这是因为SQL不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。
相关阅读:
- 软件公司的面试题(阿里巴巴DBA)收集中 (超越自我, 2008-10-22)
- Oracle数据库查询十个小技巧(一) (roy_tan001, 2008-10-22)
- Oracle数据库查询十个小技巧(二) (roy_tan001, 2008-10-22)
- sql安装和使用中常见问题的解决 (超越自我, 2008-10-23)
- mySQL常用SQL语句技法 (cuidan131860382, 2008-10-30)
- 教你怎样在Oracle数据库中高速导出/导入 (caption, 2008-10-30)
- LoadRunner之--Think Time (超越自我, 2008-10-31)
- PLSQL导入导出数据库 (ljhappy1, 2008-11-04)
- sql中的表连接 (chenjie021, 2008-11-04)
- Oracle常见错误及处理办法(整理) (Jon, 2008-11-05)
收藏
举报
TAG:
数据库