2021-08-06 索引和存储过程

MySQL索引

索引的作用

1.提高查询的速度。
2.确保数据的唯一性。
3.可以加速表之间的链接，实现表与表之间的参照完成性。
4.使用分组和排序进行数据检索时可以显著的减少时间。
5.建立索引的目的在于提高查询的效率。

索引的缺点

1.索引虽然可以提高查询效率但是会降低更新表的速度，比如INSERT、UPDATE、DELETE，因为表的更新，MySQL不仅要保存数据，还要保存索引文件。
2.建立索引会占用磁盘空间的索引文件，一般情况都不太严重，但是一个表中创建了多种组合索引索引文件就会膨胀的很快，如果某个数据列包含了许多重复的内容，它建立索引没有太大的实际效果。
3.对于非常小的表大部分情况下简单的扫描的全表效率更好。
4.索引只是提高效率的一个因素，如果MySQL有大量的表，就需要花时间研究建立最有效的索引，或者优化查询的语句。
5.MySQL中同一个表的索引总数限制为16个。

索引的分类

1.主键索引

最常见的索引类型
确保数据记录的唯一性
确定特定的数据记录在数据库中的位置

2.唯一索引

避免同一个表中某数据列中值重复
与主键索引的区别
主键索引只能有一个唯一索引可以有多个

3.常规索引

快速的定位到特定数据

4.全文索引

快速的定位特定数据
只能用于MyISAM类型的表
只能用于 CHAR 、VARCHAR、TEXT数据类型 适合大型数据集

-- 主键索引
CREATE TABLE `result`(
  INDEX/KEY `ind` (`studentNo`,`subjectNo`)
 )
ALTER TABLE table_name ADD INDEX `ind`(`studentNo`,`subjectNo`)
-- 全文索引
CREATE TABLE 表名(
) ENGINE = MYISAM;
ALTER TABLE 表名 ADD FULLTEXT (`列名`)
SELECT * FROM 表名 WHERE MATCH(title,...) AGAINST('查询的值')
SHOW INDEX(或者 key)  FROM cardinfo

5.使用索引要注意的事项

索引不是越多越好
不要对经常发生变动的数据加索引
小量数据不要加索引
索引一般加在查询多的列上
索引的底层数据结构

索引的底层数据结构

BTREE B+TREE HASH等
B树特征
关键字结合分布在整棵树中
任何一个关键字出现且只出现在一个节点中
搜索可能在非叶子结点结束
B+树是B树的变体，也是一种多路搜索数
所有的关键字都会出现在叶子结点的链表中，且链表中的关键字是有序的
不可能在非叶子结点命中
非叶子节点相当于叶子结点的索引，叶子结点相当于存储数据的层
HASH
是采用一定的哈希算法，把键换算成新的哈希值，检索时不需要类似于B+数从根节点到叶子结点，逐级查找，
只需要一次哈希算法结合立刻定位到响应的位置，速度非常快不支持范围查询比如WHERE price>100。

范式

第一范式

是指数据库中表的每一列都是不可分割的基本数据，也就是说每列都是具有原子性的，不可在分割。

第二范式

是在第一范式的基础上建立的，每个表只描述一件事情。

第三范式

是在第二范式的基础上建立的，表的非主键列必须和主键直接相关不能间接相关，也就是说，非主键列之间不能相关依赖。

存储过程

存储过程就是为之后的使用保存一条或多条MySQL语句的集合。
存储过程的思想是在数据库SQL语言层面上的代码的封装和重用。
优点：
把处理封装在容易使用的单元中，简化复杂的操作，防止错误保存数据的一致性，简化对变动的管理，提高性能。
缺点：
编写SQL语句比较复杂，权限问题，在MySQL5.0之前是不支持的。

IN OUT INOUT修饰的参数

IN 输入参数，表示该参数的值必须在调用存储过程时指定，在存储过程中改参数的值不能被返回。
OUT 输出参数，该值在存储过程中，内部被改变并可以返回。
INOUT 输入输出参数，调用是指定，并且可以被改变和返回
ps：返回指在调用存储过程结束后会再次将值传递给实际参数。

定义变量

– set @variable_name = value;
– declare variable_name; set variable_name = value;

分支与循环

--if分支
-- if 条件 THEN
-- 变量赋值 SQL语句
-- else if 条件 then
-- 变量赋值 SQL语句
-- else 条件 then
-- 变量赋值 SQL语句
--end if;

-- case分支
-- CASE variable
-- WHEN value	变量赋值 SQL语句
-- WHEN value	变量赋值 SQL语句
-- END CASE;

-- loop循环
set var = 0;
LOOP_LABLE:LOOP -- 起名为 LOOP_LABLE
INSERT INTO table1 VALUES('循'); -- 循环体
SET var = var +1; -- 改变循环条件
IF var >10 THEN -- 判断 是否达成条件 
	LEAVE LOOP_LABLE; -- 结束循环  语法 LEAVE 循环名称;
END IF; -- 结束IF
END LOOP; -- 结束循环

-- while循环
SET var = 0;
-- 循环  var < 10 循环条件
WHILE var < 10
do
	INSERT INTO table1 VALUES('循'); -- 循环体
SET var = var+1;  -- 改变循环条件
END WHILE; -- 结束

声明语句结束符 DELIMITER $$ 或 DELIMITER

-- 创建存储过程
CREATE PROCEDURE procedure_name(参数列表)
BEGIN
-- SQL语句
END
-- 查看存储过程状态
-- SHOW PROCEDURE STATUS LIKE procedure_name;
-- 查看存储过程的定义
-- SHOW CREATE PROCEDURE procedure_name;
-- 删除存储过程
--DROP PROCEDURE procedure_name;
-- 执行存储过程
-- CALL procedure_name();