mirror of
https://github.com/MariaDB/server.git
synced 2026-01-05 02:55:43 +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.
115 lines
3.2 KiB
Text
115 lines
3.2 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_sequence.inc
|
|
|
|
# Test case to demonstrate different innodb_stats_method settings
|
|
CREATE TABLE t_stats_method (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
col1 INT,
|
|
col2 INT,
|
|
INDEX idx_col1 (col1),
|
|
INDEX idx_col2 (col2)
|
|
) ENGINE=InnoDB;
|
|
|
|
# For col1: 50 NULLs, 10 distinct non-NULL values
|
|
# For col2: 50 NULLs, 5 distinct non-NULL values
|
|
INSERT INTO t_stats_method (col1, col2)
|
|
SELECT
|
|
CASE
|
|
WHEN n <= 50 THEN ((n-1) % 10) + 1
|
|
ELSE NULL
|
|
END,
|
|
CASE
|
|
WHEN n <= 50 THEN ((n-1) % 5) + 1
|
|
ELSE NULL
|
|
END
|
|
FROM (
|
|
SELECT seq AS n FROM seq_1_to_100
|
|
) AS numbers;
|
|
|
|
ANALYZE TABLE t_stats_method;
|
|
|
|
# cardinality = number of distinct value
|
|
# Nulls_equal:
|
|
#==============
|
|
# idx_col1: 50 NULLs + 10 distinct value = 11
|
|
# idx_col2: 50 NULLs + 5 distinct value = 6
|
|
|
|
# Nulls_unequal:
|
|
#==============
|
|
# idx_col1: 50 NULLs + 10 distinct value = 60
|
|
# idx_col2: 50 NULLs + 5 distinct value = 55
|
|
|
|
# Nulls_ignored:
|
|
#===============
|
|
|
|
# innodb_rec_per_key =
|
|
# number of non-null records / number of distinct keys
|
|
#
|
|
# cardinality = number of records / innodb_rec_per_key;
|
|
# idx_col1: 50 NULLs + 10 distinct value
|
|
|
|
# innodb_rec_per_key = 50/10 = 5
|
|
# cardinality = 100 / 5 = 20
|
|
|
|
# idx_col2: 50 NULLs + 5 distinct value
|
|
# innodb_rec_per_key = 50 / 5 = 10
|
|
# cardinality = 100 / 10 = 10
|
|
|
|
SELECT @@global.innodb_stats_method AS stats_method,
|
|
index_name,
|
|
cardinality AS est_rows
|
|
FROM information_schema.STATISTICS
|
|
WHERE table_schema = 'test'
|
|
AND table_name = 't_stats_method';
|
|
|
|
DROP TABLE t_stats_method;
|
|
|
|
SET NAMES utf8mb4;
|
|
# Test maximum number of index columns (64) for statistics calculation
|
|
DELIMITER $$;
|
|
BEGIN NOT ATOMIC
|
|
DECLARE c TEXT CHARACTER SET utf8mb4 DEFAULT (
|
|
SELECT CONCAT('CREATE TABLE t1(',
|
|
GROUP_CONCAT(CONCAT('`c', LPAD(seq, 2, '0'), '꠵',
|
|
REPEAT('꠵', 59), '` INT') SEPARATOR ', '), ', ',
|
|
'PRIMARY KEY(',
|
|
(SELECT GROUP_CONCAT(CONCAT('`c', LPAD(seq, 2, '0'), '꠵',
|
|
REPEAT('꠵', 59), '`') SEPARATOR ', ')
|
|
FROM seq_1_to_32),
|
|
'), KEY `idx_all`(',
|
|
(SELECT GROUP_CONCAT(CONCAT('`c', LPAD(seq, 2, '0'), '꠵',
|
|
REPEAT('꠵', 59), '`') SEPARATOR ', ')
|
|
FROM seq_33_to_64),
|
|
')) ENGINE=InnoDB STATS_PERSISTENT=1')
|
|
FROM seq_1_to_64);
|
|
EXECUTE IMMEDIATE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
|
|
INSERT INTO t1
|
|
SELECT seq, seq, seq, seq, seq, seq, seq, seq,
|
|
seq, seq, seq, seq, seq, seq, seq, seq,
|
|
seq, seq, seq, seq, seq, seq, seq, seq,
|
|
seq, seq, seq, seq, seq, seq, seq, seq,
|
|
seq, seq, seq, seq, seq, seq, seq, seq,
|
|
seq, seq, seq, seq, seq, seq, seq, seq,
|
|
seq, seq, seq, seq, seq, seq, seq, seq,
|
|
seq, seq, seq, seq, seq, seq, seq, seq
|
|
FROM seq_1_to_320;
|
|
|
|
ANALYZE TABLE t1;
|
|
|
|
# stats_method appended at the end of stat_description in case
|
|
# of non-default value
|
|
SELECT stat_name, stat_description FROM mysql.innodb_index_stats
|
|
where table_name="t1" and index_name="PRIMARY"
|
|
and stat_name="n_diff_pfx01";
|
|
|
|
# Truncation of stat_description
|
|
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";
|
|
|
|
DROP TABLE t1;
|