MySQL 8.0 Reference Manual(讀書筆記75節--Optimizer Statistics for InnoDB (1))

来源:https://www.cnblogs.com/xuliuzai/p/18097933
-Advertisement-
Play Games

目錄一、什麼是多實例二、MySQL多實例配置1、創建數據目錄2、創建配置文件3、編輯330{7..9}的配置文件4、初始化330{7..9}數據5、修改目錄許可權6、啟動多實例7、查看server_id8、進入單獨的MySQL實例9、關閉實例 一、什麼是多實例 Mysql多實例就是在一臺伺服器上同時開 ...


概述

This section describes how to configure persistent and non-persistent optimizer statistics for InnoDB tables.

Persistent optimizer statistics are persisted across server restarts【意思是重啟操作,對這些數據沒有影響】, allowing for greater plan stability and more consistent query performance. Persistent optimizer statistics also provide control and flexibility with these additional benefits:

• You can use the innodb_stats_auto_recalc configuration option to control whether statistics are updated automatically after substantial changes to a table.

• You can use the STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES clauses with CREATE TABLE and ALTER TABLE statements to configure optimizer statistics for individual tables.

• You can query optimizer statistics data in the mysql.innodb_table_stats and mysql.innodb_index_stats tables.---核心表

• You can view the last_update column of the mysql.innodb_table_stats and mysql.innodb_index_stats tables to see when statistics were last updated.

• You can manually modify the mysql.innodb_table_stats and mysql.innodb_index_stats tables to force a specific query optimization plan or to test alternative plans without modifying the database.

The persistent optimizer statistics feature is enabled by default (innodb_stats_persistent=ON).--預設開始

Non-persistent optimizer statistics are cleared on each server restart and after some other operations, and recomputed on the next table access. As a result, different estimates could be produced when recomputing statistics, leading to different choices in execution plans and variations in query performance.

This section also provides information about estimating ANALYZE TABLE complexity【kəmˈpleksəti 複雜性;難題;難懂;難以理解的局勢】, which may be useful when attempting to achieve a balance between accurate statistics and ANALYZE TABLE execution time.

Configuring Persistent Optimizer Statistics Parameters

The persistent optimizer statistics feature improves plan stability【stəˈbɪləti 穩定(性);穩定性;穩固(性)】 by storing statistics to disk and making them persistent across server restarts so that the optimizer is more likely to make consistent choices each time for a given query.

Optimizer statistics are persisted to disk when innodb_stats_persistent=ON or when individual tables are defined with STATS_PERSISTENT=1. innodb_stats_persistent is enabled by default.

Formerly【ˈfɔːrmərli 以前;原名;往時】, optimizer statistics were cleared when restarting the server and after some other types of operations, and recomputed on the next table access. Consequently【ˈkɑːnsɪkwentli 因此;所以】, different estimates could be produced when recalculating statistics leading to different choices in query execution plans and variation in query performance.

Configuring Automatic Statistics Calculation for Persistent Optimizer Statistics

The innodb_stats_auto_recalc variable, which is enabled by default, controls whether statistics are calculated automatically when a table undergoes【ʌndərˈɡoʊz 經歷,經受(變化、不快的事等)】 changes to more than 10% of its rows. You can also configure automatic statistics recalculation for individual tables by specifying the STATS_AUTO_RECALC clause when creating or altering a table.

Because of the asynchronous【eɪˈsɪŋkrənəs 不同時存在(或發生)的;非共時的】 nature of automatic statistics recalculation, which occurs in the background, statistics may not be recalculated instantly after running a DML operation that affects more than 10% of a table, even when innodb_stats_auto_recalc is enabled. Statistics recalculation can be delayed by few seconds in some cases. If up-to-date statistics are required immediately, run ANALYZE TABLE to initiate a synchronous (foreground) recalculation of statistics.

If innodb_stats_auto_recalc is disabled, you can ensure the accuracy of optimizer statistics by executing the ANALYZE TABLE statement after making substantial changes to indexed columns. You might also consider adding ANALYZE TABLE to setup scripts that you run after loading data, and running ANALYZE TABLE on a schedule at times of low activity.

When an index is added to an existing table, or when a column is added or dropped, index statistics are calculated and added to the innodb_index_stats table regardless of the value of innodb_stats_auto_recalc.

Configuring Optimizer Statistics Parameters for Individual Tables

innodb_stats_persistent, innodb_stats_auto_recalc, and innodb_stats_persistent_sample_pages are global variables. To override these systemwide settings and configure optimizer statistics parameters for individual tables, you can define STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES clauses in CREATE TABLE or ALTER TABLE statements.

• STATS_PERSISTENT specifies whether to enable persistent statistics for an InnoDB table. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_persistent setting. A value of 1 enables persistent statistics for the table, while a value of 0 disables the feature. After enabling persistent statistics for an individual table, use ANALYZE TABLE to calculate statistics after table data is loaded.

• STATS_AUTO_RECALC specifies whether to automatically recalculate persistent statistics. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_auto_recalc setting. A value of 1 causes statistics to be recalculated when 10% of table data has changed. A value 0 prevents automatic recalculation for the table. When using a value of 0, use ANALYZE TABLE to recalculate statistics after making substantial changes to the table.

• STATS_SAMPLE_PAGES specifies the number of index pages to sample when cardinality and other statistics are calculated for an indexed column, by an ANALYZE TABLE operation, for example.

舉的例子

CREATE TABLE `t1` (
`id` int(8) NOT NULL auto_increment,
`data` varchar(255),
`date` datetime,
PRIMARY KEY (`id`),
INDEX `DATE_IX` (`date`)
) ENGINE=InnoDB,
 STATS_PERSISTENT=1,
 STATS_AUTO_RECALC=1,
 STATS_SAMPLE_PAGES=25;

Configuring the Number of Sampled Pages for InnoDB Optimizer Statistics

The optimizer uses estimated statistics about key distributions to choose the indexes for an execution plan, based on the relative selectivity of the index. Operations such as ANALYZE TABLE cause InnoDB to sample random pages from each index on a table to estimate the cardinality【kɑːrdɪˈnæləti 基數;集的勢】 of the index. This sampling technique is known as a random dive.

 The innodb_stats_persistent_sample_pages controls the number of sampled pages. You can adjust the setting at runtime to manage the quality of statistics estimates used by the optimizer. The default value is 20. Consider modifying the setting when encountering the following issues:

1. Statistics are not accurate【ˈækjərət 精確的;準確的;準確無誤的】 enough and the optimizer chooses suboptimal【次優的;次優;次優化;次佳】  plans, as shown in EXPLAIN output. You can check the accuracy of statistics by comparing the actual cardinality of an index (determined by running SELECT DISTINCT on the index columns) with the estimates in the mysql.innodb_index_stats table.

If it is determined that statistics are not accurate enough, the value of innodb_stats_persistent_sample_pages should be increased until the statistics estimates are sufficiently accurate. Increasing innodb_stats_persistent_sample_pages too much, however, could cause ANALYZE TABLE to run slowly. --該增就增

2. ANALYZE TABLE is too slow. In this case innodb_stats_persistent_sample_pages should be decreased until ANALYZE TABLE execution time is acceptable. Decreasing the value too much, however, could lead to the first problem of inaccurate statistics and suboptimal query execution plans. --該減就減

If a balance cannot be achieved between accurate statistics and ANALYZE TABLE execution time, consider decreasing the number of indexed columns in the table or limiting the number of partitions to reduce ANALYZE TABLE complexity. The number of columns in the table's primary key is also important to consider, as primary key columns are appended to each nonunique index.

Including Delete-marked Records in Persistent Statistics Calculations

 By default, InnoDB reads uncommitted data when calculating statistics. In the case of an uncommitted transaction that deletes rows from a table, delete-marked records are excluded when calculating row estimates and index statistics, which can lead to non-optimal execution plans for other transactions that are operating on the table concurrently using a transaction isolation level other than READ UNCOMMITTED. To avoid this scenario, innodb_stats_include_delete_marked can be enabled to ensure that delete-marked records are included when calculating persistent optimizer statistics.---優化器不提供的方案不是最優,這也是種因素.

When innodb_stats_include_delete_marked is enabled, ANALYZE TABLE considers deletemarked records when recalculating statistics.

innodb_stats_include_delete_marked is a global setting that affects all InnoDB tables, and it is only applicable to persistent optimizer statistics.

InnoDB Persistent Statistics Tables

The persistent statistics feature relies on the internally managed tables in the mysql database, named innodb_table_stats and innodb_index_stats. These tables are set up automatically in all install, upgrade, and build-from-source procedures. ---主要依賴著兩個系統表

innodb_table_stats的欄位意思

The innodb_table_stats table contains one row for each table.

欄位名   說明
 database_name  資料庫名字
 table_name  表名:Table name, partition name, or subpartition name
 last_update  A timestamp indicating the last time that InnoDB updated this row
 n_rows  The number of rows in the table
 clustered_index_size  The size of the primary index, in pages---單位是頁,占了幾頁
sum_of_other_index_sizes The total size of other (non-primary) indexes, in pages---單位是頁,占了幾頁

 表innodb_index_stats的欄位說明

欄位名  說明
database_name 資料庫名字
 table_name  表名:Table name, partition name, or subpartition name
index_name 索引的名字
last_update A timestamp indicating the last time the row was updated
stat_name The name of the statistic, whose value is reported in the stat_value column
stat_value The value of the statistic that is named in stat_name column
sample_size The number of pages sampled for the estimate provided in the stat_value column
stat_description Description of the statistic that is named in the stat_name column

The innodb_index_stats table contains multiple rows for each index. Each row in the innodb_index_stats table provides data related to a particular index statistic which is named in the stat_name column and described in the stat_description column.

The stat_name column shows the following types of statistics:---關於欄位stat_name說明

• size: Where stat_name=size, the stat_value column displays the total number of pages in the index.

• n_leaf_pages: Where stat_name=n_leaf_pages, the stat_value column displays the number of leaf pages in the index.

For nonunique indexes, InnoDB appends the columns of the primary key.

 

註意:The innodb_table_stats and innodb_index_stats tables include a last_update column that shows when index statistics were last updated.---如果有主鍵的話,你應該發現,針對這張表,innodb_index_stats主鍵的行對應的last_update 和  innodb_table_stats 的last_update , 是一樣的.

The innodb_table_stats and innodb_index_stats tables can be updated manually, which makes it possible to force a specific query optimization plan or test alternative plans without modifying the database. If you manually update statistics, use the FLUSH TABLE tbl_name statement to load the updated statistics.---手動觸發更新

Persistent statistics are considered local information, because they relate to the server instance. The innodb_table_stats and innodb_index_stats tables are therefore not replicated when automatic statistics recalculation takes place. If you run ANALYZE TABLE to initiate a synchronous recalculation of statistics, the statement is replicated (unless you suppressed logging for it), and recalculation takes place on replicas.---命令是實例級別的,在主從環境下,要小心.

其它說明

To immediately update statistics, run ANALYZE TABLE (if innodb_stats_auto_recalc is enabled, statistics are updated automatically within a few seconds assuming that the 10% threshold for changed table rows is reached)

Retrieving Index Size Using the innodb_index_stats Table

 You can retrieve the index size for tables, partitions, or subpartitions can using the innodb_index_stats table.In the following example, index sizes are retrieved for table t1.

SELECT SUM(stat_value) pages, index_name,
 SUM(stat_value)*@@innodb_page_size size
 FROM mysql.innodb_index_stats WHERE table_name='t1' AND stat_name = 'size' GROUP BY index_name;

For partitions or subpartitions, you can use the same query with a modified WHERE clause to retrieve index sizes.For example, the following query retrieves index sizes for partitions of table t1:

 SELECT SUM(stat_value) pages, index_name,
 SUM(stat_value)*@@innodb_page_size size
 FROM mysql.innodb_index_stats WHERE table_name like 't1#P%'
 AND stat_name = 'size' GROUP BY index_name;

 

---https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • SystemEvents 是一個開發 win32 視窗項目很常用的類,其中封裝了一些常用的系統廣播消息。在 WinUI3 項目中,SystemEvents 事件經常無法觸發,簡單排查了一下原因。 SystemEvent 內封裝了一個線程和一個視窗,通過視窗消息在內部線程上調用事件,內部使用了 Sys ...
  • 大家好,我是痞子衡,是正經搞技術的痞子。今天痞子衡給大家介紹的是i.MXRT1xxx系列GPIO提早供電會影響上電時序導致內部DCDC啟動失敗。 最近有一個 RW612 產品線的同事在設計一個雙 MCU 系統 Demo 時發現,當 RW612 板卡和 RT1060 板卡通過 UART 對接時,如果 ...
  • 目錄微型電腦的硬體共性結構及基本性能指標關於存儲器的介紹微型電腦的基本性能指標1. 字長2. 主頻3. 存儲容量4. 外設擴展能力5. 軟體配置情況Arm Cortex 系列微處理器系列概述Arm Cortex-A 系列處理器Arm Cortex-R 系列處理器Arm Cortex-M 系列處理 ...
  • 目錄遠程策略配置啟用遠程桌面使用設置啟用遠程桌面使用控制面板啟用遠程桌面 工作中有時需要使用遠程桌面,但工控機上面的策略一般都比較保守,遠程桌面經常會失敗。這裡記錄一下使用的遠程策略配置,方便以後工作中使用。 遠程策略配置 運行命令 gpedit.msc 打開本地策略編輯: 打開 電腦配置->管理 ...
  • 參考 Fedora Quick Docs Fedora Server Documentation Deploy an ARM64 Fedora VM on your PC: 3 steps Architectures/AArch64/Install with QEMU Virtualization ...
  • 華為雲GeminiDB是一款相容Redis協議的彈性KV資料庫,支持遠超記憶體的容量和極致的性能,技術自主創新,不受Redis協議變更影響。 ...
  • 資料庫三大範式的學習與資料庫表設計的瞭解 內容簡單介紹 對於資料庫三大範式的理解以及一些設計表示要註意的方面 本章內容梳理圖 資料庫三大範式比較官方的定義 資料庫的三大範式(Normal Forms)是關係資料庫設計中用於確保數據結構化、減少數據冗餘、並提高數據完整性的指導和規則。 以下是三大範式的 ...
  • 1.背景概述 最近在做數據同步測試,需要通過DTS將kafka中的數據同步到資料庫中,4G的數據量同步到資料庫用了大約4個多小時,這看起來並不合理;此時查看資料庫所在主機的CPU,IO的使用率都不高,沒有瓶頸;最後通過排查發現由於kafka,DTS,資料庫不再同一個機房,網路延遲較大,導致同步速率緩 ...
一周排行
    -Advertisement-
    Play Games
  • 隨著Aspire發佈preview5的發佈,Microsoft.Extensions.ServiceDiscovery隨之更新, 服務註冊發現這個屬於老掉牙的話題解決什麼問題就不贅述了,這裡主要講講Microsoft.Extensions.ServiceDiscovery(preview5)以及如何 ...
  • 概述:通過使用`SemaphoreSlim`,可以簡單而有效地限制非同步HTTP請求的併發量,確保在任何給定時間內不超過20個網頁同時下載。`ParallelOptions`不適用於非同步操作,但可考慮使用`Parallel.ForEach`,儘管在非同步場景中謹慎使用。 對於併發非同步 I/O 操作的數量 ...
  • 1.Linux上安裝Docken 伺服器系統版本以及內核版本:cat /etc/redhat-release 查看伺服器內核版本:uname -r 安裝依賴包:yum install -y yum-utils device-mapper-persistent-data lvm2 設置阿裡雲鏡像源:y ...
  • 概述:WPF界面綁定和渲染大量數據可能導致性能問題。通過啟用UI虛擬化、非同步載入和數據分頁,可以有效提高界面響應性能。以下是簡單示例演示這些優化方法。 在WPF中,當你嘗試綁定和渲染大量的數據項時,性能問題可能出現。以下是一些可能導致性能慢的原因以及優化方法: UI 虛擬化: WPF提供了虛擬化技術 ...
  • 引言 上一章節介紹了 TDD 的三大法則,今天我們講一下在單元測試中模擬對象的使用。 Fake Fake - Fake 是一個通用術語,可用於描述 stub或 mock 對象。 它是 stub 還是 mock 取決於使用它的上下文。 也就是說,Fake 可以是 stub 或 mock Mock - ...
  • 為.net6在CentOS7上面做準備,先在vmware虛擬機安裝CentOS 7.9 新建CentOS764位的系統 因為CentOS8不更新了,所以安裝7;簡單就一筆帶過了 選擇下載好的操作系統的iso文件,下載地址https://mirrors.aliyun.com/centos/7.9.20 ...
  • 經過前面幾篇的學習,我們瞭解到指令的大概分類,如:參數載入指令,該載入指令以 Ld 開頭,將參數載入到棧中,以便於後續執行操作命令。參數存儲指令,其指令以 St 開頭,將棧中的數據,存儲到指定的變數中,以方便後續使用。創建實例指令,其指令以 New 開頭,用於在運行時動態生成並初始化對象。方法調用指... ...
  • LiteDB 是一個輕量級的嵌入式 NoSQL 資料庫,其設計理念與 MongoDB 類似,但它是完全使用 C# 開發的,因此與 C# 應用程式的集成非常順暢。與 SQLite 相比,LiteDB 提供了 NoSQL(即鍵值對)的數據存儲方式,並且是一個開源且免費的項目。它適用於桌面、移動以及 We ...
  • 1 開源解析和拆分文檔 第三方的工具去對文件解析拆分,去將我們的文件內容給提取出來,並將我們的文檔內容去拆分成一個小的chunk。常見的PDF word mark down, JSON、HTML。都可以有很好的一些模塊去把這些文件去進行一個東西去提取。 優勢 支持豐富的文檔類型 每種文檔多樣化選擇 ...
  • OOM是什麼?英文全稱為 OutOfMemoryError(記憶體溢出錯誤)。當程式發生OOM時,如何去定位導致異常的代碼還是挺麻煩的。 要檢查OOM發生的原因,首先需要瞭解各種OOM情況下會報的異常信息。這樣能縮小排查範圍,再結合異常堆棧、heapDump文件、JVM分析工具和業務代碼來判斷具體是哪 ...