Bạn có chắc chắn muốn xóa bài viết này không ?
Bạn có chắc chắn muốn xóa bình luận này không ?
MySQL INT vs TINYINT performance test
MySQL INT vs TINYINT performance test
TIL này ban đầu là 1 comment cho bài viết 1 số vấn đề về MySQL Performance, nhưng vì quá dài nên mình phải viết thành bài riêng.
Trong bài viết trên, Baka Nobita có trích dẫn 1 đoạn trong cuốn sách High Performance MySQL:
A TINYINT should be sufficient and is three bytes smaller. If you use this value as a foreign key in other tables, three bytes can make a big difference
Tác giả cuốn sách cho rằng, bằng việc dùng TINYINT thay cho INT đặc biệt cho các khoá ngoại, sẽ tạo ra một sự khác biệt lớn
. Tác giả bài viết có làm một bài test và không thấy khác biệt bao nhiêu cả.
TIL này là một số suy nghĩ của mình về test đó:
- INT lớn hơn TINYINT 3 bytes, 2M rows thì chênh lệch nhau 6Mb, shop_id xuất hiện 2 nơi (trong table shops và khoá ngoại ở orders) nên tổng chênh lệch là 12Mb, kích thước của table là khoảng 130M, chênh lệch chỉ là 9% về mặt kích thước.
- Explain query trong bài viết thì thấy mysql dùng index scan trên shop_id, mình nghĩ ý của tác giả khi nói về khoá ngoại ở đây là các trường hợp join query hơn.
explain SELECT * FROM orders where shop_id = 1;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+---------+----------+-------+
| 1 | SIMPLE | orders | NULL | ref | shop_id | shop_id | 4 | const | 2914209 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+---------+----------+-------+
P/s: btw, sao mysql lại dùng index scan cho câu query trên nhỉ, full table scan sẽ nhanh hơn nhiều chứ,
explain select * from orders_2 where shop_id = 1;
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-------+
| 1 | SIMPLE | orders_2 | NULL | ref | shop_id | shop_id | 1 | const | 996761 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+-------+
index scan chạy hết 10s
explain select * from orders_2 ignore index (shop_id) where shop_id = 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | orders_2 | NULL | ALL | NULL | NULL | NULL | NULL | 1993522 | 100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
full table scan chạy hết 2.2s
Mình muốn test thử với join với query như sau:
select *
from shops s
join orders o on s.id = o.shop_id
where shop_id = 1;
Với 2M records như trong bài viết, MySQL sẽ dùng full table scan
cho cả 2 table, sau đó inner join
. Mình muốn test sử dụng index trên khoá ngoại nên đã insert thêm shop id = 2 và 4M records nữa.
Dưới đây là kết quả
explain select * from shops s join orders o on s.id = o.shop_id where shop_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+---------+----------+-------+
| 1 | SIMPLE | s | NULL | const | id | id | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | ref | shop_id | shop_id | 4 | const | 2914209 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+---------+----------+-------+
Chạy 3 lần: 11.56s, 10.05s, 11.55s
explain select * from shops2 s join orders_2 o on s.id = o.shop_id where shop_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+---------+----------+-------+
| 1 | SIMPLE | s | NULL | const | id | id | 1 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | o | NULL | ref | shop_id | shop_id | 1 | const | 2914844 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+---------+----------+-------+
Chạy 3 lần: 10.2, 11.21s, 12.2s
Kết luận là trong test này, với việc thay INT bằng TINYINT size table giảm 9%, size index giảm 17%, nhưng performance thì ko thấy cải thiện mấy như tác giả cuốn High Performance MySQL nói.
Rey 27-11-2018




