5 mysql高级知识

视图

• 视图是一种虚拟存在的表，对于视图用户来说基本上是透明的，视图并不杂数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，视图就是查询结果集
• 视图的作用
  • 简单：提高了重用性，就像一个函数
  • 安全：提高了安全性能，可以针对不同的用户，设定不同的视图
  • 数据独立：视图结构固定后，可以屏蔽表结构变化对用户的影响，源表增加列对视图没影响；源表修改列名可以通过修改视图来解决，不会造成对访问者的影响
• 视图的命令
  • create view 视图名称 as set 语句
    （创建视图 as后接的是查询语句，查询语句结果不可有重复字段，可起别名）
  • select * from 视图名称
    （查询视图）
  • drop view 视图名称;
    （删除视图）
• 视图的修改
  • 有下列内容之一，视图不能做修改
    • select子句中包含distinct
    • select子句中包含函数
    • select语句中包含group by子句
    • select语句中包含order by子句
    • where子句中包含相关子查询
    • from子句中包含多个表
    • 如果视图中有计算列，则不能更新
    • 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

事务

• 事务是一个操作序列，这些操作要么都执行要么都不执行，它是一个不可分割的工作单位
• 事务的四大特性（简称acid）
  • 原子性（A）
    • 一个事务被视为像原子一样的最小工作单元是不可分割，要么一起执行要么全部回滚，不会出现执行一部分
  • 一致性（C）
    • 数据库总是从一个一致性的状态转换到另一个一致性的状态
  • 隔离性（I）
    • 通常来说，一个事务所做的修改在最终提交前，对其他事务是不可见的
  • 持久性（D）
    • 一旦事务提交，则其所做的修改会永久保存到数据库

事务的状态

• 活动的
  • 事务对应的数据库操作正在执行过程中
• 部分提交的
  • 当事务的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时
• 失败的
  • 当事务在活动或者部分提交状态时遇到某些错误
• 中止的
  • 事务执行了半截而变为失败的状态
• 提交的
  • 在部分提交状态将修改的数据都同步到磁盘后

事务的命令

• begin;
  （开启事务）
• start transaction;修饰符;
  （开启事务，与上一个不同的是可以指定事务模式，修饰符可为read only，read write）
• commit;
  （提交事务）
• rollback to 保存点名字;
  （回滚到保存点时状态，无保存点则回滚到开启事务前的状态）
• savepoint 保存点名字;
  （创建保存点）
• release savepoint 保存点名字;
  （删除保存点）
• 修改数据的命令会触发自动的触发事务，包括insert、update、delete，可通过show variables like ‘autocommit’;查看并修改自动提交

索引

• 索引是一种特殊的文件（innoDB数据表上的索引是表空间的一个组成部分），它们包含对数据表里所有记录的引用指针，通俗来讲，索引就是一本书的目录
• 目的：提高查询速度，排序
• 原理：B+树 BTREE
• B+树的查询过程
• 索引的命令
  • create unique index 索引名字 on 表名字(字段);
    （创建索引,unique唯一索引）
  • show index from 表名;
    （查看索引）
  • drop index 索引名 on 表名;
    （删除索引）
• 适合建立索引的情况
  • 主键自动建议索引
  • 频繁作为查询条件的字段应该建立索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 在高并发的情况下创建复合索引
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
    （建立索引的顺序跟排序的顺序保持一致）
• 不适合建立索引的情况
  • 频繁更新的字段不适合建立索引
  • where条件里面用不到的字段不创建索引
  • 表记录太少，当表中数据超过三百万条数据，可以考虑建立索引
  • 数据重复且平均的表字段，比如性别，国籍

账户管理

• 在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账号，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud
• 账户的操作主要包括创建账户、删除账户、修改密码、授权操作等
• 常用的权限主要包括：create、alter、drop、insert、update、dalete、select
• 需要root实例级用户登陆后才可授权
• create user’username’@’%’ identified by ‘password’;
  （创建用户，username为登陆的用户名，password为登陆的密码，host是指定可以登陆的主机，其中localhost表示本地主机，%表示所有主机）
• alter user user() identified by ‘password’;
  （修改用户）
• drop user username;
  （删除用户）
• grant 权限列表 on 数据库.* to ‘用户名’@‘访问主机’ identified by ‘密码’;
  （授权给用户）

事务的隔离级别

• 隔离性其实比想象要复杂，在sql中定义了四种隔离级别，每种隔离级别都规定了事务中的修改，哪些是在事务内和事务间是可见的，哪些是不可见的，较低级的隔离通常来说能承受更高的并发，系统的开销也会更小
• 未提交读 read uncommitted
  • 脏读
  • 事务的修改即使没有提交，对其他事务也是可见的，虽然性能是最优的，但是缺乏其他级别的很多好处
• 读已提交的 read commited
  • 大多数数据库默认的隔离级别就是这个，但是mysql不是
  • 一个事务只能看见已经提交的事务的修改结果
  • 在一个事务中查询到的结果可能不一致
• 可重复读 repeatable read
  • mysql默认的隔离级别，解决了脏读的问题，但无法避免换读，幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行问题，mysql的innodb引擎通过多版本并发控制（mvcc）解决了幻读问题
• 可串行化 serializeble
  • 可串行化会在读取的每一行数据都加锁，所以可能导致大量的锁等待和超时问题
  • 隔离级别最高
  • 阻塞
• select @@tx_isolation;
  （查看当前事务隔离等级）
• set session transaction isolation level 事务隔离等级;
  （设置事务隔离等级）