MySQL INT vs TINYINT performance test

Database

39

Mysql

95

TIL

797

Rey viết ngày 27/11/2018

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