mysql 查询性能优化实践

mysql 查询性能优化实践

之前为了存儲一些時戳數據,使用hash作爲關鍵字對來存儲,hash的長度一般來說有32個字符。
爲什麼使用hash這個問題就先不糾結了,對於mysql數據庫來說,使用長字符串作爲主索引有着極大的缺點,首先會導致數據庫查詢/刪除緩慢。
其次,隨機的hash值使得數據庫在索引的時候完全沒有任何優化的空間,導致對關鍵字進行索引也無法改變數據庫查詢效率。

SHOW COLUMNS FROM table

+----------+--------------+--------+-------+-----------+---------+
| Field    | Type         | Null   | Key   | Default   | Extra   |
|----------+--------------+--------+-------+-----------+---------|
| hash     | varchar(64)  | NO     |       | <null>    |         |
| name     | varchar(256) | YES    |       | <null>    |         |
| value1   | varchar(64)  | YES    |       | <null>    |         |
| value2   | varchar(64)  | YES    |       | <null>    |         |
+----------+--------------+--------+-------+-----------+---------+

經過一個簡單的測試,對一個由4萬數據的表,其查詢/刪除耗時如下:


SELECT * FROM table ... 42298 rows in set Time: 11.713s delete from table where id = “0fad8c3a708a285d12e327babb3939aa” ^TQuery OK, 246 rows affected Time: 66.868s

刪除一條數據需要的時間在300~1000ms左右,這使得對該表的維護基本無法進行。

爲了解決這個問題,正確的做法是,盡量使用數字id作爲數據庫的索引字段,同時如果hash一定是需要的內容,則將hash存入另外一張表中。經過調整後的兩張表的內容如下:


SHOW COLUMNS FROM table_1 +----------+--------------+--------+-------+-----------+----------------+ | Field | Type | Null | Key | Default | Extra | |----------+--------------+--------+-------+-----------+----------------| | id | int(11) | NO | PRI | <null> | auto_increment | | hash | varchar(32) | NO | | | | +----------+--------------+--------+-------+-----------+----------------+

SHOW COLUMNS FROM table_2 +-------------+---------------+--------+-------+-----------+----------------+ | Field | Type | Null | Key | Default | Extra | |-------------+---------------+--------+-------+-----------+----------------| | id | int(11) | NO | PRI | <null> | auto_increment | | table1_id | int(11) | NO | MUL | <null> | | | name | varchar(2048) | NO | MUL | <null> | | | value1 | varchar(32) | NO | MUL | <null> | | | value2 | varchar(32) | NO | MUL | <null> | | +-------------+---------------+--------+-------+-----------+----------------+

在新的數據結構中,table1_id是另外一張表table_1中的id,原有的數據存儲在table_2中。

稍微對數據庫有些了解的同學應該知道,這個table_id實際上是一個外鍵。但是一般在考慮性能的場合,是不允許表中有外鍵存在的。
這時候,外鍵帶來的數據一致性問題需要由代碼邏輯自行處理。之所以由這樣的要求,是因爲mysql自己實現的外鍵需要考慮的字段的通用性,所以設計上比較復雜,這導致外鍵檢查會消耗很多額外的性能,

在數據庫中,另外一個優化查詢的技巧是對常用的查詢字段添加索引。


ALTER TABLE table_1 ADD INDEX idx_hash (hash);

對於一些重復度比較高的內容,如分類信息,固定的名稱等,索引可以保持查詢時間基本在一個常量級別,所以對經常查詢的字段,添加索引是非常有必要的。

總結來說,以下幾點可以提高數據庫的查詢/刪除效率:

1 盡量不要使用長字符串作爲主鍵,要使用自增的id。
2 如果對性能有要求,則盡量不要使用外鍵,數據一致性由代碼自行保障。
3 對需要查詢的字段使用索引。

发表评论

电子邮件地址不会被公开。 必填项已用*标注