MySQL索引帮助-哪个更快?

What I'm dealing with:

我处理:

I have a project which uses ActiveCollab 2, and the database structure is new to me - practically everything gets stored to a project_objects table and has a recursively hierarchical relationship:

我有一个使用ActiveCollab 2的项目，数据库结构对我来说是全新的——几乎所有东西都存储在project_objects表中，并且具有递归的层次化关系:

Record 1234 might be type "Ticket" with parent_id of 123
记录1234可能是类型为“Ticket”，parent_id为123
Record 123 might be type "Category" with parent_id of 12
记录123可能是类型“Category”，parent_id为12
Record 12 might be type "Milestone" and so on.
记录12可能是类型“里程碑”，等等。

Currently there are upwards of 450,000 records in this table and many of the queries in the code reference the name field which does NOT have an index on it. An example value might be Design or Development.

目前，该表中有超过45万条记录，代码中的许多查询都引用了没有索引的name字段。一个示例值可能是设计或开发。

This might be an example query:

这可能是一个示例查询:

SELECT * FROM project_objects WHERE type = "Ticket" and name = "Design"

从project_objects中选择*，其中type = "Ticket"， name = "Design"

My problem:

我的问题:

I have a query that is taking upwards of 12-15 seconds and I have a feeling it's from that name column lacking the index and requiring the full text search. My understanding with indexes is that if I add one to the name field, it'll speed up the reads, but slow down the inserts and updates. Does the index need to get rebuilt completely every time a record is added or updated or is it just altered/appended? I don't want to optimize this query with an index if it means drastically slowing down other parts of the code base which depend on faster writes.

我有一个超过12-15秒的查询，我感觉它来自那个缺少索引的name列，需要完整的文本搜索。我对索引的理解是，如果我向name字段添加一个索引，它会加快读取速度，但会减慢插入和更新。每次添加或更新记录时，索引是否需要完全重新构建?还是只是修改或添加记录?我不希望使用索引来优化这个查询，如果这意味着要大大减慢依赖于更快写的代码库的其他部分。

My question:

我的问题:

Assume 100 reads and 100 writes per day, which is more likely to be a faster process for MySQL - executing the above query on the above table without the index or having to rebuild the index every time a record is added?

假设每天读100次和写100次，这对MySQL来说更可能是一个更快的过程——在上面的表上执行上面的查询而没有索引，或者每次添加记录时都必须重新构建索引?

I don't have the knowledge or authority to start running benchmarks, but I would like to offer a suggestion to the client without sounding completely novice. Thanks!

我不具备开始运行基准的知识或权限，但我想向客户提供一个建议，而不需要听起来完全是新手。谢谢!

EDIT: Here is the table:

编辑:这是表格:

'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'

2 个解决方案

#1

As @Ray points out, indexes do not have to be rebuilt on every Insert, Update or Delete operation. So, if you only want to improve efficuency of this (or similar) queries, add either an index on (name, type) or on (type, name).

正如@Ray所指出的，索引不必在每次插入、更新或删除操作上重新构建。因此，如果您只想提高这个(或类似的)查询的效率，可以在(name, type)或on (type, name)上添加索引。

Since you already have an index on (type) alone, I would add the first one:

由于您已经有了一个单独的索引，我将添加第一个索引:

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

It may take a few seconds on a busy server but it has to be done once and then all the queries with conditions like yours will benefit. It may also improve efficiency of several other types of queries that involve name only or name and 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`

2 个解决方案

#1

更多相关文章

随机推荐