mysql - 在具有频繁写入和全文列的大表上优化 mySQL 数据库？

Question

我的团队经营着一个购物信息网站，随着我们开始成长，我们开始遇到产品表上的查询响应时间影响显示速度的问题。

我们遇到的主要问题是当用户“保存”一个项目时触发更新查询，因为其他用户正在搜索也存在的 FULLTEXT 索引:

UPDATE product SET listed = listed+1 WHERE product_id = XX

例如，我刚刚在 0.01 秒内运行了更新，没有其他查询命中，但几分钟前，还有一个大的 FULLTEXT 请求也在进行，同样的请求用了 23 秒。

我假设这是因为表是 MYISAM 并且不能进行行级锁。

我们的产品表包含超过 350 万条记录，到月底将翻一番。之后，它应该趋于平稳，每月增加 2-5%。

CREATE TABLE product (
    product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id INT UNSIGNED NOT NULL DEFAULT '0',
    title VARCHAR (100) NOT NULL DEFAULT '',
    short_desc VARCHAR (255) NOT NULL DEFAULT '',
    description TEXT NOT NULL,
    msrp DECIMAL (6,2) NOT NULL DEFAULT '000.00',
    rating DECIMAL(3,2) NOT NULL DEFAULT '0.0',
    reviews INT UNSIGNED NOT NULL DEFAULT '0',
    listed INT UNSIGNED NOT NULL DEFAULT '0',
    sku VARCHAR(75) NOT NULL DEFAULT '0',
    upc VARCHAR(20) NOT NULL DEFAULT '0',
    updateddate DATETIME NOT NULL,
    PRIMARY KEY (product_id),
    KEY title (title),
    KEY category_id (category_id),  
    KEY listed (listed),
    KEY mfrg_id (mfrg_id),
    KEY identifier (identifier),
    FULLTEXT INDEX (title),
    FULLTEXT INDEX (description)
) ENGINE = MYISAM;

数据库在仅托管我们站点的专用服务器上运行。我们计划将数据库移动到一个复制结构中，该结构具有用于查询框 [slave] 的 Dual Proc、16gb RAM 服务器和处理写入的当前“web”服务器 [dual proc，4gb ram]。

我不是 DB 专家 [显然] 并且从研究开始对与 MYISAM 同时运行 InnoDB 持谨慎态度 [复制和备份影响？] 但它看起来确实像拆分产品表以容纳主要信息 [innodb]并且单独的全文描述 [myisam] 可能会有很大帮助？

如果您有想法并需要更多信息，请发表评论，我会提供更多详细信息。

谢谢

Answer 1

关于 MyIsam，您说的很对。它是表锁而不是像 innoDB 那样的行锁。

所以从你所说的，查询(全文)花费了很多时间，所以更新需要等待完成。

你真的应该考虑切换到 innoDB(简单的解决方案)或将你的全文搜索切换到其他地方，如 solr、elastic search、sphinx。

此外，您应该检查您的 slow_query 日志并优化所有这些查询。