Thứ Tư, 26 tháng 7, 2023

Tạo Index cho nhiều cột trong MySQL

Trong bài này chúng ta sẽ học cách tạo chỉ mục index cho nhiều cột trong MySQL, đây là cách giúp bạn tối ưu hóa câu truy vấn trong một số trường hợp cụ thể.

Tùy vào từng câu truy vấn và nhu cầu của bài toán mà ta sẽ chọn chỉ mục và số cột khác nhau. Việc đánh chỉ mục này giúp MySQL phân loại được dữ liệu và trả kêt quả về tốt hơn.

Mục lục

  • 1. Khi nào tạo chỉ mục tổng hợp nhiều cột?
  • 2. Một ví dụ đặt chỉ mục nhiều cột trong MySQL
    • Trường hợp chỉ mỗi lastName
    • Trường hợp có cả lastName và firstName
    • Trường hợp truy vấn mỗi firstName

1. Khi nào tạo chỉ mục tổng hợp nhiều cột?

Chỉ mục tổng hợp là một chỉ mục trên nhiều cột. MySQL cho phép bạn tạo một chỉ mục tổng hợp bao gồm tối đa 16 cột. Việc tạo chỉ mục này giúp các câu truy vấn được xử lý tối ưu hơn.

Ví dụ bạn muốn truy vấn tìm danh sách sinh viên thuộc khoa CNTT và lớp CN01 thì có thể tạo chỉ mục cho 2 field (khoa_id, lop_id). Với cách làm này sẽ giúp tất cả những câu truy vấn có sử dụng cặp điều kiện này đều chạy nhanh hơn.

Bài viết này được đăng tại tranvanbinh.vn

Để tạo một chỉ mục tổng hợp tại thời điểm tạo bảng thì ta sử dụng câu lệnh sau:

1
2
3
4
5
6
7
CREATE TABLE table_name (
    c1 data_type PRIMARY KEY,
    c2 data_type,
    c3 data_type,
    c4 data_type,
    INDEX index_name (c2,c3,c4)
);

Trong ví dụ này thì mình đã tạo chỉ mục có tên là index_name gồm ba cột (c2, c3 ,c4).

Hoặc bạn cũng có thể sử dụng lệnh Create Index để tạo chỉ mục.

1
2
CREATE INDEX index_name
ON table_name(c2,c3,c4);

Lưu ý rằng nếu bạn tạo chỉ mục cho n cột thì những câu truy vấn có số cột ít hơn đều được tối ưu hóa, và phải tuân theo thứ tự từ trái sang phải. Như trong ví dụ trên thì những trường hợp sau:

1
2
3
(c1)
(c1,c2)
(c1,c2,c3)

Ví dụ:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
    *
FROM
    table_name
WHERE
    c1 = v1;
 
 
SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND
    c2 = v2;
 
 
SELECT 
    *
FROM
    table_name
WHERE
    c1 = v1 AND
    c2 = v2 AND
    c3 = v3;

Trình tối ưu hóa truy vấn bắt buộc phải theo thứ tự từ trái qua phải, nếu không thì index không có tác dụng gì cả. Như trong ví dụ trên thì nếu bạn thiết lập where:

  • c1 = v1 and c3 = v3 => sai
  • c1 = v1 and c2 = v2 => đúng
  • c2 = v2 => sai

2. Một ví dụ đặt chỉ mục nhiều cột trong MySQL

Giả sử chúng ta có bảng employees có cấu trúc như sau:

employees table png

Sau đó mình tạo index gồm hai cột firstName và lastName.

1
2
CREATE INDEX name
ON employees(lastName, firstName);

Như vậy chúng ta chỉ có thể truy vấn cho hai trường hợp:

  • Chỉ mỗi lastName
  • Cả lastName và firstName

Trường hợp chỉ mỗi lastName

Hãy tìm kiếm nhân viên có tên là "Patterson".

1
2
3
4
5
6
7
8
SELECT
    firstName,
    lastName,
    email
FROM
    employees
WHERE
    lastName = 'Patterson';

Câu truy vấn này có áp dụng index ở trên bởi vì lệnh where đầu tiên chính là lastName. Hãy đặt thêm lệnh EXPLAIN đằng trước câu Select để xem kết quả.

1
2
3
4
5
6
7
8
EXPLAIN SELECT
    firstName,
    lastName,
    email
FROM
    employees
WHERE
    lastName = 'Patterson';

Kết quả:

MySQL Composite Index Example 1 png

Như vậy câu SQL trên đã duyệt qua 3 rows.

Trường hợp có cả lastName và firstName

1
2
3
4
5
6
7
8
9
SELECT
    firstName,
    lastName,
    email
FROM
    employees
WHERE
    lastName = 'Patterson' AND
    firstName = 'Steve';

Tương tự trường hợp này vẫn được áp dụng index ở trên, bởi vì theo thứ tự index là lastName -> fistName.

MySQL Composite Index Example 2 png

Như trong hình là câu SQL đã duyệt qua 1 rows.

Trường hợp truy vấn mỗi firstName

1
2
3
4
5
6
7
8
SELECT
    firstName,
    lastName,
    email
FROM
    employees
WHERE
    firstName = 'Leslie';

Trường hợp này index trên không có tác dụng gì cả.

Như vậy là mình đã hướng dẫn xong cách tạo ra index trên nhiều column, cũng như nguyên tắc áp dung index vào nhiều trường hợp khác nhau.

Hãy nhớ thứ tự các field rất quan trọng trong việc đặt thứ tự các lệnh where nhé.

=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels:

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master