SQL优化详解

SQL优化详解

SQL语句编写及执行顺序：
编写顺序：select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
解析顺序：from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

此期间常用SQL语句：
查询表中所有索引：show index from (表名);
删除索引：drop index (索引名) on (表名);
添加索引：alter table (表名) add index (索引名) (字段1,字段2…);
查询创建表SQL语句形式的表字段信息：show create table (表名);
查询表格形式的表字段信息：desc (表名);
查询mysql中所有表是否被锁：show open tables; （1代表加了锁，0代表没加锁）

MySQL版本

目前最新版本：8.x；
主流版本：5.x；
其中，5.0~5.1：是早期产品的延续，即将4.x版本的升级维护了一下；
5.2~5.3：用得少；
5.4以上：目前企业用户用的最多（5.5），MySQL整合了三方公司的新存储引擎；

MySQL底层原理

SQL语句解析详解链接：https://www.cnblogs.com/annsshadow/p/5037667.html

一、从客户端发送请求到服务器的原理
客户端：发送Servlet请求 给服务器端；
服务器端：Servlet请求传于此，先后抵达
1）连接层（提供与客户端连接的服务）
2）服务层（1.提供各种用户使用的接口；2.提供SQL优化器，MySQL QUery Optimizer，即将开发者写的SQL语句中它所认为不足的地方进行优化）
3）引擎层（提供了各种存储数据的方式（InnoDB MyISAM））
InnoDB和MyISAM引擎基于优化方式的区别：
InnoDB（MySQL默认引擎）：事务优先，适用于高并发操作，给出行锁，即一次给每行上锁；
MyISAM：性能优先，给出表锁，即一次给一张表上锁。
4）存储层（存储数据库数据）

二、在mysql.exe命令行中，查询数据库引擎
查询mysql所支持的所有引擎：show engines;
查询mysql当前仅使用的引擎（默认引擎）：show variables like ‘%storyage_engine%’;

三、指定数据库对象的引擎（在建表时指定）
create table (表名)(
…
)ENGINE=(引擎名);

SQL优化

一、优化可能原因
1）SQL性能低
2）SQL语句执行时间太长
3）SQL语句等待时间太长
4）SQL语句欠佳（连接查询过多）
5）索引失效
6）服务器参数设置不合理（缓冲区、线程数）
…

二、SQL语句编写及执行顺序：
编写顺序：select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
解析顺序：from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

三、SQL优化，主要是优化索引
1）索引：index，是一种数据结构，实质是树，包括：B树、Hash树，其中B树是SQL索引默认数据结构；
2）索引功能：利用B树的性质帮助MySQL高效获取数据；
3）功能原理：一个索引对应一个字段，将该字段下每个数据按顺序存储在索引中，即B树中（可通过二叉排序树来排，当然也可以用其他阶B树），树中每个结点对应该数据所在表的那一行的地址。当查询时，通过查询二叉排序树找到该结点，再找到该结点对应表中那一行的地址，则找到了该行数据。

4）索引的弊端：
a.索引本身很大，可以存放在内存或硬盘，通常放在硬盘；
b.索引不是所有情况均使用，eg：少量数据的表、频繁更新的字段（不仅要更新表，还可能使得索引的B树结构大变）、很少使用的字段（浪费存储空间）；
c.索引会降低增删改的效率，理由同频繁更新的字段。
5）索引的优势：
a.提高查询效率（降低IO使用率）；
b.降低CPU使用率，eg：select * from student order by age desc，根据age字段排序得结果，此时由于B树可以是二叉排序树（当然也可以是其他阶B树），即可直接通过中序(逆中序)遍历得到排序结果，不用再对整张表排序。

索引

一、分类
1）单值索引：单个字段，一个表中可以有多个单值索引；
2）唯一索引：字段值不能重复，该列的值可以为null，可以有多个，eg：id；
3）主键索引：字段值不能重复，与唯一索引的区别是，该列的值不可为null、且只能有一个，eg：id，
4）复合索引：多个列构成的索引（相当于多级目录），eg：(name,age) 先按name查找，若name有重复值，则按age查找，若无重复，则无需按age查找。

二、创建索引
方式1）create 索引类型 index 索引名 on 表名(字段名);

单值索引：eg：create index dept_index on tb(dept);

唯一索引：eg：create unique index name_index on tb(name);

复合索引：eg：create index name_index on tb(dept,name);

方式2）alter table 表名 add 索引类型 index 索引名(字段名);

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

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

复合索引：eg：alter table tb add index name_index(dept,name);

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

三、删除索引
  drop index 索引名 on 表名;
eg：drop index name_index on tb;

四、查询索引
  show index from 表名；
eg：show index from 表名 \G；

SQL性能问题，优化方法

一、SQL自带优化器的属性的简单介绍
a.分析SQL的执行计划：explain+(sql语句)， 可以模拟SQL优化器执行SQL语句，从而让开发人员，知道自己编写的SQL状况；（人为优化）
b.查询时MySQL自带的优化器会干扰我们自己写的优化；（自动优化）

eg：

如图：
id：编号；
select_type：查询类型；
table：表；
type：索引类型，也可直接叫“类型”；
possible_keys：预测用到的索引
key：实际使用的索引；
key_len：实际使用索引的长度；
ref：表之间的引用；
rows：查询到的行数；
Extra：额外的信息。

二、对explain语句属性的详解
1）id
 a）id值相同（简单查询(select_type:SIMPLE)，即单表或多表查询，无嵌套子查询），执行顺序：按table属性栏从上往下顺序执行。
原理：多表查询时，table属性栏按各表之间的笛卡尔积的由小到大顺序从上往下排，这样，运行时占用的内存就尽可能的降低。
 b）id值不同（嵌套子查询(select_type:PRIMARY、SUBQUERY)），id值越大越优先查询。
原理：嵌套子查询时，越内层的子句，id值越大，而嵌套查询的顺序又是从最里面开始执行，一直往外。

2）select_type：查询类型；
 a）PRIMARY：SQL语句中的主查询，即最外层查询；
 b）SUBQUERY：SQL语句中的嵌套子查询，即非最外层查询；
 c）simple：简单查询，即不包含嵌套子查询、union；
 d）derived：衍生查询，需使用到临时表；
  ①）在from后的子查询中，仅有一张表，
    eg：explain select A from ( select * from B where XXX ); ，此时B为derived，最外层主查询为PRIMARY。
  ②）在from后的子查询中，有table1 union table2，
    eg：explain select A from ( select * from B XX union select * from C where XX ); ，此时B为derived，C为union。

 e）union：将两个查询的结果合并起来，两个查询的字段数量、类型必须相同且对应；（如上述d）
 f）union result：显示了哪些表之间存在union查询。

3）table：所使用的表；

4）type：索引类型，也可叫类型；
  system>const>eq_ref>ref>range>index>all ，这些是优化类型，由高到低排序，若要对type进行优化，则要有索引。实际开发中，system、const只是理想情况，实际最高达到ref、range。

——以下，对各个优化类型的适用范围做详解：
a）system：适用只有一条数据的系统表，或衍生表中只有一条数据的主查询；

b）const：仅能查到一条数据，且where后的条件字段为Primary key或unique索引；

c）eq_ref：可查询到多条数据，where后的条件字段为Primary key或unique索引，且返回唯一匹配的数据；
 eg：select u1.id from user u1,user2 u2 where u1.id=u2.id; ，连接查询中user和user2表中的数据一一对应（个数相同），没有一方多或少；

d）ref：可查询到多条数据，where后的条件字段可为非唯一性索引，且返回匹配的所有行，即若是连接查询，则匹配的数据个数可以不同，即一方多或一方少；

e）range：检索指定范围的行，where后要是一个范围查询，即between、>、<、>=、<=，有时in会失效，从而索引失效，转为无索引，即all；

f）index：查询select中的字段，包含在索引中；

g）all：查询全部/多条数据，包括查询无索引数据，或者同时查询有索引和无索引数据，且where子句后的条件字段不是索引，或无where子句；

5）possible_keys：预测可能用到的索引，只是预测，不一定准确，若possible_key或key显示null，则说明没有使用索引；

6）key：实际使用到的索引；

7）key_len：索引的长度；
 作用：用于判断复合索引是否被完全使用，
 eg：
  utf-8中，1个字符占3个字节；
  gbk中，1个字符占2个字节；
  latin中，1个字符占1个字节；
  假设在utf-8编码中定义char(20)的字段，则占203=60个字节，若该字段可为null，则为61个字节，null占1个字节；
  假设在utf-8编码中定义varchar(20)的字段且可为null，则占203+1+2=60个字节，用2个字节来标识可变长度；

8）ref：指明当前表所对应where后的条件字段所参照的字段/常量，若无where子句，则ref属性值为NULL；
 注：此ref是explain栏中的属性，而不是type属性中的ref值，注意区分；
 eg：select * from user; ，值为NULL；
   select u1.* from user u1,user2 u2 where u1.sex=1 and u1.id=u2.id; ， u1的ref值为const，u2的ref值为test.u1.id；

9）rows：被索引优化查询的数据个数，即通过索引查询到的数据个数，但不准确；

10）Extra：
 a）using filesort：表示性能消费大，即需要“额外”的若干次排序（查询），常见于order by语句；（SQL解析中where位于order by之前）
 eg：explain select * from test02 where a1 ="XX" order by a2 ; --using filesort

  i）单索引：若排序和查找用的不是同一字段，则会出现using filesort，反之，则不会出现using filesort；避免方法：尽量使where与order by中字段相同。
  ii）复合索引：若排序或查询中使用的复合索引跨列了，或不按顺序使用，则会出现using filesort，反之，若按复合索引顺序出现，则不会出现using filesort；避免方法：where和order by中尽量按复合索引的顺序使用，不出现跨列或无序现象。

 b）using index：表示性能提升，即要查询的字段和where子句中条件字段，被索引覆盖。
 eg：explain select a1,a2 from test02 where a1='' or a2= '' ; --using index

alter table test02 add index idx_a1_a2(a1,a2) ;
explain select a1,a3 from test02 where a1='' or a3= '' ;	

–不出现using index

 注：若出现using index，则会对possible_keys和key造成影响：
  若没使用了where，则索引只出现在key中，possible_key不出现该索引；反之，索引出现在key和possible_key。

 c）using where：需要回表查询，即 既需要在索引中查，又需要在表中查；

 d）impossible where：where中条件判定结果永远为false；
  eg：explain select * from user where a1='x' and a1='y' ;

优化案例

一、单表优化

示例：

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 ; ——Using where; Using filesort

开始优化：加索引，加复合索引(bid,typeid,authorid)，再次执行
 alter table book add index idx_bta (bid,typeid,authorid);
 explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ; ——Using where; Using index; Using filesort，出现Using index的优化信息；

再次优化：调整复合索引顺序，根据SQL解析顺序，前一个复合索引顺序与之不符，所以将顺序修改，再次执行：
 drop index idx_bta on book;
 alter table book add index idx_bta (typeid,authorid,bid);
 explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ; ——Using where; Using index ，此时已将Using filesort干掉；

再次优化：因范围查询in有时会使索引失效，因此交换索引顺序，将使用in的条件字段的索引位置，放在复合索引中、与其同级别条件字段的后面，最后再次执行；
 drop index idx_bta on book;
 alter table book add index idx_bta (authorid,typeid,bid);
 explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc ; ——Using where; Using index

总结：
 1）最佳左前缀，保持索引的定义和使用的顺序一致性；
 2）逐步优化索引，根据explain中的属性值级别，一步步将语句级别向高级优化；
 3）若where子句中使用in的条件字段存在于复合索引，则将其排在where语句中条件字段的最后；

注：
 本例中同时出现Using where（需回表）; Using index（不需回表），因为authorid不需回表，而typeid使用了in，使索引失效，所以需回表。
 explain select bid from book where authorid=1 and typeid=2 order by typeid desc ; ——Using index

二、两表优化

示例：

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'; ——Using where

开始优化：加索引，怎么加？
原则：
 1）小表驱动大表，即on后的两个字段中，给数据量少的字段加，且一般将数据量少的字段放在等号“=”左边，大的放右边；
 2）索引建立在经常使用的字段上；
原因：eg：

小表：10条
大表：300条
where 小表.x 10 = 大表.y 300; --循环了10次
大表.y 300=小表.x 10 --循环了300次

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


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

结果：以上两个for循环，都会循环3000次，但对于双层循环来说，一般建议将数据量小的循环放外层，数据量大的放内层。

三、三表优化（与两表优化相似）
1）小表驱动大表
2）索引建立在经常查询的字段上

复合索引示例：

alter table user add index idx_a1_a2_a3_a4(a1,a2,a3,a4);
explain select a1,a2,a3,a4 from user where a1=1 and a2=2 and a3=3 and a4=4 ; ——推荐
explain select a1,a2,a3,a4 from user where a4=4 and a3=3 and a2=2 and a1=1 ; ——结果一样，但不推荐
——以上两个SQL，使用了全部的复合索引，即using index

explain select a1,a2,a3,a4 from user where a1=1 and a2=2 and a4=4 order by a3;
——a4出现跨列，造成索引失效，需要回表查询，即using where; using index

explain select a1,a2,a3,a4 from user where a1=4 and a4=4 order by a3;
——a4出现跨列，造成索引失效，需要回表查询，且order by中a3与where也出现跨列，造成额外一次排序，即using where; using index; using filesort

 注：复合索引在order by与where是否出现跨列，具体表现为，从where中条件字段开始到order by中条件字段结束，其复合索引是否跨列。eg：复合索引(a1,a2,a3,a4)，where中有a1，order by中就要从a2开始。

 总结：如果(a,b,c,d)复合索引，全部使用时，则按定义的顺序使用；部分使用时，则按最左原则，按从最左往右顺序使用。

避免索引失败（优化失败）的原则

1）复合索引
 a）最佳左前缀原则，不要跨列或无序使用；
 b）尽量使用全索引匹配，用什么字段就包含什么字段进复合索引；

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

eg：

	select ..where A.x = .. ;  --假设A.x是索引，不要select ..where A.x*3 = .. ;
	explain select * from book where authorid = 1 and typeid = 2 ; ---用到了2个索引
	explain select * from book where authorid = 1 and typeid*2 = 2 ; ---用到了1个索引
	explain select * from book where authorid*2 = 1 and typeid*2 = 2 ; ----无索引用到
	explain select * from book where authorid*2 = 1 and typeid = 2 ; ----无索引用到，原因：对于复合索引，如果左边失效，右侧全部失效。(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 ; ---用到了1个索引，即typeid 

3）复合索引不能使用不等于号（!=，<>）或is null（is not null），否则自身以及右侧所有索引全部失效；

 注：SQL优化，是一种概率层面的优化，即理想中的优化结果和实际优化结果可能不同，需要通过explain进行推测，具体原因往下看。

 原因：优化具有概率的情况，是因为服务层中SQL优化器，该优化器检查我们写的SQL，若不符合它的性能程度，则会改变此SQL语句，导致影响我们写的优化。

关于概率情况，示例：

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 ;--复合索引全部失效

总结：索引实际优化中，大部分情况都适用，但由于SQL优化器等原因，少部分情况会与实际不符。

4）补救：对于上述情况，可以尽量使用索引覆盖（using index），来弥补优化；
 eg：
 复合索引（a,b,c）
 select a,b,c from xx..where a= .. and b =.. ;

5）like不要以’%'开头，尽量以常量开头，否则索引失效；

 eg：
 索引tname

select * from xx where tname like '%x%' ; --name索引失效
explain select * from teacher  where tname like 'x%'; --tname索引有效
explain select tname from teacher  where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。

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

 eg：

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

7）尽量不要使用or，否则索引失效；（有点变态…or都不能用）

 eg：

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

其他优化方法

1）exists和in

select ..from (表名) where exists (子查询) ;
select ..from (表名) where 字段 in (子查询) ;

此时，
若主查询的数据量大，则使用in，效率更高；
若子查询的数据量大，则使用exists，效率更高；

2）order by优化

using filesort有两种算法：双路排序，单路排序（根据IO的次数区别二者）

双路排序：扫描2次硬盘，第1次从磁盘读取排序字段，对排序字段进行排序（在buffer中排序），第2次扫描其他字段；——IO较消耗性能
单路排序：只读取1次（读取全部字段），并在buffer中排序，但这种排序会有一定隐患，不一定真的是1次IO，有可能多次IO；原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取/多次读取”。

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

注：显然，单路排序比双路排序会占用更多的buffer。单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小，命令：set max_length_for_sort_data = 1024 ，（单位：字节byte），如果max_length_for_sort_data值太低，则mysql会自动从单路—>双路（需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）。

总结：
提高order by查询的策略：
a）可以调整buffer的容量大小使其在单路排序中；
b）避免select * …；（要查什么写什么）
c）使用复合索引时不要跨列，避免using filesort；
d）保证全部排序字段升降序的一致性（一条语句里都是升序或降序）

SQL排查 - 慢日志查询

1）SQL排查 - 慢日志查询（工具）：这是MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阀值的SQL语句（long_query_time，默认10秒）。

该日志默认是关闭的，因此建议：开发调优时打开，最终部署（产品上线）时关闭。

临时开启命令（关闭服务MySQL时自动关闭）：

set global slow_query_log = 1 ;  --在内存中开启
exit
service mysql restart --重新启动mysql服务，相当于关闭了一次

永久开启：
/etc/my.cnf 中追加配置：
vi /etc/my.cnf

在[mysqld]中最下面追加
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log

2）慢查询阀值

查询SQL默认执行几秒：show variables like '%long_query_time%' ;

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

永久设置阀值：
/etc/my.cnf 中追加配置：
vi /etc/my.cnf

在[mysqld]中最下面追加
long_query_time=3 （设置默认时间为3秒）

eg：select sleep(4); --执行SQL，功能是等待4秒
查询超过阀值的SQL语句的命令： show global status like '%slow_queries%' ; （会显示上面那条超过阀值的SQL语句）

3）查询具体的慢SQL（不是SQL命令，要先退出mysql在命令行中执行）

慢查询的sql被记录在了日志中，因此可以通过日志 查看具体的慢SQL。
在命令行中打开该日志文件：cat /var/lib/mysql/localhost-slow.log

4）通过mysqldumpslow工具查看慢SQL（不是SQL命令，要先退出mysql在命令行中执行）

该工具可以通过一些过滤条件，快速查找出需要定位的慢SQL

mysqldumpslow --help
s：排序方式
r:逆序
l:锁定时间
g:正则匹配模式

eg：
–获取返回记录最多的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 各种参数 慢查询日志的文件

分析海量数据

示例：
1）模拟插入海量数据，需用到存储过程（无return）、存储函数（有return）

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 $   --指定MySQL结尾符是$，而不是分号
	create function randstring(n int)   returns varchar(255) 	--注意此处returns有s
	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)       );	--FLOOR：向下取整；rand()*X：取[0~1*X),0闭区间，1*X开区间；	
			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

--产生随机整数
create function ran_num() returns int(5)
begin
	declare i int default 0;
	set i =floor( rand()*100 ) ;
	return i ;

end $

create procedure insert_emp( in eid_start int(10),in data_times int(10))
begin 
	declare i int default 0;
	set autocommit = 0 ;	--通过存储过程插入海量数据：emp表中  ，  10000,   100000
	
	repeat
		insert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ;
		set i=i+1 ;
		until i=data_times	--until不需要分号结尾
	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) ;

2）分析海量数据：
 a）profiles
 show profiles; ：会记录所有profiles打开之后的全部SQL查询语句所花费的时间，缺点是，不够精确，只能看到总共消费的时间，不能看到各个硬件消费的时间（CPU IO）；
 show variables like '%profiling%'; ：查询profiles是否开启；
 set profiling = on; ：打开profiles（临时的），（通过set开启某项配置一般都是临时开启）；

 b）精确分析：SQL诊断
 show profile all for query (query_id); ：查询某Query_Id的SQL语句的所有设备各占用的时间；
 show profile cpu,block io for query (query_id); ：根据参数，查询Query_Id的SQL语句的指定设备各占用的时间；

 c）全局查询日志：记录开启之后的全部SQL语句，不包含每句执行所用的时间，且仅在调优、开发过程中开启，最终部署实施时一定要关闭；
 show variables like '%general_log%'; ：查询general_log是否开启；

 --将执行的所有SQL记录在表中
 set global general_log = 1; ：开启全局日志，但只写这句没用，还要下面那句；
 set global log_output = 'table'; ：设置将全部的SQL记录在表中；
 开启后，会将所有SQL记录在mysql.general_log表中，执行：select * from mysql.general_log; 便可查到；

 --将执行的所有SQL记录在文件中
 set global general_log = 1;
 set global log_output = 'file';
 set global general_log_file = '/tmp/general.log';

锁机制

用途：解决因资源共享，而造成的并发问题。

一、锁分类
1）操作类型：
 a）读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰；
 b）写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他的读操作、写操作；

2）操作范围：
 a）表锁：一次性对一张表整体加锁，如MyISAM存储引擎，它使用表锁，优点：开销小、加锁快；缺点：锁的范围大，容易发生锁冲突，并发度低；
 b）行锁：一次性对一条数据加锁，如InnoDB存储引擎，它使用行锁，缺点：开销大，加锁慢；优点：锁的范围小，不易发生锁冲突，并发度高（发生高并发问题概率很小，eg：脏读，幻读，不可重复度，丢失更新等问题）
 c）页锁（用得少，不做详解）

因此建议，高并发使用InnoDB（默认行锁），否则使用MyISAM（默认表锁）。

二、表锁（MyISAM）
增加锁（添加单个）：lock table (表名) read/write;
增加锁（添加多个）：lock table (表名1) read/write, (表名2) read/write, ... ;

查看加了锁的表：show open tables;

注：下面说到的“会话”，表示为mysql.exe的dos命令行，或数据库客户端工具。

1）加读锁（可以用买衣服的例子来联想）

会话0：

lock table tablelock read ;  --加读锁
select * from tablelock; --读（查），可以
delete from tablelock where id =1 ; --写（增删改），不可以

select * from emp ; --读，不可以
delete from emp where eid = 1; --写，不可以

会话1（其他会话）：

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

select * from emp ;  --读（查），可以
delete from emp where eno = 1; --写，可以

结论1：如果在会话0中给表A加了读锁，则该会话只能对表A继续进行读（查）操作，而不能进行写（增删改）操作；且该会话不能对其他表进行读、写操作。

结论2：如果在会话0中给表A加了读锁，则其他会话可以对表A进行读操作，进行写操作需要等待会话0中表A释放读锁；且可以对其他表（除A以外的表）进行读、写操作；

unlock tables; --释放锁

2）加写锁

会话0：

lock table tablelock write ;  --加读锁
select * from tablelock; --读（查），可以
delete from tablelock where id =1 ; --写（增删改），可以

select * from emp ; --读，不可以
delete from emp where eid = 1; --写，不可以

会话1（其他会话）：

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

select * from emp ;  --读（查），可以
delete from emp where eno = 1; --写，可以

结论1：如果在会话0中给表A加了写锁，则该会话能对表A继续进行读（查）、写（增删改）操作；且该会话不能对其他表进行读、写操作。

结论2：如果在会话0中给表A加了读锁，则其他会话进行读、写操作需要等待会话0中表A释放读锁；且可以对其他表（除A以外的表）进行读、写操作；

3）MySQL表锁的模式说明
MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作（DML语句）前，会自动给涉及的表加写锁。

所以在MyISAM引擎下对表进行操作，会有以下情况：
 a）某一会话进行读操作时（加读锁），不会阻塞其他进程（会话）对同一表的读操作，但会阻塞其他进程对同一表的写操作；只有当读锁释放后，才会执行其他进程的写操作。
 b）某一会话进行写操作时（加写锁），会阻塞其他进程（会话）对同一表的读和写操作；只有当写锁释放后，才会执行其他进程的读写操作。

有关表锁情况的相关SQL语句：
 查询mysql中所有表是否被锁：show open tables; （1代表加了锁，0代表没加锁）
 查询表锁的严重程度：show status like 'table%';
  Table_locks_immediate :可能获取到的锁数； Table_locks_waited：需要等待的表锁数（若该值越大，则说明存在越大的锁竞争）；
 一般建议：Table_locks_immediate/Table_locks_waited > 5000，建议采用InnoDB引擎，否则采用MyISAM引擎。

三、行锁（InnoDB）
行锁在写操作时，会自动为这条数据加上写锁；而读操作时不会，需要在语句最后加for update。
从语句形式上、锁的范围上都与表锁相区别。

示例：

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关闭。
关闭自动commit方法：
 a）set autocommit = 0;
 b）start transaction;
 c）begin;
 待需要提交时，使用commit即可，若不想让语句生效，可使用rollback，即事务回滚到上一步。

1)对相同数据进行写操作
会话0： 写操作
 insert into linelock values('a6') ;

会话1： 写操作 同样的数据
 update linelock set name='ax' where id = 6; ——会话1需等待会话0中insert语句提交后，会话0中insert语句才会释放锁，会话1的update才能继续执行

注：此时，两会话对同一表的同一条数据进行写操作。

结论：如果会话0对某条数据进行写操作（DML）时，则其他会话必须等待会话0结束事务（commit/rollback）后，才能对这条数据进行写操作，但会话0和其他会话都可以进行读操作。

2)对不同数据进行写操作
会话0： 写操作
 insert into linelock values(8,'a8') ;

会话1： 写操作， 不同的数据
 update linelock set name='ax' where id = 5; ——会话1update语句可以运行，无需等待会话0释放锁

结论：如果会话0和会话1操作的不是同一条数据，则互不干扰，因为行锁一次锁一行数据。

3）对条件字段为非索引的不同数据进行写操作
注：name为非索引字段
会话0： 写操作
 update linelock set name = 'ai' where name = '3' ;

会话1： 写操作， 不同的数据
 update linelock set name = 'aiX' where name = '4' ; ——会话1需等待会话0中update语句提交后，会话0才会释放表锁，会话1的update才能继续执行

结论1：如果会话0和会话1操作的不是同一条数据，但where中条件字段为非索引字段，则会话0中行锁会转为表锁，需要将会话0中的表锁释放，即提交语句，才能执行会话1中语句。

结论2：如果会话0和会话1操作的不是同一条数据，且where中条件字段为索引字段，但因某些原因使索引失效（类型转换，参与计算等），行锁也会转为表锁。

4）间隙锁
这是行锁的一种特殊情况，即条件字段规定的范围内，缺少了某个值（该字段的值），则该行成为间隙，即该行被自动加上间隙锁（行锁）；

eg： 此时user表中，没有id=7的这行数据
update user set name ='x' where id >1 and id<9 ; ——即在此where范围中，没有id=7的数据，则id=7的数据成为间隙，其他会话对id=7进行写操作（即使没id=7这条数据，语句也能进行）时就需要等待该会话释放该间隙锁，即提交该语句。

总结：行锁实际范围：where后面的范围。

5）读操作
上面提到过，读操作时不会给行数据自动加锁，但在语句最后加for update，就会自动加锁了。

6）查询行锁详情的相关SQL语句
 查询行锁详情：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：总等待次数，即从系统启动到现在一共等待的次数；

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

示意图：

示例：
Windows：mysql，主计算机A
Linux：mysql，从计算机B

mysql安装教程：略
Navicat安装教程：略

一、配置权限

1）远程权限
A、B计算机中都执行：
 a）GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; ——授权可进行远程
 b）FLUSH PRIVILEGES; ——执行

2）关闭防火墙
 A计算机中，控制面板->查看网络状态和任务->Windows Defender防火墙，关闭防火墙；
 B计算机中，在mysql里，执行service iptables stop;

3）在A计算机中的可视化工具（Navicat/SQLyog）中进行远程连接B计算机的mysql，根据操作来，输入IP地址、用户名、密码等，完成主从的远程连接；

二、配置文件及bug处理
1）A计算机中
 a）在mysql根目录下的my.ini配置文件中：

 [mysqld]
 #id
 server-id=1 （对主机加唯一标识符）

 #二进制日志文件（注意是/ 不是\）
 log-bin=“D:/MySQL/MySQL Server 5.5/data/mysql-bin”

 #错误记录文件
 log-error=“D:/MySQL/MySQL Server 5.5/data/mysql-error”

 #主从同步时 忽略的数据库
 binlog-ignore-db=mysql

 #(可选)指定主从同步时，同步哪些数据库(只同步test数据库)
 binlog-do-db=test

 b）授权哪台计算机中的数据库，是自己的从数据库
  1.GRANT REPLICATION slave,reload,super ON *.* TO 'root'@'192.168.2.%' IDENTIFIED BY 'root'; ——'TO ‘root’‘代表能连接到目标计算机中的root用户，’%'代表任意数字均可，'BY ‘root’'代表本计算机的root用户
  2.flush privileges ;

 c）查询主数据库的状态并重启服务
 相关SQL语句：
 show master status; ——eg：结果中：mysql-bin.000001为二进制日志文件，107为position状态（每次主从同步前，都需要观察并记录主机状态的最新值，因为重启服务后position可能会变，并且它是用于主从同步的重要参数）
 重启主计算机中的mysql服务，并重新登录mysql。

2）B计算机中
 a）在my.cnf配置文件中：
 vi /etc/my.cnf

 [mysqld]
 server-id=2 (以后可能会出bug)

 log-bin=mysql-bin (相对路径，Windows里是绝对路径)

 replicate-do-db=test (要同步的数据库为test)
 ——其余的可配可不配

 b）授权哪台计算机是自己的主计算机：

	CHANGE MASTER TO 
	MASTER_HOST = '192.168.2.2', 	--主计算机IP
	MASTER_USER = 'root', 
	MASTER_PASSWORD = 'root', 
	MASTER_PORT = 3306,
	master_log_file='mysql-bin.000001',	--主计算机二进制日志文件
	master_log_pos=107;		--主计算机的position

 如果报错：This operation cannot be performed with a running slave; run STOP SLAVE first
 解决：STOP SLAVE ;，表示停止主从同步，再次执行上条授权语句
 原因：之前做过主从同步

 c）开启主从同步（在从机中）：
 start slave;
 检验是否成功开启：show slave status \G;
 主要观察：Slave_IO_Running和 Slave_SQL_Running，确保二者都是yes；
 如果不都是yes，则看下方的Last_IO_Error；

 eg：通过 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版本不一致造成的兼容性问题。
 解决：通过修改全局变量来修改server-id：set global server_id =2 ;，注意此处是下划线_，而不是-，配置文件里才是-；

 具体步骤：

	stop slave ;
	set global server_id =2 ;
	start slave ;
	show slave status \G;	--二者均为yes，则开启成功

三、实战
在主计算机中执行任意写操作，然后去从计算机查询数据是否跟着变化，是则主从同步成功，否则失败，具体略。

（完）

参考链接：https://www.bilibili.com/video/av29072634?p=5