MySQL攻略_java_内存溢出

一、索引

1.1 索引的原理

1.2 索引的类型

1.3 索引使用

二、mysql事务

2.1 什么是事务

2.2 事务和锁

2.3 回退事务

2.4 提交事务

2.5 事务的使用细节

三、mysql事务隔离级别

3.1 介绍

3.2 查看事务隔离级别

3.3 事务隔离级别

3.4 设置事务隔离级别

四、mysql事务ACID

4.1 事务的acid特性

五、mysql表类型和存储引擎

5.1 基本介绍

5.2 主要的存储引擎/表类型特点

5.3细节说明

一、索引 1.1 索引的原理

1. 没有索引会全表扫描，从而查找速度会很慢

2. 使用索引会形成一个索引的数据结构，比如二叉树

3. 索引的带价

①磁盘占用

②对dml(update、delete、insert)语句的效率影响

1.2 索引的类型

1. 主键索引，主键自动的为主索引(类型Primary key)

2. 唯一索引(UNIQUE)

3. 普通索引(INDEX)

4. 全文索引(FULLTEXT)，一般开发不适用mysql自带的全文索引，而是使用：全文搜索Solr和ElasticSearch(ES)

1.3 索引使用

添加索引

create [UNIQUE] index index_name ON table_name (col_name[(length)]) [ASC| DESC],..);

添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY(列名，.....)；

删除索引

DROP INDEX index_name ON table_name；

删除主键索引

ALTER TABLE table_name DROP PRIMARY KEY；

查找索引

show index(ex) from table_name；

show keys from table_name；

desc table_name；

二、mysql事务 2.1 什么是事务

事务用于保存数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性

2.2 事务和锁

当执行事务 *** 作时(dml语句)，mysql会在表上加锁，防止其它用户改表的数据

mysql数据库控制台事务的几个重要 *** 作

1. 开始一个事务：start transaction

2. 设置保存点：savepoint

3. 回退事务：rollback to

4. 回退全部事务：rollback

5. 提交事务，所有的 *** 作生效，不能回退：commit

-- 事务的一个重要的概念和具体 *** 作
-- 1. 创建一张测试表
CREATE TABLE t28(
	id INT,
	`name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保存点
SAVEPOINT a
-- 执行 dml  *** 作
INSERT INTO t28 VALUES(100,'tom');

SELECT * FROM t28;

SAVEPOINT b

-- 执行 dml  *** 作
INSERT INTO t28 VALUES(200,'jack');

-- 回退到 b
ROLLBACK TO b
-- 继续回退 a
ROLLBACK TO a

-- 如果这样, 表示直接回退到事务开始的状态. 
ROLLBACK
COMMIT

2.3 回退事务

介绍回退事务之前先介绍一下保存点(savepoint)。保存点是事务中的点，用于取消部分事务，当结束事务时(commit)，会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点

2.4 提交事务

使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用commit语句结束事务后，其他会话(其他连接)将可以查看到事务变化后的新数据(所有数据就正式生效)

2.5 事务的使用细节

1. 如果不开始事务，默认情况下，dml *** 作是自动提交的，不能回滚

2. 如果开始一个事务，没有创建保存点，也是可以执行rollback，默认就是回退到事务开始的状态

3. 可以在这个事务中(还没有提交时)，创建多个保存点

4. 可以在事务没有提交前，选择回退到哪个保存点

5. mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好使

6. 开始一个事务可以用start transaction，也可以使用set autocommit=off

-- 讨论 事务细节
-- 1. 如果不开始事务，默认情况下，dml  *** 作是自动提交的，不能回滚
INSERT INTO t28 VALUES(300,'milan'); -- 自动提交commit

SELECT * FROM t28;

-- 2. 如果开始一个事务，你没有创建保存点. 你可以执行 rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t28 VALUES(400, 'king');
INSERT INTO t28 VALUES(500, 'scott');
ROLLBACK -- 表示直接回退到事务开始的的状态
COMMIT;

-- 3. 你也可以在这个事务中(还没有提交时), 创建多个保存点.比如: savepoint aaa; 
-- 执行 dml , savepoint bbb

-- 4. 你可以在事务没有提交前，选择回退到哪个保存点
-- 5. InnoDB 存储引擎支持事务 , MyISAM 不支持
-- 6. 开始一个事务 start transaction, set autocommit=off

三、mysql事务隔离级别 3.1 介绍

1. 多个连接开启各自事务 *** 作数据库中数据时，数据库系统要负责隔离 *** 作，以保证各个连接在获取数据时的准确性

2. 如果不考虑隔离性，可能会引发脏读、不可重复读、幻读

隔离级别就是该事务读取连接到数据库时的数据，不希望读到其他事务在该时间点之后修改的数据，所以产生了前三种隔离级别

3.2 查看事务隔离级别

脏读：当一个事务读取另一个事务尚未提交的改变

不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读

幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入 *** 作，每次返回不同的结果集，此时发生幻读

脏读是别的事务提交前自己就会被影响，不可重复读和幻读是提交后被影响

不可重复读是修改或删除造成的影响

幻读是指插入造成的影响

3.3 事务隔离级别

概念：Mysql隔离级别定义了事务与事务之间的隔离程度

3.4 设置事务隔离级别

Mysql默认的事务隔离级别是repeatable read，一般情况下没有特殊要求没有必要修改(因为该级别可以满足绝大部分项目需求)

查看当前会话隔离级别

select @@tx_isolation;

查看系统当前隔离级别

select @@global.tx_isolation;

设置当前会话隔离级别

set global transaction isolation level repeatable read;

设置系统当前隔离级别

set global transaction isolation level repeatable read;

-- 演示 mysql 的事务隔离级别
-- 1. 开了两个 mysql 的控制台

-- 2. 查看当前 mysql 的隔离级别
SELECT @@tx_isolation; 
-- mysql> SELECT @@tx_isolation; 
-- +-----------------+
-- | @@tx_isolation | 
-- +-----------------+
-- | REPEATABLE-READ | 
-- +-----------------+

-- 3.把其中一个控制台的隔离级别设置 Read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 4. 创建表
CREATE TABLE `account`(
	id INT, `name` VARCHAR(32), money INT);

-- 查看当前会话隔离级别
SELECT @@tx_isolation

-- 查看系统当前隔离级别
SELECT @@global.tx_isolation

-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]

四、mysql事务ACID 4.1 事务的acid特性

1. 原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的 *** 作要么都发生，要么不发生

2. 一致性(Consistency)：事务必须使数据库从一个一致性状态变换到另外一个一致性状态

3. 隔离性(Isolation)：事务的隔离性是多个用户并发访问数据库时，数据库位每一个用户开启的事务，布恩那个被其他事务的 *** 作数据所干扰，多个并发事务事务之间要相互隔离

4. 持久性(Durability)：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

五、mysql表类型和存储引擎 5.1 基本介绍

1. MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MylSAM、innoDB、Memory等

2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB

3. 这六种又分两类，一类是“事务安全型”(transaction-safe)，比如：InnoDB；其余都属于第二类，称为“非事务安全型”(non-transaction-safe) [mysiam和memory]

5.2 主要的存储引擎/表类型特点

5.3细节说明

重点介绍：MyISAM、InnoDB、MEMORY

1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整型没有要求

2. InnoDB存储引擎提高了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一点并且会占用更多的磁盘空间以保留数据和索引

3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表指实际对应一个磁盘文件。MEMORY类型的表访问非常的快，因为他的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，但表的结构还在

-- 表类型和存储引擎

-- 查看所有的存储引擎
SHOW ENGINES

-- innodb 存储引擎，是前面使用过. 
-- 1. 支持事务 2. 支持外键 3. 支持行级锁

-- myisam 存储引擎
CREATE TABLE t29(
	id INT,
	`name` VARCHAR(32))ENGINE MYISAM
-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁
START TRANSACTION;
SAVEPOINT t1
INSERT INTO t29 VALUES(1,'jack')
SELECT * FROM t29;
ROLLBACK TO t1;

-- memory 存储引擎
-- 1. 数据存储在内存中[关闭了 Mysql 服务，数据丢失, 但是表结构还在]
-- 2. 执行速度很快(没有 IO 读写) 3. 默认支持索引(hash 表)
CREATE TABLE t30(
	id INT,
	`name` VARCHAR(32))ENGINE MEMORY
DESC t30
INSERT INTO t30
	VALUES(1,'tom'),(2,'jack'),(3,'ggm');
SELECT * FROM t30

-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB

5.4 如何选择表的存储引擎

1. 如果你的应用不需要事务，处理的只是基本的CRUD *** 作，那么MyISAM是不二选择，速度快

2. 如果需要支持事务，选择InnoDB

3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快，但由于是内存存储引擎，所作的任何修改在服务器重启后都将消失(经典用法就是用户的在线状态)

5.5 修改存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎；

六、视图 6.1 基本概念

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表(基表)

6.2 视图的基本使用

创建视图：create view 视图名 as select语句

更新成新的视图：alter view 视图名 as select语句

查看创建视图的指令：show create view 视图名

删除视图：drop view 视图名

-- 视图的使用
-- 创建一个视图 emp_view01，只能查询 emp 表的(empno、ename, job 和 deptno ) 信息
-- 创建视图
CREATE VIEW emp_view01
	AS
	SELECT empno,ename,job,deptno FROM emp;
-- 查看视图
DESC emp_view01;

SELECT * FROM emp_view01;
SELECT empno, job FROM emp_view01

-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;

-- 视图的细节
-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
-- 修改视图 会影响到基表
UPDATE emp_view01
	SET job = 'MANAGER'
	WHERE empno = 7369
	
SELECT * FROM emp; -- 查询基表

SELECT * FROM emp_view01;

-- 修改基本表， 会影响到视图
UPDATE emp
	SET job = 'SALESMAN'
	WHERE empno = 7369

-- 3. 视图中可以再使用视图 , 比如从 emp_view01 视图中，选出 empno,和 ename 做出新视图
DESC emp_view01

CREATE VIEW emp_view02
	AS
	SELECT empno,ename FROM emp_view01
	
SELECT * FROM emp_view02;

6.3 视图的使用细节

1. 创建视图后，到数据库去看，对应的视图只有一个视图结构文件(形式：视图名.frm)

2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图【insert update delete】

3. 视图中可以再使用视图，数据仍然来自基表

6.4 视图最佳实践          1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段,不能查看保密的字段。
        2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
        3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。七、Mysql管理 7.1 mysql用户

mysql中的用户都存储在系统数据库mysql的user表中

其中user表的重要字段说明：

1. host：允许登录的“位置”，localhost表示该用户只允许本机登录。也可以指定ip地址

2. user：用户名

3. authentication_string：密码，是通过mysql的password()函数加密之后的密码

7.2 创建用户