知识点汇总5

淡水鳗鱼的咸鱼梦
270 阅读17分钟

一、基础

1、什么是sql？什么是MySQL？

  • sql：结构化查询语言，是操作数据库的一种语言
  • MySQL：一种关系型数据库管理系统，属于Oracle旗下，在Java企业级应用经常使用

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

  1. 第一范式：每个列都不可以再拆分==>比如某个字段写了家庭信息：里面还可以分为家庭住址 + 家庭电话两个信息，这样不管是存储还是查询都不是很方便，一定要保证字段不可以再分
  2. 第二范式：在第一范式的基础上，非主键列需要完全依赖主键，而不是依赖于主键的一部分==>满足了第一范式已经确保了字段不可分，但是还是可能存在问题，比如订单编号、订单、产品编号、产品这四个字段在一个表中，订单编号 + 产品编号为主键，可是订单编号对应订单，产品编号对应产品，说明这张表存在冗余，这张表可以拆分为两个表
  3. 第三范式：在第一二范式的基础上，非主键列需要依赖于主键，而不是间接依赖于主键==>比如学号、姓名、院系、院系主任这四个，虽然学号对应一个姓名，这个姓名也对应一个院系，一个院系也对应一个系主任，但是系主任是通过院系间接关联到学号的。所以这个时候也可以把这个表拆成两个。学号+姓名+院系和院系+系主任。

3、mysql有关权限的表都有哪几个？

  • user权限表：记录连接到数据库服务器的各个用户的账号信息
  • db权限表：各个用户在各个数据库的权限
  • table_priv权限表：记录用户在数据库表的权限
  • columns_priv权限表：记录数据库列级权限
  • host权限表：记录更详细的权限

4、MySQL有哪些数据类型？

分类类型名称说明
整数类型tinyInt很小的整数(8位二进制)
smallint小的整数(16位二进制)
mediumint中等大小的整数(24位二进制)
int(integer)普通大小的整数(32位二进制)
小数类型float单精度浮点数
double双精度浮点数
decimal(m,d)压缩严格的定点数
日期类型yearYYYY 1901~2155
timeHH:MM:SS -838:59:59~838:59:59
dateYYYY-MM-DD 1000-01-01~9999-12-3
datetimeYYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
timestampYYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
文本、二进制类型CHAR(M)M为0~255之间的整数
VARCHAR(M)M为0~65535之间的整数
TINYBLOB允许长度0~255字节
BLOB允许长度0~65535字节
MEDIUMBLOB允许长度0~167772150字节
LONGBLOB允许长度0~4294967295字节
TINYTEXT允许长度0~255字节
TEXT允许长度0~65535字节
MEDIUMTEXT允许长度0~167772150字节
LONGTEXT允许长度0~4294967295字节
VARBINARY(M)允许长度0~M个字节的变长字节字符串
BINARY(M)允许长度0~M个字节的定长字节字符串

二、引擎

5、存储引擎MyISAM与InnoDB区别？

MyISAMInnodb
存储结构每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间MyISAM可被压缩，存储空间较小InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式数据和索引是分别存储的，数据.MYD，索引.MYI数据和索引是集中存储的，.ibd
记录存储顺序按记录插入顺序保存按主键大小有序插入
外键不支持支持
事务不支持支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）表级锁定行级锁定、表级锁定，锁定力度小并发能力高
SELECTMyISAM更优
INSERT、UPDATE、DELETEInnoDB更优
select count(*)myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式B+树索引，myisam 是堆表B+树索引，Innodb 是索引组织表
哈希索引不支持支持
全文索引支持不支持

6、MyISAM索引与InnoDB索引的区别？

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

三、索引

7、什么是索引？

索引是一种帮助数据库高效获取数据的数据结构。通常使用B+树来实现。

索引就相当于是书中的目录，可以通过索引更快查找数据，索引的创建也需要耗费内存空间。

8、索引有哪些优缺点？

  • 优点：
    • 索引可以更高效的获取数据
    • 通过索引提前排序，可以降低数据库排序的消耗
  • 缺点：
    • 索引的创建也需要耗费空间
    • 索引的维护也很繁琐，在新增或删除记录的时候，会造成整表维护

9、索引有哪几种类型？

  • 主键索引：创建时设置主键，根据主键排序，主键要求不能为null，也不允许重复
  • 唯一索引：创建唯一索引，要求索引不能重复，可以有多个null值
  • 普通索引：创建的索引没有特殊限制，可以为null
  • 全文索引

10、索引的基本原理？

  1. 把创建了索引的列的内容进行排序
  2. 对排序结果生成倒排表
  3. 在倒排表内容上拼上数据地址链
  4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

11、索引设计的原则？

  1. where后面的字段可以设置为索引（因为它经常查询）
  2. 基数比较小的字段不需要创建索引
  3. 短索引，如果一个字段比较长，可以创建一个短字段前缀，这样可以节约空间
  4. 不要过度创建索引，索引也需要占用空间，所以创建太多索引也会影响性能

12、创建索引的原则？

  1. 最左前缀匹配原则
  2. 较频繁查询的列可以创建索引
  3. 较频繁更新的字段不要创建索引
  4. 区分度比较低的列不要创建索引
  5. 尽可能扩展索引，而不是新建索引（重复值比较多的也不要创建索引）
  6. 外键的数据库列最好创建索引
  7. text/image/bit类型的类不要创建索引

13、创建索引的三种方式？删除索引的方式？

创建索引

第一种：创建表时创建索引

create table table_name(
	id int auto_increment PRIMARY KEY,
    name varchar(25),
    information varchar(250),
    key(name),
)engine=innodb default charset=utf8;

第二种：使用alter给创建好的表添加索引

alter table table_name add index index_name(information);

第三种：create index创建索引

create index index_name on table_name(column_name);

删除索引

alter table table_name drop KEY id;
alter table table_name drop KEY name;
alter table table_name drop KEY information;

14、什么是聚簇索引？何时使用聚簇索引与非聚簇索引

  • 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
  • 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

15、非聚簇索引一定会回表查询吗？

不一定，如果查询的字段全部命中了非聚簇索引就不会。

四、事务

16、什么是数据库事务？

事务是一组不可分割的数据库操作，它们共同控制某个数据，要么这些数据都执行，要么这些数据都不执行。

17、事物的四大特性(ACID)介绍一下?

  • A：atom：原子性，事务的操作要么都执行，要么都不执行
  • C：一致性：事务执行后，应该保证数据的前后一致性
  • I：隔离性：事务与事务之间是隔离状态的，互相不影响
  • D：持久性：事务执行后，要持久化，保证数据库故障也不会有问题

18、什么是事务的隔离级别？MySQL的默认隔离级别是什么？

三个问题

  • 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
  • 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
  • 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

解决方式：隔离级别

隔离级别脏读不可重复读幻影读
READ-UNCOMMITTED√√√
READ-COMMITTED×√√
REPEATABLE-READ××√
SERIALIZABLE×××

默认隔离级别

  • Mysql 默认采用的 REPEATABLE_READ隔离级别
  • Oracle 默认采用的 READ_COMMITTED隔离级别

五、锁

19、对MySQL的锁了解吗？

当并发情况操作数据库，容易造成数据问题，锁机制就是保证数据安全的。

20、按照锁的粒度分数据库锁有哪些？

关系型数据库按照锁的粒度可以分为：行级锁（InnoDB）、表级锁（MyISAM）、页级锁（BDB）。

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

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

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

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

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

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

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

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

共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

比如一种是来看房，多个用户一起看房是可以接受的。 一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。

他们的加锁开销从大到小，并发能力也是从大到小。

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

InnoDB是基于索引来完成行锁

例:

 select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起。

23、InnoDB存储引擎的锁的算法有几种？

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

24、什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法：

  1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
  2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
  3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁。

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

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

六、高级特性

26、为什么要使用视图？什么是视图？

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。

优缺点

优点：

  • 查询简单化。视图能简化用户的操作
  • 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
  • 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

缺点：

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

增删改视图

#创建视图
create view view_name
as
select语句；

#修改视图
alter view view_name
as
select 语句；

#删除视图
drop view view_name

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

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

优缺点

优点：

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

缺点：

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

增删改存储过程

#创建存储过程
delimiter $
create procedure procedure_name
begin
	语句；
end $

#删除存储过程
DROP PROCEDURE procedure_name；

#调用存储过程
call procedure_name();

28、什么是触发器？

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

29、MySQL中都有哪些触发器？

在MySQL数据库中有如下六种触发器：

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

30、SQL语句主要分为哪几类？

  • 数据查询语言DQL；
  • 数据操纵语言DML；
  • 数据定义语言DDL；
  • 数据控制语言DCL；
avatar
文章
阅读
粉丝