Mysql
mysql 逻辑架构
描述
逻辑架构
四层架构
连接层
服务层
引擎层
存储层
存储引擎
什么是存储引擎
mysql存储引擎是形容 表的
常见的存储引擎
MyISAM存储引擎【使用非聚集索引】 【不支持事务】
InNODB存储引擎【使用聚集索引】 【mysql 5 以后的默认存储引擎】【支持事务】
查看mysql的存储引擎
show engines;
show variables like '%storage_engine%';
InNODB存储引擎
这个存储引擎必须要有索引-推荐使用整形 自增的【使用整型的数据与b+书的数据结构有关系】
如果你没有建立索引的话-他会选一列作为主键-若果找不到这个列的话他会自动在后台帮我们维护一个索引主键
innodb 和 myisam 比较
如何选择存储引擎?
- 如果对数据一致性要求比较高,需要事务支持,可以选择 InnoDB。
- 如果数据查询多更新少,对查询性能要求比较高,可以选择 MyISAM。
- 如果需要一个用于查询的临时表,可以选择 Memory
- 如果所有的存储引擎都不能满足你的需求,并且技术能力足够,可以根据官网内部 手册用 C 语言开发一个存储引擎
InnoDB 的表有两个文件(.frm 和.ibd)
.frm 存放表结构
.ibd 存放主键和数据
什么是主键索引 , 辅助索引
- InnoDB 中,主键索引和辅助索引是有一个主次之分的。
- 辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询,会根据主键值在主 键索引中查询,最终取得数据。
- 在 InnoDB 里面,它是以主键为索引来组织数据的存储的,所以索引文件和数据文 件是同一个文件,都在.ibd 文件里面。在 InnoDB 的主键索引的叶子节点上,它直接存储了我们的数据。
为什么辅助索引不存放数据的地址值 而是 主键值
- 是因为有分叉和合并的操作,这个时候键值的地址会发生变化,所以在辅助索引里 面不能存储地址
MyISAM 的表 有三个文件(.frm、.MYD、.MYI)
.frm 存放表结构
.MYD 文件,D 代表 Data,是 MyISAM 的数据文件,存放数据记录
.MYI 文件,I 代表 Index,是 MyISAM 的索引文件,存放索引
也就是说,在 MyISAM 里面,索引和数据是两个独立的文件
MyISAM 的 B+Tree 里面,叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后,会到数据文件.MYD 中获取相应的数据记录。
辅助索引跟主键索引存储和检索数据的方式是没有任何区别的,一样是在索引文件 里面找到磁盘地址然后到数据文件里面获取数据。
mysql事务
事务的基本介绍
如果一个业务逻辑有多个步骤的操作,被事务管理之后,这些事务要么执行全部成功,要么全部失败,数据被操作的基本单元
事务的四大基本特征 ACID
01原子性
同时成功 或者 同时失败
02持久性
提交事务后数据会持久化的保存在硬盘上
03隔离性
多个事务之间, 相互独立
04一致性
数据操作之后 数据总量不变
事务的隔离级别
多个事务之间相互独立的,如果多个事务操作同一批数据,会引发问题----> 设置不同的隔离级别解决这些问题
01脏读
一个事务读取到另一个事务没有提交的数据
02不可重复读
在同一个事务中两次读取到的数据不一样---->也叫虚读 读取到了第二个事务已经提交的数据,和第一次读取的数据不一致【update/delete 造成的】
03幻读
一个事务查询数据时间,另外一个事务添加了一条数据,第一个事务查询到了第二个事务添加的数据【insert】
read uncommitted: 读到未提交数据
- 脏读
- 不可从复读
- 幻读
以上的问题都会发生
read committed: 读 已经提交
-
不可从复读
- 幻读
以上的问题都会发生
解决了脏读的问题
repeatable read: 可重复读【mysql默认】
1. 幻读
以上的问题都会发生
解决了 脏读 和 不可重复读的问题
serializable : 串行化
可以解决以上全部的问题
锁的机制 【与多线程相似】
当一个事务操作一张表的时间这个表被锁了起来,不允许被其他的事务操作 效率低下
不可重复读和幻读,的区别在那里呢?
不可重复读是修改或者删除,幻读是插入。
事务的操作
开启事务
sql--->start transaction
回滚事务
sql--->rollback transaction
提交事务
sql--->commit transaction
查询和设置隔离级别
事务全面了解
mysql 中默认自动提交事务【增加 删除 修改】--一个单独的sql 就是一个事务【增删该】 mysql自动默认提交事务
开始事务之后必须手动提交----> 如果不手动提交默认回滚;所有的操作不在生效
修改事务的默认提交方式
查看事务的提交方式: select @@autocommit --->【0】 代表手动提交事务【1】代表自动提交事务
事务的总结
事务的隔离级别根据不同业务需求进行不同的设置
innodb 实现事务的基本原理-MVVC
什么是mvvc
快照/版本控制
MVCC 的核心思想是:
我可以查到在我这个事务开始之前已经存在的数据,即使它 在后面被修改或者删除了。在我这个事务之后新增的数据,我是查不到的。
问题:这个快照什么时候创建?读取数据的时候,怎么保证能读取到这个快照而不 是最新的数据?这个怎么实现呢?
InnoDB 为每行记录都实现了两个隐藏字段:
1. DB_TRX_ID,6 字节:插入或更新行的最后一个事务的事务 ID,事务编号是自动递 增的(我们把它理解为创建版本号,在数据新增或者修改为新数据的时候,记录当前事 务 ID)。 2. DB_ROLL_PTR,7 字节:回滚指针(我们把它理解为删除版本号,数据被删除或记 录为旧数据的时候,记录当前事务 ID)。
MVVC总结
只能查找创建时间小于等于当前事务 ID 的数据,和删除时间大 于当前事务 ID 的行(或未删除)。 在 InnoDB 中,MVCC 是通过 Undo log 实现的。
需要注意,在 InnoDB 中,MVCC 和锁是协同使用的,这两种方案并不是互斥的。 第一大类解决方案是锁,锁又是怎么实现读一致性的呢?
mysql锁机制
锁的作用
解决事务隔离和并发的问题
分类
01行锁
共享锁 不同事务可以共同获取共享锁 读取数据时间加上的 加上共享锁之后,不能进行跟新删除数据,否者或死锁 手动添加共享锁: SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; 排他锁 只能有一个事务进行操作 更新/数据的时间添加的锁 加上排他锁之后,其他的事务无法获取,共享锁和排他锁 手动添加排他锁: SELECT * FROM student where id=1 FOR UPDATE;
02表锁
将这张表进行加锁 LOCK TABLES student WRITE; LOCK TABLES student Read; UNLOCK TABLES;
03意向锁
数据库自己维护的锁 当我们给一行数据加上共享锁之前,数据库会自动在这张表上面加一个 意向共享锁。 当我们给一行数据加上排他锁之前,数据库会自动在这张表上面加一个意向排他锁。 如果一张表上面至少有一个意向共享锁,说明有其他的事务给其中的某些数据行加 上了共享锁 如果一张表上面至少有一个意向排他锁,说明有其他的事务给其中的某些数据行加 上了排他锁。 我只要判断这张表上面有没有意向锁,如 果有,就直接返回失败。如果没有,就可以加锁成功。所以 InnoDB 里面的表锁,我们 可以把它理解成一个标志。就像火车上厕所有没有人使用的灯,是用来提高加锁的效率 的。
行锁的原理
InnoDB 的行锁,就是通过锁住索引 来实现的。
为什么表里面没有索引的时候,锁住一行数据会导致锁表? 所以,为什么锁表,是因为查询没有使用索引,会进行全表扫描,然后把每一个隐 藏的聚集索引都锁住了。 为什么通过唯一索引给数据行加锁,主键索引也会被锁住? 我们通过辅助索引锁定 一行数据的时候,它跟我们检索数据的步骤是一样的,会通过主键值找到主键索引,然后也给锁定
锁的算法
记录锁
第一种情况,当我们对于唯一性的索引(包括唯一索引和主键索引)使用等值查询,精准匹配到一条记录的时候,这个时候使用的就是记录锁。
间隙锁
第二种情况,当我们查询的记录不存在,没有命中任何一个 record,无论是用等值 查询还是范围查询的时候,它使用的都是间隙锁。
举个例子,where id >4 and id <7 重复一遍,当查询的记录不存在的时候,使用间隙锁。
注意,间隙锁主要是阻塞插入 insert。相同的间隙锁之间不冲突。
间隙锁只在 RR 中存在。如果要关闭间隙锁,就是把事务隔离级别设置成 RC, 并且把innodb_locks_unsafe_for_binlog 设置为 ON。这种情况下除了外键约束和唯一性检查会加间隙锁,其他情况都不会用间隙锁。
临键锁
第三种情况,当我们使用了范围查询,不仅仅命中了 Record 记录,还包含了 Gap 间隙,在这种情况下我们使用的就是临键锁,它是 MySQL 里面默认的行锁算法,相当于 记录锁加上间隙锁
其他两种退化的情况:
唯一性索引,等值查询匹配到一条记录的时候,退化成记录锁。 没有匹配到任何记录的时候,退化成间隙锁
比如我们使用>5 <9, 它包含了记录不存在的区间,也包含了一个 Record 7。
临键锁,锁住最后一个 key 的下一个左开右闭的区间。
select * from t2 where id >5 and id <=7 for update; -- 锁住(4,7]和(7,10]
select * from t2 where id >8 and id <=10 for update; -- 锁住 (7,10],(10,+∞)
为什么要锁住下一个左开右闭的区间?——就是为了解决幻读的问题。
死锁
锁的释放与阻塞
锁什么时候释放? 事务结束(commit,rollback);客户端连接断开。
如果一个事务一直未释放锁,其他事务会被阻塞多久?会不会永远等待下去?如果 是,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占 用大量计算机资源,造成严重性能问题,甚至拖跨数据库。
MySQL 有一个参数来控制获取锁的等待时间,默认是 50 秒。show VARIABLES like 'innodb_lock_wait_timeout';
死锁的发生和检测
在第一个事务中,检测到了死锁,马上退出了,第二个事务获得了锁,不需要等待 50 秒
为什么可以直接检测到呢?是因为死锁的发生需要满足一定的条件,所以在发生死 锁时,InnoDB 一般都能通过算法(wait-for graph)自动检测到。
那么死锁需要满足什么条件?死锁的产生条件
因为锁本身是互斥的,
(1)同一时刻只能有一个事务持有这把锁,
(2)其他的事 务需要在这个事务释放锁之后才能获取锁,而不可以强行剥夺,
(3)当多个事务形成等 待环路的时候,即发生死锁。
如果锁一直没有释放,就有可能造成大量阻塞或者发生死锁,造成系统吞吐量下降, 这时候就要查看是哪些事务持有了锁。
查看锁信息(日志)
show status like 'innodb_row_lock_%';
SHOW 命令是一个概要信息。InnoDB 还提供了三张表来分析事务与锁的情况:
select * from information_schema.INNODB_TRX; -- 当前运行的所有事务 ,还有具体的语句
select * from information_schema.INNODB_LOCKS; -- 当前出现的锁
select * from information_schema.INNODB_LOCK_WAITS; -- 锁等待的对应关系
找出持有锁的事务之后呢?
如果一个事务长时间持有锁不释放,可以 kill 事务对应的线程 ID,也就是 INNODB_TRX 表中的 trx_mysql_thread_id,例如执行 kill 4,kill 7,kill 8。
当然,死锁的问题不能每次都靠 kill 线程来解决,这是治标不治本的行为。我们应该 尽量在应用端,也就是在编码的过程中避免。
死锁的避免
- 在程序中,操作多张表时,尽量以相同的顺序来访问(避免形成等待环路);
- 批量操作单张表数据的时候,先对数据进行排序(避免形成等待环路);
- 申请足够级别的锁,如果要操作数据,就申请排它锁;
- 尽量使用索引访问数据,避免没有 where 条件的操作,避免锁表;
- 如果可以,大事务化成小事务;
- 使用等值查询而不是范围查询查询数据,命中记录,避免间隙锁对并发的影响。
小结:隔离级别的实现
为什么 InnoDB 的 RR【repeatable read】 级别能够解决幻读的 问题,就是用临键锁实现的。
mysql索引
原理/使用
索引概念
- 索引是什么?是mysql高效获取数的【排好顺序的】数据结构
- 索引就是一个便于查询的目录
- 索引以文件的形式存在磁盘中
- innodb 一定会有一个主键索引,自己指定或者数据库自己维护
索引的优势
- 提高检索效率 降低IO成本【所以用B树 而不是用二叉树】
- 降低数据的排序成本 降低cpu的消耗
索引分类
01普通索引
- 没有任何限制
02唯一索引
- 索引列值必须唯一,可以为空
03全文索引
- 针对比较大的数据,比如我们存放的是消息内容
- 如果要解决 like 查询效率低的问题,可以创建全文索引
- 只有文本类型 的字段才可以创建全文索引,比如 char、varchar、text
- select * from fulltext_test where match(content) against('咕泡学院' IN NATURAL LANGUAGE MODE);
04主键索引
- 主键是一种特殊的唯一索引,不可以为空
- 如果我们没有定义主键-innodb会为我们自动维护一个主键索引
05单值索引
- 一个索引只有一个列
- 一张表中可以有多个索引
06复合索引
- 一个索引包含了多个列
07聚集索引【聚簇索引】
- 索引中 b+树的叶子节点包含了完整的数据记录
- 主键索引字段和其他字段数据在同一个文件中存储 【按照B+树的数据结构】
08非聚集索引【稀疏索引】
- 索引文件中只有包含索引字段
- 索引字段和其他数据字段 分开文件存储
09覆盖索引
- 在辅助索引里面,不管是单列索引还是联合索引,如果 select 的数据列可以从索引 中取得,不必从数据区中读取,这时候使用的索引就叫做覆盖索引,这样就避免 了回表。
- 查询字段就是索引字段
- innodb=>回表
- 非主键索引,我们先通过索引找到主键索引的键值,再通过主键值查出索引里面没 有的数据,它比基于主键索引的查询多扫描了一棵索引树,这个过程就叫回表。
10前缀索引
- 当字段值比较长的时候,建立索引会消耗很多的空间,搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。
索引的数据结构
B/B+Tree
mysql 使用的是B+ 树
红黑树
mysql中数据过多的时间,使用红黑树层级太多,浪费空间
mysql没有选用红黑树
mysql 使用的是b+ 树【多路平衡树】
Hash表
适合没有范围的查找,精确查找
mysql 中也有使用【只适用于特定的场景】
我们在 Navicat 工具里面选择索引方法是哈希,但是它创建的还是 B+Tree 索引,这 个不是我们可以手动控制的
索引最左前缀原理
- 联合索引存储结构,多个字段作为索引
- 条件字段不能跳过前面的字段=顺序可以和联合索引的顺序不一致=mysql 会自动进行优化【尽量推荐按照顺序】
- 使用联合索引的时间如果查询字段中没有出现第一个索引=联合索引失效
- 索引失效的原因是:索引排序是根据第一个字段进行排序,在第一个字段拍好顺序的基础上对第二个字段进行排序,如果没有使用第一个索引,第二个索引将不会起作用
索引的操作
那些索引需要建立索引
- 主键建立索引【自动】
- 查询与其他表关联的字段【外键】 可以建立索引
- 左外连接 在右表建立索引 右外连接建立左边的索引
- 组合索引 优势于 单值索引【高并发情况下】
- 查询中分组的字段 适合做索引
- 在用于 where 判断 order 排序和 join 的(on)字段上创建索引
那些情况下不需要简历索引
- 用于范围查询的字段一般不建立在联合索引中【like .. 关键字】
- 频繁跟新/删除 的字段不适合索引建立-因为每次跟新不单单是跟新了记录,同时也跟新了索引
- 字段中重复数据过多的字段不适合建立索引,散列度太低 和全表扫描差不多.
- 顺序经常变化的字段【身份证】不建议建立索引--引起B+树的频繁的分裂合并
索引失效
索引最左原理 -- 带头大哥不能死 中间兄弟不能断
复合索引的字段 最好查询中全部使用【全值匹配】
不要在索引列上做任何操作【计算 函数 类型转换】会导致索引失效,导致全表扫描
尽量少用 *
1. % 写在右边索引不会失效
2. 其他的会失效
3. 解决办法
1. 查询字段中 只包含索引列【也称是覆盖索引】
group by 分组 【分组之前必须排序】【和 order by 一致】【索引错乱会导致 filesort【文件内排序】和临时表】
order by 如果没有按按照索引的顺序 可能产生文件内排序【filesort】
解决filesort 文件内排序的方法
- 单路排序
- 多路排序
字符串不加单引号'' 索引失效
or 使用导致索引失效
范围查询会导致索引失效
注意一个 SQL 语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系。
其实,用不用索引,最终都是优化器说了算。
性能分析/优化
sql的执行顺序
- 手写顺序
- 机器读取顺序
sql 语句加载从 from 后面开始
小表驱动大表
联合查询时间
小的结果集驱动大的结果集 在小的结果集中建立索引
联合查询相当于两成的for 循环进行遍历表的数据 找出条件相同的数据
性能下降sql 慢
- 【执行时间长】【等待时间长】【sql 写的烂】
- 关联查询join 写的太多【必须要写】【设计的缺陷】
索引的优化
慢查询日志分析
默认mysql没有开启慢查询 需要手动来设置参数
如果不是调优需要一般不用开启】【不建议】
慢查询日志分析操作
分析工具【mysqldumpslow】
分析命令 show profile
查看
打开 profile
set profiling = on;
使用
- show profile;
- show prifiles;
诊断
show profile【列表中还可以有其他的字段】 cpu , block io for query 1 【sql 代号--> 在 show profiles 列表中】;
不好的结果【status】
全局查询日志【在测试环境中使用】【了解】
explain关键字
explain 关键字用法
explain + sql 查询语句
根据explain这个工具获取的信息
- 表的读取顺序
- 数据读取操作的操作类型
- 那些索引可以使用
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
explain 中查询出来的字段
大概了解
id字段
查询多张表的时间
id 相同 表的执行顺序从上到下 id 不同 子查询 id越大优先级越高 越先被执行 id 相同也不同 数字大的先走-相同的数字顺序【上到下】执行
select_type
simple 查询中不包含join 或union
简单查询
**primary **
最后加载的那个表
**subquery **
子查询 括号里面的数据
derived
union
union result
table
表的名字
type 访问类型
【8个值】
system【可遇不可求】
系统表中只有一条数据
const【可遇不可求】
eq_ref【可遇不可求】
出现在join查询中
两张表中的数据连接条件字段刚好一一对应
ref【最好达到这个级别】
range【优化到最低要求】
【index】【all】
possible key
显示在这张表中存在的索引,但是不一定使用了
key
实际使用的索引
覆盖索引
- 估计用不到,实际用到了【possible key = null key = index】, 虽然没有符合最左匹配原则,但是我们查询的字段在符合索引中
- 查询的字段和建立的复合索引一一符合
key_lenth
ref【不是重点】
rows
查询的记录条数 预计需要扫描的数据
extra【重要的】
重要的额外信息
using index
使用了覆盖索引,查询的字段刚好就在是索引列
using where
我们需要的数据没有在 索引文件中完全过虑,还需要在数据文件中再次过滤
using index condition
使用了索引下推
using filesort
没有使用索引进行排序而是采用了外部的索引排序,文件内排序
using temporary
使用了临时表保存了中间结果 常见与oder by 和 group by