jdbc sql优化记录

记录一下

十万级数据库批量删除操作

逻辑上来说应该是很快的，但是需要联表查询，速度慢了下来

最开始是先查表a,查到数据再去操作表b，效率较低

总共要查询3000个数据

先修改一番，单个操作sql如下：

SELECT * from table_b where name like (select concat((select SUBSTRING_INDEX(e.name,'-',1) from table_a e WHERE e.id= ?),'%'));

效率还行，从原来的20多分钟到现在的10分钟左右，其实也就是减少了一半的数据库io操作

觉得10分钟也挺长的，想着如果能再减少一些io操作，效率会不会更快，找到了正则表达式

SELECT * from table_b where name REGEXP (select GROUP_CONCAT(SUBSTRING_INDEX(e.name,'-',1) separator '|') from table_a e WHERE e.id in ( :ids);

刚开始是一次性把要查询的字段传过去了，发现结果不对，分析了一下，是从table_a拿的数据太多，组合字符串的时候部分内容丢失了

改为每次查询100条数据，结果正确了，但是时间不太乐观，又上升到了20分钟，看来正则并没有想象中的效率高

使用下面的sql与上面的like操作比较

1 . SELECT * from table_b where name REGEXP (select concat('^',SUBSTRING_INDEX(e.name,'-',1)) from table_a e WHERE e.id = ?);
2 . SELECT * from table_b where name REGEXP (select SUBSTRING_INDEX(e.name,'-',1) from table_a e WHERE e.id = ?);

十万级的数据，结果为30条
耗时分别为 1. 1.125s; 2. 0.087s; 3. 0.001s
第三为上面的like语句，结果很明显，like操作较快

在搜索时，看见有人说用instr比较快，比like快，尝试一下： 

SELECT * FROM table_b where instr(name, (select SUBSTRING_INDEX(e.name,'-',1) from table_a e WHERE e.id= ?));

执行时间为0.108s，好吧，还是放弃吧

后面，又对like语句进行了一下优化：

SELECT * from table_b where name like (select concat(SUBSTRING_INDEX(e.name,'-',1),'%') from table_a e WHERE e.id= ?);

单个语句执行的时候看不出效果，批量执行的时候，效果比之前的like语句要好很多，效率差不多有一倍差距

当然，个人情况，相对来说比较主观，按道理来讲，regexp一般应该比like要快的 ，所以各位看情况选择