Mysql中级篇
一、存储引擎
1、Mysql体系结构
2、存储引擎简介
(1)介绍
- 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。
- 存储引擎是基于表的,而不是基于库的,所以也被叫做表类型。
(2)语法
- 创建表时,指定存储引擎
CREATE TABLE 表名(
字段 类型
) ENGINE = INNODB;
- 查看当前支持的存储引擎
SHOW ENGINES;
3、存储引擎特点
(1)InnoDB
- 介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在Mysql5.5之后,InnoDB是默认的MySQL存储引擎 - 特点
DML操作遵循ACID模型,支持事务;
支持外键约束,保证数据的完整性和正确性;
行级锁,提高并发访问性能; - 文件
xxx.ibd: xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构、数据和索引。
参数:innodb_file_per_table
(2)MyISAM
- 介绍
MyISAM是MySQL早期的默认存储引擎。 - 特点
不支持事务,不支持外键
支持表锁,不支持行锁
访问速度快 - 文件
xxx.sdi : 存储表结构信息
xxx.MYD : 存储数据
xxx.MYI : 存储索引
(3)Memory
- 介绍
Memory引擎的表数据存储在内存中,由于受到硬件问题、或断电问题影响,只能将这些表作为临时表或缓存使用。 - 特点
内存存放
hash索引 - 文件
xxx.sdi : 存储表结构信息
(4)比较
4、存储引擎选择
5、总结
二、索引
1、索引概述
(1)介绍
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上试下高级查找算法,这种数据结构就是索引。
(2)演示
(3)优缺点
优点 | 缺点 |
---|---|
提高数据检索的效率,降低数据库的IO成本 | 索引降低了更新表的速度,效率降低 |
通过索引对列数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引列也要占用空间 |
2、索引结构
Mysql的索引是在存储引擎层实现的,不同的存储引擎有不同的结构:
索引结构 | 描述 |
---|---|
B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。 |
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+Tree索引 | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-Tree索引 | 不支持 | 支持 | 不支持 |
Full-text索引 | 5.6之后支持 | 支持 | 不支持 |
我们平时说的索引,如果没有特别指明,都是B+树结构组织的索引。
(1)二叉树
二叉树缺点:顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度慢。
红黑树:大数据量情况下,层级较深,检索速度慢。
(2)B-Tree(多路平衡查找树)
数据结构动态图地址
(3)B+Tree
(4)hash
hash索引特点
- Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<)
- 无法利用索引完成排序操作
- 查询效率高,通常只需要一次检索就可以,效率通常高于B+tree索引
存储引擎支持
在MySQL中,支持hash索引的是Memory索引,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
(5)为什么InnoDB存储引擎选择使用B+Tree索引结构?
- 相对于二叉树,层级更少,搜索效率高;
- 相对于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大类数据,只能增加树的高度,导致性能降低;
- 相对hash索引,B+tree支持范围匹配及排序操作。
3、索引分类
(1)分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
(2)在InnoDB存储引擎中,根据索引的存储形式,又可以分为两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引 | 将数据存储和索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引 | 将数据存储和索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存多个 |
聚集索引的选区规则
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,InnoDB则会自动生成一个rowid作为隐藏的聚集索引。
4、索引语法
5、SQL性能分析
6、索引使用
7、索引设计原则
三、SQL优化
1、插入数据
(1)批量插入
(2)手动提交事务
(3)主键顺序插入
(4)大批量插入数据
2、主键优化
3、order by 优化
4、group by 优化
5、limit优化
6、count优化
7、update优化
四、视图/存储过程/触发器
略
五、锁
1、概述
(1)介绍
锁是计算机协调多个进程或线程并发访问某一资源的机制。
(2)分类
按照锁的粒度分:
- 全局锁:锁定数据库中的所有表。
- 表级锁:每次操作锁住整张表。
- 行级锁:每次操作锁住对应的行数据。
2、全局锁
(1)介绍
全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的写语句和提交语句都将被阻塞。
典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,保证数据完整性。
## 加锁
flush tables with read lock;
## 备份
mysqldump -uroot -p1234 itcast > itcast.sql;
## 解锁
unlock tables;
(2)特点
加全局锁是一个比较重的操作,存在以下问题:
- 如果主库备份,在备份期间都不能执行更新,业务基本停摆。
- 如果从库备份,在备份期间从库不能执行主库同步的二进制日志,会导致主从延迟。
mysqldump --single-transaction -uroot -p1234 itcast > itcast.sql;
3、表级锁
(1)介绍
每次操作锁住整张表。锁定粒度大,发生冲突的概率最高,并发度最低。
分为三类:
- 表锁
- 元数据所(MDL)
- 意向锁
(2)表锁
表锁分为两类:
- 表共享读锁(read lock):不会阻塞其他客户端的读,但是会阻塞写,本身写操作报错。
- 表独占写锁(write lock):本身可读可写,其他客户端的读写都阻塞。
## 加锁
lock tables 表名... read/write
## 释放锁
unlock tables
(3)元数据锁(MDL)
MDL加锁过程是系统自动控制,无需显示使用,在访问表时会自动加上。
当对表进行增删改查(DML)时,加MDL读锁;当对表结构进行变更操作(DDL)时,加MDL写锁。MDL主要作用是为了避免DML和DDL冲突,保证读写的正确性。(当进行增删改查时,不能修改表结构)。
对应SQL | 锁类型 | 说明 |
---|---|---|
lock tables xxx read/wirte | SHARED_READ_ONLY、SHARED_NO_READ_WRITE | |
select、select…lock in share mode | SHARED_READ | 与SHARED_WRITE兼容,与EXCLUSIVE互斥 |
insert、update、delete、select…for update | SHARED_WRITE | 与SHARED_READ兼容,与EXCLUSIVE互斥 |
alter tables … | EXCLUSIVE | 与其他的DML都互斥 |
查看元数据锁:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
(4)意向锁
为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB引入了意向锁,使得表锁不用检查每行数据是否加锁。
锁类型 | 语句 | 互斥情况 |
---|---|---|
意向共享锁(IS) | 由select … lock in share mode添加 | 与表锁共享锁read兼容,与表锁排它锁write互斥 |
意向排他锁(IX) | 由insert 、update、delete、select… for update添加 | 与共享锁和排它锁都互斥 |
查看意向锁:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
4、行级锁
六、InnoDB引擎
1、逻辑存储结构
- 表空间(ibd文件):一个mysql实例可以对应多个表空间,用于存储记录、索引等数据。
- 段:分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment),InnoDB是索引组织表,数据段就是B+树的叶子节点,索引段即为B+树的非叶子节点。
- 区:表空间的单元结构,每个区的大小为1M。页大小默认为16k,即一个区中有64个连续的页。
- 页:InnoDB存储引擎磁盘管理的最小单元,默认大小16k。为了保证页的连续性,InnoDB每次从磁盘中申请4~5个区。
- 行:数据是按行进行存放的。
2、架构
Mysql5.5版本开始,默认使用InnoDB存储引擎,它擅长事务处理,具有崩溃恢复特性。
InnoDB架构图:左侧是内存结构,右侧是磁盘结构。
(1)内存结构 (Memory Structures)
① Buffer Pool 缓冲池
缓冲池是内存中的一个区域,可以缓存磁盘上经常操作的真实数据,在执行增删改查操作时,先操作缓冲池中的数据(若缓冲池没有数据,则从磁盘加载并缓存),然后再以一定频率刷新到磁盘,从而减少磁盘IO,加快处理速度。
缓冲池以Page页为单位,底层采用链表数据结构管理Page。根据状态,page分为三种类型:
- free page:空闲page,未被使用。
- clean page:被使用page,数据没有被修改过。
- dirty page:脏页,被使用page,数据被修改过,与磁盘的数据不一致。
② 更改缓冲区(Change Buffer)
更改缓冲区,主要针对非唯一二级索引,在执行DML语句时,如果这些数据Page没有在Buffer Pool中,不会直接操作磁盘,而会将数据变更存在Chagne Buffer中,在未来数据被读取时,再将数据合并恢复到Buffer pool中,再将合并后的数据刷新到磁盘中。
Chagne Buffer意义是什么?
与聚集索引不同,二级索引通常是非唯一的,并且以相对随机的顺序插入二级索引。同样,删除和更新可能会影响索引树中不相邻的二级索引页,如果每一次都操作磁盘,会造成大量的磁盘IO。有了Change Buffer之后,我们可以在缓冲池中进行合并处理,减少磁盘IO.
③自适应hash索引(Adaptive Hash Index)
用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询,如果观察到hash索引可以提升速度,则建立hash索引,称之为自适应hash索引。
自适应哈希索引,无需人工干预,是系统根据情况自动完成,参数:adaptive_hash_index。
④ 日志缓冲区 (Log Buffer)
用来保存要写入到磁盘中的log日志数据(redo log、undo log),默认大小为16M,日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务,增加日志缓冲区的大小可以节省磁盘I/O
参数:
-
innodb_log_buffer_size:缓冲区大小
-
innodb_flush_log_at_trx_commit:日志刷新到磁盘时机,值为0,1,2
1:日志在每次事务提交时写入,并刷新到磁盘;
0:每秒将日志写入,并刷新到磁盘一次。
2:日志在每次事务提交后写入,并每秒刷新到磁盘一次。
(2)磁盘结构 (Disk Structures)
① 系统表空间 (System Tablespace )
系统表空间是更改缓冲区(Change Buffer)的存储区域。如果表是在系统表空间而不是独立表空间或通用表空间中创建的,它也可能包含表和索引数据。
参数:innodb_data_file_path
② 独立表空间(File-Per-Table Tablespaces)
包含单个innoDB表的数据和索引,并存储在文件系统上的单个数据文件中。
参数:innodb_file_per_table
③ 通用表空间(General Tablespaces)
需要通过CREATE TABLESPACE语法创建通用表空间,创建表时,可指定该表空间。
# 创建表空间
create tablespace ts_person add datafile 'myperson.ibd' engine = innodb;
# 建表
crate table person(id int, name varchar(20)) tablespace ts_person;
④ 撤销表空间 (Undo Tablespaces)
Mysql实例在初始化时会自动创建两个默认的undo表空间(初始大小16M),用于存储undo.log日志。
⑤ 临时表空间 (Temporary Tablespaces)
InnoDB使用会话临时表空间和全局临时表空间,存储用户创建的临时表等数据。
⑥ 双写缓冲区 (Doublewrite Buffer Files)
innoDB引擎将数据页从Buffer Pool刷新到磁盘前,先将数据页写入到缓冲区文件中,便于系统异常时恢复数据。*.dblwr
⑦ 重做日志区 (Redo Log)
用来实现事务的持久性。
(3)后台线程
后台线程作用:InnoDB存储引擎缓冲池当中的数据,在合适的时机,刷新到磁盘文件中。
① 核心后台线程 (Master Thread)
负责调度其他线程,还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性,还包括脏页的刷新、合并插入缓存、undo页的回收。
② IO线程(IO Thread)
大量使用AIO(异步IO)来处理IO请求,这样可以极大的提高数据库的性能,而IO Thread主要负责这些IO请求的回调。
线程类型 | 默认个数 | 职责 |
---|---|---|
Read Thread | 4 | 负责读操作 |
Write Thread | 4 | 负责写操作 |
Log Thread | 1 | 负责将日志缓冲区刷新到磁盘 |
Insert Buffer Thread | 1 | 负责将写缓冲区内容刷新到磁盘 |
③ Purge Thread
主要用于回收事务已经提交了的undo.log,在事务提交之后,undo log可能不用了,就用它来回收。
④ Page Cleaner Thread
协助Master Thread 刷新脏页到磁盘的线程,他可以减轻Master Thread的工作压力,减少阻塞
3、事务原理
(1)redo log(持久性)
重做日志,记录的是事务提交时数据页的物理修改,用来实现事务的持久性。
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者是在内存中,后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中。用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。
(2)undo log(原子性)
回滚日志,用于记录数据被修改前的信息,作用包含两个:提供回滚和MVCC。
undo log和redo log记录物理日志不一样,他是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert 记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。当执行rollback时,就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
undo log 销毁: undo log在事务执行时产生,事务提交时,并不会立即删除undo log,因为这些日志可能还用于MVCC.
undo log 存储:undo log采用段的方式进行管理和记录,存放在前面介绍的rollbck segment回滚段中,内部包含1024个undo log segment.
- 当insert的时候,产生的undo log日志只在回滚时需要,在事务提交后,可被立即删除。
- 当update、delete的时候,产生的undo log日志不仅仅在回滚时需要,在快照读时也需要,不会立即被删除。
4、MVCC
(1)基本概念
- 当前读
读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。对于我们日常的操作,如:select.lock in share mode(共享锁),select…for update、update、insert、delete(排他锁)都是一种当前读。 - 快照读
简单的select(不加锁)就是快照读,读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读。
Read Committed:每次select,都生成一个快照读。
Repeatable Read:开启事务后第一个selecti语句才是快照读的地方。
Serializable:快照读会退化为当前读。 - MVCC
全称Multi–Version Concurrency Control,多版本并发控制。指维护一个数据的多个版本,使得读写操作没有冲突,快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现,还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
(2)隐式字段
隐式字段 | 含义 |
---|---|
DB_TRX_ID | 最近修改事务ID,记录插入这条记录或最后一次修改该记录的事务ID |
DB_ROLL_PTR | 回滚指针,指向这条记录的上一个版本,用于配合undo log,指向上一个版本 |
DB_ROW_ID | 隐藏主键,如果表结构没有指向主键,将会生成改隐藏字段 |
(3)undo log版本链
不同事务或相同事务对同一条记录进行修改,会导致该记录的undo log生成一条记录版本链表,链表的头部是最新的旧记录,链表尾部是最早的旧记录。
(4)readView 读视图
ReadView 是快照读SQL执行时MVCC提取数据的依据,记录并维护系统当前活跃的事务(未提交的)id。
ReadView中包含了四个核心字段:
字段 | 含义 |
---|---|
m_ids | 当前活跃的事务ID集合 |
min_trx_id | 最小活跃事务ID |
max_trx_id | 预分配事务ID,当前最大事务ID+1 |
creator_trx_id | ReadView创建者的事务ID |
(5)实现原理
不同的隔离级别,生成ReadView的时机不同:
-
READ COMMITED(RC):在事务中每一次执行快照读时生成ReadView。
第一次读
第二次读
-
REPEATABLE READ(RR):尽在事务中第一次执行快照读时生成ReadView,后续服用该ReadView.
5、总结
七、MySQL管理
1、系统数据库
数据库 | 含义 |
---|---|
mysql | 存储MySQL服务器正常运行所需要的各种信息(时区、主从、用户、权限等) |
information_schema | 提供了访问数据库元数据的各种表和视图,包含数据库、表、字段类型及访问权限等 |
performance_schema | 为MySQL服务器运行状态提供了一个底层监控功能,主要用于收集数据库服务器性能参数 |
sys | 包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图 |