Một số anh em DEV trong quá trình làm việc với Cơ sở dữ liệu PostgreSQL, MySQL sẽ bắt gặp kỹ thuật tối ưu sử dụng Multicolumn Indexes. Bài viết này sẽ giúp anh em có góc nhìn rõ hơn về kiểu Index này.
1. Multicolumn Indexes là gì?
Đây là kỹ thuật sử dụng Index trên nhiều cột trong bảng.
Thuật ngữ Multicolumn Indexes của PostgreSQL hay MySQL cũng tương đồng với thuật ngữ Composite Indexes trong khi làm việc cùng cơ sở dữ liệu Oracle, SQL Server anh em nhé.
Anh em có thể đọc thêm bài viết về Composite Indexes và vấn đề ảnh hưởng tới hiệu năng của loại Index này tại đây: Click vào đây để đọc bài viết.
Giả sử bạn có bảng với cấu trúc đơn giản như sau:
mysql> desc orders; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | orderNumber | int | NO | PRI | NULL | | | orderDate | date | NO | | NULL | | | requiredDate | date | NO | | NULL | | | shippedDate | date | YES | | NULL | | | status | varchar(15) | NO | | NULL | | | comments | text | YES | | NULL | | | customerNumber | int | NO | MUL | NULL | | +----------------+-------------+------+-----+---------+-------+
Nếu chúng ta thường xuyên tìm kiếm bằng câu lệnh SQL dạng như sau:
SELECT name FROM wecommit_test WHERE customerNumber = '....' AND orderDate= .....;
Câu lệnh thường xuyên tìm kiếm trên cả 2 cột name và customerNumber và orderDate, nên chúng ta có thể tạo Multicolumn Indexes trên cả 2 cột này.
mysql> create index idx_custNumber_orderDate on orders(customerNumber, orderDate); Query OK, 0 rows affected (1.19 sec) Records: 0 Duplicates: 0 Warnings: 0
Kết thúc câu lệnh trên, hệ thống đã tạo ra một Multicolumn Indexes, thông tin như sau:
mysql> show index from orders;
2. Đánh giá hiệu quả của Multicolumn Indexes trong một số câu lệnh truy vấn khác nhau
2.1. Câu lệnh tìm kiếm điều kiện =
Thực hiện kiểm tra chiến lược thực thi của câu lệnh, kiểm chứng xem Multicolumn Indexes có sử dụng được trong điều kiện so sánh bằng hay không
mysql> explain analyze select * from orders where customerNumber=496 and orderDate='2005-04-01'; | EXPLAIN | -> Index lookup on orders using idx_custNumber_orderDate (customerNumber=496, orderDate=DATE'2005-04-01') (cost=0.35 rows=1) (actual time=0.052..0.060 rows=1 loops=1)
Với kết quả trên, chúng ta thấy hệ thống có thể sử dụng Index trong các điều kiện so sánh bằng.
Giải thuật sử dụng là: Index lookup
Chi phí của câu lệnh khi thực hiện theo chiến lược thực thi này là cost=0.35
2.1. Câu lệnh tìm kiếm điều kiện so sánh >, <, BETWEEN
mysql> explain analyze select * from orders where customerNumber > 496 and orderDate='2005-04-01'; | EXPLAIN | -> Filter: (orders.orderDate = DATE'2005-04-01') (cost=0.71 rows=0.1) (actual time=0.094..0.094 rows=0 loops=1) -> Index range scan on orders using customerNumber over (496 < customerNumber), with index condition: (orders.customerNumber > 496) (cost=0.71 rows=1) (actual time=0.091..0.091 rows=0 loops=1)
Trong điều kiện tìm kiếm trên, cột đầu tiên của Index (Cột customerNumber) chúng ta tìm kiếm với điều kiện so sánh >
Tại trường hợp này hệ thống vẫn có thể sử dụng Multicolumn Indexes như anh em có thể thấy trong kết quả của chiến lược thực thi.
Giải thuật lúc này của hệ thống đã chuyển từ Index Loopkup sang Index range scan.
Chi phí thực hiện câu lệnh cao hơn gấp đôi so với điều kiện tìm kiếm bằng (Cost lúc này bằng 0.71 so với Cost so sánh bằng ở ví dụ trước chỉ có 0.35)
2.2. Một số câu hỏi suy ngẫm sâu hơn cho anh em
- Giả sử một dự án các anh em gặp phải câu lệnh so sánh bằng như trên, hệ thống đã được tạo Multicolumn Indexes nhưng kiểm tra Explain Plan, chúng ta thấy Index không được sử dụng. ĐIều này có thể có nguyên nhân gì
- Trường hợp tìm kiếm trong câu lệnh như trên nhưng điều kiện WHERE như sau thì Index có hiệu quả hay không customerNumber != 496 and orderDate = ‘2005-04-01’;
3. Nếu bạn muốn làm chủ toàn bộ các kỹ thuật tối ưu, tiết kiếm 2-3 năm tự mình mày mò, tìm kiếm trên Google
Bạn biết được tất cả nhữn kỹ thuật và kinh nghiệm tối ưu trong các hệ thống trọng yếu (tối ưu Core banking, Core chứng khoán,…) bằng cách tham dự chương trình Từ điển tối ưu 100x hiệu năng: https://wecommit.com.vn/tu-dien-toi-uu-100x-hieu-nang/
4. Một số bài viết khác liên quan
Nếu bạn chưa biết về chiến lược thực thi, COST của câu lệnh là gì, bạn có thể xem lại một số bài viết khác của tôi:
- Chiến lược thực thi của câu lệnh trong tối ưu SQL: https://wecommit.com.vn/sql-execution-plan-trong-toi-uu-sql/
- Bạn có thể xem về một sai lầm thiết kế Index và ảnh hưởng hiệu năng trầm trọng: https://wecommit.com.vn/foreign-key-no-index/
5. Thông tin tác giả
Chào các bạn, tôi là Trần Quốc Huy – Founder Wecommit và đồng thời là tác giả của chương trình Từ điển tối ưu 100x hiệu năng.
Tôi chỉ tập trung vào 1 lĩnh vực duy nhất hơn 10 năm nay: Tối ưu cơ sở dữ liệu.
Các bạn có thể liên hệ với tôi qua các kênh sau
Facebook cá nhân của tôi: https://www.facebook.com/tran.q.huy.71/
Youtube: https://www.youtube.com/channel/UCtsYzL7iN7rBCPnkjYp4XYw