AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL运维实战 之 PHP访问MySQL你使用对了吗

2019-03-06 2307
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 大家都知道，slow query系统做的好不好，直接决定了解决slow query的效率问题 一个数据库管理平台，拥有一个好的slow query系统，基本上就拥有了解锁性能问题的钥匙 但是今天主要分享的并不是平台，而是在平台中看到的奇葩指数五颗星的slow issue 好了，关子卖完了，直接进入.

大家都知道，slow query系统做的好不好，直接决定了解决slow query的效率问题

一个数据库管理平台，拥有一个好的slow query系统，基本上就拥有了解锁性能问题的钥匙

但是今天主要分享的并不是平台，而是在平台中看到的奇葩指数五颗星的slow issue

好了，关子卖完了，直接进入正题

一、症状

一堆如下慢查询

# User@Host: cra[cra] @  [xx]  Id: 3352884621
# Query_time: 0.183673  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use xx_db;
SET timestamp=1549900927;
# administrator command: Prepare;

# Time: 2019-02-12T00:02:07.516803+08:00
# User@Host: cra[cra] @  [xx]  Id: 3351119968
# Query_time: 0.294081  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1549900927;
# administrator command: Prepare;

从我们的监控图上可以看到，每天不定时间段的slow query 总数在攀升，但是却看不到任何query 语句

这是我接触到的slow query优化案例中从来没有过的情况，比较好奇，也比较兴奋，至此决心要好好看看这个问题

二、排查

要解决这个问题，首先想到的是，如何复现这个问题，如何模拟复现这个症状

  • MySQL客户端 模拟prepare
* 模拟
root:xx> prepare stmt1 from 'select * from xx_operation_log where id = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

* 结果

# Time: 2019-02-14T14:14:50.937462+08:00
# User@Host: root[root] @ localhost []  Id:   369
# Query_time: 0.000105  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1550124890;
prepare stmt1 from 'select * from xx_operation_log where id = ?';

结论是： MySQL client 模拟出来的prepare 并不是我们期待的，并没有得到我们想要的 administrator command: Prepare

  • perl 模拟prepare
#!/usr/bin/perl

use DBI;

my $dsn = "dbi:mysql:database=${db_name};hostname=${db_host};port=${db_port}";#数据源

#获取数据库句柄
my $dbh = DBI->connect("DBI:mysql:database=xx;host=xx", "xx", "xx", {'RaiseError' => 1});

my $sql = qq{select * from xx_operation_log where id in (?)};

my $sth = $dbh->prepare($sql);
$sth->bind_param (1, '100');

sleep 3;
$sth->execute();

结论是：跟MySQL客户端一样，同样是看不到administrator command: Prepare

  • php 模拟prepare
1. 官方网址：

https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-stmt.prepare.html


<?php
$link = mysqli_connect("xx", "dba", "xx", "xx_db");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}


$city = '1';


/* create a prepared statement */
$stmt = mysqli_stmt_init($link);

if (mysqli_stmt_prepare($stmt, 'select * from xx_operation_log where id in (1,2,3)'){

    /* bind parameters for markers */
    /* mysqli_stmt_bind_param($stmt, "s", $city);

    /* execute query */
    mysqli_stmt_execute($stmt);

    /* bind result variables */
    mysqli_stmt_bind_result($stmt, $district);

    /* fetch value */
    mysqli_stmt_fetch($stmt);

    printf("%s is in district %s\n", $city, $district);

    /* close statement */
    mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($link);
?>
  • php模拟得到的slow 结果
[root@xx 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4  | grep 'User@Host' | grep 'xx_rx' | wc -l
7891

[root@xx 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4  | grep 'User@Host'  | wc -l
7908

结论： 通过php代码，我们成功模拟出了想要的结果

那顺藤摸瓜，抓取下这段时间有相同session id的整个sql执行过程

MySQL开启slow=0的抓包模式

可以定位到同一个session id（3415357118） 的 prepare + execute + close stmt


# User@Host: xx_rx[xx_rx] @  [xx.xxx.xxx.132]  Id: 3415357118
# Query_time: 0.401453  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use xx_db;
SET timestamp=1550017125;
# administrator command: Prepare;
# Time: 2019-02-13T08:18:45.624671+08:00
--
# User@Host: xx_rx[xx_rx] @  [xx.xxx.xxx.132]  Id: 3415357118
# Query_time: 0.001650  Lock_time: 0.000102 Rows_sent: 0  Rows_examined: 1
use xx_db;
SET timestamp=1550017125;
update `xx` set `updated_at` = '2019-02-13 08:18:45', `has_sales_office_phone` = 1, `has_presale_permit` = 1 where `id` = 28886;
# Time: 2019-02-13T08:18:45.626138+08:00
--
# User@Host: xx_rx[xx_rx] @  [xx.xxx.xxx.132]  Id: 3415357118
# Query_time: 0.000029  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 1
use xx_db;
SET timestamp=1550017125;
# administrator command: Close stmt;
# Time: 2019-02-13T08:18:45.626430+08:00

结论：我们发现，prepare时间的确很长，但是sql语句却执行的很快，这就很尴尬了

本来是想通过抓包，看看是否能够验证我们的猜想： prepare的语句非常大，或者条件非常复杂，从而导致prepare在服务器端很慢
结果发现query语句也都非常简单

那么既然如此，我们就找了业务方，将对应业务的prepare方法一起看看
结果发现，业务使用的是php-pdo的方式，所以我们就又有了如下发现

php-pdo 两种prepare模式



1. 本地prepare  $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);
    不会发送给MySQL Server

2. 服务器端prepare  $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
    发送给MySQL Server

验证两种prepare模式

  • 服务端prepare模式( ATTR_EMULATE_PREPARES = false)
<?php
$dbms='mysql';     //数据库类型
$host='xxx'; //数据库主机名
$dbName='test';    //使用的数据库
$user='xx';      //数据库连接用户名
$pass='123456';          //对应的密码
$dsn="$dbms:host=$host;dbname=$dbName";


try {
    $pdo = new PDO($dsn, $user, $pass); //初始化一个PDO对象
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
    echo "----- prepare begin  -----\n";
    $stmt = $pdo->prepare("select * from test.chanpin where id = ?");
    echo "----- prepare after -----\n";
    $stmt->execute([333333]);
    echo "----- execute after -----\n";
    $rs = $stmt->fetchAll();
} catch (PDOException $e) {
    die ("Error!: " . $e->getMessage() . "<br/>");
}

strace -s200 -f php mysql1.php 跟踪

pdo_prepare_remote_new

大家可以看到这个模式下，prepare的时候，是将query+占位符 发送给服务端的

  • 本地prepare模式 (ATTR_EMULATE_PREPARES = true )
<?php
$dbms='mysql';     //数据库类型
$host='xx'; //数据库主机名
$dbName='test';    //使用的数据库
$user='xx';      //数据库连接用户名
$pass='123456';          //对应的密码
$dsn="$dbms:host=$host;dbname=$dbName";


try {
    $pdo = new PDO($dsn, $user, $pass); //初始化一个PDO对象
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);
    echo "----- prepare begin  -----\n";
    $stmt = $pdo->prepare("select * from test.chanpin where id = ?");
    echo "----- prepare after -----\n";
    $stmt->execute([333333]);
    echo "----- execute after -----\n";
    $rs = $stmt->fetchAll();
} catch (PDOException $e) {
    die ("Error!: " . $e->getMessage() . "<br/>");
}

strace -s200 -f php mysql1.php 跟踪

pdo_prepare_local_new

大家可以看到这个模式下，prepare的时候，是不会将query发送给服务端的，只有execute的时候才会发送

跟业务方确认后，他们使用的是后者，也就是修改了默认值，他们原本是想提升数据库的性能，因为预处理后只需要传参数就好了
但是对于我们的业务场景并不适合，我们的场景是频繁打开关闭连接，也就是预处理基本就用不到

另外文档上面也明确指出prepared statements 性能会不好

pdo_prepare_bad_perfomance

调整和验证

如何验证业务方是否将prepare修改为local了呢？

dba:(none)> show global status like 'Com_stmt_prepare';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| Com_stmt_prepare | 716836596 |
+------------------+-----------+
1 row in set (0.00 sec)

通过观察，发现这个值没有变化，说明调整已经生效

总结

  • prepare的优点
1. 防止SQL注入
2. 特定场景下提升性能
    什么是特定场景： 就是先去服务端用占位符占位，后面可以直接发送请求来填空（参数值）
    这样理论上来说， 你填空的次数非常多，性能才能发挥出来
  • prepare的缺点
1. 在服务器端的prepare毕竟有消耗，当并发量大，频繁prepare的时候，就会有性能问题

2. 服务端的prepare模式还会带来的另外一个问题就是，排错和slow 优化有困难，因为大部分情况下是看不到真实query的

3. 尽量设置php-pdo为  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true) ，在本地prepare，不要给服务器造成额外压力
  • 建议
1. 默认情况下，应该使用php-pdo的默认配置，采用本地prepare的方式，这样可以做到防SQL注入的效果，性能差不到哪里去

2. 除非真的是有上述说的特定场景，可以考虑配置成服务器prepare模式，前提是要做好测试
文章标签：
云数据库 RDS MySQL 版
关系型数据库
MySQL
PHP
Linux
数据库
关键词：
PHP mysql
PHP实战
运维实战
云数据库 RDS MySQL 版实战
运维mysql
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
兰春
目录
相关文章
萝卜带泥
|
29天前
|
前端开发 关系型数据库 MySQL
PHP与MySQL动态网站开发实战指南####
【10月更文挑战第21天】 本文将深入浅出地探讨如何使用PHP与MySQL构建一个动态网站，从环境搭建到项目部署，全程实战演示。无论你是编程新手还是希望巩固Web开发技能的老手，都能在这篇文章中找到实用的技巧和启发。我们将一起探索如何通过PHP处理用户请求，利用MySQL存储数据，并最终呈现动态内容给用户，打造属于自己的在线平台。 ####
萝卜带泥
38 0
游客762btuqu5wybw666
|
20天前
|
存储 关系型数据库 MySQL
PHP与MySQL动态网站开发：从基础到实践####
本文将深入探讨PHP与MySQL的结合使用，展示如何构建一个动态网站。通过一系列实例和代码片段，我们将逐步了解数据库连接、数据操作、用户输入处理及安全防护等关键技术点。无论您是初学者还是有经验的开发者，都能从中获益匪浅。 ####
游客762btuqu5wybw666
33 6
游客762btuqu5wybw666
|
23天前
|
安全 关系型数据库 MySQL
PHP与MySQL动态网站开发实战指南####
——深入探索LAMP栈下的高效数据交互与处理技巧 ####
游客762btuqu5wybw666
29 9
蓝易云
|
21天前
|
关系型数据库 MySQL PHP
php实现一个简单的MySQL分页
通过本文的详细步骤和代码示例，我们实现了一个简单的PHP MySQL分页功能。主要步骤包括计算总记录数、设置分页参数、查询当前页的数据以及生成分页链接。这种分页方式适用于大多数Web应用，能够有效提升用户体验和页面响应速度。
蓝易云
24 4
aliyun0641063962-48771
|
26天前
|
关系型数据库 MySQL PHP
PHP与MySQL动态网站开发实战指南####
深入探索PHP与MySQL的协同工作机制，本文旨在通过一系列实战案例，揭示构建高效、稳定且用户友好的动态网站的秘诀。从环境搭建到数据交互，再到最佳实践分享，本文为开发者提供了一条清晰的学习路径，助力其在LAMP（Linux, Apache, MySQL, PHP/Perl/Python）栈上实现技术飞跃。 ####
aliyun0641063962-48771
50 7
叫个什么名字
|
25天前
|
关系型数据库 MySQL PHP
PHP与MySQL的无缝集成：构建动态网站的艺术####
本文将深入探讨PHP与MySQL如何携手合作，为开发者提供一套强大的工具集，以构建高效、动态且用户友好的网站。不同于传统的摘要概述，本文将以一个生动的案例引入，逐步揭示两者结合的魅力所在，最终展示如何通过简单几步实现数据驱动的Web应用开发。 ####
叫个什么名字
30 6
游客5fdji2pvmf8888
|
23天前
|
SQL 关系型数据库 MySQL
PHP与MySQL的高效协同开发策略####
本文深入探讨了PHP与MySQL在Web开发中的协同工作机制，通过优化配置、最佳实践和高级技巧，展示了如何提升数据库交互性能，确保数据安全，并促进代码可维护性。我们将从环境搭建讲起，逐步深入到查询优化、事务管理、安全防护及性能调优等核心环节，为开发者提供一套实战驱动的解决方案框架。 ####
游客5fdji2pvmf8888
21 3
游客5fdji2pvmf8888
|
26天前
|
SQL 关系型数据库 MySQL
PHP与MySQL动态网站开发深度探索####
本文旨在为读者提供一份详尽的指南，深入剖析PHP与MySQL在动态网站开发中的应用。通过具体实例与代码解析，揭示如何高效结合这两种技术构建功能强大、响应迅速的Web应用。文章将逐一探讨PHP的基础语法、MySQL数据库管理以及二者交互的关键技巧，旨在帮助开发者提升技能，优化项目开发流程。 ####
游客5fdji2pvmf8888
31 6
mg的嘟嘟
|
23天前
|
编译器 PHP 开发者
PHP 8新特性解析与实战应用####
随着PHP 8的发布，这一经典编程语言迎来了诸多令人瞩目的新特性和性能优化。本文将深入探讨PHP 8中的几个关键新功能，包括命名参数、JIT编译器、新的字符串处理函数以及错误处理改进等。通过实际代码示例，展示如何在现有项目中有效利用这些新特性来提升代码的可读性、维护性和执行效率。无论你是PHP新手还是经验丰富的开发者，本文都将为你提供实用的技术洞察和最佳实践指导。 ####
mg的嘟嘟
27 1
xibeijing
|
16天前
|
SQL 关系型数据库 MySQL
PHP与MySQL的高效交互：从基础到实践####
本文深入探讨了PHP与MySQL数据库之间的高效交互技术，涵盖了从基础连接到高级查询优化的全过程。不同于传统的摘要概述，这里我们直接以一段精简代码示例作为引子，展示如何在PHP中实现与MySQL的快速连接与简单查询，随后文章将围绕这一核心，逐步展开详细讲解，旨在为读者提供一个从入门到精通的实战指南。 ```php <?php // 数据库配置信息 $servername = "localhost"; $username = "root"; $password = "password"; $dbname = "test_db"; // 创建连接 $conn = new mysqli($se
xibeijing
22 0

热门文章

最新文章

  • 1
    RDS入门——Excel文件转存到RDS数据库实践
  • 2
    PostgreSQL SQL HINT的使用
  • 3
    如何在openGauss/PostgreSQL手动清理XLOG/WAL 文件?
  • 4
    MySql进阶使用之视图、索引（详解）
  • 5
    Reactive Spring实战——响应式MySql交互
  • 6
    Linux命令：MySQL系列之四--MySQL管理创建CREATE表和索引
  • 7
    mysql--索引 （查询）
  • 8
    Mysql5.7使用DTS增量同步数据到MaxCompute
  • 9
    mysql索引优化
  • 10
    Linux下MySQL的基础（一）
  • 1
    深入理解PHP7中的返回类型声明
    49
  • 2
    深入理解PHP的命名空间与自动加载机制
    62
  • 3
    PHP 7.4中新增特性的探索与实践
    48
  • 4
    深入理解PHP7的返回类型声明
    60
  • 5
    android通过http上传文件，服务器端用php写(原创)
    68
  • 6
    PHP医院安全不良事件管理系统源码（AEMS）前端vue2+element+后端laravel8不良事件上报与闭环管理
    85
  • 7
    深入理解PHP的命名空间与自动加载机制
    60
  • 8
    深入理解PHP的命名空间和自动加载机制
    45
  • 9
    网安之PHP基础练习
    49
  • 10
    1688快速获取整店铺列表 采集接口php Python
    57

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • mongodb
    • 下一篇
    DataWorks