Category: MySQL/NoSQL

MySQL位运算的应用

我们知道,PHP当中的错误级别常量,是根据二进制位特性而确定的一个个整数,可以简单的通过位运算定制PHP的错误报告。我们也可以将其应用到 MySQL 当中!

你是否遇到过下面这样的情况?

一、用户可能有若干不同属性或不同状态,然后你可能会在数据表中通过一个个字段去标记和实现,比如:

id int(11) 用户ID(自增)
username char(50) 用户名
password char(50) 密码
confirm_info tinyint(1) 是否确认资料 [ 0/1 ]
confirm_contract tinyint(1) 是否确认协议 [ 0/1 ]
freeze tinyint(1) 冻结状态 [ 0/1 ]
payer tinyint(1) 笔记是否付费用户 [ 0/1 ]
high_quality tinyint(1) 标记是否优质用户 [ 0/1 ]

二、用户拥有多个用户组(角色),常见的实现方法有:

1、增加一个字段,专门存它的用户组,用逗号隔开

id int(11) 用户ID(自增)
username char(50) 用户名
password char(50) 密码
confirm_info tinyint(1) 是否确认资料 [ 0/1 ]
confirm_contract tinyint(1) 是否确认协议 [ 0/1 ]
freeze tinyint(1) 冻结状态 [ 0/1 ]
payer tinyint(1) 笔记是否付费用户 [ 0/1 ]
high_quality tinyint(1) 标记是否优质用户 [ 0/1 ]
role_id varchar(255) 如:1,3,11

使用 FIND_IN_SET() 或 LIKE 进行数据查询。这种存储方式,虽然比较直观,结构也比较接单,但是,但是查询和维护数据都不方便

2、增加一个关联表

user_id int(11) 用户ID
role_id int(11) 角色ID

通过JOIN,连表查询。这种存储方式,似乎很好扩展,无论是增加了用户组,还是重新给用户划分用户组,直接操作这张关联表就行。但是,连表查询终究是需要操作多张表的,效率肯定不如单表查询,而且如果用户属性多了(不仅限于分组),那就得关联更多的表或者查多次,使查询变得更加复杂!


我们知道,PHP当中的错误级别常量,是根据二进制位特性而确定的一个个整数,可以简单的通过位运算定制PHP的错误报告。我们也可以将其应用到 MySQL 当中,还是以上面的用户表为例,我们只用一字段 status,专门记录用户状态,其结构如下:

id int(11) 用户ID(自增)
username char(50) 用户名
password char(50) 密码
status int(11) 用户状态位

然后我们给每一种状态设定一个对应的值,这个值需要使用2的N次方来表示,比如:

1 已确认资料
2 已确认协议
4 已冻结
8 付费用户
16 优质用户

查询技巧示例:

1、查询所有已冻结的用户:

SELECT * FROM `user` WHERE `status` & 4 = 4

2、查询所有未确认协议的用户

SELECT * FROM `user` WHERE `status` & 2 = 0

3、设置ID等于186的用户为优质用户

UPDATE `user` SET `status` = `status` | 16 WHERE `id` = 186

4、取消ID等于186的优质用户身份

PDATE `user` SET `status` = `status` ^ 16 WHERE `id` = 186

5、查询已冻结的优质付费用户(4 + 8 + 16 = 28)

SELECT * FROM `user` WHERE `status` & 28 = 28

同理,上述应用场景二中的分组问题,也可以使用同样的方法,优化表结构!然后使用位运算,实现各种条件的查询!

PHP实现MySQL并发查询

一般的,一个看似很简单的页面,一次http请求后,到达服务端,穿过Cache层,落到后台后,实际可能会有很多很多的数据查询逻辑!而这些查询实际是不相互依赖的,也即可以同时查询。比如各种用户信息,用户的APP列表,每个APP对应的流量数据、消耗记录、服务状态,平台运行状态,消息通知,新闻资讯等等。
这篇文章主要介绍了数据查询层,如何把串行变并行,提高查询效率、提升应用性能。实现方式包括:mysqlnd异步查询,cURL并发请求,Swoole异步非阻塞!

PHP脚本是按文档流的形式来执行的,所以我们在编写PHP程序时,代码基本都是串行的,尤其是SQL,比如:
倒流’s Bolg
这种方式,是每次查询都需要等待结果返回之后再开始下一次的查询,
运行时间 = (第1次发送请求时间 + 0.01 + 第1次返回时间)+(第2次发送请求时间 + 0.05 + 第2次返回时间)+(第3次发送请求时间 + 0.03 + 第3次返回时间)

如果是并发查询,那么流程就成了:
倒流’s Bolg
运行时间 = 发送请求时间 + 0.05 + 返回时间

显然,并发查询要比串行查询快!

那么PHP可以实现并发查询吗?答案是肯定的!

一、利用MySQL的异步查询功能

目前 MySQL 的异步查询,只在 MySQLi 扩展提供,查询方法分别是:

  1. 使用 MYSQLI_ASYNC 模式执行 mysqli::query
  2. 获取异步查询结果:mysqli::reap_async_query

需要注意的是,使用异步查询,需要使用 mysqlnd 作为PHP的MySQL数据库驱动,
mysqlnd(MySQL Native Driver) 是 Zend 公司开发的 MySQL 数据库驱动,采用PHP开源协议,用于代替旧版的由 MySQL AB公司(现在的Oracle)开发的 libmysql,PHP 5.3 及以上版本开始提供,PHP5.4 之后的版本 mysqlnd 为默认配置选项,
如果 PHP 小于 5.4,编译时需要指定编译参数:

--with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd

点击了解 mysqlnd 数据库驱动 >>

MySQL异步查询示例脚本:

/**
 * MySQL异步查询示例脚本:
 * @filename p_async.php
 * @url http://test.979137.com/ParallelSQL/p_async.php
 */

//期望结果集:获取以下用户的每个月每个APP的消费统计
$top = array('979137', '555555', '666666', '888888', '999999');
$ret = array_fill_keys($top, array());

//组织结构查询
$cmd = $resources = array();
$sql = "SELECT access_key,SUM(amount) sum_amount FROM consume_2016%s WHERE uid=%d AND product='SAE' GROUP BY access_key"; 
foreach($top as $uid) {
    for($i = 1; $i <= 12; $i++) { $ret[$uid][$i] = $tmp = array(); $tmp['uid'] = $uid; $tmp['month'] = $i; $tmp['resource'] = $resources[] = new \mysqli('localhost', 'root', '123456', 'sae', '3306'); $tmp['resource']->set_charset('utf8');
        $tmp['resource']->query(sprintf($sql, sprintf('%02d', $i), $uid), MYSQLI_ASYNC);
        $tag = spl_object_hash($tmp['resource']);
        $cmd[$tag] = $tmp;
    }   
}

$total = $query_times = count($resources);

//获取结果
do {
    $read = $error = $reject = $resources;
    //等待查询结束
    if (!\mysqli::poll($read, $error, $reject, 1)) {
        continue;
    }   
    //批量获取结果
    foreach($read as $resource) {
        $result = $resource->reap_async_query();
        if ($result) {
            $tag = spl_object_hash($resource);
            $uid = $cmd[$tag]['uid'];
            $month = $cmd[$tag]['month'];
            while(($row = $result->fetch_assoc()) != false) {
                $ret[$uid][$month][$row['access_key']] = $row['sum_amount'];
            }   
            $result->free();
            $total--;
    
        } else die('MySQLi error: '.$resource->error);
    }   
} while ($total > 0);

var_dump($ret);

二、cURL实现并发请求

先解释下 cURL 和 SQL 怎么就扯上关系了呢!

我们知道在很多系统架构里,PHP是不会直接操作DB的,而是 RESTful 架构,这时候所有操作都接口化了,
这时上述所讲的 SQL 就演变成 接口调用 了,
因为 API,所以 cURL!

以下是PHP中cURL多线程相关函数:

curl_multi_add_handle — 向curl批处理会话中添加单独的curl句柄
curl_multi_close — 关闭一组cURL句柄
curl_multi_exec — 运行当前 cURL 句柄的子连接
curl_multi_getcontent — 如果设置了CURLOPT_RETURNTRANSFER,则返回获取的输出的文本流
curl_multi_info_read — 获取当前解析的cURL的相关传输信息
curl_multi_init — 返回一个新cURL批处理句柄
curl_multi_remove_handle — 移除curl批处理句柄资源中的某个句柄资源
curl_multi_select — 等待所有cURL批处理中的活动连接
curl_multi_setopt — 为 cURL 并行处理设置一个选项
curl_multi_strerror — 返回描述错误码的字符串文本

我们可以利用这些多线程函数,实现 cURL 并发请求,从而实现并发 SQL!

cURL并发请求,服务端接口示例脚本:

/**
 * cURL并发请求,服务端接口示例脚本
 * @filename consume.php
 * @url http://test.979137.com/ParallelSQL/consume.php
 */
$resource = new \mysqli('localhost', 'root', '123456', 'sae', '3306');
$resource->set_charset('utf8');

$sql = "SELECT access_key,SUM(amount) sum_amount FROM consume_%d WHERE uid=%d AND product='%s' GROUP BY access_key";
$sql = sprintf($sql, $_GET['ym'], $_GET['uid'], $_GET['product']);
$res = $resource->query($sql);

$out['code'] = $resource->errno;
$out['message'] = $resource->error;
$data = array();
if (is_object($res)) {
    while(($row = $res->fetch_assoc()) != false) {
        $data[$row['access_key']] = $row['sum_amount'];
    }   
}
$out['data'] = $data;

header('Content-Type: application/json; charset=utf-8');
echo json_encode($out);

cURL并发请求,客户端调用示例脚本:

/**
 * cURL并发请求,客户端调用示例脚本
 * @filename p_curl.php
 * @url http://test.979137.com/ParallelSQL/p_curl.php
 */

//期望结果集:获取以下用户的每个月每个APP的消费统计
$top = array('979137', '555555', '666666', '888888', '999999');
$ret = array_fill_keys($top, array());

$mch = curl_multi_init();
$opt[CURLOPT_HEADER] = 0;
$opt[CURLOPT_CONNECTTIMEOUT] = 60; 
$opt[CURLOPT_RETURNTRANSFER] = true;
$opt[CURLOPT_HTTPHEADER] = array('Host: api.979137.com');

//生成句柄并加入到批处理
$cmd = array();
foreach($top as $uid) {
    for($i = 1; $i <= 12; $i++) { $ret[$uid][$i] = $tmp = array(); $tmp['url'] = sprintf('http://127.0.0.1/ParallelSQL/consume.php?ym=2016%02d&uid=%d&product=SAE', $i, $uid); $tmp['uid'] = $uid; $tmp['month'] = $i; $tmp['resource'] = curl_init($tmp['url']); curl_setopt_array($tmp['resource'], $opt); curl_multi_add_handle($mch, $tmp['resource']); $cmd[] = $tmp; } } //并发执行,直到全部结束。 do { curl_multi_exec($mch, $active); } while ($active > 0); 

//获取全部结果
foreach($cmd as $c) {
    $res = curl_multi_getcontent($c['resource']);
    $http_code = curl_getinfo($c['resource'], CURLINFO_HTTP_CODE);
    if ($res === false || $http_code != 200) {
        die(curl_error($c['resource']));
    }   
    $res = json_decode($res, true);
    $res['code'] && die($res['message']);
    $ret[$c['uid']][$c['month']] = $res['data'];
}
curl_multi_close($mch);

var_dump($ret);

查询效率对比

1、数据表机构:

CREATE TABLE `consume_201612` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `product` enum('UID','SAE','SC2','SCE','SEM','SCS','SLS') NOT NULL DEFAULT 'SAE',
  `access_key` varchar(255) NOT NULL,
  `service_code` varchar(255) NOT NULL,
  `amount` int(10) unsigned NOT NULL,
  `remark` varchar(255) NOT NULL,
  `data` text NOT NULL,
  `times` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid_pa` (`uid`,`product`,`access_key`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、数据量:总共分12张表,每张表的数量在 500万~1100万 之间,全量数据在1亿以上

3、MySQL异步查询结果:

$ mysql.server restart
$ php p_async.php
Query times: 60
Run time: 2.4453s
(CPU)User time: 0.087966s
(CPU)System time: 0.117625s

4、cURL并发请求查询结果

$ mysql.server restart
$ php p_curl.php
Query times: 60
Run time: 2.173035s
(CPU)User time: 0.40652s
(CPU)System time: 0.869902s

5、普通串行查询结果

$ mysql.server restart
$ php p_sync.php
Query times: 60
Run time: 20.485623s
(CPU)User time: 0.083185s
(CPU)System time: 0.036566s
Memory usage: 304.72kb

并发执行时,我们可以在 MySQL 服务器看到所有的正在执行的SQL:
倒流’s Bolg

总结

1、在并发查询下,查询效率提高了近10倍
2、使用 MySQL 异步查询,因为需要给所有查询都创建一个新的连接,而 MySQL 服务端会为每个连接创建一个单独的线程进行处理,如果创建的线程数过多,会给系统造成负担,请谨慎使用
3、使用 cURL 并发请求后端接口时,CPU负载明显上升,所以并发请求后端接口,一定程度上会增加后端压力,这和前端大流量下的高并发原理是一样的
4、使用 cURL 并发请求,还需要考虑一个网络延时的问题,网络延时越小,查询效率提升越明显。如果你是想代替类方法或函数调用,在条件允许的情况,建议直接连接服务器本机即127.0.0.1
5、在并发请求下,因为需要一次性接收全部返回结果,所以会占用更多的内存资源

需要说明的是,在实际应用中 cURL 的并发请求,一般不只单用于数据查询,而是为了完成更多的后台业务逻辑,
所以,在服务器负载能力允许的情况下,推荐使用 cURL 并行转发的形式,提升前端响应速度!

最后说一下 Swoole,

Swoole 是 PHP 的异步、并行、高性能网络通信引擎,使用纯C语言编写,提供了PHP语言的异步多线程服务器,异步TCP/UDP网络客户端,异步MySQL,异步Redis,数据库连接池,AsyncTask,消息队列,毫秒定时器,异步文件读写,异步DNS查询!
其中的异步MySQL,其原理是通过 MYSQLI_ASYNC 模式查询,然后获取 mysql 连接的 socket,加入到 epoll 时间循环中,当数据库返回结果时会回调指定函数。
这个过程是完全异步非阻塞的,不浪费CPU,具体实现方式这里不再详细介绍!

php –with-mysqli=mysqlnd –with-pdo-mysql=mysqlnd

1、什么是mysqlnd驱动?

PHP手册上的描述:

MySQL Native Driver is a replacement for the MySQL Client Library (libmysql).
MySQL Native Driver is part of the official PHP sources as of PHP 5.3.0

mysqldnd即MySQL Native Driver简写,即是由PHP源码提供的mysql驱动连接代码,它的目的是代替旧的libmysql驱动。

传统的安装php的方式中,我们在编译PHP时,一般需要指定以下几项:

–with-mysql=/usr/local/mysql
–with-mysqli=/usr/local/mysql
–with-pdo-mysql=/usr/local/mysql

这实际上就是使用了MySQL官方自带的libmysql驱动,这是比较老的驱动,PHP5.3开始已经不建议使用它了,而建议使用mysqlnd

2、PDO与mysqlnd, libmysql又是何种关系?

PDO是一个应用层抽象类,底层和mysql server连接交互需要mysql驱动的支持。也就是说无论你使用了何种驱动,都可以使用PDO.
PDO提供了PHP应用程序层API接口,而mysqlnd,libmysql则负责与mysql server进行网络协议交互(它并不提供php应用程序层API功能)

3、为何要使用mysqlnd驱动?

PHP官方手册描述:

A.libmysql驱动是由mysql AB公司(现在是oracle公司)编写, 并按mysql license许可协议发布,所以在PHP中默认是被禁用的.
而mysqlnd是由php官方开发的驱动,以php license许可协议发布,故就规避了许可协议和版权的问题

B.因为mysqlnd内置于PHP源代码,故你在编译安装php时就不需要预先安装mysql server也可以提供mysql client API (mysql_connect, pdo , mysqli), 这将减化一些工作量.

C. mysqlnd是专门为php优化编写的驱动,它使用了PHP本身的特性,在内存管理,性能上比libmysql更有优势. php官方的测试是:libmysql将每条记录在内存中保存了两份,而mysqlnd只保存了一份

D. 一些新的或增强的功能
增强的持久连接
引入特有的函数mysqli_fetch_all()
引入一些性能统计函数mysqli_get_cache_stats(), mysqli_get_client_stats(),
mysqli_get_connection_stats(),
使用上述函数,可很容易分析mysql查询的性能瓶颈!
SSL支持(从php 5.3.3开始有效)
压缩协议支持
命名管道支持(php 5.4.0开始有效)

4、看到这里,你可能跃跃欲试,很想使用mysqlnd驱动

提示: 如果使用mysqlnd,并不需要预先安装mysql
编译php时,修改以下几个项参数即可

--with-mysql=mysqlnd
--with-mysqli=mysqlnd
--with-pdo-mysql=mysqlnd 

Starting MySQL… ERROR! The server quit without updating PID file

刚编译完 MySQL 遇到这个问题?? 别着急,下面是本人总结,据说看完的99%都解决了!

1、你初始化数据库了吗??

/usr/local/mysql/bin/mysql_install_db 

2016-05-25 19:56:18 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld –initialize
2016-05-25 19:56:18 [ERROR] The data directory needs to be specified.

我的是 MySQL 5.7,新版已经改成用 mysqld –initialize 来初始化数据库了

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

注意:
1)后面的参数一定要加上,–user代表运行用户,–basedir代表MySql安装目录,–datadir代表数据库数据所在目录,每个人的配置不一样,根据自己的编译参数修改一下。
2)执行前,请先清空编译前指定的datadir,默认是在安装目录的data/(我编译时指定在了/data/mysql),否则会报如下错误:

–initialize specified but the data directory has files in it. Aborting.
2016-05-25T12:07:27.006980Z 0 [ERROR] Aborting

2、可能是 datadir 目录没有写的权限

chown -R mysql:mysql /data/mysql && chmod -R 755 /data/mysql

3、错误日志文件没有权限(根据你的编译参数和my.cnf查看你的错误文件路径)

[chown -R mysql:mysql /var/log/mysqld.log && chmod -R 755 /var/log/mysqld.log

4、可能进程里已经存在mysql进程

ps -ef | grep mysql

如果有使用 “kill -9 进程号” 杀死,然后重新启动mysqld!

5、可能是第二次在机器上安装MySQL,有残余数据影响了服务的启动。
清空之前的 datadir 目录,和MySQL安装目录,如果存在mysql-bin.index,就赶快把它删除掉吧。

6、MySQL在启动时没有指定配置文件时会使用 /etc/my.cnf 配置文件,请打开这个文件查看在[mysqld]节下有没有指定数据目录(datadir)。
请在[mysqld]下设置这一行:

datadir = /data/mysql

7、skip-federated字段问题
检查一下/etc/my.cnf文件中有没有没被注释掉的 skip-federated 字段,如果有就立即注释掉吧。

8、selinux惹的祸,如果是centos系统,默认会开启selinux
关闭它,打开/etc/selinux/config,把SELINUX=enforcing改为SELINUX=disabled后存盘退出重启机器试试。

对比Redis、Memcache、MongoDB,区别、关系、作用

1、性能

都比较高,性能对我们来说应该都不是瓶颈
总体来讲,TPS 方面 Redis 和 Memcache 差不多,要大于 MongoDB

2、操作的便利性

Redis 丰富一些,数据操作方面,Redis更好一些,较少的网络IO次数
Memcache 数据结构单一MongoDB 支持丰富的数据表达,索引,最类似关系型数据库,支持的查询语言非常丰富

3、内存空间的大小和数据量的大小

MongoDB 适合大数据量的存储,依赖操作系统VM做内存管理,吃内存也比较厉害,服务不要和别的服务在一起
Redis 在2.0版本后增加了自己的VM特性,突破物理内存的限制;可以对key value设置过期时间(类似Memcache)
Memcache 可以修改最大可用内存,采用LRU算法

4、可用性(单点问题)

对于单点问题,Redis,依赖客户端来实现分布式读写;主从复制时,每次从节点重新连接主节点都要依赖整个快照,无增量复制,因性能和效率问题,
所以单点问题比较复杂;不支持自动sharding,需要依赖程序设定一致hash 机制。
一种替代方案是,不用Redis本身的复制机制,采用自己做主动复制(多份存储),或者改成增量复制的方式(需要自己实现),一致性问题和性能的权衡;
Memcache 本身没有数据冗余机制,也没必要;对于故障预防,采用依赖成熟的hash或者环状的算法,解决单点故障引起的抖动问题。
MongoDB支持master-slave,replicaset(内部采用paxos选举算法,自动故障恢复),auto sharding机制,对客户端屏蔽了故障转移和切分机制。

5、可靠性(持久化、数据恢复)

Redis 支持(快照、AOF):依赖快照进行持久化,aof增强了可靠性的同时,对性能有所影响
Memcache 不支持,通常用在做缓存,提升性能;
MongoDB 从1.8版本开始采用binlog方式支持持久化的可靠性

6、数据一致性(事务支持)

Memcache 在并发场景下,用cas保证一致性
Redis 事务支持比较弱,只能保证事务中的每个操作连续执行
MongoDB 不支持事务

7、数据分析

MongoDB 内置了数据分析的功能(mapreduce),
其他不支持

8、应用场景

Redis:数据量较小的更性能操作和运算上
Memcache:用于在动态系统中减少数据库负载,提升性能;做缓存,提高性能(适合读多写少,对于数据量比较大,可以采用sharding)
MongoDB:主要解决海量数据的访问效率问题

最新文章

Return Top