大话MySQL性能优化

最新推荐文章于 2022-02-24 16:15:55 发布

BUG弄潮儿

最新推荐文章于 2022-02-24 16:15:55 发布

阅读量200

点赞数

文章标签：数据库 mysql java 分布式 redis

阅读文本大概需要3分钟。

性能是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          //从启动到现在共等待的次数