Bạn muốn tăng tốc độ truy vấn cơ sở dữ liệu? Tìm hiểu cách tạo chỉ mục cơ sở dữ liệu bằng SQL để tối ưu hóa hiệu suất truy vấn và tăng tốc độ truy xuất dữ liệu.
Khi truy xuất dữ liệu từ bảng cơ sở dữ liệu, bạn sẽ cần lọc dựa trên các cột cụ thể thường xuyên hơn.
Giả sử bạn đang viết một truy vấn SQL để tìm nạp dữ liệu dựa trên các điều kiện nhất định. Theo mặc định, việc chạy truy vấn sẽ quét toàn bộ bảng cho đến khi tìm thấy tất cả các bản ghi khớp với điều kiện, sau đó trả về kết quả.
Điều này có thể rất kém hiệu quả khi bạn cần truy vấn một bảng cơ sở dữ liệu lớn có vài triệu hàng. Bạn có thể tăng tốc các truy vấn như vậy bằng cách tạo chỉ mục cơ sở dữ liệu.
Chỉ mục cơ sở dữ liệu là gì?
Khi bạn muốn tìm một thuật ngữ cụ thể trong một cuốn sách, liệu bạn có quét toàn bộ cuốn sách – hết trang này đến trang khác – để tìm một thuật ngữ cụ thể không? Ồ không.
Thay vào đó, bạn sẽ tìm kiếm chỉ mục để tìm ra trang nào tham chiếu thuật ngữ nào và chuyển trực tiếp đến các trang đó. Chỉ mục trong cơ sở dữ liệu hoạt động rất giống chỉ mục trong sách.
Chỉ mục cơ sở dữ liệu là tập hợp các con trỏ hoặc tham chiếu đến dữ liệu thực nhưng được sắp xếp theo cách giúp tìm kiếm dữ liệu nhanh hơn. Trong nội bộ, chỉ mục cơ sở dữ liệu có thể được triển khai bằng cách sử dụng các cấu trúc dữ liệu như cây B+ và bảng băm. Do đó, chỉ mục cơ sở dữ liệu sẽ cải thiện tốc độ và hiệu quả của các hoạt động truy xuất dữ liệu.
Tạo chỉ mục cơ sở dữ liệu trong SQL
Bây giờ chúng ta đã biết chỉ mục cơ sở dữ liệu là gì và nó có thể tăng tốc độ truy xuất dữ liệu như thế nào, hãy tìm hiểu cách tạo chỉ mục cơ sở dữ liệu trong SQL.
Khi thực hiện thao tác lọc – bằng cách chỉ định điều kiện truy xuất bằng mệnh đề WHERE – bạn có thể muốn tìm kiếm một cột cụ thể thường xuyên hơn các cột khác.
CREATE INDEX index_name ON table (column)
Đây,
- index_name là tên của chỉ mục cần tạo
- bảng đề cập đến một bảng trong cơ sở dữ liệu quan hệ
- cột đề cập đến tên cột trong bảng cơ sở dữ liệu mà chúng ta cần tạo chỉ mục.
Bạn cũng có thể tạo chỉ mục trên nhiều cột – chỉ mục nhiều cột – tùy theo yêu cầu của bạn. Đây là cú pháp để làm điều đó:
CREATE INDEX index_name ON table (column_1, column_2,...,column_k)
Bây giờ hãy chuyển sang một ví dụ thực tế.
Hiểu sự gia tăng hiệu suất chỉ mục cơ sở dữ liệu
Để hiểu lợi ích của việc tạo chỉ mục, chúng ta cần tạo một bảng cơ sở dữ liệu với số lượng lớn các bản ghi. Các ví dụ mã dành cho SQLite. Nhưng bạn cũng có thể sử dụng RDBMS khác mà bạn chọn, chẳng hạn như PostgreSQL và MySQL.
Điền vào bảng cơ sở dữ liệu với các bản ghi
Bạn cũng có thể sử dụng mô-đun ngẫu nhiên tích hợp sẵn của Python để tạo và chèn bản ghi vào cơ sở dữ liệu. Tuy nhiên, chúng tôi sẽ sử dụng Forger để điền vào bảng cơ sở dữ liệu một triệu hàng.
Kịch bản python sau:
- Tạo và kết nối với cơ sở dữ liệu Customer_db.
- Tạo bảng khách hàng với các trường: tên, họ, thành phố và số_đơn_hàng.
- Nó tạo ra dữ liệu tổng hợp và chèn dữ liệu – một triệu bản ghi – vào bảng khách hàng.
Bạn cũng có thể tìm thấy mã trên GitHub.
# main.py
# imports
import sqlite3
from faker import Faker
import random
# connect to the db
db_conn = sqlite3.connect('customer_db.db')
db_cursor = db_conn.cursor()
# create table
db_cursor.execute('''CREATE TABLE customers (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
city TEXT,
num_orders INTEGER)''')
# create a Faker object
fake = Faker()
Faker.seed(27)
# create and insert 1 million records
num_records = 1_000_000
for _ in range(num_records):
first_name = fake.first_name()
last_name = fake.last_name()
city = fake.city()
num_orders = random.randint(0,100)
db_cursor.execute('INSERT INTO customers (first_name, last_name, city, num_orders) VALUES (?,?,?,?)', (first_name, last_name, city, num_orders))
# commit the transaction and close the cursor and connection
db_conn.commit()
db_cursor.close()
db_conn.close()Bây giờ chúng ta có thể bắt đầu truy vấn.
Tạo chỉ mục trên cột Thành phố
Giả sử bạn muốn lấy thông tin khách hàng bằng cách lọc theo cột thành phố. Truy vấn SELECT của bạn sẽ trông như thế này:
SELECT column(s) FROM customers WHERE condition;
Vì vậy, hãy tạo city_idx trong cột thành phố trong bảng khách hàng:
CREATE INDEX city_idx ON customers (city);
⚠ Việc tạo chỉ mục mất một lượng thời gian đáng kể và là thao tác chỉ thực hiện một lần. Tuy nhiên, lợi ích về hiệu suất khi bạn cần một số lượng lớn truy vấn – bằng cách lọc theo cột thành phố – sẽ rất đáng kể.
Bỏ chỉ mục cơ sở dữ liệu
Để xóa một chỉ mục, bạn có thể sử dụng câu lệnh DROP INDEX như sau:
DROP INDEX index_name;
So sánh thời gian truy vấn có và không có chỉ mục
Nếu muốn chạy truy vấn trong tập lệnh python, bạn có thể sử dụng bộ hẹn giờ mặc định để biết thời gian thực hiện truy vấn.
Ngoài ra, bạn có thể chạy truy vấn bằng ứng dụng khách dòng lệnh sqlite3. Để làm việc với customer_db.db bằng ứng dụng khách dòng lệnh, hãy chạy lệnh sau trong terminal:
$ sqlite3 customer_db.db;
Để biết thời gian thực hiện gần đúng, bạn có thể sử dụng hàm .timer được tích hợp trong sqlite3 như sau:
sqlite3 > .timer on
> <query here>Vì chúng ta đã tạo chỉ mục trên cột thành phố nên các truy vấn yêu cầu lọc dựa trên cột thành phố trong mệnh đề WHERE sẽ nhanh hơn nhiều.
Chạy truy vấn của bạn trước tiên. Sau đó tạo chỉ mục và chạy lại các truy vấn. Lưu ý thời gian thực hiện trong cả hai trường hợp. Dưới đây là một số ví dụ:
QueryTime không có IndexTime với IndexSELECT * TỪ máy khách
Ở ĐÂU thành phố THÍCH “% mới”
GIỚI HẠN 10;0.100s0.001sCHỌN * TỪ khách hàng
WHERE city=’New Wesley’;0.148s0.001sSELECT * TỪ khách hàng
Ở ĐÂU thành phố W (“New Wesley”, “New Steven”, “New Carmenmouth”);00,247s0,003s
Chúng ta có thể thấy rằng thời gian tìm kiếm với chỉ mục nhanh hơn nhiều so với thời gian tìm kiếm không có chỉ mục trong cột thành phố.
Các phương pháp hay nhất để tạo và sử dụng chỉ mục cơ sở dữ liệu
Luôn kiểm tra xem mức tăng hiệu suất có lớn hơn chi phí tạo chỉ mục cơ sở dữ liệu hay không. Dưới đây là một số phương pháp hay nhất cần ghi nhớ:
- Chọn các cột thích hợp để tạo chỉ mục. Tránh tạo quá nhiều chỉ mục do chi phí đáng kể.
- Mỗi khi một cột được lập chỉ mục được cập nhật thì chỉ mục tương ứng cũng sẽ được cập nhật. Vì vậy, việc tạo chỉ mục cơ sở dữ liệu (mặc dù nó tăng tốc độ tìm nạp) sẽ làm chậm đáng kể quá trình chèn và cập nhật. Do đó, hãy tạo chỉ mục trên các cột được tìm kiếm thường xuyên nhưng ít được cập nhật.
Khi nào bạn không nên tạo chỉ mục?
Đến bây giờ bạn đã biết thời điểm và cách tạo chỉ mục. Nhưng chúng ta cũng hãy chỉ định khi nào chỉ mục cơ sở dữ liệu có thể không cần thiết:
- Khi bảng cơ sở dữ liệu nhỏ và không chứa nhiều hàng, việc quét toàn bộ bảng để lấy dữ liệu sẽ không quá tốn kém.
- Không tạo chỉ mục trên các cột hiếm khi được sử dụng để tìm kiếm. Khi tạo chỉ mục trên các cột không được tìm kiếm thường xuyên, chi phí tạo và duy trì chỉ mục sẽ lớn hơn hiệu suất đạt được.
Tóm lại
Chúng ta hãy xem những gì chúng ta đã học được:
- Khi truy vấn cơ sở dữ liệu để truy xuất dữ liệu, bạn có thể cần lọc thường xuyên hơn dựa trên các cột cụ thể. Chỉ mục cơ sở dữ liệu cho các cột được truy vấn thường xuyên như vậy có thể cải thiện hiệu suất.
- Để tạo chỉ mục trên một cột duy nhất, hãy sử dụng cú pháp: CREATE INDEX tên_chỉ mục TRÊN bảng (cột). Nếu bạn muốn tạo chỉ mục nhiều cột thì sử dụng: CREATE INDEX index_name ON table (column_1, column_2,…,column_k)
- Bất cứ khi nào một cột được lập chỉ mục được sửa đổi, chỉ mục tương ứng cũng sẽ được cập nhật. Do đó, hãy chọn các cột thích hợp – được truy vấn thường xuyên và ít được cập nhật thường xuyên hơn – để tạo chỉ mục.
- Nếu bảng cơ sở dữ liệu tương đối nhỏ hơn thì chi phí tạo, duy trì và cập nhật chỉ mục sẽ lớn hơn hiệu suất đạt được.
Trong hầu hết các hệ thống quản lý cơ sở dữ liệu hiện đại, có một trình tối ưu hóa truy vấn để kiểm tra xem liệu chỉ mục trên một cột cụ thể có tăng tốc độ thực hiện truy vấn hay không. Tiếp theo, hãy tìm hiểu các phương pháp hay nhất để thiết kế cơ sở dữ liệu.
