哔哩哔哩视频链接：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 --nodeps2). 上传 mysql 的安装包    alt + p -------> put  E:/test/MySQL-5.6.22-1.el6.i686.rpm-bundle.tar3). 解压 mysql 的安装包     mkdir mysql    tar -xvf MySQL-5.6.22-1.el6.i686.rpm-bundle.tar -C /root/mysql4). 装置依赖包     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_645). 装置 mysql-client    rpm -ivh MySQL-client-5.6.22-1.el6.i686.rpm6). 装置 mysql-server    rpm -ivh MySQL-server-5.6.22-1.el6.i686.rpm    

1.3 启动 MySQL 服务

service mysql startservice mysql stopservice mysql statusservice mysql restart#这里应用来开启systemctl start mysqld

1.4 登录MySQL

mysql 装置实现之后, 会主动生成一个随机的明码, 并且保留在一个密码文件中 : /root/.mysql_secretmysql -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 mysqlkill -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_conditionEND 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_listEND 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 intbegin  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/deleteon 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_triggerafter 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_triggerafter 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_triggerafter 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 > ALLsystem > 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 < 4TRACE: {  "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.000002log_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.000002log_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# 该选项用来配置查问的工夫限度， 超过这个工夫将认为值慢查问， 将须要进行日志记录， 默认10slong_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.Driverjdbc.url=jdbc:mysql://192.168.142.128:3306/mysql_demojdbc.username=rootjdbc.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@Aspectpublic 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@Transactionalpublic 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.Driverjdbc.write.url=jdbc:mysql://192.168.142.128:3306/mysql_demojdbc.write.username=rootjdbc.write.password=itcast#读的从库jdbc.read.driver=com.mysql.jdbc.Driverjdbc.read.url=jdbc:mysql://192.168.142.129:3306/mysql_demojdbc.read.username=rootjdbc.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. ... ↩