mysql(2)相关知识

最新推荐文章于 2023-07-04 15:41:59 发布

大神，快来碗里

最新推荐文章于 2023-07-04 15:41:59 发布

阅读量1w

点赞数 2

分类专栏： mysql 文章标签： mysql 相关知识

本文链接：https://blog.csdn.net/cowbin2012/article/details/89684034

版权

mysql 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

文章目录

1. Mysql 表.idb文件损坏，无法启动数据库？

linux配置文件路径/etc/my.cnf

[mysql]下面添加语句：innodb_force_recovery=1

数字可以更换，其代表的含义如下：

(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
尝试用.frm文件恢复数据

2. 如何查找MySQL中查询慢的SQL语句

MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

【 vi /etc/my.cnf】

在[mysqld]里面加上以下内容

slow_query_log=ON                        #开启慢日志
slow_query_log_file=/opt/logs/slow_query_log.log   #日志存放位置，注意日志文件的权限
long_query_time=1   #当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短

查看show variables like '%quer%'

log_queries_not_using_indexes=on #记录没有使用索引的查询语句。！可能导致日志文件激增，谨慎使用。配合log_throttle_queries_not_using_indexes 使用。
log_throttle_queries_not_using_indexes #表示每分钟允许记录到slow log的且未使用索引的sql语句次数。配合long_queries_not_using_indexes开启使用。
min_examined_row_limit = 1000 #查询检查返回少于该参数指定行的SQL不被记录到慢查询日志.需要开启log_queries_not_using_indexes=on 。注意：1查询结果数量是<不会被记录，=会被记录。2，这个参数开启后，long_query_time 参数失效。
log_slow_admin_statements #记录ALTER TABLE等语句引发的慢查询
log_slow_slave_statements #记录从服务器产生的慢查询
min_examined_row_limit=Num of Rows 类似于SELECT … FROM TBL LIMIT N这样的全表扫描的查询,如果–log_queries_not_using_indexes被开启的话,因为用不到索引将要报告为慢查询，可以在配置文件中使用min_examined_row_limit=Num of Rows来设置，如果要检查的行数大于等于这个量的查询，才会被报告为慢查询。

3. Mysql的.frm .MYD .MYI .idb？

如数据库dbshared，表user。
1、如果表格b采用MyISAM，在数据库/var/lib/mysql/dbshared/user 中会产生3个文件：
b.frm ：描述表结构文件，字段长度等
b.MYD(MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)
b.MYI(MYIndex)：索引信息文件。

2、如果表格user采用InnoDB，在数据库/var/lib/mysql/dbshared/user中会产生1个或者2个文件：
b.frm ：描述表结构文件，字段长度等
如果采用独立表存储模式，/var/lib/mysql/dbshared/user中还会产生b.ibd文件（存储数据信息和索引信息）
如果采用共存储模式的，数据信息和索引信息都存储在ibdata1中

4. 碎片整理

optimize table

当我们使用mysql进行delete数据，delete完以后，发现空间文件ibd并没有减少。

好处除了减少表数据与表索引的物理空间，还能降低访问表时的IO，这个比较理解，整理之前，取数据需要跨越很多碎片空间，这时需要时间的，整理后，想要的数据都放在一起了，直接拿就拿到了，效率提高。

这是因为optimize table的本质，是alter table

mysql 5.5 的改表过程如下

1.创建一张新的临时表 tmp

2.把旧表锁住，禁止插入删除，只允许读写（这就是为什么上面的insert语句都停留在waiting for table metadata lock）

3.把数据不断的从旧表，拷贝到新的临时表，(这就是上面报copy to tmp table)

4.等表拷贝完后，进行瞬间的rename操作

5.旧表删除掉

所以optimize最大的问题是锁表

5.数据库什么情况下索引会失效

like查询是以%开头
where语句中使用 or，但是没有把or中所有字段加上索引。

注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
where语句中对字段表达式操作
如果mysql估计使用全表扫描要比使用索引快,则不使用索引

查看索引的使用情况：

show status like 'Handler_read%'

注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数

handler_read_rnd_next:这个值越高，说明查询低效

others

没有查询条件，或者查询条件没有建立索引
查询的数量是大表的大部分，应该是30％以上。

索引的优点

大大减少了服务器需要扫描的数据量
可以帮助服务器避免排序或减少使用临时表排序
索引可以随机I/O变为顺序I/O

索引的缺点

需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间
降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂
索引会产生相应的碎片，产生维护开销

6.数据库高并发下的优化思路;

一、想办法减小查询次数

1，页面静态化- 用户可以直接获取页面，不用走那么多流程，比较适用于页面不频繁更新。

2，使用缓存- 第一次获取数据从数据库准提取，然后保存在缓存中，以后就可以直接从缓存提取数据。不过需要有机制维持缓存和数据库的一致性。

3，使用储存过程-那些处理一次请求需要多次访问数据库的操作，可以把操作整合到储存过程，这样只要一次数据库访问就可以了。

4，批量读取 - 高并发情况下，可以把多个请求的查询合并到一次进行，以减少数据库的访问次数

5，延迟修改 - 高并发情况下，可以把多次修改请求，先保存在缓存中，然后定时将缓存中的数据保存到数据库中，风险是可能会断电丢失缓存中的数据，

6, 使用索引 - 索引可以看作是特殊的缓存，尽量使用索引就要求where字句中精确的给出索引列的值。

二、尽量让查询出来的数少

1，分表 - 把本来同一张表的内容，可以按照地区，类别等分成多张表，很简单的一个思路，但是要尽量避免分出来的多表关联查询。

2，分离活跃数据 - 例如登录用户业务，注册用户很多，但是活跃的登录用户很少，可以把活跃用户专门保存一张表，查询是先查询活跃表，没有的话再查总表，这也类似与缓存啦。

3，分块 - 数据库层面的优化，对程序是透明的，查询大数据只用找到相应块就行。

三、分流

1，集群 - 将并发请求分配到不同的服务器上，可以是业务服务器，也可以是数据库服务器。

2，分布式 - 分布式是把单次请求的多项业务逻辑分配到多个服务器上，这样可以同步处理很多逻辑，一般使用与特别复杂的业务请求。

3，CDN - 在域名解析层面的分流，例如将华南地区的用户请求分配到华南的服务器，华中地区的用户请求分配到华中的服务器。

四、MySQL主从配置，实现读写分离，减轻数据库压力

my.cnf my.ini

原理：主服务器（Master）负责网站NonQuery操作，从服务器负责Query操作，用户可以根据网站功能模特性块固定访问Slave服务器，或者自己写个池或队列，自由为请求分配从服务器连接。主从服务器利用MySQL的二进制日志文件，实现数据同步。二进制日志由主服务器产生，从服务器响应获取同步数据库。

7. mysql有哪些锁，意向锁有什么用

表级锁

1. 优点

实现逻辑简单，开销小。
获取锁和释放锁的速度快。
由于表级锁一次会将整个表锁定，所以能很好的避免死锁问题。

2. 缺点

由于锁粒度最大，因此出现争用被锁定资源的概率也会最高，致使并发度十分低下。

3.表级锁类型

MySQL的表级锁有两种类型：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

锁模式的兼容性：

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

MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

4.查看表级锁争用情况

执行：show status like ‘table%’;

Table_locks_immediate：产生表级锁定的次数；
Table_locks_waited：出现表级锁定争用而发生等待的次数；

如果Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

5.优化表级锁定

优化表级锁时的最大问题是：提高并发度

通过减少查询时间缩短锁定时间

缩短锁定时间的总体原则是：让Query执行时间尽可能的短。

尽量减少大的、复杂的Query，将复杂Query分拆成几个小的Query分步执行；
尽可能的建立足够高效的索引，让数据检索更迅速；
尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型；
利用合适的机会优化MyISAM表数据文件。

设置可并发插入：concurrent_insert=2

MyISAM的表锁虽是读写互相阻塞的，但依然能够实现并行操作。MyISAM存储引擎有一个控制是否打开Concurrent Insert（并发插入）功能的参数选项：concurrent_insert，取值范围为0，1，2。

concurrent_insert=0，不允许并发插入。
concurrent_insert=1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个线程读表的同时，另一个线程从表尾插入记录。这是MySQL的默认设置；
concurrent_insert=2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录；
所以，我们可通过设置concurrent_insert=2，同时定期在系统空闲时段执行optimize table tableName语句来整理空间碎片，收回因删除记录而没有真正释放的空间，从而提高并发。optimize参考：mysql中OPTIMIZE TABLE的作用及使用

合理设置读写优先级

MyISAM存储引擎默认是写优先级大于读优先级。即使是写请求后到，写锁也会插到读锁请求之前。

但是，有时像修改文章点击数操作是不那么重要的，我们希望的是读更快，此时我们可以这样：

UPDATE LOW_PRIORITY article SET click_num=134 WHERE id = 823
LOW_PRIORITY使得系统认为update操作优化级比读操作低，如果同时出现读操作和上面的更新操作，则优先执行读操作。

MySQL提供了几个语句调节符，允许你修改它的调度策略：

LOW_PRIORITY关键字应用于：DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。
HIGH_PRIORITY关键字应用于：SELECT、INSERT语句。
delayed(延迟)关键字应用于：INSERT、REPLACE语句。

如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，那么可能使用low-priority-updates选项来启动服务器。然后可通过使用insert HIGH_PRIORITY table…来把个别我们希望的INSERT语句提高到正常的写入优先级。

三分钟了解Mysql的表级锁——《深究Mysql锁》

行级锁

1. 优点

由于锁粒度小，争用率低，并发高。

2. 缺点

实现复杂，开销大。
加锁慢、容易出现死锁

3.行级锁类型

InnoDB的行级锁定同样分为两种类型：共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。

意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX）

五分钟了解Mysql的行级锁——《深究Mysql锁》

意向锁

为什么没有意向锁的话，表锁和行锁不能共存？
举个粟子（此时假设行锁和表锁能共存）：事务A锁住表中的一行（写锁）。事务B锁住整个表（写锁）。

但你就会发现一个很明显的问题，事务A既然锁住了某一行，其他事务就不可能修改这一行。这与”事务B锁住整个表就能修改表中的任意一行“形成了冲突。所以，没有意向锁的时候，行锁与表锁共存就会存在问题！

意向锁是如何让表锁和行锁共存的？
有了意向锁之后，前面例子中的事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。

所以，意向锁的作用就是：

当一个事务在需要获取资源的锁定时，如果该资源已经被排他锁占用，则数据库会自动给该事务申请一个该表的意向锁。如果自己需要一个共享锁定，就申请一个意向共享锁。如果需要的是某行（或者某些行）的排他锁定，则申请一个意向排他锁。

注：意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

意向锁是表锁还是行锁？
首先可以肯定的是，意向锁是表级别锁。意向锁是表锁是有原因的。

当我们需要给一个加表锁的时候，我们需要根据意向锁去判断表中有没有数据行被锁定，以确定是否能加成功。如果意向锁是行锁，那么我们就得遍历表中所有数据行来判断。如果意向锁是表锁，则我们直接判断一次就知道表中是否有数据行被锁定了。

一分钟深入Mysql的意向锁——《深究Mysql锁》

8.数据库索引为什么要用 B+ 树而不用红黑树呢？

磁盘读写有一个最少内容的限制，即使我们只需要这个页上的一个字节的内容，我们也要含着泪把一整个页上的内容读完。

由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅，深度代表什么？代表磁盘 io 次数啊！数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个分页上最多能放多少节点设计的啊！

B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。所以从Mysql（Inoodb）的角度来看，B+树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上。
那么Mysql如何衡量查询效率呢？– 磁盘IO次数。 B-树/B+树的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。
另一个优点是： B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。

B树相对于红黑树的区别

AVL 数和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

为什么Mysql用B+树做索引而不用B-树或红黑树

9.最左前缀的解释

1.索引左前缀性的第一层意思：必须用到索引的第一个字段。

在a和c列上建普通索引： create index iN_AAA_1 on AAA (a, c); 必须有a出现在where 语句中才会使用到该索引。

2. 索引前缀性的第二层意思：对于索引的第一个字段，用like时左边必须是固定值，通配符只能出现在右边。

**3.索引前缀性的第三层意思：如果在字段前加了函数，则索引会被抑制，**例如：select * from aaa where trim(a)=1,则不会用到索引。

**在字段前嵌入了表达式，索引也将被抑制。**假设a是date格式的，那么where a+7<sysdate将不会用到索引，而where a<sysdate-7会用到索引。

还有两个特殊声明：

1).select * from AAA where a=:xxx and c=sysdate与 select * from AAA where c=sysdate and a=:xxx;都会用到索引，即与where语句中字段出现的顺序无关；

2).select * from AAA where a=:xxx and b=1;会使用索引，此时A出现，即使其他字段不是索引字段也会使用到索引。

10.什么是redo日志、什么是undo日志

undo回滚日志

作用：
保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容：
逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

什么时候产生：
事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

什么时候释放：
当事务提交之后，undo log并不能立马被删除，
而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

在线回收表空间的功能是建立再独立undo表空间的基础上的，如果还是用的系统共享表空间ibdata1，则不支持在线回收机制。如果需要设置独立表空间，需要在初始化数据库实例的时候，指定独立表空间的数量。

MySQL跟undo有关的参数设置有这些 :show global variables like '%undo%'

innodb_undo_log_truncate --参数设置为1,即开启在线回收(收缩)undo log日志文件,支持动态设置。
innodb_undo_tablespaces --参数必须大于或等于2,即回收(收缩)一个undo log日志文件时,要保证另一个undo log是可用的。
innodb_undo_logs --回滚段的数量,至少大于等于3,默认128。
innodb_max_undo_log_size --当超过这个阀值(默认是1G),会触发truncate回收(收缩)动作,truncate后空间缩小到10M。
innodb_purge_rseg_truncate_frequency --控制回收(收缩)undo log的频率.undo log空间在它的回滚段没有得到释放之前不会收缩，
innodb_undo_directory --日志存放路径

如果修改配置前已经生成了回滚日志，重新创建实例

innodb_undo_log_truncate=1
innodb_undo_tablespaces=2
innodb_purge_rseg_truncate_frequency=1
innodb_undo_directory=/opt/logs/undo

redo重做日志

作用：
确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

内容：
物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

什么时候产生：
事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

什么时候释放：
当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用

当数据库对数据做修改的时候，需要把数据页从磁盘读到buffer pool中，然后在buffer pool中进行修改，那么这个时候buffer pool中的数据页就与磁盘上的数据页内容不一致，称buffer pool的数据页为dirty page 脏数据，如果这个时候发生非正常的DB服务重启，那么这些数据还没在内存，并没有同步到磁盘文件中（注意，同步到磁盘文件是个随机IO），也就是会发生数据丢失，如果这个时候，能够在有一个文件，当buffer pool 中的data page变更结束后，把相应修改记录记录到这个文件（注意，记录日志是顺序IO），那么当DB服务发生crash的情况，恢复DB的时候，也可以根据这个文件的记录内容，重新应用到磁盘文件，数据保持一致。

这个文件就是redo log ，用于记录数据修改后的记录，顺序记录。它可以带来这些好处：

当buffer pool中的dirty page 还没有刷新到磁盘的时候，发生crash，启动服务后，可通过redo log 找到需要重新刷新到磁盘文件的记录；
buffer pool中的数据直接flush到disk file，是一个随机IO，效率较差，而把buffer pool中的数据记录到redo log，是一个顺序IO，可以提高事务提交的速度；

进制日志（binlog）

作用：
1，用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
2，用于数据库的基于时间点的还原。

内容：
逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。
但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，
也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。
在使用mysqlbinlog解析binlog之后一些都会真相大白。
因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

什么时候产生：
事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。
这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。
因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。
这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

什么时候释放：
binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

对应的物理文件：
配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。
对于每个binlog日志文件，通过一个统一的index文件来组织。

MySQL中的重做日志（redo log），回滚日志（undo log），以及二进制日志

redo和undo日志

11.数据库常见死锁原因及处理

当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死。

数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁。

(1)事务之间对资源访问顺序的交替

出现原因：
一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：
这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

并发修改同一记录

出现原因：
　用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁由于比较隐蔽，但在稍大点的项目中经常发生。
　一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

解决方法：
a. 使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

b. 使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观！

索引不当导致全表扫描

出现原因：
如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法：
SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

MySQL数据库死锁的解决办法

解除正在死锁的状态有两种方法：

第一种：

1.查询是否锁表
show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist

3.杀死进程id（就是上面命令的id列）
kill id

第二种：

1.查看下在锁的事务 
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id（就是上面命令的trx_mysql_thread_id列）
kill 线程ID

其它关于查看死锁的命令：

1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

12.MySQL中的MVCC机制是什么意思，根据具体场景，MVCC是否有问题

MVCC主要适用于Mysql的RC,RR隔离级别 Mysql中MVCC的使用及原理详解

不同的数据库实现MVCC(多版本并发控制)的方式是不一样的，因为MVCC没有一个统一的实现标准，这里我们只谈论MySQL InnoDB中的MVCC。MVCC之（乐观锁），是通过在每行记录保存两个隐藏列来实现的。这两个列，一个是存创建时间，一个是删除时间，这里的时间指的是，系统版本号，并不是真正的时间值。
每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录版本号比较。

1 为什么需要MVCC
InnoDB采用的是两阶段锁定协议，事务执行过程中，根据需要不断的加锁，最后COMMIT或ROLLBACK的时候一次性释放所有锁，实质上是一种悲观并发控制（悲观锁），而悲观锁会降低系统的并发性能。为了提高并发性能，InnoDB同时还实现了多版本并发控制（MVCC）。

2 MVCC的实现原理
简单来说，MVCC就是同一条数据可以同时存在多个版本：更新数据时，先插入一条新记录，然后把旧记录标记为删除；删除数据时将记录标记为删除；查询时只查询事务开始前就已存在的记录。详细的实现就不展开讲了，网上有很多相关资料，详情可以参考这个网站，或者参阅《高性能MySQL第三版》1.4章节。

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，**在同一个事务里能够看到数据一致的视图。**根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

3 MVCC解决的问题

通过了解实现原理，可以看出MVCC解决了这些问题：

保证了事务周期内数据的一致性。事务A开启后，即使事务B对数据做了修改/新增/删除，不管事务B有没有提交，这些变更对于事务A的SELECT语句都是不可见的，因为这些变更是其它事务发起的，并且是在事务A开启后发生的。也就是说，它解决了不可重复读和幻读的问题。
提高了数据库的并发性能，试想，如果一个数据只有一个版本，那么多个事务对这个数据进行读写是不是需要读写锁来保护? 加锁的话就会造成阻塞，阻塞就会降低并发性能。而在MVCC里，每一个事务都有对应的数据版本，事务A开启后，即使数据被事务B修改，也不影响事务A那个版本的数据，事务A依然可以无阻塞的读取该数据，当然，只是读取不阻塞，写入还是阻塞的，如果事务A也想修改该数据，则必须要等事务B提交释放所有锁后，事务A才可以修改。所以MVCC解决的只是读-写的阻塞问题，写-写依然还是阻塞的。

补充：

1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）.

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。

3.串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

4.通过以上总结，可知，MVCC主要作用于事务性的，有行锁控制的数据库模型。

13.MySQL数据库的隔离级别，以及如何解决幻读

查看当前session的事务隔离级别：

show variables like '%tx_isolation%';
#查看全局的事务隔离级别
show global variables like '%tx_isolation%';

MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）。

未提交读（READUNCOMMITTED）。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。
提交读（READCOMMITTED）。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）。
可重复读（REPEATABLEREAD）。在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象（稍后解释）。
串行化（SERIALIZABLE）。读操作会隐式获取共享锁，可以保证不同事务间的互斥。(SERIALIZABLE是相当严格的串行化执行模式，不管是读还是写，都会影响其他读取相同的表的事务。是严格的表级读写排他锁。也就失去了innodb引擎的优点 )

四个级别逐渐增强，每个级别解决一个问题。

脏读，最容易理解。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据。
不重复读。解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致。
幻读。解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

如何解决可重复读问题呢？

1：当前读:给改条数据加锁(悲观锁),即读取的时候就加锁，防止其他事务读取该数据().它能保证读取的都是最新数据
2：快照读:mvcc，多版本并发控制，详细的原理就不说了，它使得读写不冲突，由于读的都是事务的当前版本的数据，因此重复读也不会发现数据被修改

在mysql种这两种方式的体现就是加锁和不加锁

普通查询不加锁使用快照读 ,如果在事务中出了查询外还有其他增加修改操作那么久要手动加锁实现当前读。

是否mvcc就解决了幻读呢？

一定程度上可以这么说，因为它保证事务中多次读取不会产生新的记录。
但是我们如果在事务中进行了插入操作，是否就能保证这条记录在插入时不存在呢？这点是不能保证的，这也是快照读的弊端。因此如果我们要保证操作安全就要手动加锁，如
select name from student where id >= 0 for update…或者使用串行化的隔离级别

MySQL InnoDB四个事务级别与脏读、不重复读、幻读

大神，快来碗里

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql(2)相关知识

文章目录1. Mysql 表.idb文件损坏，无法启动数据库？2. 如何查找MySQL中查询慢的SQL语句3. Mysql的.frm .MYD .MYI .idb？4. 碎片整理5.数据库什么情况下索引会失效6.数据库高并发下的优化思路;7. mysql有哪些锁，意向锁有什么用8.数据库索引为什么要用 B+ 树而不用红黑树呢？9.最左前缀的解释10.什么是redo日志、什么是undo日志11.数...
复制链接

扫一扫