第五章 MySQL备份恢复
5.1 MySQL日志管理
在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，在这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了。
数据库中数据丢失或被破坏可能原因：
-
误删除数据库
-
数据库工作时，意外断电或程序意外终止
-
由于病毒造成的数据库损坏或丢失
-
文件系统损坏后，系统进行自检操作
-
升级数据库时，命令语句不严格
-
设备故障等等
-
自然灾害
-
盗窃
5.1.1 MySQL日志类型
MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：
| 日志类型 | 记入文件中的信息类型 |
|---|---|
| 错误日志 | 记录启动、运行或停止时出现的问题 |
| 查询日志 | 记录建立的客户端连接和执行的语句 |
| 慢日志 | 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询 |
| 二进制日志 | 记录所有更改数据的语句。主要用于复制和即时点恢复 |
| 事务日志 | 记录InnoDB等支持事务的存储引擎执行事务时产生的日志 |
默认情况下，所有日志创建于mysqld数据目录中。通过刷新日志，你可以强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，出现日志刷新。如果你正使用MySQL复制功能，在复制服务器将维护更多日志文件，被称为接替日志。
在mysql中，执行SQL语句：
FLUSH LOGS
在shell中，通过mysqladmin命令执行日志刷新：
mysqladmin flush-logs mysqladmin refresh
5.1.2 错误日志
错误日志主要记录如下几种日志：
-
服务器启动和关闭过程中的信息
-
服务器运行过程中的错误信息
-
事件调度器运行一个时间时产生的信息
-
在从服务器上启动从服务器进程时产生的信息
错误日志定义：
可以用--log-error[=file_name]选项或者配置文件指定log-error来指定mysqld保存错误日志文件的位置。如果没有给定file_name的值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。如果你执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件，但是从mysql5.5.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，mysql启动或者执行flush logs时会创建新的日志文件。
查看当前错误日志配置:
mysql> SHOW GLOBAL VARIABLES LIKE '%log_error%'; 是否记录警告日志： mysql> SHOW GLOBAL VARIABLES LIKE '%log_warnings%';
5.1.3 通用查询日志
mysql服务器默认情况下并没有开启通用查询日志。如果需要通用查询日志，可通过修改配置文件来开启：
启动开关：general_log={ON|OFF}
日志文件变量：general_log_file[=/PATH/TO/file]
全局日志开关：log={ON|OFF} 该开关打开后，所有日志都会被启用
记录类型：log_output={TABLE|FILE|NONE}：FILE表示将日志存入文件，默认值是FILE；TABLE表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。mysql数据库支持同时两种日志存储方式，配置的时候以逗号隔开。
因此，要启用通用查询日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果没有指定，默认名是host_name.log。
看看上述几个值的默认配置：
mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%'; +------------------+--------------------------------+ | Variable_name | Value | +------------------+--------------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/mysql8-0-30.log | +------------------+--------------------------------+ mysql> SHOW GLOBAL VARIABLES LIKE '%log_output%';
5.1.4 慢查询日志
MySQL如果启用了 slow_query_log=ON 选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）。日志记录文件为slow_query_log_file[=file_name]，如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
默认与慢查询相关变量：
# 默认没有启用慢查询，为了服务器调优，建议开启 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';
5.1.5 二进制日志
开启日志
二进制日志启动开关：log-bin [= file_name]。
注意：5.6以下版本默认file_name为$datadir/mysqld-binlog。查看二进制日志的工具为：mysqlbinlog。
二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。
语句以“事件”的形式保存，它描述数据更改。二进制日志还包含关于每个更新数据库的语句的执行时间信息。 它不包含不会修改任何数据的语句，例如select，show等。
二进制日志的主要目的是在数据库存在故障时，恢复时能够最大可能地更新数据库（即时点恢复），因为二进制日志包含备份后进行的所有更新。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。
二进制日志是记录执行的语句还是执行后的结果数据呢？分为三种情况：
-
假如一个表有10万行数据，而现在要执行一个如下语句将amount字段的值全部在原来的基础上增加1000：
UPDATE sales.january SET amount=amount+1000 ;
此时如果要记录执行后的结果数据的话，日志会非常大。因此在这种情况下应记录执行语句。这种方式就是基于语句的二进制日志。
-
如果向某个字段插入的是当前的时间呢？如下：
INSERT INTO tb SET Birthdate=CURRENT_TIME();
此时就不能记录语句了，因为不同时间执行的结果是不一样的。这时应该记录这一行的值，这种就是基于行（row）的二进制日志。
-
在有些情况，可能会结合两种方式来记录，这种叫做混合方式的二进制日志。
二进制日志的管理
日志滚动。在my.cnf中设定max_binlog_size = 200M，表示限制二进制日志最大尺寸为200M，超过200M后进行滚动。MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。每次重启MySQL服务，日志都会自动滚动一次。
另外如果需要手动滚动，则使用命令 mysql> FLUSH LOGS ;
日志查看
mysql8.0 [(none)]>show variables like '%log_bin%'; 查看有哪些二进制日志文件：mysql> SHOW BINARY LOGS; 查看当前正在使用的是哪一个二进制日志文件：mysql> SHOW MASTER STATUS; 查看二进制日志内容：mysql> SHOW BINLOG EVENTS IN 'mysqld-binlog.000002'; ##该语句还可以加上Position（位置），指定显示从哪个Position（位置）开始： mysql> SHOW BINLOG EVENTS IN 'binlog.000002' FROM 203; #使用命令mysqlbinlog查看二进制日志内容：mysqlbinlog [options] log-files
二进制日志还原数据
使用mysqlbinlog读取需要的日志内容，使用标准输入重定向到一个sql文件，然后在mysql服务器上导入即可，如下：
mysqlbinlog mysqld-binlog.000002 >/root/temp_date.sql
如果报编码错误：mysqlbinlog: [ERROR] unknown variable 'default-character-set
原因：mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8mb4这个指令。
有两种方式解决：
-
添加 --no-defaults 参数
mysqlbinlog --no-defaults binlog.000069 >c:/a.sql # 注意需要指定binlog的位置，如果是当前路径，则可以直接使用名称即可。
-
修改配置文件
my.cnfdefault-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是需要重启MySQL服务。
删除二进制日志文件：
二进制日志文件不能直接删除的，如果使用rm等命令直接删除日志文件，可能导致数据库的崩溃。必须使用命令 PURGE 删除日志，语法如下：
PURGE { BINARY | MASTER } LOGS { TO 'log_name' |BEFORE datetime_expr }
注意：如果数据库使用的编码是utf8mb4编码，mysqlbinlog命令可能不能解析这种编码，提供两种方案：
-
在MySQL的配置/etc/my.cnf中将default-character-set=utf8mb4 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
-
用mysqlbinlog --no-defaults mysql-bin.000004 命令打开。
5.2 MySQL备份
5.2.1 备份类型
根据服务器状态，可以分为热备份、温备份、冷备份
-
热备份：读、写不受影响；
-
温备份：仅可以执行读操作；
-
冷备份：离线备份；读、写操作均中止；
从对象来分，可以分为物理备份与逻辑备份
-
物理备份：复制数据文件；
-
逻辑备份：将数据导出至文本文件中；
从数据收集来分，可以完全备份、增量备份、差异备份
-
完全备份：备份全部数据；
-
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
-
差异备份：仅备份上次完全备份以来变化的数据；
逻辑备份优缺点
逻辑备份的优点：
在备份速度上两种备份要取决于不同的存储引擎
物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表
逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理
逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高
逻辑备份也对保持数据的安全性有保证
逻辑备份的缺点：
逻辑备份要对RDBMS产生额外的压力，而裸备份无压力
逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩
逻辑备份可能会丢失浮点数的精度信息
5.2.2 备份内容
数据文件 日志文件（比如事务日志，二进制日志） 存储过程，存储函数，触发器 配置文件（十分重要，各个配置文件都要备份） 用于实现数据库备份的脚本，数据库自身清理的Crontab等……
5.2.3 备份工具
MySQL自带的备份工具
mysqldump，是mysql数据库管理系统，自带的逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢。但是在实现还原的时候，具有很大的操作余地。具有很好的弹性。
mysqlhotcopy 物理备份工具，但只支持MyISAM引擎，基本上属于冷备的范畴，物理备份，速度比较快。mysql5.7已经没有这个命令了，多用于mysql5.5之前。mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份），只能用于MyISAM引擎的数据库。本质是使用锁表语句，然后cp或scp。
文件系统备份工具
cp命令， 冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令，对其进行备份的，备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。
lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用，所以备份速度比较快，恢复速度比较快，没有什么弹性空间，而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。
其它工具
ibbackup 商业工具 MyISAM是温备份，InnoDB是热备份 ，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元。
xtrabackup 开源工具 MyISAM是温备份，InnoDB是热备份 ，是ibbackup商业工具的替代工具。
mysqlbackup ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称：mysqlbackup。
MySQL企业备份工具执行在线“热备“，无阻塞的MySQL数据库备份。全备份可以在所有InnoDB数据库上执行，而无需中断MySQL查询或更新。此外，支持增量备份，只备份自上次备份后有变化的数据。另外部分备 份，支持特定的表或表空间按需要进行备份。
第六章 MySQL索引视图
6.1 视图
6.1.1 什么是视图
视图通过以定制的方式显示来自一个或多个表的数据
视图是一种数据库对象，用户可以像查询普通表一样查询视图
视图内其实没有存储任何数据，它只是对表的一个查询
视图的定义保存在数据字典内，创建视图所基于对表称为“基表”
6.1.2 为什么需要视图
例如经常要对emp和dept表进行连接查询，每次都要做表的连接，写同样的一串语句，同时由于工资列队数据比较敏感，对外要求不可见。对这样的问题就可以通过视图来解决。
6.1.3 视图的作用和优点
作用：
-
控制安全
-
保存查询数据
优点：
-
提供了灵活一致级别安全性。
-
隐藏了数据的复杂性
-
简化了用户的SQL指令
-
通过重命名列，从另一个角度提供数据
6.1.4 创建视图
CREATE [OR REPLACE] VIEW 视图名 [(alias[, alias]...)]--为视图字段指定别名 AS subquery [WITH READ ONLY]; 创建视图, EMP_V_10, 包括10号部门的所有雇员信息. CREATE VIEW emp_v_10 AS SELECT employee_id, name, salary FROM employees WHERE deptt_id = 10;
6.1.5 视图使用规则
-
视图必须有唯一命名
-
在mysql中视图的数量没有限制
-
创建视图必须从管理员那里获得必要的权限
-
视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
-
在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的 ORDER BY。
-
视图不能索引，也不能关联触发器或默认值
-
视图可以和表同时使用
6.1.6 修改视图
使用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;
6.1.7 删除视图
删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义. DROP VIEW view_name;
案例演示：
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 \G
4、 更新视图。向视图中插入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;
6.2 索引
索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。（注意：一般数据库默认都会为主键生成索引）。
6.2.1 什么是索引
模式(schema)中的一个数据库对象 在数据库中用来加速对表的查询 通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O 与表独立存放，但不能独立存在，必须属于某个表 由数据库自动维护，表被删除时，该表上的索引自动被删除。 索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。
索引的原理
就是把无序的数据变成有序的查询
-
把创建的索引的列的内容进行排序
-
对排序结果生成倒排表
-
在倒排表内容上拼上数据地址链
-
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
6.2.2 索引优缺点
索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的 联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空 间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。
6.2.3 索引分类
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。
6.2.4 索引的设计原则
为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。本小 节将向读者介绍一些索引的设计原则。
-
选择惟一性索引
-
为经常需要排序、分组和联合操作的字段建立索引
-
为常作为查询条件的字段建立索引
-
限制索引的数目
-
尽量使用数据量少的索引
-
尽量使用前缀来索引
-
删除不再使用或者很少使用的索引
6.2.5 创建索引
创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有三种方式，这三种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。
创建表是创建索引
创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下： CREATE TABLE 表名 ( 属性名 数据类型 [完整性约束条件], 属性名 数据类型 [完整性约束条件], … 属性名 数据类型 [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [别名](属性名1 [(长度)] [ASC | DESC]) ); 1 普通索引 # 直接创建索引 CREATE INDEX index_name ON table(column(length)) # 创建表的时候同时创建索引 Create table index1( Id int, Name varchar(20), Sex boolean, index(id), ); # 修改表结构的方式添加索引 ALTER TABLE table_name ADD INDEX index_name ON (column(length)) # 查询索引 Show create table index1 \G # 查询某张表中索引情况 show index from table_name; # 使用计划查询SQL使用索引情况 Explain select * from index1 where id=1 \G # 删除索引 DROP INDEX index_name ON table # 2 创建唯一性索引 ，当然也有多种创建方式 Create table index2( Id int unique, Name varchar(20), Unique index index2_id(id asc) ); # 3 创建全文索引（FULLTEXT） # MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表； # 他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建， # 或是随后使用ALTER TABLE 或CREATE INDEX被添加。 # 对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中， # 然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。 # 不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。 只能创建在char,varchar或text类型的字段上。 create table index3( Id int, Info varchar(20), Fulltext index index3_info(info) ); explain select * from table where id=1; 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：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响 # 4 创建单列索引 Create table index4( Id int, Subject varchar(30), Index index4_st(subject(10)) ); # 5 创建多列索引 使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。 如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。 也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时， MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。 Create table index5( Id int, Name varchar(20), Sex char(4), Index index5_ns(name,sex) ); # 6 创建空间索引 Create table index6( Id int, Space geometry not null, Spatial index index6_sp(space) )engine=myisam; 建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包括 geometry,point,linestring和polygon类型等。平时很少用到。
create 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
用ALTER TABLE语句来创建索引，也是存在表的情况下。
在已经存在的表上，可以通过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．创建空间索引
6.2.6 删除索引
删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。 对于这样的索引，应该将其删除。本节将详细讲解删除索引的方法。 对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下： DROP INDEX 索引名 ON 表名 ;
索引示例：
1、 在数据库job下创建workInfo表。创建表的同时在id字段上创建名为index_id的唯一性索引，而且以
降序的格式排列。workInfo表内容如下所示
字段描述 数据类型 主键 外键 非空 唯一 自增
id 编号 INT(10) 是 否 是 是 是
name 职位名称 VARCHAR(20) 否 否 是 否 否
type 职位类别 VARCHAR(10) 否 否 否 否 否
address 工作地址 VARCHAR(50) 否 否 否 否 否
wage 工资 INT 否 否 否 否 否
contents 工作内容 TINYTEXT 否 否 否 否 否
extra 附加信息 TEXT 否 否 否 否 否
CREATE TABLE workInfo(
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
type VARCHAR(10),
address VARCHAR(50),
tel VARCHAR(20),
wage INT,
content TINYTEXT,
extra TEXT,
UNIQUE INDEX index_id(id DESC)
);
2 、使用create index语句为name字段创建长度为10的索引index_name
CREATE INDEX index_name ON workInfo(name(10));
3 、使用alter table语句在type和address上创建名为index_t的索引
ALTER TABLE workInfo ADD INDEX index_t(type,address);
4 、将workInfo表的存储引擎更改为MyISAM类型
ALTER TABLE workInfo ENGINE=MyISAM;
5 、使用alter table语句在extra字段上创建名为index_ext的全文索引
ALTER TABLE workInfo ADD FULLTEXT INDEX index_ext(extra);
6 、删除workInfo表的唯一性索引index_id
DROP INDEX index_id ON workInfo;
实例测试MySQL使用索引带来的效率提升：
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: 0
6.2.7 MySQL使用索引的场景
1) 快速查找符合where条件的记录 2) 快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那 个索引，以便尽快淘汰不符合条件的记录。 3) 如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作 为索引来加速查找。 例如，若为某表创建了3个字段(c1, c2, c3)构成的联合索引，则(c1), (c1, c2), (c1, c2, c3)均 会作为索引，(c2, c3)就不会被作为索引，而(c1, c3)其实只利用到c1索引。 4) 多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）。 5)若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引 6)对建立了索引的字段做sort或group操作时，MySQL会使用索引
MySQL索引的优化
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。
何时使用聚集索引或非聚集索引
6.2.8 SQL如何使用索引
1) B-Tree可被用于sql中对列做比较的表达式，如=, >, >=, <, <=及between操作 2) 若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引。 比如，SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'或SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'可以利用索引，而SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'（以通配符开头）和SELECT * FROM tbl_name WHERE key_col LIKE other_col（like条件不是常量串）无法利用索引。 对于形如LIKE '%string%'的sql语句，若通配符后面的string长度大于3，则MySQL会利用Turbo Boyer-Moore algorithm算法进行查找. 3) 若已对名为col_name的列建了索引，则形如"col_name is null"的SQL会用到索引。 4) 对于联合索引，sql条件中的最左前缀匹配字段会用到索引。 5) 若sql语句中的where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围 MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引（版本不同，可能会有变化）。
6.2.9 聚簇索引和非聚簇索引
非聚簇索引
索引节点的叶子页面就好比一片叶子。叶子头便是索引键值。
先创建一张表：
CREATE TABLE `user` ( `id` INT NOT NULL , `name` VARCHAR NOT NULL , `class` VARCHAR NOT NULL);
对于MYISAM引擎，如果创建 id 和 name 为索引。对于下面查询：
select * from user where id = 1
会利用索引，先在索引树中快速检索到 id，但是要想取到id对应行数据，必须找到改行数据在硬盘中的存储位置，因此MYISAM引擎的索引，叶子页面上不仅存储了主键id 还存储着 数据存储的地址信息。如图：
像这样的索引就称为非聚簇索引。
非聚簇索引的二级索引与主键索引类似。假设我们对name添加索引，那么name的索引树叶子将是如下结构：
聚簇索引
对于非聚簇索引来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据（回行）消耗时间。为了优化这部分回行取数据时间，InnoDB 引擎采用了聚簇索引。
聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面不再存该行对应的地址，而是直接存储数据：
这样便避免了回行操作所带来的时间消耗。 使得 InnoDB 在某些查询上比 MyISAM 还要快！
关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快。但事实并不一定如此。多数情况下，MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等，使得 InnoDB的叶子页面实际上还包含有事务id（换句话说就是版本号） 以及回滚指针。
在二级索引方面， InnoDB 与 MyISAM 有很大区别。
InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB 会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。
假设对 InnoDB 引擎上表name字段加索引，那么name索引叶子页面则只会存储主键id：
检索时，先通过name索引树找到主索引id，再通过id在主索引树的聚簇索引叶子页面取出数据。
参考资料：简单理解InnoDB聚簇索引与MyISAM非聚簇索引_若尘拂风的博客-CSDN博客_myisam的非聚簇索引
第七章 MySQL事务
7.1 什么是事务
Transaction，一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)。一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。
数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成
以银行转账为例：账户转账是一个完整的业务，最小的单元，不可再分——也就是说银行账户转账是一个事务：
update t_act set balance=balance - 400 where actno=1; update t_act set balance=balance + 400 where actno=2;
以上两台DML语句必须同时成功或者同时失败。最小单元不可再分，当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下；这个记录是在内存中完成的；当第二条DML语句执行成功后，和底层数据库文件中的数据完成同步。若第二条DML语句执行失败，则清空所有的历史操作记录，要完成以上的功能必须借助事务。
事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；
要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。
7.2 事务的特性
事务是由一组SQL语句 组成的逻辑处理单元，它的ACID特性如下：
原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
7.3 MySQL使用事务
MySQL开启事务、回滚事务、提交事务命令。
begin 说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。 commit：提交事务 完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。 rollback ：回滚事务 将内存中，已执行过的操作，回滚回去
自动提交策略：
MySQL默认已经开启自动提交，我们可以通过对应的设置来开启或者关闭自动提交。
db01 [(none)]>select @@autocommit; db01 [(none)]>set autocommit=0; db01 [(none)]>set global autocommit=0; 注： 自动提交是否打开，一般在有事务需求的MySQL中，将其关闭 不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能 (1) set autocommit=0; set global autocommit=0; (2) vim /etc/my.cnf autocommit=0
隐式提交语句：
用于隐式提交的 SQL 语句： begin a b begin SET AUTOCOMMIT = 1 导致提交的非事务语句： DDL语句： （ALTER、CREATE 和 DROP） DCL语句： （GRANT、REVOKE 和 SET PASSWORD） 锁定语句：（LOCK TABLES 和 UNLOCK TABLES） 导致隐式提交的语句示例： TRUNCATE TABLE LOAD DATA INFILE SELECT FOR UPDATE
开始事务流程：
1、检查autocommit是否为关闭状态 select @@autocommit; 或者： show variables like 'autocommit'; 2、开启事务,并结束事务 begin delete from student where name='alexsb'; update student set name='alexsb' where name='alex'; rollback; begin delete from student where name='alexsb'; update student set name='alexsb' where name='alex'; commit;
7.4 InnoDB 事务的ACID如何保证
7.4.1 基本概念
redo log ---> 重做日志 ib_logfile0~1 50M , 轮询使用 redo log buffer ---> redo内存区域 ibd ----> 存储 数据行和索引 buffer pool --->缓冲区池,数据和索引的缓冲 LSN : 日志序列号 磁盘数据页,redo文件,buffer pool,redo buffer MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动 WAL : write ahead log 日志优先写的方式实现持久化 脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页. CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作 TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务
7.4.2 redo log
Redo是什么？
redo,顾名思义“重做日志”，是事务日志的一种。
作用是什么？
在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用
redo日志位置
redo的日志文件：iblogfile0 iblogfile1
redo buffer
redo的buffer:数据页的变化信息+数据页当时的LSN号 LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog
redo的刷新策略
commit; 刷新当前事务的redo buffer到磁盘 还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
MySQL CSR——前滚
MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致 情况一: 我们做了一个事务,begin;update;commit. 1. 在begin ,会立即分配一个TXID=tx_01. 2. update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中 3. DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102 4. LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer 5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记) 6. 假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失 7. MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是 LSN=101,dp_01,TXID=tx_01,redolog中LSN=102 MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动 以上的工作过程,我们把它称之为基于REDO的"前滚操作"
7.4.3 undo 回滚日志
undo是什么？
undo,顾名思义“回滚日志”
作用是什么？
在事务ACID过程中，实现的是“A” 原子性的作用 另外CI也依赖于Undo 在rolback时,将数据恢复到修改之前的状态 在CSR实现的是,将redo当中记录的未提交的时候进行回滚. undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备
对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含 3 个隐藏列
db_row_id：隐藏的行 ID。在没有自定义主键也没有 Unique 键的情况下，会使用该隐藏列作为主键。
db_trx_id：操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。
db_roll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。Undo Log 中存储了回滚需要的数据
7.5 事务的隔离级别
多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。
如果不考虑隔离性，可能会引发如下问题：
1、幻想读
2、不可重复读取
3、脏读
7.5.1 脏读
指一个事务读取了另外一个事务未提交的数据。
这是非常危险的，假设Ａ向Ｂ转帐100元，对应sql语句如下所示 1. update account set money=money+100 where name=‘b’; 2. update account set money=money-100 where name=‘a’;
7.5.2 不可重复读
不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。
一种更易理解的说法是：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据并修改数据。那么，在第一个事务的两次读数据之间。由于另一个事务的修改，那么第一个事务两次读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
7.5.3 虚读/幻读
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
如：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读。
幻读和不可重复读的区别：
不可重复读：是同一条SQL查询的内容不同（被修改了）
幻读是：查询的结果条数不同（增加了、或者删除了记录）
幻读的影响
会造成一个事务中先产生的锁，无法锁住后加入的满足条件的行。
产生数据一致性问题，在一个事务中，先对符合条件的目标行做变更，而在事务提交前有新的符合目标条件的行加入。这样通过binlog恢复的数据是会将所有符合条件的目标行都进行变更的。
幻读产生的原因
行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。
如何解决幻读
-
将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。
-
间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。
7.5.4 事务的隔离级别
为了处理这些问题，SQL标准定义了以下几种事务隔离级别
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable read | × | × | √ |
| Serializable | × | x | x |
数据库共定义了四种隔离级别：
-
Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
-
Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）
-
Read committed：可避免脏读情况发生（读已提交）。
-
Read uncommitted：最低级别，以上情况均无法保证。(读未提交)
可以通过命令set transaction命令设置事务隔离级别：
set transaction isolation level 设置事务隔离级别 select @@tx_isolation 查询当前事务隔离级别
第四次作业
学生表：Student (Sno, Sname, Ssex , Sage, Sdept) 学号，姓名，性别，年龄，所在系 Sno为主键 课程表：Course (Cno, Cname,) 课程号，课程名 Cno为主键 学生选课表：SC (Sno, Cno, Score) 学号，课程号，成绩 Sno,Cno为主键 1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。 2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。 3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。 4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
1.建表
2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。
3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。
4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
295
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
