移動到較新版本后，mariaDB10.5.8存盤視圖非常慢-有解無憂

我想尋求幫助，因為我正在努力理解這個問題。

我有兩個資料庫主要和備份。兩者具有相同的資料和視圖。Main 位于 QNAP NAS (v10.5.5) 上的 VM 內，備份位于同一 NAS 上但本機安裝 (v10.5.8)。我知道這是個壞主意，但這只是我正在進行的測驗設定。

我遇到的問題是存盤的視圖。在 VM 上，他們會在不到 1 秒的時間內回傳結果，但直接在 NAS 上，完全相同的資料集和視圖需要 70 秒。不管我使用什么視圖，它們在備份資料庫上都很慢。我試圖調整 NAS 上 mariadb.conf 中的設定以增加緩沖區，但沒有任何改變。只有一個函式將等待時間減少了 10 秒，skip-name-resolve。我嘗試通過 phpMyAdmin、MySQL Workbench 和終端直接在 NAS 上運行它，結果總是相同的 ~70 秒，有時甚至更慢。同樣在 VM 內的主資料庫上，它們花費的時間不到 1 秒。這個事實讓我認為這是一個配置問題，而不是視圖本身。

這是我的 mariaDB 組態檔：

[mysqld]
tmpdir = /share/CACHEDEV1_DATA/.mariadb10/tmp
#skip-networking
user=admin
skip-external-locking
socket = /var/run/mariadb10.sock
key_buffer_size = 16M
max_allowed_packet = 16M
table_open_cache = 64
sort_buffer_size = 2M
net_buffer_length = 16K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 128M
#default-storage-engine = MyISAM
default-storage-engine = InnoDB
pid-file = /var/lock/mariadb10.pid
log-error = /var/log/mariadb10/mariadb.err
skip-name-resolve

我比較了 SHOW VARIABLES 的輸出；兩者唯一的大區別是主資料庫使用 rocksdb 而備份不是。

有誰知道哪里出了問題或我錯過了什么？如果您需要任何額外資訊，請告訴我。

親切的問候

更新：

主要解釋輸出：移動到較新版本后，mariaDB 10.5.8 存盤視圖非常慢

ID	選擇型別	桌子	型別	可能的鍵	鑰匙	密鑰長度	參考	行	r_rows	過濾	r_filtered	額外的
'1'	'基本的'	''	'全部'	無效的	無效的	無效的	無效的	'44803765561'	'0.00'	'100.00'	'100.00'	'在哪里使用'
'2'	'衍生的'	'q1'	'全部'	無效的	無效的	無效的	無效的	'211669'	'227012.00'	'100.00'	'0.00'	'在哪里使用；使用臨時的；使用檔案排序'
'2'	'衍生的'	''	'全部'	'distinct_key'	無效的	無效的	無效的	'211669'	無效的	'100.00'	無效的	'使用連接緩沖區（平面
'2'	'衍生的'	'原文報告'	'eq_ref'	'基本的'	'基本的'	'4'	'.max( `SOME_DB`... `sic_report`) `id`'	'1'	無效的	'100.00'	無效的	'在哪里使用'
'4'	'物化'	'原文報告'	'全部'	無效的	無效的	無效的	無效的	'211669'	無效的	'100.00'	無效的	'使用臨時'

備份解釋輸出：移動到較新版本后，mariaDB 10.5.8 存盤視圖非常慢

ID	選擇型別	桌子	型別	可能的鍵	鑰匙	密鑰長度	參考	行	r_rows	過濾	r_filtered	額外的
'1'	'基本的'	''	'全部'	無效的	無效的	無效的	無效的	'230068224'	'1.00'	'100.00'	'100.00'	'在哪里使用'
'2'	'衍生的'	'q1'	'全部'	無效的	無效的	無效的	無效的	'15168'	'15586.00'	'100.00'	'100.00'	'臨時使用；使用檔案排序'
'2'	'衍生的'	'原文報告'	'全部'	'基本的'	無效的	無效的	無效的	'15168'	'15586.00'	'100.00'	'0.19'	'在哪里使用；使用連接緩沖區（平面
'2'	'衍生的'	''	'eq_ref'	'distinct_key'	'distinct_key'	'4'	'SOME_DB.sic_report.id'	'1'	'0.03'	'100.00'	'100.00'	''
'4'	'物化'	'原文報告'	'全部'	無效的	無效的	無效的	無效的	'15168'	'15586.00'	'100.00'	'100.00'	'使用臨時'

示例視圖：

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER
VIEW `v_sic_stats` AS
SELECT 
    `q1`.`date_time` AS `date_time`,
    IFNULL(`q2`.`lines_`, 0) AS `lines_`,
    IFNULL(`q2`.`stations`, 0) AS `stations_avg`,
    IFNULL(ROUND(AVG(NULLIF(`q1`.`spol`, 0)), 1), 0) AS `spol_avg`,
    IFNULL(`q2`.`actual_pick`, 0) AS `actual_pick`,
    IFNULL(ROUND(NULLIF(`q2`.`lines_`, 0) / NULLIF(`q2`.`stations`, 0),
                    0),
            0) AS `delivery_rate`,
    IFNULL(ROUND(AVG(NULLIF(`q1`.`src_order_pool`, 0)), 0),
            0) AS `src_pool`,
    IFNULL(ROUND(AVG(NULLIF(`q1`.`wms_order_pool`, 0)), 0),
            0) AS `wms_pool`,
    IFNULL(ROUND(`q2`.`avg_pick_time` / `q2`.`lines_`, 1),
            0) AS `avg_pick_time`,
    IFNULL(`q2`.`sort_ff_c`, 0) AS `sort_ff_c`,
    IFNULL(`q2`.`sort_ff_dt`, '00:00:00') AS `sort_ff_dt`,
    IFNULL(`q2`.`sort_gf_c`, 0) AS `sort_gf_c`,
    IFNULL(`q2`.`sort_gf_dt`, '00:00:00') AS `sort_gf_dt`,
    IFNULL(MAX(CAST(`q1`.`sort_check_gf` AS DECIMAL (10 , 0 ))),
            0) AS `sort_check_gf`,
    IFNULL(MAX(CAST(`q1`.`sort_check_ff` AS DECIMAL (10 , 0 ))),
            0) AS `sort_check_ff`,
    IFNULL(`q2`.`tk01_sort_occupation`, 0) AS `tk01_sort_occupation`,
    IFNULL(`q2`.`tk02_sort_occupation`, 0) AS `tk02_sort_occupation`,
    IFNULL(`q2`.`tk01_sort_reloops`, 0) AS `tk01_sort_reloops`,
    IFNULL(`q2`.`tk02_sort_reloops`, 0) AS `tk02_sort_reloops`,
    IFNULL(`q2`.`tk01_emp_occ`, 0) AS `tk01_emp_occ`,
    IFNULL(`q2`.`tk01_emp_relop`, 0) AS `tk01_emp_relop`,
    IFNULL(`q2`.`tk02_emp_occ`, 0) AS `tk02_emp_occ`,
    IFNULL(`q2`.`tk02_emp_relop`, 0) AS `tk02_emp_relop`,
    IFNULL(`q2`.`pick_order_lead_time`, 0) AS `pick_order_lead_time`,
    IFNULL(ROUND(AVG(`q1`.`pick_order_postp_count`), 0),
            0) AS `pick_order_postp_count`,
    IFNULL(`q2`.`tk01_err_occ`, 0) AS `tk01_err_occ`,
    IFNULL(`q2`.`tk01_err_relop`, 0) AS `tk01_err_relop`,
    IFNULL(`q2`.`tk02_err_occ`, 0) AS `tk02_err_occ`,
    IFNULL(`q2`.`tk02_err_relop`, 0) AS `tk02_err_relop`,
    IFNULL(`q2`.`stations_tk01`, 0) AS `stations_tk01`,
    IFNULL(`q2`.`stations_tk02`, 0) AS `stations_tk02`,
    IFNULL(`q2`.`tk01_sf_sorter`, 0) AS `tk01_sf_sorter`,
    IFNULL(`q2`.`tk01_sf_prezone`, 0) AS `tk01_sf_prezone`,
    IFNULL(`q2`.`tk02_sf_sorter`, 0) AS `tk02_sf_sorter`,
    IFNULL(`q2`.`tk02_sf_prezone`, 0) AS `tk02_sf_prezone`
FROM
    (`SOME_DB`.`sic_report` `q1`
    JOIN (SELECT 
        `SOME_DB`.`sic_report`.`id` AS `id`,
            `SOME_DB`.`sic_report`.`date_time` AS `date_time`,
            `SOME_DB`.`sic_report`.`lines_` AS `lines_`,
            `SOME_DB`.`sic_report`.`stations` AS `stations`,
            `SOME_DB`.`sic_report`.`spol` AS `spol`,
            `SOME_DB`.`sic_report`.`actual_pick` AS `actual_pick`,
            `SOME_DB`.`sic_report`.`created_at` AS `created_at`,
            `SOME_DB`.`sic_report`.`delivery_rate` AS `delivery_rate`,
            `SOME_DB`.`sic_report`.`src_order_pool` AS `src_order_pool`,
            `SOME_DB`.`sic_report`.`src_order_pool_qty` AS `src_order_pool_qty`,
            `SOME_DB`.`sic_report`.`wms_order_pool` AS `wms_order_pool`,
            `SOME_DB`.`sic_report`.`wms_order_pool_qty` AS `wms_order_pool_qty`,
            `SOME_DB`.`sic_report`.`avg_pick_time` AS `avg_pick_time`,
            `SOME_DB`.`sic_report`.`sort_ff_c` AS `sort_ff_c`,
            `SOME_DB`.`sic_report`.`sort_ff_dt` AS `sort_ff_dt`,
            `SOME_DB`.`sic_report`.`sort_gf_c` AS `sort_gf_c`,
            `SOME_DB`.`sic_report`.`sort_gf_dt` AS `sort_gf_dt`,
            `SOME_DB`.`sic_report`.`sort_check_gf` AS `sort_check_gf`,
            `SOME_DB`.`sic_report`.`sort_check_ff` AS `sort_check_ff`,
            `SOME_DB`.`sic_report`.`tk01_sort_occupation` AS `tk01_sort_occupation`,
            `SOME_DB`.`sic_report`.`tk02_sort_occupation` AS `tk02_sort_occupation`,
            `SOME_DB`.`sic_report`.`tk01_sort_reloops` AS `tk01_sort_reloops`,
            `SOME_DB`.`sic_report`.`tk02_sort_reloops` AS `tk02_sort_reloops`,
            `SOME_DB`.`sic_report`.`tk01_emp_occ` AS `tk01_emp_occ`,
            `SOME_DB`.`sic_report`.`tk01_emp_relop` AS `tk01_emp_relop`,
            `SOME_DB`.`sic_report`.`tk02_emp_occ` AS `tk02_emp_occ`,
            `SOME_DB`.`sic_report`.`tk02_emp_relop` AS `tk02_emp_relop`,
            `SOME_DB`.`sic_report`.`pick_order_lead_time` AS `pick_order_lead_time`,
            `SOME_DB`.`sic_report`.`pick_order_postp_count` AS `pick_order_postp_count`,
            `SOME_DB`.`sic_report`.`tk01_err_occ` AS `tk01_err_occ`,
            `SOME_DB`.`sic_report`.`tk01_err_relop` AS `tk01_err_relop`,
            `SOME_DB`.`sic_report`.`tk02_err_occ` AS `tk02_err_occ`,
            `SOME_DB`.`sic_report`.`tk02_err_relop` AS `tk02_err_relop`,
            `SOME_DB`.`sic_report`.`stations_tk01` AS `stations_tk01`,
            `SOME_DB`.`sic_report`.`stations_tk02` AS `stations_tk02`,
            `SOME_DB`.`sic_report`.`tk01_sf_sorter` AS `tk01_sf_sorter`,
            `SOME_DB`.`sic_report`.`tk01_sf_prezone` AS `tk01_sf_prezone`,
            `SOME_DB`.`sic_report`.`tk02_sf_sorter` AS `tk02_sf_sorter`,
            `SOME_DB`.`sic_report`.`tk02_sf_prezone` AS `tk02_sf_prezone`
    FROM
        `SOME_DB`.`sic_report`
    WHERE
        `SOME_DB`.`sic_report`.`id` IN (SELECT 
                MAX(`SOME_DB`.`sic_report`.`id`)
            FROM
                `SOME_DB`.`sic_report`
            GROUP BY `SOME_DB`.`sic_report`.`date_time`)) `q2` ON (`q1`.`date_time` = `q2`.`date_time`))
WHERE
    `q1`.`date_time` > SYSDATE() - INTERVAL 2 DAY
GROUP BY `q1`.`date_time`

更新2：

主要的innodb_buffer：

'innodb_buffer_pool_chunk_size'	'134217728'
'innodb_buffer_pool_dump_at_shutdown'	'上'
'innodb_buffer_pool_dump_now'	'離開'
'innodb_buffer_pool_dump_pct'	'25'
'innodb_buffer_pool_filename'	'ib_buffer_pool'
'innodb_buffer_pool_instances'	'1'
'innodb_buffer_pool_load_abort'	'離開'
'innodb_buffer_pool_load_at_startup'	'上'
'innodb_buffer_pool_load_now'	'離開'
'innodb_buffer_pool_populate'	'離開'
'innodb_buffer_pool_size'	'805306368'

備份 innodb_buffer：

'innodb_buffer_pool_chunk_size'	'134217728'
'innodb_buffer_pool_dump_at_shutdown'	'上'
'innodb_buffer_pool_dump_now'	'離開'
'innodb_buffer_pool_dump_pct'	'25'
'innodb_buffer_pool_filename'	'ib_buffer_pool'
'innodb_buffer_pool_instances'	'1'
'innodb_buffer_pool_load_abort'	'離開'
'innodb_buffer_pool_load_at_startup'	'上'
'innodb_buffer_pool_load_now'	'離開'
'innodb_buffer_pool_size'	'1073741824'

創建陳述句：

CREATE TABLE `sic_report` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_time` timestamp NOT NULL DEFAULT current_timestamp(),
`lines_` text DEFAULT NULL,
`stations` text DEFAULT NULL,
`spol` text DEFAULT NULL,
`actual_pick` text DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`delivery_rate` text DEFAULT NULL,
`src_order_pool` text DEFAULT NULL,
`src_order_pool_qty` text DEFAULT NULL,
`wms_order_pool` text DEFAULT NULL,
`wms_order_pool_qty` text DEFAULT NULL,
`avg_pick_time` text DEFAULT NULL,
`sort_ff_c` text DEFAULT NULL,
`sort_ff_dt` text DEFAULT NULL,
`sort_gf_c` text DEFAULT NULL,
`sort_gf_dt` text DEFAULT NULL,
`sort_check_gf` text DEFAULT NULL,
`sort_check_ff` text DEFAULT NULL,
`tk01_sort_occupation` text DEFAULT NULL,
`tk02_sort_occupation` text DEFAULT NULL,
`tk01_sort_reloops` text DEFAULT NULL,
`tk02_sort_reloops` text DEFAULT NULL,
`tk01_emp_occ` text DEFAULT NULL,
`tk01_emp_relop` text DEFAULT NULL,
`tk02_emp_occ` text DEFAULT NULL,
`tk02_emp_relop` text DEFAULT NULL,
`pick_order_lead_time` text DEFAULT NULL,
`pick_order_postp_count` text DEFAULT NULL,
`tk01_err_occ` text DEFAULT NULL,
`tk01_err_relop` text DEFAULT NULL,
`tk02_err_occ` text DEFAULT NULL,
`tk02_err_relop` text DEFAULT NULL,
`stations_tk01` text DEFAULT NULL,
`stations_tk02` text DEFAULT NULL,
`tk01_sf_sorter` text DEFAULT NULL,
`tk01_sf_prezone` text DEFAULT NULL,
`tk02_sf_sorter` text DEFAULT NULL,
`tk02_sf_prezone` text DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=329266 DEFAULT CHARSET=latin1;

主資料庫：

TABLE_NAME	引擎	TABLE_ROWS	AVG_ROW_LENGTH	資料長度	INDEX_LENGTH
'原文報告'	'InnoDB'	'15644'	'235'	'3686400'	'0'

備份資料庫：

TABLE_NAME	引擎	TABLE_ROWS	AVG_ROW_LENGTH	資料長度	INDEX_LENGTH
'原文報告'	'InnoDB'	'16266'	'226'	'3686400'	'0'

uj5u.com熱心網友回復：

看起來您的表統計資訊大不相同，因此索引選擇也不同。您可以使用 - 更新這些表的統計資訊

ANALYZE TABLE table_name_1, table_name_2;

在這里閱讀更多

您可能還想更詳細地查看底層查詢，因為它幾乎肯定會受益于額外的索引。

添加 CREATE TABLE 陳述句后更新

除了主鍵之外沒有索引。鑒于您正在分組并加入date_time應該有一個索引 -

ALTER TABLE `SOME_DB`.`sic_report`
    ADD INDEX `idx_date_time` (`date_time`);

uj5u.com熱心網友回復：

innodb_buffer_pool_size = 134217728

太糟糕了，尤其是對于像你這樣的大桌子。將其設定為可用 RAM 的大約 70%——16GB 機器為 12G，而較小的機器僅為 1200M。這些應該在兩者上提供更好的性能，但較小的可能受 I/O 限制。

另外，避免構造IN ( SELECT ... ). 通常可以將其更改為JOIN.

每個表有多少行？

請SHOW CREATE TABLE為每張桌子提供；可能有一些缺失INDEXes。

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/535757.html

標籤：数据库表现看法数据库

上一篇：在Web上正確使用SVG

下一篇：如何檢查我在DatabricksPyspark中的表的一組值中是否有兩個值