MySQL-进阶篇 – TechLife

本文最后更新于 91 天前，其中的信息可能已经有所发展或是发生改变。

存储引擎

查看建表语句：show create table 表名;

查看当前数据库支持的存储引擎 show engines;

— 1、在创建表时，指定存储引擎

— 查看当前数据库支持的存储引擎
show engines;

— 查看建表语句
show create table account;

— 创建表 my_syisam，并指定myisam存储引擎

create table my_myisam(
id int
) engine= MyISAM;
show create table my_myisam;

— 创建表my_memory,指定memory存储引擎
create table my_memory(
id int
) engine=Memory;
show create table my_memory;
show ENGINES;

存储引擎特点

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎

特点

DML操作遵循ACID模型，支持事务；行级锁；外键

MyISAM早期的默认存储引擎

特点

不支持事务，外键

支持表锁，不支持行锁

访问速度快

Memory表数据存储在内存中，受硬件、断电等影响，只能用来做临时表或缓存表

特点

内存存放

hash索引（默认）

索引

index是帮助mysql高效获取数据的数据结构（有序）

索引结构

mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，

B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引

Hash索引：底层是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询

R-Tree（空间索引）：是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

Full-text（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式，类似于Lucene, Solr, Es

索引分类

主键索引 primary 表中主键创建的索引

唯一索引 unique 避免表中数据重复

常规索引快速定位特定数据

全文索引 fulltext 全文索引查询的是文文本中的关键词，而不是比较索引中的值

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引（Clustered Index）将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。特点：必须有且唯一

二级索引（Secondary Index）将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。特点：可以存在多个

use study;
— 创建索引
create [unique|fulltext] index index_name on table_name (index_col_name,…);
— 查看索引
show index from table_name;
— 删除索引
drop index index_name on table_name;

— name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);
show index from tb_user;

— 3/为profession、age、status创建联合索引
create index idx_user_pro_age_sta on tb_user(profession, age, status);

create index idx_user_name on tb_user(name);
create unique index idx_user_phone on tb_user(phone);
create index idx_user_pro_age_sta on tb_user(profession, age, status);
create index idx_email on tb_user(email);

— SQL性能分析
— SQL执行频率
— MySQL客户端连接成功后，通过show[session|global]status 命令可以提供服务器状态信息。
— 查询insert/update/delete/select/的访问频次：
show global status like ‘Com_’;
show global status like ‘Com_select’;
show global status like ‘Com_insert’;

— 慢查询日志
— 慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
— mysql的慢查询日fatkun志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：
— 开启MySQL慢日志查询开关
slow_query_log=1
— 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

— 查看每一条SQL的耗时基本情况
show profiles;

— 查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id;

— 查看指定query_id的SQL语句cpu的使用情况
show profile cpu for query query_id;

— explain执行计划
— explain 或者desc命令获取mysql如何执行select语句的信息，包括在select 语句执行过程中表现如何连接和连接的顺序。
— 语法
— explain/desc select 字段列表 from 表名 where 条件;

— Id
— select 查询的序列号，表示查询中执行select自居或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越优先执行）
— select_type
— 表示select的类型，常见的取值有simple（简单表，即不使用连接或者子查询）、primary（主查询，即外层的查询）、union（union中的第二个或者后面的查询语句）、subquery（select/where 之后包含了子查询）等

— type
— 表示连接类型，性能由好到差的连接类型为null、system、const、eq_ref、range、index、all

— possible_key
— 显示可能应用在这张表上的索引，一个或多个

— key
— 实际使用的索引，如果为null，则没有使用索引。

— key_len
— 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长短越短越好
— rows
— mysql认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是精确的

— filtered
— 表示返回结果的行数占续读取行数的百分比，filtered的值越大越好

— 索引-使用规则-验证索引效率
— 语法： \G
select * from tb_user where id = ‘2’ \G;

— 针对字段创建索引
create index idx_sku_sn on tb_sku(sn);

— 然后再次执行相同的SQL语句，再次查看SQL的耗时
select * from tb_sku where sn = ‘10000000123123’\G;

— 最左前缀法则
— 如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

第三章-SQL优化

插入数据

大批量插入数据

use study;
— 视图
— 视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
— 通俗的将，视图只保存了查询的SQL逻辑，不在村查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
— 创建
— create [or replace] view 视图名称[(列名列表)] AS select语句 [with[cascaded|local] check OPTION]

— 创建视图
create or replace view stu_v_1 as select id, name from student where id <=10;

— 查询视图
— 查看创建视图语句： show create view 视图名称;
— 查看视图数据：select * from 视图名称…;
show create view stu_v_1;
select * from stu_v_1;

— 修改视图
— 方式一：create [or replace] view 视图名称[(列名列表)] as select 语句[with [cascaded|local] check option]
— 方式二：alter view 视图名称[(列名列表)] as select语句 [with[cascaded|local] check OPTION]
create or replace view stu_v_1 as select id, name, no from student where id <=10;
alter view stu_v_1 as select id, name from student where id <=10;
— 删除
— drop view [if exists] 视图名称[,视图名称]….
drop view if exists stu_v_1;
— ———————————————————
— 视图-检查选项cascaded

create or replace view stu_v_1 as select id, name from student where id <=20;
select * from stu_v_1;

insert into stu_v_1 VALUES(31, ‘测试’);
— ———————————————————

— 视图的检查选项
— 当使用with check option字句创建视图时， MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded和local，默认值为cascaded

— CASCADED
create or replace view stu_v_1 as select id, name from student where id <=20;

insert into stu_v_1 VALUES(32, ‘测试’);

create or replace view stu_v_2 as select id, name from stu_v_1 where id >=10 with cascaded check option;

insert into stu_v_2 VALUES(36, ‘测试’); — 报错：1369 – CHECK OPTION failed ‘study.stu_v_2’

— Local
….with local check option;

— 要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
1、聚合函数或窗口函数（sum(),min(),max(),count()等)
2、DISTINCT
3、group by
4、having
5、union 或者 union all

— 视图作用
— 简单：简化用户对数据的理解、操作。常用的查询可以被定义为视图
— 安全：通过视图用户只能查询和修改他们所能见到的数据
— 数据独立：屏蔽真实表结构变化带来的影响

— 存储过程

— 特点：封装、复用
— 基本语法
— 创建
create procedure 存储过程名称([参数列表])
begin
— SQL语句
end;

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;

— 注意：在命令行中，执行创建存储过程的SQL时，需要通过delimiter指定SQL语句的结束符。
create procedure p1()
BEGIN
select count(*) from student;

END$$

— 命令行中的结束符：$$

存储过程

变量

系统变量是mysql提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL），会话变量（SESSION）

查看系统变量

show [session|global] variables; –查看所有系统变量

show [session|global] variables like ‘…’; — 可以通过like模糊匹配方法查找变量

select @@[session|global]系统变量名; –查看指定变量的值

设置系统变量

set [session|global] 系统变量名=值;

set @@[session|global] 系统变量名=值;

用户自定义变量：是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用”@变量名” 使用就可以。作用域：当前连接

赋值

set @变量名=expr [,@变量名=expr]…;

(推荐)set @变量名 :=expr [, @变量名 :=expr] ..;

select @变量名 :=expr [,@变量名=expr]…;

select 字段名 info @变量名 from 表名;

— 赋值

set @myname = 'itcast';
set @myage := 10;
set @mygender := '男', @myhobby := 'java';

select @mycolor := 'red';
select count(*) into @mycount from user;

使用

select @变量名;

select @myname, @myage, @mygender, @myhobby, @mycolor, @mycount;

局部变量：使用declare声明，可用存储过程内的局部变量和输入参数，局部变量的范围是在其内成名的begin .. end块。

declare 变量名 变量类型 [default ... ];

变量类型就是数据库字段类型：int、bigint、char、varchar、date、time等

create procedure p2()
begin
declare user_count int default 0;
select count(*) into user_count from user;
select user_count;
end;

call p2();

语法：

 if 条件1 then
...
elseif 条件2 then
...
else 
...
end if;

create procedure p3()
BEGIN
declare score int DEFAULT 50;
declare result varchar(10);
if score >= 50 then 
set result := "优秀";
elseif score >=100 then 
set result := "及格";
ELSE 
set result := "不及格";
end if;
select result;
END;

call p3();

case

语法一：

case 值

when 值 then statement_list1

…

end case;

语法二：

case

when search_condition1 then statement_list1

…

end case;

create procedure p6(in month int)

begin

declare result VARCHAR(10);

CASE

WHEN month >=1 and month <=3 THEN

set result := ‘第一季度’;

WHEN month >=4 and month <= 6 THEN

set result := ‘第二季度’;

WHEN month >= 7 and month <=9 THEN

set result := ‘第三季度’;

ELSE

set result :=’第四季度’;

END CASE;

select concat(‘您输入的月份为：’, month, ‘，所属的季度为：’,result);

end;

call p6(4);

while语法

while 条件 do

sql逻辑

end while;

loop循环

leave：配合循环使用，退出循环

iterate：必须在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP

SQL逻辑

end loop [end_label];

leave label; –退出指定标记的循环体

iterate lable; –直接进入下一次循环

create procedure p9(in n int)
BEGIN
declare total int default 0;
sum:loop
if n<=0 THEN
leave sum;
end if;

set total := total + n;
set n := n-1;
end loop sum;
select total;
end;

call p9(100);

游标cursor 用来存储查询结果集的数据类型

声明游标

declare 游标名称 cursor for 查询语句;

打开游标

open 游标名称;

获取游标记录

fetch 游标名称 into 标量[,变量];

发送评论 编辑评论

推荐文章

发送评论编辑评论