补充一：mysql并发

参考以下博主，不分先后：

https://blog.csdn.net/bzfys/article/details/48161021

https://blog.csdn.net/lu930124/article/details/72772803

https://blog.csdn.net/Kingson_Wu/article/details/70217230

https://blog.csdn.net/wsf568582678/article/details/53636747

http://blog.itpub.net/26022018/viewspace-1980883/

https://blog.csdn.net/caomiao2006/article/details/38568825

一、调整MySQL并发相关参数

 mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 58    |
| Threads_connected | 57    |   ###这个数值指的是打开的连接数
| Threads_created   | 3676  |   ###Threads_created表示创建过的线程数
| Threads_running   | 4     |   ###这个数值指的是激活的连接数，这个数值一般远低于connected数值
+-------------------+-------+

Threads_connected 跟show processlist（显示当前正在执行的MySQL连接）结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数。

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。

如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器

thread_cache_size配置：

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+

 命令： show status;

命令：show status like '%下面变量%';

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。 
Connections 试图连接MySQL服务器的次数。 不管成功与否。
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。 
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。 
Delayed_writes 用INSERT DELAYED写入的行数。 
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 
Flush_commands 执行FLUSH命令的次数。 
Handler_delete 请求从一张表中删除行的次数。 
Handler_read_first 请求读入表中第一行的次数。 
Handler_read_key 请求数字基于键读行。 
Handler_read_next 请求读入基于一个键的一行的次数。 
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。 
Handler_update 请求更新表中一行的次数。 
Handler_write 请求向表中插入一行的次数。 
Key_blocks_used 用于关键字缓存的块的数量。 
Key_read_requests 请求从缓存读入一个键值的次数。 
Key_reads 从磁盘物理读入一个键值的次数。 
Key_write_requests 请求将一个关键字块写入缓存次数。 
Key_writes 将一个键值块物理写入磁盘的次数。 
Max_used_connections 同时使用的连接的最大数目。 
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。 
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。 
Open_tables 打开表的数量。 
Open_files 打开文件的数量。 
Open_streams 打开流的数量(主要用于日志记载） 
Opened_tables 已经打开的表的数量。 
Questions 发往服务器的查询的数量。 
Slow_queries 要花超过long_query_time时间的查询数量。 
Threads_connected 当前打开的连接的数量。 
Threads_running 不在睡眠的线程数量。 
Uptime 服务器工作了多少秒。

２. 调整max_connections提高并发连接
参数max_connections控制允许连接到MySQL数据库的最大数量，默认值为151。

如果状态变量connection_errors_max_connections不为零，并且一直在增长，

就说明不断有连接请求因数据库连接数量已经到达最大的值而失败，应该考虑max_connections的值。

MySQL最大可支持连接取决很多，包括给定操作系统平台线程库的质量、内存大小、每个连接的符合以及期望的相应时间等。在linux下MySQL支持500~1000个连接不是难事。在windows平台下，受其线程库影响，最大连接数为：(open table*2 +open connections)<2048
每一个session操作MySQL数据库表都需要占用文件描述符，数据库连接本身也要占用文件描述，因此在增大max_connections时，也要考虑open_files_limit的设置是否够用。

这是是查询数据库当前设置的最大连接数：

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 60    |

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 5000  |
+------------------+-------+
1 row in set

mysql> SELECT @@MAX_CONNECTIONS;
+-------------------+
| @@MAX_CONNECTIONS |
+-------------------+
|               151 |
+-------------------+
1 row in set

mysql> SELECT @@open_files_limit;
+--------------------+
| @@open_files_limit |
+--------------------+
|               5000 |
+--------------------+

更改设置：

set global max_connections=1000;

以上设置，只是使其临时生效，重启mysql服务后将丢失。

永久生效，需要修改my.cnf配置文件

# vi /etc/my.cnf
找到max_connections一行，修改为（如果没有，则自己添加），
max_connections = 1000

2.   调整back_log
         back_log参数控制MySQL监听TCP端口时设置的挤压请求栈大小，5.6.6版本后默认值为50+(max_connections/5),5.6.6以前是50。
         如果需要漱口在较短时间内处理大量请求，应该考虑适当增大back_log的值。

mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 80    |
+---------------+-------+

3.   调整table_open_cache
         每个SQL执行线程至少都要打开一个表缓存，参数table_open_chche控制所有的SQL执行线程可打开缓存表的数量。这个参数的值应该根据最大连接数max_connections以及每个连接执行关联查询中所涉及表的最大数（用N表示）来设定
                   max_cannections*N
         在未执行flush table命令的情况下，如果MySQL状态变量opend_tables值较大，就说明table_open_cache设置太小，应适当增大。增大table_open_cache的值，会增加MySQL对文件描述符的使用量，因此，也要注意苹果open-files-limit设置是否够用。

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+

4.   调整thread_cache_size
         为了加快数据库的速度，MySQL会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控孩子MySQL缓存客户端服务线程的数量。
         可以通过计算线程cache的是效率threads_created/connections来横梁thread_cache_size的设置是否合适。该值越接近1，说明cache命中越低，应考虑适当增加thread_cache_size的值。

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+

5. innodb_lock_wait_timeout的设置
参数innodb_lock_wait_timeout可以控制InnoDB事务等待行锁的时间，默认值是50ms，可以根据需要动态设置。对于需要快速反馈的交互式OLTP应用，可以将行锁等待超时时间调小，避免事务长时间挂起；对于后台运行的批处理操作，可以讲行锁等待超时时间调大，以避免发生大的回滚操作。

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

二、mysql处理高并发-保证数据准确性

问题描述：压力高并发情况下执行update方法得到结果非预期结果

例如：多个线程执行充值金额方法

1、先查询出目前金额信息a

2、将充值的金额组装a(原金额+充值金额=新金额)

3、更新金额（执行update方法）

问题：多并发的情况下，会是多个线程同时读取到目前金额a , 之后多个线程进行分别组装数据，依次更新，这就导致 A账户本来有50元两个并发线程同时充值50元最终数据库中账户只有100元，而不是150元（第二次更新覆盖第一次更新）

解决方案：

１、先更新（更新会锁表）然后在查询如果金额超出预期直接回滚，更新中将加减运算放在sql中执行可以保证数据准确性。

2、乐观锁，类CAS机制

3、应用层分布式锁

可以在应用层使用一个分布式锁（可以放在Memcache中），控制同一时间，只允许一个应用实例进行查询并更新的操作。

4、使用队列

总库存：4个商品

请求人：a、1个商品 b、2个商品 c、3个商品

程序如下：

beginTranse(开启事务)

try{

$result = $dbca->query('select amount from s_store where postID = 12345');

if(result->amount > 0){

//quantity为请求减掉的库存数量

$dbca->query('update s_store set amount = amount - quantity where postID = 12345');

}

}catch($e Exception){

rollBack(回滚)

}

commit(提交事务)

以上代码就是我们平时控制库存写的代码了，大多数人都会这么写，看似问题不大，其实隐藏着巨大的漏洞。数据库的访问其实就是对磁盘文件的访问，数据库中的表其实就是保存在磁盘上的一个个文件，甚至一个文件包含了多张表。例如由于高并发，当前有三个用户a、b、c三个用户进入到了这个事务中，这个时候会产生一个共享锁，所以在select的时候，这三个用户查到的库存数量都是4个，同时还要注意，mysql innodb查到的结果是有版本控制的，再其他用户更新没有commit之前(也就是没有产生新版本之前)，当前用户查到的结果依然是就版本；

然后是update，假如这三个用户同时到达update这里，这个时候update更新语句会把并发串行化，也就是给同时到达这里的是三个用户排个序，一个一个执行，并生成排他锁，在当前这个update语句commit之前，其他用户等待执行，commit后，生成新的版本；这样执行完后，库存肯定为负数了。但是根据以上描述，我们修改一下代码就不会出现超买现象了，代码如下：

beginTranse(开启事务)

try{

//quantity为请求减掉的库存数量
$dbca->query('update s_store set amount = amount - quantity where postID = 12345');

$result = $dbca->query('select amount from s_store where postID = 12345');

if(result->amount < 0){

throw new Exception('库存不足');

}

}catch($e Exception){

rollBack(回滚)

}

commit(提交事务)

另外，更简洁的方法：

beginTranse(开启事务)

try{

//quantity为请求减掉的库存数量
$dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');

}catch($e Exception){

rollBack(回滚)

}

commit(提交事务)

=====================================================================================

1、在秒杀的情况下，肯定不能如此高频率的去读写数据库，会严重造成性能问题的
必须使用缓存，将需要秒杀的商品放入缓存中，并使用锁来处理其并发情况。当接到用户秒杀提交订单的情况下，先将商品数量递减（加锁/解锁）后再进行其他方面的处理，处理失败在将数据递增1（加锁/解锁），否则表示交易成功。
当商品数量递减到0时，表示商品秒杀完毕，拒绝其他用户的请求。

2、这个肯定不能直接操作数据库的，会挂的。直接读库写库对数据库压力太大，要用缓存。
把你要卖出的商品比如10个商品放到缓存中；然后在memcache里设置一个计数器来记录请求数，这个请求书你可以以你要秒杀卖出的商品数为基数，比如你想卖出10个商品，只允许100个请求进来。那当计数器达到100的时候，后面进来的就显示秒杀结束，这样可以减轻你的服务器的压力。然后根据这100个请求，先付款的先得后付款的提示商品以秒杀完。

3、首先，多用户并发修改同一条记录时，肯定是后提交的用户将覆盖掉前者提交的结果了。

这个直接可以使用加锁机制去解决，乐观锁或者悲观锁。
乐观锁，就是在数据库设计一个版本号的字段，每次修改都使其+1，这样在提交时比对提交前的版本号就知道是不是并发提交了，但是有个缺点就是只能是应用中控制，如果有跨应用修改同一条数据乐观锁就没办法了，这个时候可以考虑悲观锁。
悲观锁，就是直接在数据库层面将数据锁死，类似于oralce中使用select xxxxx from xxxx where xx=xx for update，这样其他线程将无法提交数据。
除了加锁的方式也可以使用接收锁定的方式，思路是在数据库中设计一个状态标识位，用户在对数据进行修改前，将状态标识位标识为正在编辑的状态，这样其他用户要编辑此条记录时系统将发现有其他用户正在编辑，则拒绝其编辑的请求，类似于你在操作系统中某文件正在执行，然后你要修改该文件时，系统会提醒你该文件不可编辑或删除。

4、不建议在数据库层面加锁，建议通过服务端的内存锁（锁主键）。当某个用户要修改某个id的数据时，把要修改的id存入memcache，若其他用户触发修改此id的数据时，读到memcache有这个id的值时，就阻止那个用户修改。

5、实际应用中，并不是让mysql去直面大并发读写，会借助“外力”，比如缓存、利用主从库实现读写分离、分表、使用队列写入等方法来降低并发读写。

posted on 2018-10-17 09:02 myworldworld 阅读(334) 评论(0) 收藏举报

刷新页面返回顶部

myworldworld

补充一：mysql并发

导航

公告