MySQL存储引擎，索引，锁机制

一，MySQL存储引擎

介绍：

MySQL数据库使用不同的机制存取表文件，包括存储方式，索引技巧，锁定水平等不同的功能，这些不同的技术以及配套的功能称为索引引擎

Oracle，Sqlserver等数据库只有一种存储引擎，而mysql针对不同的需求，配置不同的存储引擎，就会让数据库采取不同处理数据的方式和扩展功能

MySQL支持的存储引擎有很多，常用的有三种：InnoDB，MyISAM,MEMORY,

InnoDB:

        特点：MySQL的默认存储引擎支持事务和外键操作

        使用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作

MyISAM:

        特点：不支持事务和外键操作，读取速度块，节约资源

        使用场景：以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性，并发性要求不是很高

MEMORY:

        特点：将所有数据保存内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问

        使用场景：通常用于更新不太频繁的小表，用来快速得到访问的结果

根据需要来选择存储引擎，如果不确定，则使用数据库默认的存储引擎 

存储引擎的操作

查询数据库支持的存储引擎

show  engines;

查询某个数据库中所有数据表的存储引擎

show table stauts from 数据库名称;

查询某个数据库中数据表的存储引擎

show table status from 数据库名称 where name = '数据表名称';

创建数据表，指定存储引擎

create table 表名 （
    列名，数据类型，
    ...
    
)engine = 引擎名称；

修改数据表的存储引擎

alter table 表名 engine = 引擎名称；

二，MySQL索引

MySQL索引：使帮助MySQL高效取数据的一种数据结构，所以，索引的本质就是数据结构

在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

按照功能分类

        普通索引：最基本的索引，没有任何限制

        唯一索引：索引的指必须唯一，但允许有空值。如果是组合索引，则列值必须唯一

        主键索引：一种特殊的唯一索引，不允许有空值，在建表是有主键列同时创建主键索引

        联合索引：就是单列索引进行组合

        外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性，完整性和实现级联操作

        全文索引：快速匹配全部文档的方式，InnoDB引擎5.6版本之后才支持全文索引，MEMORY引擎不支持。

按照结构分类

        BTree索引：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型

        Hash索引：MySQL中Memory存储引擎默认支持的索引类型

索引的操作

创建索引

create [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  --默认是Btree
on 表名（列名）；

查看索引

show index from 表名；

 添加索引

--普通索引
alter table 表名 add index 索引名称（列名）；
--组合索引
alter table 表名 add index 索引名称（列名1，列名2...）；
--主键索引
alter table 表名 add primary key （主键列名）；
--外键索引
alter table 表名 add constraint 外键名 froeign key （本表外键名） references 主表名（主键列名）；
--唯一索引
alter table 表名 add unique 索引名称（列名）；
--全文索引
alter table 表名 add fulltext 索引名称（列名）；

 删除索引

drop index 索引名称 on 表名；

 索引原理

BTree数据结构

        每个节点中不仅包含key值，还有数据，会增加查询数据时磁盘的IO次数

B+Tree数据结构

        非叶子节点只存储key值

        所有数据存储在叶子节点

        所有叶子节点之间都有连接指针

B+Tree好处

        提高查询速度

        减少磁盘的IO次数

        树型结构较小

索引的设计原则

创建索引遵循的原则

        1，对查询频次较高，且数据量比较大的表建立索引

        2，使用唯一索引，区分度较高，使用索引的效率较高

        3，索引字段的选择，最佳候选列应当从where子句的条件中提取

        4，索引虽然可以有效的提升查询数据的效率，但不是越多越好

三，MySQL锁机制

锁的介绍

锁机制：数据库为了保证数据的一致性，在共享的资源被访问时变得安全所设计的一中原则

锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性

按照操作分类：

        共享锁：也叫读锁，针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据

        排他锁：也叫写锁，当前的操作没有完成前，会阻断其他操作的读取和写入

按力度分类：

        表级锁：会锁定整个表，开销小，加锁块，锁定力度大，发生锁冲突概率高，并发度低，不会出现死锁的情况

        行级锁：会锁定当前行，开销大，加锁慢，锁定力度小，发生锁冲突概率低，并发度高，会出现死锁情况

按使用方式分类

        悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁

        乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此间别人有没有去跟新这个数据

不同存储引擎支持的锁

存储引擎	表锁	行锁
InnoDB	支持	支持
MyISAM	支持	不支持
MEMORY	支持	不支持

InnoDB共享锁

特点：数据可以被多个事务查询，但是不能修改

创建共享锁格式

select 语句 lock in share mode；

InnoDB排他锁

特点：加锁的数据，不能被其他事务加锁查询或删除

创建排他锁格式

select 语句 from update；

MyISAM读锁

特点：所有连接只能查询数据，不能修改

读锁语法格式：

加锁：

lock table 表名 read；

解锁

unlock tables；

MyISAM写锁

特点：其他连接不能查询和修改数据

写锁语法格式

加锁

lock table 表名 write；

解锁

unlock tables；

悲观锁和乐观锁

方式一：

给数据表中添加一个version列，每次更新后都将这个列的值加1。

读取数据时，将版本号读取出来，在执行更新的时候，比较版本号

如果相同则执行更新，如果不同，说明此条数据已经发生了改变

用户自行根据这个通知来决定怎么处理，比如重新开始一边，或者放弃本次更新

方式二：

和版本号方式基本一样，给数据表中添加一列，名称无所谓，数据类型需要时timestamp

每次更新后将最新时间插入到此列

读取数据时，将时间读取出来，在执行更新的时候，比较时间

如果相同则执行更新，如果不相同，说明词条数据已经发生了变化。