mirror of
https://github.com/MariaDB/server.git
synced 2026-02-17 16:18:42 +01:00
57cab3e3a2
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.
76 lines
3.1 KiB
Text
76 lines
3.1 KiB
Text
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;
|
|
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;
|
|
Table Op Msg_type Msg_text
|
|
test.t_stats_method analyze status OK
|
|
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';
|
|
stats_method index_name est_rows
|
|
nulls_equal PRIMARY 100
|
|
nulls_equal idx_col1 11
|
|
nulls_equal idx_col2 6
|
|
DROP TABLE t_stats_method;
|
|
BEGIN NOT ATOMIC
|
|
DECLARE c TEXT DEFAULT (
|
|
SELECT CONCAT('CREATE TABLE t1(',
|
|
GROUP_CONCAT(CONCAT('c', LPAD(seq, 2, '0'), 'x',
|
|
REPEAT('x', 59)) SEPARATOR ' INT, '), ' INT, ',
|
|
(SELECT GROUP_CONCAT(CONCAT('KEY idx', LPAD(seq, 2, '0'),
|
|
'(c', LPAD(seq, 2, '0'), 'x',
|
|
REPEAT('x', 59), ')') SEPARATOR ', ')
|
|
FROM seq_33_to_64),
|
|
', PRIMARY KEY(',
|
|
(SELECT GROUP_CONCAT(CONCAT('c', LPAD(seq, 2, '0'), 'x',
|
|
REPEAT('x', 59)) SEPARATOR ', ')
|
|
FROM seq_1_to_32),
|
|
')) ENGINE=InnoDB STATS_PERSISTENT=1')
|
|
FROM seq_1_to_64);
|
|
EXECUTE IMMEDIATE c;
|
|
END;
|
|
$$
|
|
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;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SELECT stat_name, stat_description FROM mysql.innodb_index_stats
|
|
where table_name="t1" and index_name="PRIMARY"
|
|
and stat_name="n_diff_pfx01";
|
|
stat_name stat_description
|
|
n_diff_pfx01 c01xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
|
SELECT stat_name, stat_description FROM mysql.innodb_index_stats
|
|
where table_name="t1" and index_name="idx33"
|
|
and stat_name="n_diff_pfx17";
|
|
stat_name stat_description
|
|
n_diff_pfx17 c33xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c01xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c02xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c03xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c04xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c05xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c06xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c07xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c08xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c09xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c10xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c11xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c12xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c13xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx,c14xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
|
DROP TABLE t1;
|