我所面对的问题:
我有一个项目使用了ActiveCollab 2,数据库结构对我来说是新的 - 几乎所有数据都存储在project_objects
表中,并具有递归分层关系:
- 记录1234可能是
type
为“Ticket”,其parent_id
为123 - 记录123可能是
type
为“Category”,其parent_id
为12 - 记录12可能是
type
为“Milestone”等。
目前在这个表中有超过45万条记录,代码中的许多查询都引用了name
字段,但该字段没有索引。例如值可能是Design
或Development
。
以下是一个示例查询:
SELECT * FROM project_objects WHERE type = "Ticket" and name = "Design"
我的问题:
我有一个查询需要花费12-15秒以上,我觉得这是由于name
列缺少索引并需要进行全文搜索。我对索引的理解是,如果我向name
字段添加一个索引,它将加快读取速度,但会减慢插入和更新速度。每次添加或更新记录时,索引是否需要完全重建还是只需要更改/追加?如果我使用索引来优化此查询,是否会显著减慢依赖于更快写入的代码库的其他部分?
我的问题:
假设每天有100个读取和100个写入,对于MySQL来说哪个流程更快 - 在没有索引的情况下执行上述查询,还是每次添加记录时都要重建索引?
我没有知识或权威来开始运行基准测试,但我想提供一个建议给客户,而不显得完全新手。谢谢!
编辑:这是表格:
'CREATE TABLE `project_objects` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`source` varchar(50) DEFAULT NULL,
`type` varchar(30) NOT NULL DEFAULT ''ProjectObject'',
`module` varchar(30) NOT NULL DEFAULT ''system'',
`project_id` int(10) unsigned NOT NULL DEFAULT ''0'',
`milestone_id` int(10) unsigned DEFAULT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
`parent_type` varchar(30) DEFAULT NULL,
`name` varchar(150) DEFAULT NULL,
`body` longtext,
`tags` text,
`state` tinyint(4) NOT NULL DEFAULT ''0'',
`visibility` tinyint(4) NOT NULL DEFAULT ''0'',
`priority` tinyint(4) DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`created_by_id` smallint(5) unsigned NOT NULL DEFAULT ''0'',
`created_by_name` varchar(100) DEFAULT NULL,
`created_by_email` varchar(100) DEFAULT NULL,
`updated_on` datetime DEFAULT NULL,
`updated_by_id` smallint(5) unsigned DEFAULT NULL,
`updated_by_name` varchar(100) DEFAULT NULL,
`updated_by_email` varchar(100) DEFAULT NULL,
`due_on` date DEFAULT NULL,
`completed_on` datetime DEFAULT NULL,
`completed_by_id` smallint(5) unsigned DEFAULT NULL,
`completed_by_name` varchar(100) DEFAULT NULL,
`completed_by_email` varchar(100) DEFAULT NULL,
`comments_count` smallint(5) unsigned DEFAULT NULL,
`has_time` tinyint(1) unsigned NOT NULL DEFAULT ''0'',
`is_locked` tinyint(3) unsigned DEFAULT NULL,
`estimate` float(9,2) DEFAULT NULL,
`start_on` date DEFAULT NULL,
`start_on_text` varchar(50) DEFAULT NULL,
`due_on_text` varchar(50) DEFAULT NULL,
`workflow_status` int(4) DEFAULT NULL,
`varchar_field_1` varchar(255) DEFAULT NULL,
`varchar_field_2` varchar(255) DEFAULT NULL,
`integer_field_1` int(11) DEFAULT NULL,
`integer_field_2` int(11) DEFAULT NULL,
`float_field_1` double(10,2) DEFAULT NULL,
`float_field_2` double(10,2) DEFAULT NULL,
`text_field_1` longtext,
`text_field_2` longtext,
`date_field_1` date DEFAULT NULL,
`date_field_2` date DEFAULT NULL,
`datetime_field_1` datetime DEFAULT NULL,
`datetime_field_2` datetime DEFAULT NULL,
`boolean_field_1` tinyint(1) unsigned DEFAULT NULL,
`boolean_field_2` tinyint(1) unsigned DEFAULT NULL,
`position` int(10) unsigned DEFAULT NULL,
`version` int(10) unsigned NOT NULL DEFAULT ''0'',
PRIMARY KEY (`id`),
KEY `type` (`type`),
KEY `module` (`module`),
KEY `project_id` (`project_id`),
KEY `parent_id` (`parent_id`),
KEY `created_on` (`created_on`),
KEY `due_on` (`due_on`)
KEY `milestone_id` (`milestone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=993109 DEFAULT CHARSET=utf8'
SHOW CREATE TABLE project_objects
的输出)。对于这个查询,最好在(name, type)
或(type, name)
上创建索引。 - ypercubeᵀᴹ