Составной индекс Mysql не одинаков для всех полей

Question

Я создал составной индекс на mysql, вот команда, которую я использую: создать индекс delivery_aid_sid_rcnt_idx для DELIVER_SM(AID,STATUSID,RETRY_CNT) USING BTREE; Странно, что я получил разные значения кардинальности для каждого поля. это нормально? Я создал другой составной индекс, и значение мощности для каждого поля одинаково.

+ -------------- + ----------------- + ------------- + -------------------------+-------------+
| TABLE_SCHEMA | TABLE_NAME      | COLUMN_NAME | INDEX_NAME               | КАРДИНАЛЬНОСТЬ |
+ -------------- + ----------------- + ------------- + -------------------------+-------------+
| prddb        | DELIVER_SM      | ПОМОЩЬ | delivery_aid_sid_rcnt_idx |          28 |
| prddb        | DELIVER_SM      | СТАТУСИД | delivery_aid_sid_rcnt_idx |         286 |
| prddb        | DELIVER_SM      | RETRY_CNT   | delivery_aid_sid_rcnt_idx |         286 |
+--------------+-----------------+-------------+------------------------- + ------------- +
 

Answer 1

Индекс кардинальности - это не количество отдельных значений столбцов, а количество узлов в индексе b-дерева.

Рассмотрим пример ниже:

CREATE TABLE abc(   a int, b int, c int );

set @x = 0;
INSERT INTO abc( a, b, c ) 
SELECT (@x:=@x+1),
       round( @x / 10 ),
       round( @x / 100 )
FROM information_schema.columns
LIMIT 421;

CREATE INDEX ix1 ON abc( a, b, c );
CREATE INDEX ix2 ON abc( c, b, a );

ANALYZE TABLE abc;

и запросы, которые показывают кардинальность индексов:

SELECT COUNT( distinct a) a,
       COUNT( distinct b) b,
       COUNT( distinct c) c,
       COUNT( * )
FROM abc;

SELECT table_name, index_name, column_name, cardinality
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = 'abc' AND index_name = 'ix1';

SELECT table_name, index_name, column_name, cardinality
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = 'abc' AND index_name = 'ix2';

Посмотрите на эту демонстрацию, чтобы увидеть результаты: http://www.sqlfiddle.com/

В таблице 421 ряд.
колонка a имеет 421 различных значений.
колонка b имеет 43 различных значения.
колонка c имеет 5 различных значений.

Я плохой ящик, поэтому я не прикрепляю чертежи этих индексов здесь:)
Но я надеюсь, что вы можете представить себе изображение индекса b-дерева в своей голове, как в этой ссылке: http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm
(Кстати, я рекомендую вам изучить этот материал, он связан с оракулом, а не с MySql, но это отличное объяснение того, как работают индексы и как они организованы).

За ix1 ON abc( a, b, c ) Индекс MySql показывает следующие кардиналы:
а -> 407
б -> 407
с -> 407

Помните, что числа кардинальности не точные значения, а скорее оценки.

Вот a является ведущим столбцом в индексе (это столбец с наибольшим числом различных значений), и поэтому он создает большое количество узлов верхнего уровня в индексе. Остальные столбцы (их значения) также хранятся в (или, возможно, "под") этих индексных узлах верхнего уровня.

Hovever для ix2 ON abc( c, b, a ) Оценочные значения мощности различны:
с -> 9
б -> 101
а -> 407

В этом случае, где c является ведущим столбцом индекса, MySql "думает" (оценивает), что индекс имеет 9 узлов "верхнего уровня", значения b взять 101 узел "ниже c", а также a занимает 407 узлов в индексе.