Недавно у меня возник странный вопрос, структура моей тестовой таблицы:
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 можно.
шаг 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
Источник: