时间戳

首页 > 解决方案 > 文件排序而不是索引扫描

php - 文件排序而不是索引扫描

问题描述

我将我的 PHP/MySQL 数据库从 Unix 迁移到 Windows。我转储了数据库并将其直接导入到 Windows 上，没有任何区别。我使用相同的脚本/PHP 版本。

我的表是：

CREATE TABLE `pximg` (
  `ppoc` tinyint unsigned NOT NULL,
  `file` int unsigned NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `img` mediumblob,
  PRIMARY KEY (`ppoc`,`file`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

我要重现的 PHP 脚本：

// SQL queries
$queryNoBindings = "EXPLAIN SELECT img, ts FROM pximg WHERE ppoc = 0 ORDER BY `file` DESC LIMIT 1";
$queryBindings   = "EXPLAIN SELECT img, ts FROM pximg WHERE ppoc = ? ORDER BY `file` DESC LIMIT 1";
$ppoc = 0;

// connect
$m = new mysqli('127.0.0.1', $dbData['px'][1], $dbData['px'][2], $dbData['px'][3], $dbData['px'][4]);

// query with no bindings
$q = $m->query($queryNoBindings);
$r1 = $q->fetch_array(MYSQLI_ASSOC);
print_r($r1); 

// query with bindings
$stmt = $m->prepare($queryBindings);
$stmt->bind_param('i', $ppoc);                
$stmt->execute();
$r = $stmt->get_result();
$r2 = $r->fetch_array(MYSQLI_ASSOC);
print_r($r2);

Unix 上的结果是

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => pximg
    [partitions] => 
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 1
    [ref] => const
    [rows] => 385758
    [filtered] => 100.00
    [Extra] => Backward index scan
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => pximg
    [partitions] => 
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 1
    [ref] => const
    [rows] => 385758
    [filtered] => 100
    [Extra] => Backward index scan
)

Windows 上的结果是

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => pximg
    [partitions] => 
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 1
    [ref] => const
    [rows] => 370682
    [filtered] => 100.00
    [Extra] => Backward index scan
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => pximg
    [partitions] => 
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 1
    [ref] => const
    [rows] => 370682
    [filtered] => 100
    [Extra] => Using filesort
)

所以不同之处在于：当我在 Windows 上使用带有绑定的查询时，它现在使用文件排序而不是向后索引扫描，并且需要大约 20 秒而不是 0.02 秒。

有谁知道为什么？或者，如果文件排序没问题，为什么现在查询速度如此之慢？

标签： phpmysqlwindowsmysqliquery-performance

解决方案

看起来您新加载的服务器没有使用您的主键。

以下是一些可以尝试的事情：

  1. SHOW TABLE piximg;在您的新服务器上显示您的批量加载表的定义。主键定义是否进入您的新表？如果没有，请执行此操作。

     ALTER TABLE piximg ADD PRIMARY KEY(`ppoc`, `file`);

  2. ALTER TABLE pximg ENABLE KEYS;如果您的批量加载操作禁用了密钥处理。

  3. OPTIMIZE TABLE pximg;因为有时新批量加载的表具有在优化之前没有意义的索引统计信息。

  4. 对于这个查询的具体情况，稍微改变你的索引定义，在列上包含DESC限定符。file

     PRIMARY KEY (`ppoc`, `file` DESC)

  5. 该模式SELECT blah, blah, blob ... ORDER BY blah DESC LIMIT 1是一种臭名昭著的性能反模式。你可能想试试这个。

SELECT a.img, a.ts 
  FROM pximg a
  JOIN (
              SELECT ppoc, MAX(file) file
                FROM piximg
               GROUP BY ppoc
       ) b ON a.ppoc = b.ppoc AND a.file = b.file
 WHERE ppoc = <<<whatever>>>

这将避免使 MySQL 服务器 slurp 并对其中包含大胖 Blob 的大量行进行排序，只丢弃除一个之外的所有行。

推荐阅读