遗留mysql数据库问题

最新推荐文章于 2021-01-19 11:38:27 发布

IO大仙

最新推荐文章于 2021-01-19 11:38:27 发布

阅读量340

点赞数

分类专栏：面试疑难

本文链接：https://blog.csdn.net/weixin_41107009/article/details/103237471

版权

面试疑难专栏收录该内容

10 篇文章 0 订阅

订阅专栏

mysql的读写分离及集群

mybatis的架构和两级缓存

	将数据存放到程序内存中，用于减轻数据查询的压力，提升读取数据的速度，提高性能
	一级缓存
	两个级别
			SqlSession级别的缓存，实现在同一个会话中数据的共享
			Statement级别的缓存，可以理解为缓存只对当前执行的这一个Statement有效，执行完后就会被清空

mybatis的缓存

mybatis的缓存机制

一级缓存只在数据库会话中共享，会出现脏数据.

超发现象分析

	由于并发引起的超发现象
	如果一个数据库事务读取到产品后，就将数据直接锁定，不允许别的线程进行读写操作，直到当前数据库事务读取产品后，
	就将数据直接锁定，不允许别的线程进行读写操作  悲观锁 也就是写锁
	乐观锁 也就是不运用锁进行控制
	这种运用在数值价值高的地方
	而数据价值不高的一般不考虑并发

mysql索引

索引的意义和是什么

	如果不建立索引，mysql查询某一行数据需要从第一条记录开始读取，读取完整个表后才找出相关记录。
	如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查询整个文件，那么将会节约一部分的时间
	MySQL存储索引的类型有两种:BTree,Hash

MySQL中索引的优缺点和使用原则

	优点：
	1. 所有的MySQL列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引
	2. 大大加快数据的查询速度
	缺点：
	1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
	2. 索引也需要占空间，我们知道数据库中的数据也会有最大上线设置的，如果我们有
	大量的索引，索引文件可能会比数据文件更快达到上线值
	3. 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护
	速度。
	使用原则：
	1. 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引
	2. 数据量小的表最好不要使用索引，因为由于数据较小，可能查询全部数据花费的时间比
	遍历索引的时间还要短，索引就可能不会产生优化效果。
	3. 在一同值少的列上(字段上)不要建立索引，比如在学生表的“性别”字段上只有男，女
	两个不同值。相反的，在一个字段上不同值较多可以建立索引。

索引理解

一个表中能够创建多个索引，这些索引会被存放到一个索引文件
存储引擎和索引原理

索引的分类

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

普通索引

最基本的索引，没有任何限制，用于加快查询，允许在定义索引的列中插入重复值和空值
CREATE TABLE mytable(name VARCHAR(32),INDEX index_mytable_name(name));
CREATE INDEX index_mytable_name ON mytable(name);
ALTER TABLE mytable ADD INDEX index_mytable_name(name);

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
CREATE TABLE mytable(name VARCHAR(32),UNIQUE index_mytable_name(name));
CREATE UNIQUE INDEX index_mytable_name ON mytable(name);
ALTER TABLE mytable ADD UNIQUE INDEX index_mytable_name(name);

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候
同时创建主键索引
CREATE TABLE mytable(id INT(11) NOT NULL auto_increment,name VARCHAR(32),PRIMARY KEY(‘id’))
#添加主键约束
ALTER TABLE test.mytable ADD CONSTRAINT t1_pk PRIMARY KEY(id);

组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，
索引才会被使用，使用组合索引时遵循最左前缀集合。
组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀，不明白没关系，
举几个例子就明白了，例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，
索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，
那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询
索引讲解贴

mysql的ACID与脏读、幻读、不可重复读等问题

	ACID 只有InnoDB才有事务概念，所以只有InnoDB才会有事务引发的一系列问题
	这四个特性除了隔离性都好理解，在多个事务同时操作数据的情况下，会引发丢失更新的场景，出现多个事务同时
	访问商品库存，会发生丢失更新。
	
	mysql默认的隔离级别是RR(可重复读) 存储引擎是Innodb,可避免一切由事务引起的问题。
	
	事务的隔离性是由锁来实现的，那么MyISAM也可以由表锁来处理一些并发问题，只是效率低下，Innodb利用MVCC
	处理并发问题，解决事务问题，其主要思想是减少锁的运用。
	
	innodb采用了“一致性非锁定读”的机制（MVCC）提高了数据库并发性。一致性非锁定读表示在如果
	当前行被施加了排它锁，那么当需要读取行数据时，则不会等待行上的锁被释放，而是读取一个快照
	数据。(由undo来实现，原子性与一致性)
	RR级别的幻读出现几率用MVCC的话，几率会下降，但还是会出现，update有可能更新到隐藏的行
	
	为什么读未提交的并发性最好？事务更新后，表不加锁，表锁与行锁少，并发性自然就高了

可重复读通过MVCC实现(Innodb)
讲解贴

	mysql> show processlist;
	+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
	| Id | User            | Host            | db   | Command | Time   | State                  | Info             |
	+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
	|  4 | event_scheduler | localhost       | NULL | Daemon  | 439748 | Waiting on empty queue | NULL             |
	| 15 | root            | localhost:55337 | NULL | Query   |      0 | starting               | show processlist |
	| 16 | root            | localhost:55360 | NULL | Sleep   |    539 |                        | NULL             |
	+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
	3 rows in set (0.00 sec)
	
	mysql> show processlist;
	+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
	| Id | User            | Host            | db   | Command | Time   | State                  | Info             |
	+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
	|  4 | event_scheduler | localhost       | NULL | Daemon  | 439761 | Waiting on empty queue | NULL             |
	| 15 | root            | localhost:55337 | NULL | Query   |      0 | starting               | show processlist |
	| 16 | root            | localhost:55360 | NULL | Sleep   |    552 |                        | NULL             |
	+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
	3 rows in set (0.00 sec)
	
	mysql> use test;
	Database changed
	mysql> create table test integer(num);
	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'integer(num)' at line 1
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  100 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> start transaction;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> update name set name=name-1;
	ERROR 1146 (42S02): Table 'test.name' doesn't exist
	mysql> update num set num=num-1;
	ERROR 1146 (42S02): Table 'test.num' doesn't exist
	mysql> update test set num=num-1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> show varilables like 'tx_isolation';
	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varilables like 'tx_isolation'' at line 1
	mysql> show varilables like 'tx_isolation'；
	    -> ;
	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varilables like 'tx_isolation'；' at line 1
	mysql> show varilables like 'tx_isolation';
	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varilables like 'tx_isolation'' at line 1
	mysql> show varilables like 'isolation';
	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varilables like 'isolation'' at line 1
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> update test set num=num+1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> update test set num=num+1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  101 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> update test set num=num-1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  100 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> commit;commit;
	Query OK, 0 rows affected (0.00 sec)
	
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> set @@session.tx_isolation='SERIALIZABLE';
	ERROR 1193 (HY000): Unknown system variable 'tx_isolation'
	mysql> show variables like 'transaction_isolation';
	+-----------------------+-----------------+
	| Variable_name         | Value           |
	+-----------------------+-----------------+
	| transaction_isolation | REPEATABLE-READ |
	+-----------------------+-----------------+
	1 row in set, 1 warning (0.00 sec)
	
	mysql> set @@session.transaction_isolation='SERIALIZABLE';
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> show variables like 'transaction_isolation';
	+-----------------------+--------------+
	| Variable_name         | Value        |
	+-----------------------+--------------+
	| transaction_isolation | SERIALIZABLE |
	+-----------------------+--------------+
	1 row in set, 1 warning (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  101 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> update test set num=num-1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> update test set num=num-1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> set session transaction isolation level read committed;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> show variables like 'transaction_isolation';
	+-----------------------+----------------+
	| Variable_name         | Value          |
	+-----------------------+----------------+
	| transaction_isolation | READ-COMMITTED |
	+-----------------------+----------------+
	1 row in set, 1 warning (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> update test set num=num-1;
	Query OK, 1 row affected (43.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql>
	mysql>
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   98 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   98 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> update test set num=num-1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   97 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.01 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   97 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> update test set num=num-1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   96 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.01 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> set session transaction isolation level read uncommitted;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> show variables like 'transaction_isolation';
	+-----------------------+------------------+
	| Variable_name         | Value            |
	+-----------------------+------------------+
	| transaction_isolation | READ-UNCOMMITTED |
	+-----------------------+------------------+
	1 row in set, 1 warning (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   96 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> update test set num=num-1;
	Query OK, 1 row affected (0.00 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> robback;
	ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'robback' at line 1
	mysql> rollback;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql>
	
	
	mysql> use test;
	Database changed
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  100 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> start transaction;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  100 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  100 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql>
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> update test set num=num+1;
	Query OK, 1 row affected (16.32 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  101 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql>  show variables like 'transaction_isolation';
	+-----------------------+-----------------+
	| Variable_name         | Value           |
	+-----------------------+-----------------+
	| transaction_isolation | REPEATABLE-READ |
	+-----------------------+-----------------+
	1 row in set, 1 warning (0.00 sec)
	
	mysql> set @@session.transaction_isolation='SERIALIZABLE';
	Query OK, 0 rows affected (0.00 sec)
	
	mysql>  show variables like 'transaction_isolation';
	+-----------------------+--------------+
	| Variable_name         | Value        |
	+-----------------------+--------------+
	| transaction_isolation | SERIALIZABLE |
	+-----------------------+--------------+
	1 row in set, 1 warning (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|  101 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (33.27 sec)
	
	mysql> set session transaction isolation level read committed;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql>  show variables like 'transaction_isolation';
	+-----------------------+----------------+
	| Variable_name         | Value          |
	+-----------------------+----------------+
	| transaction_isolation | READ-COMMITTED |
	+-----------------------+----------------+
	1 row in set, 1 warning (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   99 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   98 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   98 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   98 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   97 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   97 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> select * from test;
	+------+
	| num  |
	+------+
	|   96 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql>  set session transaction isolation level read uncommitted;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql>  show variables like 'transaction_isolation';
	+-----------------------+------------------+
	| Variable_name         | Value            |
	+-----------------------+------------------+
	| transaction_isolation | READ-UNCOMMITTED |
	+-----------------------+------------------+
	1 row in set, 1 warning (0.00 sec)
	
	mysql>  select * from test;
	+------+
	| num  |
	+------+
	|   96 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql>  select * from test;
	+------+
	| num  |
	+------+
	|   95 |
	+------+
	1 row in set (0.00 sec)
	
	mysql>  select * from test;
	+------+
	| num  |
	+------+
	|   96 |
	+------+
	1 row in set (0.00 sec)
	
	mysql> commit;
	Query OK, 0 rows affected (0.00 sec)
	
	mysql>

聚集索引和外键约束

聚簇索引与非聚簇索引

详解

mysql锁

	数据库只能并行读，和只能串行写
	S 读  共享
	X 写  排它
锁的粒度：
	表锁 页锁 行锁
	不同的存储引擎所支持的锁定粒度是不同的
	InnoDB 表 行
	MyISAM 表
	BDB 表 页
	表级锁一次将整个表锁定，所以很好的避免困扰我们的死锁问题。锁定颗粒度大所带来最大的负面影响
	就是出现资源争用的概率也会很高，致使并发力度大打折扣。使用表级锁定的主要是MyISAM，
语法：
	# 获取表锁
	lock TABLES 
	tb1_name AS me lock_type
	[,tb1_name AS me lock_type] 
	....
	LOCK type:  READ | [LOW_PRIORITY]WRITE
	# 释放表锁
	UNLOCK TABLES
MyISAM在执行查询前，会自动执行表的加锁、解锁操作，一般情况下不需要用户手动加、解锁，但是有的时候也需要显示加锁。比如：
检索某一时刻t1,t2表中数据数量。
	LOCK TABLE t1 read, t2 read;
	SELECT COUNT(t1.id1) AS 'sum' FROM t1;
	SELECT COUNT(t2.id1) AS 'sum' FROM t2;
	UNLOCK TABLES;

页锁

在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减少，锁定相同数据量的数据所需要消耗的内存数量越来越多的，实现算法也会
越来越复杂。不过，随着锁定资源颗粒度的减少，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发读也会随之提升。

使用页级锁定的主要是BerkeleyDB存储引擎。

行锁

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

使用行级锁定的主要是InnoDB存储引擎

总结

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，
如一些在线事务处理（OLTP）系统。
InnoDB是聚簇索引（叶子节点存数据），MyISAM是非聚簇索引（叶子节点存指针）

InnoDB中的锁

意向锁

InnoDB支持多种粒度锁，也就是行锁和表锁。为了支持多粒度锁定，InnoDB存储引擎引入了意向锁(Intention Lock)

如果没有意向锁，当已经有人使用行级锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么需要对所
有的行是否被锁定进行扫描，在这种情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行进行
修改之前，会先为表添加意向互斥锁(IX)，再为行记录添加互斥锁(X)，在这个时候如果有人尝试对全表进行修改就不需要判断表中
的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

	意向共享锁(IS)：事务想要在获得表中某些记录的共享锁，需要在表中先加意向共享锁
	意向互斥锁(IX)：事务想要在获得表中某些记录的互斥锁，需要在表中先加意向互斥锁
意向锁其实不会阻塞全表扫描外的任何请求，他们的主要目的是为了表示是否有人请求锁定表
中的某一行数据。

参考贴

算法

InnoDB存储引擎有3种行锁的算法

Record Lock：单个行记录锁
Gap Lock:间隙锁，锁定一个范围，但不包含记录本身

Next-Key Lock：Gap Lock+Record Lock,锁定一个范围，并且锁定记录本身
Innodb下的可重复读级别解决了所有问题：胀读、不可重复读、幻读
解决这些问题之前，我们要首先知道Redo log、Undo log以及MVCC都是什么
redo log(重做日志)用来实现事务的持久性，即事务ACID中的D。其由两部分组成，一是内存中的重做日志缓冲(redo log buffer),
其实易失的。二是重做日志文件(redo log file),其是持久的。

  在一个事务中的每一次SQL操作之后都会写入一个redo log到buffer,在最后commit的时候，必须先将该事务的所有日志写到redo log file进行
  持久化(这里的写入是顺序写的)，待事务的commit操作完成才算完成。
  
  由于重做日志文件打开没有使用O_DIRECT选项，因此重做日志缓冲先写入文件系统缓存。为了确保重做日志写入磁盘，必须进行一次fsync操作。由fsync的
  效率取决于磁盘性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。由此我们可以得出批量操作的时候，不要for循环里面嵌套事务。
  
  参数 innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略，该参数有3个值：0、1和2。
  0：表示事务提交时不进行写redo log file的操作，这个操作仅在master thread中完成（master thread每隔1秒进行一次fsync操作）。
  1：默认值，表示每次事务提交时进行写redo log file的操作。
  2：表示事务提交时将redo log写入文件，不过仅写入文件系统的缓存中，不进行fsync操作。
  
  我们可以看到0和2的设置都比1的效率要高，但是破坏了数据库的ACID特性，不建议使用！
  
  对比binlog
  1. redo log是在MySQL的InnoDB引擎层产生，binlog则是在MySQL的上层产生，它不仅针对InnoDB引擎，其他任何引擎对于数据库的更改都会
  产生binlog
  2. binlog是一种逻辑日志，其记录的是对应的SQL语句。而redo log则是记录的物理格式日志，其记录的是对于每个页的修改。
  3. binlog只在事务提交完成后一次性写入，而redo log在上面页说了是在事务进行不断被写入，这表现为日志并不是随事务提交而顺序提交。
  
  保证持久性，redo log redo log files  redo log block checkpooint  故障修复 保证数据库持久性，中间的commit级别会破坏持久性
  
  undo log是InnoDB MVCC事务特性的重要组成成分。当我们对记录做了变更操作就会产生Undo记录，Undo记录默认被记录到系统表空间(ibbata)
  中。  保证原子性
  
  Rollback Segment回滚段
  
  多版本控制MVCC:用于多事务环境下，对数据读写在不加读写锁的情况下实现互不干扰，从而实现数据库的隔离性，在事务隔离级别为Read Commit和
  Repeatable Read中使用到
  
  RC和RR隔离级别ReadView的实现方式
  
  我们知道，RC隔离级别是能看到其他事务提交后的修改记录的，也就是不可重复读，但是RR隔离级别完美的避免了，但是他们都是使用的MVCC机制，那又为何
  有两种截然不同的结果
  
  在RC事务隔离级别下，每次语句执行都关闭ReadView，然后重新创建一份ReadView
  在RR下，事务开始后第一个读操作创建ReadView,一直到事务结束。
  
  和我之前理解的很相同，也就是读取的版本号不同，造成MVCC在两个不同的隔离级别下表现不同
  也就是MVCC的不同操作造成了不同的隔离级别
  A undo D redo 
  在多个事务并行进行的情况下，即使保证了每一个事务的原子性，仍然可能导致数据不一致的结果，比如丢失更新
  为了保证并发情况下的一致性，引入了隔离性，即保证每一个事务能够看到的数据总是一致的，就好像其他并发事务并不存在一样。用术语来说，就是多个
  事务并发执行后的状态，和它们串行执行后的状态是等价的。
  为什么RU级别会发生脏读，而其他的隔离级别能够避免？
  RU级别的操作其实就是对事务内部的每一条更新语句对应的记录加上读写锁操作，而不把一个事务当成一个整体来加锁，所以会导致脏读。但是RC和RR能够
  通过MVCC来保证记录只有在最后commit后才会让别的事务看到。
  为什么RC级别不能重复读，而RR级别能够避免？
  这个在上面的MVCC的最后说到了，在RC事务隔离级别下，每次语句执行都关闭ReadView,然后重新创建一份ReadView。而在RR下，事务开始后第一次读操作
  创建ReadView,一直到事务结束关闭。
  为什么InnoDB的RR级别能够防止幻读
  这是因为RR隔离级别使用了Next-key Lock，也就是Gap Lock+Record Lock的方式进行间隙锁定。
  
  一致性锁定读
  前面说到，在默认隔离级别RR下，InnoDB存储引擎的select操作使用一致性非锁定读。但是在某些情况下，用户需要显示地对数据库读取操作进行加锁以保证
  数据逻辑的一致性。InnoDB存储引擎对于select语句支持两种一致性的锁定读(locking read)操作
  * select ...for update(x 锁)
  * select .. lock in share mode(s 锁)
  不同隔离基别的运用场景，但目前对于数据库的发展来说，mysql一般默认级别为innodb下的RR，不破坏事务ACID，而且避免脏读、不可重复读、幻读问题，
  并发性也得到了保障，而数据要求低的可以用redis或mongodb实现，NoSQL并发时遵循BASE(基本可以，软状态，最终一致性)
  
  通过锁定机制可以实现事务隔离性要求，使事务可以并发的工作。锁提高了并发，但是却带来潜在的问题。不过好在有事务隔离性的要求，不同的隔离级别解决
  的锁的问题也不同。

锁带来的问题

	更新丢失的解决方法目的：1. 取消第二次更新（悲观锁、乐观锁）2. 从根本上拒绝第二次更新

超发现象更新丢失问题也可以使用隔离级别最高的serializable
为了克服超发现象，当前企业级的开发提出了乐观锁、悲观锁和使用Redis等多种方案

使用悲观锁 for update（也称为独占锁或排它锁）
悲观锁会造成性能的降低
乐观锁
乐观锁是一种不使用数据库锁和不阻塞线程并发方案(也称非阻塞锁或非独占锁)，可重入锁
CAS(Compare and Swap) 出现A->B->A问题

增加版本号（version）并且规定：只要操作过程中修改共享值，无论是业务正常、回退还是异常，版本号只能递增，不能递减。
例子如：

update … set … ,veision = version +1 where id=#{id} and version= #{version}

加入版本号的判断后，失败率高

重入会给数据库带来很大的压力。

使用限制时间或者重入次数压制过多的SQL被执行

将一个请求限制100ms的生存期弊端：系统因为自身的忙碌而大大减少重入的次数。

按重入次数的机制请求失败的次数也会大大降低

总结：乐观锁是一种不使用数据库锁的机制，并且不会造成线程的阻塞，只是采用多版本号机制来实现。但是，因为版本的冲突造成了请求失败的概率剧增，
这时往往需要通过重入的机制将请求失败的概率降低。但是，多次的重入会带来过多执行SQL的问题。为了克服这个问题，可以考虑按用时间戳或限制重入次数的办法。
可见，乐观锁还是一种比较复杂的机制。目前，使用NoSQL来处理这方面的问题，其中当属Redis解决方案。
##InnoDB存储引擎的行结构
InnoDB表数据的组织方式为主键聚簇索引，二级索引中采用的是(索引键值，主键键值)的组合来唯一确定一条记录
主键聚簇索引和二级索引二级索引也能确定主键

	InnoDB表数据为主键聚簇索引,mysql默认为每个索引行添加了4个隐藏的字段,分别是：
	DB_ROW_ID：InnoDB引擎中一个表只能有一个主键,用于聚簇索引,如果表没有定义主键会选择第一个非Null的唯一索引作为主键,如果还没有,生成一个隐藏的DB_ROW_ID作为主键构造聚簇索引。
	DB_TRX_ID：最近更改该行数据的事务ID。
	DB_ROLL_PTR：undo log的指针,用于记录之前历史数据在undo log中的位置。(version)
	DELETE BIT：索引删除标志,如果DB删除了一条数据,是优先通知索引将该标志位设置为1,然后通过(purge)清除线程去异步删除真实的数据。
	删除<版本号 才能读取 增改 增加版本号 删除，增加删除版本号  不要锁机制实现避免幻读，可重复读，避免脏读

mysql死锁问题分析

	我们mysql用的存储引擎是innodb，从日志看，innodb主动探知到死锁，并回滚了某一苦苦等待的事务。
	1. 不同表相同行冲突
	2. 相同表记录行锁冲突
	3. 不同索引锁冲突
	4. gap锁冲突
	间隙锁容易造成死锁现象，少用普通索引多用主键索引

innodb是如何探知死锁的

	直观方法是两个事务相互等待时，当一个等待时间超过设置的某一阈值，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法
	简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间
	innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。
	
	innodb将各个事务看为一个个节点，资源就是各个事务占用的锁，当事务1需要等待事务2的锁时，就生成一条有向边从1指向2，最后形成一个有向图。
	出现环路就是死锁，这就是wait-for graph算法
	死锁检测是死锁发生时innodb给我们的救命稻草，我们需要它，当我们更需要的是避免死锁发生的能力。如何避免？这需要了解innodb中的锁。
	
	
	innodb存储引擎下：不同的索引下的不同隔离级别决定加什么样的锁，锁是自动加的，但我们实现不同的功能时，需要想到用什么样的锁，然后用什么样的存储
	引擎下的什么索引加上隔离级别实现。 读锁 写锁   gap锁

参考文章

锁与索引的关系

innodb对于主键使用了聚簇索引，这是一种数据存储方式，表数据是和主键一起存储的，主键索引的叶节点存储数据。
对于普通索引，其叶节点存储的是主键值
通过隔离级别和索引条件判断操作应该加什么锁

大神贴，完全理解索引隔离级别锁之间的关系

行锁的算法

大神贴

Mysql的事务

MVCC涉及的事务和事务的基本原理参考贴

多版本并发控制MVCC
MySQL InnoDB存储引擎，实现的是基于多版本的并发控制–MVCC
与MVCC相对的，是基于锁的并发控制，Locke-Based Concurrency Control.MVCC最大的好处：读不加锁，读写不冲突,在读多写少的OLTP应用中，
读写不冲突是非常重要的，极大的增加了系统的并发性能

存储引擎

InnoDB在每行数据都增加两个隐形字段，一个记录创建的版本号，一个记录删除的版本号

Select

当隔离级别是repeatable read时select操作，InnoDB必须每行数据来保证它两个条件：

	1. InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。
	这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。
	2. 这行数据的删除版本必须是未定义的或者比事务版本大。这可以保证在事务开始之前这行数据没有被删除。
	符合这两个条件的行可能会被当作查询结果而返回。
	insert:InnoDB为这个新行记录当前的系统版本号。
	delete:InnoDB将当前的系统版本号设置为这一行的删除ID
	update:InnoDB会写一个这行数据的新拷贝，这个拷贝的版本为当前的系统版本号。同时它将这个版本号写到旧行的删除版本里。
	这个额外的记录所带来的结果就是对于大多数查询来说根本就不需要获得一个锁。他们只是简单地以最快的速度来读取数据，确保
	只选择符合条件的行。这个方案的缺点在于存储引擎必须为每一行存储更多的数据，做更多的检查工作，处理更多的善后操作。
	MVCC只工作在repeatable read和read commited隔离级别下。read uncommited不是MVCC兼容的，因为查询不到找到适合
	他们事务版本的行版本；他们每次都只能读到最新的版本。seriablable也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据。

事务

事务是由一组原子性sql查询语句，被当作一个工作单元。若mysql对该事务单元内所有sql语句都可以正常的执行完，则事务操作视为成功，所有的sql
语句才对数据生效，若sql中任意不能执行或出错则事务操作失败，所有对数据的操作则无效(通过回滚恢复数据)。

事务的四个属性

	1. 原子性：事务被认为不可分的一个工作单元，要么全部正常执行，要么全部不执行。
	2. 一致性：事务操作对数据库总是从一种一致性的状态转换到另外一种一致性状态。
	3. 隔离性：一个事务的操作结果在内部一致，可见，而对除自己以外的事务是不可见的。
	4. 永久性：事务在未提交前数据一般情况下可以回滚恢复数据，一旦提交数据的改变则变成永久
	mysam引擎的数据库不支持事务，所以事务最好不要对混合引擎操作(innodb、myisam)，若能正常运行时最好的，
	否则，事务中对非支持事务表的操作是不可能回滚恢复的。
	
	一般情况下，事务性存储引擎不只是使用表锁，行加锁的处理数据，而是结合了MVCC机制，以处理更多的并发问题。mvcc处理高并发
	能力最强，但系统开销比最大(较表锁、行级锁)。
	autocommit：mysql一个系统变量，默认情况下autocommit=1表示mysql把每一条sql语句自动的提交，而要开启事务操作时，要把autocommit设为0

Innodb与MyISAM的区别

MyISAM
MyISAM死锁分析：
由于互相给x锁定的表加x锁，出现相互等待，所以出现死锁现象，不过一般自动加锁状态下的MyISAM不会发生死锁。
MyISAM不支持事务

IO大仙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
遗留mysql数据库问题

mysql的读写分离及集群mybatis的架构和两级缓存将数据存放到程序内存中，用于减轻数据查询的压力，提升读取数据的速度，提高性能一级缓存两个级别 SqlSession级别的缓存，实现在同一个会话中数据的共享 Statement级别的缓存，可以理解为缓存只对当前执行的这一个Statement有效，执行完后就会被清空mybatis的缓存mybatis的缓存机制一级...
复制链接

扫一扫