mysql -SQL调优

SQL调优

架构

读写分离
    数据量太多的时候，可以通过主从复制来实现数据的读写分离，减轻压力，提高效率。

分库分表
    分库是为了缓解你这个数据库的并发压力，因为你单库并发能力扛不住了，随时可能会挂掉。然后增加其他数据库来缓解你这个数据库的并发压力。
    分表是为了缓解你单个表的数据压力。
    ------现在也有很多成熟的技术方案比如sharding-jdbc，分表的话根据时间分，根据订单号分，根据订单号取模分，根据手机号取模分都可以。

redis
    用redis做缓存。 
        需要考虑双写一致性问题。   根据不同的业务需求采用合适的方案来保证双写一致性。

    mysql的buffer pool缓冲池可以看做是应对大量请求下的一个写缓存。redis就可以看做在大量请求下减轻mysql压力的一个读缓存。

JAVA的数据结构--list，map，set
    比如说你查出来一个方法，你可能多次要用到这个数据，可能整个类都要用这个数据。你就可以用list，map，这种数据结构，在启动的时候先把这个数据加载出来放到list，map里面。用的时候直接去list里面去拿，不用再去数据库里面去查。

硬件

内存
    起码不能小于16G，不然buffer pool加载不了多少数据页，就会持续做刷脏页的操作，也是比较耗性能的。内存最好用32G的，buffer pool站内存的75%左右是一个比较好的性能点。

CPU
    mysql用几核的CPU，比如8核16G，还是8核32G之类的，都需要通过比如应用类型，并发数，数据库的读写比例等等做一些考量，在进行一些负载测试和性能评估之后决定的。

mysql的版本
    越高的版本mysql进行的优化越多，性能更好更稳定。比如5.6版本索引下推，8.0版本的索引跳跃扫描以及默认字符集utf-8mb4，对全球语言的支持。

mysql的连接数
    mysql多少个连接数是最优的呢。并不是越多越好，假如8核CPU，100个连接在这8核上跑，会造成上下文切换带来的很多开销。
    mysql连接数一般是CPU核心数*2。如果是固态硬盘的话，还可以+1，就是CPU核心数*2+1。（固态硬盘比机械硬盘读写速度更快，性能更好。但是价格也贵一些）

SQL优化

平时注意的一些点
    冗余字段
        一般一个表最多的话最好是在30个左右。
        就是把热点字段和冗余的字段给拆开两张表，偶尔用的话，再联表去查。

    不要select * 
        用什么查什么

    不要多次重复查询
        可能业务代码层，某个数据这个方法查一下，那个方法查一下。可以只查一次，然后作为参数去传递。或者团队开发的时候，你负责这个接口，我负责这个接口，我们提前沟通好，你查一次，把数据当参数给我传过来。很多人去查对mysql的压力也是很大的。你查三四次可以，但是尽量避免多次重复查。

    limit
        查几百条几千条前的数据速度没什么问题，但是到几万条数据的时候，就会变慢起来。如果说你的主键ID是自增的，你可以让前端在翻页的时候把末尾的ID直接传过来。你 where id>这个末尾的id数然后limit10或者20之类的，这样就会快很多。
        
        

    日期
        timedate 是占8个字节
        date 是占3个字节，但是不显示时分秒
        timestamp 是占4个字节，时间范围是1970.1.1---2038.1.19 在绝大多数情况下timestamp已经足够我们用了。

    status这种状态字段
        可以用tinyint类型，只占一个字节

    数字
        一些数字，如果明确知道数字有多长，比如电话号码，用char（11）或者int类型就可以。varchar类型还会维护你变长的字段。

    插入
        批量插入操作用mysql的语法in，不要在代码中For循环插。在mysql中in默认最大都可以批量插入一个G的数据。

    DBA
        根据业务需求去配置合适的redolog或者binglog的刷盘策略。

慢SQL处理

开启你mysql的慢SQL日志查询功能。
    可以通过一些参数设置执行时间阈值，还有走没走索引之类的。因为现在一般都是用的阿里云嘛，你可以在阿里云的控制台上看这条慢sql走了多久，扫描了多少行，走没走索引之类的一些信息。

用explain关键字
    主要是看type它的执行类型
        ALL: 表示全表扫描，将对表中的每一行都进行检查。
        index: 表示全索引扫描，将对索引中的每一行都进行检查。 --走的是索引覆盖
        range: 表示范围扫描，通常是通过索引进行范围查询。
        ref: 表示基于索引的单表连接查询，通常是通过外键关联。---走的普通索引
        eq_ref: 表示基于唯一索引的单表连接查询，通常是通过主键关联。  关联的从表，关联条件是从表的主键
        const: 表示通过常量条件进行查询，通常是在某些条件下只返回一行记录。 -- 走的唯一索引（主键索引）
        system: 表示查询表只有一行记录（或零行），这是const类型的特例。
        NULL: 表示没有表，可能是由于使用了子查询或临时表。

索引

建立索引
    索引不是越多越好，一般四个索引就足够了。
    主键尽量采取自增，减少页分裂。
    常用的字段建索引，或者联合索引。
    长字段可以用它的前一部分建立前缀索引

使用索引
    符合最左前缀匹配原则
    索引下推
    索引覆盖
    索引跳跃

索引失效
    如果联表关联条件两边用的不是同样的字符集的字段，比如一个是utf-8，一个是utf-8mb，就可能会造成索引失效。
    不符合最左前缀原则
    like模糊查询可能会造成索引失效。
    or两边如果有一个不是索引，就会造成索引失效，全表扫描。
    范围放到最后去查，使用了范围就不能保证有序了。后面的字段就会造成索引失效
    不要在索引列去进行一些函数的操作。也会造成索引失效。

MyISAM

比如说你是一个字典表，你的数据非常散列，就像HashMap一样。并且你绝大多数下只进行查询操作，也进行范围查找。就可以用hash索引直接定位到那一条数据。
在5.5之前，mysql默认的存储引擎是MyISAM，如果版本比较低，5.5,5.6这种。在你查询量比较大，并且不进行什么增删改的操作，MyISAM会快一些。但是在8.0的InnoDB已经做了很多优化了，性能上已经差不多了。而且InnoDB支持行锁，支持事务。

思考题

一个事务在update数据时，它的where条件是一个不是索引的字段，其他事务会不会卡主。
    如果一个事务在更新数据时，其WHERE条件是一个非索引字段，它将会进行全表扫描来找到需要更新的行。在这个过程中，它可能会锁定大量的行甚至整个表。
        在可重复读的隔离级别下，如果另一个事务在这个时候需要访问被锁定的行，那么这个事务就会被阻塞，直到第一个事务提交或回滚，释放了它持有的锁，这个事务才能继续执行。所以，第二个事务可能会被“卡主”。
        在读已提交的隔离级别下，它不会卡主。

    为什么
        在RR级别下，为了避免不可重复读的问题，在数据进行操作时会锁住对应的行或者表，直到事务提交才会释放锁。但是在RC级别下，它因为mvcc的机制，每次查询都会生成一个快照，在事务中不需要等待整个事务完成才释放锁。只是在实际操作中，在锁住对应数据的行或者表，操作完成之后，就会释放，这样就减少了等待时间。总而言之，就是RC级别下，通过减少锁的持续时间和范围，允许更高程度的并发，降低了因为事务等待锁而被阻塞的机会。