Lại chuyện index mysql – leftmost prefix

Note: nếu bạn muốn tìm hiểu về chủ đề mysql thì here you go https://blog.haonheo.com/?s=mysql

Tôi có 1 bảng như sau

Tôi đánh các index như sau:

Trong document của mysql nói rằng index trong mysql là leftmost prefix ví dụ đánh index (a,c,b) thì nó sẽ được dùng trong trường hợp bạn tìm kiếm (a), (a,c), (a,b,c) và sẽ không được dùng khi bạn tìm kiếm (a,b), (b), (c), (b,c)

Nhưng thật kỳ lạ khi bạn EXPLAIN câu lệnh tìm kiếm với điều kiện chứa (a,b) thì mysql vẫn nói rằng nó đang dùng index (a,c,b)

EXPLAIN
SELECT *
FROM test 
WHERE a=0 AND b=1;

=======RESULT 01======
id		:1
select_type	:SIMPLE
table		:test
type		:ref
posible_keys	:test_acb_IDX
key		:test_acb_IDX
key_len		:4
ref		:const
row		:60960
filtered	:10.0
Extra		:Using index condition

Bạn bắt đầu thấy thế giới thật giả dối, tới document mysql còn lừa bạn. Stop, thực ra không ai lừa ai ở đây cả. Hãy chú ý tới trường Extra đang thể hiện là Using index condition. Using index condition có nghĩa là nó đang dùng "ké" index vì đang tìm kiếm a và b mà có sẵn 1 cái index đang có a ở đầu. Nếu a thỏa mãn điều kiện thì nó sẽ phải đọc cả row trong DB và tìm xem b trong đó có thỏa mãn điều kiện không. Using index condition sẽ có cost cao hơn Using index rất nhiều do Using index sẽ chỉ tìm trong index thôi.

Bạn có thể thấy ngay khi tìm kiếm theo đúng index sẽ có các thông số đẹp hơn như bên dưới:

• ref của RESULT 01 chỉ có 1 const trong khi nếu tìm kiếm theo đúng chuẩn là (a,c) như RESULT 02 sẽ là const, const như bên dưới
• số row scan ở RESULT 01 tận 60960 trong khi nếu theo đúng như RESULT 02 chỉ có 1.
• filtered ở RESULT 01 chỉ 10 còn ở RESULT 02 tận 100. 100 nghĩa là không phải scan row nào trong table cả (link).

EXPLAIN
SELECT *
FROM test
WHERE a=2 AND c=2;

=======RESULT 02======
id		:1
select_type	:SIMPLE
table		:test
type		:ref
posible_keys	:test_acb_IDX
key		:test_acb_IDX
key_len		:9
ref		:const,const
row		:1
filtered	:100
Extra		:NULL

Khi bạn tìm theo (b,c), lúc này không còn a ở đầu nữa nên sẽ không được dùng ké index (a,c,b) dẫn tới không còn index nào được sử dụng (RESULT 03)

EXPLAIN
SELECT *
FROM test
WHERE b=2 AND c=2;

=======RESULT 03======
id		:1
select_type	:SIMPLE
table		:test
type		:ALL
posible_keys	:NULL
key		:NULL
key_len		:NULL
ref		:NULL
row		:121,921
filtered	:1
Extra		:Using where

Bạn thấy đấy, đánh index là cả nghệ thuật đúng không nào ?