MySQL基础知识

MySql

什么是MySql

MySQL是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

存储引擎

通过下面的命令查看默认的存储引擎：mysql> show variables like '%storage_engine%';

查看表的存储引擎:show table status like "table_name" ;

MyISAM和InnoDB区别:

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁；

4. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

5. InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而MyISAM可以没有；

InnoDB为什么推荐使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的四大特性：

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发读问题

当数据库上有多个事务同时执行的时候，就可能出现脏读、不可重复读、幻读的问题。

• 脏读: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

  读到了另一事务未提交更新数据，即读取到了脏数据

• 不可重复读: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

  对同一记录的两次读取不一致哦，因为另一事务对该记录做了修改

• 幻读: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

  对同一张表的两次查询不一致，因为另一事务插入了一条记录

查看MySql的隔离级别：show variables like '%isolation';

事务的四大隔离级别：

• SERIALIZABLE(串行化)：

顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

• REPEATABLE READ(可重复读)

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的

• READ COMMITTED(读已提交的数据)

一个事务提交之后，它做的变更才会被其他事务看到

• READ UNCOMMITTED(读未提交的数据)

一个事务还没提交时，它做的变更就能被别的事务看到

MySql默认的隔离级别是：可重复读

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

Mysql逻辑架构

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。

MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

每一个客户端发起一个新的请求都由服务器端的连接/线程处理工具负责接收客户端的请求并开辟一个新的内存空间，在服务器端的内存中生成一个新的线程，当每一个用户连接到服务器端的时候就会在进程地址空间里生成一个新的线程用于响应客户端请求，用户发起的查询请求都在线程空间内运行， 结果也在这里面缓存并返回给服务器端。线程的重用和销毁都是由连接/线程处理管理器实现的。

综上所述：用户发起请求，连接/线程处理器开辟内存空间，开始提供查询的机制。

##　MySQL查询过程

用户总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？下图展示了MySQL的查询过程。

MySQL整个查询执行过程，总的来说分为6个步骤：

1. 客户端向MySQL服务器发送一条查询请求
2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
4. MySQL根据执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端，同时缓存查询结果

索引

索引是帮助MySQL高效获取数据的排好序的数据结构。

使用索引的优点

大大减小了服务器需要扫描到的数据量可以帮助服务器避免排序和临时表

可以将随机I/O变为顺序I/O

索引分类

单值索引

利用表中的某一个字段创建单值索引。一张表中往往有多个字段，也就是说每一列其实都可以创建一个索引，这个根据我们实际需求来进行创建。还需要注意的一点就是，一张表可以创建多个“单值索引”。

假如某一张表既有age字段，又有name字段，我们可以分别对age、name创建一个单值索引，这样一张表就有了两个单值索引。

唯一索引

也是利用表中的某一个字段创建单值索引，与单值索引不同的是：创建唯一索引的字段中的数据，不能有重复值。像age肯定有很多人的年龄相同，像name肯定有些人是重名的，因此都不适合创建“唯一索引”。像编号id、学号sid，对于每个人都不一样，因此可以用于创建唯一索引。

复合索引

多个列共同构成的索引。比如说我们创建这样一个“复合索引”(name,age)，先利用name进行索引查询，当name相同的时候，我们利用age再进行一次筛选。注意：复合索引的字段并不是非要都用完，当我们利用name字段索引出我们想要的结果以后，就不需要再使用age进行再次筛选了。

B+树结构：

非叶子节点不存储data，只存储索引，每个索引页的大小约为16kb,可以存放1000多个索引，叶子节点包含所有的索引字段，叶子节点用指针连接，提高区间访问的性能。

MyISAM存储引擎：它的数据库文件有三个 .frm文件、.data文件、index文件

InnoDB:它的数据库文件有两个.frm、ibd文件

B+树的指针会维护下一个数据节点的地址，使用范围查询时，比如说查询>20的数据，当查找到20这个节点的数据时，会把后面的直接加载到内存中，大大的提高了效率。

聚集索引和非聚集索引

聚簇索引：聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，当表有聚簇索引时，它的数据行实际存放在索引的叶子页中，将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

索引优化：

索引覆盖： 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值

回表查询：先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

explain

查看执行计划

博文链接

for update

如果查询条件用了索引/主键，那么select … for update就会进行行锁。

如果是普通字段(没有索引/主键)，那么select … for update就会进行锁表。

SQL语句加锁问题

InnoDB 存储引擎中的行锁的加锁规则，并辅以实例解释。

首先众所周知，InnoDB 三种行锁：

• Record Lock（记录锁） ：锁住某一行记录
• Gap Lock（间隙锁） ：锁住一段左开右开的区间
• Next-key Lock（临键锁） ：锁住一段左开右闭的区间

哪些语句上面会加行锁？

1）对于常见的 DML 语句（如 UPDATE、DELETE 和 INSERT ），InnoDB 会自动给相应的记录行加写锁

2）默认情况下对于普通 SELECT 语句，InnoDB 不会加任何锁，但是在 Serializable 隔离级别下会加行级读锁

上面两种是隐式锁定，InnoDB 也支持通过特定的语句进行显式锁定：

3）SELECT * FROM table_name WHERE ... FOR UPDATE，加行级写锁

4）SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE，加行级读锁

加锁规则的两条核心：

1）查找过程中访问到的对象才会加锁

这句话该怎么理解？比如有主键 id 为 1 2 3 4 5 ... 10 的 10 条记录，我们要找到 id = 7 的记录。注意，查找并不是从第一行开始一行一行地进行遍历，而是根据 B+ 树的特性进行二分查找，所以一般存储引擎只会访问到要找的记录行（id = 7）的相邻区间

2）加锁的基本单位是 Next-key Lock

假设有这么一张 user 表，id 为主键（唯一索引），a 是普通索引（非唯一索引），b都是普通的列，其上没有任何索引：

id (唯一索引)	a (非唯一索引)	b
10	4	Alice
15	8	Bob
20	16	Cilly
25	32	Druid
30	64	Erik

案例 1：唯一索引等值查询

当我们用唯一索引进行等值查询的时候，根据查询的记录是否存在，加锁的规则会有所不同：

1. 当查询的记录是存在的，Next-key Lock 会退化成记录锁
2. 当查询的记录是不存在的，Next-key Lock 会退化成间隙锁

查询的记录存在

select * from user where id = 25 for update;

结合加锁的两条核心：查找过程中访问到的对象才会加锁 + 加锁的基本单位是 Next-key Lock（左开右闭），我们可以分析出，这条语句的加锁范围是 (20, 25]

不过，由于这个唯一索引等值查询的记录 id = 25 是存在的，因此，Next-key Lock 会退化成记录锁，因此最终的加锁范围是 id = 25 这一行

查询的记录不存在

select * from user where id = 22 for update;

结合加锁的两条核心：查找过程中访问到的对象才会加锁 + 加锁的基本单位是 Next-key Lock（左开右闭），我们可以分析出，这条语句的加锁范围是 (20, 25]

这里为什么是 (20，25] 而不是 (20, 22]，因为 id = 22 的记录不存在呀，InnoDB 先找到 id = 20 的记录，发现不匹配，于是继续往下找，发现 id = 25，因此，id = 25 的这一行被扫描到了，所以整体的加锁范围是 (20, 25]

由于这个唯一索引等值查询的记录 id = 22 是不存在的，因此，Next-key Lock 会退化成间隙锁，因此最终在主键 id 上的加锁范围是 Gap Lock (20, 25)

数据库锁的相关问题

为什么需要加锁

如果有多个并发请求存取数据，数据就可能会产生多个事务同时操作同一行数据。如果并发操作不加控制，不加锁的话，就可能写入了不正确的数据，或者导致读取了不正确的数据，破坏了数据的一致性。因此需要考虑加锁。

InnoDB有哪些锁

共享/排他锁

InnoDB 实现了两种标准的行级锁：共享锁（简称 S 锁）、排他锁（简称 X 锁）。

共享锁：简称为S锁，在事务要读取一条记录时，需要先获取该记录的S锁。

排他锁：简称 X 锁，在事务需要改动一条记录时，需要先获取该记录的 X 锁。

如果事务T1持有行 R 的S锁，那么另一个事务T2请求访问这条记录时，会做如下处理：

• T2 请求S锁立即被允许，结果T1和T2都持有R行的S锁

• T2 请求X锁不能被立即允许,此操作会阻塞

如果T1持有行R的X锁，那么T2请求R的X、S锁都不能被立即允许，T2必须等待T1释放X锁才可以，因为X锁与任何的锁都不兼容。

X锁和S锁是对于行记录来说的话，因此可以称它们为行级锁或者行锁。我们认为行锁的粒度就比较细，其实一个事务也可以在表级别下加锁，对应的，我们称之为表锁。给表加的锁，也是可以分为X锁和S锁的。

意向锁

什么是意向锁呢？意向锁是一种不与行级锁冲突的表级锁。未来的某个时刻，事务可能要加共享或者排它锁时，先提前声明一个意向。注意一下，意向锁，是一个表级别的锁。

为什么需要意向锁呢？或者换个通俗的说法，为什么要加共享锁或排他锁时的时候，需要提前声明个意向锁呢呢？

因为 InnoDB 是支持表锁和行锁共存的，如果一个事务 A 获取到某一行的排他锁，并未提交，这时候事务 B 请求获取同一个表的表共享锁。因为共享锁和排他锁是互斥的，因此事务 B 想对这个表加共享锁时，需要保证没有其他事务持有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁。

然后问题来了，你要保证没有其他事务持有表中任意一行的排他锁的话，去遍历每一行？这样显然是一个效率很差的做法。为了解决这个问题， InnoDb 的设计提出了意向锁。

**意向锁是如何解决这个问题的呢？**我们来看下

意向锁分为两类：

• 意向共享锁：简称IS锁，当事务准备在某些记录上加 S 锁时，需要现在表级别加一个IS锁。
• 意向排他锁：简称IX锁，当事务准备在某条记录上加上 X 锁时，需要现在表级别加一个IX锁。

比如：

• select ... lock in share mode，要给表设置IS锁;
• select ... for update，要给表设置IX锁;

意向锁又是如何解决这个效率低的问题呢：

如果一个事务 A 获取到某一行的排他锁，并未提交,这时候表上就有意向排他锁和这一行的排他锁。这时候事务 B 想要获取这个表的共享锁，此时因为检测到事务A持有了表的意向排他锁，因此事务 A 必然持有某些行的排他锁，也就是说事务 B 对表的加锁请求需要阻塞等待，不再需要去检测表的每一行数据是否存在排他锁啦。

记录锁

记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果C1字段是主键或者是唯一索引的话，这个SQL会加一个记录锁（Record Lock）

乐观锁

一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。select…for update就是MySQL悲观锁的应用。

悲观锁

允许多个事务同时对数据进行变动。实现方式：乐观锁一般会通过version版本号/时间戳判断记录是否被更改过，一般配合CAS算法实现。

记录锁是最简单的行锁，仅仅锁住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果C1字段是主键或者是唯一索引的话，这个SQL会加一个记录锁（Record Lock）

乐观锁

一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。select…for update就是MySQL悲观锁的应用。

悲观锁

允许多个事务同时对数据进行变动。实现方式：乐观锁一般会通过version版本号/时间戳判断记录是否被更改过，一般配合CAS算法实现。