索引和分區後做什麼以提高性能

August 10, 2020

我有一個大約 1 億行的大表。

我已經在表中完成了索引和分區，但查詢有時仍然需要 >100 甚至 >200 秒才能執行。

所以我在想，在索引和分區以提高 MySQL 性能之後的下一步是什麼。

更改程式碼邏輯是一種選擇（但這會減少數據，我擔心如何調整 MySQL 以僅使用這些數據）
在另一個升級硬體和MySQL版本。（我已經在使用SSD，8核CPU和32GBs RAM，大部分時間資源都可用。）

而且這個問題不僅是關於這張表，而是關於一般的 MySQL 實踐。就像做索引是基本的事情之一，索引之後的第二件事就是分區。但在那之後呢？我的問題可能有點含糊，但我認為這對許多尋找類似分析器的人會有所幫助。

為了給你一個想法，這裡是我的表的概述large_table：

+------+------------+---------------+---------------------+
| user | mobile     | is_first_time | time_send           |
+------+------------+---------------+---------------------+
| a    | xxxxxxxxxx | 0             | 2018-03-12 00:00:00 |
+------+------------+---------------+---------------------+
| b    | xxxxxxxxxx | 1             | 2018-04-02 07:08:09 |
+------+------------+---------------+---------------------+
| c    | xxxxxxxxxx | 0             | 2018-01-03 01:02:03 |
+------+------------+---------------+---------------------+

以下是輸出SHOW INDEXES FOR large_table

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| large_table |          0 | PRIMARY  |            1 | user        | A         |         493 |     NULL | NULL   |      | BTREE      |         |               |
| large_table |          0 | PRIMARY  |            2 | mobile      | A         |   105682194 |     NULL | NULL   |      | BTREE      |         |               |
| large_table |          1 | userid   |            1 | user        | A         |      188718 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

通常在此表上執行的四種查詢類型：

INSERT IGNORE INTO large_table (user,mobile) VALUES ('1234','9876543210')

UPDATE large_table SET is_first_time = 0 WHERE (user,mobile) IN(('xxxx','XXXXXXXXXX'),('zzzz','ZZZZZZZZZZ'),('yyyy','YYYYYYYYYY'),('aaaa','AAAAAAAAAA')

SELECT mobile FROM large_table WHERE user = 168807

SELECT count(mobile) FROM large_table WHERE user =xxxx

在此先感謝（這更像是一個尋求知識的問題，而不是問題的解決方案）

表的 DDL：https ://gist.github.com/kadambkaluskar/4e2ff8d5be6f625c123b58bc10cb32b0

編輯 1：無法在 UPDATE 查詢上執行 EXPLAIN，因為我使用的是 MySQL 5.5

編輯2：

每當有類似的查詢UPDATE large_table SET is_first_time = 0 WHERE (user,mobile) IN時，它就會進入updating狀態，然後所有其他查詢開始排隊。

以下是輸出SHOW PROCESSLIST

+------------+---------+------------------+--------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| Id         | User    | Host             | db     | Command | Time | State      | Info                                                                                                 |
+------------+----------------------+------------------+-----------------+---------+------+------------------+--------------------------------------------------------------------------+
| 1307948736 | my_user | 10.0.0.48:29134  | my_db  | Query   |   91 | Updating   | UPDATE large_table SET is_first_time = 0 WHERE (`user`,`mobile`) IN(('21xx67','919xxxxxx002'),('2177 |
| 1308045382 | my_user | 10.0.0.62:42912  | my_db  | Query   |   50 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('9xx75','917xxxxxx805')                          |
| 1308064919 | my_user | 10.0.0.48:56362  | my_db  | Query   |   42 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('105xx7','9189xxxxxx59')                         |
| 1308066190 | my_user | 10.0.0.21:63342  | my_db  | Query   |   41 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('91xx0','9196xxxxxx60')                          |
| 1308069898 | my_user | 10.0.0.62:48648  | my_db  | Query   |   39 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('95xx5','9192xxxxxx14')                          |
| 1308073279 | my_user | 10.0.0.88:30996  | my_db  | Query   |   38 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('57xx4','9170xxxxxx43')                          |
| 1308073424 | my_user | 10.0.0.24:4738   | my_db  | Query   |   38 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('91xx0','919xxxxxx494')                          |
| 1308073776 | my_user | 10.0.0.88:31118  | my_db  | Query   |   38 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('11xxx3','919xxxxxx224')                         |
| 1308076906 | my_user | 10.0.0.72:26482  | my_db  | Query   |   37 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('1xx17','9197xxxxxx62')                          |
| 1308077527 | my_user | 10.0.0.9:28928   | my_db  | Query   |   37 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('76xx0','9198xxxxxx66')                          |
| 1308082112 | my_user | 10.0.0.113:4230  | my_db  | Query   |   34 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('10xxx8','9197xxxxxx60')                         |
| 1308083588 | my_user | 10.0.0.72:27516  | my_db  | Query   |   34 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('11xxx4','919xxxxxx557')                         |
| 1308088011 | my_user | 10.0.0.101:60448 | my_db  | Query   |   32 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('12xxx0','9184xxxxxx48')                         |
| 1308090557 | my_user | 10.0.0.9:32118   | my_db  | Query   |   31 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('11xxx0','9193xxxxxx59')                         |
| 1308093171 | my_user | 10.0.0.72:29535  | my_db  | Query   |   30 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('11xx84','9193xxxxxx07')                         |
| 1308095347 | my_user | 10.0.0.113:5454  | my_db  | Query   |   29 | Updating   | UPDATE large_table SET is_first_time = 0 WHERE `user`=12xxx1 AND `mobile` = '917xxxxxx482'           |
| 1308096238 | my_user | 10.0.0.62:56668  | my_db  | Query   |   29 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('10xxx8','9190xxxxxx03')                         |
| 1308096342 | my_user | 10.0.0.78:16782  | my_db  | Query   |   29 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('1xxx6','9193xxxxxx54')                          |
| 1308106957 | my_user | 10.0.0.100:17930 | my_db  | Query   |   25 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('7xxx8','9189xxxxxx33')                          |
| 1308109743 | my_user | 10.0.0.107:29496 | my_db  | Query   |   23 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('1xxx29','9197xxxxxx75')                         |
+------------+---------+------------------+-----------------+---------+------+------------------+---------------------------------------------------------------------------------------+

使用者移動分發：

+-------------+----------------+
| no.of.users | mobile.no.count|
+-------------+----------------+
|      1      |   1.5milion    |
|      2      |   1 milion     |
|      10     |   500-1000k    |
|     147     |   100k-500k    |
|     173     |   50k-100k     |
|    1336     |    10k-50k     |
|    2610     |     5k-10k     |
|    6500     |     1k-5k      |
|     12k     |     100-1k     |
|     12k     |     100-1k     |
|     10k     |     10-100     |
|     23k     |      1-10      |
+-------------+----------------+

PARTITIONing買不到性能。擺脫它。（另外，400 個分區太多了，它本身會減慢速度。 更多討論。）
你已經有了這個；留著它：
PRIMARY KEY(user, mobile)
更改UPDATE. 儘管“行建構子”的WHERE(a,b) IN ((1,2),...)語法很誘人，但它的性能卻很糟糕。優化器（直到 5.7.3 版）不會對此做任何有用的事情。它掃描整個表！（請注意第一個UPDATE在PROCESSLIST91 秒，並且似乎阻止了所有其他查詢。）
“修復”更新的一種方法是使用項目建構一個表 ( tmp) IN，然後UPDATE使用
WHERE large.user   = tmp.user
 AND large.mobile = tmp.mobile
並有PRIMARY KEY(user, mobile)。tmp
更新的另一個解決方案是升級到 5.6，然後升級到 5.7。無論如何，5.5 很快就會報廢。
為了節省一點空間和一點速度，請考慮DECIMAL(11)使用mobile. 它需要 5 個字節BIGINT，而不是 8 個字節。
去掉多餘的KEY(user)。一條規則：如果你有INDEX(a,b)，你不需要INDEX(a)。並且PRIMARY KEY是唯一的和關鍵的`。
不說count(mobile)，簡單說COUNT(*)。當您指定一個表達式時，它會檢查它的存在NULL，這與您的情況無關。儘管SELECT COUNT(*) FROM lt WHERE user = xx可能會使用INDEX(key)，但那個原本不需要的索引的成本使得它可能不值得擁有。
小心點MAX(user)。100M 離 20 億不遠。請注意，這樣做INT UNSIGNED會將限制提高到 40 億，而仍然只佔用 4 個字節。
我希望我已經為您提供了一些“知識”以及多種解決方案來提高表的性能。
在所有這些建議之後，我懷疑您很少會Time在PROCESSLIST.

引用自：https://dba.stackexchange.com/questions/208349

索引和分區後做什麼以提高性能

相關問答

優化 MySQL 伺服器性能

超執行緒和 MySQL InnoDB 執行緒並發性能

磁碟活動干擾 MySQL 性能是否正常？

用於提高 MySQL (5.7) 性能的多種模式

如何使 MySQL 在整數範圍選擇查詢中使用索引

我正在刻錄 NVME SSD、3x 60GB MySQL 數據庫（網路爬蟲）