SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 03.01.2022 19:06:26

Kot666
Участник
Откуда: Санкт-Петербург
Зарегистрирован: 03.01.2022
Сообщений: 7

Большое количество уникальных значений в индексе

Добрый день!
Почему-то в одном из индексов (`user_id`) слишком много уникальных значений, хотя реально их 33.
Надо из-за этого беспокоиться?

mysql> SHOW CREATE TABLE `status_list`;
CREATE TABLE `status_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orders_id` int(11) NOT NULL,
  `order` int(3) unsigned DEFAULT '0',
  `status_name_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `set` int(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `orders_id` (`orders_id`),
  KEY `status_name_id` (`status_name_id`),
  KEY `user_id` (`user_id`),
  KEY `set` (`set`),
  KEY `date` (`date`),
  KEY `order` (`order`),
  KEY `ord_st_name_id` (`orders_id`,`status_name_id`),
  KEY `order_desc` (`order`)
) ENGINE=MyISAM AUTO_INCREMENT=570455 DEFAULT CHARSET=cp1251;

mysql> SHOW INDEX FROM `status_list`;
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| status_list |          0 | PRIMARY        |            1 | id             | A         |      569911 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | orders_id      |            1 | orders_id      | A         |       71238 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | status_name_id |            1 | status_name_id | A         |          22 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | user_id        |            1 | user_id        | A         |      189970 |     NULL | NULL   | YES  | BTREE      |         |               |
| status_list |          1 | set            |            1 | set            | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | date           |            1 | date           | A         |      284955 |     NULL | NULL   | YES  | BTREE      |         |               |
| status_list |          1 | order          |            1 | order          | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| status_list |          1 | ord_st_name_id |            1 | orders_id      | A         |       71238 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | ord_st_name_id |            2 | status_name_id | A         |      569911 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | order_desc     |            1 | order          | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> SELECT COUNT(DISTINCT `user_id`) FROM `status_list`;
+---------------------------+
| COUNT(DISTINCT `user_id`) |
+---------------------------+
|                        33 |
+---------------------------+
 

Неактивен

 

#2 03.01.2022 20:05:24

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3874

Re: Большое количество уникальных значений в индексе

Это может приводить к выбору неоптимального порядка исполнения запросов JOIN. Попробуйте

OPTIMIZE TABLE `status_list`

Неактивен

 

#3 03.01.2022 20:10:23

Kot666
Участник
Откуда: Санкт-Петербург
Зарегистрирован: 03.01.2022
Сообщений: 7

Re: Большое количество уникальных значений в индексе

mysql> OPTIMIZE TABLE `status_list`;
+--------------------------+----------+----------+-----------------------------+
| Table                    | Op       | Msg_type | Msg_text                    |
+--------------------------+----------+----------+-----------------------------+
|status_list               | optimize | status   | Table is already up to date |
+--------------------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM `status_list`;
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| status_list |          1 | user_id        |            1 | user_id        | A         |      189970 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)
 

Отредактированно Kot666 (03.01.2022 20:11:37)

Неактивен

 

#4 03.01.2022 20:34:25

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3874

Re: Большое количество уникальных значений в индексе

А какая версия MySQL? Если таблица относительно небольшая, можно перестроить индексы такой командой:

ALTER TABLE `status_list` ENGINE=`MyISAM`;

Неактивен

 

#5 03.01.2022 20:58:17

Kot666
Участник
Откуда: Санкт-Петербург
Зарегистрирован: 03.01.2022
Сообщений: 7

Re: Большое количество уникальных значений в индексе

mysql> SELECT VERSION();
+---------------------+
| VERSION()           |
+---------------------+
| 5.5.60-0+deb7u1-log |
+---------------------+
1 row in set (0.00 sec)
 
Специально перенёс для проверки на MariaDB 10.3 - "те же яйца". При том, что базу я импортировал и он создал заново индексы.
Размер в соответствии с `PRIMARY`. Но перестроить не проблема. smile

mysql> ALTER TABLE `status_list` ENGINE=`MyISAM`;
Query OK, 569911 rows affected (14.93 sec)
Records: 569911  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM `status_list`;
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| status_list |          0 | PRIMARY        |            1 | id             | A         |      569911 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | orders_id      |            1 | orders_id      | A         |       71238 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | status_name_id |            1 | status_name_id | A         |          22 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | user_id        |            1 | user_id        | A         |      189970 |     NULL | NULL   | YES  | BTREE      |         |               |
| status_list |          1 | set            |            1 | set            | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | date           |            1 | date           | A         |      284955 |     NULL | NULL   | YES  | BTREE      |         |               |
| status_list |          1 | order          |            1 | order          | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| status_list |          1 | ord_st_name_id |            1 | orders_id      | A         |       71238 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | ord_st_name_id |            2 | status_name_id | A         |      569911 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | order_desc     |            1 | order          | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)
 
Проверил на MariaDB - тот же результат.

Отредактированно Kot666 (03.01.2022 22:03:59)

Неактивен

 

#6 04.01.2022 00:11:07

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3874

Re: Большое количество уникальных значений в индексе

Возможно, Вы попали на багу алгоритма оценки Cardinality. Баги такого типа уже были. Если можете изготовить дамп, на котором проблема воспроизводится, то можно сообщить о баге на https://bugs.mysql.com

Это именно бага MyISAM? Изменится ли поведение, если заменить ENGINE на InnoDB?

Неактивен

 

#7 04.01.2022 02:08:11

Kot666
Участник
Откуда: Санкт-Петербург
Зарегистрирован: 03.01.2022
Сообщений: 7

Re: Большое количество уникальных значений в индексе

rgbeast написал:

Возможно, Вы попали на багу алгоритма оценки Cardinality. Баги такого типа уже были.

Да, именно. Я прочёл про этот баг, поднял ВМ, установил MariaDB 10.3, импортировал базу, изменил "engine" на "Aria".
Проблема осталась. Написал сюда. smile

Неактивен

 

#8 04.01.2022 03:39:24

Kot666
Участник
Откуда: Санкт-Петербург
Зарегистрирован: 03.01.2022
Сообщений: 7

Re: Большое количество уникальных значений в индексе

Действительно стало "получше", но не точно. При обратной конвертации всё вернулось. mad

MariaDB> ALTER TABLE `status_list` ENGINE='InnoDB' TRANSACTIONAL=default;
Query OK, 569847 rows affected (12.649 sec)
Records: 569847  Duplicates: 0  Warnings: 0

MariaDB> SHOW INDEX FROM `status_list`;
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| status_list |          1 | user_id        |            1 | user_id        | A         |          62 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB> ALTER TABLE `status_list` ENGINE='Aria' TRANSACTIONAL=default;
Query OK, 569847 rows affected (7.205 sec)
Records: 569847  Duplicates: 0  Warnings: 0

MariaDB> SHOW INDEX FROM `status_list`;
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| status_list |          1 | user_id        |            1 | user_id        | A         |      189949 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB> SELECT VERSION();
+------------------------------------------+
| VERSION()                                |
+------------------------------------------+
| 10.3.8-MariaDB-1:10.3.8+maria~wheezy-log |
+------------------------------------------+
1 row in set (0.029 sec)

Отредактированно Kot666 (04.01.2022 03:43:12)

Неактивен

 

#9 04.01.2022 09:59:43

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3874

Re: Большое количество уникальных значений в индексе

Cardinality в статистике индексов - оценочное значение. Точный расчет занимал бы время при каждом апдейте таблицы, поэтому нормой считается правильное по порядку величины значение. Значение в MyISAM отличается на 3-4 порядка, поэтому такое поведение нужно считать багой.

Неактивен

 

#10 04.01.2022 11:45:08

Kot666
Участник
Откуда: Санкт-Петербург
Зарегистрирован: 03.01.2022
Сообщений: 7

Re: Большое количество уникальных значений в индексе

Меня беспокоит, не деградирует ли от этого скорость выборок.
Из первого поста:

+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| status_list |          1 | orders_id      |            1 | orders_id      | A         |       71238 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | status_name_id |            1 | status_name_id | A         |          22 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | ord_st_name_id |            1 | orders_id      | A         |       71238 |     NULL | NULL   |      | BTREE      |         |               |
| status_list |          1 | ord_st_name_id |            2 | status_name_id | A         |      569911 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


Есть составной индекс (`ord_st_name_id`) для которого "посчитано" неправильно. Хотя для составляющих его индексов всё в порядке.
При добавлении этого составного индекса, некоторые запросы стали выполняться ощутимо быстрее, хотя он в их "explain" не фигурирует. После этого и стал разбираться. В принципе, каких-то серьёзных претензий к скорости работы нет.

Неактивен

 

#11 05.01.2022 14:39:04

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3874

Re: Большое количество уникальных значений в индексе

Статистика индексов влияет только на план исполнения. Когда план выбран, скорость исполнения не должна зависеть от статистики.

А какой именно запрос стал быстрее работать? Странно, что скорость исполнения изменилась, а план тот же.

Неактивен

 

#12 06.01.2022 13:02:20

Kot666
Участник
Откуда: Санкт-Петербург
Зарегистрирован: 03.01.2022
Сообщений: 7

Re: Большое количество уникальных значений в индексе

rgbeast
К сожалению не смог воспроизвести, удаляя/добавляя индексы, а "slow"-log уже стёрся. Точно не могу сказать и не хочу случайно обмануть.
Если смогу воспроизвести, то обязательно напишу.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson