数据库基本认识

桃扇骨 2022-05-26 02:41 218阅读 0赞

# 1. 乐观锁和悲观锁 #

为什么需要使用锁机制：在多用户操作数据库的时候（Java的多线程），有可能发送数据增删查改的冲突，例如某一时刻，同时两个用户操作对数据库的某一个变量num + 1。两个用户同时读取num = 1，然后用户A进行了num + 1，用户B也进行了num + 1，此时的num想要的结果应该为num = 3，但是num的结果却是2。  
以MySQL更新操作为例：  
A：update test set num = num + 1 where name = ‘wsk’;  
B：update test set num = num + 1 where name = ‘wsk’;  
在并发的情况下，数据库同时执行这2条更新语句，就有可能发生。  
这就导致了数据修改不一致而发生了冲突-更新丢失。  
还有一种情况，用户A执行更新操作，用户B执行读取操作，当用户A在更新数据的时候，将num更新为2，在这个更新的过程中，用户B读取数据，读取到了num = 1，而此时的num结果应该为2，所以B读取到的数据不正确，这就导致了脏读。  
还是以MySQL为例子：  
A：update test set num = num + 1 where name = ‘wsk’;  
B：select num from test where name = ‘wsk’;  
为了解决这些问题，所以加入了锁机制。

#### 锁机制 ####

**乐观锁**：顾名思义，就是比较乐观的想法，假设在操作的时候，并不会发生数据的冲突，只有在提交操作时检查是否违反数据的完整性。乐观锁并不能解决脏读的问题。还是上面的例子，在A更新的过程中，将num更新为2后，又更新为1，此时B读取到的数据还是1，判断num的值并没有被改变过，所以发生了脏读的可以。 (CAS和ABA问题)  
A：update test set num = num + 1 where name = ‘wsk’; update test set num = num - 1 where name = ‘wsk’;  
B：select num from test where name = ‘wsk’;  
实现：基于数据版本（Version）记录机制实现-为表增加一个版本号或者时间戳。当读取数据的时候，将版本号一同读取出来，然后在更新的时候，将版本号+1。当提交更新操作的时候，判断版本号是否一致，如果一致，则更新，如果不一致，则拒绝更新。  
A：set @version = (select version from test where name = ‘wsk’);  
update test set num = num + 1，version = version + 1 where name = ‘wsk’ and version = @version;  
通过版本号进行判断  
B：set @version = (select version from test where name = ‘wsk’);  
update test set num = num + 1，version = version + 1 where name = ‘wsk’ and version = @version;  
如果A操作和B操作都已经读取到了version为1，A操作的更新操作比B操作快一些，此时version已经被更新为2，此时进行B操作，由于version不一致，导致B操作的更新失败。

**悲观锁**：在操作的时候，假设可以会发生数据冲突，屏蔽其他的操作，只允许当前操作。  
实现：大多数情况下，一般是依靠数据库的锁机制实现的，即在该sql语句后加上“for update”字样。eg：select num from test where name = ‘wsk’ for update; 在执行该语句的时候，数据库自动为操作加锁，只有当该语句执行完成后才释放锁。

#### 结论 ####

乐观锁使用场景是写操作比较少和高并发的情况下，即冲突发生比较少和性能要求比较快，这样可以省去了锁的开销，加大了系统的吞吐量。如果系统的并发量不大，并且不允许出现脏读的情况，则可以考虑使用悲观锁。

# 2. InnoDB和MyISAM #

MySQL5.5以后，默认使用InnoDB。

### 区别： ###

来源：[https://blog.csdn.net/lc0817/article/details/52757194][https_blog.csdn.net_lc0817_article_details_52757194]

#### 存储结构： ####

MyISAM：每个MyISAM在磁盘上存储成3个文件，第一个文件名是以表的名字开始，扩展名支出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD(MYData)。索引文件的扩展名为.MYI(MYIndex)。  
InnoDB：所有的表都存储在同一个存储的数据文件中（可能是多个文件，有可能是独立的表空间文件），InnoDB的大小只受限与操作系统文件的大小，一般为2GB。

#### 存储空间 ####

MyISAM：可以被压缩，存储空间比较小。支持3种不同的存储格式：静态表（默认，但是数据末尾不能有空格，会被自动去掉），动态表，压缩表。  
InnoDB：需要更多的内存和存储，他会在主内存中建立专用的缓冲区用于高速缓冲数据和索引。

#### 可移植性，备份和恢复 ####

MyISAM：数据是以文件的形式存储的，所以在跨平台的数据转移中会比较方便。在备份和恢复的时候可以单独对某一个表进行操作。  
InnoDB：提供拷贝数据文件、备份binlog或者使用mysqldump。

#### 事务 ####

MyISAM：强调的是性能，每次查询具有原子性，所以执行速度比InnoDB更快，但是不支持事务。  
InnoDB：提供事务支持，外键等。

#### AUTO\_INCREMENT ####

MyISAM：可以和其他字段一起建立索引。索引的自增列必须是索引，如果是组合索引，自增列可以不用是第一列，可以根据前几列进行排序后递增。  
InnoDB：必须包含只有该字段的索引。引擎的自增列必须是索引，如果是组合索引也必须是组合索引的第一列。

#### 表锁差异 ####

MyISAM：支持表级锁，用户在操作MyISAM表时，select，update，delete，insert等操作的时候，会自动锁表。  
InnoDB：支持事务和行级锁（最大特色）。行锁大幅度提供了多用户并发的性能。但是该行锁，只有在where是主键的时候，才是有效的，非主键的where会锁全表。

#### 全文索引 ####

MyISAM：支持FULLTEXT类型的全文索引。  
InnoDB：不支持FULLTEXT类型的全文索引，但是可以使用sphinx插件支持全文索引，而且效率更快。

#### 表主键 ####

MyISAM：运行使用没有任何索引和主键的表存在，索引保存的都是保存行的地址。  
InnoDB：如果没有设置主键或者非空唯一索引，就会自动生成一个6字节的主键（不可见），数据是索引的一部分，附加索引保存的是主索引的值。

#### 表的具体行数 ####

MyISAM：保存有表的总行数，select count(\*) from table直接获得该值。  
InnoDB：没有保存，使用上面的SQL会直接遍历整个表，消耗大，但是如果使用where条件后，MyISAM和InnoDB处理方式是一样的。

#### CURD操作 ####

MyISAM：如果执行大量的select操作，MyISAM效果更好。删除数据表的时候，直接drop表，然后create新表。  
InnoDB：如果需要执行大量的insert和update，出于性能的考虑，应该使用InnoDB。delete从性能上来说，InnoDB会更好，但delete from table时，InnoDB不会重新建表，而是一行一行的删除，在数据量比较打的时候，会很慢。所以，最好使用truncate table命令。

#### 外键 ####

MyISAM：不支持外键。  
InnoDB：支持

# 3. 数据库优化 #

explain  
![这里写图片描述][70]  
列解释：  
select\_type：select查询的类型，主要是区别普通查询、联合查询和子查询等复杂查询  
table：输出的行所引用的表  
type：联合查询所使用的类型（这是一个比较重要的指标。）性能依次为：system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL。一般来说，查询至少达到range级别，最好是达到ref级别。  
possible\_keys：MySQL是使用哪些索引。如果为空，则表示没有使用索引，这个时候可以通过检查where条件或者建立合适的索引。  
key：显示MySQL实际使用的键。如果没有索引被选择，键是null。  
key\_len：显示使用的键的长度。如果键是null，那么长度也是null。  
ref：表示哪个字段或者常数和key一起被使用。  
rows：表示需要遍历多少数据才能找到，在InnoDB上是不正确的。  
Extra：如果是only index，表示只用索引树检索信息；如果是where used，表示使用了where条件；如果是impossible where表示用不着where，一般是查不出来的；using filesort或者using temporary的话，where和order by  
的索引经常无法一起使用，如果使用where索引，那么在order by的时候，会引起using filesort。这就需要比较先过滤再排序或者是先排序再过滤。  
常见名词：  
using filesort：需要额外的一次传递，以找出使用合适的顺序检索。  
using index：从只使用索引树中的信息之间检索相应的信息。  
using temporary：为了解决查询，需要创建一个临时表来容纳结果。  
ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。  
all：全表扫描，没有使用索引。性能差。  
index：与all类似，扫描索引树。但是比all快，因为索引文件比数据文件小。  
simple：简单的查询，不需要使用union或者子查询。

# 4. 索引 #

# 5. 事务 #

# 6. 表锁，行锁 #

[https_blog.csdn.net_lc0817_article_details_52757194]: https://blog.csdn.net/lc0817/article/details/52757194
[70]: /images/20220526/473d123230b14a4aa2f81cb7b6c63cf0.png