数据库基础

数据库
第一股 基础篇
1.1.请简述MySQL数据库的优点？
MySQL社区版是开源的，所以你不需要支付额外的费用。
MySQL支持大型的数据库,可以处理拥有上千万条记录的大型数据库。
MySQL 使用标准的 SQL 数据语言形式。
MySQL 可以允许于多个系统上，并且支持多种语言。
MySQL 对 PHP 有很好的支持，PHP 是目前最流行的 Web 开发语言。
MySQL支持大型数据库，可支持 5000 万条记录的数据仓库。
MySQL是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 Mysql 系统。

1.2.说一说三个范式
第一范式（1NF）：
数据库表中的字段都是单一属性的，不可再分。
这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：
在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分
第三范式（3NF）：
在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键
总结：
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。

1.3.事务的四大特性(ACID)介绍一下?
原子性(atomicity，或称不可分割性)： 事务的所有操作要么全部成功，要么全部回滚。

一致性(consistency)： 总是从一个一致性的状态转换到另一个一致性的状态
隔离性(isolation)： 多个事务并发执行时，一个事务的执行不应影响其他事务的执行

持久性(durability)： 已被提交的事务对数据库的修改应该永久保存在数据库中。

1.4.在 Linux 下安装MySQL有哪几种方式？
RPM （Redhat Package Manage）：
安装简单，适合初学者学习使用，一台服务器只能安装一个 MySQL，Red Hat Enterprise Linux / Oracle Linux
二进制（ Binary Package）：
安装简单；可以安装到任何路径下，灵活性好；一台服务器可以安装多个 MySQL，Linux - Generic
源码（ Source Package）：
在实际安装的操作系统进行可根据需，要定制编译，最灵活；性能最好；服务器安装多个Source Code

1.5.为什么升级 MySQL，升级前注意事项？
为什么升级MySQL
1.基于安全考虑
2.基于性能和稳定性考虑：mgr 复制 ，并行复制 writeset 等功能，性能提升
3.新的功能：Hash join ，窗口函数，DDL 即时，json 支持
4.原始环境中版本太多，统一版本
5.8.0 版本基本已到稳定期，可以大量投入生产环境中

升级前注意事项
1.注意字符集设置:
为了避免新旧对象字符集不一致的情况，在配置文件将字符集和校验规则设置为旧版本的字符集和比较规则。
2.密码认证插件变更:
为了避免连接问题，可以仍采用 5.7 的 mysql_native_password 认证插件。*
3.sql_mode 支持问题:
8.0 版本 sql_mode 不支持 NO_AUTO_CREATE_USER，要避免配置的 sql_mode 中带有NO_AUTO_CREATE_USER。*
4.是否需要手动升级系统表:
在 MySQL 8.0.16 行 版本之前，需要手动的执行 mysql_upgrade 在 来完成该步骤的升级，在 MySQL8.0.16 版本及之后是由 mysqld 来完成该步骤的升级。
5.高可用架构:
需要先升级从库，再逐步升级主库

?? 1.6.存储引擎如何选择？
如果没有特别的需求，使用默认的Innodb即可。
InnoDB 引擎由于其对事务参照完整性，以及更高的并发性等优点开始逐步的取代 MyISAM
MyISAM：
以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：
更新（删除）操作频率也高，或者要保证数据的完整性,并发量高，支持事务和外键。比如OA自动化办公系统，如下是InnoDB引擎的4大特点
1.插入缓冲（insert buffer)
2.二次写(double write)
3.自适应哈希索引(ahi)
4.预读(read ahead)

?? 1.7.事务隔离级别有哪些？
为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，
由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，
这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
1.READ-UNCOMMITTED(读取未提交)：
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2.READ-COMMITTED(读取已提交)：
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
3.REPEATABLE-READ(可重复读)：
对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4.SERIALIZABLE(可串行化)：
最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

这里需要注意的是：
Mysql 默认采用的 REPEATABLE_READ隔离级别
Oracle 默认采用的 READ_COMMITTED隔离级别

第二股 SQL篇
2.1 简述下SQL及分类？
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。
作用：用于存取数据、查询、更新和管理关系数据库系统。
基本的分类如下：
1.数据查询语言（Data Query Language, DQL）
负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。
2.数据定义语言 (Data Definition Language, DDL)
负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成
3.数据操纵语言（Data Manipulation Language, DML）
负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，
分别代表插入、更新与删除。
4.数据控制语言 (Data Control Language)
它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。
由 GRANT 和 REVOKE 两个指令组成。

2.2 drop、delete与truncate的区别是什么？
三者都表示删除，但是三者有一些差别：
在不再需要一张表的时候，用drop；
在想删除部分数据行时候，用delete；
在保留表而删除所有数据的时候用truncate。

2.3 MySQL六种关联查询是什么？
1.交叉连接（CROSS JOIN）
2.内连接（INNER JOIN）
3.外连接（LEFT JOIN/RIGHT JOIN）
4. 联合查询（UNION与UNION ALL）
5.全连接（FULL JOIN）
6.自连接（Self JOIN）

2.4 MySQL中 in 和 exists 区别？
mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。
1.如果查询的两个表大小相当，那么用in和exists差别不大。
2.如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

2.5 基于MySQL简述下SQL的生命周期
1.应用服务器与数据库服务器建立一个连接
2.数据库进程拿到请求sql
3.解析并生成执行计划，执行
4.读取数据到内存并进行逻辑处理
5.通过步骤一的连接，发送结果到客户端
6.关掉连接，释放资源

第三股 运维篇
?? 3.1 MySQL有哪些数据类型？
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型
1.数值类型
整数类型：TINYINT、SMALLINT、MEDIUMINT、INT 或 INTEGER、BIGINT
带小数的类型：FLOAT（单精度）、DOUBLE（双精度）、DECIMAL（定点数）

2 日期和时间类型
DATE：YYYY-MM-DD 日期值
TIME：HH:MM:SS 时间值或持续时间
YEAR：YYYY 年份值
DATETIME：YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP：YYYYMMDDHHMMSS 混合日期和时间值，时间戳
3.字符串类型
CHAR：定长字符串 0-255 字节
VARCHAR：0-65535 字节 变长字符串 --频繁改变的列建议用vachar 类型
BLOB：0-65535 字节 二进制形式的长文本数据，二进制大对象
TEXT：0-65535 字节 长文本数据 、varchar 的加长增强版
LONGTEXT：0-4294967295 字节极大文本数据
ENUM： 1-2 字节 枚举类型（单一值）
SET：1-8 字节 一个集合

3.2 MySQL的约束有哪几种？
NOT NULL:
用于控制字段的内容一定不能为空（NULL）。
UNIQUE:
控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY:
也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY:
用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK:
用于控制字段的值范围。

3.3 索引设计的原则？
1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2.基数较小的类，索引效果较差，没有必要在此列建立索引
3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

3.4 请简述下存储过程及其优点？
存储过程是一组为了完成特定功能的SQL语句集，经过编译之后存储在数据库当中，
用户只需要调用存储过程即可（给特定的参数）

存储过程的优点
a.增强了SQL语句的灵活性
b.固定的业务模块化封装，较少开发重复性
c.执行速度很快
d.存储过程被做为一种安全机制，充分的得到了利用
举例说明如下：
需求：输出当前数据库的用户
CREATE PROCEDURE pro_count_mysql(OUT P_o_cnt int)
BEGIN
select count(*) into P_o_cnt from mysql.user;
END;
#调用
mysql> call pro_count_mysql(@s);
mysql> select @s;

?? 3.5 MySQL如实使用定时任务（event）？
事件(event)是MySQL在相应的时刻调用的过程式数据库对象。
一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。
事件和触发器类似，都是在某些事情发生的时候启动。
事件是根据调度事件来启动的，事件取代了原先只能由操作系统的计划任务来执行的工作，
而且MySQL的事件调度器可以精确到每秒钟执行一个任务，
而操作系统的计划任务(如：Linux下的CRON或Windows下的任务计划)只能精确到每分钟执行一次。

定时任务的使用

SHOW VARIABLES LIKE ‘%event_sche%’;
开起定时任务
SET GLOBAL event_scheduler = 1;
关闭定时任务
SET GLOBAL event_scheduler = 0;
如果需要长期开起定时任务需要在my.ini中配置 event_scheduler = on
这里设置的就是从2022-08-04-03 00:10:00 开始每1天执行一次。
创建定时任务的SQL语句如下。
CREATE EVENT Untitled
ON SCHEDULE
EVERY ‘1’ DAY STARTS ‘2022-08-04-03 00:10:00’ + INTERVAL ‘2’ SECOND
DO select * from mysql.user;

第四股 体系架构
4.1 请简述下MySQL的体系架构
MySQL 由数据库和数据库实例组成，是单进程多线程架构
1.数据库：
物理操作系统文件或者其它文件的集合，在 mysql中，数据库文件可以是 frm、myd、myi、ibd 等结尾的文件，当使用 bdb 存储引擎时候，不是 os 文件，是存放于内存中的文件。
2.数据库实例：
由数据库后台进程/线程以及一个共享内存区组成，共享内存可以被运行的后台进程/线程所共享。

4.2 MySQL的参数文件位置及如何配置？
Linux的位置：
Linux默认的参数文件为/etc/my.cnf，但是也不排除其他位置,在 mysqld 启动的时候，可以加上–defaults-file 参数，该参数指定了参数文件的名称。
[root@jeames ~]# mysql --help | grep ‘my.cnf’
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
Windows的位置：

4.3 MySQL参数持久化是什么？
MySQL 的动态参数可以在运行时通过 SET GLOBAL 命令来更改，
但是这种更改只会临时生效，到下次启动时数据库又会从配置文件中读取。
在MySQL8.0之前参数文件的动态修改不能写到配置文件，
为了改善这一问题MySQL8.0支持了动态修改参数并将其保存到一个新的参数文件文件中mysqld-auto.cnf，
默认在$datadir目录下，在所有的参数之后启动，权限为SYSTEM_VARIABLES_ADMIN or SUPER。

mysql> set persist max_connections=300;
数据字典查询
mysql> SELECT * FROM performance_schema.persisted_variables;
持久化清除：
对于已经持久化了变量，可通过 reset persist 命令清除掉，

注意，其只是清空 mysqld-auto.cnf 和 performance_schema.persisted_variables 中的内容
MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，
用其中的配置来覆盖缺省的配置文件。不建议手动修改该文件，因为其内容是 json 格式的，
其有可能导致数据库在启动过程中因解析错误而失败，有些参数不支持持久化的。
非持久化：Nonpersistent
持久化：PERSIST
只读持久化：PERSIST_ONLY

4.4 socket什么，如何用socket登陆
Linux系统下 本地连接 mysql 可以采用 linux 域套接字 socket 方式 ，需要一个套接字 socket 发文件，
可以由参数 socket控制，一般默认在/tmp 目录下，也可以通过如下方式查看

[root@jeames ~]# ps -ef | grep mysql
mysql> show variables like ‘socket’;
mysql> select @@socket;
如果 socket 录 文件丢失，那么会导致不能从本地登录 mysql ，可以通过重启的方式来重新生成。

mysql.sock是mysql的主机和客户机在同一host上的时候，
使用unix domain socket做为通讯协议的载体，它比tcp快。
对mysql.sock来说，其作用是程序与mysqlserver处于同一台机器，发起本地连接时可用。

[root@jeames ~]# mysql -uroot -p -S /tmp/mysql.sock

?? 4.5 MySQL表结构文件有哪些？
InnoDB 中用于存储数据的文件总共有两个部分，一是系统表空间文件，
包括 ibdata1、 ibdata2 等文件，其中存储了 InnoDB 系统信息和元数据，是所有表公用的，
另一个是.idb文件，是每张表独有的。
.ibd 文件就是每一个表独有的表空间，文件存储了当前表的数据，索引数据和插入缓冲等信息

innodb 包括 ibd 和 frm,当你启用了 innodb_file_per_table，表被存储在他们自己的表空间里
mysql> show variables like ‘innodb_file_per_table’;

MySQL8.0以后没有.frm了，元数据都存在系统表空间里ibdata1、 ibdata2

4.6 错误日志如何配置？
错误日志对 mysql的启动、运行、关闭过程进行了记录，
MySQL DBA 在遇到问题时候，第一时间应该查看这个错误日志文件，该文件不但记录了出错信息，
还记录了一些警告信息以及正确信息，这个 error 日志文件类似于 oracle 的 alert 文件。

可以通过“show variables like ‘log_error’;”命令查看错误日志的路径

【配置方法】
默认是启动的，一般以err做后缀名,需要在参数文件中配置
先找到参数文件
log_error = /usr/local/mysql/data/error.err

4.6 慢查询日志是什么？如何配置
当查询超过一定时间没有返回结果的时候，才会记录进慢查询日志。
? 慢查询日志可以帮助 DBA 找出执行效率缓慢的 SQ语句，为数据库优化工作提供帮助。
? 慢查询日志默认是不开启的，建议开启慢查询日志。
? 当需要进行采样分析时手工开启。
【慢查询日志开启】
关于慢查询日志，主要涉及到下面几个参数：
slow_query_log ：是否开启慢查询日志功能（必填）
slow_query_log_file：慢查询日志文件及位置
long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填）
也就是说，只有满足以上三个条件，“慢查询功能”才可能正确开启。

参数文件修改如下：
修改配置文件my.cnf，在[mysqld]下的下方加入
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
重启MySQL服务service mysqld restart即可

4.7 Redo与Binlog的区别是？
所有的数据库都是日志先行，先写日志，再写数据文件，所以才会有 redo log 的规则。
为了保证事务的持久性，mysql的InnoDB 采用了 WAL 技术，WAL 的全称是 Write-Ahead Logging

1.Redo Log：

Redo Log 是 InnoDB 存储引擎提供的一种物理日志结构，用来描述对底层数据页操作的具体内容，记录物理页的修改，主要用于实现崩溃恢复（crash-recover），并提升磁盘操作效率。

2.Binlog：

Binlog是 MySQL Server 本身提供的一种逻辑日志，和具体存储引擎无关，描述的是数据库所执行的 SQL 语句或数据变更情况，主要用于数据复制和增量恢复。