Как оптимизировать таблицы MySQL с десятками миллионов данных

задняя часть

Позвольте мне сначала объяснить.Многие люди в Интернете говорят, что Али оговаривает, что данные 500w должны быть разделены на базы данных и таблицы. На самом деле эти 500w не мертвы по определению, а связаны с конфигурацией MySQL и железом машины. Для повышения производительности MySQL загрузит индекс таблицы в память. Однако, когда данные таблицы достигают определенного объема, память не может хранить эти индексы, и индексы не могут быть сохранены, поэтому может выполняться только дисковый ввод-вывод, что приводит к снижению производительности.

Актуальный тюнинг

У меня есть таблица, данные имеют 1000 Вт, и в настоящее время существует только один индекс первичного ключа.

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) DEFAULT NULL COMMENT '账号',
  `pwd` varchar(20) DEFAULT NULL COMMENT '密码',
  `addr` varchar(80) DEFAULT NULL COMMENT '地址',
  `tel` varchar(20) DEFAULT NULL COMMENT '电话',
  `regtime` char(30) DEFAULT NULL COMMENT '注册时间',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000003 DEFAULT CHARSET=utf8;

image.pngЗапрос все о 16s. Это довольно медленно. Обычно у нас есть фоновая система, например, это платформа электронной коммерции, это таблица пользователей. Система фонового управления обычно запрашивает эту информацию о пользователях и выполняет некоторые операции, такие как добавление пользователей непосредственно в фоновом режиме или удаление пользователей.

Итак, вот два требования: одно — количество запросов, другое — запрос постраничной разбивки.

Давайте проверим время, используемое подсчетом, и время, используемое поисковым запросом соответственно.

select * from user limit 1, 10   //几乎不用时
select * from user limit 1000000, 10  //0.35s
select * from user limit 5000000, 10  //1.7s
select * from user limit 9000000, 10  //2.8s
select count(1) from user  //1.7s

Как видно из времени, затраченного на приведенный выше запрос, если это запрос на подкачку, данные запроса будут занимать больше времени по мере выполнения запроса, а количество запросов также займет 1,7 с. Это явно не соответствует нашим требованиям. Итак, здесь нам нужно оптимизировать. Во-первых, давайте попробуем оптимизировать индекс здесь

Сначала посмотрите на план выполнения только с индексом первичного ключа:

image.png

alter table `user` add INDEX `sindex` (`uname`,`pwd`,`addr`,`tel`,`regtime`,`age`)

image.png

Глядя на приведенный выше план выполнения, хотя тип от all->index, а индекс sindex исчез, скорость запроса фактически не изменилась.

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

select * from user where uname='6.445329111484186' //3.5s(无联合索引)
select * from user where uname='6.445329111484186' //0.003s(有联合索引)

Так что в этом разница между совместным индексом и отсутствием индексации.

Здесь можно в основном доказать, что эффективность будет очень низкой при выполнении запроса полной таблицы с индексом или без него.

Поскольку результат индексации уже не удобен в использовании, он может найти только другие решения. Согласно тому, что я сказал в своем предыдущем интервью по MySQL, count может храниться в таблице отдельно.

CREATE TABLE `attribute` (
  `id` int(11) NOT NULL,
  `formname` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '表名',
  `formcount` int(11) NOT NULL COMMENT '表总数据',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

image.pngСкажу тут, что такого рода таблицы вообще проверяют не все, а только один запрос, поэтому при построении таблицы можно построить хэш

select formcount from attribute where formname='user' //几乎不用时

количество оптимизировано. Если выше указано условие выбора, вы можете построить индекс и выполнить запрос с помощью фильтрации индекса, чтобы вам не нужно было читать счетчик.

Тогда подсчитать не проблема, как оптимизировать оптимизацию запросов на подкачку? Здесь вы можете использовать подзапросы для оптимизации

select * from user where
id>=(select id from user limit 9000000,1) limit 10 //1.7s

На самом деле способ написания подзапроса, судя по id, на самом деле делает запрос через покрывающий индекс. Эффективность будет значительно увеличена. Однако мой тест здесь составляет 1,7 с. Раньше, когда компания оптимизировала этот аспект, время запроса было меньше, чем это. Вы также можете сгенерировать свои собственные данные и протестировать их самостоятельно.

Но если объем данных слишком велик, я все же рекомендую перейти на es или сделать какой-то выбор по умолчанию, количество можно указать отдельно

На данный момент завершена оптимизация десятков миллионов запросов на подкачку данных.

Категории