trieu.dev.da
Nguyễn Thanh Triều
1. SQL không được đánh index
Nhiều lần, truy vấn chậm của chúng ta là do không có index . Nếu không có index, nó sẽ gây ra quá trình quét toàn bộ bảng. Do đó, bạn nên xem xét khi truy vấn các cột có điều kiện, xây dựng chỉ mục và cố gắng tránh quét toàn bộ bảng.
Câu lệnh bắt đầu bằng EXPLAIN trong hình trên là để lấy thông tin về cách MariaDB thực hiện một SELECT, UPDATE hoặc DELETE. Nhìn vào cột type bạn sẽ thấy nó có giá trị là ALL. Có nghĩa là nó đang quét toàn bộ bảng điều này là không nên đối với các bảng lớn. Ngoài ra còn một số type khác bạn có thể xem ở hình sau:
Trường hợp lí tưởng chúng ta sẽ làm như sau:
// đánh index
alter table users add index idx_name (name);
bây giời nhìn vào hình trên cột type đã chuyển thành giá trị ref. Để biết giá trị ref có ý nghĩa gì các bạn có thể xem trong phần type mình có chia sẻ ở trên.
2. Index không có hiệu lực
Đôi khi chúng ta rõ ràng thêm index, nhưng index đó không có hiệu lực. Vậy index sẽ không có hiệu lực trong các trường hợp nào?
2.1 Sử dụng điều kiện truy vấn khác kiểu dữ liệu
Chúng ta tạo một bảng users như sau
CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Trường user_id là một kiểu chuỗi và là index chung của B+ tree. Nếu một số được sử dụng trong điều kiện truy vấn index sẽ không có hiệu lực như hình sau:
2.2 Điều kiện truy vấn chứa OR, có thể khiến index bị lỗi
Chúng ta vẫn sử dụng cấu trúc bảng như trên
CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Trường user_id được đánh index, nhưng age không được đánh index. Chúng ta sử dụng kết hợp với OR và cho kết quả như sau:
Đối với truy vấn OR trường hợp age không được đánh index, giả sử rằng nó đã chuyển đến index của user_id , nhưng khi đến điều kiện try vấn age, nó phải thực hiện quét toàn bộ bảng, tức là quy trình ba bước được yêu cầu: quét toàn bộ bảng + quét index + hợp nhất . Nếu nó quét toàn bộ bảng ngay từ đầu , nó sẽ được thực hiện bằng một lần quét. Vì để tối ưu trong trường hợp này, trình tối ưu hóa Mysql gặp phải các điều kiện OR nó sẽ không sử dụng index, điều này có vẻ hợp lý.
Lưu ý : Nếu tất cả các trường của điều kiện OR đều được đánh index, thì chỉ mục có thể được sử dụng hoặc không , bạn có thể tự mình thử. Nhưng khi sử dụng, bạn vẫn phải chú ý điều kiện này và sử dụng explain phân tích. Khi gặp trường hợp tương tự, hãy xem xét tách hai SQL.
2.3. Như các ký tự đặc biệt có thể làm cho index không có hiệu lực
Không phải là sử dụng like + ký tự đặc biệt index sẽ không hợp lệ, nhưng truy vấn tương tự bắt đầu bằng % sẽ khiến index bị lỗi.
% đặt lại ở cuối thì thấy index vẫn chạy bình thường, cụ thể như sau:
2.4. Điều kiện truy vấn không đáp ứng nguyên tắc leftmost prefix của index chung
Khi MySQl tạo một index chung, nó sẽ tuân theo nguyên tắc leftmost prefix, tức là index sẽ được sử dụng để tìm kiếm các bản ghi dựa trên giá trị của các trường từ trái sang phải. Nếu bạn tạo một（a,b,c）chỉ mục chung, nó tương đương với việc tạo (a), (a,b), (a,b,c) ba chỉ mục. Giả sử bạn có cấu trúc bảng sau:
CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid_name (user_id,name) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Có một chỉ mục chung idx_userid_name, chúng ta thực thi SQL này, điều kiện truy vấn là name index không hợp lệ:
Bởi vì cột điều kiện truy vấn name không phải là idx_userid_name cột đầu tiên trong index chung, index không có hiệu lực Trong một index chung, index chỉ có hiệu lực khi các điều kiện truy vấn thỏa mãn leftmost prefix.
2.5. Sử dụng các hàm tích hợp của MySQL trên các cột được đánh index
Cấu trúc bảng:
CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT,user_id varchar(32) NOT NULL,login_time datetime NOT NULL, PRIMARY KEY (id ), KEY idx_userId (user_id ) USING BTREE, KEY idx_login_time (login_Time) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Mặc dù index login_time được thêm vào , do sử dụng hàm mysql tích hợp Date_ADD(), index này không có hiệu lực như thể hiện trong hình:
Làm thế nào để tối ưu hóa tình huống này? Logic của các hàm tích hợp có thể được chuyển sang bên phải , như sau:
2.6 Thực hiện các thao tác với các cột index (chẳng hạn như +, -, , /), index sẽ không có hiệu lực
Cấu trúc bảng:
CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT,user_id varchar(32) NOT NULL,age int(11) DEFAULT NULL, PRIMARY KEY (id ), KEY idx_age (age) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Mặc dù trường age đã được đánh index, nhưng do cách viết dưới đây, index trực tiếp bị mất. . . Như trong hình:
Do đó , không thể thao tác trên cột index, và nó có thể được xử lý trong code và sau đó được chuyển vào. ....
Nhiều lần, truy vấn chậm của chúng ta là do không có index . Nếu không có index, nó sẽ gây ra quá trình quét toàn bộ bảng. Do đó, bạn nên xem xét khi truy vấn các cột có điều kiện, xây dựng chỉ mục và cố gắng tránh quét toàn bộ bảng.
Câu lệnh bắt đầu bằng EXPLAIN trong hình trên là để lấy thông tin về cách MariaDB thực hiện một SELECT, UPDATE hoặc DELETE. Nhìn vào cột type bạn sẽ thấy nó có giá trị là ALL. Có nghĩa là nó đang quét toàn bộ bảng điều này là không nên đối với các bảng lớn. Ngoài ra còn một số type khác bạn có thể xem ở hình sau:
Trường hợp lí tưởng chúng ta sẽ làm như sau:
// đánh index
alter table users add index idx_name (name);
bây giời nhìn vào hình trên cột type đã chuyển thành giá trị ref. Để biết giá trị ref có ý nghĩa gì các bạn có thể xem trong phần type mình có chia sẻ ở trên.
2. Index không có hiệu lực
Đôi khi chúng ta rõ ràng thêm index, nhưng index đó không có hiệu lực. Vậy index sẽ không có hiệu lực trong các trường hợp nào?
2.1 Sử dụng điều kiện truy vấn khác kiểu dữ liệu
Chúng ta tạo một bảng users như sau
CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Trường user_id là một kiểu chuỗi và là index chung của B+ tree. Nếu một số được sử dụng trong điều kiện truy vấn index sẽ không có hiệu lực như hình sau:
2.2 Điều kiện truy vấn chứa OR, có thể khiến index bị lỗi
Chúng ta vẫn sử dụng cấu trúc bảng như trên
CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Trường user_id được đánh index, nhưng age không được đánh index. Chúng ta sử dụng kết hợp với OR và cho kết quả như sau:
Lưu ý : Nếu tất cả các trường của điều kiện OR đều được đánh index, thì chỉ mục có thể được sử dụng hoặc không , bạn có thể tự mình thử. Nhưng khi sử dụng, bạn vẫn phải chú ý điều kiện này và sử dụng explain phân tích. Khi gặp trường hợp tương tự, hãy xem xét tách hai SQL.
2.3. Như các ký tự đặc biệt có thể làm cho index không có hiệu lực
Không phải là sử dụng like + ký tự đặc biệt index sẽ không hợp lệ, nhưng truy vấn tương tự bắt đầu bằng % sẽ khiến index bị lỗi.
2.4. Điều kiện truy vấn không đáp ứng nguyên tắc leftmost prefix của index chung
Khi MySQl tạo một index chung, nó sẽ tuân theo nguyên tắc leftmost prefix, tức là index sẽ được sử dụng để tìm kiếm các bản ghi dựa trên giá trị của các trường từ trái sang phải. Nếu bạn tạo một（a,b,c）chỉ mục chung, nó tương đương với việc tạo (a), (a,b), (a,b,c) ba chỉ mục. Giả sử bạn có cấu trúc bảng sau:
CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(32) NOT NULL, age varchar(16) NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (id), KEY idx_userid_name (user_id,name) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Có một chỉ mục chung idx_userid_name, chúng ta thực thi SQL này, điều kiện truy vấn là name index không hợp lệ:
Bởi vì cột điều kiện truy vấn name không phải là idx_userid_name cột đầu tiên trong index chung, index không có hiệu lực Trong một index chung, index chỉ có hiệu lực khi các điều kiện truy vấn thỏa mãn leftmost prefix.
2.5. Sử dụng các hàm tích hợp của MySQL trên các cột được đánh index
Cấu trúc bảng:
CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT,user_id varchar(32) NOT NULL,login_time datetime NOT NULL, PRIMARY KEY (id ), KEY idx_userId (user_id ) USING BTREE, KEY idx_login_time (login_Time) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Mặc dù index login_time được thêm vào , do sử dụng hàm mysql tích hợp Date_ADD(), index này không có hiệu lực như thể hiện trong hình:
2.6 Thực hiện các thao tác với các cột index (chẳng hạn như +, -, , /), index sẽ không có hiệu lực
Cấu trúc bảng:
CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT,user_id varchar(32) NOT NULL,age int(11) DEFAULT NULL, PRIMARY KEY (id ), KEY idx_age (age) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Mặc dù trường age đã được đánh index, nhưng do cách viết dưới đây, index trực tiếp bị mất. . . Như trong hình: