oldwu's Blog - mysql
type: channel
link: https://blog.oldwu.top/index.php/tag/mysql/
items: Mysql使用binlog来恢复误操作的数据
items: Mysql截取字符串
items: MySQL中orderby和limit分页数据重复的问题
items: 使用JDBC获取Mysql数据库中符合条件的备份表并清理n天之前的表
items: 解决mysql in查询不使用索引的问题
items: mysql查询order by奇慢无比的解决办法

Mysql使用binlog来恢复误操作的数据
type: item
date: 2023-02-22T18:57:00+08:00
description: Mysql使用binlog来恢复误操作的数据以下操作基于开启了binlog并且binlog格式为raw时才可以，否则不适用于本教程本教程使用了一个github上的开源工具来辅助操作，因为mysql自带的binlog工具操作起来过于繁琐，新手不易理解初步操作检查binlog是否开启show variables like '%log_bin%'如果log_bin一项为ON则为已经开启检查binlog是否为raw格式记录show variables like '%binlog_row_image%'如果binlog_row_image一项为FULL则为raw格式记录至此，只要binlog没被删除，数据就可以正常恢复查看binlog列表show master logs;数字编号最大的一般都是现在正在使用的binlog，如果数据刚刚删掉，那么记下现在正在使用的binlog的Log_name，比我这边这个是mysql-binlog.000063，如果你要恢复更早以前的数据，请确保你已经定位数据所在的binlog，这里不再说明准备环境请在Linux环境下操作！！！！！否则会出现一系列诡异的问题安装python3环境yum install python3如果原来装过了就不用装了，安装完毕后执行以下语句安装依赖，否则后面装依赖的时候可能会报错：pip3 install -U pip setuptools安装binlog2sqlclone项目（当然也可以直接github上下载）https://github.com/michael-liumh/binlog2sqlgit clone https://github.com/michael-liumh/binlog2sql.git cd binlog2sql安装依赖pip3 install -r requirements.txt如果使用的是mysql8.0，必须保证PyMySQL的版本等于0.9.3，如果版本不对，重新安装执行以下指令：pip3 uninstall PyMySQL pip install PyMySQL==0.9.3操作此工具的使用说明可以查看README.md，具体的命令都在下面：参数说明--help输出帮助信息-h, --host连接 MySQL 服务器的地址-P, --port连接 MySQL 服务器的端口-u, --user连接 MySQL 服务器的用户-p, --password连接 MySQL 服务器的密码（不指定这个参数也可以，默认会要求通过终端交互的形式输入密码，空密码直接回车即可）-d, --databases选择输出指定库的 SQL-t, --tables选择输出指定表的 SQL-id, --ignore-databases排除输出指定库的 SQL-it, --ignore-tables排除数据指定表的 SQL-ic, --ignore-columns过滤掉 SQL 中的指定的列--ignore-virtual-columns过滤掉 SQL 中的虚拟列--start-position, --start-pos指定 binlog 的起始位点--stop-position, --stop-pos指定 binlog 的结束位点--start-datetime解析 binlog 中指定开始时间后的 SQL--stop-datetime解析 binlog 中指定结束时间前的 SQL--include-gtids只输出指定 gtid 的 SQL--exclude-gtids不输出被排除 gtid 的 SQL--only-dml只输出 DML 类型的 SQL（排除DDL）--sql-type只输出指定类型的 DML（但不排除DDL，要排除DDL的话需要加上 --only-dml 参数）--stop-never持续不间断解析从运行脚本这个时间点开始，往后新增的 binlog 内容-K, --no-primary-key排除 SQL 中的主键字段-KK, --only-primary-keyUPDATE 与 DELETE 类型的 SQL 的条件只保留主键字段-B, --flashback生成回滚 SQL 而不生成 binlog 记录中的 SQL--replace将 INSERT 类型的 SQL 中的 INSERT INTO 关键字改为 REPLACE INTO--insert-ignore将 INSERT 类型的 SQL 中的 INSERT INTO 关键字改为 INSERT IGNORE INTO--result-file将生成的结果保存到指定的文件中（请直接指定文件名，不要添加目录，添加了也会自动去除，请用 --result-dir 指定目录）--record-file当使用 --…

Mysql截取字符串
type: item
date: 2022-10-18T13:53:16+08:00
description: Mysql截取字符串有一个结果集返回为这样122358485629275667_111986213 000000007370ba38017373f964e70097_root 00000000736bd81a01736bff3e5c243c_111986213 000000007397f5ab0173983cb16e0c20_111986213现要取_符号两边的字符取左边字符SELECT CASE WHEN INSTR( 需要截取的字段, '_' )> 0 THEN LEFT( 需要截取的字段, INSTR( 需要截取的字段, '_' ) - 1 ) -- 写成这样是为了避免分割符不存在 ELSE 需要截取的字段 END FROM XXXX结果：取右边字符SELECT CASE WHEN INSTR( 需要截取的字段, '_' )> 0 THEN SUBSTR( 需要截取的字段, INSTR( 需要截取的字段, '_' ) + 1 ) -- 写成这样是为了避免分割符不存在 ELSE 需要截取的字段 END FROM XXXX

MySQL中orderby和limit分页数据重复的问题
type: item
date: 2022-08-25T21:25:35+08:00
description: MySQL中orderby和limit分页数据重复的问题背景有一段sql，根据状态值来排序，去掉limit一切正常，当加入分页查询时，第二页会出现第一个的结果SELECT rl.id, rl.itemName, rl.menuCode, IFNULL( rf.state, '0' ) state FROM `runmattersinterior_catalog` rl LEFT JOIN `rummatters_frequently` rf ON rf.createrid = 'ADMIN' AND rl.menuCode = rf.menuCode WHERE 1 = 1 ORDER BY rf.state DESC -- ,rf.itemName limit 0,20问题原因在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用了priority queue。使用 priority queue 的目的，就是在不能使用索引有序性的时候，如果要排序，并且使用了limit n，那么只需要在排序的过程中，保留n条记录即可这样虽然不能解决所有记录都需要排序的开销，但是只需要 sort buffer 少量的内存就可以完成排序。之所以MySQL 5.6出现了第二页数据重复的问题，是因为 priority queue 使用了堆排序的排序方法，而堆排序是一个不稳定的排序方法也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。MySQL 5.5 没有这个优化，所以也就不会出现这个问题。也就是说，MySQL 5.5是不存在本文提到的问题的，5.6版本之后才出现了这种情况。解决方法索引排序字段可以直接给排序字段加上索引，这样就直接按照索引的有序性进行读取并分页，规避掉这个问题使用另一个唯一字段进行第二次排序可以使用另一个唯一的字段，进行排序，比如标题，id等这些不重复的字段

使用JDBC获取Mysql数据库中符合条件的备份表并清理n天之前的表
type: item
date: 2022-06-20T15:41:53+08:00
description: 使用JDBC获取Mysql数据库中符合条件的备份表并清理n天之前的表在执行同步操作之后会备份原来的表，导致线上数据库表越来越多，需要写一个定时清理这边定时清理使用xxl-job作为执行器，当然也可以当成正常方法来手动触发获取当前数据库名称select database() as dbname获取这个数据库中存在的符合条件的表我这边获取BACKUP_开头的表select table_name as name from information_schema.tables where table_schema= '" + dbname + "' and table_name like '" + "BACKUP_ + "%' order by table_name至于为什么不直接用show tables，这玩意貌似没法加like条件或者as xxx字段，有可能是我没找到方法完整代码如下/** * 定时清理备份的表 * @param needClear 需要清理的原始表名，多个表以逗号分隔 * @param clearDay 清理期限 * @throws Exception exception */ @XxlJob("定时清理备份的表") public void clearExpireBackupTable(String needClear, int clearDay) throws Exception { String jobParam = XxlJobHelper.getJobParam(); if (StringUtils.isNotBlank(jobParam)) { //使用xxl-job传递的参数：system_user,system_dept,system_user_dept|20 String[] split = jobParam.split("\\|"); needClear = split[0]; clearDay = Integer.parseInt(split[1]); } //清理的表只能以BACKUP为前缀 String[] split = needClear.split(","); //首先拿到所有backup开头的表 //获取主数据库名称 Map<String, Object> database = baseDao.findOneJdbc("select database() as name"); String dbname = (String) database.get("name"); //获取所有前缀匹配的表 String sql = "select table_name as name from information_schema.tables where table_schema= '" + dbname + "' and table_name like '" + BACKUP_PREFIX + "%' order by table_name"; List<Map<String, Object>> backupTableList = baseDao.findListJdbc(sql); for (Map<String, Object> stringObjectMap : backupTableList) { String tableName = (String) stringObjectMap.get("name"); //获取到改备份表的原始表名，例如：BACKUP_system_dept_2022_06_13_11_43_25 String regx = BACKUP_PREFIX + "([\\s\\S]+)_([\\d]{4}_[\\d]{2}_[\\d]{2}_[\\d]{2}_[\\d]{2}_[\\d]{2})"; String tableRealName = ReUtil.get(regx, tableName, 1); if (StringUtils.isNotBlank(tableRealName) && StringUtils.equalsAny(tableRealName, split)) { String tableCreateTimeStr = ReUtil.get(regx, tableName, 2); DateTime tableCreateTime = DateUtil.parse(tableCreateTimeStr, "yyyy_MM_dd_HH_mm_ss"); //判断这个表的创建时间是否超过设定期限 if (DateUtil.betweenDay(tableCreateTime, new Date(), false) > clearDay) { /…

解决mysql in查询不使用索引的问题
type: item
date: 2022-03-17T16:01:00+08:00
description: 解决mysql in查询不使用索引的问题mysql 使用IN关键字时，在IN中写子查询会导致索引失效的问题，对于数据量很大的情况下查询会非常慢例如：select * from tb_file where id in (select id from meeting where userId = 'xxx')这个时候查询tb_file时将不会使用索引解决办法：使用join建立关系，上述只是简单的子查询，下面的方案适用任何情况，直接把子查询作为临时表select * from tb_file t join (select id from meeting where userId = 'xxx') RES on t.id=RES.id

mysql查询order by奇慢无比的解决办法
type: item
date: 2021-12-03T17:02:00+08:00
description: 老sqlSELECT DISTINCT RES.CREATE_TIME_ AS acflowlast_end_date_time, RES.PROC_INST_ID_ AS acflowprocess_instance_id, RES.NAME_ AS acflowtask_name, f.moduleId AS acflowmodule_id, RES.TASK_DEF_KEY_ AS acflowtask_definition_key, f.STATUS AS acflowprocess_status, f.userId AS acflowuser_id ,business_.* FROM ( SELECT RES_.PROC_INST_ID_ AS PROC_INST_ID_, RES_.CREATE_TIME_, RES_.NAME_, RES_.task_def_key_ FROM ACT_RU_TASK RES_ JOIN flow_instance f ON f.processInstanceId = RES_.PROC_INST_ID_ AND f.moduleId IN ('receipt-and-installation' ) UNION ALL SELECT RES_.PROC_INST_ID_ AS PROC_INST_ID_, RES_.END_TIME_ AS CREATE_TIME_, '已办结' AS NAME_, '' task_def_key_ FROM ACT_HI_PROCINST RES_ WHERE RES_.END_TIME_ IS NOT NULL ) RES JOIN flow_instance f ON f.processInstanceId = RES.PROC_INST_ID_ AND f.STATUS IN ( 1, 2 ) AND f.moduleId IN ('receipt-and-installation' ) JOIN apply_doc_currency business_ ON f.appId = business_.id AND business_.del = '0' WHERE 1 = 1 ORDER BY business_.createtime DESC LIMIT 20速度排查经过排查发现order by语句花费的时间较多，去掉order by只需要5秒钟既然order by排序花了接近30秒，就直接解决order by的问题，不再关注别的方面explain查看索引情况，发现最后使用了filesort排列由于排序字段所在的表数据量巨大，并且filesort不会使用任何索引，因此造成了order by巨慢而这个sql属于监控信息的查询，必须使用时间排序，因此删掉order by是不可能的了解决思路是强制使用createtime的索引，而不是使用filesort排序关于filesort （文件排序），可以参考这篇文章MySQL filesort原理及优化 - 知乎 (zhihu.com)对order by的字段添加索引在要强制索引的join语句表后 直接加上FORCE INDEX (索引名称) 来强制使用索引新sqlSELECT DISTINCT RES.CREATE_TIME_ AS acflowlast_end_date_time, RES.PROC_INST_ID_ AS acflowprocess_instance_id, RES.NAME_ AS acflowtask_name, f.moduleId AS acflowmodule_id, RES.TASK_DEF_KEY_ AS acflowtask_definition_key, f.STATUS AS acflowprocess_status, f.userId AS acflowuser_id ,business_.* FROM ( SELECT RES_.PROC_INST_ID_ AS PROC_INST_ID_, RES_.CREATE_TIME_, RES_.NAME_, RES_.task_def_key_ FROM ACT_RU_TASK RES_ JOIN flow_instance f ON f.processInstanceId = RES_.PROC_INST_ID_ AND f.moduleId IN ('receipt-and-installation' ) UNION ALL SELECT RES_.PROC_INST_ID_ AS PROC_INST_ID_, RES_.END_TIME_ AS CREATE_TIME_, '已办结' AS NAME_,…