Optimization and Indexes (part 1)

How MySQL Uses Indexes

Index được sử dụng để tìm các bản ghi theo column có giá trị cụ thể một cách nhanh chóng. Nếu không có index, Mysql phải bắt đầu từ bản ghi đầu tiên sau đó đọc qua toàn bộ bảng để tìm các bản ghi có liên quan.
Kích thước bản ghi của bảng càng lớn thì chi phí tìm kiếm càng cao. Nếu bảng có index, MySql có thể nhanh chóng xác định vị trí để tìm kiếm, cách tìm kiếm này rõ ràng nhanh hơn nhiều so với việc tìm kiếm tuần tự. Hầu hết các index của MySql (PRIMARY KEY, UNIQUE, INDEX, và FULLTEXT) được lưu trữ trong B-trees. Ngoại lệ, các index trên kiểu dữ liệu không gian (spatial data type) sử dụng R-trees, các bảng MEMORY hỗ trợ chỉ mục băm (hash indexes), InnoDB sử dụng danh sách đảo ngược cho FULLTEXT indexes.

MySQL sử dụng index cho các hoạt động:

• Để tìm các bản ghi thỏa mãn với mệnh đề WHERE một cách nhanh chóng.
• Để loại bỏ các bản ghi từ việc lựa chọn khi một bảng có nhiều index. Nếu có một sự lựa chọn giữa nhiều index, MySQL thường sử dụng index mà kết quả tìm thấy số hàng nhỏ nhất (the most selective index).
• Để lấy các bản ghi từ các bảng khác nhau khi thực hiện join. MySQL có thể sử dụng index trên cột hiệu quả hơn nếu chúng được khai báo là cùng loại và kích cỡ. Trong ngữ cảnh này, VARCHAR và CHAR được coi là giống nhau nếu chúng được khai báo là cùng một kích cỡ. Ví dụ, VARCHAR (10) và CHAR (10) có cùng kích cỡ, nhưng VARCHAR (10) và CHAR (15) thì không.
• Để so sánh giữa các cột có kiểu dữ liệu là nonbinary, cả hai cột nên sử dụng bộ ký tự giống nhau. Ví dụ: so sánh cột utf8 với một cột latin1 loại trừ việc sử dụng một chỉ mục.
• So sánh các cột không giống nhau (ví dụ như so sánh giữa một string column với numeric column, hoặc date column...) có thể ngăn việc sử dụng các index nếu các giá trị không thể so sánh trực tiếp mà không cần chuyển đổi. Đối với một giá trị nhất định như 1 (số một) trong kiểu numeric column, nó có thể so sánh với bất kỳ giá trị nào trong string column như '1', '1', '00001' hoặc '01 .e1 '. Điều này loại trừ việc sử dụng bất kỳ index nào cho string column.
• Để tìm giá trị MIN () hoặc MAX () cho một cột 'key_color' đã được đánh index cụ thể. Điều này được tối ưu hóa bởi một tiền xử lý để kiểm tra xem bạn đang sử dụng 'WHERE key_part_N = constant' trên tất cả các key xảy ra trước key_col. Trong trường hợp này, MySQL thực hiện một tìm kiếm khóa duy nhất cho mỗi biểu thức MIN () hoặc MAX () và thay thế nó bằng một hằng số. Nếu tất cả các biểu thức được thay thế bằng các hằng số, truy vấn sẽ trả về cùng một lúc. Ví dụ:

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

Trong một số trường hợp, truy vấn có thể được tối ưu hóa để lấy ra các giá trị (đó là khi sử dụng một chỉ mục mà trả về tất cả các kết quả của truy vấn, và được gọi là chỉ mục bao trùm - covering index). Nếu một query sử dụng chỉ một cột và cột này bao gồm một số chỉ mục, thì truy vấn trên cây chỉ mục (index tree) sẽ có tốc độ tốt hơn.

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1

Primary Key Optimization

Khóa chính của một bảng thể hiện cột hoặc tập hợp các cột mà ta sử dụng trong các truy vấn quan trọng nhất. Nó có một chỉ mục quan hệ (associated index), giúp cho hiệu suất truy vấn nhanh. Việc sử dụng NOT NULL tối ưu hóa cho các truy vấn, bởi vì nó không thể bao gồm bất kỳ giá trị NULL. Với công cụ lưu trữ InnoDB, dữ liệu bảng được tổ chức thực tế để thực hiện tìm kiếm cực nhanh dựa trên khóa chính hoặc các cột.
Nếu bảng của ta lớn và quan trọng, nhưng không có cột hoặc nhóm cột rõ ràng để sử dụng làm khóa chính, ta có thể tạo thêm một cột riêng biệt với giá trị tăng tự động để sử dụng làm khóa chính. Những ID duy nhất này có thể phục vụ như các con trỏ tới các hàng tương ứng trong các bảng khác khi ta join bảng bằng các khoá ngoại.

Foreign Key Optimization

Nếu một bảng có nhiều cột, và truy vấn kết hợp nhiều cột khác nhau, có thể phân chia các cột dữ liệu được sử dụng thường xuyên thành bảng (hoặc các bảng) riêng biệt và liên kết chúng lại với bảng chính. Bằng cách đó, mỗi bảng nhỏ có thể có một khóa chính để tra cứu nhanh dữ liệu của nó, và ta có thể truy vấn tập các cột mà ta cần sử dụng một thao tác nối.
Tùy thuộc vào cách dữ liệu được phân phối, các truy vấn có thể thực hiện ít I / O và mất ít bộ nhớ cache vì các cột có liên quan được đóng gói lại với nhau trên đĩa. (Để tối đa hóa hiệu suất, các truy vấn cố gắng đọc từ đĩa càng ít khối dữ liệu càng tốt)

Column Indexes

Loại index phổ biến nhất thường là một single column, cho phép tra cứu nhanh các bản ghi có giá trị tương ứng với cột tương ứng. Cấu trúc dữ liệu B-tree cho phép chỉ mục nhanh chóng tìm ra một giá trị cụ thể, một tập các giá trị hoặc một dải các giá trị, tương ứng với các toán tử như =, >, ≤, BETWEEN, IN, v.v., trong mệnh đề WHERE. Số chỉ mục tối đa cho mỗi bảng và chiều dài chỉ mục tối đa được xác định cho mỗi công cụ lưu trữ. Tất cả các công cụ lưu trữ đều hỗ trợ ít nhất 16 chỉ mục cho mỗi bảng và tổng chiều dài chỉ mục ít nhất 256 byte. Hầu hết các cơ chế lưu trữ có giới hạn cao. Để biết thêm thông tin về các column index, xem phần "CREATE INDEX Syntax".

Index Prefixes

With col_name(N) syntax in an index specification for a string column, you can create an index that uses only the first N characters of the column. Indexing only a prefix of column values in this way can make the index file much smaller. When you index a BLOB or TEXT column, you must specify a prefix length for the index. For example:
Với cú pháp col_name (N) trong một đặc tả chỉ mục cho một string column, bạn phải chỉ định độ dài cho chỉ mục. Theo cách này có thể làm cho các index file nhỏ hơn nhiều. Khi bạn lập chỉ mục một cột BLOB hoặc TEXT, bạn phải chỉ định độ dài cho chỉ mục. Ví dụ:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Prefixes có thể dài tối đa 1000 byte (767 byte cho các bảng InnoDB, trừ khi bạn có bộ innodb_large_prefix).

Note: Giới hạn prefixes được tính bằng byte, trong khi độ dài prefixes trong các câu lệnh CREATE TABLE, ALTER TABLE và CREATE INDEX được hiểu là số ký tự cho các kiểu (CHAR, VARCHAR, TEXT) và số byte cho các kiểu chuỗi nhị phân (BINARY, VARBINARY, BLOB).

Để biết thêm thông tin về các tiền tố chỉ mục, xem "CREATE INDEX Syntax".

FULLTEXT Indexes

FULLTEXT Indexes được sử dụng cho tìm kiếm toàn văn. Chỉ có các công cụ lưu trữ InnoDB và MyISAM hỗ trợ các FULLTEXT Indexes và chỉ cho các cột có kiểu CHAR, VARCHAR, và TEXT. Để biết chi tiết, xem phần “Full-Text Search Functions”.

Tối ưu hóa áp dụng cho một số loại truy vấn FULLTEXT với các bảng InnoDB duy nhất đặc biệt hiệu quả với:

• Truy vấn FULLTEXT chỉ trả về document ID, hoặc document ID và thứ hạng tìm kiếm.
• Truy vấn FULLTEXT sắp xếp (sort) các bản ghi phù hợp theo thứ tự giảm dần và áp dụng một khoảng LIMIT. Áp dụng cách tối ưu này khi không có mệnh đề WHERE và chỉ dùng một mệnh đề ORDER BY DESC.
• Truy vấn FULLTEXT chỉ truy xuất COUNT (*) giá trị của bản ghi phù hợp với cụm từ tìm kiếm, không có mệnh đề WHERE bổ sung. Mệnh đề WHERE khi đó như là "WHERE MATCH (text) AGAINST ('other_text')", mà không có bất kỳ toán tử so sánh > 0 nào.

Đối với các truy vấn có chứa các full-text expressions (biểu thức), MySQL đánh giá các biểu thức đó trong giai đoạn tối ưu hóa của việc thực hiện truy vấn.
Khi dùng EXPLAIN, chúng ta thấy các full-text queries thường chậm hơn so với các non-full-text queries. EXPLAIN đối với full-text queries hiển thị "Select table" được tối ưu hóa trong cột "Extra" do sự kết hợp xảy ra trong quá trình tối ưu hoá.

Spatial Indexes

Ta có thể tạo index trên kiểu dữ liệu không gian (spatial data types). MyISAM và InnoDB hỗ trợ R-tree cho kiểu dữ liệu này . Các cơ chế lưu trữ khác sử dụng B-tree để đánh index (ngoại trừ ARCHIVE, nó không hỗ trợ spatial type indexing)

Indexes in the MEMORY Storage Engine

Cơ chế lưu trữ trên MEMORY (MEMORY storage engine) mặc định sử dụng HASH index, nhưng nó cũng hỗ trợ B-Tree index.

Multiple-Column Indexes

MySQL có thể tạo một index trên nhiều column. Một index có thể được tạo tối đa bởi 16 column. MySQL có thể sử dụng index này cho truy vấn - cái mà sẽ kiểm tra tất cả các cột trong index. hoặc truy vấn chỉ kiểm tra cột đầu tiên, hai cột đầu tiên, hoặc 3 cột đầu tiên ... Nếu bạn xác định các cột đúng theo thứ tự khi định nghĩa index, thì index này có thể tăng tốc độ một số truy vấn trên bảng. Một multiple-column index có thể được coi là một mảng được sắp xếp, các bản ghi trong đó chứa các giá trị được tạo ra bằng cách nối các giá trị của các cột được đánh index

Note Một thay thế tổ hợp index, đó là khi ta có thể đưa ra một cột mới - cái mà được "hashed" dựa trên thông tin từ các cột khác. Nếu cột này ngắn, hợp lý, và được đánh index, nó sẽ nhanh hơn là một tổ hợp index. Trong MySQL, cách thay thế này rất dễ dàng với cú pháp:

SELECT * FROM tbl_name
    WHERE hash_col = MD5(CONCAT(val1, val2))
    AND col1 = val1 AND col2 = val2;

Giả sử rằng một bảng được chỉ định như dưới đây:

CREATE TABLE test (
    id INT NOT NULL,
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id), 
    INDEX name (last_name, first_name)
);

name là một index dựa trên 2 cột là lastname và firstname. Index có thể được sử dụng để tìm kiếm trong truy vấn - cái mà sẽ xác định giá trị trong một phạm vi đã biết kết hợp với giá trị lastname và firstname. Nó cũng có thể được sử dụng cho các truy vấn - cái mà chỉ xác định giá trị last_name bởi vì cột đó là một tiền tố trái của chỉ mục (cùng xem phần mô tả dưới đây). Do đó, chỉ mục name được sử dụng để tìm kiếm trong các truy vấn sau:

SELECT * FROM test WHERE last_name = 'Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

nhưng chỉ mục name sẽ không được sử dụng để tìm kiếm trong các truy vấn sau:

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

Giả sử rằng ta đưa ra câu truy vấn SELECT sau:

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

Nếu multipe-column index chứa col1 và col2, thì các bản ghi thích hợp có thể được fetch trực tiếp. Nếu col1 và col2 là hai index tách rời, thì trình tối ưu sẽ cố gắng sử dụng tối đa index thích hợp nhất (Index merge optimization), hoặc loại trừ index nào kém hiệu quả hơn bằng cách xác định số bản ghi trả về. Nếu bảng có multipe-column index, thì bất kỳ tiền tố trái nào của chỉ mục cũng có thể sử dụng để tra cứu. Ví dụ, nếu chỉ mục được tạo bởi 3 cột (col1, col2, col3). Bạn đã lập chỉ mục khả năng tìm kiếm trên (col1), (col1, col2) và (col1, col2, col3) MySql không thể sử dụng chỉ mục để tra cứu nếu chúng không tạo thành tiền tố trái của chỉ mục. Ví dụ :

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

Nếu một index chứa (col1, col2, col3), thì chỉ 2 truy vấn đầu là sử dụng index. Truy vấn thứ ba và thứ tư có liên quan đến cột được lập chỉ mục, nhưng nó không sử dụng được index để tìm kiếm bởi vì (col2) và (col2, col3) không tạo nên tiền tố trái của (col1, col2, col3).