[大数据]MySQL 数据库--索引+事务

一、索引及分类

1.索引的作用

建立索引的目的是为了加快对表中记录的查找或排序

• 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的主要原因
• 当表很大查询或查询涉及到多个表时，使用索引可以成千上万倍提高查询速度
• 可以降低数据库I/O成本，并且还可以降低数据库的排序成本
• 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性
• 可以加快表和表之间的连接
• 在使用分组和排序自居进行数据查询时，可大大减少分组和排序的时间

2.索引的副作用（利大于弊）

• 索引需要占用额外的磁盘空间
• 对于MySAM引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址；而lnnoDB引擎的表数据文件本身就是索引文件
• 在插入和修改数据时要花费更多的时间（因为索引也要随之变动）

3.创建索引的原则依据

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

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

总结：

索引其实就是个列表是个字段，它是经过排序后的字段，是通过物理地址直接去查找所存储的数据，最大作用是加快查询速度，建议创建在最好有唯一性的，不可重复的，字段数比较少的字段上**（小字段上）**

4.索引的分类

• 案例准备

mysql -u root -p

create database club;
use club;
create table feiwu (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
desc member;

insert club.feiwu values (1,'zhangsan','123','111111','nanjing','this is vip');
insert club.feiwu values (4,'lisi','1234','444444','nanjing','this is normal');
insert club.feiwu values (2,'wangwu','12345','222222','benjing','this is normal');
insert club.feiwu values (5,'zhaoliu','123456','555555','nanjing','this is vip');
insert club.feiwu values (3,'qianqi','1234567','333333','shanghai','this is vip');
select * from member;

①普通索引

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

• 直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

#(列名(length))：length是可选项，下同
#如果忽略 length 的值，则使用整个列的值作为索引
#如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小
#索引名建议以“_index”结尾


例：
create index phone_index on feiwu (phone);
#直接创建索引

select phone from feiwu;

show create table feiwu;
#展示表的结构以及创建表的具体语句

• 修改表方式的方式创建

ALTER TABLE 表名 ADD INDEX 索引名 (列名);


例：
alter table feiwu add index id_index (id);
#这是第二种创建普通索引的方式，能修改表格式

select id from feiwu;
#这里查询索引，在自动排序
select id,name from feiwu;

show create table feiwu;

• 创建表时指定索引

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));


例：
create table xianyu(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));

show create table xianyu;

②创建唯一索引

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

唯一索引允许有空值（注意和主键不同），如果是用组合索引创建，则列值的组合必须唯一，添加唯一键将自动创建唯一索引

直接创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);


例：
create unique index name_index on feiwu (name);
#注意，创建唯一索引必须得满足每个值都是唯一的，否则无法创建

show create table feiwu;

• 修改表的方式创建

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);


例：
alter table feiwu add unique cardid_index (cardid);

select cardid from feiwu;
show create table feiwu;

• 创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));


例:
create table www (id int,name varchar(20),unique id_index (id));
show creat table www;

③创建主键索引

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

一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引

该索引要求主键中每个值都唯一

创建表的时候指定索引

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));


例：
create table huhu (id int primary key,name varchar(20));
#这里用了两种方式创建，一种是在字段中直接指定，第二种是在字段结束后指定
create table lala (id int,name varchar(20),primary key (id));

show create table huhu;
show create table lala;

• 修改表方式创建

ALTER TABLE 表名 ADD PRIMARY KEY (列名); 


例：
alter table feiwu add primary key (id);

show create table feiwu;

④创建全文索引

索引类型为 FULLTEXT

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

在 MySQL5.6 版本以前 FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引

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

每个表只允许有一个全文索引

• 直接创建索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);


例：
create fulltext index remark_index on fanren (remark);
#全文索引 fulltext，全文只可在 char、varchar、text 类型上创建，且每个表只允许有一个
show create table fanren;

• 修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

• 创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名)); 
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

• 使用全文索引

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

例：
select * from feiwu where match(remark) against('this is vip');

⑤组合索引

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

多列索引可以区分其中一列可能有相同值的行

如果经常同时搜索两列、多列、按两列或多列排序时，索引也很有帮助

例如，如果经常在同一查询中为姓和名两列设置查询条件，那么这两列上创建多列索引将很有意义

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

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';


例:
create table yiya (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));

show create table yiya;

insert into yiya values(1,'xiaowang','123123');

select * from yiya where name='xiaowang' and id=1;

5.索引查看方法

show index from 表名;
show index from 表名\G;
#在命令后面加上“\G”，可以竖向显示索引的信息
或
show keys from 表名;
show keys from 表名\G;
#这两种方法的显示结果完全相同


例：
show index from feiwu;

小结:
索引分为:
①普通索引:针对所有字段，没有特殊的需求/规则
②唯一索引:针对唯一性的字段，仅允许出现一次空值
③组合索引(多列/多字段组合形式的索引)
④全文索引
( varchar char text)
⑤主键索引:针对唯一-性字段、且不可为空，同时一-张表只允许包含一个主键索引

二、事务

• 它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位

1.事务的 ACID 特性

• 事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
• 这是一个可靠的数据库所应具备的几个特性

2.原子性

• 指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生
• 事务是一个完整的操作，事务的各元素是不可分的，即原子的
• 事务中的所有元素必须作为一个整体提交或回滚
• 如果事务中的任何元素失败，则整个事务将失败

• 例：A 给 B 转帐 100 元钱的时候只执行了扣款语句，就提交了，此时如果突然断电，A账号已经发生了扣款，B账号却没收到加款，在生活中就会引起纠纷；这种情况就需要事务的原子性来保证事务要么都执行，要么就都不执行

3.一致性

• 指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
• 当事务完成时，数据必须处于一致状态
• 在事务开始前，数据库中存储的数据处于一致状态
• 在正在进行的事务中，数据可能处于不一致的状态
• 但当事务成功完成时，数据必须再次回到已知的一致状态

• 例：对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中 A 和 B 的存款总额跟事务执行前一致

4.隔离性

• 指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间

• 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

• 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

5.持久性

• 在事务完成以后，该事务对数据库所作的更改会持久的保存在数据库之中，并不会被回滚
• 指不管系统是否发生故障，事务处理的结果都是永久的
• 一旦事务被提交，事务的效果会被永久地保留在数据库中

总结：

在事务管理中：

• 原子性是基础
• 隔离性是手段
• 一致性是目的
• 持久性是结果

6.事务之间的互相影响

①脏读(读取未提交数据): (A修改1->2,B看到了2, 但是A没有保存，其实数据还是1)

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不
会存到数据库中，也就是不存在的数据

②不可重复读(前后多次读取，数据内容不-致): (A看到表里数据是1, 第二是2,两次不同)

一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起
的

③幻读(前后多次读取，数据总量不- -致): (A改修1->2,保存失败， 同时B修改1->2保存成功， A幻读)

一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时，另一个事务也
修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表
中还有没有修改的数据行，就好象发生了幻觉一样

④丢失更新: (A把1->2,随后B把1->3,看到的结果是3)

两个事务同时读取同一条记录，A先修改记录，B也修改记录(B不知道A修改过)，B提交数据后B
的修改结果覆盖了A的修改结果

7.MySQL及事务隔离级别

read uncommitted : 读取尚未提交的数据 ：不解决脏读
read committed：读取已经提交的数据 ：可以解决脏读
repeatable read：重读读取：可以解决脏读 和 不可重复读 —mysql默认的
serializable：串行化：可以解决 脏读 不可重复读 和 虚读——相当于锁表

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

①查询全局事务隔离级别

show global variables like '%isolation%';

SELECT @@global.tx_isolation;

②查询会话事务隔离级别

show session variables like '%isolation%';

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

③设置全局事务隔离级别

select @@global.tx_isolation;

set global transaction isolation level read committed;

select @@global.tx_isolation;

④设置会话事务隔离级别

select @@tx_isolation;

set session transaction isolation level read committed;

select @@tx_isolation;

8.事务的操作

• 默认情况下，MySQL 的事务是自动提交的
• 之前我们用 SQL 操作数据库时，一条语句执行后，系统会自动执行事务提交
• 当需要把一组语句作为一个事务提交时，需要手动对事务进行控制
• 手动控制事务有两种方法，一种是使用事务处理命令控制，另一种是使用 set 设置事务的处理方式

①使用事务命令控制事务

BEGIN 或 START TRANSACTION：显式地开启一个事务
COMMIT 或 COMMIT WORK：提交事务，并使已对数据库进行的所有修改变为永久性的。

ROLLBACK 或 ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

SAVEPOINT S1：使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 SAVEPOINT；“S1”代表回滚点名称

ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点

创建数据库与数据表，用作实例

create database school;
#创建数据库
use school;
#进入数据库

create table class (id int(10) primary key not null,name varchar(40),money double);
#创建数据表
#字段一（10）：主键，且不能为空值
#字段二（40）可变长度字段
#字段三：浮点类型

insert into class values(1,'A',1000);  
insert into class values(2,'B',1000); 
#在表中插入新数据记录

select * from class;
#查看class数据表

②测试提交事务

• 使用 begin 开始事务，然后执行了两条插入语句，最后用 commit 提交事务
• 此时两条数据插入到了数据表中，这两条语句是作为一个整体操作的

begin;
#开启事务

update class set money = money - 100 where name = 'A';
#更新表字段，name字段=A的money值 - 100

select * from CLASS;
#查看表
commit;
#提交事务
quit
#退出

mysql -uroot -p123123
use school;
select * from class;
#重新登录，再次查看

• 注：如果没有使用 commit 提交，当前依然可以查询到插入的数据，但只是在当前开启的事务中才能看到

• 因为没有真正的插入到数据库中，如果退出再查询就没有了

• 使用 begin 开始事务，执行操作语句后，必须使用 commit 进行提交，否则数据时不能自动提交的

• 也是就是说，多条数据操作语句作为一个整体需要使用 commit 进行整体的事务提交

③测试回滚事务

• 假设插入一条数据后，需要恢复到插入前数据库的状态
• 这里我们用到 rollback 执行事务回滚

begin;
update class set money = money + 100 where name = 'A';

select * from CLASS;

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

mysql -uroot -p123123
use school;
select * from class;

④测试多点回滚

• 很多时候，一个事务会包含多条语句，而出现问题需要回滚时，并不一定是要回滚到 begin 之前的状态，有可能是某条语句执行后的状态
• 这时需要用到 savepoint 定义回滚点，rollback 决定回滚到的位置

begin;
#再次开启一个事务
update class set money = money + 100 where name = 'A';
#更新表中A的money值+100
select * from class;
SAVEPOINT S1;
#创建回滚点S1

update class set money= money + 100 where name = 'B';
#更新表B中的money值+100
select * from CLASS;

SAVEPOINT S2;
#创建回滚段S2

insert into class values(3,'C',1000);
#再次插入一条数据记录
select * from CLASS;

ROLLBACK TO S1;
#回滚到回滚点S1
select * from CLASS;

9.使用set设置控制事务

• 前面已经提到了 MySQL 默认是自动提交事务
• 也可以修改为不自动提交，使用 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; 都是独立的事务

例:

use school;
select * from class;

SET AUTOCOMMIT=0;
#设置禁止自动提交，不使用commit提交操作语句
#和使用begin的情况相同，执行的语句都会失效

SHOW VARIABLES LIKE 'AUTOCOMMIT';
#查看值

update class set money = money + 100 where name = 'B';
#更新表数据
select * from class;
quit

mysql -uroot -p123123
use school;
select * from class;

可以看到，如果不使用commit提交事务，退出MySQL重新进入后，插入的数据并不存在

若开启自动提交，则不需要commit提交事务也可以自动提交