导图社区 MySQL
- 125
- 11
- 1
- 举报
MySQL
Contact MySQL|Login|Register MySQL HeatWave - One MySQL Database service for OLTP, OLAP, and ML MySQL HeatWave MySQL HeatWave is a fully managed service that enables customers to run...
编辑于2022-09-26 20:50:33 四川省
- Listener
listener 音标['lisnә] 读音 汉语翻译 n. 收听者, 听众 英语解释: 名词listener: someone who listens attentively 同义词:hearer, auditor, attender
- Filter
Filter过滤器(重要) Javaweb中的过滤器可以拦截所有访问web资源的请求或响应操作。 1、Filter快速入门 1.1、步骤: 1. 创建一个类实现Filter接口 2. 重写接口中方法 d...
- 会话
会话的解释 [conversation] 指两人以上的对话(多用于学习别种语言或方言时) 详细解释 (1).聚谈;对话。现多用于学习别种语言或方言时
MySQL
社区模板帮助中心，点此进入>>
- Listener
listener 音标['lisnә] 读音 汉语翻译 n. 收听者, 听众 英语解释: 名词listener: someone who listens attentively 同义词:hearer, auditor, attender
- Filter
Filter过滤器(重要) Javaweb中的过滤器可以拦截所有访问web资源的请求或响应操作。 1、Filter快速入门 1.1、步骤: 1. 创建一个类实现Filter接口 2. 重写接口中方法 d...
- 会话
会话的解释 [conversation] 指两人以上的对话(多用于学习别种语言或方言时) 详细解释 (1).聚谈;对话。现多用于学习别种语言或方言时
- 相似推荐
- 大纲
MySQL
介绍
MySQL是一个最流行的关系型数据库管理系统之一。由瑞典MySQL AB公司开发，后被Oracle公司收购。
关系型数据库是将数据保存在不同的数据表中，而不是将所有数据放在一个大仓库内，而且表与表之间还可以有关联关系。这样就提高了访问速度以及提高了灵活性。
MySQL所使用的SQL语句是用于访问数据库最常用的标准化语言
数据库、数据表、数据的关系
MySQL服务器中可以创建多个数据库
每个数据库中可以包含多张数据表
每个数据表中可以存储多条数据记录
客户端通过数据库管理系统来操作MySQL数据库
基本操作
使用
windows
mysql -u root -p密码：123456
基本命令
更改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
查看数据库
show databases;
进入mysql数据库
use mysql;
查看版本号
在终端下
mysql -V
mysql --help | grep Distrib
mysql命令
select version（）
status
备份和恢复
备份
命令行方式
1.使用SecureCRT工具连接到Linux系统，输入：mysqldump -u root -p 数据库名称 > 文件保存路径
2.进入文件保存路径，查看文件是否存在
恢复
命令行方式
1.登录mysql数据库
2.删除已经备份的数据库
3.重新创建名称相同的数据库
4.使用该数据库
5.导入文件执行：source 备份文件路径
SQL
介绍
SQL（Structured Query Language）：结构化查询语言。其实就是定义了操作所有关系型数据库的一种规则
SQL语言是非过程化语言，进行数据库操作时，只需要提出“做什么”，不需要指明“怎么做”，“怎么做”是由DBMS来完成
形成
交互式SQL
一般DBMS都提供联机交互工具
用户可直接键入SQL命令对数据库进行操作
由DBMS来进行解释
嵌入式SQL
能将SQL语句嵌入到高级语言（宿主语言）
使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力
一般需要预编译，将嵌入的SQL语句转化为宿主语言编译器能处理的语句
语法
SQL语句可以单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
注释
单行注释
--注释内容
#注释内容
MySQL特有
多行注释
/*注程内容*/
主要组成
DDL（Data Definition Language）
数据定义语言。用来操作数据库，表，列等
DML（Data Manipulation Language）
数据操作语言。用来对数据库中表的数据进行增删改
DQL（Data Query Language）
数据查询语言。用来查询数据库中表的记录（数据）
DCL（Data Control Language）
数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户
DDL
数据库
查询
查询所有数据库
SHOW DATABASES；
查询数据库的创建语句
SHOW CREATE DATABASE 数据库名称；
创建
创建数据库
CREATE DATABASE 数据库名称；
创建数据库（判断，如果不存在则创建）
CREATE DATABASE IF NOT EXISTS 数据库名称；
创建数据库（指定字符集）
CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称；
不指定字符集，默认是utf-8字符集
修改
修改数据库（修改字符集）
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称；
删除
删除数据库
DROP DATABASE 数据库名称；
删除数据库（判断，如果存在则删除）
DROP DATABASEIF EXISTS 数据库名称；
使用
使用数据库
USE 数据库名称；
查看当前使用的数据库
SELECT DATABASE()；
数据表
查询
查询所有的数据表
SHOW TABLES；
查询表结构
DESC 表名；
查询表字符集
SHOW TABLE STATUS FROM 库名 LIKE '表名'；
创建
创建数据表
常用数据类型
int
整数类型
double
小数类型
date
日期类型。包含年月日，格式yyyy-MM-dd
datetime
日期类型，包含年月日时分秒，格式yyyy-MM-dd HH:mm:ss
timestamp
时间戳类型。包含年月日时分秒，格式yyyy-MM-dd HH:mmss
如果不给该列赋值、或赋值为null，则默认使用当前系统时间自动赋值
varchar（长度）
字符串类型
修改
修改表名
ALTER TABLE 表名 RENAME TO 新表名；
修改表的字符集
ALTER TABLE 表名 CHARACTER SET 字符集名称；
单独添加一列
ALTER TABLE 表名 ADD 列名 数据类型；
修改某列的数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型；
修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型；
删除某一列
ALTER TABLE 表名 DROP 列名；
删除
删除数据表
DROP TABLE 表名；
删除数据表（判断，如果存在则删除）
DROP TABLE IF EXISTS 表名；
DML
表数据
增加
给指定列添加数据
INSERT INTO 表名（列名1，列名2，...） VALUES（值1，值2，...）；
给全部列添加数据
INSERT INTO 表名 VALUES（值1，值2，...)；
批量添加数据
注意
列名和值的数量以及数据类型要对应，除了数字类型，其他数据类型的数据都需要加引号（单引双引都行，推荐单引)
修改
修改表中的数据
注意
修改语句中必须加条件，如果不加条件，则会将所有数据都修改
删除
删除表中的数据
DELETE FROM 表名 [WHERE条件]；
注意
删除语句中必须加条件，如果不加条件，则会将所有数据都删除
DQL
语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后的过滤条件
ORDER BY
排序
LIMIT
分页
操作
查询全部
查询全部的表数据
SELECT * FROM 表名；
查询指定字段的表数据
SELECT 列名1，列名2... FROM 表名；
去除重复查询
SELECT DISTINCT 列名1，列名2，... FROM 表名；
计算列的值（四则运算）
SELECT 列名1 运算符（+-*/） 列名2 FROM 表名；
如果某一列为null，可以进行替换
ifnull（表达式1，表达式2）
表达式1：想替换的列
表达式2：想替换的值
起别名查询
SELECT 列名 AS 别名 FROM 表名；
条件查询
条件分类
>大于<小于>=大于等于<=小于等于=等于<> 或 !=不等于BETWEEN ... AND ...在某个范围之内(都包含)IN(...)多选一LIKE 占位符模糊查询 _单个任意字符 %多个任意字符IS NULL是NULLIS NOT NULL不是NULLAND 或 &&并且OR 或 ||或者NOT 或 !非，不是
语法
SELECT 列名 FROM 表名 WHERE 条件;
聚合函数查询
聚合函数
介绍
将一列数据作为一个整体，进行纵向的计算
分类
avg(列名)
平均值
count(列名)
统计数量(一般选用不为null的列)
max(列名)
最大值
min(列名)
最小值
sum(列名)
求和
语法
SELECT 函数名(列名) FROM 表名 [WHERE 条件];
排序查询
语法
注意
ASC升序(默认的) DESC降序
多个排序条件，当前边的条件值一样时，才会判断第二条件
分组查询
分页查询
语法
注意
开始索引（当前页码） = (当前页码-1) * 每页显示的条数
约束
介绍
通过约束对表中的数据进行限定，保证数据的正确性、有效性、完整性
分类
PRIMARY KEY
主键约束
介绍
主键约束包含：非空和唯一两个功能
一张表只能有一个列作为主键
主键一般用于表中数据的唯一标识
操作
建表时添加主键约束
删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;
建表后单独添加主键约束
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;
PRIMARY KEY AUTO_INCREMENT
主键自动增长约束
介绍
MySQL中的自增约束，必须配合键的约束一起使用
规定某列数量自动增长，无需手动赋值，配合null使用
操作
建表时添加主键自增约束
删除自动增长
ALTER TABLE 表名 MODIFY 列名 数据类型;
建表后单独添加自动增长
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
UNIQUE
唯一约束
介绍
规定一列数据不能重复
操作
建表时添加唯一约束
删除唯一约束
ALTER TABLE 表名 DROP INDEX 列名;
建表后单独添加唯一约束
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
NOT NULL
非空约束
介绍
规定一列数据不能出现null
操作
建表时添加非空约束
删除非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型;
建表后单独添加非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
FOREIGN KEY
外键约束
介绍
当表与表之间的数据有相关联性的时候，使用外键约束以保证数据的准确性
操作
建表时添加外键约束
删除外键约束
建表后添加外键约束
FOREIGN KEY ON UPDATE CASCADE
外键级联更新
介绍
当把主表中的数据进行修改时，从表中有关联的数据也会随之修改
操作
添加级联更新
FOREIGN KEY ON DELETE CASCADE
外键级联删除
介绍
当把主表中的数据进行删除时，从表中有关联的数据也会随之删除
操作
添加级联删除
多表操作
介绍
多表
多张数据表，表与表之间是可以有一定的关联关系，这种关联关系通过外键约束实现
多表设计
一对一
实现原则
在任意一个表建立外键，去关联另外一个表的主键
例子
人和身份证。一个人只有一个身份证，一个身份证只能对应一个人
一对多
实现原则
在多的一方，建立外键约束，来关联一的一方主键
例子
用户和订单。一个用户可以有多个订单
多对多
实现原则
需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键
例子
学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择
多表查询
笛卡尔积查询
SELECT 列名 FROM 表名1,表名2,...;
内连接查询
介绍
内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
操作
显式内连接
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;
隐式内连接
SELECT 列名 FROM 表名1,表名2 WHERE 条件;
外连接查询
介绍
左外连接
查询左表的全部数据，和左右两张表有交集部分的数据
右外连接
查询右表的全部数据，和左右两张表有交集部分的数据
操作
左外连接
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
右外连接
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
子查询
介绍
查询语句中嵌套了查询语句。将嵌套查询称为子查询
操作
结果是单行单列的
可以作为条件，使用运算符进行判断
结果是多行单列的
可以作为条件，使用运算符in或not in进行判断
结果是多行多列的
可以作为一张虚拟表参与查询
自关联查询
介绍
同一张表中有数据关联。可以多次查询这同一个表
用起别名的方式将一张表当成多张表使用即可
视图
介绍
视图是一种虚拟存在的数据表，这个虚拟的表并不在数据库中实际存在
作用
将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以在创建视图的时候，工作重心就要放在这条SELECT查询语句上
优点
简单
对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
安全
视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
数据独立
一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响
操作
创建
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
查询
查询视图
SELECT * FROM 视图名称;
查询视图创建
SHOW CREATE VIEW 视图名称;
修改
修改视图表中的数据
UPDATE 视图名称 SET 列名=值 WHERE 条件;
注意
视图表数据修改，会自动修改源表中的数据
修改视图表结构
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;
删除
DROP VIEW [IF EXISTS] 视图名称;
存储过程和函数
介绍
存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合
优点
存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用
减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可
减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率
将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理
区别
函数必须有返回值
存储过程没有返回值
补充
DELIMITER
该关键字用来声明sql语句的分隔符，告诉MySQL该段命令已经结束
sql语句默认的分隔符是分号，但是有的时候需要一条功能sql语句中包含分号，但是并不作为结束标识。这个时候就可以使用DELIMITER来指定分隔符了
@变量名
这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样
@@变量名
这种在变量前加上 "@@" 符号, 叫做系统变量
存储过程
基本操作
创建
调用
CALL 存储过程名称(实际参数);
查看
SELECT * FROM mysql.proc WHERE db='数据库名称';
删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;
语法
变量
定义
DECLARE 变量名 数据类型 [DEFAULT 默认值];
注意
DECLARE定义的是局部变量，只能用在BEGIN END范围之内
查询
SELECT 变量名;
赋值
SET 变量名 = 变量值;
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
if语句
参数传递
IN:代表输入参数，需要由调用者传递实际数据。默认的
OUT:代表输出参数，该参数可以作为返回值
INOUT:代表既可以作为输入参数，也可以作为输出参数
调用输入参数
CALL 存储过程名称(具体值);
CALL 存储过程名称(（查询语句）);
调用输出参数
CALL 存储过程名称(@参数名);
case语句
CASE 表达式WHEN 值1 THEN 执行sql语句1;[WHEN 值2 THEN 执行sql语句2;]...[ELSE 执行sql语句n;]END CASE;
CASEWHEN 判断条件1 THEN 执行sql语句1;[WHEN 判断条件2 THEN 执行sql语句2;]...[ELSE 执行sql语句n;]END CASE;
while循环
repeat循环
初始化语句;REPEAT 循环体语句; 条件控制语句; UNTIL 条件判断语句END REPEAT;
repeat循环是条件满足则停止。while循环是条件满足则执行
repeat循环至少执行一遍循环体
loop循环
初始化语句;[循环名称:] LOOP 条件判断语句 [LEAVE 循环名称;] 循环体语句; 条件控制语句;END LOOP 循环名称;
loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。可以使用leave语句完成！如果不加退出循环的语句，那么就变成了死循环
游标
介绍
游标可以遍历返回的多行结果，每次拿到一整行数据
在存储过程和函数中可以使用游标对结果集进行循环的处理
简单来说游标就类似于集合的迭代器遍历
MySQL中的游标只能用在存储过程和函数中
语法
创建
DECLARE 游标名称 CURSOR FOR 查询sql语句;
打开
OPEN 游标名称;
使用
FETCH 游标名称 INTO 变量名1,变量名2,...;
关闭
CLOSE 游标名称;
优化
1.加标记思想
1.定义一个变量，默认值为0(意味着有数据)
DECLARE flag INT DEFAULT 0;
2.当游标结束后，将变量值改为1(意味着没有数据了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
2.配合循环使用
当游标结束后，会触发游标结束事件。可以通过这一特性来完成循环操作
存储函数
介绍
存储函数和存储过程是非常相似的。存储函数可以做的事情，存储过程也可以做到！
存储函数有返回值，存储过程没有返回值(参数的out其实也相当于是返回数据了)
语法
创建
CREATE FUNCTION 函数名称([参数 数据类型])RETURNS 返回值类型BEGIN执行的sql语句;RETURN 结果;END$
调用
SELECT 函数名称(实际参数);
删除
DROP FUNCTION 函数名称;
触发器
介绍
触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句
触发器的特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发
分类
触发器类型
OLD的含义
NEW的含义
INSERT 型触发器
无 (因为插入前状态无数据)
NEW 表示将要或者已经新增的数据
UPDATE 型触发器
OLD 表示修改之前的数据
NEW 表示将要或已经修改后的数据
DELETE 型触发器
OLD 表示将要或者已经删除的数据
无 (因为删除后状态无数据)
语法
创建
INSERT触发器
UPDATE触发器
DELETE触发器
查看
SHOW TRIGGERS;
删除
DROP TRIGGER 触发器名称;
事务
介绍
事务：一条或多条 SQL 语句组成一个执行单元，这个单元要么同时成功要么同时失败
特点
单元中的每条 SQL 语句都相互依赖，形成一个整体
如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态
如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行
基本操作
步骤
1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
2. 执行sql语句：执行具体的一条或多条sql语句
3. 结束事务(提交|回滚)
提交：没出现问题，数据进行更新
回滚：出现问题，数据恢复到开启事务时的状态
语法
开启
START TRANSACTION;
回滚
ROLLBACK;
提交
COMMIT;
提交方式
分类
自动提交(MySQL默认为自动提交)
手动提交
语法
1代表自动提交 0代表手动提交
查看
SELECT @@AUTOCOMMIT;
修改
SET @@AUTOCOMMIT=数字;
四大特征(ACID)
原子性(atomicity)
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
一致性(consistency)
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
隔离性(isolcation)
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
持久性(durability)
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作
隔离级别
介绍
多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的
如果多个事务操作同一批数据时，就会产生问题，设置不同的隔离级别可以解决这些问题
注意
隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别
隔离级别
名称
隔离级别
会引发的问题
读未提交
read uncommitted
脏读、不可重复读、幻读
读已提交
read committed
不可重复读、幻读
Oracle / SQLServer默认
可重复读
repeatable read
幻读
MySQL默认
串行化
serializable
无
可能引发的问题
脏读
指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读
指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读
select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功
操作
查询
SELECT @@TX_ISOLATION;
修改
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;
存储引擎
介绍
MySQL体系结构
客户端连接
支持接口
支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库
第一层：网络连接层
连接池
管理、缓冲用户的连接，线程处理等需要缓存的需求
第二层：核心服务层
管理服务和工具
系统的管理和控制工具，例如备份恢复、复制、集群等
SQL接口
接受SQL命令，并且返回查询结果
查询解析器
验证和解析SQL命令，例如过滤条件、语法结构等
查询优化器
在执行查询之前，使用默认的一套优化机制进行优化sql语句
缓存
如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询
第三层：存储引擎层
插件式存储引擎
管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)
第四层：系统文件层
文件系统
配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存
存储引擎
引擎就是整个机器运行的核心，不同的引擎具备不同的功能
MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎
在关系型数据库中数据的存储是以表的形式存进行储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）
Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能
MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等
常用引擎
MyISAM
介绍
不支持事务、不支持外键、支持全文检索和表级锁定，读写相互阻塞，读取速度快，节约资源
表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中
如果应用是以查询操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的
InnoDB
介绍
MySQL5.5版本后默认的存储引擎
支持事务、支持外键、行级锁定 ,支持所有辅助索引(5.5.5后不支持全文检索)，高缓存，占用磁盘空间大 ,支持并发控制
表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd中
用于对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作，那么InnoDB存储引擎是比较合适的选择，比如BBS、计费系统、充值转账
MEMORY
介绍
对表的大小有限制，太大的表无法缓存在内存中
所有数据保存在RAM中，内存存储 , 速度快 ,不安全，不支持外键
表结构保存在.frm中
MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果
基本操作
查询数据库支持的引擎
SHOW ENGINES;
补充
support
指服务器是否支持该存储引擎
transactions
指存储引擎是否支持事务
XA
指存储引擎是否支持分布式事务处理
Savepoints
指存储引擎是否支持保存点
查询某个数据库中所有数据表的引擎
SHOW TABLE STATUS FROM 数据库名称;
查询某个数据库中某个数据表的引擎
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
创建数据表，指定存储引擎
CREATE TABLE 表名( 列名,数据类型, ...)ENGINE = 引擎名称;
修改表的存储引擎
ALTER TABLE 表名 ENGINE = 引擎名称;
索引
介绍
MySQL数据库中的索引：是帮助MySQL高效获取数据的一种数据结构！所以，索引的本质就是数据结构
索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型
在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
一张数据表，用于保存数据。 一个索引配置文件，用于保存索引，每个索引都去指向了某一个数据
分类
功能
普通索引
最基本的索引，它没有任何限制
唯一索引
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一
主键索引
一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引
组合索引
顾名思义，就是将单列索引进行组合
外键索引
只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作
全文索引
快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持
结构
B+Tree索引
MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型
B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序
Hash索引
MySQL中Memory存储引擎默认支持的索引类型
操作
创建索引
语法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称[USING 索引类型] ON 表名(列名...);
注意
索引类型默认是B+树
如果一个表中有一列是主键，那么就会默认为其创建主键索引
主键列不需要单独创建索引
查看索引
SHOW INDEX FROM 表名;
添加索引
普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);
唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
外键索引
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
添加外键约束，就是外键索引
全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
mysql只支持文本类型
删除索引
DROP INDEX 索引名称 ON 表名;
索引设计原则
磁盘存储
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB
InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率
创建索引时的原则
对查询频次较高，且数据量比较大的表建立索引。
使用唯一索引，区分度越高，使用索引的效率越高
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高
联合索引
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配
索引的字段可以是任意顺序的
锁
介绍
锁机制 : 数据库为了保证数据的一致性，而使用各种共享的资源在被并发访问时变得有序所设计的一种规则
在数据库中，数据是一种供许多用户共享访问的资源，如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，MySQL由于自身架构的特点，在不同的存储引擎中，都设计了面对特定场景的锁定机制，所以引擎的差别，导致锁机制也是有很大差别的
分类
操作
共享锁
也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据记录
排他锁
也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
粒度
表级锁
操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎，InnoDB也支持
行级锁
操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎
页级锁
锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般
使用方式
悲观锁
介绍
对于数据被外界修改的操作持保守态度，认为数据随时会修改
整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制
行锁，表锁不论是读写锁都是悲观锁
使用场景
如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁
乐观锁
介绍
每次查询数据时都认为别人不会修改，所以不去加锁
但是更新时会判断一下在此期间数据有没有被修改
需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性
使用场景
对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁
实现方式
版本号
给数据表中添加一个version列，每次更新后都将这个列的值加1。
读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新
时间戳
给数据表中添加一个列，名称无所谓，数据类型需要是timestamp
每次更新后都将最新时间插入到此列。
读取数据时，将时间读取出来，在执行更新的时候，比较时间。
如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
基本使用
InnoDB
共享锁
SELECT语句 LOCK IN SHARE MODE;
排它锁
SELECT语句 FOR UPDATE;
注意
InnoDB引擎如果不采用带索引的列。则会提升为表锁
锁的兼容性
共享锁和共享锁
兼容
共享锁和排他锁
冲突
排他锁和排他锁
冲突
排他锁和共享锁
冲突
MyISAM
读锁
加锁
LOCK TABLE 表名 READ;
解锁
UNLOCK TABLES;
将当前会话所有的表进行解锁
写锁
加锁
LOCK TABLE 表名 WRITE;
解锁
UNLOCK TABLES;
将当前会话所有的表进行解锁
InnoDB锁优化建议
尽量通过带索引的列来完成数据查询，从而避免InnoDB无法加行锁而升级为表锁
合理设计索引，索引要尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定
尽可能减少基于范围的数据检索过滤条件。
尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁的产生
集群
介绍
一台数据库服务器已经无法满足海量数据的存储需求，所以由多台数据库构成的数据库集群成了必然的方式
MyCat
一款数据库集群软件
一个新颖的数据库中间件产品支持MySQL集群，提供高可用性数据分片集群
MyCat不单单是支持MySQL，像常用的关系型数据库Oracle、SqlServer都支持
主题