mirror of
https://github.com/MariaDB/server.git
synced 2025-12-23 04:35:45 +01:00
6ca9ae4e40
Problem: ======== When persistent statistics are enabled (innodb_stats_persistent=ON), the innodb_stats_method setting was not being properly passed through the statistics calculation chain. This caused NULL handling to always use the default behavior, regardless of the configured stats method. The issue affected query optimization for queries involving NULL values, as the statistics collection wasn't respecting the user's preference for NULL value treatment (NULLS_EQUAL, NULLS_UNEQUAL, or NULLS_IGNORED). Solution: ======== 1. Passed innodb_stats_method parameter through the statistics calculation chain: - dict_stats_scan_page() - dict_stats_analyze_index_below_cur() - dict_stats_analyze_index_for_n_prefix() - dict_stats_analyze_index_level - dict_stats_analyze_index - dict_stats_update_persistent - dict_stats_save 2. Conditionally set n_non_null_key_vals based on the NULLS_IGNORED method. 3. Introduced IndexLevelStats which is to collect statistics at a specific B-tree level during index analysis 4. Introduced LeafPageStats which is to collect statistics for leaf page analysis 5. IndexLevelStats, LeafPageStats replaces multiple individual parameters in function signatures. 6. Add stats method name to stat_description in case of non default innodb_stats_method variable value 7. When InnoDB scan the leaf page directly, assign leaf page count as number of pages scanned in case of multi-level index. For single page indexes, use 1. This change leads to multiple changes in existing test case.
28 lines
2.9 KiB
Text
28 lines
2.9 KiB
Text
--- stats_method.result
|
|
+++ stats_method,NULLS_IGNORED.result
|
|
@@ -28,9 +28,9 @@
|
|
WHERE table_schema = 'test'
|
|
AND table_name = 't_stats_method';
|
|
stats_method index_name est_rows
|
|
-nulls_equal PRIMARY 100
|
|
-nulls_equal idx_col1 11
|
|
-nulls_equal idx_col2 6
|
|
+nulls_ignored PRIMARY 100
|
|
+nulls_ignored idx_col1 20
|
|
+nulls_ignored idx_col2 10
|
|
DROP TABLE t_stats_method;
|
|
SET NAMES utf8mb4;
|
|
BEGIN NOT ATOMIC
|
|
@@ -68,10 +68,10 @@
|
|
where table_name="t1" and index_name="PRIMARY"
|
|
and stat_name="n_diff_pfx01";
|
|
stat_name stat_description
|
|
-n_diff_pfx01 c01꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵
|
|
+n_diff_pfx01 c01꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵ NULLS_IGNORED
|
|
SELECT stat_name, stat_description FROM mysql.innodb_index_stats
|
|
where table_name="t1" and index_name="idx_all"
|
|
and stat_name="n_diff_pfx07";
|
|
stat_name stat_description
|
|
-n_diff_pfx07 c33꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵,c34꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵,c35꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵,c36꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵,c37꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵
|
|
+n_diff_pfx07 c33꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵,c34꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵,c35꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵,c36꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵,c37꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵꠵ NULLS_IGNORED
|
|
DROP TABLE t1;
|