MySql-面试题

一、绪论

1.什么是SQL

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

2.什么是MySQL?

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

3.MySql, Oracle，Sql Service的区别

• Sql Service只能在Windows上使用，而MySql和Oracle可以在其他系统上使用， 而且可以支持数据库不同系统之间的移植。
• MySql开源免费的，Sql Service和Oracle收费。
• 小到大排序，MySql很小，Sql Service居中，Oracle最大
• Oracle支持多用户不同权限来进行操作，而MySql只要有登录权限就可操作全部数据库
• 安装所用的空间差别也是很大的，Mysql安装完后才几百M而Oracle有几G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能。
• Oracle支持大并发量，大访问量，Sql Service还行，而MySql的话压力没这么大，因此现在的MySql的话最好是要使用集群或者缓存来搭配使用
• 做分页的话，MySql使用Limit，Sql Service使用top，Oracle使用row
• Oracle没有自动增长类型，Mysql和Sql Service一般使用自动增长类型

4.什么是范式

范式是数据库设计时遵循的一种规范，不同的规范要求遵循不同的范式。

5.最常用的三大范式

第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

参考：

数据库三大范式是什么？(3NF详解)_Dzzzzzzzzzzzzzzz的博客-CSDN博客_数据库三大范式是什么

6.mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql\_install\_db脚本初始化。

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

7.数据库经常使用的函数

count(*/column)：返回行数

sum(column)： 返回指定列中唯一值的和

max(column)：返回指定列或表达式中的数值最大值

min(column)：返回指定列或表达式中的数值最小值

avg(column)：返回指定列或表达式中的数值平均值

date（Expression）: 返回指定表达式代表的日期值

…

二、数据类型

1.mysql有哪些数据类型

1、整数类型

包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。

任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。
长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

2、实数类型

包括FLOAT、DOUBLE、DECIMAL。
DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。
而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。
计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

decimal(10,2)。小数部分2位，整数部分 8位（10-2），如果超长数据库会舍弃，短了小数点用0补齐。

3、字符串类型

2.数据库中char和varchar的区别

• char的长度是不可变的，而varchar的长度是可变的

也就是说，定义一个char[10]和varchar[10]，如果存进去的是‘csdn’，那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，而varchar就立马把长度变为4了

• char的存取速度还是要比varchar要快得多

因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。

• 存储方式不同

char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节。

4、日期和时间类型

尽量使用timestamp，空间效率高于datetime，

5、枚举类型（ENUM）

三、引擎

1.什么是存储引擎

存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式，如MySQL中的数据、索引以及其他对象是如何存储的，是以文件系统的方式。

2.常用的存储引擎

存储引擎有Innodb、MyIASM和MEMORY

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎(原本Mysql的默认引擎，5.5版之前)：不提供事务的支持，也不支持行级锁和外键。
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

3.MySQL存储引擎MyISAM与InnoDB区别

MySQL5.5以后的版本，是以InnoDB作为一个默认的存储引擎，之前的版本都是MyISAM（maishen），区别主要分为五个方面：

1、数据的存储结构不同 

myisam：每张表被存放在三个文件：frm-表结构定义文件、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件。

InnoDB：：每张表被存放在二个文件：frm-表结构定义文件和ibd文件（存储的是数据和索引文件）。

2、存储空间

存储空间

MyISAM可被压缩，存储空间较小

InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引

3、外键、事务和锁                  myisam                                                InnoDB

外键	不支持	支持
事务	不支持	支持

锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）

表级锁定

行级锁定、表级锁定，锁定力度小并发能力高

4.MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

参考：MyISAM 和 InnoDB 索引的区别_骷大人的博客-CSDN博客_myisam和innodb的区别 索引结构

5.InnoDB引擎的4大特性

• 插入缓冲（insert buffer)

• 预读(read ahead)

• 二次写(double write)

• 自适应哈希索引(ahi)

6.选择合适的存储引擎

MyISAM存储引擎
  如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. 比如 bbs 中的 发帖表，回复表。

INNODB存储引擎:
  对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表。

Memory 存储
  我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快。
 

四、索引

1.什么是索引？

通俗来说，索引就相当于目录，根据目录可以快速定位要查找的内容所在的页码；

索引是数据库管理系统中一个排序的数据结构，以快速存取数据库中数据，索引存在在磁盘文件里；

2.索引作用和数据结构分类（索引方法）？

数据结构分类：MySQL索引主要有两种结构：B+Tree索引和Hash索引；

作用：索引能极大的减少存储引擎需要扫描的数据量，从而提高数据的检索速度。

3.索引有哪几种类型或简述有哪些索引？

使用：

1-主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

添加主键 # ALTER TABLE tbl_name ADD PRIMARY KEY (col_list)

2-唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

使用场景：

 语法：

可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

3-普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

 使用场景：普通索引大多数情况下都可以使用。

 语法：

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

4-全文索引： 是目前搜索引擎使用的一种关键技术。

使用场景：搜一篇文章

 语法：

可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

4.索引有哪些优缺点？

索引的优点（作用）

最主要的原因，可以大大加快数据的检索速度，改善数据库性能。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

5.主键索引与唯一索引的区别

• 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为空值 ++ 唯一索引了。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
• 主键可以被其他表引用为外键，而唯一索引不能。 

索引的数据结构

参考：

MySQL系列 | 索引数据结构大全 - 腾讯云开发者社区-腾讯云

6.B树和B+树的区别

• 在B树中，你可以将键和值存放在非叶子节点和叶子节点；但在B+树中，非叶子节点都是键，没有值，叶子节点同时存放键和值。

• B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

7.使用B树和B+树的好处

使用B树的好处

 B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

使用B+树的好处
由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

8.Hash索引和B+树索引的底层实现原理

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

9.Hash索引和B+树所有有什么区别或者说优劣呢?

• hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
• 因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
• hash索引不支持使用索引进行排序，原理同上。
• hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
• hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
• hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

10.数据库为什么使用B+树而不是B树

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
• B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
• B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
• 增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。
   

11.什么是聚簇索引？何时使用聚簇索引与非聚簇索引

 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

12.何时使用聚簇索引与非聚簇索引

13.非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

14.联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
 

五、事务

1.什么是数据库事务？

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

• 事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。

场景：

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

2.事物的四大特性(ACID)介绍一下?

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

3.数据并发读写中存在三大致命的问题

脏读

定义：脏读是指一个事务读取到其他事务没有提交的数据

例如：事务B 修改一个数据，事务A 随后读取这个数据。如果事务B 撤销了这次修改，那么 事务A读取的数据是脏数据。

不可重复读

定义：指并发更新时，另一个事务前后查询相同数据时的数据不符合预期。

例如：

幻读

定义：并发新增、删除这种会产生数量变化的操作时，另一个事务查询相同数据时不符合预期

例如：

4.什么是事务的隔离级别？MySQL的默认隔离级别是什么？

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

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

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

六、 锁

从锁的类别上分MySQL都有哪些锁呢？

锁定义：当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

从锁的类别上来讲，有共享锁和排他锁。

共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁就是让多个线程同时获取一个锁。

排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排它锁也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

隔离级别与锁的关系

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

 MySQL中有哪几种锁

• 按照数据的操作类型划分：共享锁、排他(独占)锁
• 按照数据操作粒度划分：表级锁、行级锁、页级锁
• 按照对待锁的态度划分：乐观锁、悲观锁

行级锁，表级锁和页级锁对比

行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

• 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

• 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
   

页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

• 特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MySQL中InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁。

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

什么是死锁？怎么解决？

死锁是两个或多个事务，各自占有对方的期望获得的资源，形成的循环等待，彼此无法继续正常执行的一种状态。

常见的解决死锁的方法

1、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

2、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

数据库的乐观锁和悲观锁是什么？怎么实现的？

乐观锁就是持比较乐观态度的锁，就是我们操作数据的时候是非常乐观的，认为别的线程不会同时去修改数据，所以它不会上锁，但是更新的时候，会去判断在此期间有没有别的线程，更新过这个数，如果冲突，则返回给用户异常信息，让用户决定如何去做。乐观锁适用于读多写少的场景，这样可以提高程序的吞吐量。

悲观锁就是持比较悲观态度的锁，就是我们操作数据的时候是非常悲观的，就是我们每次拿到这个数据的时候，认为别的线程也会同时修改数据，所以我们每次拿这个数据的时候，都会上锁

视图

什么是视图？

概念：视图（view）是一种虚拟存在的表，本身并不包含数据。它是作为一个select语法查询到的结果集，以此为基表创建的一张虚拟表。对视图的操作对基表有直接影响,[视图中的数据发送了改变，原始表中的数据也会变化，原始表中的数据发生了变化视图中的数据也会变化]。

为什么要使用视图？

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

视图有哪些特点？

• 视图是由基本表(实表)产生的表(虚表)。

• 视图的建立和删除不影响基本表。

• 对视图内容的更新(添加，删除和修改)直接影响基本表。

• 当视图来自多个基本表时，不允许添加和删除数据。

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

视图的优点

• 简单：因为视图是查询语句执行后返回的已经过滤好的复合条件的结果集，所以使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对于表的权限管理并不能限制到某个行或者某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构被确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

视图的缺点

• 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
• 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）
视图具体操作

视图的概述_陪雨岁岁年年的博客-CSDN博客_视图

游标

什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理。

存储过程与函数

什么是存储过程？有哪些优缺点？

• 存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点

• 存储过程是预编译过的，执行效率高。
• 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
• 安全性高，执行存储过程需要有一定权限的用户。
• 存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

• 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
• 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
• 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
• 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

触发器

什么是触发器？触发器的使用场景有哪些？

• 触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。
• 大家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异，表存储引擎InnoDB和MyISAM的区别

MySQL中都有哪些触发器？

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

左连接、右连接的区别：

左连接：左表为主，右边找不到用Null 填充，表的结果集左边一定不会为Null

右连接：右表为主，左边找不到用Null 填充，表的结果集右边一定不会为Null

常用SQL语句

• 数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER

主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。

• 数据查询语言DQL（Data Query Language）SELECT

这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。

• 数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE

主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。

• 数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

主要为以上操作 即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

SQL语句的语法顺序

超键、候选键、主键、外键分别是什么？

键：在数据库中关键码（简称，键）由一个或多个属性组成。在实际应用中，有下列键
超键：在关系中能唯一标识元组的属性集称为关系模式的超键
候选键：不含有多余属性的超键称为候选键
主键：用户选作元组标识的候选键称为主键。

主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键：在一个表中存在的另一个表的主键称此表的外键

参考：

MySQL数据库——键、超键、候选键、主键_商俊帅的博客-CSDN博客_mysql键是什么

SQL 约束有哪几种？

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

sql的六种约束_小白熊wzb的博客-CSDN博客_sql约束有哪几种

笛卡尔积查询

select * from dept3,emp3;
#查询的结果是两张表相乘的结果，称之为笛卡尔积查询
#如果左边表有m条数据，右边表有n条数据，则得到m*n条数据
#笛卡尔积查询没有考虑两张表的对应关系，所以结果中包含大量错误的数据，无法直接使用。
#虽然通常不会直接使用，但笛卡尔积查询是其他多表查询的基础，需要了解
————————————————

关联查询的一些用法_我宁愿瓜的博客-CSDN博客_关联查询

六种关联查询

• 交叉连接（CROSS JOIN）

• 内连接（INNER JOIN）

• 外连接（LEFT JOIN/RIGHT JOIN，FULL JOIN）

• 联合查询（UNION与UNION ALL）

交叉连接（CROSS JOIN） 或者   笛卡尔积连接


内连接（INNER JOIN）
#可以在笛卡尔积查询的基础上，基于外键字段筛选出正确的数据
select * from dept3,emp3 where emp3.did = dept3.id;
​
#内连接查询也可以通过专用的语法实现
select * from dept3 inner join emp3 on emp3.did = dept3.id;
#内连接查询只能查询到两张表中都有对应数据的记录。
#对于左边表有而右边表没有数据 和 右边表有而左边表没有的数据 都不会被查询到


外连接查询  

左外连接查询
右外连接查询
全外连接查询

什么是子查询

当一个查询结果是另一个查询的条件的时候,那么就称为子查询。

mysql中 in 和 exists 区别

• 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
• 如果查询的两个表大小相当，那么用in和exists差别不大。
• not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

mysql中int(10)和char(10)以及varchar(10)的区别

• int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。
• char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间
• varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符
   

FLOAT和DOUBLE的区别是什么？

• FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
• DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

drop、delete与truncate的区别

在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

UNION与UNION ALL的区别？

• 如果使用UNION ALL，不会合并重复的记录行
• 效率 UNION 高于 UNION ALL

数据库优化

为什么要优化

• 系统的吞吐量瓶颈往往出现在数据库的访问速度上
• 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
• 数据是存放在磁盘上的，读写速度无法和内存相比

优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

数据库结构优化

• 一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

• 需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

将字段很多的表分解成多个表

• 对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
• 因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表

• 对于需要经常联合查询的表，可以建立中间表以提高查询效率。
• 通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段

• 设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。
• 表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

https://www.csdn.net/tags/MtjaMgxsNTQ2NDgtYmxvZwO0O0OO0O0O.html

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

MySQL数据库cpu飙升到500%的话他怎么处理？

• 当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
• 如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。
• 一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。
• 也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等
   

参考：

————————————————
版权声明：本文为CSDN博主「小杰要吃蛋」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_43122090/article/details/105531113