`
chengyue2007
  • 浏览: 1489955 次
  • 性别: Icon_minigender_1
  • 来自: 大连
社区版块
存档分类
最新评论

Mysql建立索引

阅读更多

最普通的情况,是为出现在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,搞定?先别高兴,如果你有不止一个选择条件呢?例如:

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语句中,加入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"。

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

 

分享到:
评论

相关推荐

    给MySQL建立索引1

    总的来说,为MySQL数据库建立索引是一个涉及性能优化的关键环节。正确地设计和使用索引可以大幅提升查询效率,减少数据库服务器的负载,进而提高整体系统性能。但同时,索引也会占用存储空间,并在插入、更新和删除...

    用LUCENE连击MYSQL建立索引并搜索的JAVA代码。

    创建好`Document`后,将其添加到`IndexWriter`,这样Lucene就会为这些字段建立索引。 当索引构建完成后,我们就可以实现搜索功能了。首先,创建一个`DirectoryReader`来读取已经建立的索引,然后使用`IndexSearcher...

    详解mysql建立索引的使用办法及优缺点分析

    以下是关于MySQL建立索引的详细说明、优缺点以及常见使用方法。 **一、索引的作用** 索引的主要目的是提高数据检索速度。它类似于书籍的目录,允许数据库系统快速定位所需的数据行,而无需遍历整个表。以下是索引的...

    mysql存储与索引技术

    例如,如果查询多涉及时间戳字段,可以针对该字段建立索引,以加快查询速度。但是,索引并非没有代价,它们占用磁盘空间,并可能影响插入和更新操作的速度。对于 InnoDB 引擎,当表空间文件(ibdata1)过大且无法...

    MYSQL创建索引全过程

    代码复制粘贴 改下里面的参数 就可以用 而且参数 也有说明 简单易懂

    为mysql数据库建立索引.docx

    在本文中,我们将详细介绍如何为MySQL数据库建立索引,包括单字段索引、多字段索引、组合索引和ORDER BY索引等。同时,我们还将介绍使用EXPLAIN命令来测试数据库是否真正使用了这些索引。 单字段索引 ------------ ...

    MySQL索引类型大汇总

    MySQL 索引类型大汇总 ...* 对插入、更新、删除操作频繁的字段建立索引 * 对查询条件中使用的字段建立索引 索引是 MySQL 数据库性能优化的关键所在,合理的使用索引可以提高查询速度,提高数据库的高效运行。

    MySql索引详解,索引可以大大提高MySql的检索速度

    索引的建立对于MySOL的高效运行是很重要的,索引可以大大提高MvSOL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是...建立索引会占用磁盘空间的索引文件

    MySQL索引优化课件

    同时,避免在经常更新的列上建立索引,因为这会增加写操作的复杂性。 关于存储过程,它们是一组预先编译的SQL语句,可以在需要时重复调用,以提高执行效率。存储过程可以封装复杂的业务逻辑,减少网络通信,提高...

    mysql数据库以及索引详解.pptx

    ### MySQL数据库及索引详解 #### 一、MySQL简介与数据库发展 MySQL是一款非常流行的开源关系型数据库管理系统,由瑞典MySQL AB公司开发。它以其高性能、稳定性和易用性著称,广泛应用于Web应用程序和企业级系统中...

    MySQL索引分析和优化.pdf

    在未建立索引的情况下,MySQL需要遍历整个表才能找到符合条件的记录;而一旦对`name`列创建了索引,MySQL则能够直接根据索引快速定位到目标记录,从而大大减少了查询时间。 #### 四、索引的类型 MySQL提供了多种...

    MySQL中建立索引的集中方式

     在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:  CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录,...

    MYSQL索引知识

    使用索引时应遵循一定的原则,如避免在频繁更新的列上创建过多索引,对常用于查询的字段建立索引,数据量小的表通常无需索引,以及在区分度高的列上建立索引等。 3. **索引的分类**: - **单列索引**:包括普通...

    04-VIP-Mysql索引优化实战一.pdf

    这是因为对于小表,全表扫描的代价相对较低,而建立和使用索引的开销可能超过其带来的益处。 总之,MySQL索引优化涉及到对查询语句的理解、索引设计的合理性以及根据数据量和查询模式选择合适的索引使用策略。在...

    面试mysql 之索引 锁 事物

    ### MySQL索引、锁与事务详解 #### 一、索引 **索引定义与作用:** 索引是一种数据结构,用于加速数据检索的过程。它通过建立数据表中某些列的值与行的位置之间的映射关系,使得数据库系统能够快速定位到所需的...

    Mysql索引优化案例.pdf

    由于排序字段`name`没有建立索引,上述查询可能导致MySQL优化器选择全表扫描而非索引扫描,因为全表扫描的成本可能比遍历多个索引树更低。在这种情况下,我们可以通过先通过`ORDER BY name`获取主键,然后根据这些...

Global site tag (gtag.js) - Google Analytics