Hướng dẫn Mysql explain and indexing strategies

[Smobi]

Khi viết 1 câu sql, ta thường explain để biết câu sql của ta có tốt hay chưa, nhưng thường ta sẽ gặp khó khăn vì để hiểu explain muốn nói gì và tối ưu hóa câu sql là cả 1 vấn đề. Nhưng trước tiên ta phải hiểu được câu explain là thế nào đã:

A) Giải thích:
1) Select Types:
SIMPLE: Câu sql cơ bản, không có sub queries và union.
PRIMARY: Câu lệnh Select cấp độ cao nhất.
DERIVED: Câu queries có liên quan đến table gốc.
UNION: Câu lệnh queries là câu > 2 trong toàn bộ câu sql.
…
Nói chugn phần này không quan trọng, chỉ nói lên bản chất của câu queries hiện tại có thể loại là gì.

2) Join Types:
const: Câu sql dùng primary key hoặc unique key => very fast. (1)
eq_ref: Câu sql dùng biểu trưng khóa ngoại kết nối khóa chính. (select cust.* from cust, comment where comment.id_cust = cust.id) => fast (2)
ref: Câu sql có dùng index bình thường (không unique) => medium (3)
ref_or_null: Câu sql có dùng index bình thường (không unique) và tìm trong cả giá trị null trong index column => medium (4)
index_merge: Câu sql có dùng nhiều index để tìm (do đặt index không đúng nên xảy ra tình trạng này). (4)
unique_subqueries: Câu sql để lấy ra giá trị từ sub queries trả về primary (unique) key. Ví dụ câu select * from node where id_cust in (select id from customers where fullname = ‘song’) sẽ trả về những record đều là primary (unique) key. (2)
index_subqueries: Tương tự unique_subqueries nhưng lần này là lấy giá trị từ giá trị trả về là index key. VD: explain select * from node where id_cust in (select id_cust from comment where comment.name = ‘song’). (3)
range: trả về 1 vùng trong những row có dùng index, thường dùng cho những câu in, between… (4)
index: giống ALL nhưng nhanh hơn ALL 1 tí. (5)
ALL: chậm nhất vì không dùng index. (6)

Ghi chú (1) -> (6): Tốc độ từ nhanh nhất (1) tới chậm nhất (6).

B) Tối ưu:
- Sử dụng mysql > 5.0. Vì bản < 5.0 không hỗ trợ index tốt.
- Dùng table với số column càng ít càng tốt, nếu phải dùng nhiều row thì áp dụng các chiến thuật phân mảnh.
- Đặt index cho các tất cả các case ta cần để tối ưu. Ví dụ: index_a_b(fieldA, fieldB), index_a_c(fieldA, fieldC), index_b_c(fieldB, fieldC), index_a_b_c(fieldA, fieldB, fieldC).
- Luôn cố gắng đưa số rows về thấp nhất có thể.
- Tránh không cho field extra có giá trị là filesort bằng cách đặt index phù hợp hoặc thay inner join bằng left join. (filesort xuất hiện khi lượng dữ liệu trả về là quá lớn hoặc do dùng inner join…)

C) Thực hành, ví dụ ta có database sau:
Mã:
CREATE TABLE IF NOT EXISTS `node` (
`id` int(10) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `node_search_city` (
`id_node` int(15) NOT NULL,
`id_item` int(3) NOT NULL,
PRIMARY KEY (`id_node`,`id_item`),
KEY `id_node` (`id_node`),
KEY `id_item` (`id_item`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `node_search_district` (
`id_node` int(15) NOT NULL,
`id_item` int(4) NOT NULL,
PRIMARY KEY (`id_node`,`id_item`),
KEY `id_node` (`id_node`),
KEY `id_item` (`id_item`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `node_search_tag_vn` (
`id_node` int(18) NOT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id_node`,`name`),
KEY `name` (`name`),
KEY `id_node` (`id_node`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Nếu ta muốn lấy ra danh sách những quán cafe nằm ở thành phố có id = 2, quận có id = 3. Thường ta sẽ viết câu sql sau:
Mã:
select j.*
from node as j
inner join node_search_tag_vn as c on c.id_node = j.id
inner join node_search_district as d on d.id_node = j.id
inner join node_search_city as t on t.id_node = j.id
where d.id_item = 3 and t.id_item = 2 and c.name = 'cafe'
group by j.id
order by j.id desc
-- 20 record / 0.69s

Nhìn vào câu explain thì ta có thể thấy mysql khá thông minh khi nhận ra nếu bắt đầu lọc từ table district thì số row trả về sẽ luôn là thấp nhất(469). Nhưng khi mysql auto như vậy thì câu lệnh group by và order by của ta sẽ mất index vì lúc này trật tự đã đảo lộn.
Vậy để xử lý thì tùy vào business logic, nếu ta chú trọng vào city thì đặt key chính ở city, district thì đặt key chính ở district, ở đây mình chú trọng vào name => mình sẽ làm như sau đầu tiên lấy ra xét key word là 'cafe' rồi order by, group by tại đó luôn, sau đó sẽ xét tới các yếu tố khác như city, district...
Khi đã xác định được luồng đi của câu sql, thì ta có thể viết câu sql như sau:
Mã:
select j.id from (select result.id_node from (select c.id_node from (select id_node from node_search_tag_vn as c where c.name = 'cafe' group by c.id_node order by c.id_node desc) as c inner join node_search_city as t on t.id_node = c.id_node and t.id_item = 2) as result inner join node_search_district d on d.id_node = result.id_node and d.id_item = 3) as result
inner join node as j on result.id_node = j.id
-- 20 record / 0.02s (Nhanh ~ 30 lần câu sql đầu)

Với cùng 1 kết quả trả về nhưng câu sql sau mà ta vừa viết có thời gian thực thi nhanh gấp 20 lần câu 1, nguyên nhân là vì lúc này index của ta đã được kích hoạt, và ta có thể order by, group by theo đúng ý định của mình => Performance của project sẽ tăng 1 cách đáng kể.