Форум пользователей MySQL

Бананище · 18.01.2012 05:58:06

Частенько, в логи медленных запросов MySQL мне вываливалась масса запросов подобных этому:

SELECT u.user_id

FROM phpbb_users u, phpbb_user_group ug 

WHERE u.user_type IN (0, 3)

AND ug.user_pending = 0 

AND u.user_id = ug.user_id 

AND ug.group_id = 2

ORDER BY ug.group_leader DESC, u.user_regdate ASC

LIMIT 20

-- ~1.91 seconds

В обеих таблицах - по полмиллиона записей. EXPLAIN не выявил проблем с индексами, а вот включение и просмотр профайлинга - показал примерно следующее:

Creating tmp table 0.000052
executing 0.000002
Copying to tmp table 1.779980

Дабы лучше понять, что за шляпа происходит - я воспроизвёл примерные действия сервера, при вышеозначенном запросе:

CREATE TABLE tmp_table ENGINE = MEMORY

SELECT u.user_id, ug.group_leader, u.user_regdate

FROM phpbb_users u, phpbb_user_group ug 

WHERE u.user_type IN (0, 3)

AND ug.user_pending = 0 

AND u.user_id = ug.user_id 

AND ug.group_id = 2;

-- ~1.7 seconds

SELECT user_id, 

FROM tmp_table

ORDER BY group_leader DESC, user_regdate ASC

LIMIT 20;

-- ~0.06 seconds

Возникла масса вопросов:
- Почему такая фака происходит?
- Можно ли предотвратить создание временных таблиц для запросов ORDER BY?
- Если нельзя, то как можно максимально ускорить подобные запросы? Тут интересуют способы максимально облегчить для сервера выполнение таких запросов, за счёт уменьшения каких-либо издержек на создание временных таблиц, копирование в них временных данных и пр...
- Какие ещё можно покрутить параметры, кроме тех что я уже крутил, согласно документации? А крутил я - max_length_for_sort_data = 4096 и sort_buffer_size = 128M (Такие значения сейчас стоят)

Отредактированно Бананище (18.01.2012 06:00:43)

LazY · 18.01.2012 15:52:46

EXPLAIN не выявил проблем с индексами

А по запросу не скажешь.
Хорошо оптимизированный ORDER BY - это когда все указанные в нем колонки в индексе, и этот индекс используется.
У Вас же в ORDER BY используются колонки из разных таблиц, вряд ли MySQL сумеет не полезть за ними в данные (отчего все и станет медленно).
Для верности покажите EXPLAIN и структуру таблиц.

Бананище · 19.01.2012 11:59:46

EXPLAIN

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ug ref group_id,user_id,user_pending group_id 3 const 135189 Using where; Using temporary; Using filesort
1 SIMPLE u eq_ref PRIMARY,user_type PRIMARY 3 so_phpbb.ug.user_id 1 Using where

-- `phpbb_users`

CREATE TABLE `phpbb_users` (

  `user_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

  `user_type` tinyint(1) NOT NULL DEFAULT '0',

  `group_id` mediumint(8) unsigned NOT NULL DEFAULT '3',

  `user_permissions` mediumtext COLLATE utf8_bin NOT NULL,

  `user_perm_from` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `user_ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_regdate` int(11) unsigned NOT NULL DEFAULT '0',

  `username` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

  `username_clean` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_password` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_passchg` int(11) unsigned NOT NULL DEFAULT '0',

  `user_pass_convert` tinyint(1) unsigned NOT NULL DEFAULT '0',

  `user_email` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_email_hash` bigint(20) NOT NULL DEFAULT '0',

  `user_birthday` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_lastvisit` int(11) unsigned NOT NULL DEFAULT '0',

  `user_lastmark` int(11) unsigned NOT NULL DEFAULT '0',

  `user_lastpost_time` int(11) unsigned NOT NULL DEFAULT '0',

  `user_lastpage` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_last_confirm_key` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_last_search` int(11) unsigned NOT NULL DEFAULT '0',

  `user_warnings` tinyint(4) NOT NULL DEFAULT '0',

  `user_last_warning` int(11) unsigned NOT NULL DEFAULT '0',

  `user_login_attempts` tinyint(4) NOT NULL DEFAULT '0',

  `user_inactive_reason` tinyint(2) NOT NULL DEFAULT '0',

  `user_inactive_time` int(11) unsigned NOT NULL DEFAULT '0',

  `user_posts` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `user_lang` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_timezone` decimal(5,2) NOT NULL DEFAULT '0.00',

  `user_dst` tinyint(1) unsigned NOT NULL DEFAULT '0',

  `user_dateformat` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT 'd M Y H:i',

  `user_style` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `user_rank` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `user_colour` varchar(6) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_new_privmsg` int(4) NOT NULL DEFAULT '0',

  `user_unread_privmsg` int(4) NOT NULL DEFAULT '0',

  `user_last_privmsg` int(11) unsigned NOT NULL DEFAULT '0',

  `user_message_rules` tinyint(1) unsigned NOT NULL DEFAULT '0',

  `user_full_folder` int(11) NOT NULL DEFAULT '-3',

  `user_emailtime` int(11) unsigned NOT NULL DEFAULT '0',

  `user_topic_show_days` smallint(4) unsigned NOT NULL DEFAULT '0',

  `user_topic_sortby_type` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 't',

  `user_topic_sortby_dir` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'd',

  `user_post_show_days` smallint(4) unsigned NOT NULL DEFAULT '0',

  `user_post_sortby_type` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 't',

  `user_post_sortby_dir` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'a',

  `user_notify` tinyint(1) unsigned NOT NULL DEFAULT '0',

  `user_notify_pm` tinyint(1) unsigned NOT NULL DEFAULT '1',

  `user_notify_type` tinyint(4) NOT NULL DEFAULT '0',

  `user_allow_pm` tinyint(1) unsigned NOT NULL DEFAULT '1',

  `user_allow_viewonline` tinyint(1) unsigned NOT NULL DEFAULT '1',

  `user_allow_viewemail` tinyint(1) unsigned NOT NULL DEFAULT '1',

  `user_allow_massemail` tinyint(1) unsigned NOT NULL DEFAULT '1',

  `user_options` int(11) unsigned NOT NULL DEFAULT '2943',

  `user_avatar` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_avatar_type` tinyint(2) NOT NULL DEFAULT '0',

  `user_avatar_width` smallint(4) unsigned NOT NULL DEFAULT '0',

  `user_avatar_height` smallint(4) unsigned NOT NULL DEFAULT '0',

  `user_sig` mediumtext COLLATE utf8_bin NOT NULL,

  `user_sig_bbcode_uid` varchar(8) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_sig_bbcode_bitfield` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_from` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_icq` varchar(15) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_aim` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_yim` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_msnm` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_jabber` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_website` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_occ` text COLLATE utf8_bin NOT NULL,

  `user_interests` text COLLATE utf8_bin NOT NULL,

  `user_actkey` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_newpasswd` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_form_salt` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',

  `user_reputation` int(11) NOT NULL DEFAULT '0',

  `user_hide_reputation` tinyint(1) unsigned NOT NULL DEFAULT '0',

  `user_points` int(11) NOT NULL DEFAULT '0',

  `user_allow_thanks_pm` tinyint(1) unsigned NOT NULL DEFAULT '0',

  `user_bw_account_exist` tinyint(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`user_id`),

  UNIQUE KEY `username_clean` (`username_clean`),

  KEY `user_birthday` (`user_birthday`),

  KEY `user_email_hash` (`user_email_hash`),

  KEY `user_type` (`user_type`),

  KEY `user_ip` (`user_ip`),

  KEY `user_email` (`user_email`),

  KEY `username` (`username`),

  KEY `user_regdate` (`user_regdate`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=269006 ;

-- `phpbb_user_group`

CREATE TABLE `phpbb_user_group` (

  `group_id` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `group_leader` tinyint(1) unsigned NOT NULL DEFAULT '0',

  `user_pending` tinyint(1) unsigned NOT NULL DEFAULT '1',

  `auto_group` tinyint(1) DEFAULT '0',

  KEY `group_id` (`group_id`),

  KEY `user_id` (`user_id`),

  KEY `group_leader` (`group_leader`),

  KEY `user_pending` (`user_pending`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

paulus · 22.01.2012 19:42:11

Попробуйте добавить индексы
phpbb_user_group (group_id, user_pending, group_leader)
phpbb_users (user_id, user_regdate)

Второй индекс, возможно, прийдется FORCEить, потому что есть еще
одно условие в WHERE (которое, впрочем, сформирует RANGE).

Бананище · 22.01.2012 23:40:22

Попробовал, не помогло, однако идея форсить индексы - оказалась хороша.
Решил сидеть до конца. Перекурив мануалы - впёр индексы по полям
user_id, user_type, user_regdate таблицы phpbb_users
и group_id, user_id, group_leader, user_pending таблицы phpbb_user_group

Изменил запрос:

SELECT u.user_id

FROM phpbb_users u FORCE INDEX (user_id_type_regdate), 

phpbb_user_group ug FORCE INDEX (group_id_uid_gl_up)

WHERE u.user_type

IN ( 0, 3 )

AND ug.user_pending =0

AND u.user_id = ug.user_id

AND ug.group_id =2

ORDER BY ug.group_leader DESC , u.user_regdate ASC

И скорость выполнения запроса увеличилась в два раза и стала 0.9 секунд (Тестил с SQL_NO_CACHE).
Что-ж, на данный момент считаю это прорывом. Буду рыть дальше

.

Форум пользователей MySQL

#1 18.01.2012 05:58:06

Максимальное ускорение ORDER BY

#2 18.01.2012 15:52:46

Re: Максимальное ускорение ORDER BY

#3 19.01.2012 11:59:46

Re: Максимальное ускорение ORDER BY

#4 22.01.2012 19:42:11

Re: Максимальное ускорение ORDER BY

#5 22.01.2012 23:40:22

Re: Максимальное ускорение ORDER BY

Board footer