MySQL高级

MySQL高级

视图

视图就是将一个较为复杂的查询语句包装在视图中,简化语句,视图被存储在数据库中,可以重复使用.

视图中并不存储数据

-- 创建视图
CREATE VIEW sel_news AS 
	SELECT
	  n.id,
	  n.title,
	  t.name
	FROM
	  news n
	  LEFT JOIN TYPE t
	    ON n.typeid = t.id
-- 使用视图   
SELECT * FROM sel_news;  

-- 删除视图
DROP VIEW sel_news;

存储过程

对数据库的认知: 里面可以以表为单位存储数据, 使用sql语言 操作数据.

数据库中也是可以向java语言一样有逻辑处理功能。

事先被编写好,存储在数据库中, 使用时直接调用即可.

优点: 处理某个逻辑的过程直接存储在数据中, 运行速度较快

缺点: 对数据库依赖程度较高,移植性差.

函数

函数类似于存储过程,但是函数主要用于查询

触发器

类似于存储过程,函数, 与表相关, 有点向事件

对表 新增,修改,删除 之前或之后自动触发

触发器具有以下特点：

1.与表相关联

触发器定义在特定的表上，这个表称为触发器表。

2.自动激活触发器

当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这

个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。

3.不能直接调用

与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。

4.作为事务的一部分

触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中

的任何位置回滚。

mysql架构

连接层

负责与客户端和程序建立连接,认证…

服务层

sql接口

​ 解析器

​ 查询优化器

​ 缓存

引擎层

负责与数据文件系统连接,读,写数据

物理文件层

负责存储表数据,日志文件(非常重要 mysql事务实现就是依赖于日志的)

DBA database admin 数据库管理员

Mysql存储引擎

引擎是什么

引擎是数据库中具体与文件进行交互的技术,不同的引擎,实现方式有区别的.

每张表都有对应的引擎来进行处理,

mysql有哪些常用引擎

重点学习

MyiSam 不支持事务 查询多

​ 不支持事务,不支持外键,不支持行锁,支持表锁,支持全文索引,存储表的总行数
​ select count(*) from admin 直接获取到总行数即可 快

Innodb 支持事务 增删改多

​ 支持事务,支持外键,表锁,行锁,支持缓存,支持全文索引,支持主键自增,适合于处理增,删,改 比较多的场景. 不存储总行数.

select count(*) from admin 自行统计计算 慢

索引

数组索引 可以通过索引快速的找到某个位置的数据.

为什么要有索引呢？

不使用索引的话,查询从第一行开始,逐行向后查询,直到查询到我们需要的数据.

​ 如果数据量非常大的情况下,查询效率比较低.

什么是索引

索引是帮助 MySQL 高效获取数据的数据结构。

排好序的快速查找的数据结构.

在一个数据结构中将数据维护者,方便查找

索引原理

​ 索引就类似书的目录,通过目录快速的查询到我们需要的数据. 缩小查询范围

索引优势

提高数据检索的效率,降低数据库的 IO 成本;

通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗;

索引劣势

​ 索引也是需要占用硬盘空间

​ 对表进行新增,修改,删除操作时,在操作数据的同时,也需要对索引信息作出修改

索引创建的原则

索引虽好,但不要乱用

什么情况下需要索引

主键自动建立唯一索引 主键索引

作为查询条件的列 适合创建索引

外键建议建立索引

​ 排序,分组字段适合添加索引

什么情况下不建议使用索引

表记录太少
新增,修改,删除频繁的表 分表 将本来一张表,拆分 读写分离

不是查询条件

数据重复且分布均匀的 例如: 性别

索引分类

主键索引：

​ 设定为主键后数据库会自动建立索引 不能为空,唯一的 一个表只有一个主键.

单值索引：

​ 一个索引包含一个列, 一个表可以有多个单值索引.

​ name account

唯一索引:

​ 数据不能重复

组合索引(复合索引)：

​ 即一个索引包含多个列

组合索引最左前缀原则:

​ 使用组合索引时,要出现最左列,否则索引失效

例如表中有 a,b,c 3 列,为 a,b 两列创建组合索引

例如 select * from table where a=’’and b=’’索引生效

select * from table where b=’’and a=’’索引生效

select * from table where a=’’and c=’’索引生效

select * from table where b=’’and c=’’索引不生效

使用模糊查询 name like %张%; 这样写 ,回导致name列的索引失效 like的模糊查询不建议使用

mysql8中建议使用 全文索引

全文索引

– 全文索引
CREATE FULLTEXT INDEX news_title ON news(title) WITH PARSER ngram;
SELECT * FROM news WHERE MATCH(title) AGAINST(‘小康’)

索引数据结构

​ mysql Innodb引擎默认使用 B+树 作为数据结构存储索引.
​ 排好序的,一个节点可以存储多个数据. 横向扩展, 使得树的高度降低了.

​ 非叶子节点不存储数据,只存储索引,可以放更多的索引.

​ 数据记录都存放在叶子节点中. 找到了索引,也就找了数据.

​ 所有叶子节点之间都有一个链指针,非常适合区间查询 age>20 age<50

聚簇索引和非聚簇索引

聚簇索引: 找到了索引,就找到了数据 就是聚簇索引.

​ 主键可以直接找到数据

​ 根据学号只查询学号 可以直接命中学号 此种场景学号就是聚簇的

非聚簇索引 : 找到了索引但没有找到数据, 需要根据主键再次回表查询

根据学号只查询学号,姓名 虽然学号加了索引,但是还需要查询姓名,

需要根据学号,找到主键,通过主键回表查询 此种场景就是非聚簇的

​ 而 MyISAM 引擎采用的是非聚簇式设计,即使是主键索引

事务

事务就是一次完整的数据库操作,这个操作过程中可能包含多条sql的执行. 这多条sql的自执行时是一个整体.
要么都执行成功,要么都执行失败.

举例 转账操作 从A账号 向B账号转钱

​ A-100

​ B+100

​ 网购下单支付

​ 下订单到卖家

​ 支付到平台

Mysql中只有Innodb引擎支持数据库事务.

事务用来管理 insert,update,delete 语句.

关系型数据事务4大特性:

原子性: 一次事务过程中的多个操作要么都成功,要么都失败.

持久性: 事务一旦提交,数据就不可改变.即使数据库服务出现问题.

隔离性: 数据库是允许同时有多个事务进行访问, 这时就需要对多个事务间的操作进行隔离,

​ 隔离分为4个级别:

​ 读未提交 问题 脏读

​ 读已提交 解决 脏读 不可重复读

​ 可重复读 解决 不可重读 幻读

​ 串行化 解决一切问题 加锁 效率低

**一致性: **在事务开始之前和事务结束以后，数据库的完整性没有被破坏.

​ 例如: 我们多种方式对银行账户的余额进行多次同时操作,最终余额应该是我们所预期的结果,不能出现错误.

mysql并发操作的问题:

​ 1.脏读 读到了垃圾数据 A事务读到B事务 未提交的数据

​ 2.不可重复读 A事务开启后 读取两次数据,结果两次读到的内容不一样( 预期的效果是A在同一个事务中读取到数据应该是一样)

3.幻读 A事务开启后, 读取到的两次数据数量不一致

事务隔离级别:

读 未提交: A 可以读到B未提交的数据 问题: 会有脏读 几乎不用

读已提交: A不能读到B为提交的数据,只能读到B已提交的数据. 解决了脏读问题, 同时会发生不可重复读问题

可重复读: A事务开启后,第一次读到某个数据后,那么在这个事务中,第二次再查询同样的数据时,和原来是一致,重复读. 解决了不可重复问题

串行化: 解决所有问题, 一次只允许一个事务进行操作 是最安全的,但是效率是最低的.

MVCC

MVCC（多版本并发控制 Multi-Version Concurrent Control）

为了提升mysql 读-写,写-读两个操作同时进行, 写-写mysql支持行级锁的,如果操作同一行数据,那么肯定是不可以的.

每次对表中的记录操作时,会保存一个日志(undolog) 里面会记录事务的id号.

如果有多个事务操作时,他们就会根据事务id,找到自己操作的版本记录.

不同的隔离级别在读数据时, 会根据版本链生成一个ReadView(临时读视图) 版本链快照

READ COMMITTED：每次读取数据前都生成一个 ReadView 产生不可重复读 其中数据发生改变,版本链中也会发生修改, 每次读的时候ReadView中的数据就发生改变,所有不可重复读.

REPEATABLE READ：在第一次读取数据时生成一个 ReadView, 之后数据发生改变,版本链发生变化,没有关系,第一次读的时候,已经拍过照了.

mysql锁

​ mysql中的锁,主要是用于对写写操作.

mysql中支持行锁,间隙锁,表锁

行锁: 某个事务对某行记录进行写操作时,会把当前行锁住, 其他事务不能对当前行操作.

​ 粒度最小,并发最高的, 频繁加锁,释放锁.

间隙锁: 在条件范围操作时,会给满足条件的区间数据行加锁

表锁: 当某个事务对某行记录操作时,可以将整个表锁住. innodb用的少, myisam只支持表锁.

**共享锁（S）：**又称读锁.

**排他锁（Ｘ）：**又称写锁。

在查询时,必要的情况下, 也可以为读操作加排他锁 select … * from 表 for update 语句

1.乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的.

​ 没有加锁,可以通过行版本号来区分

2.悲观锁：上面的行锁,间隙锁,表锁等都是悲观锁.

数据库优化

牵扯表的设计,库的设计 不考虑电商级别的 分库 分表 读写分离 集群

考虑范围: 管理系统级别的 表设计 遵循三范式 第1范式 第2范式 第3范式

Sql 优化

正确使用sql

1.正确使用索引 查询条件列,排序列添加索引 项目中是如何使用的 查询条件

2.应改避免索引失效 name like “%张%” 改为全文索引

​ 在 where 子句中 避免 num is null

​ 在 where 子句中使用!=或<>操作符

​ 避免在 where 子句中使用 or 来连接条件

​ 在where num/2=100 使用运算符

​ 在where中使用函数 substring()

​ 3.mysql建议使用主键自增 合理利用索引结构

​ 4.索引不宜建立太多 一般一张表6个左右 可以考虑组合索引 最左前缀原则

5. 状态,类型…一般建议使用数字类型 int

   varchar(变长 6 最大存储6个字符) 代替 char (定长 4 只有两个字的时候,也占4个字符)

   6. 不建议使用 select * -->查询哪些列

   7.一次性不要查询数据过多 分页查询 降低每次查询数据量不要过多 查询条件

​ 8.避免字段值为null null是占空弄间的 可以给默认值’’