MySQL 四层优化大纲

SQL优化

SQL语句的优化方向

  • 没有命中索引的SQL是不允许上线的
  • where、groupby等经常用的字段添加索引
  • 对于查询的的sql上线之前需要explain执行计划查看sql的查询效率，索引利用率等
  • 关联查询
  • 表中数据比较多，尽量不要用子查询，用联合查询
  • in中的条件不要太多，用关联查询innerJoin做一个结果集

  • 一个例子——订单；按天统计每日的订单量和订单总金额，按日期进行分组对日期做了索引，数据库中存的是年月日时分秒，而查询是用的groupBy年月日，所以需要使用DATE_FORMAT()函数对日期做格式化，用了函数不会命中索引，会造成全表扫描导致慢查询，解决方案：查询的时候where下单时间大于当月第一天零点，小于当月最后一天零点，查出来当月数据后再groupby数据量小很多

    数据表优化方向

  • 订单表中加一个字段：下单日期，这样就不用使用日期格式化函数了，能够走索引

  • 报表导出：需要查询全年销售报表，这样必然是一个慢查询，可以按日生成一个报表表，凌晨写一个定时任务每天将当天报表同步到报表表中，这样除了当天的报表没有以外，昨天及之前的报表都有了。这样看报表只要查报表表就行了，不用去查询订单表

    业务优化方向

  • 缓存

  • 页面静态化
  • 存储引擎MyISAM，比InnoDB性能强，MyISAM不支持事务所以在查询和插入的时候性能都比InnoDB强

  • 将经常查询和插入但是update和delete情况比较少的业务可以用MyISAM或者NoSQL（mongoDB）存储，查询起来效率会比InnoDB高。

    架构方向

  • 用Kafka实时流处理

  • MySQL主从复制基于BinLog日志，是异步的
  • 主从同步，读写分离
  • 按业务进行分库，表做水平拆分

    一、应用层面优化

    使用连接池

    image.png
    Mybatis中自带连接池，同时也可以配置C3P0连接池或者Druid连接池。

    二、业务层面对MySQL的优化

    对业务进行调整，减少对MySQL的访问

    1、将需要的数据一次性查询出来

    image.png
    能够一次查询获取到结果就不需要多次查询去获取结果，优化查询的次数。

    2、从业务的角度出发，减少对MySQL的访问

    image.png
    使用 Redis 、 页面静态化技术 、 Mybatis一级、二级缓存 来减少对数据库的请求。

三、架构层面对MySQL进行优化

1、搭建主从结构实现读写分离

image.png
搭建主从结构，主节点承担事务操作，从节点承担查询过操作，增强数据库的处理能力。

四、MySQL查询缓存优化

4.1概述

image.png
开启MySQL查询缓存，这样查询过重复数据时将不会直接查询过数据库缓存，然后返回结果。如果数据结果更新或者删除了，缓存也将作废。

4.2 查询缓存执行流程

image.png
image.png

  1. 请求进来时先查询查询过缓存，如果查询缓存中存在该数据，则直接返回；
  2. 查询缓存中不存在该数据则SQL语句进入解析器、预处理器、查询优化器最后由查询存储引擎API接口调用存储引擎，查询到数据后返回；
  3. 返回的数据都会存储到查询缓存中。

4.3 配置查询缓存

4.3.1查询当前数据库是否支持查询缓存

image.png

4.3.2 查看是否开启了查询缓存

image.png

4.3.3 查看查询缓存的大小、状态

image.png
参数解释：
image.png

4.4 开启查询缓存

image.png
需要在MySQL的配置文件中开启查询缓存。

4.5 指定SQL使用或不使用缓存选项

image.png
如果配置的查询缓存参数是： 1或ON 则不使用该参数也会走缓存，如果配置的参数是： 2或DEMAND 则需要加： SQL_CACHE 选项该SQL才会查询和使用缓存。

4.6 查询缓存失效的情况

image.png

  1. 查询缓存需要SQL语句完全一致，所以当SQL语句不完全相同时不走缓存；
  2. SQL语句中存在变量函数时,不走缓存；
  3. 查询MySQL系统数据库时，不走缓存；
  4. 对表做了任何的事务操作时，该表对应的所有缓存作废（个人认为这是MySQL查询缓存派不上用场最重要的原因）

五、MySQL内存管理及优化

5.1 内存优化原则

image.png

  1. 在系统内存分配充足的情况下，可以尽量分配更多的内存给MySQL做缓存；
  2. MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，所以如果涉及到MyISAM的查询缓存需要给系统资源预留更多的缓存
  3. 对排序区和连接区的缓存分配需要根据并发连接数来调试分配最合适的缓存大小；

5.2 MyISAM的内存优化

image.png

  1. 通过修改 key_buffer_size 来调整索引的内存空间
  2. 通过调整 read_buffer_size 来提升全表扫描时的性能
  3. 通过调整 read_rnd_buffer_size 来提升group by的排序性能

注意：后两个参数都是对每个Session独占的，所以需要根据并发连接数来进行调试，否则将会造成内存的浪费或者造成内存不足。

5.3 InnoDB的内存优化

image.png

5.4 配置缓存大小

image.png

六、MySQL并发参数的调整

image.png

6.1 配置最大连接数

image.png
根据服务器的性能来配置MySQL的最大连接数。

6.2 配置请求堆栈的大小

image.png
如果需要在较短时间内处理大量的连接请求，可以适当提高积压请求栈的大小

6.3 配置表缓存的数量大小

image.png
一条SQL语句SELECT后面跟的表明就是一条SQL需要的表缓存数量，这个总的表缓存数量大小需要根据最大并发数以及业务中涉及多表查询的数量进行调试。

6.4 配置客户端连接的线程池大小

image.png
通过这个参数可以配置MySQL用于备用的客户服务线程数，用于加快连接数据库的速度。

6.5 配置InnoDB行锁的等待时间

image.png

  • 将行锁的等待时间调小，将减少事务被挂起的时间，提早抛出异常。
  • 将行锁的等待时间调大，将避免由于事务被挂起超时后抛出异常的大规模回滚问题。

七、MySQL 锁

image.png 用于协调多个线程或进程并发访问某一资源的机制

7.1 锁的分类

image.png
两个方向：操作粒度和操作类型

7.2表锁和行级锁的特性

image.png
行级锁会出现死锁，并发度在锁中最高；表锁不会出现死锁，在锁中并发度最低；

7.3 MyISAM读表锁

MyISAM在执行SELECT操作时会自动对表加读表锁（共享锁），在执行事务操作时会自动对表加表写锁（排他锁）
不会阻塞其他线程的读操作，但是会阻塞其他线程的事务操作，包括本线程的事务操作。
image.png

获取到读表锁后，执行读取操作和事务操作的处理方式：

image.png

  • 手动获取了读表锁，在未释放的情况下不可操作其他数据库
  • 由于读表锁是共享锁，并不影响其他客户端的读操作

image.png
当一个客户端获取了读表锁时，当前和其他客户端执行事务操作时，数据库的处理情况
image.png

7.4 MyISAM写表锁

进行事务操作时客户端会自动获取到写表锁，期间拥有锁的客户端可以执行各种事务和非事务操作，但未获取写表锁的线程将不具备访问数据库的能力，需要等待锁释放才能够访问数据库表。
image.png

7.5 MyISAM锁小结

image.png

  • MyISAM是写优先的，获取到写锁后其他线程不能够进行读和写操作，会导致效率大幅下降。这也是MyISAM不适合做写为主数据库的原因
  • 简而言之：读锁会阻塞所有线程写但不会阻塞所有线程读，而写锁会阻塞除获取到锁线程以外的其他线程所有访问数据库的操作

7.6 查看MyISAM锁的争用情况（命令）

7.6.1 查看当前状态下，哪些表在使用（被锁定）

image.png

7.6.2 查看锁的争用情况

image.png
上面是请求过来立即能够获取锁的次数，下面是请求过来需要等待其他线程释放锁的次数，如果此次数比较高，则说明该表存在比较明显的锁争用情况。

7.7 InnoDB的行锁

image.png
InnoDB默认支持的是行级锁，也可以配置为表锁。InnoDB和MyISAM两个存储引擎最大的不同是：支持事务和行级锁，这两者是有关联的。

7.7.1 并发事务带来的问题

image.png

7.7.2 事务的隔离级别

image.png

7.7.3 InnoDB的行锁模式

image.png

  • 对于事务操作，InnoDB会自动给涉及的数据加行排他锁（这点和MyISAM相同）
  • 对于普通的SELECT操作，InnoDB将不会加任何锁（这点和MyISAM不同，MyISAM将会加读表锁）

    7.7.4 InnoDB行锁情况分析

    1、单纯的SELECT情况，只进行Select不会对表加任何锁

    image.png

2、对某一行进行事务操作且WHERE后字段命中索引，会对该行加行级排他锁

image.png
在持有排他锁的事务未提交之前，其他针对该行的事务将会处于阻塞状态。

3、不同行、行锁不同可以同时执行

image.png

4、在可重复读隔离级下Select的场景

当执行Select后将开启一个事务，在这个事务内，无论其他线程对这行数据做了怎样的修改，只要这个事务还未提交，则查询出来的数据始终一致！
在其他事务Conmmit后开启的事务都能够看到最新的数据信息。

5、InnoDB行锁升级为表锁的情况

image.png
WHERE后的字段没有命中索引或者索引失效，则InnoDB的行锁升级为表锁。

7.7.5 间隙锁

image.png
流程演示：
image.png
解决方法：精确的锁定排他锁的范围，尽量避免锁住更多的间隙。

7.8 查看InnoDB锁的争用记录

image.png
主要关注：系统启动后到现在总共等待的次数

7.9 InnoDB行锁总结

image.png

  • WHERE尽量命中索引，避免行锁升级为表锁
  • 控制索引范围，避免间隙锁
  • 控制事务的大小，减少事务锁定资源的时间

八、 常用SQL技巧

8.1 SQL的执行顺序

image.png
image.png

8.2 SQL中正则表达式的使用

image.png
语法：
image.png

8.3 MySQL常用函数

8.3.1 数字类型常用函数

image.png

8.3.2 字符串函数

image.png

8.3.3 日期类型函数

image.png
image.png

8.3.4 聚合函数

image.png