使用索引优化含ORDER BY的MySQL语句

发布于: August 15, 2009, 11:08 am 分类: PHP/MySQL 作者: Saturn

谨以此文献给那些希望优化MySQL数据库查询性能，却又不知道如何着手的同仁们。本文将持续更新。

本文的第一个版本发布在CI中国社区，主要是发现很多同学虽然知道索引能够提升MySQL数据库的读取性能，但却存在很多操作上的误区。所以，我特此写了这篇文章，希望能够对大家有些启示。

下面的内容很大程度上是我在平时项目中积累所产生的认识，既然是一家之言，难免会有错误。如果对其中某一条规则有异议，欢迎和我交流。

在开始之前，我想表明一个观点，很多同学在编写WEB程序时，往往非常重视程序的结构和效率，这固然是一个很好的习惯。但他们却忽视了对数据库的结构和优化。在我看来，对于目前很多网站来说，特别是那些对数据库依赖非常严重的泛WEB 2.0网站，数据库调优比程序本身的效率更重要。

本文的主题是：在MySQL中，如何通过合理的建立索引，大幅提升包含ORDER BY语句的效率。在这里，我主要以讲述操作为主，至于为什么这么做，各位同学就要去查阅包括MySQL手册在内的其他资料了。

关于建立索引的几个准则：

  • 合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。
  • 索引越多，更新数据的速度越慢。
  • 尽量在采用MyIsam作为引擎的时候使用索引（因为MySQL以BTree存储索引），而不是InnoDB。但MyISAM不支持Transcation。
  • 尽量不要在重复值很多的栏位上建立索引。
  • 当你的程序和数据库结构/SQL语句已经优化到无法优化的程度，而程序瓶颈并不能顺利解决，那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。
  • 习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。

一个很容易犯的错误：

不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如where，order by。

例子：

上面这个语句，你在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引，那作用就相当大了。

几个常用ORDER BY语句的MySQL优化：

1、ORDER BY + LIMIT组合的索引优化。如果一个SQL语句形如

SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;

这个SQL语句优化比较简单，在[sort]这个栏位上建立索引即可。

2、WHERE + ORDER BY + LIMIT组合的索引优化，形如：

SELECT [column1],[column2],.. FROM [table] ORDER BY [sort] LIMIT [offset],[limit];

这个语句，如果你仍然采用第一个例子中建立索引的方法，虽然可以用到索引，但是效率不高。更高效的方法是建立一个联合索引key(columnX,sort)。

3、WHERE + IN + ORDER BY + LIMIT组合的索引优化，形如：

SELECT [column1],[column2],.. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]  LIMIT [offset],[limit];

这个语句如果你采用第二个例子中建立索引的方法，会得不到预期的效果（仅在[sort]上是using index，WHERE那里是using where;using filesort），理由是这里对应columnX的值对应多个。

这个语句怎么优化呢？我暂时没有想到什么好的办法，于是就用了一个愚蠢的办法，那就是将这个语句用UNION分拆，然后建立第二个例子中的索引：

SELECT [column1],[column2],.... FROM [TABLE] WHERE[columnX]=[value1] ORDER BY [sort]  LIMIT [offset],[LIMIT]
UNION
SELECT [column1],[column2],.... FROM [TABLE] WHERE[columnX]=[value2] ORDER BY [sort]  LIMIT [offset],[LIMIT]
UNION

//more union

4、不要再WHERE和ORDER BY的栏位上应用表达式(函数)，比如：

SELECT * FROM [table] ORDER BY YEAR(date)  LIMIT 0,30;

5、WHERE+ORDER BY多个栏位+LIMIT，比如

SELECT * FROM [table] WHERE uid=1 ORDER BY x,y LIMIT 0,10;

对于这个语句，大家可能是加一个这样的索引key(x,y,uid)。但实际上更好的效果是key(uid,x,y)。这是由MySQL处理排序的机制造成的。

以上例子你在实际项目中应用的时候，不要忘记在添加索引后，用EXPLAIN看看效果。

相关日志:

标签: mysql,性能优化

15 个评论 »
  1. 医者天下 医者天下
    August 16, 2009, 7:24 am

    路过，学习一下。

  2. 华晨 华晨
    August 17, 2009, 6:57 pm

    我的问题：

    1、第一个例子中的 sort 字段应该建立什么类型的索引？

    2、第二个例子中的联合索引是什么意思？应该怎么建？

    3、后面的看不明白了。帮我结果下前面两个疑问吧！谢谢！还有那几种索引都是分别由什么用？有什么区别。不要告诉我去 Google 哦，我Google过的，还是不清楚。

  3. Saturn Saturn
    August 18, 2009, 8:24 am

    @华晨

    1、第一个例子中sort索引类型BTree。

    2、可能我这里表达出现问题了，这里的联合索引意思是multiple-colum索引，就是一个索引建立在多个列上，不知道比较权威的中文翻译是什么。抱歉，一直是看英文资料，没看过中文中的索引优化相关的书籍。具体建立遵守如下规则：

    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

    [index_type]

    ON tbl_name (index_col_name,...)

    [index_type]

    3、这个问题就大了，一两句话还真说不清楚。一部分是我平时积累的，一部分是根据MySQL的Reference上所描述的。具体的原理，需要了解MySQL是如何处理索引的。Google就不用了，我推荐你看看MySQL的手册，哈哈。

  4. 华晨 华晨
    August 19, 2009, 2:08 pm

    对于[1]我是说是是primary、unique、index还是fulltext？

  5. Saturn Saturn
    August 19, 2009, 3:28 pm

    @华晨(4楼)

    这得根据需求，如果所在字段不存在重复字段，则用unique。

    如果可能存在重复字段则用index。

    如果是表的主键，在MyIsam里面会自动设置成primary类型的索引。

    fulltext适合于全表索引，用诸如matach...against这样的语句上。

  6. Saturn Saturn
    August 19, 2009, 3:29 pm

    @Saturn(5楼)

    打错字了，fulltext是全文索引。

  7. 华晨 华晨
    August 19, 2009, 6:05 pm

    @saturn

    哦，知道了，你讲的很明白，谢谢！

  8. 华晨 华晨
    August 19, 2009, 6:14 pm

    @Saturn

    再请问一下，primary和unique有什么区别的？

  9. 伪静态网址分析时的数据库优化【添加唯一索引】 伪静态网址分析时的数据库优化【添加唯一索引】
    August 19, 2009, 7:09 pm

    我不会使用你的trackback地址，就用留言的方式吧，嘿嘿！

  10. Saturn Saturn
    August 19, 2009, 7:53 pm

    @华晨(8楼)

    primary，一个表中只能存在一个。如果你设置了主键，一般都是这个主键用掉了这个primary索引的名额。而unique index是指：所指向的栏位的值必须是unique的，也就是说必须唯一，这个值可以不是主键。另外，虽然index本身允许字段有重复的值，但这必须有个度，如果这个字段存在太多重复值的话，可能就用不到这个索引了。比较极端的例子是，假设一个字段里面可能性的值只有两个，一个0,一个1。这个时候就最好不要用索引。

  11. 华晨 华晨
    August 19, 2009, 8:52 pm

    @Saturn(10楼)

    Primary不就是主键么？不懂。如果是primary只能用一次，那么大可只有unique呀。这样多方便呀。不必考虑是不是只有一个这样的索引。

  12. Saturn Saturn
    August 19, 2009, 9:27 pm

    @华晨(11楼)

    你说的没错，primary是主键专用的，呵呵。unqiue用在非主键栏位上。但是你想过没，一般primary肯定是不允许为空的，但unqiue却可能为null，在MySQL中是允许的。

  13. 华晨 华晨
    August 20, 2009, 4:11 pm

    想问一下：



    第一个表 tags： ID, tag_name；

    第二个表 posts： ID, ...；

    第三个表 tag_post：tag_id, post_id。



    我想在第三个中的两个字段上都加上 index 的索引，这样合理么？因为考虑到每篇文章有多个 tag，每个 tag 对应多篇文章。所以这样两个字段都会有重复。想问一下博客的这种重复程度能不能加普通索引？

  14. Saturn Saturn
    August 20, 2009, 4:58 pm

    @华晨(13楼)

    理论是可以的，因为你会用到inner join，在on条件和where条件上出现的字段都是可以加的。但是呢，对于一个个人博客来说，因为数据量小加不加都没多大影响。或者说，由于数据量太小还不足以让你体会到索引带来的性能提升。但这是个很好的尝试，Enjoy!

  15. 华晨 华晨
    August 20, 2009, 5:18 pm

    @Saturn

    因为要考虑到十几到几十年之后。数据量就比较大了。

    还有，inner join、on 我都不会用，因为我不懂，那些怎么用呢？

回应此文

你也可以选择引用此文章.