В последнее время я был занят решением некоторых проблем с оптимизацией SQL, оставшихся от исходного старого проекта.Из-за проблем с оригинальным дизайном таблиц и дизайном полей, с ростом бизнеса появилось большое количество медленных SQL, вызывающих ресурсы ЦП. MySQL, чтобы парить.Основываясь на этом, дайте Мы просто делимся опытом этих сравнений, которые просты в освоении и использовании.
На этот раз я кратко расскажу о том, как предотвратить аннулирование вашего индекса.
Прежде чем говорить об этом, я расскажу о своих взглядах на индексы, основанных на моем недавнем опыте.Я не думаю, что все таблицы нужно индексировать.Для некоторых бизнес-данных объем может быть относительно большим, а там уже немного давление на запрос данных, то Самый простой и быстрый способ - создать подходящий индекс, но у некоторых предприятий может быть не так много данных в таблице, или если таблица используется очень редко, нет необходимости делать индекс. Как и в некоторых наших таблицах, за 2 года может быть около 10 единиц данных, а производительность с индексами и без них примерно одинакова.
Индексация — это просто способ оптимизировать наш бизнес, и мы должны создавать индекс ради индексации.
Ниже приведена структура таблицы и некоторые тестовые данные, которые я использовал в этом тесте.
CREATE TABLE `user` (
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`create_time` datetime NOT NULL,
`name` varchar(5) NOT NULL,
`age` tinyint(2) unsigned zerofill NOT NULL,
`sex` char(1) NOT NULL,
`mobile` char(12) NOT NULL DEFAULT '',
`address` char(120) DEFAULT NULL,
`height` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_createtime` (`create_time`) USING BTREE,
KEY `idx_name_age_sex` (`name`,`sex`,`age`) USING BTREE,
KEY `idx_ height` (`height`) USING BTREE,
KEY `idx_address` (`address`) USING BTREE,
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (1, '2019-09-02 10:17:47', '冰峰', 22, '男', '1', '陕西省咸阳市彬县', '175');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (2, '2020-09-02 10:17:47', '松子', 13, '女', '1', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (3, '2020-09-02 10:17:48', '蚕豆', 20, '女', '1', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (4, '2020-09-02 10:17:47', '冰峰', 20, '男', '17765010977', '陕西省西安市', '155');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (255, '2020-09-02 10:17:47', '竹笋', 22, '男', '我测试下可以储存几个中文', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (256, '2020-09-03 10:17:47', '冰峰', 21, '女', '', NULL, '167');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (257, '2020-09-02 10:17:47', '小红', 20, '', '', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (258, '2020-09-02 10:17:47', '小鹏', 20, '', '', NULL, '188');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (259, '2020-09-02 10:17:47', '张三', 20, '', '', NULL, '180');
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (260, '2020-09-02 10:17:47', '李四', 22, '', '', NULL, '165');
единый индекс
1. Используйте != или , чтобы аннулировать индекс
SELECT * FROM `user` WHERE `name` != '冰峰';
Мы создали индекс для поля имени, но если != или это приведет к сбою индекса и выполнению полного сканирования таблицы, поэтому, если объем данных велик, используйте его с осторожностью.
Из анализа SQL видно, что тип ALL, сканируется 10 строк данных и выполняется полное сканирование таблицы. — тот же результат.
2. Инвалидация индекса из-за несоответствия типов
Прежде чем говорить об этом, я должен сказать, что при проектировании полей таблицы вы должны, должны и должны поддерживать согласованность типов полей.Что вы имеете в виду? Например, идентификатор пользовательской таблицы увеличивается на int, и при достижении поля user_id пользовательской таблицы учетных записей он должен и должен также иметь тип int.
SELECT * FROM `user` WHERE height= 175;
Вы должны четко прочитать этот SQL.Тип поля таблицы высоты - varchar, но я использую числовой тип, когда я запрашиваю.Поскольку в середине есть неявное преобразование типа, это приведет к сбою индекса и выполнению полной таблицы сканирование.
Теперь я понимаю, почему я сказал, что при проектировании полей вы должны поддерживать согласованность типов.Если вы не гарантируете согласованность, один int и один varchar неизбежно потерпят неудачу в многотабличном объединенном запросе (например: 1 = '1').index.
Когда вы сталкиваетесь с такой таблицей, в ней десятки миллионов данных, и вы не можете ее изменить, вы все еще можете испытывать боль до поры до времени.
Мальчики, помните, помните.
3. Сбой индекса, вызванный функцией
SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';
Если ваше индексное поле использует индекс, извините, он действительно не использует индекс.
4. Сбой индекса по вине оператора
SELECT * FROM `user` WHERE age - 1 = 20;
Если вы выполните (+, -, *, /,!) для столбца, то индекс не будет взят.
5. Сбой индекса, вызванный ИЛИ
SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';
OR приводит к тому, что индекс находится в определенной ситуации. Не все OR делают индекс недействительным. Если OR связано с одним и тем же полем, индекс не будет ошибочным, в противном случае произойдет сбой индекса.
6. Сбой индекса, вызванный нечетким поиском
SELECT * FROM `user` WHERE `name` LIKE '%冰';
Думаю все это понимают, если еще и по префиксу выполнять нечеткий поиск, то индекс не возьмут.
7. NOT IN, NOT EXISTS вызывают сбой индекса
SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` = '冰峰')
SELECT * FROM `user` WHERE `name` NOT IN ('冰峰');
Оба этих использования также сделают индекс недействительным. Но NOT IN по-прежнему индексируется, не поймите неправильно, что все IN не индексируется. Я неправильно понял раньше (позор...).
8. IS NULL не идет в индекс, IS NOT NULL идет в индекс
SELECT * FROM `user` WHERE address IS NULL
Не заходите на индекс.
SELECT * FROM `user` WHERE address IS NOT NULL;
перейти индекс.
В соответствии с этой ситуацией рекомендуется, чтобы при разработке полей, если нет обязательного требования быть NULL, лучше всего давать значение по умолчанию пустой строки, что может решить много последующих проблем (при большом опыте ).
индекс соответствия
1. Самый левый принцип сопоставления
EXPLAIN SELECT * FROM `user` WHERE sex = '男';
EXPLAIN SELECT * FROM `user` WHERE name = '冰峰' AND sex = '男';
Перед тестированием удалите другие одностолбцовые индексы.
Каков самый левый принцип сопоставления, то есть для совпадающего индекса порядок одного из его индексов сравнивается слева направо, как и во втором операторе запроса, имя проходит через индекс, а затем возвращается, чтобы найти возраст, в условие результата Если нет возраста, то следующий пол в индекс не пойдет.
Уведомление:
SELECT * FROM `user` WHERE sex = '男' AND age = 22 AND `name` = '冰峰';
Возможно, некоторые каменщики вначале меня неправильно поняли. Наш индексный порядок, очевидно, имя, пол, возраст. Ваш текущий порядок запросов — пол, возраст, имя. Это определенно не будет индексироваться. Если вы сами не проверите это Однако есть и такая незрелая идея.Она еще слишком молода, как и я.Это не имеет никакого отношения к порядку, потому что нижний слой mysql поможет нам сделать оптимизацию, он оптимизирует ваш SQL до того, что он думает Наиболее эффективный вид для выполнения. Так что не допускайте этого недоразумения.
2. Если используется !=, все последующие индексы будут недействительны.
SELECT * FROM `user` WHERE sex = '男' AND `name` != '冰峰' AND age = 22;
Мы использовали != в поле имени.Поскольку поле имени является самым левым полем, в соответствии с принципом самого левого сопоставления, если имя не проиндексировано, следующие поля не будут проиндексированы.
Есть только два случая, которые можно сказать о сбое индекса, вызванном индексом.На самом деле, я думаю, что для индекса самое главное, как установить эффективный индекс.Нельзя сказать, что если я использую это поле , я создам отдельный индекс. Индекс можно использовать глобально, не так ли? Это возможно, но это не соответствует эффективности индекса, поэтому, чтобы стать продвинутым каменщиком, нам все еще нужно продолжать учиться создавать эффективный индекс.
- END -