Duan1v's Blog

MySQL学习笔记：组合索引-最左原则

Duan1v published on 2020-09-22 included in MySQL

新建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `uname` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
  `nickname` varchar(255) NOT NULL DEFAULT '' COMMENT '昵称',
  `phone` char(11) NOT NULL DEFAULT '' COMMENT '电话',
  `emp_no` varchar(20) NOT NULL DEFAULT '' COMMENT '员工编号',
  `dept_id` int(4) NOT NULL DEFAULT '0' COMMENT '部门id',
  `pwd` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
  `avt` varchar(255) NOT NULL DEFAULT '' COMMENT '头像',
  `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '地址',
  PRIMARY KEY (`id`),
  KEY `index_user` (`dept_id`,`uname`,`phone`) USING BTREE,
  KEY `avt` (`avt`),
  KEY `emp_no` (`emp_no`),
  KEY `index_nickname` (`nickname`,`pwd`) USING BTREE,
  KEY `addr` (`addr`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

最左原则

where子句中字段的顺序不会影响索引的使用

1
2
3
4
5
6
7
8
SELECT
	*
FROM
	`user`
WHERE
	uname = '5f686bb79ff03'
AND dept_id = 91
AND phone = '14097548200';

https://static.duan1v.top/images/M3svut7faHiQZNK.png — MySQL学习笔记：组合索引-最左原则

where子句中，只能按照索引字段，从右向左依次截取字段，剩下的字段依次作为有效索引

1
KEY `index_user` (`dept_id`,`uname`,`phone`) USING BTREE

可以直接用的有效索引有：(dept_id,uname,phone)，(dept_id,uname)，(dept_id)

https://static.duan1v.top/images/HBC94xY1ODIPMmu.png — MySQL学习笔记：组合索引-最左原则

(dept_id,phone)中phone是走不上索引的，但是当满足MySQL的索引下推条件时，是可以减少回表次数，从而减少查询时间的。

https://static.duan1v.top/images/DcAJ5glGMOxupa2.png — MySQL学习笔记：组合索引-最左原则

mysql 批量插入获取所有自增ID

Duan1v published on 2020-08-06 included in MySQL

背景

批量插入后，对刚插入的数据进行后续操作，需要获取所有插入的id，但执行sql只返回第一条id

思路

假设批量插入的数据的自增ID是连续的，则可以通过第一个自增ID获取所有自增ID

测试

理论上

批量插入的数据的自增ID是否是连续的，即插入过程中，单独插入其他数据，批量插入的自增ID是否连续

实践

生成批量插入10w条数据的sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
$s=<<<SQL
INSERT INTO `XXXX` (
    `test_id`,
    `operate_id`,
    `create_time`
)
VALUES
%s;
SQL;
$a=array();
for ($i=0;$i<100000;$i++){
    $a[]="('".rand(10000000,99999999)."', 'system', NOW())";
}
print_r(sprintf($s,implode(',',$a)));
exit(1);

navicat打开两个页面，一个是上面的sql，一个是一条单独插入的sql（这条sql的test_id,要大于上面sql里随机数的最大值，方便查看）

步骤

先点击执行上面的sql(应该会执行20多秒吧)，中间随机点击单条sql
筛选单条sql中的test_id，可以发现开始几条都是小于批量的自增ID的
在批量执行快结束的时候，插入的单条sql的自增ID是大于批量的，并没有单条sql的自增ID在批量中间
所以批量插入的自增ID是连续的

假设成立，可以这种方式获取批量插入的所有自增ID

Css的一些示例

Duan1v published on 2019-08-15 included in Php

表格中显示 a链接自动换行

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
    table, table tr th, table tr td {
        border: 1px solid #0094ff;
    }

    table {
        width: 100%;
        min-height: 25px;
        line-height: 25px;
        text-align: center;
        border-collapse: collapse;
        margin-bottom: 20px;
    }

    table td {
        word-wrap: break-word;
        word-break: break-all;
    }

    table a {
        display: inline-block;
        word-wrap:break-word;
        overflow: hidden;
        text-overflow: ellipsis;
    }

垂直水平居中

1
2
3
display: flex;
justify-content: ceter;
align-items: center;

Laravel中使用MongoDB

Duan1v published on 2019-08-15 included in Php

扩展

https://github.com/jenssegers/laravel-mongodb

查询

我要查所有images包含uurl的数据

https://static.duan1v.top/images/10861128-61202d548f798d73.webp

原生sql

1
db.getCollection('shops').find({'images':{"$elemMatch":{'uurl':{$exists:r=true}}}})

使用laravel可以写成

1
2
3
4
5
6
7
8
9
$a = Shop::query()
    ->where('images', '=', [
        '$elemMatch' => [
            'uurl' => [
                '$exists' => ['r' => true]
            ]
        ]
    ])->get();
dd($a);

toSql得到

1
select * from "shops" where "images" = ? and "deleted_at" is null and "meta"."status" != ?

若使用下面的方式只能得到images中的子数组均含有uurl的记录

1
2
3
4
5
6
Shop::query()
    ->where('images', '<>', [])
    ->whereNotNull('images')
    ->where('images.uurl', '<>', [])
    ->whereNotNull('images.uurl')
    ->get();

Supervisor的使用

Duan1v published on 2019-08-10 included in Php

简介

Supervisor 是一个进程管理工具。用于管理和监控进程的客户端/服务器系统，它可用于 Linux 和 Unix 系统。Supervisor 提供了一个守护进程，可以监控指定的进程，并在进程异常退出时自动重新启动它们。

安装supervisor的时候，遇到了一些问题，比如:

unix:///var/run/supervisor.sock no such file
unix:///var/run/supervisor.sock refused connection
pkg_resources.VersionConflict: (supervisor 4.0.4 (/usr/local/lib/python3.7/dist-packages), Requirement.parse(‘supervisor==3.3.1’))

解决：

不用apt-get直接安装，可能是源没有supervisor 4.0.4版本

环境：

1
2
3
4
5
6
7
8
9
 % lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 18.04.2 LTS
Release:        18.04
Codename:       bionic

 % python -V
Python 3.6.8

步骤,注意版本号

安装python包

1
sudo python3.6 -m pip install supervisor==4.0.4

从git下载supervisor,解压并cd进去

1
2
3
4
wget -O supervisor-4.0.4.zip https://github.com/Supervisor/supervisor/tree/4.0.4
unzip supervisor-4.0.4.zip 
cd supervisor-4.0.4
sudo python -m setup.py install

记一下配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
; supervisor config file
[unix_http_server]
file=/var/run/supervisor.sock   ; (the path to the socket file)
chmod=0700                       ; sockef file mode (default 0700)

[supervisord]
logfile=/var/log/supervisor/supervisord.log ; (main log file;default $CWD/supervisord.log)
pidfile=/var/run/supervisord.pid ; (supervisord pidfile;default supervisord.pid)
childlogdir=/var/log/supervisor            ; ('AUTO' child log dir, default $TEMP)
; the below section must remain in the config file for RPC
; (supervisorctl/web interface) to work, additional interfaces may be
; added by defining them in separate rpcinterface: sections
[rpcinterface:supervisor]
supervisor.rpcinterface_factory = supervisor.rpcinterface:make_main_rpcinterface

[supervisorctl]
serverurl=unix:///var/run/supervisor.sock ; use a unix:// URL  for a unix socket

; The [include] section can just contain the "files" setting.  This
; setting can list multiple files (separated by whitespace or
; newlines).  It can also contain wildcards.  The filenames are
; interpreted as relative to this file.  Included files *cannot*
; include files themselves.
[include]
files = /etc/supervisor/conf.d/*.conf

根据配置生成日志文件夹

1
2
sudo mkdir -p /var/log/supervisor/
sudo touch /var/log/supervisor/supervisord.log

好了

1
2
sudo supervisord -c /etc/supervisord.conf
sudo supervisorctl restart

操作命令 http://supervisord.org/running.html

配置队列

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
sudo vim /etc/supervisor/conf.d/laravel-worker.conf

[program:laravel-worker]
process_name=%(program_name)s_%(process_num)02d
command=php artisan queue:work --sleep=3 --tries=3 --queue=default,high
directory=/mnt/g/Workplace/explore/php/blog/
autostart=true
startsecs=3
autorestart=true
user=kael
numprocs=8
priority=999
redirect_stderr=true
stdout_logfile=/mnt/g/Workplace/explore/php/blog/storage/supervisor.log
stderr_logfile=/mnt/g/Workplace/explore/php/blog/storage/supervisor.err.log
stdout_logfile_maxbytes=2MB
stderr_logfile_maxbytes=2MB


sudo supervisord -c /etc/supervisord.conf

sudo supervisorctl reread

sudo supervisorctl update

sudo supervisorctl start laravel-worker:*

supervisor常用命令

1
2
3
4
5
6
7
supervisorctl stop program_name	#停止某个进程
supervisorctl start program_name	#启动某个进程
supervisorctl restart program_name	#重启某个进程
sudo supervisorctl status [program_name] #查看（某个）进程
supervisorctl stop all	#停止全部进程
supervisorctl reload	#载入最新的配置文件，停止原有进程并按新的配置启动、管理所有进程
supervisorctl update	#根据最新的配置文件，启动新配置或有改动的进程，配置没有改动的进程不会受影响而重启

用户等级非定时更新队列优化

Duan1v published on 2019-07-30 included in Php

背景

关于用户等级，有的app会在每天某个时间点定时更新，这样可以省去处理各种触发用户升级的事件的麻烦，那如果要尽可能即时更新用户等级，可以怎么优化呢

问题

因为各种触发用户升级的事件基本都是要查数据库，以判断用户是否达到升级要求，所以要放到队列中执行。实践中发现，存在某个时间段触发某个用户很多个升级事件的行为，造成不必要的sql查询：即用户让同事朋友为其点赞收藏评论等。

思路

可以这样优化：记录当前job的编号以及存在的最大job编号，然后执行到这个人的升级任务的时候，只执行最后一个job

实现

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

class GrowthPlanLevelListener
{
    const TAG = 'GrowthPlanLevelListener: ';

    /**
     * @param HasOwner|Event $event
     */
    public function handle($event)
    {
        /** @var User $user */
        $user = $event->getOwner();
        // phpcs:ignore
        // 此处获取版本信息，需要可序列化，所以不可以把这个listener实现Illuminate\Contracts\Queue\ShouldQueue
        // 解决方式是先获取$withJpush，再生成一个实现ShouldQueue的job，并把$withJpush传进去
        $withJpush = !LevelManager::isIncompatibleAgent();
        info(sprintf(
            '%s user %d check and upgrade growth plan level.',
            self::TAG,
            $user->id
        ));
        dispatch(new GrowthPlanLevelJob(
            $user,
            $withJpush,
            $this->getNum(sprintf(GROWTH_PLAN_JOB_KEY, $user->id))
        ));
    }

    private function getNum($key)
    {
        return Redis::connection()->eval(
            $this->preventRepeated(),
            1,
            $key
        );
    }

    // 不能让前一个job刚set完，后一个job没读到又set一次
    private function preventRepeated()
    {
        return <<<'LUA'
            local hash = redis.call('get', KEYS[1])
            local num = 1
            
            if hash then
                num = redis.call('incr',KEYS[1])
            else
                redis.call('set',KEYS[1],1)
            end
            
            return num
LUA;
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
class GrowthPlanLevelJob extends Job implements ShouldQueue
{
    const TAG = 'GrowthPlanLevelJob';

    /**
     * @var User $user
     */
    private $user;

    private $withJpush;

    /**
     * 由于存在某个时间段触发某个用户很多个升级事件的行为，造成不必要的sql查询
     * 所以需要记录当前job的编号以及存在的最大job编号，然后执行到这个人的升级任务的时候，只执行最后一个job
     * @var int $jobNum
     */
    private $jobNum;

    public $tries = 3;
    
    public $timeout = 180;

    public function __construct($user, $withJpush, $jobNum = 1)
    {
        $this->user = $user;
        $this->withJpush = $withJpush;
        $this->jobNum = $jobNum;
    }

    /**
     * @throws \Illuminate\Contracts\Redis\LimiterTimeoutException
     */
    public function handle()
    {
        $jobKey = sprintf(GROWTH_PLAN_JOB_KEY, $this->user->id);
        $count = Redis::connection()->get($jobKey);
        $info = [
            'user_id' => $this->user->id,
            'count'   => $count,
            'job_num' => $this->jobNum
        ];
        if (!$count) {
            info(self::TAG . ' $count is invalid.', $info);
            return;
        }
        if ($count > $this->jobNum) {
            info(self::TAG . ' remain other job of upgrade this user growth plan.', $info);
            return;
        }
        info(self::TAG . ' start executing job.', $info);
        Redis::connection()
            ->funnel($funnelKey = self::TAG . $this->user->id)
            ->limit($limit = 1)
            ->then(function () use ($funnelKey, $limit) {
                $levelManager = new LevelManager($this->user);
                $levelManager->checkAndUpLevel($this->withJpush);
            });
        Redis::connection()->del($jobKey);
    }
}