Eviltuzki

一线码农一枚

0%

MySQL学习笔记-什么影响了MySQL性能

发表于 分类于

服务器硬件

CPU资源

  • Web应用-Core重于频率

内存大小

  • 主板支持最大频率的内存

网络

IO子系统

  • 传统硬盘

    • 存储容量
    • 传输速度
    • 访问时间
    • 主轴转速
    • 物理尺寸

  • RAID

    • RAID0

      • 多个独立磁盘串行
      • 最简单
      • 性价比最高
      • 数据没有冗余

    • RAID1

      • 磁盘镜像
      • 安全性最高

    • RAID5

      • 分布式奇偶校验磁盘阵列

    • RAID10

      • 分片镜像

  • 固态存储

    • 更好的随机读写性能

    • 更好的支持并发

    • 更容易损坏

    • 使用场景

      • 大量随机I/O场景
      • 单线程负载I/O瓶颈

  • 网络存储

    • SAN

      • 大量顺序读写

    • NAS

      • 有一定网络延迟

    • 适合数据库备份

网络性能的限制

  • 延迟
  • 带宽
  • 网络质量影响

操作系统

CentOS

  • 内核相关参数（/etc/sysctl.conf）

    • 网络参数

      • net.core.somaxconn=65535 #监听队列长度
      • net.core.netdev_max_backlog=65535
      • net.ipv4.tcp_max_syn_backlog=65535
      • net.ipv4.tcp_fin_timeout =10
      • net.ipv4.tcp_tw_reuse =1
      • net.ipv4.tcp_tw_recycle =1
      • net.core.wmem_default = 87380
      • net.core.wmem_max = 16777216
      • net.core.rmem_default = 87380
      • net.core.rmem_max = 16777216
      • net.ipv4.tcp_keepalive_time =120
      • net.ipv4.tcp_keepalive_intvl =30
      • net.ipv4.tcp_keepalive_probes =3

    • 内核参数

      • kernel.shmmax=4294967295

        • Linux内核参数中最重要的参数之一，用于定义单个共享内存段的最大值

        • 注意事项

          • 这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个的Innodb缓冲池的大小
          • 这个值的大小对于64位Linux系统，可取的最大值为物理内存值-1byte，建议设置为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可。

      • vm.swappiness = 0

        • 当内存不足时会对性能产生比较明显的影响

        • 如果完全禁用Swap

          • 降低操作系统性能
          • 容易造成内存溢出，崩溃，或都被操作系统Kill

        • 结论

          • 需要保留交换分区，但是要控制何时使用交换分区

        • 为0

          • 除非内存使用满了，否则不要使用交换分区

  • 增加资源限制（/etc/security/limit.conf）

    • * soft nofile 65535

    • * hard nofile 65535

    • 解释

      • * 所有用户有效
      • soft指的是当前系统生效的配置
      • hard表明系统中所能设定的最大值
      • nofile表示所限制的资源师打开文件的最大数目
      • 65535 限制的数量

    • 结论：把可以打开的文件数量增加到65535个以保证可以打开足够多的文件句柄

    • 注意：这个文件的修改需要重启系统才可以生效

  • 磁盘调度策略（/sys/block/devname/queue/scheduler）

    • noop

      • 电梯式调度策略（适合闪存，RAM，嵌入式系统）

    • cfq

      • 完全公平队列（默认，不适合MySQL）

    • deadline

      • 截止时间调度策略（适合数据库）

    • anticipatory

      • 预料I/O调度测量（适合写入较多的环境，比如文件服务器，不适合数据库）

    • 修改方法

      • echo deadlin > /sys/block/sda/queue/scheduler

  • 文件系统影响

    • Linux

      • 推荐使用XFS

      • EXT3/4

        • 系统挂载参数（/etc/fstab）

          • data = writeback | ordered | journal

            • Innodb适用于writeback，ordered较慢，journal最慢，最安全

          • noatime,nodiratime

            • 禁用相关时间

          • 示例：

            • /dev/sda1/ext3 noatime,nodiratime,data=writeback 1 1

数据库存储引擎的选择

MyISAM

  • MySQL5.5之前的默认存储引擎

  • 存储为MYD，MYI两个文件

  • 特性

    • 并发性与锁级别

      • 表锁

    • 表损坏修复

      • check table tablename
      • repire table tablename

    • 支持索引类型

      • 全文索引
      • Text等前缀索引

    • 支持数据压缩

      • myisampack
      • 只读

  • 限制

    • 单表<256T

  • 适用场景

    • 非事务型应用

    • 只读类应用（支持压缩）

    • 空间类应用

      • 支持空间函数

Innodb

  • 使用表空间进行数据存储

    • innodb_file_per_table

      • ON：独立表空间：tablename.ibd
      • OFF：系统表空间：ibdataX

    • 比较

      • 系统表空间无法简单的收缩文件大小
      • 独立表空间可以通过optimize table命令收缩系统文件
      • 系统表空间会产生I/O瓶颈
      • 独立表空间可以同时向多个文件刷新数据

    • 建议

      • 对Innodb使用独立表空间

  • 系统表空间

    • Innodb数据字典信息
    • Undo回滚段

  • 特性

    • Innodb是一种事务性的存储引擎
    • 完全支持事务的ACID特性
    • Redo Log和Undo Log
    • 支持行级锁
    • 行级锁可以最大程度支持并发
    • 行级锁是由存储引擎层实现的

  • 什么是锁

    • 锁主要作用是管理共享资源的并发访问

    • 锁用于实现事务的隔离性

    • 锁的类型

      • 共享锁（读锁）
      • 独占锁（写锁）

    • 锁的粒度

      • 表级锁
      • 行级锁

    • 阻塞和死锁

  • Innodb状态检查

    • show engine innodb status

  • 适用场景

    • 使用大多数OLTP应用

CSV

  • 文件系统存储特点

    • 数据以文本方式存储在文件中
    • .csv文件存储表内容
    • CSM文件存储表的元数据如表的状态和数据量
    • frm文件存储表结构信息

  • 特点

    • 以CSV格式进行数据存储

    • 所有列必须都是不能为null的

    • 不支持索引

      • 不适合大表

    • 可以对数据文件直接进行编辑

      • 保存文本文件内容

  • 适用场景

    • 适合作为数据交换的中间表

Archive

  • 文件存储特点

    • 以Zlib对表数据进行压缩，磁盘I/O更少
    • 数据存储在ARZ为后缀的文件中
    • 只支持Insert和select操作
    • 只允许在自增ID列上加索引

  • 适用场景

    • 日志和数据采集类应用

Memory

  • 文件系统特点

    • 也称为HEAP存储引擎，数据保存在内存中

  • 功能特点

    • 支持HASH索引和BTree索引

      • HASH适合等值
      • BTree适合范围

    • 所有字段都为固定长度

    • 不支持Blog和Text等大字段

    • 使用表级锁

    • 最大大小由max_heap_table_size参数决定

      • 默认16M

  • 容易混淆的概念

    • Memory存储引擎表

    • 临时表

      • 系统使用临时表

        • 超过限制使用MyISAM临时表
        • 未超过限制使用Memory表

      • create temporary table 建立的临时表

  • 适用场景

    • 用于查找或者映射表
    • 用于保存数据分析过程中产生的中间表
    • 用于缓存周期性聚合数据的结果表
    • MEMory数据容易丢失，所以要求数据可再生

Federated

  • 特点

    • 提供了访问MySQL服务器上表的方法
    • 本地不存储数据，数据全部存放到远程服务器上
    • 本地需要保存表结构和服务器连接信息

  • 如何使用

    • 默认禁止，启用需要在启动时增加federated参数

  • 适用场景

    • 偶尔统计分析以及手工查询

如何选择正确的存储引擎

  • 一般选择InnoDB

  • 参考条件

    • 事务
    • 备份
    • 崩溃恢复
    • 存储引擎的特有特性

  • 尽量不要混合使用存储引擎

数据库参数配置

MySQL获取配置信息路径

  • 命令行参数
  • 配置文件

MySQL配置参数的作用域

  • 全局参数

    • set global 参数名=参数值;
    • set @@global.参数名:=参数值；

  • 会话参数

    • set [session] 参数名=参数值
    • set @@session.参数名:=参数值

内存配置相关参数

  • 确定可以使用的内存的上限

  • 确定MySQL的每个连接使用的内存

    • sort_buffer_size
    • join_buffer_size
    • read_buffer_size
    • read_rnd_buffer_size

  • 确定需要为操作系统保留多少内存

  • 如何为缓存池分配内存

    • Innodb_buffer_pool_size

      • 总内存 -（每个线程所需要的内存*连接数）- 系统保留内存

    • key_buffer_size

      • 主要MyISAM使用

IO相关配置参数

  • Innodb I/O 相关配置

    • Innodb_log_file_size

    • Innodb_log_files_in_group

    • 事务日志总大小 = Innodb_log_file_size * Innodb_log_files_in_group

    • Innodb_log_buffer_size

    • Innodb_flush_log_at_trx_commit

      • 0：每秒进行一次log写入cache，并flush log到磁盘
      • 1：每次事务提交执行log写入cache，并flush log到磁盘
      • 2：每次事务提交，执行log数据写入到cache，每秒执行一次flush log到磁盘

    • Innodb_flush_method=O_DIRECT

      • 关闭操作系统缓存(Linux建议)

    • Innodb_file_per_table = 1

    • Innodb_doublewriter = 1

  • MyISAM

    • delay_key_write

      • OFF:每次写操作后刷新键缓冲中的脏块到磁盘
      • ON: 支队在键表时制定了delay_key_write选项的表使用延迟刷新
      • ALL:对所有MyISAM表都是用延迟建写入

安全相关配置参数

  • expire_logs_days

    • 指定自动清理binlog的天数

  • max_allowed_packet

    • 控制MySQL可以接受的包的大小

  • skip_name_resolve

    • 禁用DNS查找

  • sysdate_is_now

    • 确保sysdate()返回确定性日期

  • read_only

    • 禁止非super权限的用户写权限

  • skip_slave_start

    • 禁用Salve恢复

  • sql_mode

    • 设置MySQL所使用的SQL模式
    • strict_trans_tables
    • no_engine_subtitution
    • no_zero_date
    • no_zero_in_date
    • only_full_group_by

其他常用配置参数

  • sync_binlog

    • 控制MySQL如何向磁盘刷新binlog

  • tmp_table_size/max_heap_table_size

    • 控制内存临时表大小

  • max_connections

    • 控制允许的最大连接数

数据库结构设计和SQL语句

数据库设计对性能的影响

  • 过分的反范式化为表建立太多的列
  • 过分的范式化造成太多的表关联
  • OLTP环境中使用了不恰当的分区表
  • 使用外键保证数据的完整性

附Xmind

什么影响了MySQL性能