MySQL知识考点

MySQL考点：

MySQL存储引擎

MySQL三范式是怎样的？
1.第一范式（1NF）：即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。

2.第二范式（2NF）：是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要我们设计一个主键来实现(这里的主键不包含业务逻辑)。
即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）

3.第三范式（3NF）：必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。很多时候，我们为了满足第三范式往往会把一张表分成多张表。

1.引擎有哪些？需要记忆的列举4个
innoDB存储引擎，myisam存储引擎，memory存储引擎，NDB集群引擎
2.innoDB和MYISAM各自特点及彼此有什么区别？
innoDB特点：innoDB存储引擎给MySQL提供了事务回滚、崩溃和修复能力、多版本并发控制安全、间隙锁、辅助索引、聚簇索引、行级锁、表级锁，另外innodb是唯一一个给MySQL提供了外键约束的引擎。
MYIASM特点：MYISAM是在ISMA存储引擎的基础上发展起来的，MYISAM支持全文索引、压缩存放、空间索引（空间函数）、表级锁、延迟更新索引建。不支持事务回滚，行级锁，且数据库崩溃后不能保证数据完全恢复
innodb和MYISAM区别：1.innodb支持事务回滚，崩溃后修复能力，MYISAM不支持事务；2.innadb支持表级锁及行级锁，MYISAM支持表级锁，没有行级锁；3.innodb给mysql提供了外键约束，myisam没有提供外键约束。

MySQL索引

1.索引的原理：MySQL索引：索引对于查询的速度至关重要，比如普通全盘扫描按顺序来查询数据，那么全盘中有多少条，最坏的情况就是进行多少次I/O，这样就很耗费时间，要是建立树形结构（Btree）的索引，那就很快了，比如log100(10^6) =3次就能查询到，不使用B-tree的话则需要10^3=1000次才能查询到，这就是索引可带来的效果，因此在使用应用程序进行SQL查询速度很慢时，可以考虑建立索引，通过索引快速查找。

1）MySQL官方定义索引是：索引(index)是帮助MySQL高效获取数据的数据结构。提取句子主干，索引本质就是数据结构

,mvcc,索引的各种概念,比如聚集索引,单列索引,主键索引等等

1)主键索引：当把数据表的某一列设为主键时，该列就是主键索引列。
2)主键索引和唯一索引的区别：1.主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。2.主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。3.唯一性索引列允许空值，而主键列不允许为空值。4.主键可以被其他表引用为外键，而唯一索引不能。5.一个表最多只能创建一个主键，但可以创建多个唯一索引。

3)普通索引（NORMAL）：MySQL 中普通的索引类型，在定义索引列的值时可以允许为空值或者重复值。
Create index….这种创建索引方式创建的是普通索引（NORMAL）
4)唯一索引（Unique）：在定义索引列的值可以允许插入为空值，但是索引列的值必须是唯一的
create Unique index …… 这个是唯一索引
create FULLTEXT index …… 全文索引 （就是在一对文字中，通过某个关键字进行检索）
create SPATIAL index …… 空间索引 （就是对空间数据类型中的字段建立索引）
1）创建索引方式：create index +索引名称 on +数据表名称(字段名()) （ps：字段名是字符串类型是可设置长度即取字段的前面几个字符当做索引字段，不是字符串的时候可以不写长度）
create Unique index index_name on table_name(name);
2）Drop index +索引名称 on 表名称 删除索引
3）MySQL没有提供可直接修改索引的命令，只能是先删除，然后根据需要再重新创建索引。
4）查看索引：SHOW INDEX FROM [db_name.]table_name show index from
5)创建数据表 尽量是需要创建索引，没有索引 进行begin事务操作时容易产生死锁；创建索引对于数据查询会很快，但是缺点是插入数据会比较慢，因为插入数据会影响原有的数据结构，即会重新排列数据结构。(即所谓的页分裂，特别是在创建聚集索引时应尽量选择连续有序增长的主键，而不是随机值，否则会造成大量的页分裂)
索引数据类型（即数据结构）有两种：Btree 和 hash
MVCC:多版本控制并发协议multi-version-concurrency-contorl concurrency（并发性），contorl（控制）；

索引分类及各种概念：
1.聚簇索引（聚集索引Clustered Indexes）：表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data就包含了完整的数据记录，这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引，因此这种叶节点就包含了完整的数据记录的主索引就是聚集索引。一个表只能有一个聚集索引，因为我们所建立的聚集索引字段列本身就包含完整的数据，我们无法同时将数据行放在两个不同的位置，所以一个表只能有一个聚集索引（聚集索引优点：就好像在操场上战队，一个院系一个院系的站在一起，这样要找到一个人，就先找到他的院系，然后在他的院系里找到他就行了，而不是把学校里的所有人都遍历一遍）
2.非聚簇索引（二级索引|辅助索引）(non Clustered Indexes)：InnoDB的辅助索引data域存储相应记录主键的值而不是地址；即非聚簇索引只会给出指向数据的指针,然后再通过指针去查询对应的数据。因此可以看出，聚簇索引与非聚集索引相比查找数据速度更快。
3.单列索引：就是一个索引中只包含一个列，但是一个表中可以有多个单列索引
4.复合索引（组合索引）：就是一个索引中包含多个列，但是在数据查询中只有使用了索引中最左边的字段才会用到复合索引，使用复合索引遵循最左前缀原则；例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找。一般在在where限制条件有多个时可以选择复合索引。

MySQL锁

锁的作用：数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

1.MySQL锁有哪些分类？
锁可以分类共享锁和排它锁，也可以叫做读锁和写锁；还有意向共享锁、意向排他锁。

2.MySQL各种锁有哪些特点
1）共享锁：对某一资源加共享锁，自身可以读取该数据，同时别人也可以读取，也可以加共享锁，即对同一资源可以加多个共享锁，但是无法做任何修改，因为读锁和写锁不能同时进行对某一数据进行操作，必须等所有共享锁释放完之后，才能写操作。语法：select * from +表名称 lock in share mode
2）排他锁：对某一资源加排他锁，自身可以对该数据进行增删改查，别人无法对该数据进行任何操作。语法：select * from 表名称 for update，这个语法是加排它锁，而不是简单的更新操作。通常我们使用begin开启一个事务，然后执行增删改的命令语句会自动加排他锁，如果我们想select查询数据也是排他锁，那么在这之前就需要使用前面的语法先加一个排他锁。
3）意向锁：就是当一个事务需要获取资源锁定的时候，刚好该资源已经被排他锁的占用，这个时候该事务可以对需要的获取的资源加一个共享锁。自己需要的是加共享锁，就是加意向共享锁；需要的是加排他锁，就是意向排他锁。意向共享锁和共享锁一样可以多个共存；但是意向排他锁不一样，只能存在一个。另外意向锁是innoDB自动加的，不需要用户干预。

3.MySQL锁的大小（颗粒度）分类有哪些或者是按MySQL innodb引擎锁的模式分类有哪些？
有表级锁、行级锁、页级锁
表级锁：顾名思义，就是事务对资源加锁时，锁定的是整张表。优点：表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。最大的特点就是逻辑非常简单，因为是锁定是整张表，除了自身可以操作以外，别人无法做任何修改操作，因此不会出现死锁的问题，带来的负面影响也最小，获取以及释放锁也很快。缺点：就是事务并发控制性很低，很容易出现资源争夺。一般使用表级锁的是MYISAM、MEMORY、CSV等非事务性存储引擎，innodb也支持表级锁，不过常用的是行级锁。（InnoDB行锁实现方式： InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁）
行级锁：顾名思义，就是事务对资源加锁时，锁定的是数据表的某一行（一般mysql锁锁的是索引）优特点：
行级锁是目前各大数据库管理软件中能实现的最小颗粒度。行级锁可以做到一些事务并发进行，因此可以减小资源争夺的概率。缺点：就是获取以及释放锁需要更多的操作，消耗自然也就更大，因为行级锁也有资源争夺的情况，当两个或者两个以上的进行对统一资源进行争夺并需要相互等待时就会产生死锁，因此行级锁会有死锁的情况发生。
页级锁：页级锁颗粒度大小介于表级锁和行级锁之间，目前页级锁应用不是很常见，特点和行级锁差不多，不过因为颗粒度比行级锁大，因此在事务并发执行比行级锁小一些。也会发生死锁情况
总结：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；页级锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

4.怎么定义乐观锁和悲观锁？
乐观锁：乐观锁就是总是认为不会产生并发问题，每次去获取资源时总会认为不会有其他线程对数据进行修改操作，因此不会上锁，但是更新数据时会判断是否有线程在之前有对数据进行过修改，一般会使用版本或者CAS操作来实现。
悲观锁：悲观锁就是总是认为每次获取数据都会有其他的线程会对数据进行修改，因此都会上锁。当其他数据要访问该数据时只能阻塞挂起，读锁和写锁都是悲观锁。
乐观锁和悲观锁的特点：严格意义上不能说哪个好哪个坏，一般进行写操作非常少的话，可以选择乐观锁，这样还可以减少锁的开销；但是当写操作比较多，且经常会是并发执行，这样的话选择悲观锁好。

5.间隙锁（Gap-lock）：Gap lock间隙锁 是在索引记录之间的间隙加锁，或者是索引记录之前或者之后加锁，不包括索引记录本身。gap lock间隙锁只要是解决可重复读情况的下幻读问题。若是不加间隙锁，在索引记录前后有其他线程插入了数据就会出现幻读。（其实所谓的“间隙锁”就是where后面条件是一个范围条件而不是相等条件时，innodb引擎会给复合条件的索引记录加上间隙锁，间隙锁可以是共享锁或者排他锁）
什么是“间隙”，“间隙”就是对于键值在条件范围内但并存在实际索引记录，就叫做“间隙”（gap）。
example：一张数据表有101条数据。select * from table where id > 100 for update；这个语句检索的是一个范围，innodb引擎会对索引记录101加锁，同时还会对大于101的“间隙加锁”，防止其他线程在索引记录100之后插入数据引起幻读发生。

6.什么是死锁？
死锁（dead lock）：就是有两个或者两个以上的进程相互对某一资源进行争夺并出现相互等待的情况，就会产生死锁，若没有外力作用，将一直耗下去，此时称系统处于死锁状态或者是产生了死锁进程。

MySQL隔离级别

1.MySQL事务（transaction）的基本要素（ACID）有哪些？
1）原子性（Atomicity）：即事务开始后所有操作，要么全部做完，要么全部不做，不能停滞在中途；如果事务执行过程中出现了错误，将会回滚到事务开起之前的状态，就像没进行任何操作一样。
2）一致性（Consistency）：即事务开始前和结束后，数据库的完整性约束没有被破坏。例如A向B转了钱，不可能A扣了钱，B却没有收到。
3）隔离性（Isolation）：同一时间，只能有一个事务对某一资源进行获取，其他事务无法对该资源进行操作。
4）持久性（Durability）：即事务完成后，事务对数据库的更新修改操作将会完整的保存到数据库，不能回滚。
事务的操作方式：
begin
…
commit /rollback

2.MySQL事务有哪些隔离级别？？？
读未提交（read uncommitted）、不可重复读（read committed）、可重复读（repeatable-read）、串行化（serializable）

1）设置事务隔离级别的语法：set session transaction isolation level read uncommitted
2）串行化（serializable）：事务A在执行的时候，事务B只能是阻塞等待，直到事务A执行完毕才能进行，这就是串行化。

3.MySQL事务并发性问题有哪些？
1）脏读：事务B读取了事务A更新后的数据，但是A并未提交（commit），而是执行了回滚（rollback），此时事务B读取到的就是无效数据，就是脏读。
2）不可重复读（read committed）：就是事务B多次对同一数据进行读取，事务A在事务B读取过程中对该数据进行了多次更新并commit提交，导致事务B每次对该数据的读取结果都不一样，这就是不可能重复读。
3)幻读：事务A对数据表中的所有学生的成绩改为了ABCDE等级制度，此时并发执行的事务B在数据表中插入了一条分数成绩，此时事务再次读取该数据表时发现还有一条没有修改，感觉产生了幻读。

设置事务隔离级别语法：set session transaction isolation level +隔离级别
PS：事务A：
begin
start transaction
…
commit /rollback
事务B：
begin
set session transaction isolation level read uncommitted（事务B先设置隔离级别）
…
commit /rollback

4.搞定什么是不可重复读，什么是幻读？两者的区别？
不可重复读：主要说的是多次读取某一条数据，发现该条数据中某列的值发生了改变
幻读：主要说的是多次读取一个范围类的数据（包括直接查询所有数据和做聚合统计），得到的结果不一致。（标准档案一般指记录增多, 记录的减少应该也算是幻读）

MySQL主从配置原理

1.MySQL主从原理：从库生成两个线程，一个I/O线程，一个SQL线程；i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。

MySQL存储程序

1.MySQL存储程序的使用方式有哪些？
存储函数、存储过程、触发器、事件
1）存储函数（stored function）：返回一个计算结果，该结果解压用在表达式里面；存储函数要用create function语句来创建
2）存储过程（stored procedure）：不直接返回一个结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户；存储过程要用create procedure语句来创建
3）触发器（trigger）：与数据表相关联，当那个数据表被INSERT、DELETE或UPDATE语句修改时，触发器将自动执行；触发器要用create trigger语句来创建。
4）事件（event）：根据时间表在预定时刻自动执行。

2.存储过程创建需注意的点：
1）分隔符,由于mysql默认分隔符是 ; 如果我们使用了;表示存储过程已经结束,但是mysql默认语句也是;结束,如果我们想要在存储过程中多加几个sql语句怎么办？我们需要先修改分割符,然后写sql语句和存储过程然后恢复到默认的分隔符。
2）语法：
delimiter $ （重新定义分隔符）
create procedure +func(arg) （创建存储过程并自定义函数名，函数可以传参数）
begin （开始）
select 1; （语句1）
select 2; （语句2）
end$ （结束）
delimiter ; （将分隔符定义回原来）
ps：create procedure func(p_id) begin select ※ from table1 where id = p_id;select ※ from table2 where id = p_id;end$
存储过程如何条用：使用call +func()
PS：mysql存储实际一般很少采用，因为每当数据有发生更新修改，这些编辑好的存储函数也需要去维护，因此在更新迭代上面很是麻烦。

3.触发器（trigger）创建：
delimiter $
create trigger t_studentcourse before insert on studentcourse for each row begin set studentcourse.scsore = studentcourse.scsore + 10;
end $
delimiter ;

4.事件（event）创建：
delimiter $
create event e_studentcourse on schedule every 1 second do UPDATE studentcourse set studentcourse.scsore = studentcourse.scsore + 10; (every 1 second 代表没隔1秒执行一次后面的语句)
end$
delimiter ;
创建好之后，事件就已开始自动运行
禁止事件：alter event +事件名称 disable
激活事件：alter event +事件名称 enable

MVCC

MVCC（Multi Version Concurrency Control）多版本并发性控制系统。
MVCC好处就是：读不加锁，读写不冲突（所谓的读不加锁，并不是所有情况，串行化serilizable隔离级别就会加读锁，因为串行化同时是能有一个事务执行，其他事务只能阻塞等待。）

1.在MVCC并发控制中读可以分为哪两类？
快照读和当前读
什么是快照读和当前读？
1）快照读（snapshot read）:读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
2）当前读（current read）：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。一般的加共享锁的读和写操作的读（进行写操作时要先读取到数据才能写）都是当前读。
总结：简单来说，事务begin开始后，直接使用select ※ from table的不加锁的查询一般是快照读，直接的更新/插入/删除或者加select ※ from table where ? for update写锁或者select ※ from table where ? lock in share mode 共享锁是当前读（即所有的当前读都会加锁）。

2.实际应用
1）给出一条语句，分析会加什么锁？
select * from t1 where id = 10;
delete from t1 where id = 10;
一般考虑的是在MVCC并发控制下，是会加行锁还是表锁，在哪些字段数据记录加锁？
组合一：id主键+RC 锁的是满足记录的主键聚簇索引id=10的行锁
组合二：id唯一索引+RC 锁唯一索引id=10以及id=10所对应的主键聚簇索引记录 行锁
组合三：id非唯一索引+RC 锁的是所有的索引id=10以及所对应的主键聚簇索引记录行 行锁
组合四：id无索引+RC 走聚簇索引，而且是聚簇索引上的所有记录都会被锁上，不过现在基于MySQL的优化，加锁后系统会自动释放掉满足记录之外的其他锁，只锁上满足记录的行。
组合五：id主键+RR 同组合一
组合六：id唯一索引+RR 同组合二
组合七：id非唯一索引+RR 通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
组合八：id无索引+RR 与组合四类似，聚簇索引上的所有记录都会被锁上，同时每条记录的间隙会加上GAP锁。