Статистики на основе гистограмм

MariaDB, начиная с 10.4.3

Гистограммы собираются по умолчанию начиная с MariaDB 10.4.3.

Статистики на основе гистограмм — это механизм, улучшающий выбор плана запроса оптимизатором в определённых ситуациях. До их появления все условия по столбцам без индексов игнорировались при поиске лучшего плана выполнения. Гистограммы могут собираться для столбцов с индексом и без него и предоставляются оптимизатору.

Статистики гистограммы хранятся в таблице mysql.column_stats, которая хранит данные для статистики таблицы, независимой от движка, и, таким образом, являются подмножеством независимых от движка статистик таблицы.

Рассмотрим этот пример с использованием следующего запроса:

SELECT * FROM t1,t2 WHERE t1.a=t2.a and t2.b BETWEEN 1 AND 3;

Предположим, что

таблица t1 содержит 100 записей
таблица t2 содержит 1000 записей
есть первичный индекс на t1(a)
есть вторичный индекс на t2(a)
нет индекса, определённого для столбца t2.b
селективность условия t2.b BETWEEN (1,3) высокая (~1%)

До появления гистограмм оптимизатор выбирал план, который:

обращался к t1 с помощью сканирования таблицы
обращался к t2 с помощью индекса t2(a)
проверял условие t2.b BETWEEN 1 AND 3

Этот план проверяет все строки обеих таблиц и выполняет 100 поисков по индексу.

При наличии гистограмм оптимизатор может выбрать следующий, более эффективный план:

обращается к таблице t2 с помощью сканирования таблицы
проверяет условие t2.b BETWEEN 1 AND 3
обращается к t1 с помощью индекса t1(a)

Этот план также проверяет все строки из t2, но выполняет только 10 поисков для доступа к 10 строкам таблицы t1.

Системные переменные

Существует ряд системных переменных, влияющих на гистограммы.

histogram_size

Переменная histogram_size определяет размер в байтах (от 0 до 255), используемый для гистограммы. Это фактически количество ячеек для histogram_type=SINGLE_PREC_HB или количество ячеек/2 для histogram_type=DOUBLE_PREC_HB. Если она установлена в 0 (значение по умолчанию для MariaDB 10.4.2 и ниже), гистограммы не создаются при выполнении ANALYZE TABLE.

histogram_type

Переменная histogram_type определяет, создаются ли гистограммы с одинарной (SINGLE_PREC_HB) или двойной (DOUBLE_PREC_HB) точностью с выравненными по высоте. Начиная с MariaDB 10.4.3, двойная точность является значением по умолчанию. Для MariaDB 10.4.2 и ниже — одинарная точность.

Начиная с MariaDB 10.8, JSON_HB, поддерживаются гистограммы в формате JSON.

optimizer_use_condition_selectivity

Переменная optimizer_use_condition_selectivity контролирует, какие статистические данные может использовать оптимизатор при поиске лучшего плана выполнения запросов.

1 Использует селективность предикатов, как в MariaDB 5.5.
2 Использует селективность всех предикатов диапазона, поддерживаемых индексами.
3 Использует селективность всех предикатов диапазона, оценённых без гистограммы.
4 Использует селективность всех предикатов диапазона, оценённых с помощью гистограммы.
5 Дополнительно использует селективность определённых не диапазонных предикатов, вычисленных на выборке записей.

Начиная с MariaDB 10.4.1, значение по умолчанию 4. До MariaDB 10.4.0, значение по умолчанию 1.

Пример

Вот пример драматического влияния статистик на основе гистограмм. Запрос основан на DBT3 Benchmark Q20 с 60 миллионами записей в таблице lineitem.

select sql_calc_found_rows s_name, s_address from 
supplier, nation where 
  s_suppkey in
    (select ps_suppkey from partsupp where
      ps_partkey in (select p_partkey from part where 
         p_name like 'forest%') and 
    ps_availqty > 
      (select 0.5 * sum(l_quantity) from lineitem where
        l_partkey = ps_partkey and l_suppkey = ps_suppkey and
        l_shipdate >= date('1994-01-01') and
        l_shipdate < date('1994-01-01') + interval '1' year ))
  and s_nationkey = n_nationkey
  and n_name = 'CANADA'
  order by s_name
  limit 10;

Сначала,

set optimizer_switch='materialization=off,semijoin=off';

+---+-------- +----------+-------+...+------+----------+------------
| id| sel_type| table    | type  |...| rows | filt | Extra
+---+-------- +----------+-------+...+------+----------+------------
| 1 | PRIMARY | nation   | ALL   |...| 25   |100.00 | Using where;...
| 1 | PRIMARY | supplier | ref   |...| 1447 |100.00 | Using where; Subq
| 2 | DEP SUBQ| partsupp | idxsq |...| 38   |100.00 | Using where
| 4 | DEP SUBQ| lineitem | ref   |...| 3    |100.00 | Using where
| 3 | DEP SUBQ| part     | unqsb |...| 1    |100.00 | Using where
+---+-------- +----------+-------+...+------+----------+------------

10 rows in set
(51.78 sec)

Далее, очень плохой план, но иногда выбираемый:

+---+-------- +----------+-------+...+------+----------+------------
| id| sel_type| table    | type  |...| rows | filt | Extra
+---+-------- +----------+-------+...+------+----------+------------
| 1 | PRIMARY | supplier | ALL   |...|100381|100.00 | Using where; Subq
| 1 | PRIMARY | nation   | ref   |...| 1    |100.00 | Using where
| 2 | DEP SUBQ| partsupp | idxsq |...| 38   |100.00 | Using where
| 4 | DEP SUBQ| lineitem | ref   |...| 3    |100.00 | Using where
| 3 | DEP SUBQ| part     | unqsb |...| 1    |100.00 | Using where
+---+-------- +----------+-------+...+------+----------+------------

10 rows in set
(7 min 33.42 sec)

Постоянные статистические данные не улучшают ситуацию:

set use_stat_tables='preferably';
+---+-------- +----------+-------+...+------+----------+------------
| id| sel_type| table    | type  |...| rows | filt | Extra
+---+-------- +----------+-------+...+------+----------+------------
| 1 | PRIMARY | supplier | ALL   |...|10000 |100.00 | Using where;
| 1 | PRIMARY | nation   | ref   |...| 1    |100.00 | Using where
| 2 | DEP SUBQ| partsupp | idxsq |...| 80   |100.00 | Using where
| 4 | DEP SUBQ| lineitem | ref   |...| 7    |100.00 | Using where
| 3 | DEP SUBQ| part     | unqsb |...| 1    |100.00 | Using where
+---+-------- +----------+-------+...+------+----------+------------

10 rows in set
(7 min 40.44 sec)

Флаги по умолчанию для optimizer_switch не сильно помогают:

set optimizer_switch='materialization=default,semijoin=default';
+---+-------- +----------+-------+...+------+----------+------------
| id| sel_type| table    | type  |...| rows  | filt  | Extra
+---+-------- +----------+-------+...+------+----------+------------
| 1 | PRIMARY | supplier | ALL   |...|10000  |100.00 | Using where;
| 1 | PRIMARY | nation   | ref   |...| 1     |100.00 | Using where
| 1 | PRIMARY | <subq2>  | eq_ref|...| 1     |100.00 |
| 2 | MATER   | part     | ALL   |.. |2000000|100.00 | Using where
| 2 | MATER   | partsupp | ref   |...| 4     |100.00 | Using where; Subq
| 4 | DEP SUBQ| lineitem | ref   |...| 7     |100.00 | Using where
+---+-------- +----------+-------+...+------+----------+------------

10 rows in set
(5 min 21.44 sec)

Использование статистических данных тоже не помогает:

set optimizer_switch='materialization=default,semijoin=default';
set optimizer_use_condition_selectivity=4;

+---+-------- +----------+-------+...+------+----------+------------
| id| sel_type| table    | type  |...| rows  | filt  | Extra
+---+-------- +----------+-------+...+------+----------+------------
| 1 | PRIMARY | nation   | ALL   |...| 25    |4.00   | Using where
| 1 | PRIMARY | supplier | ref   |...| 4000  |100.00 | Using where;
| 1 | PRIMARY | <subq2>  | eq_ref|...| 1     |100.00 |
| 2 | MATER   | part     | ALL   |.. |2000000|1.56   | Using where
| 2 | MATER   | partsupp | ref   |...| 4     |100.00 | Using where; Subq
| 4 | DEP SUBQ| lineitem | ref   |...| 7     | 30.72 | Using where
+---+-------- +----------+-------+...+------+----------+------------

10 rows in set
(5 min 22.41 sec)

Теперь, учитывая стоимость зависимого подзапроса:

set optimizer_switch='materialization=default,semijoin=default';
set optimizer_use_condition_selectivity=4;
set optimizer_switch='expensive_pred_static_pushdown=on';
+---+-------- +----------+-------+...+------+----------+------------
| id| sel_type| table    | type  |...| rows | filt  | Extra
+---+-------- +----------+-------+...+------+----------+------------
| 1 | PRIMARY | nation   | ALL   |...| 25   | 4.00  | Using where
| 1 | PRIMARY | supplier | ref   |...| 4000 |100.00 | Using where;
| 2 | PRIMARY | partsupp | ref   |...| 80   |100.00 |
| 2 | PRIMARY | part     | eq_ref|...| 1    | 1.56  | where; Subq; FM
| 4 | DEP SUBQ| lineitem | ref   |...| 7    | 30.72 | Using where
+---+-------- +----------+-------+...+------+----------+------------

10 rows in set
(49.89 sec)

Наконец, используя join_buffer:

set optimizer_switch= 'materialization=default,semijoin=default';
set optimizer_use_condition_selectivity=4;
set optimizer_switch='expensive_pred_static_pushdown=on';
set join_cache_level=6;
set optimizer_switch='mrr=on';
set optimizer_switch='mrr_sort_keys=on';
set join_buffer_size=1024*1024*16;
set join_buffer_space_limit=1024*1024*32;
+---+-------- +----------+-------+...+------+----------+------------
| id| sel_type| table    | type  |...| rows | filt |  Extra
+---+-------- +----------+-------+...+------+----------+------------
| 1 | PRIMARY | nation   | AL  L |...| 25   | 4.00  | Using where
| 1 | PRIMARY | supplier | ref   |...| 4000 |100.00 | where; BKA
| 2 | PRIMARY | partsupp | ref   |...| 80   |100.00 | BKA
| 2 | PRIMARY | part     | eq_ref|...| 1    | 1.56  | where Sq; FM; BKA
| 4 | DEP SUBQ| lineitem | ref   |...| 7    | 30.72 | Using where
+---+-------- +----------+-------+...+------+----------+------------

10 rows in set
(35.71 sec)

См. также

Содержимое, воспроизводимое на этом сайте, является собственностью соответствующих владельцев, и это содержимое не проверяется предварительно MariaDB. Мнения, информация и мнения, выраженные в этом содержимом, не обязательно отражают точку зрения MariaDB или любой другой стороны.

© 2023 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/histogram-based-statistics/