mysql高级学习分享--存储引擎、sql的优化、索引等

于 2020-05-17 21:09:52 发布
阅读量279 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liuzhongyefirst/article/details/106180660
版权

一、linux下 mysql的安装：

1.1.rpm -qa | grep -i mysql 查看mysql是否安装

1.2.若已经安装低版本的，用命令：rpm -e {上面查到的安装mysql} --nodeps 卸载

 

二、索引：

2.1定义

是一种数据结构，用于高效获取/查询数据

2.2：优/劣势

优势

1）.类似于数据的目录索引，提高数据的查询效率

2）.通过索引对数据排序，降低数据排序的成本，降低CPU的消耗

  劣势

1).实际上索引就是一张表，该表保存在逐渐与索引字段，并指向实体类的记录，所以索引也是要占用空间的。

2).虽然提高了查询效率，也降低了更新表的速度，因为不仅要保存数据，还要保存索引的字段。

2.3 索引的结构

索引是在MySQL的存储引擎层中实现的，不是再服务器器层实现的。

MYsql目前支持4种索引：

BTREE索引(最常见)，HASH索引，R-TREE索引（空间索引），FUll-TEXT (全文索引)

InnoDB引擎，是默认的存储引擎，默认支持BTREE索引。

我们所说的索引，没有特别指明都是指B+树（多路搜索树，不一定是二叉树）。

2.4 索引的分类

单列索引：一个索引只包含单个列，一个表可以有多个单列索引

唯一索引: 索引列的值必须唯一，但允许有空值

复合索引：一个索引包含多个列

2.5 索引语法：后面详细写

2.6 索引设计的原则：

  • 1.查询频次高，且数据量较大的表建立索引
  • 2.索引字段的选择：最佳候选列应当从where子句中提取，如果where 字句的组合比较多，那么应当挑最常用、过滤效果最好的列的组合。
  • 3.使用唯一索引，区分度越高，使用索引的效率越高
  • 4.索引不是越多越好，适当
  • 5.尽可能使用短字段的索引，提升访问索引的I/O效率
  • 6.利用最左索引，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where句子中使用了组成该索引的前几个字段，那么这个sql可以利用组合索引提升查询效率

创建组合索引：

CREATE INDEX idx_name_email ON tb_name (NAME,email,status);

就相当于

对name 创建了索引；

对name， email 创建了索引

对name， email,status 创建了索引

三、视图：

3.1视图（View）

试图是一种虚拟存在的表。视图并不存在数据库中。

也就是一条select语句执行后返回的结果集，所以我们再创建试图的时候，主要的工作就落在创建这条sql查询语句上。

视图相对于普通表的优势：

简答：相当于单表

安全：通过视图可以实现对某个列某个行是否可见。

数据独立。

3.2 创建修改视图

create view view_user as select * from user

查看视图：（与查看表命令一样）

show tables

select * from view_user

更新视图：

update * from view_user where id = ‘1’

删除视图：

drop view if exits view_user;

 

四、存储过程和存储函数

4.1概念

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

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

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

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

4.2 创建存储过程

create procedure procedure_name ([proc_parameter[,...]])

begin

-- sql语句

end ;

delimiter $  // 用delimiter关键字把结束的分隔符;改为$ 

//创建存储过程：
create procedure procedure_user()
begin
	select 'Hello Mysql';
end $

 // delimiter ;

4.3 调用存储过程

call procedure_user() ;

4.4 查看存储过程

-- 查询db_name数据库中的所有的存储过程

select name from mysql.proc where db='db_name';

 

SELECT NAME FROM mysql.proc where db= 'test';

-- 查询存储过程的状态信息

show procedure status;

-- 查询某个存储过程的定义

 

show create procedure test.procedure_user;

 

 

4.5 删除存储过程

DROP PROCEDURE [1 IF EXISTS] sp_name ；

4.6 语法

存储过程是可以编程的，意外着可以使用变量，表达式，控制结构，来完成比较复杂的功能。

变量

DELCARE 可以定义一个局部变量，该变量的作用范围只能在BEGIN ... END块中

CREATE PROCEDURE pro_test0()
BEGIN
	DECLARE NUM INT DEFAULT 5;
	SELECT NUM + 10 ;
END 

CALL pro_test0();

SET 关键字设置值

CREATE PROCEDURE pro_test2()
BEGIN
DECLARE name VARCHAR (20);
set name = 'set方式赋值' ;
SELECT concat('赋值的方式为：',name);
END

 

也可以使用select ... into方式赋值

CREATE PROCEDURE pro_test1()
BEGIN
DECLARE num int;
-- 将查询返回的结果，赋值给num 
select count(*) into num from user ; 
SELECT concat('user表中的记录数为：',num);
END

 

if 条件判断 的语法结构

-- if 条件判断
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;

 

参数的传递

in : 输入参数 默认

out : 输出参数

inout:即是输入又是输出参数

 

-- 参数的传递
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;

-- 参数的传递，输入参数为height,函数的返回值为description
CREATE PROCEDURE pro_test7(in height int, out description varchar(10))
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;

-- 调用存储过程
CALL pro_test7(188,@description);

SELECT @description;

//@description：这种变量要在变量名称前加上“@”符号，叫做用户的绘画变量
// 代表整个绘画过程它都是有作用的，类似于全局变量。也就是说当我当前会话关闭，该变量就会释放

// @@description。2个@符号，这种叫做系统变量。

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

例如：

-- 给定一个月份，然后计算出所在的季度
CREATE PROCEDURE pro_test8(mon int )
BEGIN
	DECLARE result VARCHAR(10);
case
	when mon >=1 and mon <=3 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

while循环 ：满足条件则继续循环

while search_condition do

statement_list

end while;
-- 需求：
-- 计算从1加到n的值

CREATE PROCEDURE pro_test9(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

 

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

语法:

REPEAT 
statement_list
NUTIL search_condition
END REPEAT ;

 例如

-- 需求 ： 计算从1加到n的值
CREATE PROCEDURE pro_test9(n int)
BEGIN
	DECLARE total int DEFAULT 0;
	DECLARE num int DEFAULT 1;
	repeat 
	set total = total +n;
	set n = n -1;
	-- until 条件子句不加分号
	until n=0
	end repeat;
	SELECT total;
END;

调用函数：

 

loop语句：退出循环的条件需要使用其他的语句定义，通常可以使用LEAVE语句实现。

具体语法如下：

LOOP
statement_list
END LOOP ;

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

-- 计算从1加到n的值
CREATE PROCEDURE pro_test10(n int )
begin

	DECLARE total int DEFAULT 0;

	c:loop  -- c 是别名
		set total = total + n ;
		set n = n - 1;
		
		if n <= 0 then
			LEAVE c; -- 退出loop循环
		end if;
	end loop c;
	
	SELECT total;

end ;

 

游标/光标：用来存储查询结果集的数据类型， 在存储函数和函数中可以使用光标多结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH、和CLOSE。

语法分别为：

-- 声明光标：
DECLARE cursor_name CURSOR FOR select_statement;

-- 开启游标：
OPEN cursor_name;

-- FETCH 光标：相当于一个指针，每调用一次，抓取一次。抓取游标中的一条数据
FETCH cursor_name into [param1,param2,...];

-- 关闭游标：
CLOSE cursor_name;

如：

-- 创建sql：
CREATE TABLE `emp`  (
  `id` int(11) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `salary` int(11) NULL DEFAULT NULL COMMENT '薪水',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, '金毛狮王', 55, 8000);
INSERT INTO `emp` VALUES (2, '白眉鹰王', 65, 10000);
INSERT INTO `emp` VALUES (3, '紫衫龙王', 40, 5000);
INSERT INTO `emp` VALUES (4, '青翼蝠王', 67, 3000);


-- 查询emp表的数据并遍历展示
create PROCEDURE pro_test11()
BEGIN
	DECLARE e_id int (11);
	DECLARE e_name VARCHAR (50);
	DECLARE e_age int (11);
	DECLARE e_selary int (11);  -- 声明的表结构要和表中的字段结构和类型保持一致
	
	DECLARE emp_result cursor for select * from emp; -- 声明游标
	
	open emp_result;
	
	FETCH emp_result into e_id,e_name,e_age,e_selary; -- 抓取一条并打印
	SELECT concat('id= ',e_id,'name=',e_name,'age=',e_age,'selary=',e_selary); 
	
	FETCH emp_result into e_id,e_name,e_age,e_selary;  -- 抓取第2条条并打印
	SELECT concat('id= ',e_id,'name=',e_name,'age=',e_age,'selary=',e_selary);
 
 -- 总共有4条，超过第5条会报错
	
	close emp_result;

END;

我们可以改进下：循环fetch。

-- 查询emp表的数据并遍历展示
create PROCEDURE pro_test12()
BEGIN
	DECLARE e_id int (11);
	DECLARE e_name VARCHAR (50);
	DECLARE e_age int (11);
	DECLARE e_selary 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; -- mysql提供的一种机制，含义为：当拿不到数据时，赋值has_data = 0,并且exit退出当前程序。 注意:该声明的位置必须紧靠游标的声明后面！！！
	 
	open emp_result;
	
	repeat
		FETCH emp_result into e_id,e_name,e_age,e_selary; 
		SELECT concat('id= ',e_id,'name=',e_name,'age=',e_age,'selary=',e_selary); 
	until has_data = 0 
	end repeat ;

	
	close emp_result;

END;

执行call pro_test12 ; 总共4条数据：

 

4.7 存储过程

存储函数能做的事情，存储过程也能做。

语法结构：

CREATE FUNCTION function_name([param type] ...)
RETURNS type
BEGIN
	...	
END;
-- 定义一个存储函数，获取满足条件的user的总记录数

CREATE FUNCTION func1(ageParam int)
RETURNS int
BEGIN
        DECLARE cnum int;
        SELECT COUNT(*) into  cnum from user WHERE age > ageParam;
	return cnum;
END

//
CREATE FUNCTION func1(ageParam int)
RETURNS int
BEGIN
        DECLARE cnum int;
        SELECT COUNT(*) into  cnum from pd_sequence_dict WHERE age > ageParam;
	return cnum;
END

 五.触发器

5.1定义

触发器是与表有关的数据库对象，指在insert/update/dalete之前或者之后触发并执行触发器中定义的sql语句的集合。

特点：触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行；

作用：保证数据的完整性,日志记录，数据校验等操作。

使用别名OLD和NEW 来引用触发器中发生变化的记录内容。

5.2 创建触发器

语法：

-- 创建触发器
CREATE TRIGGER trigger_name
BEFORE/AFTER  INSERT/UPDATE/DELETE
ON table_name 
[for each row] -- 行级触发器（mysql只支持行触发器，不支持语句级触发器。oracle均支持。）
begin

-- 编写触发器的具体逻辑
trigger_statement;
END;

 需求：

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

-- 删除操作
CREATE TRIGGER emp_delete_trigger
AFTER DELETE
on emp
for each row

BEGIN
-- 编写逻辑：记录删除前的数据
insert into emp_logs(`id`, `operation`, `operate_time`, `operate_id`, `operate_params`)
VALUES(3,'delete',now(),OLD.id,CONCAT('删除前的id:',OLD.id,',name:',OLD.name)); -- new 代表

END;

删除触发器：

drop TRIGGER trigger_name;

查看触发器：

show TRIGGERS ;

 

--高级部分待续。。。。

常见索引是提高查查询效率的方式之一，但是创建索引一定能提高查询效率吗？否！

只有合理的利用索引才能提高查询效率。避免索引失效的方式：

1）全值匹配

2）最左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，并且不能跳过索引中的列。只要where条件包含name列，且没有跳跃某一列就会走索引

3）范围查询右边的列，不能使用索引

4）不要在索引上进行运算操作，否则索引失效。

5）字符串不加单引号，将造成索引失效。

6）尽量使用覆盖索引，避免使用select *

覆盖索引：只访问索引的查询（索引列完全包含查询列）

----未完待续。。

 

附件是myql的相关笔记，里面有相关的记录，可免费下载。

https://download.csdn.net/download/liuzhongyefirst/12432139

 

 

 

 

 

 

 

 

 

 

HAUT_lzy
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值