Joomla MySQL性能

Question

我一直在开发一个2.5.11版本的Joomla网站，该网站将会有非常高的流量。

我的问题是关于MySQL查询性能。数据库在内容表中包括大约60000行，下面看到的查询(核心com_content文章模型查询)的执行时间大约是6 seconds.Very慢。

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秒

查询解释：

​

​

我能做什么而不修改核心代码，我从来不想修改核心代码……

MySQL服务器配置：

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

Answer 1

我找到的改善非常慢的查询的最好方法是删除对存档的引用(这违背了存档状态的全部目的，即从列表结果中删除这些项)。只需获取已发布(或已发布+未发布)的项目，这样就可以使用http://extensions.joomla.org/extensions/style-a-design/templating/15611查看state=1或状态IN(0,1)

切换到InnoDB可能也会有所帮助，但拥有最新版本的MySQL是获得改进的最大途径。

关于“你现在能做什么”，我希望看到你的拉取请求添加日期限制到该查询。我们最近向JDatabaseQuery添加了数据数学，所以我做起来相对简单，这将是一个非常好的改进。还有其他一些对性能有很大影响的悬而未决的问题，如果你对它们进行测试和评论，它们将进入下一个版本的代码库。例如：https://github.com/joomla/joomla-cms/pull/1274

Answer 2

您真的需要对所有这些列执行GROUP BY吗？a.id的一个简单的群就足够了吗？

如果没有效果，可以尝试增加变量tmp_table_size和max_heap_table_size的值。这可能就是为什么你在“额外栏”中看到“使用临时”的原因。出现此消息是因为MySQL在执行排序时耗尽了物理内存，需要先将数据写入磁盘，这样会更慢。

了解这些值是什么：

SELECT @@tmp_table_size, @@max_heap_table_size;

然后将它们设置为更高的值，直到“使用临时”消失。(希望您有足够的内存来使用)：

SET tmp_table_size = ###, @@max_heap_table_size = ###;

在这个页面上有更多信息：http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

如果上述方法不起作用，您可以尝试将每个派生查询放入一个临时表中，并在每个要联接的列上建立索引，然后在每个新的临时表上进行联接。但这可能会使它变慢，特别是如果您必须在每个请求期间执行此操作。否则，如果数据没有更新，那么它可以工作。

附言:我刚刚意识到你的评论“我能做什么而不修改核心代码”。希望会话变量能正常工作。

Answer 3

由于您必须修改核心以更改查询，因此最好的方法是向com_content添加一个新的模型/视图。添加的内容不会被核心的更新所覆盖。