MYSQL的高级深入学习的记录

1.MYSQL的工作流程

2.存储引擎

//查看mysql支持的存储引擎
SHOW ENGINES

//查看当前mysql使用的存储引擎
show VARIABLES LIKE '%storage%'

3.常用的存储引擎

1) MyISAM

2) InnoDB

3) Memory

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行锁/表锁	表锁，即操作一条记录是会锁住整个表，不适合高并发	行锁，即操作一条记录是会锁住操作的那行数据，适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还缓存真实数据，对内存要求高

4.索引

索引是帮助MYSQL高效获取数据的数据结构,数据快速查找和排序

一般一条查询SQL包含一个索引作为查询或排序条件，因为与其说是数据库只支持一条查询语句只使用一个索引，倒不如说N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。

$注意：$

$1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够$
$2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行$

4.1）创建索引

CREATE INDEX index_name ON my_table(column_name)

4.2）查看索引

SHOW INDEX FROM tablename

4.3）查看查询语句使用索引的情况

//explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='lzj';

4.4）索引分类

索引名字	限制	例子
普通索引	没有任何限制	ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col’)
主键索引	不允许重复，不允许空值	ALTER TABLE ‘table_name’ ADD PRIMARY KEY pk_index(‘col’)
唯一索引	唯一的，允许空值	ALTER TABLE ‘table_name’ ADD UNIQUE index_name(‘col’)
复合索引	没有限制，一个索引包含多列	ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’)；
全文索引	没有任何限制,对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，	ALTER TABLE ‘table_name’ ADD FULLTEXT INDEX ft_index(‘col’)；

这里可能大家会听说覆盖索引，它不是索引类型，它指的是解决SQL索引失效的一种解决方案，详细看我这篇博客https://editor.csdn.net/md?articleId=108044084的案例七

4.5）索引类型

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如$BTree索引，B+Tree索引，哈希索引，$全文索引等等

5.事务

事务是由一组SQL语句组成的逻辑处理单元

MYSQL事务处理主要有两种方法：

方法一

BEGIN; 或者 START TRANSACTION;
事务操作。。。。
ROLLBACK; #事务回滚
COMMIT; #事务提交

方法二

SET autocommit = 0;#手动把自动提交关闭了
事务操作。。。。
ROLLBACK; #事务回滚
COMMIT; #事务提交
SET autocommit = 1;#手动把自动提交打开了

5.1 事务的特性 （ACID）

特性	描述
原子性（Atomicity）	原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性（Consistency）	一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。
隔离性（Isolation）	隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。
持久性（Durability）	持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。

5.2 并发事务处理的问题

为什么有事务隔离级别，因为当两个以上事务并发执行，如果不相互隔离，就会出现以下问题

1）更新丢失（Lost update）

解决方法：开启事务隔离级别 ，使两个

更新丢失：当两个事务并发执行，A事务如果撤销或者执行成功，会覆盖B事务的修改的值。（例子如下）

第1类更新丢失：事务A撤销时，把已经提交的事务B的更新数据覆盖了。

第2类丢失更新：事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失。

2）脏读（Dirty Reads）

解决方法：对行加锁，只允许并发一个更新事务。

A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。

3）不可重复读（Non-repeatable Reads）

解决办法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可以避免该问题。

一个事务对同一行数据重复读取两次，但是却得到了不同的结果。事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。

4）幻读 （Phantom Reads）

指两次执行同一条 select 语句会出现不同的结果，第二次读会增加一数据行，并没有说这两次执行是在同一个事务中。一般情况下，幻象读应该正是我们所需要的。但有时候却不是，如果打开的游标，在对游标进行操作时，并不希望新增的记录加到游标命中的数据集中来。隔离级别为 游标稳定性 的，可以阻止幻象读。例如：目前工资为1000的员工有10人。那么事务1中读取所有工资为1000的员工，得到了10条记录；这时事务2向员工表插入了一条员工记录，工资也为1000；那么事务1再次读取所有工资为1000的员工共读取到了11条记录。

5.3 事务的隔离级别

为了解决并发事务的问题，就有了事务隔离级别

Read uncommitted(未授权读取、读未提交) < Read committed（授权读取、读提交）< Repeatable read（可重复读取）< Serializable（序列化）

隔离级别	描述
Read uncommitted(未授权读取、读未提交)	如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。
Read committed（授权读取、读提交	读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
Repeatable read（可重复读取）	可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。这样避免了不可重复读取和脏读，但是有时可能出现幻象读。（读取数据的事务）这可以通过“共享读锁”和“排他写锁”实现。
Serializable（序列化）	提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

MYSQL默认事务隔离级别为 Repeatable read重复读

SHOW VARIABLES like 'tx%'

一键查询淘宝/拼多多内部优惠券，每日大额外卖红包，购物省钱的宝藏工具