我剛剛開始使用索引優化我的模式（我猜很晚了）。我從來沒有處理過龐大的數據集，但我很快就會處理大約 1 到 5 百萬行。

這是我現在用於測試的架構：

CREATE TABLE `object`
(
 `otype` VARCHAR(255),
 `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
 `domain` VARCHAR(255),
 `created_at` INT,
 `updated_at` INT,
 `deleted_at` INT,
 `category` VARCHAR(31), /* genre, type, category */
 `status` VARCHAR(15)
)ENGINE=InnoDB;
ALTER TABLE `object` ADD UNIQUE KEY `object` ( `otype`, `id` );
CREATE INDEX `id` ON `object` ( `otype`, `id` );
CREATE INDEX `object_id` ON `object` ( `id` );
CREATE INDEX `object_domain` ON `object` ( `domain` );
CREATE INDEX `object_category` ON `object` ( `category` );
CREATE INDEX `object_status` ON `object` ( `status` );

CREATE TABLE `offer`
(
id BIGINT UNSIGNED,
`category` VARCHAR(31), /* genre, type, category */
amount DECIMAL(12,3)
)ENGINE=InnoDB;
ALTER TABLE `offer` ADD FOREIGN KEY ( `id` ) REFERENCES object( `id` ) ON UPDATE CASCADE ON DELETE CASCADE;
CREATE INDEX `offer_id` ON `offer` ( `id` );
CREATE INDEX `offer_category` ON `offer` ( `category` );

這是我要優化的查詢：我報告了兩次，因為我正在試驗是否使用索引過濾第一個表比過濾兩個表更快，我想我是對的：不是嗎？

mysql> select SQL_NO_CACHE count(*) from object, offer where object.id = offer.id and object.category = "bid" and status = "pending" order by created_at;
+----------+
| count(*) |
+----------+
|   499510 |
+----------+
1 row in set (2.06 sec)

mysql> explain extended select SQL_NO_CACHE count(*) from object, offer where object.id = offer.id and object.category = "bid" and status = "pending" order by created_at;
+----+-------------+--------+------+-------------------------------------------------+-----------------+---------+------------------+--------+----------+------------------------------------+
| id | select_type | table  | type | possible_keys                                   | key             | key_len | ref              | rows   | filtered | Extra                              |
+----+-------------+--------+------+-------------------------------------------------+-----------------+---------+------------------+--------+----------+------------------------------------+
|  1 | SIMPLE      | object | ref  | PRIMARY,object_id,object_category,object_status | object_category | 96      | const            | 498729 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | offer  | ref  | offer_id                                        | offer_id        | 9       | testme.object.id |      1 |   100.00 | Using index                        |
+----+-------------+--------+------+-------------------------------------------------+-----------------+---------+------------------+--------+----------+------------------------------------+
2 rows in set, 1 warning (0.01 sec)

mysql> select SQL_NO_CACHE count(*) from object, offer where object.id = offer.id and offer.category = "bid" and status = "pending" order by created_at;
+----------+
| count(*) |
+----------+
|   499510 |
+----------+
1 row in set (3.78 sec)

mysql> explain extended select SQL_NO_CACHE count(*) from object, offer where object.id = offer.id and offer.category = "bid" and status = "pending" order by created_at;
+----+-------------+--------+--------+---------------------------------+----------------+---------+-----------------+--------+----------+------------------------------------+
| id | select_type | table  | type   | possible_keys                   | key            | key_len | ref             | rows   | filtered | Extra                              |
+----+-------------+--------+--------+---------------------------------+----------------+---------+-----------------+--------+----------+------------------------------------+
|  1 | SIMPLE      | offer  | ref    | offer_id,offer_category         | offer_category | 96      | const           | 498792 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | object | eq_ref | PRIMARY,object_id,object_status | PRIMARY        | 8       | testme.offer.id |      1 |   100.00 | Using where                        |
+----+-------------+--------+--------+---------------------------------+----------------+---------+-----------------+--------+----------+------------------------------------+
2 rows in set, 1 warning (0.00 sec)

我研究了不同的文章和mysql索引頁面，其中指出：

它們僅用於使用 = 或 <=> 運算符的相等比較（但速度非常快）

$$ … $$優化器不能使用雜湊索引來加速 ORDER BY 操作

1. 由於我可能會遷移到 mysql 集群，從 InnoDB 切換到 NDB 引擎並在“類別”和“狀態”列上使用 HASH 索引是否有意義？
2. 我閱讀了 btrees 如何儲存數據。如果我的大多數查詢都涉及“WHERE category = x AND status = y”，我是否應該添加 3 個不同的索引：一個在類別上，一個在狀態上，一個在兩者的組合上？
3. “顯示警告”沒有顯示有關 mysql 試圖警告我的任何有用資訊：我的查詢有什麼問題？

mysql> 顯示警告 \G *************************** 1. 行******************** ******* 級別：註釋程式碼：1003 消息：/* select#1 */ select sql_no_cache count(0) AS count(*)from testme. object加入testme。offer其中 (( testme. object. id= testme. offer. id) 和 ( testme. object. category= ‘bid’) 和 ( testme. object. status= ‘pending’)) 集合中的 1 行（0.00 秒）

4 可以：

CREATE INDEX `object_category_status` ON `object` ( `category`, `status` );
CREATE INDEX `object_category` ON `object` ( `category` );
CREATE INDEX `object_status` ON `object` ( `status` );

為相同的結果減少以下內容？

CREATE INDEX `object_category_status` ON `object` ( `category`, `status` );
CREATE INDEX `object_status` ON `object` ( `status` );

謝謝你的時間！

我將回答三個問題

問題 #1

由於我可能會遷移到 mysql 集群，從 InnoDB 切換到 NDB 引擎並在“類別”和“狀態”列上使用 HASH 索引是否有意義？

對問題 #1 的回答

雜湊索引用於一對一的查找。雜湊索引僅適用於 MEMORY 儲存引擎（請參閱我May 17, 2011的文章為什麼 MySQL 在 MyISAM 或 InnoDB 上沒有雜湊索引？）

問題2

我閱讀了 btrees 如何儲存數據。如果我的大多數查詢都涉及“WHERE category = x AND status = y”，我是否應該添加 3 個不同的索引：一個在類別上，一個在狀態上，一個在兩者的組合上？

回答問題 #2

您不想要單列索引。如果沒有復合索引，MySQL 仍然可以使用它們。通過合併來自兩個獨立索引的查找，它將使 MySQL 更加努力地生成結果，

使用複合索引（類別和狀態的索引）會更好。

在您的特定情況下

• (status,category)如果您在狀態中訂購類別，則該索引可能是。

• (category,status)如果您在某個類別下訂購狀態值（如果狀態值的數量很高），則索引可能是

• 由於您在組合中執行 ORDER BY (status,category)，您的化合物將進一步受益於這些組合索引中的一個或兩個

  • (status,category,created_at)
  • (category,status,created_at)

• 請參閱我關於使用複合索引的文章

  • Sep 18, 2012: MySQL 如何在查詢中使用多個索引？（INDEX_MERGE簡要討論）
  • Apr 11, 2014:為什麼 MySQL 不使用基數較高的索引？

問題 #3

“顯示警告”沒有顯示有關 mysql 試圖警告我的任何有用資訊：我的查詢有什麼問題？

對問題 #3 的回答

沒有看到實際的警告資訊，我不能告訴你。請注意：您在同一個查詢上執行了兩次 EXPLAIN，得到了兩個略有不同的結果。這是由於鍵的選擇。InnoDB 傾向於通過在索引的 BTREE 節點內傳遞索引頁來進行猜測，並根據基數進行選擇。

在您的情況下，您應該只執行一次

OPTIMIZE TABLE object;

這將對錶進行碎片整理並生成一組乾淨的索引統計資訊。

您可以通過將其編寫為 INNER JOIN 來稍微改進查詢

select SQL_NO_CACHE count(*) from object
inner join offer on object.id = offer.id
where object.category = "bid"
and status = "pending"
order by created_at;

您還應該考慮offer沒有 PRIMARY KEY 的事實。

您應該執行此查詢

SELECT COUNT(1),id,category from offer
GROUP BY id,category HAVING COUNT(1) > 1;

如果您沒有得到任何結果，那麼這可能是您的 PRIMARY KEY。因此，報價表應該是

CREATE TABLE `offer`
(
   id BIGINT UNSIGNED,
   `category` VARCHAR(31), /* genre, type, category */
   amount DECIMAL(12,3),
   PRIMARY KEY (id,category)
) ENGINE=InnoDB;

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