MySQL索引、事务与存储引擎

一、索引

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现。

1.1 索引的概念

索引是一个排序的列表，包含索引字段的值和其相对应的行数据所在的物理地址。

1.2 索引的实现原理

没有索引的情况下，要查询某行数据时，需要先扫描全表，再来定位某行数据，对于表数据很多的情况下，效率较低。

有了索引后，会先通过查找条件的字段值找到其索引对应的行数据的物理地址，然后根据物理地址访问相应的行数据。

1.2 索引的作用

加快表的查询速度，还可以对字段排序。

优点
1）设置了合适的索引之后，数据库利用各种快速定位技术，能够极大地加快查询速度，这是创建索引的最主要的原因；

2）当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度，避免排序和使用临时表；

3）可以降低数据库的IO成本（减少io次数），并且索引还可以降低数据库的排序成本，将随机I/O转为顺序I/O；
4）通过创建唯一性索引，可以保证数据表中每一行数据的唯一性；

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

6）在使用分组和排序时，减少分组和排序的时间；

7）建立索引在搜索和恢复数据库中的数据时能显著提高性能。

缺点

1）会额外占用磁盘空间；

2）更新包含索引的表会花费更多时间，效率会更慢。

1.3 创建索引的依据

1）表中的记录行数较多时，一般超过300行的表建议要有索引；

2）建议在表中的主键字段、外键字段、多表连接使用的公共关键字段、唯一性较好的字段、不经常更新的字段、经常出现在 where、group by、order by 子语句的字段、小字段上面创建索引；

3）不建议在唯一性较差的字段、更新太频繁的字段、大文本字段上面创建索引。

1.4 索引的分类和创建

create table test (id int,name varchar(10),address varchar(10),age int );
1

1.4.1 普通索引 index

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

直接创建索引

create index 索引名 on 表名 (字段); 
#索引名建议以“_index”结尾
1
2

#举个例子
#创建
create index address_index on test (address);
#查看
show create table info \G;
1
2
3
4
5

修改表方式创建

alter table 表名 add index 索引名 (字段);
1

#举个例子
alter table test add index address_index(address);
1
2

创建表的时候指定索引

create table 表名 ( 字段1 数据类型,字段2 数据类型[,...],index 索引名 (列名));
1

#举个例子
create table test2 (id int ,idd int,name varchar(10),index id_index(id));
1
2

1.4.2 唯一索引 unique

与普通索引类似，但区别是唯一索引列的每个值都唯一。

唯一索引允许有空值（注意和主键不同）。

如果是用组合索引创建，则列值的组合必须唯一。

添加唯一键将自动创建唯一索引。

直接创建唯一索引

create unique index 索引名 on 表名 (字段); 
1

#举个例子
create unique index idd_index on test2(idd);
1
2

修改表方式创建

语法
alter table 表名 add unique 索引名 (字段);
1
2

#举个例子
alter table test add unique age_index(age);
1
2

创建表的时候指定

create table 表名 (字段1 数据类型,字段2 数据类型[,...],unique 索引名 (列名));
1

1.4.3 主键索引 primary key

主键索引是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。

一个表只能有一个主键，不允许有空值。

添加主键将自动创建主键索引。

创建表的时候指定

create table 表名 ([...],primary key (列名));
1

#举个例子
create table test2 (id int,pid int,primary key(id));
1
2

修改表的方式创建

alter table 表名 add primary key (字段);
1

#举个例子
alter table test add primary key(id);
1
2

1.4.4 组合索引（单列索引与多列索引）

可以是单列上创建的索引，也可以是在多列上创建的索引。

需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

组合索引   
create index 索引名 on 表名 (字段1, 字段2, 字段3,....);   

alter table 表名 add index 索引名 (字段1, 字段2, 字段3,....);
           
create unique index 索引名 on 表名 (字段1, 字段2, 字段3,....);    

alter table 表名 add unique 索引名 (字段1, 字段2, 字段3,....);

select ... from 表名 where 字段1=XX and 字段2=XX and 字段3=XX    
#用 and 做条件逻辑运算符时，要创建组合索引且要满足最左原则
#用 or 做条件逻辑运算符时，所有字段都要单独创建单列索引
1
2
3
4
5
6
7
8
9
10
11
12

1.4.5 全文索引 fulltext

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。

全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。

直接创建索引

create fulltext index 索引名 on 表名 (字段);  
1

#举个例子
create fulltext index name_index on test2 (name); 
1
2

修改表的方式创建

alter table 表名 add fulltext 索引名 (字段);
1

#举个例子
alter table test2 add fulltext name_index (name);
1
2

创建表的时候指定索引

create table 表名 (字段1 数据类型[,...],fulltext 索引名 (列名));
#数据类型可以为 char、varchar 或者 text
1
2

使用全文索引查询

select * from 表名 where match(列名) against('查询内容');

insert into member values(1,'zhangsan',123123,123123,'nanjing','this is member!');
insert into member values(2,'lisi',456456,456456,'beijing','this is vip!');
insert into member values(3,'wangwu',789789,78979,'shanghai','this is vip member!');
select * from member where match(remark) against('vip');
1
2
3
4
5
6

支持模糊查询  
select ... from 表名 where match(字段) against('查询内容');
1
2

1.5 查看索引

show create table 表名;
show index from 表名;
show keys from 表名;
1
2
3

各字段的含义如下

1.6 删除索引

直接删除索引
drop index 索引名 on 表名;

修改表方式删除索引
alter table 表名 drop index 索引名;
1
2
3
4
5

删除主键索引
alter table 表名 drop primary key;
1
2

二、MySQL事务

2.1 事务的概念

事务就是一组数据库操作序列（包含一个或者多个操作命令）.

事务会把所有操作看作一个不可分割的整体向系统提交或撤销操作，所有操作要么都执行，要么都不执行.

2.2 事务的ACID特性

在可靠数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性:原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

这是可靠数据库所应具备的几个特性。

在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果。

2.3 事务并发导致的问题

2.4 事务的隔离级别

事务的隔离级别决定了事务之间可见的级别。

2.4.1 四种隔离级别

MySQL事务支持如下四种隔离，用以控制事务所做的修改，并将修改通告至其它并发的事务。

1）未提交读( Read Uncommitted(RU))
允许脏读，即允许一个事务可以看到其他事务未提交的修改。

2）提交读(Read Committed(RC))
允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的，防止脏读。

3）可重复读(Repeatable Read(RR))—mysql默认的隔离级别
确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改;

可以防止脏读和不可重复读。

4）串行读(Serializable)—相当于锁表
完全串行化的读，将一个事务与其他事务完全地隔离;

每次读都需要获得表级共享锁，读写相互都会阻塞;

可以防止脏读，不可重复读取和幻读，(事务串行化)会降低数据库的效率。

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

事务隔离级别的作用范围分为两种：
● 全局级：对所有的会话有效
● 会话级：只对当前的会话有效

2.4.2 管理事务隔离级别

1）设置隔离级别

设置全局事务隔离级别

#全局级的隔离级别，可在所有会话有效，需要重新登录才可生效
set global transaction isolation level 隔离级别名称;  
1
2

set global transaction isolation level read committed;
set @@global.tx_isolation='read-committed';   #重启服务后失效
1
2

设置会话事务隔离级别

#会话级的隔离级别，在当前会话中立即生效
set session transaction isolation level 隔离级别名称;     
1
2

set session transaction isolation level repeatable read;
set @@session.tx_isolation='repeatable-read';
1
2

2）查询隔离级别

查询全局事务隔离级别

show global variables like '%isolation%';

SELECT @@global.tx_isolation
1
2
3

查询会话事务隔离级别

show session variables like '%isolation%';

SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;
1
2
3
4

2.5 事务控制语句

#显式地开启一个事务
begin;

#提交事务，并使已对数据库进行的所有修改变为永久性的。
commit;

#回滚
#回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
rollback;

#创建回滚点，相当于存档
#一个事务中可以有多个 
#XX代表回滚点名称。
savepoint XX ；

#把事务回滚到标记点，相当于读档
rollback to XX;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

举个例子

#创建测试用表
use byyb;
create table account(  
id int(10) primary key not null,  
name varchar(40),  
money double  
);

insert into account values(1,'A',1000);  
insert into account values(2,'B',1000);  
1
2
3
4
5
6
7
8
9
10

#测试提交事务
begin;
update account set money= money - 100 where name='A';
commit;
quit

mysql -u root -p
use byyb;
select * from account;
1
2
3
4
5
6
7
8
9

#测试回滚事务
begin;
update account set money= money + 100 where name='A';
rollback;

mysql -u root -p
use byyb;
select * from account;
1
2
3
4
5
6
7
8

#测试多点回滚
begin;
update account set money= money + 100 where name='A';
SAVEPOINT S1;
update account set money= money + 100 where name='B';
SAVEPOINT S2;
insert into account values(3,'C',1000);

select * from account;
ROLLBACK TO S1;
select * from account;
1
2
3
4
5
6
7
8
9
10
11

2.6 使用 set 设置控制事务（自动提交）

如果没有开启自动提交

当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback或者commit;,当前事务才算结束。
当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。

如果开起了自动提交

mysql会把每个sql语句当成一个事务，然后自动的commit。

当然无论开启与否，begin; commit|rollback; 都是独立的事务。

#禁止自动提交
SET AUTOCOMMIT=0;

#开启自动提交，Mysql默认为1
SET AUTOCOMMIT=1;

#查看Mysql中的AUTOCOMMIT值					
SHOW VARIABLES LIKE 'AUTOCOMMIT';
1
2
3
4
5
6
7
8

use byyb;
select * from account;
SET AUTOCOMMIT=0;
update account set money= money + 100 where name='B';
select * from account;
quit

mysql -u root -p
use byyb;
select * from account;	
1
2
3
4
5
6
7
8
9
10

三、MySQL 存储引擎

3.1 存储引擎的概念

MyISAM 表支持 3 种不同的存储格式：
1）静态(固定长度)表
静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。

2）动态表
动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。

3）压缩表
压缩表由 myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

3.2 常用存储引擎（区别）

常用存储引擎：InnoDB、MyISAM
MyISAM：不支持事务、外键约束，只支持表级锁定，适合单独的查询或插入的操作，读写并发能力较弱，支持全文索引，硬件资源占用较小，数据文件和索引文件是分开存储的。存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI
使用场景：适用于不需要事务处理，单独的查询或插入数据的业务场景

InnoDB：支持事务、外键约束，支持行级锁定（在全表扫描时仍然会表级锁定），读写并发能力较好，支持全文索引（5.5版本之后），缓存能力较好可以减少磁盘IO的压力，数据文件也是索引文件。存储成两个文件：表结构文件.frm、数据文件.ibd
使用场景：适用于需要事务的支持，一致性要求高的，数据会频繁更新，读写并发高的业务场景

3.3 语句

3.3.1 查看

1）查看系统支持的存储引擎

show engines;
1

2）查看表使用的存储引擎

方法一

show table status from 库名 where name='表名'\G
1

#举个例子
show table status from byyb where name='test'\G
1
2

方法二

use 库名;
show create table 表名;
1
2

#举个例子
use byyb;
show create table testshow;
1
2
3

3.3.2 修改存储引擎

方法一：通过 alter table 修改

use 库名;
alter table 表名 engine=MyISAM;
1
2

方法二：修改配置文件

通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务。

此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会更改。

vim /etc/my.cnf
......
[mysqld]
......
default-storage-engine=INNODB

systemctl restart mysqld
1
2
3
4
5
6
7

3.3.3 创建

通过 create table 创建表时指定存储引擎

use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;
1
2

3.4 InnoDB行锁与索引的关系

InnoDB行锁是通过给索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。

1）

delete from t1 where id=1;	
1

如果id字段是主键，innodb对于主键使用了聚簇索引，会直接锁住整行记录。

如果id字段是主键，innodb对于主键使用了聚簇索引，会直接锁住整行记录。

2）

delete from t1 where name='aaa';
1

如果name字段是普通索引，会先锁住索引的两行，接着会锁住相应主键对应的记录。

如果name字段是普通索引，会先锁住索引的两行，接着会锁住相应主键对应的记录。

3）

delete from t1 where age=23;
1

如果age字段没有索引，会使用全表扫描过滤，这时表上的各个记录都将加上锁。

如果age字段没有索引，会使用全表扫描过滤，这时表上的各个记录都将加上锁。

3.4 死锁

死锁是指在并发系统中，多个进程或线程因相互竞争资源而无限期地等待的一种状态。
两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致系统无法继续执行。

案例：
create table t1(id int primary key, name char(3), age int);
insert into t1 values(1,'aaa',22);
insert into t1 values(2,'bbb',23);
insert into t1 values(3,'aaa',24);
insert into t1 values(4,'bbb',25);
insert into t1 values(5,'ccc',26);
insert into t1 values(6,'zzz',27);

session 1								session 2
begin;									begin;
delete from t1 where id=5;	
										select * from t1 where id=1 for update;
delete from t1 where id=1; #死锁发生	
										update t1 set name='abc' where id=5; #死锁发生
									
#for update 可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。
#共享锁：又叫做读锁，当用户要进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个。
#排他锁：又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁，排他锁只可以加一个，它和其它的排他锁,共享锁都相斥。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

​

3.5 如何尽可能避免死锁？

1）使用更合理的业务逻辑，以固定的顺序访问表和行；

2）大事务拆小；

大事务更倾向于死锁，如果业务允许，将大事务拆小；

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率；

4）降低隔离级别。

如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁；

5）为表添加合理的索引。