Hoan check

Compound (Composite) Indexes - MariaDB Knowledge Base.md

Source

Chỉ mục hỗn hợp - kiến thức cơ sở MariaDB

Một bhọc bài học nhỏ trong "Chỉ mục hỗn hợp"

Tài lnày tài liệu này tầm thường và có lẽ là nhàm chán, nhưng lại tạo nên nhiều thông tin thú vị, có lẽ điều mà bạn không nhận ra về cách hoạt động đánh chỉ mục của MariaDB và MySQL.

Điều này cũng giải thích [EXPLAIN][1] (đến một mức độ nào đó).

(Hầu hết điều này cũng áp dụng cho các cơ sở dữ liệu không phải là MySQL.)

Truy vấn để tranh luận Thảo luận về câu truy vấn

Câu hỏi đặt ra là "Andrew Johnson là tổng thống Hoa Kì khi nào?".

Bảng Các tổng thống có sẵn giống như thế này:

+-----+------------+----------------+-----------+
| seq | last_name  | first_name     | term      |
+-----+------------+----------------+-----------+
|   1 | Washington | George         | 1789-1797 |
|   2 | Adams      | John           | 1797-1801 |
...
|   7 | Jackson    | Andrew         | 1829-1837 |
...
|  17 | Johnson    | Andrew         | 1865-1869 |
...
|  36 | Johnson    | Lyndon B.      | 1963-1969 |
...

("Andrew Johnson" đã được chọn cho bài học này vì có sự trùng lặp.)

chỉ số Index cái nào sẽ là tốt nhất cho câu hỏi đó? cụ thể hơn, cái gì tốt nhất cho việc       SELECT term FROM Presidents WHERE last_name = 'Johnson' AND first_name = 'Andrew';

Một số các chỉ mục để thử...

• Không chỉ mục nào
• Chỉ mục index(first_name), chỉ mục index(last_name) (2 chỉ mục riêng biệt)
• "Index Merge Intersect"
• Chỉ mục index(last_name, first_name) (một chỉ mục "hỗn hợp")
• Chỉ mục index(last_name, first_name, term) (một chỉ mục "covering" (bao ngoài))
• Biến thể khác

Không chỉ mục nào

Well, Tôi đang giả định một chút ở đây. Tôi có một KHÓA CHÍNH trên seq, nhưng nó không có lợi gì trên câu truy vấn mà chúng ta đang tìm hiểu.

mysql>  SHOW CREATE TABLE Presidents G
CREATE TABLE `presidents` (
  `seq` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(30) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `term` varchar(9) NOT NULL,
  PRIMARY KEY (`seq`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8

mysql>  EXPLAIN  SELECT  term
            FROM  Presidents
            WHERE  last_name = 'Johnson'
              AND  first_name = 'Andrew';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Presidents | ALL  | NULL          | NULL | NULL    | NULL |   44 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

# Or, using the other form of display:  EXPLAIN ... G
           id: 1
  select_type: SIMPLE
        table: Presidents
         type: ALL        <-- Implies table scan
possible_keys: NULL
          key: NULL       <-- Implies that no index is useful, hence table scan
      key_len: NULL
          ref: NULL
         rows: 44         <-- That's about how many rows in the table, so table scan
        Extra: Using where

Các triển khai chi tiết

Đầu tiên, hãy mô tả cách InnoDB lưu trữ và sử dụng các chỉ mục.

• Dữ liệu và KHÓA CHÍNH được "nhóm" lại với nhau trên BTree.
• Một cây tìm kiếm cân bằng khá là nhanh và hiệu quả. Đối với một bảng có hàng triệu hàng có thể có 3 cấp độ của BTree, và hai cấp cao nhất có thể được lưu trong bộ nhớ cache.
• Mỗi chỉ số phụ nằm trong một BTree khác, với KHÓA CHÍNH ở lá.
• Tìm nạp các mục 'liên tiếp' (theo chỉ mục) từ BTree rất hiệu quả vì chúng được lưu trữ liên tiếp.
• Để đơn giản, chúng ta có thể đếm từng tra cứu BTree dưới dạng 1 đơn vị công việc và bỏ qua các lần quét cho các mục liên tiếp. Điều này xấp xỉ số lần truy cập đĩa cho một bảng lớn trong một hệ thống bận rộn. Đối với MyISAM, PRIMARY KEY không được lưu trữ với dữ liệu, vì vậy hãy nghĩ về nó như là một khóa thứ cấp (quá đơn giản).

Chỉ mục(first_name), Chỉ mục(last_name)

Với người mới làm quen, một khi anh ta học được về đánh chỉ mục, sẽ quyết định đánh chỉ mục của từng nhiều cột. Nhưng...

MySQL hiếm khi sử dụng nhiều hơn một chỉ mục tại một thời điểm trong một truy vấn. Vì vậy, nó sẽ phân tích các chỉ mục khả thi.

• first_name -- có 2 dòng khả thi (một tìm kiếm BTree, sau đó quét lần lượt)

• last_name -- có 2 dòng khả thi hảy bảo nó chọn last_name. Đây là bước thực hiện SELECT: 1. Sử dụng INDEX(last_name), tìm 2 mục chỉ mục với last_name = 'Johnson'. 2. Lấy khóa chính (ngầm được thêm vào mỗi chỉ số phụ trong InnoDB); lấy (17, 36). 3. Nhận dữ liệu sử dụng seq = (17, 36) để lấy các hàng cho Andrew Johnson và Lyndon B. Johnson. 4. Hãy sử dụng mệnh đề WHERE lọc ra tất cả những hàng mình mong muốn. 5. Đưa ra câu trả lười lời (1865-1869).

  mysql> EXPLAIN SELECT term FROM Presidents WHERE last_name = 'Johnson' AND first_name = 'Andrew' G select_type: SIMPLE table: Presidents type: ref possible_keys: last_name, first_name key: last_name key_len: 92 <-- VARCHAR(30) utf8 may need 2+3*30 bytes ref: const rows: 2 <-- Two 'Johnson's Extra: Using where

"Index Merge Intersect" (Chỉ mục hợp nhất có điểm chung)???

Vậy vạn bạn có thực sự thông minh và quyết định rằng MySQL nên đủ thông minh để sử dụng cả 2 tên chỉ mục để lấy ra câu trả lời. Nó được gọi là "Intersect". 1. Sử udngj INDEX(last_name), tìm 2 mục chỉ mục với last_name = 'Johnson'; lấy ra được (7, 17) 2. Sử dụng chỉ mục(first_name), tìm 2 mục chỉ mục với first_name = 'Andrew'; lấy ra được (17, 36) 3. "phép và" 2 list lại với nhau (7,17) & (17,36) = (17) 4. Nhận từ dữ liệu sử dụng seq = (17) để lấy ra hàng cho Andrew Johnson. 5. Đưa ra câu trả lời (1865-1869).

           id: 1
  select_type: SIMPLE
        table: Presidents
         type: index_merge
possible_keys: first_name,last_name
          key: first_name,last_name
      key_len: 92,92
          ref: NULL
         rows: 1
        Extra: Using intersect(first_name,last_name); Using where

EXPLAIN không cung cấp thông tin chi tiết về số lượng hàng (row) nhận được từ mỗi chỉ mục,.vv

Chỉ mục(last_name, first_name)

Được gọi là chỉ mục "hợp chất" hoặc "hỗn hợp" vì nó có nhiều hơn một cột. 1.Đi sâu vào BTree để chỉ mục nhận được chính xác hàng chỉ mục cho Johnson+Andrew; get seq = (17). 2. Nhận từ dữ liệu sử dụng seq = (17) để lấy ra hàng cho Andrew Johnson. 3. Đưa ra kết quả (1865-1869). Như này tốt hơn. Thực tế nó luôn là "tốt nhất".

    ALTER TABLE Presidents
        (drop old indexes and...)
        ADD INDEX compound(last_name, first_name);

           id: 1
  select_type: SIMPLE
        table: Presidents
         type: ref
possible_keys: compound
          key: compound
      key_len: 184             <-- The length of both fields
          ref: const,const     <-- The WHERE clause gave constants for both
         rows: 1               <-- Goodie!  It homed in on the one row.
        Extra: Using where

"Covering": INDEX(last_name, first_name, term)

Nhạc Ngạc nhiên chưa! Chúng tôi thực sự có thể làm tốt hơn một chút nữa. Một chỉ "Covering" index là một trong số  tất cả cà trường của SELECT được tìm thấy trong chỉ mục. Nó có thêm tiền một điểm cộng là không phải đi vào "dữ liệu" để hoàn thành nhiệm vụ. 1. Đi sâu vào BTree để chỉ mục nhận được chính xác hàng chỉ mục cho Johnson+Andrew; lấy ra seq = (17). 2. Đưa lra kết quả (1865-1869). "Dữ liệu" BTree không được động tới, đây là sự cải tiến so với "trộn".

    ... ADD INDEX covering(last_name, first_name, term);

           id: 1
  select_type: SIMPLE
        table: Presidents
         type: ref
possible_keys: covering
          key: covering
      key_len: 184
          ref: const,const
         rows: 1
        Extra: Using where; Using index   <-- Note

Everything is similar to using "compound", except for the addition of "Using index". Mọi thứ đều tương tự như sử dụng "compound", ngoại trừ việc thêm "Using index".

Các biến thể

• Điều gì sẽ xảy ra nếu bạn xáo trộn các trường trong mệnh đề WHERE? Câu trả lời: Thứ tự mọi thứ trong AND không quan trọng.
• Điều gì sẽ xảy ra nếu bạn xáo trộn các trường trong Chỉ Mục ? Câu trả lời: Nó có thể tạo 1 sự khác biệt cực lớn. Có thể hơn 1 phút.
• Sẽ thế nào nếu có thêm trường vào cuối cùng? Câu trả lời: 1 chút ảnh hưởng nhỏ, có thể rất nhiều cái tốt ( ví dụ, "bao trùm").
• Thừa? Đó là, sẽ thế nào nếu bạn có cả 2 thứu này: Chỉ mục (a), Chỉ mục (a,b)? Cấu trà lời: Thừa chi phí gì đó trong các lệnh INSERT; nó hiếm khi có tác dụng trong các lệnh SELECT.
• Tiền tố? Đó là, Chỉ mục(last_name(5). first_name(5)) Câu trả lời: đừng bận tâm; nó hiếm khi có tác dụng, và thường làm hại. (chi tiết trong 1 chủ đề khác.)

Thêm các ví dụ:

    INDEX(last, first)
    ... WHERE last = '...' -- good (even though `first` is unused)
    ... WHERE first = '...' -- index is useless

    INDEX(first, last), INDEX(last, first)
    ... WHERE first = '...' -- 1st index is used
    ... WHERE last = '...' -- 2nd index is used
    ... WHERE first = '...' AND last = '...' -- either could be used equally well

    INDEX(last, first)
    Both of these are handled by that one INDEX:
    ... WHERE last = '...'
    ... WHERE last = '...' AND first = '...'

    INDEX(last), INDEX(last, first)
    In light of the above example, don't bother including INDEX(last).

Postlog

Refreshed -- Oct, 2012; more links -- Nov 2016