前言

最近我们一个二手应用的即时通讯模块马上就用不了了，因为当时对接的是腾讯的IM服务，现在组织里说不想再续费了，功能还得保留。

那就能手写聊天模块了，所有都写得差不多的时候，在聊天记录表格里却整了一出尬尴的事儿。建表时根据消息推送方式，只存储了接收者ID，发送者ID却放在消息包的json中。

但是后面很多时候的查询需要两个ID字段联合，也就是要从json的多层路径中找到发送者ID，并作为条件。于是就有了下面通过虚拟字段解决的方法了。

知识点

1. 多层json路径标量读取
2. 虚拟字段的创建
3. 聊天消息的查询优化

原表结构

1
2
3
4
5
6
7
8
9
10

CREATE TABLE `bqs_chat_message` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT '0' COMMENT '用户ID',
  `data` json DEFAULT NULL,
  `is_get` tinyint(1) DEFAULT '0',
  `delete_time` int(11) DEFAULT '0',
  `create_time` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4;

data字段(Json)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

{
"type":"message",
"commit_id": 16,
"message":"SUCCESS",
"data":{
"type":"receive",
"id":"1111",
"sender_id":1110,
"self":true,
"time":"2020年06月01日 14:34",
"message":{
"type":"text",     
"content":"好"
},
"group_id":0,
"cache_key":"U1111",
"timestamp":1590993240,
"receiver_id":"1111"
}
}

多层json路径标量读取

JSON_EXTRACT()函数可以使用JSON路径表达式来提取JSON数据中的值，比如下面的查询将返回data字段中data.sender_id路径下的值，也就是发送者ID

1
2

SELECT JSON_EXTRACT(data, '$.data.sender_id') AS sneder_id
FROM bqs_chat_message

虚拟字段创建

1

ALTER TABLE bqs_chat_message ADD COLUMN sender_id INT(11) AS (cast(json_extract(`data`,'$.data.sender_id') as signed),0) VIRTUAL COMMENT '发送者ID';

查询优化

虽然上面的方法已经实现了虚拟字段创建，但如果data字段的json路径下不存在sender_id时。数据库里显示和查询的结果出来都是null，为了让虚拟字段的类型统一为int，我们需要对不规则的json数据做处理。

也就是如果json中没有sender_id，我们就以0为默认值，这里我们可以通过IFNULL和IF等SQL函数处理，但我这里推荐coalesce函数。

COALESCE() 函数允许我们从一系列提供的参数中返回第一个非空（非NULL）的值，这个函数可以接受两个或更多的参数，并逐一检查它们，直到找到一个非空值为止，然后立即返回该值。如果所有的参数都是NULL，那么COALESCE() 函数也会返回NULL。

1

ALTER TABLE bqs_chat_message ADD COLUMN sender_id INT(11) AS (coalesce(cast(json_extract(`data`,'$.data.sender_id') as signed),0)) VIRTUAL COMMENT '发送者ID';

写在后面

以上的三个操作已经完全解决了建表上的缺陷，但是要想让查询变得更快，我们还可以将虚拟字段添加为索引，在查询上就不会圈表检索了。

前言

因为之前安装了WPOPT，里面有个功能就是能够在控制台查看MYSQL查询次数以及页面创建的时间，之前一直都是高达四十几次的查询次数。

所以就决定安装redis将数据库查询次数降低下来。

重要提醒

如果服务器内有两个WordPress程序，请先将其中一个WordPress程序的数据库表开头从wp_改为其他的，否则会出现相互干扰的情况。

教程

本教材基于宝塔面板教学，特别方便。

1.首先到宝塔面板的软件商店找到redis 并且安装。

2.找到PHP版本，并且安装redis 扩展。

3.WordPress后台搜索并安装Redis Object Cache 。

4.设置插件启用对象存储，如出现下图则表示启用成功。

5.最后看一下MYSQL的查询次数。

只有2次查询次数，刷新了几次最多也就是15左右，大部分都是个位数，效果相当给力。

前言

迁移网站或者更改图片地址的时候需要更换数据库中域名或图片地址，对于小白来说有点困难，由子比主题作者老唐写的一款插件就排上了用场。

截图

下载地址

前言

有时候因为服务器的内存或CPU高负荷运行一段时间，就会导致MYSQL数据库停止运行，网站打开提示建立数据库连接错误 等字样。

解决办法

我们可以在宝塔的计划执行中添加如下代码，时间为每隔5-10分钟。

#!/bin/bash
# Linux监控MySQL服务，关闭就自动重启
pgrep -x mysqld &> /dev/null
if [ $? -ne 0 ];then
bash /www/server/panel/script/rememory.sh   
/etc/init.d/mysqld start    
echo "监控到MySQL已停止，已执行重启计划，时间： `date "+%Y-%m-%d %H:%M:%S"` " >> /www/mysql_jiankong.log

同理，PHP（此次以PHP5.6为例）也可以添加如下代码进行进程守护

#!/bin/bash
# Linux监控PHP服务，关闭就自动重启
pgrep -x php-fpm &> /dev/null
if [ $? -ne 0 ];then
/etc/init.d/php-fpm-56 restart
echo "监控到php56已停止，已执行重启计划，时间： `date "+%Y-%m-%d %H:%M:%S"` " >> /www/php_jiankong.log  
fi

结语

最好宝塔都添加进程守护，否则哪天没看就会导致网站访问失败从而影响网站的收录和游客的访问。

基础命令

默认端口号：3306
查看服务器版本：select version();  或者 cmd命令 mysql -verison
登录数据库：mysql -uroot -p
退出数据库：exit/quit
查看当前系统下的数据库：show databases;
创建数据库：create 库名;
使用数据库：use 库名;
查看表：show tables;
建表：create table 表名 (字段名+空格+数据类型);
查看表结构：desc 表名;
添值：insert into 表名 (列名) values (值);
查看表中所有数据：select * from 表名;
查询建表时的结构：show create table 表名;
删除字段中的值：delete from 表名 where 条件;
删除表中的字段：delete from 表名 drop column 字段名;或者alter table 表名 drop 字段名
删除表：drop table 表名;
删除库：drop database 库名;
主键约束：primary key
唯一约束：unique
非空约束：not null
默认约束：default
外键约束：foreign key（外键）references主表（主键）
查看别的数据库的表格：show tables from 表名;

where条件查询

精确查询：=、!=、>、<、>=、<=
模糊查询：like（像）、not like（不像）
通配符：%：任意字符、-：单个字符
逻辑运算符：
and：同时满足（优先级大于or）
or：满足任意条件即可
区间运算：between a and b （闭区间）
集合运算：in 、not in
非空运算：is null 、is not null

针对表内数据的操作

增加：insert into 表名 (列名) values (值);
删除：delete from 表名 where 条件;
查看：select * from 表名 where 条件;
修改：update 表名 set 字段=新值 where 条件;
排序：order by 字段名;（asc 升序、desc降序）
例：select * from 表名 order by 列名1 asc ,列名2 desc;
聚合函数：
sum()  函数：求累加和
例：select sum(字段名) as ‘别名’/别名 from 表名;
count()  函数：同级行数数量
（1）count(*) ：表示计算表中总的行数，不管某列是否有数值或者是为空
select count(*) from 表名 ；
（2）count(字段名) ：表示计算指定列下总的行数，计算或将忽略空值
select count(字段名) from 表名;
avg() 函数 ：返回一个平均值函数
例：select avg(字段名) as 别名 from 表名;
max() 函数 ：返回指定列中的最大值
select max(字段名) as 别名 from 表名;
min() 函数 ： 返回最小值
例：select min(字段名) as 别名 from 表名;
分组：
group by 字段  ：将查询结果按一列/多列的值分组，值相等为一列
having 字段 ：二次判断，用到聚合函数后，又需筛选条件时，having和group by组合用
例：select 列名1 ,count(列名2) 别名 from 表名 group by 列名1 having 别名 >2;
限制查询结果输出条数：limit 数字
传一个参数（输出前五条数据）
select * from 表名 limit 5;
传两个参数（输出6-15）
select * from 表名 limit 5,10;
:5：从5后开始，10：条数
修改表名：alter table 旧表名 rename 新表名;
修改表中id字段为sid：alter table 表名 change id sid char;
去掉某列：alter table 表名 drop 列名;
添加某列：alter table 表名 add 列名 char;
修改列为字符型：alter table 表名 modify 列名 char(20);
增加多列：alter table 表名 add(xh int(4),zc char(8),ads char(50),);
删除多列：alter table 表名 drop xh,zc,ads;
添加一个字段设主键约束：alter table 表名 add id sm unsigned primary key auto_increment;
关联查询-等值查询：select * from 表名 where a.id=b.id and 条件
内连接：select * from 表名1 inner join 表名2 on 表名1.xh=表名2.xh where 条件;
左连接：select * from 表名1 left join 表名2 on 表名1.xh=表名2.xh where 条件;
右连接：select * from 表名1 right join 表名2 on 表名1.xh=表名2.xh where 条件;

创建索引

1.要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2.在经常需要进行检索的字段上创建索引，比如要按照表字段username进行检索
3.一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要
普通索引（INDEX）
这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

直接创建索引

CREATE INDEX index_name ON table(column(length));

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length));

创建表的时候同时创建索引

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`), //主键索引
INDEX index_name (title(length)) //普通索引
);

唯一索引（UNIQUE）

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。

创建唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length));

修改表结构

ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length));

创建表时同时创建索引

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (title(length))
);

多列索引

语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10));

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );

全文索引（FULLTEXT）

ALTER TABLE `table_name` ADD FULLTEXT ( `column`);

主键索引（PRIMARY KEY）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );