数据库索引

最新推荐文章于 2025-06-17 00:33:53 发布

iteye_8118

最新推荐文章于 2025-06-17 00:33:53 发布

阅读量68

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/iteye_8118/article/details/82582496

本文探讨了数据库索引的概念、创建方法、优点及其在不同查询场景中的应用，包括为where子句、order by子句建立索引，以及索引的创建原则与注意事项。通过实例演示如何有效利用索引来加速数据检索，同时强调了索引带来的存储空间增加及维护成本的考量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库表中一列或者多列进行排序的结构， 加快访问数据库表特定信息。加快对表中记录查找与排序。

例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多的多。可见，索引是用来定位的。

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)

操作案例编辑
最普通的情况，是为出现在where子句的字段建一个索引。为方便讲述，先建立一个如下的表。
CREATE TABLE mytable(
idserial primary key,
category_id int not null default0,
user_id int not null default0,
adddate int not null default0
);
如果在查询时常用类似以下的语句：
SELECT * FROM mytable WHERE category_id=1;
最直接的应对之道，是为category_id建立一个简单的索引：
CREATE INDEX mytable_categoryid ON mytable (category_id);
OK.如果有不止一个选择条件呢？例如：
SELECT * FROM mytable WHERE category_id=1 AND user_id=2;
第一反应可能是，再给user_id建立一个索引。不好，这不是一个最佳的方法。可以建立多重的索引。
CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);
注意到在命名时的习惯了吗？使用"表名_字段1名_字段2名"的方式。很快就会知道为什么这样做了。
现在已经为适当的字段建立了索引，不过，还是有点不放心吧，可能会问，数据库会真正用到这些索引吗？测试一下就OK，对于大多数的数据库来说，这是很容易的，只要使用EXPLAIN命令：
EXPLAIN
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2;
This is what Postgres 7.1 returns (exactlyasI expected)
NOTICE:QUERY PLAN:
Index Scan using mytable_categoryid_userid on
mytable(cost=0.00..2.02 rows=1 width=16)
EXPLAIN
以上是postgres的数据，可以看到该数据库在查询的时候使用了一个索引（一个好开始），而且它使用的是创建的第二个索引。看到上面命名的好处了吧，马上知道它使用适当的索引了。
接着，来个稍微复杂一点的，如果有个ORDERBY 子句呢？不管你信不信，大多数的数据库在使用orderby的时候，都将会从索引中受益。
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;
很简单，就象为where子句中的字段建立一个索引一样，也为ORDER BY的字句中的字段建立一个索引：
CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);
注意:"mytable_categoryid_userid_adddate"将会被截短为"mytable_categoryid_userid_addda"