MySQL数据库相关笔记

一、索引

1. 概述

索引（Index）是帮助MySQL高效获取数据的数据结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

通常不加索引的情况，最基本的查询算法当然是顺序查找（linear search），其复杂度为O(n)，一些优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）只能应用于特定的数据结构之上。

为了满足快速查找的需求，数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引的优点：
1.提高数据的检索速度
2.加快表和表之间的连接速度

索引的缺点：
1.数据库建立过程中需要花费一定时间建立和维护索引
2.索引需要占一定存储空间
3.对数据库进行修改时，索引需要动态维护

2. 索引的划分

按字段个数划分

• 单一索引：索引为一列是单一索引
• 联合索引：多个列组合创建的索引为联合索引

按逻辑划分

• 普通索引：基本索引，没有任何约束，一张表中可以有多个
• 唯一索引：在普通索引的基础上增加了唯一性约束（unique），一张表中可以有多个
• 主键索引：在唯一索引（unique）基础上增加了一个不为空约束（not null），一张表中只有一个
• 全文索引：用作搜索引擎

按物理实现划分

• 聚簇索引：数据存储和索引都是按照一定的顺序组织，找到索引也找到了数据，数据的物理存储顺序和索引顺序是一致的，即：只要索引相连，那么对应的数据一定也是相邻存储在磁盘上的。聚簇索引数据直接存放在B+树的叶子节点上，索引和数据一一对应，一个表中只能有一个聚集索引。（主键一定是聚簇索引）

• 非聚簇索引：也叫二级索引。B+树的叶子节点上不直接存放数据，而是存储数据行地址。查找时，先找到数据所在行地址，再去磁盘查找数据。

• 比较：聚簇索引查找快，增删慢；非聚簇索引查找慢，增删快

3. 索引的创建与删除

使用student表作为示例进行说明：

CREATE TABLE STUDENT(
SNO VARCHAR(3) PRIMARY KEY,
SNAME VARCHAR(10) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME  DEFAULT NULL,
CCLASS VARCHAR(5) DEFAULT NULL
)CHARSET = utf8;

1.在新建表中隐式添加索引

① 普通索引

CREATE TABLE STUDENT(
SNO VARCHAR(3) PRIMARY KEY, #在创建时添加主键索引
SNAME VARCHAR(10) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME  DEFAULT NULL,
CCLASS VARCHAR(5) DEFAULT NULL，
index index_SNO(SNO)  #在创建表时添加普通索引
)CHARSET = utf8;

② 唯一索引

CREATE TABLE STUDENT(
SNO VARCHAR(3) PRIMARY KEY,
SNAME VARCHAR(10) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME  DEFAULT NULL,
CCLASS VARCHAR(5) DEFAULT NULL，
unique index index_SNO(SNO)  #在创建表时添加唯一索引
)CHARSET = utf8;

③ 全文索引

CREATE TABLE STUDENT(
SNO VARCHAR(3) PRIMARY KEY,
SNAME VARCHAR(10) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME  DEFAULT NULL,
CCLASS VARCHAR(5) DEFAULT NULL，
fulltext index index_SNO(SNO)  #在创建表时添加全文索引
)CHARSET = utf8;

④ 多列索引

CREATE TABLE STUDENT(
SNO VARCHAR(3) PRIMARY KEY, 
SNAME VARCHAR(10) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME  DEFAULT NULL,
CCLASS VARCHAR(5) DEFAULT NULL，
KEY index_sno_sname(SNO,SNAME) #添加多列索引
)CHARSET = utf8;

2.在已建表中添加索引

① 普通索引

create INDEX index_no on student(SNO);
ALTER TABLE student ADD INDEX index_no(SNO);

② 唯一索引

create UNIQUE INDEX index_no on student(SNO);
ALTER TABLE student ADD UNIQUE INDEX index_no(SNO);

③ 全文索引

create FULLTEXT INDEX index_no on student(SNO); #InnoDB引擎不支持fulltext类型索引
ALTER TABLE student ADD FULLTEXT INDEX index_no(SNO); #InnoDB引擎不支持fulltext类型索引

④ 多列索引

create INDEX index_name_no on student(SNO,SNAME);
ALTER TABLE student ADD INDEX index_name_no(SNO,SNAME);

3.删除索引

ALTER TABLE student DROP INDEX index_name_no;
DROP INDEX index_name_no ON student;

4.查看索引

SHOW INDEX FROM student;

4. 索引的注意事项

4.1 适合加索引的情况

 1. 经常作为查询条件的列（where）
 2. 经常用于表连接的列，例如外键（join）
 3. 经常用于排序的列（order by）、用于分组的列（group by）
 4. distinct 修饰的字段
 5. 区分度高（散列性高）的列适合作为索引

4.2 不适合加索引的情况

 1. 数据量少的时候不宜加索引（少于1000行）
 2. 重复度较高的列不适合加索引
 3. 经常dml（增、删、改）操作的表不需要创建创建索引

4.3 索引失效的情况

 1. 对索引字段进行函数或表达式运算操作，索引会失效
 2. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效
 3. or语句前后没有同时使用索引；只有当or左右查询字段均为索引时，才会生效
 4. 组合索引，不是使用第一列索引（不符合最左前缀原则），索引失效
 5. 如果列类型是字符串，在条件中没有使用引号引起来，数据类型会隐式转化，使索引无效，产生全表扫描
 6. 在索引字段上使用not，<>，!=，因为对他的处理只会产生全表扫描，因此索引无效。

5. 索引的原理

5.1 B树

为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为非主键数据值。

一棵m阶B-tree有以下特点：

 1. 每个节点最多有m个孩子。
 2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
 3. 若根节点不是叶子节点，则至少有2个孩子。
 4. 所有叶子节点都在同一层，且不包含其它关键字信息。
 5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
 6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
 7. ki(i=1,…n)为关键字，且关键字升序排序。
 8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)。

如下图所示为一个3阶的B-Tree：

模拟查找关键字29的过程：

 1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
 2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
 3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
 4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
 5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
 6. 在磁盘块8中的关键字列表中找到关键字29。
 
 结论：
 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。
 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。
 而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。
 B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

5.2 B+树

B+Tree是在B-Tree基础上的一种优化，从B-Tree结构图中可以看到每个节点同时包含数据的key值和data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，较多的数据会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

在B+Tree中，所有data都是按照key值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。另外，B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，所有叶子节点（即数据节点）之间是一种链式环结构。所以B+数有两种查找方式：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

上述B树优化的B+树结构示意图入下：

5.3 红黑树

红黑树是在二叉平衡树（AVL）的基础上经过优化的数据结构。由于AVL左右子高度差不能超过1，每次进行插入/删除操作时几乎都要通过旋转操作保存平衡，在频繁的插入/删除场景中，使得AVL树的性能大打折扣，红黑树通过牺牲严格的平衡，换取少量的旋转操作，性能优于AVL树。

红黑树的规则：

1.节点不是黑色，就是红色（非黑即红）
2.根节点为黑色
3.叶节点为黑色（叶节点是指末梢的空节点 Nil或Null）
4.一个节点为红色，则其两个子节点必须是黑色的（根到叶子的所有路径，不可能存在两个连续的红色节点）
5.每个节点到叶子节点的所有路径，都包含相同数目的黑色节点（相同的黑色高度）

5.4 B+树作为普遍索引结构原因

相比于红黑树：

1.平衡树查找的时间复杂度与树高h相关，红黑树出度为2，而B+数出度一般都很大，这就决定了B+树的高度要低，检索次数少。
2.数据库系统可以将索引的一个节点大小设置为页的大小，这样每次I/O都能完全载入一个节点，利用预读性，相邻的节点能够被预先载入，减少了磁盘巡道的时间。B+树可以通过链式结构顺序访问，从而利用预读性从底层加快I/O速度。

相比于B树：
1.B+树同时支持随机检索和顺序检索，B树只支持随机检索
2.B+树空间利用率更高，降低树的高度，从而减少I/O次数
3.B+树叶子节点使用指针顺序连接在一起，只要遍历叶子节点就能实现整棵树的遍历。

二、MySQL的存储引擎

摘要：MySQL有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。其中使用最多和必须掌握的是：MylSAM和InnoDB这两种引擎。

1. MylSAM和InnoDB的区别

• InnoDB支持事务，而MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务。
• InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MyISAM会失败。
• InnoDB是聚集索引，必须有主键，主键索引叶子节点存放数据，辅助索引叶子节点存放主键的值，辅助索引查找时先查找到主键，再通过主键找到数据；MyISAM是非聚集索引，其主键索引和辅助索引相互独立，叶子节点存放的是数据文件的地址指针。

• InnoDB不保存表的具体行数，而MyISAM用一个变量保存了整个表的行数。执行select count(*) from table时需要全表扫描。而MyISAM执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）
• InnoDB不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
• InnoDB支持表、行(默认)级锁，而MyISAM只支持表级锁。（InnoDB的行锁是在索引上实现的，而不是锁在物理行记录上。如果访问没有命中索引，也无法使用行锁，将要退化为表锁。）
• InnoDB表必须有唯一索引（如主键）而Myisam可以没有。（InnoDB用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键）

2. MyISAM和InnoDB如何选择

• 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM
• 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB
• 系统奔溃后，MyISAM恢复起来更困难，能否接受
• MySQL默认为InnoDB，需要使用它不支持的某种特性时才考虑使用其他存储引擎

三、事务

1. 事务的ACID特性

• 原子性（Atomicity）：当前事务的操作要么同时成功，要么同时失败。原子性由 undo log日志来保证，主要存储更新之前的数据，用作备份。
• 一致性（Consistency）：使用事务的最终目的。有业务代码正确逻辑来保证。
• 隔离性（Isolation）：在事务并发执行时，它们内部的操作不能相互干扰。
• 持久性（Durability）：一旦提交了事务，他对数据库的改变应该是持久性的。持久性由redo log日志来保证，用户将对发生了修改而为提交的数据存入了redo log日志中，当此时发生断电等其他异常时，可以根据redo log日志重新对数据做一个提交，做一个恢复。

2. 事务的隔离性

InnoDB引擎中，定义了四种隔离级别，级别越高事务隔离性越好，但性能越差，隔离性是由MySQL的各种锁以及MVCC机制来实现的。

• read uncommit（读未提交，其他事务没提交的数据可以读到）：有脏读，不可重复读，幻读问题
• read commit（读已提交，其他事物提交了才能读到）：有不可重复读，幻读问题问题（Oracle默认）
• repeatable read（可重复读，每次读操作结果以第一次为准）：有幻读问题（MySQL默认）
• serializable（串行，查询操作会等待其他事务提交完才能查询 ）：可以解决所有问题

脏读问题：读到另外一个事务没有提交的数据，另外一个事务一旦回滚该数据就不存在了。（即读到可能不存在的数据）
不可重复读问题：多个事务并发过程中，在一个事务中，多次查询一个数据，可能每次结果不一样
幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。和不可重复读的区别在于，某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

3. 锁

• 读锁（共享锁，S锁）：select ...lock in share mode
  读锁是共享的，多个事务可以同时读同一资源，但是不允许其他事务修改
• 写锁（排它锁，X锁）：select ...for update
  写锁是排他的，会阻塞其他的写锁和读锁，update,delete,insert都会加写锁

串行化的底层逻辑就是，在所有的读操作后面+读锁，这把读锁是在事务提交了之后才释放。这样在串行化的隔离级别下，读和写不能同时发生。

read commit和repeatable read则是使用MVCC机制实现。

4. MVCC机制

4.1 概述

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC主要的功能在于以不加锁的方式去处理读-写冲突，避免了读-写的相互阻塞，从而提高了数据库的并发性能。（原理类似于CopyOnWriteArrayList集合中解决读写冲突的方法，后面详细讲解）

4.2 MVCC涉及到的一些概念和数据结构

事务版本号：事务每次开启的时候都会获得一个自增长的事务ID（trx_id），用于判断事务开始执行的先后顺序。这个ID就是事务版本号。

隐藏字段：对于InnoDB存储引擎，每一行记录都有两个隐藏的列（trx_id,roll_pointer），即记录操作该行数据的事务版本号以及回滚指针。此外，由于InnoDB是聚簇索引，如果没有定义主键约束以及非空的unique约束，还会自动生成一个单调递增的隐藏主键row_id。

undo log：回滚日志，用于存储旧版本数据。如果某个事务要修改某数据，首先会将原始数据复制一份到undo log中，这样如果回滚的话，可以通过当前的回滚指针找到原始数据对其进行恢复。另外，如果当前读不可见，可以顺着undo log链找到满足可见性的记录版本。

当前读：读取的是记录的最新版本，显示加锁的都是当前读；如果当前数据不可见，会阻塞等待其他事务提交后可见了才能继续查询。

select *from user where id=1 for update; #写锁
select *from user where id=1 lock in share mode; #读锁

快照读：读取的是undo log中可见版本的数据。普通的select语句都是快照读。

select *from user where id=1;

ReadView：是事务在进行快照读时生成的快照记录，用于解决可见性问题。它主要有以下几个属性：

• trx_ids:一个list表，包含活跃的读写事务ID（未提交事务）。
• up_limit_id：trx_ids中事务最小的ID，如果trx_ids为空，则up_limit_id=low_limit_id。
• low_limit_id：目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
• creator_trx_id：表示生成该ReadView的事务ID

ReadView可见性定义规则为：

• 如果被访问版本数据的事务ID trx_id == creator_trx_id，那么表示当前事务访问的是自己修改过的记录，那么该版本对当前事务可见；
• 如果被访问版本数据的事务ID trx_id < up_limit_id，那么表示生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本数据的事务ID trx_id > low_limit_id 值，那么表示生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
• 如果被访问版本数据的事务ID在 up_limit_id<trx_id <m_low_limit_id 之间，那就需要判断一下版本的事务ID是不是在trx_ids列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

总结：

4.3 MVCC的实现原理

查询一条记录
1.获取事务自己的ID，即trx_id。（是在事务开启的时候获取的，即begin执行时）
2.获取readview（在select时候生成，RR隔离级别时只生成一次，RC级别每次select时都会生成一个新的readview）
3.数据库表中如果查询到了数据，就通过readview中的事务版本号进行比较
4.如果不符合可见性规则，就通过undo log链查找可见版本的记录。
总结： InnoDB实现MVCC，是通过readview+undo log来实现的，undo log保存了历史快照，readview用于找到符合可见性要求的版本记录。

MVCC实现读已提交和可重复读的过程：

总结： 读已提交（RC）和可重复读（RR）唯一的区别在于：在RC级别下，每个select都创建一个新的readview；而在RR级别下，则是第一个select才创建readview。

5. SQL的执行过程

从准备更新一条数据到事务的提交的流程描述：

1. 首先执行器根据MySQL的执行流程查询数据，先是从缓存池（Buffer Poll）中查询数据，如果没有就去磁盘数据库文件中查询，如果查询到了就将其放入缓冲池中;
2. 在数据被缓存到缓冲池的同时，会把该数据的原始记录写入undo log日志文件；
3. 在BufferPoll中完成更新的动作，同时会将更新后的数据添加到redo log buffer中；
4. 完成以后就可以提交事务，在提交的同时会做以下三件事:
   • 将redo log buffer中的数据写入redo log文件中
   • 本次操作记录写入到bin log中
   • 将bin log文件名字和更新内容的位置记录到redo log中，同时在redo log最后添加commit标记

四、MySQL数据库中的锁

1. 锁的分类

2. 表锁

InnoDB存在两种表锁，一种是手动添加的表读锁&& 表写锁，一种是自动添加的意向锁：

• 表读锁（S锁，共享锁）：当一个事务对一个表加读锁，则其他事务对该表可以获取读锁，但是不能获取写锁。即读-读不互斥。使用该语句为表添加读锁：lock table student read;

• 表写锁（X锁，排它锁）：当一个事务对表加写锁，则其他事务对该表无法获取读锁也无法获取写锁，即读-写互斥，写-写互斥。使用该语句为表添加读锁：lock table student write;

InnoDB具有行锁，在对行进行加锁时，为了表明有行进行了加锁，会对表加意向锁（自动添加）：

• 意向共享锁（IS锁，意向读锁）：当InnoDB中有行加了读锁，数据库会自动为表添加意向读锁。其他事务如果想要对某些数据加行S锁，必须要先获得表的IS锁。

• 意向排他锁（IX锁，意向写锁）：当InnoDB中有行加了写锁，数据库会自动为表添加意向写锁。其他事务如果想要对某些数据加行X锁，必须要先获得表的IX锁。

意向锁的作用： 假设某个事务对表某一行进行了加锁，数据库会自动加上意向锁，当另外一个事务要对整个表添加S锁/X锁时，不必去每一行遍历看是否有锁，只需看意向锁即可。此外，意向锁不会和行级X/S锁互斥，也就是说一个事务对某一行数据加锁后，另外一个事务可以获取到意向锁并在另一行数据上加行锁。

此外，InnoDB和MyISAM中的锁有一些不同：

1. InnoDB在增删改时，是对搜索到的数据集添加行锁；查操作时，根据事务的隔离级别有所不同。
2. MyISAM在增删改时是对整个表添加写锁，查操作时添加读锁。

3. 行锁

InnoDB和MyISAM有两个本质的区别：InnoDB支持行锁、InnoDB支持事务。

InnoDB实现了以下两种类型的行锁：

• 共享锁（S锁、读锁）： 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。即多个客户可以同时读取同一个资源，但不允许其他客户修改。在语句后面主动加:for share

• 排他锁（X锁、写锁)： 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的读锁和写锁。写锁是排他的，写锁会阻塞其他的写锁和读锁。在语句后面主动加:for update

注：行锁是加在索引上的而非物理行上，所以如果索引失效，行锁就退化成表锁。

行锁主要有三种：

• 记录锁（record lock）：记录锁是说事务在加锁后锁住的只是表的某一条记录。
• 间隙锁（Gap Lock）：间隙锁是一个在索引记录之间的间隙上的锁，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。主要用来解决可重复读级别下的幻读问题
• 临键锁（next-key lock）：是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住

4. 乐观锁和悲观锁

乐观锁和悲观锁不是具体的锁，而是两种思想。

乐观锁：认为数据在读取的时候没有其他的事务对其进行修改，于是就不加锁，而是通过CAS机制实现。具体来说就是让数据和原来数据进行比较，如果不一致（说明被修改），进行重试或者读取失败；如果一致，则直接读取该数据。使用乐观锁的机制，避免了加锁阻塞，提高了查询效率。适合于修改少的场景。
一般我们在数据库表中添加一个版本字段version来实现，例如操作1和操作2在更新User表的时，执行语句如下：

# 这样，当有事务修改了数据时，这边按照版本号查询会失败
update A set Name=lisi,version=version+1 where ID=#{id} and version=#{version}

悲观锁：认为数据在读的时候一定会有事务对其进行修改，于是在读的时候对其进行加锁。适合于修改多的场景。