Mysql学习之路事务、存储结构、sql优化

一 概述

  关系型数据库(Relation Data Base),建立在关系模型基础上，由多张相互链接的二维表组成的数据库。

  特点：

• 使用表存储数据，格式统一，便于维护
• 使用SQL语言，标准统一，使用方便。

二 SQL

  Mysql数据库中sql 语句不区分大小写，关键字建议使用大写。

2.1 Sql分类

2.1.1 数据定义语言DDL (Data Definition Language)

  数据定义语言，用来定义数据库对象(表，字段)。
  注:truncate 属于DDL语言，不会记录事务日志，无法回滚，大批量比Delete快(因为不用记录日志)。

• DDL查询

	--查询所有数据库
	show databases;
	--查询当前数据库
	select database();
	--查询当前数据库所有表
	show tables;
	--查询表结构
	desc 表名;
	--查询指定表的建表语句
	show create table 表名;

• 创建

	--创建数据库
	create database [if not exists] 数据库名称 [default charset 字符集] [collate 排序规则] 
	--创建表语句
	create table 表名(
		字段 字段类型
	)；

• 删除

	--删除数据库
	drop database [if exists] 数据库名称;
	--删除表
	drop table table_name;

• 使用

	--使用数据库
	use 数据库名称

2.1.2 数据操作语言DML (Data Manipulation Language)

  对表中的数据进行增删改的操作。insert\update\delete 语句

2.1.3 数据控制语言DCL (Data Control Language)

  数据库权限控制语言，控制用户权限以及创建数据库用户。

2.1.4 数据查询语言DQL(Data Query Language)

	--数据库查询语言。
	select * from table_name where 1 = 1 group by column_name having group_column_condition order by column_name desc;
	--分页查询，为数据库方言。
	limit 起始索引 ，条数;
		--起始索引：页码索引(通过当前页数-1)*条数
		--条数： 每一页展示的条数

  执行顺序：from -> where -> select -> group by -> having -> order by -> limit

2.2 常见函数

2.2.1 字符串

• concat(s1,s2),拼接两个字符串。
• trim(str) 去掉头部尾部空格
• substring(str,start,len) 截取str字符串，start开始，len为截取长度。

2.2.2 数值

• round(x,y) 对x进行四舍五入，保留y位小数。
• rand() 取0-1随机小数。
• floor(x) x向下取整。

2.2.3 时间

• now() 当前日期和时间。
• curdate() 返回当前日期。
• curtime() 当前时间。

2.2.4 流程控制函数

--condition判断条件，execute_way 满足条件执行的代码，deault_way不满足时执行。
case when condition then execute_way else [default_way] end;

2.3 约束

  非空约束、唯一约束、主键约束、默认约束、外键约束。

2.4 多表查询

2.4.1 内连接

• 隐式内连接:where后直接跟关联条件
• 显示内连接:通过inner join [表] on [关联条件]关联

2.4.2 外连接

   左外连接(left join [表] on [条件])、右外连接。将主表全部查出，匹配关联表符合条件的行。

2.4.3 子查询

• 标量子查询 :子查询中返回的值为单个值。
• 列子查询：返回多行单列值。
• 行子查询： 返回一行多列的值。
• 表子查询：返回多行多列的值

三 事务*

3.1 简介

  事务 是一组操作集合，是一个不可分割的工作单位，事务把所有操作作为一个整体一起提交或撤销操作，即要么同时成功，要么同时失败。

3.1.1 四大特性

• 原子性
  事务不可分割，要么全部成功，要么全部失败。
• 隔离性
  保证事务不被外部并发操作影响的独立环境下运行。
• 一致性
  事务完成时，必须所有数据都保持一致状态。
• 持久性
  事务提交或回滚，对数据库中的数据改变是永久的。

3.1.2 查案事务是否自动提交

	--查看事务是否自动提交
	select @@autocommit;
	--设置事务是否自动提交0否
	set @@autocommit 0;

3.2 并发事务问题*

  事务的隔离级别不同，造成不同的事务问题。

3.2.1 事务隔离级别

• 脏读 : A事务中可以读取到B事务中未提交的更新操作
• 不可重复读: A事务中两次执行同一条查询sql 结果不同，由于B事务提交更新操作提交导致。
• 幻读：A事务中查询一条数据不存在，但是插入操作时报错已经存在，再次查询还是不存在。B事务中已经插入相同数据，但是A不可见

（1）读未提交(Read uncommited)

  会出现脏读、不可重复读、幻读。该事务状态下。

（2）读已提交(Read conmmited)

   会出现不可重复读、幻读

（3） 可重复读(Repeatable Read - Mysql默认)

   会出现幻读。。

• 后续描述在该事务机制下也可以解决 不可重复读问题操作（MVCC）
• 后续描述在该事务机制下也可以解决幻读操作（间隙锁、临键锁）

（4） 串行化 (Serializable)

   解决以上问题，但消耗性能。

（5）查询当前事务隔离界别

  随着事务的隔离级别约安全，性能消耗越高。

	--查询事务隔离级别
	select @@Transaction_isolation;
	--设置事务隔离级别
	set [session会话级别|global系统级] transaction isolation level[Read uncommited | Read conmmited | 	Repeatable Read | Serializable]

四 存储引擎*

4.1 Mysql结构体系

• 连接层
  最上层是一些客户端和链接服务，主要链接处理、授权认证、及相关安全方案。
• 服务层
  Sql分析与优化，缓存查询、内置函数执行，可跨引擎操作。
• 引擎层
  负责Mysql数据存储和获取，服务层通过API和存储引擎进行通讯。
• 存储层
  将数据存储在文件系统之上，并与存储引擎交互。硬盘。

4.2 存储引擎

  存储引擎是基于表的，一个数据库中多个表，可以采用不同的存储引擎。默认innoDB。

	--创建表时指定存储引擎
	create table 表名{
		字段 字段类型 ;
	} engine