programming mysql

Làm cách nào để tạo nhiều chỉ mục trong MySQL?

Một chỉ mục có thể có hai hoặc nhiều cột. Chỉ mục nhiều cột còn được gọi là chỉ mục ghép hoặc chỉ mục nối

Chúng ta hãy xem xét một truy vấn có thể sử dụng hai chỉ mục khác nhau trên bảng dựa trên các hạn chế của mệnh đề WHERE. Trước tiên chúng tôi tạo các chỉ mục này

mysql> ALTER TABLE album
    -> ADD INDEX [country_id],
    -> ADD INDEX [album_type_id];
Query OK, 553875 rows affected [18.89 sec]

Sẽ hiệu quả hơn khi kết hợp các câu lệnh DML cho một bảng nhất định khi có thể. Nếu bạn chọn chạy các câu lệnh ALTER này dưới dạng hai câu lệnh riêng lẻ, điều sau đây sẽ xảy ra

mysql> ALTER TABLE album DROP index country_id, drop index album_type_id;
Query OK, 553875 rows affected [15.72 sec]
mysql> ALTER TABLE album ADD INDEX [country_id];
Query OK, 553875 rows affected [16.76 sec]
mysql> ALTER TABLE album ADD INDEX [album_type_id];
Query OK, 553875 rows affected [25.23 sec]

Nếu đây là bảng quy mô sản xuất và câu lệnh ALTER mất 60 phút hoặc 6 giờ, thì đây là một khoản tiết kiệm đáng kể

TIỀN BOA

Tạo một chỉ mục là một hoạt động tốn nhiều thời gian và có thể chặn các hoạt động khác. Bạn có thể kết hợp việc tạo nhiều chỉ mục trên một bảng nhất định với một câu lệnh ALTER duy nhất

mysql> EXPLAIN SELECT al.name, al.first_released, al.album_type_id
    -> FROM album al
    -> WHERE al.country_id=221
    -> AND album_type_id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: al
         type: ref
possible_keys: album_type_id,country_id
          key: country_id
      key_len: 3
          ref: const
         rows: 154638
        Extra: Using where

GHI CHÚ

Tùy thuộc vào phiên bản MySQL bạn sử dụng, các cải tiến của trình tối ưu hóa có thể cung cấp một QEP khác trong ví dụ này. Nói chung, bạn có thể đảm bảo đầu ra giống nhau của các ví dụ sau trong tất cả MySQL 5. x với cài đặt biến hệ thống tương thích ngược MySQL sau

mysql> SET @@session.optimizer_switch=‘index_merge_intersection=off’;

Tuy nhiên, nếu chúng tôi chạy cùng một truy vấn cho một giá trị khác cho loại album, thì bây giờ chúng tôi sử dụng một chỉ mục khác

mysql> EXPLAIN SELECT al.name, al.first_released, al.album_type_id
    -> FROM album al
    -> WHERE al.country_id=221
    -> AND album_type_id=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: al
         type: ref
possible_keys: album_type_id,country_id
          key: album_type_id
      key_len: 4
          ref: const
         rows: 58044
        Extra: Using where

Tại sao MySQL đưa ra quyết định này?

mysql> SHOW INDEXES FROM album\G
...
*************************** 4. row ***************************
        Table: album
   Non_unique: 1
     Key_name: album_type_id
 Seq_in_index: 1
  Column_name: album_type_id
    Collation: A
  Cardinality: 12
...
*************************** 6. row ***************************
        Table: album
   Non_unique: 1
     Key_name: country_id
 Seq_in_index: 1
  Column_name: country_id
    Collation: A
  Cardinality: 499
...

Nếu MySQL chỉ sử dụng chỉ số cardinality, thì bạn sẽ mong đợi QEP luôn sử dụng cột country_id vì cột này thường độc đáo hơn và do đó sẽ truy xuất ít hàng hơn. Mặc dù chỉ số cardinality là một chỉ báo về tính duy nhất, nhưng MySQL cũng nắm giữ các số liệu thống kê bổ sung về phạm vi và khối lượng của các giá trị duy nhất. Chúng tôi có thể xác nhận một số con số này bằng cách xem phân phối bảng thực tế

mysql> SELECT COUNT[*] FROM album where country_id=221;

Trong truy vấn đầu tiên, chỉ số country_id đã được chọn. Kết quả thực tế hiển thị 92K hàng so với 289K hàng nếu album_type_id được chọn

Đối với truy vấn thứ hai, kết quả thực tế hiển thị 92 nghìn hàng so với 111 nghìn hàng, với truy vấn sau thực sự được chọn. Nếu bạn so sánh các con số thực tế với các giá trị hàng ước tính của QEP, bạn cũng sẽ tìm thấy sự khác biệt hợp lý—ví dụ: truy vấn thứ hai ước tính 58 nghìn hàng trong khi thực tế có 111 nghìn hàng hoặc gần gấp đôi số hàng thực tế

Trong vài tuần qua, tôi đã nói về các chỉ mục và cách chúng giúp tăng tốc độ tìm kiếm cơ sở dữ liệu. Tôi đã nói về ưu và nhược điểm của chúng, sau đó nói về các loại chỉ mục khác nhau và khi nào thì phù hợp

Có một điều tôi chưa chỉ cho bạn trong loạt bài này về MySQL, và đó là cách bạn thực sự tạo các chỉ mục và thêm chúng vào các bảng cơ sở dữ liệu hiện có. Đó là những gì tôi muốn nói về ngày hôm nay. Tôi sẽ chỉ cho bạn nhiều cách khác nhau để thêm chỉ mục trong SQL và sau đó tôi sẽ đưa ra một số ý tưởng khi bạn muốn sử dụng chúng

Thêm một chỉ mục khi bạn tạo một bảng

Cũng giống như có nhiều loại chỉ mục, có nhiều cách để tạo hoặc thêm chúng vào bảng MySQL. Một cách là thêm chỉ mục khi bạn tạo bảng lần đầu tiên

Đây là một bảng đơn giản chứa ba cột để chứa họ và tên cũng như địa chỉ email của khách hàng. Tôi đã thêm một chỉ mục vào cột Email

[code type=”mysql”]
TẠO BẢNG Khách hàng[
LastName CHAR[30] NOT NULL,
FirstName CHAR[30] NOT NULL,
Email CHAR[50] NOT NULL,
INDEX [Email]
];
[/code]

Khá đơn giản. Bạn tạo một chỉ mục với một câu lệnh INDEX và bạn thêm một tham số cho cột được lập chỉ mục. Bạn cũng có thể thêm khóa chính thay vì chỉ mục

[code type=”mysql”]
TẠO BẢNG Khách hàng[
LastName CHAR[30] NOT NULL,
FirstName CHAR[30] NOT NULL,
Email CHAR[50] NOT NULL,
PRIMARY KEY [Email]
];
[/code]

Tương tự, để thêm một chỉ mục Fulltext

[code type=”mysql”]
TẠO BẢNG Bài đăng[
post_author CHAR[50],
post_date DATETIME,
post_content LONGTEXT,
FULLTEXT [post_content]
];
[/code]

Để tạo chỉ mục giảm dần [và giả sử bạn đang làm việc với MySQL 8 trở lên], bạn thêm DESC vào tên cột cần lập chỉ mục

[code type=”mysql”]
TẠO BẢNG Khách hàng[
LastName CHAR[30] NOT NULL,
FirstName CHAR[30] NOT NULL,
Email CHAR[50] NOT NULL,
INDEX [Email DESC]
];
[/code]

Thêm một chỉ mục nhiều cột rất giống nhau. Bạn tách từng cột bằng dấu phẩy. Và giống như các chỉ mục cột đơn, bạn cũng có thể thêm khóa chính thay vì chỉ mục

[code type=”mysql”]
TẠO BẢNG Khách hàng[
LastName CHAR[30] NOT NULL,
FirstName CHAR[30] NOT NULL,
Email CHAR[50] NOT NULL,
INDEX [LastName,FirstName]
];
[/code]

Thêm một chỉ mục vào một bảng hiện có

Việc thêm một chỉ mục vào một bảng hiện có cũng đơn giản như vậy và bạn có thể tạo chỉ mục theo nhiều cách khác nhau. Nhưng trước tiên, bạn có thể muốn biết liệu bảng hiện có đã có bất kỳ chỉ mục nào chưa

[code type=”mysql”]
HIỂN THỊ CHỈ SỐ TỪ tên_bảng;
[/code]

Đây là một ví dụ sử dụng bảng wp_postmeta trong WordPress

[code type=”mysql”]
HIỂN THỊ CHỈ SỐ TỪ wp_postmeta;
[/code]

Và kết quả từ việc chạy truy vấn thông qua phpMyAdmin

Bấm vào để xem hình ảnh lớn hơn

Bạn có thể thấy ba chỉ mục [cột Key_name], một trong số đó là khóa chính, cùng với các cột được liên kết của chúng. Chỉ mục trên metakey cho phép giá trị NULL, mặc dù hai giá trị còn lại thì không. Cũng lưu ý rằng cả ba chỉ mục đều được xây dựng bằng cấu trúc BTREE

Có hai cách khác nhau để thêm chỉ mục vào các bảng hiện có. Đầu tiên sử dụng lệnh CREATE, lệnh này có thể được sử dụng để tạo cả chỉ mục thông thường và chỉ mục duy nhất. Bạn gọi lệnh, đặt tên cho chỉ mục, bảng và cột để thêm chỉ mục vào

Tạo chỉ mục bằng lệnh CREATE

[code type=”mysql”]
TẠO INDEX index_name TRÊN table_name[column_name];
TẠO INDEX DUY NHẤT index_name TRÊN table_name[column_name];
[/code]

Dưới đây là một vài biến thể. Cái đầu tiên thêm chỉ mục vào nhiều cột. Cái thứ hai chỉ định cấu trúc cho chỉ mục ở cuối

[code type=”mysql”]
TẠO INDEX index_name TRÊN table_name[column1, column2, column3];
TẠO INDEX index_name TRÊN table_name[
[/code]

Nếu cột chứa các giá trị chuỗi, bạn có thể đặt chỉ mục chỉ bao gồm [n] ký tự đầu tiên của chuỗi

[code type=”mysql”]
TẠO INDEX index_name TRÊN table_name[column_name[n]];
[/code]

Các biến thể này cũng hoạt động với các chỉ mục duy nhất cũng như các chỉ mục thông thường

Và tất nhiên, bạn cũng có thể thêm chỉ mục Toàn văn vào bảng hiện có

[code type=”mysql”]
TẠO FULLTEXT INDEX index_name TRÊN table_name[column_name];
[/code]

Thêm chỉ mục bằng ALTER TABLE và lệnh ADD

Một điều bạn không thể làm với lệnh CREATE là thêm khóa chính. Để làm được điều đó, chúng ta sẽ phải xem xét phương pháp chung khác để thêm chỉ mục vào bảng hiện có. Nó sử dụng lệnh ALTER TABLE để ADD INDEX hoặc ADD UNIQUE INDEX, ADD PRIMARY KEY hoặc ADD FULLTEXT

[code type=”mysql”]
ALTER TABLE table_name THÊM CHỈ SỐ [Họ, Tên];
ALTER TABLE table_name THÊM CHỈ SỐ DUY NHẤT [Địa chỉ email
ALTER TABLE table_name ADD PRIMARY KEY [ID];
ALTER TABLE table_name ADD FULLTEXT [post_content];
[/code]

Với phương pháp bảng thay đổi, bạn không chỉ định tên cho chỉ mục. MySQL tự động tạo một chỉ mục, mặc dù bạn sẽ phải sử dụng lệnh SHOW INDEX sau khi thêm chỉ mục để xem tên nếu bạn muốn biết nó là gì

Nếu bạn muốn xóa một chỉ mục, bạn có thể thực hiện việc đó bằng hai phương pháp tương tự

[code type=”mysql”]
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX PRIMARY KEY;
[/code]

Thêm chỉ mục vào các trang web dựa trên WordPress

Vì tỷ lệ cược có lợi cho bạn khi điều hành một trang web WordPress ở đâu đó, nên bạn có thể muốn đọc một chút về cách WordPress tổ chức cơ sở dữ liệu thành các bảng khác nhau. Một số bảng không có bất kỳ chỉ mục nào được liên kết với chúng, mặc dù một chỉ mục ở đây hoặc ở đó có thể giúp tăng tốc mọi thứ

Thêm chỉ mục vào WordPress cũng giống như thêm chỉ mục vào bất kỳ bảng nào trong cơ sở dữ liệu của bạn. Một số người nghĩ rằng có thể thêm nhiều thứ hơn những gì WordPress đi kèm theo mặc định. Dưới đây là một số bài viết nói về việc thêm chỉ mục vào các bảng WordPress cụ thể có thể được hưởng lợi từ một chỉ mục

Thêm chỉ mục MySQL vào Bảng wp_options của WordPress
Thêm lập chỉ mục vào meta_value trong wp_postmeta
Cách dọn sạch Bảng wp_options và Dữ liệu được tải tự động của bạn
Plugin tự động tải chỉ mục

Mẹo để làm việc với các chỉ mục

Một vài tuần trước, tôi đã đề cập đến một số lý do để sử dụng chỉ mục và mặc dù điều đó có nghĩa là tôi phải lặp lại một chút, nhưng tôi muốn đề cập lại một vài trong số chúng để nhắc bạn khi bạn có thể muốn sử dụng chỉ mục.

Hãy nhớ rằng các chỉ mục hoạt động tốt nhất để đọc dữ liệu. Chúng hoạt động tốt nhất với câu lệnh SELECT. Bởi vì các chỉ mục là các bảng riêng biệt, khi dữ liệu trong cột hoặc các cột được lập chỉ mục thay đổi, MySQL cần lập chỉ mục lại chúng. Vì vậy, hãy suy nghĩ kỹ trước khi sử dụng các chỉ mục trên các cột mà bạn thường xuyên CẬP NHẬT, CHÈN hoặc XÓA

Các câu lệnh SELECT với mệnh đề WHERE là những ứng cử viên đặc biệt tốt cho các chỉ mục. Chỉ mục giúp nhanh chóng tìm thấy các hàng mong muốn mà không cần phải quét toàn bộ bảng theo từng hàng

Các chỉ mục cũng có thể hữu ích khi bạn muốn sắp xếp kết quả của truy vấn CHỌN bằng ORDER BY hoặc thứ gì đó tương tự. Sử dụng một chỉ mục sẽ loại bỏ một bước sắp xếp bổ sung để tăng tốc độ truy vấn

Bạn nên thêm trường hoặc cột ID vào mỗi bảng và thêm chỉ mục, thường là khóa chính, vào bảng đó. Thật tốt khi sử dụng các chỉ mục hoặc khóa duy nhất bất cứ khi nào một cột chứa các giá trị duy nhất hoặc khi các giá trị KHÔNG phải là NULL

Bất cứ khi nào bạn sử dụng THAM GIA cột và cả hai cột đều có cùng kiểu dữ liệu, bạn có thể muốn thêm một chỉ mục vào các cột. Bạn phải đảm bảo rằng các cột cùng loại [int, char, v.v. ]. Ví dụ: hai cột tên trong truy vấn sau đây tạo ra các ứng cử viên chỉ mục tốt

[code type=”mysql”]
CHỌN tên TỪ người dùng
TRÁI THAM GIA đơn hàng BẬT [người dùng. tên = mệnh lệnh. name]
WHERE người dùng. id = $userid;
[/code]

Bạn muốn tránh trùng lặp chỉ mục. Bạn không cần hoặc không muốn có nhiều chỉ mục cho cùng một truy vấn vì mỗi chỉ mục chiếm dung lượng ổ đĩa và các chỉ mục bổ sung có thể làm mọi thứ chậm lại một chút

Ngoài ra, hãy nhớ rằng đối với các bảng nhỏ, bạn có thể không cần chỉ mục bất kể chi tiết cụ thể. Khi chỉ có một vài hàng, việc quét toàn bộ bảng sẽ nhanh hơn là tạo và làm việc với các chỉ mục

Tôi sẽ để lại cho bạn một vài liên kết đến các bài báo có nhiều hơn. Phần đầu tiên hoạt động thông qua một vài truy vấn cụ thể và nói về cách lập chỉ mục chúng

Xây dựng INDEX tốt nhất cho một LỰA CHỌN nhất định
3 cách MySQL sử dụng chỉ mục
3 cách để tối ưu hóa hiệu suất MySQL. lập chỉ mục
Lập chỉ mục Nên và Không nên

Bớt tư tưởng

Hy vọng rằng trong vài tuần qua, tôi đã thuyết phục bạn về lợi ích của việc sử dụng các chỉ mục. Nói chung, chúng tăng tốc độ tìm kiếm cơ sở dữ liệu của bạn bằng cách cho phép MySQL sắp xếp dữ liệu của bạn theo cách tốt nhất cho các truy vấn khác nhau

Nói về truy vấn, tôi còn một chủ đề nữa về cơ sở dữ liệu SQL và đó là truy vấn. Trong một vài tuần nữa, tôi sẽ nói về cách bạn có thể tìm thấy các truy vấn hoạt động kém nhất của mình và sau đó tôi sẽ đưa ra các mẹo để viết các truy vấn thực hiện tốt hơn và nhanh hơn

Làm cách nào để sử dụng nhiều chỉ mục trong MySQL?

Nếu bảng có chỉ mục nhiều cột, bất kỳ tiền tố ngoài cùng bên trái nào của chỉ mục đều có thể được trình tối ưu hóa sử dụng để tra cứu hàng . Ví dụ: nếu bạn có chỉ mục ba cột trên [col1, col2, col3] , thì bạn có khả năng tìm kiếm được lập chỉ mục trên [col1], [col1, col2] và [col1, col2, col3]. Để biết thêm thông tin, xem Phần 8. 3.

Chúng ta có thể tạo nhiều chỉ mục không?

Lý do tạo nhiều chỉ mục cho cùng một cột . Ví dụ: cập nhật chỉ mục khiến trang mở rộng . Bạn có thể thực hiện lập chỉ mục lại để giải quyết vấn đề về hiệu suất này. Bắt đầu từ PG12, lập chỉ mục lại đồng thời hỗ trợ trực tiếp lập chỉ mục lại.

Chúng ta có thể tạo nhiều chỉ mục trong SQL không?

Mặc dù SQL Server cho phép chúng ta tạo nhiều Non-clustered index, tối đa 999 Non-clustered trên mỗi bảng có thể bao gồm các truy vấn của chúng ta, bất kỳ chỉ mục nào được thêm vào bảng sẽ tác động tiêu cực đến hiệu suất sửa đổi dữ liệu trên bảng đó

Chúng ta có thể tạo nhiều chỉ mục trên cùng một cột MySQL không?

Ví dụ: nếu bạn có một chỉ mục trên cột {a} hoặc cột {a,b}, bạn không thể tạo một chỉ mục khác trên cùng một cột hoặc tập hợp các cột theo cùng thứ tự. Trong 12c, bạn có thể có nhiều chỉ mục trên cùng một cột hoặc tập hợp các cột miễn là loại chỉ mục khác nhau .