MySQL进阶
1. 存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。
1.1 MySQL体系结构
- 连接层
- 主要完成一些类似于连接处理、授权认证、及相关的安全方案。
- 服务层
- 主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，是否使用索引，部分内置函数的执行。
- 引擎层
- 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的
- 存储层
- 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。
1.2 存储引擎介绍
引擎就是类似发动机，之间没有好坏之封面，只有合不合适。
-
存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。
-
==存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。==存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。
建表时指定引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
) ENGINE = INNODB [ COMMENT 表注释 ] ;
1.3 存储引擎的特点
1.3.1 InnoDB
-
介绍
- InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的
MySQL 存储引擎。
- InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的
-
特点
- 事务、行级锁、外键
-
文件
- tablename.ibd：tablename代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。
-
逻辑存储结构
-
表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以 包含多个Segment段。
-
段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管 理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
-
区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为 16K， 即一个区中一共有64个连续的页。
-
页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默 认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
-
行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时 所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。
1.3.2 MyIsAM
- 是早起的默认存储引擎
- 不支持事务、外键、行锁。访问速度快，表锁
1.4 引擎选择
InnoDB引擎与MyISAM引擎的区别 ?
①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 支持行锁。
③InnoDB引擎, 支持外键, 而MyISAM是不支持的。
2. 索引***
2.1索引概述
2.1.1 介绍
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。
2.1.2 特点
- 优点
- 提高搜索效率
- 有序，降低排序成本
- 劣势
- 占用空间
- 增删改效率降低
2.2 索引结构
B+TREE索引、Hash索引、R-tree（空间索引）、Full-text（全文索引）、B-TREE
2.2.1 B-TREE
5阶B-TREE，每个节点最多4个key，5个指针。
- 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
- 在B树中，非叶子节点和叶子节点都会存放数据
2.2.2 B+TREE***
与B-TREE不同的地方
- 所有节点均能在叶子节点找到。
- 所有的数据只在叶子节点。
- 所有叶子节点组成单向链表。
MySQL优化了的：
- MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。
2.2.2.1 联合索引下的B+TREE
设置联合索引的字段为a,b,c
则索引数据结构图如下
【例1】
select ... where a = 1
先根节点匹配a=1，在左树，再找到a=1，再去左树匹配，因为没有要求b的值，所以查到一条结果(1,1,4,5)由于叶子节点是链表，紧着匹配下一个节点，a=1，匹配成功继续返回，以此类推，最后返回2条结果
【例2】
select ... where a=13 and b=12
先根节点匹配a=13，走1~56的间隙的指针到左树。继续匹配a=13，走12~21的间隙的指针到右树，找到一个结果（13,12,4），紧接匹配b=12，成功。然后顺着链表，找到(13,16,1,6)，逐个匹配a,b，失败，以此类推。最后返回一条
2.2.3 其他
- Hash索引
- 检索数据复杂度是常数阶
- 但是，不支持排序，<,>，between等范围查找
- 二叉树
- 结构简单，容易实现
- 阶太少，数据量多时，检索深度过高
2.2.4 B+TREE好在哪？
- 相比二叉树，后者阶数更高，检索效率高
- 相比B-TREE，后者数据只存放在叶子节点，数据与结构分离，页固定16k，存放的节点更多，树的深度更低。所有数据组成单向链表，区间查找，排序效率提高
- 相比Hash索引，后者支持防伪匹配以及排序
2.3 索引分类
2.3.1 索引分类
主键索引、唯一索引、常规索引、全文索引
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键 | 针对于表中主键创建的索引 | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规 | 快速定位特定数据 | 可以有多个 | |
| 全文 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
2.3.2 聚集索引&二级索引
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 每张表必须有,而且只有一个 |
| 二级索引 | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
- 聚集索引
- 存在主键，主键即聚集索引
- 不存在主键，将使用第一个UNIQUE索引。
- 都没有，则创建隐藏的rowid为聚集索引
- 二级索引
- 非聚集索引就是二级索引
- 非主属性可以创建二级索引
可以看出，聚集索引的叶子节点放的是一行的数据，用其他字段来查的时候，找到id值，最终都是要依靠聚集索引来找到最终数据，亦称之为回表查询。
2.4 索引语法
2.4.1 创建索引
create [UNIQUE|FULLTEXT] index index_name on table_name(col1,col2...)
- 默认是常规索引，当字段不唯一时，为联合索引
- UNIQUE：只能给唯一索引的字段创建索引
- FULLTEXT：全文索引
2.4.2 删除索引
drop index index_name on table_name
2.4.3 查看索引
show index from table_name
2.5 索引性能分析
2.5.1 explain
explain <select ...>
【例】
explain select * from user where id=1;
详细字段
- id
- 查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序
两种情况
id相同，执行顺序从上往下
id不同，id值越大，优先级越高，越先执行
- 查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序
- select_type
- 表示 SELECT 的类型，常见的取值有
- SIMPLE（简单表，即不使用表连接 或者子查询）、
- PRIMARY（主查询，即外层的查询）、
- UNION（UNION 中的第二个或者后面的查询语句）、
- SUBQUERY（SELECT/WHERE之后包含了子查询）
- type
- 表示连接类型，由好到差
- system：表中仅有一行。
- const：通过索引一次就找到 （主键或者唯一索引） 。
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。
- ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体。
- range: 对索引进行范围检索。
- index: 物理文件全扫描，速度非常慢
- all：遍历全表以找到匹配的行。
- possible_keys
- 可能使用的索引
- key
- 实际用到的索引
- key_len
- 表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。
- ref
- 显示索引的哪一列被使用了。联合索引时作用可参考
- rows
- MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值， 可能并不总是准确的
- Extra
- 包含不适合在其他列中显示，但是十分重要的额外信息
- Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
- Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
- Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。
- Using where :表明使用where过滤
- using join buffer:使用了连接缓存
- impossible where:where子句的值总是false，不能用来获取任何元组
- select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
- distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
2.5.2 其他小技巧
\G #将查询出来的结果以列表显示
select ...\G
2.6 索引使用原则
2.6.1 最左前缀法则
联合索引，必须要遵守最左前缀法。
最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会后续失效
具体最左的列是什么，是由索引创建时候决定的。
如有索引：
create index idx_user_name_age_sex table(name,age,sex)
正例
select ... where name='Jack'
select ... where name='Jack' and age = '18'
select ... where name='Jack' and age = '18' and sex = '男'
select ... where age = '18' and sex = '男' and name='Jack' #不受顺序影响
反例
select ... where age = '18' #没有第一列
select ... where sex = '男' #没有前两列
select ... where name='Jack' and sex = '男' #越过了中间列，索引前半部分生效
2.6.2 范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
select ... where name='Jack' and age > 1
优化（可能还是range级别）
select ... where name='Jack' and age >= 1
2.6.4 索引失效
2.6.4.1 索引列运算
不要在索引列上进行运算操作， 索引将失效。
反例
select ... where substring(phone,10,2) = '13';
2.6.4.2 字符串不加引号
MySQL某字段是字符串类型，但是SQL中该字段没加引号，索引将失效.
select ... where phone = 1300
SQL中非纯数字不加引号会报错！
所以可能会在以字符串形式存储纯数字的字段中出现索引失效，因为得先不报错，才能执行SQL。
2.6.4.3 模糊查询
头部模糊查询，索引失效。仅仅尾部则不会。
有效
select ... from name like 'Ja%'
失效
select ... from name like '%ck'
2.6.4.4 or 连接条件
但凡or的一侧没有索引，则索引失效
select ... where id = 1 or age='18'
#id一定有索引，但是age无索引则也是白搭，索引完全失效！
若对age创建索引，则索引有效！
2.6.4.5 数据分布影响
如果MySQL优化器评估使用索引比全表更慢，则不使用索引。
因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。
- 对于有序的列表，比如手机号，如果手机号共有100w个，phone>1开始查，那么走全表扫描。如果phone>50w，则可能走索引（具体得看优化器的阈值）
- is null 与is not null 当列的绝大部分都是null时，【is null】则会全表，【is not null】会使用索引，反之也成立。
2.6.5 SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
select * from table_name <use|ignore|force> index(index_name) where ...
- use ： 建议MySQL使用的索引，类似建议java调用gc，不一定会用
- ignore：忽略指定索引。
- force ：强制使用。
2.6.6 索引使用
2.6.6.1 覆盖索引
尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。
比如
select name,phone... where name='Jack'
因为phone没有不在name所在的联合索引，因此phone需要回表查询，效率很低！
又如
select id,username,password where username='admin'
如果username与password建立了联合索引，那么就不需要回表查询，效率高！（此时的联合索引的数据域是id)
- explain的额外信息
| Extra | 含义 |
|---|---|
| Using where; Using Index | 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 |
| Using index condition或者NULL | 查找使用了索引，但是需要回表查询数据 |
2.6.6.2 前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
- 创建语法
create index idx_name_n on table_name(column_name(n))
- n值确定
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
选择性的值小于等于1，1是最好的即唯一索引。但要考虑索引使用空间，所以上面n=5跟n=6的选择性一样时，选n=5
2.6.6.3 单列索引与联合索引选择
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列 。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
另外，当单例索引与联合索引同时包含存在时，要use table_name(index_name)来建立MySQL使用联合索引。
create index on table(col1,col2)
create index on table(col2,col1)
是有区别的！要考虑最左匹配原则
2.7 索引设计原则
2.7.1 什么时候建立
- 针对于数据量较大且查询比较频繁的表，建立索引。
- 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
2.7.2 建立什么索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引 （区分度越高，使用索引的效率越高）
- 字符串类型的字段的长度较长，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
2.7.3 注意
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。（当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询，比如受数据分布影响的索引失效）
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
3. SQL优化
3.1 插入数据
-
批量插入数据
insert into table values(col1,col2),(col1,col2) -
手动控制事务
start transaction ... commit -
主键顺序插入
1,2,3,4,5,78,99 -
大批量插入
-- 客户端连接服务端时，加上参数 -–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' ;
3.2 主键优化
行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：
在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。
3.2.1 页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。
- 欲插入50
- 过程
- 但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#
- 但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。
- 调换页
3.2.2 页合并
- 如果逻辑删除（仅仅标记为删除，不是物理删除），达到阈值，默认页的50%
- 当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用
知识小贴士：
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
3.2.3 主键设计原则
- 在满足业务下，使用最低主键长度
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。
3.3 order by 优化
MySQL的排序，有两种方式：
-
Using filesort
- 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
-
Using index
- 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高 。
- 对于没有索引情况下的排序，一定是全表扫描，为index级速度(慢)
- 默认情况下的联合索引都是升序的。也就是
create index index_name on table(age asc,phone asc) #asc可以省略
正例(索引有效)
select age from table order by age
select age,phone from table order by age,phone
select age from table order by age desc
反例
select * from table order by ... #不管安排什么字段来排序，只有用*一定索引失效。
select age,name from table order by age
# 由于name字段是不包含在idx_age_name索引中所以无法使用索引，此时需要进行文件排序，即Extra: Using filesort
select age,name from table order by age,name desc #一升一降，部分失效
select age from table order by phone #需filesort辅助
总之
- 大前提，select中的字段要求在索引中出现。
- 借联合索引时，要遵循最左匹配原则，当然可以先用where age 后order by name，也是遵循的。
- 借联合索引，order by的要求字段，要么同时升序要么同时降序。
- 额外信息中出现Using index和filesort，性能都是不太达标的！
想一升一降的排序，可以这样创建索引
create index index_name on table(age ,name desc)
如此一来，一升一降就不会导致索引( 部分 )失效
3.4 group by 优化
与order by一致，有索引时候会显著提升分组速度。
- Using temporary
- 使用临时表来分组，效率低
- Using index
- 使用索引分组
同样的，
- select应该只出现索引有的字段
- 最左匹配，允许先where匹配字段，后group by
3.5 limit优化
select * from table limit 100w,10
这样的越往后的分页，效率越低。
MySQL官方建议：
覆盖索引
select id from table order by id limit 100w,10
- 如果没有order by id默认下是按照物理磁盘的存储顺序来显示数据的！而不是安排自增id，其实不存在页合并的话，那么查出来的就是id升序
使用连表查询出id的行数据
select * from tb_sku t , (select id from table order by id limit 100w,10) a where t.id = a.id;
3.6 count优化
上结论
count(*)≈count(1)>count(主键)>count(字段)
尽量使用count(*)，MySQL专门优化了性能！
3.7 update优化
使用update会使用到InnoDB的锁机制。
锁是针对索引的，如果where中没有索引或索引失效，那么将会升级为表锁，性能大大降低
update ... where name='Jack'
#若name没有索引，则是表锁；有索引，则是行锁
另外，如果name='Jack’有多条记录，这么这多条记录都会施加行锁！
3.8 汇总
3~7 本质还是针对索引优化，因此掌握索引优化，就掌握了SQL优化的80%！！！
- 插入数据
- 批量插入、手动控制事务、主键顺序插入
- 主键优化
- 长度短、顺序插入，用auto_incremrnt而不是UUID
- order by
- using index 直接索引返回而不是Using filesort
- group by
- 多字段分组要满足最左前缀匹配
- limit
- 覆盖索引(无需回表)+子查询
- count
- count(字段)<count(主键id) < count(数字) ≈ count(*)
- update
- 尽量根据(where)主键/索引字段进行数据更新
4. 锁
4.1 锁的概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
在MySQL中锁分为三类
- 全局锁 锁定数据库中的所有表。
- 表级锁：每次操作锁住整张表。
- 行级锁：每次操作锁住对应的行数据
查看锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;
要其他手动开启的事务加锁之后，才能查看的到。
4.2 全局锁
介绍
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
语法
- dos【1】
#登录到MySQL
flush tables with read lock ;
- dos【2】
#不要登录到MySQL，而是在MySQL的bin目录下的mysqldump.exe
mysqldump -uroot –p1234 itcast > itcast.sql
- 回到dos【1】
unlock tables ;# 释放全局锁
4.3 表级锁
表级锁，就是锁整张表。
4.3.1 介绍
- 表锁
- 元数据锁（meta data lock，MDL）
- 意向锁
4.3.2 表锁
-
表共享锁
- 所有事务只能读不可写
# 加锁 lock tables table_name read; # 解锁 unlock tables; -
表独占写锁
- 只有事务自己可读可写，其他不可读亦不可写
# 加锁 lock tables table_name write; # 解锁 unlock tables;
4.3.3 元数据锁
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。**为了避免DML与DDL冲突，保证读写的正确性。** ==说白了，就是修改表结构的锁==
- MDL读
- 在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；
- 就是，事务有操作时，其他事务不能对表结构就行修改。
- MDL写锁
- 当对表结构进行变更操作的时候，加MDL写锁(排他)。
- 当对表结构就行修改时，其他事务不能读写。
4.3.4 意向锁
为了解决行锁与表锁的冲突而出现，意向锁本质是表锁，用来标识该表的行锁情况，避免其他事务要遍历表来检查行锁。
- 说说过程
- 当事务A进行增删改查 (查需要手动加共享锁) 的时候，会先对需要操作的行进行加锁。并且，InnoDB会对整张表加一个意向锁。
- 其他事务想加表锁，不再需要逐行扫描行锁了，直接根据意向锁来解决锁是否可以兼容加锁即可，极大提高了效率。
4.3.4.1 意向锁分类
- 意向共享锁(IS):
- 由语句select … lock in share mode添加 。
- 与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
- 手动添加
- 意向排他锁(IX):
- 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
- 自动添加
4.4 行级锁
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
4.4.1 行锁分类
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：
- 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
- 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
- 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
4.4.2 InnoDB的行锁
- 共享锁（S）
- 允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁，但是可以自己执行增删改来把行升级成排它锁。
- 排他锁（X）
- 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。即，自己可以读写，其他事务只可以读
- ==注意：==要与表锁区分，表锁的写锁，其他事务既不可读也不可写。
常见的SQL行锁情况：
| SQL | 行锁类型 | 说明 |
|---|---|---|
| INSERT … | 排他锁 | 自动加锁 |
| UPDATE … | 排他锁 | 自动加锁 |
| DELETE … | 排他锁 | 自动加锁 |
| SELECT（正常） | 不加任何锁 | |
| SELECT … LOCK IN SHARE MODE | 共享锁 | 之后执行增删改，则会自动升级成排它锁。 |
| SELECT … FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE |
- 默认情况下，InnoDB的隔离级别是 REPEATABLE READ 即可重复读，另外使用了 next-key 锁 (间隙锁) 进行搜索和索引扫描，以防止幻读 。
警告
- 因为InnoDB的行锁是针对索引的，所以如果where字段中没有索引或者索引失效，那么将升级成表锁！！
- 不管where用的是二级还是主键索引，最后统统优化成主键索引！
4.4.3 记录锁&间隙锁&临键锁
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，为了好好理解锁的应用场景我们分情况讨论。
-
记录锁：
- 主键或唯一索引 等值 命中触发
-
间隙锁
- 使用索引，任
| 锁类型 | 命中时机 | 范围 | 备注 |
|---|---|---|---|
| 记录锁 | 主键或者唯一索引并等值，并命中 | 被查出来的记录本身 | |
| 间隙锁 | 使用索引，不论等值/范围，未命中 | 左开右开 | 间隙锁，就只有间隙 |
| 临键锁 | 使用索引，范围查询，并命中 | 左闭右开 | 记录锁+临键锁的结合 |
-
间隙锁与临键锁有时候很混乱，直接给例子说明（后缀全是lock in share mode）
-
命中记录时
select ... where age = 5 #锁区间： [1,5) [5] [5,10)，即[1,10)其实这我不理解，为什么大家都说是左开右闭，但是我反复试验，age=1的记录 (id自增) 是无论如何都无法插入，我怀疑是临键锁。
或者说，间隙锁不会单独出现，它虽然是左开右开，但是会与记录锁共同作用成临键锁。
select ... where age > 5 #锁区间： [5,+∞)
select ... where age = 15 #锁区间： [10,15) [15] [15,+∞)
-
记录不存在时：
select ... where age > 15 #锁区间： [15,+∞)select ... where age > 1000 #锁区间： [1000,+∞)select ... where age = 1000 #锁区间： [1000,+∞)
三种锁总结：
-
当开启事务并加锁（共享/排它），锁的都是左闭右开！！
-
左闭：到上一条记录，含记录，没有则是==- ∞==
-
右开：到下一条记录，不含记录，没有则是 + ∞
-
-
注意：如果是age>n，
- **实际上，**存在多条记录，那么将会把所有的age>n的记录当成参照体，重新以左闭右开进行加锁，即类似级联加锁。
- 但是，可以简单理解成：[n,+∞)
5. InnoDB引擎
理解为主
5.1 逻辑存储结构
参考 1.1 MySQL体系架构
5.2 架构
5.2.1 内存架构
-
Buffer Pool
- InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。
- 在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。
- 缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
- 缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
- free page：空闲page，未被使用。
- clean page：被使用page，数据没有被修改过。
- dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。
-
Change Buffer
- Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。
-
Adaptive Hash Index
- 自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。
- InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
- 自适应哈希索引，无需人工干预，是系统根据情况自动完成。
- 参数： adaptive_hash_index
-
Log Buffer
- Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。
- 参数:
- innodb_log_buffer_size：缓冲区大小
- innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：
- 1: 日志在每次事务提交时写入并刷新到磁盘，默认值。
- 0: 每秒将日志写入并刷新到磁盘一次。
- 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。
5.2.2 磁盘结构
-
System Tablespace
-
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)参数：innodb_data_file_path
-
File-Per-Table Tablespaces
- 如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中。
- 开关参数：innodb_file_per_table ，该参数默认开启。
-
General Tablespaces
- 通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间
-
Undo Tablespaces
- 撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。
-
Temporary Tablespaces
-
InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。
-
Doublewrite Buffer Files
- 1 CREATE TABLESPACE ts_name ADD DATAFILE ‘file_name’ ENGINE = engine_name;
- 1 CREATE TABLE xxx … TABLESPACE ts_name;
- 双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
-
Redo Log
- 重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo logbuffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。
5.2.3 守护线程
有多个线程池，线程池有多个线程。用来内存与磁盘的后台IO操作。
5.3 事务原理
- 对于这四大特性，实际上分为两个部分。 其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而持久性是通过数据库的锁，加上MVCC来保证的。
5.3.1 redo log
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo logfile）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。
如果没有redolog，可能会存在什么问题的？ 我们一起来分析一下。我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。 当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。 而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。 而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。
5.3.2 undo log
回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和
MVCC(多版本并发控制) 。
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
5.4 MVCC
5.4.1 基本概念
-
当前读
- 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode(共享锁)，select …for update、update、insert、delete(排他锁)都是一种当前读。
-
快照读
- 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- • Read Committed：每次select，都生成一个快照读。
- • Repeatable Read：开启事务后第一个select语句才是快照读的地方。
- • Serializable：快照读会退化为当前读。
- 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
-
MVCC
- 全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
-
隐藏字段
| 隐藏字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。 |
| DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版 本。 |
| DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。 |
利用好undo log，旧数据会放到undo log日志中，并做好链表。
之后就可以根据日志与readview来保证可重复读。
6.MySQL管理
6.1 系统数据库
Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：
| 数据库 | 含义 |
|---|---|
| mysql | 存储MySQL服务器正常运行所需要的各种信息 （时区、主从、用 户、权限等） |
| information_schema | 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类 型及访问权限等 |
| performance_schema | 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集 数据库服务器性能参数 |
| sys | 包含了一系列方便 DBA 和开发人员利用 performance_schema 性能数据库进行性能调优和诊断的视图 |
6.2 常用工具
6.2.1 mysql
该mysql不是指mysql服务，而是指mysql的客户端工具。
- 语法
- mysql [options] [database]
- 选项
- -u, --user=name #指定用户名
- -p, --password[=name] #指定密码
- -h, --host=name #指定服务器IP或域名
- -P, --port=port #指定连接端口
- -e, --execute=name #执行SQL语句并退出
-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。
6.2.2 mysqldump
用来备份数据库或者进行数据库的迁移，包含创建表、插入表、SQL语句等。
-
语法：
- mysqldump [options] db_name
-
选项
option 含义 -u 用户名 -p 密码 -h 服务器ip, localhost默认 -p 连接端口, 3306默认 -
输出选项
option 含义 -n 剔除数据库创建语句 -t 剔除数据表创建语句 -d 剔除数据 -T 生成两文件，一个.sql表结构，一个.txt数据文件非insert
常规使用：
- 完全导出db_name数据库
mysqldump -uroot -p12345 db_name > d:/dn_name.sql
- 剔除表结构
mysqldump -uroot -p12345 -t db_name > d:/dn_name.sql
-
分开备份
-
查看MySQL信任的导出路径
show variables like 'secure_file_priv' # var/lib/mysql-file/ -
导出
mysqldump -uroot -p12345 -T /var/lib/mysql-files/ db_name file_name -
一个file_name.sql放表结构文件，file_name.txt放数据文件，注意这个数据文件不是insert，需要 mysqlimport/source 导入，或者load批量插入。
-
6.2.3 mysqlimport/source
-
mysqlimport
用来导入mysqldump导出的file_name.txt数据文件。
mysqlimport -uroot -p12345 db_name /var/file_name.txt -
source
如果需导入sql文件,可以使用mysql中的source指令来进行导入:
source /root/file_name.sql
1532
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
