mysql性能优化介绍

女爷i 2022-03-17 04:10 158阅读 0赞

### 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逻辑架构-解析查询 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70 1]  
解析查询可以自动优化sql语句

## MySql存储引擎 ##

#看你的mysql现在已提供什么存储引擎: 
          mysql> show engines;
          #看你的mysql当前默认的存储引擎:
          mysql> show variables like '%storage_engine%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70 2]  
**Innodb是MySql 5.5以及以后版本默认存储引擎**

mysql5.6以前默认为系统表空间，之后默认为独立表空间

系统表空间无法简单的收缩文件大小  
独立表空间可以通过optimize table 收缩系统文件  
系统表空间会产生IO瓶颈  
独立表空间可以同时向多个文件刷新数据

**建议：Innodb使用独立表空间**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70 3]

## 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）  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70 4]  
    **事务并发问题**
5.  脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
6.  不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
7.  幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

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

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

# 2.业务设计 #

# 3.查询以及索引 #

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70]: /images/20220317/087339865bfd49dfb30b0421c10f9de8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70 1]: /images/20220317/55d329aa0cd04cadadc1240a40b22817.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70 2]: /images/20220317/768259b8aecd4a908816d8815b2a9dfc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70 3]: /images/20220317/b2557e8bfeb24aa9b860ac53831fe6cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxOTY5MzU4_size_16_color_FFFFFF_t_70 4]: /images/20220317/e050c42a47ed410fadded944c634d926.png