• 连接数过多

SHOW PROCESSLIST;

修改最大连接数，但是这不是一劳永逸的方法，应该要让它自动杀死那些sleep的进程。
SHOW VARIABLES LIKE "max_connections";
SET GLOBAL max_connections=1000;

这个数值指的是mysql在关闭一个非交互的连接之前要等待的秒数，默认是28800s
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SET GLOBAL wait_timeout=300;

• 修改my.cnf配置注意事项

[mysqld]
max_connections=1000 # 一定要放在 [mysqld] 下面

• 异常连接杀进程

杀进程
SELECT *,CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE USER='root' AND `TIME`>60;

SELECT *,MD5(INFO) AS md5_info FROM information_schema.processlist WHERE INFO IS NOT NULL 
AND COMMAND!='Sleep' AND TIME>0 AND INFO NOT LIKE '%information_schema.processlist%'
ORDER BY TIME DESC

SELECT CONCAT('kill ',ID,';') AS '杀进程' FROM information_schema.processlist WHERE INFO IS NOT NULL 
AND COMMAND!='Sleep' AND TIME>5 AND INFO NOT LIKE '%information_schema.processlist%'
ORDER BY TIME DESC

elasticsearch 安装

一、安装jdk

ElasticSearch是基于lucence开发的，也就是运行需要java jdk支持。所以要先安装JAVA环境。

由于ElasticSearch 5.x 往后依赖于JDK 1.8的，所以现在我们下载JDK 1.8或者更高版本。
下载JDK1.8,下载完成后安装。

二、安装ElasticSearch

1.ElasticSearch下载地址：
https://www.elastic.co/downloads/elasticsearch

2.下载安装包后解压
https://www.elastic.co/cn/downloads/elasticsearch

3.进入bin目录下,双击执行elasticsearch.bat

4.稍等一会儿浏览器能正常打开下面地址表示启动成功

http://localhost:9200

{
    "name": "DESKTOP-FH5900V",
    "cluster_name": "elasticsearch",
    "cluster_uuid": "MMGRFWOrSOGUflKQAPHJRw",
    "version": {
        "number": "7.15.1",
        "build_flavor": "default",
        "build_type": "zip",
        "build_hash": "83c34f456ae29d60e94d886e455e6a3409bba9ed",
        "build_date": "2021-10-07T21:56:19.031608185Z",
        "build_snapshot": false,
        "lucene_version": "8.9.0",
        "minimum_wire_compatibility_version": "6.8.0",
        "minimum_index_compatibility_version": "6.0.0-beta1"
    },
    "tagline": "You Know, for Search"
}

5.修改es使用的参数.编辑D:\elasticsearch\elasticsearch-7.15.1\config\elasticsearch.yml文件

# 增加新的参数，这样head插件可以访问es
http.cors.enabled: true 
http.cors.allow-origin: "*"
@注意，设置参数的时候:后面要有空格！

6.修改完配置将es重启,浏览器访问 http://localhost:9100

三、安装ElasticSearch-head插件

1、安装node环境
网址：https://nodejs.org/en/download/ 下载Windows版msi的,下载完直接安装，一直确定

安装完后cmd查看版本node-v

2、安装grunt

grunt是一个很方便的构建工具，可以进行打包压缩、测试、执行等等的工作，5.x里之后的head插件就是通过grunt启动的。因此需要安装grunt.

npm install -g grunt-cli

查看版本号 grunt -version

3.下载head插件

1.网址:https://github.com/mobz/elasticsearch-head下载安装包

2.解压

3.进入head文件夹下，执行命令：npm install (此处是为安装进行安装pathomjs)

如果安装速度慢,设置成淘宝的镜像重新安装 npm config set registry https://registry.npm.taobao.org

如果报错, 考虑打开具有管理员权限的cmd窗口执行命令或重启试试

4.安装完成之后npm run start或grunt server,启动head插件

四、ElasticSearch安装为Windows服务

1.elasticsearch的bin目录下有一个elasticsearch-service.bat

2.cmd 进入bin目录下执行: elasticsearch-service.bat install

3.查看电脑服务es已经存在了

elasticsearch-service.bat后面还可以执行这些命令
install: 安装Elasticsearch服务
remove: 删除已安装的Elasticsearch服务（如果启动则停止服务）
start: 启动Elasticsearch服务（如果已安装）
stop: 停止服务（如果启动）
manager:启动GUI来管理已安装的服务

Mysql基本函数

# 判断123是否为数值,如果是则返回0否则返回1
SELECT ('123' REGEXP '[^0-9.]');

# GROUP_CONCAT 指定分隔符
GROUP_CONCAT(uid SEPARATOR ';\n')

# GROUP_CONCAT 有长度限制,默认1024
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

清理包含指定条件的清理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;