数据库sql查询优化调优

我只是一个开发，平时也零零散散的了解过sql优化点， 但是面试的时候，无法系统的说明白。 所以在这里简单的总结梳理下。 下图中的知识点我也只是简单的了解的，有错误遗漏之处欢迎指点。

建表注意事项

如果我们建表的时候就把一些问题考虑到， 那么在查询的时候也会大大提高性能的。 常见的注意点如下。

把常用字段提取出来建表

比如 有学生表（学号， 姓名，年龄，爱好， 才艺， 格言， 评价， 自评，其他A, 其他B，其他C，其他C，其他C )；

可以看到这个表包含很多的字段，但是往往常用的字段就是前面三个字段， 后面的一堆字段平时都不会有查询的情况。

可以把前面的四个字段提取出来建立一个表专门用来查询， 后面的字段在另外一张表上用学号来关联。

把Text等超长字段单独存放到冗余表中

这个其实和上面的思想一致， 因为Text超长字段查询会大大的拖慢速度， 把其单独的提取出去，只在需要的时候查询。

与其他表关联的外键建立索引

建立索引了后， 联表查询速度很快

适当的冗余字段

比如成绩表如果只是关联学生表的学号， 那么在查看成绩单的时候，还需要联表查询下学生的姓名， 我们可以把学生姓名冗余到成绩表中， 这样避免了一次联表的操作。

但是注意冗余了字段，就意味着你需要维护多处地方， 如果学生的姓名修改了， 你除了需要修改学生表姓名， 还需要修改成绩表中的姓名。 所以冗余的字段最好是不会改变的字段。

选择合适的字段类型

• 时间使用timestamp类型，
• ip地址不要使用字符串， 可以使用int类型
• 年龄字段不要使用bigint

建立合理的索引

建立索引是一种性价比最高的一种优化手段。 但是索引建立也需要注意一些事项

值差异大的字段适合建索引

像性别字段，只有两个值的情况，建立索引没有意义。 但是像身份证号每个人都不一样的字段很适合建立索引， 如果确定字段的值不会重复的情况， 最好建立唯一索引。

查询频繁的字段建立索引

比如经常作为查询条件的字段， 建立索引的性价比高。 如果把索引建在一个不会常用作查询条件的字段上， 好久都不会查一次， 反而维护索引还消耗数据库性能。

使用覆盖索引避免回表

如果一个查询在索引中就能够找到其所有的需要的字段值， 就没有必要进入表中查询了。 我们可以使用这个特性， 建立联合索引，把经常查询的字段都放入索引中。

但是需要注意联合索引中字段的顺序。

不要建立过多的索引

索引可以极大提高查询的效率， 但是对于写入是不友好的， 每次写入都要重新维护下索引。 所以建立了过多的索引不进占用大量空间还会导致写入变慢

使用Explain查看下Sql的执行计划

我们可以使用explain 来查看下sql的执行计划，然后合理的调整sql语句， 避免写出影响性能的sql。

• select_type
  查询的类型， 是指简单查询还是子查询等
• table
  查询使用到的表
• type 这次查询的类型。 注意和上面的select_type不一样

all ：表示全表扫描， 查询没有用到索引， 是最坏的情况
ref ：索引命中记录了， 希望碰到的情况
index： 扫描全部索引， 比全表扫描好一点
ref_eq ： 索引命中，并且结果只有一个， 常见于唯一索引的等于查询
const ： 主键等于查询， 直接转换成常量， 最快。

• possible_keys: 可能会用到的索引。
  列在这个字段上的索引可能会用到， 给你参考的

• key 真正实际使用到的索引

• key_len 真正用到的索引的长度，太长的索引并不好

• rows: 本次查询扫描的行数， 数字并不是精确的， 扫描的函数越少越快

• filtered: 符合条件的记录数占检索记录数的百分比

我们在每次写sql语句的时候还是提倡使用这个来看下sql的执行计划。

sql语句的注意事项

在写sql的时候避免一些坑也能提高查询效率。

避免使用子查询

我们可以使用联表查询来代替

大型表的分页查询limit 需要优化

语句 select * from table limti 100w, 10 在大表中执行是很慢的。 如果我们改成下面的形式

select id from table limit 100w, 1
select * from table  where  id > ${id}  limit 0 ,10   

这样就把一个全表扫描的操作 拆成了根据主键id来查询的两步操作， 我在自己的项目中试过， 在900w的表中， 前者耗时29s左右， 后者优化好耗时在2.3s 。

避免类型转换 无论是隐式的还是显式的

select * from table where phone = 18788888888

在上面的语句中，phone 手机号是一个字符串，但是我的查询条件传入的却是一个数字， 这条语句，在查询的时候mysql会把phone转换成数字后在比价。

由于在phone中加上了隐式的转换类型的函数， 这个语句就不会使用phone上的索引了。

当然显式的使用sub函数等等的mysql函数， 都会使得索引失效， 所以我们可以在调用sql之前通过代码把类型转换正确。

避免字符串的编码转换

比如我们联表查询的时候，连接的两张表的编码不一致， 作为on条件的连接字段就不会走索引了

使用预编译查询

一个sql语句的执行流程大致如下。

图来自： https://www.cnblogs.com/lansetiankongblog/p/10968984.html

一条sql语句需要经历词法分析预发分析，调用计划等等编译操作，如果我们使用预编译sql， 那么在后面的查询中可以省略掉这些编译过程， 极大提高sql的执行效率。

避免使用having

having是需要把查询结果查出来后在进行计算条件的， 效率低下。 完全可以在where中就把条件过滤掉

使用union all 代替 union

对于不会重复的记录或者重复也没有关系的记录直接使用union all 。
union会去重复记录， 去重耗时较长

必要的情况才使用事务

事务是一个很重要的功能，但是我们的日常使用中，有很多的场景完全可以不用事务。

使用合理框架和设置缓存等其他手段

使用连接池

应用中建立数据库连接是一个很耗时间的操作， 所以使用连接池可以提高连接使用率。

• 连接池设置合理的最大和空闲连接数
• 设置合理的连接存活时间
• 监控慢sql语句，推荐使用druid数据连接池

使用缓存

mybatis 可以使用一级二级缓存

mysql设置合理的参数

• innodb_buffer_pool_size 的设置
  对于写多读少的场景， 可以设置的大点。
  读多写少的场景，要设置小点。
• 其他的buffer的设置， mysql有很多的缓存等参数的设置， 设置合理也会提高性能

这部分的内容我了解的不太清楚的。 所以简单说下