[MySQL]进阶篇-Day2

学习使用工具

《数据库系统概念（中文第六版）》

黑马程序员 MySQL数据库入门到精通 https://www.bilibili.com/video/BV1Kr4y1i7ru/

https://hillzhang1999.gitee.io/2020/05/29/shu-ju-ku-fu-xi-ji-yu-mysql/#toc-heading-76

一、索引失效情况与索引优化

索引失效情况
- 索引列运算
  
  在索引列上进行运算操作，索引将失效。
  
  有效索引：select * from tb_user where phone = '17799990015';
  
  无效索引：select * from tb_user where substring(phone,10,2) = '15';
- 字符串不加引号
  
  字符串类型字段使用时，不加引号，索引将失效。
  
  有效索引：select * from tb_user where phone = '17799990015';
  
  无效索引：select * from tb_user where phone = 17799990015;
- 模糊查询
  
  如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
  
  有效索引：select * from tb_user where profession like '软件%';
  
  无效索引：select * from tb_user where profession like '%工程'
- or连接条件
  
  用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
  
  select * from tb_user where id = 10 or age = 23;
  
  由于age没有索引，所以即使id有索引，索引也会失效。实际是当or连接的条件，左右两侧字段都有索引时，索引才会生效。
- 数据分布影响
  
  如果MySQL评估使用索引比全表更慢，则不使用索引。如果走全表扫描更快，则放弃索引，走全表扫描。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。
索引优化
- SQL提示
  
  在查询的时候，可以自己来指定使用哪个索引。需要借助于SQL提示来完成。SQL提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
  - use index ：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
    
    select * from tb_user use index(idx_user_pro) where profession = '软件工程';
  - ignore index ：忽略指定的索引。
    
    select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
  - force index ：强制使用索引。
    
    select * from tb_user force index(idx_user_pro) where profession = '软件工程';
- 覆盖索引
  
  尽量使用覆盖索引，减少select *。覆盖索引是指查询使用了索引，并且需要返回的列在该索引中已经全部能够找到，不需要回表查询数据。
  
  如图，id是主键，是一个聚集索引。name字段建立了普通索引，是一个二级索引（辅助索引）。
  - select * from tb_user where id = 2;
    
    根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
  - selet id,name from tb_user where name = 'Arm';
    
    虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。
  - selet id,name,gender from tb_user where name = 'Arm';
    
    由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。
- 前缀索引
  
  当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。
  
  create index idx_xxxx on table_name(column(n)) ;
  
  例：create index idx_email_5 on tb_user(email(5));表示为tb_user表的email字段，建立长度为5的前缀索引。
  
  前缀长度可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
- 单列索引与联合索引
  
  单列索引只包含单个列，联合索引包含了多个列。在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
- 索引设计原则
  - 针对于数据量较大，且查询比较频繁的表建立索引。
  - 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  - 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  - 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  - 尽量使用联合索引，减少单列索引。查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  - 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  - 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

二、SQL优化

插入数据

insert

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');

优化一：批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

优化二：手动控制事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

优化三：主键顺序插入，性能要高于乱序插入

大批量插入数据load

如果一次性需要插入大批量数据（比如: 几百万的记录），使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。可以执行如下指令，将数据脚本文件中的数据加载到表结构中。

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;

主键优化
- 数据组织方式
  
  在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。行数据都是存储在聚集索引的叶子节点上的。在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。
- 页分裂
  
  页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。当向存满的页中插入数据时，写满的页放不下新的数据，就会开辟一个新的页，将原本页后一半的数据移动到新的页，随后将数据插入，并重新排列页间的列表指针。这种现象称之为 "页分裂"，是比较耗费性能的操作。
  
  因此主键顺序插入，性能要高于乱序插入，因为顺序插入不会发生页分裂。
- 页合并
  
  当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
- 索引设计原则
  - 满足业务需求的情况下，尽量降低主键的长度。
  - 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  - 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
  - 业务操作时，避免对主键的修改。
order by优化

MySQL的排序有两种方式：
- Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
- Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。在创建联合索引时，可以指定列的排序方式。例如age 升序排序，phone 倒序排序：

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

优化原则：
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
group by优化

在分组操作时，可以通过索引来提高效率。分组操作时，索引的使用也是满足最左前缀法则的。
limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
count优化

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。如果数据量很大，在执行count操作时，是非常耗时的。
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。
- InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
如果说要大幅度提升InnoDB表的count效率，主要的优化思路只能是自己计数。
- count（*）：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
- count（主键）：InnoDB 引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。
- count（字段）：
  - 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出
    来，返回给服务层，服务层判断是否为null，不为null，计数累加。
  - 有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返
    回给服务层，直接按行进行累加。
- count（数字）：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽
量使用 count()。
update优化

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

三、视图

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

创建：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]

查询

查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称 ...... ;

修改

方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH
[ CASCADED | LOCAL ] CHECK OPTION ]

方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |
LOCAL ] CHECK OPTION ]

删除：DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

检查选项

可以通过视图来插入更新数据。创建视图时有时会指定条件，可以使用视图的检查选项来保证插入、删除、更新符合视图的定义。

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED 。
- CASCADED级联
  
  比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。
- LOCAL本地
  
  比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。则在执行检查时，知会检查v2，不会检查v2的关联视图v1。
视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
- 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
- DISTINCT
- GROUP BY
- HAVING
- UNION 或者 UNION ALL
视图的作用
- 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
- 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
- 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。

四、存储过程

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

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点
- 封装，复用：可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
- 可以接收参数，也可以返回数据：再存储过程中，可以传递参数，也可以接收返回值。
- 减少网络交互，效率提升：如果涉及到多条SQL，每执行一次都是一次网络传输。而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

基本语法

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
	select count(*) from student;
end;
-- 调用
call p1();
-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;
-- 删除
drop procedure if exists p1;

变量
- 系统变量
  
  是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。
- 用户定义变量
  
  是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用就可以。其作用域为当前连接。
```
-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;
select @abc;
```
- 局部变量
  
  是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。
```
-- 声明局部变量 - declare
-- 赋值
create procedure p2()
begin
    declare stu_count int default 0;
    select count(*) into stu_count from student;
    select stu_count;
end;
call p2();
```

IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;

参数

类型	含义	备注
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;

案例：

create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
    	set result := '优秀';
    elseif score >= 60 then
    	set result := '及格';
    else
    	set result := '不及格';
    end if;
end;
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
select @result;

case

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，执行statement_list2， 否则就执行 statement_list
CASE case_value
    WHEN when_value1 THEN statement_list1
    [ WHEN when_value2 THEN statement_list2] ...
    [ ELSE statement_list ]
END CASE;

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成立时，执行statement_list2， 否则就执行 statement_list
CASE
    WHEN search_condition1 THEN statement_list1
    [WHEN search_condition2 THEN statement_list2] ...
    [ELSE statement_list]
END CASE;

while

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;

repeat

repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环。

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
    SQL逻辑...
    UNTIL 条件
END REPEAT;

loop

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：
- LEAVE ：配合循环使用，退出循环。
- ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
```
[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
```

游标

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下：

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    -- 声明游标, 存储查询结果集
    declare u_cursor cursor for select name,profession from tb_user where age <=uage;
    
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    
    -- 开启游标
    open u_cursor;
    while true do
    	-- 获取游标记录
        fetch u_cursor into uname,upro;
        -- 插入数据到新表中
        insert into tb_user_pro values (null, uname, upro);
    end while;
    -- 关闭游标
    close u_cursor;
    
end;
call p11(30);

上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;

handler_action 的取值：
    CONTINUE: 继续执行当前程序
    EXIT: 终止执行当前程序
condition_value 的取值：
    SQLSTATE sqlstate_value: 状态码，如 02000
    SQLWARNING: 所有以01开头的SQLSTATE代码的简写
    NOT FOUND: 所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

对于上一部分中存在的报错问题，可以通过条件处理程序解决。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    -- 声明游标, 存储查询结果集
    declare u_cursor cursor for select name,profession from tb_user where age <=uage;
    -- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
	declare exit handler for SQLSTATE '02000' close u_cursor;
	-- 或 declare exit handler for not found close u_cursor;

    
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    
    -- 开启游标
    open u_cursor;
    while true do
    	-- 获取游标记录
        fetch u_cursor into uname,upro;
        -- 插入数据到新表中
        insert into tb_user_pro values (null, uname, upro);
    end while;
    -- 关闭游标
    close u_cursor;
    
end;
call p11(30);

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
    -- SQL语句
    RETURN ...;
END ;

characteristic说明：

DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL ：不包含 SQL 语句。
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;
    return total;
end;
select fun1(50);

posted @ 2023-05-02 22:44 无机呱子阅读(24) 评论(0) 收藏举报

刷新页面返回顶部

无机呱子

[MySQL]进阶篇-Day2

[MySQL]进阶篇-Day2

学习使用工具

一、索引失效情况与索引优化

二、SQL优化

三、视图

四、存储过程

公告