ifwelove/blog

blog

blog's Issues

資料庫優化流程 MySQL Partitioning

環境

• laravel 6.x and 5.5.x
• mysql 5.7.25
• php 7.3

分區表流程

1. 建立分區表

• 時間欄位 timestamp

CREATE TABLE `bigtable_p` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
  PRIMARY KEY (`id`,`created_at`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (unix_timestamp(`created_at`)) (
 PARTITION p201801 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-01 00:00:00')) ENGINE = InnoDB,
 PARTITION p201802 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-01 00:00:00')) ENGINE = InnoDB,
 .
 .
 .
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

• 時間欄位 datetime

CREATE TABLE `bigtable_p` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`,`created_at`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(`created_at`)) (
 PARTITION p201801 VALUES LESS THAN (TO_DAYS('2018-02-01 00:00:00')) ENGINE = InnoDB,
 PARTITION p201802 VALUES LESS THAN (TO_DAYS('2018-03-01 00:00:00')) ENGINE = InnoDB,
 .
 .
 .
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

• 分區表類型為 range
• 主鍵為 id + created_at
• 時間欄位型態為datatime注意SQL查詢要用等式查詢, 或者開發須注意搜尋的分區, 可用explain查詢, 時間欄位型態為timestamp則不影響

explain select * from `bigtable` where `created_at` <= '2020-01-31' and `created_at` > '2020-01-01' ;

select * from `bigtable` where `created_at` < '2020-02-01' and `created_at` > '2020-01-01';

換

select * from `bigtable` where `created_at` <= '2020-01-31' and `created_at` > '2020-01-01';

或

select * from `bigtable` where `created_at` BETWEEN '2020-01-01' AND '2020-02-01';

2. 停用相關排程
3. 舊表更名為_old

RENAME TABLE `bigtable` TO `bigtable_old`;

4. 將舊資料塞入分區表

insert into `bigtable_old` select * from `bigtable_p`;

5. 新分表更名

RENAME TABLE `bigtable_p` TO `bigtable`;

6. 排程定時檢查, 若檢查無正常新增分區, 則通知

SELECT * FROM information_schema.partitions WHERE `TABLE_SCHEMA` = 'db' AND `TABLE_NAME` = 'bigtable' AND `PARTITION_NAME` IS NOT NULL;

use DB;

DB::connection('master')
    ->table('information_schema.partitions')
    ->where('TABLE_SCHEMA', 'db')
    ->where('TABLE_NAME', 'bigtable')
    ->whereNotNull('PARTITION_NAME')
    ->get();

7. 排程定時新增分區, 並將在MAXVALUE的資料寫入到新增的分區

• php call mysql stored procedure

use DB;

DB::connection('master')->select('call alter_partition()');

對應的SQL

ALTER TABLE `bigtable` REORGANIZE PARTITION pMax INTO (
    PARTITION p201803 VALUES LESS THAN (UNIX_TIMESTAMP('2018-04-01 00:00:00')),
    PARTITION pMax VALUES LESS THAN (MAXVALUE)
);

8. 排程定時刪除分區, 資料徹底會刪除

• php call mysql stored procedure

use DB;

DB::connection('master')->select('call drop_partition()');

對應的SQL

ALTER TABLE bigtable DROP PARTITION p201801;

9. 將程式碼新增、更新、刪除同步到分區表及舊表

class service
{
    public function save()
    {
        $repository->save();
        $repository->saveToOld();
    }
    
    public function delete()
    {
        $repository->delete();
        $repository->deleteToOld();
    }
    
    public function update()
    {
        $repository->update();
        $repository->updateToOld();
    }
}

10. 啟用相關排程

如遇到問題回溯流程

1. 停用相關排程
2. 關閉刪除分區排程
3. 關閉新增分區排程
4. 分區表更名為_error

RENAME TABLE `bigtable` TO `bigtable_error`;

5. 備份表更名

RENAME TABLE `bigtable_old` TO `bigtable`;

6. 啟用相關排程

---

參考資料

https://xyz.cinc.biz/2017/07/mysql-copy-table.html
https://ithelp.ithome.com.tw/users/20124671/articles
https://ithelp.ithome.com.tw/articles/10231451
https://medium.com/17media-tech/mysql-partitioning-%E5%84%AA%E5%8C%96%E4%B9%8B%E8%B7%AF-fd8e8480789b
https://www.itread01.com/content/1522648617.html
https://blog.csdn.net/fdipzone/article/details/79769524

Stored Procedures in Laravel 6.x

use DB;

DB::connection('master')->select('call example()');