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

地狱V小生

地狱V小生

2016-02-19 09:01

下面是个简单易学的MySQL 通过索引优化含ORDER BY的语句教程,图老师小编详细图解介绍包你轻松学会,喜欢的朋友赶紧get起来吧!

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

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

2、索引越多,更新数据的速度越慢。

3、尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以BTree存储索引),而不是InnoDB。但MyISAM不支持Transcation。

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的值对应多个。

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)

这个语句怎么优化呢?我暂时没有想到什么好的办法,看到网上有便宜提供的办法,那就是将这个语句用UNION分拆,然后建立第二个例子中的索引:

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)

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 x,y LIMIT 0,10;

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

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

展开更多 50%)
分享

猜你喜欢

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

编程语言 网络编程
MySQL 通过索引优化含ORDER BY的语句

MySQL教程:Order By索引优化

编程语言 网络编程
MySQL教程:Order By索引优化

s8lol主宰符文怎么配

英雄联盟 网络游戏
s8lol主宰符文怎么配

MYSQL WHERE语句优化

编程语言 网络编程
MYSQL WHERE语句优化

MySQL 索引分析和优化

编程语言 网络编程
MySQL 索引分析和优化

lol偷钱流符文搭配推荐

英雄联盟 网络游戏
lol偷钱流符文搭配推荐

MySQL索引分析和优化

MySQL mysql数据库
MySQL索引分析和优化

MySQL Order By用法

编程语言 网络编程
MySQL Order By用法

lolAD刺客新符文搭配推荐

英雄联盟
lolAD刺客新符文搭配推荐

MYSQL数据库初学者使用指南

MYSQL数据库初学者使用指南

Android的单位以及屏幕分辨率详解

Android的单位以及屏幕分辨率详解
下拉加载更多内容 ↓