模擬百萬筆表格實驗

模擬百萬筆表格實驗

1. 如何產生測試資料

CREATE TABLE IF NOT EXISTS `MK_Skus` (
  `id` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
);

DELIMITER $$
CREATE PROCEDURE generate_skus()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE j INT DEFAULT 0;
  DECLARE k INT DEFAULT 0;
 
  WHILE i < 2000 DO
  	WHILE j < 5 Do
  	  WHILE k < 4 DO
  	  	INSERT INTO `MK_Skus` (`id`) VALUES (
      		(100010110 + k*10 + j*100 + i*100000)
    	);
	    SET k = k + 1;
  	  END WHILE;
  	  SET j = j + 1;
  	  SET k = 0;
    END WHILE;
    SET i = i + 1;
    SET j = 0;
  	SET k = 0;
  END WHILE;
END $$
DELIMITER ;

CALL generate_skus();  

2. 如何中斷在 MySQL DB 執行過久的 insert/update process?

show processlist;
kill 2246;

3. 從DB併發讀取

func method3(db *gorm.DB) {
    records := make([]Record, 0)
    defer timeTrack(time.Now(), "Method 3")
    count := 1000000
    bucketSize := 100000
    resultCount := 0
    resultChannel := make(chan []Record, 0)
    for beginID :=1; beginID <=count; beginID += bucketSize {
        endId := beginID +bucketSize
        go func(beginId int, endId int) {
            currentRecords := make([]Record, 0)
            db.Table("records").Select("id").Where("id>=? and id<?", beginId, endId).Find(&currentRecords)
            resultChannel <- currentRecords
        }(beginID, endId)
        resultCount += 1
    }
    for i:=0; i<resultCount; i++{
        currentRecords := <- resultChannel
        records = append(records, currentRecords...)
    }
}