为MySQL数据库添加索引
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，搞定？先别⾼兴，如果你有不⽌⼀个选择条件呢？例如：
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:
查看mysql索引　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语句中，加⼊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
现在使⽤我们料想的索引了，⽽且它还挺聪明，知道可以从索引后⾯开始读，从⽽避免了任何的排序。
以上说得细了⼀点，不过如果你的数据库⾮常巨⼤，并且每⽇的页⾯请求达上百万算，我想你会获益良多的。不过，如果你要做更为复杂的查询呢，例如将多张表结合起来查询，特别是where限制字句中的字段是来⾃不⽌⼀个表格时，应该怎样处理呢？我通常都尽量避免这种做法，因为这样数据库要将各个表中的东西都结合起来，然后再排除那些不合适的⾏，搞不好开销会很⼤。
如果不能避免，你应该查看每张要结合起来的表，并且使⽤以上的策略来建⽴索引，然后再⽤EXPLAIN
命令验证⼀下是否使⽤了你料想中的索引。如果是的话，就OK。不是的话，你可能要建⽴临时的表来将他们结合在⼀起，并且使⽤适当的索引。
要注意的是，建⽴太多的索引将会影响更新和插⼊的速度，因为它需要同样更新每个索引⽂件。对于⼀个经常需要更新和插⼊的表格，就没有必要为⼀个很少使⽤的where字句单独建⽴索引了，对于⽐较⼩的表，排序的开销不会很⼤，也没有必要建⽴另外的索引。
以上介绍的只是⼀些⼗分基本的东西，其实⾥⾯的学问也不少，单凭EXPLAIN我们是不能判定该⽅法是否就是最优化的，每个数据库都有⾃⼰的⼀些优化器，虽然可能还不太完善，但是它们都会在查询时对⽐过哪种⽅式较快，在某些情况下，建⽴索引的话也未必会快，例如索引放在⼀个不连续的存储空间时，这会增加读磁盘的负担，因此，哪个是最优，应该通过实际的使⽤环境来检验。
在刚开始的时候，如果表不⼤，没有必要作索引，我的意见是在需要的时候才作索引，也可⽤⼀些命令来优化表，例如MySQL可⽤"OPTIMIZE TABLE"。
综上所述，在如何为数据库建⽴恰当的索引⽅⾯，你应该有⼀些基本的概念

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。