1.網上一些文章的總結
- count(*) :它會獲取所有行的數據,不做任何處理,行數+1。
- count(1):它會獲取所有行的數據,每行固定值1,也是行數+1。
- count(id):id代表主鍵,它需要從所有行的數據中解析出id欄位,其中id肯定都不為NULL,行數+1。
- count(普通索引列):它需要從所有行的數據中解析出普通索引列,然後判斷是否為NULL,如果不是NULL,則行數+1。
- count(未加索引列):它會全表掃描獲取所有數據,解析中未加索引列,然後判斷是否為NULL,如果不是NULL,則行數+1。
由此,最後count的性能從高到低是:
count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)
以上結論錯誤至極,根本沒有得到驗證。
下面我將基於MySQL 5.7 + InnoDB引擎進行總結分析。
2.分析
下面是一張數據量為100萬的表,表中欄位比較短,整體數據量不大。
CREATE TABLE `hospital_statistics_data` (
`pk_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
`hospital_code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
`biz_type` tinyint NOT NULL,
`item_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`item_name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
`item_value` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
`is_deleted` tinyint DEFAULT NULL,
PRIMARY KEY (`pk_id`)
) DEFAULT CHARSET=utf8mb4;
下面我會通過不同的索引情況來看 count(*) 的執行計劃。
2.1 只有一個聚簇索引
EXPLAIN select COUNT(*) from hospital_statistics_data;
type: index
key: PRIMARY
key_len: 8
count(*) 會遍歷索引,並使用聚集索引。
2.2 存在非聚集索引(二級索引)
hospital_code向表中添加索引。
alter table hospital_statistics_data add index idx_hospital_code(hospital_code)
此時表中有2個索引 primary key,hospital_code。
EXPLAIN select COUNT(*) from hospital_statistics_data;
type: index
key: idx_hospital_code
key_len: 146
索引變為剛剛添加的 idx_hospital_code 。
2.3 有兩個非聚集索引(二級索引)
再添加一個二級索引。
alter table hospital_statistics_data add index idx_biz_type(biz_type);
此時表中有3個索引 primary key、hospital_code、biz_type。
EXPLAIN select COUNT(*) from hospital_statistics_data;
type: index
key: idx_biz_type
key_len: 1
現在索引變成是 biz_type,是不是很神奇。
2.4 基於以上三個索引,分別來看 count(1), count(id), count(index), count(no index)
這四個的執行計劃和 count(*) 有什麼區別?
count(1)
EXPLAIN select COUNT(1) from hospital_statistics_data;
type: index
key: idx_biz_type
key_len: 1
count(pk_id)
EXPLAIN select COUNT(pk_id) from hospital_statistics_data;
type: index
key: idx_biz_type
key_len: 1
count(index)
EXPLAIN select COUNT(biz_type) from hospital_statistics_data;
type: index
key: idx_biz_type
key_len: 1
count (no index)
EXPLAIN select COUNT(item_code) from hospital_statistics_data;
type: ALL
key: null
key_len: null
2.5 總結
- count(index) 將使用當前索引指定的索引。
- count(no index) 是沒有索引的全表掃描。
- count(1), count(*), count(id)也會選擇 idx_biz_type 索引。
三、知識點
MySQL分為服務層和引擎層。
所有的SQL在執行前都會經過服務層的優化。優化有很多種,可以簡單分為成本和規則優化。
執行計劃反映了SQL優化後服務層可能的執行過程。在大多數情況下,執行計劃是可信的(不絕對,以防有人說我只看執行計劃過於片面)。
索引類型分為聚集索引和非聚集索引(二級索引)。其中,數據掛在聚簇索引上,非聚簇索引只是記錄的主鍵id。
拋開數據內存不談,只談數據量是扯淡。什麼500萬是極限,什麼超過2個表需要優化join,什麼是null就不會去索引等等,都是錯誤的。
原因分析
原因很簡單。如上所述,服務層將根據成本進行優化。而且,一般情況下,非聚簇索引占用的內存要比聚簇索引小很多。
問題討論
3.1 如果你是 MySQL 開發者,你會在執行 count(*) 查詢時使用哪個索引?
我相信普通人使用非聚集索引。
3.2 如果有2個或多個非聚集索引如何選擇?
那麼一定要選擇占用內存最小的。同樣是非聚集索引,idx_hospital_code 長度為146位元組,而 idx_biz_type 長度僅為1。
3.3 那為什麼count(*)取了index之後還是很慢?
這裡要明確一點,索引只是提高效率的一種方式,並不能完全解決效率問題。count(*)有一個明顯的缺陷,就是需要計算總數,也就是遍歷所有符合條件的數據,相當於一個計數器。當數據量足夠大時,即使使用非聚集索引,也不能優化太多。
官方文檔:
InnoDB 以相同的方式處理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作。沒有性能差異。
簡單的說,InnoDB下的 count(*) 相當於 count(1)。
3.4 既然會自動取索引,那上面所謂的快速排序還覺得對嗎?
count(*) 的性能與數據量有很大關係。此外,二級索引的欄位長度越短越好。
另外,網上提到的索引故障大多是片面的,這裡只說一點。量變可以導致質變。索引失效取決於你劃定數據的範圍。
如果篩選的數據量占整體數據量的比例過高,就會放棄使用索引,否則,就會優先使用索引。但是這個規則並不完美,有時候可能和你預想的不一樣。也可以使用一些技巧強制使用索引,但這種方法很少用到。
例如:
通過上表hospital_statistics_data,做如下查詢:
select * from hospital_statistics_data where hospital_code is not null ;
這個SQL hospital_code 這個時候會用到索引嗎?
如果 hospital_code只有一小部分數據是null,那麼不會使用索引,否則會使用索引。
就像買橘子一樣。如果只買2斤,挑籃子裡好的就可以了。但是如果你要買一筐,相信老闆不會讓你一個一個挑,而是一次給你一整筐。當然,大家也不傻,誰都知道籃子裡肯定有幾個壞果子。但這樣對老闆來說效率最高,成本也最低。