关于mysql:mysql视图索引存储过程日志

一、视图

1.1、什么是视图？

 虚构存在的表，也有行 & 列形成，但并不理论存在于数据库中，数据库中只寄存视图的的
定义，并没有寄存视图的数据，数据寄存在视图的实在表中。实在表中的数据 =》视图中的数据 

1.2、视图和数据表的区别

• 视图不是数据库中实在的表，而是一张虚构表，其构造和数据是建设在对数据中实在表的查问根底上的
• 存储在数据库中的查问操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查问所援用的理论表，援用视图时动静生成这些数据
• 视图没有理论的物理记录，不是以数据集的模式存储在数据库中的，所对应的数据来自于实在表
• 视图的建设和删除只影响视图自身，不影响对应的根本表

1.3、视图的长处

• 定制用户数据，聚焦特定的数据
• 简化数据操作
• 进步数据的安全性
• 共享所需数据
• 更改数据格式
• 重用 SQL 语句

二、视图操作

2.1、创立视图

create view 视图名 as select 语句
留神：select 语句限度：用户除了领有 create view 权限外，还具备操作中波及的根底表和其余视图的相干权限
    select 语句不能引用零碎或用户变量
    select 语句不能蕴含 FROM 子句中的子查问
    select 语句不能引用预处理语句参数 

2.2、查看视图

2.2.1 查看视图的字段信息

desc 视图名;

2.2.2 查看指定视图的详细信息

show create view 视图名;

2.2.3 查看数据库中所有定义的视图

show table status where comment='view' \G
show full tables where table_type='view';

2.3、批改视图

2.3.1 根底语法

alter view 视图名 as select 语句 

2.3.2 批改视图内容

 能够应用 UPDATE、DELETE 或 INSERT 等语句更新根本表的内容
不可更新的状态：聚合函数 SUM()、MIN()、MAX()、COUNT() 等。DISTINCT 关键字。GROUP BY 子句。HAVING 子句。UNION 或 UNION ALL 运算符。位于抉择列表中的子查问。FROM 子句中的不可更新视图或蕴含多个表。WHERE 子句中的子查问，援用 FROM 子句中的表。ALGORITHM 选项为 TEMPTABLE（应用长期表总会使视图成为不可更新的）的时候。eg:
        alter view view_students_info as select id,name.score from studentinfo;
        update view_students_info set score=90.00 where id=2;
        insert into view_students_info vlaues(3,'tom',33.00);
        delete from view_studentss_info wher id=3;

2.4、删除视图

2.4.1 根底语法

drop view 视图 1, 视图 2,..., 视图 n;

2.4.1 删除视图

drop view if exists 视图名;

二、索引

2.1、what?

 索引是一种非凡的数据库构造，由数据表中的一列或多列组合而成，能够用来
疾速查问数据表中有某一特定值的记录 

2.2、优缺点

 长处：通过创立惟一索引能够保障数据库表中每一行数据的唯一性。能够给所有的 MySQL 列类型设置索引。能够大大放慢数据的查问速度，这是应用索引最次要的起因。在实现数据的参考完整性方面能够减速表与表之间的连贯。在应用分组和排序子句进行数据查问时也能够显著缩小查问中分组和排序的工夫
毛病：创立和保护索引组要消耗工夫，并且随着数据量的减少所消耗的工夫也会减少。索引须要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占肯定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。当对表中的数据进行减少、删除和批改的时候，索引也要动静保护，这样就升高了数据的保护速度。

2.3、索引分类

2.3.1 存储形式分

1. B- 树索引
2. 哈希索引

2.3.2 逻辑辨别

1. 一般索引 (index/key)

    eg:create index index_id on tb_student(id);

2. 惟一索引 (unique)

    eg:create unique index index_id on tb_student(id);

3. 主键索引 (primary key)

4. 空间索引 (spatial)

    eg:create spatial index index_line on tb_student(line);

5. 全文索引 (fulltext)

    eg:create fulltext index index_info on tb_students(info);
        info:char varchar text 等类型
   

2.3.3 理论应用辨别

1. 单列索引

    create index index_addr on tb_student(address(4)); 

2. 多列索引

    create index index_na on tb_student(name,address);
   

2.4、索引操作

2.4.1 创立索引

1) 应用 create index 语句
    create index 索引名 on 表名 (列名 [ 长度] [asc|desc]);
2) 应用 CREATE TABLE 语句
    create table 表名 (列 1 数据类型 约束条件, 列 2 数据类型 约束条件,key|index [ 索引名] [索引类型] (列名))
    create table 表名 (列 1 数据类型 约束条件, 列 2 数据类型 约束条件,unique [key|index] [索引名] [索引类型] (列名))

2.4.2 查看索引

show index from 表名;

2.4.3 删除索引

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;

2.4.4 批改索引

 先删除，在创立 

2.5、索引在什么状况下不会被应用？(索引生效)

1. 查问语句中应用 LIKE 关键字
    如果匹配字符串的第一个字符为“%”，索引不会被应用
2. 查问语句中应用多列索引
    多列索引是在表的多个字段上创立一个索引，只有查问条件中应用了
    这些字段中的第一个字段，索引才会被应用
3. 查问语句中应用 OR 关键字
    如果 OR 前后有一个条件的列不是索引，那么查问中将不应用索引 

2.6、怎么设计出更高效的索引

1. 抉择唯一性索引
2. 为常常须要排序、分组和联合操作的字段建设索引
3. 为常作为查问条件的字段建设索引
4. 限度索引的数目
5. 尽量应用数据量少的索引
6. 数据量小的表最好不要应用索引
7. 尽量应用前缀来索引
8. 删除不再应用或者很少应用的索引

三、存储过程

3.1、what?

 将罕用或简单的工作事后用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因而称为存储过程

3.2、长处

 封装性、可加强 SQL 语句的性能和灵活性、可缩小网络流量、高性能、进步数据库的安全性和数据的完整性、使数据独立

3.3、存储过程操作

3.3.1 创立

create procedure 过程名 ([过程参数]) 过程体
过程参数格局：in|out|inout 参数名 类型
过程体：begin
        sql 语句
    end
完整版：delimiter //
    create procedure 过程名 ([in|out|inout 参数名 类型])
    begin
    sql 语句;
    end //

3.3.2 查看存储过程

 查看存储过程的状态
    show procedure status like 存储过程名字;
查看存储过程的定义
    show create procedure 存储过程名;

3.3.3 批改存储过程

alter procedure 存储过程名 [特色]

** 特色 ** 指定了存储过程的个性，可能的取值有：contains SQL 示意子程序蕴含 SQL 语句，但不蕴含读或写数据的语句。NO SQL 示意子程序中不蕴含 SQL 语句。reads SQL DATA 示意子程序中蕴含读数据的语句。modifies SQL DATA 示意子程序中蕴含写数据的语句。SQL security {definer |invoker} 指明谁有权限来执行。definer 示意只有定义者本人才可能执行。invoker 示意调用者能够执行。comment 'string' 示意正文信息。

3.3.4 删除存储过程

drop procedure if exists 过程名

四、存储函数

4.1、创立存储函数

create function sp_name ([func_parameter[...]])
returns type
[charecteristic...] routine_body

 代码解析：sp_name 参数：示意存储函数的名称；func_parameter：示意存储函数的参数列表；returns type：指定返回值的类型；characteristic 参数：指定存储函数的个性，该参数的取值与存储过程是一样的；routine_body 参数：示意 SQL 代码的内容，能够用 BEGIN...END 来标示 SQL 代码的开始和完结。func_parameter 能够由多个参数组成，其中每个参数由参数名称和参数类型组成
[IN | OUT | INOUT] param_name type;

eg:

mysql> USE test;
Database changed
mysql> delimiter //
mysql> create function func_student(id INT(11))
    -> return VARCHAR(20)
    -> comment '查问某个学生的姓名'
    -> begin
    -> return(select name from to tb_student where tb_student.id=id);
    -> end //
Query OK, 0 rows affected (0.10 sec)
mysql> DELIMITER ;

4.2、查看存储函数

show function status like 存储函数名;
show create function 存储函数名;
select * from information_schema.Routines where routine_name= 存储函数名;

4.3、批改存储函数

alter function 存储函数名 [特色...]

4.4、删除存储函数

drop function if exists 存储函数名 

五、调用存储过程和存储函数

5.1、调用存储过程

call sp_name([parameter[...]]);
# sp_name 示意存储过程的名称，parameter 示意存储过程的参数。

5.2、调用存储函数

select 存储函数名 ([parameter[...]])

六、mysql 变量

6.1、定义

declare var_name[,...] type [default value]

其中：declare 关键字是用来申明变量的；var_name 参数是变量的名称，这里能够同时定义多个变量；type 参数用来指定变量的类型；default value 子句将变量默认值设置为 value，没有应用 default 子句时，默认值为 NULL。

eg:
declare my_sql INT DEFAULT 10;

6.2、为变量赋值

 第一种形式：set var_name = expr[,var_name = expr]
其中：SET 关键字用来为变量赋值；var_name 参数是变量的名称；expr 参数是赋值表达式。第二种形式：select...into 语句为变量赋值

select col_name [...] into var_name[,...]
from table_name Where condition

其中：col_name 参数示意查问的字段名称；var_name 参数是变量的名称；table_name 参数指表的名称；condition 参数指查问条件。

七、mysql 日志

7.1、查看谬误日志

 谬误日志作用：要记录 MySQL 服务器启动和进行过程中的信息、服务器在运行过程中产生的故障和异常情况

查看日志文件的地位：show variables like 'log_error';
通过地位查看谬误日志信息：cat 日志文件信息;

7.2、删除谬误日志

mysqladmin -uroot -p flush-logs

解析：执行该命令后，MySQL 服务器首先会主动创立一个新的谬误日志，而后将旧的谬误日志更名为 filename.err-old。

7.3、二进制日志

 作用：次要用于记录数据库的变动状况，即 SQL 语句的 DDL 和 DML 语句，不蕴含数据记录查问操作

查看二进制日志是否开启
show variables like 'log_bin';

 关上二进制日志步骤
1. 批改 mysql 配置
    vi /etc/my.cnf
2. 配置信息
    server-id=12345
    log-bin=mysql-bin
3. 重启 Mysql
    centos6:service mysqld restart
    centos7:systemctl restart mysqld

1. 查看二进制日志文件列表
    show binary logs;
2. 查看以后正在写入的二进制日志文件
    show master status;
3. 查看二进制日志文件内容
    mysqlbinlog filename.number
    eg:mysqlbinlog mysqlbin.000001

7.4、删除二进制日志

1. 删除所有二进制日志
    reset master;

2. 依据编号删除二进制日志
    purge master logs to 'filename.number'; 删除编号小于 number 的日志
eg:purge master logs to 'mylog.000004';
    代码执行完后，编号为 000001、000002 和 000003 的二进制日志将被删除。3. 依据创立工夫删除二进制日志
    purge master logs to 'yyyy-mm-dd hh:MM:ss'; 删除在指定工夫之前创立的所有二进制日志
eg：purge master logs to '2022-07-19 16:38:00';
    代码执行完后，2022-07-19 16:38:00 之前创立的所有二进制日志将被删除。

7.5、暂停二进制日志

set sql_log_bin=0/1;
0 示意暂停二进制日志性能，1 示意开启二进制性能。

7.6、二进制还原数据库

mysqlbinlog filename.number | mysql -u root -p

7.7、通用查问日志

 作用：通用查问日志（General Query Log）用来记录用户的所有操作，包含启动和敞开 MySQL 服务、更新语句和查问语句

命令查看通用查问日志是否开启：show variables like '%general%';

 配置通用查问日志
1. 批改 mysql 配置
    vi /etc/my.cnf
2. 配置信息
    log=mysql-general
3. 重启 Mysql
    centos6:service mysqld restart
    centos7:systemctl restart mysqld

进行通用查问日志
    set global general_log=off;

删除通用查问日志
    mysqladmin -uroot -p flush-logs

7.8、慢查问

 作用：记录在 MySQL 中执行工夫超过指定工夫的查问语句。通过慢查问日志，能够查找出哪些查问语句的执行效率低，以便进行优化

慢查问开启状态：show variables like 'slow_query%';

 查问超过多少秒才记录:show variables like 'long_query_time';    
    

 参数阐明如下：slow_query_log：慢查问开启状态
slow_query_log_file：慢查问日志寄存的地位（个别设置为 MySQL 的数据寄存目录）long_query_time：查问超过多少秒才记录


启动设置慢查问日志两种办法:
办法一：批改 my.cnf 配置文件
        log-slow-queries=dir\filename
        long_query_time=n

    其中：dir 参数指定慢查问日志的存储门路，如果不指定存储门路，慢查问日志将默认存储到     MySQL 数据库的数据文件夹下。filename 参数指定日志的文件名，生成日志文件的残缺名称为 filename-slow.log。如果    不指定文件名，默认文件名为 hostname-slow.log，hostname 是 MySQL 服务器的主机名。“n”参数是设定的工夫值，该值的单位是秒。如果不设置 long_query_time 选项，默认工夫为 10 秒。办法二:
    set global slow_query_log=ON/OFF;
    set global long_query_time=n;


删除慢查问日志:mysqladmin -uroot -p flush-logs

7.9、查问 mysql 全局变量

show global variablse/show variables

八、mysql 性能优化

8.1、what?

 性能优化就是在不影响零碎能正确运行的前提下，运行速度更快，实现特定性能所需的工夫更短

优化准则：缩小零碎的瓶颈，缩小资源的占用，减少零碎反馈的速度。

8.2、如何定位效率低下的 SQL？

 两种办法：办法一：show status like 'value';
value 参数是罕用的几个统计参数，罕用参数介绍如下：Connections：连贯 MySQL 服务器的次数；Uptime：MySQL 服务器的上线工夫；Slow_queries：慢查问的次数；Com_select：查问操作的次数；Com_insert：插入操作的次数，对于批量插入操作，只累加一次；Com_update：更新操作的次数；Com_delete：删除操作次数。办法二：应用慢查问日志

8.3、解析定位效率低下的 SQL

8.3.1 剖析查问语句

 通过对查问语句的剖析，能够理解查问语句执行的状况，找出查问语句执行的瓶颈，从而优化查问语句。mysql 中提供了 EXPLAIN 语句和 DESCRIBE 语句，用来剖析查问语句
explain 语法：explain [extended] select select_optios;
解释: 应用 extended 关键字,explain 语句将产生附加信息。select_options 是
    select 语句的查问选项，包含 from where 子句等等。

 查问后果进行解释阐明：

8.3.2 索引对查问速度的影响

 举例说明：应用索引和不应用的区别
不应用索引 rows 列为 4523，应用后为 1 

索引生效的几种形式
1. 应用 like 关键字的查问语句

 在应用 like 关键字进行查问的查问语句中，如果匹配字符串的第一个字符为 "%"，索引不会起作用。只有 "%" 不在第一个地位，索引才会起到作用 

2. 应用多列索引的查问语句

 对于多列索引，只有查问条件中应用了这些字段中 ** 第 1 个字段 ** 的时候，索引才会被应用

3. 应用 or 关键字的查问语句

 应用语句的查问条件中只有 or 关键字，且 or 前后的两个条件中的列都有索引时，查问中
才应用索引。否则，查问将不实用索引

8.4、MySQL 优化数据库构造的 3 种办法

形式一：分解表

 有些表在设计时设置了很多的字段，而有些字段的应用频率非常低。这样当这个表的数据量很大时，查问数据的速度就会很慢

优化形式：对于这种状况，咱们能够将这些应用频率较低的字段分离出来造成新表

形式二：减少两头表

 表连贯会升高数据库的查问速度，所以对于常常应用表连贯查问的表，咱们能够建设两头表来进步查问速度

形式三：减少冗余字段

 个别状况下，设计数据库时应尽量让表合乎三大范式。然而，有时为了进步查问速度，能够无意识地在表中减少冗余字段
eg:
    学生的信息存储在 student 表中，院系信息存储在 department 表中。通过 student 表中的 dept_id 字段与 department 表建设关联关系。如果要查问一个学生所在系的名称，就必须从 student 表中查找学生所在院系的
    编号 (dept_id），而后依据这个编号去 department 查找系的名称。这个连贯查问会节约很多的工夫。因而能够在 student 表中减少一个冗余
    字段 deptname，用来存储学生所在院系的名称。这样就不必每次都进行连贯操作了

8.5、进步插入数据的速度