MySQL索引事务与存储引擎

最新推荐文章于 2024-10-02 12:42:27 发布

xiaowei0403

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量163

点赞数

文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/m0_56405902/article/details/131756883

版权

MySQL索引事务与存储引擎

索引
事务
MYSQL存储引擎

索引

概念

是一个排序的列表，存储着索引值和这个值所对应的物理地址无须对整个表进行扫描，通过物理地址就可以找到所需数据是表中一列或者若干列值排序的方法

需要额外的磁盘空间

索引作用

·设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
·当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。

●可以降低数据库的Io成本，并且索引还可以降低数据库的排序成本。
·通过创建唯一(键)性索引，可以保证数据表中每一行数据的唯一性。
在这里插入图片描述

副作用

索引需要占用额外的磁盘空间。
在插入和修改数据时要花费更多的时间，因为索引也要随之变动

索引场景

1、小字段
2、唯一性强的字段
3、更新不频繁，但查询率很高的字段
4、表记录超过300+行
5、主键、外键、唯一键

创建索引原则

表的主键、外键必须有索引
记录数超过300行的表应该有索引
经常与其他表进行连接的表，在连接字段上应该建立索引唯一性太差的字段不适合建立索引
更新太频繁地字段不适合创建索引

索引分类

普通索引:针对所有字段，没有特殊的需求/规则

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

唯一索引︰针对唯一性的字段，仅允许出现一次空值。

create unique index address_index on ky20 (address);
alter table ky20 add unique cardid_index (cardid);
create table amd2 (id int,name varchar(20),unique id_index (id));

组合索引(多列/多字段组合形式的索引)（从左往右）

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

全文索引( varchar char text)

create fulltext index remark_index on ky20 (remark);
ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);
CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));

主键索引:针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引

create table test1 (id int primary key,name varchar(20));
ALTER TABLE 表名 ADD PRIMARY KEY (列名);

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

创建索引：
① 在创建表的时候，直接指定index
② alter修改表结构的时候，进行add 添加index
③ 直接创建索引index
PS:主键索引——》直接创建主键即可

删除索引

直接删除
drop index name_index on member;
修改表的方式
alter table member drop index id_index;
删除主键索引
ALTER TABLE表名 DROP PRIMARY KEY;

事务

概念:

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

特性

●原子性：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

事务是一个完整的操作，事务的各元素是不可分的。
事务中的所有元素必须作为一个整体提交或回滚。
如果事务中的任何元素失败，则整个事务将失败。

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

●一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

当事务完成时，数据必须处于一致状态。
在事务开始前，数据库中存储的数据处于一致状态。
在正在进行的事务中，数据可能处于不一致的状态。
当事务成功完成时，数据必须再次回到已知的一致状态。

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

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

对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

●持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

指不管系统是否发生故障，事务处理的结果都是永久的。
一旦事务被提交，事务的效果会被永久地保留在数据库中。

事务隔离级别

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

总结:读未提交的数据安全性最差﹐但性能最好(不使用! !)

(2 ) read committed（提交读）:读取已经提交的数据:可以解决脏读只能读取到已经提交的数据。0racle等多数数据库默认都是该级别〈不重复读)。

( 3 ) repeatable read(可重复度）:重读读取:可以解决脏读和不可重复读-mysql默认的
可重复读。无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响
( 4) serializable:串行化:可以解决脏读不可重复读和虚读-相当于锁表

MYSQL存储引擎

概念

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

常用的存储引擎

MyISAM

MySQL数据库中的组件，负责执行实际的数据I/o操作
MysQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个后储引婺的右快权犬讲行右储

MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的
访问速度快，对事务完整性没有要求
MyISAM适合查询、插入为主的应用场景

MyISAM在磁盘上存储成三个文件，文件名和表名都相同，但是扩展名分别为:.frm文件存储表结构的定义
数据文件的扩展名为.MYD (MYData)索引文件的扩展名是.MYI (MYIndex)
数据库在读写过程中相互阻塞:——》串行操作，按照顺序操作，每次在读或写的时候会把全表锁起来会在数据写入的过程阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
特性:数据单独写入或读取，速度过程较快且占用资源相对少
MyIsam是表级锁定，读或写无法同时进行
好处是:分开执行时，速度快、资源占用相对较少（相对)
2、MyISAM表支持3种不同的存储格式

5(1）静态(固定长度)表
静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。
(2）动态表5015
动态表包含可变字段(varchar)，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行oPTIMIZE TABLE语句或myisamchk -r
命令来改善性能，并且出现故障的时候恢复相对比较困难。
(3）压缩表
压缩表由myisamchk
工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

InnoDB

1、InnoDB特点
支持事务，支持4个事务隔离级别（数据不一致问题）★★MySQL从5.5.5版本开始，默认的存储引擎为InnoDB5.5之前是myisam (isam)默认
读写阻塞与事务隔离级别相关能非常高效的缓存索引和数据表与主键以簇的方式存储
支持分区、表空间，类似oracle数据库（5.5—》5.6和5.7第三代数据库8.0后版本)支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
对硬件资源要求还是比较高的场合
行级锁定，但是全表扫描仍然会是表级锁定(select )，如update table set a=1 where user like '%lic8’;
InnoDB中不保存表的行数，如 select count(t) from table;时，InnoDB需要扫描一遍整个表来计算有多少行，但是 MyisAM
只要简单的读出保存好的行数即可。需要注意的是，当count(*)语句包含 where条件时
MyISAM
也需要扫描整个表
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立组合索引
清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表(truncate)
死锁

MyISAM :表级锁定innodb :行级锁定
当两个请求分别访问/读取2行记录，同时又需要读取对方的记录数据，因为（行锁的限制）而造成了阻塞的现象
企业选择存储引擎依据
业务场景如果并发量大，什么并发量大，读写的并发量大，那我们建议使用innoDB如果单独的写入或是插入单独的查询，那我们建议使用没有INNODB
表级锁:开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低行级锁:开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低，并发度也最高;
MyISAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景
·支持的字段和数据类型