MySQL常见瓶颈

  1. CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  2. IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看系统的性能状态

    性能下降SQL慢 执行时间长 等待时间长 原因分析

  • 查询语句写的烂
  • 索引失效（单值、复合）
  • 关联查询太多join（设计缺陷或不得已的需求）
  • 服务器调优及各个参数设置（缓冲、线程数等）

    MySQL常见性能分析手段

    在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

    性能瓶颈定位

    我们可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：
    1. Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）
    2. Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）
    3. Mysql> show innodb status ——显示InnoDB存储引擎的状态
    4. Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
    5. Shell> mysqladmin variables -u username -p password——显示系统变量
    6. Shell> mysqladmin extended-status -u username -p password——显示状态信息

索引使用建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
  • 少用Hint强制索引

参数优化

  1. max_connections允许客户端并发连接的最大数量
  2. max_connect_errors如果客户端尝试连接的错误数量超过这个参数设置的值
  3. interactive_timeout：Mysql关闭交互连接前的等待时间，单位是秒，默认是8小时，建议不要将该参数设置超过24小时，即86400
  4. wait_timeout：Mysql关闭非交互连接前的等待时间，单位是秒，默认是8小时，建议不要将该参数设置超过24小时，即86400
  5. thread_cache_size：MySQL服务缓存以重用的线程数。当客户端断开连接的时候，如果线程缓存没有使用满，则客户端的线程被放入缓存中。如果有客户端断开连接后再次连接到MySQL服务且线程在缓存中，则MySQL服务会优先使用缓存中的线程；如果线程缓存没有这些线程，则MySQL服务器会创建新的线程。如果数据库有很多的新连接，可以增加这个参数来提升性能。如果MySQL服务器每秒有上百个连接，可以增大thread_cache_size参数来使MySQL服务器使用缓存的线程。通过检查Connections和Threads_created状态参数，可以判断线程缓存是否足够。这个参数默认的值是由下面的公式来决定的：8 + (max_connections / 100)
  6. innodb_buffer_pool_size：InnDB存储引擎缓存表和索引数据所使用的内存大小。默认值是128MB。在以InnDB存储引擎为主的系统中，可以将这个参数设为机器物理内存的80%。
  7. innodb_buffer_pool_instances：InnoDB缓存池被分成的区域数。对于1GB以上大的InnoDB缓存，将缓存分成多个部分可以提高MySQL服务的并发性，减少不同线程读缓存页的读写竞争。每个缓存池有它单独的空闲列表、刷新列表、LRU列表和其他连接到内存池的数据结构，它们被mutex锁保护。这个参数只有将innodb_buffer_pool_size参数设为1GB或以上时才生效。建议将每个分成的内存区域设为1GB大小。

连接数过高

  1. too many connections
  2. kill多余连接，加大连接数
  3. 限制连接数，max_user_connections
  4. 定时检查，kill慢查询

分区、分表、分库

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。
当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率
能干嘛

  • 逻辑数据分割
  • 提高单一的写和读应用速度
  • 提高分区范围读查询的速度
  • 分割数据能够有多个不同的物理文件路径
  • 高效的保存历史数据

怎么玩
首先查看当前数据库是否支持分区

  • MySQL5.6以及之前版本：

    SHOW VARIABLES LIKE '%partition%';
复制代码

  • MySQL5.6：

    show plugins;
复制代码

    分区类型及操作

  • RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。mysql将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。
    按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，比如交易表啊，销售表啊等，可以根据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。
    range 来分，好处在于说，扩容的时候很简单。

  • LIST分区：类似于按RANGE分区，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。
  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
    hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表
  • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

  • 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
  • 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
  • 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

    随着业务的发展，业务越来越复杂，应用的模块越来越多，总的数据量很大，高并发读写操作均超过单个数据库服务器的处理能力怎么办？

这个时候就出现了数据分片，数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。
区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

说说分库与分表的设计

MySQL分表

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

  • 垂直拆分
    垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。
  • 水平拆分(数据分片)单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。水平分割的几种方法：
    • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
    • 还可根据时间放入不同的表，比如：article_201601，article_201602。
    • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
    • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

落地方案 - 图1

MySQL分库

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

分库是什么？

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。
优点：

  • 减少增量数据写入时的锁对查询的影响
  • 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是它无法解决单表数据量太大的问题
分库分表后的难题
分布式事务的问题，数据的完整性和一致性问题。
数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。 跨库联合查询的问题，可能需要两次查询 跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并 额外的数据管理负担，如：访问数据表的导航定位 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。