备份一下
转载地址：
https://mp.weixin.qq.com/s/w1ln7-ln4MsIy05yDjdEgw
优化：数据库连接不要使用localhost，改成127.0.0.1
前言
中医讲对症下药，我们也需要定位到哪个位置消耗了多长时间，具体到每个位置去进行优化，这样我们就需要知道请求某个接口，每个流程所需要的时间，这样一般都是由日志来查看的。
本文从两点来说一下
一、缩短数据库操作耗时
这里以TP5框架为例，TP5开启日志的情况，会很详细的记录程序运行所使用的时间，来看下一个很常见的请求接口日志：
[运行时间：1.207150s] [吞吐率：0.83req/s] [内存消耗：2,881.48kb] [文件加载：68]
[ info ] [ LANG ] D:\webroot\www\thinkphp\lang\zh-cn.php
[ info ] [ ROUTE ] array (
'type' => 'module',
'module' =>
array (
0 => 'admin',
1 => 'Order',
2 => 'getConfirmOrder',
),
)
[ info ] [ HEADER ] array (
'content-type' => '',
'content-length' => '0',
'x-original-url' => '/admin/Order/getConfirmOrder',
'origin' => 'http://www.***.com',
'x-requested-with' => 'XMLHttpRequest',
'user-agent' => 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.84 Safari/537.36',
'referer' => 'http://www.***.com/admin/baby/index.html',
'host' => 'www.***.com',
'cookie' => 'security_session_verify=d66a8d5d0b34e8c6431d6bc554fbf24c; PHPSESSID=c2nph8nll013k9cu95l3a6d8h1',
'accept-language' => 'zh-CN,zh;q=0.9',
'accept-encoding' => 'gzip, deflate',
'accept' => 'application/json, text/javascript, */*; q=0.01',
'connection' => 'keep-alive',
)
[ info ] [ PARAM ] array (
)
[ info ] [ SESSION ] INIT array (
'prefix' => 'admin',
'type' => '',
'auto_start' => true,
)
[ info ] [ DB ] INIT mysql
[ info ] [ RUN ] app\admin\controller\Order->getConfirmOrder[ D:\webroot\www.***.com\application\admin\controller\Order.php ]
[ info ] [ LOG ] INIT File
[ sql ] [ DB ] CONNECT:[ UseTime:1.056759s ] mysql:host=localhost;port=3306;dbname=***;charset=utf8
[ sql ] [ SQL ] SHOW COLUMNS FROM `gmy_kanban` [ RunTime:0.011518s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` ORDER BY `kan_id` DESC [ RunTime:0.000676s ]
[ sql ] [ SQL ] SHOW COLUMNS FROM `gmy_auth` [ RunTime:0.009073s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_auth` [ RunTime:0.002334s ]
[ sql ] [ SQL ] SHOW COLUMNS FROM `gmy_admin` [ RunTime:0.010282s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_admin` WHERE `user_name` = 'admin99sj' LIMIT 1 [ RunTime:0.001304s ]
[ sql ] [ SQL ] SHOW COLUMNS FROM `gmy_role` [ RunTime:0.010645s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_role` WHERE `role_id` IN (91) LIMIT 1 [ RunTime:0.000684s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_role` WHERE `role_id` IN (196) LIMIT 1 [ RunTime:0.000588s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/yingxiao' LIMIT 1 [ RunTime:0.000677s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/xingzheng' LIMIT 1 [ RunTime:0.000469s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/kefu' LIMIT 1 [ RunTime:0.000447s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/yanguangshi' LIMIT 1 [ RunTime:0.000519s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/shichang' LIMIT 1 [ RunTime:0.000443s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/wangluo' LIMIT 1 [ RunTime:0.000463s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/table' LIMIT 1 [ RunTime:0.000506s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_auth` WHERE `auth_des` LIKE '%order/getconfirmorder%' AND ( fen_id<>9 ) LIMIT 1 [ RunTime:0.001173s ]
从运行时间来看1.2秒左右，1秒左右的时间对于人的正常感应来说已经能够感知到慢了，往下看，我们会看到具体的每个流程所使用的时间，首先是数据库连接时间：1.056759s，接下来是一些数据表查询所用的时间都很少，都在1毫秒左右，可以忽略不计。那么这里最主要的症结就是连接时间，为什么数据库连接要这么久呢？来看一下连接的参数：
mysql:host=localhost;port=3306;dbname=***;charset=utf8。
优化：数据库连接不要使用localhost，改成127.0.0.1
我们试下把数据库主机host改成127.0.0.1，再来测试下，看日志
[运行时间：0.115078s] [吞吐率：8.69req/s] [内存消耗：481.07kb] [文件加载：66]
[ info ] [ LANG ] D:\webroot\www.***.com\thinkphp\lang\zh-cn.php
[ info ] [ ROUTE ] array (
'type' => 'module',
'module' =>
array (
0 => 'admin',
1 => 'order',
2 => 'getConfirmOrder',
),
)
[ info ] [ HEADER ] array (
'content-type' => '',
'content-length' => '0',
'x-original-url' => '/admin/order/getConfirmOrder',
'sec-fetch-dest' => 'document',
'sec-fetch-user' => '?1',
'sec-fetch-mode' => 'navigate',
'sec-fetch-site' => 'none',
'upgrade-insecure-requests' => '1',
'sec-ch-ua-platform' => '"Android"',
'sec-ch-ua-mobile' => '?1',
'sec-ch-ua' => '" Not A;Brand";v="99", "Chromium";v="100", "Google Chrome";v="100"',
'user-agent' => 'Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Mobile Safari/537.36',
'host' => 'www.***.com',
'cookie' => 'security_session_verify=435345dacd7ada35e3d0ef60f48a169f; PHPSESSID=687tptp9lbd3aip2gph1uth450',
'accept-language' => 'zh-CN,zh;q=0.9',
'accept-encoding' => 'gzip, deflate, br',
'accept' => 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9',
'connection' => 'keep-alive',
'cache-control' => 'max-age=0',
)
[ info ] [ PARAM ] array (
)
[ info ] [ SESSION ] INIT array (
'prefix' => 'admin',
'type' => '',
'auto_start' => true,
)
[ info ] [ DB ] INIT mysql
[ info ] [ RUN ] app\admin\controller\Order->getConfirmOrder[ D:\webroot\www.***.com\application\admin\controller\Order.php ]
[ info ] [ LOG ] INIT File
[ sql ] [ DB ] CONNECT:[ UseTime:0.001759s ] mysql:host=127.0.0.1;port=3306;dbname=***;charset=utf8
[ sql ] [ SQL ] SHOW COLUMNS FROM `gmy_kanban` [ RunTime:0.014265s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` ORDER BY `kan_id` DESC [ RunTime:0.000765s ]
[ sql ] [ SQL ] SHOW COLUMNS FROM `gmy_auth` [ RunTime:0.009900s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_auth` [ RunTime:0.001970s ]
[ sql ] [ SQL ] SHOW COLUMNS FROM `gmy_admin` [ RunTime:0.012493s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_admin` WHERE `user_name` = 'admin99sj' LIMIT 1 [ RunTime:0.000990s ]
[ sql ] [ SQL ] SHOW COLUMNS FROM `gmy_role` [ RunTime:0.008893s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_role` WHERE `role_id` IN (91) LIMIT 1 [ RunTime:0.000611s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_role` WHERE `role_id` IN (196) LIMIT 1 [ RunTime:0.000652s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/yingxiao' LIMIT 1 [ RunTime:0.000524s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/xingzheng' LIMIT 1 [ RunTime:0.000387s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/kefu' LIMIT 1 [ RunTime:0.000376s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/yanguangshi' LIMIT 1 [ RunTime:0.000338s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/shichang' LIMIT 1 [ RunTime:0.000346s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/wangluo' LIMIT 1 [ RunTime:0.000362s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_kanban` WHERE `auth_des` = 'index/table' LIMIT 1 [ RunTime:0.000337s ]
[ sql ] [ SQL ] SELECT * FROM `gmy_auth` WHERE `auth_des` LIKE '%order/getconfirmorder%' AND ( fen_id<>9 ) LIMIT 1 [ RunTime:0.000816s ]
再看运行时间只有0.11秒，大约快了10倍，我们看下具体哪里快了，看连接数据库的时间0.001759s，这比改之前快了几百倍！
二、php性能优化
接下来我们来看下php性能的优化，Opcache和JIT，JIT在我的实际项目中体验是，开启JIT可以大略缩短100ms的时间，也就是0.1秒，现在php8都说性能很好，但是如果你不开启JIT，那么性能是体会不到的。
我们来看下，php5.6下如何开启Opcache，php5.6版本默认是安装过Opcache扩展的，在ext下找到php_opcache.dll，php8是需要自己手动下载安装这个扩展的，具体方法需要你自己去百度，接下来就是php.ini配置了，公众号里前面文章有讲过，感兴趣的可以去看看，这里不在多说，只是把opcache跑起来，配置如下：
zend_extension = "D:\webroot\zzidcconf\php\php5.6\ext\php_opcache.dll"
; Determines if Zend OPCache is enabled
opcache.enable=1
; Determines if Zend OPCache is enabled for the CLI version of PHP
opcache.enable_cli=1
; The OPcache shared memory storage size.
opcache.memory_consumption=128
; The amount of memory for interned strings in Mbytes.
opcache.interned_strings_buffer=8
; The maximum number of keys (scripts) in the OPcache hash table.
; Only numbers between 200 and 100000 are allowed.
opcache.max_accelerated_files=2000
把上边的配置项该开启的开启，该添加的添加，有的需要修改的修改下，然后重启服务器web环境，测试phpinfo，如果看到下图，则代表opcache启动成功。
image.png
总结
本篇文章从两个方面谈了下如何提升接口响应速度，一个是数据库时间，一个是php自身的运行时间，不是特别复杂的点，如果你碰到了同样的情况，可以立马使用改善。最后，告诫自己，写代码的时候，一定要尽可能的一遍成，不要想着后期再来完善。一起加油！
网友评论