本文详细讲解了MySQL中B+树的基础概念，包括其在逻辑分层中的作用，以及SQL优化的重要性及其原因。深入剖析了索引的类型、创建、删除和查询，重点讲解了B+树在索引中的应用。此外，还介绍了SQL性能问题的分析方法，如执行计划和索引类型对查询性能的影响。最后，涵盖了表的优化策略和慢查询日志的使用，以及锁机制在并发控制中的角色。
Mysql优化
B树和B+树的认识
B树的认识：
B+树的认识：
1、mysql逻辑分层
分为四层：连接层 服务层 引擎层 存储层
查询数据库引擎
show engines\G;
默认引擎InnoDB
查看当前使用的引擎
show variables like '%storage_engine%';
create database myDB;
use myDB;
create table tb(
id int(4) auto_increment,
name varchar(5),
dept varchar(5),
primary key(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2、Sql优化
为甚要进行sql优化？
产生的原因
写的sql性能低、执行时间长、等待时间过长、sql语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲区、线程数）
1、SQL:
编写过程： select … from … join … on…where…group by…having…order by…limit…
解析过程：from … on … join…where… …group by…having…select…order by…limit…
2、SQL优化概念
主要是 优化 索引
索引 ： 相当于一个书的目录
索引关键字 ： index
索引是为了帮助mysql高效获取数据的数据结构。索引是数据结构（B树、B+树等）
索引这么好，多加行不行？
索引的弊端
- 索引本身很大，可以存放在内存/硬盘（通常为 硬盘）
- 索引不是所有情况均适用：
- 少量的数据
- 频繁更新的字段
- 很少使用的字段
- 索引会降低增删改的效率 （大部分情况都是查询）
索引的优势
- 提高了查询的效率（降低io的使用率）
- 降低cpu使用率（…order by age desc，因为索引B+树本身就是一个排好序的结果，因此在排序时可以直接用！）
3、B+树和索引认识
4、索引的分类
- 单值索引： 单列 ， age ；一个表可以有多个单值索引，name
- 唯一索引： 不能重复 ，一般使用id ，可以是null
- 复合索引： 多个列组成的索引，相当于书的二级目录（name，age）
- 主键索引： 不能重复 ，一般使用id ，不能是null
5、创建索引
方式一：
# create 索引类型 索引名 on 表(字段)
# 单值索引:
create index dept_index on tb(dept);
# 唯一索引
create unique index name_index on tb(name);
# 复合索引
create index dept_name_index on tb(dept,name);
方式2:
# alter table add 索引类型 索引名(字段)
# 单值索引
alter table tb add index dept_index(dept);
# 唯一索引
alter table tb add unique name_index(name);
# 复合索引
alter table tb add index dept_name_index(dept,name);
DDL语句会自动提交，DML要手动提交
注意：如果一个字段是primary key ， 则该字段默认就是 主键索引
6、删除索引
# drop index 索引名 on 表名；
drop index name_index on tb;
7、查询索引
# show index from 表名；
show index from tb\G;
3、sql的性能问题
1、分析sql的执行计划 ： explain ， 可以模拟sql优化器执行sql语句，从而让开发人员看到自己编写的sql的优化。
2、mysql查询优化器会干扰我们的优化
sql优化的准备
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
id : 编号
select_type : 查询类型
table : 表
type : 类型
possible_keys : 预测用到的索引
key : 实际使用到的索引
key_len : 实际使用到的索引的长度
ref : 表之间的引用
rows : 通过索引查询到的数据量
Extra : 额外的信息
create table course( cid int(3), cname varchar(20), tid int(3));create table teacher( tid int(3), tname varchar(20), tcid int(3));create table teacherCard( tcid int(3), tcdesc varchar(200));insert into course values(1,'java',1);insert into course values(2,'html',2);insert into course values(3,'sql',3);insert into course values(4,'web',2);insert into teacher values(1,"张三",1);insert into teacher values(2,"里斯",2);insert into teacher values(3,"王五",3);insert into teacher values(4,"赵六",4);insert into teacherCard values(1,"我是张三");insert into teacherCard values(2,"我是里斯");insert into teacherCard values(3,"我是王五");# 查询课程编号为2 或者 教师证编号为3的老师信息select t.* from teacher t,course c,teacherCard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);
4、分析执行计划的列
1、id
id值相同 ， 从上往下 ， 依次执行。 t3-tc3-c4
表的执行顺序 因数量的个数的改变而改变 原因：笛卡尔积
数据小的表优先查询
id值不同，id值越大，越先执行，本质（在嵌套子查询的时候，先执行最内层，在执行外层）
# 查询教授sql课程老师的描述信息# 连表查询select tc.tcdesc from teacherCard t,course c,teacherCard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cname='sql');# 转为子查询SELECT tc.tcdesc FROM teacherCard tcWHERE tc.tcid = ( SELECT t.tcid FROM teacher t WHERE tid = ( SELECT c.tid FROM course c WHERE c.cname = 'sql' )); # 连接 + 子查询select tc.tcdesc from teacherCard tc,teacher t where t.tcid = tc.tcid and t.tid = (select c.tid from course c where c.cname='sql');
先 c — t — tc，原因：先执行最内层，再执行外层！！！
总结
- id相同，顺序执行
- id不同，越大越先执行
2、select_type（查询类型）
1、PRIMARY ： 包含子查询sql中的 主查询 （最外层）
2、SUBQUERY：包含主查询sql中的 子查询 （非最外层）
3、SIMPLE ： 简单查询（不包含 连接查询 和 子查询，最简单的查询！！）
4、derived ： 衍生查询（使用到了临时表）
- 在from子查询中只有一张表
explain select cr.cname from (select * from course where tid in (1,2)) cr;
- 在from子查询中，如果有table1 union table2 ， 则table1 就是derived
explain select cr.cname from (select * from course where tid=1 union select * from course where tid=2) cr;
5、union：看上例
6、union result（告诉开发者那些表存在union查询）
3、type（索引类型）
| type | 说明 |
|---|---|
| ALL | 全数据表扫描 |
| index | 全索引表扫描 |
| RANGE | 对索引列进行范围查找 |
| INDEX_MERGE | 合并索引，使用多个单列索引搜索 |
| REF | 根据索引查找一个或多个值 |
| EQ_REF | 搜索时使用primary key 或 unique类型 |
| CONST | 常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 |
| SYSTEM | 系统，表仅有一行(系统表)。这是const联接类型的一个特例。 |
对type进行优化首要条件： 必须得有索引
常见类型 ： system > const > eq_ref > ref > range > index > all
其中system、const只是理想情况；实际能达到 ref > range
01、system
system（基本不可能）： 只有一条数据的系统表 ； 或者 衍生表只有一条数据的主查询！
create table test01( tid int(3), tname varchar(20));insert into test01 values(1,"漳卅");# 增加索引alter table test01 add constraint tid_pk primary key(tid);explain select * from (select * from test01) t where tid = 1
02、const
const ：仅仅能查询到一条数据的sql，用到了primary key 或者 unique索引 （类型 与索引类型有关）
explain select tid from test01 where tid=1;
alter table test01 drop primary key;create index test01_index on test01(tid); # 一般索引
03、eq_ref
eq_ref：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有一个，不能多，不能为0）
select … from … where name = …;（name有索引，查到的值是唯一的！）
常见于唯一索引 和 主键索引
alter table teacherCard add constraint pk_tcid primary key(tcid); # teacherCard tcid增加主键alter table teacher add constraint uk_tcid unique index(tcid); # teacher 增加唯一索引select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid;# 以上sql，用到的索引是 t.tcid 及就是teacher表中的tcid字段; 如果teacher表的数据个数 和 连接查询的数据个数一致(都是3条数据)，则有可能满足 eq_ref级别，否则无法满足# 可遇不可求！！！
产生原因：
04、ref
ref：非唯一性索引：对于每个索引键的查询，返回匹配的所有行（0，多）
05、range
range：检索指定范围的行，where后面是一个范围查询（between，in，>，<，>=，<=）
alter table teacher add index tid_index(tid);explain select t.* from teacher t where t.tid in (1,2);explain select t.* from teacher t where t.tid > 3;
注意：in有时候会失效，从而转为 无索引all
查询到的数据远小于表的数据，不然会失效！！！！
explain select t.* from teacher t where t.tid in (1,2);
06、index
index： 查询全部索引中的数据
将索引树的数据查询一遍
只需要扫描索引表，不需要表中的 全部数据！
07、all
all：查询所有表的数据
查表的全部数据
需要表中的 全部数据，进行全表扫描！
总结
system/const：结果只有一条数据。
eq_ref：结果多条，每条数据是唯一的。
ref：结果多条，每条数据是0或者多条。
4、possible_keys
可能用到的索引，是一种预测，不准！！
5、key
实际使用到的索引
6、key_len
索引的长度
作用：经常用于判断复合索引是否被完全使用！ （a,b,c）。
create table test_kl( name char(20) not null default '');alter table test_kl add index name_index(name);explain select * from test_kl where name='';
alter table test_kl add column name1 char(20); # 增加name1列alter table test_kl add index name1_index(name1); # name1增加索引explain select * from test_kl where name1='';
如果索引字段可以为null，mysql则会用一个字节来标识该字段！！
drop index name1_index on test_kl;drop index name_index on test_kl;# 增加复合索引alter table test_kl add index name_name1_index(name,name1);
alter table test_kl add column name2 varchar(20);alter table test_kl add index name2_index(name2);explain select * from test_kl where name2='';
char和varchar跟字符编码也有密切的联系。
- latin：1个字符占用1个字节
- gbk：1个字符占用2个字节
- utf8：1个字符占用3个字节。
不同字符编码占用的存储空间不同
7、ref
注意与type中 ref 的区别
这里的ref作用： 指明当前表所参照的字段
select ... where a.c = b.x; # a表的c字段 引用了 b表的x（其中b.x可以为常量，const）
explain select * from course c,teacher t where c.tid = t.tid and t.tname= "王五";
8、rows
被索引优化查询到的数据的条目数
explain select * from course c,teacher t where t.tid = c.cid and t.tname="张三";
9、Extra
01、using filesort
using filesort : 性能消耗大，需要额外的一次排序（先查询）
create table test02( a1 char(3), a2 char(3), a3 char(3), index idx_a1(a1), # 添加索引 index idx_a2(a2), # 添加索引 index idx_a3(a3) # 添加索引);explain select * from test02 where a1="" order by a1;
explain select * from test02 where a1="" order by a2;
小结
- 对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；
- 对于单索引，如果排序和查找不是同一个字段，则不会出现using filesort；
- 避免： where 哪些字段 ， 就order by 哪些字段；
- 一般出现在order by 语句中！！！
对于复合索引：不能跨列（最佳左前缀）
drop index idx_a1 on test02;drop index idx_a2 on test02;drop index idx_a3 on test02;alter table test02 add index a1_a2_a3_index(a1,a2,a3);explain select * from test02 where a1='' order by a3;
explain select * from test02 where a2='' order by a3;
explain select * from test02 where a1='' order by a2;
总结
避免：where 和 order by 按照复合索引的顺序使用，不要跨列或者无序使用。
02、using temporary
using temporary ： 性能损耗大，用到了临时表。
一般出现在 group by语句中！！！！
create table test03( a1 int(4) not null, a2 int(4) not null, a3 int(4) not null, a4 int(4) not null);alter table test03 add index idx_a1_a2_a3_a4(a1,a2,a3,a4);explain select a1,a2,a3,a4 from test03 where a1='' and a2='' and a3='' and a4='';
explain select a1,a2,a3,a4 from test03 where a4='' and a3='' and a2='' and a1='';
以上两个sql使用到了全部的索引！！！
explain select a1,a2,a3,a4 from test03 where a1='' and a2='' and a4='' order by a3;
以上索引用到了a1,a2两个索引，该两个字段 不需要回表查询 ----> using index；
a4因为跨列使用了索引，索引失效了，就需要回表查询 ----> using where
以上可以通过key_len进行校验！
explain select a1,a2,a3,a4 from test03 where a1='' and a4='' order by a3;
以上sql出现了 using filesort(文件内排序，“多了一次额外的查找/排序”) ：不要跨列使用！！！（where 和 order by拼起来 不要跨列使用！！）
explain select a1,a2,a3,a4 from test03 where a1='' and a4='' order by a2,a3;
总结
-
如果（a,b,c,d）复合索引 和 使用的顺序完全一致（且不跨列使用），则全部使用
-
如果（a,b,c,d）复合索引 和 使用的顺序部分一致（且不跨列使用），则部分使用
using temporary ： 性能损耗大，用到了临时表。
一般出现在 group by语句中！！！！
explain select a1 from test02 where a1 in ('1','2','3') group by a1;
explain select a1 from test02 where a1 in ('1','2','3') group by a2;
避免：查询什么就按照什么分组
using temporary ： 一般出现在group by语句中；已经有表了，但不使用，必须再来一张表。
explain select * from test03 where a2=2 and a4=4 group by a2,a4;
explain select * from test03 where a2=2 and a4=4 group by a3;
03、using index
性能提升了！！！ 索引覆盖（不读取源文件，只从索引文件中获取数据，不需要回表查询）
回表查询
只要使用到的列全部都在索引中，就是索引覆盖using index；
例如：test02表中有一个复合索引(a1,a2,a3)
explain select a1,a2 from test02 where a1='' or a2="";
drop index a1_a2_a3_index on test02;alter table test02 add index a1_a2_index(a1,a2);explain select a1,a3 from test02 where a1='' or a3="";
explain select a1,a2 from test02 where a1='' or a2="";explain select a1,a2 from test02
如果用到了索引覆盖(using index时)，会对possible_keys和key产生影响：
- 如果没有where，则索引只出现在key中
- 如果有where，则索引出现在possible_keys和 key中
04、using where
需要回表查询！！！
假设age是索引列，但查询语句select age，name from ... where age = ..
此语句中必须回原表去查询name，因此会出现using where
explain select a1,a3 from test02 where a3='';
05、impossible where
impossible where ： where 语句永远为false
explain select * from test01 where a1="x" and a1="y";
5、表的优化和总结
01、单表优化
create table book( bid int(4) primary key, name varchar(20) not null, authorid int(4) not null, publicid int(4) not null, typeid int(4) not null);insert into book values(1,'java',1,1,2);insert into book values(2,'tc',2,1,2);insert into book values(3,'wx',3,2,1);insert into book values(4,'math',4,2,3);# 查询authorid=1 且 typeid=2或者typeid=3的 bidselect bid from book where typeid in(2,3) and authorid=1;explain select bid from book where typeid in(2,3) and authorid=1;
explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc;
需要优化，加索引
alter table book add index idx_bta(bid,typeid,authorid);
再优化，根据SQL实际解析的顺序，来调整索引的顺序！
drop index idx_bta on book; # 索引一旦进行 升级优化，需要将之前的索引删掉，防止干扰！！# 虽然可以回表查询bid，但是将bid放在索引中 可以提升使用using indexalter table book add index idx_tab(typeid,authorid,bid);
再优化，
drop index idx_tab on book; # 删除索引alter table book add index idx_atb(authorid,typeid,bid);explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc;
小结
- 索引不能跨列使用（最佳左前缀），保证索引的定义和使用的顺序一致性
- 索引需要逐步优化
- 将含in的返回查询 放在where之后，防止失效！！
- 本例子中，同时出现了Using where（需要回原表）; Using index（不需要回原表）。
- 原因：在查询时
where authorid=1 and typeid in(2,3) order by typeid desc;,authorid在索引中，不需要回原表；typeid虽然也在索引中，但是含in的返回查询已经使得typeid索引失效了，因此相当于没有typeid这个索引，所以需要回原表！！
例如没有了in，则不会出现Using where
02、双表优化
create table teacher2( tid int(4) primary key, cid int(4) not null);insert into teacher2 values(1,2);insert into teacher2 values(2,1);insert into teacher2 values(3,3);create table course2( cid int(4), cname varchar(20));insert into course2 values(1,'java');insert into course2 values(1,'web');insert into course2 values(1,'html');左连接：explain select * from teacher2 t left join course2 con t.cid = c.cid and c.cname='java';# 索引往哪里加？ -- 小表驱动大表 -- 索引建在经常使用的数据量上！select .... from ... where 小表.x = 大表.x;# -------当编写 sql时，将数据量小的表放在 左边
索引往哪里加？
– 小表驱动大表
– 索引建在经常使用的数据量上！
一般情况，对于左外连接给左表加索引，右外连接给右表加索引！！
alter table teacher2 add index cid_index(cid);
alter table course2 add index cname_index(cname);
03、三表优化
总结
- 小表驱动大表
- 索引建立在经常查询的字段上
6、避免索引失效的一些原则
01、对于复合索引而言
- 复合索引，不要跨列和无序使用（最佳左前缀）；
- 复合索引，尽量使用全索引匹配
02、不要再索引上进行操作
不要再索引上进行任何的操作和运算（计算，函数，类型转换），否则索引失效
select .... from ... where A.x*2 = 3; # A为索引
explain select * from book where authorid=1 and typeid=2; # 用到a，t两个索引
explain select * from book where authorid = 1 and typeid*2 = 2; # 只用到了a索引
explain select * from book where authorid *2 = 1 and typeid*2 = 2; # 没有使用到索引
explain select * from book where authorid*2 = 1 and typeid = 2;
对于复合索引，左边失效，右边全部失效！！
drop index idx_atb on book;# 添加单值索引alter table book add index idx_authorid(authorid); alter table book add index idx_typeid(typeid);explain select * from book where authorid *2 = 1 and typeid = 2;
03、复合索引不能使用 （!= is null is not null）
复合索引不能使用 （!= is null is not null），否则自身以及右侧所有全部失效。
explain select * from book where authorid = 1 and typeid = 2;
— SQL优化，是一种概率层面的优化，至于是否使用到了我们的优化，需要通过explain来进行分析推测。
explain select * from book where authorid != 1 and typeid = 2;
explain select * from book where authorid != 1 and typeid is not null;
体验概率情况：原因mysql底层的 服务层 有 sql优化器 会干扰我们的优化！！
drop index idx_authorid on book;drop index idx_typeid on book;alter table book add index idx_book_at(authorid,typeid); # 添加复合索引explain select * from book where authorid = 1 and typeid = 2;
explain select * from book where authorid > 1 and typeid = 2; # 自身和右侧全部失效！！
explain select * from book where authorid = 1 and typeid > 2;
---------------------------------------------------------------------- -明显的概率问题 -------------------------------------------------------------------------------------
explain select * from book where authorid < 1 and typeid = 2; # 复合索引at只用到了一个
explain select * from book where authorid < 4 and typeid = 2; # 复合索引全部失效
一般而言，复合索引，对于范围查询(> < in)，之后的索引失效！
我们学习索引优化，是一个大部分情况都适用的结论，但由于sql优化器的存在，该结论不会100%成功！！
04、补救，尽量使用索引覆盖(using index)
# 假设复合索引（a,b,c）select a,b,c from ... where a='' and b='' and c='';
05、尽量以 常量 开头，不要以 % 开头， 否则索引失效
select * from xx where name like '%x%'; # name索引失效
explain select * from teacher where tname like '%张%'; # tname 索引失效！
explain select * from teacher where tname like '张%'; # tname 索引没有失效！
如果必须使用 like '%x%'进行模糊查询，则使用索引覆盖
explain select tname from teacher where tname like '%张%';
06、尽量不要使用类型转换（显示，隐式），否则索引失效！
explain select * from teacher where tname= 'abc'; # tname为索引
explain select * from teacher where tname= 123; # tname索引失效，原因：程序底层进行了 123 --> '123'，进行了类型转换
07、尽量不能使用 or ，否则索引失效！（注意：左边也失效的情况）
explain select * from teacher where tname = '' or tid > 1; # 注意 ： 使用了or，左侧的 tname索引 也 失效了！！！
7、一些其他的优化方法
01、exist 和 in的优化
- 如果主查询的数据量大，则使用In，效率高
- 如果子查询的数据量大，使用exist，效率高
select .. from table where exist (子查询);select .. from table where 字段 in (子查询);
exist语法： 将主查询的结果，放在子查询结果中进行校验（看子查询是否有数据，有数据校验成功）
select tname from teacher where exists (select * from teacher);# --- 等价于 selelct tname from teacherselect tname from teacher where exists (select * from teacher where tid = 9999);
in语法：
select * from A where id in (select id from B);
02、order by优化
using filesort 有两种算法 ： 双路排序 、 单路排序 （根据IO的次数）
MySQL 4.1之前 默认使用 双路排序；双路：两次IO操作（1、从磁盘中读取排序字段 ，进行排序； 2、扫描其他的字段）
为什么从双路变单路？ 因为IO消耗资源
MySQL 4.1之后 默认使用 单路排序，只读取一次（全部字段），在buffer中进行排序。但这种单路排序含有一定的隐患（不一定真的是“单路 | 1次IO” ，也有可能“多次IO” ）。
原因：如果数据量特别大，则无法 将所有的字段 一次性读取完毕，因此 会进行 “分片读取”，其实就是多次读取！！
单路排序比双路排序会 更加占用buffer
如果数据量大，可以考虑调大buffer的大小
调整buffer大小
set max_length_for_sort_data = 1024; # 单位 字节
如果 max_length_for_sort_data 值太低，则MySQL会自动的从 单路------> 双路
（太低：需要排序的列的总大小 超过了 max_length_for_sort_data 定义的字节数！ ）
提高order by 查询的策略：
- 选择使用单路、多路 ； 调整 buffer 的容量的大小！
- 避免 select * from … （* 还要计算）；用什么些什么
- 复合索引 不要 跨列使用，避免 using filesort
- 尽量保证全部排序字段 排序的一致性（都是升序 或者 降序）
8、慢查询日志
慢查询日志是什么？
慢查询日志 ： 是MySQL中提供的一种日志记录，用于记录MySQL中相应时间超过阈值的SQL语句（long_query_time，默认3s）。
慢查询日志默认是关闭的；建议：开发调优时打开 ，而 最终部署时关闭
检查是否开启了慢查询日志：
show variables like '%slow_query_log%';
临时开启慢查询日志：
set global slow_query_log = 1; # 内存中开启，重启服务失效！
永久开启慢查询日志，配置文件中追加：
# 在 /etc/my.cnf 中追加配置；vim /etc/my.cnf;# 【mysqld】下加入slow_query_log=1slow_query_log_file=/var/lib/mysql/localhost-slow.log
查看慢查询阈值：
show variables like '%long_query_time%';
临时设置阈值：
set global long_query_time = 2.5; # 修改完毕后，重新登陆起效
永久设置阈值：
# 在 /etc/my.cnf 中追加配置；vim /etc/my.cnf;# 【mysqld】下加入long_query_time = 2.5;
select sleep(4) # 查询休眠4s# 查询超过慢阈值的sqlshow global status like '%slow_queries%';
# 通过 查看慢查询日志分析sql
9、通过mysqldumpslow工具查看排查慢sql
通过mysqldumpslow工具查看慢sql，可以通过一些过滤条件 快速查找出需要定位的慢的sql！！
mysqldumpslow --help # 查看帮助
-s 排序的方式
t, at: 按照查询时间或平均查询时间排序
l, al: 按照锁的时间和平均锁的时间排序
r, ar: 按照发送的行数或平均发送的行数排序
c: 按照执行次数排序
mysqldumpslow按照平均查询时间排序（ -s at）
-r 以相反的顺序排序
-t N 在输出文件中只显示前N条查询
# 获取返回记录最多的三个sqlmysqldumpslow -s r -t 3 /www/server/data/mysql-slow.log# 获取访问记录最多的3个sqlmysqldumpslow -s c -t 3 /www/server/data/mysql-slow.log# 按照时间排序，前10条包含left join查询语句的sqlmysqldumpslow -s t -t 10 -g "left join" /www/server/data/mysql-slow.log
# 语法：mysqldumpslow 各种参数 慢查询日志的文件位置
10、分析海量数据
# 建表t：create table t( id int not null, name varchar(30));# 创建存储过程，输入记录数，插入t表行数据CREATE DEFINER=`root`@`%` PROCEDURE `proc1`(cnt int)begin declare i int default 1; start transaction; repeat insert into myDB.t (id,name) values (i,concat('a',i)); set i = i + 1; until i > cnt end repeat; commit;end#调用存储过程proc1，1百万条记录call proc1(1000000);
01、开始分析海量数据 profiles
show variables like '%profiling%'; # 默认是关闭的set profiling = on; # 打开show profiles; # 会记录所有profiling打开之后的全部语句！！
show profiles： 会记录所有profiling打开之后 的全部sql语句所花费的时间！！
缺点： 只能看到总共消费的时间，不能看到各个硬件消费的时间（cpu、io）
02、精确分析：sql诊断
show profile all for query 上一步查询的Queryidshow profile all for query 2;
show profile cpu,blockio for query 2;
03、看全局的查询日志
全局的查询日志 ：记录开启之后的 全部的sql语句 。（这些全局的记录操作 仅仅在调优、开发过程中打开即可，在最终的部署实施时，一定要关闭，耗性能）
# 查看全局的查询日志信息show variables like '%general_log%'; # 执行的SQL全部记录在 表 中set global general_log = 1; #开启全局日志，开启后会记录所有的sql : 会被记录在表中！set global log_output='table'; # 设置将 所有的sql 记录在表中# 执行的SQL全部记录在 指定的文件 中set global general_log = 1; #开启全局日志，开启后会记录所有的sql : 会被记录在文件中set global log_output='file'; # 设置将 所有的sql 记录在文件中set global general_log_file='/tmp/general.log'; # 设置将 所有的sql 记录在general.log文件中
select * from t where name='a1';select * from t where name='a8';select * from t where name='a10';select count(*) from t;select * from mysql.general_log; # 查看表，作证，帮助我们分析！
11、mysql锁机制
锁机制
锁机制 ： 解决因资源共享 而造成的并发问题。
Innodb默认行锁，MyISAM默认表锁！！!
# 如：买一件衣服A: X 买衣服： X加锁 -> 试衣服...下单...付款....打包 -> X解锁B: X 买衣服： 发现X被加锁，等待X解锁 ---> X已售空
锁的分类
-
锁按操作类型区分
- 读锁（共享锁）：对同一个数据（衣服），多个读操作可以同时进行，互补干扰
- 写锁（互斥锁）：如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作，写操作
-
锁按操作返回区分
- 表锁：一次性对一张表整体加锁。如MyISAM存储引擎默认使用表锁，开销小，加锁快，无死锁 ； 但锁的范围大，容易发生锁冲突，并发度较低！
- 行锁：**一次性对一条数据进行加锁。如Innodb存储引擎默认使用行锁，开销大，加锁慢，容易出现死锁； 锁的范围较小，不易发生锁冲突，并发度高（很小概率 发生高并发问题： 脏读、幻读、不可重复读、丢失更新！） **
- 页锁
01、表锁（MyISAM）
# 创建一张表，用于测试表锁# myisam存储引擎 默认 表锁create table tablelock( id int(4) primary key auto_increment, # 自增操作 mysql/sql server支持；oracle需要借助于序列来实现自增！ name varchar(20))engine myisam; insert into tablelock(name) values ('a1');insert into tablelock(name) values ('a2');insert into tablelock(name) values ('a3');insert into tablelock(name) values ('a4');insert into tablelock(name) values ('a5');
增加锁：
# 语法： lock table 表1 read/write , 表2 read/write , ........
查看加锁的表：
# 语法: show open tables; 0没加锁/1加锁
释放锁：
unlock tables; # 解锁
# 给 tablelock 加读锁！lock table tablelock read;
会话：session ： 每一个访问数据的dos命令行、数据库客户端连接工具，都是一个会话！
加读锁：
会话1：
**加锁的表：**
lock table tablelock read;
`select * from tablelock;` --- 读（查），可以
delete from tablelock where id=1; — 写（增删改），不可以
操作其他表：
`select * from teacher;` --- 读（查），不可以
delete from teacher where tid = 1; — 写（增删改），不可以
总结1：如果某一个会话 对 A表 进行了 加读锁，则 该会话 可以对 A表 进行 读操作，不能进行写操作； 且 该会话不能对 其他表 进行任何操作（读或者写）！！
既就是 如果 给 A表 加了 read锁 ， 则当前会话只能对 A表 进行读操作！！！
会话2：
加锁的表：
`select * from tablelock;` --- 读 ，可以
delete from tablelock where id=1; — 写，会一直等待，等待 会话1 将锁释放！！
操作其他表：
`select * from teacher;` --- 读 ，可以
delete from teacher where tid = 1; — 写，可以
总结2：会话1给 A表 加了读锁： 其他会话 可以对 其他表 （除了 A 表之外的所有表） 可以进行任意的操作 ； 对 A 表可以读操作，对 A 表写操作 必须 要等到 会话1 释放锁之后 才能进行！！
既就是 如果 给 A表 加了 read锁 ， 则其他会话可以对 A表 可以进行 读操作 ，写要等待；其他会话对其他的表 可以进行 任何操作！
加写锁：
会话1：
**加锁的表：**
lock table tablelock write;
`select * from tablelock;` --- 读 ，可以
delete from tablelock where id=1; — 写，可以
操作其他表：
`select * from teacher;` --- 读 ，不可以
delete from teacher where tid = 1; — 写，不可以
总结1：如果某一个会话 对 A表 进行了 加写锁，则 该会话 可以对 A表 进行 任何操作；但不能操作 其他的表！！
会话2：
加锁的表：
`select * from tablelock;` --- 读 ，不可以
delete from tablelock where id=1; — 写，不可以！
操作其他表：
`select * from teacher;` --- 读 ，可以
delete from teacher where tid = 1; — 写，可以
总结2：如果某一个会话 对 A表 进行了 加写锁，则 其他的会话 不能对 A表 进行 任何操作（除非等到释放锁）；对其他的表可以进行 任意操作！！！
MyISAM表级锁的锁模式
MyISAM在执行 查询语句（select）前，会自动的给涉及的 所有表 加 读锁，
在执行更新操作（DML） 前，会自动的给 涉及的表 加 写锁。
所以对MyISAM 表进行操作，会出现以下的情况：
-
对MySIAM表进行读操作（加读锁），不会阻塞其他进程（会话）对同一张表的读请求，
但会阻塞对同一张表的 写请求（只有当读锁释放之后，才会执行该进程的写操作）
-
对MySIAM表进行写操作（加写锁），会阻塞其他进程（会话）对同一张表的读和写操作，
只有当写锁释放之后，才会执行该进程的读写操作。
02、行锁（Innodb）
分析表锁定：
查看那些表加了锁 ： show open tables; 0没加锁/1加锁
分析表锁定的严重程度： show status like 'table%';
一般建议：
Table_locks_immediate / Table_locks_waited > 5000，建议使用Innodb存储引擎，否则MyISAM存储引擎 。
# 创建 测试 行锁表create table linelock( id int(5) primary key auto_increment, name varchar(20))engine=innodb;insert into linelock(name) values ('1');insert into linelock(name) values ('2');insert into linelock(name) values ('3');insert into linelock(name) values ('4');insert into linelock(name) values ('5');# mysql 默认自动commit，oracle默认不会commit# 为了研究行锁，暂时将自动commit关闭 , 改为手动提交！set autocommit = 0;
加行锁：
会话1：
**写操作** `insert into linelock values(6,'a6');` // 在缓存中，没进数据库
会话2：
写操作：
`update linelock set name='ax' where id = 6;` // 更新时发生 此数据 被加了行锁了！（直到等待该行锁释放）
// 等待其他会话 commit/rollback 之后，行锁才释放！！！
对行锁情况：
1、如果 会话x 对 某条数据a 进行了 DML操作（研究时：关闭了自动commit的情况下），则其他会话 必须等待 该会话x 结束事务之后（rollback/commit），才能对数据a 进行操作
2、表锁 可以通过 unlock tables 解锁，也可以通过事务解锁！ ; 行锁是通过 事务 解锁的！！
3、行锁一次锁一行数据，如果操作的是不同的数据，则不干扰！
行锁的注意事项
1、如果没有索引，则行锁默认转为表锁
show index from linelock; # 查看索引alter table linelock add index name_index(name); # 加索引
会话1:
写操作：
update linelock set name= 'ai' where name = '3';
会话2：
写操作：
update linelock set name= 'aix' where name = '4';
上面都成功执行！！！！
会话1:
写操作：
update linelock set name= 'ai' where name = 3;
会话2：
写操作：
update linelock set name= 'aix' where id = 4; // 发现被阻塞了！！！
被阻塞的原因： 是因为索引发生 了 类型转化，导致索引失效了 ，行锁 变为了 表锁
在表锁状态下，会话1如果加了写锁，会话2对该表不能进行任何操作！！！
2、行锁的特殊情况：间隙锁
间隙锁：值在范围内，但不存在！！
此时linelock 表中没有 id = 7的数据
update linelock set name='x' where id>0 and id<8; # 在此where范围内，没有id=7的数据，则id=7的数据成为间隙！# 1 2 3 4 5 6 7 全部加锁，不管你有没有这个id的数据。
间隙 ： mysql 会自动给 间隙 加锁 —> 间隙锁（特殊的行锁）。则本题会自动给id=7的数据加间隙锁！！！
insert into linelock values(7,'xx');
对于行锁，如果有where，则实际加锁的范围 就是where后面的范围 （不是实际的值！！）
行锁总结
Innodb默认采用行锁
- 缺点：比表锁性能消耗大
- 优点：并发能力强，效率高
因此：建议，高并发使用Innodb，否则使用MyISAM
分析行锁定：
show status like '%innodb_row_lock%';
通过下面的参数，可以分析目前系统中行锁定的一些情况！！
如果仅仅是查询数据，怎么加锁？
可以 ， 加 for update
# 研究学习时，关闭自动提交方式：set autocimmit = 0;start transaction;begin;# 会话1:select * from linelock where id=3 for update;# 会话2:update linelock set name='xxxx' where id = 3; # 发现阻塞了，被锁住了（行锁）
通过for update 对 query 语句 进行 加锁！！！
扫一扫
8万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
