重组数据库以获得最佳性能 [英] Restructuring a DB for best performance

问题描述

我需要一些帮助来重组数据库，以便以下select查询更加有效. 问题在于表post中的每个条目都有一个类别列，其列表用逗号分隔.

查询

SELECT id, short_story, SUBSTRING( full_story, 1, 15 ) AS full_story, xfields, title, category, alt_name, comm_num, allow_comm, allow_rate, 
FIXED , rating, vote_num, news_read, votes, editdate, editor, reason, view_edit, tags
FROM post
LEFT JOIN post_plus ON ( post.id = post_plus.news_id ) 
WHERE category REGEXP '[[:<:]](130|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|21|22|24|25|26|27|29|133|135|125|20|132)[[:>:]]'
AND category REGEXP  '[[:<:]](73)[[:>:]]'
AND approve =1
ORDER BY FIXED DESC , DATE DESC 
LIMIT 98 , 7

列表之所以如此之长，是因为我有两个主要类别，其中包含许多子类别.目前，它正在使用ragexp扫描整个表并搜索正确的匹配项.当我检查process list时，我看到大量状态为Creating sort index的上述查询，而我的CPU处于100％使用状态

解释说明我正在使用正确的索引:

+----+-------------+---------------+------+---------------+---------+---------+--------------------+------+-----------------------------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref                | rows | Extra                       |
+----+-------------+---------------+------+---------------+---------+---------+--------------------+------+-----------------------------+
|  1 | SIMPLE      | post      | ref  | approve       | approve | 1       | const              | 9593 | Using where; Using filesort |
|  1 | SIMPLE      | post_plus | ref  | news_id       | news_id | 5       | online.post.id |    1 | NULL                        |
+----+-------------+---------------+------+---------------+---------+---------+--------------------+------+-----------------------------+

构造数据库以处理此任务的最佳方法是什么?

解决方案

您已经注意到自己，这里的主要问题是category字段的结构，导致您的语句在每次被调用时执行全表扫描. /p>

将其中的类别ouf拖到另一个表中，并将其与元表粘合在一起.

首先，创建一个类别表，也许像这样:

CREATE TABLE `post_category_meta` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

现在创建一个元表:

CREATE TABLE `post_category_meta` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `meta` (`post_id`,`category_id`),
  KEY `post_id` (`post_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM

现在将类别添加到类别表中，并将关系添加到元表中.将它们与正确的JOIN您的查询粘合在一起，完成.

I need a little help in restructuring my DB so that the following select query will be more efficient. The problem is every entry in table post has a category column with list separated by comma.

the query

SELECT id, short_story, SUBSTRING( full_story, 1, 15 ) AS full_story, xfields, title, category, alt_name, comm_num, allow_comm, allow_rate, 
FIXED , rating, vote_num, news_read, votes, editdate, editor, reason, view_edit, tags
FROM post
LEFT JOIN post_plus ON ( post.id = post_plus.news_id ) 
WHERE category REGEXP '[[:<:]](130|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|21|22|24|25|26|27|29|133|135|125|20|132)[[:>:]]'
AND category REGEXP  '[[:<:]](73)[[:>:]]'
AND approve =1
ORDER BY FIXED DESC , DATE DESC 
LIMIT 98 , 7

The list is so long because I've couple of main category with lots of subcategories. currently It's scanning the whole table with a ragexp and searching for the right match. When I check process list I see tons of above queries with a status Creating sort index and my cpu is in 100% use

explain indicates i'm using the right indexes:

+----+-------------+---------------+------+---------------+---------+---------+--------------------+------+-----------------------------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref                | rows | Extra                       |
+----+-------------+---------------+------+---------------+---------+---------+--------------------+------+-----------------------------+
|  1 | SIMPLE      | post      | ref  | approve       | approve | 1       | const              | 9593 | Using where; Using filesort |
|  1 | SIMPLE      | post_plus | ref  | news_id       | news_id | 5       | online.post.id |    1 | NULL                        |
+----+-------------+---------------+------+---------------+---------+---------+--------------------+------+-----------------------------+

What is the best way to structure a db to handle this task?

解决方案

As you've noticed yourself, the main issue here is the structure of the category field causing your statement to perform a full table scan everytime it is invoked.

Pull the categories ouf of there into another table, and glue it together with a meta table.

First, create a category table, maybe like this:

CREATE TABLE `post_category_meta` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

Now create a meta table:

CREATE TABLE `post_category_meta` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `meta` (`post_id`,`category_id`),
  KEY `post_id` (`post_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM

Now add your categories into the category table and add the relationship in the meta table. Glue them together with a proper JOIN your query, done.

这篇关于重组数据库以获得最佳性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！