在涉及量可达上百万,甚至于上千万的数据进行查询、统计、汇总等等操作场景下,数据库系统优化就尤为重要。

数据库优化的几个方面


优化选择:
优化成本:硬件 > 系统配置 > 数据库表结构 > sql及索引
优化效果:硬件 < 系统配置 < 数据库表结构 < sql及索引

数据库查询响应数据慢的原因

就数据库优化成本选择sql及索引而言。

  • 第一点:查询数据量比较大

  • 第二点:表设计不合理

  • 第三点:sql语句不合理

  • 第四点:没有合理使用索引
    了解数据库响应慢的原因,需要我们首先知道查询语句在数据库执行的过程。继续看

一条查询语句的执行过程

  • 在之前深入数据库系列一章节中介绍过查询语句的执行流程,一条语句需要经过查询缓存,解析器,优化器,执行器之后再调用存储引擎的api进行数据的过滤、获取。
  • 我们知道一条sql语句在经过解析器词法分析、语法分析后,在执行前会进入到优化器进行优化。
  • 深入数据库系列一章节中讲到,数据库会在在优化器模块中会选择不同的索引和执行顺序对语句进行优化,而优化器的目的是为了什么呢,是为了得到目标SQL的执行计划。
  • 查看一条查询语句的执行计划如下:
    select_type:SIMPLE  查询类型
        table:扫描到的表/表别名
         type:ALL 表扫描方式
    possible_keys:可能使用到的索引
          key:被使用到的索引
      key_len:使用过的索引长度
          ref:
         rows:表数据行数
        Extra:额外说明
    //参数值自查

数据库优化点

优化包括两个方面:sql优化和架构优化。(内容后续跟进补充)

sql优化

    1. 执行计划
    1. 索引
    1. SQL改写

架构优化

    1. 高可用架构
    1. 高性能架构
    1. 分库分表

19条MySQL优化方法

    1. 善用explain查看sql执行计划
    1. 语句in包含的值不应该过多
      where 字段 in(值a[,值b,值c,...]);

      mysql会对in包含的值存储在一个数组里,排好序的,值越多耗费也就越大。如果包含的值是连续的,可使用between代替

    1. select语句必须指定字段
      刚开始接触数据库,很多人试验时都喜欢使用select * from 表来查询数据。但是我们想想,如果使用all查询,是不是就包括很多结果集以外的数据,产生不必要的系统耗费。
      show fields from 表;
    1. 当只需要一条数据记录时,使用limit 1
      取一条记录不就是用limit的吗?
    1. 如果排序字段没有用到索引,就尽量少排序
      不排序还叫排序字段吗?
    1. 如果条件字段没有用到索引,尽量少用or
      如果or两边的字段不是索引,当然就会造成查询的时候不走索引的情况,使用unoin/union all会有更好的结果
    1. 尽量使用union all来代替union
      接着第六点比较这两个的差异,union需要将结果集合并后,再进行唯一性过滤操作,其中就会涉及到排序;当然使用union all前提条件是两个结果集没有重复数据,因为它不会将结果集唯一性过滤。
    1. 不使用order by rand()
    1. 区分in/exists,not in/not exists
      • in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。共同点都是先执行数据小的表。
        比如说:
        select * from 表a where id in ( select id from 表b)
        select * from 表a where exists (select * from 表b where 表b.id=表a.id)

        exists的使用看着可能有点乱,记得简单易学的数据库四:数据查找语句-select子查询in讲到的,exists判断字句查询是否为空,不为空则返回true,否则返回false,数据库系统根据语句调用存储引擎接口一句句查找时,就会找到符合条件的结果。

      • not in 可能会存在逻辑方面的问题,推荐使用not exists
        select colname … from A表 where a.id not in (select b.id from B表)
        转为高效sql语句
        select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
    1. 合理使用分页
      select id,name from product limit 866613, 20

      使用上述SQL语句做分页的时候,随着表数据量的增加,直接使用limit分页查询会越来越慢。可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612,优化语句如下:

      select id,name from product where id> 866612 limit 20
    1. 分段查询
      一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示。
    1. 避免在where子句中对字段进行null值判断
      对于null的判断会导致引擎放弃使用索引而进行全表扫描。
    1. 不建议使用%前缀模糊查询
      例如LIKE"%name"或者LIKE"%name%",这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE "name%"。
      那如何查询%name%呢?
      答曰:使用全文索引 (看看前文:简单易学的数据库五:高级操作-自定义函数、存储过程、索引中需要留意的地方)
      //这里再回顾一下全文索引的创建和使用
      //添加索引
      ALTER TABLE 表 ADD FULLTEXT INDEX 索引名称 (使用索引的字段/user_name);//字段值就是%name%需要查找的
      //还记得全文索引怎么使用吗??
      select 字段 from 表 where match(使用索引的字段/user_name) against('zhangsan' in boolean mode);
    1. 避免在where子句中对字段进行表达式操作
      对字段就行了算术运算,会造成引擎放弃使用索引。
      select user_id,user_project from user_base where age*2=36;
      //优化后
      select user_id,user_project from user_base where age=36/2;
    1. 避免隐式类型转换
      where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型。
    1. 对于联合索引来说,要遵守最左前缀法则
      举列来说索引含有字段id、name、school,可以直接用id字段,也可以id、name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面。
    1. 必要时可以使用force index来强制查询走某个索引
      有的时候MySQL优化器采取它认为合适的索引来检索SQL语句,但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。
    1. 注意范围查询语句
      对于联合索引来说,如果存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效。
    1. 关于JOIN优化
      这里指的是内连接,左外连接,右外连接。
      • 在没有全连接情况下,使用union all(前文:简单易学的数据库四:数据查找语句-select有介绍)
      • 尽量使用inner join,避免left join
        参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。
      • 合理利用索引
      • 利用小表去驱动大表
        跟第九点类似
      • 巧用STRAIGHT_JOIN
        inner join是由MySQL选择驱动表,但是有些特殊情况需要选择另个表作为驱动表,比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序,在STRAIGHT_JOIN左边的表名就是驱动表,右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接,也就是inner join。其他链接不推荐使用STRAIGHT_JOIN,否则可能造成查询结果不准确

参考:
MySQL 优化实施方案:https://www.cnblogs.com/clsn/p/8214048.html
mysql的优化器执行过程:https://blog.csdn.net/LJFPHP/article/details/89157361
mysql之优化器、执行计划、简单优化:https://www.cnblogs.com/lbg-database/p/10108513.html