一个游戏网站的Mysql搜索表优化

Question

我有困难，因为一个类似于http://box10.com的游戏网站的数据库结构。我试图稍微改变一下表的结构，但是我使它变得更糟了，现在我不能再犯另一个错误来留住我的客户。所以我害怕做任何改变。如果你能帮我，我会非常感激的。我不想写我的报告和关于配置的想法，不想占用你更多的时间。

有两张表格与我的问题有关。

    CREATE TABLE `_games` (
    `id` INT(6) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(100) NOT NULL,
    `perma` VARCHAR(100) NOT NULL,
    `approve` TINYINT(1) NOT NULL DEFAULT '0',
    `tags` MEDIUMTEXT NOT NULL,
    `description` MEDIUMTEXT NOT NULL,
    INDEX `id` (`id`),
    INDEX `approve` (`approve`),
    FULLTEXT INDEX `ad` (`title`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=15000

CREATE TABLE `_searches` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `term` VARCHAR(200) NOT NULL DEFAULT '',
    `viewcount` INT(10) NOT NULL DEFAULT '0',
    `date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=450000

使用_searches表：

在搜索页面上，如果搜索项不存在，则将搜索项插入到_searches表中。如果存在，则更新日期和视图计数。

$date = date("Y-m-d G:i:s");
$c = mysql_query("SELECT id FROM _searches WHERE term='$term'");
if (mysql_num_rows($c) == 1) {
    mysql_query("update _searches set viewcount=viewcount+1,date='$date' WHERE term='$term'");
} else {
    mysql_query("insert into _searches (term,viewcount,date) values ('$term',1,'$date')");
}

当我使用上面的代码时，mysqld CPU的使用率从%50上升到%500，负载从0.7上升到7。能给我一些建议吗？

使用_games表：

在标记页上，我使用以下sql获取记录：(例如y8.com/ tag /Action)

SELECT title,perma FROM _games WHERE tags LIKE '%action,%' AND approve=0 order by id desc LIMIT 0, 20

在搜索页面上，搜索游戏：

SELECT perma,title, MATCH(title) AGAINST('$term') AS sort
FROM _games  WHERE MATCH(title) AGAINST('$term' IN BOOLEAN MODE) and approve=0
ORDER BY sort DESC limit 10

在游戏页面的“相关游戏”部分：(例如http://www.oyunlar1.com/online.php?flash=5661)

SELECT perma,title, MATCH(title) AGAINST('mario kills the bad guy') AS sort
FROM _games  WHERE MATCH(title) AGAINST('mario kills the bad guy' IN BOOLEAN MODE) and approve=0
ORDER BY sort DESC limit 10

在主页上获取记录：

SELECT title,perma FROM _games where approve=0 order by id desc LIMIT 0,28

在游戏页面上获取游戏信息：

SELECT title,description FROM _games WHERE perma='mario-kills-the-bad-guy' AND approve=0

my.cnf文件：

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Performance optimization
skip-external-locking
max_connections         = 300  
key_buffer              = 8M
sort_buffer             = 1M
join_buffer_size        = 256K
max_allowed_packet      = 1M
thread_stack            = 128K
thread_cache_size       = 2
table_cache             = 1024
thread_concurrency      = 2
query_cache_limit       = 128k
query_cache_size        = 4M

# InnoDB optimization
innodb_file_per_table
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 30
innodb_thread_concurrency = 2
innodb_locks_unsafe_for_binlog = 1
innodb_table_locks = 0 

innodb_log_file_size = 2M
innodb_buffer_pool_size = 128M


# !!!! do not change next 2 values !!!!
# data will get destroyed unless you backup everything before changing and then import it back.
innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size = 256k

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

服务器：

CentOS 5(64位) (CEN564) Plesk 9.5

Xeon 3220 / 8GB内存/ 2x250GB SATAII /10 8GB/1 1GiGE /8 IPS (SoftLayer)

MySQL 5.0.77

服务器每天的浏览量约为50万次。

我愿意接受各种建议，谢谢你抽出时间。

Answer 1

_searches表的明显问题是，在搜索的“term”列上没有索引。因此，您应该首先添加一个索引(也许也是唯一的，我将在下面解释原因)

ie：

CREATE [UNIQUE] INDEX IX_Term on _searches(term);

这将大大加快选择查询的速度。此外，还可以使用INSERT DELAYED (http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html)和ON DUPLICATE KEY UPDATE (http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html)改进下面的if语句。要使其工作，您需要一个术语上的唯一索引。因此，查询可以是：

INSERT DELAYED INTO _searches (term, viewcount,date) VALUES ('$term', 1, '$date') ON DUPLICATE KEY UPDATE viewcount = viewcount + 1;

请注意，为了避免SQL注入，$term和$date应该被转义。

上面的查询所做的是在表中插入一个新行，但是如果这个术语已经存在(受唯一索引的限制)，而不是插入，它将更新现有的行，使viewcount递增1。

还要注意，更新可以锁定表，限制将来的插入，如果_searches表太大，很容易导致数据库上的死锁。

Answer 2

1. 您可以在_search

的插入查询中添加“关于重复的键更新”

而不是

$c = mysql_query("SELECT id FROM _searches WHERE term='$term'");
if (mysql_num_rows($c) == 1) {
    mysql_query("update _searches set viewcount=viewcount+1,date='$date' WHERE term='$term'");
} else {
    mysql_query("insert into _searches (term,viewcount,date) values ('$term',1,'$date')");
}

你可以写

mysql_query("insert into _searches (term,viewcount,date) values ('$term',1,'$date') on duplicate key update viewcount=viewcount+1, date=$date")

我也看到了，你没有“术语”字段的索引.从非索引字段中进行选择也会导致此问题。

别忘了全文搜索总是很慢..。你能安装一个像斯芬克斯(http://sphinxsearch.com/)或solr (http://lucene.apache.org/solr/)这样的全文搜索引擎吗?也可以用memcache或apc缓存到内存中，这样肯定会对你有帮助。