MySQL进阶，秒变大神（二）

最近闲来无事，整理了一波Mysql的资料，欢迎大家来围观！！！

白嫖资料

8、事务是如何通过日志来实现的？

InnoDB中，事务日志通过重做（redo）日志文件和InnoDB存储引擎的日志缓冲来实现。当开始一个事务时,会记录该事务的一个LSN（日志序列号），当事务执行时，会往InnoDB的日志缓冲里插入事务日志，当事务提交时，必须将innoDB存储引擎的日志缓冲写入磁盘。也就是在写数据前，需要先写日志。这种方式称为预写日志方式。
　InnoDB通过预写日志的方式来保证事务的完整性。这意味着磁盘上存储的数据页和内存缓冲池中的数据页是不同步的，对于内存缓冲池中页的修改，先是写入重做日志文件，然后再写入磁盘，因此是一种异步的方式。

事务有时还需要撤销，这就需要undo。对数据库进行修改时，数据库不但会产生redo，而且会产生一定量的undo，如果你执行的事务或语句由于某种原因失败了，或者如果你用一条rollback语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

9、数据库的乐观锁和悲观锁是什么？

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽掉一切可能违反数据完整性的操作，在读取的时候就对数据进行加锁， 在该用户读取数据的期间，其他任何用户都不能来修改该数据，但是其他用户是可以读取该数据的， 只有当自己读取完毕才释放锁。

乐观锁：假定不会发生并发冲突，只在提交的时候检查是否发生并发冲突。

事务和锁的存在都是为了更好地解决并发访问造成的数据不一致性问题。乐观锁和悲观锁都是为了解决并发控制问题，乐观锁可以看做一种在最后提交时检测冲突的手段，而悲观锁是一种避免冲突的手段。

（1）乐观锁：假设不会发生并发冲突，只在提交的时候检查是否发生并发冲突。可以使用版本号机制和CAS算法实现。

版本号机制：一般在数据表中加一个数据版本号version字段，表示数据被修改的次数，当数据被修改时version值加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若当前读取到的version值与第一次读取到的数据库version值相等时才更新，否则重试更新操作，直到更新成功。

例子：
白嫖资料

假设数据库中帐户信息表中有一个 version 字段，当前值为 1 ；而当前帐户余额字段（ balance ）为 100 。

操作员A此时将其读出（ version=1 ），并从其帐户余额中扣除 50（100-50 =50）；
在操作员A操作的过程中，操作员B也读入此用户信息（ version=1 ），并从其帐户余额中扣除20 （100-20=80 ）。
操作员A完成了修改工作，将数据版本号加一（ version=2 ），连同帐户扣除后余额（ balance=50 ），提交至数据库更新，此时由于提交数据版本大于数据库记录当前版本，数据被更新，数据库记录 version 更新为 2 ；
操作员B完成了操作，也将版本号加一（ version=2 ）试图向数据库提交数据（ balance=80 ），但此时比对数据库记录版本时发现，操作员 B 提交的数据版本号为 2 ，数据库记录当前版本也为 2 ，不满足 “ 当前最后更新的version与操作员第一次的版本号相等 “ 的乐观锁策略，因此，操作员B的提交被驳回。
CAS机制：即compare and swap（比较与交换），无锁编程，在不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，因此也叫非阻塞同步。

CAS过程是这样：它包含3个参数：内存值V（要更新变量的值），旧的预期值A，要修改的值B。当且仅当预期值A的值等于内存值V时，才会将内存值V修改为B，否则不会执行任何操作（V值和A值不同，则说明已经有其他线程做了更新）。一般情况下是一个自旋操作，即不断的重试。

例子：

假设 t1，t2 线程同时更新同一变量56的值。
因为t1和t2线程都同时去访问同一变量56，所以他们会把主内存的值完全拷贝一份到自己的工作内存空间，所以t1和t2线程的预期值都为56。
假设t1在与t2线程竞争中线程t1能去更新变量的值，而其他线程都失败。（失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次发起尝试）。t1线程去更新变量值改为57，然后写到内存中。此时对于t2来说，内存值变为了57，与预期值56不一致，就操作失败了（想改的值不再是原来的值）。
乐观锁的优势和劣势 ：
优势：如果数据库记录始终处于悲观锁加锁状态，可以想见，如果面对几百上千个并发，那么要不断的加锁减锁，而且用户等待的时间会非常的长， 乐观锁机制避免了长事务中的数据库加锁解锁开销，大大提升了大并发量下的系统整体性能表现。所以如果系统的并发非常大的话，悲观锁定会带来非常大的性能问题，所以建议就要选择乐观锁定的方法， 而如果并发量不大，完全可以使用悲观锁定的方法。乐观锁也适合于读比较多的场景。
劣势： 乐观锁只能在提交数据时才发现业务事务将要失败，如果系统的冲突非常的多，而且一旦冲突就要因为重新计算提交而造成较大的代价的话，乐观锁也会带来很大的问题。而且乐观锁也无法解决脏读的问题 。

（2）悲观锁：假定会发生并发冲突，在读取的时候就对数据进行加锁， 在该用户读取数据的期间，其他任何用户都不能来修改该数据，但是其他用户是可以读取该数据的， 只有当自己读取完毕才释放锁。

在数据库中可以使用Repeatable Read的隔离级别（可重复读）来实现悲观锁，它完全满足悲观锁的要求（加锁）。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

悲观锁的优势和劣势 ：
　　　　优势： 能避免冲突的发生 。
　　　　劣势 ：开销较大，而且加锁时间较长，对于并发的访问性支持不好。
两种锁的使用场景：

如果冲突很少，或者冲突的后果不会很严重，那么通常情况下应该选择乐观锁，因为它能得到更好的并发性；

如果冲突太多或者冲突的结果对于用户来说痛苦的，那么就需要使用悲观策略，它能避免冲突的发生。白嫖资料

一般乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候；悲观锁适用于多写的情况，多写的情况一般会经常产生冲突。

数据库中的乐观锁，悲观锁

面试必备之乐观锁与悲观锁

9. 共享锁与排它锁？

共享锁和排它锁是具体的锁，是数据库机制上的锁。

共享锁（读锁）： 在同一个时间段内，多个用户可以读取同一个资源，读取的过程中数据不会发生任何变化。读锁之间相互不阻塞， 多个用户可以同时读，但是不能允许有人修改。
排它锁（写锁）： 在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作，只能由这一个用户来写，其他用户既不能读也不能写。
加锁会有粒度问题，从粒度上从大到小可以划分为 ：

表锁：开销较小，一旦有用户访问这个表就会加锁，其他用户就不能对这个表操作了，应用程序的访问请求遇到锁等待的可能性比较高。
页锁：是MySQL中比较独特的一种锁定级别，锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
行锁：开销较大，能具体的锁定到表中的某一行数据，但是能更好的支持并发处理， 会发生死锁。
10、什么是存储过程？什么是触发器？
存储过程：

（1）定义：

存储过程是一组SQL命令集合，经过预编译存放在系统中。也就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，以后只要调用它就可以完成相应的功能。

（2）存储过程的种类：

存储过程一般分为“系统存储过程”与“用户存储过程”。系统存储过程一般以sp_开头，用户不可以编辑修改，只能调用；用户存储过程是用户编写的处理数据的存储过程。

（3）存储过程的创建和使用：

create procedure proc1 --创建一个存储过程
as
begin
    --在存储过程中处理SQL
    select * from bank
end

【注】如果有参数，存储过程的参数是在as关键字之前，as之后的是局部变量，2种变量在存储过程中都可以使用，但是命名时不可以冲突。例：

create procedure proc2
@mobile varchar(50),@sendMsg varchar(50)
as
begin
    print @mobile  ---输出mobile这个参数
end

（4）存储过程与一般的SQL语句有什么区别呢? （存储过程的优点： ）

存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
存储过程可以重复使用,可减少数据库开发人员的工作量 。
安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
　　缺点：对于简单的sql语句没必要使用存储过程，存储过程适合用于对数据库进行复杂的操作。

触发器：

（1）定义：

触发器（Trigger）是个特殊的存储过程，它不是由用户主动发起调用的，而是当发生某一事件而触发，由系统自动调用。比如当用户在数据库中新增一条商品记录，我们希望同时在库存中做登记，而库存登记不是人工去录入，是在发生新增商品记录这一事件时发生，由系统自动完成录入，这个工作就可以交给一个特殊的存储过程来完成，这个存储过程就是触发器。

（2）触发器的工作机制：

触发器是建在表上的，当这个表发生新增、修改、删除操作时，如果这个表上有触发器，就会被自动调用。在这个事件的过程中，系统会产生一个临时表，这个临时表只有一行记录：

当执行新增操作时，临时表的名字叫inserted
当执行删除操作时，临时表的名字叫deleted
当执行修改操作时，会同时产生2个临时表，一个是inserted，存放的是新的数据，一个是deleted，存的是旧的数据
　　当需要触发器连带操作登记库存时就可以从inserted表或者deleted表中获得变量，更新到库存表中数据。

（3）作用：维护表的完整性，记录表的修改来审计表的相关信息。分为：

DML触发器：当数据库服务器中发生数据操作语言事件时执行的存储过程，分为：After触发器和instead of触发器。
DDL触发器：特殊的触发器，在响应数据定义语言（DDL）语句时触发，一般用于数据库中执行管理任务。DDL触发器是响应create、after、或drop开头的语句而激活。
触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。

（4）创建触发器的SQL语法：

create trigger 触发器名称   --触发器名称
on 表名                 --建在那个表上
for insert|update|delete    --是插入事件处理还是修改事件处理还是删除事件处理
as                       --以下是触发器基本格式
begin
end

调用存储过程：

call procedure_name(参数,参数...)

（5）触发器优点：
白嫖资料

自动执行：触发器不用像存储过程一样需要手动调用，是自动触发的，只有当对表进行更新，删除等操作的时候会立即触发。
级联更新：触发器可以通过数据库中的相关表进行层叠更改，这比直接将代码写在前端的做法更安全合理。
强化约束：触发器可以引用其他表的列，能够实现比check约束更为复杂的约束。
跟踪变化：触发器可以阻止数据库中未经允许的指定更新和变化。
强制业务逻辑：触发器可用于执行管理任务，并强制影响数据库的复杂业务规则。
　　缺点：不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务。

参考：什么是存储过程、触发器的解释

sql的存储过程与触发器的区别，以及各自的优缺点

SQL高级–储存过程和触发器

11、存储过程与触发器的区别？与函数的区别？

存储过程与触发器：

它们都是sql语句集，不同的是：

存储过程是需要用户调用的（通过存储过程名字直接调用），而触发器不是由用户主动发起调用的，而是当发生某一事件而触发，由系统自动调用。在insert、delete和update命令之前或之后自动调用sql命令或者存储过程。

函数：

函数：MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）

自定义函数创建语法：

创建：CREATE FUNCTION 函数名称(参数列表)

RETURNS 返回值类型 　函数体

修改： ALTER FUNCTION 函数名称 [characteristic …]

删除：DROP FUNCTION [IF EXISTS] 函数名称

调用：SELECT 函数名称(参数列表)

存储过程和函数的区别：

一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

12、索引是什么？有什么作用以及优缺点？

索引是对数据库表中一或多个列的值进行排序的结构，利用索引可快速访问数据库表的特定信息。
白嫖资料

举个例子：假设有一张数据表Emplyee，该表有三列：Employee_name，Employee_age，Employee_address，表中有几万条记录。现在要执行下面这条查询语句：Select * from Employee where Employee_name=‘Jesus’。

如果没有数据库索引功能，数据库系统会全表扫描，逐行的遍历整张表，对于每一行都要检查其Employee_Name字段是否等于“Jesus”。而数据库索引功能索引的最大作用就是加快查询速度，它能从根本上减少需要扫表的记录/行的数量。

优点：

索引加快数据库的检索速度
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
加速表和表之间的连接；
使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：

创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

13、说一说MySQL数据库几个基本的索引类型？

普通索引、唯一索引、主键索引、联合索引、全文索引。

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。 为表定义主键将自动创建主键索引。（数据库表某列或列组合，其值唯一标识表中的每一行。该列称为表的主键。）
联合索引：指对表上的多个列做索引。只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。
全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。目前只有char、varchar，text 列上可以创建全文索引。
https://www.cnblogs.com/sdfgdrg/p/10095891.html

14、使用索引查询一定能提高查询的性能吗？为什么？

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价：

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4,5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况：

如果某个字段的取值范围很广，几乎没有重复，即高选择性，则此时使用B+树索引是最适合的，例如姓名。
基于一个范围的检索，一般查询返回结果集小于表中记录数的20%。（MySQL数据库的优化器会预估查询可能得到的行，如果大于某一个值，则B+树会选择全表的扫描。这个值一般在20%（即当取出的数据量超过表中数据的20%，优化器就不会使用索引））

15、为数据表建立索引的原则有哪些？

在最频繁使用的、用以缩小查询范围的字段上建立索引。
在频繁使用的、需要排序的字段上建立索引。

最后，祝大家早日学有所成，拿到满意offer，快速升职加薪，走上人生巅峰。 可以的话请给我一个三连支持一下我哟，我们下期再见

白嫖资料