PHP连接Doris查询实战

wangleigiser

已于 2022-07-07 11:23:12 修改

阅读量1.2k

点赞数

分类专栏： Doris 文章标签： php 开发语言

于 2022-07-06 15:41:29 首次发布

本文链接：https://blog.csdn.net/wangleigiser/article/details/125641102

版权

Doris 专栏收录该内容

38 篇文章 10 订阅

订阅专栏

问题背景

有客户提出来他们的客户端是PHP的，需要使用PHP语言查询Doris。

问题解决思路

Doirs高度兼容MySQL协议，Java客户端目前使用MySQL JDBC Client查询Doris。
PHP也有MySQL Client，因此建议使用PHP MySQL Client，下面进行实战。

代码实战

1. 功能测试

创建Doirs用户。

create database php_doris;
CREATE USER 'bigdata'@'%' IDENTIFIED BY 'mypss'
GRANT ALL ON php_doris.*  TO 'bigdata'@'%';
GRANT SELECT_PRIV,LOAD_PRIV,ALTER_PRIV,CREATE_PRIV,DROP_PRIV on php_doris.* TO 'bigdata'@'%';

创建Doris表

CREATE TABLE test
 (
         id VARCHAR(32) DEFAULT '',
         user_name VARCHAR(32) DEFAULT '',
         member_list VARCHAR(32) DEFAULT ''
 )
 DUPLICATE KEY(id)
 DISTRIBUTED BY HASH(id) BUCKETS 10
 PROPERTIES("replication_num" = "1");

Doris数据插入：

INSERT INTO test VALUES ('1','alex','123');

PHP环境检查

[root@17a5da45700b php]# php -v
PHP 5.4.16 (cli) (built: Apr  1 2020 04:07:17) 
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies

通过Mysqli查询Doirs代码编写。vim mysql.php ，输入如下内容到文件。

<?php
    $host     = '127.0.0.1:9030';
    $username = 'bigdata';
    $password = 'mypss';
    $dbname   = 'php_doris';
    $mysql    = new Mysqli($host, $username, $password, $dbname);
    if($mysql -> connect_errno){
        die('connection fail'.$mysql->connect_errno);
    }else{
        $mysql -> set_charset('UTF-8');            
        $sql = 'select * from test limit 1;';     
        $result = $mysql -> query($sql);     
        $data = $result -> fetch_all();
        $mysql -> close();
    }
    echo '<pre>';
    print_r($data);
?>

执行PHP命令：php mysql.php ，查询Doirs，运行结果如下：

[root@17a5da45700b php]# php mysql.php 
<pre>Array
(
    [0] => Array
        (
            [0] => 1
            [1] => alex
            [2] => 123
        )

)
[root@17a5da45700b php]#

如果Doris没有密码，则password变量为空即可。具体代码如下：

<?php
    $host     = '127.0.0.1:9030';
    $username = 'root';
    $password = '';
    $dbname   = 'ssb';
    $mysql    = new Mysqli($host, $username, $password, $dbname);
    if($mysql -> connect_errno){
        die('connection fail'.$mysql->connect_errno);
    }else{
        $mysql -> set_charset('UTF-8');            
        $sql = 'select * from lineorder_rt limit 1;';     
        $result = $mysql -> query($sql);     
        $data = $result -> fetch_all();
        $mysql -> close();
    }
    print_r($data);
?>

2. 性能测试

准备一个1.5亿数据的表，我这里为SSB数据生成的表。具体数据生成参照Doris官网：https://doris.apache.org/zh-CN/docs/benchmark/ssb.html#_6-1-%E4%B8%8B%E8%BD%BD%E5%AE%89%E8%A3%85-ssb-%E6%95%B0%E6%8D%AE%E7%94%9F%E6%88%90%E5%B7%A5%E5%85%B7%E3%80%82

压测数据验证：

mysql>CREATE TABLE `lineorder` (
  `lo_orderkey` int(11) NOT NULL COMMENT "",
  `lo_linenumber` int(11) NOT NULL COMMENT "",
  `lo_custkey` int(11) NOT NULL COMMENT "",
  `lo_partkey` int(11) NOT NULL COMMENT "",
  `lo_suppkey` int(11) NOT NULL COMMENT "",
  `lo_orderdate` int(11) NOT NULL COMMENT "",
  `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
  `lo_shippriority` int(11) NOT NULL COMMENT "",
  `lo_quantity` int(11) NOT NULL COMMENT "",
  `lo_extendedprice` int(11) NOT NULL COMMENT "",
  `lo_ordtotalprice` int(11) NOT NULL COMMENT "",
  `lo_discount` int(11) NOT NULL COMMENT "",
  `lo_revenue` int(11) NOT NULL COMMENT "",
  `lo_supplycost` int(11) NOT NULL COMMENT "",
  `lo_tax` int(11) NOT NULL COMMENT "",
  `lo_commitdate` int(11) NOT NULL COMMENT "",
  `lo_shipmode` varchar(11) NOT NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`lo_orderkey`)
COMMENT "OLAP"
PARTITION BY RANGE(`lo_orderdate`)
(PARTITION p1 VALUES [("-2147483648"), ("19930101")),
PARTITION p2 VALUES [("19930101"), ("19940101")),
PARTITION p3 VALUES [("19940101"), ("19950101")),
PARTITION p4 VALUES [("19950101"), ("19960101")),
PARTITION p5 VALUES [("19960101"), ("19970101")),
PARTITION p6 VALUES [("19970101"), ("19980101")),
PARTITION p7 VALUES [("19980101"), ("19990101")))
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"colocate_with" = "groupa1",
"in_memory" = "false",
"storage_format" = "V2"
) 
mysql>select count(1) from lineorder;
+-----------+
| count(1)  |
+-----------+
| 150005812 |
+-----------+
1 row in set (3.70 sec)

表lineorder的Key 为lo_orderkey，我们以lo_orderkey为条件随机生产SQL对Doirs进行查询。将如下代码写入mysql-batch.php：

<?php
    $host     = '127.0.0.1:9030';
    $username = 'root';
    $password = '';
    $dbname   = 'ssb';
    $mysql    = new Mysqli($host, $username, $password, $dbname);
    if($mysql -> connect_errno){
        die('connection fail'.$mysql->connect_errno);
    }else{
        $mysql -> set_charset('UTF-8');

        for($i=1; $i<=10000; $i++)
        {
            $tmp = range(1,10000);
            $r = array_rand($tmp,9999);
            $sql = sprintf('select * from lineorder where lo_orderkey= %u limit 1',$tmp);
             #print_r($sql);      
             $time_start = microtime(true);
             $result = $mysql -> query($sql);     
             $data = $result -> fetch_all();
             $time_end = microtime(true);
              // 计算sql执行时间
             $use_time = ($time_end - $time_start);
             print_r('query_end:');
             print_r($use_time);
             #print_r($data);
        }
        $mysql -> close();
    }
?>

上述代码执行10000次Doris查询。每次查询的时候先生成随机数，然后使用随机数作为SQL中where lo_orderkey=xx的条件，生成随机SQL查询Doris。之所以生成随机数数为了防止SQL缓存对性能测试的评估。

mysql-batch.php运行和结果查看：

[root@17a5da45700b php]# php mysql-batch.php
query_end:0.0027971267700195query_end:0.0093619823455811query_end:0.0028290748596191query_end:0.002288818359375query_end:0.034303188323975query_end:0.0036108493804932query_end:0.0027980804443359query_end:0.0037839412689209query_end:0.0030560493469238query_end:0.0033760070800781query_end:0.0029630661010742query_end:0.0027680397033691query_end:0.0039849281311035query_end:0.012603998184204query_end:0.0064818859100342query_end:0.0086009502410889query_end:0.0085270404815674query_end:0.0081219673156738query_end:0.02131199836731query_end:0.005903959274292

运行php mysql-batch.php命令，可以看出查询基本在50ms左右返回。