10、数据库概念知识

#### 1 为什么要一定要设置主键?

**其实这个不是一定的，有些场景下，小系统或者没什么用的表，不设置主键也没关系，mysql 最好是用自增主键，主要是以下两个原因：如果定义了主键，那么 InnoDB 会选择主键作为聚集索引、如果没有显式定义主键，则 innodb 会选择第一个不包含有 NULL 值的唯一索引作为主键索引、如果也没有这样的唯一索引，则 innodb 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引。所以，反正都要生成一个主键，那你还不如自己指定一个主键，提高查询效率！**

#### 2.自增主键用完了怎么办?

**在 mysql 中，Int 整型的范围（-2147483648\~2147483648），约 20 亿！因此不用考虑自增 ID 达到最大值这个问题。而且数据达到千万级的时候就应该考虑分库分表了。**

#### 3.为什么不直接存储图片、音频、视频等大容量内容?

**我们在实际应用中，都是文件形式存储的。mysql 中，只存文件的存放路径。虽然 mysql 中 blob 类型可以用来存放大容量文件，但是，我们在生产中，基本不用！主要有如下几个原因：**

* **Mysql 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，查询效率会非常慢。**
* **数据库特别大，内存占用高，维护也比较麻烦。**
* **binlog 太大，如果是主从同步的架构，会导致主从同步效率问题！因此，不推荐使用 blob 等类型！**

#### 4.有几种不同的 join?

**Inner join, left join, right join, full join.**

**Inner join, 就是只要有一个列能够匹配， 就简单的返回两个 table 中所有的对应行。**

```plain
select columnnames from tablename1 inner join tablename2 on columnname1=columnname2
```

**Left join 也就是 left outer join。当有一个列能够匹配时就返回左边表中所有的行。**

```plain
select columnnames from tablename1 left join tablename2 on columnname1=columnname2
```

**Right join 也就是 right outer join，当有列匹配时，返回右边表格中所有的行。**

```plain
select columnnames from tablename1 right join tablename2 on columnname1=columnname2
```

**Full join 也就是 full outer join, 当有匹配时， 会返回左边表格和右边表格任意情况下的行组合。**

#### 5.Sql 和 my sql 的区别是什么?

**Sql 是结构化查询语言，my sql 是一个关系型数据库。**

#### 6.Delete 和 truncate 的区别是什么?

**Delete 是 dml，truncate 是 ddl。**

**Delete 是用来删除一行或者多行。Truncate 是用来删除一个表中的所有行。**

**我们可以用 where 跟 delete 结合使用。Truncate 不可以。**

#### 7.Drop 跟 truncate 的区别是什么?

**truncate 是删除表中所有的行，drop 是删除整张表。这两个操作都不可以回撤。**

#### 8.什么是关系？有几种关系?

**关系是指多表在数据库中的关联。**

**有 4 种关系。**

**1 对 1，多对一，多对多，一对多。**

#### 9.什么是主键？

**唯一标识一条记录，不能有重复的，不允许为空。**

#### 10. 什么是外键？

**表的外键是另一表的主键, 外键可以有重复的, 可以是空值。**

#### 11.如何随机的从表中取行?

**select \* from tablename sample 10**

#### 12.Sql server 的 TCP/ip 端口是什么?

**1433**

#### 13.什么是 SQL？

**SQL（结构化查询语言）是一种设计用于检索和操作数据的数据库。它属于美国国家标准协会（ANSI）的一种标准，可用于执行 Select（选择）、Update（更新）、Delete（删除）和 Insert（插入）等数据任务。**

#### 14.SQL 中的 Constraints(约束)是什么？

**它可用于设置表中数据类型的限制。在创建或更新表语句时，可以使用约束。一些限制是：**

**NOT NULL、PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、DEFAULT**

#### 15.什么是 Index（索引）？

**索引用于加速查询的性能。它可以更快地从表中检索数据。可以在一组列上创建索引。**

#### 16.Clustered（群集）和 Non-Clustered Index（非群集）索引之间有什么区别？

**聚集索引——有助于轻松检索数据，并且只有一个聚集索引与一个表一起分配。它会更改记录在数据库中的保存方式。**

**非聚集索引——与聚集索引相比，非聚集索引很慢。并且在非集群索引的情况下，该表可以具有多个索引，为表创建一个对象，该表是搜索后指向表的一个点。**

#### 17.什么是 Aggregate Functions（聚合函数）？

**它是一个返回单个值的数学函数。SQL 中的聚合函数是：**

**AVG（）——返回平均值**

**COUNT（）——返回行数**

**MAX（）——返回最大值**

**MIN（）——返回最小值**

**ROUND（）——基于十进制规范，此函数对数字字段进行舍入**

**SUM（）——返回总和**

#### 18.主键是用自增还是 UUID?

**最好是用自增主键，主要是以下两个原因：**

**1.如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。**

**2.如果使用非自增主键（如 uuid），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到索引页的随机某个位置，此时 MySQL 为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成索引碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。**

**不过，也不是所有的场景下都得使用自增主键，可能场景下，主键必须自己生成，不在乎那些性能的开销。那也没有问题。**

#### 19.CHAR 和 VARCHAR 的区别？

**char 和 varchar 类型声明长度表示用户想保存的最大字符数，其中 char(M)定义的列的长度为固定的，M 的取值可以 0-255 之间，当保存 char 值时，在它们的右边填充空格以达到指定的长度。**

**当检索到 char 值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。char 存储定长数据很方便，char 字段上的索引效率很高。**

**varchar(M)定义的列的长度是可变长度字符串，在 MySQL5.0 以上的版本中，varchar 的数据类型长度支持到了 65535，因为起始位和结束位占去了 3 个字节，所以其整体最大长度为 65532 字节（varchar 的最大有效长度由最大行大小和使用的字符集确定）。**

**同样在 char 和 varchar 尾部加空格，检索时 char 类型后的被删掉，而 varchar 类型的保存。**

#### 20.主键为什么不推荐有业务含义?

**最好是主键是无意义的自增 ID，然后另外创建一个业务主键 ID， 因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义，那么主键就有可能发生变更。主键一旦发生变更，该数据在磁盘上的存储位置就会发生变更，有可能会引发页分裂，产生空间碎片。**

**还有就是，带有业务含义的主键，不一定是顺序自增的。那么就会导致数据的插入顺序，并不能保证后面插入数据的主键一定比前面的数据大。如果出现了，后面插入数据的主键比前面的小，就有可能引发页分裂，产生空间碎片。**

#### 21.货币字段用什么类型?

**货币字段一般都用 Decimal 类型， float 和 double 是以二进制存储的，数据大的时候，可能存在误差。**

#### 22.索引应该建在那些列上？

**在经常需要搜索的列上，可以加快搜索的速度；**

**在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；**

**在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；**

**在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；**

**在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；**

**在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。**

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

**存储过程就像我们编程语言中的函数一样，封装了我们的代码(PLSQL、T-SQL)**

**存储过程的优点**

* **能够将代码封装起来**
* **保存在数据库之中**
* **让编程语言进行调用**
* **存储过程是一个预编译的代码块，执行效率比较高**
* **一个存储过程替代大量 T\_SQL 语句 ，可以降低网络通信量，提高通信速率**

**存储过程的缺点：**

* **每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）**
* **业务逻辑放在数据库上，难以迭代**

#### 24.字段为什么要定义为 NOT NULL?

**一般情况，都会设置一个默认值，不会出现字段里面有 null，又有空的情况。主要有以下几个原因：**

**索引性能不好，Mysql 难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要 mysql 内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致 MYisam 中固定大小的索引变成可变大小的索引。**

**如果某列存在 null 的情况，可能导致 count() 等函数执行不对的情况。看一下 2 个图就明白了：**

**sql 语句写着也麻烦，既要判断是否为空，又要判断是否为 null 等。**

#### 25 索引的分类有哪些？

**唯一索引：唯一索引不允许两行具有相同的索引值**

**主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空**

**聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个**

**非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于 249 个**

#### 26 时间字段用什么类型?

**这个看具体情况和实际场景，timestamp ，datatime ，bigint 都行！把理由讲清楚就行！**

**timestamp，该类型是四个字节的整数，它能表示的时间范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07。2038 年以后的时间，是无法用 timestamp 类型存储的。**

**但是它有一个优势，timestamp 类型是带有时区信息的。一旦你系统中的时区发生改变，例如你修改了时区，该字段的值会自动变更。这个特性用来做一些国际化大项目，跨时区的应用时，特别注意！**

**datetime，占用 8 个字节，它存储的时间范围为 1000-01-01 00:00:00 \~ 9999-12-31 23:59:59。显然，存储时间范围更大。但是它坑的地方在于，它存储的是时间绝对值，不带有时区信息。如果你改变数据库的时区，该项的值不会自己发生变更！**

**bigint，也是 8 个字节，自己维护一个时间戳，查询效率高，不过数据写入，显示都需要做转换。**

#### 27.什么是最左前缀原则？

**最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：**

```plain
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
```

**这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。**

**由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY 子句也遵循此规则。**

#### 38.什么是事务？

**事务简单来说：一个 Session 中所进行所有的操作，要么同时成功，要么同时失败**

**ACID — 数据库事务正确执行的四个基本要素包含：**
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

**一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。**

**举个例子:A 向 B 转账，转账这个流程中如果出现问题，事务可以让数据恢复成原来一样【A 账户的钱没变，B 账户的钱也没变】。**

#### 39.mysql 有关权限的表都有哪几个?

**MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在 mysql 数据库里，由 mysql\_install\_db 脚本初始化。这些权限表分别 user，db，table\_priv，columns\_priv 和 host。下面分别介绍一下这些表的结构和内容：**

**user 权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。**

**db 权限表：记录各个帐号在各个数据库上的操作权限。**

**table\_priv 权限表：记录数据表级的操作权限。**

**columns\_priv 权限表：记录数据列级的操作权限。**

**host 权限表：配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT 和 REVOKE 语句的影响。**

#### 40.什么情况下应不建或少建索引?

**表记录太少**

**经常插入、删除、修改的表**

**数据重复且分布平均的表字段，假如一个表有 10 万行记录，有一个字段 A 只有 T 和 F 两种值，且每个值的分布概率大约为 50%，那么对这种表 A 字段建索引一般不会提高数据库的查询速度。**

**经常和主字段一块查询但主字段索引值比较多的表字段**

#### 41.数据表损坏的修复方式有哪些？

**使用 myisamchk 来修复，具体步骤：**

**1.修复前将 mysql 服务停止。**
2.打开命令行方式，然后进入到 mysql 的/bin 目录。
3.执行 myisamchk –recover 数据库所在路径/\*.MYI

**使用 repair table 或者 OPTIMIZE table 命令来修复，REPAIR TABLE table\_name 修复表 OPTIMIZE TABLE table\_name 优化表 REPAIR TABLE 用于修复被破坏的表。**

**OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了 OPTIMIZE TABLE 命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）**

#### 42.MySQL 数据库 cpu 飙升到 100% 的话他怎么处理？

**列出所有进程 show processlist 观察所有进程 多秒没有状态变化的(干掉)**

**查看慢查询，找出执行时间长的 sql；**

**explain 分析 sql 是否走索引，sql 优化；**

**检查其他子系统是否正常，是否缓存失效引起，需要查看 buffer 命中率；**

#### 43.维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

**尽可能使用约束，如 check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。**
最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

#### 44.游标是什么？

**是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。**

#### 45 数据库 NULL 是什么意思?

**NULL(空)这个值是数据库世界里一个非常难缠的东西。**

**NULL 这个值表示 UNKNOWN(未知):它不表示“”(空字符串)。假设您的 SQL Server 数据库里有 ANSI\_NULLS，当然在默认情况下会有，对 NULL 这个值的任何比较都会生产一个 NULL 值。您不能把任何值与一个 UNKNOWN 值进行比较，并在逻辑上希望获得一个答案。您必须使用 IS NULL 操作符。**

#### 46.关系型数据库和非关系型数据库区别？

**关系型数据库的优点：**

**1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；**

2、使用方便：通用的 SQL 语言使得操作关系型数据库非常方便；

3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；

4、支持 SQL，可用于复杂的查询。

5.支持事务

**关系型数据库的缺点：**

**1、为了维护一致性所付出的巨大代价就是其读写性能比较差；**

2、固定的表结构；

3、不支持高并发读写需求；

4、不支持海量数据的高效率读写

**非关系型数据库优点：**

1.无需经过 sql 层的解析，读写性能很高

2.基于键值对，数据没有耦合性，容易扩展

3.存储数据的格式：nosql 的存储格式是 key,value 形式

**非关系型数据库缺点：**
不提供 sql

#### 47.查询语句的执行先后顺序？

**查询中用到的关键词主要包含六个，并且他们的顺序依次为 select--from--where--group by--having--order by**

**其中 select 和 from 是必须的，其他关键词是可选的，这六个关键词的执行顺序 与 sql 语句的书写顺序并不是一样的，而是按照下面的顺序来执行**

**from:需要从哪个数据表检索数据**

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

order by :按照什么样的顺序来查看返回的数据

#### 48.什么情况下会造成死锁？

**所谓死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的 InnoDB。**

**死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致。**

**那么对应的解决死锁问题的关键就是：让不同的 session 加锁有次序。**

#### 49 死锁如何解决？

**查出的线程杀死 kill**

```plain
SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;
```

**设置锁的超时时间**
Innodb 行锁的等待时间，单位秒。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。

**生产环境不推荐使用过大的 innodb\_lock\_wait\_timeout 参数值**

**该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：**

```plain
set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。
```

#### 50.触发器的作用？

**触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。**

#### 51 在数据库中查询语句速度很慢，如何优化？

**1.建索引 ,2.减少表之间的关联 3.优化 sql，尽量让 sql 很快定位数据，不要让 sql 做全表查询，应该走索引,把数据 量大的表排在前面 4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据 5.尽量用 PreparedStatement 来查询，不要用 Statement**

#### 52 什么是 PL/SQL？

**PL/SQL 是一种程序语言，叫做过程化 SQL 语言（Procedural Language/SQL）。PL/SQL 是 Oracle 数据库对 SQL 语句的扩展。在普通 SQL 语句的使用上增加了编程语言的特点，所以 PL/SQL 把数据操作和查询语句组织在 PL/SQL 代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。**

#### 53 使用 SQL 命令将教师表 teacher 中工资 salary 字段的值增加 500，应该使用的命令?

**Update teacher set salary=salary+500**

#### 54.mdf、.ldf、.tif 、.ndf 哪个不是 sql 数据库文件的后缀？

**.tif**

##### 55.SQL 语言中，条件“年龄 BETWEEN 40 AND 50”表示年龄在 40 至 50 之间更准确的描述是?

**包括 40 岁和 50 岁**

#### 56.drop、delete 与 truncate 分别在什么场景之下使用？

**SQL 中的 drop、delete、truncate 都表示删除，但是三者有一些差别**

**delete 和 truncate 只删除表的数据不删除表的结构**
速度,一般来说: drop> truncate >delete

**delete 语句是 dml,这个操作会放到 rollback segement 中,事务提交之后才生效;**

**如果有相应的 trigger,执行的时候将被触发. truncate,drop 是 ddl, 操作立即生效,原数据不放到 rollback segment 中,不能回滚. 操作不触发 trigger.**

**不再需要一张表的时候，用 drop**

想删除部分数据行时候，用 delete，并且带上 where 子句

保留表而删除所有数据的时候用 truncate

#### 57.MyISAM 索引与 InnoDB 索引的区别？

**InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引。**

**InnoDB 的主键索引的叶子节点存储着行数据，因此主键索引非常高效。**

**MyISAM 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。**

**InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效**

#### 58.有哪几种类型的 SQL 命令？

**SQL 命令分为以下类型：**

**DDL（数据定义语言） - 用于定义数据库的结构。**

**DCL（数据控制语言） - 用于为用户提供权限。**

**DML（数据操作语言） - 用于管理数据。**

**DQL（数据查询语言） - 所有命令都在 SQL 中，用于检索 DQL 中的数据。**

**TCL（事务控制语言） - 用于管理 DML 所做的更改。**