MySQL优化解析

MySQL优化解析

前言

无论在工作实践还是面试理论中MySQL优化作为重要的一环，都应需要各位开发了解并掌握其解决方案

硬件与操作系统

• 硬件配置升级
  • cpu性能和核心数
  • 内存
  • 固态硬盘
  • 网络
• 操作系统
  • windows改linux

MySQL配置

• 增加MySQL服务器可用连接数

  show variables like 'MAX_CONNECTIONS';  --- 查看连接数，默认151，可以设置成16384(2^14)
  

• 减少客户端可用连接数

  • 连接池
  • 设置批量操作最大传输数据大小
    • 调节MySQL的配置文件my.ini中的 max_allowed_socket 参数大小
  • 设置第三方缓存模块
    • 使用redis等第三方中间件
    • 开启mybatis或hibernate等ORM框架缓存模块

• 慢查询等性能分析工具开启

  • 慢查询日志

    show variables like 'slow_query%';   --- 慢日志信息
    show variables like 'long_query_time'; --- 当SQL执行超过这个值时才记录到慢日志中,默认10s 
    

  • mysql专门用来分析慢日志的工具 mysqldumpslow profiles

    mysqldumoslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log  --表示前二十个耗时最长的SQL语句查询语句
    show profiles; select @@profiling;
    

数据库架构

• 分库分表
  • mycat/sharing jdbc
• 集群
  • 一主多从
  • 多主一从
  • 互为主从
  • 级联复制

存储引擎和表结构

• 存储引擎
  • 当插入和查询较多时，选择MyISAM存储引擎，不支持事务
  • 临时表，选择基于内存的Memory存储引擎
  • 大多数业务场景下使用默认Innodb存储引擎，支持事务
• 表结构
  • 字段类型选择
  • 字段大小选择

SQL和索引

• SQL优化
  • 执行计划：EXPLAIN SELECT * FROM USER WHERE id =1;
    • select_type;简单查询、子查询、联合查询
    • type:衡量SQL执行效率的一个重要指标
      • system/const/eq_ref 一般业务SQL很难优化到这种起步，忽略
      • ref：select * from student where name=‘小明’; //name为索引
      • rang：select name from student where id>1; //id字段为索引
      • index：select name from student; //name字段为索引
      • all：select name from student; //name字段不为索引
    • possible_keys：查询过程中可能使用到的索引
    • key：查询过程中实际使用到的索引
    • Extra：十分重要的额外信息
      • using filesort：select * from student order by name; //name字段不为索引
      • using temporary：select distinct name,phone from student;
      • using index：select phone from student where name=‘小明’; //name_phone为联合索引
      • using where：select * from student where phone=18012345678; //iphone不为索引
      • using index condition：索引下推
  • 索引优化