SQL优化Mysql版

2020-01-20 技术收录数据库 0 评论字数统计:21787字阅读时长:44分

系统使用的Linux Centos6

MySQL版本：

基础

5.x:

5.0-5.1:早期产品的延续，升级维护

5.4 - 5.x : MySQL整合了三方公司的新存储引擎（推荐5.5）本次使用的是 * MySQL-client-5.5.58-1.el6.x86_64.rpm * MySQL-server-5.5.58-1.el6.x86_64.rpm

安装：rpm -ivh rpm软件名

rpm -ivh MySQL-server-5.5.58-1.el6.x86_64.rpm

如果安装时与某个软件 xxx冲突，则需要将冲突的软件卸载掉：

yum -y remove xxx
yum -y remove mysql-libs-5.1.73-8.el6*

安装时有日志提示我们可以修改密码：

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.centos6 password 'new-password'

rpm -ivh MySQL-client-5.5.58-1.el6.x86_64.rpm

注意：

如果提示“GPG keys…”安装失败，解决方案：

 rpm -ivh rpm软件名  --force --nodoeps

验证：

mysqladmin --version

启动mysql应用： service mysql start

关闭： service mysql stop

重启： service mysql restart

在计算机reboot后登陆MySQL :

 mysql

退出mysql

exit

可能会报错：

 "/var/lib/mysql/mysql.sock不存在"

–原因：是Mysql服务没有启动

解决：
启动服务： 1. 每次使用前手动启动服务

    /etc/init.d/mysql start

开机自启

chkconfig mysql on      开启开机自启

chkconfig mysql off     关闭开机自启

ntsysv                  检查开机是否自动启动：

给mysql 的超级管理员root 增加密码：

/usr/bin/mysqladmin -u root password root

登陆：

mysql -u root -p
回车之后输入密码

数据库存放目录：

ps -ef|grep mysql       可以看到：

数据库目录：     datadir=/var/lib/mysql 

pid文件目录： --pid-file=/var/lib/mysql/bigdata01.pid

MySQL核心目录：

/var/lib/mysql :mysql 安装目录

/usr/share/mysql:  配置文件

/usr/bin：命令目录（mysqladmin、mysqldump等）

/etc/init.d/mysql启停脚本

MySQL配置文件

my-huge.cnf 高端服务器  1-2G内存
my-large.cnf   中等规模
my-medium.cnf  一般
my-small.cnf   较小

但是，以上配置文件mysql默认不能识别，默认只能识别

/etc/my.cnf

因此需要将上面四个文件之一复制到默认配置文件中

采用 my-huge.cnf ：

cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

注意 mysql5.5默认配置文件

/etc/my.cnf；

Mysql5.6 默认配置文件

/etc/mysql-default.cnf

默认端口 3306

mysql字符编码：

sql :

show variables like '%char%';

可以发现部分编码是 latin,需要统一设置为utf-8

设置编码：

vi /etc/my.cnf

[mysql]
default-character-set=utf8
[client]
default-character-set=utf8

[mysqld]
character_set_server=utf8
character_set_client=utf8
collation_server=utf8_general_ci

重启Mysql:

service mysql restart

sql :

show variables like '%char%' ;

注意事项：修改编码只对“之后”创建的数据库生效，因此我们建议在mysql安装完毕后，第一时间统一编码。

mysql:清屏

ctrl+L  

system clear

原理

MYSQL逻辑分层：连接层服务层引擎层存储层

逻辑分层

InnoDB(默认) ：事务优先（适合高并发操作；行锁）

MyISAM ：性能优先（表锁）

查询数据库引擎：支持哪些引擎？

show engines ;

查看当前使用的引擎

show variables like '%storage_engine%' ;

指定数据库对象的引擎：

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   ;

由Welcome to the MySQL monitor. Commands end with ; or \g.可知sql语句的结尾符有两种;和\g

SQL优化

原因：性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）

参考文章

SQL ：

编写过程：

select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..

解析过程：

from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

SQL优化，主要就是在优化索引

索引：相当于书的目录

索引： index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B树(默认)、Hash树…）

索引的弊端：

索引本身很大，可以存放在内存/硬盘（通常为硬盘）
索引不是所有情况均适用： a.少量数据 b.频繁更新的字段 c.很少使用的字段
索引会降低增删改的效率，提高查询的效率（增删改查）

优势：

提高查询效率（降低IO使用率）
降低CPU使用率（…order by age desc,因为 B树索引本身就是一个好排序的结构，因此在排序时可以直接使用）

三层Btree可以存放上百万条的数据

Btree：一般指B+树，数据全部存放在叶结点中

B+树中查询任意的数据的次数：n次(B+树的高度)

索引

分类：

主键索引：不能重复。id 不能是null

唯一索引：不能重复。id 可以是null

单值索引：单列， age ;一个表可以多个单值索引,name。

复合索引：多个列构成的索引（相当于二级目录： z: zhao） (name,age) (a,b,c,d,…,n)

创建索引：

方式一：

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);

方式二：

alter table 表名 索引类型  索引名（字段）

单值：
alter table tb add index dept_index(dept) ;

唯一：
alter table tb add unique index name_index(name);

复合索引
alter table tb add index dept_name_index(dept,name);

注意：如果一个字段是primary key，则该字段默认就是主键索引

此时的语句是DDL，会自动提交，因此不需要commit;

DML语句需要自己commit，增删改

删除索引：

drop index 索引名 on 表名 ;

drop index name_index on tb ;

查询索引：

show index from 表名 ;

show index from 表名 \G

注意：结尾符需要写成\G,结果以行的顺序以列的形式显示出来

SQL性能问题

分析SQL的执行计划 : explain ，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL状况
MySQL查询优化器会干扰我们的优化

优化方法，官网

查询执行计划：

explain +SQL语句

explain  select  * from tb ;

 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',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

查询课程编号为2 或教师证编号为3 的老师信息

SQL语句

select t.* from teacherCard tc,course c,teacher t where c.tid = t.tid
and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid=3);

explain +sql:

表字段

id:

id值相同，从上往下顺序执行。
```
t(3)-tc(3)-c(4)
```
t表中添加数据后在执行
```
tc（3）--c（4）-（t6）
```
表的执行顺序因数量的个数改变而改变的原因： 笛卡儿积
```
    a    b       c
    2    3       4   =  2*3=6 * 4   =24
    3    4       2   =  3*4=12* 2   =24
```
虽然结果最终的结果相同但是中间的过程是不同的，程序喜欢数据越小越好。(小表驱动大表)

数据小的表优先查询；

id值不同：id值越大越优先查询 (本质：在嵌套子查询时，先查内层再查外层)

查询教授SQL课程的老师的描述（desc），纯多表查询

explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql' ;

将以上多表查询转为子查询形式（纯子查询）：会先执行括号中的

explain select tc.tcdesc from teacherCard tc where tc.tcid = 
(select t.tcid from teacher t where  t.tid =  
 (select c.tid from course c where c.cname = 'sql')
);

子查询+多表：

    explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid
    and t.tid = (select c.tid from course c where cname = 'sql') ;

结论: id值有相同，又有不同： id值越大越优先；id值相同，从上往下顺序执行（c-tc-t）

select_type:

select_type:查询类型

PRIMARY:包含子查询SQL中的主查询（最外层）
SUBQUERY：包含子查询SQL中的子查询（非最外层）
simple:简单查询（不包含子查询、union）select * from teacher
derived:衍生查询(使用到了临时表)(在table列中derived 字段的后面有一个数字指向了id为该数据的临时表)

衍生查询的两种情况

在from子查询中只有一张表

explain select  cr.cname    from ( select * from course where tid in (1,2) ) cr ;

在from子查询中，如果有table1 union table2 ，则table1 就是derived,table2就是union

explain select  cr.cname    from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

union

看上面的例子。

union result :告知开发人员，那些表之间存union查询（在table列表示x表和y表之间有union）

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

type

type:索引类型、类型

system>const>eq_ref>ref>range>index>all   ，要对type进行优化的前提：有索引

越往左边性能越高。其中：system,const只是理想情况；实际能达到 ref>range

system

system（忽略，不切实际。。）: 只有一条数据的系统表；或衍生表只有一条数据的主查询

create table test01
(
    tid int(3),
    tname varchar(20)
);
insert into test01 values(1,'a') ;
commit;

增加索引（主键索引）

alter table test01 add constraint tid_pk primary key(tid) ;

explain select * from (select * from test01 )t where tid =1 ;

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) ;

explain select tid from test01 where tid =1 ;

则不行

eq_ref

eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）（每一行数据唯一，且表数据一对一不能多也不能少） select … from ..where name = … .常见于唯一索引和主键索引(因为主键索引和唯一索引会保证唯一)。

增加主键：给teacherCard表增加一个主键索引，给teacher表增加一个唯一索引

alter table teacherCard add constraint pk_tcid primary key(tcid);

alter table teacher add constraint uk_tcid unique index(tcid) ;

explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;

以上SQL，用到的索引是 t.tcid,即teacher表中的tcid字段；如果teacher表的数据个数和连接查询的数据个数一致（都是3条数据），则有可能满足eq_ref级别；否则无法满足。

ref

ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）

准备数据：

insert into teacher values(4,'tz',4) ;
insert into teacher values(5,'tz',4) ;
insert into teacherCard values(4,'tz222');

测试：先给name增加索引

alter table teacher add index index_name (tname) ;

explain select * from teacher 	where tname = 'tz';

查询到的索引列的结果有两个tz所以是ref

range

range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效，从而转为无索引all)

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 ;

index

index：查询全部索引中数据

explain select tid from teacher ;

–tid 是索引，只需要扫描索引表，不需要查询所有表中的所有数据

all

all：查询全部表中的数据

explain select cid from course ;

–cid不是索引，需要全表扫描，即需要查询所有表中的所有数据

小结

system/const: 结果只有一条数据

eq_ref:结果多条；但是每条数据是唯一的；

ref：结果多条；但是每条数据是是0或多条；

possible_keys

possible_keys ：可能用到的索引，是一种预测，不准。

alter table  course add index cname_index (cname);

explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc
where t.tcid= tc.tcid
and t.tid = (select c.tid from course c where cname = 'sql') ;

如果 possible_key/key是NULL，则说明没用索引

explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
and t.tcid = tc.tcid and c.cname = 'sql' ;

这个预测就不准

key

key ：实际使用到的索引

key_len

key_len ：索引的长度 ;

作用：用于判断复合索引是否被完全使用

create table test_kl
(
    name char(20) not null default ''
);

alter table test_kl add index index_name(name) ;

explain select * from test_kl where name ='' ;   --长度为60

– key_len :60

在utf8：1个字符站3个字节

alter table test_kl add column name1 char(20) ;  --name1可以为null

alter table test_kl add index index_name1(name1) ;

explain select * from test_kl where name1 ='' ; -- 长度为61

–如果索引字段可以为Null,则会使用1个字节用于标识。

drop index index_name on test_kl ;--删除两个索引
drop index index_name1 on test_kl ;

增加一个复合索引

alter table test_kl add index name_name1_index (name,name1) ; --会先查name，查到就不用name1，没查到会用name1

explain select * from test_kl where name1 = '' ; --复合索引会先查name在查name1

–121；用到了name1，但是name1和name符合起来了，因此两者都被使用。20*3+20*3+1=121

explain select * from test_kl where name = '' ;

–60；只用到了name，20*3=60

varchar(20)

alter table test_kl add column name2 varchar(20) ;

–可以为Null

alter table test_kl add index name2_index (name2) ;

explain select * from test_kl where name2 = '' ;

–63

20*3=60 + 1(null) +2(用2个字节标识可变长度) =63

utf8:1个字符3个字节
gbk:1个字符2个字节
latin:1个字符1个字节

ref

ref : 注意与type中的ref值区分。

作用：指明当前表所参照的字段。

select ....where a.c = b.x ;(其中b.x可以是常量:const a表中c字段引用了b表中的x字段)

alter table course  add index tid_index (tid) ;--研究的前提是使用到的字段都加上了索引

explain select * from course c,teacher t where c.tid = t.tid  and t.tname ='tw' ;

其中t.tname ='tw'中的tw是一个常量因此是const

c.tid = t.tidc表的tid用到了t表的tid

rows

rows: 被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)

explain select * from course c,teacher t  where c.tid = t.tid
and t.tname = 'tz' ;

Extra：

using filesort ：

性能消耗大；需要“额外”的一次排序（查询）。常见于 order by 语句中。

排序之前要先查询

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 ;--不是using filesort

explain select * from test02 where a1 ='' order by a2 ; --using filesort 根据a2排序但是a2没查，因此需要额外的一次查询

小结：对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；

避免： where哪些字段，就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 idx_a1_a2_a3 (a1,a2,a3) ;

explain select *from test02 where a1='' order by a3 ; --using filesort  跨列了 跨过了a2

explain select *from test02 where a2='' order by a3 ; --using filesort  跨列了 跨过了a1

explain select *from test02 where a1='' order by a2 ;--没有--using filesort

explain select *from test02 where a2='' order by a1 ; --using filesort

小结：避免： where和order by 按照复合索引的顺序使用，不要跨列或无序使用。

using temporary:

性能损耗大，用到了临时表。一般出现在group by 语句中。

explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;--没有--using temporary

explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; --using temporary

避免：查询那些列，就根据那些列 group by .

using temporary:需要额外再多使用一张表. 一般出现在group by语句中；已经有表了，但不适用，必须再来一张表。

解析过程：

from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;

–没有using temporary；先where a2\a4在group by a2\a4,在原先的基础上在分组，因此没有用到额外的一张表

explain select * from test03 where a2=2 and a4=4 group by a3 ;

–using temporary；先where a2\a4在group by a3，在原先的基础上按照a3进行分组，因此用到额外的一张表a3

using index :

性能提升; 索引覆盖（覆盖索引）。

原因：不读取原文件，只从索引文件中获取数据（不需要回表查询）

不需要回表查询:用到的数据都是索引中的数据，不需要再在表中使用没加索引的数据

只要使用到的列全部都在索引中，就是索引覆盖using index

例如：test02表中有一个复合索引(a1,a2,a3)

explain select a1,a2 from test02 where a1='' or a2= '' ; --using index 用到的a1和a2都在复合索引中

反例

drop index idx_a1_a2_a3 on test02;

alter table test02 add index idx_a1_a2(a1,a2) ;

explain select a1,a3 from test02 where a1='' or a3= '' ;--用到的a3不是索引因此没有--using index

如果用到了索引覆盖(using index时)，会对 possible_keys和key造成影响：

如果没有where，则索引只出现在key中；

如果有where，则索引出现在key和possible_keys中。

explain select a1,a2 from test02 where a1='' or a2= '' ;
explain select a1,a2 from test02  ;

using where

using where （需要回表查询）

假设age是索引列，但查询语句select age,name from ...where age =...,此语句中必须回原表查Name，因此会显示using where.

explain select a1,a3 from test02 where a3 = '' ;--a3需要回原表查询

impossible where

impossible where ： where子句永远为false

explain select * from test02 where a1='x' and a1='y'  ;--impossible where

优化实例

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=1 and a2=1 and a3=1 and a4=1;--using index

–推荐写法，因为索引的使用属性顺序（where）后面的顺序和复合索引的顺序一致

explain select a1,a2,a3,a4 from test03 where a4=1 and a3=1 and a2=1 and a1=1;--using index

虽然编写的顺序和索引的顺序不一致，但是我们发现两者结果一致：是因为mysql的服务层的sql优化器对该语句进行了优化

以上2个sql，使用了全部的符合索引：通过key_len=16

explain select a1,a2,a3,a4 from test03 where a1=1 and a2=1 and a4=1 order by a3;

a1,a2按顺序，显示using index，不需要回表查询

a4:无效索引，因为跨列使用(变为无效索引)会回表查询显示using where

可以通过key_len校验：8

explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3;

以上sql出现了–using filesort(文件内排序，“多了一次额外的查找、排序”) 不要跨列使用(where和order by拼起来不要跨列使用)

a1–(a4无效)–a3:1-3结果跨列

explain select a1,a2,a3,a4 from test03 where a1=1 and a4=1 order by a2,a3;

结果没有–using filesort，没有进行跨列使用

a1–a2–（a4失效）–a3:1-2-3

结果没有跨列

小结：

如果（a,b,c,d）复合索引和使用的顺序一致（且不跨列使用），则复合索引全部使用。如果部分一致，则使用部分索引

where和order by拼起来，不要跨列使用

优化案例

单表优化、两表优化、三表优化

单表优化

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,'tjava',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) ;	
commit;

查询authorid=1且 typeid为2或3的 bid

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);

索引一旦进行升级优化，需要将之前废弃的索引删掉，防止干扰。

drop index idx_bta on book;

根据SQL实际解析的顺序，调整索引的顺序：

from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

alter table book add index idx_tab (typeid,authorid,bid);

–虽然可以回表查询bid，但是将bid放到索引中可以提升使用using index ;

再次优化（之前是index级别）：思路,因为范围查询in有时会失效(in失效后a也会失效)，因此交换索引的顺序，将typeid in(2,3) 放到最后（这样即使in失效了我们的a还有用）。

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)中authorid在索引(authorid,typeid,bid)中，因此不需要回原表（直接在索引表中能查到）；

而typeid虽然也在索引(authorid,typeid,bid)中，但是含in的范围查询已经使该typeid索引失效，因此相当于没有typeid这个索引，所以需要回原表（using where）；

例如以下没有了In，则不会出现using where

explain select bid from book where  authorid=1 and  typeid =3 order by typeid desc ;

还可以通过key_len证明in可以使索引失效。

有in时索引长度为4
没in时索引长度为8

两表优化

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(2,'python');
insert into course2 values(3,'kotlin');
commit;

左连接：

explain select *from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';

索引往哪张表加？

小表驱动大表 (where 小表.x = 大表.y)

索引建立经常使用的字段上（本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引）,一般情况对于左外连接(以左表为基础：左表的数据要全部匹配)，给左表加索引；右外连接（以右表为基准），给右表加索引

    小表:10
    大表:300

    select ...where 小表.x10=大表.x300 ;
    for(int i=0;i<小表.length 10;i++)
    {
        for(int j=0;j<大表.length 300;j++)
        {
            ...
        }
    }
    
    select ...where 大表.x300=小表.x10 ;

    for(int i=0;i<大表.length300;i++)
    {
        for(int j=0;j<小表.length10;j++)
        {
            ...
        }
    }

以上2个FOR循环，最终都会循环3000次；但是对于双层循环来说：一般建议将数据小的循环放外层；数据大的循环放内层。小表驱动大表

当编写 ..on t.cid=c.cid 时，将数据量小的表放左边（假设此时t表数据量小）优化

	alter table teacher2 add index index_teacher2_cid(cid) ;

	alter table course2 add index index_course2_cname(cname);--where后面的字段一定要加索引

	explain select *from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';

Using join buffer:extra中的一个选项，作用：Mysql引擎使用了连接缓存。写的sql太差了mysql看不下去了，动了你的sql，给加了一个连接缓存。

左连接（left join）即为两张表进行连接时,是以处于left join语句左侧的表为基准去匹配left join语句右边的表，如果左表中的一条数据在右表中能找到与之对应的一条数据，那么就会出现在以虚表形式存在的结果表中，如果没有找到，那么会以null来代替右表中的数据去匹配左表。这样会有一个鲜明的对比，左表中的每一条数据中的对象在右表中的某个属性的存在性是一目了然的。同时在使用on 进行连接时，on的作用仅仅是进行两张表的上诉连接，发挥匹配的功能，它选出来的是满足这种匹配的所有结果，而并不一定是用户所需要的，这时候就要使用where进行条件判断，从而筛选出真正需要的信息。

右连接（right join）本质上是相当于将上述的左连接的这个过程反过来，以连接语句right join右侧的表为基准去匹配左边的表，剩下的道理是一样的，不再赘述。

内连接（inner join）就是在用两张表进行匹配的时候，如果表中任意一条数据在另一张表中都是找不到对应数据的话，那么在结果表中是不会有这一条数据的。也就是说必须是两张表中任意两条能够互相对应着的数据才能被存入到结果表中，有点类似于取交集的味道。这种适用于一旦某条数据为空便没有意义的场景，这时给它设成null也就毫无意义了。表中的数据也因此显得简练很多。

外链接（outer join）与内连接是相反的，就是说，如果某张表中的数据在另一张中找不到对应的条目并不影响它依然出现在查询的结果中，这对于两张表都是满足的，两边都有出现null的可能，这就有一点数学里的并集的意思。

自连接(self join)可能看起来有点晦涩难懂，但是实际上换个角度你就会豁然开朗，你可以把它这个过程想象成两张一样的表进行左连接或右连接，这样就会简单多了，其中一张表通过设别名的方式成为了虚表，但是共享原标中的信息。应用场景是这样的，就是表的一个字段和另一个字段是相同性质的东西，譬如员工与上司，他们本质也都是员工，在员工表中，员工的直接上司编号会以另一个字段的形式出现，但是他的上司的编号也是会出现在员工编号这个字段里。那么在这种情况下，假如需要去查询某一位员工的上司的信息，在已知该员工编号的条件下，可以根据他的编号去获得上司的编号，进而通过上司的编号去获得上司的信息。

三张表优化A B C

小表驱动大表
索引建立在经常查询的字段上

避免索引失效的一些原则

复合索引

复合索引，不要跨列或无序使用（最佳左前缀）
复合索引，尽量使用全索引匹配

复合索引可以看为是书的目录(a,b,c)一级目录a二级目录b三级目录c

索引操作

不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

select ..where A.x = .. ;  --假设A.x是索引

--不要：select ..where A.x*3 = .. ;

explain select * from book where authorid = 1 and typeid = 2 ;--用到了at2个索引

explain select * from book where authorid = 1 and typeid*2 = 2 ;--用到了a1个索引

explain select * from book where authorid*2 = 1 and typeid*2 = 2 ;----用到了0个索引

explain select * from book where authorid*2 = 1 and typeid = 2 ;----用到了0个索引,

原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。

如果是单独的索引就没有这种问题

drop index idx_atb on book ; 

alter table book add index idx_authroid (authorid) ;

alter table book add index idx_typeid (typeid) ;

独立索引，不影响

explain select * from book where authorid*2 = 1 and typeid = 2 ;--使用到了0个索引
explain select * from book where authorid*2 = 1 and typeid = 2 ;--使用到了一个索引

复合索引关键判断

复合索引不能使用不等于（!= <>）或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 ;--authorid失效

explain select * from book where authorid != 1 and typeid !=2 ;--两个索引都失效

体验概率情况(< > =)：

原因是服务层中有SQL优化器，可能会影响我们的优化。

show index from book;
drop index idx_typeid on book;

drop index idx_authroid on book;

alter table book add index idx_book_at (authorid,typeid);

explain select * from book where authorid = 1 and typeid =2 ;--复合索引at全部使用

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 < 1 and typeid =2 ;

–复合索引at只用到了1个索引

explain select * from book where authorid < 4 and typeid =2 ;-- 只是将1改为了4

–复合索引全部失效

我们学习的索引优化，是一个大部分情况适用的结论，但由于SQL优化器等原因该结论不是100%正确。
一般而言，范围查询（> < in），之后的索引失效。

补救尽量使用索引覆盖（using index）

不会出错，永远成立

（a,b,c）

select a,b,c from xx..where a=  .. and b =.. ;

Like问题

like尽量以“常量”开头，不要以’%‘开头，否则索引失效

select * from xx where name like '%x%' ;

–name即使是一个索引，也会失效

explain select * from teacher  where tname like '%x%';

–tname索引失效（开发过程中不要使用*）

explain select * from teacher  where tname like 'x%';

–使用name索引，索引为没失效

explain select tname from teacher  where tname like '%x%';

–如果必须使用like ‘%x%‘进行模糊查询，可以使用索引覆盖挽救一部分。

类型转换

尽量不要使用类型转换（显示、隐式），否则索引失效

explain select * from teacher where tname = 'abc' ;--使用了索引没问题
explain select * from teacher where tname = 123 ;--程序底层将 123 -> '123'，即进行了类型转换，因此索引失效

or

尽量不要使用or，否则索引失效

explain select * from teacher where tname ='';--索引正常
explain select * from teacher where tname ='' or tcid >1 ; --将or左侧的tname 失效。

一些其他的优化方法

exist和in

select ..from table where exist (子查询) ;
select ..from table where 字段 in  (子查询) ;

如果主查询的数据集大，则使用In ,效率高。

如果子查询的数据集大，则使用exist,效率高。

exist语法：将主查询的结果，放到子查询结果中进行条件校验（看子查询是否有数据，如果有数据则校验成功），如果符合校验，则保留数据；

select tname from teacher where exists (select * from teacher) ;

–等价于select tname from teacher

select tname from teacher where exists (select * from teacher where tid =9999) ;

子查询没数据,校验失败

in:

select ..from table where tid in  (1,3,5) ;

order by 优化

using filesort 有两种算法：双路排序、单路排序（根据IO（访问硬盘文件）的次数）

MySQL4.1之前默认使用双路排序；

双路：扫描2次磁盘

从磁盘读取排序字段 ,对排序字段进行排序（在buffer(缓冲区)中进行的排序）
扫描其他字段

–IO较消耗性能

MySQL4.1之后默认使用单路排序：

只读取一次（全部字段），在buffer中进行排序。

但此种单路排序会有一定的隐患（不一定真的是“单路|1次IO”，有可能多次IO）。

原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取、多次读取”。

注意：单路排序比双路排序会占用更多的buffer。

单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小： sql命令

set max_length_for_sort_data = 1024;  --单位byte

如果max_length_for_sort_data值太低，则mysql会自动从单路->双路（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

提高order by查询的策略：

选择使用单路、双路；调整buffer的容量大小；
避免select * … 用什么查什么
复合索引不要跨列使用，避免using filesort
保证全部的排序字段排序的一致性（都是升序或降序）

SQL排查 - 慢查询日志:

MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句（long_query_time，默认10秒）

慢查询日志默认是关闭的；

建议：开发调优是打开，而最终部署时关闭。

检查是否开启了慢查询日志：

show variables like '%slow_query_log%' ;

临时开启：mysql退出重启服务后关闭

set global slow_query_log = 1 ;  --在内存中开启

exit

service mysql restart

永久开启：

/etc/my.cnf 中追加配置：

vi /etc/my.cnf 
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log

慢查询阀值：

show variables like '%long_query_time%' ;

临时设置阀值：

set global long_query_time = 5 ; --设置完毕后，重新登陆后起效 （不需要重启服务）

永久设置阀值：

/etc/my.cnf 中追加配置：

vi /etc/my.cnf 
[mysqld]
long_query_time=3

实例

select sleep(4);
select sleep(5);
select sleep(3);
select sleep(3);

–查询超过阀值的SQL：

 show global status like '%slow_queries%' ;

慢查询的sql被记录在了日志中，因此可以通过日志查看具体的慢SQL。
```
cat /var/lib/mysql/localhost-slow.log
```
通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件快速查找出需要定位的慢SQL
```
mysqldumpslow --help
s：排序方式
r:逆序
l:锁定时间
g:正则匹配模式        
```

linux命令

获取返回记录最多的3个SQL

mysqldumpslow -s r -t 3  /var/lib/mysql/localhost-slow.log

获取访问次数最多的3个SQL

mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

按照时间排序，前10条包含left join查询语句的SQL

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

语法：

mysqldumpslow 各种参数  慢查询日志的文件

分析海量数据

模拟海量数据存储过程（无return）/存储函数（有return）

create database testdata ;
use testdata
    
create table dept
(
    dno int(5) primary key default 0,
    dname varchar(20) not null default '',
    loc varchar(30) default ''
)engine=innodb default charset=utf8;

create table emp
(
    eid int(5) primary key,
    ename varchar(20) not null default '',
    job varchar(20) not null default '',
    deptno int(5) not null default 0
)engine=innodb default charset=utf8;

通过存储函数插入海量数据：

创建存储函数：

randstring(6)  ->aXiayx  用于模拟员工名称
    
delimiter $ 
    
create function randstring(n int)   returns varchar(255) 
begin
    declare  all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ;
    declare return_str varchar(255) default '' ;
    declare i int default 0 ; 
    while i<n        
    do                                  
        set return_str = concat(  return_str,      substring(all_str,   FLOOR(1+rand()*52)   ,1)       );
        set i=i+1 ;
    end while ;
    return return_str;
        
end $

–如果报错：You have an error in your SQL syntax，说明SQL语句语法有错，需要修改SQL语句；

如果报错This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) 是因为存储过程/存储函数在创建时与之前的开启慢查询日志冲突了

解决冲突：

临时解决( 开启log_bin_trust_function_creators )

	show variables like '%log_bin_trust_function_creators%';

	set global log_bin_trust_function_creators = 1;

永久解决：

/etc/my.cnf 
[mysqld]
log_bin_trust_function_creators = 1

创建产生随机整数存储函数（0-99）

create function ran_num() returns int(5)
begin
	declare i int default 0;
	set i =floor( rand()*100 ) ;
	return i ;
end $

通过存储过程插入海量数据：

emp表中，从eid_start开始插入data_times条数据

create procedure insert_emp( in eid_start int(10),in data_times int(10))
begin 
	declare i int default 0;
	set autocommit = 0 ;
	repeat
		insert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ;
		set i=i+1 ;
		until i=data_times
	end repeat ;
	commit ;
end $

–通过存储过程插入海量数据：dept表中

create procedure insert_dept(in dno_start int(10) ,in data_times int(10))
begin
    declare i int default 0;
    set autocommit = 0 ;
    repeat
    
        insert into dept values(dno_start+i ,randstring(6),randstring(8)) ;
        set i=i+1 ;
        until i=data_times
    end repeat ;
commit ;
end$

–插入数据

	delimiter ; 
	call insert_emp(1000,800000) ;
	call insert_dept(10,30) ;

分析海量数据:

profiles

show profiles ; --默认关闭

show variables like '%profiling%';

set profiling = on ; 

show profiles  ：会记录所有profiling打开之后的  全部SQL查询语句所花费的时间。缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu  io ）

精确分析:sql诊断

 show profile all for query 上一步查询的的Query_Id

 show profile cpu,block io for query 上一步查询的的Query_Id

全局查询日志：

记录开启之后的全部SQL语句。（这次全局的记录操作仅仅在调优、开发过程中打开即可，在最终的部署实施时一定关闭）

show variables like '%general_log%';

–执行的所有SQL记录在表中

set global general_log = 1 ;--开启全局日志
set global log_output='table' ; --设置 将全部的SQL 记录在表中

–执行的所有SQL记录在文件中

set global log_output='file' ;
set global general_log = on ;
set global general_log_file='/tmp/general.log' ;

开启后，会记录所有SQL ：会被记录mysql.general_log表中。

select * from  mysql.general_log ;

锁机制

锁机制：解决因资源共享而造成的并发问题。

示例：买最后一件衣服X

A:      X   买 ：  X加锁 ->试衣服...下单..付款..打包 ->X解锁
B:  X       买：发现X已被加锁，等待X解锁，   X已售空

分类：

操作类型：
1. 读锁（共享锁）：对同一个数据（衣服），多个读操作可以同时进行，互不干扰。
2. 写锁（互斥锁）：如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作
操作范围：
1. 表锁：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。
2. 行锁：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。
3. 页锁

示例：

表锁：

–自增操作 MYSQL/SQLSERVER 支持；oracle需要借助于序列来实现自增

create table tablelock
(
    id int primary key auto_increment , 
    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');
commit;

增加锁：

locak table 表1  read/write  ,表2  read/write   ,...

查看加锁的表：

show open tables ;

会话：session :每一个访问数据的dos命令行、数据库客户端工具都是一个会话

加读锁：

会话0：
```
lock table  tablelock read ;
    
select * from tablelock; --读（查），可以
    
delete from tablelock where id =1 ; --写（增删改），不可以
```
其他表
```
select * from emp ; --读，不可以
delete from emp where eid = 1; --写，不可以
```
结论1：
- –如果某一个会话对A表加了read锁，则该会话可以对A表进行读操作、不能进行写操作：且该会话不能对其他表进行读、写操作。
- –即如果给A表加了读锁，则当前会话只能对A表进行读操作。

会话1（其他会话）：

select * from tablelock;   --读（查），可以
    
delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放

会话2（其他会话）：操作其他表

select * from emp ;  --读（查），可以

delete from emp where eid = 1; --写，可以

总结：

会话0给A表加了锁；其他会话的操作：

1. 可以对其他表（A表以外的表）进行读、写操作
1. 对A表：读-可以；  写-需要等待释放锁。

释放锁:

unlock tables ;

加写锁：

会话0：
```
lock table tablelock write ;
```
当前会话（会话0）可以对加了写锁的表进行任何操作（增删改查）；但是不能操作（增删改查）其他表
其他会话：

对会话0中加写锁的表可以进行增删改查的前提是：等待会话0释放写锁

MySQL表级锁的锁模式

MyISAM

在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，

在执行更新操作（DML）前，会自动给涉及的表加写锁。

所以对MyISAM表进行操作，会有以下情况：

对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

分析表锁定：

查看哪些表加了锁：

show open tables ;  1代表被加了锁

分析表锁定的严重程度：

show status like 'table%' ;

Table_locks_immediate :即可能获取到的锁数

Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)

一般建议：

Table_locks_immediate/Table_locks_waited > 5000，建议采用InnoDB引擎，否则MyISAM引擎

行锁（InnoDB）

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 ;

以后需要通过commit

会话0：写操作

insert into linelock values(6,'a6') ;

会话1：写操作同样的数据

update linelock set name='ax' where id = 6;

对行锁情况：

如果会话x对某条数据a进行 DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后才能对数据a进行操作。
表锁是通过unlock tables，也可以通过事务解锁 ; 行锁是通过事务解锁。

行锁，操作不同数据：

会话0：写操作

insert into linelock values(8,'a8') ;

会话1：写操作，不同的数据

update linelock set name='ax' where id = 5;

行锁，一次锁一行数据；因此如果操作的是不同数据，则不干扰。

行锁的注意事项：

如果没有索引，则行锁会转为表锁

show index from linelock ;
    
alter table linelock add index idx_linelock_name(name);

会话0：写操作

update linelock set name = 'ai' where name = '3' ;

会话1：写操作，不同的数据

update linelock set name = 'aiX' where name = '4' ;

两者互不干扰

会话0：写操作

update linelock set name = 'ai' where name = 3 ;

会话1：写操作，不同的数据

update linelock set name = 'aiX' where name = 4 ;

–可以发现，数据被阻塞了（加锁）

– 原因：如果索引类发生了类型转换，则索引失效。因此此次操作，会从行锁转为表锁。

行锁的一种特殊情况：间隙锁：值在范围内，但却不存在

–此时linelock表中没有id=7的数据
```
update linelock set name ='x' where id >1 and id<9 ;  
```
–即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。
```
insert into linelock values(7,"zs");
```
–结果被锁住了

间隙：Mysql会自动给间隙加锁 ->间隙锁。即本题会自动给id=7的数据加间隙锁（行锁）。

行锁：如果有where，则实际加锁的范围就是where后面的范围（不是实际的值）（2-8之间都加上锁）

如果仅仅是查询数据，能否加锁？

可以 for update

研究学习时，将自动提交关闭：(三种方式)

set autocommit =0 ;

start transaction ;

begin ;

	select * from linelock where id =2 for update ;

通过for update对query语句进行加锁。

行锁：

InnoDB默认采用行锁；

缺点：比表锁性能损耗大。

优点：并发能力强，效率高。

因此建议，高并发用InnoDB，否则用MyISAM。

行锁分析：

show status like '%innodb_row_lock%' ;

Innodb_row_lock_current_waits :当前正在等待锁的数量  
Innodb_row_lock_time：等待总时长。从系统启到现在 一共等待的时间
Innodb_row_lock_time_avg  ：平均等待时长。从系统启到现在平均等待的时间
Innodb_row_lock_time_max  ：最大等待时长。从系统启到现在最大一次等待的时间
Innodb_row_lock_waits ：	等待次数。从系统启到现在一共等待的次数

主从复制（集群在数据库的一种实现）

集群的优点 1. 负载均衡 2. 失败迁移

windows:mysql 主

linux:mysql从

安装windows版mysql:

如果之前计算机中安装过Mysql，要重新再安装则需要：先卸载再安装

先卸载：

通过电脑自带卸载工具卸载Mysql (电脑管家也可以)
删除一个mysql缓存文件夹C:\ProgramData\MySQL
删除注册表regedit中所有mysql相关配置
–重启计算机

安装MYSQL：

下载官网

安装时，如果出现未响应：则重新打开

xxx\bin\MySQLInstanceConfig.exe

图形化客户端： SQLyog, Navicat

如果要远程连接数据库，则需要授权远程访问。

在linux中授权远程访问 :(A->B,则再B计算机的Mysql中执行以下命令)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH PRIVILEGES;

如果仍然报错：可能是防火墙没关闭：在B关闭防火墙

service iptables stop

实现主从同步（主从复制）：

主从同步的核心：通过二进制日志

master将改变的数记录在本地的二进制日志中（binary log）；该过程称之为：二进制日志事件
slave将master的binary log拷贝到自己的 relay log（中继日志文件）中
中继日志事件，将数据读取到自己的数据库之中 MYSQL主从复制是异步的，串行化的，有延迟

MYSQL主从复制是异步的，串行化的，有延迟的

master:slave = 1:n

配置：

windows(mysql: my.ini)

linux(mysql: my.cnf)

配置前，为了无误，先将权限(远程访问)、防火墙等处理：

关闭windows/linux防火墙：

windows：右键“网络”
linux:
```
service iptables stop
```

Mysql允许远程连接(windowos/linux)：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

window mysql8.0 版本修改允许访问远程权限

use mysql;

select host, user, authentication_string, plugin from user;

update user set host='%' where user='root';

grant all privileges on *.* to root@'%';

flush privileges;

主机（以下代码和操作全部在主机windows中操作）：my.ini

[mysqld]
#id
server-id=1
#二进制日志文件（注意是/  不是\）
log-bin="C:/Program Files/MySQL/MySQL Server 8.0/data/mysql-bin"
#错误记录文件
log-error="C:/Program Files/MySQL/MySQL Server 8.0/data/mysql-error"
#主从同步时 忽略的数据库
binlog-ignore-db=mysql
#(可选)指定主从同步时，同步哪些数据库
binlog-do-db=test

windows中的数据库授权哪台计算机中的数据库是自己的从数据库：

GRANT REPLICATION slave,reload,super ON *.* TO 'root'@'192.168.2.%' IDENTIFIED BY 'root';
flush privileges ;

查看主数据库的状态（每次在左主从同步前，需要观察主机状态的最新值）

show master status;（mysql-bin.000001、 107）

（mysql-bin.000001、 107）：二进制文件名和位置

从机（以下代码和操作全部在从机linux中操作）：

my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=test

linux中的数据授权哪台计算机中的数控是自己的主计算机

CHANGE MASTER TO 
MASTER_HOST = '192.168.2.2', 
MASTER_USER = 'root', 
MASTER_PASSWORD = 'root', 
MASTER_PORT = 3306,
master_log_file='mysql-bin.000001',
master_log_pos=107;

如果报错：This operation cannot be performed with a running slave; run STOP SLAVE first

解决：

STOP SLAVE ;

再次执行上条授权语句

开启主从同步：

从机linux:

start slave ;

检验

show slave status \G

主要观察： Slave_IO_Running和 Slave_SQL_Running，确保二者都是yes；如果不都是yes，则看下方的 Last_IO_Error。

本次通过 Last_IO_Error发现错误的原因是主从使用了相同的server-id，检查:在主从中分别查看serverid:

show variables like 'server_id' ;

可以发现，在Linux中的my.cnf中设置了server-id=2，但实际执行时确实server-id=1，原因：可能是 linux版Mysql的一个bug，也可能是 windows和Linux版本不一致造成的兼容性问题。

解决改bug：

set global server_id =2 ;

重复执行上面

stop slave ;

set global server_id =2 ;

start slave ;

show slave status \G

确保两个是yes

演示：

主windows =>从

windows:

将表，插入数据

linux

观察从数据库中该表的数据

InnoDB MVCC 实现

之前在面试必问的 MySQL，你懂了吗？中简单的介绍了 MVCC 的原理，掌握了这个原理其实在面试时是可以加分不少的。

因为现在很多人的理解还是停留在《高性能 MySQL》书中的版本，也就是通过创建版本号和删除版本号来判断。这个时候如果你能给出正确的理解，则会让面试官眼前一亮，这也是我们在面试中凸显出“自己和其他候选者不一样的地方”，会更有利于在众多候选者中脱颖而出。

本文在此基础上，对 MVCC 展开详细的分析，同时修改了之前的一些不太准确的说法，希望可以助你在面试中更好的发（zhuang）挥（bi）。

PS：本文的源码基于MySQL 8.0.16，对于现阶段生产环境常用的 5.7.* 版本，MVCC 部分的源码基本相同，因此可以放心参考。而 5.6.* 则有比较大的不同，主要是一些数据结构都改变了，但是究其核心原理还是基本一致的。

基础概念

并发事务带来的问题（现象）

脏读：一个事务读取到另一个事务更新但还未提交的数据，如果另一个事务出现回滚或者进一步更新，则会出现问题。

不可重复读：在一个事务中两次次读取同一个数据时，由于在两次读取之间，另一个事务修改了该数据，所以出现两次读取的结果不一致。

幻读：在一个事务中使用相同的 SQL 两次读取，第二次读取到了其他事务新插入的行。

要解决这些并发事务带来的问题，一个比较简单粗暴的方法是加锁，但是加锁必然会带来性能的降低，因此 MySQL 使用了 MVCC 来提升并发事务下的性能。

MVCC 带来的好处？

试想，如果没有 MVCC，为了保证并发事务的安全，一个比较容易想到的办法就是加读写锁，实现：读读不冲突、读写冲突、写读冲突，写写冲突，在这种情况下，并发读写的性能必然会收到严重影响。

而通过 MVCC，我们可以做到读写之间不冲突，我们读的时候只需要将当前记录拷贝一份到内存中（ReadView），之后该事务的查询就只跟 ReadView 打交道，不影响其他事务对该记录的写操作。

事务隔离级别

读未提交（Read Uncommitted）：最低的隔离级别，会读取到其他事务还未提交的内容，存在脏读。

读已提交（Read Committed）：读取到的内容都是已经提交的，可以解决脏读，但是存在不可重复读。

可重复读（Repeatable Read）：在一个事务中多次读取时看到相同的内容，可以解决不可重复读，但是存在幻读。但是在 InnoDB 中不存在幻读问题，对于快照读，InnoDB 使用 MVCC 解决幻读，对于当前读，InnoDB 通过 gap locks 或 next-key locks 解决幻读。

串行化（Serializable）：最高的隔离级别，串行的执行事务，没有并发事务问题。

核心数据结构

trx_sys_t：事务系统中央存储器数据结构

struct trx_sys_t {
  TrxSysMutex mutex; /*! 互斥锁 */
  MVCC *mvcc;    /*!  mvcc */
  volatile trx_id_t max_trx_id; /*! 要分配给下一个事务的事务id*/
  std::atomic<trx_id_t> min_active_id; /*! 最小的活跃事务Id */
  // 省略...
  trx_id_t rw_max_trx_id; /*!< 最大读写事务Id */
  // 省略...
  trx_ids_t rw_trx_ids; /*! 当前活跃的读写事务Id列表 */
  Rsegs rsegs; /*!< 回滚段 */
  // 省略...
};

MVCC：MVCC 读取视图管理器

class MVCC {
 public:
  // 省略...
  /** 创建一个视图 */
  void view_open(ReadView *&view, trx_t *trx);  /** 关闭一个视图 */

  void view_close(ReadView *&view, bool own_mutex);  /** 释放一个视图 */

  void view_release(ReadView *&view);

 // 省略...

  /** 判断视图是否处于活动和有效状态 */

  static bool is_view_active(ReadView *view) {
    ut_a(view != reinterpret_cast<ReadView *>(0x1));

    return (view != NULL && !(intptr_t(view) & 0x1));
  }

 // 省略.

 private:



  typedef UT_LIST_BASE_NODE_T(ReadView) view_list_t;  /** 空闲可以被重用的视图*/

  view_list_t m_free;  /**  活跃或者已经关闭的 Read View 的链表 */

  view_list_t m_views;
};

ReadView：视图，某一时刻的一个事务快照

class ReadView {

  // 省略.

 private:  /** 高水位，大于等于这个ID的事务均不可见*/

  trx_id_t m_low_limit_id;  /** 低水位：小于这个ID的事务均可见 */

  trx_id_t m_up_limit_id;  /** 创建该 Read View 的事务ID*/

  trx_id_t m_creator_trx_id;  /** 创建视图时的活跃事务id列表*/

  ids_t m_ids;  /** 配合purge，标识该视图不需要小于m_low_limit_no的UNDO LOG，



   * 如果其他视图也不需要，则可以删除小于m_low_limit_no的UNDO LOG*/

  trx_id_t m_low_limit_no;  /** 标记视图是否被关闭*/

  bool m_closed;

  // 省略..
};

增加隐藏字段

为了实现 MVCC，InnoDB 会向数据库中的每行记录增加三个字段：

DB_ROW_ID：行ID，6字节，随着插入新行而单调递增，如果有主键，则不会包含该列。

DB_TRX_ID：事务ID，6字节，记录插入或更新该行的最后一个事务的事务标识，也就是事务ID。

DB_ROLL_PTR：回滚指针，7字节，指向写入回滚段的 undo log 记录。每次对某条记录进行更新时，会通过 undo log 记录更新前的行内容，更新后的行记录会通过 DB_ROLL_PTR 指向该 undo log 。当某条记录被多次修改时，该行记录会存在多个版本，通过DB_ROLL_PTR 链接形成一个类似版本链的概念，大致如下图所示。

源码分析

在源码中，添加这3个字段的方法在：/storage/innobase/dict/dict0dict.cc 的 dict_table_add_system_columns 方法中，核心部分如下图。

增删改的底层操作

当我们更新一条数据，InnoDB 会进行如下操作：

加锁：对要更新的行记录加排他锁
写 undo log：将更新前的记录写入 undo log，并构建指向该 undo log 的回滚指针 roll_ptr
更新行记录：更新行记录的 DB_TRX_ID 属性为当前的事务Id，更新 DB_ROLL_PTR 属性为步骤2生成的回滚指针，将此次要更新的属性列更新为目标值
写 redo log：DB_ROLL_PTR 使用步骤2生成的回滚指针，DB_TRX_ID 使用当前的事务Id，并填充更新后的属性值
处理结束，释放排他锁

删除操作：在底层实现中是使用更新来实现的，逻辑基本和更新操作一样，几个需要注意的点：1）写 undo log 中，会通过 type_cmpl 来标识是删除还是更新，并且不记录列的旧值；2）这边不会直接删除，只会给行记录的 info_bits 打上删除标识（REC_INFO_DELETED_FLAG），之后会由专门的 purge 线程来执行真正的删除操作。

插入操作：相比于更新操作比较简单，就是新增一条记录，DB_TRX_ID 使用当前的事务Id，同样会有 undo log 和 redo log。

源码分析

更新行记录的核心源码在：/storage/innobase/btr/btr0cur.cc/btr_cur_update_in_place 方法，核心部分如下图。

构建一致性读取视图（ReadView）

当我们的隔离级别为 RR 时：每开启一个事务，系统会给该事务会分配一个事务 Id，在该事务执行第一个 select 语句的时候，会生成一个当前时间点的事务快照 ReadView，核心属性如下：

m_ids：创建 ReadView 时当前系统中活跃的事务 Id 列表，可以理解为生成 ReadView 那一刻还未执行提交的事务，并且该列表是个升序列表。
m_up_limit_id：低水位，取 m_ids 列表的第一个节点，因为 m_ids 是升序列表，因此也就是 m_ids 中事务 Id 最小的那个。
m_low_limit_id：高水位，生成 ReadView 时系统将要分配给下一个事务的 Id 值。
m_creator_trx_id：创建该 ReadView 的事务的事务 Id。

源码分析

MVCC 模式下的普通查询主方法入口在：/storage/innobase/row/row0sel.cc 的 row_search_mvcc 方法中，之后的所有源码分析基本都在该方法内。

具体创建视图的方法在 ReadView::prepare，调用链如下：

row_search_mvcc -> trx_assign_read_view -> MVCC::view_open ->

ReadView::prepare，源码如下：

最后，会将这个创建的 ReadView 添加到 MVCC 的 m_views 中。

视图可见性判断：SQL 查询走聚簇索引

有了这个 ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

如果被访问版本的 trx_id 与 ReadView 中的 m_creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 小于 ReadView 中的 m_up_limit_id（低水位），表明被访问版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的 trx_id 大于等于 ReadView 中的 m_low_limit_id（高水位），表明被访问版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的 trx_id 属性值在 ReadView 的 m_up_limit_id 和 m_low_limit_id 之间，那就需要判断 trx_id 属性值是不是在 m_ids 列表中，这边会通过二分法查找。如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

在进行判断时，首先会拿记录的最新版本来比较，如果该版本无法被当前事务看到，则通过记录的 DB_ROLL_PTR 找到上一个版本，重新进行比较，直到找到一个能被当前事务看到的版本。

而对于删除，其实就是一种特殊的更新，InnoDB 在 info_bits 中用一个标记位 delete_flag 标识是否删除。当我们在进行判断时，会检查下 delete_flag 是否被标记，如果是，则会根据情况进行处理：1）如果索引是聚簇索引，并且具有唯一特性（主键、唯一索引等），则返回 DB_RECORD_NOT_FOUND；2）否则，会寻找下一条记录继续流程。

其实很容易理解，如果是唯一索引查询，必然只有一条记录，如果被删除了则直接返回空，而如果是普通索引，可能存在多个相同值的行记录，该行不存在，则继续查找下一条。

以上内容是对于 RR 级别来说，而对于 RC 级别，其实整个过程几乎一样，唯一不同的是生成 ReadView 的时机，RR 级别只在事务第一次 select 时生成一次，之后一直使用该 ReadView。而 RC 级别则在每次 select 时，都会生成一个 ReadView。

源码分析

走聚簇索引的核心流程在 row_search_mvcc 方法，如下：

视图可见性判断在方法：changes_visible，调用链如下：

row_search_mvcc -> lock_clust_rec_cons_read_sees ->

changes_visible，源码如下：

判断记录是否被打上 delete_flag 标的方法在：/storage/innobase/include/rem0rec.ic 的 rec_get_deleted_flag 方法中，如下图。

获取记录的上一个版本

获取记录的上一个版本，主要是通过 DB_ROLL_PTR 来实现，核心流程如下：

获取记录的回滚指针 DB_ROLL_PTR、获取记录的事务Id
通过回滚指针拿到对应的 undo log
解析 undo log，并使用 undo log 构建用于更新向量 UPDATE
构建记录的上一个版本：先用记录的当前版本填充，然后使用 UPDATE（undo log）进行覆盖。

源码解析

构建记录的上一个版本：trx_undo_prev_version_build，调用链如下：

row_search_mvcc -> row_sel_build_prev_vers_for_mysql -> row_vers_build_for_consistent_read -> trx_undo_prev_version_build，源码如下：

视图可见性判断：SQL 查询走普通（二级）索引

面试必问的 MySQL，你懂了吗？只分析了走聚簇索引的情况，本文简单的介绍下走普通（二级）索引的情况。

当走普通索引时，判断逻辑如下：

判断被访问索引记录所在页的最大事务 Id 是否小于 ReadView 中的 m_up_limit_id（低水位），如果是则代表该页的最后一次修改事务 Id 在 ReadView 创建前以前已经提交，则必然可以访问；如果不是，并不代表一定不可以访问，道理跟走聚簇索引一样，事务 Id 大的也可能提交比较早，所以需要做进一步判断，见步骤2。
使用 ICP（Index Condition Pushdown）根据索引信息来判断搜索条件是否满足，这边主要是在使用聚簇索引判断前先进行过滤，这边有三种情况：a）ICP 判断不满足条件但没有超出扫描范围，则获取下一条记录继续查找；b）如果不满足条件并且超出扫描返回，则返回 DB_RECORD_NOT_FOUND；c）如果 ICP 判断符合条件，则会获取对应的聚簇索引来进行可见性判断。

源码分析

普通（非聚簇）索引的视图可见性判断在方法：lock_sec_rec_cons_read_sees，调用链如下：

row_search_mvcc -> lock_sec_rec_cons_read_sees，源码如下：

扩展理解

ICP（Index Condition Pushdown）

ICP 是 MySQL 5.6 引入的一个优化，根据官方的说法：ICP 可以减少存储引擎访问基表的次数和 MySQL 访问存储引擎的次数，这边涉及到 MySQL 底层的处理逻辑，不是本文重点，这边不进行细讲。

这边用官方的例子简单介绍下，我们有张 people 表，索引定义为：INDEX (zipcode, lastname, firstname)，对于以下这个 SQL：

SELECT * FROM people



  WHERE zipcode='95054'



  AND lastname LIKE '%etrunia%'



  AND address LIKE '%Main Street%';

当没有使用 ICP 时：此查询会使用该索引，但是必须扫描 people 表所有符合 zipcode=‘95054’ 条件的记录。

当使用 ICP 时：不仅会使用 zipcode 的条件来进行过滤，还会使用（lastname LIKE ‘%etrunia%‘）来进行过滤，这样可以避免扫描符合 zipcode 条件而不符合 lastname 条件匹配的记录行。

ICP 的官方文档：https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

当前读和快照读

当前读：官方叫做 Locking Reads（锁定读取），读取数据的最新版本。常见的 update/insert/delete、还有 select … for update、select … lock in share mode 都是当前读。

官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

快照读：官方叫做 Consistent Nonlocking Reads（一致性非锁定读取，也叫一致性读取），读取快照版本，也就是 MVCC 生成的 ReadView。用于普通的 select 的语句。

官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

MVCC 解决了幻读了没有？

MVCC 解决了部分幻读，但并没有完全解决幻读。

对于快照读，MVCC 因为因为从 ReadView 读取，所以必然不会看到新插入的行，所以天然就解决了幻读的问题。

而对于当前读的幻读，MVCC 是无法解决的。需要使用 Gap Lock 或 Next-Key Lock（Gap Lock + Record Lock）来解决。

其实原理也很简单，用上面的例子稍微修改下以触发当前读：select * from user where id < 10 for update，当使用了 Gap Lock 时，Gap 锁会锁住 id < 10 的整个范围，因此其他事务无法插入 id < 10 的数据，从而防止了幻读。

Repeatable Read 解决了幻读是什么情况？

SQL 标准中规定的 RR 并不能消除幻读，但是 MySQL InnoDB 的 RR 可以，靠的就是 Gap 锁。在 RR 级别下，Gap 锁是默认开启的，而在 RC 级别下，Gap 锁是关闭的。

几个例子

例子1：RR（RC）真正生成 ReadView 的时机

解析：RR 生成 ReadView 的时机是事务第一个 select 的时候，而不是事务开始的时候。右边的例子中，事务1在事务2提交了修改后才执行第一个 select，因此生成的 ReadView 中，a 的是 100 而不是事务1刚开始时的 50。

例子2：RR 和 RC 生成 ReadView 的区别

解析：RR 级别只在事务第一次 select 时生成一次，之后一直使用该 ReadView。而 RC 级别则在每次 select 时，都会生成一个 ReadView，所以在第二次 select 时，读取到了事务2对于 a 的修改值。

最后

MySQL 的源码主要是 c++ 写的，因此自己看起来比较吃力，花了挺多时间学习整理的。如果你能掌握本文的内容，面试 Java 岗位，无论是哪个公司，相信都能让面试官眼前一亮。

现在互联网的竞争越来越激烈，如果很多东西都只停留在表面，很难取得面试官的“芳心”，只有在适当的时候亮出自己的“长剑”，才能在众多候选人中凸显出自己的与众不同。你需要向面试官证明，为什么是你而不是其他人。

最后 MySQL 的源码主要是 c++ 写的，因此自己看起来比较吃力，花了挺多时间学习整理的。如果你能掌握本文的内容，面试 Java 岗位，无论是哪个公司，相信都能让面试官眼前一亮。 ———————————————— 版权声明：本文为CSDN博主「程序员囧辉」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/v123411739/article/details/108379583

本文链接: https://mumulx.github.io/2020/01/sql%E4%BC%98%E5%8C%96mysql%E7%89%88/

License：CC BY 4.0 CN

木木就是两个木木木

愿生活不太拥挤，愿笑容不必刻意。

SQL优化Mysql版

MySQL版本：

基础

原理

SQL优化

SQL ：

SQL优化， 主要就是 在优化索引

索引

分类：

创建索引：

SQL性能问题

准备数据：

表字段

id:

select_type:

union

type

system

const

eq_ref

ref

range

index

all

小结

possible_keys

key

key_len

ref

rows

Extra：

using filesort ：

using temporary:

using index :

using where

impossible where

优化实例

优化案例

单表优化

两表优化

三张表优化A B C

避免索引失效的一些原则

复合索引

索引操作

复合索引关键判断

Like问题

类型转换

or

一些其他的优化方法

exist和in

order by 优化

SQL排查 - 慢查询日志:

分析海量数据

通过存储函数 插入海量数据：

分析海量数据:

profiles

精确分析:sql诊断

全局查询日志 ：

锁机制

表锁 ：

加读锁：

加写锁：

MySQL表级锁的锁模式

分析表锁定：

行锁（InnoDB）

主从复制 （集群在数据库的一种实现）

先卸载：

安装MYSQL：

主机（以下代码和操作 全部在主机windows中操作）：my.ini

从机（以下代码和操作 全部在从机linux中操作）：

开启主从同步：

演示：

InnoDB MVCC 实现

基础概念

并发事务带来的问题（现象）

MVCC 带来的好处？

事务隔离级别

核心数据结构

增加隐藏字段

增删改的底层操作

SQL优化，主要就是在优化索引

通过存储函数插入海量数据：

全局查询日志：

表锁：

主从复制（集群在数据库的一种实现）

主机（以下代码和操作全部在主机windows中操作）：my.ini

从机（以下代码和操作全部在从机linux中操作）：

例子1：RR（RC）真正生成 ReadView 的时机