性能优化

性能指标

• 吞吐量
• 延迟

• 容量

  优化原则

• 没有量化就没有优化

• 28原则

• 脱离场景谈性能都是耍流氓

  系统性能核心

• 业务系统分类

  • 计算密集型
    • 业务处理本身无状态
  • 数据密集型
    • 数据状态最重要持久化

• DB/SQL优化是业务系统性能优化的核心

  数据库

  概念

• 数据关系模型

• E-R图（Entity-Relation）

• 

  数据库设计范式

• 通常要求前三个范式

  第一范式1NF

• 关系R属于第一范式

• 当且仅当R中的每一个属性A的值域只包含原子项
• 即每一个字段都是不可再分的

• 每列都是原子的

  第二范式2NF

• 满足1NF基础上，消除非主属性对码的部分函数依赖

• 没有列只与主键的部分相关

• 即每一列都被主键唯一标识

  第三范式3NF

• 满足2NF基础上，消除非主属性对码的传递函数依赖

• 列都和主键相关

• 从表只引用主表的主键

  BC范式BCNF

• 满足3NF基础上，消除主属性对码的部分和传递函数依赖

  第四范式4NF

• 消除非平凡的多值依赖

  第五范式5NF

• 消除一些不合适的连接依赖

• 完美范式

  常见数据库

  关系型

• MySQL

• PostgreSQL
• Oracle
• SQLServer

• DB2

  内存数据库

• Redis

• VoltDB

  文件数据库

• Access

  时序数据库

• InfluxDB

  NoSQL

• MongoDB

• Cassandra

  NewSQL

• TiDB

• OB

  SQL

  DQL

• DataQueryLanguage

• 数据查询语言

• 保留字

  • SELECT
  • WHERE
  • ORDER BY
  • GROUP BY
  • HAVING

    DML

• DataManipulationLanguage

• 数据操作语言

• 保留字

  • INSERT
  • UPDATE
  • DELTE

    TCL

• TransactionControlLanguage

• 事务控制语言，确保DML影响的表的所有行及时得到更新

• 保留字

  • COMMIT
  • SAVEPOINT
  • ROLLBACK

    DCL

• DataControlLanguage

• 数据控制语言，实现权限控制

• 保留字

  • GRANT
  • REVOKE

    DDL

• DataDefinitionLanguage

• 数据定义语言

• 保留字

  • CREATE
  • ALTER
  • DROP

    CCL

• CursorControlLanguage

• 指针控制语言

• 保留字

  • DECLARE CURSOR
  • FETCH INTO
  • UPDATE WHERE

    MySQL

    发展

• MariaDB

• MySQL

  版本

• 4.0支持 InnoDB，事务

• 5.6 目前使用最多的版本
• 5.7 支持
  • 多主
  • MGR高可用
  • 分区表
  • JSON
  • 性能
  • 修复XA

• 8.0 最新版本

  • 通用表达式
  • 窗口函数
  • 持久化参数 set persist
  • 自增列持久化（自增ID如果有一行被删除了，重启服务和不重启服务新增的ID是不一样的，内存转到硬盘）
  • 默认编码utf8mb4
  • DDL原子性
  • JSON增强
  • 不再对group by 进行隐式排序（ 就是个坑，只不过现在填平了）

    原理

    

    存储文件

    独占模式

• 日志组文件：ib_logfile0和ib_logfile1默认均为5M

• 表结构文件：*.frm
• 独占表空间文件：*.ibd
• 字符集和排序规则文件：db.opt
• binlog二进制日志文件：记录主数据库服务器的DDL和DML操作

• 二进制日志索引文件：master-bin.index

  共享模式

• innodb_file_per_table=1;

• 数据都在ibdata1文件中

  执行流程

  简化版

  

  详细版

  

  执行引擎和状态

  

  SQL执行顺序

• MySQL会根据不同情况优化执行顺序

• 但通常顺序如下

  • 

    索引原理

• MySQL数据是按页来分块的

• 根据经验，当一个数据被使用到时，附近的数据也通常会马上被使用
• InnoDB使用B+树实现聚集索引
  • 只有叶子节点上有数据
  • 父节点上全部是索引（为的就是把索引都放到1个块内，每次取出的时候尽量取出多的索引放到内存）
• 为了保证速度，B+树希望不要超过3层
  • 如果ID是一个BigInt（8个字节）
  • 指针大小是6个字节
  • 所以一个主键共占用8+6=14个字节
  • 一个页默认16K，所以大概有16KB/14B = 1170个主键
  • 一棵高度为2的B+树可以有16个块，即16* 1170=18720个主键
  • 一棵高度为3的B+树可以有161170个块，即16 1170 * 1170 = 21902400个主键
• 所以如果主键设计的比较简单，占用的空间小，则高度为3的B+树则可以存储更多的索引

• 反之如果主键字段值比较大，占用的空间大，则只能存储较少的索引

  Mysql配置优化

  my.cnf

• [mysqld] 服务端配置

• [mysql] 客户端配置

  查看参数配置

• mysql -h localhost -P 3306 -u root -p 123456 -e ‘show variables’ > mysql.variables.txt

  优化连接请求

• max_connections 推荐1W以内

• back_log
• wait_timeout

• interative_timeout

  优化缓冲区

• key_buffer_size

• query_cache_size 查询缓存简称QC
• max_connect_errors
• sort_buffer_size
• join_buffer_size
• max_allowed_packet=32M 不推荐打大包

• thread_cache_size=300

  优化innodb

• innodb_buffer_pool_size

• innodb_thread_concurrency=0
• innodb_log_buffer_size

• read_buffer_size=1M

  数据库设计优化

• 如何恰当选择引擎

• 库表如何命名
• 如何合理拆分宽表
• 如何选择恰当的数据类型
  • 明确、尽量小
  • char、varchar(64|128)
  • text、blob、clob（Character Large Object）（不要用，非常影响性能）
  • 文件、图片是否要存入数据库（不要用，数据库只存标识符）
  • 时间日期的存储（推荐时间戳，bigint占用小，计算还友好）
  • 数值的精度问题（使用字符串存储）
• 是否使用外键、触发器（不建议）
• 唯一约束和索引的关系（唯一约束默认就是索引）
• 是否可以冗余字段（推荐）
• 是否使用游标、变量、视图、自定义函数、存储过程（不推荐）
• 自增主键的使用问题（分布式下的问题）
• 是否能够在线修改表结构DDL（会锁表，只能在业务量小时使用）
• 逻辑删除还是物理删除（推荐逻辑删除）
• 要不要加create_time，update_time时间戳（推荐使用）
• 数据库碎片
• 如何快速导入、导出、备份数据（loaddata、分批并行导入、只导入数据再导入索引等）