Mysql lưu trữ dữ liệu từ bảng này sang bảng khác

Trong các hoạt động hàng ngày của DBA, chúng tôi đang thực hiện thao tác Lưu trữ trên các máy chủ cơ sở dữ liệu xuyên quốc gia của mình để cải thiện các truy vấn của bạn và kiểm soát Dung lượng ổ đĩa. Lưu trữ là hoạt động tốn kém nhất vì nó liên quan đến một số lượng lớn Đọc và Viết sẽ được thực hiện. Vì vậy, bắt buộc phải chạy các truy vấn lưu trữ theo khối. Bản lưu trữ phụ thuộc vào việc sử dụng của doanh nghiệp. Nhiều người trong chúng ta cần một bản sao dữ liệu trên cơ sở dữ liệu lưu trữ để tham khảo sau này. Để thực hiện lưu trữ, chúng ta chỉ cần chạy truy vấn xóa với giới hạn. Nhưng chúng ta cần chạy đi chạy lại truy vấn cho đến khi số lượng hàng phù hợp bằng 0. Chúng ta có thể tạo một thủ tục để thực hiện điều này trong vòng lặp while. Tôi đã tạo một thủ tục như vậy để lưu trữ nhiều bảng.  

Nguồn hình ảnh. Brent Ozar không giới hạn

Tại sao Lưu trữ là một hoạt động tốn kém?

Nói chung, cách chúng tôi sắp xếp dữ liệu là xóa khỏi tên_bảng trong đó tên_cột <= some_value; . Sẽ có một IO nặng xảy ra trong Đĩa. Và nó sẽ khóa các hàng và một số khóa khác sẽ được giữ cho đến khi quá trình Lưu trữ hoàn tất. Sao chép có thể chậm trễ vì điều này.  

Khi Lưu trữ sẽ làm rối tung quá trình sản xuất?

• Chạy các lệnh lưu trữ trong thời gian lưu lượng lớn
• Lưu trữ mà không có mệnh đề thích hợp
• Xóa dữ liệu không giới hạn
• Thực hiện điều chỉnh kho lưu trữ trên một cột không được lập chỉ mục.  
• Liên tục chạy truy vấn xóa theo khối trên môi trường sao chép. {không ngủ (1 hoặc vài giây}

Làm thế nào để thực hiện các kho lưu trữ đúng cách?

• Để làm được điều này, điều kiện đầu tiên là hạn mức sử dụng trong lần xóa
• Tạo một chỉ mục trên mệnh đề where
• Ít nhất hãy ngủ 1 giây cho mỗi mâm cặp, điều này sẽ tốt cho cơ sở hạ tầng sao chép
• Đặt autocommit=1
• Không bắt buộc. Đặt cách ly giao dịch thành Đọc đã cam kết
• Không đề cập đến số vòng lặp mà không biết số vòng lặp thực tế để xử lý xóa hoàn toàn

Cách tiếp cận của tôi với điều này. #

Lấy cảm hứng từ Blog của Rick James, tôi đã chuẩn bị một quy trình được lưu trữ duy nhất để thực hiện lưu trữ trên nhiều bảng. Chúng ta chỉ cần chuyển tên bảng, cột ngày và sau đó là ngày để lưu trữ. Tôi đã thử nghiệm với cột ngày giờ và khóa chính.  

Lưu trữ một bảng duy nhất. #

Quy trình dưới đây sẽ thực hiện xóa kiểm tra trên bảng và xóa các bản ghi cũ hơn 10 ngày.  

use sqladmin;

DROP PROCEDURE
IF EXISTS archive;
delimiter //
  CREATE PROCEDURE
    archive()
  begin
    DECLARE rows INT;
    DECLARE rows_deleted INT;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET rows = 1;
    SET rows_deleted = 10000;
    WHILE rows > 0
    do
    SET autocommit=1;
    DELETE
    FROM   test
    WHERE  dop < DATE(Date_sub(Now(), INTERVAL 10 day))
    LIMIT  10000;
    SET rows = row_count();
    select sleep(1);
    commit;
    END WHILE;
    END //
delimiter ;

Lưu trữ nhiều bảng. #

Quy trình này sẽ giúp bạn lưu trữ nhiều bảng, bạn chỉ cần chuyển tên bảng, tên cột và ngày cho bản lưu trữ. Tôi thích sử dụng cái này

use sqladmin;

DROP PROCEDURE
IF EXISTS sqladmin_archive;
delimiter //
  CREATE PROCEDURE
    sqladmin_archive(IN archive_dbname varchar(100), IN archive_table varchar(100), IN archive_column varchar(100), IN archive_date varchar(100))

  begin
    DECLARE rows INT;
    DECLARE rows_deleted INT;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET rows = 1;
    SET rows_deleted = 10000;
    WHILE rows > 0
        do
        SET autocommit=1;
        SET @query =CONCAT('DELETE FROM   ',archive_dbname,'.',archive_table,' WHERE  ',archive_column,' <= "',archive_date ,'" LIMIT  10000;');
        PREPARE arcive_stmt FROM @query;
        EXECUTE arcive_stmt;
        SET rows = row_count();
        SET rows = row_count();
    select sleep(1);
   commit;
   DEALLOCATE PREPARE arcive_stmt;
  END WHILE;
 END //
delimiter ;

-- Execute this procedure
CALL sqladmin_archive ('mydb','test_table','created_at','2018-09-12');

Kết xuất trước khi lưu trữ với mệnh đề where. #

Tập lệnh này là tập lệnh yêu thích của tôi nhưng điều này phụ thuộc vào quy trình được lưu trữ ở trên. Tập lệnh shell này sẽ kết xuất bảng với mệnh đề where của ngày mà chúng tôi muốn lưu trữ. Bạn có thể tùy chỉnh điều này theo yêu cầu của bạn

#!/bin/bash

# pass variables
archive_dbname=$1
archive_table=$2
archive_column=$3
days_to_archive=$4
archive_date="'"`date +'%Y-%m-%d' --date="-$days_to_archive day"`"'"
where_clause=$archive_column'<='$archive_date
dump_file=$archive_table_`date +'%Y-%m-%d' --date="-$days_to_archive day"`".sql"

# Dump the table
echo "DUMP Starting for the table $archive_table ....."
mysqldump -u root $archive_dbname $archive_table --where=$where_clause > $dump_file
echo "DUMP Done......"

# Archive the data
echo "Deleting the data on the table $archive_table ....."
mysql -u root sqladmin -e"CALL sqladmin_archive('$archive_dbname','$archive_table','$archive_column',$archive_date);"
echo "Deleting is Done ....."

Lưu trữ ví dụ. #

Ví dụ này, tôi sẽ lưu trữ một bảng có tên là kiểm tra. Cột started_at chứa giá trị dấu thời gian. Tôi muốn xóa dữ liệu cũ hơn 15 ngày trong bảng. Bảng này nằm trong cơ sở dữ liệu tên là sqladmin