Mysql学习4

  

14 存储引擎

1、存储引擎只有在MySQL当中才有这个概念/术语，其它数据库中没有。
2、在MySQL当中数据库存储表有很多不同的方式，每一种方式都被称为存储擎。有很多存储引擎，就表示有很多不同的存储方式。
3、在MySQL当中表有很多不同的存储方式。选择不同的存储引擎，底层在组织这张表的时候，存储方式就是不同的。oracle中也有这种机制，但是没有这个概念。名字不叫“存储引擎”。

14.1 查看存储引擎

服务器可用的引擎依赖于以下因素：
  • MySQL的版本
  • 服务器在开发时如何被配置
  • 启动选项

查看当前MySQL支持的存储引擎有哪些？

SHOW ENGINES\G

当前MySQL支持9个存储引擎，当前MySQL的版本号是：5.5.36版本不同，支持的存储引擎不同。

Engine	Support	Comment	Transactions	XA	Savepints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL

14.2 指定存储引擎

在建表语句当中，最后的位置可以指定存储引擎以及字符集。

CREATE TABLE `dept` (
	`DEPTNO` int(2) NOT NULL,
	`DNAME` varchar(14) DEFAULT NULL,
	`LOC` varchar(13) DEFAULT NULL,
	 PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create table t_test(id int);
	CREATE TABLE `t_test` (
	`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

•	在创建表时，可使用ENGINE选项为CREATE TABLE语句显式指定存储引擎。
	CREATE TABLE TABLENAME (NO INT) ENGINE = MyISAM;
•	如果在创建表时没有显式指定存储引擎，则该表使用当前默认的存储引擎 
•	默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定。
•	现有表的存储引擎可使用ALTER TABLE语句来改变：ALTER TABLE TABLENAME ENGINE = INNODB;
•	为确定某表所使用的存储引擎，可以使用SHOW CREATE TABLE或SHOW TABLE STATUS语句：

mysql> SHOW CREATE TABLE emp\G
mysql> SHOW TABLE STATUS LIKE 'emp' \G

14.3 常用存储引擎

14.3.1 MyISAM

1、MyISAM存储引擎是MySQL最常用的引擎。
2、使用三个文件表示每个表：
  格式文件 — 存储表结构的定义（mytable.frm）
  数据文件 — 存储表行的内容（mytable.MYD）
  索引文件 — 存储表上索引（mytable.MYI）
3、灵活的AUTO_INCREMENT字段处理
4、可被转换为压缩、只读表来节省空间
5、MyISAM这种存储引擎不支持事务

14.3.2 InnoDB

1、InnoDB存储引擎是MySQL的缺省引擎。
2、它管理的表具有下列主要特征：
 – 每个InnoDB表在数据库目录中以.frm格式文件表示
 – InnoDB表空间tablespace被用于存储表的内容
 – 提供一组用来记录事务性活动的日志文件
 – 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
 – 提供全ACID兼容
 – 在MySQL服务器崩溃后提供自动恢复
 – 多版本（MVCC）和行级锁定
 – 支持外键及引用的完整性，包括级联删除和更新
3、最大的特点：支持事务。但是处理速度不是最快的

14.3.3 MEMORY

1、使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
2、MEMORY存储引擎管理的表具有下列特征：
 – 在数据库目录内，每个表均以.frm格式的文件表示。
 – 表数据及索引被存储在内存中。
 – 表级锁机制。
 – 不能包含TEXT或BLOB字段。
3、MEMORY存储引擎以前被称为HEAP引擎

14.4 选择合适的存储引擎

1、MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。
2、如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
3、可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

15 事务

15.1 事务概述

  事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID
  a) 原子性（Atomicity）
    整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
  b) 一致性（Consistency）
     在事务开始之前与结束之后，数据库都保持一致状态。
  c) 隔离性(Isolation)
    一个事务不会影响其他事务的运行。
  d) 持久性(Durability)
    在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

事务中存在一些概念：
  a) 事务（Transaction）：一批操作（一组DML）
  b) 开启事务（Start Transaction）
  c) 回滚事务（rollback）
  d) 提交事务（commit）
  e) SET AUTOCOMMIT：禁用或启用事务的自动提交模式

当执行DML语句是其实就是开启一个事务
关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚.
事务只对DML有效果。

注意：rollback，或者commit后事务就结束了。

15.2 事务的提交回滚

create table user(
		id int (11) primary key not null auto_increment ,
     	username varchar(30),
		password varchar(30)
		
)ENGINE=InnoDB DEFAULT CHARSET=utf8

START TRANSACTION;
insert into user (username,password) values ('zhangsan','123');
update user set username = 'lisi' where id = 1;
select * from user

rollback;
select * from user;

15.3 自动提交模式

自动提交模式用于决定新事务如何及何时启动。

1、启用自动提交模式：
  – 如果自动提交模式被启用，则单条DML语句将缺省地开始一个新的事务。
  – 如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。
  – 如果语句执行失败，事务将自动回滚，并取消该语句的结果。
  – 在自动提交模式下，仍可使用START TRANSACTION语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。

2、禁用自动提交模式：
  – 如果禁用自动提交，事务可以跨越多条语句。
  – 在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。
自动提交模式可以通过服务器变量AUTOCOMMIT来控制。
例如：

mysql> SET AUTOCOMMIT = OFF； 
mysql> SET AUTOCOMMIT = ON； 
或
mysql> SET SESSION AUTOCOMMIT = OFF； 
mysql> SET SESSION AUTOCOMMIT = ON； 
show variables like '%auto%'; -- 查看变量状态

15.4 事务的隔离级别

	事务A和事务B之间有隔离，隔离有级别，理论上有4个级别：

- 读未提交(Read Uncommited)：
		1.这个最低级别是理论值，数据库一般最低起步2档。
		2.对方事务没有提交的数据，在我当前事务当中能读取到。
		3.存在的问题是：脏读现象（dirty read）

- 读提交(read commited)：
		1.oracle默认的级别，也是最常用的隔离级别。
		2.对方事务提交之后的数据，在我当前事务当中能读取到，没有提交的读不到。
		3.解决了什么问题：解决了脏读现象。
		4.存在的问题是：不可重复读

- 可重复读(repeatable read)：
		1.mysql默认
		2.对方事务提交之后的数据在我当前事务中也读取不到。
		3.解决了什么问题：解决了不可重复读。
		4.存在的问题是：幻影读。存在数据假象。

- 序列化(serializable)：
		1.事务不可并发，事务排队执行。
		2.最高级别，解决了：数据假象。效率最低。最安全。

15.5 隔离级别与一致性的关系

隔离级别	脏读取	不可重复读	幻影读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
重复读	不可能	不可能	对InnoDB不可能
串行化	不可能	不可能	不可能

15.6 设置服务器缺省隔离级别

1、通过修改配置文件设置，可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。
• 该选项值可以是：
 – READ-UNCOMMITTED
 – READ-COMMITTED
 – REPEATABLE-READ
 – SERIALIZABLE
• 例如：

[mysqld] 文件中的 transaction-isolation = READ-COMMITTED

2、通过命令动态设置隔离级别
• 隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。
• 其语法模式为：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>

其中的可以是：
 – READ UNCOMMITTED
 – READ COMMITTED
 – REPEATABLE READ
 – SERIALIZABLE
• 例如：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

15.7 隔离级别的作用范围

• 事务隔离级别的作用范围分为两种：
  – 全局级：对所有的会话有效
  – 会话级：只对当前的会话有效
• 例如，设置会话级隔离级别为READ COMMITTED ：

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
或：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；

• 设置全局级隔离级别为READ COMMITTED ：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

15.8 查看隔离级别

• 服务器变量tx_isolation（包括会话级和全局级两个变量）中保存着当前的会话隔离级别。
• 为了查看当前隔离级别，可访问tx_isolation变量：
– 查看会话级的当前隔离级别：

mysql> SELECT @@tx_isolation;
或： 
mysql> SELECT @@session.tx_isolation;

– 查看全局级的当前隔离级别：

mysql> SELECT @@global.tx_isolation;

15.9 并发事务与隔离级别示例

15.9.1 读未提交

read uncommitted(读未提交) --脏读(Drity Read)

会话一	会话二
create table tx (id int(11),num int (10));
set global transaction isolation level read uncommitted;
start transaction;
	start transaction;
insert into tx values (1,10);
	select * from tx
rollback;
	select * from tx;

15.9.2 读已提交

read committed(已提交读)

会话一	会话二
set global transaction isolation level read committed;
start transaction;
	start transaction;
insert into tx values (1,10);
select * from tx;
	select * from tx;
commit;
	select * from tx;

15.9.3 可重复读

repeatable read(可重复读）

会话一	会话二
s1> set global transaction isolation level repeatable read;
start transaction;
	start transaction;
select * from tx;
insert into tx values (1,10);
	select * from tx;
commit;
	select * from tx;

16 索引

16.1 索引概述

1、索引是所有数据库当中都存在的机制。
2、索引相当于一本书的目录。
3、 一本书的目录有什么作用？提高查询效率而存在的，所以索引的作用 也是提高查询/检索的效率。
4、索引是给字段添加的。
5、 索引是一个对象，存储在硬盘文件当中的对象。xxx.MYI

  mysql在进行DQL语句(select)查询的时候有两种扫描机制：第一种：全表扫描机制第二种：根据索引进行检索。我们有一张表：emp，表中有empno和ename字段，假设sql语句是这样写的：

select * from emp where ename = 'FORD';

  执行的时候，先看ename上有没有添加索引，没有索引的时候，MYSQL会对这个ename字段中所有的数据全 部扫描。当这个ename字段上添加索引之后，MYSQL优先根据索引进行区域性扫描，缩小扫描的范围，提高检索速度。

索引的实现原理：
每一条数据都有一个真实存储的物理地址
创建索引对象：

create index emp_ename_index on emp(ename);
emp_ename_index对象（索引对象）：索引会自动排序

select * from emp where ename = 'JAMES';

  DBMS发现ename字段上有索引，这个时候会找索引对象，找到J区，进行区域性扫描，扫到JAMES之后，获取JAMES的物理地址fdsafds45af45dsa4f5dsa78然后这条sql语句就变成了：

select * from emp where 物理地址 = 'fdsafds45af45dsa4f5dsa78';

  根据物理地址定位数据，根本不需要扫描表，直接通过硬盘的地址检索的。速度很快，和表的大小无关。

16.2 创建索引

  如经常根据sal进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对sal建立索引，建立索引如下：

重要：（主键，unique 都会默认的添加索引）

1、create unique index 索引名 on 表名(列名);

create unique index u_ename on emp(ename);

2、alter table 表名 add unique index 索引名 (列名);
3、create index test_index on emp (sal);

16.3 查看索引

16.4 使用索引

explain select sal from emp where sal > 1500;

条件中的sal使用了索引
  如上图：假如我们要查找sal大于1500的所有行，那么可以扫描索引，索引时排序的，结果得出7行，我们知道不会再有匹配的记录，可以退出了。
如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。
  这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，通常这些技术都属于DBA的工作。

16.5 删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

  其中，前两条语句是等价的，删除掉table_name中的索引index_name。第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引

ALTER TABLE EMP DROP INDEX test_index;

删除后就不再使用索引了，查询会执行全表扫描。