1. <output id="5rrzn"></output>

        <dl id="5rrzn"><ins id="5rrzn"><strong id="5rrzn"></strong></ins></dl><dl id="5rrzn"><font id="5rrzn"><nobr id="5rrzn"></nobr></font></dl>
        1. MySQL运维实战 之 PHP访问MySQL你使用对了吗

          PHP技术大全 / 2019-03-15 14:59:35

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

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

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

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

          一、症状

          一堆如下慢查询

          # [email protected]: 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
          # [email protected]: 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;

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

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

          二、排查

          要解决这个问题,首先想到的是,如何复现这个问题,如?#25991;?#25311;复现这个症状

          • 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
          # [email protected]: 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 faia_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. 官方网址:

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


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

          /* check connection */
          if (mysqli_connect_errno()) {
          printf("Connect failed: %sn", 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 %sn", $city, $district);

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

          /* close connection */
          mysqli_close($link);
          ?>
          • php模拟得到的slow 结果

          [[email protected] 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4  | grep '[email protected]' | grep 'xx_rx' | wc -l
          7891

          [[email protected] 20190211]# cat xx-slow.log | grep 'administrator command: Prepare' -B4 | grep '[email protected]' | wc -l
          7908

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

          那顺藤摸瓜,抓取下这?#38382;?#38388;有相同session id的整个sql执行过程

          MySQL开启slow=0的抓包模式

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


          # [email protected]: 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
          --
          # [email protected]: 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`=1where `id`=28886;
          # Time: 2019-02-13T08:18:45.626138+08:00
          --
          # [email protected]: 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模式

          • http://php.net/manual/zh/pdo.prepare.php

          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)

          $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() . "
          ");
          }

          strace -s200 -f php mysql1.php 跟踪

          大家可?#38053;?#21040;这个模式下,prepare的时候,是将query+占位符 发送给服务端的

          • 本地prepare模式 (ATTR_EMULATE_PREPARES = true )

          $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() . "
          ");
          }

          strace -s200 -f php mysql1.php 跟踪

          大家可?#38053;?#21040;这个模式下,prepare的时候,是不会将query发送给服务端的,只有execute的时候才会发送

          跟业务方确认后,他们使用的是后者,也就是修改了默?#29616;擔?#20182;们原本是想提升数据库的性能,因为预处理后只需要传?#38382;?#23601;好了

          但是对于我们的业务场景并不适合,我们的场景是频繁打开关闭连接,也就是预处理基本就用不到

          另外文档上面也明?#20998;?#20986;prepared statements 性能会不好

          调整和验证

          如何验证业务方是否将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)

          通过观察,发现这个值没有变化,?#24471;?#35843;整已经生效

          总结

          • prepare的优点

          1.防止SQL注入
          2.特定场景下提升性能
          什么是特定场景: 就是先去服务端用占位符占位,后面可以直接发送请求来填空(?#38382;?#20540;)
          这样理论上来说, 你填空的?#38382;?#38750;常多,性能才能发挥出来
          • prepare的缺点

          1. 在服务器端的prepare毕竟有消?#27169;?#24403;并发量大,频繁prepare的时候,就会有性能问题

          2. 服务端的prepare模式?#22815;?#24102;来的另外一个问题就是,排错和slow 优化有困难,因为大部分情况?#29575;?#30475;不到真实query的

          3. 尽?#21487;?#32622;php-pdo为 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true) ,在本地prepare,不要给服务器造成额外压力
          • 建议

          1. 默认情况下,应该使用php-pdo的默认配置,采用本地prepare的方式,这样可以做到防SQL注入的效果,性能差不到哪里去

          2. 除非真的是有上述说的特定场景,可?#38053;?#34385;配置成服务器prepare模式,前提是要做好测试

          干货分享

          敬请关注“PHP技术大全”微信公众号


          足球外围投注app

              1. <output id="5rrzn"></output>

                <dl id="5rrzn"><ins id="5rrzn"><strong id="5rrzn"></strong></ins></dl><dl id="5rrzn"><font id="5rrzn"><nobr id="5rrzn"></nobr></font></dl>

                      1. <output id="5rrzn"></output>

                        <dl id="5rrzn"><ins id="5rrzn"><strong id="5rrzn"></strong></ins></dl><dl id="5rrzn"><font id="5rrzn"><nobr id="5rrzn"></nobr></font></dl>