Mysql学习

Mysql基础：

范式：

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项

第二范式：1NF基础下，

                1.表必须有一个主键；

                2.没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式：2NF基础下，另外非主键列必须直接依赖于主键，不能存在传递依赖。（传递依赖）

Mysql语言类型：

DDL：数据定义语言（定义数据库，表结构）

DQL：数据查询语言（查询数据）

DML：数据操作语言（记录增删改查操作）

DCL：数据控制语言（控制数据库权限和事务）

MySQL 支持哪些存储引擎?

        MySQL 支持多种存储引擎,比如 InnoDB,MyISAM,Memory,Archive 。

        InnoDB是 MySQL 的默认存储引擎。

MyISAM 和 InnoDB 的区别有哪些：

        ①InnoDB 支持事务，MyISAM 不支持

        ②InnoDB 支持外键，而 MyISAM 不支持

        ③InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；

        MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

        ④Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；

        ⑤InnoDB 不保存表的具体行数，MyISAM 用一个变量保存了整个表的行数。

        ⑥MyISAM 采用表级锁(table-level locking)

        ⑦InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

MySQL数据类型：

        其中包括整数类型、字符串型，日期型

varchar和char的区别

        varchar表示变长，char表示长度固定

        存储容量不同，对于 char 来说，最多能存放的字符个数为255。对于 varchar，最多能存放的字符个数是 65532。

        存储速度不同，char 长度固定，存储速度会比 varchar 快一些，但在空间上会占用额外的空间，属于一种空间换时间的策略。而 varchar 空间利用率会高些，但存储速度慢，属于一种时间换空间的策略。

MySQL中 in 和 exists 区别

        如果查询的两个表大小相当，那么用in和exists差别不大。

        如果两个表中一个较小，一个是较大，则子查询表大的用exists，子查询表小的用in。

not in 和not exists：

        如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；

        not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

为什么 VarChar 建议不要超过255

当定义varchar长度小于等于255时，长度标识位需要一个字节

当大于255时，长度标识位需要两个字节，并且建立的索引也会失效。

varchar(10)和int(10)代表什么含义

varchar 的10代表了申请的空间长度，也是可以存储的数据的最大长度。

int 的10只是代表了展示的长度，不足10位以0填充。

int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的，只是在展示时按照长度展示。

drop、delete和truncate的区别

	drop	delete	truncate
速度	逐行删除	慢	较快
类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	删除整个表，数据行、索引都会被删除	表结构还在，删除表的一部分或全部数据	表结构还在，删除表的全部数据

总结：删除整个表，使用drop，删除表的部分数据使用delete，保留表结构删除表的全部数据使用truncate

事务：

ACID（原子性，一致性，隔离性，持续性）

        原子性：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

        一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态

        隔离性：一个事务的执行不能受其它事务干扰

        持续性：指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的

事务的处理方式：

        ①begin（开始）②rollback（回滚）③commit（确认）

        ①set（禁止自动提交）

MySQL 的四种隔离级别：

	脏读	不可重复读	幻读
读取未提交	√	√	√
读取提交（Orale默认）	×	√	√
可重读（mysql默认）	×	×	√
可串行化	×	×	×

读取未提交内容：

        在该隔离级别，所有事务都可以看到其他未提交事务的执行结果

读取提交内容：

        一个事务只能看见已经提交事务所做的改变

可重读：

        确保同一事务的多个实例在并发读取数据时，会看到同样的数据行

可串行化：

        通过强制事务排序，使之不可能相互冲突，从而解决幻读问题

脏读，幻读，不可重复读

        脏读：事务A读取了事务B更新的数据，然后B回滚操作，则A读取到的数据是脏数据

        不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。

        幻读：老师A在数据库中定义等级，老师B在数据库中更新了成绩

不可重复读侧重于修改，幻读侧重于新增或删除（多了或少量行），脏读是一个事务回滚影响另外一个事务。

事务的实现原理

        事务是基于重做日志文件和回滚日志实现的

        每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性

什么是 MVCC？

        MVCC（多版本并发控制）

        MVCC的实现，是通过保存数据在某个时间点的快照来实现的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

MVCC的实现：

通过添加三个函数：ROW ID ，事务ID，回滚指针

        ROW ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树

        事务 ID：记录最后一次修改该记录的事务 ID

        回滚指针：指向这条记录的上一个版本（undo log）

实现：InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本（上一个版本）。这个历史版本存放在undo log中

        如果要执行更新操作，会将原记录放入undo log中，并通过隐藏的回滚指针指向undo log中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本

锁：

        多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况

        若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性

按照锁的粒度分数据库锁有哪些？

行级锁

        锁定粒度最小。开销大，加锁慢。会出现死锁。发生锁冲突的概率最低，并发度也最高。

                行级锁分为共享锁和排他锁

表级锁

        锁定粒度最大。开销小，加锁快。不会出现死锁。发出锁冲突的概率最高，并发度最低。

                表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

页级锁

        锁定粒度介于行级锁和表级锁中间的一种锁

                开销和加锁时间界于表锁和行锁之间；会出现死锁，并发度一般

从锁的类别上分MySQL都有哪些锁呢？

                从锁的类别上来讲，有共享锁和排他锁。

        共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁，可以同时加上多个。

        排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁，只可以加一个。

                       共享锁不堵塞，排他锁堵塞其他锁

数据库的乐观锁和悲观锁是什么？怎么实现的？

        悲观锁：对数据修改保存保守态度，锁定状态，需要一致性锁读

                        实现方式：使用数据库中的锁机制

                                悲观锁适用于写

        乐观锁：在修改数据的时候把事务锁起来，通过version的方式来进行锁定

                        实现方式：乐一般会使用版本号机制或CAS算法实现。

                                乐观锁适用于读，这样可以省去了锁的开销，加大了系统的整个吞吐量

死锁：

        ①互斥②请求保持③不剥夺④环路等待

常见的解决死锁的方法

        1、访问顺序

        2、一次事务中给足资源

隔离级别与锁的关系：

        未提交读级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

        提交读级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁

        可重复度级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁

        可串行化的隔离级别最高，该级别锁定整个范围的键，并一直持有锁，直到事务完成

MyISAM和InnoDB存储引擎使用的锁：

        MyISAM采用表级锁

        InnoDB支持行级锁和表级锁，默认为行级锁

索引

        索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针

        索引是一种数据结构（通常为B+数）

索引有哪些优缺点

        索引的优点

                可以大大加快数据的检索速度

        索引的缺点

               1.创建索引和维护索引要耗费时间

               2.索引需要占物理空间

MySQL有哪几种索引类型

从存储结构上来划分：

        B-Tree索引：

        B-TREE索引就是将索引值存入一个二叉树中，每次查询都是从树的入口root开始，依次遍历node，获取节点。

        Hash索引：

        HASH的唯一，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。

        但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

        全文索引：目前只有MyISAM引擎支持

        R-Tree索引：RTREE在MySQL很少使用，仅支持geometry数据类型

从索引的功能来分：普通索引，唯一索引，复合索引

普通索引：

唯一索引：普通索引+列值唯一（有null）

复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

主键索引：普通索引+列值唯一（无null）+表唯一

组合索引：多值组成索引

全文索引：对文本分词搜索

数据的物理顺序与键值的逻辑：聚集索引和非聚集索引

       聚集索引：并不是一种单独的索引类型，而是一种数据存储方式。

        非聚集索引：不是聚集索引，就是非聚集索引

聚簇索引与非聚簇索引：

        在InnoDB 里，B+Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据

        而索引B+Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引

聚簇索引与非聚簇索引的区别：

        非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键

         而聚集索引的查询只会查一次，而非聚集索引需要回表查询多次

非聚簇索引一定会回表查询吗：

        不一定，查询语句所要求的字段是否全部命中了索引，

        如果全部命中了索引，那么就不必再进行回表查询。

        一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"

MySQL的最左前缀原则

        最左前缀原则就是最左优先，从左到右建立搜索树

eg：如果是（1.2.3），则为1.2.3；如果为（2.3），此时无1，则不知道2.3（必须知道1）

        如果是（1.3），也因为失去2，而返回1的数据，再去索引3

前缀索引

        索引的字段非常长，占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。（类似于*，通配符）

索引结构为什么默认使用B+Tree：

相比于B-tree：

①B+树的磁盘读写代价更低：

        B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小

        如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

②由于B+树的数据都存储在叶子结点中，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，

        B+树分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可

        B树需要进行一次中序遍历按序来扫

Hash：

        ①虽然可以快速定位，但是没有顺序，IO复杂度高；

        ②因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；

        ③如果有大量重复键值得情况下，哈希索引的效率会很低，

        ④基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；

        ⑤适合等值查询，如=、in()、<=>，不支持范围查询 ；

        ⑥因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；

二叉树：

        树的高度不均匀，查找效率跟树的高度，并且输入/输出代价高。

红黑树：

        树的高度随着数据量增加而增加，输入/输出代价高

索引创建：

CREATE TABLE XXX (
	id INT auto_increment PRIMARY KEY,
	STU_NAME VARCHAR (16),
	STU_GROUP VARCHAR (16),
	ID VARCHAR (18),
	information text,
	KEY name (STU_NAME, STU_GROUP),
	FULLTEXT KEY (information),
	UNIQUE KEY (ID)
);

数据库优化：

        1.系统的吞吐量瓶颈往往出现在数据库的访问速度上

        2.随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢

        3.数据是存放在磁盘上的，读写速度无法和内存相比

数据库结构优化

        1.将字段很多的表分解成多个表

        2.增加中间表

mysql日志：

        ①错误日志

        ②二进制文件（DDL,DML）

        ③查询日志（DQL）

        ④慢查询日志（重要！！！一定要开）

数据库备份：

        ①逻辑备份：

                利：协同，方便，锁定

                弊：慢，耗时，锁定（影响用户）

        ②物理备份：

                innoDB无法使用

        ③双机热备份：

                问题：数据量大

不停机备份：

        逻辑备份+双机热备份

Mysql的实现：