sql优化

 

---

1.mysql  版本

　　5.x:
 5.0-5.1:早期产品的延续，升级维护
 5.4 - 5.x :  MySQL整合了三方公司的新存储引擎 （推荐5.5）

 启动mysql应用： service mysql start
 关闭： service mysql stop
 重启： service mysql restart

 给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

---

2.原理

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

　　innodb(默认) ：  事务优先  行锁 高并发

　　myisam：性能优先  表锁

查询数据库引擎？show engines;  show engines \g;  

指定数据库引擎：在创建表后面加上：ENGINE=MyISAM;

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   ;

---

3. sql优化

原因：性能低，连接查询，索引失效，服务器参数

1.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 ...

2.sql优化主要就是优化索引

索引：相当于书的目录。index:帮助高效获取数据的数据结构。树：B树(小的放左，大的放右)（Mysql）

 索引弊端：

1.索引本身很大

2.索引不适用：少量数据/频繁更改/很少使用的字段

3.降低增删改的效率

 索引优势：

1.提高查询效率（降低io使用率）

2.降低cpu使用率（B树索引是排好序的结构，在需要排序时可以直接使用）

 

三层B树可以放上百万条数据

Btree:一般指b+树，也就是所有数据存放在叶子节点中。

索引分类

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

单值索引：单列 一个表可以有多个单值索引

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

复合索引：多个列构成的索引（相当于二级目录）

 创建索引

create 索引类型 索引名 on 表（字段）

方式1：

单值索引：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 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,则该字段默认就是主键索引

3.删除  drop index 索引名 on  表名

4.查询索引  show index from tb;

---

4.sql性能问题

a. 分析sql执行计划：explain 可以模拟sql优化器执行sql语句，从而知道sql状况

explain+sql语句  explain select * from tb;

b.mysql查询优化会干扰我们的优化

相关参数：

　　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  的老师信息：

explain +sql:

（1）id：id值相同，从上往下执行。 t3-tc3-c4

表的执行顺序，因数量的个数改变而改变：笛卡尔积。结果相同，但是中间结果不同，数据小的表先查询

id：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') ;

（2）select type：查询类型

primary:子查询sql中的主查询（最外层）

subquery：子查询（非最外层）

simple：简单查询（不包含子查询，union）select * from table

derived：衍生查询（使用时用到了临时表,例如cr）

　　a.在from子查询中只有一张表 ： explain select  cr.cname  from ( select * from course where tid in (1,2) ) cr ;

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

 

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

（3）type:索引类型

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

其中：system,const只是理想情况；实际能达到 ref>range

案例：

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 ;

 

eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）

　　select ... from ..where name = ... .常见于唯一索引 和主键索引。

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

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

 

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

 

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

 

index：查询索引中全部数据
explain select tid from teacher ; --tid 是索引， 只需要扫描索引表，不需要所有表中的所有数据

 

all：查询全部表中的数据
explain select cid from course ;  --cid不是索引，需要全表所有，即需要所有表中的所有数据

 

（4）possible_keys:可能用到的索引，是一种预测，并不准

（5）key :实际使用到的索引

（6）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 ='' ;   -- 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 ='' ;
--如果索引字段可以为Null,则会使用1个字节用于标识。

drop index index_name on test_kl ;
drop index index_name1 on test_kl ;

 

(7) ref : 注意与type中的ref值区分

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

 

(8)rows: 被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)
 explain select * from course c,teacher t  where c.tid = t.tid  and t.tname = 'tz' ;

（9）Extra：

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

　　　　

10个人 根据年龄排序。

 
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 ;

a1:姓名  a2：年龄

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

对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；
 避免： where哪些字段，就order by那些字段2

 

复合索引：不能跨列（最佳左前缀）
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
explain select *from test02 where a2='' order by a3 ; --using filesort
explain select *from test02 where a1='' order by a2 ;
explain select *from test02 where a2='' order by a1 ; --using filesort
 小结

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

　　　　  2.  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 .

 

　　　　　3. using index: :性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）

例如：test02表中有一个复合索引(a1,a2,a3)
  explain select a1,a2 from test02 where a1='' or a2= '' ; --using index   
  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= '' ;

 

　　　　4.using  where(需要回表查询)

  假设age是索引列
  但查询语句select age,name from ...where age =...,此语句中必须回原表查Name，因此会显示using where.
    explain select a1,a3 from test02 where a3 = '' ; --a3需要回原表查询

 

     　　5.impossible where:where 子句永远为false

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

 

5.优化案例

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

（1）单表优化

 

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的：

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实际解析的顺序，调整索引的顺序：
 alter table book add index idx_tab (typeid,authorid,bid); --虽然可以回表查询bid，但是将bid放到索引中 可以提升使用using index ;

 

 再次优化（之前是index级别）

思路：因为范围查询in有时会实现，因此交换 索引的顺序，将typeid in(2,3) 放到最后。

 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 ;

 

小结：

 a.最佳做前缀，保持索引的定义和使用的顺序一致性 

b.索引需要逐步优化 

c.将含In的范围查询 放到where条件的最后，防止失效。

 

（2）双表优化

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

 

索引往哪张表加？  

-小表驱动大表 
-索引建立经常使用的字段上 （本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引） [一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]
 小表：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++)
  {
   ...
  }
 }
 
--以上2个FOR循环，最终都会循环3000次；但是 对于双层循环来说：一般建议 将数据小的循环 放外层；数据大的循环放内层。

 

（3）三张表优化A B C
 a.小表驱动大表 

b.索引建立在经常查询的字段上

示例：
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_4(a1,a2,a3,a4) ;

----explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4 ; --推荐写法，因为 索引的使用顺序（where后面的顺序） 和 复合索引的顺序一致

----explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4 ; --虽然编写的顺序 和索引顺序不一致，但是 sql在真正执行前 经过了SQL优化器的调整，结果与上条SQL是一致的。
 --以上 2个SQL，使用了 全部的复合索引

 explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;
 --以上SQL用到了a1 a2两个索引，该两个字段 不需要回表查询using index ;而a4因为跨列使用，造成了该索引失效，需要回表查询 因此是using where；以上可以通过 key_len进行验证

 explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3;
 --以上SQL出现了 using filesort(文件内排序，“多了一次额外的查找/排序”) ：不要跨列使用( where和order by 拼起来，不要跨列使用)

 explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2 , a3; --不会using filesort

 
 --总结：

i.如果 (a,b,c,d)复合索引  和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用。如果部分一致(且不跨列使用)，则使用部分索引。
 select a,c where  a = and b= and d=
  ii.where和order by 拼起来，不要跨列使用

 

using temporary:需要额外再多使用一张表. 一般出现在group by语句中；已经有表了，但不适用，必须再来一张表。
解析过程：   
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...
 a.
  explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;--没有using temporary
 b.
  explain select * from test03 where a2=2 and a4=4 group by a3 ;

 

（6）避免索引失效的原则

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

 

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

  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同时失效。

 

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

注意：-- SQL优化，是一种概率层面的优化，原因是服务层中有SQL优化器，可能会影响我们的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。

 

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

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

　　

　　5) 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%';
 　　 explain select tname from teacher  where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。

 　　

　　

　　6）尽量不要使用类型转换（显示、隐式），否则索引失效
 　　explain select * from teacher where tname = 'abc' ;
 　　explain select * from teacher where tname = 123 ;//程序底层将 123 -> '123'，即进行了类型转换，因此索引失效

 

 　　7）尽量不要使用or，否则索引失效
 explain select * from teacher where tname ='' or tcid >1 ; --将or左侧的tname 失效。

 

（7）一些其他的优化方法

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

 2）order by 优化
 using filesort 有两种算法：双路排序、单路排序 （根据IO的次数）
 MySQL4.1之前 默认使用 双路排序；双路：扫描2次磁盘（1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序）   2：扫描其他字段 ）
  --IO较消耗性能
 MySQL4.1之后 默认使用 单路排序  ： 只读取一次（全部字段），在buffer中进行排序。但种单路排序 会有一定的隐患 （不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行“分片读取、多次读取”。
  注意：单路排序 比双路排序 会占用更多的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.保证全部的排序字段 排序的一致性（都是升序 或 降序）

 

（8）SQL排查

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

慢查询日志默认是关闭的；建议：开发调优是 打开，而 最终部署时关闭。

检查是否开启了 慢查询日志 ：   show variables like '%slow_query_log%' ;

临时开启：
  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%' ;

(1)慢查询的sql被记录在了日志中，因此可以通过日志 查看具体的慢SQL。
 cat /var/lib/mysql/localhost-slow.log

 

 (2)通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件 快速查找出需要定位的慢SQL
 mysqldumpslow --help
 s：排序方式
 r:逆序
 l:锁定时间
 g:正则匹配模式  

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

 

（9）分析海量数据

a.模拟海量数据  存储过程（无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;
 通过存储函数 插入海量数据
 创建存储函数：
 1------ 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 $

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

 end $
 

 --通过存储过程插入海量数据：emp表中  ，  10000,   100000
 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) ;

 
 b.分析海量数据:
 （1）profiles
 show profiles ; --默认关闭
 show variables like '%profiling%';
 set profiling = on ;
 show profiles  ：会记录所有profiling打开之后的  全部SQL查询语句所花费的时间。缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu  io ）

 (2)--精确分析:sql诊断
  show profile all for query 上一步查询的的Query_Id
  show profile cpu,block io for query 上一步查询的的Query_Id

 (3)全局查询日志 ：记录开启之后的 全部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 ;

 

（10）锁机制  因资源共享而造成的并发问题

分类

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

 

 操作范围：
  a.表锁 ：一次性对一张表整体加锁。如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。
  b.行锁 ：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率 发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。
  c.页锁 

 

示例：

 （1）表锁 ：  --自增操作 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将锁释放

  会话1（其他会话）：
   select * from emp ;  --读（查），可以
   delete from emp where eno = 1; --写，可以
   结论2：
   --总结：
    会话0给A表加了锁；其他会话的操作：

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

 

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

 ===表锁分析

查看哪些加了表锁：show  open tables;  1代表加了锁

分析表锁定的严重程度：  show status like  'table%' ;

table_locks_immediate  :可能获取到的锁数

table_locks_waited:  需要等待的表锁数  （可能对同一个东西加了多个锁）

table_locks_immediate/table_locks_waited  >  5000，建议采用innodb引擎（行锁），否则myisam 。

---

===行锁分析

mysql默认自动commit,  为了研究行锁，需要关闭自动提交，进行加锁，set autocommit=0

模拟两个会话：

会话0：  写操作

会话1：  写操作  同样的数据-> 锁定无法修改，直到其他会话将该锁释放。

行锁： commit;  或者  rollback; （这样就释放了）

总结

1.如果会话x对某条数据操作（关闭了commit的情况下），则其他会话必须等待；

2.表锁解锁方式：unlock  tables; 也可以通过事务提交；     行锁解锁方式：  通过事务来解锁。

注意事项

1.如果没有索引，则行锁会转成表锁。

show index from linelock;

alter table linelock add  index idx_linelock_name(name);

会话0：  写操作

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

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

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

如果：

会话0：  写操作

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

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

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

----数据此时阻塞了（加锁）

---原因：如果索引列发生了类型转换，则索引失效；索引失效后，行锁会转成表锁，所以此时即使更改不同行，也会发生阻塞。

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

---此时linelock表中， 没有id=7的数据

update linelock set name ='x'  where id<1and id<9  ; 在此范围中，  没有id=7的数据，则 id=7的数据称为间隙。

间隙的特点：  mysql会自动给间隙 加锁  -》 间隙锁

所以：如果有where , 则实际加锁的范围  就是where 后面的范围， 而不是实际的值。

总结

innodb默认使用行锁

缺点：比表锁性能损耗大    优点：并发能力强，效率高

行锁分析：show status like '%innodb_row_lock%';   有锁定时间，等待时间，等待次数等

问题：如果仅仅查询数据，如何加锁？

　　将自动提交关闭：1.set commit=0;   2.start  transaction;  3. begin;

　　select * from linlock where id=2  for  update;

---

(11)主从复制  主数据库进行写操作，从数据库读操作，读写分离

1.负载均衡

2.失败迁移

 主从同步原理

核心：二进制日志

过程

 1.master将改变的数记录在本地的二进制日志中（binary_log），该过程称为二进制日志事件。

 2. slave 将master 的二进制日志拷贝到自己的relay_log(中继日志文件中)。

 3.中继日志事件将数据读取到自己的数据库之中

 mysql主从复制默认是异步的，串行的，有延迟

master:slave=1:n

配置：
 windows(mysql: my.ini)
   linux(mysql: my.cnf)

 

主机（以下代码和操作 全部在主机windows中操作）：
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
#(可选)指定主从同步时，同步哪些数据库
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）

从机（以下代码和操作 全部在从机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

 演示：
 主windows =>从

 windows:
 将表，插入数据 
 观察从数据库中该表的数据

 

 

 

 

  

 

 

 

 

 

---

 

 

 

　　　　　　　

 

-sql查询优化会干扰

 

 

 

 

 

 

 

 

 

什么函数不能构成虚函数

spring的生命周期

aop的实现方式

countdownlatch

hibernate与mybatis区别，说了下全映射与半映射，mybatis将sql语句解析成sqlnode

2、spring与springboot关系，说了下springboot如何自动加载

3、了解网络通信？如何设计？讲了下netty的设计思路吧，说到reactor三种线程模型打住了

给一个任意数组，偶数放在数组左边，奇数放在数组右边，相对次序不变，怎么实现？更好的方法呢
• 给两个集合，找到这两个集合中的相同元素，如何实现？更好的方法呢

• 如果存放1亿条数据，怎么处理

 

 

rabbitmq消息中间件聊了一下，自己对幂等性理解存在误区吧，

转载于:https://www.cnblogs.com/NeverGiveUp0/p/11142257.html