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的主要区别是：

1. ENUM只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有65535个成员。例如：ENUM(“N”,“Y”)表示，该数据列的取值要么是"Y"，要么就是"N"。SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。
2. ENUM和SET的值是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。
3. 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 呢，最多能存放 65535 个字符。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
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. 区分度不高的字段不是适合建立索引，如性别等

3、4 总结 索引的使用场景有哪些？

• 对于中大型表建立索引非常有效，对于非常小的表，一般全部表扫描速度更快些。
• 对于超大型的表，建立和维护索引的代价也会变高，这时可以考虑分区技术。
• 如何表的增删改非常多，而查询需求非常少的话，那就没有必要建立索引了，因为维护索引也是需要代价的。
• 一般不会出现在where条件中的字段就没有必要建立索引了。
• 多个字段经常被查询的话可以考虑联合索引。
• 字段多且字段值没有重复的时候考虑唯一索引。
• 字段多且有重复的时候考虑普通索引。

5.索引有哪些分类？

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

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

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.什么聚簇（聚集）索引？什么是非聚簇索引？

聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

• 聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。
• 非聚簇索引：将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址。

在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。

在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。

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

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

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

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

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

优点：

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

缺点：

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

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

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

非聚簇索引一定会进行回表查询吗？

上面是说了非聚簇索引的叶子节点存储的是主键，也就是说要先通过非聚簇索引找到主键，再通过聚簇索引找到主键所对应的数据，后面这个再通过聚簇索引找到主键对应的数据的过程就是回表查询，那么非聚簇索引就一定会进行回表查询吗？

答案是不一定的，这里涉及到一个索引覆盖的问题，如果查询的数据在辅助索引上完全能获取到便不需要回表查询。例如有一张表存储着个人信息包括id、name、age等字段。假设聚簇索引是以ID为键值构建的索引，非聚簇索引是以name为键值构建的索引，select id,name from user where name = ‘zhangsan’;这个查询便不需要进行回表查询因为，通过非聚簇索引已经能全部检索出数据，这就是索引覆盖的情况。如果查询语句是这样，select id,name,age from user where name = ‘zhangsan’;则需要进行回表查询，因为通过非聚簇索引不能检索出age的值。那应该如何解决那呢？只需要将索引覆盖即可，建立age和name的联合索引再使用select id,name,age from user where name = ‘zhangsan’;进行查询即可。

所以通过索引覆盖能解决非聚簇索引回表查询的问题。

8.什么是索引覆盖?

索引覆盖就是一个SQL在执行时,可以利用索引来快速查找,并且此SQL所要查询的字段在当前索引对应的字段中都包含了,那么就表示此SQL走完索引后不用回表了,所需要的字段都在当前索引的叶子节点上存在,可以直接作为结果返回了。

回表：

9.什么是前缀索引？

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

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

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

建立前缀索引的方式：

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

这里面有个prefix_length参数很难确定，这个参数就是前缀长度的意思。通常可以使用以下方法进行确定，先计算全列的区分度

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
1

然后在计算前缀长度为多少时和全列的区分度最相似。

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
1

不断地调整prefix_length的值，直到和全列计算出区分度相近。

10.索引的设计原则？

• 最适合索引的列是在where后面出现的列或者连接句子中指定的列，而不是出现在SELECT关键字后面的选择列表中的列
• 索引列的基数越大，索引的效果越好，换句话说就是索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差，因为列的基数最多也就是三种，大多不是男性就是女性。
• 索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = ’2014-05-29’ 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

如何对索引进行优化？
对索引的优化其实最关键的就是要符合索引的设计原则和应用场景，将不符合要求的索引优化成符合索引设计原则和应用场景的索引。

除了索引的设计原则和应用场景那几点外，还可以从以下两方面考虑。

• 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，因为这样无法使用索引。例如select * from table_name where a + 1 = 2
• 将区分度最高的索引放在前面
• 尽量少使用select*

索引的使用场景、索引的设计原则和如何对索引进行优化可以看成一个问题

11.索引什么时候失效？

1. 组合索引不符合最左匹配原则，或者遇到范围条件导致右边索引失效。B+树底层的链表会按照最左列的数据的大小进行排序，当做最左列数据的值相同时，下列的数据值才是有序的。
2. 条件中有or，例如select * from table_name where a = 1 or b = 3
3. 在索引上进行计算会导致索引失效，例如select * from table_name where a + 1 = 2
4. 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设 select * from table_name where a = '1’会使用到索引，如果写成select * from table_name where a = 1则会导致索引失效。
5. 在索引中使用函数会导致索引失效，例如select * from table_name where abs(a) = 1
6. 在使用like查询时以%开头会导致索引失效
7. 索引上使用！、=、<>进行判断时会导致索引失效，例如select * from table_name where a != 1
8. 索引字段上使用 is null/is not null判断时会导致索引失效，例如select * from table_name where a is null

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));
1

第二种方式：使用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允许在单个语句中更改多个表，因此可以在同时创建多个索引。
1
2
3
4
5
6

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

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

删除索引：

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

13.索引的数据结构？（重要）

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

mysql中索引的底层实现，除了B+树之外的其他结构，为什么最后选择了B+树？（2022百度提前批）参考博客

由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。

所以，我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作，因为磁盘 I/O 操作越少，所消耗的时间也就越小。

另外，MySQL 是支持范围查找的，所以索引的数据结构不仅要能高效地查询某一个记录，而且也要能高效地执行范围查找。

所以，要设计一个适合 MySQL 索引的数据结构，至少满足以下要求：

• 能在尽可能少的磁盘的 I/O 操作中完成查询工作；
• 要能高效地查询某一个记录，也要能高效地执行范围查找；

13.1 B+树索引

B+ 树是一种树数据结构，是一个n叉排序树，每个节点通常有多个孩子，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。

B+树是应文件系统所需而出的一种B树的变型树。

1. 有n棵子树的结点中含有n个关键字，每个关键字不保存数据，只用来索引，所有数据都保存在叶子节点。

2. 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3. 所有的非终端结点可以看成是索引部分，结点中仅含其子树（根结点）中的最大（或最小）关键字。

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


Innodb 使用的 B+ 树有一些特别的点，比如：

• B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历；
• B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

进行查找操作时，首先在根节点进行二分查找，找到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树更适合实现数据库索引？（重点）

MySQL 是会将数据持久化在硬盘，而存储功能是由 MySQL 存储引擎实现的，所以讨论 MySQL 使用哪种数据结构作为索引，实际上是在讨论存储引擎使用哪种数据结构作为索引，InnoDB 是 MySQL 默认的存储引擎，它就是采用了 B+ 树作为索引的数据结构。

要设计一个 MySQL 的索引数据结构，不仅仅考虑数据结构增删改的时间复杂度，更重要的是要考虑磁盘 I/0 的操作次数。因为索引和记录都是存放在硬盘，硬盘是一个非常慢的存储设备，我们在查询数据的时候，最好能在尽可能少的磁盘 I/0 的操作次数内完成。

二分查找树虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素，就会导致二分查找树退化成一个链表，此时查询复杂度就会从 O(logn)降低为 O(n)。

为了解决二分查找树退化成链表的问题，就出现了自平衡二叉树，保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树，每个节点只能有 2 个子节点，随着元素的增多，树的高度会越来越高。

而树的高度决定于磁盘 I/O 操作的次数，因为树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作，也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。

但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

• B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
• B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 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.总结

九、锁

9.1 什么是锁？

锁（LOCKING）是最常用的并发控制机构。是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。锁是事务对某个数据库中的资源（如表和记录）存取前，先向系统提出请求，封锁该资源，事务获得锁后，即取得对数据的控制权，在事务释放它的锁之前，其他事务不能更新此数据。当事务撤消后，释放被锁定的资源。
当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象 。

9.2 锁的作用？

锁的主要作用，是提供事务所需的隔离。

隔离确保事务之间不会相互干扰，此外锁提供的隔离性有助于保证数据的一致性。通过锁实现隔离，达到保证数据一致性的效果和目的。没有锁，一致的事务处理也是不可能的，锁机制是为事务隔离性服务的。当事务在对数据库中的某个数据对象进行操作之前，先向数据库系统发出请求，对数据对象进行加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务根据隔离情况决定能或者不能，对此数据对象进行某些指定操作。

1.共享锁

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

2.排它锁

排它锁又称为写锁（(eXclusive lock,简记为X锁)），若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。

3.乐观锁

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

4.悲观锁

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

set autocommit=0;
1

5.表级锁

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

6.行级锁

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

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

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

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

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

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

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

7.死锁

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

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

1. 查询是否锁表

show OPEN TABLES where In_use > 0;
1

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

show processlist
1

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

kill id
1

第二种：

1. 查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
1

2. 查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
1

3. 查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
1

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

kill id
1

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

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

（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上面的条件有一个不满足，就不会发生死锁。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁：

• 超时等待：即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。（缺点：如果回滚的事务更新了很多行，占用了较多的undo log，那么在回滚的时候花费的时间比另外一个正常执行的事务花费的时间可能还要多，就不太合适）；
• wait-for graph（等待图）：死锁碰撞检测，是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息，通过这两个部分信息构造出一张图，在每个事务请求锁并发生等待时都会判断是否存在回路，如果在图中检测到回路，就表明有死锁产生，这时候InnoDB存储引擎会选择回滚undo量最小的事务。

十、大表如何优化？

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

当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

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 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

两阶段分布式事务（2PC）：

从上面可以看出，因为redo log影响主库的数据，binlog影响从库的数据，所以redo log和binlog必须保持一致才能保证主从数据一致，这是前提。MySQL 使用两阶段提交主要解决 binlog 和 redo log 的数据一致性的问题。将事务的提交分成了两个阶段：也就是2PC (tow phase commit)，XA协议就是通过将事务的提交分为两个阶段来实现分布式事务。

redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
redo log 的写入拆成了两个步骤：prepare 和 commit，这就是两阶段提交（2PC）。

1. InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态。
2. 如果前面 prepare 成功，binlog 写盘，将事务日志持久化到 binlog，如果持久化成功，那么InnoDB 事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)。如果在第一阶段发生了错误，则认为事务失败，回撤所有数据库的事务。

十三、 MySQL事务日志

13.1 bin log和redo log有什么区别？

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

13.2 redo日志

参考：https://www.zhihu.com/question/450862540/answer/1797682794

InnoDB存储引擎是以页为单位来管理存储空间的，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问 ，所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页（内存中改了，但是磁盘中没改就叫做脏页）会以一定的频率被刷入磁盘（checkPoint机制），通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降的太快。

为什么需要redo日志？

InnoDB引擎的事务采用了WAL技术，这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log 。当发生宕机数据未刷到磁盘的时候，可以通过redo log来恢复，保证ACID中的D，这就是redo log的作用。

使用redo日志的好处、特点？

好处：

• redo日志降低了刷盘的频率
• redo日志占用的空间非常小，存储表空间ID、页面、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。

特点：

1. redo日志是顺序写入磁盘的
   在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序IO效率比随机快。
2. 事务执行过程中，redo log不断记录
   bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写
   入磁盘。

13.3 Undo日志

redo log日志是事务持久性的保证，undo log是事务原子性的保证，在事务中更新数据的前置操作其实就是要先写入一个undo log。

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

14.1 主从复制

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 将进入睡眠状态，等待下一次被唤醒。

14.2 读写分离

读写分离就是让主库处理事务性查询，从库处理select查询。数据库复制被用来把事务性查询导致的数据变更同步到从库，当然，主库也可以select查询。读写分离最大的作用就是缓解服务器的压力。

读写分离的好处都有哪些？

1. 增加冗余；
2. 缓解服务器的压力；增加机器的处理能力；
3. 对于读应用较多的，应用读写分离是最好的，可以确保写的服务器压力更小，而读的服务器又可以接受点时间上的延迟。

读写分离提高性能的原因？

1. 物理服务器增加；负荷增加；
2. 主库和从库只负责自己的写和读操作，极大的缓解了X锁和S锁；
3. 从库可以配置为myisam引擎，提高读的性能及节省系统开销；
4. 从库同步主库的数据和主库直接写还是有区别的，从库是通过主库发来的binlog来同步的，但是呢，区别在于主库向从库发送binlog是异步的，所以同步数据也是异步的。
5. 读写分离适用于读的操作应用较多的场景，如果只有一台服务器，当select很多时，update和delete会被select的访问堵塞，这时就需要等待select结束，并发性能不高；当读和写比例相近时，可以设置成互为主从。
6. 分摊读写，这个就是拿机器数量去换性能。
7. 增加冗余，提高容错，当主故障时，可以迅速地把从切换为主来使用。

十五、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需要打开并锁住所有的底层表，这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、LOADDATA 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)
1

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

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

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

十九、数据库调优（重要）

19.1 调优目标

1. 尽可能节省系统资源，以便系统可以提供更大负荷的服务（吞吐量大）
2. 合理的结构设计和参数调整，以提高用户的操作响应速度（响应速度更快）
3. 减少系统的瓶颈，提高Mysql数据库整体的性能

19.2 如何定位调优问题？

1. 用户的反馈：用户是我们的服务对象，因此他们的反馈是最直接的，虽然他们不会直接提出技术建议，但是有些问题往往是用户第一时间发现的，我们要重视用户的反馈，找到和数据相关的问题。
2. 日志分析：我们可以通过查看数据库日志和操作系统日志等方式找到异常的情况，通过他们来定位遇到的问题
3. 服务器资源使用监控：通过监控服务器的CPU、内存、I/O等使用情况，可以实时的了解服务器的性能使用，与历史情况进行对比。
4. 数据库内部状况监控：在数据库的监控中，活动会话监控是一个重要的指标，通过它你可以清楚的了解数据库当前是否处于非常繁忙的状态，是否存在SQL堆积等等。

19.3 调优的步骤

1、数据库调优措施






2、优化MySQL服务器

20、主键一般是用自增ID还是UUID？

使用自增长做主键的优点：
1、很小的数据存储空间
2、性能最好
3、容易记忆

使用自增长做主键的缺点：
1、如果存在大量的数据，可能会超出自增长的取值范围
2、很难（并不是不能）处理分布式存储的数据表，尤其是需要合并表的情况下
3、安全性低，因为是有规律的，容易被非法获取数据

使用UUID做主键的优点：
1、它是独一无二的，出现重复的机会少
2、适合大量数据中的插入和更新操作，尤其是在高并发和分布式环境下
3、跨服务器数据合并非常方便
4、安全性较高

使用UUID做主键的缺点：
1、存储空间大（16 byte），因此它将会占用更多的磁盘空间
2、会降低性能
3、很难记忆

那么一般情况下是如何选择的呢？

1、项目是单机版的，并且数据量比较大（百万级）时，用自增长的，此时最好能考虑下安全性，做些安全措施。
2、项目是单机版的，并且数据量没那么大，对速度和存储要求不高时，用UUID。
3、项目是分布式的，那么首选UUID，分布式一般对速度和存储要求不高。
4、项目是分布式的，并且数据量达到千万级别可更高时，对速度和存储有要求时，可以用自增长。

总体对比来看自增长相对来说在较大的项目中是首选

21、字段为什么要设置成not null?

在mysql数据库中“NULL”和“空值”是不一样的。NULL是一种比较特殊的数据类型，这也可以解释为什么字段设置为NOT NULL，却仍然可以插入空值。设置了NOT NULL之后便不能插入NULL值了，但仍然可以插入像 ’ ’ 这样的空值。另外空值是不占用空间的，而NULL需要占用空间。

在平常我们设计数据表时，如果是索引字段，一定要定义为NOT NULL。因为NULL值会影响cordinate统计，影响优化器对索引的选择，索引效率会下降很多。虽然表中允许空(NULL)列，但其它字段也尽量定义为NOT NULL。mysql在进行比较的时候，NULL 会参与字段比较。因为NULL是一种比较特殊的数据类型。数据库在处理的时候，需要进行特殊的处理。如此的话，就会增加数据库处理记录的复杂性。当表中有比较多的空字段时，在同等条件下，数据库处理的性能会降低许多。

22、SQL语句执行的很慢原因是什么？

一条SQL语句执行很慢的可以分两种情况：
（1）大多数情况是正常的，只是偶尔会出现很慢的情况。
（2）在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。

针对情况1的原因：

1. 数据库在刷新脏页：在往数据库插入或者更新一条数据的时候，我们知道数据库会在内存中把对应的字段更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中，而是把这些更新的记录写入到redo log日志中去，等到空闲的时候，再通过redo log日志把最新的数据同步到磁盘中去。（备注：当内存数据页跟磁盘数据页内容不一致时，我们成这个内存页为“脏页”，当内存数据刷新到磁盘后，内存和磁盘的数据一致时，就称为“干净页”）
   刷脏页有以下几种场景
   （1）redo log 日志写满了：redo log日志里的内容是很有限的，如果数据库一直很忙，更新又很频繁，这个时候redo log很快就被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘中去，只能暂停其他操作，全身心的把数据同步到磁盘中，而此时就会导致平时正常的SQL语句突然执行的很慢。
   （2）内存不够用了：如果一次性查询较多的数据，而且恰好碰到所查询的数据页不在内存中，需要申请内存，而此时恰好内存不足的时候就要淘汰一部分内存数据页，如果是干净页就直接释放，如果恰好是脏页就要刷脏页。
   （3）MySQL认为系统“空闲”的时候：这时候系统没什么压力。
   （4）MySQL正常关闭的时候：这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读取数据，启动速度会很快。
2. 拿不到锁： 我们要执行的这条SQL语句涉及到的表刚好别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。如果要判断是否真的在等待锁，可以使用 show processlist 这个命令来查看当前的状态。

针对情况2的原因：如果数据量一样的情况下，这条SQL语句每次都执行的很慢，那就是SQL语句书写的不理想

23、如何优化WHERE子句？

1、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num is null
1

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0
1

2、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
1

可以这样查询：

select id from t where num=10
union all
select id from t where num=20
1
2
3

5、下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘%c%’
1

下面走索引

select id from t where name like ‘c%’
1

若要提高效率，可以考虑全文检索。
6、in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)
1

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3
1

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num
1

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num
1

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100
1

应改为:

select id from t where num=1002
1

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’ –name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0 –’2005-11-30′生成的id
1
2

应改为:

select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
1
2

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

13、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)
1

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)
1

14、任何地方都不要使用 select * from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。

15、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

16、尽量避免大事务操作，提高系统并发能力。如有不合适则可通知

24、MySQL中的limit分页如何优化？

mysql的分页比较简单，只需要limit offset,length就可以获取数据了，但是当offset和length比较大的时候，mysql明显性能下降。

MySQL的limit工作原理就是先读取n条记录，然后抛弃前n条，读m条想要的，所以n越大，性能会越差。

1. 子查询优化法
   先找出第一条数据，然后大于等于这条数据的id就是要获取的数据
   缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性

SELECT * FROM product WHERE id > =(select id from product limit 866613, 1) limit 20 
1

2. 根据id做限定进行优化，把id作为where的条件

3. 使用临时表进行优化，先查出所有id，再试用in查出数

select * from table where id in (select id from table limit m,n)
1

25、为什么要设置主键？

数据库的主键指的是一个列或多个列组合，其值能唯一标注表中的每一行，通过他可以强制表的实体完整性。主键可以用来表示一个精确定位的特定行，如果没有主键，无法精准定位一条记录是否就是你要的相关行记录，这样就会导致更新或者删除表中特定的行很困难。如果有主键来约束行记录的唯一性后，就可以用主键来解决问题。

主键作用:

1. 保证实体的完整性
2. 加快数据库的操作速度
3. 在表中添加记录时，DBMS会自动检查记录的主键值，不允许该值与其他值重复

26、drop、delete和truncate的区别？

1、delete

1. delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
2. delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
3. delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

2、truncate

1. truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2. truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
3. 对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
4. truncatetable不能用于参与了索引视图的表。

3、drop

1. drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2. drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
3. drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

27、SQL语句的执行顺序?

案例分析：

select deptno ,avg(sal) from emp where ename is not null group by 
deptno having avg(sal)>2000 order by deptno desc;
1
2

1. 先确定从哪个表中取数据，所以最先执行from emp。如果存在多表连接，可以对表加别名，方便后面的引用。
2. 执行 where子句, 筛选 emp 表中ename数据不为 null 的数据 。
3. 执行 group by 子句, 把 emp 表按 “deptno” 进行分组。
4. 执行 avg(sal) 分组函数, 按分组计算平均工资（sal）数值。 （常用的聚合函数有max，min， count，sum，聚合函数的执
5. 在group by之后，having之前。如果在where中写聚合函数，就会出错。）
6. 执行 having 子句, 筛选平均薪资大于2000的。
7. 执行select选出要查找的字段，如果全选可以select *。这里选出部门编号，各部门平均工资。
8. 执行order by 排序语句。order by语句在最后执行，只有select选出要查找的字段，才能进行排序。
9. limit是SQL语句执行的最后一个环节。