Mysql常用知识点总结

一．概述

本篇内容主要总结的知识点有：mysql存储引擎，索引，事务，数据库锁，优化

二．存储引擎

mysql存储引擎主要有：InnoDb,MyIsam,Memory等，5.5版本之前默认为MyISAM，5.5版本后默认的存储引擎时InnoDb。

可以通过 show engins 语句查看当前数据库所支持的存储引。

现目前我在工作当中使用的最多的两款存储引擎是InnoDb和MyIAsm，那么这两款存储引擎有什么区别呢？

InnoDb引擎，它提供了对数据库ACID事务的支持，提供了行级锁和外键的约束，InnoDb运行时，会在内存中建立缓冲池用于缓冲数据和索引。启动较慢，不会保存表的行数，当进行表行数的统计如Select count(*) from tableName 时，是会进行全表扫描的。在写操作时，因为锁的粒度较小，是不会锁定全表的。在做修改时，如果不能确定范围，有可能会进行全表的锁定 如：update set core=90 where name like ‘xiaoming%’，它的索引和数据不是分开的

注意：Innodb1.2.x版本开始支持全文索引

MyIASMy引擎，不提供事务的支持，不持支行级锁和外键，在插入和更新数据的时候会进行全表的锁定，效率较低，因为保存了表的行数，所以做select count(*)的统计时可以直接读取已经保存的值而不需要进行全表的扫描。它默认生成三个文件， 表结构/数据/索引

索引和数据没有进行分开。

两者所适用的场景：

myisam 适用：项目读多写少时，并发不高，不需要支持事务大的选择MySIAM,

InnoDb适合：对性能可靠性较高，要求事务的，并发较大的，且更行和插入比较频繁的，我们选择InnoDb.

我们设置设置存储引擎：set default_storage_engine=<存储引擎名(如：MyISAM)>

归纳：

• 1. innodb支持事务和行级锁（mysql5.6（Innodb1.2.x版本）以后也支持了FULLTEXT，MyISAM一开始就支持全文索引

• 2. innodb索引和数据存储在一起，myISAM索引和数据分开存储

• 3. innodb通过事务日志恢复很快能恢复故障

三．索引

Innodb和MyISAM在索引结构方面的区别：

Innodb中的B+树索引的特点：

• 1 非叶子节点不存储数据，只存储主键值和指针

• 2叶子节点不存储指针，只存储数据

• 3叶子节点有指向相邻叶子节点的指针，这样它们之间就形成了带有顺序访问的B+树，这样有利于范围访问。

#####聚集索引和非聚集索引在innodb表现的区别：

• 1聚集索引B+树的叶子节点是一些列的数据，在查询数据时条件的字段为聚集索引时，可以一次性查询到数据

• 2非聚集索引的B+树的叶子节点是聚集索引的指针，即指向数据块的指针，利用该聚集索引的字段条件在查询数据时，会先查询到叶子节点的指针，然后通过指针访问数据块，这样会引起二次查询

聚集索引和非聚集索引在MyISAM中的区别：

聚集索引和非聚集索引的的叶子节点均是数据块的地址，而非聚集索引可以重复不唯一，而聚集索引不能重复且唯一。

四．mysql的事务（注意，在InnoDb引擎上，MyISAM是不支持事务的）

事务有4个特性简称事务的ACID（注：ACID为四个特性的首字母缩写）

• 1 原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚

• 2 一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性

• 3 隔离性（Isolation）：操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

• 4 持久性（Durability）：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

InnoDb如何保证这些事务的呢？
是利用事务日志来保证的

• redo log重做日志用来保证事务的持久性
• undo log回滚日志保证事务的原子性
• undo log+redo log保证事务的一致性

1 事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号;
当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；
2 当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），
也就是写数据前，需要先写日志。这种方式称为“预写日志方式”

五．mysql数据库锁

锁（共享、排他）用来保证事务的隔离性

数据库我们使用的锁有哪几种呢？基本使用两种锁，乐观锁和悲观锁

• 乐观锁：一般的做法是在需要锁的数据上增加一个版本号，或者时间戳，类似CAS，像我是用一个version字段保存版本号信息，更新前查询该版本号如：1，在更新时利用条件 where version=1 进行更新

• 悲观锁：又分为共享锁和排他锁

共享锁：使用在对数据进行读操作。因此多个事务可以同时为一个对象加共享锁，我们加共享锁的方式为：在查询语句后面在查询语句的后面加上 lock in share mode,数据库在查询过程中会为数据库表增加共享锁

排他锁：表示多个事务对同一个资源只能加一把锁，加锁方式：select … for update操作来实现悲观锁。

六．事务的隔离性

既然事务具有隔离性，那么事务有几种隔离级别呢，不进行事务间的隔离会带来什么问题呢？

首先说说不进行事务隔离所带来的问题：

• 1 更新丢失：两事务同时更新，一个失败回滚覆盖另一个事务的更新。或事务1执行更细操作，在事务1结束前事务2也更新，则事务1的更细结果被事务2的覆盖了。

• 2脏读：事务T2读取到事务T1修改了但是还未提交的数据，之后事务T1又回滚其更新操作，导致事务T2读到的是脏数据。

• 3 不可重复读：事务T1读取某个数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。（在重复读取某固定数据，因另一事务的修改变更而产生）

• 4 虚读（幻读）：事务T1读取在读取某范围数据时，事务T2又插入一条数据，当事务T1再次数据这个范围数据时发现不一样了，出现了一些“幻影行”。（在重复读取某范围数据，因另一事务的插入或删除一条数据导致）

区别总结：

脏读和不可重复读的区别：脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

不可重复读和幻读的区别：都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

以上问题 可以通过事务的隔离级别一一来解决 事务的隔离级别有四种

• 1 读未提交（Read Uncommitted）

只限制同一数据写事务禁止其他写事务。解决”更新丢失”。（一事务写时禁止其他事务写）此时会用到：排他写锁

• 2 读提交（Read Committed）

只限制同一数据写事务禁止其它读写事务。解决”脏读”，以及”更新丢失”。（一事务写时禁止其他事务读写）

所需的锁：排他写锁、共享读锁

• 3 可重复读（Repeatable Read）

限制同一数据写事务禁止其他读写事务，读事务禁止其它写事务(允许读)。解决”不可重复读”，以及”更新丢失”和”脏读”。（一事务写时禁止其他事务读写、一事务读时禁止其他事务写）

注意没有解决幻读，解决幻读的方法是增加范围锁（range lock）或者表锁。

所需的锁：排他写锁、共享读锁

• 4串行化（Serializable）

限制所有读写事务都必须串行化实行。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。（一事务写时禁止其他事务读写、一事务读时禁止其他事务读写）

所须的锁：范围锁或表锁

七．Spring Transaction的事务传播行为

支持当前事务的有三种：

• 1 REQUIRED如果当前存在事务，则加入该事务；如果当前没有事务，则创建一个新的事务。

• 2SUPPORTS如果当前存在事务，则加入该事务；如果当前没有事务，则以非事务的方式继续运行。

• 3MANDATORY如果当前存在事务，则加入该事务；如果当前没有事务，则抛出异常。（mandatory：强制性）

不支持当前事务的有三种：

• 1 REQUIRES_NEW创建一个新的事务，如果当前存在事务，则把当前事务挂起。

• 2 NOT_SUPPORTED以非事务方式运行，如果当前存在事务，则把当前事务挂起。

• 3 NEVER以非事务方式运行，如果当前存在事务，则抛出异常。

其他情况：

NESTED如果当前存在事务，则创建一个事务作为当前事务的嵌套事务来运行；如果当前没有事务，则该取值等价于TransactionDefinition.PROPAGATION_REQUIRED。

八．数据库的优化：

• 1 字段优化 2.简单的越好（不同类型），3.避免NULL

• 2 索引优化 如联合索引，要利用联合索引的最左匹配原则，

创建聚集索引（能通过他找到一行数据），默认主键，其次唯一索引

• 3 查询优化，利用explain检测，join代替多次查询，多次查询会生成多次临时表格

• 4 使用缓存如redis

• 5 合理的分库分表

合理利用缓存，查询的时候尽可能的命中索引，聚集索引效果高于其它索引，索引数量不能越多越好，最多6个就足够了，因为索引也需要占用存储，有些查询可能不会命中索引，如：查询语句中有计算，not in,like 左边有%，没有用到联合索引的最左匹配原则，使用了or,如果类型时字符串，则字符串要用引号””,索引列上使用函数，is null

sql 经常用explain检测下：查询的type

性能由最差到最好：

ALL  <index<range<ref<eq_ref<const<system

优化建议至少优化到>=range

喜欢的朋友可以关注下哦~