Mysql进阶

一、存储引擎

1.1 查看当前数据库支持的存储引擎

show engines;
1

1.2 指定存储引擎

create table my_myisam(
	id int,
	name varchar(10)
) engine = MyISAM ;

1
2
3
4
5

二、索引

2.1 索引结构

2.2 索引分类

2.3 索引语法

//查看索引
show index from student;

//创建索引
create [unique | fulltext] index idx_name on student(sname);

//删除索引
drop index idx_name on student;
1
2
3
4
5
6
7
8

2.4 前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘10，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

//语法
create index idx_xxx on table_name(cloumn(n))

//索引选择性越高查询效率越高
select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5))/count(*) from tb_user;
1
2
3
4
5
6

三、SQL性能分析

3.1 SQL执行频率

show global status like 'Com_______';
1

3.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_ query_ time, 单位:秒,默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf) 中配置如下信息:

#开启MySQL慢日志查询开关
slow_query_log=1

#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

1
2
3
4
5
6

3.3 profile 详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持profile操作: .

 select @@have_profiling;
//查看profiling是否开启
select @@profiling;

//开启profiling
set profiling=1;

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

//查看指定query_ id的SQL语句各个阶段的耗时情况
show profiles for query query_id;

//查看指定query_ id的SQL语句cpu的耗时情况
show profiles cpu for query query_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.4 explain执行计划

//查看字段是否走了索引
explain select * from tb_user;

1
2
3

四、视图

视图不是一个真正的表，它的数据依赖于创建时指定的sql语句，但是视图可以进行增删改操作，

4.1 创建视图

 create or replace view stu_v_1 as select * from student;

//给视图加上检查选项,检查增删改操作是否符合条件，默认cascaded ，也可以写local
 create or replace view stu_v_1 as select * from student where id<=20 with cascaded check option;
1
2
3
4

4.2 查询视图

//查询视图创建语句
show create views stu_v_1;

//查看视图数据
select * from stu_v_1;
1
2
3
4
5

4.3 修改视图

//方式一
 create or replace view stu_v_1 as select * from student;

//方式二
alter view stu_v_1 as select * from student;
1
2
3
4
5

4.4 删除视图

drop view if exists stu_v_1;
1

五、存储过程

//创建
create procedure p1()
begin
    select count(*) from student;
end;

//调用
call p1();

//查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test_data';

//删除
drop procedure if exists p1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在命令行中创建存储过程要用delimiter $$改变结束符号，此时不再是分号结束，而是$$为结束符号

---

变量

//查看全局变量或会话变量
show global | session variables;


//设置变量值
set session | global autocommit = 0 ;

//用户自定义变量
set @myname = 'tom';

//使用
select @myname;


//声明局部变量
create procedure p3()
begin
    declare stu_count int default 0;
    set stu_count := 10;
    select sid,sname from student;
    select stu_count;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

if语法

create procedure p4()
begin
    declare score int default 58;
    declare result varchar(10);
    if score>=85 then
        set result := '优秀';
    elseif score >=60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    select result;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13

参数

create procedure p5(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;


create procedure p6(inout score double)
begin
    set score := score *0.5;
end;

set @score = 100;
call p6(@score);
select @score;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

case语法

create procedure p7(in month int)
begin
    declare result varchar(10);
    case
        when month=1 then
            set result := '一月';
        when month=2 then
            set result := '二月';
        else
            set result := '未开发';
    end case;
    select result;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13

while语法

create procedure p8(in num int)
begin
    declare total int default 0;
    while num>0 do
        set total := num+total;
        set num:=num-1;
        end while;
    select concat('和为',total);
end;
1
2
3
4
5
6
7
8
9

repeat语法

create procedure p9(in num int)
begin
    declare total int default 0;
    repeat
        set total := num+total;
        set num:=num-1;
    until num<0
    end repeat;
    select concat('和为',total);
end;
1
2
3
4
5
6
7
8
9
10

loop循环语法

create  procedure p10(in num int)
begin
    declare total int default 0;
    sum:loop
        if num<0 then
            # 退出循环
            leave sum;
        end if;
        if num%2!=0 then
            set num := num-1;
            # 跳过本次循环
            iterate sum;
        end if;
        set total := num+total;
        set num := num-1;
    end loop sum;
    select concat('和为',total);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

cursor游标

//声明游标
declare s_cursor cursor for select sname from student where sid<7;

//打开游标
open s_cursor;

//获取游标记录
fetch s_cursor into uname;

//关闭游标
close s_cursor;
1
2
3
4
5
6
7
8
9
10
11

存储函数

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;
1
2
3
4
5
6
7
8
9
10

六、触发器

//创建触发器
create trigger student_trigger
    after insert on student for each row
begin
    insert into student_log values(null,concat(new.sid,new.sname,'被插入了'));
end;

//查看触发器
show triggers ;

//删除触发器
drop trigger student_trigger;
1
2
3
4
5
6
7
8
9
10
11
12