Как ускорить выборку из mysql?

Question

[Дмитрий Скогорев]

Доброго времени суток всем
есть mysql таблица со структурой id,value на ~3 млн. строк. id - primary, unique

запрос вида "select id,value from table where id=1234567 limit 1" может выполняться от 3 секунд до 30
как ускорить выполнение запроса хотя бы до десятых долей секунды?
можете вкратце рассказать про индесы? вроде применил к полю id, но результата не вижу
поможет ли разбиение таблицы на несколько, скажем по 10 тысяч?
заранее благодарю за ответы

Answer 1

[Сергей Протько]

Есть мнение что индекс не помещается в памяти. innodb_buffer_pool_size чему равен?

Comments

[Дмитрий Скогорев]

хех, дефолтные шышнацать мехабайт. Посоветуйте, пожалуйста, сколько в реальности стоит дать?

[Сергей Протько]

Дмитрий Скогорев: я бы начал с 500 мегабайт и смотрел. А вообще есть довольно простой вариант, либо выставляем 75% от RAM (либо, если на сервере не только mysql а еще что-то, 75% от того что остается) либо подобрать по даным. Для этого можно воспользоваться таким запросом:

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size 
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables 
WHERE engine='InnoDB') A, 
(SELECT 3 PowerOf1024) B;

Answer 2

[Константин Самилко]

Выполните: explain select id,value from table where id=1234567 limit 1;
И увидите, что пытается сделать оптимизатор. Используется индекс или нет.
Еще, что у вас за движок таблицы. Сколько занимает БД и таблица?

Comments

[Дмитрий Скогорев]

вот что получаю, но не пойму есть ли индекс
mysql> explain select userid,value from keys where userid=4650017 limit 1;
+----+-------------+--------+------+----------------------------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------------------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | keys | ALL | PRIMARY,userid,userid_2,userid_3 | NULL | NULL | NULL | 6344106 | Using where |
+----+-------------+--------+------+----------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Innodb, в таблице около 7 млн записей и 750Мб, остальные таблицы в базе ничтожно малы
стоит ли попробовать сделать разделение с range или использовать mongodb?

[Константин Самилко]

Дмитрий Скогорев: possible_keys показывает, какие индексы есть. колонка key, говорит, что индекс не используется.
Покажите вывод команды:
SHOW CREATE TABLE keys\G

А так же запросов:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Хотя, выше в комментарии, уже вижу что стоит дефолтные 16мб.
Вопрос следующий. сколько ОЗУ на сервере. На сервере только СУБД крутиться?

[Дмитрий Скогорев]

kuroneco: | innodb_buffer_pool_size | 1073741824 | выставил на 1гб.

Create Table: CREATE TABLE `keys` (
`userid` varchar(12) NOT NULL,
`value` varchar(12) NOT NULL,
PRIMARY KEY (`userid`),
UNIQUE KEY `userid` (`userid`),
KEY `userid_2` (`userid`),
KEY `userid_3` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[Константин Самилко]

Дмитрий Скогорев: после того как поменяли innodb_buffer_pool_size, сервер рестартовали? Поменяли в my.cnf?
Не понятно, зачем вы создали userid_2, userid_3 на одно и то же поле. Уберите.
Почему у вас userid varchar а не int? или планируются еще буквы? Может ли быть userid автоинкриментом?
Если рестартовали и тот explain дает, что дает, то выполните вот такую команду и вывод сюда:

explain select userid,value from keys where userid='4650017' limit 1;

А также такой запрос

explain select userid,value from keys FORCE INDEX (PRIMARY) where userid='4650017' limit 1;

[Дмитрий Скогорев]

mysql> explain select userid,value from keys where userid='123499' limit 1;
+----+-------------+--------+-------+----------------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | keys | const | PRIMARY,userid,userid_2,userid_3 | PRIMARY | 38 | const | 1 | NULL |
+----+-------------+--------+-------+----------------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

и

mysql> explain select userid,value from keys FORCE INDEX (PRIMARY) where userid='123499' limit 1;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | keys | const | PRIMARY | PRIMARY | 38 | const | 1 | NULL |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

почему добавились userid_2_3 - вопрос к pma, видимо

[Дмитрий Скогорев]

kuroneco:

[Константин Самилко]

Дмитрий Скогорев: понимаете, почему сейчас индекс сработал в 1ом случае?

[Константин Самилко]

Дмитрий Скогорев: Ну и естественно, если выполнить: select userid,value from keys where userid='123499'; Ответ быстро выдастся. Лимит убрал, так как вы говорили, что у вас уникальные значения.

[Дмитрий Скогорев]

kuroneco: честно скажу - я не всё понял из того что делал. однако сейчас создал новую таблицу с userid int и value bigint, добавил только индекс на userid и теперь всё отрабатывает мгновенно.
выражаю благодарность за потраченное время

[Константин Самилко]

Дмитрий Скогорев: вы не правильно создали таблицу и делали не правильный запрос. Если ваш userid будет только числовым уникальным значением, нужно сделать его типа int not null auto_increment, чтобы он сам приращивал значение при insert. То, что мы делали с вами - посмотрели, как создана таблица и посмотрели как работает ваш запрос с помощью команды explain. primary key так же можно создавать на этапе создания таблицы или позже. Если его у вас нет, правильно создать его, а не лепить индекс. А проблема в вашем запросе была, из-за того что вы указывали userid = без кавычек. Для всех типов столбцов, кроме int,float и кажется чего-то еще, кавычки обязательны. Иначе, оптимизатор отправит ваш запрос в долгое путешествие на просматривание всех строк.
Вообще, если есть желание получить фундаментальные знания по составления запросов, таблиц и т.д, рекомендую книгу - Л.Бейли "Изучаем SQL". Очень легко читается. Наполнена юмором, сарказмом и игровыми моментами.

Answer 3

[Александр]

не используйте подселекты, (де)оптимизируются как depend subquery и тормозят