【mysql数据库】存储引擎，索引，事务，锁机制，触发器，存储过程

一、存储引擎

       存储引擎在MySQL的逻辑架构中位于第三层，负责MySQL中的数据的存储和提取。MySQL存储引擎有很多，不同的存储引擎保存数据和索引的方式是不同的。存储引擎进行文件的访问控制，针对于表而存在。不同的表可以使用不同的存储引擎方式。

      查看存储引擎的命令：show engines;

       热备份：热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。所以，假如你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了

       冷备份：冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将要害性文件拷贝到另外的位置的一种说法。对于备份Oracle信息而言，冷备份是最快和最安全的方法。

MyISAM

        不支持外键，不支持事务，支持全文搜索。它的锁粒度是表锁，不支持行锁。底层为B+树索引，在磁盘存储。 数据和索引分离，即就是非聚集索引，数据离散程度大。数据有地址，索引和数据可单独存储。

InnoDB

       InnoDB是默认的事务型存储引擎，也是最重要，使用最广泛的存储引擎。在没有特殊情况下，一般优先使用InnoDB存储引擎。使用InnoDB时，会将数据表分为.frm 和 idb两个文件进行存储。InnoDB支持外键，支持事务，支持热备份。不支持全文索引，没有主键，没有唯一键，为每一行生产一个6字节的行id，作为主键。它的锁粒度是行锁，底层为B+树索引，在磁盘存储。索引当成数据一部分存储，即就是聚集索引。数据依赖索引进行存储，数据存储前提是索引要存在。

MEMORY

       默认的是哈希索引。数据在内存中存储，如果数据库重启或者宕机，表数据就会丢失。适用于临时数据的存储。把varchar当成char类型来存储，浪费内存、不用计算、效率高（空间换时间）。不支持BLOB（存储图片）与TEXT（大文本字段）。当有混合字段，只处理认识的类型，不认识的如BLOB或TEXT就交给MyISAM（磁盘）去处理，效率降低。解决方法将字段转化成认识的字段进行处理。

ARCHIVE

       以10:1归档压缩。不支持索引，适用于日志数据（不常用的）的存储。每个操作都会产生日志，数据量大平时不会去查看，除非出现错误。只支持insert与select操作。

二、索引

       索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部200条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。一般数据库默认都会为主键生成索引。

索引分为聚集索引和非聚集索引两种：

       聚集索引是按照数据存放的物理位置为顺序的，把索引当成数据一部分存储；

       非聚集索引就不一样了，是把数据和索引分离进行存储。

       各自优势：聚集索引能提高多行检索的速度，而非聚集索引对于单行的检索很快。

（一）Mysql索引的类型

          a)普通索引

         b)唯一索引

         c)全文索引

         d)单列、多列索引

         e)组合索引

(1)索引的创建

a)普通索引（这个是最基本的索引）

建表时：INDEX IndexName(`字段名`(length)) 

建表后：CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 

或ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length)

注意：如果字段数据是CHAR，VARCHAR类型，可以指定length，其值小于字段的实际长度，如果是BLOB和TEXT类型就必须指定length。

这个length的用处是什么?

有时候需要在长文本字段上建立索引，但这种索引会增加索引的存储空间以及降低索引的效率，这时就可以用到length，创建索引时用到length的索引，我们叫做前缀索引，前缀索引是选择字段数据的前n个字符作为索引，这样可以大大节约索引空间，从而提高索引效率。

此处展示的语句用于创建一个索引，索引使用字段数据的前10个字符。
CREATE INDEX part_of_name ON customer (name(10));

使用字段数据的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

前缀索引是一种能使索引更小，更快的有效办法，但是MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描。

这里又引出了一个新概念，覆盖扫描！如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：

SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;
因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

b)唯一索引，要求字段所有的值是唯一的，这一点和主键索引一样，但是允许有空值。

建表时：UNIQUE INDEX IndexName(`字段名`(length)) 

建表后：CREATE UNIQUE  INDEX IndexName ON `TableName`(`字段名`(length)) 

或ALTER TABLE TableName ADD UNIQUE  INDEX IndexName(`字段名`(length)）

c)主键索引，不允许有空值

一般在建表的时候自动创建，主键一般会设为 int 而且是 AUTO_INCREMENT自增类型的

d)全文索引
假设字段的数据类型是长文本，文本字段上(text等)建立了普通索引，我们需要查找关键字的话，那么其条件只能是where column like '%xxxx%' ，但是，这样做就会让索引失效，这时就需要全文索引了。

建表时：FULLTEXT INDEX IndexName(`字段名`(length)) 

建表后：CREATE FULLTEXT  INDEX IndexName ON `TableName`(`字段名`(length)) 

或ALTER TABLE TableName ADD FULLTEXT  INDEX IndexName(`字段名`(length)）

使用：
SELECT * FROM TableName
WHERE MATCH(column1， column2) AGAINST(‘xxx′， ‘sss′， ‘ddd′)
这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

下面我们来举个例子：

假设有一个书籍表，结构如下，文章内容字段的数据类型是text

文章id	文章标题	文章内容
1	超级塞亚人	我是超级塞亚人我喜欢吃苹果，我不是天朝的人，也不是地球人
2	天朝大国	我大天朝威武，我大天朝13亿人，我大天朝
3	我喜欢游泳	游泳有很多好方法
4	动画片	我儿子喜欢看动画片，尤其是七龙珠，因为里面有塞亚人，而且塞亚人喜欢吃苹果,他们不是地球人
5	运动	我喜欢运动，喜欢跑步，喜欢游泳，喜欢健身，喜欢xxoo
6	打炮	我是一个二战的老兵，这是我的回忆录，我最幸福的时光就是在天朝吃着苹果打炮
7	..........	..........
8	..........	..........
9	..........	..........

我想在茫茫多书籍的内容里搜索关键词，如果用%xxx%搜索，那效率就太低了。

我们在文章内容字段上建立全文索引，下面是索引文件

关键词	文章id（引用指针）
塞亚人	1,4
苹果	1,4,6
天朝	1,2,6
地球	1,4
游泳	3,5
七龙珠	4
喜欢	1,4,5,6

那么当我想搜索  “塞亚人”的时候，这个索引文件直接告诉我在文章id为1和4的文章里有这个词。

可是这些关键词是如何提取出来的呢？这就是要提到一个新概念，“分词”！分词就是提取关键词，但是MYSQL的FULLTEXT对分词不够智能，对中文也不是很支持，所以我们一般不用全文索引。取而代之的是：

coreseek=sphinx+mmesg 这个程序就可以解决这个问题的啦。

sphinx就是索引程序。

mmseg就是分词程序。

国内有人修改了sphinx源码，内建和mmseg配合，整合到一起就是coreseek啦（中文版sphinx）！

e)组合索引

假设字段a，b都有索引，我们的查询条件是a=1，b=2查询过程是mysql会先挑选出符合a=1的结果集，再在这些结果集中挑选b=2的结果集，但是mysql并不会在查询a，b时都用到索引，只会用其中一个，这和我们的预期不一样，所以，我们要使用组合索引

建表时：INDEX IndexName(`字段名`(length)，`字段名`(length)，........) 

建表后：CREATE INDEX IndexName ON `TableName`(`字段名`(length)，`字段名`(length)，........) 

或ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length)，`字段名`(length)，........) 

（2）索引的删除

DORP INDEX IndexName ON `TableName`

（3）索引失效的情况　

　1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

　　要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

　2.使用查询的时候遵循mysql组合索引的"最左前缀"规则，假设现在有组合索引（a，b，c），查询语句就只能是a=1或a=1

and b=1或a=1 and b=1 and c=1。这里有两点需要注意①a=1 and b=1和b=1 and a=1一样，没有区别，都会使用索引②组合索引（a，b，c）的最左前缀是a；组合索引（c，b，a）的最左前缀是c，最左前缀和表字段顺序无关

在组合索引中，如果where查询条件中某个列使用了范围查询（不管%在哪），则其右边的所有列都无法使用索引优化查询

　3.like查询以%开头

　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

   5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

   6.索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询。下面是例子：

1	SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

（二）系统建立索引的规则如下：

1)先看是否有主键，主键索引就变成主索引了，数据存放依赖主键存放

2)若无主键，系统看是否有唯一键，使唯一键作为主索引

3)若无主键和唯一键，系统自动添加一个隐藏的字段，一个字段有六个字节，字段类型为autoincreament（自增长类型），以此类型建立主索引，自增长类型为独有的类型。

索引的相关问题：

   （1） 何时使用聚集索引或非聚集索引？

（2）索引不会包含有NULL值的列

       只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

（3）使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

（4）不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007(YEAR时间函数不能进行索引)，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′（直接用字段进行比较，可以用到索引）。

三、事务

       事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

       严格上来说，事务必须同时满足4个特性，即通常所说事务的ACID特性。

什么是事务的ACID？

原子性 A(atomicity)

       一个事务的执行被视为一个不可分割的最小单元。事务里面的操作，要么全部成功执行，要么全部失败回滚，不可以只执行其中的一部分。

一致性 C(consistency)

       一个事务的执行不应该破坏数据库的完整性约束。简单来说应使用锁机制进行保证完整性约束。

隔离性 I(isolation)

       通常来说，事务之间的行为不应该互相影响。然而实际情况中，事务相互影响的程度受到隔离级别的影响。

持久性 D(durability)

       事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。

       脏读

       有AB两个事务，B事务查询已被修改的A事务的数据（A事务结果未提交）

       解决方法：让事务B看不见A事务修改期间的结果，而应查询已提交或回滚后的结果

       不可重复读（修改）update

       B事务查询看到A事务修改前后不同的结果，A提交前B看到旧结果，A提交前B看到新结果。

       解决方法：A（2 3），B（3 4），把B4只能看见A1的数据。

       将A事务隐身，B不知道A的存在和操作，只能读取A之前的操作。

       幻读（插入/删除）insert/delete

        解决方法：用间隙锁解决幻读问题

【隔离级别】

        未提交读   脏读  不可重复读  幻读

        已提交读   不可重复读  幻读  【sql server默认的隔离级别】

        可重复读   幻读              【rr隔离级别，mysql系统默认的隔离级别】

        可序列化  

开启事务：begin; 

提交事务：commit;

查询隔离级别：select @@tx_isolation;

修改隔离级别：set tx_isolation = “xxx”;

【如何保证原子性？】

Redo log   重做日志：记录事务每一条将要执行的操作，先把日志写到磁盘中（日志先行）。

Insert    update     select   set

Undo log   未做日志：操作的每个状态点

Redo与undo保证原子性

【持续性】由日志保证持续性。

【一致性】通过锁机制保证的。

InnoDB存储引擎支持事务。

四、锁机制

锁的互斥与兼容关系
锁和锁之间的关系，要么是相容的，要么是互斥的。
锁a和锁b相容是指：操作同样一组数据时，如果事务t1获取了锁a,另一个事务t2还可以获取锁b；
锁a和锁b互斥是指：操作同样一组数据时，如果事务t1获取了锁a，另一个事务t2在t1释放锁a之前无法获取锁b。

MyISAM表锁

给整个表加锁，粒度大

        （1）读锁：select。读锁又叫共享读锁。与读锁兼容，与写锁不兼容。

        （2）写锁：update。 写锁又叫独占锁。与读锁或写锁都不兼容。

InnoDB行锁

给表中的一行加锁。 粒度小

        （1）读锁：共享锁（可以和读锁共享一行数据），加写锁就不能加读锁，加读锁可加读锁

        （2）写锁：排它锁（排斥除我以外的其他锁）加写锁也可以查询undate；select操作不加锁

MyISAM 中连接叫做 "会话"

InnoDB 中连接叫做 "事务"

乐观锁

       乐观锁指操作数据库时想法很乐观，认为这次操作不会产生冲突，所以在操作数据时不加锁，而在进行更新的时候才采取判断，看是否有冲突。乐观锁不是数据库自带的，需要我们自己实现。一加锁就一定能保证安全。

       实现方式：版本号机制

       在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，version+1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作，则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

if(new_version == old_version)  //说明其他程序没对数据进行操作
{
    进行更新；
    version += 1;
}
if(new_version ！= old_version)  //说明其他程序已经对数据进行了操作
{
    不进行操作；
}

悲观锁

       与乐观锁相对，操作数据库时认为这次操作会产生冲突，所以每次操作前都需要进行加锁处理。悲观锁是由数据库自己实现的，需要使用时可直接调用数据库的相关语句。加锁前先加意向锁，再加读写锁。共享锁和排它锁是悲观锁的两种不同实现。

共享锁：共享锁的代号是S，是Share的缩写，共享锁的锁粒度是行或者元组（多个行）。一个事务获取了共享锁之后，可以对锁定范围内的数据执行读操作。共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。相当于对于同一把门，它拥有多个钥匙一样。

排它锁：排它锁的代号是X，是eXclusive的缩写，排它锁的粒度与共享锁相同，也是行或者元组。排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。一个事务获取了排它锁之后，可以对锁定范围内的数据执行写操作。

意向锁

表明某个事务正在某些行已经加了锁或是这个事务正准备去加锁。意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。意向锁之间相互兼容。

意向锁是表锁的两个原因：（1）如果意向锁是行锁，则需要遍历每一行数据去确认；（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

意向锁与哪些锁冲突？

答案：https://blog.youkuaiyun.com/qq_41026740/article/details/97408858

间隙锁

       区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
       比如：在 1、2、3中，间隙锁的可能值有 (∞, 1)，(1, 2)，(2, 3)，（3，∞）
       间隙锁可用于防止幻读，保证索引间的不会被插入数据

五、触发器

• 触发器用来在某些操作之后，“自动”执行一些操作。（比如插入了新的学生信息，那么在班级表中应该修改学生数）。
• 当insert delete update设置触发器之后，执行insert delete update操作就会自动触发设置的内容。
• 一张表最大能有6个触发器(3*2,三种操作（Insert，delete，update）*两种时间(before、after))。

创建触发器

create trigger trigger_name trigger_time trigger_event

                 on tbl_name for each row trigger_stmt

//trigger_name  触发器名字
//trigger_time  触发程序的动作时间,before、after
//trigger_event 指明激活触发程序的语句类型,insert、update、delete
//trigger_stmt  是当触发程序激活时执行的语句

如：
Delimiter & //手动修改分隔符;为&
create trigger tri_1 
after 
insert   //触发插入的触发器，系统自动+1
on test 
for each row 
begin 
set @count = @count+1; 
end
&

触发器会被什么事件触发？

（1）insert：将新行插入表时激活触发程序，如 insert、load、replace语句

（2）replace： delete+insert、insert语句

replace语句执行时，分以下两种情况：

情况1：insert

当不存在主键冲突或唯一索引冲突，相当于insert操作

情况2：delete and insert

当存在主键冲突或唯一索引冲突，相当于delete操作，加insert操作

（3）update：从表中删除某一行时激活触发程序，如update语句

六、存储过程

create procedure pro_name(【param_name type】 argname arg_type)

begin

       pro-stmt;

end

//param_name type：属性 [ IN | OUT | INOUT ]

//argname：存储过程的参数名称

//arg_type：存储过程的参数类型

IN：输入性参数，值传递（只修改形参，未改变实参的值），只做输入不做输出  //select@tmp->10

OUT：输出型参数，只做输出不做输入 //select@tmp->20

INOUT：输入输出型参数，引用传递，指针。既做输入又做输出

创建存储过程模板：
 

具体例子如下：

Delimiter ##

Create procedure pro_1(IN tmp int)

Begin

select tmp；

Set tmp = 20；

Select tmp；

End

##

存储过程用call调用

存储函数用select调用