mysql索引、事务、存储引擎

一、索引

索引的概念：

• 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。
• 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
• 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
• 索引是表中一列或者若干列值排序的方法。
• 建立索引的目的是加快对表中记录的查找或排序

总结：索引就是能够使系统更快速查询信息的结构

索引的作用（副作用）：

作用：

1. 数据库利用各种快速定位技术，能够大大加快查询速率

2. 当表很大或查询涉及到多个表时，可以成千上万倍地提高查询速度

3. 可以降低数据库的IO成本，并且还可以降低数据库的排序成本

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

5. 可以加快表与表之间的连接

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

副作用：

1. 索引需要占用额外的磁盘空间

2. 在插入和修改数据时要花费更多的时间，因为索引也要随之变

创建索引的原则依据：

• 索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担

1. 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位
2. 记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能
3. 经常与其他表进行连接的表，在连接字段上应该建立索引
4. 唯一性太差的字段不适合建立索引
5. 更新太频繁地字段不适合创建索引
6. 经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引
7. 索引应该建在选择性高的字段上
8. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

索引的分类：

• 普通索引：针对所有字段，没有特殊的需求和规则
• 唯一性索引：针对唯一字段，仅允许出现一次空值
• 组合索引：多列/多字段组合的形式的索引，按照排序的顺序，否则无效
• 主键索引：针对唯一字段且不可为空，同时一张表只允许包含一个主键索引
• 全文索引：varchar、char、text、blob、clob 检索内部信息来做字段的索引

创建索引：

1. 创建索引并且指向索引的字段 创建索引的时候直接致电给index

2. 修改表的字段来添加索引 alter修改表结构的时候 添加add 添加index

3. 创建表直接创建索引

   ps：主键索引直接创建主键即可

普通索引：
① ：create index 索引名 on 表名 （字段）；
② ：alter table 表名 add index 自取 （字段）
③ ：create table 表名（id int not null，name varchar(5),index 索引名 （字段））；
唯一索引：
① ：create unique 索引名 on 表名 （字段）；
② ：alter table 表名 add unique 索引名（字段）；
组合索引：
create table 表名(id int not null,name varchar(10),index 索引名 (字段1，字段2))；
select 字段1 字段2 from 表名    #查询（查询时字段的顺序要和创建时一致，否则无效）

查询索引：

1、show create table 表名；
2、show index from 表名；
3、show index from 表名\G;   #竖向显示表的索引信息
4、show keys from 表名；
5、show keys from 表名\G；

删除索引：

1、直接删除索引： drop index 索引名 on 表名；
2、修改表的方式删除索引：alter table 表名 drop index 索引名；
3、删除主键索引：alter table 表名 drop primary key；

二、事务

•  事务主要用于处理操作量大，复杂度高的数据

• 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行

• 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元

• 事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等

• 事务是通过事务的整体性以保证数据的一致性

总结：所谓事务，是一个操作序列，这些操作要么都执行，要么都不执行

事务的ACID特点：

• 原子性：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生
• 一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
• 隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间
• 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

事务之间的影响

1、脏读：就是事务B读取了事务A没有提交的数据

没有提交的数据可能会发生rollback（回滚），

• 比如事务B执行过程中修改了数据X,在未提交前,事务A读取了X,而事务B却回滚了,这样事务A就形成了脏读

2、不可重复读：前后读取的数据内容不一样的现象称为不可重复读

• 事务A第一次查询得到一行记录row1，事务B提交修改后，事务A第二次查询得到row1，但列内容发生了变化。

3、幻读：事务B前后两次读取同一范围的数据，后一次读取总数和前一次不一致，称为幻读

4、丢失更新：事务A去撤销事务时，把已提交的事务B的更新数据覆盖，称为丢失更新

mysql事务隔离级别：

1、read uncommitted ：安全性最差，但是性能最好 （不使用）

• (读取尚未提交的数据 ,不解决脏读,允许脏读，其他事务只要修改了数未提交读）据，即使未提交，本事务也能看到修改后的数据值。也就是可能读取到其他会话中未提交事务修改的数据

2、read committed (提交读) ：安全性较差，性能较好

• 读取已经提交的数据 ，可以解决脏读只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别(不重复读)

3、repeatable read (可重复度) ：安全性较高，性能较差

• 可以解决脏读 和 不可重复读 ，mysql默认的可重复读。无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响

4、serializable串行化 ：安全性最高，性能最差（不使用）

• 可以解决 脏读 不可重复读 和 虚读—相当于锁表完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

mysql默认的事务处理级别是 repeatable read ，而Oracle和SQL Server是 read committed

事务隔离级别的作用范围分为两种：

1. 全局级：对所有的会话有效

2. 会话级：只对当前的会话有效

查询全局事务隔离级别：
show global variables like '%isolation%';
select @@global.tx_isolation;

设置全局事务隔离级别：
set global transaction isolation level read committed;

查询会话事务隔离级别：
show session variables like '%isoation%';
select @@session.tx_isolation;
select @@tx_isolation;

设置会话事务隔离级别
set session transaction isolation level read committed；

事务控制语句：

begin 或 start transaction：显式地开启一个事务
rollback 或 rollback work：回滚 返回结束数据的修改，返回修改之前的数据记录
commit 或 commit work：提交事务，并使已对数据库进行的所有修改变为永久性的
savepoint S1：创建一个回滚点，一个事务中可以有多个savepoint；
          S1代表回滚点名称
rollback to S1：把事务回滚到标记点
          
使用 set 设置控制事务
SET AUTOCOMMIT=0;						#禁止自动提交
SET AUTOCOMMIT=1;						#开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';		#查看Mysql中的AUTOCOMMIT值
如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit

三、MySQL 存储引擎

存储引擎概念

• MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
• 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

MySQL常用的存储引擎：MyISAM 和 InnoDB

myisam:

• MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的

• 访问速度快，对事务完整性没有要求

• MyISAM 适合查询、插入为主的应用场景

MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为：

• .frm 文件存储表结构的定义
• 数据文件的扩展名为 .MYD (MYData)
• 索引文件的扩展名是 .MYI (MYIndex)

myisam表支持三种不同的存储格式：

1、静态表 (char)：静态表时默认的存储格式，字段都是非可变字段

2、动态表 (varchar)：包含可变字段，记录不是固定长度的

3、压缩表 (myisamchk)：每个记录都是被独立压缩的，创一个表就会压缩

MyIsam 是表级锁定

读或写无法同时进行，好处是分开执行时，速度快、资源占用相对较少

Myisam：适合于单方向的任务场景、同时并发量不高、对于事务要求不高的场景

INNODB：

特点：支持事务，支持4个事务隔离级别

死锁：当两个请求分别访问/读取2行记录，同时又需要读取对方的记录数据，因为（行锁的限制）而造成了阻塞的现象

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

查看系统支持的存储引擎
show engines;

查看表使用的存储引擎
方法一：
show table status from 库名 where name='表名'\G；
方法二：
use 库名;
show create table 表名;

修改存储引擎
方法一：
通过 alter table 修改
use 库名;
alter table 表名 engine=MyISAM;
show create table 表名

方法二：
通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务
quit
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB
systemctl restart mysqld.service
修改完记得重启mysql服务
#注意：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。

方法三：
通过 create table 创建表时指定存储引擎
use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;

例：mysql -u root -p
use SCHOOL;
create table hellolic (name varchar(10),age char(4)) engine=myisam;