Mysql 自訂HASH索引帶來的巨大效能提升

標籤：content   utf8   key   索引   hash函數   join   sql   通過   settings   

有這樣一個業務情境，需要在2個表裡比較存在於A表，不存在於B表的資料。表結構如下：

T_SETTINGS_BACKUP | CREATE TABLE `T_SETTINGS_BACKUP` (  `FID` bigint(20) NOT NULL AUTO_INCREMENT,  `FUSERID` bigint(20) NOT NULL COMMENT ‘使用者ID‘,  `FDEVICE` varchar(64) NOT NULL DEFAULT ‘‘ COMMENT ‘使用者裝置號(SN)‘,  `FAPPID` varchar(64) NOT NULL DEFAULT ‘‘ COMMENT ‘應用ID‘,  `FKEYID` varchar(32) NOT NULL DEFAULT ‘‘ COMMENT ‘設定項ID‘,  `FCONTENT` varchar(2000) NOT NULL DEFAULT ‘‘ COMMENT ‘設定項內容‘,  `FUPDATETIME` datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00‘ COMMENT ‘修改時間‘,  `FCREATETIME` datetime NOT NULL DEFAULT ‘1970-01-01 00:00:00‘ COMMENT ‘建立時間‘,  PRIMARY KEY (`FID`),  UNIQUE KEY `UDX_USERID_DEVICE_APPID_KEYID` (`FUSERID`,`FDEVICE`,`FAPPID`,`FKEYID`)) ENGINE=InnoDB AUTO_INCREMENT=21934 DEFAULT CHARSET=utf8mb4

暫訂義上表為A表，記錄數：21933

B表表結構如下，記錄數：4794959

CREATE TABLE `meizu_device_tmp_1` (  `id` int(11) unsigned NOT NULL DEFAULT ‘0‘,  `imei` bigint(20) NOT NULL DEFAULT ‘0‘ COMMENT ‘imei‘,  `sn` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT ‘‘ COMMENT ‘sn‘,  UNIQUE KEY `imei` (`imei`),  UNIQUE KEY `sn` (`sn`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

A的FDEVICE和B的SN是關聯欄位，現在要求出FDEVICE在A不在B的記錄數。自然想到下面的LEFT JOIN

mysql> explain select A.fdevice  FROM T_SETTINGS_BACKUP A left JOIN meizu_device_tmp_1 B ON A.FDEVICE=B.sn where B.sn is null;+----+-------------+-------+-------+---------------+-------------------------------+---------+------+---------+--------------------------------------+| id | select_type | table | type  | possible_keys | key                           | key_len | ref  | rows    | Extra                                |+----+-------------+-------+-------+---------------+-------------------------------+---------+------+---------+--------------------------------------+|  1 | SIMPLE      | A     | index | NULL          | UDX_USERID_DEVICE_APPID_KEYID | 654     | NULL |   22232 | Using index                          ||  1 | SIMPLE      | B     | index | NULL          | sn                            | 62      | NULL | 4772238 | Using where; Using index; Not exists |+----+-------------+-------+-------+---------------+-------------------------------+---------+------+---------+--------------------------------------+rows in set (0.00 sec)

　　

執行時間1小時以上，等不出結果直接KILL掉了。

分析上面的執行計畫，兩個表都用到了覆蓋索引，每個表都沒有過濾條件，所以需要掃描全部行，2W乘以470W是個巨大的數字，執行器在不停的做內迴圈的判斷，直到完成22232*4772238次。除了這個迴圈次數巨大外，這個執行計畫還有2個需要考量的地方

1）type=index  2)key_len 

type=index 執行效率僅高於全表掃描，在某些情況下比全部掃描更差。key_len比較大，說明索引太長。A表的索引是個4欄位的複合式索引，有用的比較欄位只有FDEVICE,為了覆蓋索引最佳化器把全部欄位都加入判斷了。

對於key_len 有兩個疑問 1）為什麼A表的key_len=654? 2)為什麼B表的ken_len=62? 

最佳化key_len, 考慮到業務特性，FUSERID肯定大於0，把SQL改一下，執行計畫看起來好一點了，type=range,key_len=8,實際上對A表只用到了FUSERID欄位索引，最左首碼，FDEVICE通過WHERE判斷。

mysql> desc select A.fdevice  FROM T_SETTINGS_BACKUP A left JOIN meizu_device_tmp_1 B ON A.FDEVICE=B.sn where A.fuserid>0  and B.sn is null;+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+---------+--------------------------------------+| id | select_type | table | type  | possible_keys                 | key                           | key_len | ref  | rows    | Extra                                |+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+---------+--------------------------------------+|  1 | SIMPLE      | A     | range | UDX_USERID_DEVICE_APPID_KEYID | UDX_USERID_DEVICE_APPID_KEYID | 8       | NULL |   11116 | Using where; Using index             ||  1 | SIMPLE      | B     | index | NULL                          | sn                            | 62      | NULL | 4911049 | Using where; Using index; Not exists |+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+---------+--------------------------------------+rows in set (0.01 sec)

執行時間依舊很長，等不了直接KILL了。

最佳化到這一步，還有什麼別的招數，可以提高執行效能的？似乎已經到了盡頭。

回顧下兩表的關聯欄位，A.FDEVICE=B.sn，兩個欄位都是字串，在資料類型上考慮，自然想到，是不是可以把記錄比較欄位從字串的比較，改成數位比較？這是個最佳化的方向。在電腦裡底層資料都是01010這樣，只需要把數字換算成0101就可以做等值比較了，但是變成字元，需要先去字元編碼表找到字元對應的數字，在把數字換算成0101，這裡多出一步尋找操作。另一方面字元佔用的空間比數字要大很多，一個頁內能存下的item條目比數位要少，這會導致更多的資料頁讀取。

根據這個方向，嘗試使用自訂HASH索引，常見的HASH函數有MD5，password,crc32,sha1等，只有crc32雜湊之後的值的數字型的。

mysql> select md5(‘sdsafa‘),password(‘sdsafa‘),crc32(‘sdsafa‘),SHA1(‘sdsafa‘);+----------------------------------+-------------------------------------------+-----------------+------------------------------------------+| md5(‘sdsafa‘) | password(‘sdsafa‘) | crc32(‘sdsafa‘) | SHA1(‘sdsafa‘) |+----------------------------------+-------------------------------------------+-----------------+------------------------------------------+| c5067032ca64a35620fc5c75aa42265c | *45ABB21DBD1E6A5659E05F1EBAF589A3B39EB835 | 1766538443 | b9349f6a0b8138e3e6461745fd257678eefeb9a2 |+----------------------------------+-------------------------------------------+-----------------+------------------------------------------+row in set (0.00 sec)

在表裡加個欄位記錄ｈａｓｈ之後的值，並對這個欄位加上索引。

mysql> CREATE TABLE `meizu_device_tmp_3` (    ->   `id` int(11) unsigned NOT NULL DEFAULT ‘0‘,    ->   `imei` bigint(20) NOT NULL DEFAULT ‘0‘ COMMENT ‘imei‘,    ->   `sn` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT ‘‘ COMMENT ‘sn‘,    ->   `hash_sn` bigint(20) DEFAULT NULL,    ->   UNIQUE KEY `imei` (`imei`),    ->   KEY `hash_sn` (`hash_sn`)    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;Query OK, 0 rows affected (0.20 sec)mysql> insert into meizu_device_tmp_3 select id,imei,sn,crc32(sn) from meizu_device_tmp_1;Query OK, 4794959 rows affected (1 min 50.31 sec)Records: 4794959  Duplicates: 0  Warnings: 0mysql> select * from meizu_device_tmp_3 limit 1;+----------+---------+--------------------+------------+| id       | imei    | sn                 | hash_sn    |+----------+---------+--------------------+------------+| 23930528 | 1311265 | MX21CA2ALHR2460302 | 2330453935 |+----------+---------+--------------------+------------+row in set (0.00 sec)

查詢時，關聯欄位先crc32計算後，再比較，這樣就變成了數字和數位比較了，被驅動表比較欄位也有索引。

但是crc32演算法可能存在hash碰撞，也就是不同的值ｈａｓｈ出來的結果是一樣的，這就“撞”上了。為了避免碰撞導致的比較結果不準確，在ｈａｓｈ比較之後，再做一次原值的比較。

最佳化之後的查詢語句是這樣的

mysql> desc select A.fdevice  FROM T_SETTINGS_BACKUP A left JOIN meizu_device_tmp_3 B ON crc32(A.FDEVICE)=B.hash_sn and A.fdevice=B.sn where  B.sn is null;+----+-------------+-------+-------+---------------+-------------------------------+---------+------+-------+-------------------------+| id | select_type | table | type  | possible_keys | key                           | key_len | ref  | rows  | Extra                   |+----+-------------+-------+-------+---------------+-------------------------------+---------+------+-------+-------------------------+|  1 | SIMPLE      | A     | index | NULL          | UDX_USERID_DEVICE_APPID_KEYID | 654     | NULL | 22232 | Using index             ||  1 | SIMPLE      | B     | ref   | hash_sn       | hash_sn                       | 9       | func |     1 | Using where; Not exists |+----+-------------+-------+-------+---------------+-------------------------------+---------+------+-------+-------------------------+rows in set (0.00 sec)

巨大的改變，被驅動表的rows=1. SQL執行時間0.38秒。

 

hash索引有這麼大的好處，但是也存在不少缺點

1）hash不能處理範圍比較，只能處理等值比較。

2）hash不能做排序，hash出來的結果是隨機分布的。

3）hash不支援部分索引，如index a(10)就不支援。

4）hash無法覆蓋索引

5）hash有碰撞，碰撞得比較厲害時，處理碰撞的代價就比較高。

 

Mysql 自訂HASH索引帶來的巨大效能提升