Mysql必知必会

Mysql

事务

特征：

1. 原子性：不可分割的操作单元，事务中所有操作，要么全部成功；要么撤回到执行事务之前的状态
2. 一致性：如果在执行事务之前数据库是一致的，那么在执行事务之后数据库也还是一致的；
3. 隔离性：事务操作之间彼此独立和透明互不影响。事务独立运行。这通常使用锁来实现。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4. 持久性：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。

存储引擎 MyISAM和InnoDB区别：

1. MyISAM不支持外键和事务，innodb支持

2. MyISAM锁的粒度是表级，而InnoDB支持行级锁定

3. MyISAM支持全文类型索引，而InnoDB不支持全文索引。

4. MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM

5. Myisam创建表生成三个文件：.frm数据表结构 .myd数据文件 .myi索引文件，Innodb只生成一个frm文件，数据存放在ibdata1.log

6. MyisAM使用delete语句删除后并不会立刻清理磁盘空间，需要定时清理，命令：OPTIMIZE table dept;

   ---

   应用场景：

   • MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
   • InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

delete drop truncate区别

• truncate 和 delete只删除数据，不删除表结构 ,drop删除表结构，并且释放所占的空间。
• 删除数据的速度，一般来说: drop> truncate > delete
• delete属于DML语言，需要事务管理，commit之后才能生效。drop和truncate属于DDL语言，操作立刻生效，不可回滚
• 使用场合：
  • 当你不再需要该表时， 用 drop;
  • 当你仍要保留该表，但要删除所有记录时， 用 truncate;
  • 当你要删除部分记录时（always with a where clause), 用 delete.

注意： 对于有主外键关系的表，不能使用truncate而应该使用不带where子句的delete语句，由于truncate不记录在日志中，不能够**触发器

索引

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

img

InnoDB也使用B+Tree作为索引结构。InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址（这一点可以通过在data目录下查看数据库文件验证。Innodb每一个数据库只有一个数据文件，而Myisam则有三个（数据文件、索引文件、表结构文件））。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

img

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

img

基于B+树

B树的定义：有序数组+平衡多叉树

多叉排序树

通常会定义一个最小度：t>=2（等于1的时候退化成为二叉搜索树）：

• 每个节点最多含有（2t-1）个关键字，每个节点最多有2t的孩子节点。
• 当节点的关键字个数为2t-1的时候，称该节点已满，需要分裂。分裂从中间节点一分为二，中间结点被提升到父节点，如果父节点也满，则父节点接着分裂，直到根节点。
• 查找可以在非叶子节点完成，一个键值只会出现一次，而B+树的键一定出现在叶子节点，并且可能在非叶子节点重复出现，以维持平衡。

B+树是一种变种的B-树。有序数组链表+平衡多叉树

基本和B树类似，只有叶子节点存放数据，而且叶子节点之间通过指针相连。

B+-tree的磁盘读写代价更低：B+-tree的内部结点并没有指向关键字具体信息的指针。因此内部结点相对B 树更小

B+-tree的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

索引种类

1. 主键索引（把某列设为主键，则该列就是索引，主键不能重复）
2. 唯一索引（unique），该列具有唯一性，同时又是索引
3. index 普通索引
4. 全文索引 （fulltext） 只有MyISAM存储引擎支持 （注：mysql 5.6之后，Innodb也开始支持全文索引，mysql5.6较之前版本有较大更新，有兴趣的小伙伴可以去查一查）

适合加索引的列：

1. 经常查询的列上加索引

2. 唯一性太差的列不要加索引（重复太多），相反唯一性比较好的列适合加索引

3. 不经常修改的列适合加。经常修改的列不要加索引（列修改，索引也要改）

   因为索引的创建和更改是有开销的。

索引生效条件

假设index（a,b,c）

1. 最左前缀匹配：模糊查询时，使用%匹配时：’a%‘会使用索引，’%a‘不会使用索引
2. 条件中有or，索引不会生效
3. a and c，a生效，c不生效
4. b and c，都不生效
5. a and b > 5 and c,a和b生效，c不生效。

创建 删除 查看

1. 创建方法一：

   create [unique | fulltext] index 索引名 on 表名（列名）

2. 创建方法二：

普通索引：alter table 表名 add index 索引名 （列名）

主键索引：alter table 表名 add primary key (列名)

唯一索引：alter table 表明 add unique (列名)

​

1. 删除

   drop index 索引名 on 表名；
   

   alter table 表名 drop index 索引名
   

   alter table 表明 drop primary key
   

   ​

2. 查看

show index from 表名；

​

检测索引的效果

show status like '%handler_read%'越大越好

sql语句分类：

1. DDL：数据定义语言（create alter drop）
2. DML：数据操作语句（insert update delete）
   1. select 数据查询语句
3. DTL：数据事务语句（commit collback savapoint）
4. DCL：数据控制语句（grant revoke）

mysql优化：

1. 数据表的优化（符合3范式）
   1. 1范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
   2. 2范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
   3. 3范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。没有冗余的数据库设计可以做到
   4. 但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。
2. sql语句优化 索引
3. 使用order by null禁用排序。group by 默认情况下会排序（file sorting），非常费时。
4. 使用join代替子查询（子查询会默认创建临时表）
5. 分表：
   1. 垂直分割：将字段分开
   2. 水平分割：将记录分开
6. 读写分离

Innodb中的事务隔离级别和锁的关系

前言:

我们都知道事务的几种性质，数据库为了维护这些性质，尤其是一致性和隔离性，一般使用加锁这种方式。同时数据库又是个高并发的应用，同一时间会有大量的并发访问，如果加锁过度，会极大的降低并发处理能力。所以对于加锁的处理，可以说就是数据库对于事务处理的精髓所在。这里通过分析MySQL中InnoDB引擎的加锁机制，来抛砖引玉，让读者更好的理解，在事务处理中数据库到底做了什么。

一次*or两段锁？

因为有大量的并发访问，为了预防死锁，一般应用中推荐使用一次*法，就是在方法的开始阶段，已经预先知道会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式可以有效的避免循环死锁，但在数据库中却不适用，因为在事务开始阶段，数据库并不知道会用到哪些数据。
数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

• 加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
• 解锁阶段：当事务释放了一个*以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

事务	加锁/解锁处理
begin；
insert into test .....	加insert对应的锁
update test set...	加update对应的锁
delete from test ....	加delete对应的锁
commit;	事务提交时，同时释放insert、update、delete对应的锁

这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化（串行化很重要，尤其是在数据恢复和备份的时候）的。

事务中的加锁方式

事务的四种隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。我们的数据库锁，也是为了构建这些隔离级别存在的。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
• 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

Read Uncommitted这种级别，数据库一般都不会用，而且任何操作都不会加锁，这里就不讨论了。

MySQL中锁的种类

MySQL中锁的种类很多，有常见的表锁和行锁，也有新加入的Metadata Lock等等,表锁是对一整张表加锁，虽然可分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做ddl处理时使用。

行锁则是锁住数据行，这种加锁方法比较复杂，但是由于只锁住有限的数据，对于其它数据不加限制，所以并发能力强，MySQL一般都是用行锁来处理并发事务。这里主要讨论的也就是行锁。

Read Committed（读取提交内容）

在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。效果如下

 

MySQL> select * from class_teacher;
+----+--------------+------------+
| id | class_name   | teacher_id |
+----+--------------+------------+
|  1 | 初三一班     |          1 |
|  3 | 初二一班     |          2 |
|  4 | 初二二班     |          2 |
+----+--------------+------------+

由于MySQL的InnoDB默认是使用的RR级别，所以我们先要将该session开启成RC级别，并且设置binlog的模式

 

SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';（或者是MIXED）

事务A	事务B
begin;	begin;
update class_teacher set class_name='初三二班' where teacher_id=1;	update class_teacher set class_name='初三三班' where teacher_id=1;
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;

为了防止并发过程中的修改冲突，事务A中MySQL给teacher_id=1的数据行加锁，并一直不commit（释放锁），那么事务B也就一直拿不到该行锁，wait直到超时。

这时我们要注意到，teacher_id是有索引的，如果是没有索引的class_name呢？update class_teacher set teacher_id=3 where class_name = '初三一班';
那么MySQL会给整张表的所有数据行的加行锁。这里听起来有点不可思议，但是当sql运行的过程中，MySQL并不知道哪些数据行是 class_name = '初三一班'的（没有索引嘛），如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤。

但在实际使用过程当中，MySQL做了一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录释放锁 (违背了二段锁协议的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。可见即使是MySQL，为了效率也是会违反规范的。

这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候，如果无法使用相应的索引，MySQL Server过滤数据的的时候特别慢，就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。

Repeatable Read（可重读）

这是MySQL中InnoDB默认的隔离级别。我们姑且分“读”和“写”两个模块来讲解。

读

读就是可重读，可重读这个概念是一事务的多个实例在并发读取数据时，会看到同样的数据行，有点抽象，我们来看一下效果。

RC（不可重读）模式下的展现

事务A	事务B
begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=1;idclass_nameteacher_id1初三二班12初三一班1
	update class_teacher set class_name='初三三班' where id=1;
	commit;
select id,class_name,teacher_id from class_teacher where teacher_id=1;idclass_nameteacher_id1初三三班12初三一班1读到了事务B修改的数据，和第一次查询的结果不一样，是不可重读的。
commit;

事务B修改id=1的数据提交之后，事务A同样的查询，后一次和前一次的结果不一样，这就是不可重读（重新读取产生的结果不一样）。这就很可能带来一些问题，那么我们来看看在RR级别中MySQL的表现：

事务A	事务B	事务C
begin;	begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=1;
idclass_nameteacher_id1初三二班12初三一班1	update class_teacher set class_name='初三三班' where id=1;commit;
		insert into class_teacher values (null,'初三三班',1);commit;
select id,class_name,teacher_id from class_teacher where teacher_id=1;idclass_nameteacher_id1初三二班12初三一班1没有读到事务B修改的数据，和第一次sql读取的一样，是可重复读的。没有读到事务C新添加的数据。
commit;

我们注意到，当teacher_id=1时，事务A先做了一次读取，事务B中间修改了id=1的数据，并commit之后，事务A第二次读到的数据和第一次完全相同。所以说它是可重读的。那么MySQL是怎么做到的呢？这里姑且卖个关子，我们往下看。

不可重复读和幻读的区别####

不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

悲观锁和乐观锁####

• 悲观锁

它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

• 乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

Serializable

这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

这里要吐槽一句，不要看到select就说不会加锁了，在Serializable这个级别，还是会加锁的！

作者：时芥蓝
链接：https://www.jianshu.com/p/5052f6a454ef
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。