php - 某游戏网站的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/tags/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

我的.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

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

MySQL 5.0.77

服务器每天接收大约 500,000 次网页浏览。

我乐于接受各种建议，感谢您的宝贵时间。

Answer 1

您的 _searches 表的明显问题是您搜索的“术语”列上没有索引。所以你应该从添加一个索引开始(也许也是唯一的，我将在下面解释原因)

即:

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

这将大大加快您的选择查询。您还可以使用 INSERT DELAYED (http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html) 和 ON DUPLICATE 改进下面的 if 语句关键更新 (http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html)。为此，您需要一个 UNIQUE INDEX on term。所以查询可以是:

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

请注意 $term 和 $date 应该被转义以避免 SQL 注入(inject)

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

另请注意，UPDATES 可能会锁定您的表以限制 future 的 INSERTS，如果您的 _searches 表变得太大，这很容易导致数据库死锁。