Các loại bảng trong mysql là gì?

Chúng ta đều biết truy cập dữ liệu trong RAM nhanh hơn rất nhiều so với truy cập dữ liệu trên đĩa. Vì vậy, với suy nghĩ này, sẽ không hợp lý nếu MySQL lưu trữ càng nhiều dữ liệu càng tốt trong bộ nhớ? . Bộ đệm truy vấn, giữ các chỉ mục trong bộ nhớ và đặc biệt nhất là giữ toàn bộ bảng trong bộ nhớ. Sau này là chủ đề của bài viết này. Các bảng trong bộ nhớ được gọi là bảng HEAP. Các loại bảng MySQL bao gồm

• Các bảng MyISAM, loại bảng mặc định, sử dụng tính năng khóa bảng, khiến nó trở nên lý tưởng cho các môi trường chèn/cập nhật khối lượng lớn, chèn/cập nhật khối lượng thấp, chẳng hạn như hầu hết các trang web
• Các bảng InnoDB, hỗ trợ các giao dịch và tính toàn vẹn tham chiếu
• Các bảng ISAM, loại bảng MySQL cũ, được thay thế bằng MyISAM trong hầu hết các trường hợp hiện nay
• BDB, cũng là giao dịch an toàn
• HỢP NHẤT các bảng, một loại bảng dành cho tập hợp các loại bảng MyISAM, cho phép chúng được sử dụng làm một

Tạo bảng HEAP

Tạo bảng HEAP chỉ đơn giản là chỉ định loại bảng là HEAP, ví dụ như sau

mysql> CREATE TABLE heapofdust (id INT, fname VARCHAR (40)) TYPE=HEAP;
Query OK, 0 rows affected (0.08 sec)

Lưu ý rằng các bảng HEAP không thể chứa các trường kiểu TEXT hoặc BLOB. Nếu bạn cố gắng tạo một bảng HEAP chứa một trong các loại này, bạn sẽ gặp lỗi như sau

mysql> CREATE TABLE heapofleaves (id INT, fname VARCHAR (40), description TEXT) TYPE=HEAP;
ERROR 1163: The used table type doesn't support BLOB/TEXT columns

Về mặt cú pháp, giờ đây bạn có thể CHỌN và CHỌN dữ liệu như cách bạn làm đối với một bảng MyISAM thông thường, nhanh hơn. Đầu năm 2002, John Lim đã viết một số điểm chuẩn để đo lường sự khác biệt mà các bảng HEAP tạo ra. Anh ấy thấy rằng các bảng HEAP nhanh hơn các bảng MyISAM, nhưng không nhiều. Có khả năng là với bộ đệm bảng của MySQL 4, sự khác biệt sẽ còn ít hơn. Đây là kết quả của anh ấy

Bạn có thể tìm thấy chi tiết đầy đủ về điểm chuẩn của anh ấy (cũng so sánh các DBMS khác) tại trang web PHP Everywhere

Tất nhiên, điểm chuẩn không có ý nghĩa gì nếu chúng không liên quan đến thiết lập của bạn. Các hệ thống có đĩa chậm có thể hưởng lợi nhiều hơn từ việc chuyển sang HEAP. Điều quan trọng nhất cần xem xét là liệu bạn có đủ bộ nhớ để chứa toàn bộ bảng trong bộ nhớ hay không. Nếu tại bất kỳ thời điểm nào bạn hết, bảng sẽ tự động được chuyển đổi thành MyISAM, mặc dù điều này có thể xảy ra vào thời điểm bất tiện và bạn sẽ muốn tránh nó. Có một số cách để quản lý việc sử dụng bộ nhớ và bảng HEAP. Đầu tiên, biến MySQL, max_heap_table_size đặt kích thước tối đa mà một bảng HEAP có thể đạt được (trước khi chuyển đổi nó thành MyISAM). Biến này được đặt trong my. cnf (hoặc của tôi. ini) tập tin. Bạn có thể đọc thêm về cách đặt các biến MySQL trong bài viết Tối ưu hóa MySQL. Phần cứng và các biến Mysqld

Cách khác (bạn có thể sử dụng đồng thời cả hai phương pháp) là chỉ định thuộc tính MAX_ROWS khi tạo bảng

mysql> CREATE TABLE heapofleaves (id INT, fname VARCHAR (40)) TYPE=HEAP MAX_ROWS=1;

Bảng và chỉ mục HEAP

Bảng heap có một số hạn chế khi sử dụng chỉ mục. Hãy xem hành vi này và so sánh nó với một bảng MyISAM giống hệt nhau. Đầu tiên, chúng ta sẽ tạo hai bảng giống hệt nhau, một bảng HEAP, bảng kia MyISAM

mysql> CREATE TABLE myisam1 (id INT, name VARCHAR(40) NOT NULL, INDEX(name)) TYPE MYISAM;

mysql> CREATE TABLE heap1 (id INT, name VARCHAR(40) NOT NULL, INDEX(name)) TYPE=HEAP;

mysql> INSERT INTO myisam1 VALUES(1,'ian'),(2,'Mzwake'),(3,'Helena');

mysql> INSERT INTO heap1 VALUES(1,'ian'),(2,'Mzwake'),(3,'Helena');

Bây giờ chúng ta sẽ thực hiện một truy vấn đơn giản, trả về một bản ghi cụ thể. Chúng tôi sẽ sử dụng EXPLAIN để xem cách các chỉ mục được sử dụng (xem tại đây để biết thêm chi tiết về cách sử dụng EXPLAIN và các loại chỉ mục)

mysql> EXPLAIN SELECT * FROM heap1 WHERE name='ian';
+-------+------+---------------+------+---------+-------+------+------------+
| table | type | possible_keys | key  | key_len | ref   | rows | Extra      |
+-------+------+---------------+------+---------+-------+------+------------+
| heap1 | ref  | name          | name |      40 | const |   10 | where used |
+-------+------+---------------+------+---------+-------+------+------------+

mysql> EXPLAIN SELECT * FROM myisam1 WHERE name='ian';
+---------+------+---------------+------+---------+-------+------+------------+
| table   | type | possible_keys | key  | key_len | ref   | rows | Extra      |
+---------+------+---------------+------+---------+-------+------+------------+
| myisam1 | ref  | name          | name |      40 | const |    1 | where used |
+---------+------+---------------+------+---------+-------+------+------------+

Như mong đợi, việc sử dụng các chỉ mục giống hệt nhau và hiệu quả. Tuy nhiên, khi chúng tôi thay đổi truy vấn để tìm kiếm một phạm vi (tất cả các tên lớn hơn một tên cụ thể), hãy lưu ý sự khác biệt giữa hai loại bảng

mysql> EXPLAIN SELECT * FROM myisam1 WHERE name>'ian';
+---------+-------+---------------+------+---------+------+------+------------+
| table   | type  | possible_keys | key  | key_len | ref  | rows | Extra      |
+---------+-------+---------------+------+---------+------+------+------------+
| myisam1 | range | name          | name |      40 | NULL |    2 | where used |
+---------+-------+---------------+------+---------+------+------+------------+

mysql> EXPLAIN SELECT * FROM heap1 WHERE name>'ian';
+-------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+-------+------+---------------+------+---------+------+------+------------+
| heap1 | ALL  | name          | NULL |    NULL | NULL |    3 | where used |
+-------+------+---------------+------+---------+------+------+------------+

The MyISAM table uses a ‘range’ join, while the HEAP table does an ‘ALL’ join, the worst type. HEAP tables only apply an index with the ‘=’ and ‘<=>’ operators. If your application relies heavily on range indexes, HEAP tables may even be slower than MyISAM tables. Let’s look at another important difference:

mysql> EXPLAIN SELECT * FROM myisam1 WHERE name LIKE 'ia%';
+---------+-------+---------------+------+---------+------+------+------------+
| table   | type  | possible_keys | key  | key_len | ref  | rows | Extra      |
+---------+-------+---------------+------+---------+------+------+------------+
| myisam1 | range | name          | name |      40 | NULL |    1 | where used |
+---------+-------+---------------+------+---------+------+------+------------+

mysql> EXPLAIN SELECT * FROM heap1 WHERE name LIKE 'ia%';
+-------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+-------+------+---------------+------+---------+------+------+------------+
| heap1 | ALL  | name          | NULL |    NULL | NULL |    3 | where used |
+-------+------+---------------+------+---------+------+------+------------+

Bảng HEAP chỉ có thể sử dụng toàn bộ khóa để trả về kết quả, trong khi MyISAM có thể sử dụng tiền tố. Tóm tắt. đây là những khác biệt quan trọng

• Không thể tạo chỉ mục HEAP trên các trường BLOB hoặc TEXT
• Bảng HEAP không thể sử dụng khóa một phần (tiền tố)
• Các bảng HEAP không hỗ trợ các trường AUTO_INCREMENT
• HEAP indexes can only use the ‘=’ and ‘<=>’ operators
• Chỉ mục HEAP không thể được sử dụng để trả về kết quả 'ORDER BY'
• Chỉ mục HEAP không thể cung cấp thông tin về số lượng bản ghi giữa hai kết quả, điều này sẽ hỗ trợ trình tối ưu hóa lựa chọn chỉ mục

Ba lý do cuối cùng đều liên quan. Các bảng HEAP sử dụng các chỉ mục được băm, cho phép chúng xác định cực kỳ nhanh các kết quả phù hợp cụ thể, nhưng không trả về bất kỳ loại dữ liệu phạm vi nào. Một chỉ mục khớp với một bản ghi, nhưng không có thứ tự nào cho phép nó trả về các bản ghi tiếp theo

Sử dụng bảng HEAP

Các bảng HEAP được MySQL tự động sử dụng khi tạo các bảng tạm thời (trừ khi chúng chứa các trường BLOB hoặc TEXT). Nếu bảng tạm thời trở nên quá lớn (được xác định bởi max_heap_table_size và tmp_table_size), bảng tạm thời sẽ tự động được chuyển đổi thành MyISAM

Bảng HEAP cũng có thể được sử dụng khi kích thước bảng có thể vừa với RAM khả dụng và khi CẬP NHẬT không thường xuyên. Vì các bảng HEAP được lưu trữ trong RAM nên nếu máy gặp sự cố hoặc mất điện thì toàn bộ dữ liệu sẽ bị mất. Vì lý do này, chúng không lý tưởng để giữ các thay đổi quan trọng, nhưng phù hợp hơn để cung cấp một tập hợp con của một bảng khác (sau đó có thể ghi lại các thay đổi), làm cho nó có sẵn để truy cập nhanh. Kịch bản lý tưởng là kịch bản trong đó các bản cập nhật xảy ra đối với bảng MyISAM mỗi ngày một lần (hoặc các thay đổi chỉ cần truy cập được một lần mỗi ngày) cho phép tạo bảng HEAP từ bảng này để sử dụng trong ngày, cung cấp khả năng truy cập nhanh chóng, không cần ổ đĩa. Ví dụ: mỗi ngày một lần, bảng heap2 được tạo từ bảng myisam1 (có thể chứa các trường TEXT/BLOB)

mysql> CREATE TABLE heap2 TYPE=HEAP SELECT name FROM myisam1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM heap2;
+--------+
| name   |
+--------+
| Helena |
| ian    |
| Mzwake |
+--------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE heap2 ADD INDEX(name);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DESC heap2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(40) |      | MUL |         |       |
+-------+----------+------+-----+---------+-------+

Bảng heap2 hiện có một chỉ mục (có thể khác với chỉ mục trên bảng MyiSAM ban đầu) và tồn tại trong bộ nhớ để truy cập nhanh như chớp. Trong thời gian chờ đợi, bảng MyISAM có thể được cập nhật liên tục và hoàn toàn không ảnh hưởng đến tốc độ truy cập của bảng HEAP. Những thay đổi này sẽ có sẵn cho người dùng để truy vấn nhanh vào ngày hôm sau. Một ví dụ nhỏ, nhưng hy vọng nó mang lại cho bạn một số cảm hứng để sử dụng bảng HEAP trong môi trường của riêng bạn. Chúc may mắn

Có bao nhiêu bảng trong MySQL?

Các loại bảng trong cơ sở dữ liệu là gì?

3 loại kiểu dữ liệu MySQL là gì?

Các bảng được gọi trong MySQL là gì?