索引
索引的概念
索引是一个排序的列表,在列表中存储索引的值对应数据所在的物理行。
索引值和数据是一个映射关系
索引的作用
使用索引之后,不需要扫描全表来定位某行的数据。加快数据库的查询速度。
索引可以是表中的一列,也可以的多个列
1、设置了索引之后,数据库可以利用索引快速定位,大大提高查询速度。这是索引的主要原因
2、尤其是表的数据很大,以及涉及多个表查询时,索引可以大大的提高查询速度
3、建立索引不仅能能够提高查询速度,在恢复数据库的数据时,也能提高性能
4、可以加快表与表之间连接查询的速度
索引的副作用
1、创建的索引也需要占用额外的磁盘空间,INNODB存储引擎表数据和索引文件在一块,一体的,相对来说占用空间小
2、更新一个包含索引的表比没用索引的表需要花费更多的时间
表需要更新,索引也要更新,所以速度慢很多
理想的做法:经常被作为搜索条件的列上面创建索引
创建索引的原则和依据
1、表的主键和外键必须有索引,主键是唯一的,外键是关联主表的,查询时可以快速定位
2、一张表有超过300行的数据,应该要创建索引
3、经常与其他表进行连接的表,在连接字段上应该创建索引
4、更新太频繁的字段不适合创建索引
5、经常作为where语句的条件列,应该创建索引
6、经常使用group by和order by的字段上要创建索引
7、选择一个性能高的字段作为索引(字段的值不同的越多越好)
8、索引要建立在小字段上(字符串的长度比较小的字段),长文本字段,不适合建立索引
索引的类型
b-tree索引:又叫b-树索引,绝大部分的数据都是使用这个索引
哈希索引:根据索引对应的hash值的方法获取表的记录行,速度比较慢,用的较少
索引的使用
查看索引的信息:show index from 表名;
创建索引
1、主键索引:
创建表的时候指定的主键就是索引。添加主键自动就是主键索引
主键:值唯一 一个表只能有一个主键,不允许有空值,创建主键,自动主键索引
主键索引是一种特殊的唯一索引
2、普通索引:
create index 列名_index (列名)
3、唯一索引:
可以用key的方式创建,也可以用索引的方式创建
create unique index 列名_index on 表名(列名);
alter table 表名 add unique index 列名_index(列名);
4、组合索引:
一次性给多个列创建索引。形成一个组合
alter table 表名 add constraint uc_列名_列名_列名 unique (列名,列名,列名);
查找:select * from 表名 where 列名 = 值 and 列名 = 值 and 列名 = 值;
5、全文索引:
适用于模糊查询,检索大文本使用的
create fulltext index 列名_index on 表名 (列名);
删除索引:
drop index 列名_index on 表名
alter tabel 表名 drop index 列名_index
删除主键索引不能用drop直接删除索引,只能删除主键约束,然后主键索引自动删除
mysql的事务
概念
事务是一个机制,一个操作序列,一组或者一条数据库的操作命令
把所有的命令作为一个整体向系统提交或者撤销的操作,要么都成功,要么都失败
数据的一致性
事务是一个不可分割的工作逻辑单元,在数据库上执行并发操作时,事务是最小的分割单元
数据库通过事务的控制和事务的整体性保证数据的一致性。
特点
ACID,在数据库的管理系统中,事务的特性有ACID这四种
A(Atomicity):原子性
C(Consistency):一致性
I(Isolation):隔离性
D(Durability):持久性
原子性:事务的最小控制单位,不可分割,要么都成功,要么都失败
一致性:事务开始之前和事务结束之后,数据库的完整性没有被破坏。
开始的时候数据是一致的,结束的时候数据也是一致的
在事务进行的时候,数据可以不一致,但是结束后,数据必须一致
隔离性:并发环境中,不同的事务同时操作相同的数据时,每个事务都有自己完整的数据空间。
对数据的修改所发生的并发事务是隔离的,每个事务之间都是独立的
一个用户的事务不给其他事务所干扰
持久性:一旦写入数据库,数据不可更改
数据库的隔离性:
1、未提交读 RU
允许脏读,一个事务可以看到其他事务未提交的修改
2、提交读 RC
一个事务只能看到其他事务已经提交的修改,未提交的修改不可见,防止脏读
roacle和sql sever
3、可重复读 RR
一个事务在执行中,执行两次相同的select语句,得到的结果都是相同的
mysql的默认隔离选项,防止脏读和不可重复读
4、串行读
相当于锁表,完全串行化的读,一个事务在使用,其他事务的读写都会阻塞
5、不可重复读
在一个事务内,多次读同一个数据,一个事务没有结束,另外一个事务也访问该数据
其中一个事务连续两次查询发现结果不一致,另外一个事务在对这个数据进行修改,两次看到的数据不能一致
6、幻读
一个事务对一个表的数据进行了修改,另一个表也修改了表中数据,前一个事务会发现修改的结果不正确
7、不可更新
两边同时对数据进行修改,但是一方先提交,后提交会覆盖先提交的
事务的控制语句
begin ;start transaction:开启一个事务
commit:提交事务
rollback:回滚
savepoint 名称:设置回滚点
rollback to savepoint 名称:回滚到指定的点
存储引擎
概念
存储引擎,就是一种数据库存储数据的机制,索引的技巧,锁定水平。它是存储的方式和存储的格式。存储引擎也属于mysql当中的组件,实际上操作的执行的就是数据的读写I/O。
存储引擎的分类
innodb:mysql5.5之后默认开始使用innodb事务型速记存储引擎。支持ACID,支持行锁定
myisam:5.5之前默认的存储引擎,插入的速度和查询速度很快,但是不支持事务
memory:内存型存储引擎,数据在写时都保存在内存当中,一旦重启所有数据全部消失
CSV:逗号分割数据的存储引擎,数据文件.csv文件保存的,execl 保存的文件是一个普通的文本文件,不支持索引
Innodb存储引擎
1.读写堵塞(锁表)和事务的隔离级别
2.能够高效的缓存数据支持多种类的索引
3.表的索引类型默认是BTREE
4.支持外键,支持全文索引
5.对硬件的资源要求比较高
6.行级锁定,会把行锁定,禁止操作
模糊查询like进行查询时,会进行全表扫描,在扫描的过程中会锁定整个表。没有创建索引的列,进行查询时,也会锁定整个表。使用的是索引列,锁定条件的行,行锁定
Innodb行锁和索引的关系:
行锁是通过索引来实现的。如果没有索引,Innodb会使用默认的隐藏索引来对记录进行加锁。加了索引就是锁行,不加索引就是锁表
死锁:事务相互等待对方的资源,最后形成一个环路造成的。
如果发生了死锁,数据库会自动选择一个事务作为受害者,回滚该事务以解除死锁
排他锁:for update 当一个事务的操作未完成时,其他事务可以读取但是不能写入
如何避免死锁的情况出现:
1.以固定的顺序访问表和行
2.大事务尽量拆分成小事务
3.为表添加合理的索引