mysql查询性能优化总结

目录

 

性能监控和分析

性能定义

性能分析

schema和数据类型优化

索引优化

查询优化

学习要用思维导图，将知识点链接起来，形成知识图谱，知道哪些点，细节去查

性能监控和分析

性能定义

性能如何度量，核心指标：
1.吞吐量 tps，olap数据库偏向吞吐量
2.时延，oltp应用偏向时延低

性能分析

1. 性能监控
   1. set profiling 1
   2. show profile看各个阶段的耗时
      1. 看官方文档 https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
      2. 查看cpu 磁盘等所有的信息
      3. 基于session不会持久化
      4.  
   3. mysql performance schema
      1. 包含各种性能监控表，不会进行持久化
      2. performance sechma on，默认开启
      3. 查看mysql在执行的线程在干啥
      4. 看官网使用文档 https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html
      5. 做一个监控面板
   4. show processlist 查看链接数量
      1. 连接池
         1. druid
            1. 监控信息文档
            2. PreparedStatementCache
         2. c3p0
   5. show status handler_read查看引擎的接口状态。引擎是通过各种handler的接口来和服务层交互的
2. 分析步骤
   1. 1. explain 看索引和扫描的行数
         1. explain
            1. id，执行优先级
            2. select type 查询类型，
               1. primary从句的最外层
            3. type
               1. all
               2. index
               3. range
               4. ref
               5. eq_ref
               6. const
      2. show waring 看优化后的sql
      3. show profiles 查看查询细粒度时间
      4. 看官方文档

schema和数据类型优化

1. 数据类型最小简单最好
2. 尽量不要用null
3. char和varchar
   1. char会自动去结尾空格
   2. char读写效率高，varchar存储空间小
4. date timestamp datetime
   1. 精度，存储空间
5. 枚举类型
   1. 使用的是整形存储
   2. 自动转换成字符名称
6. 范式和范式
   1. 主键能确定一行，每行的列不能再拆分
   2. 每列都是和主键有关系
   3. 只依赖主键，直接关系不是简介依赖
7. 主键选择
   1. 代理主键，不依赖业务
   2. 自然主键，业务相关的属性id
   3. 推荐使用代理主键：不依赖业务，通用的id生成减少维护成本
8. 字符集选择
   1. 纯英文latin1
   2. 中文使用utf8-mb4，纯utf8只有两个字节
   3. 尽量精确到字段，减少存储量，降低io操作次数，提高缓存命中率
9. 存储引擎选择
   1. 一般用innodb
10. 适当荣誉
    1. 列比较多，但是用到的不多，通常用join则浪费io，可以用单独的表来进行
    2. 类似中间表关联关系
11. 适当拆分
    1. 垂直水平拆分

索引优化

1. 数据结构
   1. b+树
      1. 每个节点数索引指针多
      2. 非叶子节点不存数据，减少io，减少io数据量
2. 回表，用非聚集索引中的主键查找
3. 覆盖索引，select的列都被在索引中
4. 最左匹配，组合索引命中索引条件
5. 索引下推，谓词下推，组合索引在存储引擎层将数据过滤，不用再服务层过滤，减少io量
6. union all,in,or使用
   1. union  会对结果排序和去重复，比union all效率低
   2. 子句简单优先
   3. 在没有索引的情况下in优于or，or判断多，in是二分查找，有索引时in or union all差不多
   4. exists 必须是子查询，使用外层限制内层，不能查询子查询的字段，比in快
   5. exists和in的效率，exists是外层表没有索引时表扫描
   6. 查询过程
      1. in 和join类似是循环嵌套，特定条件优化器会将in改成join， for for
      2. exists是外层的每个元素对字句进行判断，字句可以走索引 for if（condition(out)）
7. 范围列可以用索引，但是范围列后面的列索引不能用上，只能用一个
8. 强制类型转换会全表扫描
   1. 字段为strng，用整形来匹配
9. 更新频繁，基数低的字段不宜建索引
10. 索引字段不能为null
    1. “is null” 或者 “is not null” 或者 “<=> null” 是可以走索引的
    2. 存null会导致统计出异常
    3. 存null需要额外的存储空间
11.  

查询优化

查询优化的核心在于优化索引，创建高效的索引和行数少的查询

1. innodb引擎是基于成本的优化
   1. mysql优化器根据数据库的各种统计数据来进行优化，基于成本意味着扫描行数少的成本低
   2. 不是每次优化都是最优的需要根据查询计划来优化，同时不断尝试其他的索引。
2. 锁
   1. innodb锁的是索引
3. 查询缓存
   1. 8.0已经移除
4. calcite sql解析框架
5. 优化min max
6. 索引覆盖
7. join优化
   1. join的时候不要超过3张表
      1. 循环嵌套的实现方式，小表放外层作为驱动表，大表做为被驱动表
      2. 小表驱动大表，left join驱动表为左表
      3. 被驱动表的关联字段加索引优化
      4. mysql会优化驱动表和被动表的顺序
      5. 循环嵌套实现方式，官网
         1. 外层循环遍历每行找到内层循环的匹配行
         2. 无索引循环嵌套 join，simple nested loop join
         3. 有索引使用index nested loop join
         4. 无索引时的优化方式block nested loop join ，将驱动表放到内存 join buffer中每次不是一行扫描被驱动表，而是批量扫描，减少被驱动表扫描次数，无索引时会采用避免simple的join，可以设置join buffer size
      6. left join and 左表都输出，右表and 不符合就不显示
      7. 优先用内连接，不是外连接，因为外连接会产生null
      8. straight join 禁止sql优化join顺序
      9. 等值链接 == 即using
      10. 8.0 hash join
          1. 没索引情况的优化
          2. 等值连接
          3. 比blocked join好
8. 排序优化
   1. filesorting
      1. 有索引的列也可以能回有
   2. 排序在引擎层的优化
      1. 两次传输
         1. 先排序列在查找行
      2. 单次传输
         1. 直接进行整行排序
         2. 排序缓存较多
   3. 借助索引覆盖优化排序，排序集合大时效果明显
9. cout优化
   1. myism 不用条件很快
   2. 使用近似值
   3. innodb不会有效率区别
10. 子查询优化
    1. 尽量使用join，子查询会有临时表
    2. 使用带走索引的 join 或者in exists 优化不走索引的查询
    3. 子查询优化，使用索引覆盖的子查询优化外层查询，如果外层索引没有用索引
11. limit优化
    1. 尽量使用limit减少输出
    2. 索引覆盖优化深度分页
12. 自定义变量
    1. set @abd:=1;
    2. select @abd:=@abd+1;用来记录行数或者排名
    3. 可能会被优化掉
    4. 无法使用查询缓存
13. union列转行：https://blog.csdn.net/weter_drop/article/details/105899362?utm_medium=distribute.pc_aggpage_search_result.none-task-blog-2~all~sobaiduend~default-1-105899362.nonecase&utm_term=mysql%20union%20%E8%A1%8C%E8%BD%AC%E5%88%97&spm=1000.2123.3001.4430
    1. join 行转列
    2. case when
14. 分区表，官网，类似分表
    1. 将idb文件进行分区，分多个文件存储，#号分割
    2. 减少inode和索引的锁竞争
    3. 分区单独管理减少维护量
    4. 文件个数有上限，fd上限，和内存相关
    5. range分区
       1. year分区
    6. 列分区
    7. 列表分区，等值分区
    8. hash分区
    9. key分区
       1. 按照主键分区
    10. 原理
        1. 分区表和普通表一样
        2. 底层表都是单独的引擎
        3. 增删改时，先锁住所有底层表，然后过滤到对应的分区进行操作。
    11. 尽量不要修改分区键
15. 配置优化
    1. 最大连接数量，set max_connections.
    2. show processlist; 查看链接数
    3. 每个用户的链接数限制
    4. back log 链接等待队列
    5. 交互链接的超时
    6. 日志设置
       1. logerror error文件
       2. binlog
          1. binlog do db，那些数据库存到binlog ，白名单，黑名单
          2. 顺序写
          3. 备份和binglog同时使用
          4. sync_binlog ，这个参数直接影响mysql的性能和完整性
             1. sync_binlog=0 ，当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，风险大
             2. sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
             3. 一般设置为1
          5. redo log写入模式
             • buffer pool->log buffer -> os buffer->disk随机写
             • 最安全的是直接提交到disk，尽量每次都提交fsync
               1. 在 MySQL 的配置文件中提供了 innodb_flush_log_at_trx_commit 参数，这个可以用来控制缓冲区和磁盘之间的数据如何同步，这里有 0、1、2 三个选项，在我装的 MySQL 下默认的是 1，简单介绍一下这三个选项的区别：
                  1. 0：表示当提交事务时，并不将缓冲区的 redo 日志写入磁盘的日志文件，而是等待主线程每秒刷新。
                  2. 1：在事务提交时将缓冲区的 redo 日志同步写入到磁盘，保证一定会写入成功。
                  3. 2：在事务提交时将缓冲区的 redo 日志异步写入到磁盘，即不能完全保证 commit 时肯定会写入 redo 日志文件，只是有这个动作。

          6. 写入两阶段提交过程，prepare阶段 先写redo，进入commit阶段 写binlog，写完，redo 进行commit

             • slow query log
               1. 即时查询
             • general log
               1. 查询日志
16. 缓存
    1. key buffer size ，myism 的索引缓存大小
    2. query cache，5.7以下
       1. query cache limit
       2.  
    3. sort buffer
       1. 排序用的
    4. max allow packet 最大的tcp包
    5. join buffer ，join大缓存大小
    6. thread cache size
       1. 线程池
    7. innodb buffer pool size
    8. read buffer size 顺序读的buffer
    9. read rnd buffer size 随机读的buffer
    10. innodb file per table 一张表一个ibd，否则在ibdata1文件
17. 锁
    1. myisam锁
       1. 独占锁
          1. lock table * write
          2.  
       2. 共享锁
          1. lock table * read
       3. 自动会加读写锁
    2. innodb
       1. 意向锁，用来判断是否有被锁住
          1. 当再向一个表添加表级X锁的时候
          2. 如果没有意向锁的话，则需要遍历所有整个表判断是否有行锁的存在，以免发生冲突
          3. 如果有了意向锁，只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了，有行级锁的存在或者即将有行级锁的存在。因而无需遍历整个表，即可获取结果
       2. 有索引才是行锁，没索引是表锁，锁的是索引
       3. 自增锁，表锁
       4. 读共享锁，lock for read
       5. 写排他锁 ，lock for update
18. 集群
    1. 主从复制
       1. mts binlog无延时
    2. 读写分离
       1. mysql-proyx
       2. mycat
       3. 变形虫
    3. 分库分表
       1. sharding-jdbc