Mysql务必了解的知识

首先说下mysql比较重要以及常用的两个引擎

InnoDB和MyISAM

创建表格的时候可以指定表的存储引擎，语句如下
例：创建一个表格，指定引擎为MyISAM，编码为utf8

CREATE TABLE my_table(
	字段名称，数据类型...
) ENGINE=MyISAM DEFAULT CHARSET=utf8

有什么区别？

1.InnoDB支持事务，支持行级锁，支持外键（实际当中尽量不使用外键，减少表关联）
2.MyISAM读比InnoDB快，但写操作远远低于innoDB

事务的ACID（Transaction）？

原子性
操作指令时，要么全部成功，要不全部不执行，只要有一个指令执行失败，数据进行回滚，回到执行前的数据状态。
一致性
事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据库的完整性保持稳定。
比如A和B银行账户各有100元，A给B转100元，我们要做的是在A账户上减去100元并在B的账户上加上100元，尔A账户减了100元B的账户还没有加上这100元的中间状态是不可见的。
隔离性
当多个用户并发访问数据库时，比如操作同一张表，数据库为每一个用户开启一个事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
既要达到一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始前就已经结束，要么在T1结束之后在开始。
持久性
当事务正确完成后，它对于数据的改变是永久性的.

事务的隔离级别

隔离级别会出现的五种问题：
写写的情况下：
第一类丢失更新：事务回退把其他事务以提交的数据覆盖了。
第二类丢失更新：事务提交把另一个事务提交的数据覆盖了。
读写的情况下：
脏读： 一个事务读到另一个事务尚未提交的数据，就是脏读。（后果很严重）
不可重复读：在一个事务对一个数据读取两次的结果不一致，另一个事务在这个期间修改了这个数据。
幻读：在一个事务中两次读取的记录数不一样，另一个事务在这个期间增加或者删除了这一行。

悲观锁和乐观锁

悲观锁：mysql自带的锁，当数据库对一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁防止并发，在数据修改之前锁定，在修改的方式被称之为悲观锁。
乐观锁：并非加锁，需要自己的应用实现，数据进行提交更新时候，查询记录数版本是否有变化，如果没有则更新，更新前先查记录数，等到更新时加上记录数条件即可，如果因并发记录数被改变，则会更新失败数据自动回滚.。

悲观锁实现（for update）

//0.开始事务
begin; 
//1.查询出商品库存信息
select quantity from items where id=1 for update;
//2.修改商品库存为2
update items set quantity=2 where id = 1;
//3.提交事务
commit;

乐观锁实现

//查询出商品信息  version = 1
select version from items where id=1 
//修改商品库存为 
update items set quantity=2,version = 2where id=1 and version = 1

MVCC

即多版本并发控制，可以在并发下读不加锁，让读写不冲突，提高数据库并发性能，只适用与已提交读和可重复读级别。
它的实现原理主要版本链，undo日志，Read View 来实现的。
它的实现方式是，通过记录里的隐式字段，事务ID，回滚指针，再通过视图来分析这个事务能看到的最新数据，事务ID是增长的，当做了事务启动时间用来比较的作用，回滚指针用于配合undo日志，指向上一个版本。

当前读
它读取的数据库记录，都是当前最新版本，会对当前读取的数据行进行加锁没防止其他事务修改数据，是悲观锁的一种手段，以下操作都是当前读。

select lock in share mode (共享锁)
select for update (排他锁)
update (排他锁)
insert (排他锁)
delete (排他锁)

快照读
快照读的实现基于MVCC，既然是基于多版本，读取到的不一定是当前的最新数据，有可能是历史版本数据，如下操作时快照读：
1.不加锁的select操作（事务级别不是串行化）

SELECT * FROM t WHERE id=1

视图

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，内容由查询定义，方便用户对数据的操作。
它主要出于两种原因：安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；另一个原因是可使复杂的查询易于理解和使用。
创建视图
1.查看用户是否有查询以及创建视图的权限

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名';

参数说明：
（1）Select_priv：属性表示用户是否具有SELECT权限，Y表示拥有SELECT权限，N表示没有。
（2）Create_view_priv：属性表示用户是否具有CREATE VIEW权限；
（3）mysql.user：表示MySQL数据库下面的user表。
（4）用户名：参数表示要查询是否拥有权限的用户，该参数需要用单引号引起来。

2.创建视图

CREATE OR REPLACE VIEW 视图名
AS
	SELECT 属性值1，属性值2 FROM 表名;

函数说明：
(1) OR REPLACE 替换或覆盖相同名称的视图名，加上此函数需要用户有删除权限。可选

3.修改视图

ALTER VIEW 视图名
AS
	SELECT 属性值1，属性值2 FROM 表名;

删除视图

DROP VIEW IF EXISTS 视图名;

视图中使用IF和CASE语句
查询视图

SELECT* FROM 视图名

索引

索引是对数据库表中一列或者多列的值进行排序的一种结构，索引可以大大提高Mysql的检索速度，他的原理和看书一样，想看哪个文章通过目录即可快速找到想看到的文档，不需要一页一页的查询查找，那这个目录就是索引。

优点：
索引大大减小了服务器需要扫描的数据量，提高检索速度，这也是创建索引最重要的原因。
缺点
索引需要占用物理空间，并且创建索引和维护索引要耗费时间，这种时间随着数据量的增加尔增加。

索引的分类

索引分类
1.普通索引index :加速查找
2.唯一索引
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用

创建索引SQL

create index name on s1(name); #添加普通索引
create unique age on s1(age);添加唯一索引
alter table s1 add primary key(id); #添加主键索引，也就是给id字段增加一个主键约束
create index name on s1(id,name); #添加普通联合索引