MYSQL性能优化-锁机制记录

MYSQL性能优化-锁机制记录

1. 应用优化

1.1 使用连接池

创建数据库连接池,以提高访问性能.

1.2 减少对MYSQL的访问

1.2.1 避免对数据进行重复检索

在编写应用代码时,需要能够理清对数据库的访问逻辑.能够一次连接就获取到结果的,就不用两次连接,这样可以大大减少对数据库无用的重复请求.

1.2.2 增加cache层

在应用中,我们可以在应用中增加缓存 层来达到减轻数据库负担的目的.例如使用redis数据库来缓存数据.

1.3 负载均衡

负载均衡时应用中使用非常普遍的一种优化方法.

1.3.1 利用MYSQL复制分流查询

通过MYSQL的主从复制,实现读写分离,使增删改操作走主节点,使查询走从节点,从而可以降低单台服务器的读写压力.

1.3.2 采用分布式数据库架构

分布式数据库架构适合大数据量,负载高的情况.它有良好的拓展性和高可用性.通过多台服务器之间分布数据,可以实现在多台服务器之间负载均衡策略.

2. MYSQL中查询缓存优化

2.1 概述

开启MYSQL的查询缓存,当执行完全相同的SQL语句的时候,服务器就会直接从缓存中读取结果,当数据被修改,之前的缓存会失效,修改比较频繁的表不适合做查询缓存.

2.2 操作流程

2.3 查询缓存配置

-- 1. 查询缓存是否支持缓存
show variables like 'have_query_cache';

-- 2. 查询缓存是否开启
show variables like 'query_cache_type';

-- 3. 查询缓存的占用空间大小
show variables like 'query_cache_size';

-- 4. 查询缓存状态信息
show status like 'Qcache%';

2.4 开启查询缓存

#开启mysql查询缓存
linux系统下 : /usr/my.cnf 文件 -> query_cache_type=1;

#重启数据库即可打开mysql缓存
service mysql restart

2.5 查询缓存SELECT选项

SQL_CACHE : 如果查询结果是可缓存的,并且query_cache_type系统变量的值为ON或者DEMAND,则缓存查询结果.

SQL_NO_CACHE : 服务器不使用查询缓存.它既不检查查询缓存,也不检查结果是否已缓存,也不缓存查询结果.

示例:

SELECT SQL_CACHE xxx FROM table_name;

SELECT SQL_NO_CACHE xxx FROM table_name;

2.6 查询缓存失效的情况

SQL语句不一致的情况,想要命中缓存,必须的SQL语句就必须一致.

-- 大小写不一致则会导致查询缓存失效
select * from table_name;

Select * form table_name;

-- SQL语句中包含不确定数值时,不会使用缓存
select now(); 

-- 不使用任何表,不会使用缓存

-- 查询系统数据库的数据时,不会使用缓存

-- 在存储的函数,触发器,事件的主体内执行的查询,不会使用缓存

-- 当查询数据库数据发生变化时,缓存会被清空

3. MYSQL内存管理及优化

3.1 内存优化原则

1. 将尽量多的内存分配给MYSQL做缓存,但要给操作系统和其他程序预留足够的内存.
2. MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存,因此,如果有MyIASM表,就要预留更多的内存给操作系统做IO缓存
3. 排序区,连接区等缓存时分配给每个数据库会话专用的,其默认值的设置要根据最大连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽.

3.2 MyISAM内存优化

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

key_buffer_size

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

在_/usr/my.cnf中做如下配置:

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独占的,如果默认值设置太大,就会造成内存浪费.

3.3 InnoDB内存优化

innodb用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块.

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

4. MYSQL并发参数的调整

从实现上来说,MYSQL Server 是多线程结构,包括后台线程和客户服务线程.多线程可以有效利用服务器资源,提高数据库的并发性能.在MYSQL中,控制并发连接和线程的主要参数包括max_connections,back_log, thread_cache_size, table_open_cache.

4.1 max_connections

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

MYSQL最大可支持的连接数,取决于很多因素,包括给定操作系统平台的线程库的质量,内存大小,每个连接的负荷,CPU的处理速度,期望的响应时间等.在Linux平台下,性能好的服务器.支持500~1000个连接属于正常范畴.调节此参数需要根据服务器性能进行评估设定.

4.2 back_log

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

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

4.3 table_open_cache

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

4.4 thread_cache_size

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

4.5 innodb_lock_wait_timeout

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

5. Mysql锁问题

5.1 锁分类

从对数据操作的粒度分为:

1. 表锁: 操作时,会锁定整个表.
2. 行锁: 操作时,会锁定当前操作行.

从对数据操作的类型分为:

1. 读锁(共享锁) : 针对同一份数据,多个读操作可以同时进行而不会互相影响.
2. 写锁(排它锁) : 当前操作没有完成之前,它会阻断其他写锁和读锁.

5.2 MYSQL锁

相对于其它数据库而言,MYSQL的锁机制比较简单,其最显著的特点时不同的存储引擎支持不同的锁机制.

MYSQL 3种锁的特性归纳如下:

锁类型	特点
表级锁	偏向MyISAM存储引擎,开销小,加锁快;不会出现死锁;锁定粒度大;发生锁冲突的概率最高,并发最低
行级锁	InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度小;发生锁冲突的概率最低,并发最高
页面锁	开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发一般

5.2.1 如何加表锁

MyISAM在执行查询语句( SELECT )之前,会自动给涉及的所有表加读取锁,在执行更新操作( UPDATE, DELETE, INSERT 等) 前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE 命令给MyISAM表显式加锁.

显式加表锁语法:

-- 加读锁
lock table table_name read;

-- 加写锁
lock table table_name write;

-- 释放锁
unlock tables;

-- 查询数据库所有表加锁状态
show open tables;

-- 查询表的锁定状态
show status like 'Table_locks%'

5.3 InnoDB行锁

5.3.1 行锁介绍

行锁特点: InnoDB存储引擎 与 MyISAM最大的不同点: 1. 支持事务 2.采用行级锁

事务及其属性:

ACID属性	含义
原子性(Atomicity)	事务是一个原子操作单元,其对数据的修改,要么全部成功,要么全部失败
一致性(Consistent)	在事务开始和完成时,数据都必须保持一致状态
隔离性(Isolation)	数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的"独立"环境下运行
持久性(Durable)	事务完成之后,对于数据的修改是永久的

并发事务处理产生的问题:

问题	含义
丢失更新	当两个或多个事务选择同一行,最初的事务修改的值,会被后面的事务修改的值覆盖
脏读	在一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据
不可重复读	一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现和以前读出的数据不一致
幻读	一个事务按照相同的查询条件重新读取以前查询过的数据,却发现其他事务也插入了满足其查询条件的新数据

事务的隔离级别:

隔离级别	丢失更新	脏读	不可重复读	幻读
Read uncommitted	×	√	√	√
Read committed	×	×	√	√
Repeatable read( 默认 )	×	×	×	√
Serializable (串行化)	×	×	×	×

查看当前数据库的隔离级别:

show variables like 'tx_isolation';

5.3.2 InnoDB的行级锁模式

innoDB实现了以下两种类型的行锁:

1. 共享锁 (S) : 又称为读锁,简称 S 锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能读取数据.
2. 排他锁 (X) : 又称为写锁,简称 X 锁,排他锁就是不能与其他锁并存,如果一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务可以对数据进行读取和修改.
3. 对于UPDATE,DELETE,INSERT语句,InnoDB会自动给涉及数据行加排他锁(X);
4. 对于普通SELECT语句,InnoDB不会加任何锁.

示例如下:

-- 共享锁 (S)
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

-- 排他锁 (X)
SELECT * FROM table_name WHERE ... FOR UPDATE

5.3.3 无索引行锁升级为表锁

当索引失效时,行锁会升级为表锁.

5.3.4 InnoDB行锁争用情况查询

show status like 'innodb_row_lock%';