MySql如何实现四大隔离级别

首先mysql的四个隔离级别分别是读未提交，读已提交，可重复读，可串行化

对于读未提交与可串行化两者没有什么好讨论的，一个是什么都不做，另一个是直接无脑加锁并行化

对于读已提交和可重复读，底层实现就是多版本并发控制MVCC，具体来说就是对于这两个隔离级别，数据库会为每个事务创建一个视图，访问的时候以视图的逻辑结果为准，通过undo log版本链可以使得事务可以回滚到视图记录的状态

对于这两个隔离级别的区别，他们生成视图的时机是不一样的

在读已提交隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的
在可重复读隔离级别下，这视图是在事务启动时就创建，整个事务期间都用这个视图

Mysql的索引

Hash索引与B+ Tree索引

索引的数据结构和具体存储引擎的实现有关，MySQL主要有两种结构：Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树

Hash索引:Hash索引底层是哈希表，哈希表是以一种Key-Value存储数据的结构，所以多个数据在存储关系上是没有顺序的，也就没办法进行范围查询，必须要全表扫描。哈希索引只适用于等值查询的场景

B+树索引：B+树更适合外部存储。由于内结点不存放真正的数据（只是存放其子树的最大或最小的关键字，作为索引），一个结点可以存储更多的关键字，每个结点能索引的范围更大更精确，也意味着B+树单次磁盘IO的信息量大于B树，I/O的次数相对减少。B+树也是更容易进行区间访问的，因为叶子节点维护了一个链表，在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。在InnoDB引擎中，只有主键是聚簇索引，如果没有主键则挑选一个唯一键作为聚簇索引，如果没有唯一键，则隐式的生成一个键来建立聚簇索引

聚簇索引与非聚簇索引

对于聚簇索引和非聚簇索引，聚簇索引更快，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询。当查询使用局促索引的时候，在对应的叶子结点上可以获取到整行的数据，不再需要回表查询，而非聚簇索引则需要回表查询。

注意到，非聚簇索引不一定要回表搜索，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引,那么就不必再进行回表查询，即覆盖索引。覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

建立索引注意事项

建立索引的时候一般要考虑字段的使用频率，经常作为查询条件的字段比较适合索引。当然也不能过度建立索引，因为索引也是占据内存的，而且修改表会导致索引更新，所以在建立索引的时候也要考虑表结构。在一些场合使用联合索引是比较好的效果，比如我们可以建立一个（学校-班级-ID）的联合索引，这样会比建立三个索引效果好，但是如果我们只使用其中一个索引ID不会走联合索引，会导致全表扫描，所以要分业务情况。使用联合索引时需要注意顺序，尽量把区分度大的索引放在前面。

为什么建立联合索引要尽量把区分度大的索引放在前面：在联合索引使用中，如果想要命中索引需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。联合索引中有个最左匹配原则，当我们建立联合索引（A，B，C），实际上已经建立了（A）、（A，B）、（A，B，C）三个联合索引。

比如我们上面说的（学校-班级-ID）联合索引，b+树是按照从左到右的顺序来建立搜索树的，b+树优先比较学校来确定下一步的搜索方向，如果还未达到条件则继续执行搜索。如果只有学校字段，班级字段缺失，只能找到这个学校的所有字段，然后再匹配相应ID的学生，此种情况无法用到联合索引。

如何分析Sql语句（如何判断索引是否使用到）

一般都是通过explain命令来查看语句的执行计划，使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

explain的字段

id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
table：指的就是当前执行的表
type：type所显示的是查询使用了哪种类型。查询性能从最好到最差依次是：system > const > eq_ref > ref > range > index > all，一般来说，得保证查询至少达到range级别，最好能达到ref
possible_keys 和 key：possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。key则表示实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好
ref：显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
Extra：包含不适合在其他列中显式但十分重要的额外信息

对于数据库中的锁

排它锁和共享锁

排它锁：被加锁的对象只能被持有锁的事务读取和修改，其他事务无法在该对象上加其他锁，也不能读取和修改该对象
共享锁：被加锁的对象可以被持锁事务读取，但是不能被修改，其他事务也可以在上面再加共享锁

InnoDB 实现了标准的行级锁，也就是共享锁（Shared Lock）和排它锁（Exclusive Lock）。

**共享锁（读锁），允许事务读一行数据。排它锁（写锁），允许事务删除或更新一行数据。**而它们的名字也暗示着各自的另外一个特性，共享锁之间是兼容的，而互斥锁与其他任意锁都不兼容

Lock锁根据粒度主要分为表锁、页锁和行锁。不同的存储引擎拥有的锁粒度都不同

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高； 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。 在数据库引擎InnoDB中支持行锁和表锁用的也比较多，但是Myisam不支持事务，只支持表锁

悲观锁和乐观锁

悲观锁和乐观锁是一种思想，一种处理方式，不可和上面的锁机制(表锁，行锁，排他锁，共享锁)混为一谈

悲观锁：即对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select .... for update来实现悲观锁
乐观锁：顾名思义，就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。如果发现冲突了，则返回错误信息给用户，让用户自已决定如何操作。乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。

(给表加一个版本号或时间戳的字段，读取数据时，将版本号一同读出，数据更新时，将版本号加1。当我们提交数据更新时，判断当前的版本号与第一次读取出来的版本号是否相等。如果相等，则予以更新，否则认为数据过期，拒绝更新，让用户重新操作。)类似解决CAS的ABA问题的思想

数据库的池化思想

数据库连接是一种有限的昂贵的资源，对数据库连接的管理能影响到整个应用程序的伸缩性和健壮性，数据库连接池正式针对这个问题提出来的。数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。（数据库连接池思想和线程池思想一样）常用的三种连接池：

C3p0连接池：开源的JDBC连接池，实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate、Spring等。单线程，性能较差，适用于小型系统，代码600KB左右
Dbcp连接池：由Apache开发的一个Java数据库连接池项目， Tomcat使用的连接池组件就是DBCP。预先将数据库连接放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完再放回。单线程，并发量低，性能不好，适用于小型系统
Druid连接池：Druid不仅是一个数据库连接池，还包含一个ProxyDriver、一系列内置的JDBC组件库、一个SQL Parser

数据库的持久化机制

在InnoDB中有个redo 日志是用来保证 MySQL 持久化功能的。MySql的操作是要写入到日志中，并不会直接刷新到硬盘上进行持久化。如果我们每一次的操作都要写入到硬盘中再更新，整个过程IO成本、查找成本都很高。

日志即起到一个中间转折的作用，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

Mysql杂谈(一)