Mysql
優化數百萬行的wordpress mysql查詢
我執行一個 wordpress woocommcerce 網站,表格中有數百萬行。在我進行分區後,仍然有一些慢查詢如下:
問題:如何優化這3個查詢?
查詢 1
# Query 1: 0.12 QPS, 2.95x concurrency, ID 0x8DB901765B3761AD at byte 444597956 # This item is included in the report because it matches --limit. # Scores: V/M = 182.50 # Time range: 2017-04-11T03:47:55 to 2017-04-25T05:04:45 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 13 140749 # Exec time 82 3576991s 380us 650s 25s 76s 68s 7s # Lock time 13 30s 59us 11s 214us 131us 29ms 84us # Rows sent 0 13.42M 0 100 99.96 97.36 1.79 97.36 # Rows examine 45 570.68G 0 10.93M 4.15M 6.61M 1.15M 3.86M # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 0 4.91G 652 72.07k 36.61k 36.57k 1.10k 36.57k # Query size 44 62.09M 454 463 462.55 441.81 0 441.81 # String: # Databases mydb... (82854/58%)... 1 more # Hosts localhost # Last errno 0 (140715/99%), 1028 (28/0%), 1053 (6/0%) # Users mydb... (136700/97%)... 1 more # Query_time distribution # 1us # 10us # 100us # # 1ms # 10ms # 100ms # # 1s ################################################################ # 10s+ ######################## # Tables # SHOW TABLE STATUS FROM `mydb2` LIKE 'wp_posts'\G # SHOW CREATE TABLE `mydb2`.`wp_posts`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT l.ID, post_title, post_content, post_name, post_parent, post_author, post_modified_gmt, post_date, post_date_gmt FROM (SELECT wp_posts.ID FROM wp_posts WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'product' AND wp_posts.post_password = '' AND wp_posts.post_date != '0000-00-00 00:00:00' ORDER BY wp_posts.post_modified ASC LIMIT 100 OFFSET 5261200) o JOIN wp_posts l ON l.ID = o.ID\G
查詢 2
# Query 2: 0.72 QPS, 0.30x concurrency, ID 0x92F3B1B361FB0E5B at byte 27870878 # This item is included in the report because it matches --limit. # Scores: V/M = 0.11 # Time range: 2017-04-12T20:11:18 to 2017-04-25T05:04:40 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 75 764592 # Exec time 7 317112s 51ms 50s 415ms 609ms 214ms 412ms # Lock time 21 48s 0 143ms 62us 152us 238us 49us # Rows sent 3 292.10M 0 415 400.59 400.73 8.55 381.65 # Rows examine 42 537.09G 0 1.22M 736.58k 1.09M 312.58k 790.84k # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 5 85.08G 89.75k 133.90k 116.68k 112.33k 1.17k 112.33k # Query size 36 51.77M 71 71 71 71 0 71 # String: # Databases mydb... (731943/95%)... 1 more # Hosts localhost # Last errno 0 (764591/99%), 1053 (1/0%) # Users mydb... (760189/99%)... 1 more # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # # 100ms ################################################################ # 1s # # 10s+ # # Tables # SHOW TABLE STATUS FROM `mydb` LIKE 'wp_options'\G # SHOW CREATE TABLE `mydb`.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'\G
查詢 3
# Query 3: 0.00 QPS, 0.14x concurrency, ID 0x885D7EAD0602500C at byte 443806814 # This item is included in the report because it matches --limit. # Scores: V/M = 185.13 # Time range: 2017-04-11T03:47:28 to 2017-04-25T05:04:42 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 5101 # Exec time 4 174590s 2s 371s 34s 271s 80s 4s # Lock time 0 651ms 28us 220ms 127us 103us 3ms 57us # Rows sent 0 4.97k 0 1 1.00 0.99 0.04 0.99 # Rows examine 1 16.86G 0 5.47M 3.38M 5.44M 1.26M 2.62M # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 0 393.64k 73 98 79.02 76.28 0.84 76.28 # Query size 0 1.06M 218 218 218 218 0 218 # String: # Databases mydb... (4845/94%)... 1 more # Hosts localhost # Last errno 0 (5093/99%), 1053 (8/0%) # Users mydb... (5082/99%), skip-grant... (19/0%) # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ ################# # Tables # SHOW TABLE STATUS FROM `mydb2` LIKE 'wp_posts'\G # SHOW CREATE TABLE `mydb2`.`wp_posts`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(wp_posts.ID) FROM wp_posts WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'product' AND wp_posts.post_password = '' AND wp_posts.post_date != '0000-00-00 00:00:00'\G
查詢一:
Next
我的哀悼,你的手指一定是因為點擊按鈕而掉了OFFSET 5261200
!!好的,所以也許一些搜尋爬蟲跟隨了那個按鈕。(Google的手指很結實。)
該怎麼辦?首先不要通過 OFFSET 進行分頁,而是“記住你離開的地方”。這將解決您可能需要或可能不需要跨越數千個牛餡餅才能獲得“所需”頁面的性能。
幫助!
pt-query-digest
告訴過你能給我們什麼幫助,但你還沒有做:SHOW TABLE STATUS FROM `mydb` LIKE 'wp_posts'\G SHOW CREATE TABLE `mydb`.`wp_posts`\G EXPLAIN SELECT ...
但也許真正的問題是 WP 並沒有打算在一個主題上發布500 萬條文章!你需要一個數據庫,而不是部落格軟體。
查詢 2:
您有 122 萬行的選項!?!我再說一遍,WP 是錯誤的平台。
查詢 3:
我會停止重複自己。
永遠不要將列舉類型儲存為字元串。
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'product'
應該是這樣的:
WHERE wp_posts.post_status = 2 AND wp_posts.post_type = 7
其中 ‘2’ 和 ‘7’ 是對應表的 ID
post_status
和post_type
.char/varchar/text 欄位的索引比數字欄位慢得多。