存储引擎是存储数据、建立索引、更新/查询数据等技术的实现。存储引擎是基于表的,而不是基于库的,所以也可以称为表类型。
在MySQL 5.5之后,作为默认的存储引擎,是兼顾高可靠性和高性能的通用存储引擎。
表空间文件形如xxx.ibd
,xxx
为表名,用于存储该表的表结构(frm, sdi)、数据以及索引
MyISAM为MySQL早期的默认引擎
支持表锁,访问速度较快
内存存放,hash索引
文件:sdi
索引是一种有序的数据结构,用于高效的获取数据。数据库维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以基于此实现高级查找算法,这就是索引。
创建索引
CREATE [UNIQUE|FULLTEXT] INDEX idx_index_name ON table_name (col1, ...)
删除索引
DROP INDEX idx_index_name ON table_name
查看索引
SHOW INDEX FROM table_name
常见
在B树的基础上进行修改,所有数据都放在叶子结点,并形成一个单向链表,树枝部分仅充当索引
MySQL对B+树进行修改,在叶子节点部分增加一个指向前一个节点的指针,形成双向链表,提高区间访问的性能,每一个节点都存储在一个Page中
使用Hash函数,算出键值对应的hash值,并映射到对应的hash表槽位上,记录数据值和行hash值,并采用链表解决hash冲突
Memory引擎,而InnoDB引擎具有自适应hash功能,hash索引是InnoDB根据B+树索引在指定条件下自动构建的
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对表中主键创建的索引 | 默认自动创建,唯一 | PRIMARY |
唯一索引 | 避免一张表中某列的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | - |
全文索引 | 全文索引查找的是文本中的关键词,而不是索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB引擎中,根据索引的存储形式,可分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引 (Clustered Index) | 将数据存储与索引放到了一块,索引结构的叶子结点保存了行数据 | 必要,唯一 |
二级索引 (Secondary Index) (辅助索引) | 将数据与索引分开存储,索引结构的叶子结点关联对应主键 | 可以存在多个 |
聚集索引选取规则:
回表查询:在二级索引中拿到聚集索引后,到聚集索引中查找
使用 show global status like 'Com_______'
来查看相关命令的执行次数
慢查询日志记录了所有执行时间超过指定参数 (long_query_time,unit: s, default: 10s) 的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启,需要在MySQL配置文件中配置如下信息:
# 开启慢查询日志
slow_query_log=1
# 设置慢查询的时间为2s
long_query_time=2
在/var/mysql
下会生成xxxx-slow.log
日志文件,通过相关命令查看
通过have_profiling
参数,可以查看当前数据库是否支持profile操作: SELECT @@have_profiling;
默认 profiling 是关闭的,可以通过set在 session / global 中开启: SET profiling = 1
通过show profiles
展示最近执行的SQL的Query_ID,耗时,语句
通过show profiles for query [Query_ID]
查看具体SQL的耗时
通过show profiles cpu for query [Query_ID]
查看具体SQL的CPU使用情况
在select语句前加上关键字 explain / desc
| :------: | :--: | | SIMPLE | 简单表,即不使用连接或子查询 | | PRIMARY | 主查询,即最外层的查询 | | UNION | UNION中第二个及以后的查询 | | SUBQUERY | SELECT/WHERE包含的子查询 |
对于关联了多列的联合索引,需要遵守最左前缀法则,即从索引的最左列开始,且不跳过索引中间列
有一个联合索引 idx_pro_age_status
关联字段 profession, age, status
想让其生效,则查询可以是:
select * from tb_user where profession = '土木工程';
select * from tb_user where profession = '土木工程' and age = 23;
select * from tb_user where profession = '土木工程' and age = 23 and status = 1;
MySQL 8.0版本开始增加了索引跳跃扫描的功能
当第一列索引的唯一值较少时,即使where条件没有第一列索引,查询的时候也可以用到联合索引
遇到 Index Skip Scan 得时候, 说明索引字段选择有问题, 应该创建一个新的合适索引
详见掘金
''
,索引失效
如果MySQL认为使用索引比全表扫描更慢,则不使用索引
有一索引idx_user_age
和对应字段age
,值为1-20
如查询条件为 where age >= 1
, where age >= 11
,则走全表扫描
而where age >= 12
开始走索引
SQL提示是在SQL语句中加入一些人为的提示来达到SQL优化的目的
在上面的联合索引之外,给profession
单独创建索引idx_user_pro
,在不使用SQL提示时,使用的是联合索引
select * from tb_user use index(idx_user_pro) where profession = '土木工程';
select * from tb_user ignore index(idx_user_pro) where profession = '土木工程';
select * from tb_user force index(idx_user_pro) where profession = '土木工程';
需要注意的是,除了focre强制使用外,use仅仅只是建议,是否使用MySQL会综合条件自行判断
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能找到),减少select *
的使用
> 如extra中出现: > > using index condition:查找使用了索引,但是需要回表查询,即索引不包含部分需要返回的列 > > using where; using index:查找使用了索引,所有数据都能在索引列中找到,不需要回表查询
当字段类型为字符串等时,可能会索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率
此时可以只将字符串的一部分前缀建立索引,这样可以优化索引空间,从而提高查询效率
表tb_user中存在varchar类型字段email
为其新建一个长度为10的前缀索引:create index idx_user_email_10 on tb_user (email(10));
前缀长度可以根据索引的选择性来设置:select count(distinct substring(email, 1, 10)) / count(*) from tb_user;
在业务场景中,如果存在多个查询条件,针对查询字段建立索引时,建议使用联合索引,避免回表查询
多条件联合查询时,MySQL优化器会评估哪个字段的索引效率更高,并选择该索引完成本次查询
values(),(),()
,且不超过1000条
一次性插入大批量数据,使用insert语句的性能较低,可使用MySQL提供的load
指令进行插入
连接时加上--local-infile
参数:mysql --local-infile -u root -p
设置全局local_infile
为1:set global local_infile=1
执行load
指令将准备好的数据加载到表结构中:
load data local infile './sql/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
MERGE_THRESHOLD
(默认50%) 时,InnoDB会开始寻找它前后的页,并判断是否可以将两个页合并,以优化空间使用
Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区中完成排序操作,所有不是通过索引直接返回排序结果的排序都是 FileSort 排序
Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外资源,效率高
sort_buffer_size
(默认256K)limit 2000000, 10
需要查询前 2000010 条数据,仅返回最后10条,其余丢弃,因此查询的开销非常大
可以通过覆盖索引和子查询来进行优化
select prod.*
from tb_prod prod,
# 子查询,通过order by拿到id,再通过id覆盖查询拿到数据
(select id from tb_prod order by id limit 2000000, 10) pageProds
where prod.id = pageProds.id;
> 这种方法和直接查询仅仅只是减少了列的查询,因为id字段使用的空间和整行相比较小,相当于两次覆盖查询,一次查id,一次拿行数据
MyISAM:将一个表的总行数存在磁盘上,在不加条件 count(*) 时会直接返回这个数
InnoDB:一行一行读取并累加
优化:自行计数
总结:count(字段) < count(主键) < count(1) ≈ count(*)
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁
因此如果update的条件字段没有索引,则会对整张表加锁,影响并发性能,需要对相应字段加锁
视图View是一种虚拟存在的表
视图中的数据并不在数据库中真实存在,行和列的数据来自于定义视图的查询中使用的表(基表),是在使用视图时动态生成的
视图只保留了查询的SQL逻辑,不保存查询结果
创建
CREATE [OR REPLACE] VIEW 视图名称(列) AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
删除
DROP VIEW [IF EXISTS] 视图名称1 [, 2, 3...]
修改
# 方法一
CREATE OR REPLACE VIEW 视图名称[(列)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
# 方法二
ALTER VIEW 视图名称[(列)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
查询
查看创建视图语句:SHOW CREATE VIEW 视图名称
查看视图语句(看做表):SELECT * FROM 视图名称
当使用WITH CHEAK OPTION
子句创建视图时,MySQL会通过视图检查修改的每一个行,以使其符合视图的定义
MySQL允许基于另一个视图创建视图,它还会检查充当基表的视图中的规则以保持一致性
为了确定检查的范围,MySQL提供了两个选项:CASCADED
和LOCAL
,默认为CASCADED
要使视图可更新,需要满足视图中的行与基础表中的行之间存在一对一的关系(即视图中的行并非计算得来)
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据库和服务器之间的传输次数,提高数据处理效率
存储过程就是SQL语言层面的代码封装与重用(函数)
创建
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
# SQL语句
END;
调用
CALL 存储过程名(参数列表);
查看
# 查询指定存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;
# 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';
删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;
系统变量是MySQL提供的,不是用户定义的,属于服务器层面
分为全局变量(GLOBAL)和会话变量(SESSION)
查看系统变量
# 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIBLES;
# 模糊匹配查找系统变量
SHOW [SESSION | GLOBAL] VARIBALES LIKE 'xxx';
# 查看指定变量 没有空格
SELECT @@[SESSION. | GLOBAL.]系统变量名;
设置系统变量
SET [SESSION | GLOBAL] 系统变量名 = VAL;
SET @@[SESSION. | GLOBAL.]系统变量名 = VAL;
如果没有指定SESSION/GLOBAL,默认SESSION
MySQL服务重新启动后,所有变量会失效,除非在/etc/my.cnf文件中配置
用户定义变量是用户根据需要自己定义的变量,用户变量不用提前声明,如未声明,则为NULL
作用域为当前连接
赋值
SET @var_name = expr [, @var_name2 = expr2, ...];
SET @var_name := expr [, @var_name2 = expr2, ...];
SELECT @var_name := expr [, @var_name2 = expr2, ...];
SELECT field_name INTO @var_name FROM table_name
使用
SELECT @var_name [, @var_name2, ...];
局部变量是根据需要定义在局部生效的变量,访问之前需要DECLARE
声
可用作存储过程内的局部变量和输入参数,有效范围在其声明的BEGIN...END
块内
声明
DECLARE 变量名 类型 [DEFAULT = xxx];
赋值
SET var_name = expr;
SET var_name := expr;
SELECT field_name INTO var_name FROM table_name
类型 | 含义 |
---|---|
IN (默认) | 作为入参 |
OUT | 返回值 |
INOUT | 既可以作为入参,也可作为返回 |
用法:
CREATE PROCEDURE 存储过程名称(IN/OUT/INOUT 参数名 参数类型)
BEGIN
...
END;
IF
IF CONDITION1 THEN
...
ELSEIF CONDITION2 THEN
...
ELSE
...
END IF;
CASE
CASE [CONDITION_VAL]
WHEN CASE1 THEN ...
[WHEN CASE2 THEN ...
...
ELSE ...]
END CASE;
WHILE
WHILE CONDITION
DO
...
END WHILE;
REPEAT
REPEAT
...
# 满足则退出, do while
UNTIL CONDITION
END REPEAT;
LOOP
[begin_label]: LOOP
...
END LOOP [end_label];
# 退出指定的循环 break@label
LEAVE label;
# 进入下一次循环 continue@label;
ITERATE label;
游标(CURSOR)是用来存储查询结果集的数据类型(迭代器),在存储过程和函数中可以使用游标对结果集进行循环的处理
游标的使用包括游标的声明、OPEN、FETCH、CLOSE
声明
DECLARE 游标名称 CURSOR FOR 查询语句
打开游标
OPEN 游标名称
获取游标记录
FETCH 游标名称 INTO 变量 [, var2, ...]
关闭游标
CLOSE 游标名称;
条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时的处理方案(try..catch)
DECLARE handler_action HANDLER FOR condition [, condition2, ...] statement;
-- 参数说明
hander_action:
# 继续
CONTINUE
# 终止
EXIT
condition:
# 状态码
SQLSTATE sqlstate_value
# 所有以01开头的状态码简写
SQLWARNING
# 所有以02开头的状态码
NOT FOUND
# 所有没有被上面捕获的状态码
SQLEXCEPTION
存储函数是有返回值的存储过程,存储函数的参数只能是IN类型的
定义
CREATE FUNCTION 函数名(参数)
RETURNS type characteristic
BEGIN
...
RETURN ...
END;
-- 参数说明
characteristic:
# 相同的入参总是产生相同的结果
DETERMINSTIC
# 不包含SQL语句
NO SQL
# 包含读取数据的语句,但不包含增删改的语句
READS SQL DATA
使用
select fun(111);
其他同理
触发器是与表有关的数据库对象,在增删改之前或之后,触发并执行触发器中定义的SQL语句集合(事件监听器)
可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作
使用别名 OLD (原来的数据) 和 NEW (新的数据) 来引用触发器中发生变化的记录内容,这与其他数据库是类似的
只支持行级触发,不支持语句触发
类型 | 解释 |
---|---|
INSERT | NEW 将要或已新增的数据 |
UPDATE | OLD 旧数据 NEW 新数据 |
DELETE | OLD 将要或已删除的数据 |
创建
CREATE TRIGGER trigger)name
BEFORE/AFTER INSERT/UPDATE/DELETE
# EACH ROW 行级触发器
ON table_name FOR EACH ROW
BEGIN
...
END;
查看
SHOW TRIGGERS;
删除
# 不指定数据库默认为当前
DROP TRIGGER [schema_name.]trigger_name;
MySQL中的锁,按粒度分为三类:
加全局锁后,整个数据库实例处于只读状态,后续的DDL,DML,事务提交将被阻塞
典型使用场景是做全库的逻辑备份 (mysqldump),对所有表进行锁定,从而获得一致性视图,保证数据完整性
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES
加全局锁,是一个比较重的操作,存在以下问题:
在InnoDB中,可以在备份时加上参数--single-transaction
来完成不加锁的一致性数据备份
> InnoDB底层通过快照读实现
分为两类:
LOCK TABLES table_name... READ/WRITE;
UNLOCK TABLES
/ 客户端断开连接元数据锁(Meta Data Lock,MDL)主要作用是维护表元数据的数据一致性,在表上有活动事务时,不可以对元数据进行写入操作
MDL加锁过程是系统自动控制,无需显式使用
避免DML与DDL冲突,保证读写的正确性
在MySQL 5.5中引入MDL,当对一张表进行CRUD时,加MDL读锁(共享);当对表结构进行变更操作时,加MDL写锁(排他)
> 元数据简单理解为表结构
SQL | 锁类型 | 说明 |
---|---|---|
lock tables xxx read/write | SHARED_READ_ONLY / SHARED_NO_READ_WRITE | - |
select | select ... lock in share mode | SHARED_READ | 与SHARED_READ、SHARED_WRITE兼容 |
insert | update | delete | select ... for update | SHARED_WRITE | 与SHARED_READ、SHARED_WRITE兼容 |
alter table | EXCLUSIVE | 与其他MDL互斥 |
查看库中的元数据锁:
select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;
为了避免DML在执行时,加的行锁与表锁之间的冲突,InnoDB引入了意向锁
意向锁使得表锁不用检查每一行数据是否加锁,减少了表锁的检查
流程:A在加行锁之后,给整张表加意向锁;B在加表锁时,检查意向锁,如果锁兼容,则加表锁,否则阻塞
意向锁分为两种:
类型 | SQL | 兼容性 |
---|---|---|
意向共享锁 IS | select ... lock in share mode | 与表锁共享锁兼容,与表锁排它锁互斥 |
意向排它锁 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;
行级锁每次锁住对应的行数据,粒度最小,发生锁冲突的概率最低,并发程度最高
InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而非对记录加锁
行级锁主要分为三类:
InnoDB提供以下两种类型的行锁:
CRUD加锁情况:
SQL | 锁类型 | 说明 |
---|---|---|
INSERT | X | 自动加锁 |
UPDATE | X | 自动加锁 |
DELETE | X | 自动加锁 |
SELECT | - | - |
SELECT ... LOCK IN SHARE MODE | S | 手动 |
SELECT ... FOR UPDATE | X |
默认情况下,InnoDB在 rr 级别运行,使用Next-Key锁进行搜索和索引扫描,以防止幻读
查看表中的行锁 (意向锁):
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
默认情况下,InnoDB在 rr 级别运行,使用临键锁进行搜索和索引扫描,以防止幻读
> 间隙锁唯一的目的是防止其他事务插入间隙,造成幻读 > > 间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁
从MySQL 5.5版本开始,默认使用InnoDB存储引擎,它擅长事务处理,具有崩溃恢复特性,在日常开发中使用非常广泛
adaptive_hash_index
innodb_log_buffer_size
日志刷新到磁盘时机:innodb_flush_log_at_trx_commit
1: 日志在每次事务提交时写入并刷新到磁盘 0: 每秒写入并刷新 2: 包含0, 1
略
ACID中的Durability是由redo log
保证的
重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性的
该日志文件由两部分组成:重做日志缓冲(redo log buffer) 和 重做日志文件 (redo log file),前者在内存中,后者在磁盘中
事务提交后会把所有修改信息都保存到该日志文件中,用于在刷新脏页到磁盘发生错误时,进行数据恢复使用
ACID中的Atomicity是由undo log
保证的
回滚日志,用于记录数据被修改前的信息,包含两个作用:Rollback
(回滚) 和 MVCC
(多版本并发控制)
undo log 和 redo log 记录物理日志不一样,它是逻辑日志
当delete一条记录时,undo log中会记录一条对应的insert日志;当update一条记录时,会记录一条相反的update记录
因此当执行rollback时,可以从undo log中读到相应的内容并进行回滚
rollback segment
回滚段中,内部包含1024个undo log segment
MVCC + 锁,实现了事务的隔离性
一致性则是由 redo log 与 undo log 保证。
select ... lock in share mode
, select ... for update
, update
, insert
, delete
都是一种当前读
Multi-Version Concurrency Control
指维护一个数据的多个版本,使得读写操作没有冲突,快照读为MySQL实现MVCC提供了一个非阻塞读功能
MVCC的具体实现还需要依赖数据库记录中的三个隐式字段、undo log日志、readView
指的是row中的 DB_TRX_ID
, DB_ROLL_PTR
, DB_ROW_ID
字段 | 说明 |
---|---|
DB_TRX_ID | 最近修改事务id,记录插入这条记录或最后一次修改该记录的事务id |
DB_ROLL_PTR | 回滚指针,指向这条记录的上一个版本,配合 undo log 使用 |
DB_ROW_ID | 隐藏主键,如果表结构没有指定主键,将会生成该字段 |
在insert时,产生的undo log日志只在回滚时需要,在事务提交后,可以被立即删除
在update、delete的时候,产生的undo log日志不仅在回滚时需要,在快照读时也需要,不会被立即删除
不同事务或相同事务对同一条记录进行修改,会导致该记录的undo log生成一条记录版本的链表
链表的头部是最新的旧记录,链表的尾部是最早的旧记录
读视图ReadView
,是快照读SQL执行时MVCC提取数据的依据,记录并维护当前活跃的事务(未提交)的id
包含四个核心字段:
字段 | 说明 |
---|---|
m_ids | 当前活跃(未提交)事务的ID集合 |
min_trx_id | 最小活跃事务ID |
max_trx_id | 预分配事务ID,当前最大事务ID+1(事务ID自增) |
creator_trx_id | ReadView创建者的事务ID |
不同的隔离级别,生成ReadView的时机不同:
条件 | 访问权 | 说明 |
---|---|---|
trx_id == creator_trx_id | 可以访问该版本 | 说明数据是当前这个事务更改的 |
trx_id < min_trx_id | 可以访问该版本 | 说明数据已经提交 |
trx_id > max_trx_id | 不可以访问该版本 | 说明该事务是在ReadView生成后才开启 |
min_trx_id <= trx_id <= max_trx_id | 如果trx_id不在m_ids中,可以访问该版本的 | 说明数据已经提交 |