sql面试题

mysql优化

优化准则：

建表时：合理选择字段的类型，单表字段数量

sql查询尽量单表操作，避免复杂操作，复杂的多表通过java代码实现
构建复合索引优化，索引尽量可以覆盖主要业务查询
sql避免索引失效
避免大事务
谓词前置，能过掉多数数据的条件往前放

索引失效场景：

https://zhuanlan.zhihu.com/p/220028437

1. 使用!= 或者 <> 导致索引失效
2. 类型不一致导致的索引失效
3. where条件中有计算

SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';`
SELECT * FROM `user` WHERE age - 1 = 20;

4. OR引起的索引失效
   OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

SELECT * FROM `user` WHERE `name` = '张三' OR height = '175';

5. 模糊搜索导致的索引失效
   模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。

SELECT * FROM `user` WHERE `name` LIKE '%冰';

6. IS NULL不走索引，IS NOT NULL走索引
   如果没有必要的要求必须为NULL，那么最好给个默认值空字符串

7. 复合索引 不遵循最左匹配原则会不走索引

EXPLAIN SELECT * FROM `user` WHERE name = '冰峰' AND sex = '男';

事务失效：

spring 事物是通过aop动态代理实现的，所以没有办法通过aop动态代理的情况会导致事物失效

1. 方法的类未被 spring 管理
2. 类内部的方法相互调用
3. 方法是私有方法
4. 被 final或者static修饰的方法
5. 多线程调用，子线程中的事物会失效
6. 自己tray catch的异常
7. 数据库引擎是myisam
8. 手动抛了别的异常
   上面的这种情况，开发人员自己捕获了异常，又手动抛出了异常：Exception，事务同样不会回滚。

因为 spring 事务，默认情况下只会回滚RuntimeException（运行时异常）和Error（错误），对于普通的 Exception（非运行时异常），它不会回滚。

@Slf4j
@Service
public class UserService {
    
    @Transactional
    public void add(UserModel userModel) throws Exception {
        try {
             saveData(userModel);
             updateData(userModel);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new Exception(e);
        }
    }
}

分页查询

使用limit分页时的MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。【阿里巴巴JAVA开发手册】

select * from big_table where data_type in (1,2) limit 1000000,10; ## 耗时：39.23s

1.基于主键ID实现查询优化分页查询优化

select * from big_table where id > (select id from big_table where data_type in (1,2) limit 1000000,1) limit 10; ## 耗时：0.716s

优化缺陷：如果主键不是自增长的（如uuid方式），则此方法失效；如果查询要求中需要针对一些字段进行过滤查询，如big_table中有一个data_type的字段，取值范围1,2,3。现在在查询中我们固定使用1,2进行查询，那么此方法也会失效，因为你根据条件筛选出最大的id后，后面10个数据，不一定都是你想要的，可能包含data_type=3的数据。

2.基于主键ID实现查询优化分页查询优化

select * from big_table a inner join (select id from big_table where data_type in (1,2) limit 1000000,10) b on a.id = b.id; ##耗时：0.69s

先快速定位需要获取的id段，然后再关联, 避免回表查询