为mysql数据库建立索引

jekeyhui99

前些时候，一位颇高级的程序员居然问我什么叫做索引，令我感到十分的惊奇，我想这绝不会是沧海一粟，因为有成千上万的开发者（可能大部分是使用MySQL的）都没有受过有关数据库的正规培训，尽管他们都为客户做过一些开发，但却对如何为数据库建立适当的索引所知较少，因此我起了写一篇相关[size=1em][url=]文章[/url]的念头。

  最普通的情况，是为出现在where子句的字段建一个索引。为方便讲述，我们先建立一个如下的表。

Code代码如下:

CREATE TABLE mytable ( 　id serial primary key, 　category_id int not null default 0, 　user_id int not null default 0, 　adddate int not null default 0 );

  很简单吧，不过对于要说明这个问题，已经足够了。如果你在查询时常用类似以下的语句：

SELECT * FROM mytable WHERE category_id=1;

  最直接的应对之道，是为category_id建立一个简单的索引：

CREATE INDEX mytable_categoryid
　ON mytable (category_id);

  OK，搞定？先别高兴，如果你有不止一个[size=1em][url=]选择[/url]条件呢？例如：

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 (exactly as I expected)

　NOTICE: QUERY PLAN:

Index Scan using mytable_categoryid_userid on
  mytable (cost=0.00..2.02 rows=1 width=16)

EXPLAIN

  以上是postgres的数据，可以看到该数据库在查询的时候使用了一个索引（一个好开始），而且它使用的是我创建的第二个索引。看到我上面命名的好处了吧，你马上知道它使用适当的索引了。

  接着，来个稍微复杂一点的，如果有个ORDER BY字句呢？不管你信不信，大多数的数据库在使用order by的时候，都将会从索引中受益。

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"

CREATE

  EXPLAIN SELECT * FROM mytable
　　WHERE category_id=1 AND user_id=2
　　　ORDER BY adddate DESC;

　NOTICE: QUERY PLAN:

　Sort (cost=2.03..2.03 rows=1 width=16)
　　-> Index Scan using mytable_categoryid_userid_addda
　　　　on mytable (cost=0.00..2.02 rows=1 width=16)

EXPLAIN

  看看EXPLAIN的输出，好象有点恐怖啊，数据库多做了一个我们没有要求的排序，这下知道性能如何受损了吧，看来我们对于数据库的自身运作是有点过于乐观了，那么，给数据库多一点提示吧。

  为了跳过排序这一步，我们并不需要其它另外的索引，只要将查询语句稍微改一下。这里用的是postgres，我们将给该数据库一个额外的提示--在ORDER BY语句中，[size=1em][url=]加入[/url]where语句中的字段。这只是一个技术上的处理，并不是必须的，因为实际上在另外两个字段上，并不会有任何的排序操作，不过如果加入，postgres将会知道哪些是它应该做的。

EXPLAIN SELECT * FROM mytable
  WHERE category_id=1 AND user_id=2
　　ORDER BY category_id DESC,user_id DESC,adddate DESC;

NOTICE: QUERY PLAN:

Index Scan Backward using
　mytable_categoryid_userid_addda on mytable
　  (cost=0.00..2.02 rows=1 width=16)

EXPLAIN

  现在使用我们料想的索引了，而且它还挺[size=1em][url=]聪明[/url]，知道可以从索引后面开始读，从而避免了任何的排序。

  以上说得细了一点，不过如果你的数据库非常巨大，并且每日的页面请求达上百万算，我想你会获益良多的。不过，如果你要做更为复杂的查询呢，例如将多张表结合起来查询，特别是where限制字句中的字段是来自不止一个表格时，应该怎样处理呢？我通常都尽量避免这种做法，因为这样数据库要将各个表中的东西都结合起来，然后再排除那些不合适的行，搞不好开销会很大。

  如果不能避免，你应该查看每张要结合起来的表，并且使用以上的策略来建立索引，然后再用EXPLAIN命令验证一下是否使用了你料想中的索引。如果是的话，就OK。不是的话，你可能要建立临时的表来将他们结合在一起，并且使用适当的索引。

  要注意的是，建立太多的索引将会[size=1em][url=]影响[/url]更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

  以上介绍的只是一些十分基本的东西，其实里面的学问也不少，单凭EXPLAIN我们是不能判定该方法是否就是最优化的，每个数据库都有自己的一些优化器，虽然可能还不太完善，但是它们都会在查询时对比过哪种方式较快，在某些情况下，建立索引的话也未必会快，例如索引放在一个不连续的存储空间时，这会[size=1em][url=]增加[/url]读磁盘的负担，因此，哪个是最优，应该通过实际的使用环境来检验。

  在刚开始的时候，如果表不大，没有必要作索引，我的意见是在需要的时候才作索引，也可用一些命令来优化表，例如MySQL可用"OPTIMIZE TABLE"。

  综上所述，在如何为数据库建立恰当的索引方面，你应该有一些基本的概念了。

jekeyhui99

创建索引   
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。  
1．ALTER TABLE   
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。      ALTER TABLE table_name ADD INDEX index_name (column_list)   
ALTER TABLE table_name ADD UNIQUE (column_list)   
ALTER TABLE table_name ADD PRIMARY KEY (column_list)      
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名 index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
  2．CREATE INDEX   
CREATE INDEX可对表增加普通索引或UNIQUE索引。      
CREATE INDEX index_name ON table_name (column_list)   
CREATE UNIQUE INDEX index_name ON table_name (column_list)      
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
3．索引类型   
在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。   
PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。   
下面的SQL语句对students表在sid上添加PRIMARY KEY索引。      ALTER TABLE students ADD PRIMARY KEY (sid)     


删除索引   
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。      DROP INDEX index_name ON talbe_name   
ALTER TABLE table_name DROP INDEX index_name   
ALTER TABLE table_name DROP PRIMARY KEY      
其中，前两条语句是等价的，删除掉table_name中的索引index_name。   
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。   
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

jekeyhui99

MySQL:索引工作原理
为什么需要索引（Why is it needed）?当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点（或数据块）的指针，不需要连续存储。

记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索（Linear Search）的过程，平均需要访问N/2的数据块，N是表所占据的数据块数目。如果这个字段是一个非主键字段（也就是说，不包含唯一的访问入口），那么需要在N个数据块上搜索整个表格空间。

但是对于一个有序字段，可以运用二分查找（Binary Search），这样只要访问log2 (N)的数据块。这就是为什么性能能得到本质上的提高。

什么是索引（What is indexing）?索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。

副作用是索引需要额外的磁盘空间，对于MyISAM引擎而言，这些索引是被统一保存在一张表中的，这个文件将很快到达底层文件系统所能够支持的大小限制，如果很多字段都建立了索引的话。

索引如何工作（How does it work?）首先，我们建立一个示范数据库表：

字段名       数据类型      大小
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes
注意：使用char是为了指定准确的磁盘占用大小。这个示范数据库包含500万行，而且没有索引。我们将分析一些查询语句的性能，一个是使用主键id（有序）查询，一个是使用firstName（非关键无序字段）。

例1

我们的示范数据库有r=5,000,000条记录，每条记录长度R=204字节而且使用MyISAM引擎存储（默认数据块大小为B=1024字节），这张表的块因子（blocking factor）会是bfr = (B/R) = 1024/204 = 5 条记录每磁盘数据块。保存这张表所需要的磁盘块为N = (r/bfr) = 5000000/5 = 1,000,000 blocks。

在id字段上的线性搜索平均需要N/2 = 500,000块访问来找到一条记录假设id字段是查询关键值，不过既然id字段是有序的，可以执行一个二分查询，这样平均只需要访问log2 (1000000) = 19.93 = 20 个数据块。我们马上就看到了极大的提高。

现在firstName字段既不是有序的，无法执行二分搜索，数值也不具有唯一性，所以对这张表的查找必须到最后一个记录即全表扫描N = 1,000,000个数据块访问。这就是索引用来改进的地方。

假如索引记录只包含一个索引列以及一个指向原记录数据的指针，那么它显而易见会比原记录（多列）要小。所以索引本身所需要的磁盘块要更少，扫描数目也少。firstName索引表结构如下：

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes
注意: MySQL里的指针按表大小的不同分别可能是 2, 3, 4 或 5 个字节。

例2

假设我们的数据库有r = 5,000,000 条记录，建立了一个长R = 54字节的索引，并且使用默认磁盘块大小为1,024字节。那么该索引的块因子为bfr = (B/R) = 1024/54 = 18 条记录每磁盘块。容纳这个索引表总共需要的磁盘块为N = (r/bfr) = 5000000/18 = 277,778 块。

现在使用FirstName字段来进行搜索就可以利用索引来提高性能。这允许使用一个二分查找，平均log2 (277778) = 18.08 -> 19次数据块访问。找到实际记录的地址，这需要进一步的块读取，这样总数达到19 + 1 = 20次数据块访问，这和非索引表的数据块访问次数有天壤之别。

什么时候使用索引（When should it be used?）鉴于创建索引需要额外的磁盘空间（上面的例子需要额外的277778个磁盘块），以及太多的索引会导致文件系统大小限制所产生的问题，所以对哪些字段建立索引，什么情况下使用索引，需要审慎考虑。

由于索引只是用来加速数据查询，那么显然对只是用来输出的字段建立索引会浪费磁盘空间以及发生插入、删除操作时的处理时间，所以这种情况下应该尽量避免。此外鉴于二分搜索的特性，数据的基数或独立性是很重要的。在基数为2的字段上建立索引，将把数据分割一半，而基数为1000则将返回大约1000条记录。低基数的二分查找效率将降低为一个线性排序，而且查询优化器可能会在基数小于记录数某个比例时（如30%）的情况下将避免使用索引而直接查询原表，所以这种情况下的索引浪费了空间。