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

Cache and Summary Tables

Thỉnh thoảng cách tốt nhất để nâng cao hiệu năng là lưu dữ liệu dư thừa ở cùng luôn với bảng origin giống như dữ liệu trước khi đổ vào bảng. Tuy nhiên có lúc bạn phải tạo hẳn một bảng sumary hay cache riêng. Cách làm này đặc biệt hiiệu quả khi bạn có thể đủ space để lưu trữ data, nhưng một vài trường hợp thì không có lựa chọn nào khác. Cách gọi “cache table” và “summary table” không có định nghĩa tiêu chuẩn nào. Chúng tôi sử dụng “cache table” để liên hệ tới table chứa data có thể dễ dàng khôi phục lại được nếu trường hợp cần chạy query origin bị lâu. Ví dụ như table chứa logic data sinh ra khi xử lý. “Summary table” có nghĩa là chứa data từ GROUP BY query. Quay trở lại ví dụ website, bạn cần đếm số lượng message post lên trong 24h cuối. Rất khó để lúc nào cũng count đc theo real-time nhưng có thể update 1h mỗi lần, bạn có thể làm đc bằng 1 câu query đơn giản và nó hiệu quả hơn count real-time. Mặt không có lợi là count không phải 100% chính xác.

Nếu bạn muốn count chính xác mà không thích dữ liệu bị quá cũ, đây là một lựa chọn. Bắt đầu với bảng per-hours summary table. Bạn có thể đếm chính xác messege bằng cách cộng message trong 23h dưới. Cộng thêm khoảng từ 23h -> thời gian hiện tại + khoảng từ thời gian hiện tại 24h trước -> 23h trước. Ví dụ câu sql tạo bảng summary:

CREATE TABLE msg_per_hr (
hr DATETIME NOT NULL,
cnt INT UNSIGNED NOT NULL,
PRIMARY KEY(hr)
);

Cộng kết quả từ 3 lệnh đếm:

mysql> SELECT SUM(cnt) FROM msg_per_hr
-> WHERE hr BETWEEN
-> CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR
-> AND CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 1 HOUR;

mysql> SELECT COUNT(*) FROM message
-> WHERE posted >= NOW() - INTERVAL 24 HOUR
-> AND posted < CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR;

mysql> SELECT COUNT(*) FROM message
-> WHERE posted >= CONCAT(LEFT(NOW(), 14), '00:00');

Cách tiếp cận này có thể đếm chính xác mà và hữu hiệu hơn là đếm trên message table. Đây là điểm mục đích chính của summary table. Các logic thống kê baoh cũng tốn CPU + time, bởi vì chúng cần scan rất nhiều data, nhiều query mà chỉ có thể chạy nhanh đc khi có index - nhưng bạn lại không muốn dùng index vì tác động của chúng khi update table làm update cả index nên mất thời gian. Tính toán là hoạt động hay dùng nhất các thứ đã nói bên trên.

Cache table hữu dụng cho tối ưu cho những câu query tìm kiếm và lấy ra dữ liệu. Những câu query đó thường yêu cầu một table cụ thể nào đó và index structure khác với cái bạn thường sẽ sử dụng cho online transaction processing (OLTP). Ví dụ, bạn có thể cần rất nhiều index khác nhau kết hợp lại để tăng tốc query. Nhưng những điều này lại đối đầu với yêu cầu thỉnh thoảng bạn tạo cache table mà chỉ chứa một trong vài column của bảng chính. Một kỹ thuật hữu dụng là sử dụng một engine khác cho cache table. Nếu main table sử dụng InnoDB, thì cache table sẻ dụng MyISAM sử dụng index nhỏ hơn và khả năng dùng full-text search. Thỉnh thoảng bạn muốn đưa table ra khỏi MySQL và cho vào một system đặc biệt để search nhanh hơn như Lucene hay Sphinx search engine.

Khi sử dụng cache và summary table, bạn phải quyết định hoặc là maintain data real-time hoặc qua khoảng tgian rồi rebuild lại. Cái nào tốt hơn thì phải phụ thuộc vào ứng dụng của bạn. Nhưng cách làm thứ 2 không chứa đc toàn dữ liệu, nó lại đưa ra kết quả rất nhanh vì không phân mảnh nhiều và fully sorted indexes.

Khi bạn rebuild summary hay cache table, bạn thường cần lưu lại data ở đau đó. Thường thì sử dụng shadown table là table mà bạn build đằng sau real table. Khi xây dựng nó xong, bạn có thể swap 2 table với một câu rename. Ví dụ, bạn cần rebuild my_summary, bạn sẽ tạo my_summary_new, fill data vào nó và swap real table.

mysql> DROP TABLE IF EXISTS my_summary_new, my_summary_old;
mysql> CREATE TABLE my_summary_new LIKE my_summary;

-- populate my_summary_new as desired

mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;

Materialized Views

Rất nhiều database management system như Oracle, SQL Server đưa ra tính năng gọi là materialized views. Những view này thật ra là đã được tính toán từ trước và lưu vào disk và đc refresh và update thông qua một vài cách. Mysql không support ở mức native. Nhưng bạn vẫn có thể implement materialized view bằng cách sử dụng Justin Swanhart’s open source Flexviews tools. Flex-view phức tạp hơn rất nhiều so với cách bạn tự define và mang đến rất nhiều tính năng để tạo materialized view đơn giản và maintain cũng đơn giản. Gồm các phần:

• A Change data capture(CDC) đọc server binary log và trích xuất ra sự thay đổi thích hợp cho row.
• Set các procedure giúp define và manage view.
• Tool để thay đổi materialized view.

Counter Tables

Nếu một ứng dụng có count table có thể dính tới concurrency problem khi update counter. Những bảng này thường rất phổ biến trong web application. Bạn có thể sử dụng chúng đẻ cache number friend, number of downloaded file, ...Nó thường là giải pháp tốt khi build một bảng riêng cho couter giúp nó vừa nhỏ vừa nhanh. Sử dụng bảng riêng giúp bạn tránh query cache invalid là để sử dụng đc một số kỹ thuật nâng cao chúng tôi sẽ nêu bên dưới.

Để giữ mọi thứ đơn giản nhất có thể, giả sử bạn có bảng counter với 1 dòng chỉ count số lượng truy cập đến website của bạn:

mysql> CREATE TABLE hit_counter (
-> cnt int unsigned not null
-> ) ENGINE=InnoDB;

Mỗi hit của website update counter:

mysql> UPDATE hit_counter SET cnt = cnt + 1;

Vấn đề sảy ra là một dòng duy nhất là nếu nó đang được nằm trong transaction nào đó. Bạn có thể sử dụng conccurency để lưu lại số lượng dòng lúc row chính bị khóa:

mysql> CREATE TABLE hit_counter (
-> slot tinyint unsigned not null primary key,
-> cnt int unsigned not null
-> ) ENGINE=InnoDB;

Khi table của bạn có 100 row.Bh câu query để chọn random row và update counter:

mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

Để khôi phục lại tổng:

mysql> SELECT SUM(cnt) FROM hit_counter;

Nếu yêu cầu bạn phải count thường xuyên theo thời gian. Có thể làm như sau ví dụ cho mỗi ngày 1 row:

mysql> CREATE TABLE daily_hit_counter (
-> day date not null,
-> slot tinyint unsigned not null,
-> cnt int unsigned not null,
-> primary key(day, slot)
-> ) ENGINE=InnoDB;

Và khi update sử dụng on duplicate key:

mysql> INSERT INTO daily_hit_counter(day, slot, cnt)
-> VALUES(CURRENT_DATE, RAND() * 100, 1)
-> ON DUPLICATE KEY UPDATE cnt = cnt + 1;

Nếu bạn muốn làm giảm số lượng của row để giữ table nhỏ, bạn có thể viết một job nhỏ cộng tất cả count lại:

mysql> UPDATE daily_hit_counter as c
-> INNER JOIN (
-> SELECT day, SUM(cnt) AS cnt, MIN(slot) AS mslot
-> FROM daily_hit_counter
-> GROUP BY day
-> ) AS x USING(day)
-> SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),
-> c.slot = IF(c.slot = x.mslot, 0, c.slot);

mysql> DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

Faster Reads, Slower Writes

Bạn thường xuyên phải cần thêm index, các trường thêm để lưu dữ liệu hay thậm chí là cache/summary table để tăng tốc query đọc. Nhưng nó lại thêm việc cho những query ghi và maintain job. Đó là một kỹ thuật thường thấy để nâng cao hiệu năng. Hi sinh phần ghi để cải thiện phần đọc. Nhưng cũng có trường hợp đấy không phải là cái giá duy nhất bạn phải trả cho việc đọc nhanh. Mà bạn còn làm tăng sự phức tạp của cả việc viết lẫn đọc

Speeding Up ALTER TABLE

Performace của câu alter có thể trở thành đáng quan ngại với những bảng cực to. Hầu hết MySQl thực hiện hành động alter là xóa đi rồi tạo mới và insert lại, xóa data table cũ. Điều này có thể thấy là mất rất nhiều thời gian. Đặc biệt khi bạn có ít bộ nhớ và bảng thì to cùng với nhiều index. Nhiều người có trải nghiệm với ALTER TABLE rằng nó mất vài tiếng hay ngày để hoàn tất.

MySQL 5.1 và bản mới hơn hỗ trợ cho vài kiểu “online” operation - không lock table cho cả operation. Version InnoDB gần đây hỗ trợ build index bằng sort - làm cho build index nhanh hơn nhiều và thu nhỏ không gian index.

Nhìn chung, phần lowpsn ALTER TABLE operation đều gây nên interruption of service in MySQL. Chúng tôi sẽ show cho bạn vài kỹ thuật để tránh cái này, nhưng nó chỉ dành cho trường hợp đặc biệt. Trường hợp chung bạn cần sử dụng hoặc là operational trick như là swapping server, thực hiện alter trên server không phải là production hoặc phải dùng shadow copy. Cách sử dụng shadown copy là build một bảng mới giống với bảng cũ rồi rename rồi đờ róp.

Không phải tất cả ALTER TABLE đều phải rebuild. Ví dụ, bạn có thể thay đổi hoặc drop column’s default value theo 2 cách(1 nhanh, 1 chậm). Nếu bạn muốn đổi default duration từ 3 ->5 ngày theo cách chậm:

mysql> ALTER TABLE sakila.film
-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

SHOW STATUS cho thấy câu lệnh này làm 1000 lần read, 1000 lần insert, nói cách khác, nó copy từ bảng cũ sang bảng mới mặc dù những trường khác không thay đổi.

Về lý thuyết, MySQl có thể bỏ qua build new table. Giá trị mặc định cho column thực ra được lưu trữ tại frm file, bạn nên thay đổi nó mà không động j đến table. MySQL chưa sử dụng sự tối ưu này, dỹ nhiên bất cứ hành động MODIFY COLUMN có thể gây nên table rebuild.

Bạn có thể thay đổi default theo cách nhanh:

mysql> ALTER TABLE sakila.film
-> ALTER COLUMN rental_duration SET DEFAULT 5;

Câu lện trên modify .frm file và không động chạm gì đến table. Kết quả thì rất là nhanh.

Modifying Only the .frm File

Chúng ta vừa modify .frm file và nó rất nhanh, MySQL đã rebuild table nhưng nó k cần phải làm vậy. Nêu bạn có í định kiếm cái j đó mạo hiểm, bạn có thể thuyết phục mysql làm điều gì đó khác thay vì rebuild table.

Kỹ thuật vừa nói có thể k đc support, không có document, và có thể không hoạt động. Sử dụng nó là bạn đã mạo hiểm. Chúng tôi khuyên bạn nên back up trước.

Bạn có thể làm theo vài thứ sau mà không cần phải rebuild table:

• Remove auto_increment attribute của cột nào chứa
• Add, remove, change enum, set constants

Kỹ thuật cơ bản để tạo .frm file cho table structure mông muốn và copy nó đặt vào chỗ tồn tại của file .frm cho bảng đó.

1. Tạo một bảng empty với phần trường còn lại giống hệt, chỉ khác những cái gì muốn thay đổi.
2. Thực hiện FLUSH TABLES WITH READ LOCK. Cái này sẽ đóng tất cả table đang sử dụng và ngăn cản bất cứ table nào mở ra.
3. Swap .frm file.

Thực hiện UNLOCK TABLES để giải phóng mặt bằng.

Building MyISAM Indexes Quickly

Trick thường dùng để load MyISAM table nhanh là disable keys, load data và enable lại key:

mysql> ALTER TABLE test.load_data DISABLE KEYS;
-- load the data
mysql> ALTER TABLE test.load_data ENABLE KEYS;

Bởi vì MyISAM bị delay để build key khi load data. Cách làm này nhanh hơn nhiều và cho result không bị phân mảnh và index tree kích thước nhỏ.

Không may, cái này không dùng được cho unique index.bởi vì DISABLE KEYS chỉ dành cho nonunique index. MyISAM build unique index trong bộ nhớ và check tính unique khi nó load mỗi row. Việc load sẽ trở thành cực kỳ chậm khi kích thước index vượt quá bộ nhớ có sẵn.

Trong version hiện đại của InnoDB, bạn có thể dùng một tính năng dựa trên InnoDB’s fast online index creation. Nó sẽ drop tất cả non unique index, và add cột mới. Sau đó add lại index.

Với ALTER TABLE đã thảo luận từ trước, bạn có thể tăng tốc quá trình này nếu bạn làm thêm 1 tí việc và chập nhận mạo hiểm.Cái này có thể hữu dụng nếu load data từ backup, ví dụ, khi bạn đã biết data là valid và không cần phải unique check. Đây là những gì cần làm:

1. Tạo bảng mới với structure mong muốn và không có index nào cả.
2. Load data từ bảng vào trong .myd file.
3. Tạo một bảng empty nữa cũng sutructre như thế, và lần này có cả index. Điều này sẽ tạo .frm file và .myi file - cái bạn sẽ cần.
4. Flush các table vào trạng tái read lock.
5. Rename file .frm của bảng thứ 2 và .myi file nữa đẻ mySQl sử dụng chúng cho bảng đầu tiên.
6. Mở lock.
7. Sử dụng REPAIR TABLE để build table index.

Cái này có thể chạy nhanh hơn rất nhiều đối với những bảng lớn.

Summary

Schema được design tốt thì rất phổ biến, nhưng MySQL có một số thứ đặc biệt cần lưu ý. Tóm tắt cần phải giữ mọi thứ thật đơn giản nhất có thể. MySQL phù hợp với sự đơn giản.

• Tránh thiết kế cực phức tạp, giống như sử dụng những index vô cùng phức tạp, table rất nhiều column.
• Sử dụng kiểu dữ liệu đơn giản, nhỏ, tránh dùng NULL.
• Sử dụng cùng kiểu dữ liệu với những loại dữ liệu tương tự nhau. Để join dễ hơn.
• Cẩn thận variable-length của string.
• Cố gắng sử dụng integer để định danh.
• Tránh MySQL-isms như phần chính xác của , động và độ dài hiển thị của integer.
• Cận thận với ENUM và SET.

Normalized được thì rất tốt nhưng đôi khi sẽ có lợi hơn khi ta dùng Denormalized. Chúng ta sẽ thấy nhiều hơn ví dụ ở chapter sau. Những tips nên dùng được đưa ra là sử dụng tính toán trước, cache, hoặc summary table. Cuối cùng, ALTER TABLE có thể mang đến nỗi đau trong hầu hết các trường hợp, nó sẽ khóa bảng và rebuild lại một bảng mới. Chúng tôi đã giới thiệu về những việc làm thay thế ALTER TABLE trong một số trường hợp đặc biệt, còn trường hợp chung bạn sẽ phải sử dụng những kỹ thuật khác, như sử dụng shadown copy hoặc swap server.

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