Mysql 索引失效

mysql index explain

qoo100116 2020-01-03 17:18:12 ‧ 2978 瀏覽

環境：Mysql 5.7
A 表總行數：約11200000
機器規格：1 vCPU , 2G memory

 CREATE TABLE A (
 id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 site_id int(11) unsigned NOT NULL,
 `member_id` int(11) unsigned NOT NULL,
 `member_type` tinyint(3) unsigned NOT NULL,
 ......
 create_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`,`create_at`),
 KEY `idx_site_id` (`site_id`),
 KEY `idx_member_id` (`member_id`),
 KEY `idx_create_at_site_id` (`create_at`,`site_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 PARTITION BY RANGE (UNIX_TIMESTAMP(create_at))
 (PARTITION p20190923 VALUES LESS THAN (1569283200) ENGINE = InnoDB,
  PARTITION p20190924 VALUES LESS THAN (1569369600) ENGINE = InnoDB,
  .....
  PARTITION p20191231 VALUES LESS THAN (1577836800) ENGINE = InnoDB,
  PARTITION others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

--A 表索引的基數(Cardinality) (我知道建site_id這個當索引很差因為只有3種不同的值,但這是因為測試資料都只塞同一個...)
mysql> select count(distinct site_id),count(distinct create_at) from A;
+-------------------------+---------------------------+
| count(distinct site_id) | count(distinct create_at) |
+-------------------------+---------------------------+
| 3 | 775931 |
+-------------------------+---------------------------+

mysql> select site_id,count(site_id) from A group by site_id;
+---------+----------------+
| site_id | count(site_id) |
+---------+----------------+
| 1 | 536558 |
| 3 | 11346874 |
| 4 | 298 |
+---------+----------------+

--A表 12月份總資料筆數
mysql> select count() from A where create_at between '2019-12-01 00:00:00' and '2019-12-31 23:59:59';
+----------+
| count() |
+----------+
| 8545861 |
+----------+

###語法1：
select member_type from A where create_at between '2019-12-01 00:00:00' and '2019-12-31 23:59:59' and site_id='3';
語法1

###語法2：(有加force index (idx_create_at))
select member_type from A force index (idx_create_at) where create_at between '2019-12-01 00:00:00' and '2019-12-31 23:59:59' and site_id='3';
語法2

###問題：
1.在查詢12-01 ~ 12-31月份資料,查詢的explain卻是使用 idx_site_id ,使用idx_create_at這個索引效能正常來說應該會比較好吧,那為什麼Mysql會判斷走idx_site_id索引效能比較快??

2.有查到語法Select的範圍超過全表資料的30%就會選擇Full table scan(全表掃),
所以在查12月份資料 8百多萬占全表約76%,合理判斷它不會走idx_create_at索引,那又為什麼會選擇基數(Cardinality)更低的idx_site_id,而不是直接全表掃??

3.有測試就算只撈12月份其中幾小時不到10萬筆資料,一樣不使用idx_create_at,走idx_site_id,是時間欄位的索引效能太差嗎,不管查詢的筆數多少Mysql都認為idx_create_at這個索引太慢所以才不用的嗎?

--當前參數都使用預設,提供給各位專家 (知道buffer會影響查詢速度 ,但explain結果影響不大才對)
mysql> show global variables like '%buff%';
+-------------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------------+----------------+
| aurora_binlog_use_large_read_buffer | ON |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 734003200 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 734003200 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | none |
| innodb_log_buffer_size | 16777216 |
| innodb_shared_buffer_pool_uses_huge_pages | OFF |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------------+----------------+

再麻煩各位專家賜教,目前還正在查找原因
因小弟現在報表相關的語法都遇到此問題。

馬克 iT邦研究生 3 級 ‧ 2020-01-03 18:23:55 檢舉

這裡可以幫我試一下嗎 ? 如果將 create_at 的分區表移除掉 ~ 結果還會是一樣嗎 ?

石頭 iT邦高手 1 級 ‧ 2020-01-03 20:06:05 檢舉

請問你有更新那張表的統計值嗎

qoo100116 iT邦新手 5 級 ‧ 2020-01-04 22:34:08 檢舉

有手動更新統計值

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

ckp6250

iT邦好手 1 級 ‧ 2020-01-03 20:37:42

我知道建site_id這個當索引很差因為只有3種不同的值

既然只有3種，何必用 int(11) ？

select member_type from A where create_at between '2019-12-01 00:00:00' and '2019-12-31 23:59:59' and site_id='3';

換成
select member_type from A force index (idx_create_at) where site_id='3' and create_at between '2019-12-01 00:00:00' and '2019-12-31 23:59:59' ;

PRIMARY KEY (`id`,`create_at`),
KEY `idx_site_id` (`site_id`),
KEY `idx_member_id` (`member_id`),
KEY `idx_create_at_site_id` (`create_at`,`site_id`)

換成

PRIMARY KEY (`id`),
KEY `idx_site_id` (`site_id`,`create_at`),
KEY `idx_member_id` (`member_id`),

試試看。

結論，我覺得，您對索引檔的觀念，有些薄弱，請多閱讀相關文件。

回應 14
分享
檢舉

看更多先前的回應...收起先前的回應...

馬克 iT邦研究生 3 級 ‧ 2020-01-03 22:12:47 檢舉

可不可以先解釋一下，它上面幾個問題的原因呢 ? 大神 ?

ckp6250 iT邦好手 1 級 ‧ 2020-01-04 05:01:44 檢舉

一級大神在樓下，已經示範了。
原則上，【小就是好】，索引能越小越好。
tinyint 比 int(11) 小，
int 又比 timestamp 小，
欄位長度夠用就好，越小越好。
where 時，把小的排在前面比較好。

石頭 iT邦高手 1 級 ‧ 2020-01-04 09:24:41 檢舉

據我所知，欄位大小開合適是為了節省儲存資源
一般索引會建立在可識別性高的欄位上
至於您說的

where 時，把小的排在前面比較好。

就算換了條件順序跑出來的執行計畫還是一樣,結果一樣沒差別

ckp6250 iT邦好手 1 級 ‧ 2020-01-04 09:46:55 檢舉

　　我是說比較好，沒有說一定好。

　　site_id只有3種，create_at可能有上千萬種，

　　把它 site_id 優先排在前面，這是我的想法，如果結果沒差別，也不會更糟糕啊，但卻有可能更好，要看資料分布情形而定。

　sql的調校，本來就要不斷地測試，找出最好的方案。

　　幾千萬筆對 mysql 來說，不算什麼，不會有效率問題，除非索引建的不好或指令下得太差。

一級屠豬士 iT邦大師 1 級 ‧ 2020-01-04 10:30:28 檢舉

MySQL的index 很陽春,屬於基本款,沒有什麼黑科技,蠻無聊的.
MySQL的優化器,近年有一些加強.
但是MySQL有一些不錯的特色,可以在這類應用上搭配使用.我幾年前有寫過一些介紹,有興趣的可以去看看.

一級屠豬士 iT邦大師 1 級 ‧ 2020-01-04 10:47:17 檢舉

ckp6250大大說的: 要看資料分布情形而定。這句話很重要啊.
MySQL的 index, 若不指定成 hash, default 就是 B+Tree.
既然是動態平衡樹了, 當有 site_id , create_at 要並作一組當key, 可依照資料的特性, 來調配使用.

ckp6250 iT邦好手 1 級 ‧ 2020-01-04 11:01:15 檢舉

MySQL的index 很陽春,屬於基本款,沒有什麼黑科技,蠻無聊的.

　　我一直期待【有條件索引】早日出世，這麼重要的東西，不知為何 mysql 就是沒有。

一級屠豬士 iT邦大師 1 級 ‧ 2020-01-04 11:15:10 檢舉

哥哥你說的:有條件索引能舉例說一下嗎?

ckp6250 iT邦好手 1 級 ‧ 2020-01-04 11:48:47 檢舉

有條件索引是 vfp上的索引，太好用了，其語句如下：

INDEX ON eExpression TO IDXFileName | TAG TagName [BINARY]
   [COLLATE cCollateSequence] [OF CDXFileName] [FOR lExpression]
   [COMPACT] [ASCENDING | DESCENDING] [UNIQUE | CANDIDATE] [ADDITIVE]

　　有幾個重點：

　　１、建立索引時可以加上 for , 比如 for age>30 and city='高雄'，那麼，建出來的索引檔就只有符合條件的資料了，索引檔更小，速度更快。

　　２、建立索引時，可以指定 ASCENDING | DESCENDING，連由大而小或由小而大都可以事先指定。（您可以想像它的用途）

　　３、可以建立 COMPACT 壓縮型態型態的索引，如果某個 table 是屬於唯讀型，建立後就不再異動時，建立此類型索引會使得索引更小更快。

　　每套資料庫當然各有特色，其中 vfp 的有條件索引最吸引我，很好用，如果 mysql 有這個東西，那麼，透過 select force index , 指定事先過濾好的索引，肯定速度無人可比。

　　講效能，沒別的訣竅，【要找的筆數越少，速度越快】，有條件索引可以很容易達到這個目的。

qoo100116 iT邦新手 5 級 ‧ 2020-01-04 22:54:19 檢舉

TO ckp6250

PRIMARY KEY (`id`),
KEY `idx_site_id` (`site_id`,`create_at`),
KEY `idx_member_id` (`member_id`)

原本建的索引就跟您提的意見是一樣的,
但語法變慢後才測試改成文章中的那些形式,
因為現在的資料分布測試後(site_id,create_at)永遠都只會使用site_id的部分,所以才會改成(creat_at,site_id),想讓他走樣式比較多的create_at。

資料分布對所引影響很大我知道,一開始時(site_id,create_at)並沒有問題,在資料集中在site_id='3'並且都集中在同一段時間內才出現問題的,但對於為什麼測試時Mysql都判斷site_id比create_at還要快,我認為可能就像您說的可能是這個原因
tinyint 比 int(11) 小
int 又比 timestamp 小
所以才都只使用int的site_id

ckp6250 iT邦好手 1 級 ‧ 2020-01-05 05:22:07 檢舉

Less is More

馬克 iT邦研究生 3 級 ‧ 2020-01-06 18:24:54 檢舉

那為什麼 mysql 會選小的呢 ?

ckp6250 iT邦好手 1 級 ‧ 2020-01-06 20:06:43 檢舉

越小速度越快啊

馬克 iT邦研究生 3 級 ‧ 2020-01-07 10:53:27 檢舉

TO qoo100116
這裡我覺得應該不是因為 site_id 小所以才選他，如果是的話那以下的 query 應該也是會選 site_id。但這裡你那裡試應該會是使用 (create_at,site_id)。

select member_type from A where create_at between '2019-12-01 00:00:00' and '2019-12-31 23:59:59' and site_id>='3';

比較大的問題應該是『範圍』這個原因，當你使用以下的 query 時。它使用 explain 解析出來的 type 應該是『ref』。

select member_type from A where create_at between '2019-12-01 00:00:00' and '2019-12-31 23:59:59' and site_id='3';

而使用以下 query explain 出來的結果 type 應該是『 range 』

select member_type from A (idx_create_at_site_id) where create_at between '2019-12-01 00:00:00' and '2019-12-31 23:59:59' and site_id='3';

這裡只是我自已的推測，mysql 在判斷要使用什麼類型的索引『可能』會先看看是範圍還是單點，如果是單點就會優先使用單點，然後根據實驗結果，感覺只要是在單點與範圍的二選一情況下，看起來就不會根據統計值來決定走那一個。

以上你參考看看。

登入發表回應

一級屠豬士

iT邦大師 1 級 ‧ 2020-01-03 21:34:31


select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+

create table ithelp200103a (
  id int unsigned not null auto_increment primary key
, site_id tinyint unsigned not null
, create_at timestamp not null default current_timestamp
);

insert into ithelp200103a (site_id, create_at)
select ceil(rand() * 4)
     , timestampadd(second, floor(rand() * 7948800), timestamp '2019-10-01 00:00:00')
  from (select variable_name
          from performance_schema.global_variables) a
     , (select variable_name
          from performance_schema.global_variables) b;

create index ithelp200103a_site on ithelp200103a(site_id);
create index ithelp200103a_crets_site on ithelp200103a(create_at, site_id);

analyze table ithelp200103a;

explain
select site_id
     , count(site_id)
  from ithelp200103a
 group by site_id\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ithelp200103a
   partitions: NULL
         type: index
possible_keys: ithelp200103a_site,ithelp200103a_crets_site
          key: ithelp200103a_site
      key_len: 1
          ref: NULL
         rows: 298135
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

explain
select count(*)
  from ithelp200103a
 where create_at between timestamp '2019-12-01 00:00:00' 
   and timestamp '2019-12-31 23:59:59'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ithelp200103a
   partitions: NULL
         type: range
possible_keys: ithelp200103a_crets_site
          key: ithelp200103a_crets_site
      key_len: 4
          ref: NULL
         rows: 149067
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

explain
select count(*)
  from ithelp200103a
 where create_at between timestamp '2019-12-01 00:00:00' 
   and timestamp '2019-12-31 23:59:59'
   and site_id = 3\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ithelp200103a
   partitions: NULL
         type: ref
possible_keys: ithelp200103a_site,ithelp200103a_crets_site
          key: ithelp200103a_site
      key_len: 1
          ref: const
         rows: 149067
     filtered: 50.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

show warnings;
+-------+------+
|Level  | Code |
+-------+------+
| Note  | 1003 |
+-------+------+

--------
-- drop old indexes and create new one

drop index ithelp200103a_site on ithelp200103a; 
drop index ithelp200103a_crets_site on ithelp200103a;
create index ithelp200103a_site_crets on ithelp200103a(site_id, create_at);

analyze table ithelp200103a;

explain
select count(*)
  from ithelp200103a
 where create_at between timestamp '2019-12-01 00:00:00' 
   and timestamp '2019-12-31 23:59:59'
   and site_id = 3\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ithelp200103a
   partitions: NULL
         type: range
possible_keys: ithelp200103a_site_crets
          key: ithelp200103a_site_crets
      key_len: 5
          ref: NULL
         rows: 49526
     filtered: 100.00
        Extra: Using where; Using index


explain
select site_id
     , count(site_id)
  from ithelp200103a
 group by site_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ithelp200103a
   partitions: NULL
         type: index
possible_keys: ithelp200103a_site_crets
          key: ithelp200103a_site_crets
      key_len: 5
          ref: NULL
         rows: 305808
     filtered: 100.00
        Extra: Using index

explain
select count(*)
  from ithelp200103a
 where create_at between timestamp '2019-12-01 00:00:00' 
   and timestamp '2019-12-31 23:59:59'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ithelp200103a
   partitions: NULL
         type: range
possible_keys: ithelp200103a_site_crets
          key: ithelp200103a_site_crets
      key_len: 5
          ref: NULL
         rows: 33975
     filtered: 100.00
        Extra: Using where; Using index for skip scan

回應 3
分享
檢舉

qoo100116 iT邦新手 5 級 ‧ 2020-01-04 23:10:09 檢舉

再索引改成ithelp200103a(site_id, create_at)後

explain
select site_id
     , count(site_id)
  from ithelp200103a
 group by site_id\G

為什麼key_len是 5 ,這不就代表同時使用到(site_id, create_at)但在語法上沒有帶入時間條件也會使用到?

explain
select count(*)
  from ithelp200103a
 where create_at between timestamp '2019-12-01 00:00:00' 
   and timestamp '2019-12-31 23:59:59'\G

也是相同的問題,看key_len可以知道使用到site_id,還是(site_id, create_at),tinyint是1 timestamp是4
在後面兩個語法看來都有用到才顯示5,但語法卻沒有同時代site_id跟create_at

一級屠豬士 iT邦大師 1 級 ‧ 2020-01-05 00:29:48 檢舉

你最好能夠自己做一些測試,對照組,進而結合理論與實務,得到一些心得.後續再來做些討論,會更能體會.

馬克 iT邦研究生 3 級 ‧ 2020-01-07 11:23:57 檢舉

TO qoo100116

第一個 group 後 key_len 是 5 這個問題我不知道答案，而我這裡試的時後是 1，這個還要待調查。

而第二個問題，你的索引是 (site_id,create_at) 為什麼兩個會用到呢 ? 這點也還不太清楚。

但這裡我比較在意的是，為什麼 create_at 可以使用(site_id,create_at) 索引， explain possible key 是 null 但 key 卻有用。

這個問題到有查到答案，主要是 mysql 機制的問題，不卻定對你有沒有幫助，但也參考看看，所不定會找到上面兩個問題的答案。

It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_key

登入發表回應