Tối ưu hóa schema và data type trong MySQL - P1

Bài đăng này đã không được cập nhật trong 8 năm

Thiết kế tốt về cả logic lẫn physic là những cột mốc quan trọng đem tới hiệu năng cao, và bạn phải thiết kế schema của mình theo những câu query mà bạn sẽ chạy. Điều này thường dẫn đến việc trao đổi được mất. Ví dụ như denormalized schema có thể tăng tốc một số loại query nhưng lại làm những loại khác chậm đi. Thêm vào bảng counter và summary là một cách hay để tối ưu query, nhưng chúng gây khó khăn khi maintain. Những tính năng của MySQL bị ảnh hưởng một chút từ suy nghĩ trên. Chapter này và chapter tiếp theo sẽ tập chung vào đánh index, xem xét một chút về schema design. Chúng tôi cho rằng bạn đã biết về design database, nên đây không phải là chapter để giới thiệu, hay là chapter nâng cao. Đây là chapter về MySQL database design. Chapter này là sự chuẩn bị cho 2 chapter kế tiếp. Trong 3 chapter, chúng ta sẽ tìm hiểu sự tương tác của logical design, physical design và query execution. Điều này yêu cầu cả sự hiểu biết toàn cảnh lẫn các chi tiết bên trong. Bạn cần hiều được toàn bộ system để có thể biết được làm sao các phần nhỏ tương tác với nhau. Bạn có thể thấy nó có ích khi đọc chapter này trước sau đó bước vào chapter index, query optimaztion. Rất nhiều phần thảo luận có liên hệ với nhau cho nên hãy đọc hết.

**Choosing Optimal Data Types **

smaller usually better: Kiểu dữ liệu nhỏ hơn mà vẫn đáp ứng yêu cầu lưu trữ thường nhanh hơn vì chúng sử dụng ít bổ nhớ hơn, ít cache hơn. Đừng đánh giá thấp khoảng dữ liệu bạn cần chứa vì nó có thể làm tăng dữ liệu ở rất nhiều nơi trong schema và có thể làm bạn đau đớn, tiêu tốn nhiều thời gian. Nếu bạn đang phân vân cái nào là the best data type, hãy chọn kiểu nhỏ nhất mà bạn nghĩ nó sẽ k baoh vượt quá đc.
simple is good: Kiểu dữ liệu đơn giản giúp chúng ta tiêu tốn CPU cycles ít hơn. Ví dụ integer tốt hơn so với string vì string khó so sánh và sắp xếp hơn. Bạn nên dùng kiểu datetime built-in của Mysql thay vì dùng string, và nên sử dụng integer để biểu diễn IP address.
NULL: Tránh null nếu có thể. Nếu để default null có thể yêu cầu nhiều bộ nhớ hơn kiểu dữ liệu của nó bt. Nếu column có đánh index, nó yêu cầu nhiều bytes hơn bt và có thể gây fixed-size index. Performance improvement khi chuyển từ null -> not null thường không tăng lắm. Cho nên không nên đặt trường hợp này có độ ưu tiên cao để thay đổi schema. Nếu có í định đánh index clumn thì nên tránh trường hợp null. Kiểu datetime và timestamp chứa cùng 1 kiểu dữ liệu, độ chính xác hàng giây. Nhưng timestamp chỉ sử dụng một nửa bộ nhớ, và có khả năng tự động update. Nhưng nó có range value nhỏ hơn và đôi khi cái lợi của nó trở thành mặt tiêu cực. Các kiểu Integer, bool, numeric đều là alias của 1 kiểu dữ liệu. Nhưng chúng k ảnh hưởng tới performance. Nếu bạn tạo bảng với kiểu dữ liệu alias, khi show create table thì sẽ thấy base type.

WHOLE NUMBER: Có 2 kiểu của number whole number và real number. Nếu dùng số nguyên có thể dùng: tinyint, smallint, medinumint, int, hoặc bigint yêu cầu 8, 16, 24, 32 và 64 bit lưu trữ. Số nguyên còn có option usigned. MySQL để bạn định nghĩa độ dài của integer như int(11) nhưng nó k có ý nghĩa gì trong hầu hết ứng dụng. Nó không hạn chế range of value, chỉ hiển thị ra 11 chữ số. Mặc dù khai báo là int(1) nhưng nó có thể chứa đc = int(20)

**REAL NUMBER: ** Dành cho kiểu có phần thực, nhưng vẫn dùng cho kiểu nguyên đc. FLOAT và DOUBLE hộ trợ tính toán ~ với dấu , động. Kiểu DECIMAL hỗ trợ tính toán chính xác phần phân số. Cả 2 kiểu , động và DECIMAL đều có thể qui định độ chính xác.

STRING TYPE: VARCHAR & CHAR

VARCHAR:
- Store string có độ dài < cho trước, thường là thế, còn MyISAM có create table mà có row_format = fixed thì sẽ sử dụng fixed space, cho nên sẽ phí space.
- Sử dụng 1 hay 2 byte thêm để lưu độ dài dữ liệu: 1byte nếu độ dài column max là 255 byte và 2 nếu nheieuf hơn. Giả sử latin charset, varchar(10) sử dụng 11 byte bộ nhớ, varchar(1000) sử dụng 1002 byte.
- Giúp performance tốt vì save space. Vì nó dùng thêm variable-length nên khi update thì cần làm thêm 1 số việc. Nếu như độ lớn của nó tăng vượt quá khai báo thì phụ thuộc vào engine. Nếu là MyISAM có thể phân mảnh row, InnoDB có thể chia nhỏ page để vừa row. Những engine khác có thể k làm gì cả.
- Thường sử dụng varchar là hợp lý.
CHAR:
- Char là kiểu fixed length,, luông lallocate space cho value.
- Char là good khi sử dụng string fixed length như MD5. Tốt hơn varchar khi dữ liệu hay đc update vì không phải fragment.
BLOB and TEXT type: Khác nhau là BLOB lưu kiểu byte nên không cần collation ahy charset còn text thì ngược lại. BLOB và TEXT sort kiểu khác. Thay vì sort full length nó chỉ sort max_sort_length. Mysql không thể index các trường này nên nó không dùng để sort.
DATETIME và TIMESTAMP
- DATETIME: năm từ 1001 -> 9999, độ chính xác hàng s. Dữ liệu của nó đc đóng gói vào int YYYYMMDDHHMMSS format, sử dụng 8byte lưu trữ. Có thể sort đc.
- TIMESTAMP: store theo dạng tổng số giây. Range từ 1-1-1970. Chỉ có 4 byte lưu trữ. 1970 -> 2038. Mysql có FROM_UNIXTIME() and UNIX_TIMESTAMP() convert từ unix timestamp to date. Và ngc lại. Timestamp hiện thị dựa vào system timezone. Khi update hay insert 1 row mà không có giá trị của cột timestamp thì nó tự động insert/update current timestamp. Timestamp default khong đc not null.

Tips:

Sử dụng integer cho indentifiers. Vì nó nhanh và có auto_increment. Tránh String cho indentifiers. Cực kỳ cẩn thận khi dùng string cho indentifier với MyISAM. Nó sử dụng packed indexes cho string default => much slower.
Cẩn thận với “random” string, ví dụ như string đc sinh ra từ các hàm hashcode. Vì nó làm chậm insert và select vì khó khăn khi đánh index cho những string random.
Too many column: Mysql storage engine hoạt động = cách copy row giữa server và engine thông qua row buffer. Server decode buffer trong column. Làm thế này sẽ tốn thời gian với Inno và MyISAM trừ myISAM fixedrow. Thử nghiệm với > 100 column làm tăng CPU consumption.
Too many joins: Không nên dùng nhiều join. Nếu entity mà to quá nên chia ra nhiều bảng để tránh join cả bảng to.
Enum: Cận thận khi lạm dụng enum. Ví dụ:

CREATE TABLE ... (
country enum('','0','1','2',...,'31')

Trong trường hợp này nên sử dụng foreign key hoặc lookup table. Nếu bạn muốn thêm một new country thì k có cách nào khác ngoài sử dụng alter table. Và trong MySQL 5.0 trở lên, bạn muốn thêm một phần tử vào vị trí nào đi nữa thì nó vẫn ở cuối list.

Thỉnh thoảng enum dễ gây nhầm lẫn với set. Ví dụ:

CREATE TABLE ...(
is_default set('Y','N') NOT NULL default 'N'

Trong trường hợp này nên sử dụng ENUM thay vì SET. Vì ENUM chỉ cho phép chứa 1 trong 2 giá trị còn SET cho phép chứa 1 -> nhiều giá trị.

NULL:

Chúng tôi đã viết từ trước về việc tránh sử dung NULL, và thật lòng chúng tôi đề nghị bạn sử dụng một cách khác khi vẫn có thể. Kể cả khi bạn muốn lưu trữ một giá trị “k có giá trị”, bạn cũng k nên dùng null. Có thể sử dụng số 0 hoặc 1 giá trị qui ước trước hoặc một empty string. Nhưng trong một số trường hợp sử dụng NULL là tốt hơn và làm cho code đỡ rắc rối hơn tuy NULL là một lỗi rất khó tránh nhưng nó có thể còn là một cách làm tốt hơn những cái khác. Như đã nhắc từ trước, MySQL có đánh index cho NULL nhưng Oracle thì không.

Normalization and Denormalization
- Pros/Cons của Normalized Schema Mọi người khi yêu cầu giúp về vấn đề performance thường được khuyên sửa đổi thành normalized schema. Đặc biệt nếu workload nặng về write:
  - Normailized thường update nhanh hơn denormalized
  - Khi dữ liệu được normailized thì sẽ có ít dữ liệu bị lặp lại, cho nên ít dữ liệu cần thay đổi hơn.
  - Normalized table thường có kích thước nhỏ hơn, tiết kiệm bộ nhớ và performance.
  - Khi có ít dữ liệu dư thừa đồng nghĩa với việc ít sử dụng DISTINCT và GROUP BY query.
  - Mặt tiêu cực của nó là với những well-normalized schema thường khi để lấy dữ liệu phải dùng ít nhất một câu join. Và phải mất nhiều space hơn để đánh index.
- Pros/Cons của Denormalized Schema
  - Denormalized schema chạy tốt vì tất cả mọi thứ đều nằm trong một bảng nên tránh đc việc dùng join.
  - Nếu bạn không cần join thì câu query oái oăm nhất - khi không sử dụng index - là full table scan. Nhưng nó vẫn có thể nhanh hơn so với join bởi vì nó tránh việc random I/O.
  - Một bảng đơn có thể có nhiều cách đánh index hiệu quả hơn. Ví dụ bạn có một website, có người dùng post message, và có cả premium user. Nếu muốn xem 10 message cuối của premium user với normalized schema và đánh index publishing date của message. Câu query có giống như sau:

Mysql> SELECT message_text, user_name
-> FROM message
-> INNER JOIN user ON message.user_id=user.id
-> WHERE user.account_type=’premiumv’
-> ORDER BY message.published DESC LIMIT 10;

Để thực hiện câu query này, MySQL cần scan published index của message table. Mỗi row nó tìm được, nó sẽ thăm dò trong table user và check xem user đó có là premium không. Cách này chỉ hiệu quả khi số lượng nhỏ của user là premium. Một câu query khác sẽ bắt đầu từ table user, select premium user và lấy ra tất cả message sau đó sort. Trường hợp này còn tệ hơn. Vấn đề nằm ở chỗ join, nó làm cả 2 việc là sort và filter nhưng chỉ với 1 index. Nếu bạn denormalize data bằng cạch gộp nhiều bảng và đánh index với (account_type, publised), bạn sẽ k cần join và hiệu quả hơn nhiều:

mysql> SELECT message_text,user_name
-> FROM user_messages
-> WHERE account_type='premium'
-> ORDER BY published DESC
-> LIMIT 10;

A Mixture of Normalized and Denormalized

Cả normalized và denormalized đều có ưu và nhược. Làm sao chọn được cái nào là best design? Sự thật là, fully normalized và fully denormalized schema giống như những con chuột thí nghiệm: chúng thường không có í nghĩa gì ngoài thể giới bên ngoài. Trong thế giới bên ngoài bạn thường kết hợp 2 cách tiếp cận, sử dụng một phần nào đó là normalized, cache table,... Trong ví dụ trên, thực tế thay vì denormalizing fully table thì có thể lưu account_type trên cả bảng user và message. Điều này tránh vấn đề insert và delete bị mất toàn bộ dữ liệu. Bạn sẽ vẫn lưu lại được user mặc dù đã xóa hết message. Nó không làm bảng user, message lớn hơn quá nhiều nhưng giúp bạn select hiệu quả. Tuy nhiên, bây giờ update user lại khó khăn hơn bởi vì phải thay đổi trên cả 2 table. Để xem xét nó có là vấn đề thực sự hay không bạn phải tính toán tần suất thực hiện việc thay đổi có nhiều không và thời gian để hoàn thành thay đổi và so sánh với thời gian thực hiện câu lệnh select. Một ví dụ hay khác để chuyển từ parent table -> child table là dùng để sort. Nếu với normalized schema thì thật là tốn nhiều thời gian để sort message theo author’s name nhưng denormalized schema thì nhanh hơn rất nhiều nều cache và đánh index author_name trong bảng message.

(Chapter 4 - high performance MySQL, 3nd edition)

(tobecontinue)

MySql Optimization

Mục lục