通过索引优化含ORDER BY的MySQL语句

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

1.合理的建立索引能够加速数据读取速率，不合理的建立索引反而会拖慢数据库的响应速度。

2.索引越多，更新数据的速度越慢。

3.尽量在采用MyISAM作为引擎的时候使用索引（因为MySQL以BTree存储索引），而不是InnoDB。但MyISAM不支持Transcationl。

4.当你的程序和数据库结构/SQL语句已经优化到无法优化的程度，而程序瓶颈并不能顺利解决，那就应该考虑使用诸如memcached这样的分布式缓存系统。

5.习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。

一个很容易犯错的错误：

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

例：

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

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

几种常见ORDER BY语句的MySQL优化：

1.ORDER BY + LIMIT 组合的索引优化。

如果一个SQL语句的形式：

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

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

2.WHERE + ORDER BY + LIMIT 组合的索引优化。

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

这个语句，任然可以采用第一个例子中建立的索引方法，但是效率不高。更高效率的方法是建立一个联合索引（columnX, sort）

3.WHERE + IN + ORDER BY + LIMIT 组合的索引优化。

SELECT [column1],[column2],... FROM [table] WHERE [columnX] IN ([value1],[value2],...) 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

....

但经验证，这个方法更别不行。效率反而会更低。

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;

对于这样的语句，添加（uid, x, y）这样的索引效果会不错。这是由于MySQL处理排序的机制造成的。

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