文章目錄

• 生猛干貨
• DB Version
• Table
• 數(shù)據(jù)量
• 案例一 ：explain select * from employees where name = 'LiLei' and position = 'dev' order by age
• 案例二： explain select * from employees where name = 'LiLei' order by position
• 案例三：explain select * from employees where name = 'LiLei' order by age , position
• 案例四：explain select * from employees where name = 'LiLei' order by position , age
• 案例五：explain select * from employees where name = 'LiLei' and age = 18 order by position , age ;
• 案例六：explain select * from employees where name = 'LiLei' order by age asc , position desc ;
• 案例七：explain select * from employees where name in ('HanMeiMei' , 'LiLei') order by age , position ;
• 案例八： explain select * from employees where name > 'HanMeiMei' order by name ;
• group by 優(yōu)化
• 小結(jié)
• 搞定MySQL

---

生猛干貨

帶你搞定MySQL實(shí)戰(zhàn)，輕松對(duì)應(yīng)海量業(yè)務(wù)處理及高并發(fā)需求，從容應(yīng)對(duì)大場(chǎng)面試

---

DB Version

mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.28 | +-----------+ 1 row in setmysql>

---

Table

CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時(shí)間',PRIMARY KEY (`id`),KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';

兩個(gè)索引

主鍵索引

二級(jí)索引 KEY idx_name_age_position (name,age,position) USING BTREE

重點(diǎn)就是這個(gè)二級(jí)索引 ，記號(hào)了哈。

---

數(shù)據(jù)量

mysql> select count(1) from employees ; +----------+ | count(1) | +----------+ | 100002 | +----------+ 1 row in setmysql>

---

案例一 ：explain select * from employees where name = ‘LiLei’ and position = ‘dev’ order by age

explain select * from employees where name = 'LiLei' and position = 'dev' order by age ;

先想一下這個(gè)order by 會(huì)不會(huì)走索引 ？

會(huì)走索引

原因呢 ？

腦海中要有這個(gè)聯(lián)合索引在MySQL底層的B+Tree的數(shù)據(jù)結(jié)構(gòu) ， 索引 排好序的數(shù)據(jù)結(jié)構(gòu)。

name = ‘LiLei’ and position = ‘dev’ order by age

name 為 LiLei , name 確定的情況下， age 肯定是有序的 ，age 有序不能保證position 有序

所以 這個(gè)order by age 是可以走索引的

繼續(xù)分析下這個(gè)explain

mysql> explain select * from employees where name = 'LiLei' and position = 'dev' order by age ; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 10 | Using index condition | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in set

order by 走的索引 是不會(huì)體現(xiàn)在key_len上的, 這個(gè)74 = 3 * 24 + 2 ， 是計(jì)算的name 。 最左匹配原則 ，中間字段不能斷，因此查詢用到了name索引。

但是 Extra直接里面可以看出來(lái) Using index condition ，說(shuō)明age索引列用在了排序過(guò)程中 。 如果沒(méi)有走索引的話，那就是 Using FileSort 了

接下來(lái)繼續(xù)看幾個(gè)例子，加深理解，重點(diǎn)是腦海中的 索引B+Tree結(jié)構(gòu)

---

案例二： explain select * from employees where name = ‘LiLei’ order by position

mysql> explain select * from employees where name = 'LiLei' order by position ;

想一想，這個(gè)order by 會(huì)走索引嗎？

我們來(lái)看下索引 KEY idx_name_age_position (name,age,position) USING BTREE

再來(lái)看下查詢SQL

where name = 'LiLei' order by position ;

name = LiLei , name 值能確定下來(lái)， 符合最左匹配原則 所以查詢會(huì)走索引 ， 用了聯(lián)合索引中的name字段， key len = 74 . 所以 Using index condition

order by position , 在索引中 中間缺失了age , 用position ，跳過(guò)了age , 那索引樹(shù)能是有序的嗎？ 肯定不是。。。所以 position肯定不是排好序的 ， 無(wú)法走索引排序，因此 Extra信息 有 Using filesort

來(lái)看下執(zhí)行計(jì)劃

mysql> explain select * from employees where name = 'LiLei' order by position ; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100 | Using index condition; Using filesort | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ 1 row in setmysql>

正如分析~

有感覺(jué)了嗎？ 再來(lái)看一個(gè)

---

案例三：explain select * from employees where name = ‘LiLei’ order by age , position

這個(gè)SQL和案例二的很相似 ， 僅僅在排序的時(shí)候在前面多了一個(gè)age字段參與排序 ， 那分析分析 order by 會(huì)走索引嗎

mysql> explain select * from employees where name = 'LiLei' order by age , position ;

時(shí)刻不要那個(gè)索引樹(shù) ，來(lái)分析一下

name = LiLei , name 固定，結(jié)合 建立的索引， 最左原則，所以查詢肯定會(huì)走聯(lián)合索引中的部分索引 name .

在name都是LiLei 的情況下 ， order by age , position 結(jié)合索引樹(shù) ，age和position用于排序 也是有序的，應(yīng)該不會(huì)走using filesort

我們來(lái)看下執(zhí)行計(jì)劃

mysql> explain select * from employees where name = 'LiLei' order by age , position ; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100 | Using index condition | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in setmysql>

---

案例四：explain select * from employees where name = ‘LiLei’ order by position , age

再分析一個(gè)，和案例上也很像。 把 order by的排序順序 調(diào)整一下，我們來(lái)分析一下 order by會(huì)不會(huì)走索引

explain select * from employees where name = 'LiLei' order by position , age ;

mysql> explain select * from employees where name = 'LiLei' order by position , age ; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100 | Using index condition; Using filesort | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ 1 row in setmysql>

咦 ， 執(zhí)行計(jì)劃中有 using filesort

為什么呢？

看看我們二級(jí)索引的建立的字段順序 ， 創(chuàng)建順序?yàn)閚ame,age,position，但是排序的時(shí)候age和position顛倒位置了， 那排好序的特性肯定就無(wú)法滿足了，那你讓MySQL怎么走索引？

---

案例五：explain select * from employees where name = ‘LiLei’ and age = 18 order by position , age ;

這個(gè)order by 會(huì)走索引嗎？

mysql> explain select * from employees where name = 'LiLei' and age = 18 order by position , age ; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 78 | const,const | 1 | 100 | Using index condition | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+ 1 row in setmysql>

走了dx_name_age_position 索引中的 name 和 age , order by 其實(shí)也走了索引，你看extra中并沒(méi)有 using filesort ,因?yàn)閍ge為常量，在排序中被MySQL優(yōu)化了，所以索引未顛倒，不會(huì)出現(xiàn)Using filesort

---

案例六：explain select * from employees where name = ‘LiLei’ order by age asc , position desc ;

mysql> explain select * from employees where name = 'LiLei' order by age asc , position desc ; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100 | Using index condition; Using filesort | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+ 1 row in set

我們可以看到雖然排序的字段列與建立索引的順序一樣， order by默認(rèn)升序排列，而SQL中的 position desc變成了降序排列，導(dǎo)致與索引的排序方式不同，從而產(chǎn)生Using filesort。

Note: Mysql8以上版本有降序索引可以支持該種查詢方式。

---

案例七：explain select * from employees where name in (‘HanMeiMei’ , ‘LiLei’) order by age , position ;

mysql> explain select * from employees where name in ('HanMeiMei' , 'LiLei') order by age , position ; +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+---------------------------------------+ | 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 74 | NULL | 2 | 100 | Using index condition; Using filesort | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+---------------------------------------+ 1 row in setmysql>

對(duì)order by 來(lái)講 ，多個(gè)相等的條件也是 范圍查詢。 既然是范圍查詢， 可能對(duì)于每個(gè)值在索引中是有序的，但多個(gè)合并在一起，就不是有序的了，所以 using filesort .

---

案例八： explain select * from employees where name > ‘HanMeiMei’ order by name ;

mysql> explain select * from employees where name > 'HanMeiMei' order by name ; +----+-------------+-----------+------------+------+-----------------------+------+---------+------+-------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+------+---------+------+-------+----------+-----------------------------+ | 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 96845 | 50 | Using where; Using filesort | +----+-------------+-----------+------------+------+-----------------------+------+---------+------+-------+----------+-----------------------------+ 1 row in setmysql>

MySQL自己內(nèi)部有一套優(yōu)化機(jī)制，且數(shù)據(jù)量不同、版本不一樣，結(jié)果也可能有差異

一般情況下， 聯(lián)合索引第一個(gè)字段用范圍不一定會(huì)走索引 ， 可以采用 覆蓋索引進(jìn)行優(yōu)化，避免回表帶來(lái)的性能開(kāi)銷 。

mysql> explain select namefrom employees where name > 'a' order by name ; +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 74 | NULL | 48422 | 100 | Using where; Using index | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+-------+----------+--------------------------+ 1 row in setmysql>

---

group by 優(yōu)化

• group by與order by類似，其實(shí)質(zhì)是先排序后分組，遵照索引創(chuàng)建順序的最左前綴法則。

• 對(duì)于group by的優(yōu)化如果不需要排序的可以加上order by null禁止排序。

• where高于having，能寫(xiě)在where中的限定條件就不要去having限定了。

---

小結(jié)

• MySQL支持兩種方式的排序filesort和index，Using index是指MySQL掃描索引本身完成排序

• order by滿足兩種情況會(huì)使用Using index
  A: order by語(yǔ)句使用索引最左前列。
  B: 使用where子句與order by子句條件列組合滿足索引最左前列

• 盡量在索引列上完成排序，遵循索引建立（索引創(chuàng)建的順序）時(shí)的最左前綴法則

• 如果order by的條件不在索引列上，就會(huì)產(chǎn)生Using filesort

• 能用覆蓋索引盡量用覆蓋索引

---

搞定MySQL

總結(jié)

以上是生活随笔為你收集整理的MySQL - order by和 group by 优化初探的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。