mysql 性能优化学习

性能监控

show global status ：查看全局状态
可以列出MySQL服务器运行各种状态值，通过show global status like '查询值%';  可以查询指定信息

 

查看sql语句的执行频次

通过show global status like 'Com_______'; 查询sql语句执行的频次

查看当前数据库的语句访问频次，来判断对于该数据库需要进行哪一种优化，对于增删改操作比重较大的数据库，优化程度可以放轻，主要对查询为主的数据库进行性能优化

 

 

慢查询日志

慢查询日志记录了执行时间超过指定时间的sql语句，通过查看日志可以定位已经执行的sql语句中执行效率低的语句，对其语句格式进行针对优化

配置

慢查询日志默认关闭状态
在 /etc/my.cnf 文件中添加以下配置

# 开启慢日志查询开关
slow_query_log = 1

# 设置慢日志时间为1秒，超过两秒的会被标记为慢查询，记录到日志中

long_query_time = 1

配置完重启mysql服务后，日志生成在 /var/lib/mysql/localhost-slow.log 中，通过对日志中记录的低效率语句进行sql优化来提升性能

 

 

profile

show profiles; 可用来查询SQL执行状态，System lock和Table lock 花多少时间等等，对定位一条语句的I/O消耗和CPU消耗 非常重要。(SQL 语句执行所消耗的最大两部分资源就是IO和CPU)

通过 select  @@have_profiling; 查询数据库版本是否支持该工具
通过 set profiling = 1; 启动工具

通过 show profiles;  来对执行过的sql进行性能分析

通过 show profile for query  query_id;  来查询指定query_id语句各个阶段的耗时

通过 show profile cpu for query query_id;  查看指定id 的局域cpu的使用情况

 

 

 

--在mysql5.7之后，profile信息将逐渐被废弃，mysql推荐使用performance schema
Performance Schema比show profile功能更全，当从SQL语句执行时间等的执行过程分析中无法找到原因进行优化时，用Performance Schema可以直接查看表中有哪些线程在运行，从每个线程的包含信息记录中找到耗时原因。

 

explain 执行计划

explain 或者 desc 命令获取mysql是如何执行 select语句的信息，包括执行过程中表的连接和连接顺序，根据信息定向优化

EXPLAIN  SELECT  字段  FROM  表名  WHERE  条件;

id ： 表示 查询中执行select子句或者操作表的顺序，id越大越优先执行  id相同则顺序执行，此处单表查询，所以只有一条执行顺序

 

type ： 表示查询使用了何种类型，性能由好到差依次为：NULL、system、const、eq_ref、ref、range、index、all。 NULL表示不访问任何表，所以性能最好，一般不可能优化到NULL；const表示使用了主键索引或者唯一索引；ref表示使用了普通索引；range表示使用普通索引进行范围匹配；index表示对索引进行全盘扫描；all表示对整个表进行全表扫描 效率最低

 

possible_key ：可能会用到的索引

 

key ：实际使用的索引

key_len ：索引最大可能长度，非实际长度，在不影响精度前提下，越短越好

 

rows ：mysql预估要执行查询的行数，估值

 

filtered ：返回结果的行数占读取行数的百分比，该数值越大越好

 

 

 

sql语句优化

 

insert 优化

最好控制单次插入语句的数据不超过1000条，通过多条插入语句分批处理所有的数据

# 通过手动提交事务的方式
start transaction;
insert into tb_user values(1,...),(2,...),(3,...)

insert into tb_user values(1000,...),(2000,...),(3000,...)

commit;

# 按主键顺序插入的性能高于乱序（页分裂）

主键乱序插入：9  5  11 21  3  34  1

主键顺序插入：1  3  5  9  11  21  34

大批量插入数据时，通过load指令加载

# 连接数据库时加上参数  --local-infile

>mysql --local-infile -u root -p

# 设置全局参数local_infile为1，开启本地加载文件导入数据功能

set global local_infile = 1;

# 执行load指令

load data local infile '/data' into table 'tb_user' fields terminated by ',' lines terminated by '\n'
load data local infile  表示加载数据文件的路径

into table  表示将数据加载到哪张表中

fields terminated  表示按 ',' 分隔字段

lines terminated  表示按 '\n' 分隔行数据
 

 

主键优化

在innodb存储引擎中，表数据都是根据主键顺序组织存放的，称之为索引组织表，

 

页分裂

innoBD的逻辑存储结构依次为表、段、区、页，一页16k，64个页为一个区，256个区为一段，每页包含2-N行数据（若只有一行数据则为链表）

现有以下情况，两个数据页已满，此时想插入主键值为50的数据

则会进行以下操作：

1、新建一个数据页

2、将主键值为50的数据应该插入的那一数据页 进行对半分割，将其加入到新建数据页中

 3、将主键值为50的数据插入到新建的数据页中

4、修改数据页的指针指向

一般在主键乱序插入的时候才会有页分裂现象会额外增加性能损耗，所以主键顺序插入的性能要高于乱序插入

 

页合并

当删除数据页中的一行记录时，记录对应的地址会被标记为删除，同时可以被其他记录声明使用，当页中删除的记录达到50%时（默认50%，可通过修改MERGE_THERESHOLD更改），innoDB会寻找前后页是否能进行合并以优化空间

 

主键设计原则

1、满足业务需求的情况下，尽量降低主键的长度，因为二级索引的叶子结点会存储主键的值，主键的值越长，二级索引越多，占用的磁盘空间也就越大，在进行查询时也会耗费大量磁盘IO。
2、插入数据尽量选择顺序插入，使用自增主键，乱序插入会产生页分裂现象导致性能额外损耗。
3、尽量不要使用uuid或者其他自然主键，比如身份证号等，长度较长且会导致乱序插入过多

 

 

order by优化

Using filesort

将满足条件的数据行放入排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都是fileort排序

在查询没有建立索引的字段时，会默认使用filesort进行排序

Using index

通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高

对已建立索引的字段进行查询排序，会使用using index；对于联合索引进行查询排序操作时，同样要满足最左前缀法则

 

现有以下情况，对两个字段name和age创建一个联合索引：

create index idx_user_name_age on user(name,age);

执行以下查询：

select id, name, age from user order by age;

select id, name, age from user order by name;

select id, name, age from user order by name desc, age desc;

三种语句执行结果均是通过Using index进行排序，但是如果出现

select id, name, age from user order by name, age desc;

一个升序一个降序的情况，name字段会通过Using index进行排序，而age字段会通过filesort进行排序，降低效率；此时要额外建立一个索引并指定字段排序方式

create index idx_user_name_age_ad on user(name asc, age desc);

后续再执行

select id, name, age from user order by name, age desc;

就会走idx_user_name_age_ad索引，通过Using index方式排序

 

如果不可避免的出现filesort，在大数据排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256K)

 

 

group by优化

主要是索引对分组操作的影响，为了避免出现using temporary使用临时表的情况降低效率，对排序的字段也可以建立索引提高效率，最好是覆盖索引；对于索引是联合索引的情况，同样要满足最左前缀法则

 

 

limit优化

对于大数据量的情况下，分页越往后，耗时越长效率越低，也会出现 limit 1000000,10 排序一百万条数据，最后仅返回十条数据，其他的记录全部丢弃的情况，代价非常大

官方给出的方案是通过覆盖索引加子查询的形式优化

select u.* from

        user u ,

        (select id from user oder by id limit 1000000,10) u2

        where u.id = u2.id;

 

 

count优化

count()对于返回的结果，一行行的判断，如果不是NULL才进行加一操作，并非数据存在就加一

常用形式：count(*)  count(主键)  count(字段)  count(1)

count(主键)  innoDB会遍历整张表，把每一行的主键取出来返回给服务层，服务层进行累加操作，因为主键不可能为null，所以计数准确

 

count(字段)  如果字段值没有非空约束，innoDB会遍历表取出字段值返回给服务层，服务层判断是否为空，再进行累加操作；如果有非空约束，服务层不进行判断，直接按行累加

 

count(1)  innoDB遍历表，但不取值，服务层对于返回的每一行仅放一个数字 1 进去，直接按行累加

count(*)  innoDB不会把全部字段取出，而是有专门优化，不取值，服务层直接按行累加

按效率排序： count(*) ≈ count(1) > count(主键) > count(字段)  

 

update优化

现有以下情况，表users中有id 和 name字段，其中id有主键索引，name没有索引，表中有两条数据 (id = 1, name = 'win'),(id = 2, name = 'fin');

1、开启一个事务，在事务中通过主键更新数据，暂时不提交，

begin;

update users set name = 'win1' where id = 1;

此时在另一个窗口开启事务然后同样对id = 1的行数据进行更新

begin;

update users set name = 'win2' where id = 1;

就会出现阻塞，因为窗口1中的事务为id = 1的行数据上了行锁，但是对id = 2的数据进行更新则没有问题

 

2、开启一个事务，在事务中通过没有索引的字段更新数据，暂时不提交

begin;

update users set name = 'win1' where name = 'win';

此时在另一个窗口开启事务，窗口2中的事务无论对users表中哪条数据进行更新操作都会阻塞，因为name字段没有索引，加的锁就不是行锁而是表锁

innoDB的行锁是针对索引加的锁，而不是记录，并且索引不能失效，否则会从行锁升级成表锁
在使用update进行更新数据时，一定要根据索引字段进行更新，且索引不能失效

 

 

存储对象

视图 view

视图是一种虚拟存在的表。视图中的数据是在使用视图时动态生成；，视图只保存查询的sql语句，需要时通过保存的语句动态获取数据；
视图不仅可以简化用户对数据的理解，也可以简化对数据的操作，经常被使用的查询可以定义成视图，从而使用户不必为每次的操作都指定条件；
同时数据库可以授权用户权限，但是不能授权到指定行和列上。通过视图，用户只能查询和修改指定的数据

 

创建视图

CREATE  [OR  REPLACE]  VIEW  视图名称[(列名列表)]  AS SELECT语句 [WITH [CASCADED | LOCAL]  CHECK  OPTION]

or replace : 替换某一个视图，可省略

with cascaded check option : 判断对视图的数据修改（增删改）是否符合创建视图时定义的条件，如果不符合则阻止该操作； mysql还允许基于一个视图创建另一个视图，在对视图进行数据修改时不仅会检查是否符合当前视图条件定义，还会检查是否符合当前视图所依赖的视图条件定义，全部符合时，数据修改才会生效

 

查询视图

# 查看创建视图语句

SHOW CREATE VIEW 视图名称;

# 查看视图数据

SELECT * FROM 视图名称 [OPTION] ;

 

修改视图

方式一：创建视图的语句加上 or place 将其替换掉

方式二

ALTER VIEW 视图名称 AS SELECT ;

 

删除视图

DROP VIEW [IF EXISTS]  视图名称;

 

视图内数据的操作

视图内数据的操作不会对视图生效，而是对创建视图时关联的表生效，当视图的数据来自多个基本表时，不允许对视图进行添加和删除操作

例如创建了以下视图

create  view  user_v_1 as select id,name from user where id < 10;

如果对视图进行插入数据

insert into user_v_1 values(2, 'ls');

该插入语句是对视图关联的user表生效，而非视图，同时因为视图是动态生成数据，即使不对视图进行更新数据也会同步到视图中

但是以下插入语句

insert into user_v_1 values(20, 'ww');

虽然同样对user表生效，但是视图中不会同步该条数据，因为视图在创建时的条件为id < 10，因此即使对user表的插入操作成功了，视图也不会将其同步
为了避免这种情况出现，在创建视图的时候加上with cascaded check option，阻止不满足视图创建时条件定义的数据插入

 

视图检查选项

WITH  CASCADED | LOCAL  CHECK OPTION，阻止不满足视图创建时条件定义的数据修改

CASCADED

翻译为级联，添加了该检查选项的视图，默认会对当前视图依赖的视图添加同样的检查选项

创建一个视图v1

create view v1 as select id,name from user where id < 10;

对v1进行插入操作

insert into v1 values(5, 'we');

insert into v1 values(15, 'dw');

第一条插入语句能成功执行，刷新视图可以看到插入的数据
第二条也可以成功执行，但是视图中不会显示该条数据，因为第二条数据不符合视图创建时的条件定义，因此数据插入到了表user中，但是不会在视图中显示

基于视图v1 创建视图v2

create view v2 as select id,name from v1 where id > 5 with cascaded check option;

对视图v2添加检查选项 cascaded，

此时再执行如下插入语句

insert into v2 values(8, 'ggt');

insert into v2 values(3, 'xd');

insert into v2 values(15, 'cw');

其中第一条语句同时符合v1和v2的条件所以成功执行
第二条语句不符合v2的条件被阻止
第三条语句符合v2的条件，然后去上一级依赖视图v1，原本v1没有检查选项check option 即使不满足条件也可以执行，但是因为v2检查选项cascaded的级联特性，v1也被添加了cascaded，因此需要检查是否符合v1的条件，不符合所以被阻止

在v2的基础上创建视图v3

create view v3 as select id,name from v2 where id = 6;

执行以下语句

insert into v3 values(6, 'qw');

insert into v3 values(7, 'as');

insert into v3 values(12, 'zx');

其中第一条符合v1 v2 v3的条件，执行成功并可以在v3中查询；第二条不符合v3，符合v1,v2，因为v3没有cascaded，所以会执行成功，但是不能在v3中查询；第三条不符合v1,v2的条件，所以会被阻止

 

LOCAL

local 和 cascaded的作用相同，添加了local的视图需要在数据修改时检查数据是否符合当前视图的定义条件，不符合阻止执行，符合去上一级依赖进行检查

不同于cascaded的是，添加了cascaded的视图的在返回上一级依赖进行检查时如果没有找到检查选项，会对上一级依赖进行级联cascaded，如果有检查选项则不做操作
而local检查选项，不会级联，仅对当前视图生效

 

视图的更新 

要使视图可以进行更新操作，必须满足视图中的行与基础表中的行存在一对一的关系，任何对行数据的修改都会让视图不可更新，例如聚合函数、distinct、group by、having、union [all]等都会让视图不可进行更新操作

 

缺点

1. 可维护性问题：如果基础表结构发生变化（例如列的添加、删除或重命名），视图可能会变得不可用。

2. 性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的.

3. 表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更新视图。

 

 

 

 

 

存储过程

存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以减少数据库和应用端之间的传输，提高数据处理的效率。就是数据库sql代码的封装和重用

特点：封装，服用；可以接收参数，也可以返回数据；减少网络交互提升效率。

创建

CREATE PROCEDURE  名称([参数])
BEGIN

        # sql语句

END;

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

-- 删除名为idata的存储过程
drop procedure idata;
-- 改变默认的命令分隔符，以便在存储过程定义中使用分号
delimiter ;;
-- 创建名为idata的存储过程，不指定参数
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
-- 前面指定了两个分号;;为命令分隔符，表示该命令已结束
end;;
-- 将命令分隔符改回单个分号
delimiter ;
-- 调用存储过程idata
call idata();

create table t1 like t2;
create table t3 like t2;
insert into t1 (select * from t2 where id<=100);
insert into t3 (select * from t2 where id<=200);

 

 

 

 

调用

call  存储过程名称([传入参数])

 

查看

# 查看指定数据中的存储过程的信息

SELECT * FROM

        INFORMATION_SCHEMA.ROUTINES WHERE ROUTINES.SCHEMA='数据库名';

 

# 查看存储过程定义语句
SHOW CREATRE PROCEDURE 存储过程名称;

 

删除

DROP PROCEDURE [IF EXISTS]  存储过程名称;

 

变量

系统变量

由mysql提供，分为全局变量 GLOBAL  ，会话变量 SESSION

# 查看系统变量

SHOW [ SESSION | GLOBAL] VARIABLES LIKE '...';

# 查看指定变量

SELECT @@[SESSION | GLOBAL ].系统变量名

# 设置系统变量

SET [SESSION | GLOBAL ] 系统变量名 = 值;

如果没有指定session/global，系统默认为session
重启mysql服务后，设置的变量都会初始化，想要设置不失效，需要修改my.cnf配置文件

用户变量

是用户根据需要自行定义的变量，不需要声明，用的时候通过'@变量名'使用 

# 赋值

SET @var_name = value ,[@var_name_2 = value]...;

SET @var_name := value ,[@var_name_2 := value]...;

SELECT @var_name := value, ,[@var_name_2 := value]...;

SELECT 字段名 INTO @var_name FROM 表名;
将表中查询出来的数据赋值给 var_name 变量

# 读取变量

SELECT @var_name;

 

局部变量

需要通过DECLARE声明定义；可用作存储过程内的局部变量和输入参数，作用域在BEGIN...END之间

# 声明

DECLARE  变量名  变量类型 [DEFAULT ...];

DEFAULT ： 默认值

# 赋值

SET 变量名 = 值;

SET 变量名 := 值;

SELECT 字段名 INTO 变量名 FROM 表名 ...;

# 读取变量

SELECT 变量名;

参数

CREATE  PROCEDURE  名称(IN/OUT/INOUT 参数名 参数类型)

BEGIN

...

END;

IN  ：输入参数，需要调用时传入值

OUT  ：返回参数，该类参数可以作为返回值

INOUT ：同时拥有以上两类特性

 

IF

IF 条件1  THEN

        ...

ELSEIF 条件2 THEN

        ...

ELSE

        ...

END IF;

 

CASE

语法一

CASE case_value

        WHEN value1 THEN statement_list1

        [WHEN value2 THEN statement_list2]

        [ELSE statement_list]

END CASE;

如果表达式case_value的值为 value1 执行 statement_list1，为 value2 执行 statement_list2，没有在WHEN中匹配到，执行statement_list

 

语法二

CASE 

        WHEN value1 THEN statement_list1

        [WHEN value2 THEN statement_list2]

        [ELSE statement_list]

END CASE;

略

 

WHILE

WHILE 条件 DO

        ...

END WHILE;

 

REPEAT

REPEAT

        ...

        UNTIL 条件

END REPEAT;

 

LOOP

[label:] LOOP

        ...

END LOOP [label];

begin_label  ：给当前LOOP循环定义一个标签

LEAVE  ： 类似break; 用于退出循环

ITERATE ：类似continue; 

用法：LEAVE / ITERATE label; 

 

游标 cursor

用来存储查询结果集的数据类型，存储过程内部可以对结果集进行循环处理

声明游标

DECLARE 游标名称  CURSOR FOR 查询语句;

使用游标前必须打开游标

OPEN 游标名称;

获取游标记录

FETCH 游标名称 INTO 变量;

关闭游标

CLOSE 游标名称; 

 

条件处理程序 Handler

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ...statement;

handler_action:

        CONTINUE ：继续执行当前程序

        EXIT：终止当前程序

condition_value:

        SQLSTATE sqlstate_value：状态码，如02000

        SQLWARNING：所有以01开头的状态码简写

        NOT FOUND：所有以02开头的状态码简写

        SQLEXCEPTION：没有被上面两个捕获的状态码简写

用于在执行过程中的指定情况condition_value出现时，如果存在statement就将其执行，最后执行handler_action

 

 

 

案例

需求：根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业( profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

delimiter ;;

create procedure pf(in uage int)

begin

        declare user_count int default 0;

        declare uname varchar(20);

        declare upro varchar(50);

        declare cur_user cursor for

                select name,profession from tb_user where age < uage;

        select count(*) into user_count from tb_user;
 

        drop table if exists user_name_pro;

        create table if not exists user_name_pro(

                id int primary key auto_increment,

                name varchar(20) not null,

                profession varchar(50) not null

        );

        

        open cur_user;

        

        while user_count>0 do

                fetch cur_user into uname,upro;

                insert into user_name_pro values (null,uname,upro);

                set user_count := user_count - 1;

        end while;

 

        close cur_user;

END;;

delimiter ;

使用handler

delimiter ;;

create procedure pf(in uage int)

begin

        declare uname varchar(20);

        declare upro varchar(50);

        declare cur_user cursor for

                select name,profession from tb_user where age < uage;

        -- 当遇到状态码02000，就关闭游标，然后执行退出操作

        declare exit handler for SQLSTATE '02000' close cur_user;
 

        drop table if exists user_name_pro;

        create table if not exists user_name_pro(

                id int primary key auto_increment,

                name varchar(20) not null,

                profession varchar(50) not null

        );

        

        open cur_user;

        

        while true do

                fetch cur_user into uname,upro;

                insert into user_name_pro values (null,uname,upro);

               

        end while;

 

        close cur_user;

END;;

delimiter ;

 

 

 

触发器

在 insert/update/delete 语句运行之前或之后触发并执行设定好的sql语句，触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作

使用OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的；现在触发器仅支持行级触发（当某个语句影响了五行数据，触发器就被触发五次），不支持语句级触发

INSERT型触发器：NEW表示将要或者已经新增的数据，OLD没有作用

UPDATE型触发器：OLD表示修改之前的数据，NEW表示将要或者已经修改的数据

DELETE型触发器：OLD表示将要或已经删除的数据，NEW没有作用

 

创建

CREATE TRIGGER trigger_name

        BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW

BEGIN

        trigger_name;

END;

案例：将对表tb_user的操作日志，添加到user_logs中

CREATE TABLE user_logs (  
    id INT(11) NOT NULL AUTO_INCREMENT,  
    operation VARCHAR(20) NOT NULL COMMENT 'insert/update/delete',  
    operate_time DATETIME NOT NULL COMMENT '操作时间',  
    operate_id INT(11) NOT NULL COMMENT '操作ID',  
    operate_params VARCHAR(500) COMMENT '操作参数',  
    PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create trriger tb_user_insert_trigger

        after insert on tb_user for each row

begin

        insert into user_logs values

        (null, 'insert', now(), new.id, concat('插入的数据为:id=', new.id, 'name=', new.name));

end;

create trigger tb_user_update_trigger

        after update on tb_user for each row

begin

        insert into user_logs values

        (null, 'update', now(), new.id,

        concat('更新前数据:id=', old.id, 'name=', old.name,

        ' | 更新后的数据:id=', new.id, 'name=', new.name

        ));

end;

create trigger tb_user_delete_trigger

        after delete on tb_user for each row

begin

        insert into user_logs values

        (null, 'delete', now(), old.id, concat('删除的数据为:id=', old.id, 'name=', old.name));

end;

 

 

 

 

 

查看

SHOW TRIGGERS;

 

删除

DROP TRIGGER [db_name.]trigger_name;

 

 

 

锁

保证数据并发访问的一致性、有效性

select语句默认不会加任何类型的锁

全局锁

对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续除了读之外的操作都会被阻塞。常用场景是做全库逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整

# 添加全局锁

FLUSH TABLES WITH READ LOCK;

# 释放全局锁

UNLOCK TABLES;
 

 数据库备份

# 备份数据库

mysqldump -uroot -p1234 dbname > dbname.sql

 

# 不加锁的情况下备份，通过快照读实现
mysqldump --single-transaction -uroot -p1234 dbname > dbname.sql

 

表级锁

发生锁冲突的概率最高，并发度最低

表锁

加锁语法

lock tables 表名 read/write;

释放锁

unlock tables; 或者 关闭会话断开连接自动释放锁

1、表共享读锁 read lock

客户端对表加上读锁之后，只能对该表进行读操作，进行写操作会报错；其他客户端对该表同样只能进行读操作，阻塞写操作直到锁释放

2、表独占写锁 write lock

客户端对表加上写锁之后，当前客户端可以进行读写操作，其他客户端不能对该表进行任何操作直到释放锁

 

 

元数据锁 meta data lock

MDL加锁过程由系统控制，无需显示使用，在访问一张表时会自动加上；主要作用是维护元数据的数据一致性，元数据主要作用就是维护表结构的数据一致性；在表上有活动事务时，不可以对元数据进行写入操作

当对一张表进行增删改查（DQL,DML）的时候，加MDL读锁（共享）；当对一张表进行修改（alter）操作时，加MDL写锁（排他）

1、对表加上表锁时，自动加上对应的元数据锁
2、执行select语句自动加上元数据共享读锁，select ... lock in share mode表示加上意向共享锁
3、执行insert update delete语句自动加上元数据共享写锁（本质也是读锁）；SELECT ... FOR UPDATE 意向排它锁一般在事务中使用
4、alter table ... 修改表结构的时候加写锁，具有排他特性，不能与其他锁同时存在

情景一：在会话1中开启一个事务并执行一个select语句不提交事务，此时在会话2中开启一个事务，可以执行insert update delete select语句，因为执行这些语句加的元数据锁时兼容的

情景二：在会话1中开启一个事务并执行一个select语句不提交事务，此时在会话2中开启一个事务，该事务会阻塞alter table 操作，因为alter table加的锁具有排他性，与select语句加的锁互斥，只有在提交会话1的事务后才会接触阻塞状态

 

意向锁

情景：线程A执行update操作，对某行记录加上了行锁，此时线程B想对整张表加上表锁，就要对表逐行检查是否有行锁，效率很低

意向锁是innoDB中为了避免行锁与表锁的冲突，使得表锁不用检查每行数据是否加锁，减少表锁的检查。
当线程A对某行记录加上行锁的同时加上一个意向锁，线程B在加表锁的时候会直接检查意向锁来提高效率，当检查到意向锁跟表锁兼容的，就可以加表锁；不兼容的时候就会阻塞，直到行锁被释放

创建

# 共享锁  IS

由select ... lock in share mode添加

与表共享锁（read lock）兼容，与表独占锁（write lock）互斥，本质上表独占锁也不允许其他客户端访问

意向锁之间不会互斥

# 排他锁   IX

insert/update/delete/select ... for update

与表共享锁和表排他锁都互斥

是 SQL 中用于锁定选中记录行以供当前事务进行更新或删除操作的语句，防止其他用户或线程在你完成更新或删除操作之前修改这些记录。

begin;
  
SELECT * FROM orders  
WHERE customer_id = 123  
FOR UPDATE;  
  

UPDATE orders  
SET status = 'PROCESSED'  
WHERE customer_id = 123 AND order_id = ?; 
  
COMMIT;

 

 

行级锁

每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高。
innoDB的数据是基于索引组织的，行锁是通过对所用上的索引项加锁来实现的，而不是对记录加的锁

行锁 record lock

锁定单个记录，防止其他事务对该记录进行update和delete

1、共享锁 S

select ... lock in share mode

允许其他共享锁事务去读一行，阻止其他事务获得相同数据集的排它锁；简而言之共享锁和共享锁兼容，和排他锁互斥

2、互斥锁 X

insert/update/delete/select ... for update

仅允许获取排他锁的事务更新数据，与其他事务获取的共享锁和排他锁互斥

在进行针对唯一索引unique进行检索时，对已存在的记录进行等值匹配时，会自动优化成行锁record lock
行锁是针对索引加的锁，不通过索引进行检索数据的话，innoDB将会对表中所有记录加锁，此时就会升级成表锁

间隙锁 gap lock

锁定索引索引之间的间隙，不锁定记录，确保索引记录间隙不变，防止其他事务在这个间隙进行insert产生幻读（在同一个事务中，一开始没有读取到某个数据，随后要操作该数据时又读取到其他事务插入的该数据）

临键锁 next-key lock 

行锁和间隙锁的组合，同时锁住当前记录的数据和该记录之前的所有间隙

 

默认情况下，innoDB在repeat read事务隔离级别运行，该级别产生幻读，所以innoDB使用临键锁进行搜索和扫描，防止幻读

 

 

 

innoDB引擎

innoDB架构

 

内存架构

Buffer pool：缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

Change Buffer:更改缓冲区(针对于非唯一二级索引页)，在执行DML语句时，如果这些数据页没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer 中，在未 来数据被读取时，再将数据合井恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。二级索引的叶子结点的值时相对随机的，每一次都操作磁盘会造成大量IO，将数据更改存放在更改缓冲区中，然后在由缓冲池操作磁盘响应数据最后在缓冲池中进行合并刷新到磁盘中，能减少磁盘IO

Adaptive Hash Index:自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监 控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash 索引。
自适应哈希索引，无需人工干预，是系统根据情况自动完成。开关参数：adaptive_hash_index

LogBuffer:日志缓冲区，用来保存要写入到磁盘中的log日志数据(redo log、undo log)，默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中；如果需要更新、插入或删除许多行的事务，增加 日志缓冲区的大小可以节省磁盘I/0。
参数: innodb_log_buffer_size:缓冲区大小
innodb_flush_log_at_trx_commit:日志刷新到磁盘时机

 

磁盘架构

System Tablespace:系统表空间是更改援冲区（change Buffer）的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典（元数据）、undolog等)  参数:innodb_data_file_path，文件:ibdata1

File-Per-Table Tablespaces:每个表的文件表空间包含该表的结构、数据和索引，以后缀ibd的文件形式存储在磁盘上。 参数:innodb_file per table

General Tablespaces:通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。通用表空间是MySQL默认的表空间类型。在通用表空间中，多个表和索引存储在共享的.ibd文件中。这意味着多个表可以共享已创建的表结构，从而减少磁盘使用量。当创建新表时，它们将自动放置在已有的共享表空间中。

创建一个名为ts_name通用表空间，表空间对应的磁盘文件是mydb.ibd

create tablespace ts_name add datafile 'mydb.ibd' engine = innodb;

创建表时指定表数据存放在指定表空间中
create table xxx ...  tablespace ts_name

UndoTablespaces:回滚表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间(初始大小16M,undo_001和undo_002)，用于存储undo log回滚日志。

Temporary Tablespace:临时表空间，存储用户创建的临时表数据

Doublewrite Buffer Files:双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据保证数据的安全性。

Redo Log:重做日志，是用来实现事务的持久性，事务的持久性依赖于重做日志。该日志文件由两部分组成:重做日志缓冲 (redo log buffer)以及重做日志文件(redo log),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘发生错误时,进行数据恢复使用。
当一个数据页在内存中被修改后，但这些修改尚未被写回到磁盘上的数据文件时，这个数据页被称为“脏页”，脏页是缓冲池（Buffer Pool）中已被修改但尚未同步到磁盘的数据页。操作系统或数据库管理系统会在适当的时候将这些脏页的数据写回到磁盘，以保证数据的一致性和持久性。以循环方式写入两个重做日志文件

 

后台线程

将缓存池中的数据在合适的时机刷新到磁盘文件中

Master Thread

核心后台线程，负责调度其他线程，还负责将缓存池中的数据异步刷新到磁盘中，保证数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收（undo页（undo logs）是InnoDB存储引擎用于事务回滚和恢复机制的重要组成部分）

 

IO Thread

innoDB中大量使用了AIO（异步io）在处理IO请求，这样可以极大提高数据库的性能，IO Thread主要负责这些IO请求的回调

 

Purge Thread

主要用于回收事务已经提交的undo log，在事务提交后，undo log可能不用了

 

Page Cleaner Thread

协助Master Thread刷新脏页到磁盘的线程，减轻核心线程的压力