1、MySQL基础

1.1 MySQL的基本知识

按照惯例，先介绍一下 MySQL基本知识：

MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL 的 SQL “结构化查询语言”，是用于访问数据库的最常用标准化语言。

MySQL 软件采用了 GPL（GNU 通用公共许可证），由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本，而选择 MySQL 作为其网站数据库。

1.2 MySQL的优势

MySQL的优势有哪些，我们来瞧瞧。

・MySQL开源的，无需支付额外费用的；

・MySQL使用标准的SQL数据语言形式；

・MySQL可以运行多个系统上，并支持多个语言(如：C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 等)；

・MySQL可以定制，采用 GPL 协议，可修改源码来开发自己的 MySQL 系统。 

所以，我们要搞好MySQL数据库，是不是非常有必要。

今天，

我们就先从 order by和group by来说起。

2、order by 原理

2.1 创建表

为了更好，更容易理解相关知识点，咱先举个例子。

也是小鱼的一贯作风， 举例说话 !

先创建一张测试表，

/* auth:carl_奕然  */
 /* 使用ClassDJ这个database */
 use ClassDJ;  
 /* 如果表t1存在则删除表t1 */
 drop table if exists t1;  
 /* 创建表t1 */
 CREATE TABLE 't1'(      
 'id' int(11) NOT NULL AUTO_INCREMENT,
 'a' int(20) DEFAULT NULL ,
 'b' int(20) DEFAULT NULL ,
 'c' int(20) DEFAULT NULL ,
 'd' datetime NOT NULL  DEFAULT CURRENT_TIMESTAMP ,
 PRIMARY KEY (`id`),
 KEY `idx_a_b` (`a`,`b`),
 KEY `idx_c` (`c`)
 )ENGINE=InnoDB CHARSET=utf8mb4 ;
 /* 如果存在存储过程insert_t1，则删除 */
 drop procedure if exists insert_t1;
 delimiter ;;
 /* 创建存储过程insert_t1 */
 create procedure insert_t1()
 begin 
   /* 声明变量i */
   declare i int;  
   /* 设置i的初始值为1 */
   set i=1; 
   /* 对满足i<=10000的值进行while循环 */
   while(i<=10000)do
   /* 写入表d1中a、b两个字段，值都为i当前的值 */
     insert into t1(a,b,c) values(i,i,i); 
     /* 将i加1 */
     set i=i+1;    
   end while;
 end ;;
 delimiter ;
  /* 运行存储过程insert_t1 */
 call insert_t1();       
 /* 将id大于9000的行的a字段更新为1000 */
 update t1 set a=1000 where id >9000;

创建完表，我们就来研究一下MySQL的排序原理 

2.2 MySQL的排序方式

按照排序原理分，MySQL 排序方式分两种：

    ①通过有序索引直接返回有序数据

    ②通过Filesort进行的排序 

那么，问题来了，

如何确定某条排序的SQL所使用的排序方式？

答：使用explain来查看该排序SQL的执行计划，重点关注****字段。

★如果Extra字段里显示是 Using index，则表示是通过有序索引直接返回有序数据 

例如；

explain select id,c from t1 order by c；

★如果Extra字段里显示是 Using filesort，则表示该 SQL 是通过 Filesort 进行的排序 

例如：

explain select id,d from t1 order by d;

是不是，这就一目了然 了。

Nice

2.3 Filesort 在哪里完成排序

MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，

内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。

・如果 “排序的数据大小” < sort_buffer_size: 内存排序

・如果 “排序的数据大小” > sort_buffer_size: 磁盘排序

同样，问了又来了， 

如何确定使用 Filesort 排序的 SQL 是在内存还是在磁盘中进行的排序操作？ 

答：使用 trace 进行分析重点关注 number_of_tmp_files，

★如果等于 0，则表示排序过程没使用临时文件，在内存中就能完成排序；

★如果大于0，则表示排序过程中使用了临时文件。

举例

一、未使用临时文件排序

上图中，因为 number_of_tmp_files 等于 0，表示未使用临时文件进行排序，所以是内存排序。

参数解析：

・rows：预计扫描的行数

・examined_rows：参与排序的行

・number_of_tmp_files：使用临时文件的个数

・sort_buffer_size：sort_buffer 的大小

・sort_mode：排序模式

二、使用临时文件排序

上图中，因为 number_of_tmp_files 等于 7，所以表示使用的是磁盘排序。

 number_of_tmp_files 等于 7表示：

该SQL 将需要排序的数据分为 7 份，然后每份单独排序，再存放在 7 个临时文件中，最后把 7 个临时文件合并成一个大的有序文件。

2.4 Filesort 下的排序模式

一、排序模式

Filesort 下的排序模式有三种： 

    ・< sort_key, rowid >双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；

    ・< sort_key, additional_fields >单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

    ・< sort_key, packed_additional_fields >打包数据排序模式：与单路排序相似，区别是将 char 和 varchar 字段存到 sort buffer 中时，更加紧缩。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和需要查询的字段总大小来判断使用哪种排序模式 

二、判断排序模式

通过 max_length_for_sort_data 的大小和需要查询的字段总大小来判断使用哪种排序模式。

・如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 < **sort_key, additional_fields >**排序模式；

・如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 <sort_key, rowid> 排序模式。

三、实例举例

① 

set session optimizer_trace="enabled=on",end_markers_in_json=on;
 SET max_length_for_sort_data = 20;
 /* 查询表d1的id、a、d三个字段的值，按照字段d进行排序  */
 select a,d from t1 order by d; 
 SELECT * FROM information_schema.OPTIMIZER_TRACE\G

 OPTIMIZER_TRACE 结果中排序信息，如下图：

可以看到，使用的是**<sort_key，additional_fields>**排序模式。

②

我们要是变成 <sort_key, rowid>，怎么整呢？

思路：因为 a、d 两个字段的总长度为 12，可以尝试把 max_length_for_sort_data 改为小于 12 的值，看排序模式是否有改变。

操作：

set session optimizer_trace="enabled=on",end_markers_in_json=on;
SET max_length_for_sort_data = 4;
/* 查询表d1的id、a、d三个字段的值，按照字段d进行排序  */
select a,d from t1 order by d; 
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

OPTIMIZER_TRACE 结果中排序信息，如下图：

看看，是不是变成 <sort_key ，rowid>排序模式了。

非常Nice。

嗯，今天的就到这里，

关于如何优化orderby 及groupby ,我会在下一篇中着重介绍：

《深聊MySQL之：让orderby、groupby查询速度飞起来(下)》