查询语法和执行步骤

查询语法和执行步骤传送门

索引

索引是什么?

索引是帮助mysql高效获取数据的排好序数据结构
(类似于字典页)

索引数据结构

  • 二叉树
    不适合有序key进行索引查找,有序key会退化成链表
  • 平衡二叉树(红黑树)
    改进了二叉树的的退化成链表,但是I/O次数还是很多,为logn(以2为底)次
  • 平衡多叉树(B树,B+树)
    B树:在平衡二叉树的基础上将二叉变为多叉,也就是不再是一次提取一个key进内存进行搜索,而是一次提取多个key进内存进行搜索,当key个数固定的时候,树的高度变小,有效减少了I/O次数。(若为m叉树,那么I/O次数为logn(以m为底)),但仍然保留了平衡二叉树的一个key就要带上key所在的key的记录的物理地址(但是没有key的冗余),这样的话降低了有限的空间内存储key的密度,因此放入内存的索引会降低key的存储密度。
    B+树:在B树上进行了两个改进:1.允许冗余key的存在,并将key所在的记录的物理地址放在叶子节点上。在子节点中选取key放入父结点中,提高了key的存储密度,这样就能在key个数固定的情况下相同的节点空间就能存储更多的key,就能减少树的高度,这样就能减少I/O次数。2.将各个叶子节点用指针相连,这样,就能支持区间查找。
  • hash表
    根据key的值进行散列,理论上查找单个值最优秀,但是会有冲突产生,也不支持区间查找(因为当条件子句中含有区间查找>6等时,就会无法进行查找,而B+树叶子节点按照有序排列,因此找到符合结点之后,可以根据区间合理查找)

为什么索引可以高效获取数据呢?
我们要知道首先,数据库的数据都存储在磁盘上,如果我们把所有数据库的数据都放入内存,会造成内存空间不够的情况,因此我们只能将数据库的数据存放在磁盘上,内存中加载一定的索引机制,这样能有效减少I/O次数。

先假设一个问题,如果没有索引,就按照表的顺序从第一条开始查找,那么就相当于是一个链式查找的效率,并且由于表都存放在磁盘上,因此我们每查一条数据就需要I/O一次,会造成效率十分低下。

因此我们需要为表建立合适索引,利用对表的某些属性为key建立合适的索引数据结构,就能提高查找的效率。

我们在数据结构中已经学过了二分查找,也就是二叉树查找,二叉树查找可以将查询时间进行一定的优化,那么我们就可以建立二叉树这样的索引结构,具体如下:

typedef struct TNode{
   //二叉树结点 
	int key;//以某个属性为关键字 
	int dizhi;//存储该关键字key所在的那条记录的地址 
	struct TNode * lchild;//指向的左孩子 
	struct TNode * rchild;//右孩子 
} 

我们只需要对key进行二分查找(二叉树搜索),就能减少查找的时间,然后得到该key所在的表的记录所在的物理地址,就能找到该记录了,这样可以从n的时间复杂度变成logn的时间复杂度,并且I/O次数也相对于链表的n次减少了很多,只需要logn次I/O即可,大大节省了开销。
但是,当碰到有序的key时,就会退化成链表,因此,不适合作为数据库的索引结构。
退化成链表的二叉树
因为二叉树的这些缺点,所以大佬们针对二叉树进行了一定的改造,变成了平衡二叉树(红黑树)
改造后的二叉树,红黑树
红黑树规定了左右子树高度之差不能超过1,因此有效的防止了二叉树退化成链表,提高了查询效率,但是大佬们还不满足,虽然红黑树已经能减少把I/O次数减少到logn(以二为底)了,但是当数据很大的时候,查找还是需要很多次的I/O,为了解决I/O次数过多的问题,B树应运而生
改造后的红黑树,m叉平衡树,B树
在这里插入图片描述
B树在平衡二叉树的基础上增加了叉树,也增加了一个树节点所能容纳的key的个数,这样就能一次多读取几个key进入内存,从而减少了树的高度,减少了I/O次数。缺点:因为还是在二叉树的基础上改进的,每一个key还是对应有一个dizhi,这样在一个树节点的有效空间内,key的存储密度就下降了,并且,B树不支持区间查找,因为key并不是都在叶子节点上,查找的key可能在不同层上,所以没法有效做到区间查询。
那么如何解决呢? 大佬们又提出了B+树,B树2.0.
B树2.0版本,B+树
在这里插入图片描述
我们可以看到,所有的关键字key都在叶子层出现了,并且有指针有序相连,这样我们在进行区间查询的时候,我们只需要固定区间的两端key,然后按照在叶子结点找到的key开始遍历区间中的所有结点就能找到所有记录,因此B+树在B树的基础上进行了区间查找的优化。
此外,因为B树是每个结点都有key和dizhi,这样降低了key的存储密度,B+树只有叶子节点处,才保存有dizhi,所以B+树节点中的key密度会更高,因此I/O次数也会更少。

所以综上所述,B+树是最优秀的索引结构,一方面I/O次数足够优秀,另一方面支持区间查找。

聚集索引和非聚集索引

非聚集索引:以该属性为key的B+树索引结构中,叶子节点中保存的是该key关键字所在的记录的磁盘地址

在这里插入图片描述

聚集索引:以该属性为key的B+树索引结构中,叶子节点中保存的是该key关键字以及该条记录的其余属性值
在这里插入图片描述
为什么索引必须有主键,并且推荐使用整型的自增主键?
因为key的搜索时,整型所占空间较小,并且比较起来开销小,如果是字母比较还需要转成ASCII码比较,而且要比较每一位。所以使用整型
而又因为自增的key在构建B树的时候所要进行的分裂和合并开销最小,如果不是自增的插入key的话,那么就会因为合并和分裂产生大量的开销,并且,叶子节点处也要求是自增有序,因此推荐使用整型的自增主键。

联合索引

在这里插入图片描述
联合索引实质上类似于结构体排序,将整个索引看成一个结构体然后按照结构体排序来进行排序

索引优缺点

优点:能够提升查找的效率(联想查字典)
缺点:因为不止要维护一个表,还需要维护索引,因此插入删除修改等都会造成效率的降低

什么时候创建索引:

  1. 当表项数据很少的时候不需要创建索引,如果只有一行数据创建索引没有必要
  2. 主键自带聚集索引,因此没有必要再围绕主键建立索引

索引语法

语法如下:

创建索引 
//修改表,给他增加索引 
1.alter table 表名 add [unique] index 索引名(表的列名) 
//创建索引在表上 
2.create [unique](可选) index 索引名 on 表名(列名1,列名2....); 
 
删除索引 
1.drop index 索引名 on 表名 
2.alter table 表名 drop index 索引名 

注意:

  1. unique是可选的,添加以后表示当前列是唯一索引
  2. 可以同时为多个列建立索引,称为复合索引,建立好复合索引以后,只有这些列同时出现在where的后面,索引才会起作用。,不推荐使用
  3. 索引名字除在删除索引时有用外,其他时候没有作用,但是不能重复。

视图

视图是什么?

视图的定义
视图是基于一个或多个表或视图的逻辑表,本身不含数据,通过它可以对表里面的数据进行查询和修改。
视图基于的表称为基表。
视图是存储在数据字典里的一条select语句。
通过创建视图可以提取数据的逻辑上的集合或组合。

视图的作用:
1.当我们多次使用同一个select语句时,我们可以把该select语句包装起来,创建成视图,这样下次再用的时候,我们就可以直接对视图进行操作了,不用再写一长串sqll语句了。而用户可以不知道封装的底层细节,可以直接拿来用即可。
2.因为用户可以不知道封装的底层细节,所以可以对用户屏蔽掉机密的表,只让他操作视图中可以操作的表即可。

视图语法

  • 查询视图
create view 视图名称 as  
子查询 
  • 修改视图
replace view 视图名称 as 查询语句 
alter view 视图名称 as 查询语句 
  • 删除视图
drop view 视图名称 

别的增删改查数据可以将视图当成表用

触发器

触发器的定义
在mysql中也就是对某一个表的一定的操作,触发某种条件(insert,update,delete等),从而自动执行的一段程序,就像传感器一样。

触发器的语法

  • 创建触发器
create trigger 触发器名称  //创建触发器,命名 
after/before             //选择触发时机 
insert/update/delete on 表名//作用在哪个表上 
for each row //对每一行 
程序体(可以是begin...end(保证操作的原子性),也可以是一句sql) 

注:
new是新值,代表新行,
old是旧值,代表旧行,
insert只有new
update有new和old
delete只有old
----------------------------------------------------------------------------------------------------------------------------------------------------
example:
创建两种表t1,t2两种表中都有id,name字段,新加t1表记录后自动添加t2

create trigger t 
after insert 
on t1 
for each row 
insert into t2(name) values(new.name); 
//其中new代表新插入的行记录,新插入的行中的name字段 

存储过程

存储过程的语法

delimiter//前后各一个,用来标识哪个地方开始和结束 
create procedure 过程名(in 输入参数 输入参数类型, out 输出参数 输出参数类型) 
//若有inout x int,表示x既可以做输入,也可以做输出,详情看例子 
begin//原子操作开始 
	过程实现 
end 
delimiter 

注:mysql中的变量前面加上@

example
存储查询给定部门的平均工资的过程

delimiter 
create procedure emp_avg_sal(in dept_no int,out avg_sal decimal(8,2)) 
begin 
	select avg(e.sal) into avg_sal from emp e where e.deptno=dept_no; 
end 
delimiter; 
call emp_avg_sal(10,@result); 
select @result; 
create procedure pro(inout x int) 
begin 
	set x=x+1; 
end 
 
//调用 
set @x=10; 
select @x; 
call pro(@x); 
select @x; 

创建函数

创建函数语法

create or replace function 函数名(参数 参数类型) 
returns 返回值类型 
begin 
	return 返回值; 
end 
//调用 
select 函数名称(参数列表) 

可以看出,函数在存储过程的基础上,取消了out输出参数,将输出参数改成了返回值

example:
将下述存储过程转换成函数

存储过程

delimiter 
create procedure emp_avg_sal(in dept_no int,out avg_sal decimal(8,2)) 
begin 
	select avg(e.sal) into avg_sal from emp e where e.deptno=dept_no; 
end 
delimiter; 
call emp_avg_sal(10,@result); 
select @result; 

函数

delimiter 
create function emp_avg_sal_fun(dept_no int) 
returns decimal(8,2); 
begin 
	return (select avg(e.sal) from emp e where e.deptno=dept_no); 
end 
delimiter 
//函数调用 
select emp_avg_sal_fun(10); 
发布评论
IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

数据库设计的基本流程讲解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。