mysql片段收集

Mysql使用别名多表查询join时问题

SELECT * FROM t1, t2 LEFT JOIN t3 ON (t1.id=t3.id) WHERE t1.id=t2.id
但是在执行的时候报错“Unknown column t1.id”,以前博主习惯利用AS 表别名来进行多表关联查询，这让博主很凌乱，一时不知道如何下手解决了
实际上执行 SELECT * FROM t1,( t2 LEFT JOIN t3 ON (t1.id=t3.id)) WHERE t1.id=t2.id

SELECT * FROM t1 INNER JOIN t2 LEFT JOIN t3 ON (t1.id=t3.id) WHERE t1.id=t2.id

重置root密码

kill `cat /mysql-data-directory/host_name.pid`
#ps方法
ps aux|grep 'mysql'
#这里把%pid%替换为上面ps给的进程id
sudo kill %pid%
#5.7.6和以后版本
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
#5.7.6以前版本
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
#官网手册命令，mac用户不推荐
 
mysqld_safe --init-file=/home/me/mysql-init &
 
#mac用户推荐命令
 
sudo /usr/local/mysql/bin/mysqld --user=_mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/mysqld.local.err --pid-file=/usr/local/mysql/data/mysqld.local.pid --init-file=/Users/king/sql/alter_root.sql &
1. 停止mysql服务:
systemctl stop mysqld

2. 设置mysql的环境变量参数 
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

3. 启动mysql服务
systemctl start mysqld

4. root无密码登录
mysql -u root

5. 更新root密码
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
    -> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit

6. 关闭mysql服务
systemctl stop mysqld

7. 删除mysql环境参数
systemctl unset-environment MYSQLD_OPTS

8. 再次启动:
systemctl start mysqld

9. 用root登录:
. mysql -u root -p

mysql5.7无法远程连接问题

mysql -uroot -p
 
use mysql;
 
select * from user\G;
sudo iptables -L #查看防火墙规则列表
 
sudo iptables -F  #清空防火墙列表
vi /etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address = 127.0.0.1
bind-address = 0.0.0.0

select *导致的mysql线程sending data

当有大量的”select * from xxx”存在时，虽然这个表的数据量不是很大，只有区区几千条记录记录，但是大量的查询引起mysql线程状态卡在”sending data”时，服务器的负载就上来了。

那么什么是”sending data”状态哪?其实这是一个很容易引起误导的状态说明，”sending data”是包含读取数据+发送数据的。这里以innodb存储引擎来说，我们在使用索引找到我们所需的记录时，期初得到是索引列信息和主键信息，如果我们查询的信息索引列中已经包含，那么万事大吉，mysql会把这些信息发送给客户端。但是如果像我的例子中的是使用”select *”这种情况，或者要索引列中未包含我们需要需要的更多信息，那么这时mysql就会拿着主键id去数据行获取信息，然后再把些信息发送给客户端。

现在回到上面的问题，我们数据表记录数不多，为什么会引起”sending data”哪?我们活动信息表有几个字段是MEDIUMTEXT或者VARCHAR(3000)类似这种要存储比较长字符串内容的字段，所以在使用”select *”时我们把本来不需要，但是却占用很大空间的字段也返回了，造成了大量无用的IO操作，这里包含读取数据和发送数据。由于接口我们使用的是被动缓存，所以活动刚开始时这些请求都打到了数据库，后面接口缓存生效以后数据库压力就降下来了。

开发框架使用的是Lumen,而且我们主要使用的是里面的ORM-Elopment,有些地方没有注意查询时设定字段就引起了这个问题，说句实话以前在使用ORM时，我一直没有养成限制返回字段的习惯。希望看到这篇文章的同学也能引以为戒，以后尽量不要使用”seleect *”这样粗暴的查询方式。

lumen数据库时区设置

http://www.helpergarden.com/2018/05/lumen%e6%95%b0%e6%8d%ae%e5%ba%93%e6%97%b6%e5%8c%ba%e8%ae%be%e7%bd%ae.html
timestamp里面居然保存的居然是带时区的
查看ORM源码，它的注释里面说日期类型的字符创会转化成“DateTime/Carbon”实例，然后再进一步处理是输出或者存入数据库。

我们可以通过在.env中设置”DB_TIMEZONE”来解决时区不一致的问题。

DB_TIMEZONE=+8:00
一般来说要保证我们设置DB_TIMEZONE和APP_TIMEZONE一致的，所以一般配置文件都是这样的。

APP_TIMEZONE=Asia/Shanghai
DB_TIMEZONE=+8:00

nginx跨域设置

location / {
       index  index.html index.htm index.php;
       autoindex on;
       try_files $uri $uri/ /index.php?$query_string;
}

include cors.conf;
vi cors.conf 
add_header Access-Control-Allow-Origin *;
add_header Access-Control-Allow-Methods 'GET, POST, OPTIONS';
add_header Access-Control-Allow-Headers 'DNT,X-Mx-ReqToken,Keep-Alive,User-Agent,X-Requested-With,If-Modified-Since,Cache-Control,Content-Type,Authorization';

if ($request_method = 'OPTIONS') {
    return 204;
}

swoole process父子进程使用队列通信

<?php
//http://www.helpergarden.com/2018/05/swoole-process%e7%88%b6%e5%ad%90%e8%bf%9b%e7%a8%8b%e4%bd%bf%e7%94%a8%e9%98%9f%e5%88%97%e9%80%9a%e4%bf%a1.html
$worker_num = 2;
$process_pool = [];

$process= null;
$pid = posix_getpid();

function sub_process(swoole_process $worker)
{
    sleep(1); //防止父进程还未往消息队列中加入内容直接退出
    echo "worker ".$worker->pid." started".PHP_EOL;
    while($msg = $worker->pop()){
        if ($msg === false) {
            break;
        }
        $sub_pid = $worker->pid;
        echo "[$sub_pid] msg : $msg".PHP_EOL;
        sleep(1);//这里的sleep模拟必须，否则可能1个worker就把所有信息全接受了
    }
    echo "worker ".$worker->pid." exit".PHP_EOL;
    $worker->exit(0);
}

$customMsgKey = 1;//默认为空，这个地方可以随便填的
$mod = 2 | swoole_process::IPC_NOWAIT;//这里设置队列为非阻塞模式

//创建worker进程
for($i=0;$i<$worker_num; $i++) {
    $process=new swoole_process('sub_process');
    $process->useQueue($customMsgKey, $mod);
    $process->start();
    $pid = $process->pid;
    $process_pool[$pid] = $process;
}

$messages = [
    "Hello World!",
    "Hello Cat!",
    "Hello King",
    "Hello Leon",
    "Hello Rose"
];
//由于所有进程是共享使用1个消息队列，所以只需向一个字进程发送消息即可
$process = current($process_pool);
foreach ($messages as $msg) {
    $process->push($msg);
}

swoole_process::wait();
swoole_process::wait();

echo "master exit".PHP_EOL;

yum安装elasticsearch

//http://www.helpergarden.com/2018/03/yum%e5%ae%89%e8%a3%85elasticsearch.html
yum install java
yum makecache
yum install elasticsearch
cd /usr/share/elasticsearch
./bin/elasticsearch-plugin install https://github.com/medcl/elasticsearch-analysis-ik/releases/download/v6.2.2/elasticsearch-analysis-ik-6.2.2.zip
  systemctl start elasticsearch
curl http://127.0.0.1:9200
访问“http://127.0.0.1:9200/_cat/plugins”可以查看es安装的插件。

curl http://127.0.0.1:9200/_cat/plugins

grant

mysql>grant select,delete,update,create,drop on *.* to test@"%" identified by "1234";

     //test用户对所有数据库都有select,delete,update,create,drop 权限。https://laravel-china.org/articles/22147
　 @"%" 表示对所有非本地主机授权，不包括localhost。

　对localhost授权：加上一句grant all privileges on testDB.* to test@localhost identified by '1234';即可

EXPLAIN 查看SQL执行计划

EXPLAIN SELECT ……
EXPLAIN EXTENDED SELECT ……
将执行计划"反编译"成SELECT语句，运行SHOW WARNINGS，可得到被MySQL优化器优化后的查询语句。
EXPLAIN PARTITIONS SELECT ……
用于分区表的EXPLAIN生成QEP的信息

卸载mysql8

[root@VM_0_14_centos ~]# rpm -qa|grep mysql

mysql80-community-release-el7-1.noarch
mysql-community-client-8.0.13-1.el7.x86_64
mysql-community-libs-8.0.13-1.el7.x86_64
mysql-community-libs-compat-8.0.13-1.el7.x86_64
php70w-mysqlnd-7.0.32-1.w7.x86_64
mysql-community-common-8.0.13-1.el7.x86_64
mysql-community-server-8.0.13-1.el7.x86_64

yum remove mysql80-community-release-el7-1.noarch
[root@VM_0_14_centos ~]# find / -name mysql
/etc/selinux/targeted/tmp/modules/100/mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/var/lib/docker/volumes/68a07333528e60edd5980282ae6b0a3ce2324021eb8ca2f1963adb5a199ade7a/_data/mysql
/var/lib/docker/volumes/f17d91c72d0178c3746414175662884b29c462146c18fcf775c139a875dbd746/_data/mysql
/var/lib/docker/volumes/f8f07e5586d9f47cb5ec67a6b1fcd174c60446091e75ad0a920d58afefc8c405/_data/mysql
/var/lib/docker/overlay2/68559b409701bd076a41a3a062d786093709ba872db563d90ce969222990e3ff/merged/etc/init.d/mysql
/var/lib/docker/overlay2/68559b409701bd076a41a3a062d786093709ba872db563d90ce969222990e3ff/merged/etc/mysql
/var/lib/docker/overlay2/68559b409701bd076a41a3a062d786093709ba872db563d90ce969222990e3ff/merged/var/lib/mysql
/var/lib/docker/overlay2/68559b409701bd076a41a3a062d786093709ba872db563d90ce969222990e3ff/merged/var/log/mysql
/var/lib/docker/overlay2/68559b409701bd076a41a3a062d786093709ba872db563d90ce969222990e3ff/merged/usr/bin/mysql
/var/lib/docker/overlay2/68559b409701bd076a41a3a062d786093709ba872db563d90ce969222990e3ff/merged/usr/lib/mysql
/var/lib/docker/overlay2/68559b409701bd076a41a3a062d786093709ba872db563d90ce969222990e3ff/merged/usr/share/mysql

rm -rf /var/lib/mysql
rm -rf /etc/my.cnf
rm -rf /var/log/mysqld.log

安装mysql5.7

wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
yum localinstall mysql57-community-release-el7-8.noarch.rpm
yum repolist enabled | grep "mysql.*-community.*"
mysql> system yum repolist enabled | grep "mysql.*-community.*"
Repository epel is listed more than once in the configuration
mysql-connectors-community/x86_64 MySQL Connectors Community                  74
mysql-tools-community/x86_64      MySQL Tools Community                       74
mysql57-community/x86_64          MySQL 5.7 Community Server                 307
 [root@VM_0_14_centos ~]# yum install mysql-community-server
 grep 'temporary password' /var/log/mysqld.log
 Loaded plugins: fastestmirror, langpacks
 Repository epel is listed more than once in the configuration
 Determining fastest mirrors
  * nux-dextop: li.nux.ro
  * webtatic: us-east.repo.webtatic.com
 Package mysql-community-server-5.7.24-1.el7.x86_64 already installed and latest version
 Nothing to do
     [root@VM_0_14_centos ~]# grep 'temporary password' /var/log/mysqld.log
     [root@VM_0_14_centos ~]#
     [root@VM_0_14_centos ~]# service mysqld restart
     Redirecting to /bin/systemctl restart  mysqld.service
     
     [root@VM_0_14_centos ~]# /bin/systemctl restart  mysqld.service
     [root@VM_0_14_centos ~]# ps aux|grep mysql
     systemd+   765  0.0  0.2 1121296 4316 ?        Ssl   2018  38:00 mysqld
     mysql    11499  0.3  8.8 1119512 166152 ?      Sl   17:47   0:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
     root     11769  0.0  0.0 112708   964 pts/3    R+   17:49   0:00 grep --color=auto mysql
     [root@VM_0_14_centos ~]# which mysqld
     /usr/sbin/mysqld
     [root@VM_0_14_centos ~]# lsof -i:3306
     COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
     mysqld  11499 mysql   18u  IPv6 60264974      0t0  TCP *:mysql (LISTEN)
     [root@VM_0_14_centos ~]# grep 'password' /var/log/mysqld.log
     2019-01-14T09:46:16.712229Z 1 [Note] A temporary password is generated for root@localhost: RpvpdgHKZ5;*
     2019-01-14T09:47:39.920810Z 0 [Note] Shutting down plugin 'validate_password'
     2019-01-14T09:47:41.646659Z 0 [Note] Shutting down plugin 'sha256_password'
     2019-01-14T09:47:41.646662Z 0 [Note] Shutting down plugin 'mysql_native_password'
     [root@VM_0_14_centos ~]# mysql -uroot -p
     Enter password:
     Welcome to the MySQL monitor.  Commands end with ; or \g.
     Your MySQL connection id is 2
     Server version: 5.7.24
mysql> use mysql
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>  ALTER USER 'root'@'localhost' IDENTIFIED BY '*SUsheng*';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>  ALTER USER 'root'@'localhost' IDENTIFIED BY '*SUsheng123*';
Query OK, 0 rows affected (0.00 sec)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'YourPassword9#' WITH GRANT OPTION;
修改/etc/my.cnf配置文件，在[mysqld]下添加编码配置
vim /etc/my.cnf
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
λ mysql -h 118.24.158.116 -uroot -p
Enter password: ***********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24 MySQL Community Server (GPL)

mysql5.7 以后的争强了安全机制,所以使用yum安装,启动会系统会自动生成一个随机的密码，并且不能设置简单密码。所以需要修改 mysql 全局参数

先用日志密码登录 mysql 

grep 'temporary password' /var/log/mysqld.log

会输出结果: A temporary password is generated for root@localhost: ******

使用此密码登录后 执行 SHOW VARIABLES LIKE 'validate_password%‘;  查看 mysql 密码策略

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    | 
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM | 
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

执行 set global validate_password_policy=LOW;  修改密码策略

执行 set global validate_password_length=6; 修改验证密码长度

切换 user 库

update user set authentication_string = password('root'), password_expired = 'N', password_last_changed = now() where user = 'root';

重启 mysqld 服务，再用新密码登录即可

如果无法登录,提示Access denied for user 'root'@'localhost'

重新更新 root 用户的 plugin 字段

update user set plugin='mysql_native_password' where user = 'root';

更新成功后.重新执行更新密码操作

刷新权限  flush privileges;

查看实时执行的SQL语句

mysql> SHOW VARIABLES LIKE "general_log%";
如下general_log值为OFF说明没有开启：

+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log      | OFF                              |
| general_log_file | /var/lib/mysql/galley-pc.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)
mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL general_log_file = '/var/log/mysql/general_log.log';
永久有效需要配置my.cnf文件，加入下面两行：

general_log = 1
general_log_file = /var/log/mysql/general_sql.log
$ tail -f /var/lib/mysql/general_sql.log
/usr/sbin/mysqld, Version: 5.7.24 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2019-01-15T08:45:47.794791Z       137 Query     SHOW VARIABLES LIKE 'general%'

直接使用 /etc/mysql/debian.cnf 文件中 [client] 节提供的用户名和密码
mysql -u*** -p
切换到 mysql 数据库
use mysql;
更新密码
update user set authentication_string = password('root'), password_expired = 'N', password_last_changed = now() where user = 'root';
刷新权限 flush privileges;
重启 mysqld 服务，再用新密码登录即可
如果无法登录,提示 Access denied for user 'root'@'localhost'
重新更新 root 用户的 plugin 字段
update user set plugin='mysql_native_password' where user = 'root';
更新成功后.重新执行更新密码操作

Can’t connect to local MySQL server

[root@localhost app]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@localhost app]# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
查询自己的 mysql.sock文件

发现自己的路径在 

[root@localhost data]# find / -name mysql.sock
/tmp/mysql.sock
则在 my.cnf 中添加指定 mysql.sock文件

[root@localhost data]# vim /etc/my.cnf
[mysqld]
socket=/tmp/mysql.sock


fack!! 重启mysql服务问题依旧存在

于是添加软连接

[root@localhost data]# mkdir -pv /var/lib/mysql
mkdir: 已创建目录 “/var/lib/mysql”
[root@localhost data]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
[root@localhost data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Access denied for user ‘root‘@’localhost’

# /etc/init.d/mysqld stop //停止mysql服务的运行
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & //跳过受权表访问
# mysql -u root mysql //登录mysql



在mysql5.7以下的版本如下：
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root' and host='127.0.0.1' or host='localhost';//把空的用户密码都修改成非空的密码就行了。

在mysql5.7版本如下：

update mysql.user set authentication_string=password('newpassword') where user='root' and host='127.0.0.1' or host='localhost';


mysql> FLUSH PRIVILEGES;
mysql> quit # /etc/init.d/mysqld restart //离开并重启mysql
# mysql -uroot -p
Enter password: <输入新设的密码newpassword> 
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock

[root@localhost ~]# ps aux|grep mysqld
root     10913  0.0  0.0 106228  1396 ?        S    Jan19   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data1/mysql --socket=/tmp/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql    11147 57.8  3.4 3502672 416152 ?      Sl   Jan19 1706:42 /usr/sbin/mysqld --basedir=/usr --datadir=/data1/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/tmp/mysql.sock

Can’t connect to MySQL server on ‘127.0.0.1’

[root@localhost ~]# mysql -uroot -p -h 127.0.0.1
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
[root@localhost ~]# grep bind /etc/my.cnf
bind-address=172.16.7.27
[root@localhost ~]# mysql -uroot -p -h 172.16.7.27
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3979750
Server version: 5.7.11 MySQL Community Server (GPL)
Connection:             172.16.7.27 via TCP/IP#和通过socket链接不同

转换编码

#!/bin/bash
DIR=$1 # 转换编码文件目录https://tonydeng.github.io/2015/11/27/batch-conversion-file-encoding/
FT=$2  # 需要转换的文件类型（扩展名）
SE=$3  # 原始编码
DE=$4  # 目标编码
for file in `find $DIR -type f -name "*.$FT"`; do
	echo "conversion $file encoding $SE to $DE"
    iconv -f $SE -t $DE "$file" > "$file".tmp
    mv -f "$file".tmp "$file"
done
//./batch_conversion_encoding.sh ~/sdk1 java GBK UTF8

安装 MySQL8.0.13

wget -c 'https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.13-1.el7.x86_64.rpm-bundle.tar'
解压
tar xvf mysql-8.0.13-1.el7.x86_64.rpm-bundle.tar
安装
yum install mysql-community-libs-8.0.13-1.el7.x86_64.rpm
yum install mysql-community-libs-compat-8.0.13-1.el7.x86_64.rpm
yum install mysql-community-client-8.0.13-1.el7.x86_64.rpm
yum install mysql-community-server-8.0.13-1.el7.x86_64.rpm
设置数据库https://laravel-china.org/articles/22686
初次安装没进去，在/etc/my.cnf 中添加 skip-grant-tables, 使用mysql -uroot 直接进入数据库，
修改root账号密码：
alter user 'root'@'localhost' identified by '**';
mysql 8.0以后默认加密方式跟5.7不一样 ：
ALTER USER 'root'@'localhost' IDENTIFIED BY '**' PASSWORD EXPIRE NEVER; //修改用户永不过期
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '***';//更新一下用户的密码加密方式，修改密码
flush privileges; //执行完刷新权限

创建新用户
CREATE USER smile@% IDENTIFIED BY 'passowrd'; //# 创建账号密码
GRANT ALL ON . TO smile@% WITH GRANT OPTION; //授予远程登录权限（.远程访问所有数据）
REVOKE all privileges ON databasename.tablename FROM 'username'@'host'; //删除某些数据权限
flush privileges;//刷新权限
//不常用的 --- 创建过期用户
CREATE USER smile@% IDENTIFIED BY '***' PASSWORD EXPIRE INTERVAL 90 DAY;
//数据库搞定
service mysqld start restart stop

查询指定数据库的所有表名称

USE information_schema;

SELECT TABLE_NAME,table_rows FROM TABLES WHERE TABLE_SCHEMA = '数据库名字' ORDER BY table_rows DESC;
查询指定库的数据大小，索引大小
SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS total_data_size, CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS total_index_size FROM TABLES  WHERE table_schema  =  '数据库名字';

MySQL 分区表

//https://learnku.com/articles/22947
如果需要定时清理一张普通大表里的历史数据。

可以使用一个或多个带 where 条件的 delete 语句去删除（where条件是时间）。 如果表数据量较大，这对数据库的造成了很大压力。即使我们把这些旧数据删除了，但是底层的数据文件并没有变小。

为什么没有变小？
当删除数据 时，MYSQL 并不会立即回收表空间。被已删除数据的占据的存储空间，以及索引位会空在那里，等待新的数据来弥补这个空缺。
强行回收： OPTIMIZE TABLE
mysql> SHOW PLUGINS \G;

*************************** 43. row ***************************
   Name: partition
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL
CREATE TABLE hash_partition_test (
    id INT,
    pdate INT
)
PARTITION BY HASH(id)
PARTITIONS 4;
CREATE TABLE range_partition_test (
    id INT,
    pdate INT
)
PARTITION BY RANGE (pdate) (
    PARTITION p1 VALUES LESS THAN ( 201702 ),
    PARTITION p2 VALUES LESS THAN ( 201703 ),
    PARTITION p3 VALUES LESS THAN ( 201704 ),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
mysql> select * from range_partition_test;
+------+--------+
| id   | pdate  |
+------+--------+
|    1 | 201701 |
|    2 | 201702 |
|    3 | 201703 |
|    4 | 201704 |
|    5 | 201705 |
+------+--------+

mysql> explain partitions select * from range_partition_test where pdate between 201702 and 201703;
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table                | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | range_partition_test | p2,p3      | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

limit优化

MySQL的 limit m,n 工作原理就是先读取符合where条件的前面m+n条记录，然后抛弃前m条，返回后面n条，所以m越大，偏移量越大，性能就越差。这也是大部分ORM框架生成的分页sql

SELECT * FROM
 t_tel_record t1
INNER JOIN (
 SELECT f_id
 FROM t_tel_record
 WHERE f_qiye_id = xxx
 ORDER BY f_id DESC
 LIMIT 999900, 100
) t2 ON t1.f_id = t2.f_id

min_id = SELECT f_id
FROM t_tel_record
WHERE f_qiye_id = xxx
ORDER BY f_id DESC
LIMIT 999900, 1
 
 
SELECT * FROM
  t_tel_record t1
WHERE f_qiye_id = xxx
AND f_id < {min_id} + 1
ORDER BY f_id DESC
LIMIT 100
第一页：（降序）
SELECT * FROM t_tel_record t1
WHERE f_qiye_id = xxx
ORDER BY f_id DESC LIMIT 100
 
 
获取结果集最大最小id：一般是第一条和最后一条，或者 max_id=max(f_id), min_id=min(f_id)
下一页（如果有）：
SELECT * FROM t_tel_record t1
WHERE f_qiye_id = xxx
AND f_id < {min_id}  -- min_id变量
ORDER BY f_id DESC LIMIT 100
 
 
上一页（如果有）：
SELECT * FROM t_tel_record t1
WHERE f_qiye_id = xxx
AND f_id > {max_id}  -- max_id变量
ORDER BY f_id DESC LIMIT 100
最后一页：（降序）
SELECT * FROM (
  SELECT * FROM t_tel_record t1
  WHERE f_qiye_id = xxx
  ORDER BY f_id ASC LIMIT 100) AS t
ORDER BY f_id DESC
 
 
倒数第二页：(以此类推)
SELECT * FROM (
  SELECT * FROM t_tel_record t1
  WHERE f_qiye_id = xxx
  ORDER BY f_id ASC LIMIT 100, 100) AS t
ORDER BY f_id DESC

insert

-- 将 oldUsers 表中的数据插入到 users 表
INSERT INTO `users`(`email`, `name`, `password`) SELECT `email`, `name`, `password` FROM `oldUsers`;

-- 将 users 表的数据复制到 usersCopy 表
SELECT * INTO `usersCopy` FROM `users`;

-- 创建新表 usersCopy 并将 users 复制过去
CREATE TABLE `usersCopy` AS SELECT * FROM `users`;
-- 查询 products 表中 price 与 number 的乘积并设为别名 total
SELECT `price` * `number` AS `total` FROM `products`;
-- 查询 users 表中的 name 列并将其用括号括起而且设置列别名为 newName
SELECT Concat('(', `name`, ')') AS `newName` FROM `users`;

bindParam 和 bindValue

bindParam 的作用是将变量的引用绑定到预编译的 SQL 语句中，当绑定的变量值改变时 SQL 语句随之改变。

$statement = $pdo->prepare("INSERT INTO `posts` (`id`, `title`, `author`) VALUES (:id, :title, :author)");
$id = 11;
$title = 'new post';
$author = 'unknown';
$statement->bindParam(':id', $id, PDO::PARAM_INT);
$statement->bindParam(':title', $title, PDO::PARAM_STR);
$statement->bindParam(':author', $author, PDO::PARAM_STR);
$statement->execute();
$id = 12;
$title = 'new post2';
$author = 'unknown2';
$statement->execute();
// 执行代码后查询数据库显示如下
// +----+-----------+----------+
// | id | title     | author   |
// +----+-----------+----------+
// |  1 | new post  | unknown  |
// |  2 | new post2 | unknown2 |
// +----+-----------+----------+
警告：由于 bindParam 是绑定的是变量的引用，所以在 foreach 中会出现意料之外的 BUG。示例如下：

$data = [':title' => 'title', ':author' => 'author'];
$statement = $pdo->prepare("INSERT INTO `posts` (`title`, `author`) VALUES (:title, :author)");
foreach ($data as $key => $val) {
    $statement->bindParam($key, $val);
}
$statement->execute();
// 执行代码后查询数据库显示如下
// +----+--------+--------+
// | id | title  | author |
// +----+--------+--------+
// |  1 | author | author |
// +----+--------+--------+
当循环结束后 $val 的值即为数组中最后一个元素的值，而 bindParam 方法绑定的是引用而不是值，所以此时所有的占位符绑定的都是循环结束后的 $val 变量值，因此就有了这个结果。 解决这个问题我们可以用 bindValue 方法实现。

bindValue 的作用是绑定值到预编译的 SQL 语句中，一经绑定 SQL 语句就固定不变。示例如下：

$data = [':title' => 'title', ':author' => 'author'];
$statement = $pdo->prepare("INSERT INTO `posts` (`title`, `author`) VALUES (:title, :author)");
foreach ($data as $key => $val) {
    $statement->bindValue($key, $val);
}
$statement->execute();
// 执行代码后查询数据库显示如下
// +----+--------+--------+
// | id | title  | author |
// +----+--------+--------+
// |  1 | title  | author |
// +----+--------+--------+
这样就解决了这个问题，当然我们还可以在 foreach 中使用引用的方式解决这个问题，但是不推荐。示例如下：

$data = [':title' => 'title', ':author' => 'author'];
$statement = $pdo->prepare("INSERT INTO `posts` (`title`, `author`) VALUES (:title, :author)");
foreach ($data as $key => &$val) {
    $statement->bindParam($key, $val);
}
$statement->execute();
// 执行代码后查询数据库显示如下
// +----+--------+--------+
// | id | title  | author |
// +----+--------+--------+
// |  1 | title  | author |
// +----+--------+--------+

行列转换

#准备示例数据
create table tbl_name (ID int ,mSize varchar(100));
insert into tbl_name values (1,'tiny,small,big');
insert into tbl_name values (2,'small,medium');
insert into tbl_name values (3,'tiny,big');

#用于行列转换循环的自增表
create table incre_table (AutoIncreID int);
insert into incre_table values (1);
insert into incre_table values (2);
insert into incre_table values (3);
 

#实现行列转换的SQLhttp://cenalulu.github.io/mysql/column-row-reverse/
select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1) 
from 
tbl_name a
join
incre_table b
on b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1)
order by a.ID;
select a.ID,substring_index(substring_index(a.mSize,',',b.help_topic_id+1),',',-1) 
from 
tbl_name a
join
mysql.help_topic b
on b.help_topic_id < (length(a.mSize) - length(replace(a.mSize,',',''))+1)
order by a.ID;

MySQL密码

mysql> select password('root'),concat('*',sha1(unhex(sha1('root'))));
+-------------------------------------------+-------------------------------------------+
| password('root')                          | concat('*',sha1(unhex(sha1('root'))))     |
+-------------------------------------------+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | *81f5e21e35407d884a6cd4a731aebfb6af209e1b |
+-------------------------------------------+-------------------------------------------+
5.6以前如果你向MySQL发送了例如create user,grant user ... identified by这样的携带初始明文密码的指令，那么会在binary log中原原本本的被还原出来。
mysql [localhost] {msandbox} (mysql) > create user plain_password identified by 'plain_pass';
Query OK, 0 rows affected (0.00 sec)
用mysqlbinlog查看二进制日志 http://cenalulu.github.io/mysql/myall-about-mysql-password/

shell> mysqlbinlog binlog.000001
# at 106
#150227 23:37:59 server id 1  end_log_pos 223   Query   thread_id=1 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1425051479/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;

change master to master_password=''命令不在这个范畴中。这也就意味着MySQL5.6中仍然使用这样的语法来启动replication时有安全风险的。这也就是为什么5.6中使用带有明文密码的change master to时会有warning提示，具体如下：

slave1 [localhost] {msandbox} ((none)) > change master to master_host='127.0.0.1',master_port =21288,master_user='rsandbox',master_password='rsandbox',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

slave1 [localhost] {msandbox} ((none)) > show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

导出表数据

select * from table into outfile "/home/root/example.sql" where +条件
导入数据库

$ mysqldump -uroot -p --default-character-set=utf8 dbname tablename >  /home/root/example.sql
转载数据

load data local infile "/home/table.txt" into table `table`;

从数据库中获取随机的数据

User::inRandomOrder()->get();
User::inRandomOrder()->first();
User::orderByRaw("RAND()")->get();
use Illuminate\Database\Query\Builder;
//https://learnku.com/laravel/wikis/16199
Builder::macro('orderByRandom', function () {

    $randomFunctions = [
        'mysql'  => 'RAND()',
        'pgsql'  => 'RANDOM()',
        'sqlite' => 'RANDOM()',
        'sqlsrv' => 'NEWID()',
    ];

    $driver = $this->getConnection()->getDriverName();

    return $this->orderByRaw($randomFunctions[$driver]);
});
SELECT 
FROM table AS t1 JOIN (SELECT ROUND(RAND() ((SELECT MAX(id) FROM table)-(SELECT MIN(id) FROM table))+(SELECT MIN(id) FROM table)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
以前用过的，效率不错。

mysql5.7 datetime 默认值为‘0000-00-00 00:00:00’值无法创建问题解决

1、使用root登陆数据库 命令界面执行

select @@sql_mode;
 结果中包含下面两个

NO_ZERO_IN_DATE,NO_ZERO_DATE
2、修改/etc/my.cnf，查找sql_model如果找不到则添加如下代码 

sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
3、重启mysql

/etc/ini.d/mysql restart
简单几步大功告成！

 http://118.25.60.91:9080/article/15 https://learnku.com/articles/16807

原因：

NO_ZERO_IN_DATE,NO_ZERO_DATE是无法默认为‘0000-00-00 00:00:00’的根源。

NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零 

NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

Redis 分布式锁

/**
 *
 * redis 加锁 --单Redis实例实现分布式锁
 *
 * -- 分布式请使用：Redlock:https://github.com/ronnylt/redlock-php
 * -- 详情参考： http://www.redis.cn/topics/distlock.html
 *
 * @package app\common\service
 */
class RedisLock
{
    const LOCK_SUCCESS = 'OK';
    const IF_NOT_EXISTS = 'NX';
    const MILLISECOND_EXPIRE_TIME = 'PX';
    const EXPIRE_TIME = 60000; // millisecond => 60s
    const LOCK_VALUE = 1;

        /**
         * 加锁https://learnku.com/articles/15825/redis-distributed-lock-solution
         * @param $redis object
         * @param $key
         * @param string $expire_time 60000
         */
        public static function lock($redis, $key, $expire_time='')
        {
            if (empty($expire_time)) {
                    $expire_time = self::EXPIRE_TIME;
            }

            $result = $redis->set($key, self::LOCK_VALUE, [self::IF_NOT_EXISTS, self::MILLISECOND_EXPIRE_TIME => $expire_time]);

            return self::LOCK_SUCCESS === (string)$result;
        }

        /**
         * 解锁https://github.com/laravel/framework/blob/5.6/src/Illuminate/Cache/RedisLock.php#L36 
         *
         * 参考： https://github.com/phpredis/phpredis/blob/develop/tests/RedisTest.php
         * @param $redis
         * @param $key
         */
        public static function unlock($redis, $key)
        {
            $lua =<<<EOT
if redis.call("get",KEYS[1]) == ARGV[1] then
    return redis.call("del",KEYS[1])
else
    return 0
end
EOT;
            $result = $redis->eval($lua, array($key, self::LOCK_VALUE), 1);
            return $result;
    }
}
public function acquire()
    {
        $result = $this->redis->setnx($this->name, 1);
        if ($result === 1 && $this->seconds > 0) {
            $this->redis->expire($this->name, $this->seconds);
        }
        return $result === 1;
    }
    /**
     * Release the lock.
     *
     * @return void
     */
    public function release()
    {
        $this->redis->del($this->name);
    }

redis-cli

//https://juejin.im/book/5afc2e5f6fb9a07a9b362527/section/5bcfd27051882577e962f064
$ redis-cli incrby foo 5
(integer) 5
$ redis-cli incrby foo 5
(integer) 10
$ redis-cli info > info.txt
$ wc -l info.txt
     120 info.txt
// -n 2 表示使用第2个库，相当于 select 2
$ redis-cli -h localhost -p 6379 -n 2 ping
PONG
$ cat cmds.txt
set foo1 bar1
set foo2 bar2
set foo3 bar3
......
$ cat cmds.txt | redis-cli
OK
OK
OK
$ redis-cli < cmds.txt
OK
OK
OK
// 间隔1s，执行5次，观察qps的变化
$ redis-cli -r 5 -i 1 info | grep ops
instantaneous_ops_per_sec:43469
instantaneous_ops_per_sec:47460
如果将次数设置为 -1 那就是重复无数次永远执行下去。如果不提供 -i 参数，那就没有间隔，连续重复执行。在交互模式下也可以重复执行指令，形式上比较怪异，在指令前面增加次数

127.0.0.1:6379> 5 ping
PONG
PONG
PONG
PONG
PONG
# 下面的指令很可怕，你的屏幕要愤怒了
127.0.0.1:6379> 10000 info
$ redis-cli rpush lfoo a b c d e f g
(integer) 7
$ redis-cli --csv lrange lfoo 0 -1
"a","b","c","d","e","f","g"
$ redis-cli hmset hfoo a 1 b 2 c 3 d 4
OK
$ redis-cli --csv hgetall hfoo
"a","1","b","2","c","3","d","4"
$ redis-cli --csv -r 5 hgetall hfoo
"a","1","b","2","c","3","d","4"
"a","1","b","2","c","3","d","4"
"a","1","b","2","c","3","d","4"
"a","1","b","2","c","3","d","4"
"a","1","b","2","c","3","d","4"
127.0.0.1:6379> eval "return redis.pcall('mset', KEYS[1], ARGV[1], KEYS[2], ARGV[2])" 2 foo1 foo2 bar1 bar2
OK
127.0.0.1:6379> eval "return redis.pcall('mget', KEYS[1], KEYS[2])" 2 foo1 foo2
1) "bar1"
2) "bar2"
redis-cli --stat 参数来实时监控服务器的状态，间隔 1s 实时输出一次。
$ ./redis-cli --bigkeys -i 0.01
--bigkeys 参数可以很快扫出内存里的大 KEY，使用 -i 参数控制扫描间隔，避免扫描指令导致服务器的 ops 陡增报警。
$ redis-cli --host 192.168.x.x --port 6379 monitor
1539853410.458483 [0 10.100.90.62:34365] "GET" "6yax3eb6etq8:{-7}"
1539853410.459212 [0 10.100.90.61:56659] "PFADD" "growth:dau:20181018" "2klxkimass8w"
如果你想观察主从服务器之间都同步了那些数据，可以使用 redis-cli 模拟从库。

$ ./redis-cli --host 192.168.x.x --port 6379 --slave
SYNC with master, discarding 51778306 bytes of bulk transfer...
$ ./redis-cli --host 192.168.x.x --port 6379 --rdb ./user.rdb
SYNC sent to master, writing 2501265095 bytes to './user.rdb' 远程 rdb 备份

只有query执行时间大于slowlog-log-slower-than的才会定义成慢查询，才会被slowlog进行记录。slowlog-log-slower-than设置的单位是微秒，默认是10000微秒，也就是10毫秒。slowlog-max-len表示慢查询最大的条数，当slowlog超过设定的最大值后，会将最早的slowlog删除，是个FIFO队列。

查看slowlog总条数

127.0.0.1:6379> SLOWLOG LEN

(integer) 4
127.0.0.1:6379> CONFIG GET slowlog-*
1) "slowlog-log-slower-than"
2) "100"
3) "slowlog-max-len"
4) "1024"
127.0.0.1:6379> SLOWLOG GET 11) 1) (integer) 26            // slowlog唯一编号id2) (integer) 1440057815    // 查询的时间戳3) (integer) 47            // 查询的耗时（微秒），如表示本条命令查询耗时47微秒4) 1) "SLOWLOG"            // 查询命令，完整命令为 SLOWLOG GET，slowlog最多保存前面的31个key和128字符2) "GET"

 debug sleep阻塞了set命令，set命令的整体响应时间(R)是1530357微秒，而其服务时间(S)为8微秒，排队延迟(Q)为1530349微秒。
 
 Session-1:
 xxxx:6386> debug sleep 6
 OK
 (6.00s)
 
 Session-2:
 xxxxx:6386> set a b
 OK
 (1.53s)
 127.0.0.1:6379> SLOWLOG RESET
 OK
 127.0.0.1:6379> SLOWLOG LEN
 (integer) 0
 redis-cli monitor打印出所有sever接收到的命令以及其对应的客户端地址
 
 $ redis-cli monitor
 redis-cli --stat查看当前连接的客户端数，连接数等
 按前缀/模式批量删除keys
 redis-cli keys david* | xargs redis-cli del
 127.0.0.1:6379> eval "local keys = redis.call('keys',KEYS[1]) for i,v in ipairs(keys) do redis.call('del',v) end" 1 david*

每个分组的最后一条记录

select * from t_tmp group by FCityId order by FUpdateTime desc;` 结果却是错误的！http://blog.text.wiki/2015/04/03/retrieve-the-last-record-in-each-group.html
SELECT * FROM (SELECT * FROM t_tmp ORDER BY FUpdateTime DESC)tmptable GROUP BY FCityId ORDER BY FUpdateTime DESC;

SELECT m1. * FROM t_tmp m1 LEFT JOIN t_tmp m2 ON ( m1.FCityId = m2.FCity
Id AND m1.FId < m2.FId )  WHERE m2.FId IS NULL ORDER BY FUpdateTime DESC LIMIT 0
, 30;

超过经理收入的员工

 SELECT e.Name AS Employee
    FROM Employee e, Employee e1
    WHERE e.ManagerId = e1.id
        AND e.Salary > e1.Salary;
Department	Employee	Salary
IT	Max	90000
Sales	Henry	80000
https://learnku.com/articles/24492

MySQL 之事务隔离级别

MySQL事务主要用于处理一个包含操作量比较大、复杂的业务。比如说，删除一个学生，我们除了要删除该学生的基本信息，同时也要删除考试记录、违规记录等。诸多的操作组成一个事务。事务是用来管理insert、update、delete基本指令的。当MySQL使用innodb引擎的前提下才支持事务操作
https://learnku.com/articles/24581#topnav https://www.itcodemonkey.com/article/13020.html
隔离性的类别

read uncommitted | 读未提交
read committed | 读已提交
repeatable read | 可重复读
serializable | 串行化
在MySQL数据库中，引擎默认使用repeatable read

# SELECT @@tx_isolation 或者 SELECT @@transaction_isolation
# MySQL 8.x 
# transaction_isolation在MySQL 5.7.20中添加了作为别名 tx_isolation，现已弃用，并在MySQL 8.0中删除。
# 应调整应用程序transaction_isolation以优先使用 tx_isolation。
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.01 sec)

## 将事务隔离的模式设置为[可重复读]
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

## (1)事务A读取scor数据表
mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    75 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

## (2)事务B新增删除一条数据并提交
mysql> delete from score where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

## (3)事务A再次读取score数据表
mysql> select * from score;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | alicfeng |    75 |
|  2 | feng     |   100 |
|  3 | alic     |    90 |
+----+----------+-------+
3 rows in set (0.00 sec)

MySQL性能突发事件问题排查技巧

https://mp.weixin.qq.com/s/qCRfxIr1RoHd9i8-Hk8iuQ
top — Linux 系统进程监控
iostat
 vmstat — 虚拟内存统计
[xxx@localhost ~]$ vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0 945448 437836 618928 5950612    1    0    13   685    0    0 16  2 82  0  0
 lsof — 打开文件列表
 [finance@localhost ~]$ lsof -c php-fpm
 COMMAND   PID    USER   FD      TYPE     DEVICE SIZE/OFF       NODE NAME
 php-fpm  3893    root  cwd   unknown                                /proc/3893/cwd (readlink: Permission denied)
 php-fpm  3893    root  rtd   unknown                                /proc/3893/root (readlink: Permission denied)
 php-fpm  3893    root  txt   unknown                                /proc/3893/exe (readlink: Permission denied)
 php-fpm  3893    root NOFD                                          /proc/3893/fd (opendir: Permission denied)
 php-fpm  3894 root  cwd       DIR        8,2     4096      90113 /root
 php-fpm  3894 root  rtd       DIR        8,2     4096          2 /
 php-fpm  3894 root  txt       REG        8,7 37943970     264189 /data/php55/sbin/php-fpm
tcpdump — 网络数据包分析器
netstat — 网络统计 监控网络数据包传入和传出的统计界面的命令行工具
[XXX@localhost ~]$ netstat -a |more
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State
tcp        0      0 127:6379 *:*                         LISTEN
tcp        0      0 localhost:6379              *:*                         LISTEN
tcp        0      0 localhost:9004              *:*                         LISTEN

SHOW PROCESSLIST; —当前MySQL数据库的运行的所有线程
INNODB_TRX; — 当前运行的所有事务
mysql> select *from information_schema.INNODB_TRX\G
INNODB_LOCKS; — 当前出现的锁
select *from information_schema.INNODB_LOCKS\G
INNODB_LOCK_WAITS; — 锁等待的对应关系计
mysql> select *from information_schema.INNODB_LOCK_WAITS\G
SHOW OPEN TABLES where In_use >0; — 当前打开表
SHOW ENGINE INNODB STATUS  \G; —Innodb状态
SHOW STATUS LIKE  'innodb_row_lock_%'; — 锁性能状态
SQL语句EXPLAIN; — 查询优化器

Mysql 批量写入数据

https://my.oschina.net/famoustone/blog/856736
public function insertTest(){

		set_time_limit(0);   //防止超300s 500错误
	
		$t1 = microtime(true);
	
	
		//随机插入num条
		for ($i=1; $i<=200000; $i++){
			
			$result = $this->db->insert('myisam', ['value' => uniqid().$i]);
		}

		//程序运行时间
		$t2 = microtime(true);
		echo '耗时：'.round($t2-$t1,3).'秒<br>';
		echo '内存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>";
	
	}
	    167秒，时间20w 数据 Myisam要 接近3分钟了。
    
my.cnf 当innodb_flush_log_at_trx_commit=2时, 每次事务提交时, MySQL会把log buffer的数据写入log file, 但          不会主动触发flush(刷新到disk)操作同时进行. 然而, MySQL会每秒执行一次flush(刷新到disk)操作.
public function insertTest(){

		set_time_limit(0);   //防止超300s 500错误
	
		$t1 = microtime(true);
	
	
		$sql = "insert into innodb (value) VALUES";
		//随机插入num条
		for ($i=1; $i<=200000; $i++){
			
			$val = uniqid().$i;
				
			$sql .= "('{$val}'),";
			
		}
		
		$sql = substr($sql,0,-1);
		
		//程序运行时间
		$t2 = microtime(true);
		echo '循环耗时：'.round($t2-$t1,3).'秒<br>';
		
		$this->db->query($sql);  //批量插入
		
		$t3 = microtime(true);
		echo '插入耗时：'.round($t3-$t2,3).'秒<br>';
		
		echo '内存消耗：'.round(memory_get_usage()/1048576,2)." M<br/>";
	
	}拼接语句可能会报错
     设置一下
     
     max_allowed_packet = 500M
     
     允许mysql 接受数据包大小。

Redis延时队列

https://rsy.me/posts/redis-application-in-web-development/?hmsr=toutiao.io&utm_medium=toutiao.io&utm_source=toutiao.io
$queueKey = "queue";

// 生产者
$redis->rpush($queueKey, $data)

// 消费者
while (true) {
    $data = $redis->lpop($queueKey);
    if (null === $data) {
        usleep(100000);
        continue;
    }
    // 业务逻辑
    ...
}

$queueKey = "queue";

// 生产消息

// 消费时间, 这里设置为1小时候
$consumeTimestamp = time() + 3600;
// $data需要添加随机串前缀(or后缀)，防止出现重复member被丢弃
$data = $data . md5(uniqid(rand(), true));
$redis->zadd($queueKey, $consumeTimestamp, $data);

// 消费消息
while (tue) {
    $arrData = $redis->zrangebyscore($queueKey, 0, time());
    if (!$arrData) {
        usleep(100000);
        continue;
    }
    // 业务逻辑
    foreach ($arrData as $data) {
        $data = substr($data, 0, strlen($data) - 32);
        
        // 消费$data

    }
}

redis 分布式锁

$lockStatus = $redis->setnx($lockKey, 1);
if (1 === $lockStatus) {
    // 加锁成功，为锁设置超时时间
    $redis->expire($lockKey, 300);

    // 进行后续操作

} elseif (0 === $lockStatus) {
    // 加锁失败
} else {
    // 其他异常
}
$lockStatus = $this->redis->set($lockKey, 1, "EX", 30, "NX");
if ("OK" === $lockStatus) {
    // 加锁成功，可进行后续操作
    
    //业务逻辑执行完毕，释放锁
    $this->redis->del($lockKey);

} elseif (null === $lockStatus) {
    // 加锁失败
}
$lockToken = md5(uniqid(rand(), true));
// 此处超时时间根据具体业务逻辑配置
$expire = rand(280, 320);
$lockStatus = $this->redis->set($lockKey, $lockToken, "EX", $expire, "NX");
if ("OK" === $lockStatus) {
    // 加锁成功，可进行后续操作
    
    // 业务逻辑执行完毕，释放锁
    // 删除锁之前需要判断是否是自己上的锁
    $currentToken = $this->redis->get($lockKey);
    if ($currentToken === $lockToken) {
        $this->redis->del($lockKey);
    }

} elseif (null === $lockStatus) {
    // 加锁失败
}

redis 无法启动

redis突然挂掉后，无法启动，查看log日志，发现报Short read or OOM loading DB. Unrecoverable error, aborting now
[root@localhost ~]# rm -f /var/lib/redis/dump.rdb  
[root@localhost ~]# rm -f /var/run/redis.pid  
[root@localhost ~]# service redis start

redis 导出 导入

[root@localhost tank]# yum install ruby rubygems ruby-devel   //安装rubygems 以及相关包  
  
[root@localhost tank]# gem sources -a http://ruby.taobao.org/   //源，加入淘宝，外面的源不能访问  
http://ruby.taobao.org/ added to sources  
  
[root@localhost tank]# gem install redis-dump -V   //安装redis-dump 
查看复制打印?
[root@localhost tank]# telnet 127.0.0.1 6379 //telnet到redis  
Trying 127.0.0.1...  
Connected to 127.0.0.1.  
Escape character is '^]'.  
set test 11 //设置一个值  
+OK  
get test //取值  
$2  
11  
  
[root@localhost tank]# redis-dump -u 127.0.0.1:6379 >test.json //导出数据  
[root@localhost tank]# telnet 127.0.0.1 6379 //telnet到redis  
Trying 127.0.0.1...  
Connected to 127.0.0.1.  
Escape character is '^]'.  
flushall //请空所有数据  
+OK  
keys * //查看已清空  http://blog.51yip.com/nosql/1656.html
*0  
  
[root@localhost tank]# < test.json redis-load //导入数据  
  
[root@localhost tank]# telnet 127.0.0.1 6379  
Trying 127.0.0.1...  
Connected to 127.0.0.1.  
Escape character is '^]'.  
keys * //已导入成功  
*1  
$4  
test

优化not in 和<>查询

http://fanqieto.top/2017/11/26/mysql%E6%9F%A5%E8%AF%A2%E4%BC%98%E5%8C%96/
select customer_id,first_name,last_name,email
from customer
where customer_id
not in (select customer_id from payment)
改为：

select a.customer_id,a.first_name,a.last_name,a.email 
from customer a
left join payment b on a.customer_id = b.customer_id
where b.customer_id is null m
使用汇总表优化查询
例子

select count(*) from product_comment where product_id = 999
改为

create table product_comment_cnt(product_id int, cnt int);
select sum(cnt) from (
select cnt from product_comment_cnt where product_id = 999
union all
select count(*) from product_comment where product_id = 999
and timestr > date(now())
) a

Redis的n种妙用

setnx key value，当key不存在时，将 key 的值设为 value ，返回1。若给定的 key 已经存在，则setnx不做任何动作，返回0。



当setnx返回1时，表示获取锁，做完操作以后del key，表示释放锁，如果setnx返回0表示获取锁失败
# 实现方式一
# 一直往list左边放
lpush key value 
# key这个list有元素时，直接弹出，没有元素被阻塞，直到等待超时或发现可弹出元素为止，上面例子超时时间为10s
brpop key value 10 

# 实现方式二
rpush key value
blpop key value 10
# 参加抽奖活动
sadd key {userId} 

# 获取所有抽奖用户，大轮盘转起来
smembers key 

# 抽取count名中奖者，并从抽奖活动中移除
spop key count 

# 抽取count名中奖者，不从抽奖活动中移除
srandmember key count
# 1001用户给8001帖子点赞
sadd like::8001 1001

# 取消点赞
srem like::8001 1001

# 检查用户是否点过赞
sismember like::8001 1001 

# 获取点赞的用户列表
smembers like::8001 

# 获取点赞用户数
scard like::8001 
# 返回sevenSub和qingSub的交集，即seven和青山的共同关注
sinter sevenSub qingSub -> {mic,james}

# 我关注的人也关注他,下面例子中我是seven
# qing在micSub中返回1，否则返回0
sismember micSub qing
sismember jamesSub qing

# 我可能认识的人,下面例子中我是seven
# 求qingSub和sevenSub的差集，并存在sevenMayKnow集合中
sdiffstore sevenMayKnow qingSub sevenSub -> {seven,jack}
# 将拯救者y700P-001和ThinkPad-T480这两个元素放到集合brand::lenovo
sadd brand::lenovo 拯救者y700P-001 ThinkPad-T480
sadd screenSize::15.6 拯救者y700P-001 机械革命Z2AIR
sadd processor::i7 拯救者y700P-001 机械革命X8TIPlus

# 获取品牌为联想，屏幕尺寸为15.6，并且处理器为i7的电脑品牌(sinter为获取集合的交集)
sinter brand::lenovo screenSize::15.6 processor::i7 -> 拯救者y700P-001
# user1的用户分数为 10
zadd ranking 10 user1
zadd ranking 20 user2

# 取分数最高的3个用户
zrevrange ranking 0 2 withscores
Redis 的持久化机制有两种，第一种是快照（RDB RDB是隔一段时间来备份数据），第二种是 AOF 日志。快照是一次全量备份，AOF 日志是连续的增量备份。快照是内存数据的二进制序列化形式，在存储上非常紧凑，而 AOF 日志记录的是内存数据修改的指令记录文本。AOF 日志在长期的运行过程中会变的无比庞大，数据库重启时需要加载 AOF 日志进行指令重放，这个时间就会无比漫长。所以需要定期进行 AOF 重写，给 AOF 日志进行瘦身。
假如客户端每秒发送5000个请求，其中4000个为黑客的恶意攻击，即在数据库中也查不到。举个例子，用户id为正数，黑客构造的用户id为负数，

如果黑客每秒一直发送这4000个请求，缓存就不起作用，数据库也很快被打死。
如何解决缓存穿透



查询不到的数据也放到缓存，value为空，如set -999 “”



总而言之，缓存雪崩就是缓存失效，请求全部全部打到数据库，数据库瞬间被打死。缓存穿透就是查询了一个一定不存在的数据，并且从存储层查不到的数据没有写入缓存，这将导致这个不存在的数据每次请求都要到存储层去查询，失去了缓存的意义
https://www.itcodemonkey.com/article/12951.html

防止库存超卖

https://www.fanhaobai.com/2017/09/record-question-1.html

$num = $redis->incr($key);
if ($num < $max) {
    //入抢购成功队列，异步去执行抢购成功逻辑
} else {
    //不好意思呢，已经被抢完了
}
不知道你有没有闻到这段代码的坏味道，在大部分情况下会如你所想地运行，但是特殊场景下会 出现判断失效 的逻辑问题，例如：

1、key 由于某些原因失效了；
2、Incr 操作失败了，不会抛异常并返回 false；
通过日志定位到 Incr 操作问题，便 Telnet 连接到线上 Redis 服务，发现了异常情况：

# 查看值
GET key
100
# 尝试修改
INCR key
READONLY You can't write against a read only slave

INFO
# Replication
role:slave
可以看出来，该连接的机器目前处于从机状态，不可写操作，所以 Incr 操作返回 false，同时 PHP 不同类型比较会存在隐式转化，所以false < $num恒成立，导致计数器失效。

增加计数器容错处理：

$num = $redis->incr($key);
if ($num > 0 && $num < $max) {
    //入抢购成功队列，异步去执行抢购成功逻辑
} else {
    //不好意思呢，已经被抢完了
}
然后，切换 Redis 源到高可用集群（Codis），测试并重新上线，第二日的抢购已经正常，看着 Cat 上流量逐渐平稳，心里也踏实了。

Redis管道提升性能

Redis 的 管道 （pipelining）是用来打包多条无关命令批量执行，以减少多个命令分别执行带来的网络交互时间。在一些批量操作数据的场景，使用管道可以显著提升 Redis 的读写性能

https://www.fanhaobai.com/2017/08/redis-pipelining.html
# 安装nc命令
$ yum install nc
# nc打包多个命令
$ (printf "PING\r\nPING\r\nPING\r\n") | nc localhost 6379
# 响应
+PONG
+PONG
+PONG
$start = nowTime();
foreach (range(1, 1000) as $id) {
    $user[] = $redis->hgetAll($keyPrex.$id);
}
echo '时间：', nowTime() - $start, 'ms', PHP_EOL;

时间：39ms
$start = nowTime();
$redis->multi(Redis::PIPELINE);
foreach (range(1, 1000) as $id) {
    //返回资源id相同的socket资源，并未执行命令
    $redis->hgetAll($keyPrex.$id);  
}
$user = $redis->exec();
echo '时间：', nowTime() - $start, 'ms', PHP_EOL;

时间：6ms
在批量操作（查询和写入）数据时，我们应尽量避免多次跟 Redis 的网络交互。这时，可以使用管道实现，也可以 Redis 内嵌 Lua 脚本实现。需要注意的是：

管道只适用于无因果关联的多命令操作，否则就需要借助 Lua 脚本实现批量操作；
在实际应用中，Redis 往往不可能是单机部署，如果想要在集群中使用管道，可以部署为一主多从架构，此时所有节点的数据都一致，随机选取节点使用管道即可；
总结

在批量获取数据时，尽管使用 Redis 的管道性能会显著提升，但是使用管道时 Redis 会缓存之前命令的结果，最后一并输出给终端，因此所打包的命令不宜太多，否则内存使用会很严重。

处理重复数据

CREATE TABLE `allowed_user`
(
  `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `uid` VARCHAR(36)  DEFAULT ''  NOT NULL,
  `last_time` TIMESTAMP  NOT NULL,
  UNIQUE (uid)
)

INSERT INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-03 19:31:15')
REPLACE INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:31:15')
注意执行影响行数为 2
INSERT INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:31:15') ON DUPLICATE  KEY UPDATE `last_time` = '2017-09-01 19:40:15'
SQL 执行影响记录数为 2 条
INSERT IGNORE INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:41:15')
try {
    $user = $model->query("SELECT * FROM `allowed_user` WHERE `uid` = '8e9b8c14-fae8-49d4-bbac-a733c09ec82f'");
    if (user) {
       $model->exec("UPDATE `allowed_user` SET `last_time` = '2017-09-01 19:50:15' WHERE `uid` = '8e9b8c14-fae8-49d4-bbac-a733c09ec82f'");
    } else {
       $model->exec("INSERT INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:50:15'");
    }
} catch(Exception $e) {

}
这段代码通过程序逻辑去试图保证唯一性，但是在高并发情况下，并不能保证数据唯一性，因为不是原子性操作，修改后为：https://www.fanhaobai.com/2017/09/mysql-repetition-deal.html

try {
    $model->exec("INSERT INTO `allowed_user` (`uid`, `last_time`) VALUES ('8e9b8c14-fae8-49d4-bbac-a733c09ec82f', '2017-09-01 19:50:15') ON DUPLICATE  KEY UPDATE `last_time` = '2017-09-01 19:50:15'");
} catch(Exception $e) {

}
替换掉异常数据的特殊换行符即可。

UPDATE table_a SET uid = REPLACE(REPLACE(uid, CHAR(10), ''), CHAR(13), '');
在 MySQL 中，CHAR(10) 和 CHAR(13) 分别代 换行符 和 回车符，这里都替换掉。

lua 抢购场景

$key = 'number:string';
$redis = new Redis();
$number = $redis->get($key);
if ($number <= 0) {
    return 0;
}
$redis->decr($key);
return $number--;
local key = 'number:string'
local number = tonumber(redis.call("GET", key))
if number <= 0 then
   return 0
end
redis.call("DECR", key)
return number--
Redis 中嵌入 Lua 脚本，所具有的几个特性为：

原子操作：Redis 将整个 Lua 脚本作为一个原子执行，无需考虑并发，无需使用事务来保证数据一致性；
高性能：嵌入 Lua 脚本后，可以减少多个命令执行的网络开销，进而间接提高 Redis 性能；
可复用：Lua 脚本会保存于 Redis 中，客户端都可以使用这些脚本；
Redis 中执行 Lua 脚本都是以原子方式执行，所以是原子操作。另外，redis-cli 命令行客户端支持直接使用--eval lua_file参数执行 Lua 脚本。
> EVAL "return {KEYS[1],KEYS[2],ARGV[1],ARGV[2]}" 2 key1 key2 first second
1) "key1"
2) "key2"
3) "first"
4) "second"
> EVAL "return 3.333" 0
(integer) 3
> EVAL "return 'fhb'" 0
"fhb"
> EVAL "return {'fhb', 'lw', 'lbf'}" 0
1) "fhb"
2) "lw"
3) "lbf"
> EVAL "return false" 0
(nil)
> EVAL "return true" 0
(integer) 1
通过 Lua 实现一个针对用户的 API 访问速率控制，Lua 代码如下：

local key = "rate.limit:string:" .. KEYS[1]
local limit = tonumber(ARGV[1])
local expire_time = tonumber(ARGV[2])
local times = redis.call("INCR", key)
if times == 1 then
    redis.call("EXPIRE", key, expire_time)
end
if times > limit then
    return 0
end
return 1
KEYS[1] 可以用 API 的 URI + 用户 uid 组成，ARGV[1] 为单位时间限制访问的次数，ARGV[2] 为限制的单位时间。
> EVAL "return redis.call('SET', 'name', 'fhb')" 0
> EVAL "return redis.pcall('GET', 'name')" 0
"fhb"
https://www.fanhaobai.com/2017/09/lua-in-redis.html

缓存与数据库双写一致性问题

周所周知，在项目性能优化、提升的时候，我们引进了一个缓存的概念，即一款缓存数据的技术，项目在最初期架构规划时都会引进的一个组件。使用缓存有很多好处：加快请求的响应速度、减少数据库的交互与浪费大量的IO操作等，但是在某些场景下使用缓存也有可能会造成雪崩、剧透、数据不一致等问题，我们研究下使用缓存会导致有哪些数据不一致的情况发生以及在哪些场景会使用哪些具体的解决方案，首先我们必然还是会使用缓存的。

缓存更新策略

先更新数据库再更新缓存

浪费资源
每次去更新数据库再更新缓存都是需要申请CPU进行数据库的修改的，同时倘若数据的修改比较频繁以及数据的读操作却又比较少的时候，这种策略会导致出现冷数据的情况。
数据脏读
如果两个操作同时对数据进行操作时，举个栗子：线程A 在线程B更新数据库成功后、更新缓存成功之前读取到数据，也就是读取到了缓存的旧数据。
该策略比较适合更新的频次比较少的场景下，比如博客的文章、基础数据、个人信息等场景。
先更新数据库再删除缓存

数据脏读
一个请求处理中过程中，倘若数据库更新成功了但是缓存更新失败了，那么后面的请求读取的数据都是旧数据、则脏数据。可以通过缓存过期时间定义缓存的有效期(推荐)，或者使用消息队列在删除缓存失败的时候再次异步更新缓存，直到成功为止，这两种方案都是尽可能减少读取脏数据的方案，还有一种方案可以完全解决数据脏读，就是异步请求串行化，一个字锁，但是会增加业务处理的时间甚至会出现死锁的情况。
这种缓存操作的策略使用的情况被使用的比较多、使用的场景也比较广泛。
先删除缓存再更新数据库
数据脏读
与第二种情况类似，但是更容易出现数据脏读，比如：删除缓存失败更新数据库成功(一般的业务可能在缓存修改失败后不再进行数据库的更新了)、线程B读取了线程A已经成功删除了缓存后更新数据库之前读取了脏数据并且也导致缓存的数据也是旧数据。解决方案还是使用缓存过期时间或者消息队列，在缓存过期的时候务必要注意雪崩的问题，比如大批量数据的过期时间几乎集中在同一个时间点上，容易造成雪崩。

VARCHAR(N) 中的 N 代表的是字符数，而不是字节数。使用 UTF8 存储 255 个汉字 Varchar(255)=765 个字节。过大的长度会消耗更多的内存
 优先选择符合存储需要的最小的数据类型
 方法
 1、将字符串转换成数字类型存储，如：将IP地址转换成整形数据。
 
 MySQL 提供了两个方法来处理 IP 地址
 
 inet_aton 把ip转为无符号整型(4-8位)
 inet_ntoa 把整型的ip转为地址
 插入数据前，先用 inet_aton 把 IP 地址转为整型，可以节省空间。显示数据时，使用 inet_ntoa 把整型的 IP 地址转为地址显示即可。
 
 2、对于非负型的数据（如自增 ID、整型 IP）来说，要优先使用无符号整型来存储,因为无符号相对于有符号可以多出一倍的存储空间。
 SIGNED INT -2147483648~2147483647
 UNSIGNED INT 0~4294967295
 同财务相关的金额类数据必须使用 decimal 类型
 
 非精准浮点：float，double
 精准浮点：decimal
 Decimal 类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节。可用于存储比 bigint 更大的整型数据。
  避免数据类型的隐式转换
 
 隐式转换会导致索引失效。如：
 select name,phone from customer where id = '111';
 https://learnku.com/articles/25020#topnav

MongoDB读写分离

PHP7中的MongoDB\Driver\ReadPreference MongoDB读写分离（Read Preference）的几种模式

const integer RP_PRIMARY = 1 ;
const integer RP_PRIMARY_PREFERRED = 5 ;
const integer RP_SECONDARY = 2 ;
const integer RP_SECONDARY_PREFERRED = 6 ;
const integer RP_NEAREST = 10 ;

primary
主节点，默认模式，读操作只在主节点，如果主节点不可用，报错或者抛出异常。

primaryPreferred
首选主节点，大多情况下读操作在主节点，如果主节点不可用，如故障转移，读操作在从节点。

secondary
从节点，读操作只在从节点， 如果从节点不可用，报错或者抛出异常。

secondaryPreferred
首选从节点，大多情况下读操作在从节点，特殊情况（如单主节点架构）读操作在主节点。

nearest
最邻近节点，读操作在最邻近的成员，可能是主节点或者从节点
在使用mongo副本集的时候就在想，这些副本不用来读太浪费了，再翻阅php的mongodb驱动，发现一个美好的readPreference，可以设定读取的优先级，其中就有优先读取副本，甚至还可以设定读取最小网络延迟的节点，具体可以参考：http://php.net/manual/zh/mongodb-driver-readpreference.construct.php
http://imhuchao.com/918.html
写入安全级别的使用
W选项

0：非确认式写入

1：确认式写入

说明：这个级别下，对副本级只对主库做确认写入

2：副本级确认式写入

说明：这个级别下，副本集第一个slave写入成功后就响应给client

majority：复制级更多slave写入成功后，在响应给client
$filter = array();
$options = array(
    /* Only return the following fields in the matching documents */
    "projection" => array(
        "title" => 1,
        "article" => 1,
    ),
    "sort" => array(
        "views" => -1,
    ),
    "modifiers" => array(
        '$comment'   => "This is a query comment",
        '$maxTimeMS' => 100,
    ),
);

$query = new MongoDB\Driver\Query($filter, $options);

$manager = new MongoDB\Driver\Manager("mongodb://localhost:27017");
$readPreference = new MongoDB\Driver\ReadPreference(MongoDB\Driver\ReadPreference::RP_PRIMARY);
$cursor = $manager->executeQuery("databaseName.collectionName", $query, $readPreference);

foreach($cursor as $document) {
    var_dump($document);
}

vi config/database.php
"mongodb" => [
            'driver' => 'mongodb',
            'host' => env('MONGO_DEFAULT_HOST', '172.1.7.21'),
            'port' => env('MONGO_DEFAULT_PORT', 27017),
            'database' => env('MONGO_DEFAULT_DATABASE', 'app'),
            'username' => env('MONGO_DEFAULT_USER', ''),
            'password' => env('MONGO_DEFAULT_PASSWORD', ''),
            'options' => [
                'replicaSet' => env('MONGO_DEFAULT_REPLICASET'),
                'readPreference'=> env('MONGO_READ_PREFERENCE', 'nearest'),
            ]
        ],
vi  /vendor/jenssegers/mongodb/src/Jenssegers/Mongodb/Connection.php:28
public function __construct(array $config)
    {
        $this->config = $config;

        // Build the connection string
        $dsn = $this->getDsn($config);

        // You can pass options directly to the MongoClient constructor
        $options = array_get($config, 'options', []);

        // Create the connection
        $this->connection = $this->createConnection($dsn, $config, $options);
        dd($this->connection->getReadPreference());
        array:1 [▼
          "type" => "nearest"
        ]
        // Select database
        $this->db = $this->connection->{$config['database']};

        $this->useDefaultPostProcessor();
    }

Laravel 分组获取最新记录

///https://learnku.com/articles/20626
select * from (select * from project where user_id = :user_id order by id desc) as a group by a.name order by id desc;
select * from (select * from project where user_id = :user_id order by id desc limit 10000) as a group by a.name order by id desc;
public function projectList(Request $request){
        $limit = $request->get('limit',10);
        $user_id = $request->get('user_id',null);

        $sub_query = Project::where('user_id',$user_id)->orderBy('id','desc')->limit(1000);//子查询

        $results = Project::select('*')
                ->from(DB::raw('('.$sub_query->toSql().') as a')) //from() 类似与 DB::table(), toSql()得到带 ? 号的执行 sql 语句
                ->mergeBindings($sub_query->getQuery())//mergeBindings() 合并绑定参数,getQuery()获得具体值
                ->groupBy('name')
                ->orderBy('id','desc')
                ->paginate($limit);

        return $this->pageDate($results);
    }

将分组无结果的记录显示为0

http://hshine.net/article/12 
SELECT
    ISNULL( SUM ( u_table1917.field5 ), 0 ) As val,
    u_table1916.companycode 
FROM
    u_table1917
    JOIN u_table1916 ON u_table1917.field3= u_table1916.field6 
WHERE
    u_table1916.field3 LIKE '2018%' 
    AND u_table1916.companycode IN ( 'GY00051', 'GY00071', 'GY00072', 'GY00073' ) 
GROUP BY
    u_table1916.companycode
    
    SELECT DISTINCT Ma.companycode,ISNULL(Sub.val,0) FROM --此处使用ISNULL对结果进行判空处理 
    (
    SELECT
        u_table1916.companycode 
    FROM
        u_table1917
        JOIN u_table1916 ON u_table1917.field3= u_table1916.field6
    --此处使用LEFT JOIN 关联，因为左表是包括所有数据记录的，而右表只包括符合条件的，这样就能获得
    --将所有的结果来进行分组，再加上ISNULL的处理，就可以显示聚合为0的记录
    ) AS Ma LEFT JOIN 
    (
    SELECT
        SUM ( u_table1917.field5 ) As val,
        u_table1916.companycode 
    FROM
        u_table1917
        JOIN u_table1916 ON u_table1917.field3= u_table1916.field6 
    WHERE
        u_table1916.field3 LIKE '2018%' 
    GROUP BY
        u_table1916.companycode
    ) AS Sub ON Ma.companycode=Sub.companycode

MISCONF Redis is configured to save RDB snapshots

使用redis-cli，你可以这样做：

 
CONFIG SET dir /tmp/some/directory/other/than/var
CONFIG SET dbfilename temp.rdb
千万不要使用https://blog.yiranzai.cn/posts/901/

 
config set stop-writes-on-bgsave-error no
忽视这些错误并不是解决问题的办法。

我的解决方案是

修改配置文件，重启服务
 
$ mkdir /usr/local/redis/db
$ vim redis.conf
# 第263行左右  设置快照文件目录，切勿设置成一个redis用户没有权限的目录
dir /usr/local/redis/db/
$ chown -R redis:redis /usr/local/redis

MySQL数据库主从复制

$ systemctl start mysqld.service
$ firewall-cmd --zone=public --add-port=80/tcp --permanent
$ firewall-cmd --reload
master服务器配置：https://blog.yiranzai.cn/posts/5420/
$ vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin # 启用二进制日志

slave服务器配置：
$ vim /etc/my.cnf
[mysqld]
log_bin           = mysql-bin
server_id         = 2
relay_log         = mysql-relay-bin
log_slave_updates = 1
read_only         = 1
slave-skip-errors = all   #忽略因复制出现的所有错误

systemctl restart mysqld.service
mysql> MySQL -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* to 'user'@'192.168.0.124' identified by 'password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
mysql> UNLOCK TABLES;

mysql> show maser status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |    24364 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)
mysql> change master to
 -> master_host='192.168.0.123',
 -> master_user='user',
 -> master_password='password',
 -> master_log_file='mysql-bin.000004',
 -> master_log_pos=24364;

mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.169.0.123
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 24364
               Relay_Log_File: mysql-relay-bin.000292
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

Redis 快速实现签到统计

https://learnku.com/articles/25181
在你想要的位置操作字节值，比如说用户 3 在 3月13号 签到了，那么 setbit(20190313, 3 ,1) 就可以实现签到功能了，这里的 offset 就是3
 $dayKey = 'login:'.\date('Ymd',\time());
$redis->bitop('AND', 'threeAnd', 'login:20190311', 'login:20190312', 'login:20190313');
echo "连续三天都签到的用户数量：" . $redis->bitCount('threeAnd');

$redis->bitop('OR', 'threeOr', 'login:20190311', 'login:20190312', 'login:20190313');
echo "三天中签到用户数量（有一天签也算签了）：" . $redis->bitCount('threeOr');

$redis->bitop('AND', 'monthActivities'', $redis->keys('login:201903*'));
echo "连续一个月签到用户数量：" . $redis->bitCount('monthActivities');

echo "当前用户指定天数是否签到：" . $redis->getbit('login:20190311', $this->user->id);

MySQL分组查询TOP N的实践和踩坑

https://blog.yiranzai.cn/posts/991/ 三个字段 课程 学生 成绩，如何取每门课程成绩 top3 的学生
CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `course` varchar(20) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `course` (`course`,`score`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
ysql> INSERT INTO `test2`(name,course,score) VALUES
('a1', 'a', 50),
('a2', 'a', 50),
('a3', 'a', 50),
('a4', 'a', 40),
('a5', 'a', 40),
('a6', 'a', 40),
('a7', 'a', 30),
('a8', 'a', 30),
('a9', 'a', 30);
mysql> select * from test2;
+----+------+--------+-------+
| id | name | course | score |
+----+------+--------+-------+
|  1 | a1   | a      |    50 |
|  2 | a2   | a      |    50 |
|  3 | a3   | a      |    50 |
|  4 | a4   | a      |    40 |
|  5 | a5   | a      |    40 |
|  6 | a6   | a      |    40 |
|  7 | a7   | a      |    30 |
|  8 | a8   | a      |    30 |
|  9 | a9   | a      |    30 |
+----+------+--------+-------+
9 rows in set (0.06 sec)
select *
from test1 a
where 3>(select count(*) from test1 where course=a.course and score>a.score)
order by a.course,a.score desc;

case when

SELECT
    CASE
    WHEN user_sleep_time <= 1536595176
        AND user_sleep_time > 1535212776 THEN
    'twoWeekdsAgo'
    WHEN user_sleep_time <= 1535212776
        AND user_sleep_time > 1532620776 THEN
    'thirtyDaysAgo'
    WHEN user_sleep_time <= 1532620776
        AND user_sleep_time > 1530028776 THEN
    'sixtyDaysAgo'
    WHEN user_sleep_time <= 1530028776
        AND user_sleep_time > 1527436776 THEN
    'ninetyDaysAgo'
    WHEN user_sleep_time <= 1527436776
        AND user_sleep_time > 1524844776 THEN
    'oneHundredAndTwentyDaysAgo'
    WHEN user_sleep_time <= 1524844776
        AND user_sleep_time > 1522252776 THEN
    'oneHundredAndFiftyDaysAgo'
    WHEN user_sleep_time <= 1522252776 THEN
    'oneHundredAndEightyDaysAgo'
    ELSE 0
    END sleep, COUNT(*) AS userCount
FROM `user_sleep_relation`
WHERE `user_recall_time` < 1536595176
GROUP BY  `sleep`  
https://learnku.com/articles/17547

mysql replace into 坑

replace into执行的逻辑
1、遇到PRIMARY KEY或UNIQUE索引的，新记录与旧记录有冲突的（这里实际产生了异常duplicate key error），会把旧记录删除，然后再插入新记录
2、若是新记录没有冲突，就直接插入一条新记录，与insert into一样
 
看起来很正常，这里针对第一种逻辑会有问题https://jjhpeopl.iteye.com/blog/2368927  https://blog.xupeng.me/2013/10/11/mysql-replace-into-trap/
1、把旧记录删除之后，插入的新记录只是插入了那些指定的字段，原本不想更新的字段，直接为默认值了，会导致数据丢失
2、若旧记录的id跟其他表是有关联的，更新后新记录会产生新的id，导致这种关联丢失
3、而且使用replace into会导致自增主键id一直增大，很容易导致id值范围不够用
另外，若是数据库存在主从关系，在主机器上进行了replace into操作之后，从机器上对应表的AUTO_INCREMENT是不会更新的，导致从机器转为主机器时，新插入数据会出现异常，直到AUTO_INCREMENT增加到原来主机器的值为止。 
CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO auto (k, v, extra) VALUES (1, '1', 'extra 1'), (2, '2', 'extra 2'), (3, '3', 'extra 3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  1 | 1 | 1    | extra 1 |
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
+----+---+------+---------+
3 rows in set (0.00 sec)
REPLACE INTO auto (k, v) VALUES (1, '1-1');
SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 1 | 1-1  | NULL    |
+----+---+------+---------+
3 rows in set (0.00 sec)
执行完 REPLACE INTO auto (k, v) VALUES (1, ‘1-1’) 之后，由于新写入记录时并未给 extra 字段指定值，原记录 extra 字段的值就「丢失」了，而通常这并非是业务上所预期的，更常见的需求实际上是，当存在 k=1 的记录时，就把 v 字段的值更新为 ‘1-1’，其他未指定的字段则保持原状，而满足这一需求的 MySQL 方言是 INSERT INTO auto (k, v) VALUES (1, ‘1-1’) ON DUPLICATE KEY UPDATE v=VALUES(v);

鉴于此，很多使用 REPLACE INTO 的场景，实际上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE，在正确理解 REPLACE INTO 行为和副作用的前提下，谨慎使用 REPLACE INTO。

类型转换对 MySQL 选择索引的影响

mysql [localhost] {msandbox} (test) > explain select age from
    -> indextest where name=111222\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: indextest
         type: ALL
possible_keys: idx_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)
https://blog.xupeng.me/2012/02/08/type-conversion-and-index-selection-of-mysql/
mysql [localhost] {msandbox} (test) > SELECT '18015376320243459' =
    -> 18015376320243459;
+-----------------------------------------+
| '18015376320243459' = 18015376320243459 |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT '18015376320243459' + 0;
+-------------------------+
| '18015376320243459' + 0 |
+-------------------------+
|    1.80153763202435e+16 |
+-------------------------+
1 row in set (0.00 sec)


mysql [localhost] {msandbox} (test) > SELECT
    -> cast('18015376320243459' as unsigned) =  18015376320243459;
+-----------------------------------------------------------+
| cast('18015376320243459' as unsigned) = 18015376320243459 |
+-----------------------------------------------------------+
|                                                         1 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
因为浮点数精度(53 bits)问题，并且 MySQL 将字符串转换为浮点数和将整数转换为浮点数使用不同的方法，字符串 '18015376320243459' 和整数 18015376320243459 相比较就不相等，如果要避免隐式浮点数转换带来的精度问题，可以显式地使用 cast 做类型转换，将字符串转换为整数。

按照这些规则，对于上面的例子来说，name 字段的值和查询参数 '111222' 都会被转换为浮点数才会做比较，而很多文本都能转换为和 111222 相等的数值，比如 '111222', '111222aabb', ' 111222' 和 '11122.2e1'，所以 MySQL 不能有效使用索引，就退化为索引扫描甚至是全表扫描。
反过来，如果使用一个字符串作为查询参数，对一个数字字段做比较查询，MySQL 则是可以有效利用索引的
MySQL 可以将查询参数 '30' 转换为确定的数值 30，之后可以快速地在索引中找到与之相等的数值

 explain select * from
    -> indextest where date(create_time)='2012-02-02'\G
explain select * from
    -> indextest where create_time between '2012-02-02' and '2012-02-03'\G

redis keys scan

$redis->keys('login:201903*')
$redis->bitop('AND',  'monthActivities'', $redis->keys('login:201903*'));

echo "连续一个月签到用户数量：" . $redis->bitCount('monthActivities');https://learnku.com/articles/25892
当前的 keys 指令执行完了才可以继续，再加上 keys 操作是遍历算法，复杂度是 O(n)，乍一想就知道问题所在了，当实例中数据量过大的时候，Redis 服务可能会卡顿，其余指令可能会延时甚至超时报错..

127.0.0.1:6379> setbit login:20190321 1 1
(integer) 0
127.0.0.1:6379> setbit login:20190321 2 1
(integer) 0
127.0.0.1:6379> setbit login:20190322 2 1
(integer) 0
127.0.0.1:6379> setbit login:20190323 2 1
(integer) 0
127.0.0.1:6379> keys login:201903*
1) "login:20190323"
2) "login:20190322"
3) "login:20190321"
127.0.0.1:6379> scan 0 match login:201903*
1) "3"
2) 1) "login:20190323"
   2) "login:20190322"
127.0.0.1:6379> scan 0 match login:201903* count 2
1) "4"
2) (empty list or set)
127.0.0.1:6379> scan 0 match login:201903* count 20
1) "0"
2) 1) "login:20190323"
   2) "login:20190322"
   3) "login:20190321"

mysql5.7Incorrect datetime value: ‘0000-00-00 00:00:00’ for column

`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
show variables like 'sql_m%';
sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

alter table test modify created_at datetime NOT NULL;
alter table test modify updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

5.7 timestamp类型取值范围：1970-01-01 00:00:00 到 2037-12-31 23:59:59，初始值调整为 1970-01-02 00:00:00 就可以了 

alter table test modify created_at timestamp NOT NULL DEFAULT '1970-01-02 00:00:00';
alter table test modify updated_at timestamp NOT NULL DEFAULT '1970-01-02 00:00:00';

mysql的limit进行分页时出现重复

select * from table order by xx limit 0,10

当xx不存在索引，且有xx相同的行是，可能出现分页数据重复问题


解决办法：
         1.加上索引排序
         select * from table order by xx,id（任意有索引的字段） limit 0,10
         2、给xx字段加上索引
         作为验证，您可以在这个字段上加索引  alter table tea_course_sort add index(course_sort_order)，然后由于这个表数目太小，以防加索引都未必能用得上，语句修改为
       select * from tea_course_sort  force index(course_sort_order) order by tea_course_sort.course_sort_order desc  limit 0,10;
来得到您预期的结果
 
select  * from table_1 where 1=1 limit m,n

这样后面的页可能会出现重复数据，这时可以通过加入order by 子句来解决这种情况， select * from table_1  where 1=1 order by field_1 limit m,n

但是这里需要特别注意，如果field_1字段有相同值的情况下，后面的页还是会出现重复数据，这时可以加入第二个排序字段（值唯一），可以选主键id,

对应的sql语句是select * from table_1  where 1=1 order by field_1 , id limit m,n

但是最好保证field_1在表中的值是唯一的，这样就可以少写一个排序字段，增加查询效率，因为在只有一个排序字段的情况下，mysql会使用索引，如果是有多个排序字段的话，mysql会放弃索引做全表扫描。

预估 Mysql 数据表的数据大小和索引大小

SELECT data_length,index_length
FROM information_schema.TABLES t
WHERE table_schema='your_db_name'
AND table_name = 'your_table_name';
直接查询 avg_row_length 字段，这个字段表示数据表的平均行大小，和上面自己计算的结果类似。
SHOW COLUMNS FROM TABLES;
https://www.playpi.org/2019041001.html

mysql limit查询优化方法

对同一张表在不同的地方取10条数据： 1）offset比较小时  
 
代码示例: select * from user limit 10,10;   这条sql语句多次运行，时间保持在0.0004-0.0005之间。  
 
代码示例: select * from user where uid >=( select uid from user order by uid limit 10,1 ) limit 10;   这条sql语句多次运行，时间保持在0.0005-0.0006之间，主要是0.0006。 结论：偏移offset较小时，直接使用limit较优。这个显然是子查询的原因。
 
2）offset大时  
 
代码示例: select * from user limit 10000,10;   这条sql语句多次运行，时间保持在0.0187左右  
 
代码示例: select * from user where uid >=( select uid from user order by uid limit 10000,1 ) limit 10; 这条sql语句多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。
 
通过以上对比，得出mysql limit查询语句优化经验： 使用limit语句时，当数据量偏移量较小时可以直接使用limit，当数据量偏移量较大时，可以适当的使用子查询来做相关的性能优化。
--------------------- 
 select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;

FOUND_ROWS()

分页程序一般由两条SQL组成：
SELECT COUNT(*) FROM ... WHERE ....
SELECT ... FROM ... WHERE LIMIT ...

如果使用SQL_CALC_FOUND_ROWS的话，一条SQL就可以了：
SELECT SQL_CALC_FOUND_ROWS ... FROM ... WHERE LIMIT ...

 
当我们在处理分页程序的时候，会使用 limit 来限制返回的数量，然后会有两种获取分页的方法：

第一种方法：

在 SELECT 语句中加入 SQL_CALC_FOUND_ROWS 选项，然后通过 SELECT FOUND_ROWS() 来获取总行数：

SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();
第二种方式：
使用正常的 SQL 语句，然后再用 SELECT COUNT(*) 来获取总行数：

SELECT * FROM table WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM table WHERE id > 100;
经过测试，一般来说 SQL_CALC_FOUND_ROWS 是比较慢的，SQL执行的时间甚至会达到10倍那么夸张，所以最好别使用 MySQL 的 SQL_CALC_FOUND_ROWS 来获取总行数

left join on where

使用了 left join，where 是针对左表，但左表是日期表，那如何做业务表上的条件限制
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户；

where 条件是在临时表生成好后，再对临时表进行过滤的条件；

因此：where 条件加上，已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

解决方案是把限制条件放在 on 后面

select a.*,b.*
from table1 a
left join table2 b on b.X=a.X and XXX
结论：https://learnku.com/articles/26796 

where 后面：是先连接然生成临时查询结果，然后再筛选

on 后面：先根据条件过滤筛选，再连 生成临时查询结果

如何索引 JSON 字段

CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
);
SELECT * FROM `players`;
https://learnku.com/articles/27046#topnav
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| id | player_and_games                                                                                                                                                                                           | names_virtual |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
|  1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}}                  | Sally         |
|  2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}}            | Thom          |
CREATE INDEX `names_idx` ON `players`(`names_virtual`);  

EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ref
possible_keys: names_idx  
          key: names_idx
      key_len: 22
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

length 检测 vachar 字节长度

insert into test value (1, '测')
SELECT id, LENGTH(str), CHAR_LENGTH(str) FROM test;返回结果是 1 3 1
ALTER TABLE `test`
ADD COLUMN `str2`  varchar(2000) NULL AFTER `str`;
SELECT id, LENGTH(str2), CHAR_LENGTH(str2) FROM test where id = 2;
添加了一个 str2 字段，然后插入 256 个汉字，返回结果是
2 768 256
mysql lenght 是字符串所占的字节，并没有计算额外的所需字节。https://learnku.com/laravel/t/27848

浅析乐观锁与悲观锁

使用悲观锁https://learnku.com/articles/27880
悲观并发控制实际上是 “先取锁，再访问” 的保守策略，为数据处理的安全提供了保证。
begin;
select quantity from products where id = 1 for update;
update products set quanntity = 2 where id = 1;
commit;
以上，对 id 为 1 的产品进行修改，先通过 for update 的方式进行加锁，然后再修改。典型的悲观锁策略。
在对数据修改前，尝试增加排他锁。
加锁失败，意味着数据正在被修改，进行等待或者抛出异常。
加锁成功，对数据进行修改，提交事务，锁释放。
如果我们加锁成功，有其他线程对该数据进操作或者加排他锁的操作，只能等待或者抛出异常。

如果修改库存的逻辑发生并发，同一时间只有一个线程可以开启事务并获得 id = 1 的锁，其他事务必须等本次提交之后才能执行，这样可以保证数据不被其他事务修改。

使用排他锁会把数据锁住，不过需要注意一些基本的锁级别，MySQL InnoDB 默认行级锁。行级锁是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁，会使用表级锁把整张表锁住。
使用乐观锁

select quantity from products where id = 1
update products set quantity = 2 where id = 1 and quantity = 3
先查询库存表当前库存数，然后更新的时候判断数据表对应数据的 quantity 与第一次取出来的是否一致，一致则更新，否则认为是过期数据。

这样实现有一个问题，线程 1 从数据库取出 quantity 为 3，线程 2 也取出同一条数据的 quantity，进行操作，变成了 2，然后又进行某些操作 变成了 3，此时线程 1 进行更新操作成功。但是这个过程有问题。

引入 version 参数，乐观锁每次在执行数据修改的操作，都会带上版本号，一旦版本号和数据的版本号一致就可以执行修改操作并对 version 执行 +1 操作，否则就执行失败。

这样实现也有一个问题，如果真的有高并发的时候，就只有一个线程可以修改成功，就会存在大量的失败。

如果你的应用存在超高并发，这样解决也不好，因为会总让用户感知到失败。

尝试减小乐观锁力度，最大程度提高吞吐。

update products set quantity = quantity - 1 where id = 1 and quanntity - 1 > 0
使用这条 SQL 语句，在执行过程中，会在一次原子操作中查询一遍 quantity 的值，并且减去 1。
laravel 悲观锁对应 lockForUpdate，乐观锁对应 sharedLock https://learnku.com/docs/laravel/5.8/queries/3926

mysql 8.0 使用简单密码

直接在 my.cnf 配置文件中 [mysqld] 部分加入下面参数，然后重启 mysqld 即可。

validate_password.policy = 0
validate_password.mixed_case_count = 0
validate_password.number_count = 0
validate_password.special_char_count = 0
validate_password.length = 0
validate_password.policy

可以配置密码的复杂度，可以配置的级别：

0 or LOW
1 or MEDIUM
2 or STRONG
validate_password.length

最终密码的长度，允许为 0 ，但是要注意这里有个坑，validate_password.length 的长度要大于 validate_password.mixed_case_count + validate_password.number_count + validate_password.special_char_count 的和。 例如默认这 3 个 参数的长度都是 1， 所以密码长度最小也只能是 4， 即使配置成了 1 或者 0 ，最终它也会自动变成 4 。 要是想使用 0 的长度，需要将另外三个参数也配置成 0 。
https://broqiang.com/posts/mysql-8-0-uses-a-simple-password

Mysql 批量更新多行

update test set test.sex = case name
	when '小白' then 2
	when '小红' then 2
	else 11
	end
where name in ('小白','小红') and test.group = 1
https://www.h57.pw/2016/09/11/mysql-batch-update-multiline-notes/

MySQL中的注释符

# 注释从#字符到行尾
-- 注释从–序列到行尾，后面需要跟上一个或多个空格，tab也可以
/* */ 注释中间的字符

获取元数据

 获取当前的数据库用户，数据库名称，数据库的版本信息
select user(),database(),version() from dual;
# 查询数据库，有时需要限制返回的数量，或者偏移，例如页面只显示一条数据的情况 limit 0,1 limit 1,2
# 需要通过偏移来返回所有的数据库
select schema_name from information_schema.schemata;
# group_concat 函数是将多行数据连接成一行
select group_concat(schema_name) from information_schema.schemata;
# 查询表
# 方法1
select group_concat(table_name) from information_schema.tables where table_schema=database();  
# 方法2
select table_name from information_schema.tables where table_schema='database_name';
# 方法3
select table_name from information_schema.tables where table_schema=(select database());

# 查询列
select column_name from information_schema.columns where table_schema='database_name' and table_name='users';
select group_concat(column_name) from information_schema.columns where table_schema=database() and table_name='flag';

# 上面可能会被waf识别，也可以这样
select group_concat(column_name) from information_schema.columns where table_name='users';

# 字符串可以转换成16进制
select concat(group_concat(distinct+column_name)) from information_schema.columns where table_name=0x696e666f;
```   
### max key length is 767
```javascript
使用 mysql5.7 没有问题，可以正常 migrate,
使用 mysql5.6 时，就出现这个问题，把字段长度缩小可以解决
由于 MySQL Innodb 引擎表索引字段长度的限制为 767 字节，因此对于多字节字符集的大字段（或者多字段组合索引），创建索引会出现上面的错误。
以 utf8mb4 字符集 字符串类型字段为例：utf8mb4 是 4 字节字符集，则默认支持的索引字段最大长度是： 767 字节 / 4 字节每字符 = 191 字符，因此在 varchar (255) 或 char (255) 类型字段上创建索引会失败。https://learnku.com/laravel/t/28819
处理方案：在 App\Providers\AppServiceProvider 的 boot () 方法中添加 \Illuminate\Support\Facades\Schema::defaultStringLength(191); 即可

SQL 注入

select * from user where username='' and pass=''
# 构造 username=devnull' or '1后，sql 语句变成
select * from user where username='devnull' or '1' and pass=''
SELECT * from users t where t.username=a()
// 报错信息，数据库名是 test_db
[Err] 1305 - FUNCTION test_db.a does not exist
# 原始的
http://192.168.137.140/cms/show.php?id=35
# 后面加上 order by 数字，就会按照第几个字段进行排序，如果没有会报错
http://192.168.137.140/cms/show.php?id=35 order by 16
mysql执行：语句正常；
# mssql执行：语句错误，数据类型不匹配，无法正常执行
select id,username from users union select 1,2;     

# oracle执行：语句错误，数据类型不匹配
select id,username from users union select 1,2 from dual;
// 使用括号，select, from , where 这些关键字不能用括号
select(table_name)from(information_schema.tables)where(table_schema)=database()
// 使用内联注释
select/*1*/username/*1*/from/*1*/users
// 使用%0a 绕过
1 or 1 == 0x31206f722031
不区分大小写
select * from table_name where a like 'a%'
select * from table_name where a regexp '^a'

# 区分大小写
select * from table_name where binary a like 'a%'
select * from table_name where binary a regexp '^a'
如果是数字开头的，则会变成前面的字符串 
'123abc' == 123
'abc' == 0
# 可以把username不以数字开头的数据取出来
select * from users WHERE username=0;

#  'abc' + 0 为 0
select 'abc' + 0;
#  'abc' + 123 为 123
select 'abc' + 123;
# 'abc' + '0' 为 0，做加法运算的时候，两边都变成数字 
select 'abc' + '0';
获取所有用户名和密码不为0的数据，利用这种方式可以构造万能密码

 et/2018/10/29/ctf-sqli-notes/
select * from users where username=0 and password=0
select * from users where username='abcd' + '0' and password='abc' + '0'
``` 
### limit 优化
```javascript
在偏移量非常大的时候，例如可能是LIMIT 1000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面1000条记录都被抛弃，这样的代价非常高。如果所有的页面被访问的频率相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。考虑下面的查询：

select id,desc from film order by title limit 50000,10;

select id,desc from file inner join (select id from file order by title limit 50000,10) b on film.id=b.id;
”延迟关联“将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回元表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。

LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描达赖给你不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录得位置开始扫描，这样就可以避免使用OFFSET。

select id,desc from film where id>5000 limit 10;
https://shuwoom.com/?p=2659

取出一组热门作者及他们最近发表的 3 篇文章

$users = \App\Models\User::limit(10)->get();

$users = $users->map(function ($user) {
    //可以考虑$user->id缓存,在保证了速度的同时避免大面积的缓存重建
    $user->posts = $user->posts()->limit(3)->get();

    return $user;
});

return $users;
SELECT
    posts.*,
    @number := IF (@current_user_id = `user_id`, @number + 1, 1) AS number,
    @current_user_id := `user_id`
FROM
    (select * from `posts` where `posts`.`user_id` IN (572, 822, 911, 103, 234, 11, 999, 333, 121, 122) order by `posts`.`user_id` ASC) AS posts
HAVING
    number <= 3
简单解析一下这个 sql 语句.
https://learnku.com/articles/24787#replies
FORM 为一个子查询，初步筛选出我们需要的作者的所有文章，且正序排列后生成一个临时表.

SELECT 为上面临时表添加标号，添加的方式如下. (你需要从上往下一行行一行的观察，与 select 的执行方式一致即可)

MySQL 中调用未定义的变量，其值默认为 null.
id	user_id	@current_user_id	if 判断	@number
1	1	null	false, number 被赋值为 1	1
2	1	1	true, @number = @number + 1	2
3	1	1	true, @number = @number + 1	3
4	1	1	true, @number = @number + 1	4
5	2	1	false, number 被赋值为 1	1
6	2	2	true, @number = @number + 1	2
..	..	..	..	..
HAVING 执行于 SELECT 之后，其再次筛选上面的临时表，只取 number <= 3 的 行.

select *, substring_index(group_concat(id order by id desc), ',', 10) from orders
group by sid;

User::with(['posts'=>function($query){
            $query->whereRaw('3 > (select count(*) from posts as sub where sub.user_id = posts.user_id and sub.id > posts.id ) ')
            ->orderByDesc('id');
        }])->get();

多更新

update your_table set is_default = (case when id = 3 then 1 else 0 end)

MySQL 调优经历

create table `users` (
  `id` int(10) unsigned not null auto_increment,
  `name` varchar(255) default 'name',
  `a` varchar(255) default 'aaaaaaaaaaaaaaaa',
  `b` varchar(255) default 'bbbbbbbbbbbbbbbb',
  `c` varchar(255) default 'cccccccccccccccc',
  primary key (`id`)
) engine=innodb;
delimiter ;;
  create procedure usersdata()
  begin
    declare i int;
    set i=1;
    while(i<=4000)do
      insert into users(`name`) values('name');
      set i=i+1;
    end while;
  end;;
delimiter ;
call usersdata();
create table `user_enterprises` (
  `id` int(11) unsigned not null auto_increment,
  `user_id` int(11) default null,
  primary key (`id`)
) engine=innodb;
delimiter ;;
  create procedure enterprisesdata()
  begin
    declare i int;
    set i=1;
    while(i<=4000)do
      insert into user_enterprises(`user_id`) values(i);
      set i=i+1;
    end while;
  end;;
delimiter ;
call enterprisesdata();
select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;
首先把 users 表的所有数据加入到 join buffer 中。
扫描整个 user_enterprises 表的每一行数据，与 join buffer 中的 users 数据作对比，将满足条件的加入结果集。

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;

/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */
select @b-@a;
我们得到的扫描行数是 12000。在执行 join 的时候，扫描行数应该是 4000 + 4000，还有 4000 的扫描行数我推测应该是回表取了数据。

启用 optimizer_trace 调试：

/* 打开 optimizer_trace，只对本线程有效 */
set optimizer_trace='enabled=on'; 

/* 执行语句 */
select * from `users` left join `user_enterprises` on `users`.`id` = `user_enterprises`.`user_id` order by `users`.`id` desc;

/* 查看 OPTIMIZER_TRACE 输出 */
select * from `information_schema`.`optimizer_trace`;
我们看到使用的排序方法是 rowid 排序，select @@max_length_for_sort_data 的结果为 1024，即参与排序的字段大于了这个值，mysql 会把排序字段和主键取出来放入 sort buffer，完成排序后回表取数据。在这儿还用到了临时表。所以大致执行过程应该是 join 之后把数据存在了临时表，然后使用 rowid 排序。

从上面我们发现这个过程是复杂的，如果在 user_enterprises 表上给 user_id 加上索引。

alter table `user_enterprises` add key `user_id_index` (`user_id`);

首先 join 的执行流程发生了变化，大体流程是：

在 users 表里取出一行数据
根据索引在 user_enterprises 表里获取结果，组成结果集
我们发现使用到了索引后，就没有在 join buffer 里那些复杂操作了。因为索引的有序性，排序也免了，整个查询过程所需要的时间也大大减少。https://learnku.com/articles/28964

exists

https://learnku.com/articles/29168
SELECT 
sql_no_cache `product_id`
FROM
`zx_tests` AS a
WHERE
`pn_id` = 101 AND `pv_id` = 59
    AND EXISTS( SELECT 
       sql_no_cache  *
    FROM
        `zx_tests`
    WHERE
    a.product_id = product_id and
        `pn_id` = 101 AND `pv_id` = 171);

2 组条件下 0.657，3 组 0.695，4 组 0.759，5 组 0.743
SELECT `product_id` FROM `product` WHERE `pn_id` = 5 AND `pv_id` = 135 AND `product_id` IN (SELECT `product_id` FROM `product` WHERE `pn_id` = 11 AND `pv_id` = 23);

    2 组条件下 0.729，3 组 0.75，4 组 0.730，5 组 0.757

批量更新所有字段字符集

begin
    declare f_name varchar(100); 
    declare b int default 0;    /*是否达到记录的末尾控制变量*/
		-- 注意修改下面的数据库名称 wsm_aliyun
    declare table_name cursor for SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = 'construction_online';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;

    OPEN table_name;
    REPEAT
    FETCH table_name INTO f_name; /*获取第一条记录*/
				SET @STMT :=CONCAT("ALTER TABLE ",f_name," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;");   
			PREPARE STMT FROM @STMT;   
    EXECUTE STMT;  
-- INSERT into TestTable(name) VALUES (f_name);
       -- ALTER TABLE f_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 
    UNTIL b = 1
		END REPEAT;
    close table_name;
end
https://wi1dcard.cn/posts/mysql-update-all-collations/

每门科目成绩前三的数据

表：student_score，姓名：name，科目：subject，分数 score
SELECT
    a.*
FROM
    student AS a
LEFT JOIN student AS b ON a.`subject` = b.`subject`
AND a.score < b.score
GROUP BY
    a.id,
    a. SUBJECT,
    a.score
HAVING
    COUNT(b.id) < 3
ORDER BY
    a.`subject`,
    a.score DESC

删除表中重复数据，并保留一条

DELETE FROM student WHERE
(`name`,`subject`,score) IN (
    SELECT t.name,t.subject,t.score FROM (
        SELECT `name`,`subject`,score FROM student 
        GROUP BY `name`,`subject`,score 
        HAVING COUNT(1)>1
    )t
)
AND id not in(
  SELECT a.minId FROM (
        SELECT id as minId FROM student 
        GROUP BY `name`,`subject`,score 
        HAVING COUNT(1)>1
    )a
)
所有科目成绩都大于 80 分的学生数据

select name from student group by name having min(score)>80;
https://learnku.com/articles/29467

并列排名和顺序排名查询

获取分数排名，要求并列排名。如果两个分数相同，则两个分数排名（rank）相同。名次之间不应该有“间隔”。
id	score
1	99
2	80
3	87
4	60
5	80
6	99
select id, score, (select count(distinct(score)) from scores as b where b.score > a.score ) + 1 as rank from scores as a order by rank;

select id, score, (select count(distinct(score)) from scores as b where b.score > a.score ) + 1 as rank from scores as a order by rank;
https://learnku.com/articles/27599
select t.id, t.score,@rowNum := @rowNum +1 as rank from (select @rowNum :=0) r, scores as t order by t.score desc ;

id	score	rank
1	99	1
6	99	1
3	87	2
2	80	3
5	80	3
4	60	4

 INNER JOIN...ON...: 返回俩表关联的所有行，不执行上面说的第三部JOIN添加外部行。
 LEFT JOIN...ON... : 返回左表中的所有行，若有些行在右表中没有对应的值，将会使用NULL填充。
 RIGHT JOIN...ON...: 返回右表中的所有行，若有些行在左表中没有对应的值，将会使用NULL填充。
 
 SELECT * FROM user_info as i LEFT JOIN user_account as a ON i.userid = a.userid and i.userid = 1003;
 
 SELECT * FROM user_info as i LEFT JOIN user_account as a ON i.userid = a.userid where i.userid = 1003;
 第一种情况 LEFT JOIN 在执行完第二步 ON 子句后，筛选出满足 i.userid = a.userid and i.userid = 1003 的行，生成表 vt2，然后执行第三步 JOIN 子句，将外部行添加进虚拟表生成 vt3 即最终结果：
 
 vt2:
 | userid | name | userid | money |
 |  --------   |  --------  |   --------   |  --------  |
 |   1003 | z    |   1003 |     8 |
 
 vt3:
 | userid | name | userid | money |
 |  --------   |  --------  |   --------   |  --------  |
 |   1001 | x    |   NULL |  NULL |
 |   1002 | y    |   NULL |  NULL |
 |   1003 | z    |   1003 |     8 |
 |   1004 | a    |   NULL |  NULL |
 |   1005 | b    |   NULL |  NULL |
 |   1006 | c    |   NULL |  NULL |
 |   1007 | d    |   NULL |  NULL |
 |   1008 | e    |   NULL |  NULL |
 而第二种情况 LEFT JOIN 在执行完第二步 ON 子句后，筛选出满足 i.userid = a.userid 的行，生成表 vt2；再执行第三步 JOIN 子句添加外部行生成表 vt3；然后执行第四步 WHERE 子句，再对 vt3 表进行过滤生成 vt4，得的最终结果：
 
 vt2:
 | userid | name | userid | money |
 |  --------   |  --------  |   --------   |  --------  |
 |   1001 | x    |   1001 |    22 |
 |   1002 | y    |   1002 |    30 |
 |   1003 | z    |   1003 |     8 |
 vt3:
 | userid | name | userid | money |
 |  --------   |  --------  |   --------   |  --------  |
 |   1001 | x    |   1001 |    22 |
 |   1002 | y    |   1002 |    30 |
 |   1003 | z    |   1003 |     8 |
 |   1004 | a    |   NULL |  NULL |
 |   1005 | b    |   NULL |  NULL |
 |   1006 | c    |   NULL |  NULL |
 |   1007 | d    |   NULL |  NULL |
 |   1008 | e    |   NULL |  NULL |
 vt4:
 | userid | name | userid | money |
 |  --------   |  --------  |   --------   |  --------  |
 |   1003 | z    |   1003 |     8 |
 如果将上例的 LEFT JOIN 替换成 INNER JOIN，不论将条件过滤放到 ON 还是 WHERE 里，结果都是一样的，因为 INNER JOIN 不会执行第三步添加外部行。
 
 SELECT * FROM user_info as i INNER JOIN user_account as a ON i.userid = a.userid and i.userid = 1003;
 SELECT * FROM user_info as i INNER JOIN user_account as a ON i.userid = a.userid where i.userid = 1003;
 返回结果都是：
 
 | userid | name | userid | money |
 |  --------   |  --------  |   --------   |  --------  |
 |   1003 | z    |   1003 |     8 |
 https://learnku.com/articles/27701

MySQL 分页数据错乱重复

select xx from table_name wheere xxx order by 字段A limit offset;，
表数据总共 48 条，分页数量正常，但出现了结果混杂的情况，第一页的数据出现在了第二页上；如果 order by 字段B 就不会出现这个现象
mysql 5.6 
create table glon(  
    ->     id int not null auto_increment primary key,  
    ->     name varchar(20) not null,  
    ->     create_time datetime not null,  
    ->     age tinyint unsigned default 18  
    -> );
INSERT INTO `glon` VALUES (1, 'Eason Chan', '2017-05-02 08:10:10', 19),(2, 'Glon Ho', '2017-05-03 12:10:10', 18),(3, '赵敏', '2017-05-03 14:10:10', 17),(4, 'Jacky Cheung', '2017-05-02 14:00:00', 22),(5, '周芷若', '2017-05-02 14:00:00', 16),(6, 'Andy Lau', '2017-05-02 14:00:00', 50),(7, '至尊宝', '2017-05-02 14:00:00', 20),(8, '刘三姐', '2017-05-02 14:00:00', 19);

root@localhost [glon_ho]>select * from glon;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  1 | Eason Chan   | 2017-05-02 08:10:10 |   19 |
|  2 | Glon Ho      | 2017-05-03 12:10:10 |   18 |
|  3 | 赵敏         | 2017-05-03 14:10:10 |   17 |
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
|  5 | 周芷若       | 2017-05-02 14:00:00 |   16 |
|  6 | Andy Lau     | 2017-05-02 14:00:00 |   50 |
|  7 | 至尊宝       | 2017-05-02 14:00:00 |   20 |
|  8 | 刘三姐       | 2017-05-02 14:00:00 |   19 |
+----+--------------+---------------------+------+

root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 0, 4;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  1 | Eason Chan   | 2017-05-02 08:10:10 |   19 |
|  8 | 刘三姐       | 2017-05-02 14:00:00 |   19 |
|  6 | Andy Lau     | 2017-05-02 14:00:00 |   50 |
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
+----+--------------+---------------------+------+
4 rows in set (0.00 sec)

root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 4, 4;
+----+-----------+---------------------+------+
| id | name      | create_time         | age  |
+----+-----------+---------------------+------+
|  7 | 至尊宝    | 2017-05-02 14:00:00 |   20 |
|  8 | 刘三姐    | 2017-05-02 14:00:00 |   19 |
|  2 | Glon Ho   | 2017-05-03 12:10:10 |   18 |
|  3 | 赵敏      | 2017-05-03 14:10:10 |   17 |
+----+-----------+---------------------+------+
4 rows in set (0.00 sec)

两次查询结果中都出现了 id 为 8 的刘三姐，从上面初始化数据来看，总共有 8 条数据，现在不但分页出现重复数据，还丢了一条！
select * from glon ORDER BY create_time,id limit 0, 4;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  1 | Eason Chan   | 2017-05-02 08:10:10 |   19 |
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
|  5 | 周芷若       | 2017-05-02 14:00:00 |   16 |
|  6 | Andy Lau     | 2017-05-02 14:00:00 |   50 |
order by 排序的时候，如果排序字段中有多行相同的列值，则排序结果是不确定的。所以后面的几组组合形式的排序或者是主键 id 的排序，因为唯一性高，所以排序是确定的，不会出现结果混乱的问题。
最简单的方法就是在排序列（如 create time）上加索引，然后在 order by 上明示 primary key，这个问题就非常圆满的解决了。
https://www.cnblogs.com/glon/p/6806064.html

MySQL 闪回工具之 binlog2sql

直接整个解析 mysql-bin.000001 日志

[root@node1 binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uglon -p'123456' -dglonho -ttest --start-file='mysql-bin.000001'
USE glonho;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `glonho`.`test`(`create_time`, `id`, `name`) VALUES ('2012-10-01 00:00:00', 1, 'Glon Ho'); #start 547 end 772 time 2017-05-12 15:02:23
INSERT INTO `glonho`.`test`(`create_time`, `id`, `name`) VALUES ('2016-05-02 00:00:00', 2, 'Eason Chan'); #start 547 end 772 time 2017-05-12 15:02:23

[root@node1 binlog2sql]# python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uglon -p'123456' -dglonho -ttest --start-file='mysql-bin.000001' --start-datetime="2017-05-12 14:57:00" --stop-datetime="2017-05-12 15:04:22"
INSERT INTO `glonho`.`test`(`create_time`, `id`, `name`) VALUES ('2015-05-02 00:00:00', 3, 'Jacky Cheung'); #start 1164 end 1350 time 2017-05-12 15:04:03
UPDATE `glonho`.`test` SET `create_time`='2012-10-01 00:00:00', `id`=1, `name`='Glon Ho' WHERE `create_time`='2017-05-12 00:00:00' AND `id`=1 AND `name`='Glon Ho' LIMIT 1; #start 868 end 1068 time 2017-05-12 15:03:34
1、在配置文件中设置了以下参数：

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full # 默认
2、在闪回的时候必须启动 MySQL 服务，因为它是通过 BINLOG_DUMP 协议来获取 binlog 内容，需要读取server端 information_schema.COLUMNS 表，来获取表结构的元信息，才能拼接成 SQL 语句。因此需要给用户提供的最小权限如下：

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%';
https://www.cnblogs.com/glon/p/6856192.html

You can’t specify target table for update

mysql不允许update目标表和子查询里面的表为同一张表

错误sql：

UPDATE mg_brand set `status`='0' where iID=(SELECT id from mg_industry where `name`='汽车') and id in (SELECT id from mg_brand WHERE nameC = '欧宝' or pID = (SELECT id from mg_brand WHERE nameC = '欧宝'));

解决办法：子查询sql可以改变双层的子查询，即可执行成功

示例sql:

UPDATE mg_brand SET `status` = '0' WHERE iID = ( SELECT id FROM mg_industry WHERE `name` = '汽车' ) AND id IN ( SELECT id FROM (SELECT id FROM mg_brand) AS temp WHERE nameC = '欧宝' OR pID = ( SELECT id FROM ( SELECT id FROM mg_brand WHERE nameC = '欧宝' ) AS te WHERE 1 ));

分组无数据查询填充0

select date(t.create_time) as `date`,
       count(t.id)  as num
from t_user t
group by `date`;
select * from (SELECT DATE_FORMAT(DATE_SUB('2019-05-09', INTERVAL xc day), '%Y-%m-%d') as day
      FROM (SELECT @xi := @xi + 1 as xc
             from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
                  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
                  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc3,
                  (SELECT @xi := -1) xc0
           ) as t) as t2
					 where t2.`day` > '2019-05-02' and t2.`day` <= '2019-05-09'
select t2.`day` as `date`, count(t.id) as num
from (SELECT DATE_FORMAT(DATE_SUB('2019-05-09', INTERVAL xc day), '%Y-%m-%d') as day
      FROM (
             SELECT @xi := @xi + 1 as xc
             from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
                  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
                  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc3,
                  (SELECT @xi := -1) xc0
           ) as x1x2x) t2
       left join t_user t on date(t.create_time) = t2.day
where t2.`day` > '2019-05-02' and t2.`day` <= '2019-05-09'
group by date ORDER BY date asc;

+------------+-----+
|    date    | num |
+------------+-----+
| 2019-05-03 |  0  |
+------------+-----+
| 2019-05-04 |  0  |
+------------+-----+
| 2019-05-05 |  0  |
+------------+-----+
| 2019-05-06 |  2  |
+------------+-----+
| 2019-05-07 |  0  |
+------------+-----+
| 2019-05-08 |  2  |
+------------+-----+
| 2019-05-09 |  9  |
+------------+-----+
http://nullpointer.pw/Mysql%E6%97%A5%E6%9C%9F%E5%88%86%E7%BB%84%E6%97%A0%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2%E5%A1%AB%E5%85%850.html

保留 IN 中的顺序

SELECT * FROM `user_temporary` WHERE ( `id` IN ('29500','29582','29583','28299') ) LIMIT 0, 20;
SELECT * FROM `user_temporary` WHERE ( `id` IN ('29500','29582','29583','28299'') ) ORDER BY field(id,29500,29582,29583,28299) LIMIT 0, 20;

分组取前3

cs_goods 表结构

id	goods_id	cs_merchant_id	created_at
1	234	3	2019-07-09 19:45:33
2	33	3	2019-07-10 19:45:33
3	44	3	2019-07-11 19:45:33
4	55	3	2019-07-01 19:45:33
5	33	5	2019-07-02 19:45:33
6	44	5	2019-07-08 19:45:33
7	55	6	2019-07-06 19:45:33
需求：获取 cs_goods 表中的所有商品，但同一个超市最多只能出现 3 个商品（cs_merchant_id 是超市 id），并用 created_at 进行倒序排列，最后的结果应该是 3 2 1 6 7 5

 https://learnku.com/laravel/t/31355

原生sql：
SELECT a.* FROM cs_goods a WHERE ( SELECT COUNT(*) FROM cs_goods WHERE cs_merchant_id = a.cs_merchant_id AND id > a.id ) < 3 ORDER BY a.created_at desc;
SELECT
`a`.* 
FROM
`cs_goods` AS `a` 
WHERE
( SELECT COUNT(`b`.`id`)  FROM `cs_goods` AS b WHERE `b`.`cs_merchant_id` = `a`.`cs_merchant_id` AND `b`.`created_at` > `a`.`created_at` ) < 3 
ORDER BY
`a`.`created_at` DESC;
SELECT
`a`.* 
FROM
`cs_goods` AS `a`
LEFT JOIN `cs_goods` AS `b` ON `a`.`cs_merchant_id` = `b`.`cs_merchant_id` 
AND `b`.`created_at` > `a`.`created_at` 
GROUP BY
`a`.`id` 
HAVING
COUNT( b.id ) < 3 
ORDER BY
`a`.`created_at` DESC

 \DB::table('cs_goods as a')
        ->select('a.*')
        ->leftJoin('cs_goods as b', function ($join){
            return $join->on('a.cs_merchant_id','=','b.cs_merchant_id')
                ->on('b.created_at','>','a.created_at');
        })
        ->groupBy('a.id')
        ->havingRaw('COUNT(b.id) < 3')
        ->orderByDesc('a.created_at')
        ->toSql();

升级mongodb3

Server at 10.23.2.1:27017 reports wire version 2, but this version of libmongoc requires at least 3 (MongoDB 3.0)   

https://segmentfault.com/a/1190000004547368
curl https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-3.0.9.tgz  

tar -xvzf mongodb-linux-x86_64-3.0.9.tgz  
cd mongodb-linux-x86_64-3.0.9
[root@localhost ~]# ps aux|grep mongo
root      6217  0.2  0.3 4773512 37412 ?       Sl   May06 232:35 mongod --fork --dbpath=/data2/mongodb --logpath=/data2/mongodb/mongodb.log --logappend
root     28665  0.0  0.0 103256   844 pts/1    S+   16:19   0:00 grep mongo
[root@localhost ~]# kill -9 6217
[root@localhost ~]# /data1/projects/mongodb-linux-x86_64-3.0.9/bin/mongod --fork --dbpath=/data2/mongodb --logpath=/data2/mongodb/mongodb.log --logappend
about to fork child process, waiting until server is ready for connections.
forked process: 29713
child process started successfully, parent exiting
[root@localhost ~]# /data1/projects/mongodb-linux-x86_64-3.0.9/bin/mongo
MongoDB shell version: 3.0.6
connecting to: test
Server has startup warnings:

Mysql 大表分页查询优化

select id, content from table_name where status = 1 order by id asc limit 1328000, 1000
执行了 90 秒，扫描了 1329172 行。

改写成

select a2.id, a2.content from (select id from table_name where status = 1 order by id asc limit 1328000, 1000) a1, table_name a2 where a1.id=a2.id;

SELECT * FROM table_name a1 INNER JOIN (SELECT id FROM table_name ORDER BY id LIMIT 1328000,1000) a2 ON a1.id=a2.id
select id from table_name where id>73575000 order by id asc limit 0, 5000
执行时间19.63ms

select a2.id,a2.keyword,a2.url from (select id from table_name where id>73575000 order by id asc limit 0, 5000) a1, table_name a2 where a1.id=a2.id
执行时间26.35ms，又可以愉快的玩耍了。

或者直接

select id,keyword,url  from table_name where id>73575000 order by id asc limit 0, 5000

https://mengkang.net/1300.html

不区分大小写

 
 
insert into tableName(date,workflow,cou) values('2019-01-04','tx',10000);
insert into tableName(date,workflow,cou) values('2019-01-04','Tx',100000);

alter table statistic_daily_workflow_count modify column workflow varchar(170) binary character set utf8 collate utf8_bin;
创建表时字段指定binary
create table PlainText(
Content nvarchar(50) binary,
primary key(Content)
);
修改列指定binary

 
-- 修改列
ALTER TABLE `Md5Data`.`PlainText` 
CHANGE COLUMN `Content` `Content` VARCHAR(55) CHARACTER SET 'utf8' BINARY NOT NULL DEFAULT '' ;

select * from usertable where binary id = 'A';
http://www.ikeguang.com/2019/01/05/mysql-unique/

MySQL 备份

[root@localhost mc_orderdb]# mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events  --tab="/tmp/mc_orderdb"  mc_orderdb
Enter password: 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000009', MASTER_LOG_POS=1095;
mysqldump: Got error: 1: Can't create/write to file '/tmp/mc_orderdb/order_cart.txt' (Errcode: 13 - Permission denied) when executing 'SELECT INTO OUTFILE'
1. 权限问题：chown mysql:mysql mc_orderdb/
2. 关闭防火墙selinux
　shell>vi /etc/selinux/config
           SELINUX=disabled
3. shell>setsebool -P mysqld_disable_trans=1

groupBy 分组统计

数据中 4 号～7 号的时间段没有返回，我们理想中的返回格式是补全没有的日期，然后在这个日期对应的数量字段填 0。

在网上 google 了一下，解决方案是新建一张自期表做主表，左联要统计的表，

CREATE TABLE num (i int);-- 创建一个表用来储存0-9的数字
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- 生成0-9的数字，方便以后计算时间

CREATE TABLE  if not exists calendar(datelist date); -- 生成一个存储日期的表，datalist是字段名

-- 这里是生成并插入日期数据
INSERT INTO calendar(datelist) SELECT
    adddate(
        (   -- 这里的起始日期，你可以换成当前日期
            DATE_FORMAT("2016-1-1", '%Y-%m-%d') 
        ),
        numlist.id
    ) AS `date`
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
        CROSS JOIN num AS n10000
    ) AS numlist;
运行 sql 语句后，请删除 num 表
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户；

where 条件是在临时表生成好后，再对临时表进行过滤的条件；

因此：where 条件加上，已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

解决方案是把限制条件放在 on 后面

select a.*,b.*
from table1 a
left join table2 b on b.X=a.X and XXX
结论：

where 后面：是先连接然生成临时查询结果，然后再筛选

on 后面：先根据条件过滤筛选，再连 生成临时查询结果https://learnku.com/articles/26796

导入文件

修改 my.cnf [mac]，my.ini.[win],
secure-file-priv='txt 文本存放的目录'
重启 mysql
\t 是 txt 文本中的字段区分，\n 是文本行的结尾，sys_log20190731 表名
80000000.txt 要放到你给权限的目录的下，secure-file-priv='txt 文本存放的目录'
load data infile 'mypath/80000000.txt' ignore into table sys_log20190731 character set gbk fields terminated by '\t' enclosed by '"' lines terminated by '\n' (`field1`,`field2`,`field3`,`field4`,`field5`,`field6`,`field7`);
https://learnku.com/articles/31979

关联查询

内连接分为三类
    - 等值连接：on a.id = b.id
    - 不等值连接：on a.id > b.id
    - 自连接：select * from a t1 inner join a t2 on t1.id = t2.id
  //创建student表，并插入测试数据
  create table student(
  id int unsigned primary key auto_increment,
  name char(10) not null
  );
  insert into student(name) values('小明'),('小红');
  //创建course表，并插入测试数据
  create table course(
  id int unsigned primary key auto_increment,
  name char(20) not null
  );
  insert into course(name) values('PHP'),('JAVA');
  //创建student_course表，并插入测试数据
  create table student_course(
  sid int unsigned,
  cid int unsigned,
  score int unsigned not null,
  foreign key (sid) references student(id),
  foreign key (cid) references course(id),
  primary key(sid, cid)
  );
  insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);  
    查询 student 表中重名的学生，结果包含 id 和 name，按 name 升序
    
    select id,name
    from student
    where name in (
    select name from student group by name having(count(*) > 1)
    ) order by name ASC;
    查询每个学生的总成绩，结果列出学生姓名和总成绩。
    
    select name,sum(score)
    from student left join student_course
    on student.id=student_course.sid
    group by sid;
    +------+------------+
    | name | sum(score) |
    +------+------------+
    | ???  |        170 |
    | ??   |        160 |
    
    在 student_course 表查询各科成绩最高的学生，结果列出学生 id、课程 id 和对应的成绩
    
    select * from student_course as x where score>=
    (select max(score) from student_course as y where cid=x.cid);
    +-----+-----+-------+
    | sid | cid | score |
    +-----+-----+-------+
    |   1 |   2 |    90 |
    |   2 |   1 |    90 |
    +-----+-----+-------+
    在 student_course 表中查询每门课成绩都不低于 80 的学生 id
    
    select distinct sid
    from student_course
    where sid not in (
    select sid from student_course
    where score < 80);
    +-----+
    | sid |
    +-----+
    |   1 |
    +-----+
    https://learnku.com/articles/32039

MySQL大数据分页

SELECT * FROM test where status = 1 limit start, 1000
select * from test a , 
    (select id from test where status = 1 limit 300000, 10) b 
    where a.id  = b.id;
---
10 rows in set (0.06 sec)
select * from test 
    inner join (select id from test  limit 300000, 10) t2 
    using (id);
---
10 rows in set (0.06 sec)
select * from test 
    where status = 1 and id > xxx
    order by id asc
    limit 10
---
10 rows in set (0.00 sec)
https://blog.jiaojie.site/_posts/2017/12/22/mysql-pagination-tips/

MySQL 索引使用策略及优化

MySQL 的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。
从 https://github.com/datacharmer/test_db 倒入 employees.sql 文件
EXPLAIN 出来的信息有 10 列，分别是 id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
概要描述：

id: 选择标识符
select_type: 表示查询的类型。
table: 输出结果集的表
type: 表示表的连接类型
all（全表扫描） 、index（按照索引顺序的全表扫描）、range (有范围的索引扫描)
req (查找条件列使用了索引而且不为主键和 unique, 使用该索引列的值并不唯一)、ref_eq（使用了主键或者唯一性索引进行查找的情况）、
const（主键放置到 where 后面作为条件查询，mysql 优化器就能把这次查询优化转化为一个常量）
possible_keys: 表示查询时，可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
ref: 列与索引的比较
rows: 扫描出的行数 (估算的行数)
Extra: 执行情况的描述和说明

不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

Index Selectivity = Cardinality / #T
显然选择性的取值范围为 (0, 1]，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的
select count(distinct title)/count(*) from table ; https://learnku.com/articles/32225

datetime 默认值为‘0000-00-00 00:00:00’值无法创建

使用root登陆数据库 命令界面执行
select @@sql_mode;
 结果中包含下面两个
NO_ZERO_IN_DATE,NO_ZERO_DATE
2、修改/etc/my.cnf，查找sql_model如果找不到则添加如下代码 
sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
3、重启mysql
/etc/ini.d/mysql restart
简单几步大功告成！
 
原因：
NO_ZERO_IN_DATE,NO_ZERO_DATE是无法默认为‘0000-00-00 00:00:00’的根源。
NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零 
NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

删除重复数据

select *from hso_min WHERE id not IN (SELECT bid FROM (SELECT min(id) as bid FROM hso_min where symbol='xau' and uts>1565971200  GROUP BY min)as b) and symbol='xau' and uts>1565971200;

delete from hso_min WHERE id not IN (SELECT bid FROM (SELECT min(id) as bid FROM hso_min where symbol='xau' and uts>1565971200  GROUP BY min)as b) and symbol='xau' and uts>1565971200;

每个用户最新一条

表一 messages

id (PRIMARY KEY)、to_user_id、from_user_id、content、created_at
表二 user

id (PRIMARY KEY)、name、created_at
查询当天最新的 messages 并分页，要求同 user 只取一条 messages

group by 执行在 order by 之前，order by 只是对group by 结果进行排序。可以参考类似文章https://blog.csdn.net/zcd3f/article/details/84767206
SELECT max(created_at),from_user_id,content from messages WHERE created_at like "%2019-08-21%" GROUP BY from_user_id ORDER BY from_user_id desc limit 100;
DB::table('messages as t1')
  ->leftJoin('messages as t2', function ($join) {
    $join->on('t1.from_user_id', '=', 't2.from_user_id')->on('t1.id', '<', 't2.id');
})
  ->leftJoin('user as t3', 't1.from_user_id', '=', 't3.id')
  ->where('t1.to_user_id', $id)
  ->whereNull('t2.id')->paginate(15);
https://learnku.com/articles/32919 
需要查询表里不同分类下的order最大的记录
SELECT id,tid,`order`, FROM_UNIXTIME(yestime) FROM tfen
WHERE tid IN(7512, 7514) 
GROUP BY tid 
ORDER BY `order` DESC ;
结果不对
SELECT id,tid,`order`,FROM_UNIXTIME(yestime) FROM (
SELECT * FROM tfen WHERE tid IN(7512, 7514) ORDER BY `order` DESC 
) AS t 
GROUP BY tid
除非记录完全相同，否则 mysql 不保证每次 group by 结果相同

mysql索引测试

命令行直接查询sql
mysql -vv -uroot -h127.0.0.1 -P3306 -p123456 -Dtest -e"select count(*) from test where a = 123467 and b > 123954";
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select count(*) from test where a = 123467 and b > 123954
--------------

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (4.16 sec)
localhost:~ ws$ mysql -vv -uws -p123456 -Dtest -e"select sql_no_cache count(*) from test where a = 26727 and b > 15512"
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
select sql_no_cache count(*) from test where a = 26727 and b > 15512
--------------

+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set, 1 warning (0.32 sec)
https://learnku.com/laravel/t/33768

MySQL 避坑宝典

修改表的默认字符集不会改表各个字段的字符集   ALTER TABLE tbl_name [DEFAULT] CHARACTER SET 'UTF8' 误认为会修改所有字段的字符集，但实际上它只会影响后续新增的字段不会改表已有字段的字符集。如果想修改整张表所有字段的字符集建议使用 ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

 隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严
 SELECT * FROM sakila.film WHERE length >= '60';

对于 SELECT COUNT (*) 类型的请求如果不要求精度，建议使用 SHOW TABLE STATUS 或 EXPLAIN 替代。

默认 MySQL 会对 'GROUP BY col1, col2, ...' 请求按如下顺序排序 'ORDER BY col1, col2, ...'。如果 GROUP BY 语句不指定 ORDER BY 条件会导致无谓的排序产生，如果不需要排序建议添加 'ORDER BY NULL'。
select c1,c2,c3 from t1 where c1='foo' group by c2
当 GROUP BY 条件为表达式或函数时会使用到临时表，如果在未指定 WHERE 或 WHERE 条件返回的结果集较大时性能会很差。select description from film where title ='ACADEMY DINOSAUR' GROUP BY length-language_id;
 删除全表时建议使用 TRUNCATE 替代 DELETE
 当表结构发生变更，如果 INSERT 或 REPLACE 请求不明确指定列名，请求的结果将会与预想的不同；建议使用 “INSERT INTO tbl (col1，col2) VALUES ...” 代替。
 整型定义建议采用 INT (10) 或 BIGINT (20)
 INT (M) 在 integer 数据类型中，M 表示最大显示宽度。 在 INT (M) 中，M 的值跟 INT (M) 所占多少存储空间并无任何关系。 INT (3)、INT (4)、INT (8) 在磁盘上都是占用 4 bytes 的存储空间。
 varchar 是可变长字符串，不预先分配存储空间，长度不要超过 255，如果存储长度过长 MySQL 将定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
 COUNT (DISTINCT col) 计算该列除 NULL 之外的不重复行数，注意 COUNT (DISTINCT col, col2) 如果其中一列全为 NULL 那么即使另一列有不同的值，也返回 0。

MyISAM 表对于 COUNT (*) 统计全表行数进行了特殊的优化，通常情况下非常快。但对于非 MyISAM 表或指定了某些 WHERE 条件，COUNT (*) 操作需要扫描大量的行才能获取精确的结果，性能也因此不佳。有时候某些业务场景并不需要完全精确的 COUNT 值，此时可以用近似值来代替。EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行 EXPLAIN 并不需要真正去执行查询，所以成本很低。
不要使用 COUNT (col) 或 COUNT (常量) 来替代 COUNT (*), COUNT (*) 是 SQL92 定义的标准统计行数的方法，跟数据无关，跟 NULL 和非 NULL 也无关。
当某一列的值全是 NULL 时，COUNT (COL) 的返回结果为 0, 但 SUM (COL) 的返回结果为 NULL，因此使用 SUM () 时需注意 NPE 问题。可以使用如下方式来避免 SUM 的 NPE 问题: SELECT IF (ISNULL (SUM (COL)), 0, SUM (COL)) FROM tbl
 触发器的执行没有反馈和日志，隐藏了实际的执行步骤，当数据库出现问题是，不能通过慢日志分析触发器的具体执行情况，不易发现问题。在 MySQL 中，触发器不能临时关闭或打开，在数据迁移或数据恢复等场景下，需要临时 drop 触发器，可能影响到生产环境。
不建议使用存储过程  存储过程无版本控制，配合业务的存储过程升级很难做到业务无感知。存储过程在拓展和移植上也存在问题。
当前 MySQL 版本不支持在子查询中进行 'LIMIT & IN/ALL/ANY/SOME'。
SELECT * FROM staff WHERE name IN (SELECT NAME FROM customer ORDER BY name LIMIT 1)
 DISTINCT 关键字在对元组排序后删除重复。相反，考虑使用一个带有 EXISTS 关键字的子查询，您可以避免返回整个表。
 SELECT DISTINCT c.c_id, c.c_name FROM c,e WHERE e.c_id = c.c_id

DUAL 表为虚拟表，不需要创建即可使用，也不建议服务以 DUAL 命名表。

MySQL 将外部查询中的每一行作为依赖子查询执行子查询。 这是导致严重性能问题的常见原因。这可能会在 MySQL 5.6 版本中得到改善，但对于 5.1 及更早版本，建议将该类查询分别重写为 JOIN 或 LEFT OUTER JOIN。
建议普通二级索引以 idx_为前缀，唯一索引以 uniq_为前缀。
"<>" 才是标准 SQL 中的不等于运算符 不是！=
数据库必须字段 （`last_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' 最后更新时间 '; `is_del` TINYINT (1) UNSIGNED NOT NULL DEFAULT '0' COMMENT ' 是否删除 0：未删除 1：已删除 '）
建议使用 datetime 替换 timestamp 类型，且默认值设置为 1970-01-01 00:00:00。 datetime 类型能保存大范围的值，从 1001 年到 9999 年，且与时区无关。使用 8 个字节的存储空间（比 timestamp 多出 4 字节）
SELECT INTO OUTFILE 需要授予 FILE 权限，这通过会引入安全问题。LOAD DATA 虽然可以提高数据导入速度，但同时也可能导致从库同步延迟过大。
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
UPDATE/DELETE 操作不要指定 ORDER BY 条件。
UPDATE/DELETE 操作使用 LIMIT 条件和不添加 WHERE 条件一样危险，它可将会导致主从数据不一致或从库同步中断。
没有 ORDER BY 的 LIMIT 会导致非确定性的结果，这取决于查询执行计划。
SQL 返回的列既不在聚合函数中也不是 GROUP BY 表达式的列中，因此这些值的结果将是非确定性的。如：select a, b, c from tbl where foo="bar" group by a，该 SQL 返回的结果就是不确定的。
字符串字面上看起来像 IP 地址，但不是 INET_ATON () 的参数，表示数据被存储为字符而不是整数。将 IP 地址存储为整数更为有效。
当主键为自增键时使用 INSERT ON DUPLICATE KEY UPDATE 可能会导致主键出现大量不连续快速增长，导致主键快速溢出无法继续写入。极端情况下还有可能导致主从数据不一致。
因为 SQL_CALC_FOUND_ROWS 不能很好地扩展，所以可能导致性能问题；建议业务使用其他策略来替代 SQL_CALC_FOUND_ROWS 提供的计数功能，比如：分页结果展示等。 select SQL_CALC_FOUND_ROWS col from tbl where id>1000

请提前检查添加唯一索引列的数据唯一性，如果数据不唯一在线表结构调整时将有可能自动将重复列删除，这有可能导致数据丢失。
在 MySQL 8.0 之前当 ORDER BY 多个列指定的排序方向不同时将无法使用已经建立的索引。SELECT * FROM tbl ORDER BY a DESC, b ASC;
 未指定主键或主键非 bigint，建议将主键设置为 bigint unsigned。 无主键或唯一键，无法在线变更表结构
 当需要同时删除或更新多张表时建议使用简单语句，一条 SQL 只删除或更新一张表，尽量不要将多张表的操作在同一条语句。
 UPDATE users u LEFT JOIN hobby h ON u.id = h.uid SET u.name = 'pianoboy' WHERE h.hobby = 'piano';
将嵌套查询重写为 JOIN 通常会导致更高效的执行和更有效的优化
https://github.com/XiaoMi/soar/blob/master/doc/heuristic.md
https://learnku.com/articles/25270

数据中的排名

CREATE TABLE IF NOT EXISTS `employee` (
  `id`     INT                  AUTO_INCREMENT PRIMARY KEY,
  `name`   VARCHAR(10) NOT NULL DEFAULT '',
  `income` INT         NOT NULL DEFAULT '0'
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO `employee` (`name`, `income`)
VALUES ('麻子', 20000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('李四', 12000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('张三', 10000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('王二', 16000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('土豪', 40000);
SELECT t1.name, t1.income, COUNT(*) AS rank
FROM employee AS t1,
     employee AS t2
WHERE t1.income < t2.income
   OR (t1.income = t2.income AND t1.name <= t2.name)
GROUP BY t1.name, t1.income
ORDER BY rank;
 

name	income	rank
土豪	40000	1
麻子	20000	2
王二	16000	3
李四	12000	4
张三	10000	5
找出中位数的排名数字：


SELECT (COUNT(*) + 1) DIV 2 as rankFROM employee;
从一组数据中获得中位数
SELECT income AS median
FROM (SELECT t1.name, t1.income, COUNT(*) AS rank
      FROM employee AS t1,
           employee AS t2
      WHERE t1.income < t2.income
         OR (t1.income = t2.income AND t1.name <= t2.name)
      GROUP BY t1.name, t1.income
      ORDER BY rank) t3
WHERE rank = (SELECT (COUNT(*) + 1) DIV 2 FROM employee)
索引的类型，从好到坏的情况是：system>const>range>index>All
https://learnku.com/articles/18390

树查询

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
        (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
        (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
https://learnku.com/articles/33688

profile 工具

//开启操作
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

//查看是否开启成功
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |//开启成功
| profiling_history_size | 15    |
mysql> select * from article where no_index=666666;
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00150700 | show variables like '%profil%'              |
|        2 | 0.01481100 | select * from article where no_index=666666 |
+----------+------------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000291 |
| checking permissions | 0.000007 |

limit 语句的索引使用优化

针对于 limit 语句的优化，我们可以在它前面加 order by 索引字段

如果 order by 的字段是索引，会先去索引文件中查找指定行数的数据
mysql> explain select sql_no_cache  * from article order by id  limit 90000,10 \G;
索引覆盖 + 延时关联

原理：主要利用索引覆盖查询，把覆盖索引查询返回的 id 作为与我们要查询记录的 id 进行相关联，

mysql> select sql_no_cache  * from article limit 1000000,10;
mysql> select t1.* from article as t1 inner join (select id as pid from article  limit 10000,10) as t2 on t1.id=t2.pid;
https://learnku.com/articles/28609#replies

group by 城市行业后获取工资最低

MySQL 求助， group by 城市行业后获取工资最低的一条数据https://cn.v2ex.com/t/600001#reply34
select business.id, business.money from business join (select city, min(money) as min_money from business group by city) as temp on business.city = temp.city and business.money = temp.min_money;

select * from (select * from T order by money limit 100) tmp group by city, business;

每个司机今天最早的一笔订单

https://mp.weixin.qq.com/s?__biz=MzI1MzY0MzE4Mg==&mid=2247483773&idx=1&sn=d2578386819d66147f09209ecc834436&chksm=e9d011bcdea798aa21d65f935567eb1c66511d2fc9925b51ccea6a4985a21d85d95c01a513b3&scene=21#wechat_redirect

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid order by orderinfo.ordertime limit 1
select name,sex,register,min(ordertime) from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid
张三的第一个用户应该是二号，细心的你可能已经发现问题了，还是group by的问题，它返回的是链接之后分组的第一条记录，min(ordertime)相当于是不在表中的一个新加入的字段，它的值通过min函数计算而来，所以会出现上面的结果

select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)
where子句限定了ordertime的取值范围，所以不会出现那些没有订单信息的用户，所以我们还要对语句作如下修改，让ordertime可以为Null值：
select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid) or orderinfo.ordertime is Null

select name,sex,register,ordertime,orderuser from userinfo,orderinfo,(select userid,min(ordertime) as ordertime2 from orderinfo group by userid) as orderinfo2 where userinfo.id = orderinfo.userid and orderinfo.ordertime = orderinfo2.ordertime2 and orderinfo.userid = orderinfo2.userid

mysql没有row_number()/rank()/dense_rank() over(partition by)这样高级的sql语法，不过我们可以通过编程的方式来模拟实现类似的功能
select u.name,u.sex,u.register,o.ordertime,o.orderuser from userinfo as u,(select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b order by orderinfo.userid,orderinfo.ordertime) as o where u.id = o.userid and o.rank = 1

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。
第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量
第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”

使用变量添加行号

我们可以设置一个初始行号，接下来在 select语句中不断改变行号的值即可：

set @i = 0;
select   (@i:=@i+1)  as   i,userinfo.*   from   userinfo
如果使用一句话，我们可以将设置初始值的过程放在from后面：

select   (@i:=@i+1)  as   i,userinfo.*   from   userinfo,(select   @i:=0)   as  it 

我们根据司机的注册时间划分司机类型：

select name,if(register > '2017-08-05','A','B') as type from userinfo
select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b

删除重复

select *from hso_minline where phymin in(select phymin from hso_minline where symbol='xau' and uts>1565971200 group by phymin having count(*) >1) and  symbol='xau' and uts>1565971200 limit 20;

 
delete from hso_minline WHERE id not IN (SELECT bid FROM (SELECT min(id) as bid FROM hso_minline where symbol='xau' and uts>1565971200  GROUP BY phymin)as b) and symbol='xau' and uts>1565971200;

MySQL 通过 binlog 恢复数据

如果不知道 mysql 的配置文件路径，可以使用 mysql 命令进行查找，

mysql --verbose --help|grep -A 1 'Default options’ #该命令会罗列出my.cnf顺序查找的路径。
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
binlog 就是 binary log，二进制日志文件，记录所有数据库更新语句，包括表更新和记录更新，即数据操纵语言 (DML)，binlog 主要用于数据恢复和配置主从复制等；

数据恢复：当数据库误删或者发生不可描述的事情时，可以通过 binlog 恢复到某个时间点的数据。
主从复制：当有数据库更新之后，主库通过 binlog 记录并通知从库进行更新，从而保证主从数据库数据一致；

log_bin=ON
log_bin_basename=/path/bin-log
log_bin_index=/path/bin-log.index

statement 格式日志，执行 mysqlbinlog /path/bin-log.000001，可以直接看到原始执行的 SQL 语句
row 格式日志，则可读性没有那么好，但仍可通过参数使文档更加可读 mysqlbinlog -v /path/bin-log.000001
mysqlbinlog 两对非常重要的参数

--start-datetime --stop-datetime 解析某一个时间段内的 binlog；
--start-position --stop-position 解析在两个 position 之间的 binlog；


CREATE TABLE `users` (
          `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
          `name` varchar(255) DEFAULT NULL,
          `age` int(8) DEFAULT NULL,
          PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 INSERT INTO `users` (`id`, `name`, `age`)
    VALUES
        (null, '姓名一', 5);
 
 mysqldump -uroot -p T > /path/xxx.sql;   # 备份数据库
 show master status;   # 查看当前的position位置，此时值为154
INSERT INTO `users` (`id`, `name`, `age`)
VALUES
 (null, '姓名二', 13),
 (null, '姓名三', 14),
 (null, '姓名四', 15),
 (null, '姓名五', 16),
 (null, '姓名六', 17);
 
 update users set age = 5；
 INSERT INTO `users` (`id`, `name`, `age`)
 VALUES
 (null, '姓名七', 16),
 (null, '姓名八', 18);
 mysqlbinlog --start-position=154  --stop-position=513  bin-log.000001 > /path/bak.sql;
 mysql -uroot -p < /path/bak.sql;
 
 https://learnku.com/articles/20628

MySQL 8.0 加密方式不兼容

Illuminate\Database\QueryException  : SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from information_schema.tables where table_schema = weibo and table_name = migrations and table_type = 'BASE TABLE')
运行 php artisan migrate -v 查看更详细信息如下：

 Exception trace:

  1   PDOException::("PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]")
      /www/weibo/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70
      MySQL 8.0 使用了新的密码加密方式：caching_sha2_password，而许多客户端还不支持这种方式，比如 php 的 PDO 扩展。
      
     修改 docker-compose.yml 的 mysql 服务部分，添加一行：
     
      command: --default-authentication-plugin=mysql_native_password
     my.cnf 配置文件中 [mysqld] 下添加一行：
     default-authentication-plugin=mysql_native_password
     重新构建服务，依次执行：docker-compose down, docker-compose up -d
     运行 docker container exec -it <container_name or id> /bin/bash 进入 mysql 所在的容器。登录 root 账号：mysql -u root -p <password>，登入 mysql 后依次运行：
     ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
     FLUSH PRIVILEGES; 
https://learnku.com/articles/34823

MySQL 安装常见错误

rpm -Uvh mysql57-community-release-el7-10.noarch.rpm
#命令行出现 mysql57-community-release-el7-8.noarch
rpm -e --nodeps mysql57-community-release-el7-8.noarch
2. 依赖报错

Error: Package: mysql-community-server-5.7.20-1.el7.x86_64 (mysql57-community)
           Requires: libsasl2.so.3()(64bit)
Error: Package: mysql-community-client-5.7.20-1.el7.x86_64 (mysql57-community)
           Requires: libstdc++.so.6(GLIBCXX_3.4.15)(64bit)
Error: Package: mysql-community-libs-5.7.20-1.el7.x86_64 (mysql57-community)
...
分析

因为 旧版本 mysql 的依赖问题；最快的解决方案就是卸载重装

3. 卸载

1. 快速删除

yum remove  mysql mysql-server mysql-libs mysql-server
2. 查找残留文件

rpm -qa | grep -i mysql
## 将查询出来的文件删除
yum remove mysql-community-common-5.7.20-1.el6.x86_64
## 删除残余目录
whereis mysql
rm –rf /usr/lib64/mysql
3. 删除依赖

## 查找依赖 
yum list installed | grep mysql
## 删除找到的依赖
yum -y remove mysql-libs.x86_64
4. 安装新 mysql

1. 添加镜像源，从官网 downloads 选取

wget dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum localinstall mysql-community-release-el6-5.noarch.rpm
yum repolist all | grep mysql
yum-config-manager --disable mysql55-community
yum-config-manager --disable mysql56-community
yum-config-manager --enable mysql57-community-dmr
yum repolist enabled | grep mysql
2. 安装

yum install mysql-community-server
https://learnku.com/articles/35042

数据库脏读

MYSQL查看数据库事务隔离级别

1
show variables like '%isolation%';
修改MYSQL数据库事务隔离级别

设置innodb的事务级别方法是：set 作用域 transaction isolation level 事务隔离级别，例如：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
 
mysql> set global transaction isolation level read committed; #全局的

mysql> set session transaction isolation level read committed; #当前会话
http://blog.qicunshang.com/2018/02/07/database-dirty-read/

创建外键报错

外键关联的数据结构不对，查看了表的数据结构发现 ID 主键自增是 int unsigned 类型的，而我关联的 parent_id 是 int 类型的，所以数据结构不一致才会报错
Schema:：create（"store_menus'，function（Blueprint $table）{
$table->increments（'id）：
$table->string（'title'）->upinue（）->comment（菜单名称）；
$table->integer（'parent_id'）->index（）->default（0）->comment“父类ID’）；
https://learnku.com/articles/35353

并发测试

    DB::table('articles')->where('id' , $request->id)->increment('like');
100并发结果为100
  return DB::transaction(function() use ($request){
       $article = Article::find($request->id);
       $article->like += 1;
       $article->save();
       return $this->message('点赞成功！');
    });
100并发结果为177
.increment 方法编译后的结果就是 update like = like +1 where id = 'id',update 语句是行级锁，所有是顺序执行更新的。
2. 在事务里面进行查询之后再更新，MySQL 默认使用 RR 级别隔离，所有可能存在并发请求的时候读取的值是同一个，
这里避免的方式是加上 Article::lockForUpdate ()->find ($request->id); 排它锁，目的是在本次事物执行查询的时候，其他事务是不允许读取的，这样才能防止并发下产生的问题。https://learnku.com/articles/35337
单个请求是可以实现的 只有在多数并发访问的时候才会出现异常

mysql update超卖

以抢购 100 件商品为例，商品表 num=100
在函数 doPageGoodsOrder 中，如果使用如下 sql

$sql="update ims_hotmallstore_goods set num=$number where num>0 and id=".$goods['id'];

即直接更新数据，不在 sql 中运算，在使用 apache ab 测试时

ab -n 1000 -c 100 http://redis.test/demo/test.php?i=2

发现：

商品表，num 字段不为 0
订单表，生成 456 份订单
日志表，1000 条日志，其中抢购成功的即 status=1 的是 100 条
如果在 sql 中计算，即：

$sql="update ims_hotmallstore_goods set num=num-".$goods_number." where num>0 and id=".$goods['id'];

结果正常，num=0，订单 100 条，日志 1000 条
并发高的情况下，必须要用 mysql 的更新锁。
问题出在上面判断库存的时候：

假设 A 取到的是剩余 100 库存，并发时 B 取到的也是 100 库存
当 A 自行减法算出库存为 99 并更新，这时 C 拿到的库存可能是 100，也可能是 99
当 C 自行减一后，算出 98 时，这时候可能 B 算出来是 99 并更新到库存
当 D 获得库存时，可能是 99 还可能是 100
以上就会出现这种情况。
解决方案：
使用 mysql 事务处理，在获取库存之前开始处理事务并锁定表，然后结束事务，laravel 文档中有这一条代码

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
// 抢购下单
public function doPageGoodsStore()
{
        $pdo = self::Pdo();
        $goods_id=1;
        /**诺，问题所在
            对于 redis 来说，它属于单线程的原子性操作，但是 mysql 不是。
            所以这里拿商品数据，你是通过查询数据库获取的。
            而又因为你算库存的时候，通过查询数据库获取到的库存来计算，
            在并发情况下自然会引起不正确，这里的库存数，建议也通过 redis 中存储的热数据来计算
            比如 $stock = $redis->llen('num');
        **/
        $sql="select id, num, money from ims_hotmallstore_goods where id=".$goods_id;
}
补充第二个问题，看下来是不会的，但是会出现数据的错误，最直接的就是你能看到的超库存。

当然，这个还有一些隐藏的问题。因为并发，数据库压力比较大，如果实时去 update 和 insert 的话，会出现一些单点故障，比如数据库奔溃。

我一般入库的做法是在热度过后再批量入库。
https://learnku.com/laravel/t/34293

mysql5.7 datetime 默认值为‘0000-00-00 00:00:00’值无法创建

1、使用root登陆数据库 命令界面执行
select @@sql_mode;
 结果中包含下面两个
NO_ZERO_IN_DATE,NO_ZERO_DATE
2、修改/etc/my.cnf，查找sql_model如果找不到则添加如下代码 
sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
3、重启mysql
/etc/ini.d/mysql restart
简单几步大功告成！
 
原因：
NO_ZERO_IN_DATE,NO_ZERO_DATE是无法默认为‘0000-00-00 00:00:00’的根源。
NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零 
NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

INFORMATION_SCHEMA

INFORMATION_SCHEMA 是用来访问数据库的元数据（比如数据库，表的名称，列的数据类型或者访问权限等）的，在每个 MySQL 的实例中，INFORMATION_SCHEMA 保存了它维护的所有数据库的信息
查询出数据库 wizard 中所有的表以及数据类型，存储引擎。
使用 INFORMATION_SCHEMA 查询可能会从多个数据库检索信息，所以查询可能会比较耗时，对性能产生一定的影响。在执行之前，可以使用 EXPLAIN 命令检查一下查询的效率
mysql> SELECT table_name, table_type, engine
    -> FROM information_schema.tables
    -> WHERE table_schema = 'wizard'
    -> ORDER BY table_name;

https://github.com/mylxsw/growing-up/blob/master/doc/mysql-lock-transaction.md  程序猿成长计划
https://learnku.com/articles/35773

Got error 134 from storage engine

 SELECT * FROM `xx_article` WHERE `cid1`  =6 LIMIT 0  ,  30

. #1030 - Got error 134 from storage engine
　　这才发现原来是存储引擎发生了错误，知道了原因就来弱弱的修复下：

　　先 check 表:

check table `xx_article`;
　　这里需要注意的一点是，check 完表后必须修复之后 才能看到数据，切莫看到数据记录为空，就大惊失色。

　　然后再修复表：

 repair  table `xx_article`;
https://learnku.com/articles/35836

MySQL批量更新与插入

replace into table （ `column1`,`column2`,`column3` ）values (1,2,3),(2,3,4),(5,6,7)
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;
update table set 
column1 = case  when column2 = 1 then 2 else 3 end ,
column3 = case  when column4 = 1 then 2 else 3 end 
where id in (1,2,3,4)
//拼装按条件批量更新SQL语句
protected function handleUpdate($data, $key)
    {
        if (empty($data) || !is_array($data)) {
            return false;
        }

        $keys_array = array_keys(current($data));

//需要被更新的字段
        $update_column = [
            $keys_array[5],
            $keys_array[6],
            $keys_array[8],
            $keys_array[9],
            $keys_array[16],
            $keys_array[14]
        ];

//更新条件
        $vehicle_id = $keys_array[2];
        $body_color = $keys_array[3];
        $interior_color = $keys_array[4];
        $city_id = $keys_array[11];
        $province_id = $keys_array[10];

        $q = "UPDATE ce SET ";
        foreach ($update_column as $update_colum) {
            $q .= ' ' . $update_colum . ' = CASE ';
            foreach ($data as $value) {
                $value[$update_colum] = !isset($value[$update_colum]) || empty($value[$update_colum])
                    ? 0 : $value[$update_colum];
                $q .= ' when ' . $vehicle_id . ' =' . $value[$vehicle_id]
                    . ' and ' . $body_color . ' = "' . $value[$body_color] . '"'
                    . ' and ' . $interior_color . ' = "' . $value[$interior_color] . '"'
                    . ' and ' . $city_id . ' =' . $value[$city_id]
                    . ' and ' . $province_id . ' =' . $value[$province_id] . ' then ' . $value[$update_colum];
            }
            $q .= " ELSE " . $update_colum . " END, ";
        }
        $q = rtrim($q, ", ");
    }
https://dalebao.github.io/2019/04/27/MySQL%E6%89%B9%E9%87%8F%E6%9B%B4%E6%96%B0%E4%B8%8E%E6%8F%92%E5%85%A5/

恢复误删的数据库

mysql -h127.0.0.1 -uroot -p del < bak.sql
误删后可以保存下binlog

mysqlbinlog mysql-bin.000011 > del.log
打开文件，去掉drop操作并保存。

mysql -h127.0.0.1 -uroot -p <del.log
当然，比较建议通过dump全备份操作，然后找出备份到drop的pos。

show processlist 神器

SELECT id, db, USER, HOST, command, time, state, info FROM information_schema. PROCESSLIST WHERE command != 'Sleep' ORDER BY time DESC;
mysql> show full processlist;
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| Id     | User | Host                 | db    | Command | Time | State    | Info                  |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| 449000 | root | 127.123.213.11:59828 | stark | Sleep   | 1270 |          | NULL                  |

-- 查询执行时间超过3分钟的线程，然后拼接成 kill 语句
select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 3*60
order by time desc;
https://learnku.com/articles/36035

like 条件的优化

like 语句查询慢的可以尝试使用函数来查询，具体为 instr (字段名，' 值 ')>0
比如查询包含 “钟” 字的，instr (name, ' 钟 ') > 0
使用该函数后，查询仅需 0.031s
https://learnku.com/articles/36187

MySQL事务隔离

事务就是一组原子性的sql查询，或者说是一个独立的工作单元。简而言之，事务内的语句要么全部执行成功，要么全部执行失败。

在Mysql中，事务支持是在引擎层实现的，但并不是所有的Mysql引擎都支持事务，比如MyISAM引擎就不支持事务，这也是MyISAM被InnoDB取代的重要原因之一。

提到事务，我们肯定会想到ACID：

原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
隔离级别
当数据库中有多个事务同时执行时，就可能会出现脏读、不可重复读、幻读等问题，因为就有了事务隔离级别的概念。

SQL标准正定义了四种隔离级别：

READ UNCOMMITTED (未提交读)

事务中的修改，即使还没有提交，对其他事务都是可见的。事务可以读取未提交的数据，也被称为脏读(Dirty Read)。

READ COMMITTED(提交读)

一个事务提交后，所做的变更才能被其他事务看到。这个级别也叫不可重复读，因为事务中执行2次相同的查询，可能得到的结果是不一样的。

REPEATABLE READ(可重复读)

一个事务执行的过程中，总是和这个事务在启动时看到的数据是一致的。当然在这个级别下，未提交的数据变更对其他事务也是不可见的。

SERIALIZABLE(可串行化)

对同一行记录，写和读都会加锁，当出现读写锁冲突时，后访问的事务必须等前一个事务执行完成才能继续执行，就会导致大量的超时和锁争用的问题。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑为准。

在可重复读这个隔离级别下，这个视图是事务开启的时候创建的，整个事务期间都用这个视图。

在读提交的隔离级别下，这个视图是在sql语句开始执行的时候创建的。

在读未提交的隔离级别下，直接返回记录上的最新值，没有视图概念。

在串行化的隔离级别下，直接用加锁的方式避免并行访问。

配置的方式是将启动参数transaction-isolation设置成想要的隔离级别。

查看当前设置：

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

总之，存在即合理，不同的隔离级别适用于不同的场景，具体我们应该根据业务场景来决定。

事务隔离的实现
在Mysql中，实际上每条记录的更新同时也会记录一条回滚操作，记录上的最新值通过回滚操作，都可以得到前一个状态的值。

系统会自动判断，当没有事务再需要回滚日志时，会删除回滚日志。

为什么不建议使用长事务：

长事务意味着系统里面会存在很老的事务视图，由于这些事务随时可以访问数据库里面的任何数据，所以这个事务提交之前，数据库里可能用到的回滚记录必须保留着，这就会占用大量的存储空间。同时长事务还占用锁资源，也可能拖垮整个库。

事务启动的方式
显式启动事务语句，begin或者start transaction,提交就是commit,回滚用rollback。
set autocommit = 0,这个命令会将线程的自动提交关掉，意味着如果执行一个select 语句，这个事务就启动了，并且不会自动提交，直到你主动执行commit或者rollback，或者断开连接。
个人建议还是通过第一种方式显式启动事务，避免长事务的发生。

在 set autocommit = 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。

查询长事务：

下面语句是查询持续时间超过60s的事务

mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
Empty set (0.00 sec)
总结下来，我们在开发过程中，尽量少用长事务，如果无法避免，保证逻辑日志空间足够大，并且支持动态日志空间增长。监控Innodb_trx表，发现长事务报警

from https://tsmliyun.github.io/mysql/MySQL%E4%BA%8B%E5%8A%A1%E9%9A%94%E7%A6%BB/

后台权限表

DROP TABLE IF EXISTS `crm_admin_permission`;

CREATE TABLE `crm_admin_permission` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `title` varchar(50) NOT NULL DEFAULT '' COMMENT '标题',
  `icon` varchar(50) NOT NULL DEFAULT '',
  `path` varchar(50) NOT NULL DEFAULT '' COMMENT '路由',
  `is_menu` tinyint(4) NOT NULL DEFAULT 0 COMMENT '是否是菜单,1=>是,0=>否',
  `sort` int(11) NOT NULL DEFAULT 0 COMMENT '排序',
  `level` tinyint(4) NOT NULL DEFAULT 0 COMMENT '等级',
  `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '状态 1=>启用 0=>禁用',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_path` (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='菜单表';

LOCK TABLES `crm_admin_permission` WRITE;
/*!40000 ALTER TABLE `crm_admin_permission` DISABLE KEYS */;

INSERT INTO `crm_admin_permission` (`id`, `parent_id`, `title`, `icon`, `path`, `is_menu`, `sort`, `level`, `status`, `created_at`, `updated_at`)
VALUES
	(23,0,'管理员管理','','',1,0,1,1,'2019-05-20 18:37:40','2019-05-20 18:37:40'),
	(24,23,'管理员列表','','admin/admin/lists',1,1,2,1,'2019-05-20 18:38:31','2019-05-20 18:38:31'),
	(25,23,'角色管理','','admin/role/lists',1,2,2,1,'2019-05-20 18:38:59','2019-05-20 18:38:59'),
	(26,23,'权限管理','','admin/permission/lists',1,3,2,1,'2019-05-20 18:39:36','2019-05-20 18:39:36'),
	(27,24,'添加管理员','','admin/admin/create',0,0,3,1,'2019-05-20 18:41:44','2019-05-20 18:41:44'),
	(28,24,'编辑管理员','','admin/admin/edit',0,0,3,1,'2019-05-20 18:42:33','2019-05-20 18:42:33'),
	(29,25,'新增角色','','admin/role/create',0,0,3,1,'2019-05-20 18:43:42','2019-05-20 18:46:29'),
	(30,25,'编辑角色','','admin/role/edit',0,0,3,1,'2019-05-20 18:44:21','2019-05-20 18:46:30'),
	(36,25,'赋权','','admin/role/permission',0,0,3,1,'2019-05-20 18:46:49','2019-05-20 19:00:29'),
	(37,25,'赋权-提交','','admin/role/updatePermission',1,0,3,1,'2019-05-20 18:47:10','2019-05-20 18:47:10'),
	(31,26,'新增权限','','admin/permission/create',0,0,3,1,'2019-05-20 18:46:49','2019-05-20 19:00:29'),
	(32,26,'编辑权限','','admin/permission/edit',1,0,3,1,'2019-05-20 18:47:10','2019-05-20 18:47:10'),
	(33,0,'首页','','admin/index',0,0,1,1,'2019-05-20 23:51:37','2019-05-20 23:51:47'),
	(34,26,'新增权限-提交','','admin/permission/store',0,0,3,1,'2019-05-21 14:38:36','2019-05-21 14:38:36'),
	(35,26,'编辑权限-提交','','admin/permission/update',0,0,3,1,'2019-05-21 14:38:57','2019-05-21 14:38:57');

/*!40000 ALTER TABLE `crm_admin_permission` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table crm_admin_role
# ------------------------------------------------------------

DROP TABLE IF EXISTS `crm_admin_role`;

CREATE TABLE `crm_admin_role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '角色名称',
  `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '状态 1=>启用 0=>禁用',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

LOCK TABLES `crm_admin_role` WRITE;
/*!40000 ALTER TABLE `crm_admin_role` DISABLE KEYS */;

INSERT INTO `crm_admin_role` (`id`, `name`, `status`, `created_at`, `updated_at`)
VALUES
	(1,'超级管理员',1,'2019-05-06 18:52:06','2019-05-07 16:56:26'),
	(2,'普通管理员',1,'2019-05-06 18:52:29','2019-05-06 18:52:29'),
	(8,'开发',1,'2019-05-20 16:50:40','2019-05-20 16:50:40');

/*!40000 ALTER TABLE `crm_admin_role` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table crm_admin_role_permission
# ------------------------------------------------------------

DROP TABLE IF EXISTS `crm_admin_role_permission`;

CREATE TABLE `crm_admin_role_permission` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL DEFAULT 0,
  `permission_id` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_role_id` (`role_id`),
  KEY `idx_permission_id` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

LOCK TABLES `crm_admin_role_permission` WRITE;
/*!40000 ALTER TABLE `crm_admin_role_permission` DISABLE KEYS */;

INSERT INTO `crm_admin_role_permission` (`id`, `role_id`, `permission_id`, `created_at`, `updated_at`)
VALUES
	(12,2,20,'2019-05-20 18:04:36','2019-05-20 18:04:36'),
	(13,2,21,'2019-05-20 18:04:36','2019-05-20 18:04:36'),
	(14,2,22,'2019-05-20 18:04:36','2019-05-20 18:04:36'),
	(26,1,23,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(27,1,24,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(28,1,27,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(29,1,28,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(30,1,25,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(31,1,29,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(32,1,30,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(33,1,26,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(34,1,31,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(35,1,32,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(36,1,34,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(37,1,35,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(38,1,33,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(39,1,36,'2019-05-21 14:39:34','2019-05-21 14:39:34'),
	(40,1,37,'2019-05-21 14:39:34','2019-05-21 14:39:34');

/*!40000 ALTER TABLE `crm_admin_role_permission` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table crm_admin_user
# ------------------------------------------------------------

DROP TABLE IF EXISTS `crm_admin_user`;

CREATE TABLE `crm_admin_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL DEFAULT '' COMMENT '登陆的用户名',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',
  `phone` char(11) NOT NULL DEFAULT '' COMMENT '电话号码',
  `password` char(60) NOT NULL DEFAULT '' COMMENT '密码',
  `status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '1 => 启用 0=>禁用',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员信息表';

LOCK TABLES `crm_admin_user` WRITE;
/*!40000 ALTER TABLE `crm_admin_user` DISABLE KEYS */;

INSERT INTO `crm_admin_user` (`id`, `username`, `email`, `phone`, `password`, `status`, `created_at`, `updated_at`, `deleted_at`)
VALUES
	(27,'li12312','1234@qq.com','15111179935','$2y$10$LFQrR5PWcsxSTYhNYG1tK.IipQHjiJsVakluH4F0iDK50rtUTMVfq',1,'2019-05-20 00:17:56','2019-05-20 11:45:59',NULL);

/*!40000 ALTER TABLE `crm_admin_user` ENABLE KEYS */;
UNLOCK TABLES;


# Dump of table crm_admin_user_role
# ------------------------------------------------------------

DROP TABLE IF EXISTS `crm_admin_user_role`;

CREATE TABLE `crm_admin_user_role` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `role_id` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

LOCK TABLES `crm_admin_user_role` WRITE;
/*!40000 ALTER TABLE `crm_admin_user_role` DISABLE KEYS */;

INSERT INTO `crm_admin_user_role` (`id`, `user_id`, `role_id`, `created_at`, `updated_at`)
VALUES
	(1,13,1,'2019-05-07 15:45:55','2019-05-07 15:45:55'),
	(9,1,1,'2019-05-07 16:33:18','2019-05-07 16:33:18'),
	(13,27,1,'2019-05-20 10:37:04','2019-05-20 10:37:04');

/*!40000 ALTER TABLE `crm_admin_user_role` ENABLE KEYS */;
UNLOCK TABLES;
https://github.com/tsmliyun/laravel_quick_admin

取分组数据的前N条纪录

CREATE TABLE tbl ( cate  varchar(10), item  int, note varchar(20));
INSERT INTO tbl VALUES
    ('a', 2, 'a的第二个值'),
    ('a', 1, 'a的第一个值'),
    ('a', 3, 'a的第三个值'),
    ('b', 1, 'b的第一个值'),
    ('b', 3, 'b的第三个值'),
    ('b', 2, 'b的第二个值'),
    ('b', 4, 'b的第四个值'),
    ('b', 5, 'b的第五个值');
取每个 cate item 最大/最小的那条记录

mysql> SELECT a.* FROM tbl a WHERE item = (
    -> SELECT MAX(item) FROM tbl WHERE cate = a.cate
    -> ) ORDER BY a.cate;
+------+------+------------------+
| cate | item | note             |
+------+------+------------------+
| a    |    3 | a的第三个值      |
| b    |    5 | b的第五个值      |
+------+------+------------------+
2 rows in set (0.01 sec)
按 cate 分组取最大/最小的两个(N个)item

mysql> SELECT a.* FROM tbl a WHERE EXISTS (
    -> SELECT COUNT(*) FROM tbl
    -> WHERE cate = a.cate AND item < a.item HAVING COUNT(*) < 2
    -> ) ORDER BY a.cate, a.item;
+------+------+------------------+
| cate | item | note             |
+------+------+------------------+
| a    |    1 | a的第一个值      |
| a    |    2 | a的第二个值      |
| b    |    1 | b的第一个值      |
| b    |    2 | b的第二个值      |
+------+------+------------------+
4 rows in set (0.00 sec)
select 
from table a
where 2>(select count() from table where bind_category_id=a.bind_category_idand created_at>a.created_at)
order by a.bind_category_idand ,a.created_at desc
https://learnku.com/laravel/t/36766
https://n3xtchen.github.io/n3xtchen/postgresql/2015/08/13/pgsql-vs-mysql-get-nth-value-per-group

MySQL 定时备份

#!/bin/bash
backupdir=/data/backup
time=`date +%Y-%m-%d-%H-%M`
if  [ ! -e  /data/backcup  ];then
 mkdir  -p  /data/backup
fi
/usr/bin/mysqldump  --single-transaction  --flush-logs --skip-add-drop-table  -uroot  -p'root@xx'  数据库  |bzip2 > $backupdir/数据库$time.sql.bz2

# 清理超过7天没使用的文件
find $backupdir -name "数据库*.sql.bz2" -type f -mtime +7 -exec rm {} \; > /dev/null 2>&1

批注：
--single-transaction参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响
--skip-add-drop-table  ---取消每个数据表创建之前添加drop数据表语句(默认每个表之前存在drop语句)
--flush-logs  在开始导出前刷新服务器的日志文件
最后导出的数据库备份文件 就是这个 xx2019-11-14-02-00.sql.bz2
mysql -u 用户名 -p密码 数据库名 < 备份文件.sql  恢复某个节点数据
https://learnku.com/articles/36965

MySQl事务最全详解

MySQL 中的事务有如下几个特点(ACID):

原子性(atomicity):

一个事务必须被作为一个不可分割的最小工作单元,每个事务中的所有操作必须要么成功,或者要么失败,永远不可能一些操作失败,一些操作成功,这就是所谓的原子性的概念.

一致性(consistency):

一致性就像上面举的一个例子一样,当发生异常情况下,数据仍然是正确的.就是说当一个事务执行失败了,数据之间是不会受异常的情况而影响,永远保持着他的正确性.

隔离性(isolation):

当一个事务还未提交,每个事务之间是相互隔离的,互补受到影响.

持久性(durability):

当一个事务进行提交之后,发生的变化就会永远保存在数据库中.

事务的隔离级别

可重复读(REPEATABLE READ)

多次读取记录的结果都是一致的,可重复读可以解决上面的不可重复读的情况.但是有这样一种情况,当一个事务在读取某个范围的记录时,另外一个事务在这个范围内插入了一条新的数据,当事务再次进行读取数据时,发现比第一次读取记录多了一条,这就是所谓的幻读,两次读取的结果不一致.

举例:小明女朋友在查看银行卡的记录时,看见有 5 条消费记录,此时小明正在消费,这时候消费记录里面记录了这条消费记录,当女朋友再次读取记录时,发现有 6 条记录了.
MySQL 中事务隐式开启的,也就是说,一个 sql 语句就是一个事务,当 sql 语句执行完毕,事务就提交了.在演示的过程中,我们显式开启  上面提到了 MySQL 中事务是隐式开启的,则代表我们每一个 sql 是自动提交的,需要关闭则需要设置 autocommit 选项

show variables like '%autocommit%';set autocommit = 0;

// 查看当前的事务隔离级别

mysql root@127.0.0.1:test> select @@tx_isolation;
// 连接MySQL

$mysqli = new mysqli('127.0.0.1', 'root', '123456', 'test', 3306);

// 关闭事务自动提交

$mysqli->autocommit(false);

// 1.开启事务

$mysqli->begin_transaction();

// 2.修改数据

$mysqli->query("update user set age=10 where id=1");

// 3.查看数据

$mysqli->query("select * from user");

// 4.事务回滚

$mysqli->rollback();

// 5.查看数据

$mysqli->query("select * from user");

// 7.修改数据

$mysqli->query("update user set age=15 where id=1");

// 8.事务提交

$mysqli->commit();

// 9.事务回滚

$mysqli->rollback();

// 10.查看数据https://learnku.com/articles/33749

$mysqli->query("select * from user");
READ COMMITTED（提交读），大多数数据库系统的默认隔离级别，一个事务开始时，只能 “看见” 已经提交的事务所做的修改，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，也叫不可重复读（nonrepeatable read），有可能出现幻读（Phantom Read），指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）

REPEATABLE READ（可重复读），通过 InnoDB 和 XtraDB 存储引擎，是 MySQL 的默认事务隔离级别，该级别保证了在同一个事物中多次读取同样记录的结果是一致的。解决了脏读，又通过多版本并发控制 mvcc 解决了幻读。https://learnku.com/articles/32336
https://www.qqdeveloper.com/2019/08/18/MySQl%E4%BA%8B%E5%8A%A1%E6%9C%80%E5%85%A8%E8%AF%A6%E8%A7%A3/
read uncommitted | 读未提交
read committed | 读已提交
repeatable read | 可重复读
serializable | 串行化
事务 A 读取了事务 B 更新的数据，然后事务 B 在某些因素下执行了回滚，那么事务 A 读取的数据就是不合理的，即脏数据。
事务 A 需要重复多次读取某组数据，事务 A 在事务 B 对该组数据修改提交前后进行读取，很显然、两次读取的数据是不一致的，即不可重复读。侧重于元数据的修改。
事务 A 在修改每一条元数据的时候，事务 B 在此时添加了一条新记录，事务 A 在处理的过程中突然多了一条数据，即幻读。侧重于数据的删除与修改。隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
                                                                        事务隔离级别为读提交时，写数据只会锁住相应的行
                                                                        事务隔离级别为串行化时，读写数据都会锁住整张表
   https://learnku.com/articles/24581                                                                     
    不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表 https://learnku.com/articles/30933

MySQL大小写不敏感问题

show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `a` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
a列有个校验规则为collate utf8_bin的设置，这个就是在a列上查询可以区分大小写的原因。 

可以通过show collation命令查看有哪些校验规则：

mysql> show collation like 'utf8%';
通过show character set查看有哪些字符集：

mysql> show character set like 'utf8%';
可以看到utf8的默认校验规则是utf8general_ci，想要设置在某一列上查询区分大小写，可能通过对列指定collate utf8_bin来解决。
https://www.daemoncoder.com/a/MySQL%E5%A4%A7%E5%B0%8F%E5%86%99%E4%B8%8D%E6%95%8F%E6%84%9F%E9%97%AE%E9%A2%98/4e413d3d

MySQL分页offset过大性能问题与优化

SELECT b FROM t WHERE c<1000 LIMIT 2000000, 500
可以看到这个一个分页查询，从位置2000000处开始，取500条数据，问题的原因正是这个过大的分页起点导致。mysql分页查询会并不是直接跳过前2000000再取出500条数据，而是把前2000000条和后面的500条都取出来，再把前2000000条抛弃，这样的话，上面的慢查询相当于从表中取2000500条数据，这么大的数据量必然会慢。

解决方案

sql修改为：

SELECT b FROM (SELECT a FROM t WHERE c<1000 LIMIT 2000000, 500) ta INNER JOIN t tb ON ta.a = tb.a
这种方式先用一个子查询表的主键（还是和原来一样带有过大分页），结果做为一个临时表，再和原来的t表JOIN，查出需要的字段。

这种方式不仔细看的话，也是要查出2000500条数据，因为子任务的where和limit设置和原来一样，关键就在于子任务SELECT出来的是a字段（t表的主键），而不是像原来直接b字段，这样查出500条数据后再和原有的表join再查出需要的数据字段b，下面详细分析下这个细节带来的性能差异。

基础知识：innodb的索引分为聚集索引和辅助索引，innodb是用聚集索引组织数据的，辅助索引上只存了一个主键，按辅助索引查询数据时，先从辅助索引对应记录的主键，再用主键去聚集索引查具体的数据字段。（这里不详细分析两个种索引的区别，不了解可以自行百度）

上面的慢sql会从辅助索上查2000500条数据，对于每一条数据还要从聚集索引上查一次。修改后的sql会从辅助索引上查出2000500条主键，由于辅助索引上本身就有主键，所以这2000500无需再去聚集索引查，生成临时表后再把这500条数据去聚集索引查出b字段，sql从聚集索引中查2000500条数据变成了只需要查500条，并且b字段在真实的情况往往是大量数据的字段，因此修改前后的sql性能差别很大（这里我理解修改前的sql按辅助索引顺序查询时，再去查聚集索引就不再是顺序读了，而是随机的离散读，也是一部分性能差的原因，具体只是自己的猜测，没有验证）。
https://www.daemoncoder.com/a/MySQL%E5%88%86%E9%A1%B5offset%E8%BF%87%E5%A4%A7%E6%80%A7%E8%83%BD%E9%97%AE%E9%A2%98%E4%B8%8E%E4%BC%98%E5%8C%96/4d513d3d

MySQL 反向模糊查找

假如数据库我们只存一条记录, 不管用户输入, 大卫还是大卫王我们都会回复它同一个内容.这时候我们只需要反向模糊查找即可
表数据如下
id     keyword      reply
1      %大卫%       他就是大卫
select * from table_name where '大卫' like keyword

http://www.shiguopeng.cn/archives/379

大表分页查询调研

mysql> explain select * from user_status_record_1 order by id limit 5000000,5;
+----+-------------+----------------------+-------+---------------+---------+---------+------+---------+-------+
| id | select_type | table                | type  | possible_keys | key     | key_len | ref  | rows    | Extra |
+----+-------------+----------------------+-------+---------------+---------+---------+------+---------+-------+
|  1 | SIMPLE      | user_status_record_1 | index | NULL          | PRIMARY | 8       | NULL | 5000005 |       |
+----+-------------+----------------------+-------+---------------+---------+---------+------+---------+-------+
1 row in set (0.01 sec)

mysql> explain SELECT * FROM anti_health_rate_43 WHERE id >=(select id from anti_health_rate_43 limit 2000010, 1) limit 5;
+----+-------------+---------------------+-------+---------------+----------------+---------+------+---------+-------------+
| id | select_type | table               | type  | possible_keys | key            | key_len | ref  | rows    | Extra       |
+----+-------------+---------------------+-------+---------------+----------------+---------+------+---------+-------------+
|  1 | PRIMARY     | anti_health_rate_43 | range | PRIMARY       | PRIMARY        | 8       | NULL | 1779770 | Using where |
|  2 | SUBQUERY    | anti_health_rate_43 | index | NULL          | idx_create_day | 4       | NULL | 3559541 | Using index |
+----+-------------+---------------------+-------+---------------+----------------+---------+------+---------+-------------+
2 rows in set (0.27 sec)
$ret = select id from table order by aaa limit 100,10
select * from table where id in ( $ret );
https://bettercuicui.github.io/2018/04/01/MYSQL/%E5%A4%A7%E8%A1%A8%E5%88%86%E9%A1%B5%E6%9F%A5%E8%AF%A2%E8%B0%83%E7%A0%94/

修改密码

先把root的旧密码置空
use mysql;
update user set authentication_string='' where user='root';
备注：Mysql5.7+ password字段 已改成 authentication_string字段
重置成新密码
alter user 'root'@'localhost' identified by 'newpassword';
备注：Mysql8.0修改密码方式已有变化（此处是个坑，需要注意）
Mysql8.0之前：
update user set password=password('root') where user='root';
mysql -uroot -proot
https://learnku.com/articles/38466  https://learnku.com/articles/38455

Orderby 排序优化

Create Table: CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(10) NOT NULL DEFAULT '0',
`city` varchar(20) NOT NULL,
`addr` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_age_city` (`name`,`age`,`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

order by 能使用索引最左前缀
* select id,name,age,city from user order by name;
* select id,name,age,city from user order by name,age,city;
* explain select id,name,age,city from user order by name desc,age desc,city desc
如果 where 使用索引的最左前缀定义为常量，则 order by 能使用索引
* select * from user where name = 'zhangsan' order by age,city;
* select * from user where name = 'zhangsan' and age = 20 order by city;
* select * from user where name = 'zhangsan' and age > 20 order by age,city;

不能使用索引进行排序
select * from user order by name,age,city;//query*字段
select * from user order by addr;//非索引字段排序
select * from user order by name,addr;//含有非索引字段
select * from user where age = 20 order by city;//跳过了name字段，违反最左前缀法则
select * from user where name = 'zhangsan' order by city;//跳过了age字段，违反最左前缀法则
select * from user where name = 'zhangsan' order by age,addr;//含有非索引字段
https://learnku.com/articles/38925

更新同一个表不同字段

update diag_product_list_ios as a join (select soft_package_id,soft_name from diag_product_list_ios where pdt_code='ThinkDiag' ) as b on a.soft_package_id=b.soft_package_id set a.soft_name=b.soft_name where a.pdt_code='EASYDIAG4' and a.soft_name is null

慢查询分析调优工具

show variables like 'profiling%';//默认关闭，保存近15次的运行结果
set profiling = on;
show profiles;
备注：
show warnings;//可以显示警告和报错的信息
show profile cpu,block io for query 3;
通过Status一列，可以看到整条SQL的运行过程
如出现以下一种或者几种情况，说明SQL执行性能极其低下，亟需优化
* converting HEAP to MyISAM  //查询结果太大，内存都不够用了往磁盘上搬了
* Creating tmp table //创建临时表：拷贝数据到临时表，用完再删
* Copying to tmp table on disk //把内存中临时表复制到磁盘，危险
* locked //出现死锁
select * from information_schema.profiling;

记录sql到数据库（建议只在测试库环境进行）

方式1:命令行
1. set global general_log = 1;
2. set global log_output = 'TABLE';
方式2:配置文件
* vim my.cnf
general_log =1
general_log_file = /path/logfile
log_output = FILE
* 重启MySQL服务
诊断 SQL
select * from mysql.general_log;
https://learnku.com/articles/39087

慢查询分析调优工具

show variables like '%slow_query_log%';
* slow_query_log //是否开启，默认关闭，建议调优时才开启
* slow_query_log_file //慢查询日志存放目录
set global slow_query_log =1; //只对当前会话生效，重启失效
show variables like 'long_query_time%';//查看阀值（大于），默认10s
set global long_query_time = 3 //设置慢查询阀值
如何把未使用索引的 SQL 记录写入慢查询日志
show variables like 'log_queries_not_using_indexes'; //查看设置，默认关闭
set global log_queries_not_using_indexes = on; //设置
select sleep(4);//睡眠4s再执行
show global status like '%Slow_queries%';//查看慢查询条数
得到返回记录集最多的10条SQL：
mysqldumpslow -s r -t  10 /var/lib/mysql/695f5026f0f6-slow.log
得到访问次数最多的10条SQL：
mysqldumpslow -s r -t  10 /var/lib/mysql/695f5026f0f6-slow.log
得到按照时间排序的前10条里面含有左连接的SQL：
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/695f5026f0f6-slow.log
也支持管道符命令
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/695f5026f0f6-slow.log | more //分页显示

cat /var/lib/mysql/695f5026f0f6-slow.log //查看慢查询日志
-- 执行SQL时间
# Time: 2019-12-31T05:54:23.893042Z
-- 执行SQL的主机信息
# User@Host: root[root] @ localhost []  Id:    40
-- SQL的执行信息
# Query_time: 4.013664  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
-- SQL执行时间
SET timestamp=1577771659;
-- SQL内容
select sleep(4);
https://learnku.com/articles/39009

索引失效

explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy' and phone = '18730658760';
如果索引有多列，要遵守最左前缀法则
即查询从索引的最左前列开始并且不跳过索引中的列
explain select * from user where age = 20 and phone = '18730658760' and pos = 'cxy';
mysql在使用不等于（!=、<>）的时候无法使用索引会导致全表扫描（除覆盖索引外）
explain select * from user where age != 20;
explain select * from user where age <> 20;
少用or
explain select * from user where name = '2000' or age = 20 or pos ='cxy';
正常（索引参与了排序）
explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;
备注：索引有两个作用：排序和查找
https://learnku.com/articles/38889
导致额外的文件排序（会降低性能）
explain select name,age from user where name = 'zhangsan' order by pos;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' order by pos,age;//违反最左前缀法则
explain select * from user where name = 'zhangsan' and age = 20 order by created_time,age;//含非索引字段

布尔值来作为 Group By Raw 条件

如何根据「 2001 年之前和 2001 年之后出生」 的条件进行分组？这是代码:

$results = User::select(\DB::raw('YEAR(birth_date) < 2001 as adult, COUNT(id) as amount'))
    ->groupBy(\DB::raw('YEAR(birth_date) < 2001'))
    ->get();
看起来很奇怪，对吧？让我解释一下。条件年份 (出生日期)<2001 将返回两个值之一 真或假。换句话说，1 或 0。这正是我们需要分组的地方 —— 不管这个人是不是成年人。
https://learnku.com/laravel/t/39537#reply126312

悲观锁与乐观锁

CREATE TABLE `order_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `oid` int(50) NOT NULL COMMENT '商品ID',
  `quantity` int(20) NOT NULL COMMENT '库存',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
“乐观锁🔒”认为拿锁的用户多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。这样就可以避免使用数据库自身定义的行锁，可以避免死锁现象的产生。

UPDATE order_stock SET quantity = quantity - 1 WHERE oid = 1 AND quantity - 1 > 0; 
乐观并发控制多数用于数据争用不大、冲突较少的环境中，这种环境中，偶尔回滚事务的成本会低于读取数据时锁定数据的成本，因此可以获得比其他并发控制方法更高的吞吐量。
悲观锁采用了“一锁🔒二查🔍三更新”模式，就是采用数据库中自带 select ... for update 关键字进行对当前事务添加行级锁🔒，🧵先将要操作的数据进行锁上，之后执行对应查询数据并执行更新操作。

BEGIN
SELECT quantity FROM order_stock WHERE oid = 1 FOR UPDATE;
UPDATE order_stock SET quantity = 2 WHERE oid = 1; 
COMMIT;
MySQL还有个问题是select ... for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在MySQL中用悲观锁务必要确定走了索引，而不是全表扫描。
https://www.debuginn.cn/4579.html

MySQL 递归查询

create table question_type (
  id       bigint primary key  not null
  comment '问题编号',
  parentId bigint              not null
  comment '问题父分类编号，根节点为 0',
  name     varchar(20)         not null
  comment '编号名称',
  path     varchar(100)        not null
  comment '全路径，每级使用 , 分割'
)
  comment '问题编号';
insert into question_type values (1, 0, '数学', '0,1');
insert into question_type values (2, 1, '高等数学', '0,1,2');
insert into question_type values (3, 1, '线性代数', '0,1,3');
insert into question_type values (4, 0, '英语', '0,4');
insert into question_type values (5, 4, '即时翻译', '0,4,5');
insert into question_type values (6, 4, '口语阅读', '0,4,6');
insert into question_type values (7, 0, '物理', '0,7');
insert into question_type values (8, 7, '高能物理', '0,7,8');
insert into question_type values (9, 8, '无限能量', '0,7,8,9');
insert into question_type values (10, 9, '迪克拉之海', '0,7,8,9,10');
# 查询物理分类及其子级
select *
from question_type
where path regexp concat(
    ',', 7,
    ',|^', 7,
    ',|,', 7,
    '$|^', 7,
    '$');
https://blog.rxliuli.com/p/5830226b/

MySQL 行列转换

drop table if exists exam;
create table exam (
  name    varchar(20) not null
  comment '姓名',
  subject varchar(20) not null
  comment '考试科目',
  score   int         null
  comment '考试成绩'
)
  comment '考试记录';

insert into exam values ('琉璃', '语文', 90);
insert into exam values ('琉璃', '英语', 85);
insert into exam values ('楚轩', '数学', 100);
insert into exam values ('楚轩', '物理', 100);
insert into exam values ('张三', '化学', 40);
insert into exam values ('李四', '生物', 100);
select
  name                              as '姓名',
  max(if(subject = '语文', score, 0)) as '语文',
  max(if(subject = '数学', score, 0)) as '数学',
  max(if(subject = '英语', score, 0)) as '英语',
  max(if(subject = '物理', score, 0)) as '物理',
  max(if(subject = '化学', score, 0)) as '化学',
  max(if(subject = '生物', score, 0)) as '生物'
from exam
group by name;
select
  name                                                                  as '姓名',
  sum(if(subject = '语文' or subject = '数学' or subject = '英语', score, 0)) as '主科',
  sum(if(subject = '物理' or subject = '化学' or subject = '生物', score, 0)) as '副科'
from exam
group by name;
select
  name as '姓名',
  max(case subject when '语文' then score else 0 end) as '语文',
  max(case subject when '数学' then score else 0 end) as '数学',
  max(case subject when '英语' then score else 0 end) as '英语',
  max(case subject when '物理' then score else 0 end) as '物理',
  max(case subject when '化学' then score else 0 end) as '化学',
  max(case subject when '生物' then score else 0 end) as '生物'
from exam
group by name;
https://blog.rxliuli.com/p/73dac442/

MySQL 获取随机条数据

create table topic (
  id      int primary key not null
  comment '编号',
  content varchar(20)     not null
  comment '内容'
)
  comment '主题表';
select *
from topic
order by rand()
limit 50000;
select *
from (
       select
         topic.*,
         rand() as order_column
       from topic
     ) as temp
order by order_column
limit 50000;

select *
from topic
where id >= ((select max(id)
              from topic)
             - (select min(id)
                from topic))
            * rand()
            + (select min(id)
               from topic)
limit 50000;
SELECT t.*
FROM topic t
  JOIN
  (SELECT id
   FROM `topic`
   ORDER BY RAND()
   LIMIT 50000) AS z ON z.id = t.id;
https://blog.rxliuli.com/p/931be19d/

Redis 知识点汇总

一张图搞定七种 JOIN 关系

MySQL EXPLAIN结果集分析

SQL注入总结

数据库水平切分二三事

mysql锁

InnoDB锁

MySQL 优化笔记

手把手带你探索 MySQL 事务的隔离

MySQl事务最全详解

MySQL的又一神器-锁，MySQL面试必备

MySQL 优化笔记

一条 SQL 查询语句是如何执行的

MySQL 表结构生成 Markdown 文档

mysql wiki

MySQL 三大范式

在线sql

事务知识点总结

MySQL 学习笔记

悲观锁 & 乐观锁

SQL注入总结https://anemone.top/sqli-SQL%E6%B3%A8%E5%85%A5%E6%80%BB%E7%BB%93/

MySQL事务最全详解

SQL注入的几种类型和原理

信息收集思路整理https://anemone.top/%E4%BF%A1%E6%81%AF%E6%94%B6%E9%9B%86-%E4%BF%A1%E6%81%AF%E6%94%B6%E9%9B%86%E6%80%9D%E8%B7%AF%E6%95%B4%E7%90%86/

搭建 MySQL 主从服务器

分库分表de那些事【理论篇】

一次慢查询优化过程

Navicat Premium 12 for Mac无补丁无毒的激活方法

一个 sql 转 mongo query 的小工具, https://github.com/guxingke/sql2mongo

Mysql数据库设置主从同步

MySQL 事务最全详解

MySQL 索引 +explain

MySQL 运行原理【事务】

MySQL 运行原理【表】

MySQL 社区规范 | 数据库篇

MySQL反应慢的排查思路

my.cnf生成工具

后端程序员必备：mysql数据库相关流程图/原理图

Mysql 使用 optimizer_trace 查看执行流程，分析、验证优化思路

一次 group by + order by 性能优化分析

sql_mode=only_full_group_by 错误

ClickHouse 快速入门

在线执行sql

MySQL数据恢复工具binlog2sql

CTF 中的 SQL 注入总结

MySQL 常用技巧

mysql 证明为什么用limit时，offset很大会影响性能

MySQL 细致总结之基础篇

mysql connection time out

mysql replace into 坑

Redis学习

MyCAT实现MySQL读写分离

Redis 内存分析工具

mysql —— InnoDB行级锁机制

MySQL 社区规范 | 数据库篇

MySQL 规范 (数据库表设计规范)

MySQL 规范

mysql数据库结构优化

阿里云Redis开发规范

MySQL索引优化实战

一个字节的网络漫游故事独白

技术精选

Redis过期策略及实现原理

19条MySQL优化

Mysql分表和分区的区别、分库分表介绍与区别

用Redis实现分布式锁 与 实现任务队列

面试官问你如何解决web高并发这样回答就好了

Redis实现分布式锁与任务队列的思路与源码

PHP+Redis，实现延迟任务 实现自动取消订单，自动完成订

MySQL 图形化工具

MYSQL的SQL性能优化总结

MySQL 主从复制 实例讲解

Redis从入门到实践

10分钟快速入门Redis

MySQL工具汇总

MySQL 大表优化方案

Mysql 锁

pdo bindParam bindValue

MYSQL 事务隔离实验-认识：脏读、不可重复读、幻读

MySQL分页优化

msyql日志

MySQL · 最佳实践 · 分区表基本类型

花式搭建 Laravel 运行环境

mysql中的数据存储选择

安装LNMP开发环境

Mysql Master&Slave 实战

MySQL 主从复制部署与 Laravel 读写分离配置

教你 MySQL Binlog 实用攻略

《高性能MySQL》学习笔记1——MySQL架构

MySQL 部分整理

PHP开发之网站安全

mysql的水平分库分表和垂直分库分表

MySQL 索引初探

redis主从库读写分离

MySQL 中的四种隔离级别

MySQL 主从复制部署与 Laravel 读写分离配置

部署Laravel实战：Step 4 MySQL主从复制

Redis 面试内容

Pika是一个可持久化的大容量redis存储服务

yum 快速搭建 lnmp 开发环境

redis笔记

面试前必须要知道的 Redis 面试内容

elastic stack来分析下你的redis slowlog

Redis 入门

在线sqlhttp://mywebsql.net/

MySQL 详细学习笔记

SQL 最近位置查询语句