java之sql优化集合

1.大分页查询,先查询id,在查具体数据,减少回表次数

select id from xxx;
select * from xxx where id in (上面语句结果);

2.多表left join查询

多表left join 查询1

原始sql

select a.*,b.* 
 from xxx as a
left join yyy as b on a.id=b.aid
where a.type=1

可先子查询a表，用子表再left join b表
减少笛卡尔积次数

多表left join 查询2

如果出现多表left join 的情况,并且left join 表比较巨大
可分拆sql,单个sql的left join 不超过3个,在内存中,java8 stream工具将数据拼装,处理

3.利用索引

• 联合索引>单字段索引，能用联合索引就用联合索引
• explain 分析索引

避免索引失效

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引

-- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

-- 字符串与数字比较不使用索引;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字

-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

4.对于大表使用 pt-online-schema-change 修改表结构

• 避免大表修改产生的主从延迟
• 避免在对表字段进行修改时进行锁表

对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。

pt-online-schema-change 它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。把原来一个 DDL 操作，分解成多个小的批次进行。

Percona Toolkit工具使用介绍 - yangjiahui - 博客园