Можно ли написать серьёзное web-приложение с использованием MySQL, но без знания принципов работы MySQL?
Можно. Доказано разработчиками WordPress.
Хотя я люблю WordPress, но то, что я увидел сегодня в коде, меня сильно потрясло.
Речь пойдёт о виджетах, а именно, о календаре и архиве. Я вкратце опишу реализацию каждого из них, а затем расскажу, почему так делать нельзя.
Начнём с виджета архивов.
В самом простом случае (если не установлено никаких сторонних плагинов, которые могут повлиять на запросы виджета), получение данных для формирования архива производится таким вот запросом (в зависимости от опций виджета запрос может отличаться):
FROM wp_posts
WHERE post_type = 'post' AND post_status = 'publish'
GROUP BY YEAR(post_date), MONTH(post_date)
ORDER BY post_date DESC
В случае, если в базе данных хранится несколько тысяч статей и все они опубликованы, фильтрация по полям post_type
и post_status
мало что даст (если только записи не содержат много вложений), так как всё равно будут выбраны почти все записи. Затем выбранные записи будут группироваться по выражению, и конечный результат будет отсортирован. Проблема здесь состоит в том, что MySQL может использовать только один индекс, и он его использует — для нахождения опубликованных статей. А вот для группировки и сортировки индексы MySQL использовать не сможет: в случае с группировкой из-за того, что используется группировка по выражению, а в случае с сортировкой — потому что группировка и сортировка осуществляются по разным полям.
Запустив EXPLAIN, мы увидим, как всё плохо:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+------------------+------------------+---------+-------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | wp_posts | ref | type_status_date | type_status_date | 124 | const,const | 3594 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+----------+------+------------------+------------------+---------+-------------+------+-----------------------------------------------------------+
Во-первых, длина ключа не радует: 124 байта — это перебор. Сравнение длинных строк неэффективно, и чем больше похожих или одинаковых строк, тем хуже производительность.
Во-вторых, использование временной таблицы. Создание временной таблицы (пусть даже в памяти) — это лишнее время. Если таблица становится слишком большой, она сбрасывается на диск и преобразовывается в другой формат. Еще больше потерянного времени.
В-третьих, использование файловой сортировки. MySQL не смог использовать индекс для сортировки; как следствие, ему придётся сделать еще один проход с просмотром всех записей. Опять теряется драгоценное время.
Можно, конечно, поспорить, что современное железо очень быстрое, а память дешёвая. Да, всё так. Но есть одно но: такое решение очень плохо масштабируется. Как следствие, изобретаются различные костыли в виде кэширования. Но не проще ли было нормально спроектировать базу данных?
Как ни крути, запрос переписать невозможно: с ростом количества опубликованных статей производительность будет падать. Что-то мне подсказывает, что нелинейно. А с увеличением количества посетителей производительность будет падать еще сильнее. В результате может упасть MySQL. Или его убьёт знаменитый OOM Killer.
Что нужно было сделать: ввести избыточность в базу данных. С точки зрения третьей нормальной формы это звучит кощунственно. Но избыточность позволяет повысить производительность.
Я внёс изменения в таблицу wp_posts
:
UPDATE `wp_posts` SET `ym` = EXTRACT(YEAR_MONTH FROM `post_date`);
ALTER TABLE `wp_posts` ADD KEY(`post_type`, `post_status`, `ym`);
Затем переписал запрос:
FROM wp_posts
WHERE post_type = 'post' AND post_status = 'publish'
GROUP BY `ym`
ORDER BY `ym` DESC
DISTINCT
в запросе явно лишний, потому что группировка по году и месяцу гарантирует, что каждая пара (год, месяц)
в результирующем наборе будет уникальной. Сортировка по полной дате также бессмысленна, ибо точность до дня в запросе не нужна.
В результате получаем:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+----------------------------+-----------+---------+-------------+------+-------------+
| 1 | SIMPLE | wp_posts | ref | type_status_date,post_type | post_type | 124 | const,const | 3594 | Using where |
+----+-------------+----------+------+----------------------------+-----------+---------+-------------+------+-------------+
Никаких временных таблиц, никакой файловой сортировки. Ключ по-прежнему длинный, но это уже издержки производства. Лично я бы поменял VARCHAR
на ENUM
.
Теперь переходим к виджету календарь.
В нём четыре запроса, но каких!
- Поиск самой недавней записи прошлых месяцев:
[-]View Code MySQLSELECT DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
FROM wp_posts
WHERE post_date < '2008-11-01'
AND post_type = 'post' AND post_status = 'publish'
ORDER BY post_date DESC
LIMIT 1Ну зачем нужен
DISTINCT
?!! Выбирается одна запись, в результирующем наборе она уникальна в любом случае.[-]View Code (Unknown Language)+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE | wp_posts | range | type_status_date,post_type | type_status_date | 132 | NULL | 3238 | Using where; Using index; Using temporary |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+Уберём
DISTINCT
и получим более красивый результат:[-]View Code (Unknown Language)+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | wp_posts | range | type_status_date,post_type | type_status_date | 132 | NULL | 3238 | Using where; Using index |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+--------------------------+Как видим, необходимость во временной таблице исчезла.
- Поиск записи в последующих месяцах:
[-]View Code MySQLSELECT DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
FROM wp_posts
WHERE post_date > '2008-11-01'
AND MONTH( post_date ) != MONTH( '2008-11-01' )
AND post_type = 'post' AND post_status = 'publish'
ORDER BY post_date ASC
LIMIT 1У нас это называют "начемоданечерезкитайскими методами". Про
DISTINCT
я уже говорил, в нём смысла нет. А с датой перемудрили: ищем статьи, написанные после первого числа этого месяца, но не в этом месяце. Не проще ли написать, что ищем статьи, написанные с первого числа следующего месяца?[-]View Code (Unknown Language)+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE | wp_posts | range | type_status_date,post_type | type_status_date | 132 | NULL | 356 | Using where; Using index; Using temporary |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+Исправим запрос:
[-]View Code MySQLSELECT MONTH(post_date) AS month, YEAR(post_date) AS year
FROM wp_posts
WHERE post_date > '2008-11-31 23:59:59'
AND post_type = 'post' AND post_status = 'publish'
ORDER BY post_date ASC
LIMIT 1[-]View Code (Unknown Language)+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | wp_posts | range | type_status_date,post_type | type_status_date | 132 | NULL | 1 | Using where; Using index |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+--------------------------+ - Поиск дней в текущем месяце, в которые была написана хотя бы одна статья:
[-]View Code MySQLSELECT DISTINCT DAYOFMONTH(post_date)
FROM wp_posts WHERE MONTH(post_date) = '11'
AND YEAR(post_date) = '2008'
AND post_type = 'post' AND post_status = 'publish'
AND post_date < '2008-11-24 18:30:04'Очевидно, что MySQL сможет использовать только стратегию range. В случае, если рассматриваемый месяц является последним в базе данных, MySQL придётся просмотреть все записи, которые вернёт
WHERE
. Ибо не умеет MySQL использовать индексы с выражениями.[-]View Code (Unknown Language)+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE | wp_posts | range | type_status_date,post_type | type_status_date | 132 | NULL | 3594 | Using where; Using index; Using temporary |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+MySQL придётся просмотреть 3594 записи. А если мы перепишем запрос:
[-]View Code MySQLSELECT DISTINCT DAYOFMONTH(post_date)
FROM wp_posts
WHERE post_date >= '2008-11-01 00:00:00'
AND post_type = 'post' AND post_status = 'publish'
AND post_date < '2008-11-24 18:30:04'[-]View Code (Unknown Language)+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE | wp_posts | range | type_status_date,post_type | type_status_date | 132 | NULL | 267 | Using where; Using index; Using temporary |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------------------------------------+Получим более, чем 10-кратную экономию. Ain't it sweet?
- Получение дней и соответствующих им статей:
[-]View Code MySQLSELECT post_title, DAYOFMONTH(post_date) as dom
FROM wp_posts
WHERE YEAR(post_date) = '2008'
AND MONTH(post_date) = '11'
AND post_date < '2008-11-24 18:30:04'
AND post_type = 'post'
AND post_status = 'publish'Та же самая ошибка, что и в прошлый раз.
[-]View Code (Unknown Language)+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------+
| 1 | SIMPLE | wp_posts | range | type_status_date,post_type | type_status_date | 132 | NULL | 3594 | Using where |
+----+-------------+----------+-------+----------------------------+------------------+---------+------+------+-------------+
Теперь, собственно, патчи.
- Патч для WordPress 2.6.x:
[-]Download general-template.php.diff--- general-template.php.original 2008-06-11 20:25:55.000000000 +0300
+++ general-template.php 2008-11-24 20:37:42.000000000 +0200
@@ -414,7 +414,7 @@
$join = apply_filters('getarchives_join', "", $r);
if ( 'monthly' == $type ) {
- $query = "SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC $limit";
+ $query = "SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC $limit";
$key = md5($query);
$cache = wp_cache_get( 'wp_get_archives' , 'general');
if ( !isset( $cache[ $key ] ) ) {
@@ -435,7 +435,7 @@
}
}
} elseif ('yearly' == $type) {
- $query = "SELECT DISTINCT YEAR(post_date) AS `year`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date) ORDER BY post_date DESC $limit";
+ $query = "SELECT YEAR(post_date) AS `year`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date) ORDER BY post_date DESC $limit";
$key = md5($query);
$cache = wp_cache_get( 'wp_get_archives' , 'general');
if ( !isset( $cache[ $key ] ) ) {
@@ -456,7 +456,7 @@
}
}
} elseif ( 'daily' == $type ) {
- $query = "SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, DAYOFMONTH(post_date) AS `dayofmonth`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MONTH(post_date), DAYOFMONTH(post_date) ORDER BY post_date DESC $limit";
+ $query = "SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, DAYOFMONTH(post_date) AS `dayofmonth`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MONTH(post_date), DAYOFMONTH(post_date) ORDER BY post_date DESC $limit";
$key = md5($query);
$cache = wp_cache_get( 'wp_get_archives' , 'general');
if ( !isset( $cache[ $key ] ) ) {
@@ -557,7 +557,7 @@
ob_start();
// Quick check. If we have no posts at all, abort!
if ( !$posts ) {
- $gotsome = $wpdb->get_var("SELECT ID from $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 1");
+ $gotsome = $wpdb->get_var("SELECT ID from $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish' LIMIT 1");
if ( !$gotsome )
return;
}
@@ -591,16 +591,16 @@
$unixmonth = mktime(0, 0 , 0, $thismonth, 1, $thisyear);
// Get the next and previous month and year with at least one post
- $previous = $wpdb->get_row("SELECT DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
+ $previous = $wpdb->get_row("SELECT MONTH(post_date) AS month, YEAR(post_date) AS year
FROM $wpdb->posts
WHERE post_date < '$thisyear-$thismonth-01'
AND post_type = 'post' AND post_status = 'publish'
ORDER BY post_date DESC
LIMIT 1");
- $next = $wpdb->get_row("SELECT DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
+ $last_day = date('t');
+ $next = $wpdb->get_row("SELECT MONTH(post_date) AS month, YEAR(post_date) AS year
FROM $wpdb->posts
- WHERE post_date > '$thisyear-$thismonth-01'
- AND MONTH( post_date ) != MONTH( '$thisyear-$thismonth-01' )
+ WHERE post_date > '$thisyear-$thismonth-{$last_day} 23:59:59'
AND post_type = 'post' AND post_status = 'publish'
ORDER BY post_date ASC
LIMIT 1");
@@ -655,8 +655,7 @@
// Get days with posts
$dayswithposts = $wpdb->get_results("SELECT DISTINCT DAYOFMONTH(post_date)
- FROM $wpdb->posts WHERE MONTH(post_date) = '$thismonth'
- AND YEAR(post_date) = '$thisyear'
+ FROM $wpdb->posts WHERE post_date >= '{$thisyear}-{$thismonth}-01 00:00:00'
AND post_type = 'post' AND post_status = 'publish'
AND post_date < '" . current_time('mysql') . '\'', ARRAY_N);
if ( $dayswithposts ) {
@@ -675,8 +674,7 @@
$ak_titles_for_day = array();
$ak_post_titles = $wpdb->get_results("SELECT post_title, DAYOFMONTH(post_date) as dom "
."FROM $wpdb->posts "
- ."WHERE YEAR(post_date) = '$thisyear' "
- ."AND MONTH(post_date) = '$thismonth' "
+ ."WHERE post_date >= '{$thisyear}-{$thismonth}-01 00:00:00' "
."AND post_date < '".current_time('mysql')."' "
."AND post_type = 'post' AND post_status = 'publish'"
); - Патч для WordPress 2.7b3:
[-]Download general-template.php.diff--- general-template.php.original 2008-11-13 09:45:11.000000000 +0200
+++ general-template.php 2008-11-24 20:45:36.000000000 +0200
@@ -750,7 +750,7 @@
$output = '';
if ( 'monthly' == $type ) {
- $query = "SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_da
+ $query = "SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC $
$key = md5($query);
$cache = wp_cache_get( 'wp_get_archives' , 'general');
if ( !isset( $cache[ $key ] ) ) {
@@ -771,7 +771,7 @@
}
}
} elseif ('yearly' == $type) {
- $query = "SELECT DISTINCT YEAR(post_date) AS `year`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date) ORDER BY post_date DESC $limit";
+ $query = "SELECT YEAR(post_date) AS `year`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date) ORDER BY post_date DESC $limit";
$key = md5($query);
$cache = wp_cache_get( 'wp_get_archives' , 'general');
if ( !isset( $cache[ $key ] ) ) {
@@ -792,7 +792,7 @@
}
}
} elseif ( 'daily' == $type ) {
- $query = "SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, DAYOFMONTH(post_date) AS `dayofmonth`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_d
+ $query = "SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, DAYOFMONTH(post_date) AS `dayofmonth`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MON
$key = md5($query);
$cache = wp_cache_get( 'wp_get_archives' , 'general');
if ( !isset( $cache[ $key ] ) ) {
@@ -913,7 +913,7 @@
ob_start();
// Quick check. If we have no posts at all, abort!
if ( !$posts ) {
- $gotsome = $wpdb->get_var("SELECT ID from $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 1");
+ $gotsome = $wpdb->get_var("SELECT ID from $wpdb->posts WHERE post_type = 'post' AND post_status = 'publish' LIMIT 1");
if ( !$gotsome )
return;
}
@@ -947,16 +947,16 @@
$unixmonth = mktime(0, 0 , 0, $thismonth, 1, $thisyear);
// Get the next and previous month and year with at least one post
- $previous = $wpdb->get_row("SELECT DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
+ $previous = $wpdb->get_row("SELECT MONTH(post_date) AS month, YEAR(post_date) AS year
FROM $wpdb->posts
WHERE post_date < '$thisyear-$thismonth-01'
AND post_type = 'post' AND post_status = 'publish'
ORDER BY post_date DESC
LIMIT 1");
- $next = $wpdb->get_row("SELECT DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
+ $last_day = date('t');
+ $next = $wpdb->get_row("SELECT MONTH(post_date) AS month, YEAR(post_date) AS year
FROM $wpdb->posts
- WHERE post_date > '$thisyear-$thismonth-01'
- AND MONTH( post_date ) != MONTH( '$thisyear-$thismonth-01' )
+ WHERE post_date > '$thisyear-$thismonth-{$last_day} 23:59:59'
AND post_type = 'post' AND post_status = 'publish'
ORDER BY post_date ASC
LIMIT 1");
@@ -1011,8 +1011,7 @@
// Get days with posts
$dayswithposts = $wpdb->get_results("SELECT DISTINCT DAYOFMONTH(post_date)
- FROM $wpdb->posts WHERE MONTH(post_date) = '$thismonth'
- AND YEAR(post_date) = '$thisyear'
+ FROM $wpdb->posts WHERE post_date >= '{$thisyear}-{$thismonth}-01 00:00:00'
AND post_type = 'post' AND post_status = 'publish'
AND post_date < '" . current_time('mysql') . '\'', ARRAY_N);
if ( $dayswithposts ) {
@@ -1031,8 +1030,7 @@
$ak_titles_for_day = array();
$ak_post_titles = $wpdb->get_results("SELECT post_title, DAYOFMONTH(post_date) as dom "
."FROM $wpdb->posts "
- ."WHERE YEAR(post_date) = '$thisyear' "
- ."AND MONTH(post_date) = '$thismonth' "
+ ."WHERE post_date >= '{$thisyear}-{$thismonth}-01 00:00:00' "
."AND post_date < '".current_time('mysql')."' "
."AND post_type = 'post' AND post_status = 'publish'"
);
Патч нужно применять к файлу wp-includes/general-template.php
.
[...] комментариями к статье “Трудности веб-разработки” и недавним копанием во внутренностях [...]
Ответить на данный комментарий