почему mysql объясняет, что шоу с использованием индекса предпочитает столбец bigint, чем столбец int

Недавно у меня возник странный вопрос, структура моей тестовой таблицы:

CREATE TABLE `index_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(64) NOT NULL DEFAULT '',
  `card_no` bigint(20) NOT NULL,
  `card_no2` bigint(20) NOT NULL,
  `optype` int(11) NOT NULL,
  `optype2` int(11) NOT NULL,
  `create_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  `_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_card_no` (`card_no`),
  KEY `idx_card_no2` (`card_no2`),
  KEY `idx_optype` (`optype`),
  KEY `idx_optype2` (`optype2`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

5 основных столбцов, varchar, cardno и cardno2 — bigint, optype и optype2 — int, по моему опыту, индекс mysql предпочитает выбирать высокую кардинальность, малый тип данных и ненулевые столбцы, но когда я запускаю объяснение операторы запроса, возникло несколько проблем, вот моя процедура инициализации данных

DELIMITER ;;
CREATE DEFINER=`xx`@`%` PROCEDURE `simple_insert`( )
BEGIN
  DECLARE counter BIGINT DEFAULT 0;

  my_loop: LOOP
    SET counter=counter+1;

    IF counter=10000 THEN
      LEAVE my_loop;
    END IF;

    INSERT INTO `index_test` (`a`,`card_no`,`card_no2`,`optype`,`optype2`, `create_time`) VALUES (replace(uuid(), '-', ''),counter,counter%180, counter,counter%180,current_timestamp);

  END LOOP my_loop;
END;;
DELIMITER ;

вставьте 10 000 строк данных, сначала я выполняю запрос статистики

select * from information_schema.statistics where table_schema = 'test' and table_name = 'index_test';

вывод

+---------------+--------------+------------+------------+--------------+--------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME   | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT |
+---------------+--------------+------------+------------+--------------+--------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| def           | test         | index_test |          0 | test         | PRIMARY      |            1 | id          | A         |       10089 |     NULL | NULL   |          | BTREE      |         |               |
| def           | test         | index_test |          1 | test         | idx_a        |            1 | a           | A         |        9999 |     NULL | NULL   |          | BTREE      |         |               |
| def           | test         | index_test |          1 | test         | idx_card_no  |            1 | card_no     | A         |        9999 |     NULL | NULL   |          | BTREE      |         |               |
| def           | test         | index_test |          1 | test         | idx_card_no2 |            1 | card_no2    | A         |         180 |     NULL | NULL   |          | BTREE      |         |               |
| def           | test         | index_test |          1 | test         | idx_optype   |            1 | optype      | A         |        9999 |     NULL | NULL   |          | BTREE      |         |               |
| def           | test         | index_test |          1 | test         | idx_optype2  |            1 | optype2     | A         |         180 |     NULL | NULL   |          | BTREE      |         |               |
+---------------+--------------+------------+------------+--------------+--------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+   

шаг 2:

explain select * from index_test where  optype=9600 and a= 'e095af180f4911ea8d907036bd142a99';

вывод:

+----+-------------+------------+------------+------+------------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys    | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+------------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | ref  | idx_a,idx_optype | idx_a | 194     | const |    1 |     5.00 | Using where |
+----+-------------+------------+------------+------+------------------+-------+---------+-------+------+----------+-------------+

по моему опыту, пространство varchar (64) больше, чем int, поэтому использовать столбец int можно.

См. также:  MySQL выбирает максимальную дату и заказы точно в дату

шаг 3:

explain select * from index_test where  optype=9600 and card_no = 9600;

вывод

+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys          | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | index_test | NULL       | ref  | idx_card_no,idx_optype | idx_card_no | 8       | const |    1 |     5.00 | Using where |
+----+-------------+------------+------------+------+------------------------+-------------+---------+-------+------+----------+-------------+

Итак, вопрос в том, почему оптимизатор запросов mysql предпочитает использовать столбец bigint, а не столбец int, любой может мне помочь или дать несколько официальных ссылок на документы по этому вопросу, спасибо.

кстати, моя тестовая среда — macos (10.14.6) x64, а версия сервера mysql — 5.7.26.

Чтобы сделать вопрос более точным, я корректирую имя таблицы и вывод шага, в этом случае я не обсуждаю правила левого префикса mysql, только только порядок выбора индекса оптимизатора запросов mysql (который index лучше всего подходит для этого запроса)   —  person guanzhisong    schedule 26.11.2019

Понравилась статья? Поделиться с друзьями:
IT Шеф
Комментарии: 1
  1. guanzhisong

    Я не думаю, что INT против BIGINT является проблемой. Во-первых, позвольте мне упомянуть лучшие индексы:

    За

    where  optype=9600 and a= 'e095af180f4911ea8d907036bd142a99'
    

    Любой из этих «составных» индексов будет оптимальным и лучше, чем у вас есть:

    INDEX(optype, a)
    INDEX(a, optype)
    

    За

    where  optype=9600
      and card_no = 9600
      and  a= 'e095af180f4911ea8d907036bd142a99'
    

    любой индекс, начинающийся с этих трех столбцов, оптимален; любые 2 будут «хорошими», а индексы с одним столбцом будут плохими, но лучше, чем отсутствие индекса.

    Оптимизатор может проводить исследования, чтобы определить, какой из трех плохих индексов является лучшим.

    Я не могу объяснить, почему он не указал a как «Возможный ключ».

    Составной индекс на самом деле, пожалуй, лучший индекс, но в этом случае я просто создаю три индекса для целей тестирования (три индекса с одним столбцом), и запрос также тестируется для оптимизатора запросов mysql , который является лучшим способом для запроса данных < /b>, как я уже упоминал, индекс mysql предпочитает выбирать высокую кардинальность, небольшой тип данных и ненулевые столбцы, поэтому я просто создаю несколько одинаковых столбцов типа данных (int, bigint varchar) и другие столбцы кардинальность, просто для проверки person guanzhisong; 26.11.2019

    @guanzhisong — при добавлении дополнительных индексов имейте в виду, что INDEX(x,y) устраняет необходимость в INDEX(x). См. также mysql.rjweb.org/doc.php/index_cookbook_mysql. person guanzhisong; 26.11.2019

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: