清理包含指定条件的清理sql生成

SELECT delete_sql as '-- delete_sql' FROM (
SELECT distinct concat('DELETE from `',c.TABLE_SCHEMA,'`.`',t.TABLE_NAME,'` WHERE `pid`=1 AND `uid`=222;') AS 'delete_sql',count(1) as num
FROM information_schema.`TABLES` t
LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_SCHEMA=c.TABLE_SCHEMA
WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_SCHEMA != 'sys' AND t.TABLE_TYPE='BASE TABLE' AND c.COLUMN_NAME in ('pid','uid')
GROUP BY delete_sql
HAVING num=2
ORDER BY
c.TABLE_SCHEMA,c.TABLE_NAME,c.ORDINAL_POSITION
) aaaa

所有表字段信息

SELECT
    C.TABLE_SCHEMA AS '库名',
    T.TABLE_NAME AS '表名',
    C.COLUMN_NAME AS '列名',
    T.TABLE_COMMENT AS '表注释',
    C.COLUMN_COMMENT AS '列注释',
    C.ORDINAL_POSITION AS '列的排列顺序',
    C.COLUMN_DEFAULT AS '默认值',
    C.IS_NULLABLE AS '是否为空',
    C.DATA_TYPE AS '数据类型',
    C.CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    C.NUMERIC_PRECISION AS '数值精度(最大位数)',
    C.NUMERIC_SCALE AS '小数精度',
    C.COLUMN_TYPE AS 列类型,
    C.COLUMN_KEY 'KEY',
    C.EXTRA AS '额外说明'
FROM
    information_schema.`TABLES` T
LEFT JOIN information_schema.`COLUMNS` C ON T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE
    T.TABLE_SCHEMA != 'mysql'
ORDER BY
    C.TABLE_NAME,
    C.ORDINAL_POSITION;

linux

# linux
ln -s 源路径 新路径
# 在目标目录中创建src软链到源src目录,名字如果不一致需要二次修改软链名称
ln -s /data/code/typecho_blog/src /data/www.wuloves.com
# 在目标目录中创建test软链到源src目录
ln -s /data/code/typecho_blog/src/ /data/www.wuloves.com/test


查看系统里面的git公钥
cat  ~/.ssh/id_rsa.pub

window

# window
mklink /d 新路径 源路径
# 在新路径中创建名为blog的挂载目录软链到源src目录, blog后面有没有\ 效果都一样
mklink /d D:\www\blog\ D:\code\typecho_blog\src\

MySQL查询优化(分段查询优化索引的使用)

[toc]

实验环境

测试表的创建语句

CREATE TABLE `live_user_join_room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL COMMENT '用户id',
  `room_id` int(11) DEFAULT NULL COMMENT '直播间id',
  `is_out` int(11) NOT NULL DEFAULT '0' COMMENT '0 未退出 1 已退出',
  `time` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '时间',
  `course_id` bigint(20) DEFAULT NULL COMMENT '课程 id',
  `source` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '来源',
  `source_id` int(11) DEFAULT NULL COMMENT '来源ID',
  `created_by` int(11) NOT NULL DEFAULT '0' COMMENT '创建者',
  `updated_by` int(11) NOT NULL DEFAULT '0' COMMENT '更新者',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`),
  KEY `a` (`uid`,`room_id`,`course_id`),
  KEY `b` (`room_id`,`uid`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=8376813 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户加入房间'

IN条件结果数量 1920 条记录

查询索引

SHOW KEYS FROM `test`.`live_user_join_room`; 

优化前的sql, 平均查询用时 8.36s 左右

SELECT uid,COUNT(DISTINCT(room_id)) AS num FROM `live_user_join_room` FORCE INDEX(`created_at`)
WHERE room_id IN (
    SELECT id FROM live_im_rooms WHERE pid=1 AND room_status!=5 AND room_status!=6
    AND course_id NOT IN (SELECT id FROM courses WHERE pid=1 AND `type`=1 AND price=0)
)
AND created_at>'2020-11-07'
GROUP BY uid

优化前的explain的结果

优化后的sql, 平均查询用时 0.572s 左右

SELECT uid,COUNT(DISTINCT(room_id)) AS num FROM `live_user_join_room` USE INDEX(a)
WHERE room_id IN (
    SELECT id FROM live_im_rooms WHERE pid=1 AND room_status!=5 AND room_status!=6
    AND course_id NOT IN (SELECT id FROM courses WHERE pid=1 AND `type`=1 AND price=0)
)
AND id>(SELECT MIN(id) FROM `live_user_join_room` WHERE created_at>'2020-11-07')
GROUP BY uid

explain的结果

差异的原因个人理解为

MySQL 每次查询只能使用一个索引, 优化后的sql虽然扫描行数多, 但是消除了 Using filesort , 解决了慢查询的问题

实际测试, 如果同一个端口有多个配置时, 和 ServerName 值无关, 如果请求进来的 ServerName 没有配置到, 按靠前加载的第一条虚拟主机设置配置为准, 不管 ServerName 名字是啥, 也不管里面的访问的域名是否在host中有配置, 都没有关系