將主鍵作為 InnoDB 表中復合二級索引的最後一列有什麼作用？

假設我有一對 N 的關係(person_id, pet_id)。我有一張表，pet_id主鍵在哪裡。

我知道 InnoDB 二級索引本質上是一個 B 樹，其中的值是該行的相應主鍵值。

現在，假設一個人可以擁有數千隻寵物，而我經常想要一個人的寵物按pet_id. 然後，二級索引中的記錄是按排序(person_id, pet_id)還是僅person_id使用pet_id’s 來person_id排序是很重要的。以後猜。

那麼，如果person_id是非唯一的，記錄是按物理排序(person_id, pet_id)還是只是pet_id？

謝謝

否。如果您的表具有 InnoDB 引擎和PRIMARY KEYis (pet_id)，那麼將二級索引定義為(person_id)or(person_id, pet_id)沒有任何區別。

索引pet_id也包括該列，因此(person_id, pet_id)在這兩種情況下都會對值進行排序。

像您這樣的查詢：

SELECT pet_id FROM yourtable 
WHERE person_id = 127 
ORDER BY pet_id ;

只需要訪問索引來獲取值，甚至更多，它不需要做任何排序，因為pet_id值已經在索引中排序。您可以通過查看執行計劃 ( EXPLAIN) 來驗證這一點：

---

首先，我們嘗試使用 MyISAM 表：

CREATE TABLE table pets 
( pet_id int not null auto_increment PRIMARY KEY, 
  person_id int not null, 
  INDEX person_ix (person_id)
) ENGINE = myisam ;

INSERT INTO pets (person_id) 
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets 
              WHERE person_id = 2  
              ORDER BY pet_id asc \G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: pets
        type: ref
possible_keys: person_ix
         key: person_ix
     key_len: 4
         ref: const
        rows: 3
       Extra: Using where; Using filesort
1 row in set (0.00 sec)

注意文件排序！

現在，具有復合索引的 MyISAM：

DROP TABLE IF EXISTS pets ;

CREATE TABLE table pets 
( pet_id int not null auto_increment PRIMARY KEY, 
  person_id int not null, 
  INDEX person_ix (person_id, pet_id)            -- composite index
) ENGINE = myisam ;

INSERT INTO pets (person_id) 
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;


mysql> EXPLAIN SELECT pet_id FROM pets 
              WHERE person_id = 2  
              ORDER BY pet_id asc \G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: pets
        type: ref
possible_keys: person_ix
         key: person_ix
     key_len: 4
         ref: const
        rows: 3
       Extra: Using where; Using index
1 row in set (0.00 sec)

正如預期的那樣，文件排序消失了。

---

現在讓我們嘗試使用 InnoDB 引擎：

DROP TABLE IF EXISTS pets ;

CREATE TABLE table pets 
( pet_id int not null auto_increment PRIMARY KEY, 
  person_id int not null, 
  INDEX person_ix (person_id)            -- simple index
) ENGINE = innodb ;                      -- InnoDB engine

INSERT INTO pets (person_id) 
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets 
              WHERE person_id = 2  
              ORDER BY pet_id asc \G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: pets
        type: ref
possible_keys: person_ix
         key: person_ix
     key_len: 4
         ref: const
        rows: 3
       Extra: Using where; Using index
1 row in set (0.00 sec)

**也沒有文件排序！**即使索引沒有明確包含該pet_id列，但值在那裡並已排序。您可以檢查如果您使用 定義索引(person_id, pet_id)，則EXPLAIN是相同的。

讓我們用 InnoDB 和復合索引來實際操作：

DROP TABLE IF EXISTS pets ;

CREATE TABLE table pets 
( pet_id int not null auto_increment PRIMARY KEY, 
  person_id int not null, 
  INDEX person_ix (person_id, pet_id)    -- composite index
) ENGINE = innodb ;                      -- InnoDB engine

INSERT INTO pets (person_id) 
VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;

mysql> EXPLAIN SELECT pet_id FROM pets 
              WHERE person_id = 2  
              ORDER BY pet_id asc \G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: pets
        type: ref
possible_keys: person_ix
         key: person_ix
     key_len: 4
         ref: const
        rows: 3
       Extra: Using where; Using index
1 row in set (0.00 sec)

與前一個案例的計劃相同。

---

100% 確定，我還執行了最後 2 個案例（InnoDB 引擎，具有單個索引和復合索引）啟用file_per_table設置並在表中添加數千行：

DROP TABLE IF EXISTS ... ;
CREATE TABLE ... ;

mysql> INSERT INTO pets (person_id) 
      VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3) ;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id) 
      VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id) 
      VALUES (1),(2),(3),(1),(2),(3),(4),(1),(8),(1),(2),(3),(127) ;
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pets (person_id) 
      SELECT a.person_id+b.person_id-1 
      FROM pets a CROSS JOIN pets b CROSS JOIN pets c ;
Query OK, 54872 rows affected (0.47 sec)
Records: 54872  Duplicates: 0  Warnings: 0

在這兩種情況下，檢查實際文件大小都會產生相同的結果：

ypercube@apollo:~$ sudo ls -la /var/lib/mysql/x/ | grep pets
-rw-rw----  1 mysql mysql     8604 Apr 21 07:25 pets.frm
-rw-rw----  1 mysql mysql 11534336 Apr 21 07:25 pets.ibd

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