南宁网站制作系统,app开发程序,苏州网络公司策划,王者做网站二、进阶篇#x1f6a9;
1. 存储引擎#x1f346;
1.1 MSQL体系结构 连接层#xff1a; 连接处理#xff0c;连接认证#xff0c;每个客户端的权限
服务层#xff1a; 绝大部分核心功能#xff0c;可跨存储引擎
可插拔存储引擎#xff1a; 需要的时候可以添加或拔掉…二、进阶篇
1. 存储引擎
1.1 MSQL体系结构 连接层 连接处理连接认证每个客户端的权限
服务层 绝大部分核心功能可跨存储引擎
可插拔存储引擎 需要的时候可以添加或拔掉数据存储和提取的方式数据库服务器通过API与其进行交互。**索引在这一层实现不同的存储引擎有不同的索引实现方式。**InnoDB是MySQL5.5之后默认的存储引擎。
存储层 数据存储在磁盘文件中
1.2 存储引擎
1.2.1 简介
发动机不同的场景需要不同的引擎。**存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。**存储引擎是基于表的不是基于库的也可被称为表类型。
# 在创建表时指定存储引擎
creat table 表名(字段名 字段类型 [comment 字段注释],……字段名 字段类型 [comment 字段注释]
)ENGINE INNODB [comment 表注释];# 查询当前数据库支持的存储引擎
SHOW ENGINES;1.2.2 常见的存储引擎
InnoDB 1介绍兼顾高可靠和高性能的通用存储引擎 2特点 DML操作遵循ACID模型支持事务行级锁 提高并发访问性能支持外键 FOREIGN KEY约束保证数据 3文件文件名.ibd每张表参数innodb_file_per_table都会有一个表空间文件存储表结构frm,sdi、数据和索引。 4逻辑结构表空间Tablespace-段Segment-区Extent-页page-行Rowpage是磁盘操作的最小单元
MYISAM
1介绍MYISAM早期MySQL的默认存储器2特点 不支持事务表锁不支持行锁不支持外键 3文件 文件名.sdi表结构文件名.MYD数据文件名.MDI索引
MEMORY
1介绍存储在内存中会受到硬件、断电问题的影响。只能将这些表作为临时表或缓存使用2特点 内存存放速度快hash索引 3文件文件名.sdi
1.2.3 存储引擎的选择
根据特点选对于复杂系统可以使用多种存储引擎进行组合
InnoDB 要求并发、数据操作有插入、查询外还有很多更新、删除操作
MyISAM 主要是读和插入数据——评论等——MongoDB代替
MEMORY 访问速度快太大的不能缓存在内存中且无法保障数据的安全性——Redis代替
2. 索引
2.1 索引概述
概念 索引是一种数据结构是帮助MySQL高效获取数据的
无索引——全表扫描有索引——查找更高效
优点
提高数据检索的效率降低数据库的IO成本通过索引列对数据进行排序降低数据排序成本降低CPU消耗
缺点
占用空间降低更新表的速度对表进行插入、更新、删除时效率低
2.2 索引结构
不同的存储引擎有不同的结构
BTree索引最常见大部分引擎都支持——InnoDB、MyISAMHash索引 底层是哈希表实现只有精确匹配索引列的查询才有效不支持范围查询——MemoryR-tree空间索引MyISAM引擎的一个特殊索引类型主要用于地理空间数据类型Full-text全文索引通过建立倒排索引快速匹配文档的方式
1BTree所有元素会出现在叶子节点形成链表非叶子节点只是起索引作用MySQL中进行了优化是双向循环链表
2Hash采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中
注InnoDB中有自适应hash功能自动根据BTree索引构建hash索引
2.3 索引分类
分类
分类含义特点关键字主键索引针对表中主键创建的索引默认自动创建只能有一个PRIMARY唯一索引避免同一个表中某列数据中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引找文本中的关键词可以有多个FULLTEXT
InnoDB中的两种索引
聚集索引 索引结构的叶子节点保存了行数据——必须有而且只有一个
二级索引 数据与索引分开存放索引结构的叶子节点关联的是对应的主键——可以存在多个
回表查询——根据查询条件在二级索引找到查询的索引值回到聚集索引中找到整行数据的信息
2.4 索引语法
# 创建索引
CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ……); -- 索引列可以是单列也可以是多列多列就是联合索引
-- 索引名称idx_表名_列名# 查看索引
SHOW INDEX FROM table_name; -- 指定表的所有索引# 删除索引
DROP INDEX index_name ON table_name;2.5 SQL索引性能分析
主要是查询
1SQL执行频率——查看表中哪种操作的频率高
show global status like Com_______;2慢查询日志
定位哪些SQL语句的执行效率低从而对这类型语句进行优化 慢查询日志在/var/lib/mysql/localhost-slow.log
慢查询日志配置 慢查询日志默认没有开启配置文件存放在/etc/my.cnf中 systemctl restart mysqld # 开启MySQL慢日志查询开关
slow_query_log1
# 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志
long_query_time2注Ubuntu中
启动MySQLservice mysqld start
停止MySQLservice mysqld stop
重启MySQLservice mysqld restart
开启慢查询set global slow_query_logON;——临时开避免在配置文件中进行修改
3profile详情
# 在做SQL优化的时候帮助了解时间都耗费在哪里
show profiles;
# 查看当前MySQL是否支持profiles
SELECT have_profiling;SELECT profiling; # 0没有开关# 开启
set profiling 1;
SELECT profiling; # 1# 查看执行的SQL语句的耗时情况
show profiles;# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile fpr query query_id;# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;4explain执行计划
explain/desc命令获取MySQL如何执行SELECT语句的信息包括select语句执行过程中表如何连接和连接的顺序
# 直接在select语句之前加上关键字explain / desc
explain select 字段列表 from 表名 where 条件;2.6 索引使用
-----where之后的-----
1最左前缀法则——联合索引复合索引
查询从索引的最左列开始并且不跳过索引中的列如果跳过某一列索引将部分失效后面的字段索引失效——创建的时候最左边的字段只要存在就会启动索引不考虑在selecet中字段排列的前后顺序
2范围查询
出现,这样的范围查询后面的列索引将会失效。可以使用或
3索引列运算
不要再索引列上进行运算操作否则索引将失效
4字符串不加引号
字符串不加单引号存在隐式类型转换——自动类型转换之后索引就失效了
5迷糊查询
如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效
6or连接的条件
or前后连接的都有索引索引才会被用到否则涉及的索引都不会被用到
7数据分布影响
如果MySQL评估使用索引比全表更慢则不使用索引
-----from之后的-----
8SQL提示——操作者提示SQL
在SQL语句中加入一些人为的提示来达到优化操作的目的
# 使用索引
explain select * from 表名 use index(索引名) where 条件判断
# 忽略索引
explain select * from 表名 ignore index(索引名) where 条件判断
# 强制索引
explain select * from 表名 force index(索引名) where 判断条件-----select之后的-----
9覆盖索引
查询使用了索引并且需要返回的列在该索引中已经全部能够找到减少select*
会在explain的最后一列Extra中显示——正常没有使用条件中包含索引的列会显示Using where; Using index性能比较高不需要回表查询否则出现Using index condition性能比较低回表查询了NULL是回表查询
select * 减少使用极易出现回表查询降低性能
10前缀索引——空间和效率的平衡
如果字符串类型很长的时候直接建立索引会变得非常大查询得时候会浪费大量得磁盘IO影响查询效率。——只将字符串得一部分前缀建立索引节约索引空间提高索引效率
# 前缀的长度可以根据索引的选择性来决定而选择性是指不重复的索引值和数据表的记录总数的比值索引的选择性越高则查询效率越高
# 不重复数/总数选择性
# 唯一索引的选择性是1这是最好的索引选择性性能也是最好的。
create index 索引名 on 表名(column(n));11单列索引和联合索引的选择
看select需要的数据如果只要一列那单列索引就可以否则还需要回表查询就转战联合索引。
2.7 索引设计原则
1针对数据量大百万级且查询比较频繁的表建立索引
2针对常作为查询条件where、排序order by、分组group by操作的字段建立索引
3尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高
4如果是字符串类型的字段字段的长度较长可以针对字段的特点建立前缀索引
5尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表
6控制索引的数量数量越多维护索引结构的代价就越大会影响增删改的效率
7如果索引列不能存储NULL要在见表的时候使用NOT NULL进行约束。优化器知道每列是否包含NULL可以更好地切丁哪个索引最有效地用于查询
3. SQL优化
3.1 插入数据
传统一条一条插入
1批量插入500-1000条一次
2手动提交事务
3主键顺序插入
4大批量插入数据——insert性能较低可以使用MySQL数据库提供地load指令进行插入
# 客户端连接服务器时加上参数--local-infile
mysql --local-infile -u root -p;# 设置全局参数local-infile为1开启从本地加载文件导入数据地开关
set global local_infile 1;# 执行load指令将准备好地数据加载到表结构中
load data local infile /root/sql/tb_sku1.sql数据源地址 into table tb_sku表明 fields terminated by , lines terminated by \n;3.2 主键优化
顺序插入比乱序插入(页分裂删除数据时页合并)快
InnoDB存储引擎中数据组织方式按主键顺序组织存放这种存储方式地表称为索引组织表index organized table, IOT
主键设计原则
1在满足业务需求的情况下尽量降低主键的长度
主键作为二级索引的叶子节点如果太长会占用空间搜索时耗费磁盘IO
2插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键
3尽量不要使用UUIDJAVA中生成随机字符串的一个类方法做主键或者其他自然主键
4业务操作时避免对主键的修改
3.3 order by 优化
分类
1Using filesort通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sort buffer中完成排序操作——通过索引不直接返回排序结果——创建联合索引防止索引失效——创建索引的时候可以指定排序方式
2Using index通过有序索引顺序扫描直接返回有序数据——不需要额外排序
优化
1根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则
2尽量使用覆盖索引
3多字段排序一个升序一个降序可以在联合索引创建时建立规则ASC/DESC
4如果不可避免出现filesort大量数据排序时可以适当增大排序缓冲区的大小sort_buffer_size默认256K
3.4 group by 优化
在分组操作时可以通过索引来提高效率
分组操作时索引的使用也满足最左前缀法则
3.5 limit 优化
问题 大数据量的时候越往后效率越低
优化
覆盖查询——避免回表 子查询形式——其实也是在回表阶段给简化了
3.6 count 优化
问题 InnoDB引擎执行count(*)的时候需要把数据一行一行地从引擎中读出来然后累积计数
注 MyISAM引擎把一个表地总行数存在了磁盘上没有where条件地时候就会直接返回这个数效率很高。
优化思路 自己计数在Redis中保存一个数来记录
count的几种用法
count()会一行一行数据进行判断如果count函数的参数不是NULL累计值就加1否则不加
用法
count(*) ——总记录数不取值直接累加
count(主键) ——直接累加不用判断null
count(字段) ——没加NOT NUL约束如果NULL不加
count(1) ——InnoDB引擎遍历整张表但不取值。服务层对于返回的每一行放一个数字“1”直接按行进行累加。
优化 count(*) ~ count(1) count(主键id) count(字段)
3.7 update 优化
没有索引就会锁住整张表
优化 根据索引字段进行更新并且索引字段不能失效否则行锁就会升级为表锁影响并发性能
4. 视图/存储过程/触发器——存储对象
4.1 视图
概念 虚拟存在的表(可以像操作表一样对视图进行操作)使用视图时是动态生成的——视图只保存了查询的SQL逻辑不保存查询结果。
操作
# 创建视图
create or replace view 视图名[(列名列表)] as select …… [with[cascaded|local] check option];
# [with[cascaded|local] check option]
# 如果加了检查语句通过检查语句的记录才会被添加如果不加检查语句任意一条记录都可以被添加# 查询
-- 查看创建视图语句
show create view 视图名称;
-- 查看视图数据
select * from 视图名称;# 修改
-- 新建或者替换
create or replace view 视图名称[(列名列表)] as select语句 [with[cascaded|local] check option];
-- 真正的修改
alter view 视图名称[(列名列表)] as select语句 [with[cascaded|local] check option];# 删除
drop view [if exists] 视图名称 [,视图名称] ……视图的检查选项
cascaded级联 创建视图时如果指定了cascaded选项这个视图基于的视图都会被检查基于创建了检查选项的视图创建的心视图如果没有设置检查选项本条不会被检查。传递的——with cascaded check option with check optionlocal 创建视图时如果指定了local选项这个视图基于的视图如果定义了检查选项那就检查一下如果没有定义那就不检查了——不传递不会把自己的带给自己基于的视图
视图的更新——修改视图会对原表的数据进行修改
条件 视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项视图不可更新①聚合函数/窗口函数SUM()、MIN()、MAX()、COUNT();②Distinct③Group by④having⑤union或union all
视图作用
简单 经常使用的查询可以定义为视图之后的操作每次就不用指定全部的条件了安全 数据库不能授权到特定的行/列上。视图可以让用户看到只让他们查询或修改的数据数据独立 屏蔽基表的结构变化对查询带来的影响
4.2 存储过程
概念 多次操作数据库涉及多次网络请求——在MySQL中将多个操作放在一个集合中用的时候去调这个集合数据库中可以有很多个这样的集合减少数据在数据库和应用服务器之间的传输提高数据处理的效率。
缺点 存储过程难以调用和扩展更没有移植性
特点
封装、复用可以接收参数也可以返回数据可以见减少应用服务和数据库之间的网络交互提升效率
存储过程
# 创建
create procedure 存储过程名称([参数列表])
begin--SQL语句
end;# 在命令行下创建存储过程时要通过关键字delimiter指定SQL语句的结束符
-- 因为SQL语句中包含分号命令行会以为输入结束但实际创建指令还没输入完成
delimiter $$ -- 会以$$为结束符
create procedure cout_user ()
beginselect count(*) from user;
end$$# 调用
call 名称([参数]);# 存储过程——计算user表中的总记录数
create procedure cout_user ()
beginselect count(*) from user;
end;-- 调用
call cout_user();-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA test;-- 查看定义存储过程时的SQL语句
show create procedure cout_user;-- 删除
drop procedure if exists cout_user;存储过程中的语法
1变量
系统变量 MySQL服务器提供的不是用户定义的。分为全局变量GLOBAL、会话变量SESSION
# 查看
show [session|global] variables; -- 查看所有系统变量默认是session。session就是本次会话global就是其全部会话但是重启后就回复默认永久修改要修改配置文件
show [session|global] variables like ……; -- 可以通过LIKE模糊匹配的方式查找变量
select [session|global] 系统变量名; -- 查看指定变量的值# 设置系统变量
set [session|global] 系统变量名 值;
set [session|global]系统变量名 值;用户定义变量 不用提前声明在用的时候直接’变量名‘使用作用域为当前连接
# 赋值
-- 赋值
set myname xy;
set myage : 18; # 推荐使用与比较运算符区分
set mygender 女, myhobby : java;select mycolor : blue;
select count(*) into mycount from user;-- 使用
select myname, myage, mygender, myhobby;select mycolor, mycount;局部变量 根据需要定义的在局部生效的变量访问前需要declare声明。可用作存储过程内的局部变量和输入参数作用范围是begin……end
create procedure p1()
begin# 声明declare stu_count int default 0;# 赋值set stu_count : 100;select count(*) into stu_count from user;select stu_count;
end;call p1();2if判断
if 条件1 then……
elseif 条件2 then……
else……
end if;3参数
# 参数类型
-- IN 该类参数作为输入也就是需要调用时传入值
-- OUT 该类参数作为输出也就是该参数可以作为返回值
-- INOUT 既可以作为输入参数也可以作为输出参数# 用法
create procedure 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
begin-- SQL语句
end;4case
# 语法一
case case_valuewhen 条件一 then ……else ……
end case;# 语法二
casewhen 条件一 then ……else ……
end case;5 while
# 语法
# 先判断条件如果条件为true则执行逻辑否则不执行逻辑
WHILE 条件 DOSQL逻辑
END WHILE;-- 计算1-n的和
create procedure p(in n int)
begindeclare total int default 0;while n 0 doset total : total n;set n : n - 1;end while;select total;
end;
call p(100);6repeat——满足条件则退出循环
repeatSQL逻辑until 条件
end repeat;7loop
[begin_label:] loopSQL逻辑end loop [end_label];
LEAVE label; -- 退出指定标记的循环体相当于break
ITERATE label; -- 直接进入下一次循环相当于continue8游标
概念 用来存储查询结果集的数据类型 在存储过程和函数中可以使用游标对结果集进行循环处理。类似集合、list
内容 声明、OPEN、FETCH、CLOSE
# 声明游标 注先声明普通变量再声明游标
declare 游标名称 cursor for 查询语句; -- 将查询语句的结果存在游标中# 打开游标
open 游标名称;# 获取游标记录
fetch 游标名称 into 变量 [, 变量]; -- 将游标中包含的多条数据记录每次提取一条配合while使用# 关闭游标
close 游标名称;9条件处理程序
用来定义再流程控制结构执行过程中遇到问题时相应的处理步骤
# 声明
declare handler_action handler for condition_value [, condition_value] … statement;/*
handler_actioncontinue 继续执行当前程序exit 终止执行当前程序
condition_valuesqlstate 状态码sqlwarning 所有以01开头的状态码的简写not found 所有以02开头的状态码的简写sqlexception 没有被以上两种捕获的状态
*/10存储函数——存储过程的特殊情况参数为in必须有返回值独特的是他有自己的语法
create function 存储函数名称([参数列表])
returns type [characteristic…]
beginSQL语句return……;
end;/*
characteristic说明——必须有否则会报错
deterministic相同的输入参数总是产生相同的结果
no sql不包含sql语句
reads sql data包含读取数据的语句但不包含写入数据的语句
*/4.3 触发器
1概念——数据库管理员可以监控到所有用户操作数据库的每一条操作信息
触发器是与表有关的数据库对象在insert/update/delete之前或之后触发并执行触发器中定义的SQL语句集合
作用 在数据库端确保数据的完整性、日志记录、数据校验等操作
使用别名OLD和NEW来引用触发器中发生变化的记录内容
注 现在触发器只支持行级触发器执行一个SQL语句影响了多少行就执行多少次不支持语句级触发器执行一个SQL语句不管影响了多少行只执行一次 before——在操作数据前 对数据进行合法性检查
2语法
# 创建
create trigger 触发器的名字
before/after insert/update/delete
on 表名 for each row -- 行级触发器
begintrigger_stmt;
end;# 查看
show trigger;# 删除
drop trigger [schema_name.]trigger_name; -- 如果没有指定schema_name默认是当前数据库3应用场景
记录操作日志数据校验
5. 锁
5.1 概述
计算机协调多个进程或线程并发访问某一资源的机制数据库中的锁也是想让共享数据能保证并发访问的一致性和有效性
锁冲突也是影响数据库并发访问性能的一个重要因素
分类——粒度
全局锁 锁住数据库中的所有表表级锁 每次操作锁住整张表行级锁 每次操作锁住对应的行数据
5.2 全局锁
应用场景 全库的逻辑备份对所有表进行锁定从而获取一致性视图保证数据的完整性
# 加锁
flush tables with read lock;
# 备份操作
mysqldump -u 用户名 -p 密码 数据库名 备份文件名 -- 不是sql语句命令行就行不用在mysql中执行
# 释放锁
unlock tables;问题
如果在主库上备份备份期间都不执行更新业务停摆如果在从库上备份在备份期间从库不能执行主库同步过来的二进制日志导致主从延迟
InnoDB中不加锁实现一致性数据备份
# 在备份时加入参数--single-transaction
mysqldump --single-transaction -uroot -p 123456 数据库名 备份文件名5.3 表级锁
锁定粒度大发生锁冲突的概率最高并发度最低
分类
1表锁
表共享读锁read lock只能读不能写不会阻塞其他客户端的读会阻塞其他客户端的写释放掉锁之后就可以执行写表独占写锁write lock能读能写其他客户端不能读也不能写
# 加锁
lock tables 表名可以是多个表 read/write;
# 释放锁
unlock tables; / 客户端断开连接2元数据锁meta data lockMDL
系统自己加的在有事务的时候事务中进行查询系统就会自动加上共享读锁SHARED_READ另一个事务进行插入、更新、删除操作的时候系统会自动加上共享写锁SHARED_WRITE他俩是兼容的事务提交后就释放掉。但是修改表结构也就是alter table的时候系统会加上EXCLUSIVE锁是排他锁可以理解为修改表结构那么增删改查都不能同时进行的。
# 查看元数据锁的语法
select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;3意向锁
解决在加入行锁的时候对表加上意向锁再来一个线程想要对该表进行加锁那么就检查想要加的锁和意向锁列表能不能是兼容的能兼容就加互斥的情况就阻塞等待着
意向锁分类
意向共享锁IS与表锁共享锁read兼容与表锁排他锁write互斥意向排他锁IX与表锁共享锁read及排他锁write都互斥。意向锁之间不会互斥
# 查看意向锁及行锁的加锁情况
select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.data_locks;5.4 行级锁
概念 每次操作锁住对应的行数据。锁粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中
InnoDB数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。——如果不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时会升级为表锁
分类 行锁Record Lock锁定单个行记录防止其他事务对此行进行update和delete。在RC和RR隔离级别下都支持——事务开始事务自动加锁事务提交自动释放锁。 共享锁S允许一个事务读一杠其他事务不能来排他锁 排他锁X这一行不能有共享锁和排他锁啦其他行请随意 间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持 索引上的等值查询唯一索引给不存在的记录加锁时优化为间隙锁 索引上的等值查询普通索引最后一个值不满足查询需求退化为间隙锁——前后都加本条记录也加 索引上的范围查询唯一索引访问到第一个不满足条件的值为止 目的防止其他事务插入间隙 临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持——避免幻读现象
6. InnoDB引擎️
6.1 逻辑存储结构 6.2 架构
擅长事务处理具有崩溃恢复特性
InnoDB架构 内存
Buffer Pool 缓冲池缓存磁盘上经常操作的真实数据在执行增删改查操作时先操作缓冲池中的数据如果缓冲池中没有就从磁盘加载并缓存然后再以一定的频率刷新到磁盘从而减少磁盘IO加快处理速度
缓冲池是以page页为单位底层采用链表数据结构管理Page不同小块的颜色表示不同的Page类型free page表示空闲page未被使用clean page表示被使用page数据没有被修改过dirty page表示脏页是被使用过的数据被修改过并且与磁盘数据产生了不一致
Change Buffer 更改缓冲区针对于非唯一二级索引页——插入顺序是相对随机的删除和更新可能会影响索引树中不相邻的二级索引页也就是如果操作的数据没在Buffer pool那就先修改到change Buffer中等到bufferpool中有了再一起合并到Buffer pool然后等待机会刷新到磁盘中
Adaptive Hash Index 自适应哈希索引InnoDB是不支持这个索引的他是用来优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询如果察觉到hash可以提升速度没那就建立hash索引无需人工干预。参数是adaptive_hash_index
log buffer: 日志缓冲区用来保存要写入到磁盘中的log日志数据redo log、undo log默认大小是16MB日志缓冲区的日志会定期刷新到磁盘中。如果更新、插入、删除操作比较多增加日志缓冲区的大小可以节省磁盘I/O。参数innodb_log_buffer_size:缓冲区大小innodb_flush_log_at_trx_commit日志刷新到磁盘的时机
磁盘 内存中的数据如何刷入磁盘——后台线程 6.3 事务原理
也就是如何保证的事务的四个特性 6.4 MVCC——高频面试
基本概念
当前读 读取的是记录的最新版本读取时要保证其他并发事务不能修改当前记录会对读取的记录进行加锁select…lock in share mode、select …for update\update\insert\delet都是一种当前读——如果不是当前读另一个事务修改了数据是读不到最新数据的
快照读 简单的select(不加锁)就是快照读读取的是记录数据的可见版本有可能是历史数据不加锁是非阻塞读
Read Committed每次select都生成一个快照读Repeatable Read开启事务后第一个select语句是快照读的地方Serializable快照读会退化为当前读
**MVCCMulti-Version Concurrency Control, 多版本并发控制**维护一个数据的多个版本使读写操作没有冲突快照读为MySQL实现MVCC提供了一个非阻塞读功能。实现依赖于数据库中的三个隐式字段、undo log日志、readView
1三个隐藏字段——每一条记录
DB_TRX_ID 最近修改事务的ID记录插入或最后一次修改该记录的事务ID
DB_ROLL_PTR 回滚指针指向该记录的上一个版本用于配合undo log
DB_ROW_ID 隐藏主键如果表结构没有指定主键将会生成该隐藏字段——如果表存在主键就不会生成该隐藏字段
2undo log日志
回滚日志是在insert、update、delete的时候产生的便于数据回滚的日志。insert的时候产生的undo log日志只在回滚时需要事务提交之后可被立即删除在update、delete的时候产生的undo log日志不仅在回滚时需要在快照读的时候也需要不会被立即删除 快照读时读取的是旧的数据
undo log 版本链 链表
3readview读视图
是快照读SQL执行时MVCC提取数据的依据记录并维护系统当前活跃的事务未提交的id
四个核心字段
m_ids 当前活跃的事务ID集合min_trx_id 最小活跃事务IDmax_trx_id 预分配事务ID当前最大事务ID1因为事务ID是自增的creatoe_trx_id readview创建者的事务ID
版本链数据访问规则
trx_id代表当前事务ID
①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中是可以访问该版本的—— 成立说明数据已经提交
不同的隔离级别生成readview的时机不同
read commited 在事务中每一次执行快照读时生成readview在读取的时候保证读已提交repeatable read尽在事务中第一次执行快照读的时候生成后续复用该readview
7. MySQL管理
7.1 系统数据库
1mysql存储MySQL服务器正常运行所需要的各种信息时区、主从、用户、权限等
2information_schema提供了访问数据库元数据的各种表和视图包括数据库、表、字段类型及访问权限等
3performance_schema为MySQL服务器运行时状态提供了一个底层监控功能主要用于收集数据库服务器性能参数
4sys包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图
7.2 常用工具
客户端工具
# 1. mysql
# 语法
mysql [options] [database]
# 选项
-u --username # 指定用户名
-p --password[name] # 指定密码
-h --hostname # 指定服务器IP或域名
-P --portport # 指定连接端口
-e --executename # 执行sql语句并退出连接数据库的时候处理一些脚本很方便# 2. mysqladmin 是一个执行管理操作的客户端程序可以用它来检查服务器的配置和当前状态、创建并删除数据库等。# 3. mysqlbinlog 由于服务器生成的二进制日志文件以二进制格式保存如果想要检查这些文本的文本格式就会用到mysqlbinlog日志管理工具
mysqlbinlog [options] log-files1 log-files2...-d --databasename # 指定数据库名称只列出指定的数据库相关操作
-o --offset # 忽略掉日志中前n行命令
-r --result-filename # 将输出的文本格式日志输出到指定文件
-s --short-form # 显示简单格式省略掉一些信息
--start-datatimedate1 --stop-datetimedate2 # 指定日期间隔内的所有日志
--start-positionpos1 --stop-positionpos2 # 指定位置间隔内的所有日志# 4. mysqlshow 客户端对象查找工具用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引
mysqlshow [options][db_name[table_name[col_name]]]
--count # 显示数据库及表的统计信息数据库表均可以不指定
-i # 显示指定数据库或指定表的状态信息# 5. mysqldump客户端工具用来备份数据库或在不同的数据库之间进行数据迁移。备份内容包含创建表以及插入表的SQL语句
# 语法
mysqldump [options] db_name[tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
# 连接选项
-u --username # 指定用户名
-p --password[name] # 指定密码
-h --hostname # 指定服务器ip或域名
-P --port # 指定连接端口
# 输出选项
--add-drop-database # 在每个数据库创建语句前面加上drop database语句
--add-drop-table # 在每个表创建语句前加上drop table语句默认开启不要开的话--skip -add-drop-table
-n --no-create-info # 不包含表的创建语句
-t --no-create-db # 不包含数据库的创建语句
-d --no-data # 不包含数据
-T --tabname # 自动生成两个文件一个.sql文件创建表结构的语句一个.txt文件数据文件# 6. mysqlimport/source数据导入工具。用于导入mysqldump加-T参数后导出的文本文件
# 语法
mysqlimport [options] db_name textfiles [textfiles2...]
# 如果需要导入sql文件可以使用mysql中的source指令
source/root/xxx.sql总结
