2020 - 面试刷题 ING（Sql优化知识点）

1. Sql设计的三大范式： 
   1. 列不可再分 (例：姓名&年龄)
   2. 每列都与主键关联 (例：姓名｜年龄｜班级地址)
   3. 每列都与主键直接关联，而不是间接  (例：姓名｜年龄｜班级)

================================

1.Explain执行计划如何查看 ?

看type ｜ key｜ rows  |  extra

2.Mysql中查询连续数值范围是如何选择 Between 和 In ，为什么？

• 优选between
• 分析：在索引的列上
  1. in表示等值查询，有N个数就查询N次，然后合并返回
  2. between是范围检索，就深度查询一次，查询到第一个符合之后根据索引结构B+tree，遍历后续符合条件的叶子结点
• mysql中between和in的区别_两菜一汤、的博客-CSDN博客_between和in的区别

3.SELECT语句务必指明字段名称

• 当使用< select * > 的时候会增加很多不必要的消耗，(CPU | IO | 内存｜ 网络带宽)
• 而且指明字段可以使用覆盖索引，当条件中无索引生效时，这是非常有效的方式

4.当只需要一条数据的时候，使用limit 1

• 当设置limit 1，只要查到符合条件的一条数据之后就停止
• 执行计划中能看到type=const

5、如果排序字段没有用到索引，就尽量少排序 

• 第一种 filesort 排序

  • 非索引字段进行排序会使用filesort cache buff，非常消耗内存，速度还慢

• 第二种  索引排序  

  • mysql-索引优化_java易学堂的博客-CSDN博客

  • select 字段名 -（需要注意字段的位置）

6、如果限制条件中其他字段没有索引，尽量少用or

• or连接的前后条件，只要有一个没有索引，会导致前后条件都不走索引

7、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

8、不使用order by rand()

• rand()放置在order by 后边进行排序的时候会执行多次，效率低下
• You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.  搜索Google，网上基本上都是查询max(id) * rand()来随机获取数据。
• 例： mysql 随机选取一条记录 – rand() 坑 | 码农家园

  select  id  from `TableA`  order by rand()  limit 1000;

  上面的SQL语句，可优化为：

  select id from `TableA` t1  join  (select  rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nid  limit 1000;

9、区分in和exists、not in和not exists

• 区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），
  • 如果是exists，那么以外层表为驱动表，先被访问
  • 如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
• 关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题

11、分段查询

数据量大之后分页查询速度会变的非常缓慢，但是通过分段查询，循环便利，可以处理这个问题

13、不建议使用%前缀模糊查询

• 但如果需要有这个查询功能怎么办 ？全文索引
• 在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like %zhangsan%; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。
• 创建全文索引的SQL语法是：
• ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);
• 使用全文索引的SQL语句是：
• select id,fnum,fdst from dynamic_201606 where match(user_name) against(zhangsan in boolean mode);
• 注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

14、避免在where子句中对字段进行表达式操作 ｜ 函数操作

对字段就行了算术运算 ｜ 函数操作，会造成引擎放弃使用索引

15、避免隐式类型转换

where varchar字段=123456;
等价于
where CAST(字段 AS signed int) = 123456;/*此字段上面有索引*/

而条件字段上使用了函数就不会走索引了，索引这个效率非常低 MySQL性能调优——函数、隐式类型转换、隐式字符编码对性能的影响_沐沐沐晨风的博客-CSDN博客

16、对于联合索引来说，要遵守最左前缀法则

左前缀法则，不愿多说

17、必要时可以使用force index来强制查询走某个索引

18、注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。所以这几类条件都放在语句最后

19、关于JOIN优化

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

注意⚠️： 驱动表数据的多少 决定X->Y的嵌套请求次数的多少，最终是合并结果，（A*B的遍历数是固定的）

1. 尽量使用inner join表，小表驱动大表
2. 合理使用索引：on上字段带索引
3. 巧用Straight-join