sql部分笔记

• in查询优化
  使用in语句查询，可能会导致页面查询慢，甚至进一步影响java虚拟机内存溢出。可使用 exists语句的方式代替in语句（两者的查询时间复杂度一个是O（1），一个是O（n）， in拼接的参数越多时， 查询效率差距越明显）

select * from tb_user t where concat(t.user_id, '_', t.username)  in ('1_Tom','2_Jerry','3_Nacy');
使用exists替换：
1.
select * from tb_user t where 
 1=1 and 
 exists (
 select 1 from 
 (select '1_Tom' as id union all select '2_Jerry' union all select '3_Nacy')
 ids where ids.id = concat(t.user_id, '_', t.username);
 );
2.
select * from tb_user t left join (select '1_Tom' as id union all select '2_Jerry' union all select '3_Nacy') ids
on ids.id = concat(t.user_id, '_', t.username);

使用exist union all时，将会构造一个虚拟表ids，底层将使用哈希集

• 回表查询
  指的是mysql的二级索引无法直接查询到所有列的数据，所以通过二级索引查找到聚簇索引再查询到所有列的数据，这种不能通过一颗索引树获取SQL所有列数据的情况就叫回表。虽然使用了索引，但查询了多次。
  如果explain查看执行计划，在Extra中看到了Using index condition则表示通过是二级索引回表。
• 索引覆盖
  当explain的输出结果Extra字段为Using index时能触发索引覆盖，即索引包含/覆盖所有需要查询的字段的值，只需扫描索引而无须回表。
• 索引覆盖的常见方法
  将被查询的字段，建立到联合索引里去。
• 索引下推优化（ICP优化）
  首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。