目录
事务
事务默认是自动提交
事务操作
控制事务
开启事务
START TRANSACTION 或 BEGIN ;
提交事务
commit
回滚事务
ROLLBACK;
举例
-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;
事务的四大特性
原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立
环境下运行。
持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
脏读、不可重复读、幻读
在事务的过程中,并发数据导致的问题,主要有脏读、不可重复读、幻读0
脏读:一个事务读取到另一个事务还未提交的数据。比如b事务读取到a事务还未提交的数据
不可重复读:一个事务先后读取同一条数据,但两次读取的数据不同。即事务a第二次读取同一条数据的过程中,事务b更新了该条数据,而事务a在未提交事务的情况下读取到了事务b更新好的数据
幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 "幻影"。事务a查询id为1的值不存在后准备插入,事务b将插入的数据提交,导致事务a插入失败,在已经解决不可重复读的问题后,再一次查询却并未读取到id为1的值
事务隔离级别
为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。主要有以下几种:
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意:事务隔离级别越高,数据越安全,但是性能越低。
存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。
建表时指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;
查询当前数据库支持的存储引擎
show engines;
存储引擎特点
InnoDB
介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.5 之后,InnoDB是默认的
MySQL 存储引擎。
特点
DML操作遵循ACID模型,支持事务;
行级锁,提高并发访问性能;
支持外键FOREIGN KEY约束,保证数据的完整性和正确性;
文件
xxx.ibd:xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结
构(frm-早期的 、sdi-新版的)、数据和索引。参数:innodb_file_per_table,如果该参数开启,代表对于InnoDB引擎的表,每一张表都对应一个ibd文件。 我们直接打开MySQL的数据存放目录: C:\ProgramData\MySQL\MySQL Server 8.0\Data , 这个目录下有很多文件夹,不同的文件夹代表不同的数据库,我们直接打开itcast文件夹。
MyISAM
MyISAM是MySQL早期的默认存储引擎。
特点
不支持事务,不支持外键
支持表锁,不支持行锁
访问速度快
文件
xxx.sdi:存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引
Memory
1). 介绍
Memory引擎的表数据时存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为
临时表或缓存使用。
特点
内存存放
hash索引(默认)
文件
xxx.sdi:存储表结构信息
面试题:
InnoDB引擎与MyISAM引擎的区别 ?
①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。
索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。
索引的特点
索引结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:
不同的存储引擎对于索引结构的支持情况。
注意: 我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。
索引分类
在MySQL数据库,将索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引。
聚集索引和非聚集索引
而在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
二级索引又叫非聚集索引。聚集索引的叶子节点保存的是对应行所有值,是将索引和数据一起保存的,所以叫做聚集索引。二级索引叶子结点保存的是对应的主键。
聚集索引选取规则:
如果存在主键,主键索引就是聚集索引。如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索
引。除了聚集索引之外的任何索引都叫做二级索引。
聚集索引和二级索引的具体结构如下:
聚集索引的叶子节点下挂的是这一行的数据 。
二级索引的叶子节点下挂的是该字段值对应的主键值。
当具体执行sql语句时,具体的查找过程是什么样的
具体过程如下:
①. 由于是根据name字段进行查询,所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。②. 由于查询返回的数据是*,所以此时,还需要根据主键值10,到聚集索引中查找10对应的记录,最终找到10对应的行row。
③. 最终拿到这一行的数据,直接返回即可。这个过程又称为回表查询
索引语法
创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;
//例如
CREATE INDEX idx_user_name ON tb_user(name);
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
注:如果一个索引关联了多个字段则称为联合索引。
查看索引
SHOW INDEX FROM table_name ;
删除索引
DROP INDEX index_name ON table_name ;
SQL性能分析
查看不同操作执行频次
通过 show [session|global] status 命令可以提供服务器状态信息。
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有
SQL语句的日志。
profile-查询不同操作执行时间
通过have_profiling参数,能够看到当前MySQL是否支持profile操作。通过profiling参数可以查看到开关是否开启:
SELECT @@have_profiling ;
SET profiling = 1;
然后通过如下指令查看指令的执行耗时
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
explain执行计划
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行
过程中表如何连接和连接的顺序。
语法:
-- 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
举例:
explain select s.*,c.* from student s,course c,student_course sc where s.id=sc.stuentid and c.id=sc.courseid
结果:
参数信息
id:select的查询序列号,id相同,执行从上到下进行,id不同,值越大,越先执行。
select_type:表示select的类型
type:表示连接类型
possible_key:显示可能应用在这张表上的索引,一个或多个。
key:实际使用的索引,如果为NULL,则没有使用索引。
key_len:表示索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 。
最左前缀法则
如果索引是联合索引(即索引了多个字段),要遵循最左前缀法则,即查询时必须存在联合索引中最左边的索引,只需要存在即可与放置的位置无关,否则索引失效,并且不能跳过中间的某一列,否则该列后面的索引字段失效。
联合索引查询时,出现范围查询(<>),范围查询右侧的列索引失效。当范围查询使用>= 或 <= 时,走联合索引了,但是索引的长度为54,就说明所有的字段都是走索引的
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0'; //查询结果是staus没有索引
注:字段从左到右依次为profession、age、status组成的联合索引。
索引失效情况
1、 索引列运算
不要在索引列上进行运算操作, 索引将失效。当根据phone字段进行函数运算操作之后,索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';
2、字符串不加引号
字符串类型字段使用时,不加引号,索引将失效。是数据库存在隐式类型转换,索引将失效
3、模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
//索引生效
explain select * from tb_user where profession like '软件%';
//索引失效
explain select * from tb_user where profession like '%工程';
4、 or连接条件
如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
EXPLAIN SELECT * FROM t_user WHERE PASSWORD='admin' OR username='admin'
5、 数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
select * from tb_user where phone >= '17799990005';//未使用索引
select * from tb_user where phone >= '17799990015';//使用了
为什么?
MySQL在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃
索引,走全表扫描。 因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不
如走全表扫描来的快,此时索引就会失效。
6、最左前缀法则
SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优
化操作的目的可以在查询的时候,自己来指定使用哪个索引。
当表中某一字段比如profession字段已经是联合索引,又定义了一个单列索引,在进行查询时mysql数据库会进行评估,确定应该走哪个索引。
use index : 建议MySQL使用哪一个索引完成此次查询(仅仅是建议,mysql内部还会再次进
行评估)。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工
程';
ignore index : 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工
程';
force index : 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工
程'
覆盖索引
覆盖索引是指查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到 。
现有五个字段id、profession、age、status。id为聚集索引,profession、age、status组成联合索引。执行以下sql语句
#以下语句除了查询的字段不同其他都相同
explain select id, profession from tb_user where profession = '软件工程' and age =
31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程'
and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软
件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
执行结果
using where;using index指的是查找使用了索引,需要的字段数据在索引列中都能找到,所以不需要回表查询。
using index condition指的是查找使用了索引但是需要回表查询数据。
比如第一句sql,profession、age、status组成了聚集索引,该索引是二级索引叶子节点下面挂的是id值,索引当查询id、profession时都能在二级索引中找到,不需要回表查询。
第三句sql中,name在二级索引中找不到,索引会根据id值返回到聚集索引中查询数据。
所以一般不推荐select *,因为容易回表影响性能。
前缀索引
当字段类型为字符串(varchar,text,longtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO, 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
语法:
create index idx_xxxx on table_name(column(n)) ;
#例如
create index idx_email_5 on tb_user(email(5));
前缀索引的查询流程
单列索引和联合索引
单列索引:一个索引包含单个列
联合索引:一个索引包含多个列
create unique index idx_user_phone_name on tb_user(phone,name);
联合索引的查询流程