金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > Mysql锁的优化,对锁机制的影响金沙棋牌app手机下

Mysql锁的优化,对锁机制的影响金沙棋牌app手机下

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-09-23 05:16

1. 恢复和复制的需要,对innodb锁机制的影响

获取锁等待情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:mysql> show status like 'Table%';+----------------------------+----------+| Variable_name | Value |+----------------------------+----------+| Table_locks_immediate | 105 || Table_locks_waited | 3 |+----------------------------+----------+2 rows in set (0.00 sec) 可以通过检查Innodb_row_lock状态变量来分析系统上的行锁的争夺情况:mysql> show status like 'innodb_row_lock%';+----------------------------------------+----------+| Variable_name | Value |+----------------------------------------+----------+| Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 2001 || Innodb_row_lock_time_avg | 667 || Innodb_row_lock_time_max | 845 || Innodb_row_lock_waits | 3 |+----------------------------------------+----------+5 rows in set (0.00 sec) 另外,针对Innodb类型的表,如果需要察看当前的锁等待情况,可以设置InnoDB Monitors,然后通过Show innodb status察看,设置的方式是: CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;监视器可以通过发出下列语句来被停止: DROP TABLE innodb_monitor;设置监视器后,在show innodb status的显示内容中,会有详细的当前锁等待的信息,包括表名、锁类型、锁定记录的情况等等,便于进行进一步的分析和问题的确定。打开监视器以后,默认情况下每15秒会向日志中记录监控的内容,如果长时间打开会导致.err文件变得非常的巨大,所以我们在确认问题原因之后,要记得删除监控表以关闭监视器。或者通过使用--console选项来启动服务器以关闭写日志文件。什么情况下使用表锁
表级锁在下列几种情况下比行级锁更优越:很多操作都是读表。
在严格条件的索引上读取和更新,当更新或者删除可以用单独的索引来读取得到时:
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
SELECT 和 INSERT 语句并发的执行,但是只有很少的 UPDATE 和 DELETE 语句。
很多的扫描表和对全表的 GROUP BY 操作,但是没有任何写表。

你目前接触的mysql版本是什么?除了官方版本,还接触过其他的mysql分支版本嘛?

一、并发性

  mysql 通过binlog文件对增删除改等更新数据的sql语句,实现数据库的恢复和主从复制。mysql的恢复机制(复制其实就是在slave mysql不断做基于binglog的恢复)特点有如下:
  (1) mysql 的恢复是sql语句级的,也就是重新执行binlog中的sql语句, oracle数据库则是基于数据库文件块的。
  (2) mysql 的binlog是按照事务提交的先后顺序记录的,恢复也是按这个顺序进行的。这也与oracle不同,oracle是按照系统更新号(SCN)来恢复数据的。

什么情况下使用行锁
行级锁定的优点:当在许多线程中访问不同的行时只存在少量锁定冲突。
回滚时只有少量的更改。
可以长时间锁定单一的行。

产生分支的原因

并发性是oltp数据库最重要的特性,但并发涉及到资源的获取、共享与锁定。

2.  insert into 和create table对于原表也会加共享锁   下面演示原表加锁的例子:

行级锁定的缺点:比页级或表级锁定占用更多的内存。
金沙棋牌app手机下载 ,当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。
用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。

许多开发人员认为有必要将其拆分成其他项目,并且每个分支项目都有自己的专长。该需求以及Oracle对核心产品增长缓慢的担忧,导致出现了许多开发人员感兴趣的子项目和分支

mysql:
mysql以表级锁为主,对资源锁定的粒度很大,如果一个session对一个表加锁时间过长,会让其他session无法更新此表中的数据。
虽然InnoDB引擎的表可以用行级锁,但这个行级锁的机制依赖于表的索引,如果表没有索引,或者sql语句没有使用索引,那么仍然使用表级锁。

会话1

会话2

SET autocommit=0;

SELECT * FROM city WHERE CityCode='003'

city_id      country_id        cityname CityCode

103  2       杭州         003

SET autocommit=0;

SELECT * FROM city WHERE CityCode='003'

city_id      country_id        cityname CityCode

103  2       杭州         003

INSERT INTO  cityNew

SELECT  * FROM city WHERE CityCode='003'

共 1 行受到影响

 

 

UPDATE city SET CityCode='004' WHERE CityCode='003'

等待超时

Lock wait timeout exceeded; try restarting transaction

Commit;

 

 

Commit;

insert …select …带来的问题
当使用insert...select...进行记录的插入时,如果select的表是innodb类型的,不论insert的表是什么类型的表,都会对select的表的纪录进行锁定。对于那些从oracle迁移过来的应用,需要特别的注意,因为oracle并不存在类似的问题,所以在oracle的应用中insert...select...操作非常的常见。例如:有时候会对比较多的纪录进行统计分析,然后将统计的中间结果插入到另外一个表,这样的操作因为进行的非常少,所以可能并没有设置相应的索引。如果迁移到mysql数据库后不进行相应的调整,那么在进行这个操作期间,对需要select的表实际上是进行的全表扫描导致的所有记录的锁定,将会对应用的其他操作造成非常严重的影响。究其主要原因,是因为mysql在实现复制的机制时和oracle是不同的,如果不进行select表的锁定,则可能造成从数据库在恢复期间插入结果集的不同,造成主从数据的不一致。如果不采用主从复制,关闭binlog并不能避免对select纪录的锁定,某些文档中提到可以通过设置innodb_locks_unsafe_for_binlog来避免这个现象,当这个参数设置为true的时候,将不会对select的结果集加锁,但是这样的设置将可能带来非常严重的隐患。如果使用这个binlog进行从数据库的恢复,或者进行主数据库的灾难恢复,都将可能和主数据库的执行效果不同。因此,我们并不推荐通过设置这个参数来避免insert...select...导致的锁,如果需要进行可能会扫描大量数据的insert...select操作,我们推荐使用select...into outfile和load data infile的组合来实现,这样是不会对纪录进行锁定的。next-key锁对并发插入的影响
在行级锁定中,InnoDB 使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定:当它搜索或扫描表的索引之时,它对遇到的索引记录设置共享或独占锁定。因此,行级锁定事实上是索引记录锁定。InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定,另一个用户 不能紧接在R之前以索引的顺序插入一个新索引记录。这个间隙的锁定被执行来防止所谓的“幽灵问题”。可以用next-key锁定在你的应用程序上实现一个唯一性检查:如果你以共享模式读数据,并且没有看到你将要插入的行的重复,则你可以安全地插入你的行,并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复。因此,the next-key锁定允许你锁住在你的表中并不存在的一些东西。隔离级别对并发插入的影响
REPEATABLE READ是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE语句只锁定找到的索引记录,而不锁定记录前的间隙。用其它搜索条件,这些操作采用next-key锁定,用next-key锁定或者间隙锁定锁住搜索的索引范围,并且阻止其它用户的新插入。在持续读中,有一个与READ COMMITTED隔离级别重要的差别:在这个级别,在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句,这些SELECT语句对相互之间也是持续的。READ COMMITTED隔离级别是一个有些象Oracle的隔离级别。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MOD语句仅锁定索引记录,而不锁定记录前的间隙,因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录,而不包括记录前的间隙。在范围类型UPDATE和DELETE语句,InnoDB必须对范围覆盖的间隙设置next-key锁定或间隙锁定以及其它用户做的块插入。这是很必要的,因为要让MySQL复制和恢复起作用,“幽灵行”必须被阻止掉。如果应用是从基于ORACLE的应用迁移到MYSQL数据库的,那么建议使用该隔离级别提供数据库服务,因为该隔离级别是最接近ORACLE的默认隔离级别的,迁移可能遇到的锁问题最小。如何减少锁冲突
对Myisam类型的表:1) Myisam类型的表可以考虑通过改成Innodb类型的表来减少锁冲突。2) 根据应用的情况,尝试横向拆分成多个表或者改成Myisam分区对减少锁冲突也会有一定的帮助。对Innodb类型的表:1) 首先要确认,在对表获取行锁的时候,要尽量的使用索引检索纪录,如果没有使用索引访问,那么即便你只是要更新其中的一行纪录,也是全表锁定的。要确保sql是使用索引来访问纪录的,必要的时候,请使用explain检查sql的执行计划,判断是否按照预期使用了索引。2) 由于mysql的行锁是针对索引加的锁,不是针对纪录加的锁,所以虽然是访问不同行的纪录,但是如果是相同的索引键,是会被加锁的。应用设计的时候也要注意,这里和Oracle有比较大的不同。3) 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,当表有主键或者唯一索引的时候,不是必须使用主键或者唯一索引锁定纪录,其他普通索引同样可以用来检索纪录,并只锁定符合条件的行。4) 用SHOW INNODB STATUS来确定最后一个死锁的原因。查询的结果中,包括死锁的事务的详细信息,包括执行的SQL语句的内容,每个线程已经获得了什么锁,在等待什么锁,以及最后是哪个线程被回滚。详细的分析死锁产生的原因,可以通过改进程序有效的避免死锁的产生。5) 如果应用并不介意死锁的出现,那么可以在应用中对发现的死锁进行处理。6) 确定更合理的事务大小,小事务更少地倾向于冲突。7) 如果你正使用锁定读,(SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE),试着用更低的隔离级别,比如READ COMMITTED。8) 以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。

三个流行MySQL分支:Drizzle、MariaDB和Percona Server(包括XtraDB引擎)

oracle:
oracle使用行级锁,对资源锁定的粒度要小很多,只是锁定sql需要的资源,并且加锁是在数据库中的数据行上,不依赖与索引。所以oracle对并发性的支持要好很多。

  上面的例子中,只是简单的读取city表,相当于一个普通的select 语句,在这里innodb给city表加了共享锁,并有使用多版本数据一致性技术。原因还是为了保证恢复和复制的正确性,因为不加锁,上述语句的执行过程中,其他事务对city表做了更新操作,可能导致数据恢复结果错误。如需要演示这种可以将系统变量 innodb_locks_unsafe_for_binlog的值设置为"NO"不加共享锁(set innodb_locks_unsafe_for_binlog='on') 默认是"OFF" 。如果设置上面的值为ON, 可能会使Binlog中记录的sql执行顺序不一致,使用恢复的结果与实际的应用逻辑不符,如果进行复制,就会导致主从数据库不一致。
  如果不想设置为ON,又不希望对源表的并发更新产生影响,可以使用 into outfile 将city表导入到一个txt文件,再使用load data infile 导入到新表。使用这种间接方式不会对源city表加锁。

MariaDB不仅是mysql的替代品,主要还是创新和提高mysql自有技术。

二、一致性
oracle:
oracle支持serializable的隔离级别,可以实现最高级别的读一致性。每个session提交后其他session才能看到提交的更改。oracle通过在undo表空间中构造多版本数据块来实现读一致性,
每个session查询时,如果对应的数据块发生变化,oracle会在undo表空间中为这个session构造它查询时的旧的数据块。

 

   新功能介绍

mysql:
mysql没有类似oracle的构造多版本数据块的机制,只支持read commited的隔离级别。一个session读取数据时,其他session不能更改数据,但可以在表最后插入数据。
session更新数据时,要加上排它锁,其他session无法访问数据。

  1. multi-source replication 多源复制

  2. 表的并行复制

  3. galera cluster集群

  4. spider水平分片

  5. tokuDB存储引擎

三、事务
oracle很早就完全支持事务。

XtraDB是innodb存储引擎的增强版,可用来更好地发挥最新的计算机硬件系统性能,还包含在高性能模式下的新特性。它可以向下兼容,因为它是在innodb基础上构建,所以他有更多的指标和扩展功能。而且它在cpu多核的条件下,可以更好地使用内存,时数据库性能提到更高!

mysql在innodb存储引擎的行级锁的情况下才支持事务。

Drizzle与mysql的差别就比较大了,并且不能兼容,如果想运行此环境,就需要重写一些代码了!

四、数据持久性
oracle
保证提交的数据均可恢复,因为oracle把提交的sql操作线写入了在线联机日志文件中,保持到了磁盘上,
如果出现数据库或主机异常重启,重启后oracle可以考联机在线日志恢复客户提交的数据。
mysql:
默认提交sql语句,但如果更新过程中出现db或主机重启的问题,也许会丢失数据。

 

五、提交方式
oracle默认不自动提交,需要用户手动提交。
mysql默认是自动提交。

Question 2:

六、逻辑备份

mysql主要的存储引擎myisam和innodb的不同之处?

oracle逻辑备份时不锁定数据,且备份的数据是一致的。

  1. 事务的支持不同(innodb支持事务,myisam不支持事务)

  2. 锁粒度(innodb行锁应用,myisam表锁)

  3. 存储空间(innodb既缓存索引文件又缓存数据文件,myisam只能缓存索引文件)

  4. 存储结构

    (myisam:数据文件的扩展名为.MYD myData ,索引文件的扩展名是.MYI myIndex)

mysql逻辑备份时要锁定数据,才能保证备份的数据是一致的,影响业务正常的dml使用。

      (innodb:所有的表都保存在同一个数据文件里面 即为.Ibd)

七、热备份
oracle有成熟的热备工具rman,热备时,不影响用户使用数据库。即使备份的数据库不一致,也可以在恢复时通过归档日志和联机重做日志进行一致的回复。
mysql:
myisam的引擎,用mysql自带的mysqlhostcopy热备时,需要给表加读锁,影响dml操作。
innodb的引擎,它会备份innodb的表和索引,但是不会备份.frm文件。用ibbackup备份时,会有一个日志文件记录备份期间的数据变化,因此可以不用锁表,不影响其他用户使用数据库。但此工具是收费的。
innobackup是结合ibbackup使用的一个脚本,他会协助对.frm文件的备份。

   5. 统计记录行数

八、sql语句的扩展和灵活性
mysql对sql语句有很多非常实用而方便的扩展,比如limit功能,insert可以一次插入多行数据,select某些管理数据可以不加from。
oracle在这方面感觉更加稳重传统一些。

       (myisam:保存有表的总行数,select count(*) from table;会直接取出出该值)

九、复制
oracle:既有推或拉式的传统数据复制,也有dataguard的双机或多机容灾机制,主库出现问题是,可以自动切换备库到主库,但配置管理较复杂。
mysql:复制服务器配置简单,但主库出问题时,丛库有可能丢失一定的数据。且需要手工切换丛库到主库。

       (innodb:没有保存表的总行数,select count(*) from table;就会遍历整个表,消耗相当大)

十、性能诊断
oracle有各种成熟的性能诊断调优工具,能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等
mysql的诊断调优方法较少,主要有慢查询日志。

 

十一、权限与安全

Question  3:

mysql的用户与主机有关,感觉没有什么意义,另外更容易被仿冒主机及ip有可乘之机。
oracle的权限与安全概念比较传统,中规中矩。

Innodb的体系结构简单介绍一下?

十二、分区表和分区索引
oracle的分区表和分区索引功能很成熟,可以提高用户访问db的体验。
mysql的分区表还不太成熟稳定。

谈及到innodb的体系结构,首先要考虑mysql的体系结构,分为两部分mysql的server层和存储引擎层

十三、管理工具
oracle有多种成熟的命令行、图形界面、web管理工具,还有很多第三方的管理工具,管理极其方便高效。
mysql管理工具较少,在linux下的管理工具的安装有时要安装额外的包(phpmyadmin, etc),有一定复杂性。

先要跟面试官聊清楚mysql的整体方向,然后再去涉及innodb体系结构

建议从三方面介绍innodb体系结构:内存----线程-----磁盘

内存中包含insert_buffer,data_buffer,index_buffer,redo_log_buffer,double_write

内存刷新到磁盘的机制,redo,脏页,binlog的刷新条件

各种线程的作用,master_thread,purge_thread,redo log thread,read thread,write thread,page cleaner thread

磁盘中存放着数据文件,redo log,undo log,binlog

 

Question  4:

mysql有哪些索引类型:

  1. 数据结构角度上可以分:B+tree索引,hash索引,fulltext索引(innodb,myisam都支持)

  2. 存储角度上可以分:聚集索引,非聚集索引

  3. 逻辑角度上可以分:primary key,normal key,单列,复合,覆盖索引

     

     

 

Question  5:

mysql binlog有几种格式:

  1. statement

    优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能

    缺点:当使用一些特殊函数的时候,或者跨库操作的时候容易丢失数据

注:在生产中不建议使用

  1. row

    优点:清晰记录每行的数据信息,不会出现跨库丢数据的情况

    缺点:内容当记录到日志中的时候,都将以每行记录的修改来记录,但就会产生大量的binlog,对于网络开销也比较大

注:生产中推荐使用

  1. mixed

    是mysql5.1的时候,一个过渡版本,DDL语句会记录成statement,DML会记录row。

注:生产中不建议使用

 

Qusetion 6:

mysql主从复制的具体原理是什么?

主 服务器把数据更新记录到二进制日志中,从服务器通过io thread向主库发起binlog请求,主服务器通过IO dump thread把二进制日志传递给从库,从库通过io thread记录到自己的中继日志中。然后再通过sql thread应用中继日志中sql的内容。

 

Qusetion 7:

数据库中双一是什么?

sync_binlog=1

innodb_flush_log_at_trx_commit=1

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数

innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且刷到磁盘中去。

sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去

 

Qusetion 8:

如何监控mysql  replication复制延迟?

  1. 可以通过第三方工具 业界中的瑞士军刀percona-toolkit中的命令,pt-heartbeat进行主从延迟监控。

  2. 传统方法,通过比较主从服务器之间的position号的差异值。

  3. 还可以通过查看seconds_behind_master估算一下主从延迟时间

     

 

Qusetion  9:

大表DDL语句,如何实施,才能把性能影响降到最低?

  1. 可以通过传统方法导入导出数据,新建一张与原表一样的表结构,把需要执行的ddl语句在无数据的新表执行,然后把老表中的数据导入到新表中,把新表改成老表的名字

  2. 通过第三方工具 业界中的瑞士军刀percona-toolkit中的命令,pt-online-schema-change进行在线操作

  3. 对于新版本的mysql(5.7)可以直接在线online ddl

 

Qusetion  10:

为什么要为innodb表设置自增列做主键?

1.使用自增列做主键,写入顺序是自增的,和B+数叶子节点分裂顺序一致

2.表不指定自增列做主键,同时也没有可以被选为主键的唯一索引,InnoDB就会选择内置的rowid作为主键,写入顺序和rowid增长顺序一致

所以InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话,这时候存取效率是最高

 

Qusetion  11:

如何优化一条有问题的sql语句?

针对sql语句的优化,我们不要上来就回答添加索引,这样显得太不专业。我们可以从如下几个角度去分析

  1. 回归到表的设计层面,数据类型选择是否合理

  2. 大表碎片的整理是否完善

  3. 表的统计信息,是不是准确的

  4. 审查表的执行计划,判断字段上面有没有合适的索引

  5. 针对索引的选择性,建立合适的索引(就又涉及到大表DDL的操作问题)

 

Qusetion  12:

服务器负载过高或者网页打开缓慢,简单说说你的优化思路 ?

  1. 首先我们要发现问题的过程,通过操作系统,数据库,程序设计,硬件角度四个维度找到问题所在

  2. 找到瓶颈点的位置

  3. 制定好优化方案,形成处理问题的体系

  4. 体系制定好之后,在测试环境进行优化方案的测试

  5. 测试环境如果优化效果很好,再实施到生产环境

  6. 做好处理问题的记录

 

Qusetion  13:

接触过哪些mysql的主流架构?架构应用中有哪些问题需要考虑?

  1. M-S

  2. MHA

  3. MM keepalived

  4. PXC

共同存在的问题:主从延迟问题的存在,在主库宕机,切换过程中要考虑数据一致性的问题,避免出现主从复制不一致

 

Qusetion14:

什么是死锁?锁等待?如何优化这类问题?通过数据库哪些表可以监控?

死锁是指两个或多个事务在同一资源上互相占用,并请求加锁时,而导致的恶性循环现象。当多个事务以不同顺序试图加锁同一资源时,就会产生死锁。

锁等待:mysql数据库中,不同session在更新同行数据中,会出现锁等待

重要的三张锁的监控表innodb_trx,innodb_locks,innodb_lock_waits

 

Qusetion  15:

处理过mysql哪些案例

我们可以简单从mysql四个知识模块跟他聊聊mysql体系结构,数据备份恢复,优化,高可用集群架构

  1. mysql版本的升级

  2. 处理mysql集群的各种坑和问题

  3. 根据公司业务类型,设计合理mysql库,表,架构。

  4. 定期进行灾备恢复演练

  5. 误删除数据之后,恢复数据

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:Mysql锁的优化,对锁机制的影响金沙棋牌app手机下

关键词: