mysql索引，事务，视图，存储过程，存储引擎

一，索引

1.概念

索引：
提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。

索引就类似于书的目录，根据目录来快速查找所需的内容信息；

使用索引后，无需扫描全表来定位某行数据，而是通过索引表找到该行数据对应的物理地址来访问相应的数据，加快查询速度；

建立索引的目的就是加快对表中记录的查找和排序。

2.作用

设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率；

特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成干倍；

可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本；

通过创建唯一性索引保证数据表数据的唯一性；

可以加快表与表之间的连接；

在使用分组和排序时，可大大减少分组和排序时间；

3.创建索引原则

表的主键、外键必须有索引，因为主键的唯一性，外表关联的是子表的主键，可以快速定位；

数据量超过300行的表应该有索引，若无索引，需要把表遍历查询，严重影响性能；

唯一性太差的字段不适合建立索引；

更新太频繁地字段不适合创建索引；

经常与其他表进行连接的表，在连接字段上应该建立索引；

经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引；

索引应该建在选择性高的字段上；

索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

如果必须需要建立索引，则建立FULLTEXT （全文索引）

4.索引优缺点

优点：
加快数据的检索速度；
加速表和表之间的连接；
创建唯一性索引,保证数据库表中每一行数据的唯一性；
在使用分组和排序子句进行数据检索时,减少查询中分组和排序的时间；

缺点：
索引需要占物理空间；
当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。

5.索引的分类

1.普通索引

最基本的索引类型，没有唯一性之类的限制

1)直接创建索引
CREATE INDEX 索引名 ON 表名 (列名[(length)]);
(列名(length))：length是可选项，如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的length个字符来创建索引，这样有利于减小索引文件的大小。
【索引名建议以“_index”结尾。】

2）修改表方式创建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

3）创建表的时候指定索引
CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));

2.唯一性索引

索引列的所有值都只能出现一次，必须唯一;
唯一索引允许为空，仅允许为空一次;
添加唯一键将自动创建唯一索引。

1）直接创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

2）修改表方式创建
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

3）创建表的时候指定
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

3.主键索引

主键是一种唯一性索引，但必须指定为PRIMARY KEY;
主键索引具备唯一索引除允许为空外的特性;
添加主键将自动创建主键索引。

1）创建表的时候指定
CREATE TABLE 表名 ([...],PRIMARY KEY (列名));

2）修改表方式创建
ALTER TABLE 表名 ADD PRIMARY KEY (列名); 

4.组合索引（单列&多列）

可以是单列上创建的索引，也可以是在多列上创建的索引,通过多列索引可以确保定位到某个实体;
需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

5.全文索引（FULLTEXT）

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息;
在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎;
在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引;
全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。

1）直接创建索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

2）修改表方式创建
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

3）创建表的时候指定索引
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

4）使用全文索引查询
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

6.查询索引的两种方式

show index from (表名)；

show keys from (表名);

二，mysql事务

1.事务的概念及ACID特点

1）概念：

1.事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行；
2.事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元；
3.适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统；
4.通过事务的整体性以保证数据的一致性；
回滚：如果事务成功了一部分，一部分未成功，则执行回滚，回到事务的起点，重新开始操作。

2）事务的ACID特点

1）原子性 （Atomictity）
事务是一个完整的操作，事务的各元素是不可分的；
事务中的所有元素必须作为一个整体提交或回滚；
如果事务中的任何元素失败，则整个事务将失败。

2）一致性 （Consistency）
当事务完成时，数据必须处于一致状态：在事务开始之前，数据库中存储的数据处于一致状态；
在正在进行的事务中，数据可能处于不一致的状态；当事务成功完成时，数据必须在此回到已知的一致状态。

3）隔离性 （Isolation）
对数据进行修改的所有并发事务时彼此隔离的，这表名事务必须是独立的，它不应以任何方式依赖于或影响其他事务；
修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

4）持久性 （Durability）
事务持久性指不管系统是否发生故障，事务处理的结果都是永久的；
一旦事务被提交，事务的效果会被永久的保留在数据库中。

2.事务隔离级别

脏读（一个事务读取到另一个事务未提交的数据）
不可重复读（一个事务读取到另一个事务已经提交的数据）
幻读（一个事务多次查询整表数据，由于其他事务新增（删除）记录造成多次查询的记录条数不同（一个事务读取到另一个事务已经提交的数据））

read uncommitted : 读取尚未提交的数据 ：不解决脏读
read committed：读取已经提交的数据 ：可以解决脏读
repeatable read：重读读取：可以解决脏读和不可重复读
serializable：串行化：可以解决脏读、不可重复读和虚读—相当于锁表
Mysql默认的事务处理级别是 repeatable read ，而Oracle和SQL Server是read committed

1）查询全局事务隔离级别

show global variables like '%isolation%';
或
select @@global.tx_isolation;

2）查询会话事务隔离级别

show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

3）设置全局事务隔离级别

set global transaction isolation level read committed;
show global variables like '%isolation%';

4）设置会话事务隔离级别

set session transaction isolation level read committed;
show session variables like '%isolation%';

3.事务控制语句

begin;               开启事务
commit;              提交事务，使已对数据库进行的所有修改变为永久性的
rollback;            回滚事务，撤销正在进行的所有未提交的修改
savepoint s1;        建立回滚点，s1为回滚点名称，一个事务中可以有多个
rollback to s1;      回滚到s1回滚点

1）创建表+测试提交事务

begin;
update info set money=money - 200 where name='A';
commit;
quit

再次登录：
select * from info;

2）测试回滚事务

begin;
update info set money=money + 200 where name='A';
select * from info;
rollback;
select * from info;

3）测试多点回滚

begin;
update info set money=money+200 where name='B';
select * from info;
savepoint s1;
update info set money=money+200 where name='A';
select * from info;
savepoint s2;
insert into info values (3,'C','1500');
select * from info;
rollback to s2;
select * from info;

4.set 设置控制事务

SET AUTOCOMMIT=0;			#禁止自动提交
SET AUTOCOMMIT=1;			#开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';	#查看Mysql中的AUTOCOMMIT值

如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。
当然无论开启与否，begin; commit|rollback; 都是独立的事务。

使用rollback只能向前回滚，无法向后；
使用commit提交后，事务结束，此时再次使用的rollback属于另一个新的事务；

三，视图

1.概念

视图是一张虚拟的表，数据不存在试图中，真实表的映射数据；
利用，条件筛选，分组，排序等产生出一个结果集。并且做成持久化保存。（并不保存数据，只保存映射,存储于内存中）；
视图占用资源相对内存小，真实表中数据变化，视图会对应变化。

创建视图、多表相连查询：
命令格式：
创建视图
create view <视图名称>;
create view info_view as select id,name,age from info 条件;
（多表相连的条件是on、单表的条件是where）
查询视图
select * fom info_view;
select name,age from info_view;

2.特点

安全性高；
简化sql操作；
可以针对不同用户创建不同的视图，不同权限的用户浏览不同的信息

四，存储过程

1.概念

概念
存储过程：多用于软件开发；
存储过程是防止代码在网络传输过程中被截获，做了安全性保障；
原始状态：在代码过程中需要嵌入sql语句，通过连接驱动把sql语句作为参数，传递给mysql（数据库）进行执行，此时就会有安全风险

通过存储过程解决安全隐患；
存储过程是写在数据库中，并不是程序中；
程序是通过调用存储过程名称去触发操作。

2.优点

代码量优化，传输安全，网络资源优化

五，存储引擎

1.存储引擎

MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎；
存储引擎就是MySQL将数据存储在文件系统中的存储方式或存储格式
目前MySQL常用的两种存储引擎：
MylSAM
InnoDB
MySQL存储引擎是MySQL数据库服务器中的组件，负责数据库执行世纪的数据I/O操作

MyISAM 和 INNODB区别
1),存储结构
MyISAM：每个MyISAM在磁盘上存储成三个文件;
InnoDB：所有的表都保存在同一个数据文件中

2),存储空间
MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3),可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

4), 事务支持
MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力

5),表锁差异
MyISAM：只支持表级锁;
InnoDB：支持事务和行级锁，是innodb的最大特色

6),全文索引
MyISAM：支持 FULLTEXT类型的全文索引
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

7),表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

8),表的具体行数
MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。
InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

9), 外键
MyISAM：不支持
InnoDB：支持

10),自增长 AUTO_INCREMENT
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列；
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。