Evial
Приветствую.
Есть следующий sql запрос, который выполняется достаточно долго
SELECT
g.gal_id,
g.title,
g.description,
g.duration,
g.tags,
g.mc,
g.type_id,
DATE_FORMAT(g.create_dt, '%e-%m-%y') as create_dt,
g.categories,
DATE_FORMAT(g.publish_dt, '%e-%m-%y') as publish_dt,
g.status_id,
g.url as gal_url,
rt.thumb_id,
rt.rotation_id,
rt.views,
rt.clicks,
rt.ctr,
rt.category_id,
0 as is_rotating,
rt.status_id as rotation_status_id,
ps.domain as paysite_domain,
ps.name as paysite_name,
ps.ref_url as paysite_ref_url
FROM rotation rt
STRAIGHT_JOIN gal_tags gt ON gt.gal_id = rt.gal_id
JOIN gals g ON gt.gal_id = g.gal_id
JOIN paysites ps ON g.paysite_id = ps.paysite_id
WHERE gt.tag_id = '6110'
AND rt.category_id = '3'
AND g.disabled_sum = 0
ORDER BY rt.ctr
LIMIT 0, 140
17 rows in set (39.36 sec)
План выполнения запроса следующий
+----+-------------+-------+--------+-----------------------------------------------+----------+---------+-----------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------+----------+---------+-----------------------------+------+---------------------------------+
| 1 | SIMPLE | ps | ALL | PRIMARY | NULL | NULL | NULL | 9 | Using temporary; Using filesort |
| 1 | SIMPLE | g | ref | PRIMARY,FK_g_ps | FK_g_ps | 4 | testdb.ps.paysite_id | 4253 | Using where |
| 1 | SIMPLE | rt | ref | U_rt_g_c,I_for_ctr_sort,I_for_publish_dt_sort | U_rt_g_c | 8 | testdb.g.gal_id,const | 1 | |
| 1 | SIMPLE | gt | eq_ref | U_gt_1,FK_gt_g,FK_gt_tag | U_gt_1 | 8 | testdb.rt.gal_id,const | 1 | Using where; Using index |
+----+-------------+-------+--------+-----------------------------------------------+----------+---------+-----------------------------+------+---------------------------------+
Размер базы 2.7 ГБ
Информация по кол-ву строк в таблицах, которые используются в этом запросе
mysql> select count(rotation_id) as result_count FROM rotation;
+--------------+
| result_count |
+--------------+
| 1952399 |
+--------------+
1 row in set (1.67 sec)
mysql> select count(gal_tag_id) as result_count FROM gal_tags;
+--------------+
| result_count |
+--------------+
| 2714263 |
+--------------+
1 row in set (1.94 sec)
mysql> select count(paysite_id) as result_count FROM paysites;
+--------------+
| result_count |
+--------------+
| 9 |
+--------------+
1 row in set (0.00 sec)
mysql> select count(gal_id) as result_count FROM gals;
+--------------+
| result_count |
+--------------+
| 505496 |
+--------------+
1 row in set (0.34 sec)
Аналогичных баз на сервере 16 штук. По итогу запросы к этим базам нагружают 10 из 16 ядер процессора.
Предложение следующее.
В запросе STRAIGHT_JOIN gal_tags gt ON gt.gal_id = rt.gal_id заменить STRAIGHT_JOIN просто на JOIN.
Документация mysql говорит:
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order
По итогу запрос
SELECT sql_no_cache
g.gal_id,
g.title,
g.description,
g.duration,
g.tags,
g.mc,
g.type_id,
DATE_FORMAT(g.create_dt, '%e-%m-%y') as create_dt,
g.categories,
DATE_FORMAT(g.publish_dt, '%e-%m-%y') as publish_dt,
g.status_id,
g.url as gal_url,
rt.thumb_id,
rt.rotation_id,
rt.views,
rt.clicks,
rt.ctr,
rt.category_id,
0 as is_rotating,
rt.status_id as rotation_status_id,
ps.domain as paysite_domain,
ps.name as paysite_name,
ps.ref_url as paysite_ref_url
FROM rotation rt
JOIN gal_tags gt ON gt.gal_id = rt.gal_id
JOIN gals g ON gt.gal_id = g.gal_id
JOIN paysites ps ON g.paysite_id = ps.paysite_id
WHERE gt.tag_id = '6110'
AND rt.category_id = '3'
AND g.disabled_sum = 0
ORDER BY rt.ctr
LIMIT 0, 140
отрабатывает за 17 rows in set (0.01 sec)
План выполнения запроса при этом следующий
+----+-------------+-------+--------+-----------------------------------------------+-----------+---------+----------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------+-----------+---------+----------------------------+------+---------------------------------+
| 1 | SIMPLE | gt | ref | U_gt_1,FK_gt_g,FK_gt_tag | FK_gt_tag | 4 | const | 20 | Using temporary; Using filesort |
| 1 | SIMPLE | g | eq_ref | PRIMARY,FK_g_ps | PRIMARY | 4 | testdb.gt.gal_id | 1 | Using where |
| 1 | SIMPLE | ps | eq_ref | PRIMARY | PRIMARY | 4 | testdb.g.paysite_id | 1 | |
| 1 | SIMPLE | rt | ref | U_rt_g_c,I_for_ctr_sort,I_for_publish_dt_sort | U_rt_g_c | 8 | testdb.g.gal_id,const | 1 | Using where |
+----+-------------+-------+--------+-----------------------------------------------+-----------+---------+----------------------------+------+---------------------------------+
Результат получаем точно такой же, как и с STRAIGHT_JOIN, но несколько строк немного в другом порядке идут. В результате нагрузка на процессор снизиться колоссально
Хотелось бы получить маленький апдейт для inxy, который подправит запрос.