sql - 帮助优化MySQL查询

Question

(使用MySQL 4.1.22)

我无法让我的查询在大表(200k+ 行)上使用索引，它正在对其进行全表扫描。目前查询大约需要 1.2 秒。如果可能的话，我希望将其控制在 0.2 秒以内。

这是我的查询:

SELECT st_issues.issue_id, st_issues.cat_id,st_categories.name AS cat_name, st_issues.status_id,st_statuses.name AS status_name, st_issues.priority_id,st_priorities.name AS priority_name,st_priorities.color AS color, st_issues.assigned_cid,assigned_u.firstname,assigned_u.lastname,assigned_u.screenname, message, rating, created_by_email,created_by_cid,created_by_uid,by_user.firstname AS by_firstname,by_user.lastname AS by_lastname,by_user.screenname AS by_screenname, st_issues.browser,from_url,created_by_store,created,st_issues.stamp
FROM st_issues
 JOIN st_categories ON (st_issues.cat_id=st_categories.cat_id)
 JOIN st_statuses ON (st_issues.status_id=st_statuses.status_id)
 JOIN st_priorities ON (st_issues.priority_id=st_priorities.priority_id)
 LEFT JOIN users AS assigned_u ON (assigned_u.cid=st_issues.assigned_cid)
 LEFT JOIN users AS by_user ON (by_user.uid=st_issues.created_by_uid)
 LEFT JOIN st_issue_changes ON (st_issues.issue_id=st_issue_changes.issue_id AND change_id=0)
WHERE st_issues.assigned_cid=0

解释结果:

1, 'SIMPLE', 'st_issues', 'ALL', '', '', , '', 4, 'Using where'
1, 'SIMPLE', 'st_categories', 'eq_ref', 'PRIMARY', 'PRIMARY', 1, 'sg.st_issues.cat_id', 1, ''
1, 'SIMPLE', 'st_priorities', 'eq_ref', 'PRIMARY', 'PRIMARY', 1, 'sg.st_issues.priority_id', 1, ''
1, 'SIMPLE', 'assigned_u', 'ref', 'cid', 'cid', 8, 'sg.st_issues.assigned_cid', 1, ''
1, 'SIMPLE', 'st_statuses', 'ALL', 'PRIMARY', '', , '', 4, 'Using where'
1, 'SIMPLE', 'by_user', 'ALL', '', '', , '', 221623, ''
1, 'SIMPLE', 'st_issue_changes', 'eq_ref', 'PRIMARY', 'PRIMARY', 6, 'sg.st_issues.issue_id,const', 1, ''

显然问题出在“by_user”上的联接，因为它没有使用索引。

以下是“用户”表的一些定义:

CREATE TABLE  `users` (
  `cid` double unsigned NOT NULL auto_increment,
  `uid` varchar(20) NOT NULL default '',
...
  `firstname` varchar(20) default NULL,
  `lastname` varchar(20) default NULL,
...
  PRIMARY KEY  (`uid`),
...
) ENGINE=InnoDB

有人知道为什么在连接中不使用主键吗？
有人对如何加快此查询有任何想法或提示吗？

(如果需要/想要，我可以添加其他表的表定义)

编辑:

这是 st_issues 的表定义:

CREATE TABLE  `st_issues` (
  `issue_id` int(10) unsigned NOT NULL auto_increment,
  `cat_id` tinyint(3) unsigned NOT NULL default '0',
  `status_id` tinyint(3) unsigned NOT NULL default '0',
  `priority_id` tinyint(3) unsigned NOT NULL default '0',
  `assigned_cid` int(10) unsigned NOT NULL default '0',
  `rating` tinyint(4) default NULL,
  `created_by_email` varchar(255) NOT NULL default '',
  `created_by_cid` int(10) unsigned NOT NULL default '0',
  `created_by_uid` varchar(20) NOT NULL default '',
  `created_by_store` tinyint(3) unsigned NOT NULL default '0',
  `browser` varchar(255) NOT NULL default '',
  `from_url` varchar(255) NOT NULL default '',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `stamp` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`issue_id`),
  KEY `idx_create_by_cid` (`created_by_cid`),
  KEY `idx_create_by_uid` (`created_by_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

这是用户表的全部定义吗？

因为它说:

) ENGINE=InnoDB

而 st_issues 说:

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果您的两个表使用不同的排序规则，则 uid 和created_by_uid 的两个字符串数据类型是不同的，MySQL 必须在比较它们之前执行字符集强制转换，从而破坏您的索引。

最好确保数据库中的所有文本使用相同的字符集/排序规则。