文章目录👀
1️⃣一、索引介绍
索引是一种数据结构,是对数据库表中的一列或多列的值进行排序的一种结构。【索引就类似于书中的目录】
1、索引的优缺点
在数据库中创建索引的优点:
① 加快查询速度,避免全表扫描
② 保证数据的唯一性;
③ 使用group by、order by子句进行查询时,利用索引可以减少排序和分组的时间。
缺点:
- 索引并非越多越好,过多的索引会导致CPU的使用率居高不小
- 索引会占据磁盘空间
- 索引虽然会提高查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件。
设计索引时,应考虑以下数据库准则:
(1)一个表如果建有大量索引,会影响insert、update和delete语句的性能。
(2)避免对经常更新的表创建过多的索引。
(3)使用多个索引可以提高更新少而数据量大的查询的性能。
(4)对小表进行索引可能不会产生优化效果;因为对于非常小的表而言,大部分情况下简单的全表扫描更高效。
二、索引分类
普通索引:没有任何限制条件,可以给任何类型的字段创建普通索引
唯一性索引:使用unique修饰的字段,索引列中的值必须是唯一的,且不能重复,但是允许为空值。
主键索引:使用primary key修饰的字段,索引列中的值必须是唯一的,不允许有空值。
单列索引:在一个字段上创建索引
多列索引:在表中多个字段上创建索引
全文索引:使用fulltext参数设置全文索引,只能在char、varchar和text类型的字段上创建全文索引,常用于数据量较大的字符串类型上,可以提高查询速度,MyISAM存储引擎支持全文索引,InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
聚集性索引:在InNoDB引擎下建立的索引,将索引及数据存放在一起
非聚集性索引:在MyISAM引擎下建立的索引,将索引和数据分开存储在不同的文件中
三、索引的创建与删除
1、在创建表时创建索引
create table 表名(
属性名 属性类型 [完整性约束]
[unqiue|fulltext|spatial] index|key [别名] 属性名 (asc,desc)
);
说明:
unqiue|fulltext|spatial都是可选参数:
- unqiue表示索引为唯一性索引
- fulltext表示全文索引
- spatial表示空间索引
- 如果不写,就默认是普通索引
index|key:用来创建索引,index与key作用相同
别名:可以给自己所创建的索引起一个别名,可以是任意的字段
属性名:需要创建索引的属性的名称,该属性必须是已经存在的,如果不存在,需要先进行创建。
asc|desc是可选参数,asc:表示升序排列 ,desc:表示降序排列
示例如下:
创建普通索引
create table index1 (
id int ,
name varchar(20),
sex varchar(2),
index (name)
);
创建唯一性索引
create table index2(
id int ,
name varchar(20),
sex varchar(2),
unique index idx_id (id)
);
2、在已经存在的表上创建索引
第一种:通过create语法创建索引
格式:
create [unqiue|fulltext|spatial] index 索引名 on 表名 (属性[asc|desc])
第二种:通过alter语法创建
格式:
alter table 表名 add [unqiue|fulltext|spatial] index 索引名(属性名 [asc|desc])
3、删除索引
drop index 索引名 on 表名;
2️⃣四、事务定义
事务:由一条或多条对数据库操作的SQL语句组成的一个不可分割的单元,只有当事务中所有的操作都正常执行完了,整个事务才会被提交到数据库,如果部分事务处理失败,那么事务就要回滚到最初的状态,因此,事务要么全部执行成功,要么全部失败。
经典案例:
银行转账:当两个人发起转账业务时,如果A转账发起,而B因为一些原因不能成功接受,事务最终将不会提交,则A和B的请求最终不会成功。
五、事务特征(ACID)
事务的原子性(Atomic)
事务是一个不可分割的整体,事务必须具有原子特性,事务中的操作要么全都执行,要么全都不执行,不允许部分执行。
事务的一致性(Consistency)
事务的执行不能破坏数据库数据的完整性和一致性,一个事务在执行之前和执行之后,数据库的数据都必须处于一致性状态。
数据库的一致性状态由用户来控制,并发控制机制实现。
事务的隔离性(Isolation)
当两个或者多个事务并发执行时,为了保证数据的安全性,将一个事务内部的操作与其他事务的操作隔离起来,不能被其他正在执行的事务所看到。
隔离性使每个事务的更新操作之前,对其他事务是不可见的。
事务的持久性(Durability)
事务执行完成之后,它对数据库中数据的修改是永久性的,即使数据因为故障出错,也应该能够恢复数据。
六、事务的使用
1、查看事务
select @@autocommit;
查看MySQL中事务是否打开自动提交:
默认为1表示开启自动提交, 0 表示手动提交。
2、修改事务
set autocommit=0;
set autocommit=X 操作,设置事务提交方式为手动提交还是自动提交
注意:设置事务手动还是自动只针对当前的窗口、当前的用户连接起作用,其他的连接是不受影响
3、事务操作
(1)开启事务
begin;
(2)事务提交
commit ;
当组成一个事务的多个SQL都执行成功,调用commit提交一个事务,意味着数据被写入到数据行中,可被其他的事务读取到。
(3)事务回滚
rollback ;
如果事务执行的过程中有操作执行失败就会回滚到初始的状态,begin之前的状态
(4)保存点
savepoint point1; //设置一个名称为point1的保存点
rollback to point1; //回滚到指定的保存点point1,而不是回滚到初始状态
七、并发事务问题
在多个事务并发运行时,会遇到操作相同的数据来完成各自任务,可能会存在以下问题:
1、脏数据(Dirty READ)
当一个事务正在访问数据并且对数据进行修改,而这个操作还没有提交到数据库中,这时另外一个事务也访问了这个数据,然后使用了该数据,因为数据还没有提交到数据,那么另一个事务读到的这个数据就是脏数据,依据脏数据所做的操作是不正确的。
如果A事务读取到B事务尚未提交的更改数据,并在这个脏数据的基础上进行的操作,如果B事务进行回滚,A事务读取到的事务是不被承认的。
2、不可重复读(UnrepeatableRead)
A事务内多次读取同一个数据,在这个事务还没结束时,B事务也访问该数据,A事务在两次读数据之间,B事务修改了该数据,A事务再次读取数据可能和第一次读取的不太一样。一个事务内两次读取的数据是不一样的,因此称为不可重复读。
一个事务内两次读取同一个数据的结果不一样。不可重复读指A事务读取到了B事务已经提交的更改数据
3、幻读(phantom Read)
A事务读取几行数据,接着B事务插入了一些数据,在随后的查询中,A事务会发现一些原来不存在的记录,好像发生了幻觉一样,所以称为幻读。
不可重读读和幻读区别:
不可重读一般针对行级别的数据的更改,重点是修改数据;
幻读一般针对表级别的新增/删除数据,在统计事务中,两次读取的数据统计不一样,幻读的重点在于新增或者删除。