Mysql

InnoDB 中是否有像 MyISAM 這樣的隱式索引？

  • March 27, 2013

如果您有一個(id, a, b, c, d)帶有主鍵(id)和另一個鍵的表(a, b, c)，在 MyISAM 中這意味著您還具有以下隱式鍵：

  • (a)
  • (a, b)
  • (a, b, c, id)

這對 InnoDB 也有效嗎？

還是我應該明確定義必要的？

InnoDB 完全不是這種情況。原因如下：

對於 InnoDB 表，主鍵是 InnoDB 儲存在gen_clust_index 中，即 Clustered Index。每個二級索引都有一個返回到 gen_clust_index 的內部鍵。

鑑於 PRIMARY KEY 儲存的背景，現在進行區別：

MyISAM

給定 Primary Keyid和 index a,b,c，隱式鍵是

  • (a)
  • (a, b)
  • (a, b, c)

a,b,c無論索引是唯一的還是非唯一的，索引總是如此。索引不知道列 ID。有一個 rowid 將每個.MYI條目連結回 中的一行.MYD，但不直接了解id. id 在主鍵中非常明顯。

InnoDB

給定 Primary Keyid和 index a,b,c，隱式鍵是

  • (a, id)
  • (a, b, id)
  • (a, b, c, id)

原因id是已知的，因為每個二級索引條目都有一個返回gen_clust_index.

請在 gen_clust_index 上查看我過去的文章。

證明…

讓我們創建兩個範例表。一個使用 MyISAM，另一個使用 InnoDB

  • 相同的數據
  • 相同的列
  • 相同的索引

這是程式碼：

DROP DATABASE IF EXISTS pavel;
CREATE DATABASE pavel;
USE pavel
CREATE TABLE rolando_myisam
(
   id int not null auto_increment,
   a int not null,
   b int not null default 99,
   c int not null default 99,
   primary key (id),
   key abc_ndx (a,b,c)
) ENGINE=MyISAM;
CREATE TABLE rolando_innodb LIKE rolando_myisam;
ALTER TABLE rolando_innodb ENGINE=InnoDB;
INSERT INTO rolando_myisam (a)
SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
INSERT INTO rolando_innodb (a)
SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;

在這裡載入：

mysql> DROP DATABASE IF EXISTS pavel;
Query OK, 2 rows affected (0.23 sec)

mysql> CREATE DATABASE pavel;
Query OK, 1 row affected (0.00 sec)

mysql> USE pavel
Database changed
mysql> CREATE TABLE rolando_myisam
   -> (
   ->     id int not null auto_increment,
   ->     a int not null,
   ->     b int not null default 99,
   ->     c int not null default 99,
   ->     primary key (id),
   ->     key abc_ndx (a,b,c)
   -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE rolando_innodb LIKE rolando_myisam;
Query OK, 0 rows affected (0.13 sec)

mysql> ALTER TABLE rolando_innodb ENGINE=InnoDB;
Query OK, 0 rows affected (0.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_myisam (a)
   -> SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
   -> SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
   -> SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
   -> SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
   -> SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_innodb (a)
   -> SELECT  1 n UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION
   -> SELECT  5   UNION SELECT  6 UNION SELECT  7 UNION SELECT  8 UNION
   -> SELECT  9   UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION
   -> SELECT 13   UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
   -> SELECT 17   UNION SELECT 18 UNION SELECT 19 UNION SELECT 20;
Query OK, 20 rows affected (0.05 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>

這裡有兩個查詢來顯示id列的檢索：

SELECT id FROM rolando_myisam;
SELECT id FROM rolando_innodb;

讓我們解釋一下：

mysql> EXPLAIN SELECT id FROM rolando_myisam;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_myisam | index | NULL          | PRIMARY | 4       | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | index | NULL          | abc_ndx | 12      | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

請注意，查詢優化器PRIMARY KEY為 MyISAM 表選擇了 ，但abc_ndx為 InnoDB 表選擇了鍵。這說明了什麼？MyISAM 表的abc_ndx索引看不到該id列。因此，id對於 MyISAM 來說並不是隱含的。

讓我們嘗試不同類型的查詢：

EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;

查看 EXPLAIN 計劃：

mysql> EXPLAIN SELECT id FROM rolando_myisam order by a,b,c;
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | rolando_myisam | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb order by a,b,c;
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | index | NULL          | abc_ndx | 12      | NULL |   20 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

同樣，abc_ndx不能用於獲取，id因為它對於 MyISAM 索引不是隱含的。對於 InnoDB，它絕對是隱含的。

讓我們嘗試另一種不同類型的查詢：

EXPLAIN SELECT id FROM rolando_myisam where a = 12;
EXPLAIN SELECT id FROM rolando_innodb where a = 12;

查看 EXPLAIN 計劃：

mysql> EXPLAIN SELECT id FROM rolando_myisam where a = 12;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table          | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | rolando_myisam | ref  | abc_ndx       | abc_ndx | 4       | const |    1 | NULL  |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT id FROM rolando_innodb where a = 12;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table          | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | rolando_innodb | ref  | abc_ndx       | abc_ndx | 4       | const |    1 | Using index |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql>

再一次，abc_ndx索引不能用於獲取，id因為它不是 MyISAM 索引的隱含。它對於 InnoDB 是隱含的。請注意，InnoDB 查找僅使用索引。這就是為什麼ExtraEXPLAIN 計劃中Using index的 InnoDB 有，而 MyISAM 沒有。MyISAM 查找必須深入.MYD到id.

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

相關問答

Mysql

為什麼 InnoDB 上的簡單 SELECT 比 MyISAM 慢 100 倍？

  • December 31, 2021
檢視問答
Mysql

OPTIMIZE TABLE 會對我的數據產生任何影響嗎？

  • December 31, 2021
檢視問答
Mysql

InnoDB 表上的空間索引

  • June 22, 2018
檢視問答
Mysql

為什麼 MySQL 在 MyISAM 或 InnoDB 上沒有雜湊索引？

  • August 31, 2017
檢視問答
Mysql

最好的 MyISAM 和 InnoDB

  • October 14, 2016
檢視問答
Mysql

具有許多索引的 InnoDB 與 MyISAM

  • July 6, 2012
檢視問答