MySQL(学习笔记)

其他文章链接
Java基础
Java集合
多线程
JVM
MySQL
Redis
docker
计算机网络
操作系统

---

1.索引

  MySQL索引使⽤的数据结构主要有BTree索引和哈希索引。
  对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余⼤部分场景，建议选择BTree索引。

哈希适用存储引擎如下图：

B+树适用存储引擎如下图：

R树（解决高维空间搜索问题，如范围内餐厅问题）适用存储引擎如下图：

  即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb和MyISAM默认的索引是B-tree索引;而Memory默认的索引是Hash索引。

1.1 哈希索引

Hash结构效率高，那为什么索引结构要设计成树型呢?

1. Hash 索引仅能满足(=) (<>)和IN查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为o(n);而树型的“有序”特性，依然能够保持o(log2N)的高效率。
2. Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。
3. 对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
4. 对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以,Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

Hash索引的适用性:

• Hash索引存在着很多限制，相比之下在数据库中B+树索引的使用面会更广，不过也有一些场景采用Hash索引效率更高，比如在键值型(Key-Value）数据库中，Redis存储的核心就是 Hash表。
• MySQL中的yMemory存储引擎支持Hash存储，如果我们需要用到查询的临时表时，就可以选择Memory存储引擎，把某个字段设置为Hash索引，比如字符串类型的字段，进行Hash计算之后长度可以缩短到几个字节。当字段的重复度低，而且经常需要进行等值查询的时候，采用Hash索引是个不错的选择。
• InnoDB本身不支持 Hash索引，但是提供自适应 Hash索引(Adaptive Hash Index，默认开启)。什么情况下才会使用自适应Hash 索引呢?如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让B+树也具备了Hash索引的优点。

1.2 Hash索引与B+树索引的区别

1. Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
2. Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，Hash索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算 Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
3. Hash索引不支持ORDER BY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面后模糊查询(比如%结尾)的话就可以起到优化作用。
4. InnoDB 不支持哈希索引。

1.3 B+树和B树的区别

下图为B树

1. B+树中，有k个孩子的节点就有k个关键字,也就是孩子数量=关键字数。而B树中，孩子数量=关键字数+1。
2. B+树非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
3. B+树非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
4. B+树所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

B+树查询效率更稳定、查询效率更高、范围查询效率也更高。

1.4 B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引的原因

1. B+树的磁盘读写代价更低
   B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说Io读写次数也就降低了。
2. B+树的查询效率更加稳定
   由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一数据的查询效率相当。

1.5 为什么平时用到的B+树都不会超过4层

因为数层越低，I/O次数越少。
比如4层，那每查询一个数据，最多进行4次I/O操作。

1.6 聚簇索引

特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
   ①页内的记录是按照主键的大小顺序排成一个单向链表。
   ②各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
   ③存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
2. B+树的叶子节点存储的是完整的用户记录。
   所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

  我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MysQL语句中显式的使用INDEX语句去创建,InnoDB存储引擎会自动的为我们创建聚簇索引。
  聚簇索引索引和数据存放在一个文件。

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
• 聚簇索引对于主键的排序查找和范围查找速度非常快。
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

限制：

• 对于MysQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引。
• 由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。
• 如果没有定义主键，Innodb会选择非空的唯一索引代替。如果没有这样的索引，Innodb会隐式的定义一个主键来作为聚簇索引。
• 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

1.7 二级索引(辅助索引、非聚簇索引)

  聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢?我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一棵B+树。

回表:我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树！

1.8 联合索引（属于非聚簇索引）

  我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引（但是只建立一颗B+树），比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义:

• 先把各个记录和页按照c2列进行排序。
• 在记录的c2列相同的情况下，采用c3列进行排序。

1.9 聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别:

1. 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
2. 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
3. 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入、删除、更新等操作，效率会比非聚簇索引低。

1.10思考题:B+树的存储能力如何?为何说一般查找行记录，最多只需1~3次磁盘lo

  InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10³。也就是说一个深度为3的B+Tree索引可以维护10³*10³*10³=10亿条记录。(这里假定一个数据页也存储10³条行记录数据了)
  实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在2-4层。MysQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

1.11 lnnoDB的B+树索引的注意事项

根页面位置万年不动
B+树的形成过程:

• 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
• 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
• 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到页a或者b中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是:一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

内节点中目录项记录的唯一性

• B+树索引的叶子节点中记录的内容是索引列＋主键
• B+树索引的内节点中目录项记录的内容是索引列＋页号+主键

一个页面最少存储2条记录

不建议使用过长的字段作为主键，因为所有二级索引都引用主键索引，过长的主键索引会令二级索引变得过大。

用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时，数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

1.12 MyISAM与InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

1. 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。
2. InnoDB的数据文件本身就是索引文件（.ibd文件即包含索引又包含数据），而MyISAM 索引文件和数据文件（.MYI文件存储索引，.MYD文件存储数据）是分离的，索引文件仅保存数据记录的地址。
3. InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
4. MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
5. InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
6. MyISAM在索引检索的时候，⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“⾮聚簇索引”。InnoDB在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂。

1.13 索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

• 空间上的代价
  每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。
• 时间上的代价
  每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

1.14 索引的分类

• 从功能逻辑上说（4种）：普通索引、唯一索引、主键索引、全文索引。
• 按照物理实现方式（2种）：聚簇索引和非聚簇索引。
• 按照作用字段个数进行划分（2种）：单列索引和多列(组合、联合)索引。

唯一性索引:使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。
主键索引:主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。
多列(组合、联合)索引：最左匹配原则。

1.15 创建索引

1.15.1 创建表的时候创建索引

隐式：主键、UNIQUE、外键会自动创建索引
显示：基本语法格式如下：

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

例如：

CREATE TABLE book(
id INT ,
name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
#声明普通索引
INDEX yp(year_publication),
#声明组合索引
INDEX multi_idx(name, authors),
);

• UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
• INDEX（推荐）与KEY为同义词，两者的作用相同，用来指定创建索引；
• index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
• col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• ASC或DESC指定升序或者降序的索引值存储。

注：修改主键索引时必须先删除(drop)原索引，再新建(add)索引。

ALTER TABLE student drop PRIMARY KEY;  

1.15.2 在已经存在的表上创建索引

1. 使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

如：

ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name) ;

2. 使用CREATE INDEX创建索引。在MySQL中， CREATE INDEX被映射到一个ALTER TABLE语句上。

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

如：

CREATE INDEX uk_idx_bname ON book6(book_name);

1.16 查看索引

1. SHOW CREATE TABLE book;

2. SHOW INDEX FROM book;

1.17 删除索引

1. 使用ALTER TABLE删除索引

ALTER TABLE table_name DROP INDEX index_name;

2. 使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

注意：
①添加AUTO_INCREMENT约束字段的唯一索引不能被删除。
②删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

1.18 索引的设计原则

1. 字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2. 频繁作为 WHERE 查询条件的字段
3. 经常 GROUP BY 和 ORDER BY 的列
   如果针对不同列既有GROUP BY又有ORDER BY ，那么可以建立联合索引，联合索引是最左匹配原则，一定要GROUP BY的字段在左，ORDER BY 的字段在右。
4. UPDATE、DELETE 的 WHERE 条件列
   如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
5. DISTINCT 字段需要创建索引
6. 多表 JOIN 连接操作时，创建索引注意事项
   首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
   其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
   最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致(不一致会用函数转换类型，索引失效)。比如course_id在student_info表和course表中都为int(11)类型，而不能一个为int另一个为varchar类型。
7. 使用列的类型小的创建索引
   ①数据类型越小，在查询时进行的比较操作越快。
   ②数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘工/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
8. 使用字符串前缀创建索引
   如果使用很长的字符串做索引，缺点：
   ①B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
   ②如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

索引列前缀对排序的影响:
如果使用了索引列前缀，比方说前边只把address列的前12个字符放到了二级索引中
下边这个查询可能就有点儿尴尬了:
SELECT * FROM shop ORDER BY address LIMIT 12;
因为二级索引中不包含完整的address列信息，所以无法对前12个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。

注意：Alibaba《Java开发手册》【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinctleft(列名,索引长度))/count(*)的区分度来确定。

9. 区分度高(散列性高)的列适合作为索引
   比如，学号区分度高，性别区分度低
10. 使用最频繁的列放到联合索引的左侧
11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

1.19 限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因:

1. 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
2. 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时索引也会进行调整和更新，会造成负担。
3. 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

1.20 不适合创建索引的情况

1. 在where中使用不到的字段，不要设置索引
2. 数据量小的表最好不要使用索引
3. 有大量重复数据的列上不要建立索引
   比如性别：只有男女
   当数据重复度大，比如高于10%的时候，也不需要对这个字段使用索引.
4. 避免对经常更新的表创建过多的索引
5. 不建议用无序的值作为索引
   例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引

2.事务

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

2.1 事物的四⼤特性(ACID)

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

ACID是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的。

2.2 并发事务带来的问题

• 脏读（Dirtyread）:当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Losttomodify）:指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据，那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。这样第⼀个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）:指在⼀个事务内多次读同⼀数据。在这个事务还没有结束时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读。
• 幻读（Phantomread）:幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

不可重复读和幻读区别：不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除⽐如多次读取⼀条记录发现记录增多或减少了。

2.3事务隔离级别,MySQL的默认隔离级别

SQL标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
• REPEATABLE-READ(可重复读)：对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
• SERIALIZABLE(可串⾏化)：最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）。

注意：与SQL标准不同的地⽅在于InnoDB存储引擎在REPEATABLEREAD（可重读）事务隔离级别下使⽤的是Next-KeyLock锁算法，因此可以避免幻读的产⽣，这与其他数据库系统(如SQLServer)是不同的。所以说InnoDB存储引擎的默认⽀持的隔离级别是REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了SQL标准的SERIALIZABLE(可串⾏化)隔离级别。因为隔离级别越低，事务请求的锁越少，所以⼤部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容)，但是你要知道的是InnoDB存储引擎默认使⽤REPEAaTABLE-READ（可重读）并不会有任何性能损失。

InnoDB存储引擎在分布式事务的情况下⼀般会⽤到SERIALIZABLE(可串⾏化)隔离级别。

3.锁机制

MyISAM和InnoDB存储引擎使⽤的锁：

• MyISAM采⽤表级锁(table-levellocking)。
• InnoDB⽀持⾏级锁(row-levellocking)和表级锁,默认为⾏级锁

表级锁和⾏级锁对⽐：

• 表级锁：MySQL中锁定粒度最⼤的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也⽐较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最低，MyISAM和InnoDB引擎都⽀持表级锁。
• ⾏级锁：MySQL中锁定粒度最⼩的⼀种锁，只针对当前操作的⾏进⾏加锁。⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁。

InnoDB存储引擎的锁的算法有三种：

• Recordlock：单个⾏记录上的锁
• Gap lock：间隙锁，锁定⼀个范围，不包括记录本身
• Next-key lock：record+gap 锁定⼀个范围，包含记录本身

相关知识点：

1. innodb对于⾏的查询使⽤next-key lock
2. Next-locking keying为了解决幻读问题
3. 当查询的索引含有唯⼀属性时，将next-key lock降级为record key
4. Gap锁设计的⽬的是为了阻⽌多个事务将记录插⼊到同⼀范围内，⽽这会导致幻读问题的产⽣
5. 有两种⽅式显式关闭gap锁：（除了外键约束和唯⼀性检查外，其余情况仅使⽤record lock） ①将事务隔离级别设置为RC ②将参数innodb_locks_unsafe_for_binlog设置为1

4.⼤表优化

限定数据的范围
务必禁⽌不带任何限制数据范围条件的查询语句。⽐如：我们当⽤户在查询订单历史的时候，我们可以控制在⼀个⽉的范围内；

读/写分离
经典的数据库拆分⽅案，主库负责写，从库负责读；

垂直分区
根据数据库⾥⾯数据表的相关性进⾏拆分。例如，⽤户表中既有⽤户的登录信息⼜有⽤户的基本信息，可以将⽤户表拆分成两个单独的表，甚⾄放到单独的库做分库。
简单来说垂直拆分是指数据表列的拆分，把⼀张列⽐较多的表拆分为多张表。如下图所示，这样来说⼤家应该就更容易理解了。

垂直拆分的优点：可以使得列数据变⼩，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应⽤层进⾏Join来解决。此外，垂直分区会让事务变得更加复杂。

⽔平分区
保持数据表结构不变，通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同的表或者库中，达到了分布式的⽬的。⽔平拆分可以⽀撑⾮常⼤的数据量。⽔平拆分是指数据表⾏的拆分，表的⾏数超过200万⾏时，就会变慢，这时可以把⼀张的表的数据拆成多张表来存放。举个例⼦：我们可以将⽤户信息表拆分成多个⽤户信息表，这样就可以避免单⼀表数据量过⼤对性能造成影响。

⽔平拆分可以⽀持⾮常⼤的数据量。需要注意的⼀点是：分表仅仅是解决了单⼀表数据过⼤的问题，但由于表的数据还是在同⼀台机器上，其实对于提升MySQL并发能⼒没有什么意义，所以⽔平拆分最好分库。
⽔平拆分能够⽀持⾮常⼤的数据量存储，应⽤端改造也少，但分⽚事务难以解决，跨节点Join性能较差，逻辑复杂。《Java⼯程师修炼之道》的作者推荐尽量不要对数据进⾏分⽚，因为拆分会带来逻辑、部署、运维的各种复杂度，⼀般的数据表在优化得当的情况下⽀撑千万以下的数据量是没有太⼤问题的。如果实在要分⽚，尽量选择客户端分⽚架构，这样可以减少⼀次和中间件的⽹络I/O。

数据库分⽚的两种常⻅⽅案：

• 客户端代理：分⽚逻辑在应⽤端，封装在jar包中，通过修改或者封装JDBC层来实现。当当⽹的Sharding-JDBC、阿⾥的TDDL是两种⽐较常⽤的实现。
• 中间件代理：在应⽤和数据中间加了⼀个代理层。分⽚逻辑统⼀维护在中间件服务中。Mycat、360的Atlas、⽹易的DDB等等都是这种架构的实现。

详细请点击：MySQL大表优化方案

5.池化设计思想及数据库连接池

我们常⻅的如java线程池、jdbc连接池、redis连接池等就是池化设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。除了初始化资源，池化设计还包括如下这些特征：池⼦的初始值、池⼦的活跃值、池⼦的最⼤值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。

数据库连接本质就是⼀个socket的连接。数据库服务端还要维护⼀些缓存和⽤户权限信息之类的所以占⽤了⼀些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重⽤这些连接。为每个⽤户打开和维护数据库连接，尤其是对动态数据库驱动的⽹站应⽤程序的请求，既昂贵⼜浪费资源。**在连接池中，创建连接后，将其放置在池中，并再次使⽤它，因此不必建⽴新的连接。如果使⽤了所有连接，则会建⽴⼀个新连接并将其添加到池中。**连接池还减少了⽤户必须等待建⽴与数据库的连接的时间。

6.分库分表之后,id主键如何处理

要是分成多个表之后，每个表都是从1开始累加，这样是不对的，我们需要⼀个全局唯⼀的id来⽀持。
⽣成全局id有下⾯这⼏种⽅式：

• UUID：不适合作为主键，因为太⻓了，并且⽆序不可读，查询效率低。⽐较适合⽤于⽣成唯⼀的名字的标示⽐如⽂件的名字。
• 数据库⾃增id:两台数据库分别设置不同步⻓，⽣成不重复ID的策略来实现⾼可⽤。这种⽅式⽣成的id有序，但是需要独⽴部署数据库实例，成本⾼，还会有性能瓶颈。
• 利⽤redis⽣成id:性能⽐较好，灵活⽅便，不依赖于数据库。但是，引⼊了新的组件造成系统更加复杂，可⽤性降低，编码更加复杂，增加了系统成本。
• Twitter的snowflake算法：https://github.com/twitter-archive/snowflake。
• 美团的Leaf分布式ID⽣成系统：Leaf是美团开源的分布式ID⽣成器，能保证全局唯⼀性、趋势递增、单调递增、信息安全，⾥⾯也提到了⼏种分布式⽅案的对⽐，但也需要依赖关系数据库、Zookeeper等中间件。https://tech.meituan.com/2017/04/21/mt-leaf.html

附：
一条SQL语句在MySQL中如何执行的
一条SQL语句执行得很慢的原因有哪些
MySQL高性能优化规范建议
书写高质量SQL的30条建议