SELECT COUNT(*) 會(huì)造成全表掃描？回去等通知吧
閱讀本文大概需要 6 分鐘。
來自：程序員大彬
-
前言 -
SQL 選用索引的執(zhí)行成本如何計(jì)算 -
實(shí)例說明 -
總結(jié)
前言
SELECT COUNT(*)會(huì)不會(huì)導(dǎo)致全表掃描引起慢查詢呢？
SELECT COUNT(*) FROM SomeTable
where_clause 的 COUNT(*)，MySQL 是有優(yōu)化的，優(yōu)化器會(huì)選擇成本最小的輔助索引查詢計(jì)數(shù)，其實(shí)反而性能最高，這種說法對不對呢
EXPLAIN 來查詢了一下執(zhí)行計(jì)劃
EXPLAIN SELECT COUNT(*) FROM SomeTable
COUNT(1)，還是 COUNT(*)，MySQL 都會(huì)用成本最小 的輔助索引查詢方式來計(jì)數(shù)，也就是使用 COUNT(*) 由于 MySQL 的優(yōu)化已經(jīng)保證了它的查詢性能是最好的！隨帶提一句，COUNT(*)是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，并且效率高，所以請直接使用COUNT(*)查詢表的行數(shù)！
-
SQL 選用索引的執(zhí)行成本如何計(jì)算 -
實(shí)例說明
SQL 選用索引的執(zhí)行成本如何計(jì)算
-
IO 成本: 即從磁盤把數(shù)據(jù)加載到內(nèi)存的成本，默認(rèn)情況下，讀取數(shù)據(jù)頁的 IO 成本是 1，MySQL 是以頁的形式讀取數(shù)據(jù)的，即當(dāng)用到某個(gè)數(shù)據(jù)時(shí)，并不會(huì)只讀取這個(gè)數(shù)據(jù)，而會(huì)把這個(gè)數(shù)據(jù)相鄰的數(shù)據(jù)也一起讀到內(nèi)存中，這就是有名的程序局部性原理，所以 MySQL 每次會(huì)讀取一整頁，一頁的成本就是 1。所以 IO 的成本主要和頁的大小有關(guān) -
CPU 成本：將數(shù)據(jù)讀入內(nèi)存后，還要檢測數(shù)據(jù)是否滿足條件和排序等 CPU 操作的成本，顯然它與行數(shù)有關(guān)，默認(rèn)情況下，檢測記錄的成本是 0.2。
實(shí)例說明
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`score` int(11) NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `name_score` (`name`(191),`score`),
KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
name_score 及 create_time。然后我們在此表中插入 10 w 行數(shù)據(jù)，只要寫一個(gè)存儲(chǔ)過程調(diào)用即可，如下:
CREATE PROCEDURE insert_person()
begin
declare c_id integer default 1;
while c_id<=100000 do
insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
set c_id=c_id+1;
end while;
end
EXPLAIN SELECT COUNT(*) FROM person
create_time 輔助索引，顯然 MySQL 認(rèn)為使用此索引進(jìn)行查詢成本最小，這也是符合我們的預(yù)期，使用輔助索引來查詢確實(shí)是性能最高的！
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
name_score 或者 create_time 索引才對，從 WHERE 的查詢條件來看確實(shí)都能命中索引，那是否是使用 SELECT * 造成的回表代價(jià)太大所致呢，我們改成覆蓋索引的形式試一下
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18'
-- 全表掃描執(zhí)行時(shí)間: 4.0 ms
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
-- 使用覆蓋索引執(zhí)行時(shí)間: 2.0 ms
SELECT create_time FROM person force index(create_time) WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
SHOW TABLE STATUS LIKE 'person'
-
行數(shù)是 100264，我們不是插入了 10 w 行的數(shù)據(jù)了嗎，怎么算出的數(shù)據(jù)反而多了，其實(shí)這里的計(jì)算是估算 ，也有可能這里的行數(shù)統(tǒng)計(jì)出來比 10 w 少了，估算方式有興趣大家去網(wǎng)上查找，這里不是本文重點(diǎn)，就不展開了。得知行數(shù)，那我們知道 CPU 成本是 100264 * 0.2 = 20052.8。 -
數(shù)據(jù)長度是 5783552，InnoDB 每個(gè)頁面的大小是 16 KB，可以算出頁面數(shù)量是 353。
20052.8 + 353 = 20406。
optimizer trace 功能來查看優(yōu)化器生成計(jì)劃的整個(gè)過程 ，它列出了選擇每個(gè)索引的執(zhí)行計(jì)劃成本以及最終的選擇結(jié)果，我們可以依賴這些信息來進(jìn)一步優(yōu)化我們的 SQL。
optimizer_trace 功能使用如下
SET optimizer_trace="enabled=on";
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
name_score，create_time 索引及全表掃描的成本。
name_score 索引執(zhí)行的的預(yù)估執(zhí)行成本:
{
"index": "name_score",
"ranges": [
"name84059 <= name"
],
"index_dives_for_eq_ranges": true,
"rows": 25372,
"cost": 30447
}
create_time 索引執(zhí)行的的預(yù)估執(zhí)行成本:
{
"index": "create_time",
"ranges": [
"0x5ec8c516 < create_time"
],
"index_dives_for_eq_ranges": true,
"rows": 50132,
"cost": 60159,
"cause": "cost"
}
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`person`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 100264,
"access_type": "scan",
"resulting_rows": 100264,
"cost": 20406,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 100264,
"cost_for_plan": 20406,
"chosen": true
}
]
}
optimizer trace 詳細(xì)列出了覆蓋索引，回表的成本統(tǒng)計(jì)情況，有興趣的可以去研究一下。
總結(jié)
EXPLAIN, optimizer trace 來優(yōu)化我們的查詢語句。
推薦閱讀：
Java21正式發(fā)布，史詩級增強(qiáng)！虛擬線程、分代 ZGC 正式來襲！！
互聯(lián)網(wǎng)初中高級大廠面試題(9個(gè)G) 內(nèi)容包含Java基礎(chǔ)、JavaWeb、MySQL性能優(yōu)化、JVM、鎖、百萬并發(fā)、消息隊(duì)列、高性能緩存、反射、Spring全家桶原理、微服務(wù)、Zookeeper......等技術(shù)棧！
?戳閱讀原文領(lǐng)取！ 朕已閱
