TL;DR: Cách khắc phục nhanh
MySQL từ chối đánh chỉ mục (index) toàn bộ nội dung của cột TEXT hoặc BLOB vì những trường này có thể chứa lượng dữ liệu khổng lồ. Để sửa lỗi Error 1170, bạn phải xác định độ dài tiền tố (prefix length)—một số lượng ký tự cụ thể để MySQL theo dõi.
Thay vì câu lệnh bị lỗi này:
CREATE INDEX idx_description ON products(description);
Hãy sử dụng cú pháp sau để chỉ đánh chỉ mục cho 191 ký tự đầu tiên:
CREATE INDEX idx_description ON products(description(191));
Tại sao lỗi này xảy ra?
Các công cụ lưu trữ (storage engine) như InnoDB có giới hạn nghiêm ngặt về độ dài khóa chỉ mục. Trong khi một cột LONGTEXT có thể lưu trữ 4GB dữ liệu, giới hạn chỉ mục nội bộ của InnoDB thường là 767 byte (cho các định dạng cũ) hoặc 3072 byte (cho các định dạng DYNAMIC hiện đại). Nếu MySQL cố gắng đánh chỉ mục toàn bộ trường 4GB, hiệu suất cơ sở dữ liệu của bạn sẽ sụt giảm nghiêm trọng và kích thước các tệp chỉ mục sẽ bùng nổ.
Bằng cách yêu cầu độ dài tiền tố, MySQL đảm bảo chỉ mục của bạn luôn gọn nhẹ. Về cơ bản, bạn đang nói với cơ sở dữ liệu rằng: "Chỉ cần nhìn vào phần đầu của chuỗi này để xác định vị trí dữ liệu." Điều này giúp việc tra cứu nhanh chóng mà không gặp phải các nút thắt cổ chai về phần cứng.
Ba cách để khắc phục lỗi 1170
1. Triển khai Chỉ mục Tiền tố (Prefix Index)
Hầu hết các nhà phát triển giải quyết vấn đề này bằng cách chọn một độ dài cân bằng giữa tính duy nhất và hiệu suất. Lựa chọn phổ biến thường là 191 hoặc 255 ký tự.
Sử dụng ALTER TABLE:
ALTER TABLE articles ADD INDEX (content(255));
Sử dụng CREATE INDEX:
CREATE INDEX idx_title_prefix ON blog_posts (title(100));
Lưu ý kỹ thuật: Nếu bạn sử dụng bảng mã utf8mb4, MySQL sẽ dự phòng 4 byte cho mỗi ký tự. Tiền tố 191 ký tự tương đương với 764 byte (191 * 4), nằm trong ngưỡng an toàn dưới giới hạn 767 byte cổ điển thường thấy trong các cấu hình MySQL cũ.
2. Chuyển đổi cột sang VARCHAR
Bạn có thực sự cần một cột TEXT không? Nếu dữ liệu của bạn thường nằm trong khoảng 255 hoặc 500 ký tự, VARCHAR là một lựa chọn tốt hơn. Không giống như TEXT, các cột VARCHAR không yêu cầu độ dài tiền tố để đánh chỉ mục, miễn là tổng kích thước nằm trong giới hạn của engine.
-- Chuyển từ TEXT sang VARCHAR(255)
ALTER TABLE users MODIFY COLUMN bio VARCHAR(255);
CREATE INDEX idx_bio ON users(bio);
3. Sử dụng FULLTEXT cho các tìm kiếm chuyên sâu
Các chỉ mục B-tree tiêu chuẩn rất tốt cho các tìm kiếm khớp chính xác, nhưng chúng gặp khó khăn với các tìm kiếm dạng "chứa từ khóa" (contains). Nếu bạn đang xây dựng thanh tìm kiếm cho các bài viết blog, chỉ mục tiền tố sẽ không giúp ích nhiều. Thay vào đó, hãy chuyển sang chỉ mục FULLTEXT; nó không yêu cầu độ dài tiền tố và xử lý các khối văn bản lớn một cách hiệu quả.
ALTER TABLE products ADD FULLTEXT(description);
Lưu ý rằng các chỉ mục FULLTEXT yêu cầu cú pháp cụ thể. Bạn sẽ cần sử dụng MATCH() ... AGAINST() trong các truy vấn SQL của mình thay vì các mệnh đề WHERE thông thường.
Tìm "điểm vàng" cho độ dài tiền tố
Đừng đoán mò độ dài chỉ mục. Bạn có thể sử dụng toán học để tìm ra con số hiệu quả nhất. Mục tiêu của bạn là tìm một độ dài mà tại đó các tiền tố có tính duy nhất gần như tương đương với toàn bộ chuỗi.
Hãy so sánh các số liệu thống kê này để xem mức độ duy nhất thay đổi như thế nào:
-- 1. Tổng số giá trị duy nhất trong bảng
SELECT COUNT(DISTINCT description) FROM products;
-- 2. Các giá trị duy nhất nếu chúng ta chỉ xem xét 10 ký tự đầu tiên
SELECT COUNT(DISTINCT LEFT(description, 10)) FROM products;
-- 3. Các giá trị duy nhất nếu chúng ta xem xét 20 ký tự đầu tiên
SELECT COUNT(DISTINCT LEFT(description, 20)) FROM products;
Nếu số lượng cho 20 ký tự đạt 99% tổng số lượng duy nhất, thì 20 là độ dài tiền tố hoàn hảo và mang lại hiệu suất cao.
Cách xác minh các thay đổi của bạn
Sau khi bạn đã áp dụng bản sửa lỗi, hãy kiểm tra lại cấu trúc chỉ mục. Chạy lệnh sau:
SHOW INDEX FROM table_name;
Kiểm tra cột Sub_part. Nó sẽ hiển thị số nguyên mà bạn đã chỉ định (ví dụ: 191). Nếu nó hiển thị NULL, nghĩa là chỉ mục bao phủ toàn bộ cột—điều này chỉ xảy ra với các kiểu dữ liệu không phải BLOB.
Sự đánh đổi về hiệu suất
- Giữ cho tiền tố ngắn gọn: Tiền tố lớn hơn làm cho chỉ mục nặng hơn và làm chậm các thao tác
INSERT. Chỉ đánh chỉ mục những gì bạn cần. - Tránh sử dụng BLOB làm Khóa chính: Sử dụng cột
TEXTlàm Khóa chính (Primary Key) là một dấu hiệu cảnh báo về mặt kiến trúc. Hãy trung thành vớiBIGINT AUTO_INCREMENThoặcUUIDđể có hiệu suất tốt hơn. - Vấn đề về sắp xếp: MySQL thường không thể sử dụng chỉ mục tiền tố cho các thao tác
ORDER BYhoặcGROUP BY. Nếu bạn thường xuyên sắp xếp theo cột này, hãy cân nhắc thiết kế lại schema.