mysql性能优化
- 前言
- 1 MySql架构与存储引擎
- MySql逻辑架构-缓存
- MySql逻辑架构-解析查询
- MySql存储引擎
- MySql锁
- MySql事务
- 2.业务设计
- 3.查询以及索引
前言
分三个方面和大家介绍下实际工作中如何对mysql进行优化
衡量指标
TPS:Transactions Per Second(每秒传输的事物处理个数),这是指服务器每秒处理的事务数,支持事务的存储引擎如InnoDB等特有的一个性能指标。
QPS:Queries Per Second(每秒查询处理量)同时适用与InnoDB和MyISAM 引擎
等待时间:执行Sql等待返回结果之间的等待时间
TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME
QPS=QUESTIONS/UPTIME
1 MySql架构与存储引擎
MySql逻辑架构-缓存
//查看缓存是否开启
show variables like '%query_cache_type%'
//如果不是ON,修改配置文件以开启查询缓存:vi /etc/my.cnf
[mysqld]中添加:
query_cache_size = 20M
query_cache_type = ON
生产环境中不建议开启,容易出现脏读,占用过度内存
MySql逻辑架构-解析查询
解析查询可以自动优化sql语句
MySql存储引擎
#看你的mysql现在已提供什么存储引擎:
mysql> show engines;
#看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
Innodb是MySql 5.5以及以后版本默认存储引擎
mysql5.6以前默认为系统表空间,之后默认为独立表空间
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table 收缩系统文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
建议:Innodb使用独立表空间
MySql锁
MySQL的锁机制比较简单
其最显著的特点是不同的存储引擎支持不同的锁机制
比如:
MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);
InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
1.表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
2.行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
3.页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
仅从锁的角度来说:
表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如OLAP系统
行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适
InnoDb行锁
在mysql 的 InnoDB引擎支持行锁
行锁:
共享锁又称:读锁。当一个事务对某几行上读锁时,允许其他事务对这几行进行读操作,但不允许其进行写操作,也不允许其他事务给这几行上排它锁,但允许上读锁。
排它锁又称:写锁。当一个事务对某几个上写锁时,不允许其他事务写,但允许读。更不允许其他事务给这几行上任何锁。包括写锁。
MySql事务
事务的特性
- 原子性(atomicity)。一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。
- 一致性(consistency)。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
- 隔离性(isolation)。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
- 持久性(durability)。持久性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
事务的四种隔离级别
- 未提交读(READ UNCOMMITED)
- 已提交读 (READ COMMITED)
- 可重复读(REPEATABLE READ)
- 可串行化(SERIALIZABLE)
事务并发问题 - 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
- 不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。
- 幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。
不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表
隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大,对于多数应用程序,可以优先考虑把数据库系统的隔离级别设为Read Committed,它能够避免脏读取,而且具有较好的并发性能。
2.业务设计
3.查询以及索引