昨天跟同事聊起資料表性能的問題，能不能僅用覆寫索引實作資料的匯總統計，找了一個開發環境已有的資料表進行測驗，通過explain命令，能看到mysql通過覆寫索引就能實作sum的需求，而無須去讀取實際行資料，

但開發環境資料量太小，對執行時間的優化，沒有直觀感受，于是決定做一個資料量能到千萬級的資料表，方便測驗，寫個java程式來填充隨機資料是第一選擇，但還要動用IDE太麻煩，嘗試直接使用mysql的函式來實作，

1 資料表設計

目的是演示如何生成千萬級資料，只設計了一個最簡單常用的資料表:user，

CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account` varchar(32) COLLATE utf8_bin NOT NULL,
  `password` varchar(128) COLLATE utf8_bin NOT NULL,
  `name` varchar(32) COLLATE utf8_bin NOT NULL,
  `email` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `mobile` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2 撰寫函式/程序

mysql的rand()函式，回傳的是一個隨機浮點數，為了實作隨機插入資料，將基于這個函式實作，

2.1 獲取隨機整數

CREATE FUNCTION `getRandomInt`(`maxValue` int) RETURNS int(11)
BEGIN
  DECLARE randomInt int default 0;
  SET randomInt = FLOOR(rand() * `maxValue`);
  RETURN randomInt;
END

2.2 獲取隨機字串

CREATE FUNCTION `getRandomString`(`length` int) RETURNS varchar(128) CHARSET utf8 COLLATE utf8_bin
BEGIN
  DECLARE result VARCHAR(128) default '';
  DECLARE chars varchar(30) default 'abcdefghijklmnopqrstuvwxyz';  #全小寫字母
  DECLARE charIndex int default 0;
  WHILE length > 0 DO
    SET charIndex = getRandomInt(26);
    SET result = concat(result, SUBSTRING(chars, charIndex + 1, 1));
    SET length  = length - 1;
  END WHILE;
  RETURN result;
END

2.3 獲取隨機手機號

11位手機號，必須1開始，后續10位只要是數字就行，有點不符合現在的手機號規則，

CREATE FUNCTION `getRandomMobile`() RETURNS varchar(128) CHARSET utf8 COLLATE utf8_bin
BEGIN
  DECLARE result VARCHAR(128) default '1';
  DECLARE chars varchar(30) default '123456789';
  DECLARE charIndex int default 0;
  DECLARE length int DEFAULT 10;
  WHILE length > 0 DO
    SET charIndex = getRandomInt(9);
    SET result = concat(result, SUBSTRING(chars, charIndex + 1, 1));
    SET length  = length - 1;
  END WHILE;
  RETURN result;
END

2.4 獲取隨機漢字

中文漢字的unicode，是從0X4E00(19968)開始的，寫個函式隨機從前2000個漢字中讀出一個，這兒要注意的是char的方法，想生成漢字要使用 using utf16，實測生成的資料存入到 utf8 編碼的資料表欄位中，能正確顯示，

CREATE FUNCTION `getRandomChineseChar`() RETURNS varchar(2) CHARSET utf8
BEGIN
  DECLARE charValue int DEFAULT 19968;
  SET charValue = charValue + getRandomInt(2000);
  RETURN char(charValue using utf16);
END

2.5 獲取隨機姓名

姓名還不能完全使用隨機漢字，“姓”我決定從百家姓里取前兩百個，貼出來的代碼中字串不完整，感興趣的自己上網查下來補一下就行，

CREATE FUNCTION `getRandomChineseName`() RETURNS varchar(20) CHARSET utf8
BEGIN
  DECLARE LAST_NAMES VARCHAR(300) DEFAULT '趙錢孫李周吳鄭王...';
  DECLARE chineseName varchar(20) default '';
  SET chineseName = SUBSTRING(LAST_NAMES, getRandomInt(200) + 1, 1);
  SET chineseName = concat(chineseName, getRandomChineseChar());
  SET chineseName = concat(chineseName, getRandomChineseChar());
  RETURN chineseName;
END

2.6 插入隨機用戶資料

在這個程序中實作真正插入用戶資料，

CREATE PROCEDURE `createRandomUser`(IN `count` int)
BEGIN
  DECLARE userCount DECIMAL(10) default 0;

  DECLARE account VARCHAR(32) DEFAULT '';
  DECLARE thePassword VARCHAR(128) DEFAULT '';
  DECLARE theName VARCHAR(32) DEFAULT '';
  DECLARE email VARCHAR(64) DEFAULT '';
  DECLARE mobile VARCHAR(20) DEFAULT '';
  DECLARE age int DEFAULT 0;
 
  WHILE userCount < `count` DO
    SET account = getRandomString(10);
    SET thePassword = getRandomString(20);
    SET theName = getRandomChineseName();
    SET email = concat(account, '@codestory.tech');
    SET mobile = getRandomMobile();
    SET age = 10 + getRandomInt(50); #年齡10-60歲
 
    insert into user values(null, account, thePassword, theName, email, mobile, age);
    SET userCount = userCount + 1;
  END WHILE;
END

3 生成資料

執行程序，就可以生成相應的資料，如下代碼生成100行

[SQL] call createRandomUser(100);
受影響的行: 100
時間: 1.004s

我電腦上這個表的資料行數

mysql> select count(*) from user\G;
*************************** 1. row ***************************
count(*): 10001102
1 row in set (5.70 sec)

如下是我生成的部分資料

4 索引對查詢性能的影響

設計一個簡單的查詢：所有趙姓用戶且手機號139開頭，平均年齡是多少?

測驗SQL，以及查看執行情況

select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
explain select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;

4.1 只有主鍵的情況

我們前面創建資料表時，只設定了主鍵，沒有創建任何索引，這時候執行情況

mysql> select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
count(user_id): 682
    avg(age): 34.4296
1 row in set (7.03 sec)

執行耗時7.03秒

mysql> explain select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9928072
        Extra: Using where
1 row in set (0.00 sec)

可以看到，查詢使用的是全表查詢，讀了所有的資料行，

4.2 單欄位索引-name

首先在name欄位創建一個單欄位索引

mysql>ALTER TABLE `user` ADD INDEX `idx_user_name` (`name`) USING BTREE ;
Query OK, 0 rows affected (1 min 34.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

執行SQL

mysql> select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
count(user_id): 682
    avg(age): 34.4296
1 row in set (3.52 sec)

耗時3.52秒

mysql> explain select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: idx_user_name
          key: idx_user_name
      key_len: 98
          ref: NULL
         rows: 100634
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

使用索引進行檢索，讀取的資料減少到 10萬行，

4.3 單欄位索引-mobile

為了測驗方便，先洗掉name欄位的索引，再創建一個mobile欄位索引

mysql> ALTER TABLE `user` DROP INDEX `idx_user_name`;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>ALTER TABLE `user` ADD INDEX `idx_user_mobile` (`mobile`) USING BTREE ;
Query OK, 0 rows affected (1 min 27.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

執行SQL

mysql> select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
count(user_id): 682
      avg(age): 34.4296
1 row in set (9.93 sec)

耗時9.93秒

mysql> explain select count(user_id), avg(age) from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: idx_user_mobile
          key: idx_user_mobile
      key_len: 63
          ref: NULL
         rows: 233936
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

盡管我們的SQL陳述句將mobile欄位作為第二個查詢條件，mysql仍然使用了mobile上的索引進行檢索，mobile索引過濾出來的資料有23萬行，比基于name的更多，所以耗時也就更長，

4.4 雙欄位索引-name & mobile

這次我們將兩個欄位建成一個聯合索引，

mysql> ALTER TABLE `user` DROP INDEX `idx_user_mobile`;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE `user` ADD INDEX `idx_user_name_mobile` (`name`, `mobile`) USING BTREE ;
Query OK, 0 rows affected (1 min 54.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

執行SQL

mysql> select avg(age) as age_avg from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
age_avg: 34.4296
1 row in set (0.06 sec)

執行時間大大縮短，只需要0.06秒

mysql> explain select avg(age) as age_avg from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: idx_user_name_mobile
          key: idx_user_name_mobile
      key_len: 161
          ref: NULL
         rows: 100764
        Extra: Using index condition
1 row in set (0.00 sec)

讀取的行數還是10萬行，但時間大大縮短，從這個時間，我們應該能夠猜出mysql的過濾資料的程序，mysql執行where過濾時僅僅通過索引即可完成，然后根據索引中的user_id去資料頁面讀取相應的age值出來做平均，

4.5 終極版-覆寫索引

前面的分析可以看到，為了計算平均值，mysql還需要讀取行資料，如果age欄位也在這個索引中，查詢性能會進一步提升嗎？因為不再讀行資料，

調整索引

mysql> ALTER TABLE `user` DROP INDEX `idx_user_name_mobile`;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `user` ADD INDEX `idx_user_name_mobile_age` (`name`, `mobile`, `age`) USING BTREE ;
Query OK, 0 rows affected (1 min 55.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

執行SQL

mysql> select avg(age) as age_avg from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
age_avg: 34.4296
1 row in set (0.04 sec)

執行時間更短，僅為0.04秒，資料量可能還不夠大，同上一個執行的區別不是太大，

mysql> explain select avg(age) as age_avg from user where name like '趙%' and mobile like '139%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
         type: range
possible_keys: idx_user_name_mobile_age
          key: idx_user_name_mobile_age
      key_len: 161
          ref: NULL
         rows: 103688
        Extra: Using where; Using index
1 row in set (0.00 sec)

最重要的變化是Extra資訊：Using index condition 變成 Using index，Using index condition 表示使用了索引作為查詢過濾的條件；Using index表示整個SQL只使用了索引，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/127254.html

標籤：MySQL

上一篇：mysql備份腳本并保留7天

下一篇：SQL能做一個手工錄入彈窗嗎

制作mysql大資料表驗證覆寫索引

1 資料表設計

2.1 獲取隨機整數

2.2 獲取隨機字串

2.3 獲取隨機手機號

2.4 獲取隨機漢字

2.5 獲取隨機姓名

2.6 插入隨機用戶資料

3 生成資料

4 索引對查詢性能的影響

4.1 只有主鍵的情況

4.2 單欄位索引-name

4.3 單欄位索引-mobile

4.4 雙欄位索引-name & mobile

4.5 終極版-覆寫索引