第六周作业

最新推荐文章于 2024-09-20 11:12:39 发布

Luke657

最新推荐文章于 2024-09-20 11:12:39 发布

阅读量941

点赞数 15

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_64673497/article/details/137472709

版权

1. 总结关系型数据库相关概念，关系，行，列，主键，唯一键，域。

关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
行row：表中的每一行，又称为一条记录record
列column：表中的每一列，称为属性，字段，域field
主键Primary key：PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段，一张表只有一个主键, 主键字段不能为空NULL
唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而且UK字段可以为NULL
域domain：属性的取值范围，如，性别只能是'男'和'女'两个值，人类的年龄只能0-150

2. 总结关联类型，1对1，1对多，多对多关系。可以自行设计表进行解释。

一对一联系(1:1): 在表A或表B中创建一个字段﹐存储另一个表的主键值如: 一个人只有一个身份证
一对多联系(1:n)：外键, 如: 部门和员工
多对多联系(m:n)：增加第三张表, 如: 学生和课程

例：1对1

学生表student：
id name
1001 张三
1002 张四

学生卡表card

id name

1001 card1

1002 card2

#添加外键

学生卡表card

id name student_id（设置为唯一键）

1001 card1 1001

1002 card2 1002

例：1对多

表A：学生表student（子表）

id name class_id（外键非空：班级id）
1001 张三 111
1002 张四 222
1003 王五 111
1004 赵六 111

表B：班级表class（父表）

id name
111 class1
222 class2
一个班级对应多个学生，一个学生只能对应一个班级

例：多对多

学生表student：

id name
1001 张三
1002 张四
1003 王五
1004 赵六

课程表course：

id name
111 java
222 mysql

#额外创建一个关系表

学生课程关系表 student_course_relation

student_id course_id
1001 111
1001 222
1002 111
1002 222

3. 总结mysql设计范式

第一范式：1NF

无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，确保每一列的原子性。除去同类型的字段，就是无重复的列

下面这个例子就不符合第一范式：

ID	课程名称
1	语文，数学，英语

说明:第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库

第二范式：2NF

第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常为表加上每行的唯一标识主键PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键

订单号	商品编号	商品名	数量	单价
1001	001	西瓜	5	6.0
1002	002	草莓	10	8.0

订单号和商品编号组合作为主键，但商品名只依赖于商品编号而不是订单号和商品编号，因此不符合第二范式。

第三范式：3NF

满足第三范式必须先满足第二范式属性，非主键属性不依赖于其它非主键属性。第三范式要求一个数据表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系

学号	姓名	班级	班主任	性别
001	张三	一班	李老师	男
002	李四	二班	王老师	男
003	王五	一班	李老师	男

班级和班主任都依赖于班级，因此存在传递依赖关系。

4. 总结Mysql多种安装方式，及安全加固，并总结mysql配置文件, 完成将server和client端的mysql配置默认字符集为utf8mb4;

4.1 Mysql安装

安装方式：

程序包管理器管理的程序包
源代码编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用

RPM 包安装 MySQL

CentOS 安装光盘

项目官方：https://downloads.mariadb.org/mariadb/repositories/
国内镜像：https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/
https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/

CentOS 8：安装光盘直接提供

mysql-server：8.0
mariadb-server : 10.3

CentOS 7：安装光盘直接提供

mariadb-server：5.5 服务器包
mariadb 客户端工具包

CentOS 6：

mysql-server：5.1 服务器包
mysql 客户端工具包

范例: CentOS 7 利用yum源安装MySQL5.7

[root@centos7 ~] #tee /etc/yum.repos.d/mysql.repo <<EOF

[mysql]

name = mysql5.7

baseurl = https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-

x86_64/

gpgcheck = 0

EOF

[root@centos7 ~] #yum -y install mysql-community-server

[root@centos7 ~] #systemctl enable --now mysqld

[root@centos7 ~] #ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 *:22 *:*

LISTEN 0 100 127 .0.0.1:25 *:*

LISTEN 0 128 [::]:22 [::]:*

LISTEN 0 100 [::1]:25 [::]:*

LISTEN 0 80 [::]:3306 [::]:*

[root@centos7 ~] #mysql

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password:

NO)

[root@centos7 ~] #grep password /var/log/mysqld.log

2021 -01-27T00 :45:09.953242Z 1 [Note] A temporary password is generated for

root@localhost: pe%b #S8ah)j-

2021 -01-27T00 :46:09.491494Z 2 [Note] Access denied for user 'root' @ 'localhost'

(using password: NO)

# 修改初始密码方法 1

[root@centos7 ~] #mysql -uroot -p'pe%b#S8ah)j-'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5 .7.33

Copyright (c) 2000 , 2021 , Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 使用初始密码登录无法执行操作 , 需要修改密码后才可以

mysql> status

ERROR 1820 (HY000): You must reset your password using ALTER USER statement

before executing this statement.

# 修改简单密码不符合密码策略

mysql> alter user root@ 'localhost' identified by 'Magedu2021' ;

ERROR 1819 (HY000): Your password does not satisfy the current policy

requirements

# 修改为复杂密码

mysql> alter user root@ 'localhost' identified by 'Magedu0!' ;

Query OK, 0 rows affected (0.00 sec)

mysql> status

--------------

mysql Ver 14 .14 Distrib 5 .7.33, for Linux (x86_64) using EditLine wrapper

Connection id: 4

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5 .7.33

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 3 min 38 sec

Threads: 1 Questions: 8 Slow queries: 0 Opens: 106 Flush tables: 1 Open

tables: 99 Queries per second avg: 0 .036

--------------

mysql> exit

# 修改初始密码方法 2

[root@centos7 ~] #mysqladmin -uroot -p'pe%b#S8ah)j-' password 'Magedu0!'

mysqladmin: [Warning] Using a password on the command line interface can be

insecure.

Warning: Since password will be sent to server in plain text, use ssl connection

to ensure password safety

范例: Ubuntu18.04 利用默认仓库安装 MySQL 5.7

[root@ubuntu1804 ~] #apt install mysql-server

[root@ubuntu1804 ~] #systemctl status mysql.service

● mysql.service - MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:

enabled)

Active: active (running) since Wed 2021 -01-27 15 :41:33 CST; 10min ago

Main PID: 2115 (mysqld)

Tasks: 28 (limit: 2290 )

CGroup: /system.slice/mysql.service

└─2115 /usr/sbin/mysqld --daemonize --pid-file = /run/mysqld/mysqld.pid

Jan 27 15 :41:33 ubuntu1804.magedu.org systemd[1]: Starting MySQL Community

Server...

Jan 27 15 :41:33 ubuntu1804.magedu.org systemd[1]: Started MySQL Community

Server.

[root@ubuntu1804 ~] #mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5 .7.32-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000 , 2020 , Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status

--------------

mysql Ver 14 .14 Distrib 5 .7.32, for Linux (x86_64) using EditLine wrapper

Connection id: 3

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5 .7.32-0ubuntu0.18.04.1 (Ubuntu)

Protocol version: 10 2.2.3 初始化脚本提高安全性

运行脚本： mysql_secure_installation

范例 : 针对 MySQL5.6 前版本进行安全加固

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /var/run/mysqld/mysqld.sock

Uptime: 10 min 43 sec

Threads: 1 Questions: 10 Slow queries: 0 Opens: 105 Flush tables: 1 Open

tables: 98 Queries per second avg: 0 .015

例：脚本安装

#! /bin/bash



. /etc/init.d/functions 
SRC_DIR=`pwd`
#MYSQL='mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz'
MYSQL='mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz'
COLOR='echo -e \E[01;31m'
END='\E[0m'
MYSQL_ROOT_PASSWORD=magedu


check (){

if [ $UID -ne 0 ]; then
  action "当前用户不是root,安装失败" false
  exit 1
fi

cd  $SRC_DIR
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
        $COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
        action "数据库已存在，安装失败" false
        exit
else
    return
fi
} 

install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
    yum  -y -q install libaio numactl-libs ncurses-compat-libs
    cd $SRC_DIR
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R  root.root /usr/local/mysql/
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; action "创建mysql用户"; }
        
    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
    ln -s /usr/local/mysql/bin/* /usr/bin/
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
    [ -d /data ] || mkdir /data 
    mysqld --initialize --user=mysql --datadir=/data/mysql 
    cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
    chkconfig --add mysqld
    chkconfig mysqld on
    service mysqld start
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
    sleep 3
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
    mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
    action "数据库安装完成" 
}


check
install_mysql

4.2 安全加固

运行脚本：mysql_secure_installation

设置数据库管理员 root 口令

禁止 root 远程登录

删除 anonymous 用户帐号

删除 test 数据库

范例: 针对MySQL5.6前版本进行安全加固

root@centos7 ~] #mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5 .6.51 MySQL Community Server (GPL)

Copyright (c) 2000 , 2021 , Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| information_schema |

| mysql |

| performance_schema |

+ -------------------- +

3 rows in set (0.00 sec)

mysql> select user,host from mysql.user;

+ ------ + -------------------------- +

| user | host |

+ ------ + -------------------------- +

| root | 127 .0.0.1 |

| root | ::1 |

| | centos7.wangxiaochun.com |

| root | centos7.wangxiaochun.com |

| | localhost |

| root | localhost | + ------ + -------------------------- +

6 rows in set (0.00 sec)

mysql> exit

Bye

[root@centos7 ~] #

[root@centos7 ~] #mysql -uxxx

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5 .6.51 MySQL Community Server (GPL)

Copyright (c) 2000 , 2021 , Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status

--------------

mysql Ver 14 .14 Distrib 5 .6.51, for Linux (x86_64) using EditLine wrapper

Connection id: 3

Current database:

Current user: xxx@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5 .6.51 MySQL Community Server (GPL)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 5 min 2 sec

Threads: 1 Questions: 11 Slow queries: 0 Opens: 67 Flush tables: 1 Open

tables: 60 Queries per second avg: 0 .036

--------------

mysql> exit

Bye

[root@centos7 ~] #file `which mysql_secure_installation `

/usr/bin/mysql_secure_installation: Perl script, ASCII text executable

[root@centos7 ~] #mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we 'll need the current

password for the root user. If you 've just installed MySQL, and

you haven 't set the root password yet, the password will be blank,

so you should just press enter here. Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL

root user without the proper authorisation.

Set root password? [Y/n] y

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MySQL installation has an anonymous user, allowing anyone

to log into MySQL without having to have a user account created for

them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] y

... Success!

Normally, root should only be allowed to connect from 'localhost' . This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y

... Success!

By default, MySQL comes with a database named 'test' that anyone can

access. This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] y

- Dropping test database...

ERROR 1008 (HY000) at line 1 : Can 't drop database ' test '; database doesn' t exist

... Failed! Not critical, keep moving...

- Removing privileges on test database...

... Success!

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] y

... Success!

All done ! If you 've completed all of the above steps, your MySQL

installation should now be secure.

Thanks for using MySQL!

Cleaning up...

[root@centos7 ~] #

4.3 Mysql配置文件

服务器端(mysqld)：工作特性有多种配置方式

1 、命令行选项：

2 、配置文件：类 ini 格式 , 集中式的配置，能够为 mysql 的各应用程序提供配置信息

服务器端配置文件：

/etc/my.cnf #Global 选项

/etc/mysql/my.cnf #Global 选项

~/.my.cnf #User-specific 选项

配置文件格式：

[mysqld]

[mysqld_safe]

[mysqld_multi]

[mysql]

[mysqladmin]

[mysqldump]

[server]

[client]

格式：

parameter = value

说明：

_ 和 - 相同

1 ， ON ， TRUE 意义相同， 0 ， OFF ， FALSE 意义相同 , 无区分大小写

4.4 将server和client端的mysql配置默认字符集为utf8mb4

[root@Rocky8 ~]# vim /etc/my.cnf
#针对mysql客户端
[mysql]
default-character-set=utf8mb4

#针对所有MySQL客户端
[client]
default-character-set=utf8mb4

5. 掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin.创建host表，字段（id，host，ip，cname等）

获取 SQL 命令使用帮助 ：

官方帮助：

https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

https: //dev.mysql.com/doc/refman/5.7/en/sql-statements.html

查看帮助：

#创建testdb数据库，设置字符集为utf8，排序规则utf8_bin。

mysql> create database testdb character set utf8 collate utf8_bin;

#创建host表，字段（id,host,ip.cname等）

mysql>create table host(id int auto_increment primary key, host varchar(255) not full, ip varchar(15)not full, cname varchar(255) )character set utf8 collate utf8_bin;

6. 根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。

6.1 DDL语句

创建表 CREATE TABLE
查看表 SHOW TABLE
修改表和删除表 ALTER TABLE ; DROP TABLE

例：

创建表和字段名，并设置属性

19:02:10(root@localhost) [testdb]> CREATE TABLE student (
-> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(20) NOT NULL,
-> age tinyint UNSIGNED,
-> #height DECIMAL(5,2),
-> gender ENUM('M','F') default 'M'
-> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

查询表结构desc student

删除表

DROP TABLE [ IF EXISTS ] 'tbl_name' ;

6.2 DML语句

INSERT 表插入数据

19:06:28(root@localhost) [testdb]> insert student (name,age) value("huang",26)；

UPDATE 表更新数据

19:09:11(root@localhost) [testdb]> update student set age='18'where id='10'；

DELETE 表删除数据

19:09:32(root@localhost) [testdb]> delete from student where id='10'；

7. 总结mysql架构原理

MySQL 是 C/S 架构的

连接器（connectors）： 可供 Native C API 、 JDBC 、 ODBC 、 NET 、 PHP 、 Perl、Python、 Ruby 、 Cobol 等连接 mysql ；

服务层

MySQL Server的核心，主要包括系统管理和控制工具、连接池、SQL接口、解析器、查询优化器、缓存六个部分

连接池（connection pool）：

用于接受客户端发送的各自SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。

MySQL是单进程多线程模型的。分为长连接和短连接。

长连接：使用MySQL客户端等于数据库后，直到使用exit退出数据库
短连接：使用mysql -e选项，客户端向服务端申请运行一个命令后立即退出

Authentication :认证。用户发送的账号密码是否正确。

Thread reuse： 线程重用。当一个用户连接进来以后要用一个线程来响应它，而后当用户退出时，这个线程可能并非被销毁，而是把它清理完后，重新收归到线程池中的空闲线程中，以完成线程重用。

Connection limit:连接限制。线程池的大小决定了连接并发数量的上限，例如，最多容纳100线程，一旦达到上限后，后续的请求只能排队或拒绝连接。

Check memory：检测内存

caches: 线程缓存

SQL接口：

服务器收到SQL 语句后，要对语句完成执行，首先要能理解 sql 语句需要有 sql 解释器或叫 sql 接口 sql interface就可理解为是整个 mysql 的外壳，就像 shell 是 linux 操作系统的外壳一样；

解析器（parser） ：

负责将请求的SQL解析升成一个解析树，然后根据MySQL规则进一步检查解析树是否合法。

查询优化器（Optimizer）：

当解析树通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

缓存（Cache&Buffer）:

缓存机制是由一系列小缓存组成的，比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存中有查询结果，就可以直接去查询缓存中读数据。

存储引擎：

存储引擎负责MySQL中数据的存储和提取，与底层系统文件进行交互。

最常见的存储引擎是MyISAM和InnoDB。

系统文件层：

负责将数据库的数据和日志存储在文件系统上，并完成与存储引擎的交互，是文件的物理存储层。主要包括日志文件，数据文件，配置文件，pid文件，socket文件等。

8. 总结myisam和Innodb存储引擎的区别。

MyISAM 引擎特点:

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5 前默认的数据库引擎

MyISAM 引擎文件

tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件

InnoDB 引擎特点

行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎

InnoDB 数据库文件

所有InnoDB表的数据和索引放置于同一个表空间中

数据文件：ibdata1, ibdata2,存放在datadir定义的目录下

表格式定义：tb_name.frm,存放在datadir定义的每个数据库对应的目录下

每个表单独使用一个表空间存储表的数据和索引

两类文件放在对应每个数据库独立目录中

数据文件(存储数据和索引)：tb_name.ibd

表格式定义：tb_name.frm

9. 总结mysql索引作用，同时总结哪些查询不会使用到索引。

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键 key ，索引通过存储引擎实现

优点：

索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序 I/O

缺点：

占用额外空间，影响插入速度

索引类型：

B+ TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引: 是否是多个字段的索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

以下查询不会使用到索引：

全表扫描：当数据量大或者查询列未被索引时，可能会选择全表扫描
使用函数或者表达式：在查询条件中对列使用函数或者表达式可能导致索引失效
非最左前缀：对于复合索引，如果查询条件没有涉及最左边的列，那么索引可能不会被使用
数据类型不匹配：查询条件中的数据类型与索引列的数据类型不匹配可能导致索引失效
OR条件：当使用OR条件时，如果每个条件涉及的列都未被同一个复合索引覆盖，则可能不会使用索引
Like查询：当LIKE查询的模式以通配符开始时，索引可能不会被使用
NULL值：对于包含NULL值的列，索引可能不会被使用。

10. 总结事务ACID事务特性

A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律(N50周启皓语录)
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

11. 总结事务日志工作原理。

事务日志：transaction log

redo log：记录某数据块被修改后的值，数据更新前先记录redo log( WALWrite Ahead Log ),可以用来恢复未写入data file的已成功事务更新的数据
undo log：保存与执行的操作相反的操作,即记录某数据被修改前的值，可以用来在事务失败时进行rollback

Innodb 事务日志相关配置：

命令：show variables like '%innodb_log%';

innodb_log_file_size 50331648 # 每个日志文件大小

innodb_log_files_in_group 2 # 日志组成员个数

innodb_log_group_home_dir ./ # 事务文件路径

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性

0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务

2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

高并发业务行业最佳实践，是使用第三种折衷配置（=2）：

1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快

2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

12. 总结mysql日志类型，并说明如何启动日志。

mysql日志类型：

事务日志 transaction log：

包括redo log 和undo log。通过设置innodb_flush_log_at_trx_commit=0|1|2 决定事务日志何时被写入并刷新到磁盘中。默认启动

错误日志 log_error：

mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

默认启用。

日志路径查询：SHOW GLOBAL VARIABLES LIKE 'log_error' ;

记录哪些警告信息至错误日志文件

#CentOS7 mariadb 5.5         默认值为1

#CentOS8 mariadb 10.3         默认值为2

log_warnings=0|1|2|3...           #MySQL5.7之前

log_error_verbosity=0|1|2|3...           #MySQL8.0

通用日志 general_log：

记录对数据库的通用操作，包括:错误的SQL语句。

通用日志相关设置

启用：general_log=ON

日志路径：general_log_file=HOSTNAME.log

日志存储为哪种类型：log_output=TABLE|FILE|NONE

日志当前存放路径查询：SHOW GLOBAL VARIABLES LIKE 'log_output';

慢查询日志slow_query_log：

记录执行查询时长超出指定时长的操作

相关变量：

slow_query_log= ON |OFF           # 开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件

long_query_time=N          # 慢查询的阀值，单位秒 , 默认为 10s

slow_query_log_file=HOSTNAME- slow .log           # 慢查询日志文件

log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,

query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# 上述查询类型且查询时长超过 long_query_time ，则记录日志

log_queries_not_using_indexes= ON           #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF ，即不记录

log_slow_rate_limit = 1          #多少次查询才记录， mariadb 特有

log_slow_verbosity= Query_plan, explain          #记录内容

log_slow_queries = OFF           # 同 slow_query_log ， MariaDB 10.0/MySQL 5.6.1 版后已删除

二进制日志（备份）：

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型

功能：通过"重放"日志文件中的事件来生成数据副本

注意：建议二进制日志和数据文件分开存放

二进制日志记录三种格式：

基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下），日志量较少
基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

格式配置

show variables like 'binlog_format';

二进制日志文件的构成 ：

有两类文件

1. 日志文件： mysql|mariadb-bin. 文件名后缀，二进制格式 , 如： on.000001,mariadb-bin.000002

2. 索引文件： mysql|mariadb-bin.index ，文本格式 , 记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量：

sql_log_bin= ON |OFF：         # 是否记录二进制日志，默认 ON ，支持动态修改，系统变量，而非服务器选项

log_bin=/PATH/BIN_LOG_FILE：         # 指定文件位置；默认 OFF ，表示不启用二进制日志功能，上述两项都开

启才可以

binlog_format=STATEMENT| ROW |MIXED：         # 二进制日志记录的格式， mariadb5.5 默认 STATEMENT

max_binlog_size= 1073741824 ：         # 单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为 1G

# 说明：文件达到上限时的大小未必为指定的精确值

binlog_cache_size= 4 m          # 此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）

max_binlog_cache_size= 512 m          # 限制用于缓存多事务查询的字节大小。

sync_binlog= 1 | 0 ：         # 设定是否启动二进制日志即时同步磁盘功能，默认 0 ，由操作系统负责同步日志到磁盘

expire_logs_days=N：         # 二进制日志可以自动删除的天数。默认为 0 ，即不自动删除

二进制日志相关配置 :

查看 mariadb 自行管理使用中的二进制日志文件列表，及大小

SHOW {BINARY | MASTER } LOGS

查看使用中的二进制日志文件

SHOW MASTER STATUS

在线查看二进制文件中的指定内容

SHOW BINLOG EVENTS [ IN 'log_name' ] [ FROM pos] [ LIMIT [ offset ,] row_count]

范例：

show binlog events in 'mysql-bin.000001' from 6516 limit 2 , 3

mysqlbinlog ：二进制日志的客户端命令工具，支持离线查看二进制日志

命令格式：

mysqlbinlog [OPTIONS] log_file…

--start-position = # 指定开始位置

--stop-position = #

--start-datetime = #时间格式： YYYY-MM-DD hh:mm:ss

--stop-datetime =

--base64-output [ = name]

-v -vvv

二进制日志事件的格式：

# at 328

#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1

exec_time=0 error_code=0

use `mydb` /*!*/ ;

SET TIMESTAMP = 1446712300 /*!*/ ;

CREATE TABLE tb1 (id int , name char ( 30 ))

/*!*/ ;

事件发生的日期和时间： 151105 16 :31:40

事件发生的服务器标识： server id 1

事件的结束位置： end_log_pos 431

事件的类型： Query

事件发生时所在服务器执行此事件的线程的 ID ： thread_id= 1

语句的时间戳与将其写入二进制文件中的时间差： exec_time= 0

错误代码： error_code= 0

事件内容：

GTID ： Global Transaction ID ， mysql5 .6 以 mariadb10 以上版本专属属性： GTID

范例：同步远程主机的二进制日志

# 从 10.0.0.8 远程主机实时同步从指定的二进制日志 binlog.000002 开始向后进行同步到当前目录

[root@centos8 data] #mysqlbinlog -R --host=10.0.0.8 --user=test --password=123456

--raw --stop-never binlog.000002

清除指定二进制日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

范例：

PURGE BINARY LOGS TO 'mariadb-bin.000003' ; # 删除 mariadb-bin.000003 之前的日志

PURGE BINARY LOGS BEFORE '2017-01-23' ;

PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30' ;

删除所有二进制日志， index 文件重新记数

RESET MASTER [ TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从 # 开始记数，默认从1开始，一般是 master 主机第一次启动时执行， MariaDB 10.1.6 开始支持 TO #

切换日志文件：

FLUSH LOGS ;

范例: 切换二进制日志

[root @centos8 ~] #mysqladmin flush-binary-log

[root @centos8 ~] #mysqladmin flush-logs

[root @centos8 ~] #mysql

MariaDB [hellodb]> flush logs ;

13. 总结二进制日志的不同格式的使用场景。

二进制日志记录三种格式：

基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下），日志量较少
基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

14. 总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证。

备份类型：

1.完全备份，部分备份

完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表

2.完全备份、增量备份、差异备份

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

增量和差异备份的基础和前提是完全备份

注意：二进制日志文件不应该与数据文件放在同一磁盘

3.冷、温、热备份

冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行

MyISAM ：温备，不支持热备

InnoDB ：都支持

4.物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中"导出"数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

例：mysqldump数据备份和还原

mysql> use hellodb;

mysql> select * from students;

+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+

[root@localhost ~]# mysqldump -uroot -A --single-transaction --source-data=2 --flush-privileges > /backup/all.sql

25 rows in set (0.00 sec)
mysql> update students set classid=6 where stuid>=20;

mysql> select * from students;

.....省略

| 20 | Diao Chan | 19 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 6 | NULL |
| 23 | Ma Chao | 23 | M | 6 | NULL |
| 24 | Xu Xian | 27 | M | 6 | NULL |
| 25 | Sun Dasheng | 100 | M | 6 | NULL |
+-------+---------------+-----+--------+---------+-----------+

mysql> update teachers set age=18 where tid=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 18 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

mysql> source /backup/all.sql

mysql> select * from students;

| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+

mysql> select * from teachers;

+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+

例：xtrabackup数据备份和还原

1 安装xtrabackup包

#先安装MySQL5.7和xtrabackup包

[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm

2 在原主机做完全备份到/backup

[root@centos8 ~]#mkdir /backup

[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base

#目标主机无需创建/backup目录,直接复制目录本身

[root@centos8 ~]#scp -r /backup/ 目标主机:/

3 在目标主机上还原

1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务

[root@centos8 ~]#yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm

[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base

2）复制到数据库目录

注意：数据库目录必须为空，MySQL服务不能启动

[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base

3）还原属性

[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql

4）启动服务

[root@centos8 ~]#service mysqld start

15. 编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份

[root@rocky8 ~]# crontab -l
0 2 * * 1,5 backup_per_day.sql

0 2 * * 2-4 backup_per_day_increment.sql

[root@rocky8 ~]# cat backup_per_day.sql

#! /usr/bin/bash
base_path=/mysql/db/
timestamp=`date +%Y%m%d`
mysql_output=${base_path}/test-table
database_name=test
for db in `mysql -uroot -e 'show databases' |grep -Ev '^(Datebase|information_schema|performance_schema|sys)$'`;do

  xtrabackup -uroot --backup --databases='$database_name.$db' --target-dir= $base_path/$timestamp/base/

done

[root@rocky8 ~]# cat backup_per_day_increment.sql

#! /usr/bin/bash

.source backup_per_day.sql
base_path=/mysql/db/
inc_timestamp=`date +%Y%m%d`
mysql_output=${base_path}/test-table
database_name=test

for db in `mysql -uroot -e 'show databases' |grep -Ev '^(Datebase|information_schema|performance_schema|sys)$'`;do

  xtrabackup -uroot --backup --databases='$database_name.$db' --target-dir= $base_path/$inc_timestamp/$inc/ --incremental-basedir=$base_path/$timestamp/base/

done