# 優化order by語句
當進行數據的insert操作的時候,可以考慮采用以下幾種優化方案。
## 1. 環境準備
```SQL
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp(age,salary);
```
## 2.兩種排序方式
1). 第一種是通過對返回數據進行排序,也就是通常說的 filesort 排序,所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序。
~~~
explain select * from emp order by age,salary;
~~~

2). 第二種通過有序索引順序掃描直接返回有序數據,這種情況即為 using index,不需要額外排序,操作效率高。

3). 第三種情況排序順序跟索引順序不一樣,但好像mysql 8 好像不太一樣

mysql8.0 有降序descend索引,有backward index scan的掃描方式,兩者是有區別的,
對于descend索引,是在創建索引的時候就加上desc就可以了。
對于一般的升序索引,從根節點到葉子節點是升序的,所有索引節點從左到右也是升序的。但是想取值升序索引的后面的數據(就是值最新的,舉例主鍵id,默認升序,select * from a where id <1000 order by id desc),然后還要對這個索引進行降序排序,這樣就使用了backward index scan,這就是基于雙向鏈表的。
**總結:**
了解了MySQL的排序方式,優化目標就清晰了:盡量減少額外的排序,通過索引直接返回有序數據。where 條件和Order by 使用相同的索引,而且盡可能要命中索引,否則肯定需要額外的操作,這樣就會出現FileSort。
## 2. Filesort 的優化
通過創建合適的索引,能夠減少 Filesort 的出現,但是在某些情況下,條件限制不能讓Filesort消失,那就需要加快 Filesort的排序操作。對于Filesort , MySQL 有兩種排序算法:
**1) 兩次掃描算法:** MySQL4.1 之前,使用該方式排序。首先根據條件取出排序字段和行指針信息,然后在排序區 sort buffer 中排序,如果sort buffer不夠,則在臨時表 temporary table 中存儲排序結果。完成排序之后,再根據行指針回表讀取記錄,該操作可能會導致大量隨機I/O操作。
**2)一次掃描算法** 一次性取出滿足條件的所有字段,然后在排序區 sort buffer 中排序后直接輸出結果集。排序時內存開銷較大,但是排序效率比兩次掃描算法要高。
MySQL 通過比較系統變量 max_length_for_sort_data 的大小和Query語句取出的字段總大小, 來判定是否那種排序算法,如果max_length_for_sort_data 更大,那么使用第二種優化之后的算法;否則使用第一種。
可以適當提高 sort_buffer_size 和 max_length_for_sort_data 系統變量,來增大排序區的大小,提高排序的效率。
- 鳴謝
- 安裝和配置
- Mac 安裝MySql 8
- 授權用戶遠程登錄
- MySql 命令加入系統命令
- 啟動Mysql
- 索引
- 索引介紹與優勢
- 索引結構
- 索引操作語法
- 索引設計原則
- 存儲引擎
- MySql的體系解構
- 存儲引擎
- 各種存儲引擎特性
- 存儲引擎的選擇
- 優化SQL
- 查看SQL執行效率
- 定為低效率執行SQL
- explain分析執行計劃
- show profile分析SQL
- tract 分析優化器執行計劃
- 索引的使用
- 驗證索引可以提高查詢效率
- 索引使用
- 查看索引的使用情況
- SQL優化
- 大批量插入數據
- 優化insert語句
- 優化order by語句
- 優化group by的優化
- 優化嵌套索引
- 優化OR條件
- 優化分頁查詢
- 使用SQL提示