MySql优化的几个维度

连接 - 配置优化

1. 第一个环节就是客户端连接到服务端,可以从 两个方面考虑优化
   1. 客户端
      1. 减少从服务端获取的连接数,如果我们想要不是每一次执行SQL都要创建一个连接,可以引用连接池,实现连接的重用
   2. 服务端
      1. 修改配置参数增加可用连接数
      2. 及时释放不活动的连接
         1. 默认28800–>修改缩小时间

缓存 - 架构优化

1. 缓存

   1. 在应用系统的并发访问量大的情况下,会导致两种情况
      1. 一方面是给数据库带来巨大的压力
      2. 另一方面对于应用层来说,操作的数据也会受到影响
   2. 我们可以采用第三方缓存来解决这个问题—>比如:Redis

2. 集群,主从复制

   1. 如果单台数据库服务器满足不了访问需求,我们可以采用数据的集群方案
   2. 做了主从复制的方案之后,我们只需要把数据写入Master节点,Slave只需要负责读取,实现读写分离

   需要注意数据一致性的问题

   • ​ 解决思路
     • 半同步复制
       • 等待其中一个从库接受到Binlog事务并成功写入Relay Log之后,才会返回Commit操作成功给客户端;如此半同步就保证了事务成功提交后至少有两份日志,一份在主库Binlog上,另一份在从库Relay Log上,从而进一步保证了数据的完整性,半同步复制很大程度取决于主从网络RTT(往返时延),以插件形式存在
     • 数据库中间件
       • 如果有了数据库中间件,所有的数据库请求都走中间件,这个主从不一致问题就可以解决
       • 所有的读写请求都走中间件,然后请求路由到主库,读的请求路由到从库
       • 但是我们中间件会记录写库的一个key,在设置一个允许同步时间,假设是1s
       • 当有一个写请求过来的时候,生成一个key A,马上路由写到主库,然后立马有一个读写请求,从库可能是旧数据,或者没有来得及同步,如果时间在1s内的,就对应的key继续路由到主库,就路由到从库.
         • 说白了,中间件就是给个同步时间,给你同步,在同步时间内,所有的请求都落到主库
     • 缓存记录key法
       1. 将某个库上的某个Key要发生写操作,记录在cache中,并设置"经验主从同步时间"的cache超时时间,例如1s
       2. 修改数据库
          1. 先到cache里面查看,对应库的对应key有没有数据
          2. 如果cache hit，有相关数据，说明这个key上刚发生过写操作，此时需要将请求路由到主库读最新的数据
          3. 如果cache miss，说明这个key上近期没有发生过写操作，此时将请求路由到从库，继续读写分离

3. 分库分表

   1. 垂直分库,可以减少并发压力
   2. 水平分表,可以解决性能瓶颈

优化器-SQL语句的分析和优化

把我们SQL语句执行情况记录下来

1. 慢查询日志 slow query log

2. mysqldumpslow

   1. 这是MySQL提供的工具,在MySQL的bin目录下
      1. Count 代表这个SQL执行了多少次
      2. Time代表执行的时间,括号里面是累计时间
      3. Lock代表锁定时间,括号是累计
      4. Rows表示返回的记录数,括号是累计

3. Show Profile

   1. 可以查看SQL执行时,占用的CPU和IO消耗的情况
   2. show processlist 显示用户运行进程

4. Explain

   1. 通过Explain可以模拟优化器执行SQL查询语句的过程,通过这种方式我们可以分析语句或者表的性能瓶颈
      

   2. 其中参数含义

      1. id -->查询序列变化

      2. select type -->查询类型

         1. SIMPLE -->简单查询,不包含子查询和关联查询
         2. PRIMARY–>子查询SQL语句中的主查询
         3. SUBQUERY -->子查询中所有的内层查询都是SUBQUERY类型的
         4. DERIVED -->派生查询,表示在得到最终查询结果之前会用到临时表
         5. UNION -->用到了UNION查询(UNION会用到内部的临时表)
         6. UNION RESULT -->主要是显示那些表之间存在UNION查询

      3. type 访问方法

         1. const -->主键索引或者唯一索引与常数进行等值匹配,只能查到一条SQL

         2. System -->system是const的一种特例,只有一行满足条件,对于MyISAM,Memory的表,只查询到一条数据

         3. eq_ref -->通常出现在多表的join查询,被驱动表通过唯一索引进行访问,此时被驱动表的访问方式就是eq_ref,eq_ref是除了const之外最好的访问类型

         4. ref -->查询用到了非唯一性索引

         5. range -->对索引进行范围扫描

         6. index --> 查询全部索引中的数据(比不走索引快)

         7. All --> 如果没有用到索引或者没有索引,type就是All,代表全盘索引

            小结:一般来说,至少查询要达到range级别(上面参数从上往下排序),否则就需要优化

      4. possible_key,key

         1. possible_key代表可能用到的索引,key代表实际用到的索引,如果是NULL就代表没有用到索引
         2. possible_可能有一个或者多个,比如查询多个字段上都有索引,或者一个索引同时有单列索引和联合索引
         3. 能用到的索引并不是越多越好,可能用到索引不代表一定要用索引
         4. 如果通过分析发现没有用到索引,就要检查SQL或者创建索引

      5. key_len -->索引的长度,跟索引字段的类型,长度有关.

      6. rows -->MySQL认为扫描多少行(数据或者索引)才能返回请求的数据,是一个预估值,一般来说行数越少越好

      7. filtered -->这个字段表示存储引擎的数据在server层过滤后,剩下多少满足的记录数量的比例,它是一个百分比,如果比例很低,说明存储引擎层返回的数据要经过大量过滤,这个是会消耗性能的

      8. ref -->使用哪个列或者常数和索引一起从表中筛选数据

      9. Extra -->执行计划给出的额外的信息说明

         1. using index 属于覆盖索引的情况,不需要回表

         2. using where 使用where过滤,表示存储引擎返回的记录并不是所有的都满足查询条件,需要再server层进行过滤(跟是否使用索引没有关系)

         3. using index Condition 索引下推

         4. using filesort 不能使用索引来排序,用到了额外的排序(跟磁盘或文件没有关系),需要优化

         5. using temporary 在查询的时候,需要去重排序,排序之类的工作的时候,可能会用到临时表

            总结: 模拟优化器执行SQL查询语句的过程,来指导MYSQL是怎么样处理一条SQL语句的,通过这种方式我们可以分析语句或者表的性能瓶颈

            如果我们要具体的cost信息 可以EXPLAIN FORMAT=JSON

SQL与索引优化

• SQL语句的优化的目标,大部分都是用到索引
• 对于每一种具体的SQL,也有相应的优化方案

存储引擎

存储引擎的选择:

• 为不同的业务表选择不同的存储引擎,例如;查询插入操作多的业务表,可以选择MyISAM,临时数据用Memeroy.常规的并发大的更新多的用INNODB
• 字段定义:
  • 原则:使用可以正确存储数据量的最小数据类型
  • 为每一列都选择何时的字段类型
• 不要用外键,触发器,视图
  • 降低了可读性
  • 影响数据库性能,应该把计算的事情交给程序,数据库只负责专心的存储
  • 数据的完整性应该在程序检查
• 不要存储大文件
• 表查分或字段冗余
  • 将不常用的字段拆分出去,避免列数过多或者数据量过大

总结:

你会从那几个维度来优化数据库:

1. SQL与索引
2. 存储引擎与表结构
3. 数据库架构
4. MySQL配置
5. 硬件与操作系统

除了对于代码,SQL语句,表定义,架构,配置优化之外,业务层面的优化也不能忽略

在应用层面同样有很多其他优化方案,达到尽量减轻数据库的压力的目的,比如限流,或者引入MQ销峰等等

• 分析查询基本情况

  • 设计到表结构,字段的索引情况,每张表的数量,查询的业务含义,这个非常有必要,因为有的时候就会发现SQL根本没必要这么写,或者表设计是有问题的

• 找出慢的原因

  • 查看执行计划,分析SQL的执行情况,了解表访问顺序,访问索引,扫描行数等信息
  • 如果总体时间很长,不确定哪一个因素影响最大,通过条件的筛选,顺序的调整,找出引起查询慢的主要原因,

• 对症下药

  • 创建索引或者联合索引

  • 改写SQL语句,

    • 使用小表驱动大表
    • 用join来代替子查询
    • not exist 转换为left join is null
    • or 改为union
    • 使用union all代替unnion ,如果结果集允许重复的话
    • 大偏移使用limit,先过滤再筛选

    如果SQL本身解决不了问题,就要上升到表结构和架构了

  • 标结果(冗余,拆分,not null 等),架构优化

  • 业务层的优化,必须条件是否必要