从varchar更改为中文本会导致性能下降 [英] Changing from varchar to mediumtext causes performance degradation

问题描述

我有一个表，用于存储网站的产品评论.该表使用varchar(1000)存储评论评论的平均响应时间为0.5秒.我更改了将数据保存为中文本的列的数据类型，页面响应时间跃升至1.5-2秒.请记住，没有向该列添加任何其他数据，并且PHP代码是相同的.

I have a table which stores product reviews for a website. The table uses varchar(1000) to store the review comment average response time is 0.5 seconds. I changed the datatype of the column that holds data to mediumtext and the page response time jumps to 1.5 - 2 seconds. Baring in mind no additional data was added to the column and the PHP code is the same.

我认为查询时间不是问题，因为MySQL报告它需要0.019秒，无论varchar还是中文本都是相同的.

I don't think the query time is the issue, as MySQL reports it takes 0.019secs, which is the same whether varchar or mediumtext.

我很茫然，不明白这里发生了什么.我正在使用MySQL PDO和PHP.我不知道这是服务器问题，中文本固有的问题还是其他所有问题.

I'm at a loss to understand what's happened here. I'm using MySQL PDO and PHP. I don't know if it's a server issue, an inherent issue with mediumtext, or something else entirely.

我尝试将表类型从MyISAM更改为InnoDB，但这没什么区别.

I tried changing the table type from MyISAM to InnoDB but it made no difference.

这是PHP代码:

$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$attrs = array(PDO::ATTR_PERSISTENT => true);
$pdo = new PDO($dsn, $user, $pass, $attrs);

$stmt = $pdo->prepare("SELECT SQL_NO_CACHE comment
       FROM reviews_product_comments
       WHERE part_id =:partid
         and language =:language");

foreach ($parts as $part) {

    // bind the parameters
    $stmt->bindValue(":partid", $part);
    $stmt->bindValue(":language", "en");

    if ($stmt->execute()) {
        if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {                
            $comment = $row['comment'];
            echo $comment . "<br>";        
        }
    }

}

$pdo = null;

有什么想法吗?

推荐答案

这两种数据类型实际上是相同的.呆滞还有其他许多可能的原因. (但是据我所知，没有人会说MEDIUMTEXT比VARHAR更糟糕.)

The two datatypes are handled virtually identically. There are many other possible reasons for sluggishness. (But none that I know of that would say MEDIUMTEXT is worse than VARHAR.)

所以...让我们看看是否可以加快网页速度...

So... Let's see if we can speed up the web page...

在MySQL调用周围放置microtime(true) －确保它是MySQL，而不是PHP. "0.019secs"很有意义； "1.5-2秒"听起来像是PHP中正在发生某些事情.

Put microtime(true) around the mysql calls -- to make sure it is MySQL, not PHP. "0.019secs" makes sense; "1.5 - 2 seconds" sounds like something is going on in PHP.

使用InnoDB，不 MyISAM. (尽管您有相反的说法.)

Use InnoDB, not MyISAM. (In spite of your claims to the contrary.)

正确调音；让我们看看SHOW VARIABLES LIKE '%buffer%';您有多少RAM? (交换性能很糟糕.)

Tune correctly; let's see SHOW VARIABLES LIKE '%buffer%'; How much RAM do you have? (Swapping is terrible for performance.)

您要返回多少行?在一个网页上放置几十个是不现实的，因此添加ORDER BY...LIMIT....

How many rows are you returning? It's not practical to have more than a few dozen on a web page, so add ORDER BY...LIMIT....

如果UI限制为1000个字符，请使用TEXT或VARCHAR(1000)，而不是MEDIUMTEXT.如果您尝试增加最多64K字节(可能 4K utf8mb4 个字符)，请使用TEXT.

If the UI limit is 1000 characters, use TEXT or VARCHAR(1000), not MEDIUMTEXT. If you are trying to raise up to 64K bytes (potentially 4K utf8mb4 characters), then use TEXT.

您确实需要这样做(列以任意顺序排列):

You do need this (with the columns in either order):

INDEX(part_id, language)

如果MyISAM表中有很多搅动"(删除和/或更新，然后进行更多插入)，则数据可能会碎片化，因此速度很慢. VARCHAR和TEXT都可能发生这种情况. InnoDB不会发生这种情况.

If there has been a lot of "churn" (deletes and/or updates followed by more inserts) in the MyISAM table, the data can be fragmented, hence slow. This can happen for both VARCHAR and TEXT. This does not happen with InnoDB.

这篇关于从varchar更改为中文本会导致性能下降的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！