MySQL索引帮助 - 哪个更快？

Question

我所面对的问题：

我有一个项目使用了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'

- Maverick

---

添加表定义（SHOW CREATE TABLE project_objects 的输出）。对于这个查询，最好在 (name, type) 或 (type, name) 上创建索引。 - ypercubeᵀᴹ

Answer 1

正如@Ray所指出的，索引不需要在每个插入、更新或删除操作上都重新构建。因此，如果您只想提高这种（或类似）查询的效率，请在（名称、类型）或（类型、名称）上添加索引。

由于您已经有了一个单独的（类型）索引，我会添加第一个。

ALTER TABLE project_objects 
  ADD INDEX name_type_IDX
    (name, type) ;

在繁忙的服务器上可能需要几秒钟，但只需执行一次，然后所有带有类似于您的条件的查询都将受益。这也可能提高涉及仅name或name和type的其他类型查询的效率：

WHERE name = 'Design' AND type = 'Ticket'      --- your query

WHERE name = 'Design'                          --- condition on `name` only 

GROUP BY name                                  --- group by  `name`

WHERE name LIKE 'Design%'                      --- range condition on `name` only

WHERE name = 'Design'                          --- equality condition on `name`
  AND type LIKE 'Ticket%'                      --- and range condition on `type`

WHERE name = 'Design'                          --- equality condition on `name`
GROUP BY type                                  --- and group by `type`

GROUP BY name                                  --- group by  `name`
       , type                                  --- and  `type`

- ypercubeᵀᴹ

Answer 2

在name列添加单点索引的插入成本很可能可以忽略不计——它可能会增加一个常量时间，可能不超过几毫秒。你会占用一些额外的磁盘空间，但这通常不是一个问题。这与你在选取性能上经历的多秒钟相去甚远。

添加索引，享受性能提升吧。

顺便说一下：索引不会在每次插入时“重建”。它们通常实现为B树，除非你频繁删除，否则在超过几个级别后应该只需要进行很少的重新平衡（并且深度较小的重新平衡是相当便宜的）。

- Ray