MySQL - order by和 group by 优化初探
文章目錄
- 生猛干貨
- 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è)索引
重點(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 setorder 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)題。
- 上一篇: MySQL - 使用trace工具来窥
- 下一篇: MySQL - 分页查询优化的两个案例