MySQL 视图,触发器,存储过程,索引,树,慢优化查询

目录

视图

触发器

存储过程

内置函数

流程控制

索引

        联合索引

        唯一索引

        树

慢优化查询

---

视图

视图是一张虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。(视图不是真实存在磁盘上的)

create view 视图名 as SQL语句;

        create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;

视图本质上是一张虚拟表. 此虚拟表不可更改, 尽量不要使用.

1. 操作视图会比直接操作基础表要慢，要尽量避免在大型表上创建视图
2. 使用嵌套视图时，会重复访问基础表，带来性能损耗
3. 视图不能索引，也不能有关联的触发器或默认值

触发器

触发器是针对表数据的增删改前后自动触发的功能.  

"""
语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
    sql语句
end
"""

注意触发器内部的SQL语句需要用到分号 但是分号又是SQL语句默认的结束符
所以为了能够完整的写出触发器的代码 需要临时修改SQL语句默认的结束符
    delimiter $$
     编写需要用到分号的各种语句
      delimiter ;

 
    
# 案例
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);
 
CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);
 
delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了
 
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');
 
 
 
# 查看当前库下所有的触发器信息
show triggers\G;
 
 
# 查询errlog表记录
select * from errlog;
 
 
# 删除触发器
drop trigger tri_after_insert_cmd(触发器名字);

存储过程

类似于python中的自定义函数
 
delimiter 临时结束符
create procedure 名字(参数,参数)
begin
    sql语句;
end 临时结束符
delimiter ;
 
delimiter $$
create procedure p1(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;
 
 
# 针对res需要先提前定义
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看
 
"""
查看存储过程具体信息
    show create procedure pro1;
查看所有存储过程
    show procedure status;
删除存储过程
    drop procedure pro1;
"""

内置函数

mysql 内置函数只能在sql语句中使用. 

"ps:可以通过help 函数名    查看帮助信息!"
 1.移除指定字符
Trim、LTrim、RTrim

 2.大小写转换
Lower、Upper

 3.获取左右起始指定个数字符
Left、Right

 4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
        但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
        where Soundex(name)=Soundex('J.Lie')
"""

5.日期格式:date_format

 
# 5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
 
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
	adddate	增加一个日期 
	addtime	增加一个时间
	datediff	计算两个日期差值

流程控制
 

# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;
 
END //
delimiter ;
 
 
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
 
    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;
 
END //
delimiter ;

索引

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
    primary key        主键
    unique key         唯一键
    index key           索引键
上述的三种键在数据查询的时候使用都可以加快查询的速度

     primary key、unique key除了可以加快数据查询还有额外的限制
     index key只能加快数据查询 本身没有任何的额外限制
真正理解索引加快数据查询的含义
    索引的存在可以加快数据的查询 但是会减慢数据的增删

索引：是一种数据结构（既结构数据），换句话说：索引就是一种组织数据的方式.

索引底层原理

树:是一种数据结构 主要用于优化数据查询的操作

hash索引: hash索引是基于hash表实现，记算出hash值对应的k value适合等值查询，不适合范围查询

树索引：

二叉树：非叶子节点只允许最多两个子节点存在，右边的子节点大于当前节点的值，每个节点只存储一个键值和数 据

平衡二叉树：左右高度子节点不超过1、在数据量大的时候会进行多次磁盘io，降低查找效率

B树（平衡树）：每个节点储存更多的键值key和数据data，并且每个节点拥有更多的子节点。 B+树： 在非叶子节点上，不保存数据，只存放键值，能存储更多的键值，所有数据均存储在叶子节点，并且是按照顺序排列 的。查找数据会减少IO，数据查询的效率更快。这使得B+树在做范围查找、分段查找、去重查找、分组查找异常简单。

为什么索引结构默认使用B+树，而不是Hash，二叉树，红黑树？

Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

二叉树：树的高度不均匀，不能自平衡，查找效率跟树的高度有关，并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

参见;平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了 - 知乎

     上述结构都是为了更好的基于树查找到相应的数据

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定，层级越低次数越少
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

"""
聚集索引(primary key)
辅助索引(unique key,index key)
    查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据

覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
    select name from user where name='jason';
非覆盖索引:虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
    select age from user where name='jason';
"""

索引管理

索引建立在表的列上(字段)的。
在where后面的列建立索引才会加快查询速度。
pages<---索引（属性）<----查数据。

索引分类: 主键索引 ,普通索引, 唯一索引., 联合索引

普通索引 一张表中可以有多个普通索引，随便一个字段都可以建立的索引，我们平常建立的索引大部分都是普通索引  id, name, age, addr....

基于id查找数据很快 但是基于addr查找数据就很慢 
      解决的措施可以是给addr添加索引
'''索引虽然好用 但是不能无限制的创建!!!'''

**索引的影响:**
    * 在表中有大量数据的前提下，创建索引速度会很慢
    * 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

联合索引 好几个字段联合起来建立的索引

唯一索引 业务中唯一的字段适合建立唯一索引，一个表中可以有多个唯一索引

主键索引 和唯一索引一样，主键索引也是唯一的，不同的就是，一个表只能有一个主键索引

索引下推技术： 索引下推（index condition pushdown ）简称ICP 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级 索 引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器 然后判断数据是否符合条件， 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服 务器将这一部分判断条件传递给存储引擎，然 后由存储引擎通过判断索引是否符合 MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL服 务器 。 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL 服务器从存储引擎接收数 据的次数。

慢优化查询

explain :索引执行计划查看 rows的大小 rows是核心指标、优化语句 主要在优化rows

explain命令使用方法

mysql> explain select name,countrycode from city where id=1;

explain命令应用

查询数据的方式

1. 全表扫描>>>>在explain语句结果中type为ALL

2. 什么时候出现全表扫描?

        业务确实需要获取所有数据

        不走索引导致的全表扫描>> 没索引,索引创建有问题,语句有问题.

生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null

从上到下，性能从最差到最好，我们认为至少要达到range级别

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select * from city where id=1000000000000000000000000000;

Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer

如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上没有索引

mysql> explain select * from city where countrycode='CHN' order by population;

当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现

key_len: 越小越好

• 前缀索引去控制 rows: 越小越好

详见: 索引与执行计划 - JasonJi - 博客园

索引测试

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
 
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
 
#3. 查看存储过程
show create procedure auto_insert1\G 
 
#4. 调用存储过程
call auto_insert1();

表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;
 
# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢
 
select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢
 
 
"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;
 
alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了
 
create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性
 
# 区分度低的字段不能建索引
drop index idx_name on s1;
 
# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算
 
drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速
 
drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度
 
create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段
 
drop index idx_id on s1
 
create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉 

联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 
 
# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快