首页  思维导图  详情

MySQL知识点整理

2020-04-08 19:25:03   34  举报





AI智能生成

为你推荐

查看更多



MySQL知识点个人整理

作者其他创作

大纲/内容

DB、DBS和DBMS的区别是什么

DBMS

DBS

注意

设计数据表的原则

“三少一多”原则

1.数据表的个数越少越好

2.数据表中的字段个数越少越好

3.数据表中联合主键的字段个数越少越好

4.使用主键和外键越多越好

常见约束

约束：主键、外键、唯一性、NOT NULL、 DEFAULT、CHECK约束等。

范式设计 : 3NF

数据库的设计范式都有哪些？

目前关系型数据库一共有6种范式

从1NF到3NF

1NF指的是数据库表中的任何属性都是原子性的，不可再分.

2NF指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。

3NF在满足2NF的同时，对任何非主属性都不传递依赖于候选键。

MySQL知识点整理

功能分类

DDL

数据定义语言

修改表结构

添加字段

ALTER TABLE player ADD (age int(11));

修改字段名，将age字段改成player_age

ALTER TABLE player RENAME COLUMN age to player_age

修改字段的数据类型

删除字段

ALTER TABLE student DROP COLUMN player_age;

DML

DCL

DQL

视图

创建视图:CREATE VIEW

存储过程

如何实现

事务

特性:ACID

原子性（Atomicity）

一致性（Consistency）

就是隔离性（Isolation）

持久性（Durability）

控制语句

START TRANSACTION或者 BEGIN

作用是显式开启一个事务

COMMIT

提交事务。当提交事务后，对数据库的修改是永久性的。

ROLLBACK

意为回滚事务。

SAVEPOINT

在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。

RELEASE SAVEPOINT

删除某个保存点。

SET TRANSACTION

设置事务的隔离级别

自动提交

mysql> set autocommit =0; //关闭⾃动提交

并发处理

三种异常

脏读（Dirty Read）

读到了其他事务还没有提交的数据

不可重复读（Nnrepeatable Read）

对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除

幻读（Phantom Read）

事务A根据条件查询得到了N条数据，但此时事务B更改或者增加了M条符合事务A查询条件的数据，这样当事务A再次进行查询的时候发现会有N+M条数据，产生了幻读

隔离级别

索引

逻辑分类

MySQL

普通索引、唯一索引和主键索引

MySQL自带的全文索引只支持英文。

全文索引

ES(ElasticSearch)和Solr

物理分类

聚集索引

非聚集索引

区别

聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索引不会影响数据表的物理存储顺序。

一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个索引目录提供数据检索。

使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

其他分类

单一索引

联合索引

最左匹配原则

索引的原理

B+tree

为什么

二叉书O(n)

平衡⼆叉搜索树（AVL树）O(log2n)

什么情况使用索引?

1.字段的数值有唯⼀性的限制，⽐如⽤⼾名

2.频繁作为WHERE查询条件的字段，尤其在数据表⼤的情况下

3.需要经常GROUP BY和ORDER BY的列

4.UPDATE、DELETE的WHERE条件列，⼀般也需要创建索引

5.DISTINCT字段需要创建索引

什么时候不需要创建索引

WHERE条件（包括GROUP BY、ORDER BY）⾥⽤不到的字段

第⼆种情况是，表记录太少

第三种情况是，字段中如果有⼤量重复数据，也不⽤创建索引，⽐如性别字段。

最后⼀种情况是，频繁更新的字段不⼀定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从⽽影响效率。

什么情况下索引失效

1.如果索引进⾏了表达式计算，则会失效

2.如果对索引使⽤函数，也会造成失效

3.在WHERE⼦句中，如果在OR前的条件列进⾏了索引，⽽在OR后的条件列没有进⾏索引，那么索引会失效。

4.当我们使⽤LIKE进⾏模糊查询的时候，后⾯不能是%

5.索引列为NULL进⾏判断的时候也会失效。

6.我们在使⽤联合索引的时候要注注意最左原则

为什么没有理想的索引？

窄索引

包含索引列数为1或2

宽索引

包含的索引列数⼤于2

可以防止回表

过滤因子

where后的条件

针对SQL查询的理想索引设计：三星索引

1. 在WHERE条件语句中，找到所有等值谓词中的条件列，将它们作为索引⽚中的开始列；

2. 将 GROUP BY和ORDER BY中的列加⼊到索引中；

3. 将SELECT字段中剩余的列加⼊到索引中。

为什么很难存在理想的的索引设计

2. 增加了索引维护的成本。

你能看到针对⼀条SQL查询来说，三星索引是个理想的⽅式，但实际运⾏起来我们要考虑更多维护的成本，在索引效率和索引维护之间进⾏权衡。

设置合理索引

⼀张表的索引个数不宜过多

在索引⽚中，我们也需要控制索引列的数量

单列索引和复合索引的⻓度也需要控制

缓冲池

数据库缓冲池

如何读取数据

执⾏SQL语句的时候更新了缓存池中的数据，那么这些数据会⻢上同步到磁盘上吗？

做checkpoint的机制将数据回写到磁盘

脏⻚（dirty page）

指的是缓冲池中被修改过的⻚，与磁盘上的数据⻚不⼀致。

查看缓冲池的⼤⼩

show variables like 'innodb_buffer_pool_size'

设置缓冲池大小 128MB

set global innodb_buffer_pool_size = 134217729

查看缓冲池个数

mysql > show variables like 'innodb_buffer_pool_instances'

开启多个缓冲池，你⾸先需要将innodb_buffer_pool_size参数设置为⼤于等于1GB

然后再针对innodb_buffer_pool_instances参数进⾏修改

数据⻚加载的三种⽅式

1. 内存读取

如果该数据存在于内存中，基本上执⾏时间在1ms左右，效率还是很⾼的。

2.随机读取

如果数据没有在内存中，就需要在磁盘上对该⻚进⾏查找，整体时间预估在10ms左右

3. 顺序读取

通过last_query_cost统计SQL语句的查询成本

锁

按照锁粒度进⾏划分

分别为⾏锁、⻚锁和表锁

从数据库管理的⻆度对锁进⾏划分

共享锁

排它锁

其他事务⽆法对已锁定的数据进⾏查询或修改

意向锁（Intent Lock）

从程序员的⻆度对进⾏划分

乐观锁

通过程序来实现

我们可以采⽤版本号机制或者时间戳机制实现

悲观锁

上面那些数据库的锁

防止死锁

采用乐观锁

MVCC

什么是MVCC

MVCC是通过数据⾏的多个版本管理来实现数据库的并发控制

使用MVCC的好处

读写互相不阻塞

降低了死锁概率

解决了一致性读写问题

快照读

读取的是快照数据(历史版本)

当前读

InnoDB中的MVCC

事务版本号

行记录的隐藏列

Undo Log

InnoDB将我们的行记录快照保存在了Undo Log里

Read View是如何工作的

Read View的结构

1. trx_ids，系统当前正在活跃的事务ID集合。

2. low_limit_id，活跃的事务中最⼤的事务ID。

3. up_limit_id，活跃的事务中最⼩的事务ID。

4. creator_trx_id，创建这个Read View的事务ID。

Read View的原则

trx_id < 活跃的最⼩事务ID（up_limit_id）

这个⾏记录在这些活跃的事务创建之前就已经提交了，那么这个⾏记录对该事务是可⻅的

trx_id > 活跃的最⼤事务ID（low_limit_id）

该⾏记录在这些活跃的事务创建之后才创建，那么这个⾏记录对当前事务不可⻅

up_limit_id < trx_id < low_limit_id

查询数据的步骤

1. ⾸先获取事务⾃⼰的版本号，也就是事务ID；

2. 获取Read View；

3. 查询得到的数据，然后与Read View中的事务版本号进⾏⽐较；

4. 如果不符合ReadView规则，就需要从Undo Log中获取历史快照；

5. 最后返回符合规则的数据

InnoDB是如何解决幻读的

在可重复读的情况下，InnoDB可以通过Next-Key锁+MVCC来解决幻读问题。

在读已提交的情况下，即使采⽤了MVCC⽅式也会出现幻读。

InnoDB 三种⾏锁的⽅式

1. 记录锁：针对单个⾏记录添加锁。

2. 间隙锁（Gap Locking）：可以帮我们锁住⼀个范围（索引之间的空隙），但不包括记录本⾝。采⽤间隙锁的⽅式可以防⽌幻读情况的产⽣。

3. Next-Key锁：帮我们锁住⼀个范围，同时锁定记录本⾝，相当于间隙锁+记录锁，可以解决幻读的问题。

查询优化器

SQL语句执行

SQL和NoSQL

SQL

如何执行

架构

Client

应用程序

Server

mysqld

三层结构

连接层

SQL层

SQL层结构

查询缓存

解析器

优化器

执行器

到SQL语句在MySQL中的流程是：SQL语句→缓存查询→解析器→优化器→执行器

存储引擎层

存储引擎层结构

磁盘

内存

网络

插件式存储引擎

InnoDB

MyISAM

Memory

NDB