1 số vấn đề về MySQL Performance
performance
6
Mysql
47
Database
27
White

Baka Nobita viết ngày 26/11/2018

Xin thân chào các thí chủ.
Bần tăng lại trở lại và ăn hại gấp n lần.

Dạo gần đây nhìn vào khuynh hướng tìm hiểu tech của thiên hạ, vẫn như mọi khi, các chủ để như AI, block chain vẫn là những chủ đề rất hot. Bần tăng cũng muốn hòa mình vào cùng đồng đạo võ lâm để tìm hiểu AI hay block chain để khi bàn chuyện, biết và hiểu thiên hạ nói cái gì. Sau 1 thời gian tìm hiểu qua, kết luận là: bần tăng đếch hợp với AI hay block chain. Thế nên là tạm thời PEND cái AI và block chain để quay lại tìm hiểu và giải quyết các vấn đề trước mắt.

Đặt vấn đề

THIẾU KIM TỰ (KIM - money) - nơi hiện tại bần tăng đang tu luyện, đang vận hành 1 app thương mại điện tử.
Thời điểm ban đầu, số lượng người dùng, số lượng orders ít, chạy khá là mượt. Thế nhưng rồi theo thời gian, số lượng người dùng càng ngày càng tăng, số lượng orders cũng tăng lên theo cấp số nhân, bắt đầu xảy ra các vấn đề liên quan đến performance. Về vấn đề giải quyết performance này thì trước kia 1 đồng đạo của bần tăng (Pháp danh: 12) cũng tìm hiểu và đã giải quyết được 1 phần. Nói thật là khi nghe cách giải quyết, bần tăng cũng thấy hay nhưng mà đếch hiểu. (yaoming) Thế nên là bắt đầu tìm hiểu cách up performance cho app.

Các vấn đề

Theo kinh nghiệm hạn chế của bần tăng, thì 1 application nó có cấu trúc tổng quát nó như thế này:

alt text

Theo như bần tăng thì các vấn đề liên quan đến performance nó sẽ xảy ra ở trong phase:

  • Bản thân cách implemement thuật toán trong source code
  • ① Cách mà thí chủ xây dựng và thao tác với database
  • ② Cách mà thì chủ tương tác với 1 app hoặc database của bên thứ 3 (Ko biết là gọi là 3rd party application có đúng không, nhưng ko biết gọi như thế nào khác.)

Hôm nay bần tăng sẽ nói về 1 số vấn đề mà bần tăng để ý thấy khi mà đang cày cuốn High Performance MySQL liên quan đến

① Cách mà thí chủ xây dựng và thao tác với database

Choose Optimal Data Types

Trong chương 3 (Schemal Optimization and Indexing) ghi 3 tiêu chí:

  • Smaller is usually better. (Đương cmn nhiên)
  • Simple is good. (Tưởng gì, cái này bần tăng cũng biết trước rồi)
  • Avoid NULL if possible. (Cái này mới) Nếu set 1 column là nullable → nó khó cho MySQL tối ưu các query mà có refer đến các column này, bởi vì nó làm cho việc đánh index (make indexes), thống kê index (index statistics) và việc so sánh giá trị trở nên phức tạp hơn. Hơn nữa, việc đánh index cho 1 nullable column sẽ cần thêm 1 byte thừa cho mỗi entry (extra byte) → sẽ sử dụng nhiều bộ nhớ hơn để lưu và sẽ cần 1 xử lý đặc biệt trong MySQL

Vì thế nên nếu có thể thì tránh việc sử dụng set cho 1 column là nullable. Thí chủ có thể sử dụng 1 giá trị thay thế để lưu thay cho NULL để thể hiện cho phần tử no value trong table. (Giả sử như 0, empty string, hoặc 1 giá trị đặc biệt nào đó.)

Tuy nhiên cái việc thay đổi NULL → NOT NULL nó không làm thay đổi nhiều performance, vì vậy thiên hạ bảo ko nên thay đổi schema đã có. Chỉ là khi mà tạo bảng mới hay thêm 1 column mới vào bảng đã tồn tại, thí chủ nên tránh set nullable này nếu mà muốn index column này.

Số nguyên (Whole Number)

MySQL cung cấp các kiểu số nguyên như sau:

Type Range Number of bits
TINYINT -2^7 ~ 2^7-1 8
TINYINT UNSIGNED 0 ~ 2^8-1 8
SMALLINT -2^15 ~ 2^15-1 16
SMALLINT UNSIGNED 0 ~ 2^16-1 16
MEDIUMINT -2^23 ~ 2^23-1 24
MEDIUMINT UNSIGNED 0 ~ 2^24-1 24
INT -2^31 ~ 2^31-1 32
INT UNSIGNED 0 ~ 2^32-1 32
BIGINT -2^63 ~ 2^63-1 64
BIGINT UNSIGNED 0 ~ 2^64-1 64

Nhìn qua cái này, hiện tại đa số các framework đều default tạo id với type là INT.
Nó cũng không là vấn đề gì lớn lắm. Tuy nhiên, theo bần tăng thì chúng ta nên dựa theo số lượng dự đoán của record mà nên đặt type cho hợp lý. Giả sử như là có cái bảng state để lưu tất cả các tỉnh của VN thì cũng không cần thiết phải sử dụng INT mà chỉ cần sử dụng TINYINT UNSIGNED là ok.
Hay là đối với orders của 1 trang thương mại điện tử thì nên để INT UNSIGNED thay cho INT chẳng hạn.
Chỉ những cái rất nhỏ như thế thôi nhưng nó cũng chẳng thể hiện được cái gì cả. (facepalm)

Số thực (Real number)

Cái này thì bần tăng chỉ để ý là MySQL nó cung cáp có 2 kiểu là FLOATDOUBLE, DECIMAL nữa.
Đối với những tính toán cần lưu chính xác (như kiểu là các số liệu tài chính) thì nên sử dụng DECIMAL.

Kiểu String
  • VARCHARCHAR:
    • VARCHAR sử dụng 1 hoặc 2 byte nữa (extra bytes) để lưu record length. Khi thí chủ khai báo là VARCHAR(1000) có nghĩa là có thể sử dụng MAX là 1002 bytes để lưu data.
    • CHAR: được fix độ dài. CHAR rất hữu dụng để lưu các string rất ngắn. Ví dụ: Nên dùng CHAR để lưu các giá trị MD5 cho user passwords. (Vì lúc nào cũng có độ dài giống nhau). Tại sao? Vì CHAR không cần thêm 1, 2 bytes để lưu độ dài của string giống như VARCHAR.
  • BLOBTEXT: Để lưu trử các data kiểu string với khối lượng lớn.
    • Về 2 kiểu dữ liệu này thì anh Baron (tác giả) khuyên là nên tránh sử dụng BLOB, TEXT trừ trường hợp bất khả kháng. Bởi vì các Memory store engine của MySQL không hỗ trợ BLOBTEXT. Thế nên là đối với mỗi query liên quan đến 2 kiểu dữ liệu này, MySQL tạo thêm 1 bảng temporary ngầm để lưu các giá trị kiểu này.
Sử dụng ENUM thay cho 1 string type
  • Đối với 1 column mà chỉ có 1 tập các giá trị xác định, các giá trị trong tập giá trị của nó có độ dài không khác nhau lắm thì sử dụng ENUM.
  • Đối với phép join, thì anh Baron test thử tốc độ và đưa ra kết quả sau đây (sắp xếp theo thứ tự speed giảm dần)
1. ENUM joined to ENUM
2. VARCHAR joined to VARCHAR
3. ENUM joined to VARCHAR
4. VARCHAR joined to ENUM

→ Phép join sẽ nhanh hơn sau khi convert tất cả các column to ENUM (Baron said)

Date và Time

Về kiểu dữ liệu này ko có gì để nói nhiều, nên bần tăng xin phép ko nói.

  • DATETIME
  • TIMESTAMP
Bit-packed Data Types
  • Cái phần này bần tăng cũng ko có gì nói nhiều (bởi vì ko hiểu (yaoming))
Chọn kiểu dữ liệu
  • Tại sao? Vì MySQL chọn xử lý các phép so sánh, insert, ... dựa trên kiểu dữ liêu của columns. Việc kiểu dữ liệu mà không tốt sẽ ảnh hưởng đến performance của MySQL khi xử lý trên column tương ứng.
  • Trong sách anh Baron có nêu ví dụ: Khi lưu state cho tất cả các bang của USD, ko cần thiết sử dụng INT mà chỉ cần sử dụng TINYINT là được. Baron có nói thêm 1 câu: If you use this valie as a foreign key in other tables, 3 bytes can make a difference. Để confirm lại cái speed thay đổi như thế nào khi thay đổi INTTINYINT, bần tăng đã làm như sau:

 □ Tạo 2 table shopsshops2 với cấu trúc như sau:

  CREATE TABLE shops (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    name VARCHAR(255)
  );

  CREATE TABLE shops2 (
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    name VARCHAR(255)
  );

 □ Tạo 2 table ordersorders_2

  CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    attr1 VARCHAR(20),
    attr2 BIGINT,
    attr3 DECIMAL,
    shop_id INT UNSIGNED NOT NULL,

    FOREIGN KEY (shop_id) REFERENCES shops(id),
    INDEX (shop_id)
  );

  CREATE TABLE orders_2 (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
    attr1 VARCHAR(20),
    attr2 BIGINT,
    attr3 DECIMAL,
    shop_id TINYINT UNSIGNED NOT NULL,

    FOREIGN KEY (shop_id) REFERENCES shops2(id),
    INDEX (shop_id)
  );

Khác nhau giữa shopsshops2, ordersorders_2 chỉ là kiểu của shop_id.

 □ Input data vào các table:
  ○ shopsshops2: 1 record
  ○ ordersorders_2: 2000000 record

  INSERT INTO shops (name) VALUES ("Test Shop");
  INSERT INTO shops2 (name) VALUES ("Test Shop2");

  drop procedure if exists createOrders;
  DELIMITER //
  CREATE PROCEDURE createOrders()
  BEGIN
  DECLARE i INT DEFAULT 2000000;
  SET i = 0;
  WHILE (i <= 2000000) DO
      INSERT INTO Orders (attr1, attr2, attr3, shop_id) values ("test_attr1", i, i + 1, 1);
      SET i = i+1;
      SELECT i;
  END WHILE;
  END;
  //
  DELIMITER ;

  drop procedure if exists createOrders2;
  DELIMITER //
  CREATE PROCEDURE createOrders2()
  BEGIN
  DECLARE i INT DEFAULT 2000000;
  SET i = 0;
  WHILE (i <= 2000000) DO
      INSERT INTO orders_2 (attr1, attr2, attr3, shop_id) values ("test_attr1", i, i + 1, 1);
      SET i = i+1;
      SELECT i;
  END WHILE;
  END;
  //
  DELIMITER ;

  CALL createOrders();
  CALL createOrders2();

 □ Tiến hành check cho các câu select, bần tăng được kết quả như thế này

  // Chạy 3 lần
  SELECT * FROM orders where shop_id = 1;
  2000001 rows in set (19.93 sec)
  2000001 rows in set (18.40 sec)
  2000001 rows in set (17.06 sec)

  // Chạy 3 lần
  SELECT * FROM orders_2 where shop_id = 1;
  2000001 rows in set (18.10 sec)
  2000001 rows in set (16.55 sec)
  2000001 rows in set (16.07 sec)

Dựa vào kết quả trên thấy có 1 chút khác biệt, nhưng không lớn lắm. Theo bần tăng thì nó chưa đến mức big diference như Baron nói.

Tạm thời bần tăng xin dừng ở đây. Trước khi kết thúc, bần tăng xin gửi đến các thí chủ 1 câu mà Baron và các đồng đạo của anh nhắn nhủ:

Beware of Autogenerated Schemas
(Cẩn thận với những schema được auto generate bởi các framework)

1 số chương trình sử dụng default kiểu VARCHAR lớn để lưu tất cả mọi thứ, hoặc là sử dụng các kiểu data khác nhau cho các column được sử dụng để join. Vì thế nên cần check 1 schema nếu nó được tạo 1 cách tự động.

Các hệ thống ORM (Object-oriented mapping), thường được gọi là framework, cũng là cơn ác mộng thường xuyên cho việc xử lý performance. 1 số framework cho phép các thí chủ lưu bất kì 1 kiểu dữ liệu nào vào trong backend data store → đồng nghĩa với việc không được thiết kế cho việc sử dụng các điểm mạnh của các kiểu dữ liệu khác nhau. Việc này có thể giúp cho developer đơn giản hơn trong phát triển phần mềm, thế nhưng nó sẽ là điểm chết người về performance khi mà hệ thống trở nên lớn hơn. Vì vậy, các thí chủ nên cần thận test performance cho product với số lượng data lớn giống như thật để có thể tránh được trong vấn đề performance xảy ra trong tương lai.

Thân ái và chào quyết thắng.

2018/11/25

Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

White

Baka Nobita

11 bài viết.
19 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
White
15 2
Sau 1 thời gian hơi sấp mặt 1 chút, bần tăng đã trở lại và ăn hại gấp n lần. Mở đầu Đối với các công ty cung cấp dịch vụ B2B, khách hàng là thượ...
Baka Nobita viết 4 tháng trước
15 2
White
11 6
Bần tăng, 1 dev Ruby đã có kinh nghiệm làm việc cho 1 công ty Nhật 2 năm. Trong 2 năm qua bần tăng đã học được rất nhiều điều mà bản thân ngày xưa ...
Baka Nobita viết 11 tháng trước
11 6
White
4 1
Mở đầu Gần đây các từ khóa như Machine Learning, Data mining, Kmean, AI, ... đang rất là hot trên các cộng đồng engineer. Tuy nhiên, mặc dù bần t...
Baka Nobita viết 10 tháng trước
4 1
Bài viết liên quan
White
21 0
Mysql innodb internal là một chủ đề khá sâu. Bản thân tôi cũng chưa bao quát hết. Bài viết này chỉ cung cấp một cái nhìn sơ lược. Để các bạn khôn...
manhdung viết hơn 3 năm trước
21 0
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


White
{{userFollowed ? 'Following' : 'Follow'}}
11 bài viết.
19 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!