Mysql+php的几十万数据的datatables服务端程序，翻页就会出错。

几十万数据的datatables服务端程序，用的是mysql（MySQL8.0.12）+php搭建的，使用的是官网的标准例子（ https://datatables.net/examples/server_side/simple.html ），数据量小时都挺好的（翻页、搜索都很顺），但是数据量达到几十万之后，执行翻到最后一页时就会报错，500错误，搜索也是报错，没办法进行任何搜索。
错误提示如下图所示。

img

所指向的官网报错页面 http://datatables.net/tn/7 也没看懂是啥问题～

以下是服务器日志和php日志
而且php日志里面有乱码，也不知道是什么意思～

Apache2.4.39       error.log

[Sun Oct 24 07:16:37.935400 2021] [core:warn] [pid 16588:tid 588] AH00098: pid file E:/phpstudy_pro/Extensions/Apache2.4.39/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run?
[Sun Oct 24 07:16:37.940386 2021] [mpm_winnt:notice] [pid 16588:tid 588] AH00455: Apache/2.4.39 (Win64) OpenSSL/1.1.1b mod_fcgid/2.3.9a mod_log_rotate/1.02 configured -- resuming normal operations
[Sun Oct 24 07:16:37.940386 2021] [mpm_winnt:notice] [pid 16588:tid 588] AH00456: Server built: Mar 27 2019 11:06:20
[Sun Oct 24 07:16:37.940386 2021] [core:notice] [pid 16588:tid 588] AH00094: Command line: 'E:\\phpstudy_pro\\COM\\..\\Extensions\\Apache2.4.39\\\\bin\\httpd.exe -d E:/phpstudy_pro/Extensions/Apache2.4.39'
[Sun Oct 24 07:16:37.968311 2021] [mpm_winnt:notice] [pid 16588:tid 588] AH00418: Parent: Created child process 21240
[Sun Oct 24 07:16:39.724613 2021] [mpm_winnt:notice] [pid 21240:tid 804] AH00354: Child: Starting 1024 worker threads.
[Sun Oct 24 07:23:44.803111 2021] [core:warn] [pid 12608:tid 696] AH00098: pid file E:/phpstudy_pro/Extensions/Apache2.4.39/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run?
[Sun Oct 24 07:23:44.809095 2021] [mpm_winnt:notice] [pid 12608:tid 696] AH00455: Apache/2.4.39 (Win64) OpenSSL/1.1.1b mod_fcgid/2.3.9a mod_log_rotate/1.02 configured -- resuming normal operations
[Sun Oct 24 07:23:44.809095 2021] [mpm_winnt:notice] [pid 12608:tid 696] AH00456: Server built: Mar 27 2019 11:06:20
[Sun Oct 24 07:23:44.809095 2021] [core:notice] [pid 12608:tid 696] AH00094: Command line: 'E:\\phpstudy_pro\\COM\\..\\Extensions\\Apache2.4.39\\\\bin\\httpd.exe -d E:/phpstudy_pro/Extensions/Apache2.4.39'
[Sun Oct 24 07:23:44.815079 2021] [mpm_winnt:notice] [pid 12608:tid 696] AH00418: Parent: Created child process 11632
[Sun Oct 24 07:23:46.473642 2021] [mpm_winnt:notice] [pid 11632:tid 668] AH00354: Child: Starting 1024 worker threads.

php7.3.4nts      php_errors.log

[23-Oct-2021 23:24:52 UTC] PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_firebird' (tried: E:\phpstudy_pro\Extensions\php\php7.3.4nts\ext\pdo_firebird (�Ҳ���ָ����ģ�顣), E:\phpstudy_pro\Extensions\php\php7.3.4nts\ext\php_pdo_firebird.dll (�Ҳ���ָ����ģ�顣)) in Unknown on line 0
[23-Oct-2021 23:24:52 UTC] PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_oci' (tried: E:\phpstudy_pro\Extensions\php\php7.3.4nts\ext\pdo_oci (�Ҳ���ָ����ģ�顣), E:\phpstudy_pro\Extensions\php\php7.3.4nts\ext\php_pdo_oci.dll (�Ҳ���ָ����ģ�顣)) in Unknown on line 0
[23-Oct-2021 23:30:50 UTC] PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_firebird' (tried: E:\phpstudy_pro\Extensions\php\php7.3.4nts\ext\pdo_firebird (�Ҳ���ָ����ģ�顣), E:\phpstudy_pro\Extensions\php\php7.3.4nts\ext\php_pdo_firebird.dll (�Ҳ���ָ����ģ�顣)) in Unknown on line 0
[23-Oct-2021 23:30:50 UTC] PHP Warning:  PHP Startup: Unable to load dynamic library 'pdo_oci' (tried: E:\phpstudy_pro\Extensions\php\php7.3.4nts\ext\pdo_oci (�Ҳ���ָ����ģ�顣), E:\phpstudy_pro\Extensions\php\php7.3.4nts\ext\php_pdo_oci.dll (�Ҳ���ָ����ģ�顣)) in Unknown on line 0

而且，程序运行时，一页一页的翻页都还行，但是会越来越慢，当点击最后一页时，就会报错。而且也无法进行任何搜索。实在是不知道哪里出问题了！

SELECT `Id`, `Name`, `Age`, `DeparmentS`, `Email`, FROM `data_content_110` ORDER BY `Id` ASC LIMIT 0, 40

中断调试后发现，目前的sql语句中已经包含了LIMIT 0, 40参数了啊

翻页的sql语句我猜你是使用limit来做的，这样会出现慢查询的情况，建议将limit换成id < 10000 and 2000>id 的条件语句来做。这样速度会快很多。

这篇文章看一下是否可帮到您：

下载对应版本的Firebird库

img


解压之后有一个fbclient.dll文件:

img


放到PHP根目录(php.ini所在目录)下就可以了.

用explain 分析一下是不是sql的问题

查看一下mysql的日志，看看有没有错误或者非常慢的慢语句查询导致的
SELECT Id, Name, Age, DeparmentS, Email, FROM data_content_110 ORDER BY Id ASC LIMIT 0, 40 单看这个语句是没问题的，你要综合来看，所以要看相关的错误日志来排除问题