新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT > 数据库 > MYSQL

MySQL的 order by 工作原理

时间:2019-05-15 17:05:34  来源:  作者:
<a href=http://www.solves.com.cn/it/sjk/MYSQL/ target=_blank class=infotextkey>MySQL</a>的 order by 工作原理

 

在程序设计当中,我们很多场景下都会用 group by 关键字。比如在分页读取数据时,为了避免重复扫描记录,这就是必须要使用 group by 了。

比如我们使用如下 DDL 创建表:

CREATE TABLE `user_info` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `city` varchar(16) NOT NULL COMMENT '城市',
 `name` varchar(16) NOT NULL COMMENT '姓名',
 `age` int(11) NOT NULL COMMENT '年龄',
 `addr` varchar(128) DEFAULT NULL COMMENT '地址',
 PRIMARY KEY (`id`),
 KEY `city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

并且我们会执行如下查询语句

SELECT city,`name`,age FROM user_info WHERE city='上海' ORDER BY `name` LIMIT 1000;

全字段排序

因为上面的建表语句已经在 city 字段上面创建索引了,当我们使用 EXPLAIN 命令时,会有如下结果:

MySQL的 order by 工作原理

 

上面 Extra 字段中的 “Using filesort” 表示的就是需要排序,MySQL 会为每个线程分配一块内存用于排序,成为 sort_buffer。下面我们看一下 index(city) 的结构示意图。

MySQL的 order by 工作原理

 

执行流程如下:

  1. 初始化 sort_buffer,确定放入 city name age 这 3 个字段;
  2. 从 city 索引中获取到第一个 city='上海' 的记录,也就是 id_x;
  3. 到主键索引中获取对应的记录,并取出 name city age 的值放入 sort_buffer;
  4. 取下一条符合条件的记录,重复 3 4 的操作,直至不符合条件为止;
  5. 对 sort_buffer 中的数据按照 name 做快速排序;
  6. 取出前 1000 条数据并返回。

我们暂时叫这种排序过程为“全字段排序”,如下所示:

MySQL的 order by 工作原理

 

图中的“按 name 排序” 可能在内存中,也可能使用磁盘文件排序,这取决与排序所需要的内存和 sort_buffer_size 。sort_buffer_size 就是 MySQL 为排序开辟的内存大小,当所需内存小于 sort_buffer_size 时,就直接在内存中完成排序,如果所需要的内存 大于 sort_buffer_size ,就需要额外的磁盘空间辅助排序。

rowid 排序

上面的算法在数据量比较大的时候,可能会出现一些问题。因为在排序的时候,存放了所有的返回字段,增加了 排序空间 (sort_buffer)的压力。

SET max_length_for_sort_data=16;

max_length_for_sort_data 是MySQL 限制排序行大小的参数。意思是,如果排序行大小超过了这个值,就会另选排序算法。上面 name city age 3 个字段的大小为 36,大于 16 ,在新的算法中将只有 name (排序字段) 和id 参与 sort_buffer 中的排序。过程如下

  1. 初始化 sort_buffer,确定放入 name id 这 2 个字段;
  2. 从 city 索引中获取到第一个 city='上海' 的记录,也就是 id_x;
  3. 到主键索引中获取对应的记录,并取出 name id 的值放入 sort_buffer;
  4. 取下一条符合条件的记录,重复 3 4 的操作,直至不符合条件为止;
  5. 对 sort_buffer 中的数据按照 name 做快速排序;
  6. 取出前 1000 条数据,然后根据 id 取出对应记录的 name city age 3 个字段并返回结果。

这种排序过程,我们称为 rowid 排序,过程如下所示:

MySQL的 order by 工作原理

 

全字段排序 VS rowid 排序

从上面 2 个流程看来,如果内存足够时,MySQL 会让返回值中所有字段存放在排序空间。当MySQL 内存过小时,才会考虑使用rowid 排序。但是从上面的流程看来,rowid 排序在返回结果前,还会再一次的回表。因此MySQL 认为内存充足的时候,会优先采用 全字段排序。

上面的场景是:city 字段过滤后,name 字段不是有序的。其实我们可以通过联合索引来规避掉 name 字段的排序。

alter table user_info add index idx_city_user(city, name);

下面我们看一下联合索引的示意图:

MySQL的 order by 工作原理

 

从上面流程图可以看出,当我们取出 city='上海' 的记录时,name的字段也是有序的。过程如下

  1. 从 (city, name)索引中获取到第一个 city='上海' 的记录 id_x;
  2. 到主键索引中获取对应的记录,并取出 name city age 的值作为结果集的一部分直接返回;
  3. 取下一条符合条件的记录,重复 2 3 的操作,直至不符合条件或者达到 1000 条为止;
MySQL的 order by 工作原理

 

从联合索引看来,我们是可以不用排序操作了,那么我们是否可以直接通过 索引就直接返回结果呢?也就是不要回表操作。答案是有的,那就是覆盖索引。

alter table user_info add index idx_city_user_age(city, name, age);

当执行查询语句时,不仅 name 中的字段是有序的,并且 索引中已经包含了结果集中的所有字段,过程如下:

  1. 从 (city, name,age)索引中获取到第一个 city='上海' 的记录,并取出 name city age 的值作为结果集的一部分直接返回;
  2. 取下一条符合条件的记录,重复 1 2 的操作,直至不符合条件或者达到 1000 条为止;
MySQL的 order by 工作原理

 


参考:《极客时间:MySQL实战》、《高性能MySQL》
 



Tags:MySQL   点击:()  评论:()
声明:本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
零、用户管理:1、新建用户:>CREATE USER name IDENTIFIED BY &#39;ssapdrow&#39;;2、更改密码:>SET PASSWORD FOR name=PASSWORD(&#39;fdddfd&#39;);3、权限管理>SHOW GRANTS...【详细内容】
2019-07-18   MySQL  点击:(3)  评论:(0)  加入收藏
先看结构体:以下代码块是用来连接数据库的通讯过程,要连接MYSQL,必须建立MYSQL实例,通过mysql_init初始化方能开始进行连接.typedef struct st_mysql {NET net; /* Communicati...【详细内容】
2019-07-18   MySQL  点击:(4)  评论:(0)  加入收藏
1、从概念上来讲,它们是不同的,truncate是DDL语句,会隐式提交,所以,不能回滚,不会触发触发器。delete是DML语句,这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应...【详细内容】
2019-07-17   MySQL  点击:(1)  评论:(0)  加入收藏
概述在MySQL数据库中,直到5.7这个版本,开始引入JSON数据类型,在此之前如果想在表中保存JSON格式类型的数据,则需要依靠varchar或者text之类的数据类型,如果在低于5.7版本的数据库...【详细内容】
2019-07-17   MySQL  点击:(2)  评论:(0)  加入收藏
概述今天主要分享一下mysql的数据库规范,仅供参考。从基础、命名、表设计、字段设计、索引设计、sql编写、行为规范几个方面做介绍。 基础规范 1、必须使用InnoDB存储引擎说...【详细内容】
2019-07-17   MySQL  点击:(1)  评论:(0)  加入收藏
48作者 | Python语音识别来源 | 深度学习与python(ID:PythonDC)不管是机器学习、web开发或者爬虫,数据库都是绕不过去的。那么今天我们就来介绍Python如何Mysql数据库进行连...【详细内容】
2019-07-17   MySQL  点击:(0)  评论:(0)  加入收藏
概述今天主要介绍一下mysql数据库一般修改InnoDB redo log事务日志文件大小的步骤,然后用一个实验来演示一下,下面一起来看看吧~基本步骤在MySQL 5.5版本里,如果想修改ib_logfi...【详细内容】
2019-07-16   MySQL  点击:(3)  评论:(0)  加入收藏
前言假设现在我们要向mysql插入500万条数据,如何实现高效快速的插入进去?暂时不考虑数据的获取、网络I/O、以及是否跨机操作,本文将在本地进行数据的插入,单纯从mysql入手,把优化...【详细内容】
2019-07-16   MySQL  点击:(10)  评论:(0)  加入收藏
概述有朋友问怎么去看连接mysql数据库有哪些用户及对应的IP地址等等信息,所以顺便整理了下这块内容,下面是涉及的一些命令。1、查看当前连接到数据库的用户和Host## 查看当前...【详细内容】
2019-07-15   MySQL  点击:(1)  评论:(0)  加入收藏
概述闲来无事,看了下高性能mysql这本书,其中的一些MySQL高级特性写的还不错,在这里总结分享下。01分区表1、分区表限制 一张表最多1024个分区 分区表中无法使用外键约束2、分区...【详细内容】
2019-07-12   MySQL  点击:(10)  评论:(0)  加入收藏
对于Mysql常用的SQL语句比如select、create、insert、update、delete、join、order by、group by等等相信大家都不陌生;但对于一些不是很常用却又十分实用的语句在要使用的...【详细内容】
2019-07-11   MySQL  点击:(9)  评论:(0)  加入收藏
一、MySQL 优点:体积小、速度快、总体拥有成本低,开源;支持多种操作系统;是开源数据库,提供的接口支持多种语言连接操作 ;MySQL的核心程序采用完全的多线程编程。线程是轻量级的进...【详细内容】
2019-07-11   MySQL  点击:(6)  评论:(0)  加入收藏
概述今天主要分享一款MySQL日志分析神器--mysqlsla,对于我们分析mysql数据库的三大日志还不错,这里介绍一下。什么是mysqlsla?Mysqlsla 是daniel-nichter 用perl 写的一个脚本,...【详细内容】
2019-07-11   MySQL  点击:(4)  评论:(0)  加入收藏
应用程序慢如牛,原因多多,可能是网络的原因、可能是系统架构的原因,还有可能是数据库的原因。那么如何提高数据库SQL语句执行速度呢?有人会说性能调优是数据库管理员(DBA)的事,然...【详细内容】
2019-07-11   MySQL  点击:(6)  评论:(0)  加入收藏
介绍获取MySQL分区表信息的几种方法。 1. show create table 表名可以查看创建分区表的create语句。 /*!...*/ 是一种特殊的注释,其他的数据库产品当然不会执行。mysql特殊处...【详细内容】
2019-07-09   MySQL  点击:(6)  评论:(0)  加入收藏
时间存储时间存储是我们在MySQL中最常用的一种存储类型,MySQL为我们提供了timestamp和datetime两种数据类型,那么这两者有什么区别,又该如何进行选择呢timestamp和datetime的...【详细内容】
2019-07-08   MySQL  点击:(19)  评论:(0)  加入收藏
概述前面已经介绍了Oracle如何去获取某用户下的所有表的行数,所以就不介绍了,今天主要分享的是怎么去获取mysql数据库下所有表的数据行数,主要介绍3个方法。1、估算某个数据库...【详细内容】
2019-07-08   MySQL  点击:(6)  评论:(0)  加入收藏
创建用户并授权创建用户CREATE USER &#39;custom&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;password&#39;; mysql 8.0 默认身份验证插件为caching_sha2_password,导致很...【详细内容】
2019-07-04   MySQL  点击:(9)  评论:(0)  加入收藏
一、主从数据库的区别从数据库(Slave)是主数据库的备份,当主数据库(Master)变化时从数据库要更新,这些数据库软件可以设计更新周期。这是提高信息安全的手段。主从数据库服务...【详细内容】
2019-07-03   MySQL  点击:(10)  评论:(0)  加入收藏
一、摘要本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持...【详细内容】
2019-07-03   MySQL  点击:(8)  评论:(0)  加入收藏
推荐资讯
相关文章
栏目更新
栏目热门