[Mysql] Đánh Index cho hiệu năng cao - P3

Handling hash collisions. Khi bạn search một giá trị nào đó = hash của nó, bạn phải thêm cả nó vào where clause:

mysql> SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com") AND url="http://www.mysql.com";

Câu query sau đây sẽ không hoạt động chính xác được, bởi vì nếu URL khác mà có CRC32() cũng như vậy thì nó sẽ trả về > 2 record:

mysql> SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com");

Xác suất để sảy ra hash collision tăng lên rất nhanh hơn bạn suy dựa theo bài toán Birthday Paradox. CRC32() trả về 32-bit integer, xác suất sảy ra collision là 1% với khoảng 93,000 row. Để minh họa điều này, chúng tôi load tất cả các từ của từ điển cùng với hash CRC32() của chúng, kết quả được 98,569 row. Chỉ có một collision trong dataset.

mysql> SELECT word, crc FROM words WHERE crc = CRC32('gnu');

+---------+------------+

| word | crc |

+---------+------------+

| codding | 1774765869 |

| gnu | 1774765869 |

+---------+------------+

Câu query đúng phải như sau:

mysql> SELECT word, crc FROM words WHERE crc = CRC32('gnu')AND word = 'gnu';

+------+------------+

| word | crc |

+------+------------+

| gnu | 1774765869 |

+------+------------+

Để tránh vấn đề với collision, bạn phải cụ thể hóa 2 điều kiện của where. Nếu collision không phải là vấn đề - ví dụ, bởi vì bạn đang làm 1 query thống kê và bạn không cần kết quả chính xác - bạn có thể làm đơn giản và sử dụng 1 số lợi điểm. Bằng cách sử dụng CRC32() trong where clause. Bạn còn có thể sử dụng FNV64() function.

Spatial (R-Tree) indexes

MyISAM support spatial index, bạn có thể sử dụng thông số dạng tọa độ. Không giống B-Tree index, spatial index không yêu cầu where clause phải thực hiện từ bên trái cùng sang của index. Nó đánh index theo tất cả các chiều cùng 1 lúc, ví dụ có 3 column thì đánh 3 chiều index. Với kết quả đó, look up có thể sử dụng bất kỳ cách kết hợp nào giữa các chiều 1 cách hiệu quả. Tuy nhiên bạn phải sử dụng MySQL GIS function kiểu như MBRCONTAINS() cho công việc tìm kiếm và MySQL support GIS không tốt lắm, phần lớn mọi người không dùng nó. Giải pháp toàn vẹn là sử dụng PostGIS của PostgreSQL.

Full-text indexes

FULLTEXT là 1 trường hợp đặc biệt của index, nó chỉ tìm keywork xuất hiện trong value chứ không phải là so sánh toàn diện với value. Full-text searching hoàn toàn khác với những loại matching khác. Nó có rất nhiều sự tinh tế, ví dụ như stopwords, stemming và plurals và boolean searching.

Một column có full-text index không có nghĩa là nó loại trừ B-Tree index. Full-text index dùng toán tử MATCH AGAINST chứ không phải toán tử WHERE.

Other types of index

Một số loại từ third-party storage engine sử dụng những kiểu cấu trúc dữ liệu khác nhau có index. Ví dụ, TokuDB sử dụng fractal tree index. Đây là một structure mới được phá triển với những ưu điểm của B-Tree mà không mắc phải các nhược điểm của nó. Nếu như bạn đọc chapter này bạn sẽ biết chúng tôi nhắc đến InnoDB rát nhiều. Trong hầu hết các trường hợp thảo luận của InnoDB áp dụng dược với TokuDB.

ScaleDB sử dụng Patricia tries, và những kỹ thuật khác ở InfiniDB hay Infobright có một loại structure đặc biệt cho optimizing query.

Benefits of Indexes

Index cho phép server phi một cách nhanh chóng đến vị trí nào đó trên talbe. Nhưng không phải lúc nào chúng cũng là lựa chọn tốt. Như những thông tin mà bạn đã tích tụ đến giờ, index có nhiều ưu nhược dựa trên data structure của nó.

B-Tree index là loại index hay dùng nhất, có thể dùng order được vì data sort theo order. Bởi vì data được sort nên những giá trị nào gần nhau được nằm cạnh nhau. 1.Index làm giảm lượng data server phải truy vấn. 2.Index giúp server tránh việc sắp xếp và sử dụng bảng tạm. 3.Index chuyển random I/O thành sequential I/O

Chủ đề này rất có ích trong toàn bộ quyển sách. Cho những ai muốn đào sâu hơn nữa, chúng tôi đề nghị đọc Relational Database Index Design and the Optimizers của tác giả Tapio Lahdenmaki và Mike Leach (Wiley). Nó có những topic giải thích cách tính cost và benefit của index, làm sao để ước tính tốc độ query, làm sao để xác định khi nào thì cái giá sử dụng index đắt hơn so với quản lý, update index.

Sách của Lahdenmaki and Leach còn giới thiệu hệ thống three-star để xem index câu query có phù hợp với index hay không. Index có được 1 star nếu những cột liên quan đến nhau được nằm cạnh nhau, star thứ 2 nếu row được sort theo thứ thự mà query cần, và cuối cùng nếu nó bao gồm tất cả các column mà query cần.

Chúng tôi sẽ quay trở lại phương pháp đánh giá này trong suốt chapter.

Is an Index the Best Solution?

Index không phải lúc nào cũng là công cụ thích hợp. Giữ trong đầu rằng index chỉ hiệu quả nhất khi giúp storage engine tìm row. Với những bảng nhỏ, thì sẽ có lợi hơn khi đọc tất cả row của table. Cho bảng vừa, index là hợp lý nhất. Cho bảng khổng lồ có thể gây ra overhead cho index. Trong trường hợp đó bạn phải lựa chọn kỹ thuật định danh index bằng một tập các column thay vì sử dụng mỗi column 1 index.

Indexing Strategies for High Performance

Tạo index và sử dụng 1 cách đúng đắn là cái cơ bản của query performance. Chúng tôi vừa giới thiệu sự khác biệt của các loại index và điểm mạnh điểm yếu của chúng. Bây giờ hãy xem cách sử dụng index hiệu quả thực sự.

Có rất nhiều cách để chọn index hiệu quả, bởi vì có rất nhiều trường hợp tối ưu và những hành vi khác nhau. Lý giải sử dụng cái nào vào khi nào và đánh giá hiệu năng của cái được chọn là những skill bạn sẽ học qua nhiều thời gian. Section tiếp theo giúp bạn hiểu cách sử dụng index hiệu quả.

Isolating the Column

Chúng tôi thường thấy những câu query sử dụng sai cách index trong MySQL. MySQL thường không thể sử dụng index column trừ khi column nó đứng độc lập trong câu query. Độc lập có nghĩa là nó không nên là một thành phần nào của biểu thức hay function trong câu query. Ví dụ, đây là câu query không thể sử dụng được index của actor_id:

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

Một người bình thường có thể dễ dàng nhận ra mệnh đề WHERE bên trên tương đương với actor_id = 4, nhưng MySQL không thể tự biết là actor_id = 4. Bạn phải tự làm thôi. Bạn nên có thói quen làm đơn giản mệnh đề WHERE, như vậy index sẽ được đứng một mình 1 bên của toán tử so sánh. Đây là một ví dụ khác về lỗi chung đó:

mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;