Mysql高级 索引 视图 存储过程和函数 触发器 引擎 SQL优化_数据库怎么利用视图索引 储存过程和触发器对数据库表进行优化sql

Mysql高级

第一章

1.Linux系统安装MySQL

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

登录
mysql -u root -p是本地的

记得关防火墙
攻略很多 https://www.cnblogs.com/winton-nfs/p/11524007.html

2.索引

2.1索引概述

索引（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版本开始支持全文索引
  
  我们平常所说的索引，如果没有特别指明，都是B+树（多路平衡搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，复合索引，前缀索引，唯一索引默认都是B+tree树索引，统称为索引。

2.3.1BTREE结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

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

以5×BTree为例，key的数量：公式推导[ceil(m/2)-1]<=n<=m1。索引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向上分裂

到此，该BYREE树就已经构建完成了，BTREE树与二叉树相比，查询数据的效率更高，因为相对于相同的数据量来说，BTREE的层级结构比二叉树更小，因此搜索速度更快

2.3.3B+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.3MySQL中的B+Tree

MySql索引数据结构对经典的B+tree进行了优化，在原来B+tree的基础上，增加一个指向相邻节点的链表指针，就形成了带有顺序指针的B+Tree，（便于范围搜索）提高区间访问的性能。

2.4索引分类

1. 单值索引：即一个索引包含单个列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，但允许有空值
3. 复合索引：即一个索引包含多个列

2.5索引语法

索引再创建表的时候，可以同时创建，也可以随时增加新的索引

环境准备

2.5.1 创建索引

create index index_name on table_name(index_col_name)

2.5.2 查看索引

show index from table_name
1

show index from xxx\G;

2.5.3 删除索引

drop index index_name on table_name
1

2.5.4ALTER命令

key是索引的名字

alter table table_name add primary key(column_list)
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL

alter table table_name add unique index_name(column_list)
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

alter table tb_name add index index_name（column_list）
添加普通索引，所以只可以出现多次

alter table table_name add fulltext index_name(column_list)
该语句指定了索引为FULLTEXT,用于全文索引

2.5索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升所有的使用效率，更高效的使用索引

• 对查询频次较高，且数据量比较大的表建立索引
• 所有字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用，过滤效果最好的列的组合
• 使用唯一索引，区分度越高，使用索引的效率越高
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代码自然也就水涨船高。对于插入，更新，删除等DML操作比较频繁的表来说，索引越多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MYSQL也会犯选择困难症，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。加入构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，响应的可以有效的提升MySQL访问索引的I/O效率
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率
  

3.视图

3.1视图概述

视图（View）是一种虚拟存在的表。视图并不在数据库中，行和列数据来自自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条select语句执行后返回的结果集，所以我们在创建视图的时候，主要的工作就落在建立这条SQL查询语句上。
视图相对于普通的表的优势主要包括以下几项。

• 简单：使用视图的用户完全不需要关心后面对应的表的结构，关联条件和筛选条件对用户来说已经是过滤好的，符合条件的结果集
• 安全：使用视图的用户只能访问，他们被允许查询的结果及对表的权限管理，并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化，对用户的影响，源表增加列对视图没有影响原表修改列名，则可以通过试修改视图来解决，不会造成对访问者的影响。

3.2创建或者修改视图

创建视图的语句为：

先把要的虚拟表先写出来

SELECT u.username,r.`name` FROM t_user u,t_user_role ur,t_role r WHERE u.id =ur.user_id AND ur.role_id =r.id AND u.id=1;

1
2

然后前面加create view 虚拟表名 as

CREATE VIEW view_user_role as SELECT u.username,r.`name` FROM t_user u,t_user_role ur,t_role r WHERE u.id =ur.user_id AND ur.role_id =r.id AND u.id=1;
1

看一眼

SELECT * FROM view_user_role;
1

简单修改数据

UPDATE view_user_role set name='XXX' WHERE username='admin'
1

修改视图的语法为:


CASCADED是默认选项

不建议更新视图
注意改变了视图数据,表中的数据也会发生改变!!!

3.3查看视图

show tables

可以看到具体执行的语句
show create view view_name

3.4删除视图

drop view view_name
drop view if exists view_name

4.存储过程和函数

4.1存储过程和函数概述

存储过程和函数是,事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值,而存储过程没有
函数:是一个有返回值的过程
过程:是一个没有返回值的函数

4.2创建存储过程

delimiter $
create procedure pro_tes1()
begin
     select 'hello sql'
end $

delimiter ;
1
2
3
4
5
6
7

delimiter $
CREATE PROCEDURE pro_test1()
BEGIN
    SELECT u.username,r.`name` FROM t_user u,t_user_role ur,t_role r WHERE u.id =ur.user_id AND ur.role_id =r.id AND u.id=1 ;
END $
delimiter ;

1
2
3
4
5
6
7

知识小贴士
DELIMITER
该关键字用来声明SQL语句的分隔符,告诉MYSQL解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号（；）。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会只想该命令

4.3调用存储过程

call pro_test1()

4.4查看存储过程

–查看db_name数据库中所有的存储过程
select name from mysql.proc where db=‘db_name’

–查询存储过程的状态信息
show procedure status
show procedure status\G
–查询某个存储过程的定义
show create procedure pro_test1\G

4.5删除存储过程

drop procedure pro_test

4.6语法存储过程

4.6.1变量

DECLARE
通过DECLARE可以定义一个局部遍历,改遍历的作用范围只能在BEGIN…END块中

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

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

delimiter ;
1
2
3
4
5
6
7
8

• SET
  直接赋值使用SET,可以赋常量或者附表达式,具体语句如下

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

DELIMITER $
 CREATE PROCEDURE pro_test3()
 BEGIN
   DECLARE NAME VARCHAR(20);
   SET NAME='MYSQL';
   SELECT NAME;
 END$
  
 DELIMITER ;
1
2
3
4
5
6
7
8
9

4.6.2 if条件判断

if search_condition then statement_list
    [elseif search_condition then statement_list]...
     [else statement_list]
end if;
1
2
3
4

create procedure pro_test4()
begin
  declare height int default 175;
  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$
1
2
3
4
5
6
7
8
9
10
11
12
13

call pro_test4();

4.6.3传递参数

create procedure procedure_name（[in/out/inout]参数名 参数类型）
...
IN 作为输入，也就是需要调用方法传入值，默认
OUT 作为输出，也就是该参数可以作为返回值
INOUT 既可以作为输入，也可以作为输出
1
2
3
4
5

IN
需求：根据定义的身高变量，判定当前身高的所属的身材类型

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$
1
2
3
4
5
6
7
8
9
10
11
12

call pro_test5(199);

OUT
需求：根据传入的身高变量，获取当前按身高的所属的身材类型（返回值）

create procedure pro_test6(in height int，out description varchar)
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;
 end$
1
2
3
4
5
6
7
8
9
10
11

call pro_test6(xx,@description);
select @description

4.6.4case结构

方式一：
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;
1
2
3
4
5
6
7
8
9
10
11
12
13

需求：给定一个月份，然后计算出所在的季度

create procedure pro_test7(mon int)
begin
   declare result varchar(10);
   case 
     when mon>=1 and mon<=2 then
      set result='第一季度';
     when mon>=4 and mon<=6 then
      set result='第二季度';
     when mon>=7 and mon<=9 then
      set result='第三季度';
     ELSE
      set result='第四季度';
    end case;
    select concat('传递的月份为：'，mon,',计算出的结果为',result) as content;
end$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

call pro_test7(1);

4.6.5while循环

while search_condition do
  statement_list
 end while
1
2
3

需求：计算从1加到n的值

create procedure pro_test8(n int)
begin
  declare total int default 0;
  declare num int default 1;
  while num<=n
     set total=total+num;
     set num=num+1;
  end while;
  select total;
end$
1
2
3
4
5
6
7
8
9
10

call pro_test8(8);

4.6.6repeat结构

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

REPEAT
   statment_list
   UNTIL search_condition

END REPEAT
1
2
3
4
5

需求：计算从1加到n的值

create procedure pro_test8(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$
1
2
3
4
5
6
7
8
9
10
11

4.6.7loop语法

LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用LEAVE语句实现，具体语法如下：

[begin_lable:]LOOP
   statement_list
 END LOOP [end_label]
1
2
3

如果不在statement_list中增加推出循环的语句，那么LOOP语句可以用来实现简单的死循环

需求：计算从1加到n的值

create procedure pro_test10(n int)
begin
  declare total int default 0;
  
 c:loop
   set total=total+n;
   set n=n-1;
   if n<=0 then
     leave c;
     end if;
 end loop c; 
  select total;
end$
1
2
3
4
5
6
7
8
9
10
11
12
13

4.6.8leave语法

用来从标注的流程构造中退出，通常和BEGIN…END或者循环一起使用。下面是一个使用LOOP和LEAVE来实现退出

create procedure pro_test10(n int)
begin
  declare total int default 0;
   
   c:loop
     set total=total+n;
     set n=n-1;
     if n<0 then 
      leave c;
      end if;
   end loop c;
  select total;
end$
1
2
3
4
5
6
7
8
9
10
11
12
13

4.6.9游标 光标

create procedure pro_test11()
begin 
  declare e_id int(11);
  declare e_name varchar(10);
  declare emp_result cursor for select * from table_name;
 
  open emp_result;

   fetch emp_result into e_id,e_name
   select concat('id=',e_id,',name=',e_name)

   close emp_result;
end $
1
2
3
4
5
6
7
8
9
10
11
12
13

只能展示一条，而且多了超过了会报错

通过循环
count(*) ------>num 每次减一
sql有边界变量,句柄机制

create procedure pro_test11()
begin 
  declare e_id int(11);
  declare e_name varchar(10);
  declare has_data int default 1;
  
  declare emp_result cursor for select * from table_name;
  declare exit HANDLER for not found set has_data=0;
 
  open emp_result;
   repeat
   fetch emp_result into e_id,e_name
   select concat('id=',e_id,',name=',e_name)
   until has_data=0
   end repeat;

   close emp_result;
end $
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4.7存储函数

select count_city(1)

5.触发器

5.1介绍

5.2创建触发器

create trigger trigger_name
before/after inset/update/delete

on table_name

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

begin

trigger_stmt;  

end;
1
2
3
4
5
6
7
8
9
10
11
12

通过触发器记录emp表的数据变更日志，包含增加，修改，删除

插入

create trigger emp_inset_trigger
after insert
on emp
for each row
begin
   insert into emp_logs(id,operation,operate_time,operation_id,opeartion_params) values (null,'insert',now(),new.id,contcat('插入后的(id',new.id,', name:',new.name))
end$
1
2
3
4
5
6
7

更新

create trigger emp_inset_trigger
after insert
on emp
for each row
begin
   insert into emp_logs(id,operation,operate_time,operation_id,opeartion_params) values (null,'insert',now(),new.id,contcat('修改前的(id',old.id,', name:',old.name,'修改后的(',new.id,',name',new.name))
end$
1
2
3
4
5
6
7

删除,用old完事

5.3删除与查看触发器

drop trigger trigger_name
1

show triggers;
1

第二章

1.Mysql体系结构

整个MYSQL Server由以下组成

• Connection Pool 连接池工具
• Management Services&Utlities 管理服务和工具组件
• 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%' ;
1

2.2存储引擎特性

下面重点介绍几种常用的存储引擎，并对比各个存储引擎之间的区别

2.2.1 InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交，回滚，崩溃恢复能力的事物安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引
InnoDB存储引擎不同于其他存储引擎的特点
事物控制

外键约束
MYSQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对于的索引，子表在创建外键的时候，也会自动的创建对于的索引。

ON DELETE RESTRICE--------->删除主表数据时，如果有关联记录，不删除
ON UPDATE CASCADE---------->更新主表时，没有子表有关联记录，更新子表子路
在创建索引时，可以指定在删除，更新父表时，对子表进行的相应操作，包括RESTRICE,CASCADE,SET NULL,和NO ACTION。
RESTRICE和NO ACTION相同，是指限制在子表有关联记录的情况下，父表不能更新
CASCAD表示父表在更新或者删除时，更新或者删除子表对应得记录
SET NULL则表示父表在更新或者删除得时候，子表得对应字段被SET NULL
针对上面创建得两个表，子表得外键指定得时ON DELETE RESTRICT ON UPDATE CASCADE方式的，那么在主表删除记录得时候，如果子表有记录，则不允许删除，主表在更新记录得时候，如果子表有对应记录，则子表对应更新。

存储方式
InnoDB存储表和索引有以下两种方式
①使用共享表空间存储，这种方式创建得表得表结构表村在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义得表空间中，可以时多个文件
②四月夺标空间存储，这种方式创建得表得表结构仍然存在.frm文件，但是每个表得数据和索引单独保存在.idb中

2.2.2 MyISAM

2.2.3 MEMORY(内存)

Memory存储引擎将表得数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm，该文件值存储表得结构，而其数据文件，都是存储在内存中，这样有利于数据得快速处理，提高整个表得效率。MEMORY类型的表访问非常得快，因为数据时放在内存中的，并且默认使用HASH索引，但是父五一旦关闭，表中的数据就会丢失

2.2.4 MERGE（合并）

2.3存储引擎的选择

3.优化sql步骤

3.1查看SQL执行效率

MySQL客户端链接成过后，通过show[session|global]status 命令可以提供服务器状态信息。show[session|global]status可以根据需要加上参数“session”或者“global”来显示session即(当前链接)的计结果和global级（子数据库上次启动至今）的统计结果。如果不写，默认使用参数是"session"
_是模糊匹配
当前链接的信息

show status like 'Com_______';
1

全局的信息

show global status like 'Com_______';
1

查询Innodb的操作数量

show global status like 'Innodb_rows_%'
1

3.2定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的SQL语句

• 慢查询日志:通过慢查询日志定位哪些执行效率较低的SQL语句,用–log-slow-queries[=file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句d饿日志文件.
• show processlist:慢查询日志在查询结束以后才记录,所有在应用反应执行效率出现问题的时候查询慢查询日志并不能定位,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态,是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操作进行优化

3.3 explain分析执行计划

通过以上步骤查询到效率低的SQL语句后,可以通过EXPLAIN或者DESC命令获取MYSQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中标如何连接和连接顺序

explain select * from table_name where xxx
1

环境准备

3.3.1 explain之id

explain select * from table_name
1

id字段是select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序.id分为三种情况
1)id相同表示加载表的顺序是从上到下

2)id不同id值越大,优先级越高,越先被执行

3)id有相同,也有不同,同时存在.id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越大,优先级越高,约先执行

3.3.2 explain之select_type

3.3.3 explain之table

展示这一行的数据是关于哪一张的

3.3.4 explain之type

type显示的是访问类型,是较为重要的一个指标,可取值为:

3.3.5 explain之key

3.3.6 explain之rows

扫描行的数量

3.3.7 explain之extra

尽量优化到using index

3.4 show profile 分析SQL

MySQL从5.0.37版本开始增加了对show Profiles和 show profile 语句的支持.show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪去了

查看是否支持profile

SELECT @@have_profiling;
1

默认profiling是关闭的,可以通过set语句在Session级别开启profiling

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

通过Profile,我们能够更清楚地了解SQL执行的过程

show profiles
1

看详细信息

show profile for query id ;
1

Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅返回个客户端.由于在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整个查询中耗时最长的状态
1

在获取到最消耗时间的线程后,MySQL支持进一步选择all,cpu,block io,context switch,page faults等明细类型查看MySQL在使用什么资源上消耗了过高的时间,例如查看CPU的耗时

show profile cpu for query 6;
1

show profile all for query 6;
1

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;
1
2

执行SQL语句

select * from tb_item where id<4;
1

最后,检查Information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的

select * from information_schema.optimizer_trace\G;
1

3.6 mysqldumpslow

mysqldumpslow [ OPTS... ] [ LOGS... ] //命令行格式
1

常用到的格式组合

-s 表示按照何种方式排序 
c 访问次数 
l 锁定时间 
r 返回记录 
t 查询时间 
al 平均锁定时间 
ar 平均返回记录数 
at 平均查询时间 
-t 返回前面多少条数据 
-g 后边搭配一个正则匹配模式，大小写不敏感 
1
2
3
4
5
6
7
8
9
10

1、拿到慢日志路径

show variables like '%slow_query_log%';
1

日志路径为：/opt/mysql-5.7.28/data/linux-141-slow.log
查看日志

cat /opt/mysql-5.7.28/data/linux-141-slow.log
1

2、得到访问次数最多的10条SQL

[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t 10 /opt/mysql- 
5.7.28/data/linux-141-slow.log 
-bash: ./bin/mysqldumpslow: /usr/bin/perl: 坏的解释器: 没有那个文件或目录 
[root@linux-141 mysql-5.7.28]# yum -y install perl perl-devel 
[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t 10 /opt/mysql- 
5.7.28/data/linux-141-slow.log
1
2
3
4
5
6

3、按照时间排序的前10条里面含有左连接的SQL

[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s t -t 10 -g "left join" 
/opt/mysql-5.7.28/data/linux-141-slow.log 
Reading mysql slow query log from /opt/mysql-5.7.28/data/linux-141-slow.log 
Died at ./bin/mysqldumpslow line 167, <> chunk 28. 
[root@linux-141 mysql-5.7.28]#
1
2
3
4
5

慢日志文件分析

1、查看慢查询日志

[root@linux-141 mysql-5.7.28]# cat /opt/mysql-5.7.28/data/linux-141-slow.log 
/opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28 (MySQL Community Server (GPL)). 
started with: 
Tcp port: 3306 Unix socket: /tmp/mysql.sock 
Time Id Command Argument 
/opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28 (MySQL Community Server (GPL)). 
started with: 
Tcp port: 3306 Unix socket: /tmp/mysql.sock 
Time Id Command Argument 
/opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28-log (MySQL Community Server 
(GPL)). started with: 
Tcp port: 3306 Unix socket: /tmp/mysql.sock 
Time Id Command Argument 
# Time: 2021-09-08T09:00:55.830099Z 
# User@Host: root[root] @ [192.168.36.1] Id: 4 
# Query_time: 0.000528 Lock_time: 0.000060 Rows_sent: 15 Rows_examined: 324 
use itcast; 
SET timestamp=1631091655; 
SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING 
GROUP BY QUERY_ID; 
# Time: 2021-09-08T09:00:55.831006Z 
# User@Host: root[root] @ [192.168.36.1] Id: 4 
# Query_time: 0.000535 Lock_time: 0.000079 Rows_sent: 25 Rows_examined: 348 
SET timestamp=1631091655; 
SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, 
CONCAT(ROUND(SUM(DURATION)/0.000313*100,3), '') AS `Percentage` FROM 
INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=5 GROUP BY SEQ, STATE ORDER BY SEQ; 
# Time: 2021-09-08T09:11:07.500778Z (执行SQL时间) 
# User@Host: root[root] @ [192.168.36.1] Id: 4 (执行SQL的主机信息) 
# Query_time: 0.000527 Lock_time: 0.000079 Rows_sent: 26 Rows_examined: 370 
(SQL的执行信息) 
SET timestamp=1631092267; (SQL执行时间) 
SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, 
CONCAT(ROUND(SUM(DURATION)/0.000560*100,3), '') AS `Percentage` FROM 
INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=82 GROUP BY SEQ, STATE ORDER BY SEQ; 
(SQL内容) 
[root@linux-141 mysql-5.7.28]# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

属性解释

-- 执行SQL时间 
# Time: 2019-12-31T05:54:23.893042Z 
-- 执行SQL的主机信息 
# User@Host: root[root] @ localhost [] Id: 40 
-- SQL的执行信息 
# Query_time: 4.013664 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 
-- SQL执行时间 
SET timestamp=1577771659; 
-- SQL内容
1
2
3
4
5
6
7
8
9

4.索引使用

索引是数据库优化最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题

4.1索引的使用

1)全职匹配,对索引中所有列都指定具体值.
索引生效,执行效率高

2)最左前缀法则
如果索引了多列,要遵守最左前缀法则.指的是查询从索引的最左列开始,并且不能跳过索引中的列.

3)范围查询右边的列,不能使用索引

根据前面的两个字段name,status查询是走索引的,但是最后一个条件address没有用到索引

4)不要在索引列上进行运算操作,否则索引失效

5)字符串不加单引号,造成索引失效

6)尽量使用覆盖索引,避免select *
进来使用覆盖所有(值访问索引的查询(索引列完成包含查询列)),减少select*
返回的列最好在索引上,防止再回表查

如果查询列,超出索引列,也会降低性能.

using index:使用覆盖索引的时候就会出现
using where:在查找使用索引的情况下,需要回表去查询所需的数据
using index condition:查询使用索引,但需要回表查询数据
using index;using where :查询使用了索引,但是需要的数据都在索引列中能找到,索引**不需要回表查询数据**
1
2
3
4

7)用or分隔开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,name涉及的所有都不会被用到.

8)以%开头的LIke模糊查询,索引失效
如果仅仅是尾部模糊匹配,索引不会失效.如果是头部模糊匹配,索引失效

9)如果MySQL评估使用索引比全表更慢,则不使用索引
大多数数据都是这个,比如10条中有9条是1,而你要1,会全表

10)is NULL ,is NOUT NULL 有时索引失效

所有或者大多数的字段都是is not null is not null会走全表,not null 会走索引

11)in走索引 not in 索引失效

12)单列索引和复合索引
尽量使用复合索引,而少使用单列索引.

4.2查看索引使用情况

show status like 'handler_read%';

show global status like 'handler_read%';

1
2
3
4

5.SQL优化

5.1大批量插入数据

当使用load命令导入数据的时候,适当的设置可以提高导入的效率

对于InnoDB类型的表,有以下几种方式可以提高导入的效率
1)主键顺序插入
因为InnoDB类型的表示按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率.如果INnoDB表没有主键,name系统会自动默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这点,来提高导入数据的效率

2)关闭唯一性校验
在导入数据前执行SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率

3)手动提交事务
如果应用试验自动提交的方式,建议在导入前执行SET AUTOCOMMIT=0,关闭自动提交,导入结束后再执行SET AUTOCOMMIT=1,打开手动提交,也可以提高导入的效率

5.2优化insert语句

在进行数据的insert操作的时候,可以采用以下几个优化方案

• 如果需要同时对一张表插入很多行数据时,应尽量使用多个值表的insert语句,这种方式将大大的缩减客户端与数据库之间的链接,关闭等消耗.使得效率比分开执行的单个insert语句快

insert into table_name values(1,1);
insert into table_name values(2,2);
insert into table_name values(3,3);
1
2
3

优化后

insert into table_name values(1,1),(2,2),(3,3);
1

• 在事务中进行数据插入

start transaction;
insert into table_name values(1,1);
insert into table_name values(2,2);
insert into table_name values(3,3);
commit;
1
2
3
4
5

• 数据有序插入

insert into table_name values(1,1);
insert into table_name values(3,3);
insert into table_name values(2,2);
1
2
3

优化后

insert into table_name values(1,1);
insert into table_name values(2,2);
insert into table_name values(3,3);
1
2
3

5.3优化order by语句

两种排序方式
1)第一种是通过对返回数据进行排序,也就是通常说的filesort排序,所有不是通过索引直接返回排序结果的排序都叫FileSort排序
2)第二组通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高

多字段排序
1.统一升序或者统一降序
2.排序的顺序和索引的顺序保持一致

总结:尽量减少额外的排序,通过索引直接返回有序数据.where条件和Order by使用相同的所有,并且Order By的顺序和索引顺序相同,并且Order by的字段都是升序,或者都是降序.否则肯定需要额外的操作,这样就会出现FileSort

Filesort的优化

show variables like 'max_length_for_sort_data';

show variables like 'sort_buffer_size'
1
2
3

5.4优化group by语句

group by会自动排序，如果不想排序可以加order by null提高效率，同时给上group by字段索引，会基于索引去排序提高效率

5.5优化嵌套查询

关联链接查询代替子查询

5.6优化OR条件

or关联的字段都要有索引，不然会失效（不能用到覆合索引）
建议使用union替换or

5.7优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好的提高性能，一个常见又非常头疼的问题时limit 200000,10,此时需要Mysql排序前200000 10记录，仅仅返回200000-200010的记录，其他记录丢弃，查询代价非常大

优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

优化思路二
该方案适合用于主键自增的表，可以把limit查询转换成某个位置的查询

5.8使用SQL提示

SQL提升，是优化数据库的一个重要手段，简单来说，就是SQL语句中加入一些认为的提示来达到优化操作的目的

5.8.1 USE INDEX

在查询语句中表名的后面，添加上use index来提供索引 MySQL去参考的索引列表，就可以让Mysql不再考虑其他可能的索引

selec *from table_name use index(index_name) where xxx

5.8.2 IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用ignore index
selec *from table_name ignore index(index_name) where xxx

5.8.1 FORCE INDEX

为强制MySQL使用一个特定的索引，可以查询中使用force index 作为索引
selec *from table_name force index(index_name) where xxx