mysql Ver 14.14 Distrib 5.1.58,for redhat-linux-gnu (x86_64) using readline 5.1
我正在接管一个旧项目。我被要求加快速度。我通过启用慢查询日志。我正在检查慢查询日志中出现的查询。此查询需要 10 秒才能运行,并返回 11,000 行。
select
substring_index(ob1.literal,'/',1) AS sku4,
substring_index(
substring_index(ob1.literal,'/',2),'/',-(1)) AS sku5,
substring_index(
substring_index(ob1.literal,'/',3),'/',-(1)) AS sku6,
substring_index(ob2.literal,'/',1) AS sku7,
substring_index(
substring_index(ob2.literal,'/',2),'/',-(1)) AS sku8,
substring_index(
substring_index(ob2.literal,'/',3),'/',-(1)) AS sku9,concat_ws(',',ob.level_one,
substring_index(ob1.literal,'/',1),
substring_index(ob2.literal,'/',1)) AS sku1_filter,concat_ws(',',ob.level_two,
substring_index(
substring_index(ob1.literal,'/',2),'/',-(1)),
substring_index(
substring_index(ob2.literal,'/',2),'/',-(1))) AS sku2_filter,concat_ws(',',ob.level_three,
substring_index(
substring_index(ob1.literal,'/',3),'/',-(1)),
substring_index(
substring_index(ob2.literal,'/',3),'/',-(1))) AS sku3_filter,
t.title_id AS title_id,
t.us_list_price AS us_list_price,
t.uk_list_price AS uk_list_price,
t.can_list_price AS can_list_price,
t.aus_list_price AS aus_list_price,
t.min_age AS min_age,
t.max_age AS max_age,
t.book_club AS book_club,
t.best_seller AS best_seller,
t.award_winner AS award_winner,
t.asin AS asin,
t.format AS format,
ob.literal AS literal_1,
ob1.literal AS literal_2,
ob2.literal AS literal_3,
t.series AS series,
t.volume AS volume,
group_concat(distinct concat(u.first_name, ' ', u.last_name) separator ', ') AS marketer,
a.group_letter AS group_letter,
group_concat(distinct concat(u2.first_name, ' ', u2.last_name) separator ', ') AS editor,
oi.imprint_name AS imprint_name,
ob.level_one AS level_one,
ob.level_two AS level_two,
ob.level_three AS level_three,
ot1.territory_name AS rights,
(case when (isnull(t.active) or (t.active = ''))
then '' when (t.active = 'Y')
then 'Active' when (t.active = 'N')
then 'In Development' when (t.active = 'X')
then 'Terminated' when (t.active = 'T')
then 'Transmittal' end) AS status,
(case when (isnull(t.format) or (t.format = ''))
then '' when (t.format = 4)
then 'Ebook' when (t.format = 8)
then 'Print Book' when (t.format = 9)
then 'Audio Book' end) AS format_name,
t.title AS title,t.primary_isbn13 AS primary_isbn13,
group_concat(distinct a.display_name order by a.display_name ASC separator ', ') AS contributors,
group_concat(distinct c1.display_name order by c1.display_name ASC separator ', ') AS publishers,
t.publish_date AS pub_date,
group_concat(distinct g.name order by g.name ASC separator ', ') AS category,
group_concat(distinct g0.id order by g0.id ASC separator ', ') AS secondary_category_ids,
group_concat(distinct g0.name order by g0.name ASC separator ', ') AS secondary_categories
from ((((((((((((((((((((wawa_title t
left join wawa_title_to_imprint tti on((t.title_id = tti.title_id)))
left join wawa_imprint oi on((tti.imprint_id = oi.imprint_id)))
left join wawa_title_to_supplier t2a on((t2a.title_id = t.title_id)))
left join wawa_territories ot1 on((t.territory_id = ot1.territory_id)))
left join wawa_supplier a on((a.supplier_id = t2a.supplier_id)))
left join wawa_sku ob on((ob.code = t.sku1)))
left join wawa_sku ob1 on((ob1.code = t.sku2)))
left join wawa_sku ob2 on((ob2.code = t.sku3)))
left join bll_formats tf on((t.format = tf.id)))
left join bll_suppliers_to_wawa_editors aoe on((a.supplier_id = aoe.supplier_id)))
left join bll_suppliers_to_wawa_marketing_contacts amc on((a.supplier_id = amc.supplier_id)))
left join bll_contacts c0 on((c0.id = amc.id)))
left join users u on((amc.user_id = u.id)))
left join users u2 on((aoe.user_id = u2.id)))
join wawa_title_to_publisher t2p on((t2p.title_id = t.title_id)))
join wawa_publisher p on((p.publisher_id = t2p.publisher_id)))
join bll_contacts c1 on((c1.id = p.org_contact_id)))
left join wawa_title_to_genre t2g on((t2g.title_id = t.title_id)))
left join wawa_genres g on((g.id = t2g.genre_id_2)))
left join wawa_genres g0 on((g0.id = t2g.genre_id_3))) where ((t.title_id = t.title_id) and (t.active <> 'X'))
group by t.title_id
解释的输出是:
+----+-------------+-------+--------+-----------------------------------------------------------------------------------------------+-------------------------------------+---------+------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------------------+-------------------------------------+---------+------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | p | index | PRIMARY,org_contact_fk | org_contact_fk | 5 | NULL | 66 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | c1 | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.p.org_contact_id | 1 | |
| 1 | SIMPLE | t2p | ref | idx_title_id,idx_publisher_id | idx_publisher_id | 4 | wawa_ripple_development.p.publisher_id | 66 | Using where |
| 1 | SIMPLE | t | eq_ref | PRIMARY,active_index,idx_title_active_isprimary | PRIMARY | 4 | wawa_ripple_development.t2p.title_id | 1 | Using where |
| 1 | SIMPLE | t2g | ref | idx_title_id | idx_title_id | 4 | wawa_ripple_development.t2p.title_id | 1 | |
| 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.t2g.genre_id_2 | 1 | |
| 1 | SIMPLE | g0 | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.t2g.genre_id_3 | 1 | |
| 1 | SIMPLE | tti | ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.t.title_id | 1 | Using index |
| 1 | SIMPLE | oi | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.tti.imprint_id | 1 | |
| 1 | SIMPLE | t2a | ref | title_id | title_id | 4 | wawa_ripple_development.t.title_id | 1 | Using index |
| 1 | SIMPLE | ot1 | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.t.territory_id | 1 | |
| 1 | SIMPLE | a | eq_ref | PRIMARY,wawa_supplier_venue_id_supplier_id | PRIMARY | 4 | wawa_ripple_development.t2a.supplier_id | 1 | |
| 1 | SIMPLE | ob | eq_ref | PRIMARY | PRIMARY | 29 | wawa_ripple_development.t.sku1 | 1 | |
| 1 | SIMPLE | ob1 | eq_ref | PRIMARY | PRIMARY | 29 | wawa_ripple_development.t.sku2 | 1 | |
| 1 | SIMPLE | ob2 | eq_ref | PRIMARY | PRIMARY | 29 | wawa_ripple_development.t.sku3 | 1 | |
| 1 | SIMPLE | tf | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.t.format | 1 | Using index |
| 1 | SIMPLE | aoe | ref | idx_by_supplier_and_editor,index_bll_suppliers_to_wawa_editors_on_supplier_id | idx_by_supplier_and_editor | 4 | wawa_ripple_development.a.supplier_id | 1 | Using index |
| 1 | SIMPLE | amc | ref | idx_by_supplier_and_marketing_contact,index_bll_suppliers_to_wawa_marketing_contacts_on_supplier_id | idx_by_supplier_and_marketing_contact | 4 | wawa_ripple_development.a.supplier_id | 1 | Using index |
| 1 | SIMPLE | c0 | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.amc.id | 1 | Using index |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.amc.user_id | 1 | |
| 1 | SIMPLE | u2 | eq_ref | PRIMARY | PRIMARY | 4 | wawa_ripple_development.aoe.user_id | 1 | |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------------------+-------------------------------------+---------+------------------------------------------+------+----------------------------------------------+
我担心两件事:
1.) 使用索引;使用临时的;使用文件排序
2.) 使用位置
我担心这些是对的吗?
关于我可以做些什么来加快速度的任何建议?
一些变量:
mysql> show variables like '%buffer%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+---------+
- 使用 index(也叫 type=index)——这是一件"好"事。 它说该表的工作可以完全在索引的 BTree 中完成,而无需接触数据。
- 使用 temporary -- 这表示执行查询至少需要一个临时表。 有些查询绝对必须具有 tmp 表。 所以忍受它。 注意:此短语在
EXPLAIN
中的位置并不表示哪个表真正需要 tmp 或文件排序。 有关此类详细信息,请参阅EXPLAIN FORMAT=JSON SELECT ...
。 - 使用文件排序 - 这表示至少有一个步骤需要排序。 它没有说明这种类型是否必须击中磁盘。 同样,这可能是绝对必要的。
- 使用 where -- 并不意味着什么
- 使用索引条件 -- 这与"使用索引"不同。 ICP意味着将一定的效率用于复杂的
WHERE
;这仅在较新版本中可用。 - eq_ref - 只需要下一个表中的一行。 好。
- 参考 - 不是 1:1,而是 1:多。 从
EXPLAIN
来看,似乎经常接近1:1。
至于加快速度...
- 你需要
LEFT
吗? 如果不摆脱它;优化程序可能更喜欢其他一些表的顺序。 EXPLAIN
说不需要获取多少行。 (注意:解释的"行"数字是近似值。 所以,我在这里看不到太多帮助。- 看看你是否可以为某些
JOINs
制作"覆盖索引"——但只对"ref"情况这样做,而不是"eq_ref + PRIMARY"情况。 看起来您已经对此进行了彻底的工作。 - 您将如何处理 11000 行? 对于 UI 来说,这似乎"不合理"? 如果它注定要进行一些进一步的处理,它多久完成一次? (也就是说,10秒真的重要吗?
- 您使用什么引擎? 您有多少内存?
SHOW VARIABLES LIKE '%buffer%';
-- 我正在钓鱼内存利用率。 -
JOIN
+GROUP BY
-- 这通常意味着行的爆炸,然后是内爆。 将SELECT
中的某些JOIN
更改为子查询可能会改善以下内容:( SELECT ... FROM ... WHERE ... LIMIT 1 ) AS whatever
. - 是否有任何字段
TEXT
? 这会强制从内存中取出"文件排序"(因此速度较慢)。 给我更多细节,也许我们可以解决这个问题。
总之,EXPLAIN
看起来很干净。 大量的JOINs
是另一回事。
两个可能不良的架构设计:
- 跨表的数组 (*_price)
- 跨列的数组(级别*、SKU*、用户*、流派*)
附录
对于16GB的RAM以及InnoDB和MyISAM的混合,建议key_buffer_size = 1500M
和innodb_buffer_pool_size = 5G
。 在 my.cnf
(或 my.ini
)中设置这些,然后重新启动mysqld
。