[大数据]MySQL面经

前言

面试数据库基础知识常见题

一、为什么要使用数据库？

1、数据保存在内存中

• 优点：存取速度快
• 缺点：数据不能永久保存

2、数据保存在文件中

• 优点：数据永久保存
• 缺点：1.数据比内存操作慢，频繁的IO操作。2.查询数据不方便

3、数据保存在数据库中

• 数据永久保存
• 使用SQL语句，查询方便效率高
• 管理数据方便

二、什么是SQL? 什么是MySQL?

1.什么是SQL？

结构化查询语言（Structure Query Language）简称SQL，是一种数据库查询语言。
作用：用于存取数据、查询、更新和管理关系型数据库系统。

2.什么是MySQL？

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS(Relational Database Management System，关系数据库管理系统)应用软件之一。在Java企业级开发中非常常用，因为MySQL是开源免费的，并且方便扩展。

三、数据库的三大范式是什么？

1. 第一范式：确保数据库表的原子性。即数据库表的每一列都不可以再拆分。
2. 第二范式：在满足第一范式的基础上，还要包含两部分内容，一是表必须有一个主键，二是非主键列必须完全依赖于主键，而不能只依赖主键的一部分。
   例子：
   假定选课关系表为Student_Course(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，主键为(学号, 课程名称)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。可以拆分成三个表：学生：Student(学号, 姓名, 年龄)；课程：Course(课程名称, 学分)；选课关系：Student_Course_Relation(学号, 课程名称, 成绩)。
3. 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
   例子：假定学生关系表为Student(学号, 姓名, 年龄, 学院id, 学院地点, 学院电话)，主键为"学号"，其中学院id依赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不符合第三范式。可以把学生关系表分为如下两个表：学生：(学号, 姓名, 年龄, 学院id)；学院：(学院,id 地点, 电话)。
4. 第二范式和第三范式的区别？
   第二范式的依据是非主键列是否完全依赖于主键，还是依赖于主键的一部分。
   第三范式的依据是非主键列是直接依赖于主键，还是直接依赖于非主键。

四、MySQL有哪些字段类型？

整数类型，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字
节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整
数。

实数类型，包括FLOAT、DOUBLE、DECIMAL。DECIMAL可以用于存储比BIGINT还大的整型，能存储
精确的小数。而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。

字符串类型，包括VARCHAR、CHAR、TEXT、BLOBVARCHAR用于存储可变长字符串，它比定长类型更节省空间。

枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。有时可以使用ENUM代替常用的字符
串类型。ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。ENUM在内部存储时，其实存的是
整数。尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。排序是按照内部存储的整数

ENUM和SET都是比较特殊的字符串数据列类型，它们的取值范围是一个预先定义好的列表。ENUM或SET数据列的取值只能从这个列表中进行选择。ENUM和SET的主要区别是：
ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。例如：ENUM(“N”,“Y”)表示，该数据列的取值要么是"Y"，要么就是"N"。SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。
ENUM和SET的值是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。
ENUM的合法取值列表中的字符串被按声明定义的顺序被编号，从1开始。SET的编号不是按顺序进行编号的，SET中每一个合法取值都对应着SET值里的一个位。第一个合法取值对应0位，第二个合法取值对应1位，以此类推，如果数值形式的SET值等于0,则说明它是一个空字符串，如果某个合法的取值出现在SET数据列里，与之对应的位就会被置位；如果某个合法的取值没有出现在SET数据列里，与之对应的位就会被清零。正因为SET值与位有这样的对应关系，所以SET数据列的多个合法取值才能同时出现并构成SET值。

日期和时间类型，尽量使用timestamp，空间效率高于datetime，用整数保存时间戳通常不方便处理。
如果需要存储微妙，可以使用bigint存储。
关于 mysql 的数据类型主要以上几种，一般我们创建表结构都是使用 innodb 引擎，特别我们需要注意的是，为了获取更好的兼容性，建议使用 utf8mb4 字符集，主要是用来兼容四字节的 unicode。

1.utf8mb4 与 utf8 的区别

mysql 在 5.5.3 版本之后增加了 utf8mb4 编码，mb4 就是 most bytes 4 的意思，专门用来兼容四字节的 unicode。其实，utf8mb4 是 utf8 的超集，理论上原来使用 utf8，然后将字符集修改为 utf8mb4，也不会对已有的 utf8 编码读取产生任何问题。mysql 支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就插入异常。

2.char 和 varchar 区别

char 表示定长，长度固定，varchar 表示变长，即长度可变。char 如果插入的长度小于定义长度时，则用空格填充；varchar 小于定义长度时，还是按实际长度存储，插入多长就存多长。

char 的存取速度还是要比 varchar 要快得多，方便程序的存储与查找；但是 char 也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar 则刚好相反，以时间换空间。

对 char 来说，最多能存放的字符个数 255，和编码无关。而 varchar 呢，最多能存放 65532 个字符。varchar 的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532 字节。

3.timestamp 和 datatime 区别

timestamp 占 4 个字节。datetime 占用 8 个字节

timestamp 记录的时间范围是：‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC，受时区影响。datetime 不受时区影响，时间范围:‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’

timestamp 存储占用的空间和 INT 类型相同，客户端插入的时间从当前时区转化为 UTC，查询时，将其又转化为客户端当前时区进行返回。datetime，不做任何改变，基本上是原样输入和输出。

五、事务的隔离级别有哪些？

1. 什么是事务？

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

2. 事务的特性有哪些？

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。

一致性（consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的。

隔离性（ioslation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的。

持久性（durability）：一个事务被提交之后，对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3.隔离所导致的问题有哪些？

脏读：指一个事务读取了另外一个事务未提交的数据。未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读

不可重复读：前后多次读取，数据内容不一致。一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。

幻读：前后多次读取，数据总量不一致。一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。(如果时删除一行数据就不是幻读，只能是插入数据)

4.MySql 创建事务方式

START TARNSACTION | BEGIN：显式地开启一个事务。
COMMIT：提交事务，使得对数据库做的所有修改成为永久性。
ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。
mysql 默认的事务隔离级别是：可重复读。

5.隔离级别有哪些？

MySQL数据库为我们提供的四种隔离级别：

1. Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
2. Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
3. Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
4. Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

但是在MySql也解决了幻读.就是因为MVCC。

查看隔离级别：

select @@transaction_isolation;

设置隔离级别：

set session transaction isolation level read uncommitted;

六、索引（重要）

1.什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。
优点：

1. 降低数据库的IO成本，这也是建立索引最主要的原因。
2. 可以创建唯一索引，保证数据库中表数据的唯一性。
3. 在实现数据的参考完整性方面，可以加速表与表之间的连接。换句话说，对于子表和父表联合查询时可以提高查询速度。
4. 在使用分组和排序子句进行查询时，可以显著的减少查询中分组和排序的时间,减少CPU消耗

缺点：

1. 创建和维护索引需要耗费时间,随着数据的增加，耗费的时间也随之增加。
2. 创建索引需要占用磁盘空间, 除了数据表占据数据空间之外，每个索引还要占用一定的物理空间，存储在磁盘上如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
3. 虽然索引大大提高了查询速度，同时却也会降低更新表的速度，当对表中的数据进行增加、删除、修改的操作，索引也要动态维护，这样就降低了数据的维护速度。

2.索引有什么作用？或者问为什么使用索引？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2~4层，最多只需要读取2-4次磁盘，查询速度大大提升。

3.创建索引？

3.1哪些情况需要创建索引？

1.经常用于查询的字段
2.经常用于连接的字段建立索引，可以加快连接的速度
3.经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度

1. 字段的数值有唯一性限制
   索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引，这样就可以更快速的通过该索引来确定某条记录。
2. 频繁作为WHERE查询条件的字段
   某个字段在SELECT语句的 WHERE条件中经常用到，那么就需要这个字段创建索引了，尤其是在数据量大的情况下，创建普通的索引就可以大幅度提升数据的查询效率。
3. 经常GROUP BY和ORDER BY的列
   索引就是让数据按照某种顺序进行存储或检索，因此当我们使用GROUP BY对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引，如果待排序的列有多个，那么可以在这些列上建立组合索引。
4. UPDATE DELETE 的WHERE条件列
5. DISTINCT字段需要创建索引
6. 区分度高的列适合作为索引
7. 使用最频繁的列放到联合索引的左侧（最左前缀原则）
8. 在多个字段都要创建索引的情况下，联合索引优于单值索引。

3.2 多表JOIN连接操作时,创建索引注意事项

1. 连接表的数量尽量不要超过3张,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长会非常快,严重影响查询的效率。
2. 对WHERE条件创建索引,因为 WHERE才是对数据条件的过滤。如果在数据量非常大的情况下,没有WHERE条件过滤是非常可怕的。
3. 对用于连接的字段创建索引,并且该字段在多张表中的类型必须一致。比如 course_id在 student_info表和course表中都为int(11)类型,而不能一个为int另一个为 varchar类型。

3.3. 使用列的类型小的创建索引原因是什么？

这里的类型小指的就是该类型表示的数据范围的大小。
我们在定义表结构的时侯要显式的指定列的类型,以整数类型为例,有 TINYINT、 MEDIUMINT、INT、BIGINT等,它们占用的存储空间依次递增,能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话,在表示的整数范围允许的情況下,尽量让索引列使用较小的类型,比如我们能使用INT就不要使用 BIGIINT,能使用 MEDIIUMINT就不要使用NT。
原因是：
1.数据类型越小,在査询时进行的比较操作越快。
2.数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录,从而减少磁盘I/0带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。
这个建议对于表的主键来说更加适用,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键使用更小的数据类型,也就意味着节省更多的存储空间和更高效的I/O。

3.4. 使用字符串前缀创建索引原因是什么？

假设我们的字符串很长,那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时,那就意味着在对应的B+树中有这么两个问题：
1.B+树索引中的记录需要把该列的完整字符串存储起来,更费时。而且字符串越长,在索引中占用的存储空间越大。
2.如果B+树索引中索引列存储的字符丰很长|那在做字符比较时会占用更多的时间
我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置,但是能定位到相应前缀所在的位置,然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间,又减少了字符串的比较时间，还大体能解决排序的问题。

列的基数指的是某一列中不重复数据的个数,比方说某个列包含值2,5,8,2,5,8,2,5,8,虽然有9条记录,但该列的基数却是3。也就是说,在记录行数一定的情况下,列的基数越大,该列中的值越分散;列的基数越小,该列中的值越集中。这个列的基数指标非常重要,直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引,为基数太小列的建立索引效果可能不好。

4.什么情况下不适合建立索引？

1. where条件中用不到的字段不适合建立索引
2. 数据量小的表最好不要创建索引
3. 有大量重复数据的列上不要建立索引
4. 避免对经常更新的表创建过多的索引
   第一层含义:频繁更新的字段不一定要创建索引。因为更新数据的时候,也需要更新索引,如果索引太多,在更新索引的时候也会造成负担,从而影响效率。
   第二层含义:避免对经常更新的表创建过多的索引,并且索引中的列尽可能少。此时,虽然提高了查询速度,同时却会降低更新表的速度。
5. 不建议使用无序的值作为索引
6. 参与列计算的列不适合建索引
7. 区分度不高的字段不是适合建立索引，如性别等

5.索引有哪些分类？

1. 主键索引：名为primary的唯一非空索引，不允许有空值。
2. 唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：UNIQUE 约束的列可以为null且可以存在多个null值。UNIQUE KEY的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的SQL语句如下：

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3. 组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀原则。

4. 全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

6.什么是最左匹配原则？

如果SQL语句中用到了组合索引中的最左边的索引，那么这条SQL语句就可以利用这个组合索引去进行匹配。当遇到查询（>、<、between、like）就会停止匹配，后面的字段不会用到索引。
对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。
对(a,b,c,d)建立索引，查询条件为 a = 1 and b = 2 and c > 3 and d = 4 ，那么，a,b,c三个字段能
用到索引，而d就匹配不到。因为遇到了范围查询！
如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当a相等时，会
对b进行比较排序）。直接执行 b = 2 这种查询条件没有办法利用索引。
从局部来看，当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a=2时候，b的值为1,4也是有序状态。 因此，你执行 a = 1 and b = 2 是a,b字段能用到索引的。而你执行 a > 1 and b = 2 时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。

7.什么聚簇（聚集）索引？

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

术语聚簇表示数据行与相邻的键值聚簇的存储在一起

特点：
使用记录主键值的大小进行记录和页的排序包括三个方面的含义

1. 页内的记录是按照主键的大小顺序排成一个单向链表。
2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

对于InnoDB来说，聚簇索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

优点：

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

缺点：

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

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

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

8.什么是覆盖索引?

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，换句话说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL只能使用b+树索引做覆盖索引。

9.什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

建立前缀索引的方式：

// email列创建前缀索引 
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

10.索引的设计原则？

1. 索引列的区分度越高，索引的效果就越好。
2. 尽量使用短索引，对于较长的字符串进行索引是应该指定一个较短的前缀长度。
3. 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
4. 利用最左前缀原则。
5. 对于有外键的数据列一定要建立索引。

11.索引什么时候失效？

1. 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引。
2. 以%开头的like查询，如%abc，无法使用索引；非%开头的like查询，如abc%，相当于范围查询，会使用索引。
3. 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，是索引失效。
4. 判断索引列是否不等于某个值的时候。
5. 对索引列进行运算
6. 查询条件使用or连接，也会导致索引失效。

12.创建索引的3种方式

第一种方式：在执行CREATE TABLE时创建索引

CREATE TABLE user_index2(id INT auto_increment PRIMARY KEY,first_name VARCHAR(16), last_name VARCHAR(16),id_card VARCHAR(18),information text, KEY name(first_name,last_name), FULLTEXTKEY(information),UNIQUEKEY(id_card));

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name(column_list);
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分
隔。
索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。
另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name(column_list);
CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARYKEY索引）

删除索引：

ALTER TABLE table_name DROP INDEX index_name;(语法格式)

13.索引的数据结构？

索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

13.1 B+树索引

B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的数据项。

在第0层（最底层） 中存放这具体数据，数据与数据之间为单向链表，页与页之间为双向链表。

B+Tree树：不论是存放用户记录的数据，还是存放目录项记录的数据页，我们都把他们放在b+树这个数据结构中，所以我们也这些数据页为节点，我们的实际用户记录其实都存放在b+树最底层的节点上，这些节点也称之为叶子节点，其余用来存放目录项的节点称之为非叶子节点或者内节点，其中B+树最上面的节点称为跟节点。

B+Tree树节点可以分为很多层，规定最下面的那层，也就是存放记录的第0层，之后依次往上加。

假设：所有存放记录的叶子节点能存放100条用户记录，所有存放目录项记录的内节点存放100条目录项，那么：

通常在一般情况下，我们用到的B+树不会超过4层.，节点层越高I/O 次数越多。

MySQL 数据库使用最多的索引类型是BTREE索引，底层基于B+树数据结构来实现。

13.2 哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

13.3 哈希索引与B+索引的区别

1. 哈希索引不支持排序，因为哈希表是无序的
2. 哈希索引不支持范围查找
3. 哈希索引不支持模糊查询和多列索引的最左前缀匹配
4. 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

13.4 为什么B+树比B树更适合实现数据库索引？

1. 由于B+树的数据都是存储在叶子节点中，叶子节点均为索引，方便扫库，只需要扫一遍叶子节点即可，但是B树因为其分支节点同样存储这数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合区间查询的情况，而在数据库中基于范围的查询时非常频繁的，所以通常B+树用于数据库的索引。
2. B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。
3. B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

七、存储引擎

1.常见的存储引擎有哪些？

MySQL中常用的四种存储引擎分别是： MyISAM存储引擎、InnoDB存储引擎、MEMORY存储引擎、ARCHIVE存储引擎。MySQL 5.5版本后默认的存储引擎为InnoDB。

2.InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。
优点：支持事务和崩溃修复能力。InnoDB引入了行级锁和外键约束。
缺点：占用的数据空间相对较大。
适用场景：需要事务支持，并且有较高的并发读写频率。

3.MyISAM存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

优点：访问速度快。

缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

适用场景：对事务完整性没有要求；只读的数据，或者表比较小，可以忍受修复repair操作。

MyISAM特性：

1. MyISAM对整张表加锁，而不是针对行。读取数据时会对需要读到的所有表加共享锁，写入时则对
   表加排它锁。但在读取表记录的同时，可以往表中插入新的记录（并发插入）。

2. 对于MyISAM表，MySQL可以手动或者自动执行检查和修复操作。执行表的修复可能会导致数据丢
   失，而且修复操作非常慢。可以通过 CHECK TABLE tablename 检查表的错误，如果有错误执行
   REPAIR TABLE tablename 进行修复。

4.MEMORY存储引擎(了解即可)

MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

优点：访问速度较快。

缺点：

1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。
2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
3. 只支持等值比较，不支持范围查询。
4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的
   行。

5.MyISAM和InnoDB的区别？（重要）

1. 是否支持行级锁 : MyISAM 只有表级锁，而 InnoDB 支持行级锁和表级锁，默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复： MyISAM 注重性能，每次查询具有原子性，其执行速度比InnoDB 类型更快，但是不提供事务支持。而 InnoDB 提供事务支持，具有事务、回滚和崩溃修复能力。
3. 是否支持外键： MyISAM 不支持，而 InnoDB 支持。
4. 是否支持MVCC ： MyISAM 不支持， InnoDB 支持。应对高并发事务，MVCC比单纯的加锁更高效。
5. MyISAM 不支持聚集索引， InnoDB 支持聚集索引。

MyISAM 引擎主键索引和其他索引区别不大，叶子节点都包含索引值和行指针。
InnoDB 引擎二级索引叶子存储的是索引值和主键值（不是行指针），这样可以减少行移动和
数据页分裂时二级索引的维护工作。

八、多版本并发控制（MVCC）

1.什么是MVCC?

MVCC就是多版本并发控制。MVCC是通过数据行的多个版本管理来实现数据库的并发控制，这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

MVCC的实现是依赖于：隐藏字段，Undo Log, Read View。

2.快照读与当前读

MVC在 MySQL InnoDB中的实现主要是为了提高数据库并发性能,用更好的方式去处理读-写冲突,做到即使有读写沖突时,也能做到不加锁,非阻塞并发读,而这个读指的就是快照读,而非当前读。当前读实际上是一种加锁的操作,是悲观锁的实现。而MCC本质是采用乐观锁思想的一种方式。
快照读：又叫一致性读，读取的是快照数据，不加锁的简单的select都属于快照读。
之所以出现快照读的情況,是基于提高并发性能的考虑,快照读的实现是基于MVCC,它在很多情況下,避免了加锁操作,降低了开销。既然是基于多版本,那么快照读可能读到的并不一定是数据的最新版本,而有可能是之前的历史版本
快照读的前提是隔离级別不是串行级別,串行级别下的快照读会退化成当前读。

当前读： 当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他的并发事务不能修改当前的记录，会对当前读取的记录进行加锁。加锁的select,或者对数据进行增删改都会进行当前读。

3.隐藏字段与Undo Log版本链

4.MVCC实现原理之ReadView

4.1 什么是ReadView?

4.2 设计思路

4.3 ReadView规则

5.MVCC整体操作流程

6.总结

innodb 的 MVCC 是如何工作的？

事务版本号：每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号），这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。

行记录的隐藏列：innodb 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段：

• DB_TRX_ID(6 字节)： 它是最近一次更新或者插入或者删除该行数据的事务 ID(若是删除，则该行有一个删除位更新为已删除。但并不是真正的进行物理删除，当 InnoDB 丢弃为删除而编写的更新撤消日志记录时，它才会物理删除相应的行及其索引记录。此删除操作称为清除，速度非常快)
• DB_ROLL_PTR(7 字节)： 回滚指针，指向当前记录行的 undo log 信息(指向该数据的前一个版本数据)
• DB_ROW_ID(6 字节)： 随着新行插入而单调递增的行 ID。InnoDB 使用聚集索引，数据存储是以聚集索引字段的大小顺序进行存储的，当表没有主键或唯一非空索引时，innodb 就会使用这个行 ID 自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行 ID 了。这个 DB_ROW_ID 跟 MVCC 关系不大。

undo log 将行记录快照保存在里面，我们可以在回滚段中找到它们。

在可重复读的隔离级别下：

查询：符合下面两个条件的记录作为返回结果：1）innodb 只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。2）行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

插入：innodb 为新插入的每一行保存当前系统版本号作为行版本号。

删除：innodb 为删除的每一行保存当前系统版本号作为行删除标识。删除在内部被视为更新，行中的一个特殊位会被设置为已删除。

更新：innodb 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

7.总结

九、锁

1.共享锁

共享锁又称读锁 read lock，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据。

2.排它锁

若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

3.乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即 为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实 现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我 们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

4.悲观锁

在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中synchronized很 相似，共享锁（读锁）和排它锁（写锁）是悲观锁的不同的实现。
要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

set autocommit=0;

5.表级锁

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的

6.行级锁

行锁又分共享锁和排他锁,由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？

只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

7.死锁

所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：
第一种：

1. 查询是否锁表

show OPEN TABLES where In_use > 0;

2. 查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3. 杀死进程id（就是上面命令的id列）

kill id

第二种：

1. 查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. 查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3. 查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

4. 杀死进程id（就是上面命令的id列）

kill id

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。

产生死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁：
（1）按同一顺序访问对象。
（2）避免事务中的用户交互。
（3）保持事务简短并在一个批处理中。
（4）使用低隔离级别。
（5）使用绑定连接。

十、大表如何优化？

某个表有近千万数据，查询比较慢，如何优化？

当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围。比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内；
2. 读写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
3. 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分。

十一、MySQL的整体架构？

mysql 整体架构大概可以分为：网络连接层、服务层、存储引擎层和系统文件层。

1）网络连接层

Connectors 组件，是 mysql 向外提供的交互组件，如 java,.net,php 等语言可以通过该组件来操作 SQL 语句，实现与 SQL 的交互。

2）服务层

服务层是 mysql Server 的核心。主要包含系统管理和控制工具、连接池、SQL 接口、解析器、查询优化器和缓存 Cache&Buffer 六个部分。

连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。

系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群 管理等

SQL 接口（SQL Interface）：用于接受客户端发送的各种 SQL 命令，并且返回用户需要查询的结果。

解析器（Parser）：负责将请求的 SQL 解析生成一个"解析树"。然后根据一些 mysql 规则进一步检查解析树是否合法。

查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

缓存（Cache&Buffer）：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

3）存储引擎层（Pluggable Storage Engines）

存储引擎负责 MySQL 中数据的存储与提取，与底层系统文件进行交互。MySQL 存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是 MyISAM 和 InnoDB。

4）系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

十二、MySQL 的查询和插入的执行流程

下面我们图解 MySQL 一条查询语句是怎么运行的：

1. MySQL 客户端对 MySQL server 的监听端口发起请求
2. 在连接池组件创建连接，分配线程，并验证用户名，密码，库表>权限。
3. 查询 query_cache，如果有数据直接返回，没有则继续执行。
4. 通过 SQL接口组件接收 SQL语句，SQL会通过查询分析器分解成数据结构，并且这个结构传递给后续步骤
5. 查询优化器组件组成查询路径树，并选举一条最优的查询路径。
6. 调用存储引擎接口，打开表，执行查询，检查存储引擎缓存中是否有对应的缓存记录，如果没有就继续往下执行。
7. 到磁盘物理文件中寻找数据。
8. 当查询到所需要的数据之后，先写入存储引擎缓存中，并往 query_cache 写进去。
9. 返回数据给客户端。
10. 关闭表。
11. 关闭线程。
12. 关闭连接。

MySQL 插入的过程如下：

1. MySQL客户端对 MySQL server 的监听端口发起请求
2. 在连接池组件创建连接，分配线程，并验证用户名，密码，库表>权限。
3. 检查没有问题之后，便进入引擎层开始正式的提交。我们知道 InnoDB 会将数据页缓存至内存中的 buffer pool，所以 insert 语句到了这里并不需要立刻将数据写入磁盘文件中，只需要修改 buffer pool 当中对应的数据页就可以了。
4. 在开启 redo log 刷盘策略的时候，当 innodb_flush_log_at_trx_commit=1 时，每次事务提交都会触发一次 redo log 刷盘。（redo log 是顺序写入，相比直接修改数据文件，redo 的磁盘写入效率更加高效）
5. 如果开启了 binlog 日志，我们还需将事务逻辑数据写入 binlog 文件，且为了保证复制安全，建议使用 sync_binlog=1 ，也就是每次事务提交时，都要将 binlog 日志的变更刷入磁盘。
6. 返回数据给客户端。
7. 关闭表。
8. 关闭线程。
9. 关闭连接。

更新流程：
更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log(prepare 状态)、bin log、redo log(commit状态)
举个例子，更新语句如下：

update user set name = 'FYP' where id = 1;

1. 先查询到 id 为1的记录，有缓存会使用缓存。
2. 拿到查询结果，将 name 更新为 大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保
   存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态。
3. 执行器收到通知后记录 bin log，然后调用引擎接口，提交 redo log 为提交状态。
4. 更新完成。

为什么记录完 redo log，不直接提交，先进入prepare状态？
假设先写 redo log 直接提交，然后写 bin log，写完 redo log 后，机器挂了，bin log 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bin log 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

十三、 bin log和redo log有什么区别？

1. bin log会记录所有日志记录，包括innoDB、MyISAM等存储引擎的日志；redo log只记录innoDB
   自身的事务日志。
2. bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写
   入磁盘。
3. binlog 是逻辑日志，记录的是SQL语句的原始逻辑；redo log 是物理日志，记录的是在某个数据页
   上做了什么修改。

十四、什么是MySQL的主从复制？（重要）

mysql 主从复制是指数据可以从一个 mysql 数据库服务器主节点复制到一个或者多个从节点。mysql 默认使用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

为什么要主从复制？

1. 读写分离，使数据库能支撑更大的并发。
2. 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
3. 数据备份，保证数据的安全。

主从复制实现原理

1. master 服务器将数据的改变记录二进制 binlog 日志，当 master 上的数据发生改变时，则将其改变写入二进制日志中。

2. slave 服务器会在一定时间间隔内对 master 二进制日志进行探测其是否发生改变，如果发生改变，则开始一个 I/OThread 请求 master 二进制事件。

3. 同时主节点为每个 I/O 线程启动一个 dump 线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动 SQL 线程从中继日志中读取二进制日志，在本地解析执行，使得其数据和主节点的保持一致，最后 I/O Thread 和 SQL Thread 将进入睡眠状态，等待下一次被唤醒。

基于 gtid 的复制模式
gtid（Global Transaction ID）对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID 实际上 是由 UUID+TID 组成的。其中 UUID 是一个 mysql 实例的唯一标识。TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

gtid 的复制原理

1. 当一个事务在主库端执行并提交时，产生 gtid，一同记录到 binlog 日志中。
2. binlog 传输到 slave,并存储到 slave 的 relaylog 后，读取这个 gtid 的这个值设置 gtid_next 变量，即告诉 Slave，下一个要执行的 gtid 值。
3. sql 线程从 relay log 中获取 gtid，然后对比 slave 端的 binlog 是否有该 gtid。
4. 如果有记录，说明该 gtid 的事务已经执行，slave 会忽略。
5. 如果没有记录，slave 就会执行该 gtid 事务，并记录该 gtid 到自身的 binlog，在读取执行事务前会先检查其他 session 持有该 gtid，确保不被重复执行。
6. 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

十五、MySQL 如何保证写入数据不丢失？

总结：保证 redo log 和 bin log 可以持久化到磁盘，并且确保 MySQL 在异常重启后进行数据恢复。

bin log 的写入机制：

1. 事务执行过程中，先把日志写到 bin log cache（内存）
2. 事务提交的时候（MySQL 客户端执行 commit 指令），再把 bin log cache 中写到 bin log 文件中，并清空 bin log cache
3. 每个线程都有自己的一个 bin log cache，但是共同使用同一份 bin log
4. write 把 bin log cache 写入到文件系统的 page cache，不会真正将数据持久化到磁盘。
5. fsync 才是将数据持久化到磁盘（此时会占用磁盘的 IOPS）

redo log 的写入机制：

1. 事务在执行过程中，生成的 redo log 首先会写到 redo log buffer
2. redo log 会在一些特定条件下写入日志文件
3. write 到磁盘（存储在 Page Cache 中），此时没有实际调用 fsync 写入磁盘
4. 持久化到磁盘，调用了 fsync

十六、mysql 如何分库分表？

分库分表主要解决 IO 瓶颈，CPU 瓶颈。

分库分表：水平分库分表，垂直分库分表等

具体分库分表的方式经验是：日志类的拆分策略是按照日期，另外拆分策略就是 hash 法。

垂直划分:

垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能，但这种方式并没有解决高数据量带来的性能损耗。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点：

1. 主键出现冗余，需要管理冗余列；
2. 会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；
3. 依然存在单表数据量过大的问题。

水平划分:

水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。

优点：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。

缺点：

1. 分片事务一致性难以解决
2. 跨节点join性能差，逻辑复杂
3. 数据分片在扩容时需要迁移

十七、什么是分区表？

分区表是一个独立的逻辑表，但是底层由多个物理子表组成。

当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。

分区的问题？

1. 打开和锁住所有底层表的成本可能很高。当查询访问分区表时，MySQL需要打开并锁住所有的底层
   表，这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，会影响到查询速度。可
   以通过批量操作来降低此类开销，比如批量插入、LOAD DATA INFILE和一次删除多行数据。
2. 维护分区的成本可能很高。例如重组分区，会先创建一个临时分区，然后将数据复制到其中，最后
   再删除原分区。
3. 所有分区必须使用相同的存储引擎。

十八、exists 和 in的区别？

exists 用于对外表记录做筛选。
exists 会遍历外表，将外查询表的每一行，代入内查询进行判断。当 exists 里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A awhere exists(select 1 from B b where a.id = b.id)

in 是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from A where id in(select id from B)

子查询的表大的时候，使用exists可以有效减少总的循环次数来提升速度；当外查询的表大的时候，使用IN可以有效减少对外查询表循环遍历来提升速度。