大话MySQL性能优化

发表于 分类于 Changyan： 本文字数： 3.9k 阅读时长 ≈ 4 分钟

MYSQL性能优化

性能是MySQL一直引以为豪的一点。在性能和功能两个方面，MySQL第一考虑的还是性能。

影响MySQL性能的相关因素

商业需求对性能的影响

对于某一些功能在整个系统中是画蛇添足的，那么这些需求就可能会影响系统性能。比如，一个论坛要求对在线人数进行实时统计。

系统架构及实现对性能的影响

服务器调优

应用程序调优

  • 不可是存储到数据库的数据
    • 二进制多媒体数据
    • 流水队列数据
    • 超大文本数据
  • 合理的利用应用层Cache（适合Cache的数据有：）
    • 系统的各种配置和规则数据
    • 活跃用户的基本信息数据
    • 活跃用户的个性化定制信息数据
    • 准实时的统计信息
    • 其他一些访问频繁但变更很少的数据
  • 数据层实现精简
  • 过度依赖数据库SQL语句功能
  • 常见的架构设计不当带来的性能问题和资源浪费
    • Cache系统设计不合理，导致Cache命中率低下
    • 过度依赖面向对象
    • 对可扩展性的过度追求（促使系统设计的时候对象拆分的过于分散，造成系统中出现大量的join语句）
    • 对数据库的过于依赖（将不适合存储在数据库中的数据存储在数据库中）
    • 过度理想化系统的用户体验（使大量非核心业务消耗大量系统资源）

Query语句对性能的影响

分析手段

  • explain

    分析选项：

    • 索引（key），query语句中是用到的索引
    • row中的rows，查找的记录数

  • profiling

    先打开profiling；set profiling=1；

    查看profiling信息：show profiling \G;

    show profile CPU,BLOCK IO FOR query 1(2);

    分析选项

    • CPU IO消耗

MySQL的锁机制

锁机制的目的： 保证数据的一致完整性

锁机制的影响： 直接影响一个数据库系统的并发处理能力和性能

  • 行级锁（row-level）

    优点： 锁定对象的粒度最小，发生锁定资源争用的概率就最小，能给予最大可能的并发处理能力。

    缺点： 由于粒度最小，所以每次获得锁和释放锁需要做的工作也最多。带来的消耗也就最大。另外，容易发生死锁。

  • 表级锁（table-level）

    优点： 最大粒度的锁定机制。所以逻辑简单，实现也较为容易。获得锁和释放锁的速度也很快。而且能很好的避免死锁问题。

    缺点： 出现锁定资源争用的概率会提高，导致并发处理能力大打折扣。

  • 页级锁（page-level）

    介于row-level和table-level二者之间。

合理的应用锁机制

MyISAM表锁优化

关键：提高并发度

  • 缩短锁定时间，即query的执行时间要尽可能的短
    • 尽量减少大的、复杂的query，将复杂的query拆分
    • 尽可能的建立高效索引，使数据的检索更加迅速
    • 尽量让MySQL的表只存放必要的信息，控制字段类型
  • 分离能并行的操作
    • 关键：Concurrent insert（并发插入特性）
    • 打开 Concurrent_insert功能，选项有0，1和2。具体说明如下：
    • concurrent_insert=2,无论MyISAM表数据文件中间部分是否存在因删除而留下的空闲空间，都允许在尾部进行concurrent insert操作。
    • concurrent_insert=1,当MyISAM表数据文件中间部分不存在因删除而留下的空闲空间，可以在尾部进行concurrent insert操作。
    • concurrent_insert=0,无论MyISAM表数据文件中间部分是否存在因删除而留下的空闲空间，都不允许在尾部进行concurrent insert操作。
    • 建议：如果数据删除的肯呢个性很小，则建议将concurrent_insert设置成1，如不在乎浪费少量空间设置成2也可以。但当有少量删除时，设置成1更合适。
  • 读写优先级设置
    • 默认，写的优先级大于读的优先级
    • 参数选项，low_priority_updates=1(将写的优先级调低)

InnoDB行锁优化

  • 尽可能让所有的数据检索都通过索引来完成，避免InnoDB因为无法通过索引键加锁升级成为表级锁定
  • 合理设计索引，让InnoDB加锁是尽可能准确，尽可能缩小锁定范围
  • 尽可能的减少基于范围的数据检索过滤条件
  • 尽量控制事务的大小，尽量减少锁定的资源量和锁定的时间长度
  • 尽量使用级别低的事务隔离级别，减少MySQL因为事务隔离带来的附加成本
  • 尽可能减少死锁产生的概率
    • 类似业务模块中，尽可能用相同的访问顺序来访问，防止产生死锁
    • 同一事务中，尽可能一次锁定所有需要的资源
    • 对于非常容易产生死锁的业务部分，可以尝试升级锁定粒度，通过表级锁定减少死锁产生的概率
  • 系统锁定争用情况的查询
    • 标记锁定争用状态变量
      mysql> show status like 'table%'
variable_name value
table_lock_immediate 100  //产生标记锁定次数
table_lock_waited 0       //出现标记锁定争用出现的等待的次数
    • 行级锁定争用状态变量
      mysql> show status like 'innodb_row_lock%'
innodb_row_lock_current_waits 0   //当前正在等待锁定的数量
innodb_row_lock_time 3999999      //从系统启动到现在锁定的总时间长度
innodb_row_lock_time_avg 36666    //每次等待所花费平均时间
innodb_row_lock_time_max 122222   //从启动到现在等待最长一次所花时间长度
innodb_row_lock_waits 20          //从启动到现在共等待的次数

垂直和水平联合切分的使用

优点：

  • 充分利用垂直切分和水平切分各自的优势，而避免各自的缺陷
  • 让系统扩展性得到最大提升

缺点：

  • 数据库系统架构比较复杂，使维护难度更大
  • 应用程序架构也相对更复杂

合理的设计并利用索引

MySQL常用的四种索引类型

  • B-tree索引
  • Hash索引
  • Fulltext索引
  • Rtree索引

索引的弊端

  • 增加了更新所带来的IO量，和调整索引导致的计算量
  • 占用存储空间，并会跟数据量的增加而增加

如何判断是否需要索引

  • 较频繁作为查询条件的字段应该创建索引
  • 唯一性太差的字段不适合做单独的索引，即使它频繁作为查询条件
  • 更新非常频繁的字段不适合做索引
  • 不会出现在where子句中的字段不适合做索引

MySQL索引的限制

  • MyISAM存储引擎索引键长度总和长度不能超过1000字节
  • Text和Blob类型的字段只能创建前缀索引
  • 使用不等于（!= 或 <> ）的时候MySQL无法使用索引
  • 过滤字段使用了函数运算后（如，abs（culumn）），MySQL无法使用索引
  • join语句中join条件字段类型不一致不能使用索引
  • 使用LIKE操作的时候，如果条件以通配符开始（如，’%abc’）MySQL无法使用索引
  • 使用非等值查询时，MySQL无法使用索引

Query的优化

优化更需要优化的query

高并发低消耗的query（相对）对整个系统的影响远比低并发高消耗的query要大。

定位优化对象的性能瓶颈（profiling）

  • IO，数据访问方面
  • CPU，数据运算（如分组、排序）

明确优化手段

优化更偏向与对系统功能比较重要的query

从explain入手

获取一个query在当前状态的数据库中的执行计划

多用profile

永远用小结果集驱动大结果集

尽可能在索引中完成排序

只需取出自己需要的column

仅适用最有效的过滤条件

where字句中的条件并非越多越好

尽可能的避免复杂的join查询和子查询

query语句涉及的表越多，需要锁定的资源就越多，所阻塞的其他线程也就越多。

其他优化

join的实现与优化

join是一种算法，即大名鼎鼎的Nested Loop Join。它是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

  • 尽可能的Join语句中的Nested Loop的循环次数

    最有效的方法只有一个：让驱动表的结果集尽可能的小。即『永远用小结果集驱动大结果集』

  • 优先优化Nested Loop的内层循环

  • 保证Join语句中被驱动表上的join字段已经被索引

  • 当无法保证被驱动表join字段被索引且内存充足的前提下，不要太吝啬joinBuffer的设置

order by的优化

  • 加大max_length_for_sort_data参数的设置
  • 去掉不必要的返回字段
  • 增大sort_buffer_size参数设置

Schema设计优化

  • 范式理论
  • 十度冗余，尽量减少join

大字段垂直拆分

什么样的字段适合

  • 大字段（比如，文章内容、帖子内容、产品的介绍、小说内容等）
  • 表中和其他字段比较，访问明显要少的字段

优点

  • 数据库拆分简单明了，拆分规则明确
  • 应用程序模块清晰明了，整合容易
  • 数据维护方便易行，容易定位

缺点

  • 部分表关联无法在数据库级别完成，需要在程序中完成
  • 对于访问极其频繁且数据量超大的表任然存在性能瓶颈，不一定满足要求
  • 事务处理相对更为复杂
  • 切分达到一定程度后，扩展性会收到限制
  • 过度切分可能会带来系统过度复杂而难以维护

大表水平拆分

基于类型的分拆优化

优点

  • 表关联基本能在数据库端完成
  • 不会存在某些超大型数据量和高负载的表遇到的瓶颈问题
  • 应用程序端整体架构改动相对较少
  • 事务处理相对简单
  • 只要切分规则能够定义好，基本上较难遇到扩展性限制

缺点

  • 切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则
  • 后期数据的维护难度有所增加，人为手工定位数据更困难
  • 应用系统各模块耦合度较高，可能会对后面数据的迁移、拆分造成一定困难

MySQL的备份与恢复

备份使用场景

  • 数据丢失应用场景
    • 人为操作事务造成的某些数据丢失
    • 如那件Bug造成的数据部分丢失或者全部丢失
    • 硬件故障造成的数据部分或者全部丢失
    • 安全漏洞被入侵数据被恶意破坏
  • 非数据丢失应用场景
    • 特殊应用场景下基于时间点恢复
    • 开发测试环境数据库搭建
    • 相同数据库的新环境搭建
    • 数据库或者数据迁移