優化數百萬行的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’ 是對應表的 IDpost_status和post_type.

char/varchar/text 欄位的索引比數字欄位慢得多。

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