一直很想搞一搞新東家的DB
最近終於開始有時間了
另一方面是
最近比較了解產品運作
所以去動資料庫就比較舒服
這邊遇到的情況是
只有三台或四台機器會進來DB處理事情
QPS其實也沒到多少，最多來個 100 好了 (可能平常 50 都不到)
可是呢平常 Threads_connected 就差不多是 200 了
看了一下一堆 Sleep 到天荒地老的
MySQL Threads_connected 越高會導致效率越差，不要粘著不放啊
這時候去 parameter group 找到這台機器用的 parameter 後
修改 wait_timeout = 60 大概就是個合理數值了
現在看著 Threads_connected 大概 50 就好舒服….
上班又用到了，所以乾脆寫下來 XD
參考 https://www.percona.com/blog/2006/08/17/duplicate-indexes-and-redundant-indexes/
I call redundant indexes BTREE indexes which are prefix of other index, for example KEY(A), KEY (A,B), KEY(A(10)). – First and last are redundant indexes because they are prefix of KEY(A,B)
意即現在有三個 INDEX
KEY(A)
KEY(A,B)
KEY(A(10))
因為 B+TREE 結構問題， KEY(A) 跟 KEY(A(10)) 其實都與 KEY(A,B) 部分相同
刪除 KEY(A) 及 KEY(A(10)) 並不會影響效能 (因為會轉而參考 KEY(A,B))
但這樣會造成空間上的浪費，所以要刪除掉這類的 INDEX
檢測方法可以透過 Percona Toolkit 下的 pt-duplicate-key-checker 來檢測
簡單寫個 shell script 列排程，若有找到重複的就通知
因為看到 int(10)、int(5)、tinyint(5)、int(11) unsigned、smallint(5) 這些寫法，感覺頭真的好痛。
不知道這是多久以前版本的寫法了，還是根本沒這寫法，因為我也接觸不深。
所以弄了一個測試的環境，一開始 table 長這樣子，測試 MySQL integer type 的實際資料情形。
mysql> SHOW CREATE TABLE data_type; +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | data_type | CREATE TABLE `data_type` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `int_normal` int(11) DEFAULT NULL, `int_unsigned_normal` int(10) unsigned DEFAULT NULL, `int_11` int(11) DEFAULT NULL, `int_10` int(10) DEFAULT NULL, `int_5` int(5) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED | +-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
然後塞各種測試資料及驗證。
mysql> insert into data_type (int_normal, int_unsigned_normal, int_11, int_10, int_5) VALUE (0,0,0,0,0); Query OK, 1 row affected (0.00 sec) mysql> insert into data_type (int_normal, int_unsigned_normal, int_11, int_10, int_5) VALUE (1,1,1,1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into data_type (int_normal, int_unsigned_normal, int_11, int_10, int_5) VALUE (-1,-1,-1,-1,-1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------+ | Warning | 1264 | Out of range value for column 'int_unsigned_normal' at row 1 | +---------+------+--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into data_type (int_normal, int_unsigned_normal, int_11, int_10, int_5) VALUE (2147483647,2147483647,2147483647,2147483647,2147483647); Query OK, 1 row affected (0.00 sec) mysql> insert into data_type (int_normal, int_unsigned_normal, int_11, int_10, int_5) VALUE (-2147483648,-2147483648,-2147483648,-2147483648,-2147483648); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------+ | Warning | 1264 | Out of range value for column 'int_unsigned_normal' at row 1 | +---------+------+--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into data_type (int_normal, int_unsigned_normal, int_11, int_10, int_5) VALUE (4294967295,4294967295,4294967295,4294967295,4294967295); Query OK, 1 row affected, 4 warnings (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-----------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------+ | Warning | 1264 | Out of range value for column 'int_normal' at row 1 | | Warning | 1264 | Out of range value for column 'int_11' at row 1 | | Warning | 1264 | Out of range value for column 'int_10' at row 1 | | Warning | 1264 | Out of range value for column 'int_5' at row 1 | +---------+------+-----------------------------------------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM data_type; +----+-------------+---------------------+-------------+-------------+-------------+ | id | int_normal | int_unsigned_normal | int_11 | int_10 | int_5 | +----+-------------+---------------------+-------------+-------------+-------------+ | 1 | 0 | 0 | 0 | 0 | 0 | | 2 | 1 | 1 | 1 | 1 | 1 | | 3 | -1 | 0 | -1 | -1 | -1 | | 4 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | | 5 | -2147483648 | 0 | -2147483648 | -2147483648 | -2147483648 | | 6 | 2147483647 | 4294967295 | 2147483647 | 2147483647 | 2147483647 | +----+-------------+---------------------+-------------+-------------+-------------+ 6 rows in set (0.00 sec) mysql>
所以，還是附上 MySQL 5.6 Manual 關於括號裡面的參數：
1. 基本上沒有跟著 ZEROFILL 是沒用的
2. 找一個最適合的資料型態用，不然會爆表還是會爆表
3. 我已經對這些人放棄治療了