ORDER BY 可以匹配索引是限制的
具体可以参考下http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html
本地做了下测试,发现跟单纯的读文档的理解差别还是挺大的。
知识点一:ORDER BY的字段是否走索引跟表的数据量有关系,表的数据量比较小的时候会走全表扫描,数据量比较大的时候才会走索引,这可能也是MySQL基于代价的结果。
知识点二:WHERE KEY_PART1 > CONST ORDER BY KEY_PART1,KEY_PART2也是可以走索引的,但是官方没有列举这么仔细。
知识点三:MySQL两种排序方式一种是需要回表的,另外一种不需要回表。
一般情况包含大字段的时候会需要回表,否则直接都拿出来了对关键字排序即可。
知识点四:这里filesort并不是说通过磁盘文件进行排序,仅仅告诉我们进行了一个排序操作。
知识点五:如果排序字段同时存在两个表中,或者join完之后排序,则需要在临时表中进行,一般如果order by字段在驱动表上则先对驱动表进行排序再做join也是顺序的。
知识点六:如果无法避免排序,该如何优化。
1.增大max_length_for_sort_data 如果所有字段的最大长度小于这个参数值的时候,MySQL会选择第二种排序算法,否则选择第一种。
2.去掉不必要的字段,如果内存不够但是增大max_length_for_sort_data,则需要排序的数据会分成很多段进行,效率比较低,去掉不必要的字段来适应max_length_for_sort_data。
3.增大sort_buffer_size参数设置:
增大这个参数并不是为了让MySQL可以选择第二种排序算法,而是为了让数据减少排序的分段。
ORDER BY可以走索引的情况:
-- Order by explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float`,`real`; -- Order by的排序顺序必须一致 explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float` asc,`real` asc; -- Order by不一定完全匹配索引,但是where中必须是常量 explain SELECT * FROM `mytest`.`table_rm002` where `float` = 100 ORDER BY `real` asc; -- Order by是组合索引,where中必须是常量 explain SELECT * FROM `mytest`.`table_rm002` where `float` = 100 ORDER BY `float` asc,`real` asc; -- Order by是组合索引,where中必须是常量【不能走索引】 explain SELECT * FROM `mytest`.`table_rm002` where `real` = 100 ORDER BY `float` asc,`real` asc; -- 下面三种待验证 SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC; SELECT * FROM t1 WHERE key_part1 < constant ORDER BY key_part1 DESC; SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2 测试了下官网给出的三个模板SQL有点令人误解: 其实这样的格式也是可以使用索引的 SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1, key_part2;
测试案例:
构建10条数据的表
看看10条数据的ORDER BY索引会怎么走,竟然没有走索引,而是使用了using filesort
测试50W条数据情况
看看50W条数据的ORDER BY索引会怎么走,发现走了索引idx_cmplx,奇怪吧,反正我是有点颠覆了价值观。
看吧,虽然这里的key_part1是< ,order by是key_part1和key_part2但是仍然走了索引
mysql> explain SELECT * FROM `mytest`.`table_rm002` where `float` < 100 order by `float`,`real` \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: table_rm002 type: range possible_keys: idx_cmplx,idx_float key: idx_cmplx key_len: 5 ref: NULL rows: 59 Extra: Using where 1 row in set (0.01 sec)
附上测试中使用的SQL语句
-- 查看Query的执行计划 explain SELECT * FROM `mytest`.`table_rm002` ORDER BY `float`,`real`; -- 重建表索引 analyze table `mytest`.`table_rm002`; -- 删除数据 delete from `mytest`.`table_rm002`; -- 构造数据 insert into `mytest`.`table_rm002` select * from `mytest`.`table_rm001` limit 500000; -- 表的创建语句 CREATE TABLE `table_rm002` ( `id` int(11) NOT NULL AUTO_INCREMENT, `varchar` varchar(32) DEFAULT NULL, `tinyint` tinyint(4) DEFAULT NULL, `smallint` smallint(6) DEFAULT NULL, `mediumint` mediumint(9) DEFAULT NULL, `bigint` bigint(20) DEFAULT NULL, `integer` int(11) DEFAULT NULL, `float` float DEFAULT NULL, `real` double DEFAULT NULL, `decimal` decimal(10,0) DEFAULT NULL, `boolean` tinyint(1) DEFAULT NULL, `date` date DEFAULT NULL, `datetime` datetime DEFAULT NULL, `time` time DEFAULT NULL, `year` year(4) DEFAULT NULL, `text` text, `blob` blob, PRIMARY KEY (`id`), KEY `idx_float` (`float`), KEY `idx_varchar` (`varchar`), KEY `idx_cmplx` (`float`,`real`) ) ENGINE=MyISAM AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
相关推荐
从执行计划、SELECT语句、IN和EXIST语句、LIMIT语句、RAND函数、Order by、Group by、Distinct和Count等方面对MySQL数据库优化进行了详细的讲解。 一、执行计划 执行计划是MySQL数据库优化的重要步骤。执行计划...
MySQL数据库在处理含`ORDER BY`的SQL语句时,索引优化是非常关键的一环,因为这直接影响到查询性能。以下是一些关于如何利用索引来优化`ORDER BY`语句的知识点: 1. **合理创建索引**:索引可以显著提高数据读取...
创建表&创建索引 create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc...通过索引优化来实现MySQL的ORDER BY语句优化: 1、ORDER BY的索引优化 如果一个SQL语句形如: SELECT
总之,理解`ORDER BY`和`GROUP BY`的索引优化策略,以及如何通过`EXPLAIN`分析查询计划,可以帮助我们有效地优化查询,避免不必要的`filesort`操作,从而提升MySQL数据库的性能。在设计索引时,应考虑查询语句的常见...
- **查询优化**: 优化SQL语句,避免全表扫描和子查询,使用JOIN代替子查询,合理使用LIMIT和ORDER BY。 - **存储引擎选择**: 根据数据访问模式选择合适的存储引擎,如InnoDB支持事务处理,MyISAM适合读取密集型...
这可能包括遵循规范的SQL编写习惯,避免在WHERE子句中使用函数,合理使用LIMIT和ORDER BY,以及避免过度依赖临时表。 总的来说,这份"Mysql优化 PPT"应该是一份全面的指南,帮助读者理解MySQL性能优化的各个方面,...
MySQL 优化方法主要涵盖...总之,MySQL优化涉及多个层面,包括合理选择存储引擎、优化字段类型、建立合适索引以及编写高效的SQL语句。每个环节都对数据库性能有着直接影响,需要根据实际业务需求进行综合考虑和调整。
SELECT * FROM user WHERE area = '$area' AND sex = '$sex' ORDER BY lastlogin DESC LIMIT 0, 30; ``` - **解决方案**:创建`area + sex + lastlogin`的复合索引,这样可以同时利用地区和性别筛选结果,并按...
### DISTINCT优化之MySQL官方文档翻译解析 #### 一、引言 在数据库查询操作中,经常需要使用`DISTINCT`关键字来去除重复记录,确保结果集中的每一条记录都是唯一的。然而,在某些场景下,使用`DISTINCT`可能会导致...
MySQL优化是指通过一系列技术和策略来提高数据库性能,确保系统能够高效地处理大量数据请求。以下是对MySQL优化的详细探讨: 一、SQL查询优化 1. 索引优化:创建合适的索引来加速查询。主键自动创建唯一索引,非...
课程大纲: 第1课 数据库与关系代数 综述数据库、关系代数、查询优化技术 综述数据库调优技术 预计时间1小时 第2课 数据库查询优化技术总揽 ...真正认识、掌握MySQL的查询优化技术,大步流星步入查询优化的高手之列。
本文讨论了 MYSQL 优化方案的多个方面,涵盖 BIOS 设置优化、IO 子系统优化、Schema 设计优化、索引设计优化和无法使用索引的场景等方面的知识点,旨在帮助读者更好地了解 MYSQL 优化的方法和策略。
- 索引优化SQL:避免全表扫描,尽可能利用索引来加速查询,减少`SELECT`、`JOIN`、`ORDER BY`、`GROUP BY`操作的复杂度。 - 索引维护:定期重建和优化索引,确保其整洁高效。 4. **数据库表结构优化** - **数据...
我们可以通过使用其他方法来优化ORDER BY RAND()语句,提高SQL语句的性能。 七、in和exists的区分 in和exists是SQL语句中常用的两个关键字。in和exists的主要区别在于驱动顺序的改变。如果是in,那么先执行子查询...
在深入探讨辛星笔记之MySQL优化篇的内容之前,我们首先要了解MySQL优化的几个关键方面,这将帮助我们更好地理解文档中提供的知识点。 MySQL优化通常包括对数据库查询的优化、数据库结构的设计优化、服务器配置的...
考虑在经常用于WHERE子句、JOIN条件和ORDER BY语句的列上创建索引。 - **避免全表扫描**:尽量避免使用SELECT *,只选择需要的列,减少I/O操作。 - **使用EXPLAIN分析查询**:理解查询执行计划,找出可能导致全表...
在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦...
本文将深入探讨“mysql查询优化之索引优化”这一主题。 首先,了解索引的基本概念至关重要。索引是数据库为了快速查找数据而创建的一种数据结构,类似于书籍的目录,它使得数据检索更快,减少了全表扫描的可能性。...
SELECT * FROM order WHERE status IN (0, 1) ORDER BY date DESC; ``` 此查询可以利用`status`和`date`的索引来提高效率,特别是如果这两个字段都有索引的话。 4. **组合条件查询**: ``` SELECT * FROM ...