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

我的问题是关于 MySQL 查询性能的。数据库包含大约 60000 行的内容表，下面看到的查询(核心 com_content 文章模型查询)执行时间大约为 6 秒。非常慢。

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

最佳答案

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

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

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

关于mysql - Joomla MySQL 性能，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17065038/