mysql - SugarCRM 慢查询(在 MySQL 上)

Question

我们将 SugarCRM 与 MySQL 5.5 数据库一起使用，发现频繁出现的查询性能不佳。

不幸的是，由于 SugarCRM 的性质，查询的重新排序是不可能的。我已经尝试通过索引进行优化，但我还没有走得太远。也就是说，我也不太擅长这样做。

您能否建议任何替代索引来改善我们的结果并避免文件排序？

查询:

SELECT DISTINCT cases.id, cases.case_number, cases.status, cases.name,
   cases.date_entered, 
   cases.assigned_user_id, cases.system_id
FROM cases
INNER JOIN team_sets_teams tst ON tst.team_set_id = cases.team_set_id
INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id
  AND team_memberships.user_id = 'f09ab586-986c-a6f6-0c2e-4d1f1432b6ec' 
  AND team_memberships.deleted=0
ORDER BY cases.case_number DESC
LIMIT 0,11;

解释结果:

select_type table             type possible_keys                                   key                 ref                               rows Extra
SIMPLE      team_memberships  ref  team_id,user_id,idx_team_membership             idx_team_membership const                             26   Using where; Using index; Using temporary; Using filesort
SIMPLE      tst               ref  idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id idx_ud_team_id      sugarcrm.team_memberships.team_id 7    Using where
SIMPLE      cases             ref  idx_cases_tmst_id,idx_cases_created             idx_cases_tmst_id   sugarcrm.tst.team_set_id          5    Using where

表定义:

CREATE TABLE `cases` (
`id` char(36) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`date_entered` datetime DEFAULT NULL,
`date_modified` datetime DEFAULT NULL,
`modified_user_id` char(36) DEFAULT NULL,
`created_by` char(36) DEFAULT NULL,
`description` text,
`deleted` tinyint(1) DEFAULT '0',
`assigned_user_id` char(36) DEFAULT NULL,
`team_id` char(36) DEFAULT NULL,
`case_number` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(255) DEFAULT NULL,
`status` varchar(100) DEFAULT NULL,
`priority` varchar(100) DEFAULT NULL,
`resolution` text,
`system_id` int(11) DEFAULT NULL,
`work_log` text,
`account_id` char(36) DEFAULT NULL,
`portal_viewable` tinyint(1) DEFAULT '0',
`team_set_id` char(36) DEFAULT NULL,
`parent_id` char(36) DEFAULT NULL,
`parent_type` varchar(255) DEFAULT 'Cases',
PRIMARY KEY (`id`),
UNIQUE KEY `casesnumk` (`case_number`),
UNIQUE KEY `case_number` (`case_number`,`system_id`),
KEY `idx_case_name` (`name`),
KEY `idx_account_id` (`account_id`),
KEY `idx_cases_stat_del` (`assigned_user_id`,`status`,`deleted`),
KEY `idx_cases_tmst_id` (`team_set_id`),
KEY `date_modified` (`date_modified`),
KEY `modified_user_id` (`modified_user_id`),
KEY `idx_cases_created` (`team_set_id`,`date_entered`),
KEY `team_id` (`team_id`),
KEY `idx_cases_del` (`deleted`),
KEY `idx_cases_date_entered` (`date_entered`),
KEY `idx_cases_status` (`status`),
KEY `idx_cases_parent_id` (`parent_id`),
KEY `idx_cases_priority` (`priority`)
) ENGINE=InnoDB

CREATE TABLE `team_sets_teams` (
`id` char(36) NOT NULL,
`team_set_id` char(36) DEFAULT NULL,
`team_id` char(36) DEFAULT NULL,
`date_modified` datetime DEFAULT NULL,
`deleted` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_ud_set_id` (`team_set_id`,`team_id`),
KEY `idx_ud_team_id` (`team_id`),
KEY `idx_ud_team_set_id` (`team_set_id`),
KEY `idx_tst_deleted` (`deleted`)
) ENGINE=InnoDB

CREATE TABLE `team_memberships` (
`id` char(36) NOT NULL,
`team_id` char(36) DEFAULT NULL,
`user_id` char(36) DEFAULT NULL,
`explicit_assign` tinyint(1) DEFAULT '0',
`implicit_assign` tinyint(1) DEFAULT '0',
`date_modified` datetime DEFAULT NULL,
`deleted` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `date_modified` (`date_modified`),
KEY `team_id` (`team_id`),
KEY `user_id` (`user_id`),
KEY `idx_team_membership` (`user_id`,`team_id`,`deleted`)
) ENGINE=InnoDB

Answer 1

我在从 Ubuntu 10.04 服务器切换到 12.04 时遇到了类似的问题。因为 InnoDB 作为 Ubuntu 12.04 中打包的较新 MySQL 版本的默认引擎。我尝试了一些 MySQL 服务器设置，例如 query_cache_size、innodb_buffer_pool_size、innodb_log_buffer_size，它显着提高了性能。