MySQL 5.7 - B-Tree 索引、性能和碎片

我有一個帶有時間戳列的大表，它包含大量數據（幾百萬行）。

每當在此表的時間戳列上執行按範圍過濾的查詢時，根據范圍的大小，它需要相當長的時間（2 秒以上）。這個列有一個為它創建的索引，這會讓我相信查詢返回應該更快。

我注意到通過銷毀索引並重新添加它，查詢的性能輕鬆提高了 40%。

Bellow 是一個簡單的表格結構範例：

create table foo (
 id int(11) not null auto_increment,
 fk_id int(11) not null,
 datecolumn timestamp,
 primary key (id, fk_id),
 key idx_timestamp (datecolumn)
 constraint fk_fk_id foreign key (fk_id) references bla (id)
) engine = InnoDB;

重新創建索引會使查詢執行得更快的任何原因以及如何將其作為維護的一部分？我知道 MySQL InnoDB 預設索引是 BTree，它可能會變得混亂，但它應該有一種優化或重新組織這些索引的方法，因此查詢性能還不錯。

注意：我跑了

optimize table foo;
flush tables;

這些似乎並沒有解決與索引相關的任何問題。

當您使用解釋時，它會顯示一些正在使用的索引，但主要是顯示類型範圍，並且在這種類型下，大多數行數被分析為只返回其中的一些。

fk_id是 where 子句的一部分，必須是，它也是複合鍵的一部分，這意味著返回結果是確定性的。

查詢基本上是這樣的：

select id, fk_id, datecolumn, bar.id, bar.name, bar.type
   from foo
   join bar on bar.id = foo.fk_id
  where foo.datecolumn between '2016-01-01 05:00:00' and '2017-01-01 04:59:59'
  and foo.fk_id in (1, 2, 4)

即使查詢是一個簡單的選擇 where datecolumn between range 我得到相同的結果。

在大量 random 之後，BTree 塊固有地從 100% 滿（實際上大約 15/16）到大約 69% 滿INSERTs。這可能佔您看到的 40%。但請注意，它不會隨著時間的推移而變慢。因此，我認為頻繁OPTIMIZE TABLE是浪費時間，特別是如果您需要始終可以訪問該表。

而且，您的 40% 速度將迅速下降。

我希望FLUSH TABLES實際上會受到傷害——因為它會從 RAM 中刪除記憶體的資訊。

為了討論查詢，請向我們展示查詢、它EXPLAIN SELECT ...的和可能SHOW CREATE TABLE bla的。

foo本質上是兩張表，一張是有序(id, fk_id, datecolumn)的，另一張是有序的(datecolumn, id, fk_id)。另外，我認為，(fk_id, id)（否datecolumn）的二級索引。

如果fk_id是WHERE子句的一部分，則它們都可能不是最佳的。

附加物

（來自OP的評論：）

select  id, fk_id, datecolumn, bar.id, bar.name, bar.type
   from  foo
   join  bar  ON bar.id = foo.fk_id
   where  foo.datecolumn between '2016-01-01 05:00:00'
                             AND '2017-01-01 04:59:59'
     and  foo.fk_id in (1, 2, 4)

最優指標是

foo:  INDEX(fk_id, datecolumn) -- 3 disjoint ranges
bar:  PRIMARY KEY(id) -- I assume it is already the PK; if not, then INDEX(id)

請注意，您目前沒有foo. 添加它，您的需求OPTIMIZE TABLE應該會減少。

至於為什麼優化似乎有幫助——可能是記憶體。你有多少記憶體？的價值是innodb_buffer_pool_size多少？

我更喜歡這種模式：

   where  foo.datecolumn between '2016-01-01 05:00:00'
                             AND '2016-01-01 05:00:00' + INTERVAL 1 YEAR

（沒有閏年、數據類型等的麻煩）

更多關於索引。

對我來說，桌子設計看起來很糟糕。

PK 是 (id, fk_id)，它允許每個“id”多行。真的想要嗎？

時間戳是一個完全動態的時間戳（DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），需要嗎？

‘datecolumn’ 這個名字表明它是一個’date’，那麼為什麼不使用’date’ 類型呢？

引用自：https://dba.stackexchange.com/questions/154097