写在前面

不知不觉小巴哥面试题一天一个面试题系列已经更新了一周,细心的小伙伴可以发现之前讲的那些都是些概念经验类的问题,那么今天,我们来更新一篇专业一点的内容,sql 优化问题,这个问题如果没有开发经验或者没有系统学习过数据库的小伙伴来说,可能只能说出一个到两个相关点,这样对于面试官来说可能不是一个满意的答案.

题外

有的小伙伴也关心另一个问题,就是开发可以学习数据库优化,测试为什么要学呢?我们工作中又遇不到这种问题.我总结有以下几点:

• 现在的面试,都是面试造飞机,入职拧螺丝.面试题都是一些难度较高的问题,也不能说这样完全错误,因为面试官和面试者是一个陌生的关系,相互不了解,回答上难度较高的问题也会给面试加分,回答不上,可能也不会影响面试结果.
• 开发同学平时会对线上 sql 进行优化,同时也会提交给测试,如果不懂这些,那只能做一些基础的业务回测.
• 测试开发同学是需要掌握该技能的,平时线上数据量较大的时候,就容易在你的代码中出现慢 sql 问题,应该有这个能力处理这些问题.

回归正题

小巴哥其实之前也没有系统的学习过,但是有一次做一个数据上报的功能,就是客户端同学把用户行为报错上报给服务端,我们根据在测试平台增加这些问题的报警和监控任务.因为是线上环境,用户量较大,所以就遇到了慢 sql 问题,当时请教了很多次开发同学,才解决了这个问题,后来,小巴哥励志把 mysql 系统的学习了一遍,现在把学习成果分享给大家,也让大家能更好的应对面试和工作中的问题.

回答

• sql 优化的法则
  • 法则一：只返回需要的结果
    
  • 法则二：确保查询使用了正确的索引
    
  • 法则三：尽量避免使用子查询
    
  • 法则四：不要使用 OFFSET 实现分页
    
  • 法则五：了解 SQL 子句的逻辑执行顺序
• 根据该法则书写 sql
  • 尽量使用确定的列名,少用 * 号
    
  • 尽量少嵌套子查询 (比较耗费 cpu 资源)
    
  • 避免使用耗费资源的操作
    
    • distinct
      
    • union 或者 union all(两个结果集相加)
    • order by
    • minus(两个结果集相减)
    • intersect(两个结果集交集)
    • 对于 or 比较多的查询,建议差分成多个 union all 连接起来
• 索引的讲解
  • 索引分类
    
    • 主键索引
      
    • 唯一索引
      • 唯一索引不止用于提升查询性能，还用于保证数据完整性。唯一索引不允许向表中插入任何重复值
      • 创建语句 CREATE UNIQUE INDEX index_nameon table_name (column_name);
        
    • 普通索引
      
      • 单列索引基于单一的字段创建
        
      • 创建语句 CREATE INDEX index_nameON table_name (column_name);
    • 全文索引
      
    • 组合索引 (聚簇索引)
      • 聚簇索引在表中两个或更多的列的基础上建立
        
      • 创建语句 CREATE INDEX index_name on table_name (column1, column2);
        
    • 注意
      
      • 创建单列索引还是聚簇索引，要看每次查询中，哪些列在作为过滤条件的 WHERE 子句中最常出现。
        
      • 如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列，那么聚簇索引就是最好的选择。
        
  • 可以建立索引的查询条件
    • where 所查询的条件
      
    • join on 所关联的条件
      
    • group by 和 order by 条件
      
  • 索引失效的集中情况
    
    • 索引列中 增加函数运算
      
    • 范围条件后的索引条件会失效（例:select * from table age> 50 and height =1.5）
      
    • 尽量使用覆盖索引 (查询列和索引列一致)
      
    • like % 在前不使用索引、% 在后使用索引
      
    • 索引类型转换（字符类型的引号不能去掉，会进行类型转换，不使用索引）
• mysql 执行计划
  
  • explain 查询 sql 执行计划
    
  • table（代表哪张表的执行计划）
    
  • type
    • const>eq_ref>ref>range>index>all
      
    • const（用到了主键索引）
    • eq_ref (使用了唯一索引)
    • ref(使用索引查到多个数据,但非唯一索引)
    • range (在索引中使用了范围查询)
      
    • index (全索引扫描)
    • all(全表扫描,没用到索引)
      
  • possible_keys（可能用到的索引）
  • key（实际用到的索引）
    
  • key_len (索引的长度,越小越好)
    
  • ref(索引列的类型)
    
  • rows(涉及多少行记录)
    
  • extra (其他说明)
    
• 除了索引还有一些解决 sql 优化问题的方法
  • 创建视图
    
    • 创建的也是 CUSTOMERS_VIEW 视图，查询视图可以增加查询速度
      
    • 语句 CREATE VIEW CUSTOMERS_VIEW ASSELECT name, ageFROM CUSTOMERSWHERE age IS NOT NULLWITH CHECK OPTION;
      
  • mybatis 二级缓存、懒加载
    
  • 绑定 id
    
  • 设计表的时候严格根据数据库的设计范式来设计数据库
  • 使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘 IO
  • 优化硬件;采用 SSD，使用磁盘队列技术 (RAID0,RAID1,RDID5)
  • 采用 MySQL 内部自带的表分区技术，把数据分成不同的文件，能够提高磁盘的读取效率
  • 垂直分表;把一些不经常读的数据放在一张表里，节约磁盘 I/O
  • 主从分离读写;采用主从复制把数据库的读操作和写入操作分离开来
  • 分库分表分机器 (数据量特别大)，主要的的原理就是数据路由;
  • 选择合适的表引擎，参数上的优化
  • 进行架构级别的缓存，静态化和分布式
  • 采用更快的存储方式，例如 NoSQL 存储经常访问的数据。
• sql 语句的解析顺序
  1. from + (table) 2.where + (where 条件) 3.group by +(group by list) 4.having +(having 条件) 5.select + (select list) 6.order by + (order by 的条件)

写在最后

我是小巴哥,一个陪你成长,实实在在分享 测试干货职场经验的人,欢迎关注!!!