MySQL进阶（当前结束，未完成全部学习）

1.标题MySQL体系结构

2. 存储引擎简介

存储引擎就是存储数据，建立索引，更新/查询数据等技术的实现方式，存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被称为表类型

语句：

在建表时选择引擎
creat table user(
	字段1,字段2
)Engine = innodb;

Engine = innodb；默认是innodb引擎

查看表的存储引擎
Show creat table 表名；

查询当前数据库支持的存储引擎
Show engines；

注：Myisam是早期MySQL的默认存储引擎

3.存储引擎的特点

1.Innodb

Innodb是一种兼顾高可靠和高性能的通用引擎，在mysql5.5之后，innodb是默认的mysql存储引擎

1.特点

1. DML操作遵循ACID模型，支持事务
2. 行级锁，提高并发访问性能
3. 支持外键foreign key约束，保证数据的完整性和正确性

2.文件xxx.ibd:xxx代表表名，innodb引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
参数：innodb_file_per_table

2.Myisam

介绍：是最早mysql的默认引擎
特点:
1.不支持事务，不支持外键
2.支持表锁，不支持行锁

4. 索引

索引（index）是帮助mysql高效获取数据的数据结构（有序），在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构是以某种方式引用（指向）数据，这样就可以在数据结构之上实现高级查找算法，这种数据结构就是索引。

优势：
提高数据检索的效率，降低io成本
通过索引列1对数据进行排序，降低数据排序的成本，降低cpu的消耗

劣势：
索引也是要占用空间的
索引调大大的提高的查询效率，同时也降低了表的更新速度，如对表进行insert、update、delete时，效率降低。

索引结构
Mysql的索引实在存储引擎层实现的，不同的存储引擎又不同的结构，主要包含以下几种

1.索引支持情况：

1.二叉树：

顺序插入时，会形成一个链表，查询性能大大降低。大数据情况下，层级较深，检索速度慢。

2.红黑树：

大数据情况下，层级较深，检索速度慢。

3.B+tree

所有的元素都会出现在叶子节点，叶子节点是存储数据的，叶子节点会形成一个单向链表，除叶子节点都是索引。
Mysql索引数据结构对经典的b+tree进行了优化，增加一个指向叶子节点的链表指针，就行程了带有顺序指针的b+tree，提高区间访问性能

4.Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash’，映射到对应的槽位上，然后存储在hash表中。如果两个（或多个）键值映射岛同一个槽位上，他们就产生了hash冲突（也称为哈市碰撞），可以通过链表来解决。


Hash索引特点

1. Hash索引只能用于对等比较（=，in），不支持范围查询（between，<,>,…）
2. 无法利用索引完成排序操作
3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于b+tree索引
   但在hash冲突下效率会降低

存储引擎支持
在mysql中，支持hash索引的是memory引擎，而innodb中具有hash功能，hash索引是存储引擎根据b+tree索引在指定条件下自动构建的。

为什么innodb选择b+tree索引结构？
相对于二叉树，层级更少，搜索效率高，
对于b-tree，无论是叶子节点还是非叶子节点都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，同样要保存大量数据，只能增加数的高度，导致性能降低。

2.索引分类

注：在表创建时某一字段使用了唯一约束，表会自动创建唯一索引。

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

聚集索引的选取规则：

1. 如果存在主键，主键就是聚集索引
2. 如果不存在主键，将使用第一个唯一（inoque）索引作为聚集索引
3. 如果表没有主键或没有合适的唯一索引，则innodb会自动生成一个rowid作为隐藏的聚集索引

3.索引语法

创建索引
Create [unique | fulltext] index 索引名 on 表名 （字段名1、字段名2…）；
[unique | fulltext]unique或fulltext为可选参数

查看索引
Show index form 表名；

删除索引
Drop index 索引名 on 表名；

注：创建索引命名规范idx_表名_字段名1_字段名2…

1.Sql性能分析

查询全局（global）或当前（session）inser、update、select、delete语句使用次数；

Show global/session status like ‘Com_______’;
global/session 全局/或当前会话可选参数

2.慢查询日志

查询慢查询日志是否开启

show  variables like 'show_query_log';

在linux中开启：

3.查看语句执行时间

show profiles ;

explain执行计划
explain或者desc命令获取mysql如何执行select语句信息，包括在select语句执行中如何表现何连接和连接的的顺序。
语法：
直接在select前加explain或desc

explain select 字段列表 from 表名 where 条件。

4.索引使用规则

1.最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则是指查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）
注：最左列只要存在就会使用，与位置无关

2. 范围查询

在复合索引当中，出现范围查询（>,<）,范围查询右侧的列索引失效，可以使用>=,<=来解决

3. 索引列运算

不要在索引列上进行运算操作，索引将失效

4. 字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

5. 模糊查询

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引将失效

6. Or连接的条件

Or连接条件的索引必须在or两边都有索引，如果只有一边又索引，索引失效。

7.数据分布影响

如果mysql评估使用索引比全表更慢，则不会使用索引

8.Sql提示

Use index（索引名）建议mysql使用那个索引，但mysql会根据查询速度判断是否使用你的建议
Ignore index（索引名）忽略索引，不使用那个索引
Force index（索引名）强制使用那个索引

9.覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。虽然使用了索引但是比一般的索引多查寻了一次，select *很容易造成回表查询

10.前缀索引

只匹配设定字符数量的

11.单列索引与联合索引

单列索引：一个索引只包含了单个列
联合索引：一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑正对于字段建立索引时，建议建立联合索引，而非单列索引

5.索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。数据大于100万条，1千或1万不用建立索引
2. 针对于常作为查询条件(where)、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高（身份证，手机号等），使用索引的效率越高，相对于（状态，性别，名字）区分度低，性能越低。
4. 如果时字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要尽量控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改查的效率。
7. 如果索引列不能存储null值，请在创建表时使用not null约束它。当优化器知道每列是否包含null值时，它可以更好的确定那个索引最有效地用于查询。

6.存储过程

介绍：
存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库sql语言的代码封装与重用。

特点：
封装复用，可以接受参数，也可以返回数据，减少网络交互，效率提升。

创建存储过程
create procedure p1()
begin
   多条sql语句；
end;

调用存储过程
call p1();

查看存储过程
select * from information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 表名;
show create procedure p1;

删除存储过程
drop procedure if exists p1;

if exists关键字:如果存在

注：在命令行中，执行创建存储过程的sql时，需要通过关键字delimiter指定sql语句的结束符，否则会报错
Delimiter $$ 指定两个$为结束符号。

7.变量

1.系统变量

是Mysql服务器提供，不是用户定义的，属于服务器层面，分为全局变量（global）、会话变量（session）。

查看系统变量默认是会话
show variables ;
通过模糊匹配查看变量
show session variables like 'auto%';
查看具体是那个变量
select @@autocommit;
设置系统变量
set session autocommit = 1;

注：当服务器重启后设置会还原

2.用户定义变量

用户定义变量是用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接使用’@变量名’使用就可以.其作用域为当前连接

赋值
set @myage = 10;
set @myname := '劫';
set @mygender := '男',@mysqlsex = '004';
推荐使用:=,因为在mysql当中赋值也是=

在表中查询的数据赋值给变量
select COUNT(*) into @mycount from tx_user;

使用
select @myage,@myname,@mygender,@mysqlsex;
select @mycount;

注意:在mysql中的变量无需初始化,如果没有赋值变量的值为null;

3.局部变量

是根据需要定义在局部生效的变量,访问之前,需要declare声明.可以用作存储过程的局部变量和输入参数,局部变量的范围是在其内声明的begin…end块

create procedure p2()
begin
	声明局部变量需要定义数据类型,如果没有默认值可以不用default;
    declare stu_count int default 0;
    select COUNT(*) into stu_count from tx_user;
    select stu_count;
end;

4.参数

语法：

create procedure p3(in/out/inout 参数名字 数据类型)
begin
end;

5.if语句

语法：

create procedure p3()
begin
    if 判断条件 then
        符合判断条件执行SQL语句
    elseif 判断条件 then
        se符合判断条件执行SQL语句
    else
        符合判断条件执行SQL语句
    end if;
end;

示例：

create procedure p3()
begin
    declare socre float default 56.1;
    declare result varchar(10);
    if socre>85 then
        set result := '优秀';
    elseif socre >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
    select result;
end;

call p3();

6.cese语句

语法：

create procedure p6(in month int)
begin
    case
        when 判断条件 then
           符合判断条件执行的SQL语句；
        when 判断条件 then
           符合判断条件执行的SQL语句；
        else
           符合判断条件执行的SQL语句；
    end case;
end;

示例：

create procedure p6(in month int)
begin
    declare a varchar(20);
    case
        when month>=1 and month <=3 then
           set a := '春季';
        when month>=4 and month <=6 then
           set a := '夏季';
        when month>=7 and month <=9 then
           set a := '秋季';
        when month>=11 and month <=12 then
            set a := '冬季';
        else
            set a := '输入错误';
    end case;
    select a;
end;
call p6(13);

注意:存储过程在写错后无法通过修改原来的代码再执行让存储过程正确,只有删除原来的存储过程,修改为正确后再执行.

7.while循环语句

语法：

create procedure addStdunt()
begin
    while 判断条件 do
        sql语句;
    end while ;
end;

示例：

create procedure addStdunt()
begin
    declare i int default 1;
    while i<1000000 do
        insert into student values (i,concat('张',i),lpad(round(rand()*100),2,0));
        set i := i+1;
    end while ;
end;
call addStdunt();

8.repeat循环控制语句

Repeat是有条件控制语句的循环控制语句，当满足条件的时候推出循环

语法:

create procedure p4()
begin
    repeat
          sql语句
    until 条件
    end repeat;
end;

示例：

create procedure p4(in num int)
begin
    declare num1 int;
    declare num2 int default 1;
    repeat
           set num1 := num2 + num;
           set num2 := num2 +1;
    until  num = num2
    end repeat;
    select num1;
end;

8.loop循环语句

Loop实现简单的循环，如果不在sql逻辑中增加循环退出的条件，可以用来实现简单的死循环。Loop可以配合一下两个语句使用

1. Leave：配合循环使用，退出循环
2. Iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

语法：

create procedure p8()
begin
	#sum:是循环标记，类似取别名
    sum:loop
        if 判断条件 then
            leave sum;
        end if;
        if 判断条件 then
            iterate sum;
        end if;
    end loop sum;
end;

示例：

create procedure p8(in n int)
begin
    declare n1 int default 0;
    sum:loop
        if n<=0 then
            leave sum;
        end if;
        if n%2 = 1 then
            set n := n - 1;
            iterate sum;
        end if;
        set n1 := n1 + n;
        set n := n - 1;
    end loop sum;
    select n1;
end;

5.游标

游标（cursor）使用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理，游标包括游标的声明、open、fetch和close

语法：

声明游标
Declare 游标名称 cursor for 查询语句；

打开游标
Open 游标名称；

获取游标
Fetch 游标名称 into 变量1，变量2…；

关闭游标
Close 游标名称；

示例：

查询一个表的数据集合后创建新表并插入

create procedure p9(in score int)
begin
	创建两个变量来接收游标的数据
    declare uname varchar(20);
    declare ustatus int(20);
    声明游标
 	declare s_cursor cursor for select name,status from tx_user where score = tx_user.id;
	创建查询数据后的新表
  	create table if not exists students(
        id int primary key auto_increment,
        name varchar(20),
        status int
    );
    开启游标
    open s_cursor;

    while true do
         获取游标
            fetch s_cursor into uname,ustatus;
            insert into students values (null,uname,ustatus );
    end while;

end;

注：游标必须声明在变量之后

1.条件处理程序

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

语法：

Declare 处理方式 handler for 条件；

处理方式
Continue：继续执行当前程序
Exit：终止执行当前程序

条件
状态码，如02000
Sqlseate 状态码

所有以01开头的sqlstate代码的简写
Sql warning

所有以02开头的sqlstate代码的简写
Not found

所有没有被sqlwarning或not found捕获的sqlstate代码的简写
Sqlexception

示例：

create procedure p9(in score int)
begin
    declare uname varchar(20);
    declare ustatus int(20);
    #声明游标
    #注：游标必须声明在变量之后
    declare s_cursor cursor for select name,status from tx_user where score = tx_user.id;
   
    条件处理程序
    
    错误状态码
    #declare exit handler for sqlstate '02000' close s_cursor;
	以0开头的错误码
    declare exit handler for not found close s_cursor;

    create table if not exists students(
        id int primary key auto_increment,
        name varchar(20),
        status int
    );
    #开启游标
    open s_cursor;

    while true do
#         获取游标
            fetch s_cursor into uname,ustatus;
            insert into students values (null,uname,ustatus );
    end while;

    close s_cursor;
    select * from students;

end;

2.存储函数

存储函数是·有返回值的存储过程，存储函数的参数只能是in类型的（不写也是in类型）

用的比较少一般用存储过程，而且必须有返回值

语法：

Creat function 存储函数名称（参数1，参数2…）
Returns type 说明
Begin
	Sql语句
End；

注：在mysql创建存储函数时必须有说明，否则报错

说明：
Deterministic：相同的输入参数总是产生相同的结果
No sql：不包含sql语句
Reads sql data：包含读取数据的语句，但不包含写入数据的语句。

6.触发器

介绍：触发器是与表有关的数据库对象，在insert/updata/delete之前或之后，触发并执行触发器中定义的sql语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名old和new来引用触发器中发生变化的记录内容，这与其他数据库是相似的。现在触发器还支持行级触发，不支持语句级触发。

语法：
创建触发器：

Creat trigger 触发器名字
Before/after insert/update/detele On 表名 for each row 
注：触发时机二选一，更新操作三选一。
Begin
	Sql语句；
End；

查看触发器
Select triggers；

删除触发器
Drop trigger 数据库名字 触发器名字

注:删除触发器的上号可以没有数据库名字，如果没有默认是当前触发器。
示例：

创建插入数据时的触发器
create trigger tb_user_inert_trigger
    after insert on tx_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) values
    (
     null,
     'insert',
     now(),
     NEW.id,
     concat(
         '插入的内容为:id=',new.id,
         '，name=',new.name,
         '，phone=',new.phone,
         '，email=',new.email,
         '，stauts=',new.status
         )
    );
end;