今日目标
-
数据库日志
-
数据库的备份
-
数据库对象——视图、索引
-
SQL编程
数据库日志
在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了。
数据库中数据丢失或被破坏可能原因：
-
误删除数据库
-
数据库工作时，意外断电或程序意外终止
-
由于病毒造成的数据库损坏或丢失
-
文件系统损坏后，系统进行自检操作
-
升级数据库时，命令语句不严格
-
设备故障等等
-
自然灾害
-
盗窃
如何避免：数据库日志，日志会记录我们需要的各种数据的变化，一旦出现问题，可以通过日志，进行数据的回溯和溯源，从而将数据恢复过来。
MySQL的日志
MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：
| 日志类型 | 记入文件中的信息类型 |
|---|---|
| 错误日志 | 记录启动、运行或停止时出现的问题 |
| 查询日志 | 记录建立的客户端连接和执行的语句 |
| 二进制日志 | 记录所有更改数据的语句。主要用于复制和即时点恢复 |
| 慢日志 | 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询 |
| 事务日志 | 记录InnoDB等支持事务的存储引擎执行事务时产生的日志 |
错误日志
错误日志，顾名思义，就是用来记录数据库错误的日志。
启动、运行、关闭等等操作中，如果DBMS出现了错误信息，则会记录到错误日志中。
在window系统中，错误日志会被写入data\主机名称.err，在这个日志文件中记录错误信息
在Linux中，随着安装的方式不同，该日志文件可能存储的位置也会发生变化
show variables like "%log_error%";
----------------------------+------------------------------------------------------+
| Variable_name | Value |
+----------------------------+------------------------------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | D:\dev_soft\mysql-8.0.20-winx64\data\liujianhong.err |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+------------------------------------------------------+通用查询日志
会记录各种查询及相关日志信息，需要手动开启
show variables like "general_log";
+------------------+------------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------------+
| general_log | OFF |
| general_log_file | D:\dev_soft\mysql-8.0.20-winx64\data\liujianhong.log |
+------------------+------------------------------------------------------+在配置文件可以开启查询日志
慢查询日志
慢日志，主要是用来做软件优化的，将查询速度较慢的SQL记录下来，以方便于开发者进行SQL优化。
默认没有被开启，如果要开启，则需要在配置文件中添加对应项，开启服务。
# 默认没有启用慢查询，为了服务器调优，建议开启
mysql> SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';# 开启方法,当前生效，永久有效配置文件中设置
SET GLOBAL slow_query_log=ON; # 使用 mysqldumpslow 命令获得日志中显示的查询摘要来处理慢查询日志
# mysqldumpslow slow.log# 那么多久算是慢呢？ # 如果查询时长超过long_query_time的定义值（默认10秒），即为慢查询：
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';二进制日志
bin_log日志，记录数据变量的日志，所以主要记录数据增删改。主要用来做数据备份、恢复等等一系列功能。
注意，不同的版本中，是否开启。
查看有哪些二进制日志文件：
mysql> SHOW BINARY LOGS;查看当前正在使用的是哪一个二进制日志文件：
mysql> SHOW MASTER STATUS;查看二进制日志内容：
mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002';##该语句还可以加上Position（位置），指定显示从哪个Position（位置）开始：
mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002' FROM 203;使用命令mysqlbinlog查看二进制日志内容：mysqlbinlog [options] log-files
MySQL备份
根据日志，做主从复制，就可以实现数据的备份。
根据服务器状态，可以分为热备份、温备份、冷备份
-
热备份：读、写不受影响；
-
温备份：仅可以执行读操作；
-
冷备份：离线备份；读、写操作均中止；
-
从对象来分，可以分为物理备份与逻辑备份
-
物理备份：复制数据文件；
-
逻辑备份：将数据导出至文本文件中；
从数据收集来分，可以完全备份、增量备份、差异备份
-
完全备份：备份全部数据；
-
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
-
差异备份：仅备份上次完全备份以来变化的数据；
mysqldump工具
mysqldump是mysql官方提供的一个逻辑备份工具，可以通过这个工具，实现数据库的备份
mysqldump -u用户 -p密码 -B 数据库 > 存储位置
存在SQL文件，如何还原数据。
第一种方式：使用SQL粘贴执行
第二种，使用MySQL命令
mysql -u用户 -p密码 -B 数据库 < xxx.sql 内容回顾：
-
数据库日志
-
MySQL备份问题
MySQL索引视图
视图（view）
数据库对象——view，可以看成一个特殊的表（但是注意，视图不是表）。
也就是在开发者，将查询结果封装成一个视图，我们就可以将这个结果当做一张表来使用。
视图通过以定制的方式显示来自一个或多个表的特定数据
视图是一种数据库对象，用户可以像查询普通表一样查询视图
视图内其实没有存储任何数据，它只是对表的一个查询
视图的定义保存在数据字典内，创建视图所基于对表称为“基表”
视图的优点
-
简化查询，不用做复杂的查询，而是将从视图中查询需要的结果【视图不是表，不能加快查询速度】
-
在安全性而言，视图有一定的保护作用，通过视图可以隐藏或者显示需要显示和隐藏的字段
优点：
-
提供了灵活一致级别安全性。
-
隐藏了数据的复杂性
-
简化了用户的SQL指令
-
通过重命名列，从另一个角度提供数据
需要注意，视图主要是用来展示数据，查询数据的，不是用来更新数据【增删改】。除非有必要，否则不要修改视图中数据。
定义视图
create view [可选项] 视图名称 as (查询语句)# 定义一个视图
create view v_emp_dept2 as select emp.*, dept.name as deptName, dept.intro as intro from emp, dept where emp.dept
_id = dept.id;# 视图操作起来和表没有区别
select * from v_emp_dept2;视图的注意事项
-
视图必须有唯一命名
-
在mysql中视图的数量没有限制
-
创建视图必须从管理员那里获得必要的权限
-
视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
-
在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。
-
视图不能索引，也不能关联触发器或默认值
-
视图可以和表同时使用
修改视图
使用CREATE OR REPLACE VIEW 语句修改EMP_V_10 视图. 为每个列指定列名.
CREATE OR REPLACE VIEW emp_v_10
(id, name, sal, dept_id)
AS SELECT id,name,
salary, dept_id
FROM employees
WHERE dept_id = 10;在CREATE VIEW 语句中字段与子查询中的字段必须一一对应，否则就别指定别名，或在子查询中指定别名 使用ALTER VIEW 语句修改EMP_V_10 视图. 为每个列指定列名.
ALTER VIEW emp_v_10
(id, name, sal, dept_id)
AS SELECT id,name,
salary, dept_id
FROM employees
WHERE dept_id = 10; 在CREATE VIEW 语句中字段与子查询中的字段必须一一对应，否则就别指定别名，或在子查询中指定别名 创建复杂视图，创建一个从两个表中查询数据，并进行分组计算的复杂视图.
CREATE VIEW dept_sum_vu_10
(name, minsal, maxsal, avgsal)
AS SELECT d.name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.dept_id = d. id
AND e.dept_id = 10;修改视图的结构，没有修改是数据！！！
视图的数据是表中的数据，视图本身是没有数据的！
视图是定义在数据字典中的。
不要对视图的数据修改增删改，它就是用来查询的！！！！
删除视图
drop view 视图名称;课堂案例：1、在数据库example下创建college表。College表内容如下所示
字段名 字段描述 数据类型 主键 外键 非空 唯一 自增
number 学号 INT(10) 是 否 是 是 否
name 姓名 VARCHAR(20) 否 否 是 否 否
major 专业 VARCHAR(20) 否 否 是 否 否
age 年龄 I NT(5) 否 否 否 否 否
CREATE TABLE college(
number INT(10) NOT NULL UNIQUE PRIMARY KEY COMMENT '学号',
name VARCHAR(20) NOT NULL COMMENT '姓名',
major VARCHAR(20) NOT NULL COMMENT '专业',
age INT(5) COMMENT '年龄'
); 2、在student表上创建视图college_view。视图的字段包括student_num、student_name、 student_age和department。ALGORITHM设置为MERGE类型，并且为视图加上WITH LOCAL CHECK OPTION条件
CREATE ALGORITH=MERGE VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major FROM college
WITH LOCAL CHECK OPTION;3、查看视图college_view的详细结构
SHOW CREATE VIEW college_view \G4、 更新视图。向视图中插入3条记录。记录内容如下表所示
umer name major age
0901 张三 外语 20
0902 李四 计算机 22
0903 王五 计算机 19
INSERT INTO college_view VALUES(0901,'张三',20,'外语');
INSERT INTO college_view VALUES(0902,'李四',22,'计算机');
INSERT INTO college_view VALUES(0903,'王五',19,'计算机');5 、修改视图，使其显示专业为计算机的信息，其他条件不变 方法一：
CREATE OR REPLACE ALGORITHM=UNDEFINED VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major
FROM college WHERE major=’计算机’
WITH LOCAL CHECK OPTION;
方法二：
ALTER ALGORITHM=UNDEFINED VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major
FROM college WHERE major=’计算机’
WITH LOCAL CHECK OPTION;6 、删除视图college_view
DROP VIEW college_view;索引（index）
索引也是一种数据库对象，这种数据库对象非常特殊，索引主要用来加快数据查询和检索的速度。
索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。
索引底层采用特殊的数据结构和相关优秀的算法，实现快速检索数据的能力。
如果表的数据当作一本书，索引可以看成这本书的目录。
索引优缺点
优点：索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
缺点：
-
占据对应的存储空间
-
数据发生了更改，索引也要进行更新和维护
索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。
索引分类
根据索引在底层的存储方式，分为聚簇索引和非聚簇索引。
MySQL中，innodb引擎中，主键索引就是一种聚簇索引。而其他的普通索引都是非聚簇索引。
根据索引的功能：普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等
索引的设计原则
为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。本小节将向读者介绍一些索引的设计原则。
-
选择惟一性索引
-
为经常需要排序、分组和联合操作的字段建立索引
-
为常作为查询条件的字段建立索引
-
限制索引的数目
-
尽量使用数据量少的索引
-
尽量使用前缀来索引
-
删除不再使用或者很少使用的索引
创建索引
创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有三种方式，这三种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。
-- 普通索引的创建方式
create index 索引名称 on 表名称(字段名称); -- 可以通过前缀表示对应的索引
create unique|Fulltext|其他类型 index 索引名称 on 表名称(字段名称);-- 创建表的同时创建索引
create table t_name (
字段 类型 约束,
……
-- 主键索引
primary key(主键字段),
-- 普通所以
index 索引名称(字段 [,其他字段]),
-- 其他索引
unique index 索引名称(字段)
) 在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：help create index
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );
1．创建普通索引
CREATE INDEX index_name ON table(column(length))2．创建惟一性索引
CREATE UNIQUE INDEX indexName ON table(column(length))3．创建全文索引
CREATE FULLTEXT INDEX index_content ON article(content)4．创建单列索引
CREATE INDEX index3_name on index3 (name(10));5．创建多列索引 6．创建空间索引 # 修改表结构的方式添加索引
ALTER TABLE table_name ADD [索引类型] INDEX index_name ON (column)在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下：
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名（属性名 [ (长度) ] [ ASC | DESC]）;1．创建普通索引
ALTER TABLE table_name ADD INDEX index_name (column(length))2．创建惟一性索引
ALTER TABLE table_name ADD UNIQUE indexName (column(length))3．创建全文索引
ALTER TABLE index3 add fulltext index index3_name(name);4．创建单列索引
ALTER TABLE index3 add index index3_name(name(10));5．创建多列索引 6．创建空间索引
注意：MySQL5.5及以后版本，默认采用了innodb引擎，如果没有特殊修改过，默认采用的索引类型底层都是基于B+树（B+ Tree）。
如何查看某张表是否存在索引和索引情况
show index from 表名称;
-- 也可以通过查看创建的语句来查看索引情况
show create table 表名称删除索引
drop index 索引名称 on table;索引命中分析
explain 命令
EXPLAIN分析结果的含义:
table：这是表的名字。
type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
possible_keys：可能可以利用的索引的名字
Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计。
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响
MySQL索引的优化
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。
何时使用聚集索引或非聚集索引
1) B-Tree可被用于sql中对列做比较的表达式，如=, >, >=, <, <=及between操作
2) 若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引。比如，SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'或SELECT * FROMtbl_name WHERE key_col LIKE 'Pat%_ck%'可以利用索引，而SELECT * FROM tbl_name WHEREkey_col LIKE '%Patrick%'（以通配符开头）和SELECT * FROM tbl_name WHERE key_col LIKEother_col（like条件不是常量串）无法利用索引。对于形如LIKE '%string%'的sql语句，若通配符后面的string长度大于3，则MySQL会利用TurboBoyer-Moore algorithm算法进行查找.
3) 若已对名为col_name的列建了索引，则形如"col_name is null"的SQL会用到索引。
4) 对于联合索引，sql条件中的最左前缀匹配字段会用到索引。
5) 若sql语句中的where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围
MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引（版本不同，可能会有变化）。
课堂练习
1、创建测试表
create table test1(
id int,num int,pass varchar(50)
);
create table test2(
id int,num int,pass varchar(50),
index idIdx (id)
);
create table test3(
id int,num int,pass varchar(50)
); 2、向表test1里插入1000000条数据
for ((i=1;i<=1000000;i++));do `mysql -p123456 -uroot -e "insert into it.test1
values($i,floor($i+rand()*$i),md5($i));"`; done > /tmp/mysql.txt 2>&1# 注意：测试时可以插入300000条记录
mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.12 sec) 3、在有索引和没有索引的情况下执行查询 1）没有创建索引时查询
mysql> reset query cache;
mysql> explain select num,pass from test3 where id>=5000 and id<5050; 2）创建索引后再次查询
mysql> reset query cache;
mysql> explain select num,pass from test2 where id>=5000 and id<5050;4、在有索引和没有索引的情况下新增数据 1）没有创建索引时插入数据
mysql> insert into test3 select * from test1;
Query OK, 300000 rows affected (1.00 sec)
Records: 300000 Duplicates: 0 Warnings: 0 2）创建索引后再次插入数据
mysql> insert into test2 select * from test1;
Query OK, 300000 rows affected (1.17 sec)
Records: 300000 Duplicates: 0 Warnings: 0SQL编程
SQL语法，结构化查询语言，主要用在关系型数据库操作上，但是要注意，SQL语句，是一种编程语言。
-
是否存在变量
-
存在程序控制流程（三大流程）
SQL编程，也是做数据库相关的编程，如定义数据库函数、数据库存储过程、触发器、游标。
触发器（trigger）
触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。
触发器的定义语法
create trigger 触发器名称 before|after insert|delete|update
ON 表名 FOR EACH ROW
BEGIN
-- 触发器对应的代码
-- 可能有多行代码;
-- 每行代码需要使用分号;
END课堂案例
create table student(
id int unsigned auto_increment primary key not null,
name varchar(50)
);
insert into student(name) values('jack');
create table student_total(total int);
insert into student_total values(1);
delimiter $$
create trigger student_insert_trigger after insert
on student for each row
BEGIN
update student_total set total=total+1;
# 其他SQL
END
$$
扫一扫
2056
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
