MySQL索引、事务、存储引擎-CSDN博客

本文链接：https://blog.csdn.net/2401_83784774/article/details/139790319

索引

select 查询语句执行速度慢的解决办法

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

（2）查看存储引擎

死锁

避免死锁的方式

索引

概述

索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址
可以用来加快表的查询速度（主要作用）以及对表的字段进行排序
索引会占用额外的磁盘空间，更新有索引的表要花费更长的时间

索引的工作方式

在没有索引的情况下，如果要查询某行数据，需要全表进行扫描，然后去定位某行数据所在的物理地址

在有索引的情况下，会通过索引查询到记录数据所在的物理地址，可直接访问相应的行数据记录

创建索引的依据

（1）表的记录行比较多时，并且读操作也多的情况下需要创建索引

（2）建议在表的主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段上创建索引

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

索引的分类和创建

（1）普通索引

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

直接创建索引

create index 索引名 on 表名(字段(长度));

修改表方式创建

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

创建表的时候指定索引

create table 表名 (... , index 索引名(字段));

（2）唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同）

直接创建唯一索引

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

修改表方式创建

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

创建表的时候指定唯一索引

crete table 表名 (... , unique 索引名(字段));

（3）主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。添加主键将自动创建主键索引。

修改表方式创建主键索引

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

创建表的时候指定主键索引

create table 表名 (.... , primary key(字段));

（4）组合索引

可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则。

直接创建组合索引

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

修改表方式创建

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

根据字段的值查看数据

select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ;

查询语句使用and做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（满足最左原则）

（5）全文索引

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

直接创建全文索引

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

修改表方式创建

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

创建表的时候指定全文索引

 create table 表名 (.... , fulltext 索引名(字段));

删除索引

（1）适用于删除普通索引、唯一索引、组合索引和全文索引

drop index 索引名 on 表名;

alter table 表名 drop index 索引名;

（2）删除主键索引

alter table 表名 drop primary key;

查看索引

show create table 表名;

show index from 表名;

show keys from 表名;

select 查询语句执行速度慢的解决办法

（1）升级 CPU 内存硬盘硬件性能

（2）对 MySQL 配置进行优化

（3）对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接

（4）进行索引优化：

先使用 explain 分析 select 语句（看 key rows type 字段），判断这个查询语句是否正确的使用了索引
再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）

事务

概述

事务是数据库管理系统中的一个基本概念，用于描述一组数据库操作（读取、写入、修改等）的逻辑单位。是一组数据库的操作序列，把操作看成一个不可分割的整体，向数据库提交操作或撤销操作，所有操作要么都一起执行，要么都不去执行。

事务的特性

事务的特性有四个，称为事务ACID特性。

原子性：把事务的操作看成一个不可分割的整体，操作要么全部执行成功，要么全部不执行
一致性：保证事务开始前和结束数据的完整一致
隔离性：多个事务进行并发操作同一个数据时，保证每个事务都有独立的空间，受不到其他事务干扰（可以通过隔离级解决一系列一致性问题）
持久性：事务提交过后，保证事务操作结果被持久化保存，不会被回滚

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

隔离级别

未提交读	允许脏读、不可重复读、幻读
提交读	不允许脏读；允许不可重复读、幻读
可重复度	不允许脏读、不可重复读；有条件的允许幻读
串行读	都不允许

隔离级别的操作

（1）设置隔离级别

1）全局级隔离级别

可在所有会话有效，当前会话需要重新登录方可有效

set global transaction isolation level 隔离级别名称;

2）会话级隔离级别

仅在当前会话中立即有效

set session transaction isolation level 隔离级别名称;

（2）查看隔离级别

show global variables like '%isolation%';

show session variables like '%isolation%';

事务的管理操作

begin;               #显式的开启一个事务
（事务性操作命令）
savepoint XX;        #在事务之中创建回滚点
rollback to XX;      #在事务中回滚到指定的回滚点位置

commit;              提交数据结束事务
rollback;            #回滚结束事务

事务性操作可以是

insect into 表名 (字段) values (字段的值);

uodate 表名 set 字段=字段的值 where 条件表达式;

delete from 表名 where 条件表达式;

设置自动提交事务

set global/session autocommit = 0/1

global 设为全局级别

session 设为会话级别

1 开启自动提交

0 关闭自动提交

查看是否开启自动提交事务

show global/session variables like 'autocommit';

存储引擎

概述

存储引擎是指数据库系统中负责数据存储和访问的组件或模块。负责将数据存储在硬盘上，并提供数据的读写操作。是MySQL数据库的组件，负责执行实际的数据IO操作（数据的存储和提取）。工作在文件系统之上，数据库的数据会先传输到存储引擎，再按照存储引擎的存储格式保存到文件系统。

常见的存储引擎

常用的两种存储引擎：InnoDB MyISAM

InnoDB

不支持事务，不支持外键约束

支持全文索引

锁定类型只支持表级锁定

适合单独插入和查询数据

读写会相互阻塞

硬件资源占用小

数据文件和索引文件分开存储，存储成 .frm 表结构文件、.MYI数据文件、.MYD索引文件

使用场景：

适用不需要支持事务，可以单独查询数据或插入数据的场景

MyISAM

支持事务，支持外键约束

支持全文索引

锁定类型支持行级锁定

读写并发能力好

缓存能力好能减少磁盘IO压力

数据文件就是索引文件，存储为 .frm 表结构文件 .ibd 表空间文件

使用场景：

适用于需要事务支持，数据一致性要求高，数据更新频繁，读写性能高的场景

存储引擎的管理操作

（1）指定存储引擎

1）对已存在的表修改存储引擎

alter table 表名 engine=innodb/myisam;

2）新建表时指定存储引擎

create table 表名 (....) engine=innodb/myisam;

3）设置默认存储引擎

set global/session default_storage_engine=innodb/myisam;

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

vim /etc/my.cnf
default_storage_engine=INnoDB/MyISAM

systemctl restart mysql.service

（2）查看存储引擎

show create table 表名;

show table status [from 库名] where name = '表名';

死锁

死锁是指两个或多个事务在同一个资源相互占用，并请求对方锁定的资源，从而产生相互阻塞的现象

避免死锁的方式

设置事务的锁等待超时时间 innodb_lock_wait_timeout
设置开启死锁检测功能 innodb_deadlock_detect
为表建立合理的索引，减少表锁发生的概率
如果业务允许，可以降低隔离级别，比如选用提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
建议开发人员在读多血少的场景下采用乐观锁机制