哔哩哔哩视频链接：https://www.bilibili.com/vide…
笔记是从黑马程序员那里获取的~

Mysql 高级 -day01

MySQL 高级课程简介

1. Linux 零碎装置 MySQL

1.1 下载 Linux 安装包

https://dev.mysql.com/downloads/mysql/5.7.html#downloads

1.2 装置 MySQL

1). 卸载 centos 中预装置的 mysql
    rpm -qa | grep -i mysql
    rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps
2). 上传 mysql 的安装包
    alt + p -------> put  E:/test/MySQL-5.6.22-1.el6.i686.rpm-bundle.tar
3). 解压 mysql 的安装包 
    mkdir mysql
    tar -xvf MySQL-5.6.22-1.el6.i686.rpm-bundle.tar -C /root/mysql
4). 装置依赖包 
    yum -y install libaio.so.1 libgcc_s.so.1 libstdc++.so.6 libncurses.so.5 --setopt=protected_multilib=false   // 在 centos7 上装置失败，yum  update libstdc++-4.4.7-4.el6.x86_64
5). 装置 mysql-client
    rpm -ivh MySQL-client-5.6.22-1.el6.i686.rpm
6). 装置 mysql-server
    rpm -ivh MySQL-server-5.6.22-1.el6.i686.rpm    

1.3 启动 MySQL 服务

service mysql start
service mysql stop
service mysql status
service mysql restart


#这里应用来开启
systemctl start mysqld

1.4 登录 MySQL

mysql 装置实现之后, 会主动生成一个随机的明码, 并且保留在一个密码文件中 : /root/.mysql_secret
mysql -u root -p 
登录之后, 批改明码 :
set password = password('itcast');
受权近程拜访 : 
grant all privileges on *.* to 'root' @'%' identified by 'itcast';
flush privileges;

#敞开防火墙
service iptables status;

以上步骤在 1.2 装置依赖包时出错，参考上面链接在 centos7 上装置 MySQL5.7

https://blog.csdn.net/jeikerx… 明码：P@ssw0rd

1.5 应用 MySQL 呈现的问题

首先就是 systemctl start mysqld 呈现问题，怎么定位问题呢？

# 输出以下指令查看日志，根本就能够定位到哪里有问题了
cat /var/log/mysqld.log

#有可能是端口被占用的问题，应该先查看 mysql 的端口
netstat -nltp | grep 3306    或    ps aux|grep mysql
kill -9 端口号

#第一次登录的适宜可能会呈现初始密码不对的状况，生成默认明码
grep 'temporary password' /var/log/mysqld.log

#如果还是输出不对，那么就须要先不要明码登录了，进入配置文件
vim /etc/my.cnf

#而后进行批改，轻易加在哪里都行
skip-grant-tables

#重启 mysql，而后就能登录了
systemctl restart mysqld

#登录过后要应用以下指令更新
update user set authentication_string='123456' where user='root'@'localhost';

2. 索引

2.1 索引概述

MySQL 官网对索引的定义为：索引（index）是帮忙 MySQL 高效获取数据的数据结构（有序）。索引是在数据库表的字段上增加的，是为了进步查问效率存在的一种机制。在数据之外，数据库系统还保护着满足特定查找算法的数据结构，这些数据结构以某种形式援用（指向）数据，这样就能够在这些数据结构上实现高级查找算法，这种数据结构就是索引。如上面的 示意图 所示 :

右边是数据表，一共有两列七条记录，最右边的是数据记录的物理地址（留神逻辑上相邻的记录在磁盘上也并不是肯定物理相邻的）。为了放慢 Col2 的查找，能够保护一个左边所示的二叉查找树，每个节点别离蕴含索引键值和一个指向对应数据记录物理地址的指针，这样就能够使用二叉查找疾速获取到相应数据。

一般来说索引自身也很大，不可能全副存储在内存中，因而索引往往以索引文件的模式存储在磁盘上。索引是数据库中用来进步性能的最罕用的工具。

2.2 索引劣势劣势

劣势

1）相似于书籍的目录索引，进步数据检索的效率，升高数据库的 IO 老本。

2）通过索引列对数据进行排序，升高数据排序的老本，升高 CPU 的耗费。

劣势

1）实际上索引也是一张表，该表中保留了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。

2）尽管索引大大提高了查问效率，同时却也升高更新表的速度，如对表进行 INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保留数据，还要保留一下索引文件。每次更新增加了索引列的字段，都会调整因为更新所带来的键值变动后的索引信息。

2.3 索引构造

索引是在 MySQL 的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不肯定完全相同，也不是所有的存储引擎都反对所有的索引类型的。MySQL 目前提供了以下 4 种索引：

• BTREE 索引：最常见的索引类型，大部分索引都反对 B 树索引。
• HASH 索引：只有 Memory 引擎反对，应用场景简略。
• R-tree 索引（空间索引）：空间索引是 MyISAM 引擎的一个非凡索引类型，次要用于天文空间数据类型，通常应用较少，不做特地介绍。
• Full-text（全文索引）：全文索引也是 MyISAM 的一个非凡索引类型，次要用于全文索引，InnoDB 从 Mysql5.6 版本开始反对全文索引。

MyISAM、InnoDB、Memory 三种存储引擎对各种索引类型的反对

咱们平时所说的索引，如果没有特地指明，都是指 B + 树（多路搜寻树，并不一定是二叉的）构造组织的索引。其中汇集索引、复合索引、前缀索引、惟一索引默认都是应用 B+tree 索引，统称为 索引。

2.3.1 BTREE 构造

BTree 又叫多路均衡搜寻树，一颗 m 叉的 BTree 个性如下：

• 树中每个节点最多蕴含 m 个孩子。
• 除根节点与叶子节点外，每个节点至多有 [ceil(m/2)] 个孩子。
• 若根节点不是叶子节点，则至多有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由 n 个 key 与 n + 1 个指针组成，其中[ceil(m/2)-1] <= n <= m-1

以 5 叉 BTree 为例，key 的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4。当 n >4 时，两头节点决裂到父节点，两边节点决裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

演变过程如下：

1). 插入前 4 个字母 C N G A

2). 插入 H，n>4，两头元素 G 字母向上决裂到新的节点

3). 插入 E，K，Q 不须要决裂

4). 插入 M，两头元素 M 字母向上决裂到父节点 G

5). 插入 F，W，L，T 不须要决裂

6). 插入 Z，两头元素 T 向上决裂到父节点中

7). 插入 D，两头元素 D 向上决裂到父节点中。而后插入 P，R，X，Y 不须要决裂

8). 最初插入 S，NPQR 节点 n >5，两头节点 Q 向上决裂，但决裂后父节点 DGMT 的 n >5，两头节点 M 向上决裂

到此，该 BTREE 树就曾经构建实现了，BTREE 树 和 二叉树 相比，查问数据的效率更高，因为对于雷同的数据量来说，BTREE 的层级构造比二叉树小，因而搜寻速度快。

2.3.3 B+TREE 构造

B+Tree 为 BTree 的变种，B+Tree 与 BTree 的区别为：

1). n 叉 B +Tree 最多含有 n 个 key，而 BTree 最多含有 n - 1 个 key。

2). B+Tree 的叶子节点保留所有的 key 信息，依 key 大小顺序排列。

3). 所有的非叶子节点都能够看作是 key 的索引局部。

因为 B +Tree 只有叶子节点保留 key 信息，查问任何 key 都要从 root 走到叶子。所以 B +Tree 的查问效率更加稳固。

2.3.3 MySQL 中的 B +Tree

MySql 索引数据结构对经典的 B +Tree 进行了优化。在原 B +Tree 的根底上，减少一个指向相邻叶子节点的链表指针，就造成了带有程序指针的 B +Tree，进步区间拜访的性能。

MySQL 中的 B+Tree 索引构造示意图:

2.4 索引分类

1）单值索引：即一个索引只蕴含单个列，一个表能够有多个单列索引

2）惟一索引：索引列的值必须惟一，但容许有空值

3）复合索引：即一个索引蕴含多个列

2.5 索引语法

索引在创立表的时候，能够同时创立，也能够随时减少新的索引。

筹备环境:

create database demo_01 default charset=utf8mb4;

use demo_01;

CREATE TABLE `city` (`city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (`country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

2.5.1 创立索引

语法：

CREATE     [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

示例：为 city 表中的 city_name 字段创立索引；

2.5.2 查看索引

语法：

show index  from  table_name;

示例：查看 city 表中的索引信息；

2.5.3 删除索引

语法：

DROP  INDEX  index_name  ON  tbl_name;

示例：想要删除 city 表上的索引 idx_city_name，能够操作如下：

2.5.4 ALTER 命令

1). alter  table  tb_name  add  primary  key(column_list); 
    该语句增加一个主键，这意味着索引值必须是惟一的，且不能为 NULL    
    
2). alter  table  tb_name  add  unique index_name(column_list);
    这条语句创立索引的值必须是惟一的（除了 NULL 外，NULL 可能会呈现屡次）3). alter  table  tb_name  add  index index_name(column_list);
    增加一般索引，索引值能够呈现屡次。4). alter  table  tb_name  add  fulltext  index_name(column_list);
    该语句指定了索引为 FULLTEXT，用于全文索引

2.6 索引设计准则

索引的设计能够遵循一些已有的准则，创立索引的时候请尽量思考合乎这些准则，便于晋升索引的应用效率，更高效的应用索引。

• 对查问频次较高，且数据量比拟大的表建设索引。
• 索引字段的抉择，最佳候选列该当从 where 子句的条件中提取，如果 where 子句中的组合比拟多，那么该当筛选最罕用、过滤成果最好的列的组合。
• 应用惟一索引，区分度越高，应用索引的效率越高。
• 索引能够无效的晋升查问数据的效率，但索引数量不是多多益善，索引越多，保护索引的代价天然也就水涨船高 。对于插入、更新、删除等 DML 操作比拟频繁的表来说，索引过多，会引入相当高的保护代价，升高 DML 操作的效率，减少相应操作的工夫耗费。 另外索引过多的话，MySQL 也会犯抉择艰难病，尽管最终依然会找到一个可用的索引，但无疑进步了抉择的代价。
• 应用短索引，索引创立之后也是应用硬盘来存储的，因而晋升索引拜访的 I / O 效率，也能够晋升总体的拜访效率。如果形成索引的字段总长度比拟短，那么在给定大小的存储块内能够存储更多的索引值，相应的能够无效的晋升 MySQL 拜访索引的 I / O 效率。

• 利用最左前缀，N 个列组合而成的组合索引，那么相当于是创立了 N 个索引，如果查问时 where 子句中应用了组成该索引的前几个字段，那么这条查问 SQL 能够利用组合索引来晋升查问效率。

  创立复合索引:
      CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
  
  就相当于
      对 name 创立索引 ;
      对 name , email 创立了索引 ;
      对 name , email, status 创立了索引 ;

3. 视图

3.1 视图概述

视图（View）是一种虚构存在的表 。视图并不在数据库中理论存在，行和列数据来自定义视图的查问中应用的表，并且是在应用视图时动静生成的。艰深的讲， 视图就是一条 SELECT 语句执行后返回的后果集。所以咱们在创立视图的时候，次要的工作就落在创立这条 SQL 查问语句上。

视图绝对于一般的表的劣势次要包含以下几项。

• 简略：应用视图的用户齐全不须要关怀前面对应的表的构造、关联条件和筛选条件，对用户来说曾经是过滤好的复合条件的后果集。
• 平安：应用视图的用户只能拜访他们被容许查问的后果集，对表的权限治理并不能限度到某个行某个列，然而通过视图就能够简略的实现。
• 数据独立：一旦视图的构造确定了，能够屏蔽表构造变动对用户的影响，源表减少列对视图没有影响；源表批改列名，则能够通过批改视图来解决，不会造成对访问者的影响。

3.2 创立或者批改视图

创立视图的语法为：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

批改视图的语法为：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]
选项 : 
    WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否容许更新数据使记录不再满足视图的条件。LOCAL：只有满足本视图的条件就能够更新。CASCADED：必须满足所有针对该视图的所有视图的条件才能够更新。默认值.

示例 , 创立 city_country_view 视图 , 执行如下 SQL :

create or replace view city_country_view 
as 
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

查问视图 :

3.3 查看视图

从 MySQL 5.1 版本开始，应用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在独自显示视图的 SHOW VIEWS 命令。

同样，在应用 SHOW TABLE STATUS 命令的时候，岂但能够显示表的信息，同时也能够显示视图的信息。

如果须要查问某个视图的定义，能够应用 SHOW CREATE VIEW 命令进行查看：

3.4 删除视图

语法 :

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]    

示例 , 删除视图 city_country_view :

DROP VIEW city_country_view ;

4. 存储过程和函数

4.1 存储过程和函数概述

存储过程和函数是当时通过编译并存储在数据库中的一段 SQL 语句的汇合，调用存储过程和函数能够简化利用开发人员的很多工作，缩小数据在数据库和应用服务器之间的传输，对于进步数据处理的效率是有益处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

函数：是一个有返回值的过程；

过程：是一个没有返回值的函数；

4.2 创立存储过程

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
    -- SQL 语句
end ;

示例：

delimiter $

create procedure pro_test1()
begin
    select 'Hello Mysql' ;
end$

delimiter ;

常识小贴士

DELIMITER

该关键字用来申明 SQL 语句的分隔符 , 通知 MySQL 解释器，该段命令是否曾经完结了，mysql 是否能够执行了。默认状况下，delimiter 是分号;。在命令行客户端中，如果有一行命令以分号完结，那么回车后，mysql 将会执行该命令。

4.3 调用存储过程

call procedure_name() ;    

4.4 查看存储过程

-- 查问 db_name 数据库中的所有的存储过程
select name from mysql.proc where db='db_name';

-- 查问存储过程的状态信息
show procedure status;

-- 查问某个存储过程的定义
show create procedure test.pro_test1 \G;

4.5 删除存储过程

DROP PROCEDURE  [IF EXISTS] sp_name；

4.6 语法

存储过程是能够编程的，意味着能够应用变量，表达式，控制结构，来实现比较复杂的性能。

4.6.1 变量

• DECLARE

通过 DECLARE 能够定义一个局部变量，该变量的作用范畴只能在 BEGIN…END 块中。

DECLARE var_name[,...] type [DEFAULT value]

示例 :

 delimiter $

 create procedure pro_test2() 
 begin 
     declare num int default 5;
     select num+ 10; 
 end$

 delimiter ; 

• SET

间接赋值应用 SET，能够赋常量或者赋表达式，具体语法如下：

  SET var_name = expr [, var_name = expr] ...

示例 :

  DELIMITER $
  
  CREATE  PROCEDURE pro_test3()
  BEGIN
      DECLARE NAME VARCHAR(20);
      SET NAME = 'MYSQL';
      SELECT NAME ;
  END$
  
  DELIMITER ;

也能够通过 select … into 形式进行赋值操作 :

DELIMITER $

CREATE  PROCEDURE pro_test5()
BEGIN
    declare  countnum int;
    select count(*) into countnum from city;
    select countnum;
END$

DELIMITER ;

4.6.2 if 条件判断

语法结构 :

if search_condition then statement_list

    [elseif search_condition then statement_list] ...
    
    [else statement_list]
    
end if;

需要：

依据定义的身高变量，断定以后身高的所属的身材类型 

    180 及以上 ----------> 身材高挑

    170 - 180  ---------> 规范身材

    170 以下  ----------> 个别身材

示例 :

delimiter $

create procedure pro_test6()
begin
  declare  height  int  default  175; 
  declare  description  varchar(50);
  
  if  height >= 180  then
    set description = '身材高挑';
  elseif height >= 170 and height < 180  then
    set description = '规范身材';
  else
    set description = '个别身材';
  end if;
  
  select description ;
end$

delimiter ;

调用后果为 :

4.6.3 传递参数

语法格局 :

create procedure procedure_name([in/out/inout] 参数名   参数类型)
...

IN :   该参数能够作为输出，也就是须要调用方传入值 , 默认
OUT:   该参数作为输入，也就是该参数能够作为返回值
INOUT: 既能够作为输出参数，也能够作为输入参数

IN – 输出

需要 :

依据定义的身高变量，断定以后身高的所属的身材类型 

示例 :

delimiter $

create procedure pro_test5(in height int)
begin
    declare description varchar(50) default '';
  if height >= 180 then
    set description='身材高挑';
  elseif height >= 170 and height < 180 then
    set description='规范身材';
  else
    set description='个别身材';
  end if;
  select concat('身高', height , '对应的身材类型为:',description);
end$

delimiter ;

OUT- 输入

需要 :

依据传入的身高变量，获取以后身高的所属的身材类型  

示例:

create procedure pro_test5(in height int , out description varchar(100))
begin
  if height >= 180 then
    set description='身材高挑';
  elseif height >= 170 and height < 180 then
    set description='规范身材';
  else
    set description='个别身材';
  end if;
end$     

调用:

call pro_test5(168, @description)$

select @description$

小常识

@description : 这种变量要在变量名称后面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个相似于全局变量一样。

@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 零碎变量

4.6.4 case 构造

语法结构 :

形式一 : 

CASE case_value

  WHEN when_value THEN statement_list
  
  [WHEN when_value THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;


形式二 : 

CASE

  WHEN search_condition THEN statement_list
  
  [WHEN search_condition THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;

需要:

给定一个月份, 而后计算出所在的季度

示例 :

delimiter $


create procedure pro_test9(month int)
begin
  declare result varchar(20);
  case 
    when month >= 1 and month <=3 then 
      set result = '第一季度';
    when month >= 4 and month <=6 then 
      set result = '第二季度';
    when month >= 7 and month <=9 then 
      set result = '第三季度';
    when month >= 10 and month <=12 then 
      set result = '第四季度';
  end case;
  
  select concat('您输出的月份为 :', month , ', 该月份为 :' , result) as content ;
  
end$


delimiter ;

4.6.5 while 循环

语法结构:

while search_condition do

    statement_list
    
end while;

需要:

计算从 1 加到 n 的值

示例 :

delimiter $

create procedure pro_test8(n int)
begin
  declare total int default 0;
  declare num int default 1;
  while num<=n do
    set total = total + num;
    set num = num + 1;
  end while;
  select total;
end$

delimiter ;

4.6.6 repeat 构造

有条件的循环管制语句, 当满足条件的时候退出循环。while 是满足条件才执行，repeat 是满足条件就退出循环。

留神：UNTIL search_condition 完结条件没有分号

语法结构 :

REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

需要:

计算从 1 加到 n 的值

示例 :

delimiter $

create procedure pro_test10(n int)
begin
  declare total int default 0;
  
  repeat 
    set total = total + n;
    set n = n - 1;
    until n=0     // 留神：完结条件没有分号
  end repeat;
  
  select total ;
  
end$


delimiter ;

4.6.7 loop 语句

LOOP 实现简略的循环，退出循环的条件须要应用其余的语句定义，通常能够应用 LEAVE 语句实现，具体语法如下：

[begin_label:] LOOP   // 给 LOOP 循环起别名

  statement_list

END LOOP [end_label]

如果不在 statement_list 中减少退出循环的语句，那么 LOOP 语句能够用来实现简略的死循环。

4.6.8 leave 语句

用来从标注的流程结构中退出，通常和 BEGIN … END 或者循环一起应用。上面是一个应用 LOOP 和 LEAVE 的简略例子 , 退出循环：

delimiter $

CREATE PROCEDURE pro_test11(n int)
BEGIN
  declare total int default 0;
  
  ins: LOOP
    
    IF n <= 0 then
      leave ins;
    END IF;
    
    set total = total + n;
    set n = n - 1;
  
  END LOOP ins;
  
  select total;
END$

delimiter ;

4.6.9 游标 / 光标

游标是用来存储查问后果集的数据类型 , 在存储过程和函数中能够应用光标对后果集进行循环的解决。光标的应用包含光标的申明、OPEN、FETCH 和 CLOSE，其语法别离如下。

申明光标：

DECLARE cursor_name CURSOR FOR select_statement ;  // 申明游标，封装 select 语句查问的后果

OPEN 光标：

OPEN cursor_name ;   // 关上游标，才能够进行迭代

FETCH 光标：

FETCH cursor_name INTO var_name [, var_name] ...  // 迭代游标，调用一次，读取游标中一行数据

CLOSE 光标：

CLOSE cursor_name ;   // 敞开游标

示例 :

初始化脚本:

create table emp(id int(11) not null auto_increment ,
  name varchar(50) not null comment '姓名',
  age int(11) comment '年龄',
  salary int(11) comment '薪水',
  primary key(`id`)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

-- 查问 emp 表中数据, 并逐行获取进行展现
create procedure pro_test11()
begin
  declare e_id int(11);
  declare e_name varchar(50);
  declare e_age int(11);
  declare e_salary int(11);
  declare emp_result cursor for select * from emp;  // 以后游标外面封装 select 查问的后果
  
  open emp_result;  // 开始游标
  
  fetch emp_result into e_id,e_name,e_age,e_salary;  // 获取游标的数据，并赋值给变量
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:',e_salary);
  
  close emp_result;  // 敞开游标
end$

通过循环构造 , 获取游标中的数据 :

DELIMITER $

create procedure pro_test12()
begin
  DECLARE id int(11);
  DECLARE name varchar(50);
  DECLARE age int(11);
  DECLARE salary int(11);
  DECLARE has_data int default 1;
  
  DECLARE emp_result CURSOR FOR select * from emp;
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;  // 拿不到数据会触发句柄，意思是抓取不到数据时，将 has_data 设置为 0，该句申明必须在申明游标语句之后
  
  open emp_result;
  
  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id 为',id, ', name 为' ,name , ', age 为' ,age , ', 薪水为:', salary);
    until has_data = 0
  end repeat;
  
  close emp_result;
end$

DELIMITER ; 

4.7 存储函数

语法结构:

CREATE FUNCTION function_name([param type ...]) 
RETURNS type 
BEGIN
    ...
END;

案例 :

定义一个存储函数, 申请满足条件的总记录数 ;

delimiter $

create function count_city(countryId int)
returns int
begin
  declare cnum int ;
  
  select count(*) into cnum from city where country_id = countryId;
  
  return cnum;
end$

delimiter ;

调用:

select count_city(1);

select count_city(2);

5. 触发器

5.1 介绍

触发器是与表无关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的 SQL 语句汇合。触发器的这种个性能够帮助利用在数据库端 确保数据的完整性 , 日志记录 , 数据校验 等操作。

应用别名 OLD 和 NEW 来援用触发器中发生变化的记录内容，这与其余的数据库是类似的。当初触发器还只反对行级触发，不反对语句级触发。（Oracle 既有行级触发器，又有语句级触发器）

5.2 创立触发器

语法结构 :

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[for each row]  -- 行级触发器

begin

    trigger_stmt ;

end;

示例

需要

通过触发器记录 emp 表的数据变更日志 , 蕴含减少, 批改 , 删除 ;

首先创立一张日志表 :

create table emp_logs(id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作工夫',
  operate_id int(11) not null comment '操作表的 ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

创立 insert 型触发器，实现插入数据时的日志记录 :

DELIMITER $

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));    
end $

DELIMITER ;

创立 update 型触发器，实现更新数据时的日志记录 :

DELIMITER $

create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('批改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 批改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
end $

DELIMITER ;

创立 delete 行的触发器 , 实现删除数据时的日志记录 :

DELIMITER $

create trigger emp_logs_delete_trigger
after delete 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
end $

DELIMITER ;

测试：

insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);

update emp set age = 39 where id = 3;

delete from emp where id = 5;

5.3 删除触发器

语法结构 :

drop trigger [schema_name.]trigger_name

如果没有指定 schema_name，默认为以后数据库。

5.4 查看触发器

能够通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

语法结构：

show triggers ;

Mysql 高级 -day02

1. Mysql 的体系结构概览

整个 MySQL Server 由以下组成

• Connection Pool : 连接池组件
• Management Services & Utilities : 治理服务和工具组件
• SQL Interface : SQL 接口组件
• Parser : 查问分析器组件
• Optimizer : 优化器组件
• Caches & Buffers : 缓冲池组件
• Pluggable Storage Engines : 存储引擎
• File System : 文件系统

1）连贯层

最上层是一些客户端和链接服务，蕴含本地 sock 通信和大多数基于客户端 / 服务端工具实现的相似于 TCP/IP 的通信。次要实现一些相似于连贯解决、受权认证、及相干的平安计划。在该层上引入了线程池的概念，为通过认证平安接入的客户端提供线程。同样在该层上能够实现基于 SSL 的平安链接。服务器也会为平安接入的每个客户端验证它所具备的操作权限。

2）服务层

第二层架构次要实现大多数的外围服务性能，如 SQL 接口，并实现缓存的查问，SQL 的剖析和优化，局部内置函数的执行。所有跨存储引擎的性能也在这一层实现，如 过程、函数等。在该层，服务器会解析查问并创立相应的外部解析树，并对其实现相应的优化如确定表的查问的程序，是否利用索引等，最初生成相应的执行操作。 如果是 select 语句，服务器还会查问外部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中可能很好的晋升零碎的性能。

3）引擎层

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通信。不同的存储引擎具备不同的性能，这样咱们能够依据本人的须要，来选取适合的存储引擎。

4）存储层

数据存储层，次要是将数据存储在文件系统之上，并实现与存储引擎的交互。

和其余数据库相比，MySQL 有点不同凡响，它的架构能够在多种不同场景中利用并施展良好作用。次要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其余的零碎工作以及数据的存储提取拆散。这种架构能够依据业务的需要和理论须要抉择适合的存储引擎。

2. 存储引擎

2.1 存储引擎概述

和大多数的数据库不同, MySQL 中有一个存储引擎的概念, 针对不同的存储需要能够抉择最优的存储引擎。

存储引擎就是存储数据，建设索引，更新查问数据等等技术的实现形式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

Oracle，SqlServer 等数据库只有一种存储引擎。MySQL 提供了插件式的存储引擎架构。所以 MySQL 存在多种存储引擎，能够依据须要应用相应引擎，或者编写存储引擎。

MySQL5.0 反对的存储引擎蕴含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事务平安表，其余存储引擎是非事务平安表。

能够通过指定 show engines，来查问以后数据库反对的存储引擎：

创立新表时如果不指定存储引擎，那么零碎就会应用默认的存储引擎，MySQL5.5 之前的默认存储引擎是 MyISAM，5.5 之后就改为了 InnoDB。

查看 Mysql 数据库默认的存储引擎，指令：

 show variables like '%storage_engine%'；

2.2 各种存储引擎个性

上面重点介绍几种罕用的存储引擎，并比照各个存储引擎之间的区别，如下表所示：

上面咱们将重点介绍最长应用的两种存储引擎：InnoDB、MyISAM，另外两种 MEMORY、MERGE，理解即可。

2.2.1 InnoDB

InnoDB 存储引擎是 Mysql 的默认存储引擎。InnoDB 存储引擎提供了具备提交、回滚、解体恢复能力的事务平安。然而比照 MyISAM 的存储引擎，InnoDB 写的解决效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB 存储引擎不同于其余存储引擎的特点：

事务管制

create table goods_innodb(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;
start transaction;

insert into goods_innodb(id,name)values(null,'Meta20');

commit;

测试，发现在 InnoDB 中是存在事务的；

外键束缚

MySQL 反对外键的存储引擎只有 InnoDB，在创立外键的时候，要求父表必须有对应的索引，子表在创立外键的时候，也会主动的创立对应的索引。

上面两张表中，country_innodb 是父表，country_id 为主键索引，city_innodb 表是子表，country_id 字段为外键，对应于 country_innodb 表的主键 country_id。

create table country_innodb(
    country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table city_innodb(
    city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ON DELETE RESTRICT: 删除主表，如果有关联记录，不删除
-- ON UPDATE CASCADE: 更新主表，如果子表有关联记录，更新子表记录

insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);

在创立索引时，能够指定在删除、更新父表时，对子表进行的相应操作，包含 RESTRICT、CASCADE、SET NULL 和 NO ACTION。

RESTRICT 和 NO ACTION 雷同，是指限度在子表有关联记录的状况下，父表不能更新；

CASCADE 示意父表在更新或者删除时，更新或者删除子表对应的记录；

SET NULL 则示意父表在更新或者删除的时候，子表的对应字段被 SET NULL。

针对下面创立的两个表，子表的外键指定是 ON DELETE RESTRICT ON UPDATE CASCADE 形式的，那么在主表删除记录的时候，如果子表有对应记录，则不容许删除，主表在更新记录的时候，如果子表有对应记录，则子表对应更新。

表中数据如下图所示：

外键信息能够应用如下两种形式查看：

show create table city_innodb ;

删除 country_id 为 1 的 country 数据：

 delete from country_innodb where country_id = 1;

更新主表 country 表的字段 country_id :

update country_innodb set country_id = 100 where country_id = 1;

更新后，子表的数据信息为：

存储形式

InnoDB 存储表和索引有以下两种形式：

①. 应用共享表空间存储，这种形式创立的表的表构造保留在.frm 文件中，数据和索引保留在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，能够是多个文件。

②. 应用多表空间存储，这种形式创立的表的表构造依然存在 .frm 文件中，然而每个表的数据和索引独自保留在 .ibd 中。

2.2.2 MyISAM

MyISAM 不反对事务、也不反对外键，其劣势是拜访的速度快，对事务的完整性没有要求或者以 SELECT、INSERT 为主的利用基本上都能够应用这个引擎来创立表。有以下两个比拟重要的特点：

不反对事务

create table goods_myisam(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;

通过测试，咱们发现，在 MyISAM 存储引擎中，是没有事务管制的；

文件存储形式

每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名雷同，但拓展名别离是：

.frm (存储表定义)；

.MYD(MYData , 存储数据)；

.MYI(MYIndex , 存储索引)；

2.2.3 MEMORY

Memory 存储引擎将表的数据寄存在内存中。每个 MEMORY 表理论对应一个磁盘文件，格局是.frm，该文件中只存储表的构造，而其数据文件，都是存储在内存中，这样有利于数据的疾速解决，进步整个表的效率。MEMORY 类型的表拜访十分地快，因为他的数据是寄存在内存中的，并且默认应用 HASH 索引，然而服务一旦敞开，表中的数据就会失落。

2.2.4 MERGE

MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须构造完全相同，MERGE 表自身并没有存储数据，对 MERGE 类型的表能够进行查问、更新、删除操作，这些操作实际上是对外部的 MyISAM 表进行的。

对于 MERGE 类型表的插入操作，是通过 INSERT_METHOD 子句定义插入的表，能够有 3 个不同的值，应用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最初一个表上，不定义这个子句或者定义为 NO，示意不能对这个 MERGE 表执行插入操作。

能够对 MERGE 表进行 DROP 操作，然而这个操作只是删除 MERGE 表的定义，对外部的表是没有任何影响的。

上面是一个创立和应用 MERGE 表的示例：

1）. 创立 3 个测试表 order_1990, order_1991, order_all , 其中 order_all 是前两个表的 MERGE 表：

create table order_1990(
    order_id int ,
    order_money double(10,2),
    order_address varchar(50),
    primary key (order_id)
)engine = myisam default charset=utf8;


create table order_1991(
    order_id int ,
    order_money double(10,2),
    order_address varchar(50),
    primary key (order_id)
)engine = myisam default charset=utf8;


create table order_all(
    order_id int ,
    order_money double(10,2),
    order_address varchar(50),
    primary key (order_id)
)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;

2）. 别离向两张表中插入记录

insert into order_1990 values(1,100.0,'北京');
insert into order_1990 values(2,100.0,'上海');

insert into order_1991 values(10,200.0,'北京');
insert into order_1991 values(11,200.0,'上海');

3）. 查问 3 张表中的数据。

order_1990 中的数据：

order_1991 中的数据：

order_all 中的数据：

4）. 往 order_all 中插入一条记录，因为在 MERGE 表定义时，INSERT_METHOD 抉择的是 LAST，那么插入的数据会想最初一张表中插入。

insert into order_all values(100,10000.0,'西安')；

2.3 存储引擎的抉择

在抉择存储引擎时，应该依据利用零碎的特点抉择适合的存储引擎。对于简单的利用零碎，还能够依据理论状况抉择多种存储引擎进行组合。以下是几种罕用的存储引擎的应用环境。

• InnoDB : 是 Mysql 的默认存储引擎，用于事务处理应用程序，反对外键。如果利用对事务的完整性有比拟高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查问意外，还蕴含很多的更新、删除操作，那么 InnoDB 存储引擎是比拟适合的抉择。InnoDB 存储引擎除了无效的升高因为删除和更新导致的锁定，还能够确保事务的残缺提交和回滚，对于相似于计费零碎或者财务零碎等对数据准确性要求比拟高的零碎，InnoDB 是最合适的抉择。
• MyISAM：如果利用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么抉择这个存储引擎是十分适合的。
• MEMORY：将所有数据保留在 RAM 中，在须要疾速定位记录和其余相似数据环境下，能够提供几块的拜访。MEMORY 的缺点就是对表的大小有限度，太大的表无奈缓存在内存中，其次是要确保表的数据能够复原，数据库异样终止后表中的数据是能够复原的。MEMORY 表通常用于更新不太频繁的小表，用以疾速失去拜访后果。
• MERGE：用于将一系列等同的 MyISAM 表以逻辑形式组合在一起，并作为一个对象援用他们。MERGE 表的长处在于能够冲破对单个 MyISAM 表的大小限度，并且通过将不同的表散布在多个磁盘上，能够无效的改善 MERGE 表的拜访效率。这对于存储诸如数据仓储等 VLDB 环境非常适合。

3. 优化 SQL 步骤

在利用的开发过程中，因为初期数据量小，开发人员写 SQL 语句时更器重性能上的实现，然而当利用零碎正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐步透出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个零碎性能的瓶颈，因而咱们必须要对它们进行优化，本章将具体介绍在 MySQL 中优化 SQL 语句的办法。

当面对一个有 SQL 性能问题的数据库时，咱们应该从何处动手来进行零碎的剖析，使得可能尽快定位问题 SQL 并尽快解决问题。

3.1 查看 SQL 执行频率

MySQL 客户端连贯胜利后，通过 show [session|global] status 命令能够提供服务器状态信息。show [session|global] status 能够依据须要加上参数“session”或者“global”来显示 session 级（以后连贯）的统计后果和 global 级（自数据库上次启动至今）的统计后果。如果不写，默认应用参数是“session”。

上面的命令显示了以后 session 中所有统计参数的值：

show status like 'Com_______';

show status like 'Innodb_rows_%';

Com_xxx 示意每个 xxx 语句执行的次数，咱们通常比较关心的是以下几个统计参数。

Com_* : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_* : 这几个参数只是针对 InnoDB 存储引擎的，累加的算法也略有不同。

3.2 定位低效率执行 SQL

能够通过以下两种形式定位执行效率较低的 SQL 语句。

• 慢查问日志 : 通过慢查问日志定位那些执行效率较低的 SQL 语句，用 –log-slow-queries[=file_name] 选项启动时，mysqld 写一个蕴含所有执行工夫超过 long_query_time 秒的 SQL 语句的日志文件。具体能够查看本书第 26 章中日志治理的相干局部。
• show processlist : 慢查问日志在查问完结当前才记录，所以在利用反映执行效率呈现问题的时候查问慢查问日志并不能定位问题，能够应用 show processlist 命令查看以后 MySQL 正在进行的线程，包含线程的状态、是否锁表等，能够实时地查看 SQL 的执行状况，同时对一些锁表操作进行优化。

1）id 列，用户登录 mysql 时，零碎调配的 "connection_id"，能够应用函数 connection_id()查看

2）user 列，显示以后用户。如果不是 root，这个命令就只显示用户权限范畴的 sql 语句

3）host 列，显示这个语句是从哪个 ip 的哪个端口上发的，能够用来跟踪呈现问题语句的用户

4）db 列，显示这个过程目前连贯的是哪个数据库

5）command 列，显示以后连贯的执行的命令，个别取值为休眠（sleep），查问（query），连贯（connect）等

6）time 列，显示这个状态继续的工夫，单位是秒

7）state 列，显示应用以后连贯的 sql 语句的状态，很重要的列。state 形容的是语句执行中的某一个状态。一个 sql 语句，以查问为例，可能须要通过 copying to tmp table、sorting result、sending data 等状态才能够实现

8）info 列，显示这个 sql 语句，是判断问题语句的一个重要依据

3.3 explain 剖析执行打算

通过以上步骤查问到效率低的 SQL 语句后，能够通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包含在 SELECT 语句执行过程中表如何连贯和连贯的程序

查问 SQL 语句的执行打算：

explain  select * from tb_item where id = 1;

explain  select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通 3G 手机 3';

3.3.1 环境筹备

CREATE TABLE `t_role` (`id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (`id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_role` (`id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生 1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生 2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师 1');



INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');


INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

3.3.2 explain 之 id

id 字段是 select 查问的序列号，是一组数字，示意的是查问中执行 select 子句或者是操作表的程序。id 状况有三种：

1）id 雷同示意加载表的程序是从上到下。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

2）id 不同 id 值越大，优先级越高，越先被执行。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

3）id 有雷同，也有不同，同时存在。id 雷同的能够认为是一组，从上往下程序执行；在所有的组中，id 的值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ; 

3.3.3 explain 之 select_type

示意 SELECT 的类型，常见的取值，如下表所示：从上往下效率越来越低

表中 derived2 示意 derived 中 id 为 2 的表。

3.3.4 explain 之 table

展现这一行的数据是对于哪一张表的

3.3.5 explain 之 type

type 显示的是拜访类型，是较为重要的一个指标，可取值为：

后果值从最好到最坏以此是：

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


system > const > eq_ref > ref > range > index > ALL

一般来说，咱们须要保障查问至多达到 range 级别，最好达到 ref。

3.3.6 explain 之 key

possible_keys : 显示可能利用在这张表的索引，一个或多个。key：理论应用的索引，如果为 NULL，则没有应用索引。key_len : 示意索引中应用的字节数，该值为索引字段最大可能长度，并非理论应用长度，在不损失精确性的前提下，长度越短越好。

3.3.7 explain 之 rows

扫描行的数量。

3.3.8 explain 之 extra

其余的额定的执行打算信息，在该列展现。须要优化的后面两个，放弃的是前面 using index

3.4 show profile 剖析 SQL

Mysql 从 5.0.37 版本开始减少了对 show profiles 和 show profile 语句的反对。show profiles 可能在做 SQL 优化时帮忙咱们理解工夫都消耗到哪里去了。

通过 have_profiling 参数，可能看到以后 MySQL 是否反对 profile：

默认 profiling 是敞开的，能够通过 set 语句在 Session 级别开启 profiling：

set profiling=1; // 开启 profiling 开关；

通过 profile，咱们可能更分明地理解 SQL 执行的过程。

首先，咱们能够执行一系列的操作，如下图所示：

show databases;

use db01;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

执行完上述命令之后，再执行 show profiles 指令，来查看 SQL 语句执行的耗时：

通过 show profile for query query_id 语句能够查看到该 SQL 执行过程中每个线程的状态和耗费的工夫：

TIP：Sending data 状态示意 MySQL 线程开始拜访数据行并把后果返回给客户端，而不仅仅是返回个客户端。因为在 Sending data 状态下，MySQL 线程往往须要做大量的磁盘读取操作，所以常常是整个查问中耗时最长的状态。

在获取到最耗费工夫的线程状态后，MySQL 反对进一步抉择 all、cpu、block io、context switch、page faults 等明细类型类查看 MySQL 在应用什么资源上消耗了过高的工夫。例如，抉择查看 CPU 的消耗工夫：

3.5 trace 剖析优化器执行打算

MySQL5.6 提供了对 SQL 的跟踪 trace, 通过 trace 文件可能进一步理解为什么优化器抉择 A 打算, 而不是抉择 B 打算。

关上 trace，设置格局为 JSON，并设置 trace 最大可能应用的内存大小，防止解析过程中因为默认内存过小而不可能残缺展现。

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行 SQL 语句：

select * from tb_item where id < 4;

最初，查看 information_schema.optimizer_trace 就能够晓得 MySQL 是如何执行 SQL 的：

select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from tb_item where id < 4
TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {"expanded_query": "/* select#1 */ select `tb_item`.`id` AS `id`,`tb_item`.`title` AS `title`,`tb_item`.`price` AS `price`,`tb_item`.`num` AS `num`,`tb_item`.`categoryid` AS `categoryid`,`tb_item`.`status` AS `status`,`tb_item`.`sellerid` AS `sellerid`,`tb_item`.`createtime` AS `createtime`,`tb_item`.`updatetime` AS `updatetime` from `tb_item` where (`tb_item`.`id` < 4)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`tb_item`.`id` < 4)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`tb_item`.`id` < 4)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "table_dependencies": [
              {
                "table": "`tb_item`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [] /* depends_on_map_bits */}
            ] /* table_dependencies */
          },
          {"ref_optimizer_key_uses": [] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`tb_item`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 9816098,
                    "cost": 2.04e6
                  } /* table_scan */,
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": ["id"] /* key_parts */
                    }
                  ] /* potential_range_indices */,
                  "setup_range_conditions": [ ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": ["id < 4"] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 1.6154,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 3,
                      "ranges": ["id < 4"] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 3,
                    "cost_for_plan": 1.6154,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {"plan_prefix": [] /* plan_prefix */,
                "table": "`tb_item`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "rows": 3,
                      "cost": 2.2154,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.2154,
                "rows_for_plan": 3,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {"original_condition": "(`tb_item`.`id` < 4)",
              "attached_conditions_computation": [ ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`tb_item`",
                  "attached": "(`tb_item`.`id` < 4)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`tb_item`",
                "access_type": "range"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [] /* steps */} /* join_execution */
    }
  ] /* steps */
}

4. 索引的应用

索引是数据库优化最罕用也是最重要的伎俩之一, 通过索引通常能够帮忙用户解决大多数的 MySQL 的性能优化问题。

4.1 验证索引晋升查问效率

在咱们筹备的表构造 tb_item 中，一共存储了 300 万记录；

A. 依据 ID 查问

select * from tb_item where id = 1999\G;

查问速度很快，靠近 0s，次要的起因是因为 id 为主键，有索引；

2). 依据 title 进行准确查问

select * from tb_item where title = 'iphoneX 挪动 3G 32G941'\G; 

查看 SQL 语句的执行打算：

解决计划，针对 title 字段，创立索引：

create index idx_item_title on tb_item(title);

索引创立实现之后，再次进行查问：

通过 explain，查看执行打算，执行 SQL 时应用了方才创立的索引

4.2 索引的应用

4.2.1 筹备环境

create table `tb_seller` (`sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO 科技有限公司','OPPO 官网旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官网旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官网旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

-- 创立联结索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

4.2.2 防止索引生效

1). 全值匹配，对索引中所有列都指定具体值。该状况下，索引失效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

2). 最左前缀法令

如果索引了多列，要恪守最左前缀法令。指的是查问从索引的最左前列开始，并且不跳过索引中的列。(设想爬楼梯)

匹配最左前缀法令，走索引：

守法最左前缀法令，索引生效：

如果合乎最左法令，然而呈现跳跃某一列，只有最左列索引失效：

3). 范畴查问左边的列，不能应用索引。范畴查问条件之后的字段，索引生效

依据后面的两个字段 name，status 查问是走索引的，然而最初一个条件 address 没有用到索引。

4). 不要在索引列上进行运算操作，索引将生效。

5). 字符串不加单引号，造成索引生效。

因为，在查问是，没有对字符串加单引号，MySQL 的查问优化器，会主动的进行类型转换，造成索引生效。

6). 尽量应用笼罩索引，防止 select *

尽量应用笼罩索引（只拜访索引的查问（索引列齐全蕴含查问列）），缩小 select *。

如果查问列，超出索引列，也会升高性能。(索引列没有 password，所以须要回表查问)

TIP : 
    
using index：应用笼罩索引的时候就会呈现

using where：在查找应用索引的状况下，须要回表去查问所需的数据

using index condition：查找应用了索引，然而须要回表查问数据

using index ; using where：查找应用了索引，然而须要的数据都在索引列中能找到，所以不须要回表查问数据

7). 用 or 宰割开的条件，如果 or 前的条件中的列有索引，而前面的列中没有索引，那么波及的索引都不会被用到。

示例，name 字段是索引列，而 createtime 不是索引列，两头是 or 进行连贯是不走索引的：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;    

8). 以 % 结尾的 Like 含糊查问，索引生效。

如果仅仅是尾部含糊匹配，索引不会生效。如果是头部含糊匹配，索引生效。

解决方案：通过笼罩索引来解决

9). 如果 MySQL 评估应用索引比全表更慢，则不应用索引。

明明独自创立了 address 索引，然而 explain 查看没有应用索引，这种状况跟表中数据无关，查看表数据晓得，12 条数据，11 条是 ’ 北京市 ’，应用索引查找效率不如间接全表扫描来的快，所以在执行 SQL 语句的时候，MySQL 放弃应用索引，而应用全表扫描。

10). is NULL，is NOT NULL 有时 索引生效。

MySQL 底层会主动判断，如果全表扫描快，则间接应用全表扫描，不走索引。如果表中该索引列数据绝大多数是非空值，则应用 is not null 的时候走索引，应用 is null 的时候不走索引（还不如全表扫描快），全表扫描；反之亦然。

11). in 走索引，not in 索引生效。

12). 单列索引和复合索引。

尽量应用复合索引，而少应用单列索引。

创立复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创立了三个索引：name
    name + status
    name + status + address

创立单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

应用单列索引的时候，数据库会抉择一个最优的索引（辨识度最高索引）来应用，并不会应用全副索引。

4.3 查看索引应用状况

show status like 'Handler_read%';     -- 查看以后会话索引应用状况

show global status like 'Handler_read%';    -- 查看全局索引应用状况

Handler_read_first：索引中第一条被读的次数。如果较高，示意服务器正执行大量全索引扫描（这个值越低越好）。Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，示意索引失去的性能改善不高，因为索引不常常应用（这个值越高越好）。Handler_read_next：依照键程序读下一行的申请数。如果你用范畴束缚或如果执行索引扫描来查问索引列，该值减少。Handler_read_prev：依照键程序读前一行的申请数。该读办法次要用于优化 ORDER BY ... DESC。Handler_read_rnd：依据固定地位读一行的申请数。如果你正执行大量查问并须要对后果进行排序该值较高。你可能应用了大量须要 MySQL 扫描整个表的查问或你的连贯没有正确应用键。这个值较高，意味着运行效率低，应该建设索引来补救。Handler_read_rnd_next：在数据文件中读下一行的申请数。如果你正进行大量的表扫描，该值较高。通常阐明你的表索引不正确或写入的查问没有利用索引。

5. SQL 优化

5.1 大批量插入数据

环境筹备：

CREATE TABLE `tb_user_2` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL COMMENT '用户状态',
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)  -- 唯一性束缚
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

当应用 load 命令导入数据的时候，适当的设置能够进步导入的效率。

对于 InnoDB 类型的表，有以下几种形式能够进步导入的效率：

1）主键程序插入

因为 InnoDB 类型的表是依照主键的程序保留的，所以将导入的数据依照主键的顺序排列，能够无效的进步导入数据的效率。如果 InnoDB 表没有主键，那么零碎会主动默认创立一个外部列作为主键，所以如果能够给表创立一个主键，将能够利用这点，来进步导入数据的效率。

脚本文件介绍 :
    sql1.log  ----> 主键有序
    sql2.log  ----> 主键无序

插入 ID 顺序排列数据：

插入 ID 无序排列数据：

留神呈现：【LOAD DATA 语法上传数据】

具体解法见 最初【Mysql 笔记 ERROR】

2）敞开唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，敞开唯一性校验，在导入完结后执行 SET UNIQUE_CHECKS=1，复原唯一性校验，能够进步导入的效率。

3）手动提交事务

如果利用应用主动提交的形式，倡议在导入前执行 SET AUTOCOMMIT=0，敞开主动提交，导入完结后再执行 SET AUTOCOMMIT=1，关上主动提交，也能够进步导入的效率。

5.2 优化 insert 语句

当进行数据的 insert 操作的时候，能够思考采纳以下几种优化计划。

• 如果须要同时对一张表插入很多行数据时，应该尽量应用多个值表的 insert 语句，这种形式将大大的缩减客户端与数据库之间的连贯、敞开等耗费。使得效率比离开执行的单个 insert 语句快。

  示例，原始形式为：

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');

  优化后的计划为：

  insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

• 在事务中进行数据插入。

  start transaction;
  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  commit;

• 数据有序插入

  insert into tb_test values(4,'Tim');
  insert into tb_test values(1,'Tom');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(5,'Rose');
  insert into tb_test values(2,'Cat');

  优化后

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(4,'Tim');
  insert into tb_test values(5,'Rose');

5.3 优化 order by 语句

5.3.1 环境筹备

CREATE TABLE `emp` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary);

5.3.2 两种排序形式

1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引间接返回排序后果的排序都叫 FileSort 排序。效率低

2). 第二种通过有序索引程序扫描间接返回有序数据，这种状况即为 using index，不须要额定排序，操作效率高。

多字段排序

理解了 MySQL 的排序形式，优化指标就清晰了：尽量减少额定的排序，通过索引间接返回有序数据(using index)。where 条件和 Order by 应用雷同的索引，并且 Order By 的程序和索引程序雷同，并且 Order by 的字段都是升序，或者都是降序。否则必定须要额定的操作，这样就会呈现 FileSort。

5.3.3 Filesort 的优化

通过创立适合的索引，可能缩小 Filesort 的呈现，然而在某些状况下，条件限度不能让 Filesort 隐没，那就须要放慢 Filesort 的排序操作。对于 Filesort，MySQL 有两种排序算法：

1）两次扫描算法：MySQL4.1 之前，应用该形式排序。首先依据条件取出排序字段和行指针信息，而后在排序区 sort buffer 中排序，如果 sort buffer 不够，则在长期表 temporary table 中存储排序后果。实现排序之后，再依据行指针回表读取记录，该操作可能会导致大量随机 I / O 操作。

2）一次扫描算法：一次性取出满足条件的所有字段，而后在排序区 sort buffer 中排序后间接输入后果集。排序时内存开销较大，然而排序效率比两次扫描算法要高。

MySQL 通过比拟零碎变量 max_length_for_sort_data 的大小和 Query 语句取出的字段总大小，来断定是否那种排序算法，如果 max_length_for_sort_data 更大，那么应用第二种优化之后的算法；否则应用第一种。

能够适当进步 sort_buffer_size 和 max_length_for_sort_data 零碎变量，来增大排序区的大小，进步排序的效率。

5.4 优化 group by 语句

因为 GROUP BY 实际上也同样会进行排序操作，而且与 ORDER BY 相比，GROUP BY 次要只是多了排序之后的分组操作。当然，如果在分组的时候还应用了其余的一些聚合函数，那么还须要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也能够利用到索引。

如果查问蕴含 group by 然而用户想要防止排序后果的耗费，则能够执行 order by null 禁止排序。如下：

drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

优化后

explain select age,count(*) from emp group by age order by null;

从下面的例子能够看出，第一个 SQL 语句须要进行 ”filesort”，而第二个 SQL 因为 order by null 不须要进行 “filesort”，而上文提过 Filesort 往往十分消耗工夫。

创立索引：

create index idx_emp_age_salary on emp(age,salary)；

5.5 优化嵌套查问

Mysql4.1 版本之后，开始反对 SQL 的子查问。这个技术能够应用 SELECT 语句来创立一个单列的查问后果，而后把这个后果作为过滤条件用在另一个查问中。应用子查问能够一次性的实现很多逻辑上须要多个步骤能力实现的 SQL 操作，同时也能够防止事务或者表锁死，并且写起来也很容易。然而，有些状况下，子查问是能够被更高效的连贯（JOIN）代替。

示例，查找有角色的所有的用户信息 :

 explain select * from t_user where id in (select user_id from user_role);

执行打算为 :

优化后 :

explain select * from t_user u , user_role ur where u.id = ur.user_id;

连贯 (Join) 查问之所以更有效率一些，是因为 MySQL 不须要在内存中创立长期表来实现这个逻辑上须要两个步骤的查问工作。

5.6 优化 OR 条件

对于蕴含 OR 的查问子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引，而且不能应用到复合索引；如果没有索引，则应该思考减少索引。

获取 emp 表中的所有的索引：

示例：

explain select * from emp where id = 1 or age = 30;

倡议应用 union 替换 or ：

咱们来比拟下重要指标，发现次要差异是 type 和 ref 这两项

type 显示的是拜访类型，是较为重要的一个指标，后果值从好到坏顺次是：

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

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，能够看到这是一个很显著的差距

UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 示意是常量值援用，十分快

这两项的差距就阐明了 UNION 要优于 OR。

5.7 优化分页查问

个别分页查问时，通过创立笼罩索引可能比拟好地进步性能。一个常见又十分头疼的问题就是 limit 2000000,10，此时须要 MySQL排序 前 2000010 记录，仅仅返回 2000000 – 2000010 的记录，其余记录抛弃，查问排序的代价十分大。

5.7.1 优化思路一

在索引上实现排序分页操作，最初依据主键关联回原表查问所须要的其余列内容。

5.7.2 优化思路二

该计划实用于主键自增的表，能够把 Limit 查问转换成某个地位的查问。(局限性：主键不能断层)

5.8 应用 SQL 提醒

SQL 提醒，是优化数据库的一个重要伎俩，简略 来说，就是在 SQL 语句中退出一些人为的提醒来达到优化操作的目标。

5.8.1 USE INDEX

在查问语句中表名的前面，增加 use index 来提供心愿 MySQL 去参考的索引列表，就能够让 MySQL 不再思考其余可用的索引。

create index idx_seller_name on tb_seller(name);

5.8.2 IGNORE INDEX

如果用户只是单纯的想让 MySQL 疏忽一个或者多个索引，则能够应用 ignore index 作为 hint。

 explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';

5.8.3 FORCE INDEX

为强制 MySQL 应用一个特定的索引，可在查问中应用 force index 作为 hint。

create index idx_seller_address on tb_seller(address);

Mysql 高级 -day03

1. 利用优化

后面章节，咱们介绍了很多数据库的优化措施。然而在理论生产环境中，因为数据库自身的性能局限，就必须要对前台的利用进行一些优化，来升高数据库的拜访压力。

1.1 应用连接池

对于拜访数据库来说，建设连贯的代价是比拟低廉的，因为咱们频繁的创立敞开连贯，是比拟消耗资源的，咱们有必要建设 数据库连接池，以进步拜访的性能。

1.2 缩小对 MySQL 的拜访

1.2.1 防止对数据进行反复检索

在编写利用代码时，须要可能理清对数据库的拜访逻辑。可能一次连贯就获取到后果的，就不必两次连贯，这样能够大大减少对数据库无用的反复申请。

比方，须要获取书籍的 id 和 name 字段，则查问如下：

 select id , name from tb_book;

之后，在业务逻辑中有须要获取到书籍状态信息，则查问如下：

select id , status from tb_book;

这样，就须要向数据库提交两次申请，数据库就要做两次查问操作。其实齐全能够用一条 SQL 语句失去想要的后果。

select id, name , status from tb_book;

1.2.2 减少 cache 层

在利用中，咱们能够在利用中减少 缓存 层来达到加重数据库累赘的目标。缓存层有很多种，也有很多实现形式，只有能达到升高数据库的累赘又能满足利用需要就能够。

因而能够局部数据从数据库中抽取进去放到利用端以文本形式存储，或者应用框架 (Mybatis, Hibernate) 提供的一级缓存 / 二级缓存，或者应用 redis 数据库来缓存数据。

1.3 负载平衡

负载平衡是利用中应用十分广泛的一种优化办法，它的机制就是利用某种平衡算法，将固定的负载量散布到不同的服务器上，以此来升高单台服务器的负载，达到优化的成果。

1.3.1 利用 MySQL 复制分流查问

通过 MySQL 的主从复制，实现读写拆散，使增删改操作走主节点，查问操作走从节点，从而能够升高单台服务器的读写压力。

1.3.2 采纳分布式数据库架构

分布式数据库架构适宜大数据量、负载高的状况，它有良好的拓展性和高可用性。通过在多台服务器之间散布数据，能够实现在多台服务器之间的负载平衡，进步拜访效率。

2. Mysql 中查问缓存优化

2.1 概述

开启 Mysql 的查问缓存，当执行完全相同的 SQL 语句的时候，服务器就会间接从缓存中读取后果，当数据被批改，之前的缓存会生效，批改比拟频繁的表不适宜做查问缓存。

2.2 操作流程

1. 客户端发送一条查问给服务器；
2. 服务器先会查看查问缓存，如果命中了缓存，则立刻返回存储在缓存中的后果。否则进入下一阶段；
3. 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行打算；
4. MySQL 依据优化器生成的执行打算，调用存储引擎的 API 来执行查问；
5. 将后果返回给客户端。

2.3 查问缓存配置

1. 查看以后的 MySQL 数据库是否反对查问缓存：

   SHOW VARIABLES LIKE 'have_query_cache';    

2. Mysql8，曾经勾销了查问缓存：如图所示

3. 查看以后 MySQL 是否开启了查问缓存：

   SHOW VARIABLES LIKE 'query_cache_type';

4. 查看查问缓存的占用大小：

   SHOW VARIABLES LIKE 'query_cache_size';

5. 查看查问缓存的状态变量：

   SHOW STATUS LIKE 'Qcache%';

   各个变量的含意如下：

   参数	含意
   Qcache_free_blocks	查问缓存中的可用内存块数
   Qcache_free_memory	查问缓存的可用内存量
   Qcache_hits	查问缓存命中数
   Qcache_inserts	增加到查问缓存的查问数
   Qcache_lowmen_prunes	因为内存不足而从查问缓存中删除的查问数
   Qcache_not_cached	非缓存查问的数量（因为 query_cache_type 设置而无奈缓存或未缓存）
   Qcache_queries_in_cache	查问缓存中注册的查问数
   Qcache_total_blocks	查问缓存中的块总数

2.4 开启查问缓存

MySQL 的查问缓存默认是敞开的，须要手动配置参数 query_cache_type，来开启查问缓存。query_cache_type 该参数的可取值有三个：

在 /usr/my.cnf 配置中，减少以下配置：

配置结束之后，重启服务既可失效；

而后就能够在命令行执行 SQL 语句进行验证，执行一条比拟耗时的 SQL 语句，而后再多执行几次，查看前面几次的执行工夫；获取通过查看查问缓存的缓存命中数，来断定是否走查问缓存。

2.5 查问缓存 SELECT 选项

能够在 SELECT 语句中指定两个与查问缓存相干的选项：

SQL_CACHE : 如果查问后果是可缓存的，并且 query_cache_type 零碎变量的值为 ON 或 DEMAND，则缓存查问后果。

SQL_NO_CACHE : 服务器不应用查问缓存。它既不查看查问缓存，也不查看后果是否已缓存，也不缓存查问后果。

例子：

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

2.6 查问缓存生效的状况

1） SQL 语句不统一的状况，要想命中查问缓存，查问的 SQL 语句必须统一。

SQL1 : select count(*) from tb_item;
SQL2 : Select count(*) from tb_item;

2）当查问语句中有一些不确定的时，则不会缓存。如：now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database()。

SQL1 : select * from tb_item where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database();

3）不应用任何表查问语句。

select 'A';

4） 查问 mysql，information_schema 或 performance_schema 数据库中的表时，不会走查问缓存。(默认的零碎数据库)

select * from information_schema.engines;

5）在存储的函数，触发器或事件的主体内执行的查问。

6）如果表更改，则应用该表的所有高速缓存查问都将变为有效并从高速缓存中删除 。这包含应用MERGE 映射到已更改表的表的查问。一个表能够被许多类型的语句，如被扭转 INSERT，UPDATE，DELETE，TRUNCATE TABLE，ALTER TABLE，DROP TABLE，或 DROP DATABASE。

3. Mysql 内存治理及优化

3.1 内存优化准则

1）将尽量多的内存调配给 MySQL 做缓存，但要给操作系统和其余程序预留足够内存。

2）MyISAM 存储引擎的数据文件读取依赖于操作系统本身的 IO 缓存，因而，如果有 MyISAM 表，就要预留更多的内存给操作系统做 IO 缓存。

3）排序区、连接区等缓存是调配给每个数据库会话（session）专用的，其默认值的设置要依据最大连接数正当调配，如果设置太大，岂但浪费资源，而且在并发连贯较高时会导致物理内存耗尽。

3.2 MyISAM 内存优化

myisam 存储引擎应用 key_buffer 缓存索引块，减速 myisam 索引的读写速度。对于 myisam 表的数据块，mysql 没有特地的缓存机制，齐全依赖于操作系统的 IO 缓存。

key_buffer_size

key_buffer_size 决定 MyISAM 索引块缓存区的大小，间接影响到 MyISAM 表的存取效率。能够在 MySQL 参数文件中设置 key_buffer_size 的值，对于个别 MyISAM 数据库，倡议至多将 1 / 4 可用内存调配给 key_buffer_size。

在 /usr/my.cnf 中做如下配置：

key_buffer_size=512M

read_buffer_size

如果须要常常程序扫描 myisam 表，能够通过增大 read_buffer_size 的值来改善性能。但须要留神的是 read_buffer_size 是每个 session 独占的，如果默认值设置太大，就会造成内存节约。

read_rnd_buffer_size

对于须要做排序的 myisam 表的查问，如带有 order by 子句的 sql，适当减少 read_rnd_buffer_size 的值，能够改善此类的 sql 性能。但须要留神的是 read_rnd_buffer_size 是每个 session 独占的，如果默认值设置太大，就会造成内存节约。

3.3 InnoDB 内存优化

innodb 用一块内存区做 IO 缓存池，该缓存池不仅用来缓存 innodb 的索引块，而且也用来缓存 innodb 的数据块。

innodb_buffer_pool_size

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保障操作系统及其他程序有足够内存可用的状况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，拜访 InnoDB 表须要的磁盘 I /O 就越少，性能也就越高。

innodb_buffer_pool_size=512M

innodb_log_buffer_size

决定了 innodb 重做日志缓存的大小，对于可能产生大量更新记录的大事务，减少 innodb_log_buffer_size 的大小，能够防止 innodb 在事务提交前就执行不必要的日志写入磁盘操作。

innodb_log_buffer_size=10M

4. Mysql 并发参数调整

从实现上来说，MySQL Server 是多线程构造，包含后盾线程和客户服务线程。多线程能够无效利用服务器资源，进步数据库的并发性能。在 Mysql 中，管制并发连贯和线程的主要参数包含 max_connections、back_log、thread_cache_size、table_open_cahce。

4.1 max_connections

采纳 max_connections 管制容许连贯到 MySQL 数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且始终增长，则阐明一直有连贯申请因数据库连接数已达到容许最大值而失败，这是能够思考增大 max_connections 的值。

Mysql 最大可反对的连接数，取决于很多因素，包含给定操作系统平台的线程库的品质、内存大小、每个连贯的负荷、CPU 的处理速度，冀望的响应工夫等。在 Linux 平台下，性能好的服务器，反对 500-1000 个连贯不是难事，须要依据服务器性能进行评估设定。

4.2 back_log

back_log 参数管制 MySQL 监听 TCP 端口时设置的积压申请栈大小。如果 MySql 的连接数达到 max_connections 时，新来的申请将会被存在堆栈中，以期待某一连贯开释资源，该堆栈的数量即 back_log，如果期待连贯的数量超过 back_log，将不被授予连贯资源，将会报错。5.6.6 版本之前默认值为 50，之后的版本默认为 50 +（max_connections / 5），但最大不超过 900。

如果须要数据库在较短的工夫内解决大量连贯申请，能够思考适当增大 back_log 的值。

4.3 table_open_cache

该参数用来管制所有 SQL 语句执行线程可关上表缓存的数量，而在执行 SQL 语句时，每一个 SQL 执行线程至多要关上 1 个表缓存。该参数的值应该依据设置的最大连接数 max_connections 以及每个连贯执行关联查问中波及的表的最大数量来设定：

max_connections x N；

4.4 thread_cache_size

为了放慢连贯数据库的速度，MySQL 会缓存肯定数量的客户服务线程以备重用，通过参数 thread_cache_size 可管制 MySQL 缓存客户服务线程的数量。(在 MySQL Server 端设置了线程池的大小)

4.5 innodb_lock_wait_timeout

该参数是用来设置 InnoDB 事务期待行锁的工夫，默认值是 50s，能够依据须要进行动静设置。对于须要疾速反馈的业务零碎来说，能够将行锁的等待时间调小，以防止事务长时间挂起；对于后盾运行的批量处理程序来说，能够将行锁的等待时间调大，以防止产生大的回滚操作。

5. Mysql 锁问题

5.1 锁概述

锁是计算机协调多个过程或线程并发拜访某一资源的机制（防止争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发拜访的一致性、有效性是所有数据库必须解决的一个问题，锁抵触也是影响数据库并发拜访性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加简单。

5.2 锁分类

从对数据操作的粒度分：

1）表锁：操作时，会锁定整个表。

2）行锁：操作时，会锁定以后操作行。

从对数据操作的类型分：

1）读锁（共享锁）：针对同一份数据，多个读操作能够同时进行而不会相互影响。

2）写锁（排它锁）：以后操作没有实现之前，它会阻断其余写锁和读锁。

5.3 Mysql 锁

绝对其余数据库而言，MySQL 的锁机制比较简单，其最显著的特点是不同的存储引擎反对不同的锁机制。下表中列举出了各存储引擎对锁的反对状况：

MySQL 这 3 种锁的个性可大抵归纳如下：

从上述特点可见，很难抽象地说哪种锁更好，只能就具体利用的特点来说哪种锁更适合！仅从锁的角度来说：表级锁更适宜于以查问为主，只有大量按索引条件更新数据的利用，如 Web 利用；而行级锁则更适宜于有大量按索引条件并发更新大量不同数据，同时又有并查问的利用，如一些在线事务处理（OLTP）零碎。

5.4 MyISAM 表锁

MyISAM 存储引擎只反对表锁，这也是 MySQL 开始几个版本中惟一反对的锁类型。

5.4.1 如何加表锁

MyISAM 在执行查问语句（SELECT）前，会主动给波及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会主动给波及的表加写锁，这个过程并不需要用户干涉，因而，用户个别不须要间接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

加读锁：lock table table_name read;

加写锁：lock table table_name write；

5.4.2 读锁案例

筹备环境

create database demo_03 default charset=utf8mb4;

use demo_03;

CREATE TABLE `tb_book` (`id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java 编程思维','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr 编程思维','2088-08-08','0');



CREATE TABLE `tb_user` (`id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');

客户端 一：

1）取得 tb_book 表的读锁

lock table tb_book read;

客户端 一：

2）执行查问操作

select * from tb_book;

能够失常执行，查问出数据。

客户端 二：

3）执行查问操作

select * from tb_book;

也能够失常执行，查问出数据。

客户端 一：

4）查问未锁定的表

select name from tb_seller;

客户端一查问未锁定的表失败。因为持有了一张 tb_book 的读锁，并未开释锁。

客户端 二：

5）查问未锁定的表

select name from tb_seller;

能够失常查问出未锁定的表；

客户端 一：

6）执行插入操作

insert into tb_book values(null,'Mysql 高级','2088-01-01','1');

执行插入，间接报错，因为以后 tb_book 取得的是 读锁，不能执行更新操作。只能读

客户端 二：

7）执行插入操作

insert into tb_book values(null,'Mysql 高级','2088-01-01','1');

当在客户端一中开释锁指令 unlock tables 后，客户端二中的 inesrt 语句，立刻执行；

如果对某一张表加了读锁，不会阻塞其它线程的读操作，然而会阻塞其它线程的写操作。

5.4.3 写锁案例

客户端 一 :

1）取得 tb_book 表的写锁

lock table tb_book write ;

客户端 一 :

2）执行查问操作

select * from tb_book ;

查问操作执行胜利；加了写锁能够读。

客户端 一 :

3）执行更新操作

update tb_book set name = 'java 编程思维（第二版）' where id = 1;

更新操作执行胜利；（加了写锁当然能够写）

客户端 二 :

4）执行查问操作

select * from tb_book ;

当在客户端一中开释锁指令 unlock tables 后，客户端二中的 select 语句，立刻执行；（因为客户端一线程加的是写锁，写锁是排他锁，会阻断其余线程的读和写操作）

5.4.4 论断

锁模式的互相兼容性如表中所示：

由上表可见：

1）对 MyISAM 表的读操作，不会阻塞其余用户对同一表的读申请，但会阻塞对同一表的写申请；

2）对 MyISAM 表的写操作，则会阻塞其余用户对同一表的读和写操作；

简而言之，就是读锁会阻塞写，然而不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

此外，MyISAM 的读写锁调度是写优先，这也是 MyISAM 不适宜做写为主的表的存储引擎的起因。因为写锁后，其余线程不能做任何操作，大量的更新会使查问很难失去锁，从而造成永远阻塞。

5.4.5 查看锁的争用状况

show open tables；

In_user : 表以后被查问应用的次数。如果该数为零，则表是关上的，然而以后没有被应用。

Name_locked：表名称是否被锁定。名称锁定用于勾销表或对表进行重命名等操作。

show status like 'Table_locks%';

Table_locks_immediate：指的是可能立刻取得表级锁的次数，每立刻获取锁，值加 1。

Table_locks_waited：指的是不能立刻获取表级锁而须要期待的次数，每期待一次，该值加 1，此值高阐明存在着较为重大的表级锁争用状况。

5.5 InnoDB 行锁

5.5.1 行锁介绍

行锁特点：偏差 InnoDB 存储引擎，开销大，加锁慢；会呈现死锁；锁定粒度最小，产生锁抵触的概率最低, 并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：一是反对事务；二是 采纳了行级锁。

5.5.2 背景常识

事务及其 ACID 属性

事务是由一组 SQL 语句组成的逻辑处理单元。

事务具备以下 4 个个性，简称为事务 ACID 属性。

并发事务处理带来的问题

事务隔离级别

为了解决上述提到的事务并发问题，数据库提供肯定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离本质上就是应用事务在肯定水平上“串行化”进行，这显然与“并发”是矛盾的。

数据库的隔离级别有 4 个，由低到高顺次为 Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别能够一一解决脏写、脏读、不可反复读、幻读这几类问题。

备注：√ 代表可能呈现，× 代表不会呈现。

Mysql 的数据库的默认隔离级别为 Repeatable read，查看形式：

show variables like 'tx_isolation';

5.5.3 InnoDB 的行锁模式

InnoDB 实现了以下两种类型的行锁。

• 共享锁（S）：又称为读锁，简称 S 锁，共享锁就是多个事务对于同一数据能够共享一把锁，都能拜访到数据，然而只能读不能批改。
• 排他锁（X）：又称为写锁，简称 X 锁，排他锁就是不能与其余锁并存，如一个事务获取了一个数据行的排他锁，其余事务就不能再获取该行的其余锁，包含共享锁和排他锁，然而获取排他锁的事务是能够对数据就行读取和批改。

对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会主动给波及数据集加排他锁（X)；

对于一般 SELECT 语句，InnoDB 不会加任何锁；

能够通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE（乐观锁）

乐观锁和乐观锁

乐观锁：事务必须排队执行。数据锁住了，不容许并发。（行级锁：select 前面增加 for update）

乐观锁：反对并发，事务也不须要排队，只不过须要一个版本号。

5.5.4 案例筹备工作

create table test_innodb_lock(id int(11),
    name varchar(16),
    sex varchar(1)
)engine = innodb default charset=utf8;

insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');

create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

5.5.5 行锁根本演示

如果执行了更新语句，会对这一行数据加上排他锁（写锁），提交 commit 之后，会开释锁。另外一个线程 update 语句才能够执行解除阻塞状态。前提是两个线程操作同一行数据。

5.5.6 无索引行锁降级为表锁

如果不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，实际效果跟表锁一样。

查看以后表的索引：show index from test_innodb_lock ;

因为 执行更新时，name 字段原本为 varchar 类型，咱们是作为数组类型应用，存在类型转换，索引生效，最终行锁变为表锁；(字符串类型，在 SQL 语句应用的时候没有加单引号，导致索引生效，查问没有走索引，进行全表扫描是，索引生效，行锁就降级为表锁)

5.5.7 间隙锁危害

当咱们用范畴条件，而不是应用相等条件检索数据，并申请共享或排他锁时，InnoDB 会给符合条件的已有数据进行加锁；对于键值在条件范畴内但并不存在的记录，叫做 “ 间隙（GAP）”，InnoDB 也会对这个 “ 间隙 ” 加锁，这种锁机制就是所谓的 间隙锁（Next-Key 锁）。

示例：

怎么防止间隙锁呢？

在更新的时候，或者对数据行进行加锁的时候，尽量去放大条件，使得间隙数据尽量的少，最大水平防止间隙锁的存在。

5.5.8 InnoDB 行锁争用状况

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: 系统启动后到当初总共期待的次数


当期待的次数很高，而且每次期待的时长也不小的时候，咱们就须要剖析零碎中为什么会有如此多的期待，而后依据剖析后果着手制订优化打算。

5.5.9 总结

InnoDB 存储引擎因为实现了行级锁定，尽管在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，然而在整体并发解决能力方面要远远因为 MyISAM 的表锁的。当零碎并发量较高的时候，InnoDB 的整体性能和 MyISAM 相比就会有比拟显著的劣势。

然而，InnoDB 的行级锁同样也有其软弱的一面，当咱们使用不当的时候，可能会让 InnoDB 的整体性能体现不仅不能比 MyISAM 高，甚至可能会更差。

优化倡议：

• 尽可能让所有数据检索都能通过索引来实现，防止无索引行锁降级为表锁。
• 正当设计索引，尽量放大锁的范畴
• 尽可能减少索引条件，及索引范畴，防止间隙锁
• 尽量管制事务大小，缩小锁定资源量和工夫长度
• 尽可应用低级别事务隔离（然而须要业务层面满足需要）

6. 罕用 SQL 技巧

6.1 SQL 执行程序

编写程序

SELECT DISTINCT
    <select list>
FROM
    <left_table> <join_type>
JOIN
    <right_table> ON <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT
    <limit_params>

执行程序

FROM    <left_table>

ON         <join_condition>

<join_type>        JOIN    <right_table>

WHERE        <where_condition>

GROUP BY     <group_by_list>

HAVING        <having_condition>

SELECT DISTINCT        <select list>

ORDER BY    <order_by_condition>

LIMIT        <limit_params>

6.2 正则表达式应用

正则表达式（Regular Expression）是指一个用来形容或者匹配一系列合乎某个句法规定的字符串的单个字符串。

select * from emp where name regexp '^T';

select * from emp where name regexp '2$';

select * from emp where name regexp '[uvw]';

6.3 MySQL 罕用函数

数字函数

字符串函数

日期函数

聚合函数

Mysql 高级 -day04

1. MySql 中常用工具

1.1 mysql

该 mysql 不是指 mysql 服务，而是指 mysql 的客户端工具。

语法：

mysql [options] [database]

1.1.1 连贯选项

参数：-u, --user=name            指定用户名
    -p, --password[=name]    指定明码
    -h, --host=name            指定服务器 IP 或域名
    -P, --port=#            指定连贯端口

示例：mysql -h 127.0.0.1 -P 3306 -u root -p
    
    mysql -h127.0.0.1 -P3306 -uroot -p2143  // 都不加空格也能够
    

1.1.2 执行选项

-e, --execute=name        执行 SQL 语句并退出

此选项能够在 Mysql 客户端执行 SQL 语句，而不必连贯到 MySQL 数据库再执行，对于一些批处理脚本，这种形式尤其不便。

示例：mysql -uroot -p2143 db01 -e "select * from tb_book";

1.2 mysqladmin

mysqladmin 是一个执行治理操作的客户端程序。能够用它来查看服务器的配置和以后状态、创立并删除数据库等。

能够通过：mysqladmin –help 指令查看帮忙文档

示例：mysqladmin -uroot -p2143 create 'test01';  
    mysqladmin -uroot -p2143 drop 'test01';
    mysqladmin -uroot -p2143 version;
    

1.3 mysqlbinlog

因为服务器生成的二进制日志文件以二进制格局保留，所以如果想要查看这些文本的文本格式，就会应用到 mysqlbinlog 日志管理工具。

语法：

mysqlbinlog [options]  log-files1 log-files2 ...

选项：-d, --database=name : 指定数据库名称，只列出指定的数据库相干操作。-o, --offset=# : 疏忽掉日志中的前 n 行命令。-r,--result-file=name : 将输入的文本格式日志输入到指定文件。-s, --short-form : 显示简略格局，省略掉一些信息。--start-datatime=date1  --stop-datetime=date2 : 指定日期距离内的所有日志。--start-position=pos1 --stop-position=pos2 : 指定地位距离内的所有日志。

1.4 mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁徙。备份内容蕴含创立表，及插入表的 SQL 语句。

语法：

mysqldump [options] db_name [tables]

mysqldump [options] --database/-B db1 [db2 db3...]

mysqldump [options] --all-databases/-A

1.4.1 连贯选项

参数：-u, --user=name            指定用户名
    -p, --password[=name]    指定明码
    -h, --host=name            指定服务器 IP 或域名
    -P, --port=#            指定连贯端口

1.4.2 输入内容选项

参数：--add-drop-database        在每个数据库创立语句前加上 Drop database 语句
    --add-drop-table        在每个表创立语句前加上 Drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
    
    -n, --no-create-db        不蕴含数据库的创立语句
    -t, --no-create-info    不蕴含数据表的创立语句
    -d --no-data            不蕴含数据
    
     -T, --tab=name            主动生成两个文件：一个.sql 文件，创立表构造的语句；一个.txt 文件，数据文件，相当于 select into outfile  

示例：mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a
    
mysqldump -uroot -p2143 -T /tmp test city  -- 将 test 数据库中 city 表输入到 tem 目录下，生成两个文件，一个.sql 文件一个.txt 文件

1.5 mysqlimport/source

mysqlimport 是客户端数据导入工具，用来导入 mysqldump 加 -T 参数后导出的文本文件。

语法：

mysqlimport [options]  db_name  textfile1  [textfile2...]

示例：

mysqlimport -uroot -p2143 test /tmp/city.txt

如果须要导入 sql 文件, 能够应用 mysql 中的 source 指令 :

source /root/tb_book.sql

1.6 mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

语法：

mysqlshow [options] [db_name [table_name [col_name]]]

参数：

--count        显示数据库及表的统计信息（数据库，表 均能够不指定）-i            显示指定数据库或者指定表的状态信息

示例：

# 查问每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p2143 --count

#查问 test 库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count

#查问 test 库中 book 表的详细情况
mysqlshow -uroot -p2143 test book --count

#如果将以上 count 换成 i，则显示详细信息

2. Mysql 日志

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮忙数据库管理员追踪数据库已经产生过的各种事件。MySQL 也不例外，在 MySQL 中，有 4 种不同的日志，别离是 谬误日志、二进制日志（BINLOG 日志）、查问日志和慢查问日志，这些日志记录着数据库在不同方面的形迹。

2.1 谬误日志

谬误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和进行时，以及服务器在运行过程中产生任何严重错误时的相干信息。当数据库呈现任何故障导致无奈失常应用时，能够首先查看此日志。

该日志是默认开启的 ，默认寄存目录为 mysql 的数据目录（var/lib/mysql）, 默认的日志文件名为 hostname.err（hostname 是主机名）。

查看日志地位指令：

show variables like 'log_error%';

查看日志内容：

tail -f /var/lib/mysql/xaxh-server.err

2.2 二进制日志

2.2.1 概述

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，然而不包含数据查问语句。此日志对于劫难时的数据恢复起着极其重要的作用，MySQL 的主从复制，就是通过该 binlog 实现的。

二进制日志，默认状况下是没有开启的，须要到 MySQL 的配置文件中开启，并配置 MySQL 日志的格局。

配置文件地位 : /usr/my.cnf

日志寄存地位 : 配置时，给定了文件名然而没有指定门路，日志默认写入 Mysql 的数据目录。

# 配置开启 binlog 日志，日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格局
binlog_format=STATEMENT

2.2.2 日志格局

STATEMENT

该日志格局在日志文件中记录的都是 SQL 语句（statement），每一条对数据进行批改的 SQL 都会记录在日志文件中，通过 Mysql 提供的 mysqlbinlog 工具，能够清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库从新执行一次。

ROW

该日志格局在日志文件中记录的是每一行的数据变更，而不是记录 SQL 语句。比方，执行 SQL 语句：update tb_book set status=’1′ , 如果是 STATEMENT 日志格局，在日志中会记录一行 SQL 文件；如果是 ROW，因为是对全表进行更新，也就是每一行记录都会产生变更，ROW 格局的日志中会记录每一行的数据变更。

MIXED

这是目前 MySQL 默认的日志格局，即混合了 STATEMENT 和 ROW 两种格局。默认状况下采纳 STATEMENT，然而在一些非凡状况下采纳 ROW 来进行记录。MIXED 格局能尽量利用两种模式的长处，而避开他们的毛病。

2.2.3 日志读取

因为日志以二进制形式存储，不能间接读取，须要用 mysqlbinlog 工具来查看，语法如下：

mysqlbinlog log-file；

查看 STATEMENT 格局日志

执行插入语句：

insert into tb_book values(null,'Lucene','2088-05-01','0');

查看日志文件：

mysqlbin.index : 该文件是日志索引文件，记录日志的文件名；

mysqlbing.000001：日志文件

查看日志内容：

mysqlbinlog mysqlbing.000001；

查看 ROW 格局日志

配置 :

# 配置开启 binlog 日志，日志的文件前缀为 mysqlbin -----> 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin

#配置二进制日志的格局
binlog_format=ROW

插入数据 :

insert into tb_book values(null,'SpringCloud 实战','2088-05-05','0');

如果日志格局是 ROW , 间接查看数据 , 是查看不懂的 ; 能够在 mysqlbinlog 前面加上参数 -vv

mysqlbinlog -vv mysqlbin.000002 

2.2.4 日志删除

对于比拟忙碌的零碎，因为每天生成日志量大，这些日志如果长时间不分明，将会占用大量的磁盘空间。上面咱们将会解说几种删除日志的常见办法：

形式一

通过 Reset Master 指令删除全副 binlog 日志，删除之后，日志编号，将从 xxxx.000001 从新开始。

查问之前，先查问下日志文件：

执行删除日志指令：

Reset Master

执行之后，查看日志文件：

形式二

执行指令 purge master logs to 'mysqlbin.******'，该命令将删除 ****** 编号之前的所有日志。

形式三

执行指令 purge master logs before 'yyyy-mm-dd hh24:mi:ss'，该命令将删除日志为 “yyyy-mm-dd hh24:mi:ss” 之前产生的所有日志。

形式四

设置参数 –expire_logs_days=#，此参数的含意是设置日志的过期天数，过了指定的天数后日志将会被主动删除，这样将有利于缩小 DBA 治理日志的工作量。

配置如下：

2.3 查问日志

查问日志中记录了客户端的所有操作语句，而二进制日志不蕴含查问数据的 SQL 语句。

默认状况下，查问日志是未开启的。如果须要开启查问日志，能够设置以下配置：

# 该选项用来开启查问日志，可选值：0 或者 1；0 代表敞开，1 代表开启 
general_log=1

#设置日志的文件名，如果没有指定，默认的文件名为 host_name.log 
general_log_file=file_name

在 mysql 的配置文件 /usr/my.cnf 中配置如下内容：

配置结束之后，在数据库执行以下操作：

select * from tb_book;
select * from tb_book where id = 1;
update tb_book set name = 'lucene 入门指南' where id = 5;
select * from tb_book where id < 8;

执行结束之后，再次来查问日志文件：

2.4 慢查问日志

慢查问日志记录了所有执行工夫超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的 SQL 语句的日志。long_query_time 默认为 10 秒，最小为 0，精度能够到微秒。

2.4.1 文件地位和格局

慢查问日志默认是敞开的。能够通过两个参数来管制慢查问日志：

# 该参数用来管制慢查问日志是否开启，可取值：1 和 0，1 代表开启，0 代表敞开
slow_query_log=1 

# 该参数用来指定慢查问日志的文件名
slow_query_log_file=slow_query.log

# 该选项用来配置查问的工夫限度，超过这个工夫将认为值慢查问，将须要进行日志记录，默认 10s
long_query_time=10

2.4.2 日志的读取

和谬误日志、查问日志一样，慢查问日志记录的格局也是纯文本，能够被间接读取。

1）查问 long_query_time 的值。

2）执行查问操作

select id, title,price,num ,status from tb_item where id = 1;

因为该语句执行工夫很短，为 0s，所以不会记录在慢查问日志中。

select * from tb_item where title like '% 阿尔卡特 (OT-927) 炭黑 联通 3G 手机 双卡双待 165454%' ;

该 SQL 语句，执行时长为 26.77s，超过 10s，所以会记录在慢查问日志文件中。

3）查看慢查问日志文件

间接通过 cat 指令查问该日志文件：

如果慢查问日志内容很多，间接查看文件，比拟麻烦，这个时候能够借助于 mysql 自带的 mysqldumpslow 工具，来对慢查问日志进行分类汇总。

3. Mysql 复制

3.1 复制概述

复制是指将主数据库的 DDL 和 DML 操作通过 二进制日志 传到从库服务器中，而后在从库上对这些日志从新执行（也叫重做），从而使得从库和主库的数据放弃同步。

MySQL 反对一台主库同时向多台从库进行复制，从库同时也能够作为其余从服务器的主库，实现链状复制。

3.2 复制原理

MySQL 的主从复制原理如下。

从下层来看，复制分成三步：

• Master 主库在事务提交时，会把数据变更作为工夫 Events 记录在二进制日志文件 Binlog 中。
• 主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log。
• slave 重做中继日志中的事件，将扭转反映它本人的数据。

3.3 复制劣势

MySQL 复制的有点次要蕴含以下三个方面：

• 主库呈现问题，能够疾速切换到从库提供服务。
• 能够在从库上执行查问操作，从主库中更新，实现读写拆散，升高主库的拜访压力。
• 能够在从库中执行备份，以防止备份期间影响主库的服务。

3.4 搭建步骤

3.4.1 master

1）在 master 的配置文件（/usr/my.cnf）中，配置如下内容：

#mysql 服务 ID, 保障整个集群环境中惟一
server-id=1

#mysql binlog 日志的存储门路和文件名
log-bin=/var/lib/mysql/mysqlbin

#谬误日志, 默认曾经开启
#log-err

#mysql 的装置目录
#basedir

#mysql 的长期目录
#tmpdir

#mysql 的数据寄存目录
#datadir

#是否只读,1 代表只读, 0 代表读写
read-only=0

#疏忽的数据, 指不须要同步的数据库
binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=db01

2）执行结束之后，须要重启 Mysql：

service mysql restart；

3）创立同步数据的账户，并且进行受权操作：为了实现主从复制

grant replication slave on *.* to 'itcast'@'192.168.192.131' identified by 'itcast';    

flush privileges;

-- 主服务器的 ip 是 192.168.192.130
-- 从服务器的 ip 是 192.168.192.131

4）查看 master 状态：

show master status;

字段含意：

File : 从哪个日志文件开始推送日志文件 
Position：从哪个地位开始推送日志
Binlog_Ignore_DB : 指定不须要同步的数据库

3.4.2 slave

1）在 slave 端配置文件中，配置如下内容：

#mysql 服务端 ID, 惟一
server-id=2

#指定 binlog 日志
log-bin=/var/lib/mysql/mysqlbin

2）执行结束之后，须要重启 Mysql：

service mysql restart；

3）执行如下指令：

change master to master_host= '192.168.192.130', master_user='itcast', master_password='itcast', master_log_file='mysqlbin.000001', master_log_pos=413;

-- 指定主节点的 ip，账户，明码，二进制日志文件名称，以及从哪一行开始同步

指定以后从库对应的主库的 IP 地址，用户名，明码，从哪个日志文件开始的那个地位开始同步推送日志。

4）开启同步操作

start slave;

show slave status \G;

5）进行同步操作

stop slave;

3.4.3 验证同步操作

1）在主库中创立数据库，创立表，并插入数据：

create database db01;

user db01;

create table user(id int(11) not null auto_increment,
    name varchar(50) not null,
    sex varchar(1),
    primary key (id)
)engine=innodb default charset=utf8;

insert into user(id,name,sex) values(null,'Tom','1');
insert into user(id,name,sex) values(null,'Trigger','0');
insert into user(id,name,sex) values(null,'Dawn','1');

2）在从库中查问数据，进行验证：

在从库中，能够查看到方才创立的数据库：

在该数据库中，查问 user 表中的数据：

4. 综合案例

4.1 需要剖析

在业务零碎中，须要记录以后业务零碎的拜访日志，该拜访日志蕴含：操作人，操作工夫，拜访类，拜访办法，申请参数，申请后果，申请后果类型，申请时长 等信息。记录具体的零碎拜访日志，次要便于对系统中的用户申请进行追踪，并且在零碎 的治理后盾能够查看到用户的拜访记录。

记录零碎中的日志信息，能够通过 Spring 框架的 AOP 来实现。具体的申请解决流程，如下：

4.2 搭建案例环境

4.2.1 数据库表

CREATE DATABASE mysql_demo DEFAULT CHARACTER SET utf8mb4 ;

CREATE TABLE `brand` (`id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '品牌名称',
  `first_char` varchar(1) DEFAULT NULL COMMENT '品牌首字母',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `item` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品 id',
  `title` varchar(100) NOT NULL COMMENT '商品题目',
  `price` double(10,2) NOT NULL COMMENT '商品价格，单位为：元',
  `num` int(10) NOT NULL COMMENT '库存数量',
  `categoryid` bigint(10) NOT NULL COMMENT '所属类目，叶子类目',
  `status` varchar(1) DEFAULT NULL COMMENT '商品状态，1- 失常，2- 下架，3- 删除',
  `sellerid` varchar(50) DEFAULT NULL COMMENT '商家 ID',
  `createtime` datetime DEFAULT NULL COMMENT '创立工夫',
  `updatetime` datetime DEFAULT NULL COMMENT '更新工夫',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';



CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `operation_log` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `operate_class` varchar(200) DEFAULT NULL COMMENT '操作类',
  `operate_method` varchar(200) DEFAULT NULL COMMENT '操作方法',
  `return_class` varchar(200) DEFAULT NULL COMMENT '返回值类型',
  `operate_user` varchar(20) DEFAULT NULL COMMENT '操作用户',
  `operate_time` varchar(20) DEFAULT NULL COMMENT '操作工夫',
  `param_and_value` varchar(500) DEFAULT NULL COMMENT '申请参数名及参数值',
  `cost_time` bigint(20) DEFAULT NULL COMMENT '执行办法耗时, 单位 ms',
  `return_value` varchar(200) DEFAULT NULL COMMENT '返回值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

4.2.2 pom.xml

<properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <maven.compiler.source>1.7</maven.compiler.source>
  <maven.compiler.target>1.7</maven.compiler.target>

  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <maven.compiler.source>1.8</maven.compiler.source>
  <maven.compiler.target>1.8</maven.compiler.target>
  <spring.version>5.0.2.RELEASE</spring.version>
  <slf4j.version>1.6.6</slf4j.version>
  <log4j.version>1.2.12</log4j.version>
  <mybatis.version>3.4.5</mybatis.version>
</properties>

<dependencies> <!-- spring -->
  <dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.6.8</version>
  </dependency>

  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.16</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context-support</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>${spring.version}</version>
  </dependency>

  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
  </dependency>

  <dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>3.1.0</version>
    <scope>provided</scope>
  </dependency>

  <dependency>
    <groupId>javax.servlet.jsp</groupId>
    <artifactId>jsp-api</artifactId>
    <version>2.0</version>
    <scope>provided</scope>
  </dependency>


  <dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>${log4j.version}</version>
  </dependency>

  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>${mybatis.version}</version>
  </dependency>

  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.0</version>
  </dependency>

  <dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.2</version>
  </dependency>

  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.5</version>
  </dependency>

  <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-core</artifactId>
    <version>2.9.0</version>
  </dependency>

  <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.9.0</version>
  </dependency>

  <dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-annotations</artifactId>
    <version>2.9.0</version>
  </dependency>
</dependencies>




 <build>
   <plugins>
     <plugin>
       <groupId>org.apache.tomcat.maven</groupId>
       <artifactId>tomcat7-maven-plugin</artifactId>
       <version>2.2</version>
       <configuration>
         <port>8080</port>
         <path>/</path>
         <uriEncoding>utf-8</uriEncoding>
       </configuration>
     </plugin>
   </plugins>
 </build>

4.2.3 web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee"
       xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
       version="2.5">

    <!-- 解决 post 乱码 -->
    <filter>
        <filter-name>CharacterEncodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>utf-8</param-value>
        </init-param>
        <init-param>
            <param-name>forceEncoding</param-name>
            <param-value>true</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>CharacterEncodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:applicationContext.xml</param-value>
    </context-param>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>


    <servlet>
        <servlet-name>springmvc</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <!-- 指定加载的配置文件，通过参数 contextConfigLocation 加载 -->
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>classpath:springmvc.xml</param-value>
        </init-param>
    </servlet>
    <servlet-mapping>
        <servlet-name>springmvc</servlet-name>
        <url-pattern>*.do</url-pattern>
    </servlet-mapping>

    <welcome-file-list>
      <welcome-file>log-datalist.html</welcome-file>
    </welcome-file-list>
</web-app>

4.2.4 db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.142.128:3306/mysql_demo
jdbc.username=root
jdbc.password=itcast

4.2.5 applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
                            http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
                            http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <!-- 加载配置文件 -->
    <context:property-placeholder location="classpath:db.properties"/>

    <!-- 配置 spring 创立容器时要扫描的包 -->
    <context:component-scan base-package="cn.itcast">
        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller">    
        </context:exclude-filter>
    </context:component-scan>

    <!-- 配置 MyBatis 的 Session 工厂 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="typeAliasesPackage" value="cn.itcast.pojo"/>
     </bean>

    <!-- 配置数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <!-- 配置 Mapper 扫描器 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.itcast.mapper"/>
    </bean>

    <!-- 配置事务管理器 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- 配置事务的注解驱动 -->
    <tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
</beans>

4.2.6 springmvc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/mvc
            http://www.springframework.org/schema/mvc/spring-mvc.xsd
            http://www.springframework.org/schema/aop
            http://www.springframework.org/schema/aop/spring-aop.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context.xsd">

    <context:component-scan base-package="cn.itcast.controller"></context:component-scan>

    <mvc:annotation-driven></mvc:annotation-driven>

    <aop:aspectj-autoproxy />

</beans>

4.2.7 导入根底工程

4.3 通过 AOP 记录操作日志

4.3.1 自定义注解

通过自定义注解，来标示办法需不需要进行记录日志，如果该办法在拜访时须要记录日志，则在该办法上标示该注解既可。

@Inherited
@Documented
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface OperateLog {}

4.3.2 定义告诉类

@Component
@Aspect
public class OperateAdvice {private static Logger log = Logger.getLogger(OperateAdvice.class);
   
   @Autowired
   private OperationLogService operationLogService;
   

   @Around("execution(* cn.itcast.controller.*.*(..)) && @annotation(operateLog)")
   public Object insertLogAround(ProceedingJoinPoint pjp , OperateLog operateLog) throws Throwable{System.out.println("************************ 记录日志 [start]  ******************************");
      
      OperationLog op = new OperationLog();
      
      DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

      op.setOperateTime(sdf.format(new Date()));
      op.setOperateUser(DataUtils.getRandStr(8));
      
      op.setOperateClass(pjp.getTarget().getClass().getName());
      op.setOperateMethod(pjp.getSignature().getName());
      
      // 获取办法调用时传递的参数
      Object[] args = pjp.getArgs();
      op.setParamAndValue(Arrays.toString(args));

      long start_time = System.currentTimeMillis();

      // 放行
      Object object = pjp.proceed();

      long end_time = System.currentTimeMillis();
      op.setCostTime(end_time - start_time);

      if(object != null){op.setReturnClass(object.getClass().getName());
         op.setReturnValue(object.toString());
      }else{op.setReturnClass("java.lang.Object");
         op.setParamAndValue("void");
      }

      log.error(JsonUtils.obj2JsonString(op));

      operationLogService.insert(op);
      
      System.out.println("************************** 记录日志 [end]  ***************************");
      
      return object;
   }
   
}

4.3.3 办法上加注解

在须要记录日志的办法上加上注解 @OperateLog。

@OperateLog
@RequestMapping("/insert")
public Result insert(@RequestBody Brand brand){
    try {brandService.insert(brand);
        return new Result(true,"操作胜利");
    } catch (Exception e) {e.printStackTrace();
        return new Result(false,"操作失败");
    }
}

4.4 日志查问后端代码实现

4.4.1 Mapper 接口

public interface OperationLogMapper {public void insert(OperationLog operationLog);

    public List<OperationLog> selectListByCondition(Map dataMap);

    public Long countByCondition(Map dataMap);

}

4.4.2 Mapper.xml 映射配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.itcast.mapper.OperationLogMapper" >

    <insert id="insert" parameterType="operationLog">
        INSERT INTO operation_log(id,return_value,return_class,operate_user,operate_time,param_and_value,
        operate_class,operate_method,cost_time)
      VALUES(NULL,#{returnValue},#{returnClass},#{operateUser},#{operateTime},#{paramAndValue},
        #{operateClass},#{operateMethod},#{costTime})
    </insert>

    <select id="selectListByCondition" parameterType="map" resultType="operationLog">
      select
        id ,
        operate_class as operateClass ,
        operate_method as operateMethod,
        return_class as returnClass,
        operate_user as operateUser,
        operate_time as operateTime,
        param_and_value as paramAndValue,
        cost_time as costTime,
        return_value as returnValue
      from operation_log
      <include refid="oplog_where"/>
      limit #{start},#{size}
    </select>


    <select id="countByCondition" resultType="long" parameterType="map">
        select count(*) from operation_log
        <include refid="oplog_where"/>
    </select>


    <sql id="oplog_where">
        <where>
            <if test="operateClass != null and operateClass !='' ">
                and operate_class = #{operateClass}
            </if>
            <if test="operateMethod != null and operateMethod !='' ">
                and operate_method = #{operateMethod}
            </if>
            <if test="returnClass != null and returnClass !='' ">
                and return_class = #{returnClass}
            </if>
            <if test="costTime != null">
                and cost_time =  #{costTime}
            </if>
        </where>
    </sql>

</mapper>

4.4.3 Service

@Service
@Transactional
public class OperationLogService {//private static Logger logger = Logger.getLogger(OperationLogService.class);

    @Autowired
    private OperationLogMapper operationLogMapper;

    // 插入数据
    public void insert(OperationLog operationLog){operationLogMapper.insert(operationLog);
    }

    // 依据条件查问
    public PageResult selectListByCondition(Map dataMap, Integer pageNum , Integer pageSize){if(paramMap ==null){paramMap = new HashMap();
        }
        paramMap.put("start" , (pageNum-1)*rows);
        paramMap.put("rows",rows);

        Object costTime = paramMap.get("costTime");
        if(costTime != null){if("".equals(costTime.toString())){paramMap.put("costTime",null);
            }else{paramMap.put("costTime",new Long(paramMap.get("costTime").toString()));
            }
        }

        System.out.println(dataMap);


        long countStart = System.currentTimeMillis();
        Long count = operationLogMapper.countByCondition(dataMap);
        long countEnd = System.currentTimeMillis();
        System.out.println("Count Cost Time :" + (countEnd-countStart)+"ms");


        List<OperationLog> list = operationLogMapper.selectListByCondition(dataMap);
        long queryEnd = System.currentTimeMillis();
        System.out.println("Query Cost Time :" + (queryEnd-countEnd)+"ms");


        return new PageResult(count,list);

    }

@RestController
@RequestMapping("/operationLog")
public class OperationLogController {

    @Autowired
    private OperationLogService operationLogService;

    @RequestMapping("/findList")
    public PageResult findList(@RequestBody Map dataMap, Integer pageNum , Integer pageSize){PageResult page = operationLogService.selectListByCondition(dataMap, pageNum, pageSize);
        return page;
    }

}

4.5 日志查问前端代码实现

前端代码应用 BootStrap + AdminLTE 进行布局，应用 Vuejs 进行视图层展现。

4.5.1 js

<script>
   var vm = new Vue({
       el: '#app',
       data: {dataList:[],
           searchEntity:{
               operateClass:'',
               operateMethod:'',
               returnClass:'',
               costTime:''
           },

           page: 1,  // 显示的是哪一页
           pageSize: 10, // 每一页显示的数据条数
           total: 150, // 记录总数
           maxPage:8  // 最大页数
       },
       methods: {pageHandler: function (page) {
               this.page = page;
               this.search();},

           search: function () {
               var _this = this;
               this.showLoading();
               axios.post('/operationLog/findList.do?pageNum=' + _this.page + "&pageSize=" + _this.pageSize, _this.searchEntity).then(function (response) {if (response) {
                       _this.dataList = response.data.dataList;
                       _this.total = response.data.total;
                       _this.hideLoading();}
               })
           },

           showLoading: function () {$('#loadingModal').modal({backdrop: 'static', keyboard: false});
           },

           hideLoading: function () {$('#loadingModal').modal('hide');
           },
       },

       created:function(){this.pageHandler(1);
       }
   });

</script>

4.5.2 列表数据展现

<tr v-for="item in dataList">
    <td><input name="ids" type="checkbox"></td>
    <td>{{item.id}}</td>
    <td>{{item.operateClass}}</td>
    <td>{{item.operateMethod}}</td>
    <td>{{item.returnClass}}</td>
    <td>{{item.returnValue}}</td>
    <td>{{item.operateUser}}</td>
    <td>{{item.operateTime}}</td>
    <td>{{item.costTime}}</td>
    <td class="text-center">
        <button type="button" class="btn bg-olive btn-xs"> 详情 </button>
        <button type="button" class="btn bg-olive btn-xs"> 删除 </button>
    </td>
</tr>

4.5.3 分页插件

<div class="wrap" id="wrap">
    <zpagenav v-bind:page="page" v-bind:page-size="pageSize" v-bind:total="total"
              v-bind:max-page="maxPage"  v-on:pagehandler="pageHandler">
    </zpagenav>
</div>

4.6 联调测试

能够通过 postman 来拜访业务零碎，再查看数据库中的日志信息，验证能不能将用户的拜访日志记录下来。

4.7 剖析性能问题

零碎中用户拜访日志的数据量，随着工夫的推移，这张表的数据量会越来越大，因而咱们须要依据业务需要，来对日志查问模块的性能进行优化。

1）分页查问优化

因为在进行日志查问时，是进行分页查问，那也就意味着，在查看时，至多须要查问两次：

A. 查问符合条件的总记录数。–> count 操作

B. 查问符合条件的列表数据。–> 分页查问 limit 操作

通常来说，count() 都须要扫描大量的行（意味着须要拜访大量的数据）能力取得准确的后果，因而是很难对该 SQL 进行优化操作的。如果须要对 count 进行优化，能够采纳另外一种思路，能够减少汇总表，或者 redis 缓存来专门记录该表对应的记录数，这样的话，就能够很轻松的实现汇总数据的查问，而且效率很高，然而这种统计并不能保障百分之百的精确。对于数据库的操作，“疾速、准确、实现简略”，三者永远只能满足其二，必须舍掉其中一个。

2）条件查问优化

针对于条件查问, 须要对查问条件, 及排序字段建设索引。

3）读写拆散

通过主从复制集群，来实现读写拆散，使写操作走主节点，而读操作，走从节点。

4）MySQL 服务器优化

5）利用优化

4.8 性能优化 – 分页

4.8.1 优化 count

创立一张表用来记录日志表的总数据量：

create table log_counter(logcount bigint not null)engine = innodb default CHARSET = utf8;

在每次插入数据之后，更新该表：

<update id="updateLogCounter" >
    update log_counter set logcount = logcount + 1
</update>

在进行分页查问时, 获取总记录数，从该表中查问既可。

<select id="countLogFromCounter" resultType="long">
    select logcount from log_counter limit 1
</select>

4.8.2 优化 limit

在进行分页时，个别通过创立笼罩索引，可能比拟好的进步性能。一个十分常见，而又十分头疼的分页场景就是 “limit 1000000,10”，此时 MySQL 须要搜寻出前 1000010 条记录后，仅仅须要返回第 1000001 到 1000010 条记录，前 1000000 记录会被摈弃，查问代价十分大。

当点击比拟靠后的页码时，就会呈现这个问题，查问效率十分慢。

优化 SQL：

select * from operation_log limit 3000000 , 10;

将上述 SQL 优化为 :

select * from operation_log t , (select id from operation_log order by id limit 3000000,10) b where t.id = b.id ;
<select id="selectListByCondition" parameterType="map" resultType="operationLog">
  select
    id ,
    operate_class as operateClass ,
    operate_method as operateMethod,
    return_class as returnClass,
    operate_user as operateUser,
    operate_time as operateTime,
    param_and_value as paramAndValue,
    cost_time as costTime,
    return_value as returnValue
  from operation_log t,
    
  (select id from operation_log 
  <where>
    <include refid="oplog_where"/>
  </where>
  order by id limit #{start},#{rows}) b  where t.id = b.id  
</select>

4.9 性能优化 – 索引

当依据操作人进行查问时，查问的效率很低，耗时比拟长。起因就是因为在创立数据库表构造时，并没有针对于 操作人 字段建设索引。

CREATE INDEX idx_user_method_return_cost ON operation_log(operate_user,operate_method,return_class,cost_time);

同上，为了查问效率高，咱们也须要对 操作方法、返回值类型、操作耗时 等字段进行创立索引，以进步查问效率。

CREATE INDEX idx_optlog_method_return_cost ON operation_log(operate_method,return_class,cost_time);

CREATE INDEX idx_optlog_return_cost ON operation_log(return_class,cost_time);

CREATE INDEX idx_optlog_cost ON operation_log(cost_time);

4.10 性能优化 – 排序

在查问数据时，如果业务需要中须要咱们对后果内容进行了排序解决 , 这个时候, 咱们还须要对排序的字段建设适当的索引, 来进步排序的效率。

4.11 性能优化 – 读写拆散

4.11.1 概述

在 Mysql 主从复制的根底上，能够应用读写拆散来升高单台 Mysql 节点的压力，从而来进步拜访效率，读写拆散的架构如下：

对于读写拆散的实现，能够通过 Spring AOP 来进行动静的切换数据源，进行操作：

4.11.2 实现形式

db.properties

# 写的主库
jdbc.write.driver=com.mysql.jdbc.Driver
jdbc.write.url=jdbc:mysql://192.168.142.128:3306/mysql_demo
jdbc.write.username=root
jdbc.write.password=itcast

#读的从库
jdbc.read.driver=com.mysql.jdbc.Driver
jdbc.read.url=jdbc:mysql://192.168.142.129:3306/mysql_demo
jdbc.read.username=root
jdbc.read.password=itcast

applicationContext-datasource.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">


    <!-- 配置数据源 - Read -->
    <bean id="readDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"  lazy-init="true">
        <property name="driverClass" value="${jdbc.read.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.read.url}"></property>
        <property name="user" value="${jdbc.read.username}"></property>
        <property name="password" value="${jdbc.read.password}"></property>
    </bean>


    <!-- 配置数据源 - Write -->
    <bean id="writeDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"  destroy-method="close"  lazy-init="true">
        <property name="driverClass" value="${jdbc.write.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.write.url}"></property>
        <property name="user" value="${jdbc.write.username}"></property>
        <property name="password" value="${jdbc.write.password}"></property>
    </bean>


    <!-- 配置动态分配的读写 数据源 --> <!-- 继承自 AbstractRoutingDataSource -->
    <bean id="dataSource" class="cn.itcast.aop.datasource.ChooseDataSource" lazy-init="true">
        <property name="targetDataSources">
            <map key-type="java.lang.String" value-type="javax.sql.DataSource">
                <entry key="write" value-ref="writeDataSource"/>
                <entry key="read" value-ref="readDataSource"/>
            </map>
        </property>

        <property name="defaultTargetDataSource" ref="writeDataSource"/>

        <property name="methodType">
            <map key-type="java.lang.String">
                <entry key="read" value=",get,select,count,list,query,find"/>
                <entry key="write" value=",add,create,update,delete,remove,insert"/>
            </map>
        </property>
    </bean>

</beans>

ChooseDataSource

public class ChooseDataSource extends AbstractRoutingDataSource {public static Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>();

    /**
     * 实现父类中的形象办法，获取数据源名称
     * @return
     */
    protected Object determineCurrentLookupKey() {return DataSourceHandler.getDataSource();
    }

    // 设置办法名前缀对应的数据源
    public void setMethodType(Map<String, String> map) {for (String key : map.keySet()) {List<String> v = new ArrayList<String>();
            String[] types = map.get(key).split(",");
            for (String type : types) {if (!StringUtils.isEmpty(type)) {v.add(type);
                }
            }
            METHOD_TYPE_MAP.put(key, v);
        }
        System.out.println("METHOD_TYPE_MAP :"+METHOD_TYPE_MAP);
    }
}

DataSourceHandler

public class DataSourceHandler {

    // 数据源名称
    public static final ThreadLocal<String> holder = new ThreadLocal<String>();

    /**
     * 在我的项目启动的时候将配置的读、写数据源加到 holder 中
     */
    public static void putDataSource(String datasource) {holder.set(datasource);
    }

    /**
     * 从 holer 中获取数据源字符串
     */
    public static String getDataSource() {return holder.get();
    }
}

DataSourceAspect

@Aspect
@Component
@Order(-9999)
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DataSourceAspect {protected Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 配置前置告诉, 应用在办法 aspect()上注册的切入点
     */
    @Before("execution(* cn.itcast.service.*.*(..))")
    @Order(-9999)
    public void before(JoinPoint point) {String className = point.getTarget().getClass().getName();
        String method = point.getSignature().getName();
        logger.info(className + "." + method + "(" + Arrays.asList(point.getArgs())+ ")");

        try {for (String key : ChooseDataSource.METHOD_TYPE_MAP.keySet()) {for (String type : ChooseDataSource.METHOD_TYPE_MAP.get(key)) {if (method.startsWith(type)) {System.out.println("key :" + key);
                        DataSourceHandler.putDataSource(key);
                        break;
                    }
                }
            }
        } catch (Exception e) {e.printStackTrace();
        }

    }
}

通过 @Order(-9999) 注解来管制事务管理器, 与该告诉类的加载程序 , 须要让告诉类 , 先加载 , 来断定应用哪个数据源 .

4.11.3 验证

在主库和从库中，执行如下 SQL 语句，来查看是否读的时候，从从库中读取；写入操作的时候，是否写入到主库。

show status like 'Innodb_rows_%' ;

4.11.4 原理

4.12 性能优化 – 利用优化

4.12.1 缓存

能够在业务零碎中应用 redis 来做缓存，缓存一些基础性的数据，来升高关系型数据库的压力，进步拜访效率。

4.12.2 全文检索

如果业务零碎中的数据量比拟大（达到千万级别），这个时候，如果再对数据库进行查问，特地是进行分页查问，速度将变得很慢（因为在分页时首先须要 count 求共计数），为了进步拜访效率，这个时候，能够思考退出 Solr 或者 ElasticSearch 全文检索服务，来进步拜访效率。

4.13.3 非关系数据库

也能够思考将非核心（重要）数据，存在 MongoDB 中，这样能够进步插入以及查问的效率。

Mysql 笔记 ERROR

一、The used command is not allowed with this MySQL version

1.1 无奈应用 LOAD DATA 语法上传数据

原本想将 txt/log 文件中的数据传入数据库，然而呈现报错：

谬误的起因是没有开启 local_infile 模块。

1.2 解决办法

首先看一下 local_infile 模块是否关上：

show global variables like 'local_infile';

显示如下：

而后能够发现这个模块曾经启用了：

而后还须要重启一下 Mysql，首先要退出 Mysql，而后从新登录 Mysql：

mysql --local-infile=1 -uroot -p123456

这个时候在上传数据能够上传了：

LOAD DATA LOCAL INFILE '文件地位' INTO TABLE pet;
load data local infile 'D:/Temp/sql1.log' into table `tb_user_2` fields terminated by ',' lines terminated by '\n' ;

1. … ↩