MySQL应用优化

[TOC]

1.数据库连接池

对于数据库来说，频繁的关闭创建连接是比较消耗资源的，所以有必要建立数据库连接池

2.减少对MySQL的访问

在写代码的时候，一个sql查询了某两个字段，而另一个sql查询了id字段，则可以将这sql合并，就可以减少对数据进行重复检索。

还可以增加缓存层，使用Mybatis、Hibernate提供的一级二级缓存，或者Redis数据库来缓存数据

3.负载均衡

（1）利用某种均衡算法，将载荷量分布在不同的服务器上，比如Nginx代理服务器

（2）利用MySQL主从复制，实现读写分离

我们有一个MySQL的主节点服务器，它会将数据同步到其他节点的服务器，并且完全一致，在进行增删改操作的时候，主节点服务器发生修改，并且同步到子节点服务器，但是在查询的时候，就不需要通过主节点服务器，直接在子节点进行操作，这样主从复制读写分离就能分解数据库的压力。

（3）使用分布式数据库架构

4.MySQL查询缓存优化

在MySQL中开启缓存查询，当执行完全相同的SQl语句的同时，服务器就直接在缓存中拿数据，数据被修改缓存就会失效。

1.服务器收到一个sql

2.先检查缓存器中是否有缓存，有的话直接返回结果

3.没有的话要进行解析SQL解析、预处理，再由优化器生成对应的执行计划

4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询

5.将结果返回给客户端

5.MySQL如何使用缓存

1.首先查看数据库是否支持查询缓存

show variables like \'have_query_cache\';

2.查看数据库是否开启了缓存

show variables like \'query_cache_type\';

OFF或者0：查询缓存关闭

ON或者1：查询缓存打开，符合条件的SQL就会缓存，显式指定SQL_NO_CACHE不予缓存

DEMAND或者2：查询缓存按需进行，显式指定SQL_CACHE的select语句才混缓存

找到mysql的配置文件然后加上，就开启了，重启MySQL服务生效

query_cache_type=1

通过查询语句的执行时间，可以判断是否进入了缓存

3.查询缓存占用的大小下面单位是字节算出来大约是1M

show variables like \'query_cache_size\';

4.查看查询缓存的状态变量

show status like \'Qcache%\';

Qcache_free_blocks：可用的内存快的个数

Qcache_free_memory：可用的内存空间

Qcache_hits：查询缓存的命中次数

Qcache_inserts：添加到查询缓存的次数如果MySQL中没有操作过缓存这两个数据为0

Qcache_lowmem_prunes：内存不足查询缓存中删除的次数

Qcache_not_cached：非缓存查询的次数

Qcache_queries_in_cache：查询缓存中注册的查询数

Qcache_total_blocks ：查询缓存中的块总数

5.查询缓存select选项

可以在select查询的时候选择使用缓存或者不使用缓存

SQL_CATCH：如果查询缓存已经打开，则缓存查询结果

SQL_NO_CATCH：服务器不使用查询缓存，不缓存

select  SQL_CATCH * from Student;

6.查询结果缓存失效

（1）SQL语句不相同，这里就不再演示了

（2）查询语句结果不固定，比如查询当前时间等等

select Now()

（3）没有使用表的语句

（4）查询Mysql系统表的时候

（5）在存储的函数，或者存储过程中的查询

（6）表被更改，增删改都可以让表发生更改，删除表。就会将表的缓存删除变为无效

6.MySQL内存管理以及优化

原则

（1）要尽量将多的内存分配给MySQL

（2）MyISAM存储引擎依赖操作系统本身的IO，因此如果有MyISAM表就要预留更多的内存给操作系统

（3）排序区和缓存区的内存要合理分配，因为过大的话，并发连接较高的时候，就会导致物理内存消耗

MyISAM内存优化

存储引擎使用key_buffer 缓存索引块，加速myisam索引的读写速度。对于myisam表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。

key_buffer_size

key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值，对于一般MyISAM数据库，建议至少将1/4可用内存分配给key_buffer_size。

在MySQL配置文件中做如下配置：

key_buffer_size=512M

read_buffer_size

如果需要经常顺序扫描myisam表，可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。

read_rnd_buffer_size

对于需要做排序的myisam表的查询，如带有order by子句的sql，适当增加 read_rnd_buffer_size 的值，可以改善此类的sql性能。但需要注意的是 read_rnd_buffer_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费。

InnoDB内存优化

InnoDB用内存区做缓存，用来缓存数据块和索引块，因此就要给InnoDB分配过多的缓存，在MySQL配置文件中

innodb_buffer_pool_size

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

innodb_buffer_pool_size=512M

innodb_log_buffer_size

决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

innodb_log_buffer_size=10M

MySQL并发参数的调整

max_connections

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。

Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

show variables like \'max_connection\';

back_log

back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5），但最大不超过900。

如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值。

show variables like \'back_log\';

table_open_cache

该参数用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定：

max_connections x N ；

show variables like \'table_open_cache\';

thread_cache_size

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量。

show variables like \'thread_cache_size\';

innodb_lock_wait_timeout

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ，可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起；对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。

show variables like \'innodb_lock_wait_timeout\';

MySQL锁

锁（避免资源争抢）

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

MySQL锁

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如

Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发

查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM表锁

（1）在MyISAM引擎中查询会自动的加上读锁，增删改自动加上写锁。不需要人为加上

（2）读锁：给一个表接了一个读锁之后，其他客户端也可以查询到，因为读锁可以共同进行不会影响操作，必须要解锁之后

unlock tables

，才能进行增删改，不然直接进行修改表，会进去等待状态。

（3）写锁：在进行写锁之后，在当前客户端可以增删改查，但是在其他客户端执行操作就会进入等待状态，需要解锁才可以进行下一步操作。

可见

（1）对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

（2）对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

查看锁的争用指令

show open tables;

In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。

Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

show status like \'table_locks%\';

Table_locks_immediate ：指的是能够立即获得表级锁的次数，每立即获取锁，值加1。

Table_locks_waited ：指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

InnoDB行锁

InnoDB 与 MyISAM 的最大不同有两点：

（1）支持事务（TRANSACTION）；

（2）采用了行级锁。

事务是由一组 SQL 语句组成的逻辑处理单元，事务具有以下 4 个属性，通常简称为事务的ACID 属性。

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如 B 树索引或双向链表）也都必须是正确的。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

事务隔离级别

数据库实现事务隔离的方式，基本上可分为以下两种。

（1）在读取数据前，对其加锁，阻止其他事务对数据进行修改。

（2）不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或MCC），也经常称为多版本数据库。

MySQL默认隔离级别

show variables like \'tx_isolation\';

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交：别人改数据的事务提交之后，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读，我读到的还是我自己刚开始看到的数据。
串行：我的事务尚未提交，别人就别想改数据，改不了，得等我改完了才能继续改。

这4种隔离级别，并行性能依次降低，安全性依次提高。

InnoDB行锁演示

先关闭InnoDB表的事务自动提交，这种情况下只有提交事务才会生效，然后打开终端A更新一条id=3的数据，现在数据更新成功但是不提交（commit）,再打开终端B，写上同样的Sql语句，语句执行就处于等待状态，这时候提交A终端数据，B终端正常更新了。InnoDB行级锁只锁定一行，更新两条不同的行，正常可以获取到锁，只要进行增删改的时候InnoDb就会为行加上排它锁，别的事务无法执行SQL语句。关闭自动提交，每次相当于开启了一个事务，其他地方提交的东西看不到，因为事务具有隔离级别，再提交一次才可以看到。

行锁升级表锁的原因

在关闭事务自动提交之后，更新查询字段为varchar没有加单引号导致索引会失效，索引失效就会导致行锁升级为表锁。

间隙锁的危害

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁；对于键值在条件范围内但并不存在的记录，叫做 "间隙（GAP）" ， InnoDB也会对这个 "间隙" 加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

比如在数据中更新分数大于80的学生，因为数据中有的学生没有成绩，为空也会被加上锁。这时候没有提交数据，将无法更新分数大于80的学生。

查看锁的争用情况

show status like \'innodb_row_lock%\';

Innodb_row_lock_current_waits: 当前正在等待锁定的数量Innodb_row_lock_time: 从系统启动到现在锁定总时间长度Innodb_row_lock_time_avg:每次等待所花平均时长Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间Innodb_row_lock_waits: 系统启动后到现在总共等待的次数当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能表现比表锁更高一些但是在横踢并发处理方面能力要远远优于MyISAM的表锁，当系统并发量较高的时候，InnoDB的增提性能和MyISAM相比就会有比较明显的优势。但是当InnoDB行级锁处理不当的时候，可能让InnoDB整体性能表现比更差。

建议：

尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽可能缩小锁的范围
尽可能减少索引失效的条件，及索引范围，避免间隙锁。
尽可能控制事务大小，减少锁定资源和时间长度。
尽可能使用低级别事务隔离

MySQL应用优化