MySQL（二）
- 存储引擎
- 事务
- 索引
- 备份与还原
- 视图
存储引擎
存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）；实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。
-
创建存储引擎
CREATE TABLE `t_student` ( `student_id` int NOT NULL, `student_name` varchar(20) DEFAULT NULL, `sex` char(2) DEFAULT NULL, `birthday` date DEFAULT NULL, `email` varchar(30) DEFAULT NULL, `classes_id` int DEFAULT NULL, PRIMARY KEY (`student_id`), KEY `fk_classes_id` (`classes_id`), CONSTRAINT `fk_classes_id` FOREIGN KEY (`classes_id`) REFERENCES `t_classes` (`classes_id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk -- ENGINE=InnoDB就是指定存储引擎 CHARSET是字符集mysql默认的存储引擎是InnoDB。CHARSET默认的字符集是utf8
-
MySQL有哪些常用的存储引擎
CMD下使用show engines\G可以查看MySQL支持的存储引擎
-
MyISAM存储引擎 它管理的表具有以下特征： 使用三个文件表示每个表：
- 格式文件 — 存储表结构的定义（mytable.frm）
- 数据文件 — 存储表行的内容（mytable.MYD）
- 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。
- 可被转换为压缩、只读表来节省空间
提示一下：对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。
MyISAM存储引擎特点：可被转换为压缩、只读表来节省空间这是这种存储引擎的优势！！！！
MyISAM不支持事务机制，安全性低。
-
InnoDB存储引擎
- 这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
- InnoDB支持事务，支持数据库崩溃后自动恢复机制。
- InnoDB存储引擎最主要的特点是：非常安全。
- 它管理的表具有下列主要特征：
- 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
- InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名，表空间存储数据+索引。）
- 提供一组用来记录事务性活动的日志文件
- 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
- 提供全 ACID 兼容
- 在 MySQL 服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
- InnoDB最大的特点就是支持事务：以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间。
-
MEMORY存储引擎
- 使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，
- 这两个特点使得 MEMORY 存储引擎非常快。
- MEMORY 存储引擎管理的表具有下列特征：
- 在数据库目录内，每个表均以.frm 格式的文件表示。
- 表数据及索引被存储在内存中。（目的就是快，查询快！）
- 表级锁机制。
- 不能包含 TEXT 或 BLOB 字段。
- MEMORY 存储引擎以前被称为HEAP 引擎。
- MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
- MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。
-
MyISAM和InnoDB的区别
在MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，可谓是风光一时。
虽然，MyISAM 的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。
5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），MySQL 5.5 版本后默认的存储引擎为 InnoDB。
下面是两者的简单对比：
-
是否支持行级锁
MyISAM只有表级锁(table-level locking)，而InnoDB支持行级锁(eow-level locking)和表级锁，默认为行级锁。
也就是说，MyISAM一锁就锁住了整张表，这在并发写的时候会很蠢！！这也就是为什么InnoDB在并发写的时候，性能更加厉害了。
-
是否支持事务
MyISAM不提供事务支持
InnoDB提供事务支持，具有提交(commit)和回滚(rollback)事务的能力
-
是否支持外键
MyISAM不支持，InnoDB支持
一般我们也是不建议在数据库层面使用外键的，应用层面可以解决。不过，这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定
-
是否支持数据库异常崩溃后的安全恢复
MyISAM不支持，InnoDB支持。
使用InnoDB的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于
redo log。- MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
- MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是可重复读
REPEATABLE-READ）。 - 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。
-
是否支持 MVCC
MyISAM 不支持，而 InnoDB 支持。
讲真，这个对比有点废话，毕竟 MyISAM 连行级锁都不支持。
MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提供性能。
-
-
-
锁机制与InnoDB锁算法
MyISAM和InnoDB存储引擎使用的锁：
- MyISAM采用表级锁(table-level locking)
- InnoDB支持行级锁(row-level locking)
表级锁和行级锁对比：
-
表级锁：MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和InnoDB引擎都支持表级锁。
-
行级锁：MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
锁粒度：所谓加锁粒度就是你要锁住的范围是多大。
比如你在家上卫生间，你只要锁住卫生间就可以了吧，不需要将整个家都锁起来不让家人进门吧，卫生间就是你的加锁粒度。
InnoDB存储引擎的锁的算法有三种：
- Record lock：记录锁，单个行记录上的锁
- Gap lock：间隙锁，锁定一个范围，不包括记录本身
- Next-key lock：record+gap临键锁，锁定一个范围，包含记录本身
事务
-
什么是事务？
事务其实就是一个完整的业务逻辑。
一个完整的业务逻辑：假设A向B转账1000；将A账户的钱减去1000；将B账户的钱增加1000。这就是一个完整的业务逻辑
以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的
在本质上，一个事务其实就是多条DML语句同时成功或同时失败。
只有DML语句才会有事务，其他语句和事务无关。因为 只有insert、update、delete三个语句是数据库表中数据进行增、删、改的。只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑安全问题。
-
事务是怎么做到多条DML语句同时成功和同时失败的
InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。
演示事务的回滚操作
mysql> use bjpowernode Database changed mysql> select * from dept_bak; Empty set (0.00 sec) -- 开启事务 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept_bak(deptno,dname,loc) values(10,'ming','guangzhou'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak(deptno,dname,loc) values(10,'ming','guangzhou'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak(deptno,dname,loc) values(10,'ming','guangzhou'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak(deptno,dname,loc) values(10,'ming','guangzhou'); Query OK, 1 row affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+-----------+ | DEPTNO | DNAME | LOC | +--------+-------+-----------+ | 10 | ming | guangzhou | | 10 | ming | guangzhou | | 10 | ming | guangzhou | | 10 | ming | guangzhou | +--------+-------+-----------+ 4 rows in set (0.00 sec) -- 回滚操作 mysql> rollback; Query OK, 0 rows affected (0.30 sec) mysql> select * from dept_bak; Empty set (0.00 sec)演示事务的提交操作
mysql> select * from dept_bak; Empty set (0.00 sec) -- 开启事务 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept_bak(deptno,dname,loc) values(10,'ming','guangzhou'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak(deptno,dname,loc) values(10,'ming','guangzhou'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak(deptno,dname,loc) values(10,'ming','guangzhou'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak(deptno,dname,loc) values(10,'ming','guangzhou'); Query OK, 1 row affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+-----------+ | DEPTNO | DNAME | LOC | +--------+-------+-----------+ | 10 | ming | guangzhou | | 10 | ming | guangzhou | | 10 | ming | guangzhou | | 10 | ming | guangzhou | +--------+-------+-----------+ 4 rows in set (0.00 sec) -- 提交事务 mysql> commit; Query OK, 0 rows affected (0.07 sec) mysql> select * from dept_bak; +--------+-------+-----------+ | DEPTNO | DNAME | LOC | +--------+-------+-----------+ | 10 | ming | guangzhou | | 10 | ming | guangzhou | | 10 | ming | guangzhou | | 10 | ming | guangzhou | +--------+-------+-----------+ 4 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+-----------+ | DEPTNO | DNAME | LOC | +--------+-------+-----------+ | 10 | ming | guangzhou | | 10 | ming | guangzhou | | 10 | ming | guangzhou | | 10 | ming | guangzhou | +--------+-------+-----------+ 4 rows in set (0.00 sec)由上面代码测试，我们可以知道
- commit：提交事务
- rollback：回滚事务（回滚永远都是只能回滚带上一次的提交点！）
在mysql中，默认情况下是支持自动提交事物的（自动提交！），即没执行一条DML语句，则提交一次。
这种自动提交实际上是不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条
使用
start transaction可以把mysql的自动提交机制关闭掉 -
事务的三个特性
- 原子性：说明事务是最小的工作单元。不可再分。
- 一致性：所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
- 隔离性：A事务和B事务之间具有一定的隔离。例如教室A和教室B之间有一道墙，这道墙就是隔离性。
- 持久性：事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！
-
事物的隔离级别
- 读未提交：read uncommitted（最低的隔离级别）
- 事务A可以读取到事务B未提交的数据。
- 这种隔离级别存在的问题就是：脏读现象！(Dirty Read)；我们称读到了脏数据
- 这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是读已提交起步
- 读已提交：read committed
- 事务A只能读取到事务B提交之后的数据。
- 这种隔离级别解决了脏读的现象。
- 这种隔离级别存在不可重复读取数据的问题。
- 这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
- oracle数据库默认的隔离级别是：read committed
- 可重复读：repeatable read
- 事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。
- 解决了不可重复读取数据。
- 可重复读会出现幻读。每一次读取到的数据都是幻象。不够真实
- 序列化/串行化：serializable（最高的隔离级别）
- 这是最高隔离级别，效率最低。解决了所有的问题。
- 这种隔离级别表示事务排队，不能并发！与synchronized相似，线程同步（事务同步）
- 每一次读取到的数据都是最真实的，并且效率是最低的。
InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。
——摘自《MySQL 技术内幕：InnoDB 存储引擎(第 2 版)》7.7 章
设置隔离级别：
set global transaction isolation level read uncommitted;查看隔离级别：
SELECT @@tx_isolation - 读未提交：read uncommitted（最低的隔离级别）
索引
-
什么是索引
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，也可以多个字段联合起来添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
对于一本字典来说，查找某个汉字有两种方式： 第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。效率比较低。 第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过索引检索，效率较高。
-
索引类型
-
主键索引(Primary Key)
数据表的主键列使用的就是主键索引。
一张数据表只能有一个主键，并且主键不能为null，不能重复。
在Mysql的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。
-
二级索引（辅助索引）
二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位到主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。
- 唯一索引(Unique K)：唯一索引也是一种约束。**唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。**建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率、
- 普通索引(Index)：普通索引的唯一作用就是快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
- **前缀索引(Prefix)：**前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建议的数据更小，因为只取几个字符。
- 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
二级索引：
-
-
索引的优缺点
优点：
- 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点：
- 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
- 索引需要使用物理文件存储，也会耗费一定空间。
但是，使用索引一定能提高查询性能吗?
大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。
-
MySQL的查询方式
- 第一种方式：全表扫描（如果字段没有添加索引就是用全表扫描，效率低）
- 第二种方式：根据索引检索。
在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。
遵循左小又大原则存放。采用中序遍历方式遍历取数据。
-
索引的实现原理
假设现在有一张表t_user
id(PK) name 每一行记录在硬盘上都有物理存储编号 ------------------------------------------------------------------------ 100 zhangsan 0x1111 120 lisi 0x2222 99 wangwu 0x8888 88 zhaoliu 0x9999 101 jack 0x6666 55 lucy 0x5555 130 tom 0x7777提醒：
- 在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
- 在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
- 在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）
索引的简单实现原理：存放遵循左小右大原则
注意：上图只是索引的实现原理的大概模型，事实上索引的实现原理远比上图复杂的多！
-
索引的创建和删除
创建索引：
-- 给emp表的ename字段添加索引，起名：emp_ename_index create index emp_name_index on emp(ename);删除索引：
-- 将emp表上的emp_ename_index索引对象删除。 drop index emp_name_index on emp;mysql中查看一个SQL语句是否使用了索引检索
explain select * from emp where ename = 'KING'; -
需要添加索引的条件。
在mysql当中，主键上，以及unique字段上都会自动添加索引的
- 条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
- 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
- 条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）
建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。 建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。
-
索引失效
-
select * from emp where ename like '%T';ename上即使添加了索引，也不会走索引。原因是因为模糊匹配当中以“%”开头了！尽量避免模糊查询的时候以“%”开始。这是一种优化的手段/策略。explain select * from emp where ename like '%T';
-
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
explain select * from emp where ename = 'KING' or job = 'MANAGER'; -
使用复合索引的时候，没有使用左侧的列查找，索引失效
两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。
create index emp_job_sal_index on emp(job,sal); explain select * from emp where sal = 800; -
在where当中索引列参加了运算，索引失效。
create index emp_sal_index on emp(sal); explain select * from emp where sal+1 = 800; -
在where当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';
-
备份与还原
/* 备份与还原 */ ------------------
备份，将数据的结构与表内数据保存起来。
利用 mysqldump 指令完成。
-- 导出
mysqldump [options] db_name [tables]
mysqldump [options] ---database DB1 [DB2 DB3...]
mysqldump [options] --all--database
1. 导出一张表
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库
mysqldump -u用户名 -p密码 --lock-all-tables --database 库名 > 文件名(D:/a.sql)
可以-w携带WHERE条件
-- 导入
1. 在登录mysql的情况下：
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
视图
-
什么是视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表并且在引用视图时动态生成。
视图具有表结构文件，但不存在数据文件。 对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。