MySQL cho dữ liệu lớn

Một số ví dụ về cách dữ liệu lớn có thể mang lại lợi ích cho doanh nghiệp là

• Nghiên cứu sự tham gia của khách hàng vì nó liên quan đến cách các sản phẩm và dịch vụ của công ty so sánh với các đối thủ cạnh tranh;
• Phân tích tiếp thị để tinh chỉnh các chương trình khuyến mãi cho các dịch vụ mới;
• Phân tích sự hài lòng của khách hàng để xác định các lĩnh vực trong việc cung cấp dịch vụ có thể được cải thiện;
• Lắng nghe trên phương tiện truyền thông xã hội để khám phá các xu hướng và hoạt động xung quanh các nguồn cụ thể có thể được sử dụng để xác định đối tượng mục tiêu tiềm năng

Các hạn chế của MySQL khi xử lý dữ liệu lớn

MySQL không được thiết kế dành cho dữ liệu lớn. Điều này không có nghĩa là nó không thể được sử dụng để xử lý các tập dữ liệu lớn, nhưng một số yếu tố phải được xem xét khi sử dụng cơ sở dữ liệu MySQL theo cách này. Dưới đây là một số hạn chế của MySQL cần ghi nhớ.

• Việc thiếu công cụ tìm kiếm tập trung vào bộ nhớ có thể dẫn đến tắc nghẽn hiệu suất và chi phí cao
• Xử lý khối lượng dữ liệu lớn yêu cầu các kỹ thuật như tô bóng và chia tách dữ liệu trên nhiều nút để vượt qua kiến ​​trúc một nút của MySQL
• Xử lý dữ liệu dễ bay hơi có thể gây ra sự cố trong MySQL. Vấn đề này có thể được giảm bớt phần nào bằng cách thiết kế dữ liệu phù hợp
• Khả năng phân tích của MySQL được nhấn mạnh bởi các truy vấn phức tạp cần thiết để rút ra giá trị từ các nguồn dữ liệu lớn

Những hạn chế này đòi hỏi phải nhấn mạnh thêm vào việc giám sát và tối ưu hóa cơ sở dữ liệu MySQL được sử dụng để xử lý và tài sản dữ liệu lớn của tổ chức. Nó có thể là sự khác biệt trong khả năng tạo ra giá trị từ dữ liệu lớn của bạn

Tối ưu hóa hiệu suất của   Cơ sở dữ liệu MySQL của bạn

Quản lý môi trường MySQL được sử dụng, ít nhất là một phần, để xử lý dữ liệu lớn, cần tập trung vào việc tối ưu hóa hiệu suất của từng phiên bản. Trình quản lý chẩn đoán SQL dành cho MySQL cung cấp một công cụ chuyên dụng để giám sát MySQL giúp xác định các sự cố tiềm ẩn và cho phép bạn thực hiện hành động khắc phục trước khi hệ thống của bạn bị ảnh hưởng tiêu cực. Công cụ này giúp các nhóm đối phó với một số hạn chế do MySQL đưa ra khi xử lý dữ liệu lớn.

Một số tính năng cụ thể của Trình quản lý chẩn đoán SQL dành cho MySQL sẽ hỗ trợ xử lý dữ liệu lớn là.

• Giám sát truy vấn thời gian thực để tìm và giải quyết các vấn đề trước khi chúng ảnh hưởng đến người dùng cuối;
• Giám sát các truy vấn dài hạn và bị khóa có thể xuất phát từ sự phức tạp của việc xử lý khối lượng thông tin trong các tập dữ liệu lớn;
• Tạo bảng điều khiển và biểu đồ tùy chỉnh tập trung vào các khía cạnh cụ thể của hệ thống MySQL của bạn và giúp xác định các xu hướng và mẫu trong hiệu suất hệ thống;
• Sử dụng hơn 600 màn hình tích hợp bao gồm tất cả các lĩnh vực hiệu suất của MySQL

Cả dữ liệu lớn và MySQL sẽ không sớm biến mất. Để họ chơi độc đáo với nhau có thể yêu cầu các công cụ của bên thứ ba và các kỹ thuật sáng tạo. Trình quản lý chẩn đoán SQL cho MySQL là một trong những công cụ như vậy có thể được sử dụng để duy trì hiệu suất của môi trường MySQL của bạn để nó có thể giúp tạo ra giá trị kinh doanh từ dữ liệu lớn

Hầu hết các cơ sở dữ liệu tăng kích thước theo thời gian. Tốc độ tăng trưởng không phải lúc nào cũng đủ nhanh để tác động đến hiệu suất của cơ sở dữ liệu, nhưng chắc chắn có những trường hợp điều đó xảy ra. Khi nó xảy ra, chúng ta thường tự hỏi có thể làm gì để giảm tác động đó và làm thế nào chúng ta có thể đảm bảo hoạt động cơ sở dữ liệu trơn tru khi xử lý dữ liệu trên quy mô lớn

Trước hết, chúng ta hãy thử định nghĩa “khối lượng dữ liệu lớn” nghĩa là gì? . InnoDB hoạt động theo cách nó được hưởng lợi rất nhiều từ bộ nhớ khả dụng – chủ yếu là nhóm bộ đệm InnoDB. Miễn là dữ liệu phù hợp ở đó, quyền truy cập đĩa được giảm thiểu để chỉ xử lý ghi – các lần đọc được phục vụ ngoài bộ nhớ. Điều gì xảy ra khi dữ liệu vượt quá bộ nhớ? . Khi lượng dữ liệu tăng lên, khối lượng công việc sẽ chuyển từ giới hạn CPU sang giới hạn I/O. Điều đó có nghĩa là nút cổ chai không còn là CPU (trường hợp dữ liệu nằm gọn trong bộ nhớ – truy cập dữ liệu trong bộ nhớ nhanh, chuyển đổi và tổng hợp dữ liệu chậm hơn) mà là hệ thống con I/O (các hoạt động của CPU trên dữ liệu bị cản trở). . ) Với việc sử dụng flash ngày càng nhiều, khối lượng công việc ràng buộc I/O không còn khủng khiếp như trước đây trong thời kỳ ổ đĩa quay (truy cập ngẫu nhiên nhanh hơn nhiều với SSD) nhưng hiệu suất vẫn còn đó

Một điều khác chúng ta phải ghi nhớ rằng chúng ta thường chỉ quan tâm đến tập dữ liệu đang hoạt động. Chắc chắn, bạn có thể có hàng terabyte dữ liệu trong lược đồ của mình nhưng nếu bạn chỉ phải truy cập 5GB cuối cùng, thì đây thực sự là một tình huống khá tốt. Chắc chắn, nó vẫn đặt ra những thách thức trong hoạt động, nhưng về mặt hiệu suất thì nó vẫn ổn

Hãy giả sử cho mục đích của blog này và đây không phải là một định nghĩa khoa học, rằng theo khối lượng dữ liệu lớn, chúng tôi muốn nói đến trường hợp kích thước dữ liệu hoạt động lớn hơn đáng kể so với kích thước của bộ nhớ. Có thể là 100GB khi bạn có bộ nhớ 2GB, có thể là 20TB khi bạn có bộ nhớ 200GB. Điểm mấu chốt là khối lượng công việc của bạn bị ràng buộc I/O nghiêm ngặt. Hãy đồng ý với chúng tôi trong khi chúng tôi thảo luận về một số tùy chọn có sẵn cho MySQL và MariaDB

phân vùng

Cách tiếp cận lịch sử (nhưng hoàn toàn hợp lệ) để xử lý khối lượng lớn dữ liệu là thực hiện phân vùng. Ý tưởng đằng sau nó là chia bảng thành các phân vùng, loại bảng con. Việc phân chia xảy ra theo các quy tắc do người dùng xác định. Hãy cùng xem một số ví dụ (các ví dụ SQL được lấy từ MySQL 8. 0 tài liệu)

mysql 8. 0 đi kèm với các loại phân vùng sau

• PHẠM VI
• DANH SÁCH
• CỘT
• Băm
• CHÌA KHÓA

Nó cũng có thể tạo phân vùng con. Chúng tôi sẽ không viết lại tài liệu ở đây nhưng chúng tôi vẫn muốn cung cấp cho bạn một số thông tin chi tiết về cách hoạt động của các phân vùng. Để tạo phân vùng, bạn phải xác định khóa phân vùng. Nó có thể là một cột hoặc trong trường hợp là RANGE hoặc LIST nhiều cột sẽ được sử dụng để xác định cách chia dữ liệu thành các phân vùng

Phân vùng HASH yêu cầu người dùng xác định một cột, cột này sẽ được băm. Sau đó, dữ liệu sẽ được chia thành số lượng phân vùng do người dùng xác định dựa trên giá trị băm đó

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

Trong trường hợp này, hàm băm sẽ được tạo dựa trên kết quả được tạo bởi hàm YEAR() trên cột 'đã thuê'

Phân vùng KEY tương tự với ngoại lệ là người dùng xác định cột nào sẽ được băm và phần còn lại tùy thuộc vào MySQL để xử lý

Trong khi các phân vùng HASH và KEY phân phối dữ liệu ngẫu nhiên trên số lượng phân vùng, RANGE và LIST cho phép người dùng quyết định phải làm gì. RANGE thường được sử dụng với thời gian hoặc ngày tháng

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

Nó cũng có thể được sử dụng với các loại cột khác

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

Các phân vùng DANH SÁCH hoạt động dựa trên danh sách các giá trị sắp xếp các hàng trên nhiều phân vùng

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

điểm trong việc sử dụng các phân vùng bạn có thể yêu cầu là gì? . Giả sử bạn muốn tìm kiếm các hàng đã được tạo trong một tháng nhất định. Nếu bạn có dữ liệu được lưu trữ trong vài năm trong bảng, đây sẽ là một thách thức – một chỉ mục sẽ phải được sử dụng và, như chúng ta biết, các chỉ mục giúp tìm các hàng nhưng việc truy cập các hàng đó sẽ dẫn đến một loạt các lần đọc ngẫu nhiên từ . Nếu bạn có các phân vùng được tạo theo năm tháng, MySQL chỉ có thể đọc tất cả các hàng từ phân vùng cụ thể đó – không cần truy cập chỉ mục, không cần thực hiện đọc ngẫu nhiên. chỉ cần đọc tuần tự tất cả dữ liệu từ phân vùng và chúng ta đã sẵn sàng

Các phân vùng cũng rất hữu ích trong việc xử lý luân chuyển dữ liệu. Nếu MySQL có thể dễ dàng xác định các hàng cần xóa và ánh xạ chúng vào một phân vùng duy nhất, thay vì chạy bảng DELETE FROM WHERE…, vốn sẽ sử dụng chỉ mục để định vị các hàng, bạn có thể cắt bớt phân vùng đó. Điều này cực kỳ hữu ích với phân vùng RANGE – theo ví dụ trên, nếu chúng tôi chỉ muốn giữ dữ liệu trong 2 năm, chúng tôi có thể dễ dàng tạo một công việc định kỳ, công việc này sẽ xóa phân vùng cũ và tạo một phân vùng mới, trống cho tháng tiếp theo

Nén InnoDB

Nếu chúng ta có một lượng lớn dữ liệu (không nhất thiết phải nghĩ đến cơ sở dữ liệu), điều đầu tiên chúng ta nghĩ đến là nén nó. Có rất nhiều công cụ cung cấp tùy chọn nén tệp của bạn, giảm đáng kể kích thước của chúng. InnoDB cũng có một tùy chọn cho điều đó – cả MySQL và MariaDB đều hỗ trợ nén InnoDB. Ưu điểm chính của việc sử dụng nén là giảm hoạt động I/O. Dữ liệu khi nén nhỏ hơn nên đọc ghi nhanh hơn. Trang InnoDB điển hình có kích thước 16KB, đối với SSD, đây là 4 thao tác I/O để đọc hoặc ghi (SSD thường sử dụng các trang 4KB). Nếu chúng tôi quản lý để nén 16KB thành 4KB, chúng tôi chỉ giảm bốn hoạt động I/O. Nó không thực sự giúp ích nhiều về tỷ lệ dữ liệu trên bộ nhớ. Trên thực tế, nó thậm chí có thể làm cho nó tồi tệ hơn – MySQL, để hoạt động trên dữ liệu, phải giải nén trang. Tuy nhiên, nó đọc trang nén từ đĩa. Điều này dẫn đến vùng đệm InnoDB lưu trữ 4KB dữ liệu nén và 16KB dữ liệu không nén. Tất nhiên, có các thuật toán để xóa dữ liệu không cần thiết (trang không nén sẽ bị xóa khi có thể, chỉ giữ một trang đã nén trong bộ nhớ) nhưng bạn không thể mong đợi quá nhiều cải tiến trong lĩnh vực này

Điều quan trọng cần lưu ý là cách nén hoạt động đối với bộ nhớ. Ổ đĩa trạng thái rắn là tiêu chuẩn cho các máy chủ cơ sở dữ liệu ngày nay và chúng có một số đặc điểm cụ thể. Họ nhanh, họ không quan tâm lắm đến việc lưu lượng truy cập là tuần tự hay ngẫu nhiên (mặc dù họ vẫn thích truy cập tuần tự hơn ngẫu nhiên). Chúng đắt đối với khối lượng lớn. Chúng bị “mòn” vì chúng có thể xử lý một số chu kỳ ghi hạn chế. Nén giúp ích đáng kể ở đây – bằng cách giảm kích thước dữ liệu trên đĩa, chúng tôi giảm chi phí của lớp lưu trữ cho cơ sở dữ liệu. Bằng cách giảm kích thước dữ liệu chúng tôi ghi vào đĩa, chúng tôi tăng tuổi thọ của SSD

Thật không may, ngay cả khi nén giúp, đối với khối lượng dữ liệu lớn hơn, nó vẫn có thể không đủ. Một bước khác là tìm kiếm thứ gì khác ngoài InnoDB

MyRocks

MyRocks là một công cụ lưu trữ có sẵn cho MySQL và MariaDB dựa trên một khái niệm khác với InnoDB. Đồng nghiệp của tôi, Sebastian Insausti, có một blog hay về việc sử dụng MyRocks với MariaDB. Ý chính là, do thiết kế của nó (nó sử dụng Log Structured Merge, LSM), MyRocks tốt hơn đáng kể về mặt nén so với InnoDB (dựa trên cấu trúc B+Tree). MyRocks được thiết kế để xử lý lượng lớn dữ liệu và giảm số lần ghi. Nó bắt nguồn từ Facebook, nơi có khối lượng dữ liệu lớn và yêu cầu truy cập dữ liệu cao. Do đó, lưu trữ SSD – tuy nhiên, ở quy mô lớn như vậy, mọi mức tăng nén đều rất lớn. MyRocks có thể cung cấp khả năng nén tốt hơn gấp 2 lần so với InnoDB (có nghĩa là bạn cắt giảm số lượng máy chủ xuống còn hai). Nó cũng được thiết kế để giảm khuếch đại ghi (số lần ghi cần thiết để xử lý thay đổi nội dung hàng) – nó yêu cầu ghi ít hơn 10 lần so với InnoDB. Điều này rõ ràng là giảm tải I/O, nhưng quan trọng hơn, nó sẽ tăng tuổi thọ của SSD gấp mười lần so với việc xử lý cùng một tải bằng InnoDB). Từ quan điểm hiệu suất, khối lượng dữ liệu càng nhỏ thì truy cập càng nhanh, do đó các công cụ lưu trữ như vậy cũng có thể giúp lấy dữ liệu ra khỏi cơ sở dữ liệu nhanh hơn (mặc dù đó không phải là ưu tiên cao nhất khi thiết kế MyRocks)

Kho dữ liệu dạng cột

Tài nguyên liên quan

 Quản lý hiệu suất của Kiểm soát cụm

 Hiểu ảnh hưởng của độ trễ cao trong các giải pháp MySQL và MariaDB có tính sẵn sàng cao

 Bảng gian lận hiệu suất MySQL

Tại một số điểm, tất cả những gì chúng ta có thể làm là thừa nhận rằng chúng ta không thể xử lý khối lượng dữ liệu đó bằng MySQL. Chắc chắn, bạn có thể chia nhỏ nó, bạn có thể làm những việc khác nhau nhưng cuối cùng nó không còn ý nghĩa nữa. Đã đến lúc tìm kiếm các giải pháp bổ sung. Một trong số đó là sử dụng kho dữ liệu dạng cột – cơ sở dữ liệu, được thiết kế với mục đích phân tích dữ liệu lớn. Chắc chắn, chúng sẽ không giúp ích gì với loại lưu lượng truy cập OLTP nhưng các phân tích ngày nay khá tiêu chuẩn khi các công ty cố gắng điều khiển dữ liệu và đưa ra quyết định dựa trên các con số chính xác, không phải dữ liệu ngẫu nhiên. Có rất nhiều kho dữ liệu cột nhưng chúng tôi muốn đề cập ở đây hai trong số đó. MariaDB AX và ClickHouse. Chúng tôi có một số blog giải thích MariaDB AX là gì và MariaDB AX có thể được sử dụng như thế nào. Điều quan trọng, MariaDB AX có thể được mở rộng dưới dạng một cụm, cải thiện hiệu suất. ClickHouse là một tùy chọn khác để chạy phân tích – ClickHouse có thể dễ dàng được định cấu hình để sao chép dữ liệu từ MySQL, như chúng ta đã thảo luận trong một trong các bài đăng trên blog của mình. Nó nhanh, miễn phí và nó cũng có thể được sử dụng để tạo thành một cụm và phân đoạn dữ liệu để có hiệu suất tốt hơn

Phần kết luận

Chúng tôi hy vọng rằng bài đăng trên blog này đã cung cấp cho bạn thông tin chi tiết về cách xử lý khối lượng dữ liệu lớn trong MySQL hoặc MariaDB. May mắn thay, có một vài lựa chọn theo ý của chúng tôi và cuối cùng, nếu chúng tôi không thể thực sự làm cho nó hoạt động, thì vẫn có những lựa chọn thay thế tốt

MySQL có phù hợp với dữ liệu lớn không?

SQL nào là tốt nhất cho dữ liệu lớn?

MySQL có thể xử lý 1 triệu bản ghi không?

Làm thế nào lớn là quá lớn cho cơ sở dữ liệu MySQL?