sql - 帮助优化 MySQL 表(约 500,000 条记录)和 PHP 代码

Question

我有一个 MySQL 表，用于从各种游戏服务器(Urban Terror)收集玩家数据。收集数据的机器人 24/7 运行，目前该表约有 475,000 多条记录。因此，从 PHP 查询该表变得相当慢。我想知道我可以在数据库方面做些什么来使其尽可能优化，然后我可以专注于查询数据库的应用程序。表格如下:

CREATE TABLE IF NOT EXISTS `people` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `ip` int(4) unsigned NOT NULL,
  `guid` varchar(32) NOT NULL,
  `server` int(4) unsigned NOT NULL,
  `date` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Person` (`name`,`ip`,`guid`),
  KEY `server` (`server`),
  KEY `date` (`date`),
  KEY `PlayerName` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='People that Play on Servers' AUTO_INCREMENT=475843 ;

我将IPv4(ip和服务器)描述为4字节整数，并使用MySQL函数NTOA()等进行编码和解码，我听说这种方式比varchar(15)更快。

guid 是 md5sum、32 个字符的十六进制值。日期存储为 unix 时间戳。

我在名称、IP 和 GUID 上有一个唯一的键，以避免同一玩家重复。

我的 key 设置正确吗？我存储数据的方式有效吗？

这是查询该表的代码。您搜索名称、ip 或 guid，它会抓取查询结果并交叉引用与第一个查询结果中的名称、ip 或 guid 匹配的其他记录，并对每个字段执行此操作。这有点难以解释。但基本上，如果我按名字搜索一名玩家，我会看到他使用过的所有其他名称、他使用过的每个 IP 以及他使用过的每个 GUID。

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Search: <input type="text" name="query" id="query" /><input type="submit" name="btnSubmit" value="Submit" />
</form>

<?php if (!empty($_POST['query'])) { ?>

<table cellspacing="1" id="1up_people" class="tablesorter" width="300">
<thead>
<tr>
    <th>ID</th>
    <th>Player Name</th>
    <th>Player IP</th>
    <th>Player GUID</th>
    <th>Server</th>
    <th>Date</th>
</tr>
</thead>
<tbody>
<?php

function super_unique($array)
{
  $result = array_map("unserialize", array_unique(array_map("serialize", $array)));

  foreach ($result as $key => $value)
  {
    if ( is_array($value) )
    {
      $result[$key] = super_unique($value);
    }
  }

  return $result;
}

    if (!empty($_POST['query'])) {
        $query = trim($_POST['query']);
        $count = 0;
        $people = array();
        $link = mysql_connect('localhost', 'mysqluser', 'yea right!');
                if (!$link) {
                        die('Could not connect: ' . mysql_error());
                }
                mysql_select_db("1up");
                $sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (name LIKE \"%$query%\" OR INET_NTOA(ip) LIKE \"%$query%\" OR guid LIKE \"%$query%\")";
        $result = mysql_query($sql, $link);
        if (!$result) {
            die(mysql_error());
        }
        // Now take the initial results and parse each column into its own array
        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
            $name = htmlspecialchars($row[1]);
            $people[] = array(
                'id' => $row[0],
                'name' => $name,
                'ip' => $row[2],
                'guid' => $row[3],
                'server' => $row[4],
                'date' => $row[5]
            );
        }
        // now for each name, ip, guid in results, find additonal records
        $people2 = array();
        foreach ($people AS $person) {
            $ip = $person['ip'];
            $sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (ip = \"$ip\")";
            $result = mysql_query($sql, $link);
            while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                $name = htmlspecialchars($row[1]);
                $people2[] = array(
                    'id' => $row[0],
                    'name' => $name,
                    'ip' => $row[2],
                    'guid' => $row[3],
                    'server' => $row[4],
                    'date' => $row[5]
                );
            }
        }

                $people3 = array();
                foreach ($people AS $person) {
                        $guid = $person['guid'];
                        $sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (guid = \"$guid\")";
                        $result = mysql_query($sql, $link);
                        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                                $name = htmlspecialchars($row[1]);
                                $people3[] = array(
                                        'id' => $row[0],
                                        'name' => $name,
                                        'ip' => $row[2],
                    'guid' => $row[3],
                    'server' => $row[4],
                    'date' => $row[5]
                                );
                        }
                }


                $people4 = array();
                foreach ($people AS $person) {
                        $name = $person['name'];
                        $sql = "SELECT id, name, INET_NTOA(ip) AS ip, guid, INET_NTOA(server) AS server, date FROM 1up_people WHERE (name = \"$name\")";
                        $result = mysql_query($sql, $link);
                        while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                                $name = htmlspecialchars($row[1]);
                                $people4[] = array(
                                        'id' => $row[0],
                                        'name' => $name,
                                        'ip' => $row[2],
                    'guid' => $row[3],
                    'server' => $row[4],
                    'date' => $row[5]
                                );
                        }
                }


        // Combine people and people2 into just people
        $people = array_merge($people, $people2);
        $people = array_merge($people, $people3);
        $people = array_merge($people, $people4);

        $people = super_unique($people);        

        foreach ($people AS $person) {
            $date = ($person['date']) ? date("M d, Y", $person['date']) : 'Before 8/1/10';
            echo "<tr>\n";
            echo "<td>".$person['id']."</td>";
            echo "<td>".$person['name']."</td>";
            echo "<td>".$person['ip']."</td>";
            echo "<td>".$person['guid']."</td>";
            echo "<td>".$person['server']."</td>";
            echo "<td>".$date."</td>";
            echo "</tr>\n";
            $count++;
        }
        // Find Total Records
        //$result = mysql_query("SELECT id FROM 1up_people", $link);
        //$total  = mysql_num_rows($result);
        mysql_close($link);
    }
?>
</tbody>
</table>
<p>
<?php 
    echo $count." Records Found for \"".$_POST['query']."\" out of $total";
?>
</p>

<?php 
} 
$time_stop = microtime(true);
print("Done (ran for ".round($time_stop-$time_start)." seconds).");

?>

感谢任何帮助!

谢谢。

Answer 1

SELECT id,
       name,
       Inet_ntoa(ip)     AS ip,
       guid,
       Inet_ntoa(server) AS server,
       DATE
FROM   1up_people
WHERE  ( name LIKE "%$query%"
          OR Inet_ntoa(ip) LIKE "%$query%"
          OR guid LIKE "%$query%" ) 

上述查询的一些问题:

1. 该查询在 where 子句中使用 3 个字段，并在每个字段上使用 OR 条件。 MySQL 一次查询只能使用一个索引。因此，它必须为此查询选择名称或 ip 或 guid 上的索引。即使有复合索引(name、ip、guid)，在这种情况下也不能使用它，因为条件是 OR 运算的。执行此类查询的更好方法是使用 UNION。例如。

    SELECT <fields> FROM table1 WHERE field1='val1' /*will use index on field1*/
    UNION
    SELECT <fields> FROM table1 WHERE field2='val2' /*will use index on field2*/
    ...
    SELECT <fields> FROM table1 WHERE fieldn='valn' /*will use index on fieldn*/.
   

   在上面的查询中，您分别对每个字段进行选择，然后对其进行 UNION。这允许使用每个字段上的索引，从而提高查询效率。它的缺点是，如果同一行匹配多个条件，则会得到重复的结果。为了避免这种情况，您可以使用 UNION DISTINCT 而不是 UNION，但会更昂贵，因为 mysql 必须对输出进行重复数据删除。为了使该建议发挥作用，还需要解决下面讨论的问题。 (guid上没有索引，需要建立)。

2. 条件使用 LIKE '%query%' 作为名称和 guid，即开头的通配符 (%)。这意味着即使索引存在也无法使用。当在字符串末尾使用 = 或 % 作为“query%”时，可以使用索引。当 % 用于字符串开头时，将不会使用索引。 (引用:http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html)。一种可能的解决方法是最后仅使用通配符或对这些字段使用全文索引。

3. ip 条件为 INET_NTOA(ip) LIKE "%query%"。当在字段上使用函数时，无法使用该字段上的任何索引。 MySQL 目前不支持函数式索引。如果需要支持这样的查询，您可能还必须将此字段存储为 varchar 并将其视为类似于名称和 guid。

由于上述问题，查询将始终进行全表扫描并且不会使用任何索引。使用 UNION(如 1 中建议的)不会提供任何改进 2 和 3 没有修复，事实上它可能会损害性能，因为它可能会执行 3 次表扫描而不是 1 次。您可以尝试在(name,guid,ip_string) 并执行查询 MATCH(name, guid, ip_string) AGAINST ("$query")

从代码中我发现，在从上述查询中获取结果后，后续查询将根据该查询的结果触发。我不确定是否需要这样做，因为我认为它不会找到任何新记录。当您搜索 f LIKE "%q%" 并使用结果进行诸如 f='r1' 之类的搜索时，LIKE 条件应该已经捕获了所有出现的 'r1 ' 并且后续查询将仅返回重复的结果。在我看来，可以跳过额外的查询，但可能我遗漏了一些东西。

顺便说一句，不要将 SQL 语句中的查询字符串插入为 name LIKE "%$query%"。这是不安全的，可用于 SQL 注入(inject)攻击。使用带有绑定(bind)变量的准备好的语句。