外贸模板建站,中国经济网人事,lnmp 502 wordpress,个人信息查询网索引
在无索引情况下#xff0c;就需要从第一行开始扫描#xff0c;一直扫描到最后一行#xff0c;我们称之为 全表扫描#xff0c;性能很低。 如果我们针对于这张表建立了索引#xff0c;假设索引结构就是二叉树#xff0c;那么也就意味着#xff0c;会对age这个字段…索引
在无索引情况下就需要从第一行开始扫描一直扫描到最后一行我们称之为 全表扫描性能很低。 如果我们针对于这张表建立了索引假设索引结构就是二叉树那么也就意味着会对age这个字段建立一个二叉树的索引结构。 优势 1.提高数据查询的效率降低数据库的IO成本。数据库的数据是存在磁盘的你要查询就要操作磁盘就会有IO 2.通过索引列对数据进行排序降低数据排序的成本降低CPU的消耗。
劣势 1.索引列也是要占用空间的。 2.降低更新表的速度对表进行DML时效率降低。
以下为MYSQL支持的所有索引数据结构和相应的支持引擎。
索引结构
B-tree
B树的定义
B树又称多路平衡查找树B树中所有结点的孩子个数的最大值称为B树的阶通常用m表示。一棵m阶B树或为空树或为满足如下特性的m叉树 1.树中每个结点至多有m棵子树即至多含有m−1个关键字。 2.若根结点不是终端结点则至少有两棵子树。 3.除根结点外的所有非叶结点至少有⌈m/2⌉棵子树即至少含有⌈m / 2 ⌉−1个关键字。 4.所有非叶结点的结构如下 其中n为元素个数K代表节点的关键字P代表指针。满足K1K2…Kn;Pi指针指向子树的根节点Pi-1所指的子树中所有节点关键字小于KiPi所指子树中所有节点关键字大于Ki。 节点中关键字个数有限制为n(⌈m/2⌉-1 n m-1) 5.所有的叶结点都出现在同一层次上并且不带信息(可以视为外部结点或类似于折半查找判定树的查找失败结点实际上这些结点不存在指向这些结点的指针为空)。
以一颗最大度数max-degree为5(5阶)的b-tree为例那这个B树每个节点最多存储4个key5 个指针 B树的插入 1.定位。找出插入该关键字的最低层中的某个非叶结点(在B树中查找key时,会找到表示查找失败的叶结点,这样就确定了最底层非叶结点的插入位置。注意插入位置一定是最低层中的某个非叶结点)。 2.插入。每个非失败结点的关键字个数都在区间[ [⌈m/2⌉−1,m−1]内。插入后的结点关键字个数小于m可以直接插入插入后检查被插入结点内关键字的个数当插入后的结点关键字个数大于m−1时必须对结点进行分裂。 3.分裂取一个新结点在插入key后的原结点从中间位置⌈m/2⌉将其中的关键字分为三部分左部分包含的关键字放在原结点中右部分包含的关键字放到新结点中中间位置⌈m/2⌉的结点插入原结点的父结点。若此时导致其父结点的关键字个数也超过了上限则继续进行这种分裂操作直至这个过程传到根结点为止,进而导致B树高度1
B树的查找
在B树上查找到某个结点后先在有序表中进行查找若找到则查找成功否则按照对应的指针信息到所指的子树中去查找。
B树的查找包含两个基本操作①在B树中找结点②在结点内找关键字。
由于B树常存储在磁盘上因此前一个查找操作是在磁盘上进行的而后一个查找操作是在内存中进行的即在找到目标结点后先将结点信息读入内存然后在结点内采用顺序查找法或折半查找法。 例如在上图中查找关键字42首先从根结点开始根结点只有一个关键字且4222若存在必在关键字22的右边子树上右孩子结点有两个关键字而364245则若存在必在36和45中间的子树上在该子结点中查到关键字42查找成功。若查找到叶结点时对应指针为空指针则说明树中没有对应的关键字查找失败。
B 树
B树是应文件系统比如数据库所需而出现的一种B树的变形树。 m阶的B树与m阶的B树的主要差异如下
1.有n棵子树的结点中包含有n个关键字;2.所有的数据都在叶子节点。叶子结点本身依关键字的大小自小而大顺序链接;3.所有分支非叶子结点可以看成是索引不含具体数据结点中仅含有其子树中的最大(或最小)关键字。4.在B树中每个结点(非根内部结点)的关键字个数n的范围是⌈m/2⌉≤n≤m(根结点1≤n≤m)在B树中每个结点(非根内部结点)的关键字个数n范围是⌈m/2⌉−1≤n≤m−1 (根结点: 1≤n≤m−1)。 在mysql中对Btree进行了一定优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针形成了带有顺序指针的BTree提高区间访问的性能利于排序。 Hash
哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。
如果两个(或多个)键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决。 特点
A. Hash索引只能用于对等比较(in)不支持范围查询between …B. 无法利用索引完成排序操作C. 查询效率高通常(不存在hash冲突的情况)只需要一次检索就可以了数据较少时效率通常要高于Btree索引。
索引分类
在MySQL数据库将索引的具体类型主要分为以下几类主键索引、唯一索引、常规索引、全文索引。 而在Innodb引擎中又可以分为聚集索引和二级索引。
聚集索引(Clustered Index)将数据存储与索引放到了一块索引结构的叶子节点保存了行数据。必须有,而且只有一个。
如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
二级索引将数据与索引分开存储索引结构的叶子节点关联的是对应的主键。可以存在多个。 聚集索引的叶子节点下挂的是这一行的数据 。二级索引的叶子节点下挂的是该字段值对应的主键值。 查找举例 1.根据name字段进行查询根据nameArm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。 2.由于查询返回的数据是*所以此时还需要根据主键值10到聚集索引中查找10对应的记录最终找到10对应的行row。 3.最终拿到这一行的数据直接返回即可。
索引语法
创建索引 CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,… ) ; name字段为姓名字段该字段的值可能会重复为该字段创建索引。 CREATE INDEX idx_user_name ON tb_user(name); 为profession、age、status创建联合索引。 CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status); phone手机号字段的值是非空且唯一的为该字段创建唯一索引。 CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone); 查看索引 SHOW INDEX FROM table_name ; 删除索引 DROP INDEX index_name ON table_name ; 索引使用
索引失效
最左前缀法则
如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。 在 tb_user 表中有一个联合索引这个联合索引涉及到三个字段顺序分别为professionagestatus。
对于最左前缀法则指的是查询时最左的列也就是profession必须生效否则索引全部失效。
PS必须存在但是顺序无所谓。索引无效不代表无法查询只是用于辅助查询的索引失效了。
范围查询
联合索引中出现范围查询(,)范围查询右侧的列索引失效。 例如 select * from tb_user where profession ‘软件工程’ and age 30 and status ‘0’; 这里的status字段的索引无效。
运算引起失效
不要在索引列上进行运算操作 索引将失效。 select * from tb_user where substring(phone,10,2) ‘15’; 这样会使得phone失效。
字符串不加引号失效
字符串类型字段使用时不加引号索引将失效。
这样写没问题 explain select * from tb_user where profession ‘软件工程’ and age 31 and status ‘0’; 但是由于数据库中status是字符串类型这样不加引号会导致数据库做一次隐式转换从而又导致了运算引起失效。 explain select * from tb_user where profession ‘软件工程’ and age 31 and status 0; 模糊查询
在左侧添加模糊查询会使得索引失效。
生效 explain select * from tb_user where profession like ‘软件%’; 失效 explain select * from tb_user where profession like ‘%工程’; explain select * from tb_user where profession like ‘%工%’; or连接
当or连接的条件左右两侧字段都有索引时索引才会生效。
如果age没有索引但是id和phone有索引那么。 explain select * from tb_user where id 10 or age 23; explain select * from tb_user where age 23 or phone ‘17799990017’; 数据分布影响
MySQL在查询时会评估使用索引的效率与走全表扫描的效率如果走全表扫描更快则放弃索引走全表扫描。
因为索引是用来索引少量数据的如果通过索引查询返回大批量的数据则还不如走全表扫描来的快此时索引就会失效。
SQL提示
use index 建议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 ‘软件工程’; 索引使用性能优化
覆盖索引
尽量使用覆盖索引减少select *。
我们在创建索引的时候就已经把表中的id和很多字段给放到索引中了但是如果你查找的数据不再索引中就还是会造成回表查询增加io次数。我们就是要尽量避免这种情况。
先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取数据的方式就称之为回表查询。
explain中会有extra字段。
前缀索引
有些字段的长度很长查询的时候进行比对就会造成大量的IO影响查询的效率我们可以对这种字符串建立索引从而提高索引效率。
例如 create index idx_email_5 on tb_user(email(5)); 联合索引
针对于查询字段建立索引时建议建立联合索引而非单列索引。
单列索引即一个索引只包含单个列。 联合索引即一个索引包含了多个列。
假如我们频繁查询的数据是phone和name两个字段我们可以选择建立两个单列索引和一个联合索引。
但是如果我们的select语句中同时用phone和name作为判断变量那么如果选择单列索引还是会造成回表查询。
联合索引的索引示意。
索引的简历原则 针对于数据量较大且查询比较频繁的表建立索引。 针对于常作为查询条件where、排序order by、分组group by操作的字段建立索 引。 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。 如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。 尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。 如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。
SQL优化
insert优化
如果我们需要一次性往数据库表中插入多条记录可以从以下三个方面进行优化。 批量插入数据 Insert into tb_test values(1,‘Tom’),(2,‘Cat’),(3,‘Jerry’); 手动控制事务 start transaction; insert into tb_test values(1,‘Tom’),(2,‘Cat’),(3,‘Jerry’); insert into tb_test values(4,‘Tom’),(5,‘Cat’),(6,‘Jerry’); insert into tb_test values(7,‘Tom’),(8,‘Cat’),(9,‘Jerry’); commit; 插入大批量数据用MySQL数据库提供的load指令进行插入
-- 客户端连接服务端时加上参数 -–local-infile
mysql –-local-infile -u root -p-- 设置全局参数local_infile为1开启从本地加载文件导入数据的开关
set global local_infile 1;-- 执行load指令将准备好的数据加载到表结构中
load data local infile /root/sql1.log into table tb_user fields terminated by , lines terminated by \n ;主键优化
在InnoDB存储引擎中表数据都是根据主键顺序组织存放的。
页分裂
B树将聚集索引存储在叶子节点一个叶子节点可以看做一个页面如果插入的数据不断增加会进行“页分裂”。
顺序插入主键 我们顺序插入主键时当一个页的空间不够了会进行页分裂然后插入到下一个页中。 乱序插入主键
但是当我们乱序插入时就会造成频繁的页分裂还会造成频繁的存储修改和指针修改。 所以对于主键的设计有如下原则。 1.尽量降低主键长度。 2.尽量选择自增主键循序插入。 3.尽量不要用无规则主键。 4.尽量避免对主键的修改。
页合并
如果数据删除则会在页面空间小于一定阈值时进行“页合并”。
order by优化
B树自身有一定顺序所以在排序时尽量使用覆盖索引。
比如 create index idx_user_age_phone_aa on tb_user(age,phone); 创建索引后根据age, phone进行升序排序 select id,age,phone from tb_user order by age; 需要注意的是order by的多个字段的顺序必须和索引相应字段的顺序都一致或都相反会无法完成覆盖索引。
例如
如下sql语句的两个字段age和phone和索引中的是相反顺序但是由于都是相反的就仍然可以完成覆盖索引。 select id,age,phone from tb_user order by age desc , phone desc ; 但是一个升序一个降序就无法完成覆盖索引。 explain select id,age,phone from tb_user order by age asc , phone desc ; group by优化
分组操作优化也是尽量使用覆盖索引避免回表查询。
注意gropu by后跟的关键字也是要符合最左原则的。
比如如下sql可以完成覆盖索引。 select profession ,count(*) from tb_user group by profession , age; 但是这样就不行了。 select profession ,count(*) from tb_user group by age; limit优化
通过创建 覆盖索引 能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化。 select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id a.id; count优化
MyISAM 引擎把一个表的总行数存在了磁盘上因此执行 count(*) 的时候会直接返回这个数效率很高 但是如果是带条件的countMyISAM也慢。InnoDB 引擎就麻烦了它执行 count(*) 的时候需要把数据一行一行地从引擎里面读出来然后累积计数。
效率方面 count(字段) count(主键 id) count(1) ≈ count(*)。
所以尽量使用 count(*)。
update优化
当我们在执行删除的SQL语句时会锁定id为1这一行的数据然后事务提交之后行锁释放。 update course set name ‘javaEE’ where id 1 ; 但是当我们在执行如下SQL时。 update course set name ‘SpringBoot’ where name ‘PHP’ ; 当我们开启多个事务在执行上述的SQL时我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。
存储过程
存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合调用存储过程可以简化应用开发人员的很多工作减少数据在数据库和应用服务器之间的传输对于提高数据处理的效率是有好处的。
从思想来看就是sql语句的封装和复用。
但是个人觉得这个东西其实都是可以在jvm里实现的。
基本语法
创建
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN-- SQL语句
END ;调用 CALL 名称 ([ 参数 ]); 查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA xxx; -- 查询指定数据库的存储过程及状态信息HOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义删除 DROP PROCEDURE [ IF EXISTS ] 存储过程名称 例如
create procedure p1()
beginselect count(*) from student;
end;-- 调用
call p1();-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA itcast;show create procedure p1;-- 删除
drop procedure if exists p1;参数
IN 该类参数作为输入也就是需要调用时传入值 。是默认参数类型OUT 该类参数作为输出也就是该参数可以作为返回值。INOUT 既可以作为输入参数也可以作为输出参数。
CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN-- SQL语句
END ;变量
系统变量
查看 SHOW [ SESSION | GLOBAL ] VARIABLES ; – 查看所有系统变量 SHOW [ SESSION | GLOBAL ] VARIABLES LIKE ‘…’; – 可以通过LIKE模糊匹配方式查找变量 SELECT [SESSION | GLOBAL] 系统变量名; 赋值 SET [ SESSION | GLOBAL ] 系统变量名 值 ; 用户变量
创建与赋值 SET 变量名 值 [, 变量名 值] ; SELECT 字段名 INTO 变量名 FROM 表名; 使用 SELECT var_name ; 局部变量
创建 DECLARE 变量名 变量类型 [DEFAULT 值] ; 赋值 SET 变量名 值 ; SELECT 字段名 INTO 变量名 FROM 表名; 例如
create procedure p2()
begindeclare stu_count int default 0;select count(*) into stu_count from student;select stu_count;
end;call p2();if-Then
IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;
例如
create procedure p3()
begindeclare score int default 58;declare result varchar(10);if score 85 thenset result : 优秀;elseif score 60 thenset result : 及格;elseset result : 不及格;end if;select result;
end;call p3();case
创建 语法1
CASE case_valueWHEN when_value1 THEN statement_list1[ WHEN when_value2 THEN statement_list2] ...[ ELSE statement_list ]
END CASE;语法2
CASEWHEN search_condition1 THEN statement_list1[WHEN search_condition2 THEN statement_list2] ...[ELSE statement_list]
END CASE;例如
create procedure p6(in month int)
begindeclare result varchar(10);casewhen month 1 and month 3 thenset result : 第一季度;when month 4 and month 6 thenset result : 第二季度;when month 7 and month 9 thenset result : 第三季度;when month 10 and month 12 thenset result : 第四季度;elseset result : 非法参数;end case ;select concat(您输入的月份为: ,month, , 所属的季度为: ,result);
end;
call p6(16);while
与while类似的还有repeat和loop功能上来讲都可以用while替代感兴趣可以自己去看一下。
WHILE 条件 DOSQL逻辑...
END WHILE;例如
create procedure p7(in n int)
begindeclare total int default 0;while n0 doset total : total n;set n : n - 1;end while;select total;
end;call p7(100);游标
用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进 行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE其语法分别如下。
通俗来讲就是一个存放SQL——结果集的变量打开就读了sql然后通过fetch就可以读结果集。 创建游标 DECLARE 游标名称 CURSOR FOR 查询语句 ; 打开游标 OPEN 游标名称 ; 获取游标记录 FETCH 游标名称 INTO 变量 [, 变量 ] ; 关闭游标 CLOSE 游标名称 ; 例如
create procedure p11(in uage int)
begindeclare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name,profession from tb_user where age uage;drop table if exists tb_user_pro;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));open u_cursor;while true dofetch u_cursor into uname,upro;insert into tb_user_pro values (null, uname, upro);end while;close u_cursor;end;
call p11(30);Handler
用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。
基本语法
DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement ;handler_action 的取值CONTINUE: 继续执行当前程序EXIT: 终止执行当前程序condition_value 的取值SQLSTATE sqlstate_value: 状态码如 02000SQLWARNING: 所有以01开头的SQLSTATE代码的简写NOT FOUND: 所有以02开头的SQLSTATE代码的简写SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写例如
create procedure p11(in uage int)
begindeclare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name,profession from tb_user where age uage;declare exit handler for SQLSTATE 02000 close u_cursor;drop table if exists tb_user_pro;create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));open u_cursor;while true dofetch u_cursor into uname,upro;insert into tb_user_pro values (null, uname, upro);end while;close u_cursor;end;call p11(30);上面的 declare exit handler for SQLSTATE ‘02000’ close u_cursor; 可以改为 declare exit handler for not found close u_cursor; 函数
这个我觉的就更没必要在sql里写了
存储函数是有返回值的存储过程存储函数的参数只能是IN类型的。具体语法如下
CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN-- SQL语句RETURN ...;
END ;对于characteristic
DETERMINISTIC相同的输入参数总是产生相同的结NO SQL 不包含 SQL 语句。READS SQL DATA包含读取数据的语句但不包含写入数据的语句。
create function fun1(n int)
returns int deterministic
begindeclare total int default 0;while n0 doset total : total n;set n : n - 1;end while;return total;
end;
select fun1(50);锁
在多线程并发访问数据库时锁用于确保数据的一致性和有效性。
按照锁的粒度讲MySQL的锁分为三类
全局锁锁定数据库中的所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。
全局锁
全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态。 其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。
基本语法
加全局锁 flush tables with read lock ; 数据备份 mysqldump -uroot –p1234 itcast itcast.sql 释放锁 unlock tables ; 表级锁
锁定粒度大发生锁冲突的概率高并发度低。应用在MyISAM、InnoDB、BDB等存储引擎中。
表锁
加锁 lock tables 表名… read/write。 释放锁 客户端断开连接默认释放锁 unlock tables 共享锁read lock对于所有线程写操作全部拒绝但是允许所有线程的读操作。 排他锁write lock允许持锁线程读写拒绝其他线程一切读写
元数据锁
meta data lock , 元数据锁简写MDL。
MDL加锁过程由系统自动控制无需显式使用访问表时自动获取。
MDL作用是维护表元数据(表明字段名字段类型)的数据一致性表上有活动事务时不可以对表元数据进行写入操作。避免DML与DDL和DQL冲突保证读写的正确性。
也就是说会给DML和DDL语句执行时加MDL。DQL会获取SHARED_READDML会获取SHARED_WRITE而DDL会获取EXCLUSIVE。EXCLUSIVE和另外两个都是互斥的。
意向锁
在InnoDB中使用意向锁来减少表锁的检查使表锁无需检查每行数据是否加锁。
意向锁的作用是避免DML或DQL在执行时行锁与表锁的冲突。
原理对涉及到的行加锁同时也对整个表加意向锁其他线程想要加表锁时不必逐行判断行锁而是通过判断想要添加的表锁和意向锁是否冲突从而判断能否成功添加表锁。
意向共享锁与 表锁共享锁(read)兼容与表锁排他锁(write)互斥。 select … lock in share mode 意向排他锁与表锁共享锁(read) 及 排他锁(write)都互斥。 由DML自动添加。 行级锁
每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于无索引字段不会添加行锁会直接添加表锁。
行锁(Record Lock)
锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。 共享锁允许一个事务去读一行对于此行阻塞想要获取排他锁的进程。不同线程对对同一行数据获取共享锁是允许的。
添加和意向锁的操作一致。 select … lock in share mode 排他锁允许获取排他锁的事物对数据更新阻塞想获取改行任何锁的进程。 由DML自动添加 间隙锁(gap lock)
锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别支持。 临键锁(Next-Key Lock)
行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。
间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。 默认情况下InnoDB在 REPEATABLE READ事务隔离级别运行InnoDB使用 临键锁进行搜索和索引扫描以防止幻读。
索引上的等值查询(唯一索引)给不存在的记录加锁时, 优化为间隙锁 。索引上的等值查询(非唯一普通索引)向右遍历到最后一个不满足查询需求的值时临键锁退化为间隙锁。索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。
Innodb
InnoDB: 是Mysql的默认存储引擎支持事务、外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。 表空间 表空间是InnoDB存储引擎逻辑结构的最高层 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) 则每张表都会有一个表空间xxx.ibd一个mysql实例可以对应多个表空间用于存储记录、索引等数据。
段 段分为数据段Leaf node segment、索引段Non-leaf node segment、回滚段Rollback segmentInnoDB是索引组织表数据段就是B树的叶子节点 索引段即为B树的非叶子节点。段用来管理多个Extent区。
区 区表空间的单元结构每个区的大小为1M。 默认情况下 InnoDB存储引擎页大小为16K 即一个区中一共有64个连续的页。
页 页是InnoDB 存储引擎磁盘管理的最小单元每个页的大小默认为 16KB。为了保证页的连续性InnoDB 存储引擎每次从磁盘申请 4-5 个区。
行 InnoDB 存储引擎数据是按行进行存放的。
基本架构
内存架构 Buffer Pool
缓冲池。是主存中的一个区域用于缓冲磁盘和内存之前的访问速率差值将经常访问的数据加载到缓冲池在执行DML和DQL时先操作缓冲池中的数据之后以一定频率刷新到磁盘减少了磁盘IO。在专用服务器上通常将80%内存分配给缓冲池。
缓冲池中包含索引页数据页undo页插入缓存自适应哈希索引锁信息。
缓冲池中的页分为三种。
free page空闲page未被使用。clean page被使用page数据没有被修改过。dirty page脏页被使用page数据被修改过也中数据与磁盘的数据产生了不一致。
Change Buffer
更改缓冲区。当DML操作的数据不在缓冲池时先将数据变更存入更改缓冲区直到数据被读取时将该数据合并入缓冲池中。避免了DML操作对索引的影响从而产生大量磁盘IO。
Adaptive Hash Index 自适应哈希索引。Innodb会在某种特定条件下使用hash索引这种索引无需人工调控。
Log Buffer
日志缓冲区。默认大小16MB存储需要存入磁盘的log文件定期刷新到磁盘。
磁盘结构 System Tablespace 系统表空间。是上述内存架构的更改缓冲区存储区域。也可能会有表的数据和索引。 File-Per-Table Tablespaces 文件表空间。每创建一个表都产生一个对应存储表数据和索引的表空间。
General Tablespaces 通用表空间。用户主动创建的表空间。可在创建表时指定该空间。
创建表空间 CREATE TABLESPACE ts_name ADD DATAFILE ‘file_name’ ENGINE engine_name; 指定该空间 CREATE TABLE xxx … TABLESPACE ts_name; Undo Tablespaces 撤销表空间。用于存储undo log日志。 Temporary Tablespaces 临时表空间。用于存储用户创建的会话临时表或全局临时表。 Doublewrite Buffer Files 双写缓冲区。缓冲池将数据刷新到磁盘前先将数据写入双写缓冲区便于恢复数据。 Redo Log 重做日志。分为重做日志缓冲和重做日志文件。前者存于内存后者存于磁盘。事物提交后将修改信息存入重做日志文件用于数据恢复。
后台线程
Master Thread 核心后台线程。负责调度其他线程以及各种操作。
IO Thread 要负责IO请求的回调。 有四种 Read thread 4个 负责读操作。 Write thread 4个 负责写操作。 Log thread 1个 负责将日志缓冲区刷新到磁盘。 Insert buffer thread 1个 负责将写缓冲区内容刷新到磁盘。
Purge Thread 用于回收事务提交后不可用的undo log。
Page Cleaner Thread 协助 Master Thread 刷新脏页到磁盘的线程
事物支持
谈到事物支持就是谈如何满足事物的四个特性原子一致持久隔离。 Innodb有两份日志锁和mvcc模式来保证事物实现这些特性。
MVCC弱一致性多版本并发控制
MVCC(Multi-Version Concurrency Control)多版本并发控制。维护一个数据的多个版本实现读写无冲突通过快照读实现非阻塞读功能。具体的实现依赖于隐藏字段undo log链ReadView
当前读读取记录的最新版本读取时需要加锁。可避免脏读。
快照读读取当前可见版本有可能是历史数据无需加锁。通过undo log版本链实现。
Read Committed每次select都生成一个快照读。Repeatable Read开启事务后第一个select语句才是快照读的地方。 普通的select是快照读而在默认的RR隔离级别下开启事务后第一个select语句才是快照读的地方后面执行相同的select语句都是从快照中获取数据可能不是当前的最新数据这样也就保证了可重复读。 Serializable快照读会退化为当前读。
隐藏字段和undo log链
DB_TRX_ID 最近修改事务ID记录插入这条记录或最后一次修改该记录的事务ID。DB_ROLL_PTR 回滚指针指向这条记录的上一个版本用于配合undo log指向上一个版本。DB_ROW_ID 隐藏主键如果表结构没有指定主键将会生成该隐藏字段。
回滚指针和最近修改事物ID 如下是一条insert的原始数据。可见最近修改事物ID是1因为新插入所以回滚指针是null 而后有如下四个事物并发访问。 当执行完事物2之后。 当执行完事物3之后
readview
读视图。作为MVCC为快照读SQL提供数据时的依据记录当前系统活跃事物的id。
m_ids 当前活跃的事务ID集合min_trx_id 最小活跃事务IDmax_trx_id 预分配事务ID当前最大事务ID1因为事务ID是自增的creator_trx_id ReadView创建者的事务ID
trx_id 代表当前undo log版本链对应事务ID。
READ COMMITTED 在事务每一次执行快照读都生成ReadView。REPEATABLE READ在事务第一次快照读时生成ReadView后续复用该ReadView。
举例分析
上述事物5在读取时就会分两次产生两个readview。 以第一个readview为例。在进行匹配时会从undo log的版本链从上到下进行挨个匹配
其实看到这里也就明白了MVCC实际上是就是通过“维护”数据版本来实现的非阻塞读提高了并发性这也是弱一致性的体现。
由此可见高并发和高一致是互斥的概念并非一致性弱就不好也并非高一致性就好具体业务需要具体权衡。
