- 浏览: 230393 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
thepastsee:
304572183股票交流 欢迎加入
实时股票 -
345161974:
很不错,我第一个接触的CSS卡片布局效果,多谢
css卡片效果
在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序。尽管 ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了。
使用索引的MySQL Order By
下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分:
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
不使用索引的MySQL Order By
在另一些情况下,MySQL无法使用索引来满足 ORDER BY,尽管它会使用索引来找到记录来匹配 WHERE 子句。这些情况如下:
* 对不同的索引键做 ORDER BY :
SELECT * FROM t1 ORDER BY key1, key2;
* 在非连续的索引键部分上做 ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* 同时使用了 ASC 和 DESC:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* 用于搜索记录的索引键和做 ORDER BY 的不是同一个:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* 有很多表一起做连接,而且读取的记录中在 ORDER BY 中的字段都不全是来自第一个非常数的表中(也就是说,在 EXPLAIN 分析的结果中的第一个表的连接类型不是 const)。
* 使用了不同的 ORDER BY 和 GROUP BY 表达式。
* 表索引中的记录不是按序存储。例如,HASH 和 HEAP 表就是这样。
通过执行 EXPLAIN SELECT ... ORDER BY,就知道MySQL是否在查询中使用了索引。如果 Extra 字段的值是 Using filesort,则说明MySQL无法使用索引。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。当必须对结果进行排序时,MySQL 4.1以前 它使用了以下 filesort 算法:
1. 根据索引键读取记录,或者扫描数据表。那些无法匹配 WHERE 分句的记录都会被略过。
2. 在缓冲中每条记录都用一个‘对’存储了2个值(索引键及记录指针)。缓冲的大小依据系统变量 sort_buffer_size 的值而定。
3. 当缓冲慢了时,就运行 qsort(快速排序)并将结果存储在临时文件中。将存储的块指针保存起来(如果所有的‘对’值都能保存在缓冲中,就无需创建临时文件了)。
4. 执行上面的操作,直到所有的记录都读取出来了。
5. 做一次多重合并,将多达 MERGEBUFF(7)个区域的块保存在另一个临时文件中。重复这个操作,直到所有在第一个文件的块都放到第二个文件了。
6. 重复以上操作,直到剩余的块数量小于 MERGEBUFF2 (15)。
7. 在最后一次多重合并时,只有记录的指针(排序索引键的最后部分)写到结果文件中去。
8. 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作,MySQL将记录指针读取放到一个大的块里,并且使用它来按序读取记录,将记录放到缓冲中。缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。
这个逼近算法的一个问题是,数据库读取了2次记录:一次是估算 WHERE 分句时,第二次是排序时。尽管第一次都成功读取记录了(例如,做了一次全表扫描),第二次是随机的读取(索引键已经排好序了,但是记录并没有)。在MySQL 4.1 及更新版本中,filesort 优化算法用于记录中不只包括索引键值和记录的位置,还包括查询中要求的字段。这么做避免了需要2次读取记录。改进的 filesort 算法做法大致如下:
1. 跟以前一样,读取匹配 WHERE 分句的记录。
2. 相对于每个记录,都记录了一个对应的;‘元组’信息信息,包括索引键值、记录位置、以及查询中所需要的所有字段。
3. 根据索引键对‘元组’信息进行排序。
4. 按序读取记录,不过是从已经排序过的‘元组’列表中读取记录,而非从数据表中再读取一次。
使用改进后的 filesort 算法相比原来的,‘元组’比‘对’需要占用更长的空间,它们很少正好适合放在排序缓冲中(缓冲的大小是由 sort_buffer_size 的值决定的)。因此,这就可能需要有更多的I/O操作,导致改进的算法更慢。为了避免使之变慢,这种优化方法只用于排序‘元组’中额外的字段的大小总和超过系统变量 max_length_for_sort_data 的情况(这个变量的值设置太高的一个表象就是高磁盘负载低CPU负载)。想要提高 ORDER BY 的速度,首先要看MySQL能否使用索引而非额外的排序过程。如果不能使用索引,可以试着遵循以下策略:
* 增加 sort_buffer_size 的值。
* 增加 read_rnd_buffer_size 的值。
* 修改 tmpdir,让它指向一个有很多剩余空间的专用文件系统。
如果使用MySQL 4.1或更新,这个选项允许有多个路径用循环的格式。各个路径之间在 Unix 上用冒号(':')分隔开来,在 Windows,NetWare以及OS/2 上用分号(';')。可以利用这个特性将负载平均分摊给几个目录。注意:这些路径必须是分布在不同物理磁盘上的目录,而非在同一个物理磁盘上的不同目录。
优化嵌套查询
MySQL4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个查询结果作为过滤条件用在另一个查询中,使用子查询可以一次性地完成多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且些起来也很容易。但是,有些情况下,子查询可以被更有效的连接(JOIN)替代。
例如:
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->explain select * from sales2 where company_id not in(select id from company2) \G explain select * from sales2 left join comany2 on sales2.company_id = company2.id where sales2.company_id is null \G;
第一句看起来比第二句更简洁,但是第二句比第一就更快。因为使用JOIN来完成这个查询,速度比较快,尤其如果对compay2表中的id建立了索引的话,那么性能将会更好。那为什么在这种情况下使用JOIN会更有效率呢。因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
优化OR条件
对于含有OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引;如果没有索引,则考虑增加索引。
使用SQL提示
SQL 提示(SQL HINT)是优化数据库的一个重要手段,简单来说就是在SQL语句中加入一些人为的提示来达到优化的操作的目的。 例如:
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->SELECT SQL_BUFFER_RESULTS * FROM ...
这个语句将强制MySQL生成一个临时结果集。只要临时结果集生成后,所有表上的锁定均被释放。这能在遇到表锁定问题时或者要花很长时间将结果传给客户端时所帮助,因为可以尽快释放锁资源,
下面是一些在MySQL中常用的SQL提示。
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->explain select * from sales2 use index (ind_sales2_id) where id 3 \G;
2. IGNORE INDEX
如果用户只是单纯地想让MySQL忽略一个或者多个索引,则可以使用IGNORE INDEX 作为HINT
3. FORCE INDEX
为强制MySQL使用一个特定的索引,可在查询中使用FORCE INDEX作为HINT。例如当不强制使用索引的时候,因为id的值都是大于0的,因为MySQL会默认进行全表扫描,而不使用索引。例如:
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->expalin select * from sales2 where id > 0 \G;
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->explain select * from sales2 force index(index_sales2_id) where id > 0 \G;
SQL优化问题是数据库性能优化最基础也是最重要的一个问题,实践表明很多数据库性能问题都是由于不合适的SQL语句造成。一些列的SQL优化描述。怎么定位问题,怎么在编写的时候优化,怎么来应对。不过优化SQL语句经常需要考虑的几个方面,比如索引,表分析,排序等等。
发表评论
-
mysql_group by 奇怪问题解决
2011-10-22 13:32 954我回去查了一下关于mysql group by 奇怪的现像, ... -
mysql 密码
2011-09-26 17:46 743linux ---- ... -
mysql调优_20转
2011-06-22 15:24 708http://coolshell.cn/articles ... -
数据库设计
2011-06-22 11:43 659http://www.cnblogs.com/DBFocus/ ... -
MySQL索引的索引长度问题
2011-06-07 21:11 943转 http://blog.haohtml.com/a ... -
mysql_error_code
2011-05-13 21:44 742原创 mysql出错代码及 ... -
sql之left join、right join、inner join的区别_转
2011-04-17 23:37 860http://www.cnblogs.com/pcjim ... -
mysql_p150 error_转
2011-04-17 21:06 742ERROR ... -
mysql_序列表_02
2011-03-27 11:08 667lkjlkj -
mysql_投档项目终结
2011-03-21 21:41 619kljljlkj;lkj -
mysql_批量数据脚本..
2011-03-21 20:29 783lkjlkj -
window 启动仃止mysql
2011-03-17 23:32 939[导读]在非NT版本的Windows中,在后台启动mysqld ... -
flush privileges 意思_转转转
2011-03-17 23:31 1450test是mysql安装好后,就有的一个示例数据库 楼主可以 ... -
mysql 权限列表_转转
2011-03-17 23:30 999菜鸟课堂:MySQL权限的详细解答 2009-09-02 1 ... -
mysql 存储过程参数_可能乱码
2011-03-17 01:25 737DELIMITER $$ DROP PROCEDURE ... -
mysql存储过程_游标_项目练习
2011-03-17 01:04 852;lk;kl;kl;lk -
mysql 权限管理_转转转
2011-03-15 22:21 1032Mysql用户与权限管理 ... -
给mysql加上 rownum _转
2011-03-12 23:01 1379转 http://home.21cto.com/foru ... -
MySQL动态行转列_zhuan
2011-03-05 18:49 802网上的都是一些静态的,用CASE WHEN结构实现。所以我写了 ... -
MYSQL中删除重复记录的方法_zhuanzhuan
2011-03-05 18:48 840在实际应用中,很可能会碰到一些需要删除某些字段的重复记录, ...
相关推荐
尚硅谷_MySQL高级_为排序使用索引OrderBy优化 · 49.尚硅谷_MySQL高级_慢查询日志 · 50.尚硅谷_MySQL高级_批量插入数据脚本 · 51.尚硅谷_MySQL高级_用Show Profile进行sql分析 · 52.尚硅谷_MySQL高级_全局...
3、尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以BTree存储索引),而不是InnoDB。但MyISAM不支持Transcation。 4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度,而程序瓶颈并不能顺利解决,那...
关于mysql索引一些优化介绍与创建原则,还有对order by排序的算法的介绍等等
在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序
创建表&创建索引 create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc...通过索引优化来实现MySQL的ORDER BY语句优化: 1、ORDER BY的索引优化 如果一个SQL语句形如: SELECT
MySQL通常使用GROUPBY(本质上是排序动作)完成DISTINCT操作,如果DISTINCT操作和ORDERBY操作组合使用,通常会用到临时表.这样会影响性能. 在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个...
48.MySQL高级_为排序使用索引OrderBy优化.avi 47.MySQL高级_in和exists.avi 46.MySQL高级_小表驱动大表.avi 45.MySQL高级_索引优化答疑补充和总结口诀.avi 44.MySQL高级_索引面试题分析.avi 43.MySQL高级...
在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦...
通过联合索引优化MYSQL慢查询 一、慢SQL分析 慢SQL如下 SELECT SUM(t.amount) as amount FROM (SELECT game_id,room_id,is_robot,amount,record_time FROM t_statements_logs WHERE game_id = 30 AND is_robot = 0 ...
Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景,其优化原理也非常的简单,就是利用索引的有序性,优化器沿着索引的顺序扫描,在扫描到符合条件的M行数据后,停止扫描;看起来非常的简单,但是...
当查询语句的 order BY 条件和查询的执行计划中所利用的 Index 的索引键(或前面几个索引键)完全一致,且索引访问方式为 rang,ref 或者 index 的时候,MySQL 可以利用索引顺序而直接取得已经排好序的数据。...
从理论看,GROUP BY、ORDER BY、LIMIT、DISTINCT等怎么被优化? MySQL中:GROUP BY是怎么优化的?ORDER BY是怎么被优化?LIMIT是怎么被优化?DISTINCT是怎么被优化? 非SPJ优化与索引的关系。 预计时间1小时 第10课...
1.如果索引了多列,要遵守最左前缀法则 2.当MySQL一旦估计检查的行数可能会”太多”,范围查找优化将不会被 3.索引列不应该作为表达式的一部分,即也不能在索
通过将查询条件放在WHERE子句中,并使用ORDER BY和LIMIT限制结果集大小,可以减少数据传输量,提高查询效率。 - 接下来,我们展示了如何为id和score字段创建索引,以及如何删除不必要的索引。创建合适的索引可以加快...
在MySQL数据库中,Order by语句的使用频率是比较高的。但是众所周知,在使用这个语句时,往往会降低数据查询的性能。因为可能需要对数据库的记录进行重新排序。在这篇文章中,笔者就谈谈提高Order By语句查询效率的...
本篇文章我们将了解ORDER BY语句的优化,在此之前,你需要对索引有基本的了解,不了解的老少爷们可以先看一下我之前写过的索引相关文章。现在让我们开始吧。 MySQL中的两种排序方式 1.通过有序索引顺序扫描直接返回...
满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表,表中每个组的所有行应为连续的,然后... 由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组
group by什么时候会出现Using filesort — 理论3 order by 和 group by什么时候会出现Using filesort — 实践3.1 不会出现 Using filesort的情况 — 符合最佳左前缀法则3.1.1 order by也满足索引最佳左前缀法则的...