MYSQL、存储引擎、Innodb和Myisam区别

0.简述

         sql（structure query language）结构化查询语言，定义和操作数据库，维护数据的完整性和安全性

1.优点

        1）操作简单

        2）绝大多数数据库都支持sql

        3）高度非过程化，大部分操作由dbms完成

2.分类

1	ddl数据库定义语言，用来操作数据库、表、列等
2	dml数据库操作语言，用来操作数据库中的数据
3	dcl数据库控制语言，用来操作访问权限和安全级别
4	dql数据库查询语言，用来查询数据

3.三大范式

第一范式	每一列都是独立存在的，不可再分割
第二范式	每一列都依赖于主键，不能只依赖于一部分
第三范式	每一列都必须与主键直接相关，不能间接相关

4.数据类型

        整数、浮点数、定点数、日期、时间、字符串、二进制

        char的字节数比verchar少一，可变字符串与不可变字符串的区别

5.库基本操作

•        create database 数据库名称
•        drop database 数据库名称
•        alter database 数据库名称 character set 字符集类型
•        use 数据库名称
•        select database（）

6.表基本操作

•       create table 表名（

                 字段名   类型，

                 字段名   类型

             ）

•        show tables
•        desc 表名
•        alter table 表名 rename to 新表名
•        alter table 表名 add 列名 列类型
•        alter table 表名 drop 列名
•        drop table 表名

表约束

•        primary key
•        foreign key
•        not null
•        unique
•        default

crud

•        insert into 表名（字段1，字段2，字段3…）values（值1，值2，值3…）
•        update 表名 set 字段名=值
•        delete from 表名

                     delete时再次插入数据自增字段会按照之前的最大值加一

•        truncate 删除数据，所有数据，保留表结构，速度比delete块
•        select * from 表名

聚合函数

1	SUM（字段名）计算指定列的数值和，如果不为数值的话默认为0
2	MAX（）计算指定列的最大值，若指定列为字符串时默认为字符串排序
3	MIN（）计算指定列的最小值，如果是字符串的话就会按照字符串排序
4	COUNT（）返回列的行数或统计该列不为0的个数
5	AVG（）指定列的平均值，若该列不为数值类型的话返回0
6	时间函数返回年月日等
7	字符串函数字符串连接，长度等
8	数学函数，绝对值，向上取整，向下取整等

条件查询

1	where语句，使用关系运算符限制返回结果
2	in ，判断值是否在集合内
3	between and 判断值是否在指定范围内
4	is null 、is not null空值查询
5	and关键字，可以连接多个限制条件
6	or关键字，只要满足其一就可以
7	like关键字，使用like关键字可以判断两个字符串是否匹配，%一对多，可以有零个，也可以有多个，_一对一，必须存在，也与个数有关
8	limit关键字，限制读取个数，跟值为一个时默认从零开始，往后读取跟值的个数，跟值为两个时，第一个为开始位置，第二个为读取个数
9	group by 按照某一列进行分组，分为不同的小表
10	order by 排序，按照列的大小进行排序（asc，desc）正序，倒序
11	exists关键字，与where连用，true执行外部查询，false不执行外部查询
12	all关键字，与where连用，当满足所有条件时执行外部查询

7.索引

          1）介绍

           索引是帮助数据库高效获取数据的数据结构，可以有效加快查询速度，索引一般存储在磁盘的文件中，有可能与数据文件存放在一起，有的独立存在，与存储引擎有关

          2）索引结构

            聚集索引、覆盖索引、前缀索引、组合索引、唯一索引，多用B+树结构组织（多路搜索树）

          3）优缺点

优点	有效的检索数据，减少磁盘io成本，降低CPU消耗。索引会自动排序，单列排序或者组合排序（与索引类型有关），有效提高order by速度
缺点	占用磁盘空间，降低数据更新表速度，使用增删改后索引会重新保存或更新索引文件

          4）索引类型

主键索引	索引列的值唯一，不能为空
普通索引	索引列可以存放重复值和空值
全文索引	只能在char verchar。 text列建立，供模糊查询使用，使用btree，一般有两层，第一层关键字，第二层指向文件地址或者数据行
空间索引	根据空间对象的位置，形状建立的索引结构
前缀索引	建立在char verchar   text类型的列上的索引，可以指定位数，不能指定类型
单列索引，组合索引	最左匹配原则

          5）索引的数据结构

hash表	键值对存储，键存放索引，值存放数据列或者磁盘地址，等值查询效率高，匹配查询还是全表扫描
二叉树	折半查找，设计不好的情况下退化为链表
平衡二叉树	左右子树维持在相差1的前提下，通过左旋右旋维持平衡，最接近二分查找的数据结构，树高依旧影响效率，依旧频繁的发生io操作，不支持范围查询
b树	（多叉平衡查找树），一次io读取一页数据，包含本业数据以及其它指针，指向其它叶子结点，缺点：当本页包含数据过大时存储的叶子结点指针依旧很少，不支持范围查询，依旧要多次遍历
b+树	b树结构升级版，只有叶子结点存储数据，非叶子结点存储索引，保证了行数据或者磁盘地址都在同一层，范围查询时会根据起始点或结束点向后向前查询，有效减少磁盘io

        6）拓展

                1）主键索引（聚集索引）

1	每个表都会有一个聚集索引，叶子结点存储的是行数据，一般聚集索引就是主键索引，当没有创建主键索引时就会自动创建一个聚集索引
2	默认为主键作为索引列
3	没有主键时会自动的选取非空且不重复的列作为聚集索引列
4	以上条件都不满足时会自动创建一个六字节长度的隐式字段构建聚簇索引
5	除了聚簇索引外的其他索引都为辅助索引，辅助索引叶子结点存储的都为主键值，获得主键值后再去聚簇索引中查找数据
6	主键索引的叶子结点存储数据行，辅助索引存储主键值，叶子结点顺序是由小到大排列
其他	由辅助索引到主键索引的过程称为回表

                 2）组合索引

                       由多个字段组成，遵循最左匹配原则，范围递增，一般优化使用，

                 3）覆盖索引：

                        不属于任何索引结构，优化手段，指的是叶子结点覆盖着所需要的数据，不用继续

                 回表查询

                  4）联合索引

                        一种优化手段，将频繁使用到的列、区分度高的列放在首位，提高首位利用率，一

                  般用于多个单列索引的合并

8.存储引擎

Myisam	Myisam的索引文件和数据文件是分开的，使用b+树存储结构时，叶子结点存储的是索引列和磁盘地址
	索引存储在.myi文件中，数据文件存储在.myd文件中
	辅助索引，在myisam存储引擎中，辅助索引与主键索引是一样的，唯一的区别就是主键索引唯一，辅助索引键可以重复
innodb	索引与数据存放在同一个文件中，.ibd文件中
	innodb是一个完全支持事务的存储引擎，并且支持行锁
	innodb有多个内存块，合并成为大的内存池，负责维护所有进程，所有线程要访问的多个内存结构

         1.Myisam与InnoDB的区别

1	InnoDB支持事务，Myisam不支持事务
2	InnoDB支持外键，Myisam不支持外键，含外键的innodb转化myisam会失败
3	InnoDB是聚集索引，辅助索引存储的是主键索引的键，再根据键回表查询主键索引获取数据，主键不易过大，否则会导致其他索引占用空间变大、Myisam是非聚集索引，主键索引与辅助索引存储的都是数据文件的地址
4	InnoDB使用count时会进行全表扫描，没有存储列数（因为支持事务，不同事物之间会有差别，因此没有维护数量，为了节约资源，innodb会找到一个尽可能小的索引进行count，如果对实时性没有太高的要求的话可以建立一个专门维护表数量的表，在对其他表进行增删操作时修改此表），Myisam存储了列数
5	myisam表可以在压缩后查询
6	innodb支持行锁（默认）、表锁，myisam仅支持表锁，（innodb的行锁是建立在索引上的，如果没有命中索引的话依旧是表锁），在并发操作时myisam的性能没有innodb好
7	innodb必须有聚集索引，myisam可以没有
8	文件差别，myisam有索引文件和数据文件，innodb只有一个文件，包含了索引文件和数据文件

                图中后台线程负责刷新内存池中的数据，保证内存池和磁盘数据的一致性，还负责将已

        经修改的脏数据刷新到磁盘中

                innodb是多线程模型，一般有以下几种线程构成

mast thread	核心后台线程，负责刷新数据（内存池中的数据，脏数据，合并插入缓冲，undo页的回收）
io thread	负责处理io请求，一般有4个read，4个write，1个insert buffer，1个log
purge thread	负责undo页的回收
page cleaner thread	为了处理刷新操作

        2.缓冲池

                innodb读取单位是页，每次获取数据都需要从磁盘获取，为了优化磁盘加载速度慢的缺

        点，引入了缓冲池，从内存中选取一部分空间作为缓冲池，用来弥补磁盘速度不足的缺点，

        每次加载新页都会将新页放入缓冲池中，下次读取时首先查看该页是否在缓冲池中，没有则

        去磁盘文件加载，缓冲池可缓冲的类型（索引页、数据页、undo页、插入缓冲、自适应哈

        希、数据字典、锁信息）

                innodb从1.0开始加入多个缓冲池实例，通过不同页的哈希值去分配不同的缓冲池，减少

        内部资源的竞争，增加数据库的并发能力

                数据库缓冲池管理算法LRU算法，使用最频繁的页放到lru列表的最前端，反之放在最后

        段，一般新加进来的页会放在5/8的地方，后面随着页的频繁访问会将该页置于lru列表首位，

        一般为了减少多余的系统资源浪费，3/8的页不会根据访问的频繁而互相的变动，一般新页进

        来时会从free列表申请空闲缓冲池，申请完毕之后加入到lru列表中，对于修改后的页将其加入

        到fluse列表中，按照一定的规则去刷新到磁盘中去

        3.重做日志缓冲

                innodb首先将重做日志信息放到重做日志缓冲中去，然后经过特定的事件约束

        去刷新到日志文件中，（master thread线程每秒刷新、提交事务后，缓存空间不足1\2）

        4.checkpoint技术

                减少日志恢复时间，缓冲池不够用时刷新到磁盘中，重做日志不可用时刷新脏页

        5.master thread工作方式

                1.会不断的进入循环，主循环，后台循环，刷新循环，暂停循环。

                2.关键特性，insert buffer、两次写、自适应哈希、异步io、刷新邻接页 

        6.insert buffer：

                进行插入操作时不直接进行插入，首先判断页是否在缓冲池中，若存在直接插

         入，不存在则放入到insert buffer中，按照一定的逻辑去刷入到磁盘中，多个insert

         操作合成了一个

        7.两次写

                页面刷回磁盘时不直接的进行刷新，首先进入double Write buffer，此时double write

        buffer会将数据分为两次，每次1m刷到共享double write中去，写完之后直接异步同时将

        double write buffer写入磁盘中去

        8.自适应哈希

                当innodb如果发现有任何可以优化的哈希索引时，就建立哈希索引，

        9.io异步

                多个io线程同时运行

        10.刷新邻接页

                当一个页被加载时，其相邻的页也有很大几率被访问到，也会被加载进来                

9.隔离级别

      数据库的事务四大特征

隔离性	多适用于并发场景，指的是多个用户操作同一张表或者同一条数据时，不会影响他人的操作，相当于拷贝了一份数据进行操作
原子性	指的是每个事务内无论对数据进行了多少crud操作，到最后提交时，要么都成功，要么都失败
一致性	指的是事务前与事务后必须保持一致的状态，ab转账问题
持久性	事务一旦被提交，就会被永久的保存在数据库中

      事务隔离性

                当多个用户并发访问数据时，一个事务不应该被另一个事务影响，事 务之间是互相隔离

        的，不考虑隔离的话，会出现以下问题：

脏读	指的是一个事务读取到另一个事务未提交的数据
不可重复读	指的是一个事务内部读取同一条数据时，两次读取的结果不一致，一般                                         采用行锁
幻读	aba问题，一般采用表锁

                    事务通过原子性、隔离性、持久性来维持一致性

        隔离级别

                mysql默认为rr隔离级别，避免快照读和当前读同时存在就能避免大部分问题

读未提交	不加锁，会读取到不一样的数据，事务内部的修改也会读取到
读已提交	可防止脏读，一个事务必须等待另一个事务提交后才能读取数据
可重复读	事务开启时加锁，不允许其他事务进入，等待提交后才允许，mvcc
串行化	可避免脏读，幻读，不可重复读

9.spring事务的传播行为

        事务是在方法中怎样传播的，父事务回滚会对子事务有何影响，都是事务的传播机制管理的

级别	简介
REQUIRED	如果有事务则加入事务，否则新建一个事务
REQUIRES_NEW	无论是否有事务存在，都会新建一个事务，将原方法挂起，新的方法执行完毕后，继续执行老的事务
SUPPORTS	如果其他Bean调用该方法时声明了事务，则使用该事务，如果没有声明就不开启事务
NOT_SUPPORTED	spring不为该方法开启事务，无事务状态运行，每条语句单独执行
MANDATORY	必须在已有的事务中运行，否则报错
NEVER	必须在无事务的状态下执行，否则报错
NESTED	如果有事务则新建一个嵌套事务，没有事务则新建一个事务运行，父事务回滚影响嵌套子事务