Joomla MySQL性能 [英] Joomla MySQL Performance
问题描述
我一直在开发一个版本为2.5.11的Joomla网站，该网站的访问量非常大.
I have been developing a Joomla site with version 2.5.11.Site will be under very high traffic.
我的问题是关于MySQL查询性能的.数据库在内容表中包含约60000行，下面看到的查询(核心com_content文章模型查询)执行时间约为6秒.非常慢.
My problem is about MySQL query performance. Database includes about 60000 rows in content table, and the query seen below (core com_content articles model query) execution time is about 6 seconds.Very slow.
SELECT a.id,
a.title,
a.alias,
a.title_alias,
a.introtext,
a.checked_out,
a.checked_out_time,
a.catid,
a.created,
a.created_by,
a.created_by_alias,
CASE
WHEN a.modified = 0 THEN a.created
ELSE a.modified
END as modified,
a.modified_by,
uam.name as modified_by_name,
CASE
WHEN a.publish_up = 0 THEN a.created
ELSE a.publish_up
END as publish_up,
a.publish_down,
a.images,
a.urls,
a.attribs,
a.metadata,
a.metakey,
a.metadesc,
a.access,
a.hits,
a.xreference,
a.featured,
LENGTH(a.fulltext) AS readmore,
CASE
WHEN badcats.id is not null THEN 0
ELSE a.state
END AS state,
c.title AS category_title,
c.path AS category_route,
c.access AS category_access,
c.alias AS category_alias,
CASE
WHEN a.created_by_alias > ' ' THEN a.created_by_alias
ELSE ua.name
END AS author,
ua.email AS author_email,
contact.id as contactid,
parent.title as parent_title,
parent.id as parent_id,
parent.path as parent_route,
parent.alias as parent_alias,
ROUND(v.rating_sum / v.rating_count, 0) AS rating,
v.rating_count as rating_count,
c.published,
CASE
WHEN badcats.id is null THEN c.published
ELSE 0
END AS parents_published
FROM #__content AS a
LEFT JOIN #__content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN #__categories AS c ON c.id = a.catid
LEFT JOIN #__users AS ua ON ua.id = a.created_by
LEFT JOIN #__users AS uam ON uam.id = a.modified_by
LEFT JOIN
(SELECT contact.user_id, MAX(contact.id) AS id, contact.language FROM
#__contact_details AS contact WHERE contact.published = 1 GROUP BY
contact.user_id, contact.language) AS contact ON contact.user_id =
a.created_by
LEFT JOIN #__categories as parent ON parent.id = c.parent_id
LEFT JOIN #__content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN
(SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS
parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension
= 'com_content' AND parent.published != 1 GROUP BY cat.id) AS badcats ON
badcats.id = c.id
WHERE CASE
WHEN badcats.id is null THEN a.state
ELSE 0
END = 1 AND
a.featured = 0 AND
a.id NOT IN (8921, 33722, 33728, 33729, 34187, 35047, 36784, 36236, 33724,
19522) AND
a.catid IN (8, 39, 40, 38, 72, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 36, 37, 41) AND
(a.publish_up = '0000-00-00 00:00:00' OR
a.publish_up <= '2013-06-12 06:44:44') AND
(a.publish_down = '0000-00-00 00:00:00' OR
a.publish_down >= '2013-06-12 06:44:44')
GROUP BY a.id,
a.title,
a.alias,
a.title_alias,
a.introtext,
a.checked_out,
a.checked_out_time,
a.catid,
a.created,
a.created_by,
a.created_by_alias,
a.created,
a.modified,
a.modified_by,
uam.name,
a.publish_up,
a.attribs,
a.metadata,
a.metakey,
a.metadesc,
a.access,
a.hits,
a.xreference,
a.featured,
a.fulltext,
a.state,
a.publish_down,
badcats.id,
c.title,
c.path,
c.access,
c.alias,
uam.id,
ua.name,
ua.email,
contact.id,
parent.title,
parent.id,
parent.path,
parent.alias,
v.rating_sum,
v.rating_count,
c.published,
c.lft,
a.ordering,
parent.lft,
fp.ordering,
c.id,
a.images,
a.urls
ORDER BY publish_up DESC
LIMIT 4, 4
例如，当我改变
(a.publish_up = '0000-00-00 00:00:00' OR
a.publish_up <= '2013-06-12 06:44:44') AND
(a.publish_down = '0000-00-00 00:00:00' OR
a.publish_down >= '2013-06-12 06:44:44')
与
(a.publish_up >= DATE_SUB(NOW(), INTERVAL 2 MONTH))
查询执行时间约为〜0.5秒
Query execution time is about ~0.5second
查询说明:
在不破解核心代码的情况下该怎么办，我从不希望破解核心代码...
What can I do without hacking the core code,I never want to hack the core code...
MySQL服务器配置:
MySQL server Config:
skip-external-locking
skip-name-resolve
key_buffer_size = 128M
max_connections=1024
max_allowed_packet = 16M
net_buffer_length = 8K
table_open_cache = 512
table_cache = 2048
join_buffer_size = 128M
read_buffer_size = 128M
sort_buffer_size = 128M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=128M
thread_concurrency=16
query_cache_size = 128M
query_cache_type=1
thread_cache_size = 1300
query_cache_limit=128M
max_heap_table_size = 1024M
tmp_table_size = 1024M
推荐答案
我发现改进这种非常慢的查询的最佳方法是删除它对存档的引用(这破坏了存档状态的全部目的，即从列表结果中删除这些项目).只需接受已发布(或已发布+未发布)的项目，这样eiher state = 1或state IN(0,1)即可使用
The best way I've found to improve that very slow query it to remove the references to archive (which defeat the entire purpose of the archive status which is to remove those items from list results). Just take items that are published (or published + unpublished) so eiher state=1 or state IN(0,1) You could look at possibly using http://extensions.joomla.org/extensions/style-a-design/templating/15611
切换到InnoDB可能也会有所帮助，但是拥有最新的MySQL版本是获得改进的最大途径.
Switching to InnoDB will possibly help also but having the most recent possible version of MySQL is the biggest way to get improvements.
关于您现在可以做什么"，我很乐意看到您提出的拉取请求，以便为该查询添加日期限制.我们最近在JDatabaseQuery中添加了数据数学运算，因此我的工作相对简单，将是一个非常不错的改进.还有一些其他悬而未决的问题，它们会对性能产生重大影响，如果您对其进行测试和评论，它们将进入下一个版本的代码库.例如: https://github.com/joomla/joomla-cms/pull/1274
About "what can you do now" I would love to see a pull request from you to add date limitations to that query. We have recently added data math to JDatabaseQuery so i is relatively simple to do and would be a really nice improvement. There are also some other pending issues that have big performance impacts and if you test and comment on them they will move forward into the code base for the next release. For example: https://github.com/joomla/joomla-cms/pull/1274
这篇关于Joomla MySQL性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
