代码编织梦想

order by排序方式

一般分两种,在索引中排序(索引里面数据有序),在内存中排序(内存不够的话会产生临时文件辅助排序)。其中走索引的排序会快很多。

索引排序

既然我们知道排序走索引会快很多,那我们排序时应该尽量让排序走索引。那什么情况下排序会走索引呢?我们知道查询排序语句一般由这几个部分构成:select +where+order by+limit…等等。所以SQL走不走索引主要由这几部分的限制决定。一般查询排序语句是先将排序的几列和id从数据库读出进行排序,排序完成后再根据id去表中查询select的列,最后返回客户端结果集。因为索引中的数据是有序的,排序的列和查询的列同时在一个索引中,并且顺序是相同的,就可以直接从索引中读出这几列返回给客户端。

例子

下面是t3表建表语句
在这里插入图片描述

  • 查询语句是select idc,name from t3 order by idc,因为查询和排序的字段存在索引
    idx_idc_name_id,所以会走索引。
  • 查询语句是select idc,name,nickname from t3 order by idc,因为不存索引同时包含idc,name,nickname,所以不会走索引。
  • 查询语句是select idc,name from t3 order by idc,name,因为查询和排序的存在索引
    idx_idc_name_id,所以会走索引。
  • 查询语句是select idc,name from t3 order by name,因为排序的字段是name,没有以这个字段为顺序的索引,所以不会走索引。
  • 查询语句是select idc,name,id from t3 order by idc,id,因为排序的字段是idc和id,没有以这两个字段为顺序的索引(索引idx_idc_name_id里面的排序顺序是按照idc,name,id来排序的),所以排序不会走索引,但是查询会走索引(会先在内存中将idc,id两个字段排序好,再根据id去查询select需要的字段,因为查询的字段存在索引idx_idc_name_id,所以直接再索引中得到id相对应的字段)。
  • 查询语句是select name,id from t3 order by idc,因为查询和排序的存在索引
    idx_idc_name_id,所以会走索引。
  • 查询语句是select id,name from t3 order by id,因为排序的存在主键,所以会走主键。
  • 查询语句是select idc,name from t3 order by idc,name,因为查询和排序的存在索引idx_idc_name_id,所以会走索引。
  • 查询语句是select id,name from t3 where idc=3 order by idc,因为排序和查询字段存在索引idx_idc_name_id,所以会走索引。
  • 查询语句是select id,name from t3 where idc=3 order by name,虽然不存在以name为顺序的索引,但是!!!where idc=3这个条件,限制了idc是相同的,当idc相同时,索引 idx_idc_name_id就是按照name排序的,因此这个语句依然会走索引。
    在这里插入图片描述
  • 查询语句是select id,name from t3 where idc>2 order by name,虽然where语句限定了idc>2这个条件,将数据分为了两部分>2,<=2。但是索引idx_idc_name_id是先按照idc排序,再按照name,最后按id排序,而我们的排序是只按照name,idc>2的数据里面id也是不一样的,所以没有走索引。
    在这里插入图片描述

非索引排序

当没有走索引的时候,MySQL内部会有三种排序实现分别是常规排序优化排序优先队列排序。主要涉及三种排序算法快速排序归并排序堆排序

常规排序

常规排序使用的排序算法是快速排序!
MySQL会先把符合where限定的行筛选出来,然后读出排序的字段+主键到内存中(sort buffer),如果sort buffer满了,会写入临时文件中进行排序。排序完后根据主键去筛选出来的行中读出select需要的字段形成结果集返回给客户端。

优化排序

常规排序除了排序所消耗的时间外,还有两次io(第一次是读出排序的列和主键,第二次是排序完后根据主键去读select的列),这也会造成时间的消耗,所以就有了优化排序。优化排序是一次行将排序列和select列都读入sort buffer中,排序完后直接将结果集方会给客户端,减少了第二次io造成的时间开销,但是因为需要的内存较大,经常会产生临时文件(如果很多个临时文件,还会使用归并排序)。

优先队列排序

5.6版本的MySQL产生了优先队列排序实现,采用堆排序,对于升序采用最大堆,降序采用最小堆。
但是limit有时会产生分页重复(再MySQL8中被修复),因为堆排序是非稳定的,对于相同的key无法保证排序前后位置一致。

总结

对select字段,where条件,order字段进行设计,尽可能的让排序走索引。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接: https://blog.csdn.net/nntcdhl/article/details/111092855

SQL之sqli-labs注入Less-2~4-爱代码爱编程

一、Less-2 与Less-1相同,我们同样使用?id=1'看是否可以注入, 发现不可以,并且提示错误,我们可以知道,这里不需要引号,这就是与Less-1不同的地方,除此之外,以下步骤与Less-1相同 2.接下来我们使用 order by 查看有多少列 仍然有三列? id=1 order by 3--+3.使用联合查询 union select 看有

使用 DML语句,对 “锦图网” 数据进行操作,连接查询(内连接,左外连接,右外连接,全连接)-爱代码爱编程

查看本章节 查看作业目录 需求说明: 对 “锦图网” 数据进行操作: 统计每一种线路类型的线路数量、最高线路价格、最低线路价格和平均线路价格,要求按照线路数量和平均线路价格升序显示。统计每种类型线路的订购数量和平均价格,要求按线路数量和平均线路价格升序显示查询指定客户(如“郝琼琼”)所预订的所有线路信息,要求显示下单客户姓名、出行客户姓名、订单号、

手把手搭建Java汽车租赁管理系统【附源码】(毕设)-爱代码爱编程

文末源码和视频 一、项目简介 ​ 【汽车租凭管理系统】,基于Java实现的汽车租凭管理系统 二、技术实现 IOC容器:Spring Web框架:SpringMVC ORM框架:Mybatis(持久层) 数据源:C3P0 日志:log4j 前端框架:layui、jQuery 三、系统功能

mac 上的mysql 修改了my.cnf后不生效问题处理-爱代码爱编程

在mac上安装了mysql后,需要修改下mysql的配置,但是在mysql的安装目录下的修改了my.cnf后不生效,目录如下图: 原因是,mysql默认的配置文件并不是这个文件。 使用命令( mysql --verbose --help | grep my.cnf )查看mysql的配置文件加载顺序: MyMac:~ wx$ mysql --ve

MySQL高级查询与编程笔记 • 【第3章 子查询】-爱代码爱编程

全部章节   >>>> 本章目录 3.1 子查询定义和单行子查询 3.1.1 子查询定义 3.1.2 单行子查询应用 3.1.4 实践练习 3.2 多行子查询应用 3.2.1 in 比较符 3.2.3 any|some 关键字子查询 3.2.4 实践练习 3.3 子查询特殊应用 3.3.1 from 子句中的子

JavaEE之Mybatis关系映射-爱代码爱编程

JavaEE之Mybatis关系映射 创建封装工具类一对一(旅客与护照)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试一对多(部门与职员)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试多对多(学生与科目)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper

SQL:多表查询语句(嵌套子查询,多表连接)操作实例-爱代码爱编程

一、SQL Server多表查询,包括连接操作和嵌套子查询 背景知识: 一、连接:分成内连接和外连接,内连接相当于取交集,外连接相当于取并集 二、嵌套子查询:连接操作浪费资源,使用嵌套子查询可以避免连接同时加快执行速度,分成相关子查询和非相关子查询 名称解释(不区分大小写) student学生表: 包含属性列:sno学号、sname学

SQL之sqli-labs注入Less-2~4-爱代码爱编程

一、Less-2 与Less-1相同,我们同样使用?id=1'看是否可以注入, 发现不可以,并且提示错误,我们可以知道,这里不需要引号,这就是与Less-1不同的地方,除此之外,以下步骤与Less-1相同 2.接下来我们使用 order by 查看有多少列 仍然有三列? id=1 order by 3--+3.使用联合查询 union select 看有

SQL:简单查询语句操作实例-爱代码爱编程

一、SQL Server简单查询语句 背景知识: 一、查询:SQL中最基本、最常用的操作,用来对数据库进行查询 二、表达式: select 属性列 from 表 where 筛选条件 group by 分组属性列 having 分组后筛选条件 orser by 排序 名称解释(不区分大小写) student学生表: 包含属性列:sno

MySQL高级查询与编程笔记 • 【第3章 子查询】-爱代码爱编程

全部章节   >>>> 本章目录 3.1 子查询定义和单行子查询 3.1.1 子查询定义 3.1.2 单行子查询应用 3.1.4 实践练习 3.2 多行子查询应用 3.2.1 in 比较符 3.2.3 any|some 关键字子查询 3.2.4 实践练习 3.3 子查询特殊应用 3.3.1 from 子句中的子

JavaEE之Mybatis关系映射-爱代码爱编程

JavaEE之Mybatis关系映射 创建封装工具类一对一(旅客与护照)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试一对多(部门与职员)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper文件测试多对多(学生与科目)创建数据表创建相应实体类创建DAO接口创建Mapper文件注册Mapper

使用子查询统计“国内长线游”线路数、线路最高价格和线路最低价格-爱代码爱编程

查看本章节 查看作业目录 需求说明: 使用子查询统计“国内长线游”线路数、线路最高价格和线路最低价格 使用子查询获得指定客户(如“魏国兰”)订购线路的相关信息,要求显示订单名和订单日期 说明:客户姓名“魏国兰”没有重名 实现思路: 需求说明(1)的解决思路: 单行子查询:从线路类型表获取类型名为“国内长线游”的类型编号主查询:使用聚合函数从