Mariadb-索引及存储引擎

一.存储引擎

1.什么是存储引擎？

	拿汽车来说,一个好的引擎就决定了整个车的性能，你跑的块还是慢全靠引擎来输出动力,那么在MySQL中,引擎也决定着当前数据库能干嘛，
定义了数据的存储方式，数据更新的方法，索引的建立等等，同时存储引擎也被称为表类型,在MySQL中,所有数据偶读是以表的方式进行存储。
	在MySQL当中有多种存储引擎为你提供，不同的存储引擎有不同的优势及缺陷,常见的存储引擎Innodb及myISAM及第三方引擎，
同时用户也可以根据自己的需求编写自己的引擎(这种能编写自己引擎的都是大牛级别)。

2.Mariadb引擎介绍

MariaDB [(none)]> show engines\G;				#通过此命令可以查询当前Mariadb支持的存储引擎有哪些
MariaDB [(none)]> show variables like 'storage_engine%';			#查看当前用的最多的存储

Innodb引擎
	主要功能是支持事务，一般面向在线事务处理方面的应用。
	特点：
	支持外键，行锁，非锁定读(默认情况下读取数据不会产生锁),现在高版本的默认存储引擎都是Innodb。
	Innodb通过使用mvcc来获取高并发性，并且实现了sql标准的4种隔离级别，默认为repeatable级别。
	同时使用一种称为 netx-key locking 的策略来避免换读(phantom)现象。
	Innodb存储引擎默认是将数据放在一个逻辑的表空间中(独立的idb文件中)，这个表空间是由innodb引擎自身来管理。
	Innodb还提供插入缓存(insert uffer),二次写(double write),自适应哈希索引(adaptive hash index),预读(read ahead)等高性能技术提升MySQL的效率。
	对于表中的数据，innodb采用了聚集(clustered)的方式，每张表都是按主键的顺序进行存储，如果建表的时候没有指定主键，
innodb存储引擎会为每一行生成一个6字节的rowid,并以此作为主键。想要了解更深入的可以去看下mysql技术innodb存储引擎。
	
myisam引擎
	myisam存储引擎，myisam特点是不支持事物，适合olap应用，myisam表由MYD和MYI组成。MYD用来存放数据文件,MYI来存放索引文件。
	mysql-5.0版本之前，myisam默认支持的表大小为4G，从mysql-5.0以后，myisam默认支持256T的表单数据。myisam只缓存索引数据。
	注意：myisam存储引擎表，mysql数据库只缓存其索引文件,数据文件的缓存交由操作系统本身来完成，这也与其他大多数数据库都不相同。

3.相关名词解析 事务 多并发版本控制(mvcc)

事务
	什么是事务？
	MySQL事务是主要用来处理操作量大，复杂度高的数据。比如，在人员管理系统中，你要删除一个人员，那么就得清除他的个人基本信息，工作信息，公司邮箱等等，但是这些信息合在就是一个事务。简单来说事务可以跨表存储信息获取信息。
	##功能
	在MySQL中目前只有innodb及BDB类型的数据包能很好的支持事务。通过事务，可以用户来维护数据的完整性，执行事务只会有2个结果，要么成功，要么失败，没有一半失败，一半成功那种。
	##特点：
	事务必须满足4个条件(ACID)
	1.原子性(Autmic):一组事务执行，只有2种结果，要么失败，要么成功，不会部分执行成功，部分执行失败,
即使因为故障的原因导致事务执行失败，恢复时也会自动消除对数据库的影响。
	2.一致性(Consistency)：由外键主键，触发器，检查语句来保证数据的完整性。
	3.隔离性：事务都是独立运行，每个事务对并发的事务都是隔离的，这些基本是通过锁来实现，但是需要牺牲一点性能。
	4.持久性(Durability):每个事务一旦提交，都是永久性的改变数据，当MySQL发生故障导致宕机等问题，对事务都没有啥影响，
同时在MySQL宕机恢复的过程中，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得。
	##操作（到时候看会视频在来写）：
	start transaction; 开启事务

多版本并发控制(mvcc)
	##什么是多版本并发控制？(其他的原理看的我头大)
	MVCC全称Mutli Version Concurreny Control,多版本并发控制，也可称之为一致性非锁定读；
	它通过行的多版本控制方式来读取当前执行时间数据库中的行数据。实质上使用的是快照数据，这样就可以实现不加锁读。
	MVCC 主要应用于 Read Commited 和 Repeatable read 两个事务隔离级别。

二.索引

1.什么是索引？

	索引是一种特殊的文件(innodb引擎数据表上的索引是表空间的一部分),它包含着对数据表里所记录的引用指针，
简单来说就相当于书本中的目录,可以快速帮助你定位数据在哪里，提升查询速度。
	优点:加快搜索速度,减少查询时间。
	缺点:
		索引是以文件方式存在，索引过多，占用磁盘过大，并且还影响insert,update，delete的执行时间。
		索引中的数据必须与数据表中的数据同步，也就是数据表中的数据每做一次操作，索引也得跟着同步操作，当索引过多就会造成效率低下，所以索引怎么建,在后面会谈到。

2.索引类型

索引分为5种：普通索引，唯一索引，主键索引(主索引)，复合索引,全文索引

	#普通索引
	是最基本的索引，不具备唯一性，目的就是加快查询速度，可以建立多个。
	创建方法：
	方法一:在创建表的时候添加索引  用法: create table 表名 ( 字段 字段类型,......,index(字段名)/key(字段名) )；
	MariaDB [abc]> create table sa(id int,name varchar(20),gonghao varchar(20),index(gonghao));  #index是放在最后面的,然后对存在在当前内容里的字段名添加索引
	MariaDB [abc]> desc sa;												#key字段显示MUL是普通索引				
	MariaDB [abc]> show create table  sa;
	MariaDB [abc]> create table ss(id int,name varchar(20),gh varchar(20),key(gh));	
	
方法二:在表创建后添加普通索引 用法:alter table 表名 add key 索引名称(字段名) #索引名称是不可改
	MariaDB [abc]> alter  table abe add key price(price);
	
删除普通索引 用法:alter table 表名 drop key 索引名称	  #可以干掉普通索引,唯一索引
	MariaDB [abc]> alter table sa drop key gonghao;
	
	
#唯一索引
		唯一索引功能基本与普通索引相同,但是有个区别，那就是唯一索引列的值只能出现一次,必须唯一，
主要是用来约束内容,如果你想某个字段的值每个只能出现一次,那就就该唯一索引,用的最多的是对id添加唯一索引,不允许id重复。
	有个特性：唯一索引支持值为null哦！
	创建方法：
	方法1：创建表时，添加唯一索引 用法:create table 表名 (字段名,字段类型.....,unique index/key(字段名))
	MariaDB [abc]> create table jh(id int,name varchar(20),gh varchar(20),unique key(id));
	MariaDB [abc]> create table ja(id int,name varchar(20),gh varchar(20),unique index(id));
	MariaDB [abc]> desc jh;								#key字段显示为UNI 就是唯一索引
	MariaDB [abc]> show create table jh;				#通过此命令可以查看具体的创建过程,就知道创建的是啥索引了、
	
方法二：创建表之后添加唯一索引 用法:alter table 表名 add unique 索引名(字段名)
	MariaDB [abc]> alter table abe add unique id(id);	
	desc abe 
	show create table abe；都可以查询
	
#主键索引
	进行数据库查询，通过主键查询是最快的,但是每个表只能有一个主键列,同时还可以增加多个普通索引列。
	主键列有个要求，那就是列里的所有内容必须是唯一的，索引列就没有这个要求,不允许为空值。
	创建方法：
	方法1：在建表是添加主键 用法:create table 表名 (字段名 字段类型.......,primary key(字段名称)) 
	MariaDB [abc]> create table jq(id int,age int,name varchar(20),primary key(name));
	MariaDB [abc]> desc jq;								#key字段显示为PRI就是主键
	MariaDB [abc]> show create table jq;			
	注：在primary 后面不能加index,不然会报错的
	MariaDB [abc]> create table jz(id int auto_increment primary key,age int,name varchar(20),unique key(name));		
	#这个用法与上面创建主键的方式有点不同,这个是我对id做了主键,同时对name做了唯一索引(在这种方式你得保证你做索引的列值不会被重复) auto_increment自动增长
	
方法2:在建表后新增主键 用法: alter table 表名 add primary key(字段)；(不推荐,一般在创建表的时候已经指定好了,在生成环境中,数据很难保持唯一性,不建议使用)
	MariaDB [abc]> alter table abe add primary key(id);	
	
#删除主键索引
	MariaDB [abc]> alter table abe  change id id int not null;	#设置自增长就得先取消自增长 字段名 int not null就是取消自增长
	MariaDB [abc]> alter table abe drop primary key;			#删除主键
	总结：主键索引，唯一性索引区别：主键索引不能有NULL，唯一性索引可以有空值	

#复合索引
	由2个索引列及以上组成索引就被称为复合索引(一个索引是一个索引列)
	例如：
	MariaDB [abc]> create table firewall(host varchar(20) not null,port smallint(4) not null,acces enum('deny','allow') not null,primary key(host,port));	#创建firewall表,要记录中的IP和port是唯一的。其中：primary key(host,port)); 就是联合主键
	
#全文索引
	全文索引(也称全文检索)是目前搜索引擎中使用的一种关键技术。它可以利用[分词技术]等多种智能算法技术分析出文本中的关键字的使用频率及重要性，
根据一定的算法规则智能匹配出我们想要的搜索结果,简答来说是通过算法技术按照一定的规则快速搜索到我们想要的结果。
	在高并发或者用户访问数据库量大的时候，通过select等方式进行全表扫描，造成服务器压力上升，这个时候全文索引就体现它的强大，
通过算法匹配关键字，快速检索到想要的结果，又不用全表查询，极大提升服务器性能。
	·创建全文索引：
	方法一：创建表时创建
	create table 表名（
		列定义，
		fulltext key 索引名 （字段）;
	）
	方法二：修改表时添加
	alter table 表名 add fulltext 索引名 （字段）;
	ALTER TABLE `books` ADD FULLTEXT  [索引名] (`author` ) 
	MariaDB [abc]> create table gf(id int ,sm varchar(30),price int,fulltext key sm(sm));
	MariaDB [abc]> desc gf; 	#当key值为MUL就是全文索引
	MariaDB [abc]> alter tabe jh add fulltext key gh(gh);

2.外键约束

	foreign key就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整，关联性更强,
简单来说，你对其中一张表进行操作，另外一张表也跟着做对应操作。
	关于完整性，关联性我们举个例子
	有二张表，一张是用户表，一张是订单表：
	1）如果我删除了用户表里的用户，那么订单表里面与这个用户有关的数据，就成了无头数据了，不完整了。
	2）如果我在订单表里面，随便插入了一条数据，这个订单在用户表里面，没有与之对应的用户。这样数据也不完整了。
	如果有外键的话，就方便多了，可以不让用户删除数据，或者删除用户的话，通过外键同样删除订单表里面的数据，
这样也能让数据完整。(使用触发也可以进行相关操作，不一定要设置外键约束)
	MariaDB [abc]> create table `user`(id int not null auto_increment,name varchar(20) not null,sex enum("男","女"),primary key id(id))ENGINE=innodb;
	MariaDB [abc]> create table `order`(aid int auto_increment,bid int default '0',username varchar(20),money int,primary key(aid),index(bid),foreign key order_f_key(bid) references user(id) on delete cascade on update cascade)ENGINE=innodb; 
	解析：
	index(bid) 设置bid为普通索引
	foreign key order_f_key(bid)  将（bid）设置为外键索引
	order_f_key是索引名称，可有可无
	references  user(id) 关联上面use表的id字段，也就是把order表的bid字段跟user表id字段关联起来
	 on delete cascade  on update cascade 添加级联删除和更新：
	外键字段和关联字段，数据类型必须一致。
	注： 建表时，如果表名是sql关键字，使用时，需要使用反引号``