mysql性能优化介绍

前言

分三个方面和大家介绍下实际工作中如何对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事务

事务的特性

1. 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
2. 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
3. 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事务的四种隔离级别

1. 未提交读（READ UNCOMMITED）
2. 已提交读 （READ COMMITED）
3. 可重复读（REPEATABLE READ）
4. 可串行化（SERIALIZABLE）
   
   事务并发问题
5. 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
6. 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
7. 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。

2.业务设计

3.查询以及索引