MySQL Mục lục: Hướng dẫn tạo, thêm và xóa

Một MySQL Mục lục?

An chỉ số in MySQL Chỉ mục là một cấu trúc dữ liệu lưu trữ các giá trị cột theo thứ tự để công cụ có thể tìm kiếm các hàng nhanh chóng. Chỉ mục được tạo trên cột hoặc các cột được sử dụng thường xuyên nhất để lọc dữ liệu. Hãy nghĩ về chỉ mục như một danh sách được sắp xếp theo thứ tự bảng chữ cái: việc tìm kiếm một tên trong một danh sách đã được sắp xếp sẽ nhanh hơn nhiều so với việc tìm kiếm trong một danh sách chưa được sắp xếp.

Việc sử dụng chỉ mục có một sự đánh đổi — mỗi thao tác INSERT hoặc UPDATE đều phải duy trì chỉ mục, vì vậy việc thêm quá nhiều chỉ mục vào một bảng có nhiều thao tác ghi có thể làm giảm hiệu suất tổng thể. Theo nguyên tắc chung, hãy lập chỉ mục cho các cột xuất hiện trong mệnh đề WHERE, JOIN và ORDER BY trên các bảng được đọc thường xuyên hơn là được ghi.

Tại sao cần sử dụng mục lục?

Không ai thích hệ thống chậm chạp. Hiệu năng cao là mối quan tâm hàng đầu đối với hầu hết các ứng dụng dựa trên cơ sở dữ liệu. Các doanh nghiệp chi rất nhiều tiền cho phần cứng để đảm bảo tốc độ truy vấn nhanh, nhưng khả năng của phần cứng đơn thuần cũng có giới hạn. Tối ưu hóa chỉ mục là một biện pháp hiệu quả và tiết kiệm hơn.

Thời gian phản hồi chậm thường xuất phát từ việc các hàng được lưu trữ theo thứ tự vật lý trên đĩa. Nếu không có chỉ mục, MySQL Phải quét từng hàng để tìm những hàng khớp với một điều kiện — gọi là "quét toàn bộ bảng". Chỉ mục cho phép MySQL nhảy thẳng đến các hàng phù hợp, điều này chuyển đổi kế hoạch truy vấn từ O(n) thành xấp xỉ O(log n) đối với việc tra cứu cây B.

Cú pháp: Tạo chỉ mục

Chỉ số có thể được định nghĩa ở hai nơi:

1. Tại thời điểm tạo bảng.
2. Sau khi bảng đã tồn tại.

Ví dụ: Tạo chỉ mục trực tiếp bằng lệnh CREATE TABLE

Đối với myflixdb Trong cơ sở dữ liệu này, chúng tôi dự đoán sẽ có rất nhiều lượt tìm kiếm trên cột "họ và tên đầy đủ". Đoạn mã bên dưới sẽ tạo một cơ sở dữ liệu mới. members_indexed bảng có chỉ mục trên full_names cột.

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

Thực thi tập lệnh trong MySQL Bàn làm việc dựa vào myflixdb cơ sở dữ liệu.

Refresh myflixdb để xem cái mới members_indexed bàn. Các full_names cột hiện xuất hiện bên dưới Chỉ số nút.

Khi số lượng thành viên tăng lên, các truy vấn tìm kiếm trên... members_indexed sử dụng mệnh đề WHERE và ORDER BY để đối chiếu. full_names nhanh hơn nhiều so với các truy vấn tương tự trên hệ thống gốc. members bảng không có mục lục.

Thêm chỉ mục sau khi bảng đã tồn tại.

Bạn thường sẽ phát hiện ra rằng một bảng hiện có cần chỉ mục — các truy vấn tìm kiếm chậm và kế hoạch EXPLAIN hiển thị quá trình quét toàn bộ bảng trên một cột xuất hiện trong mệnh đề WHERE. CREATE INDEX Câu lệnh này thêm chỉ mục mà không cần tạo lại bảng.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Ví dụ cụ thể — tăng tốc độ tìm kiếm trên title cột của movies bàn:

CREATE INDEX `title_index` ON `movies` (`title`);

Mọi truy vấn lọc theo movies.title Hiện tại, dữ liệu đã được hỗ trợ bởi chỉ mục mới. Các truy vấn lọc theo các cột khác vẫn sẽ quét toàn bộ bảng trừ khi chúng có chỉ mục riêng.

Lưu ý: Bạn có thể tạo chỉ mục tổng hợp trên nhiều cột khi các truy vấn của bạn luôn lọc hoặc sắp xếp theo cùng một tổ hợp cột. Thứ tự các cột rất quan trọng — cột đầu tiên quyết định xem chỉ mục có thể được sử dụng hay không.

Liệt kê các chỉ mục trên một bảng.

Sử dụng SHOW INDEXES Để xem tất cả các chỉ mục được định nghĩa trên một bảng.

SHOW INDEXES FROM `table_name`;

Ví dụ — liệt kê các chỉ mục trên movies bàn:

SHOW INDEXES FROM `movies`;

Chạy câu lệnh trong MySQL Workbench chống lại myflixdb Để xem các chỉ mục hiện có và các cột mà chúng bao phủ.

Lưu ý: Khóa chính và khóa ngoại được lập chỉ mục tự động bởi MySQLMỗi chỉ mục có một tên duy nhất và liệt kê (các) cột mà nó bao gồm.

Cú pháp: Drop Index

Sử dụng DROP INDEX Để xóa một chỉ mục hiện có khỏi bảng. Điều này hữu ích khi một bảng có nhiều thao tác ghi đang bị chậm lại do một chỉ mục không còn mang lại hiệu quả trong việc đọc dữ liệu.

DROP INDEX `index_id` ON `table_name`;

Ví dụ cụ thể — bỏ đi full_names chỉ mục từ members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

các loại MySQL Chỉ số

MySQL Hỗ trợ nhiều loại chỉ mục khác nhau, mỗi loại phù hợp với một khối lượng công việc khác nhau.

Kiểu	Mục đích
KHÓA CHÍNH	Mã định danh hàng duy nhất; được nhóm cùng với dữ liệu bảng trong InnoDB.
UNIQUE	Đảm bảo tính duy nhất đồng thời đóng vai trò như một chỉ mục.
CHỈ MỤC (Cây B)	Chỉ mục phụ mặc định được sử dụng cho các truy vấn phạm vi và tra cứu bằng nhau.
FULLTEXT	Được tối ưu hóa cho tìm kiếm văn bản ngôn ngữ tự nhiên với MATCH … AGAINST.
KHÔNG GIAN	Chỉ mục R-tree cho các kiểu dữ liệu GIS như POINT và POLYGON.
Băm	Tra cứu sự bằng nhau trong thời gian không đổi; được sử dụng bởi công cụ lưu trữ MEMORY.
Tổng hợp (nhiều cột)	Kết hợp nhiều cột thành một chỉ mục; tuân thủ quy tắc tiền tố bên trái nhất.

Các phương pháp hay nhất cho MySQL Chỉ số

Những thói quen dưới đây giúp các chỉ số luôn hữu ích và tránh trở thành gánh nặng.

• Chỉ mục cho mẫu truy vấn, không phải tên cột: Thêm các chỉ mục khớp với các mệnh đề WHERE, JOIN và ORDER BY thực sự, chứ không phải "mọi cột nghe có vẻ quan trọng".
• Thứ tự chỉ số tổng hợp của đồng hồ: Cột đầu tiên phải xuất hiện trong truy vấn để chỉ mục được sử dụng.
• Tránh tạo chỉ mục trùng lặp: Tiền tố hàng đầu của chỉ mục tổng hợp đã bao gồm các tìm kiếm cột đơn trên tiền tố đó.
• Kiểm tra bằng EXPLAIN: Xác nhận rằng công cụ lập kế hoạch thực sự đang chọn chỉ mục mới.
• Xóa các chỉ mục không sử dụng: sử dụng sys.schema_unused_indexes in MySQL 5.7+ để tìm các chỉ mục mà không có gì đọc được.
• Đối khớp các kiểu dữ liệu: Nếu mệnh đề WHERE so sánh một cột kiểu VARCHAR với một số, chỉ mục không thể được sử dụng do có sự chuyển đổi ngầm định.

Câu Hỏi Thường Gặp