Форум программистов, компьютерный форум, киберфорум
MySQL
Форум программистов и сисадминов Киберфорум > Форум программистов > Базы данных > MySQL
Войти
Регистрация
Восстановить пароль
Карта форума Темы раздела Блоги Сообщество Поиск Заказать работу  
 
Рейтинг 4.50/6: Рейтинг темы: голосов - 6, средняя оценка - 4.50
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
1

Оптимизация запроса

07.04.2014, 14:06. Показов 1113. Ответов 18
Метки нет (Все метки)
Author24 — интернет-сервис помощи студентам
Здравствуйте. Ломаю голову об оптимизации запроса.
Есть две таблицы

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

CREATE TABLE `test` (
    `id` SMALLINT(5) UNSIGNED NOT NULL,
    `dttm` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `t` CHAR(140) NOT NULL COLLATE 'cp1251_bin',
    PRIMARY KEY (`id`)
)
COLLATE='cp1251_bin'
ENGINE=MyISAM
ROW_FORMAT=FIXED;
 
 
CREATE TABLE `test_connection` (
    `id1` SMALLINT(5) UNSIGNED NOT NULL,
    `id2` SMALLINT(5) UNSIGNED NOT NULL
)
COLLATE='cp1251_bin'
ENGINE=MyISAM
ROW_FORMAT=FIXED;

Движок и фиксированный формат строк выбраны для скорейшей обработки. Кодировка для минимизации объёма выдаваемых данных.
Нужно максимально быстро выдать результат вот такого запроса
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
    `test`.`t`
FROM
    `test`,
    `test_connection`
WHERE
    `test`.`id` != `test_connection`.`id2`
        AND
    `test_connection`.`id1` = 1
ORDER BY
    `test`.`dttm` DESC
LIMIT
    1
Как построить индексы?
Будет ли быстрее, если я вместо id1 и id2 введу строку id1.id2 и буду искать значение не равное ей?
0
Лучшие ответы (1)
Сообщение: #6011890
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
 07.04.2014, 14:06
Ответы с готовыми решениями:

Оптимизация запроса
Добрый день, есть вот такой запрос: select id, content, vote from table1 where DATE_FORMAT(date,...

Оптимизация запроса
Добрый день. Подскажите как по человечески построить запрос: нужно получать данные по сотрудникам...

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

Оптимизация запроса
Здравствуйте! Столкнулся с такой проблемой, мне нужно выполнить запрос вида: SELECT `id` FROM...

18
0 / 0 / 1
Регистрация: 03.06.2013
Сообщений: 39
07.04.2014, 14:53 2
Как по мне то в принципе и так должно искать достаточно быстро.
Но я бы использовал JOIN вместо перебора:

MySQL
1
2
3
4
5
6
7

SELECT test.t
FROM test 
JOIN test_connection
WHERE test.id != test_connection.id2
      AND test_connection.id1 = 1
ORDER BY test.dttm DESC
LIMIT 1
Для целостности данных можно из test_connection.id2 сделать внешних ключ на test.id. Но это только для целостности данных, на скорость обработки данных это не повлияет.
0
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
07.04.2014, 15:01  [ТС] 3
Таблице test 65 535 строк, в таблицу test_connection 1 000 000. Время выполнения запроса 14 минут 29 секунд. Долго очень. Поиск без индексов.

Для использования внешних ключей придётся использовать InnoDB. Я не пробовал, но боюсь что будет ещё дольше. Скоро протестирую.

А разве JOIN не медленнее, перебора?
0
0 / 0 / 1
Регистрация: 03.06.2013
Сообщений: 39
07.04.2014, 16:34 4
Движок MYISAM быстро работает только на инсертах, на селекте он уступает InnoDB.
В свою очередь InnoDB уступает на селекте MYISAM.
попробуйте поменять движок.
0
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
07.04.2014, 16:45  [ТС] 5
Myisam с join 13:42
Innodb c join 12:21
Innodb c перебором 13:55

2 минуты отъиграли.

Добавлено через 34 секунды
Странно, что join быстрее. Почему?
0
0 / 0 / 1
Регистрация: 03.06.2013
Сообщений: 39
07.04.2014, 16:51 6
Потому что JOIN проверяет только те строки которые подходят по условию.

Добавлено через 2 минуты
Еще можно проиндексировать таблицу test_connection по id2. Возможно поможет
0
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
07.04.2014, 16:58  [ТС] 7
Не поможет.

EXPLAIN показывает, что не используются индексы. Более того: Using temporary; Using filesort; Using where; Using join buffer (flat, BNL join). И проход по всем значениям всех таблиц.

Как ускорить?
0
0 / 0 / 1
Регистрация: 03.06.2013
Сообщений: 39
07.04.2014, 17:19 8
Если вам нужна первая запись с конца, то никак
0
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
07.04.2014, 17:24  [ТС] 9
А если я могу этим поступиться, то как? Пусть вообще без сортировки будет. Любая запись, соответствующая условию WHERE.

Кстати сработал индекс по id1 даже с ORDER BY. Получилось 08:44.
0
1313 / 945 / 144
Регистрация: 17.01.2013
Сообщений: 2,348
07.04.2014, 17:32 10
Лучший ответ Сообщение было отмечено paveltkachev как решение

Решение

попробуйте такие:
test_connection(id1,id2)
test(dttm,id,t)
1
0 / 0 / 1
Регистрация: 03.06.2013
Сообщений: 39
07.04.2014, 17:36 11
Без DESC запрос должен выдать первый результат выборки и на этом остановиться.
0
1313 / 945 / 144
Регистрация: 17.01.2013
Сообщений: 2,348
07.04.2014, 17:38 12
Можно в test(dttm,id,t) не включать t и id, чтобы уменьшить размер индекса, но тогда много времени уйдет на RID-чтение (поиск по кластерному ключу и получение данных из него)
0
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
07.04.2014, 17:53  [ТС] 13
Спасибо! Индексы сработали. Результат выдаётся мгновенно!

Добавлено через 2 минуты
Если текстовых полей несколько, то я правильно понимаю, что оптимально будет в 2 запроса: первым я найду id, а вторым по id получу значения текстовых полей.

Добавлено через 7 минут
При большом количестве таких запросов какой движок лучше выбрать?
0
0 / 0 / 1
Регистрация: 03.06.2013
Сообщений: 39
07.04.2014, 17:55 14
Innodb
0
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
07.04.2014, 18:08  [ТС] 15
А нагрузка при этом будет выше?

Добавлено через 1 минуту
Дискового пространства занимать будет в 2 раза больше.
0
0 / 0 / 1
Регистрация: 03.06.2013
Сообщений: 39
07.04.2014, 18:17 16
Цитата Сообщение от paveltkachev Посмотреть сообщение
Дискового пространства занимать будет в 2 раза больше.
Это будет происходить из-за индексации
0
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
07.04.2014, 18:38  [ТС] 17
При тех же индексах MuISAM требует меньше в 1,5 раза места на жёстком диске.
0
0 / 0 / 1
Регистрация: 03.06.2013
Сообщений: 39
07.04.2014, 18:41 18
да, но MyISAM на SELECT-е медленнее работает и это факт

Добавлено через 27 секунд
MyISAM надо юзать когда нужен моментальный инсерт в базу

Добавлено через 24 секунды
во всех остальных случаях Innodb

Добавлено через 10 секунд
для временных таблиц мемори
0
3 / 3 / 0
Регистрация: 07.04.2014
Сообщений: 82
07.04.2014, 18:47  [ТС] 19
А дорого стоит поддержка внешних ключей и транзакции?
0
07.04.2014, 18:47
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
 07.04.2014, 18:47
Помогаю со студенческими работами здесь

Оптимизация запроса
Здравствуйте!! Есть таблица с полем rfid. в Нем через запятую хранятся записи (не более...

Оптимизация запроса
Идет поиск по полю date_added(TIMESTAMP) ... WHERE DATE(date_added) = '2017-09-02' Как и...

оптимизация запроса
Помогите оптимизировать такой запрос.. Выполняется около секунды..использую сортировку по 2...

Оптимизация запроса
Здравствуйте, проблема в следующем, время генерации страницы без запроса ниже: 0,00702, с ним около...

Оптимизация запроса
SELECT t.id AS t0_c0, t.catid AS t0_c1, t.title AS t0_c2, t.object AS t0_c3, t.description AS...

Нужна оптимизация запроса
Есть две таблицы, в первой записи по полю Id уникальны, во второй нет Задача выбрать ограниченное...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
19
Ответ Создать тему
КиберФорум - форум программистов, компьютерный форум, программирование
Поддержать - Реклама - Обратная связь
Powered by vBulletin
Copyright ©2000 - 2024, CyberForum.ru