面试整理-数据库-SQL

1. sql的执行顺序

from —> left join on —> where —> group by —> having —> select —> distinct —> order by

2. sql优化方法

1. 避免全表扫描，加索引(联合索引，避免回表)

2. 使用缓存

3. 读写分离

4. 分库分表

5. 架构优化（如便于高速搜索，把冗数据余存储在es中，但需要保证数据库和es的数据一致性)

3. mysql查询列为null时的坑

假如user表共10条数据，有两条数据name列为NULL

3.1 count数据丢失

select count(name) from user (结果为8)；可用select(*)或select(id)

3.2 distinct数据丢失

select count(distinct name, phone) from user (结果为8)

3.3 使用了<>，>=，<=，!=查询，数据丢失

select * from user where name != ‘无名' (结果为8)；可用where name != ‘无名' or isnull(name)

4. 最佳左前缀法则的原理

索引失效主要是针对联合索引，原因就是破坏了最佳左前缀法则

a. 索引a有序，索引b无序

b. 在索引a想到等的条件下，b是有序的

1. select * from temp where a > 1 and b =1 (a>1的情况下，b是无序的，索引失效，会全表扫描)

2. select * from temp where a = 1 and b =1 (a=1的情况下，b是有序的，索引有效，走索引查询)

3. select * from temp where a like ’%1‘(’%1%‘) (类似case1，索引失效)

4. select * from temp where a like ’1%’ (类似case2，索引有效)

5. 单表1000W条数据的查询方案

使用分页查询，针对查询字段创建唯一索引，减少不需要的列，同时可采用子查询方式

SELECT * FROM `user_operation_log` LIMIT 1000000, 10；(耗时4818ms)

SELECT id FROM `user_operation_log` LIMIT 1000000, 1；(无索引4329ms，有索引199ms)

SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10；(无索引4319ms，有索引201ms)

缺点：只适合id自增