mysql视图，索引，存储过程，日志
站长
· 阅读数 68
一、视图
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 存储方式分
- B-树索引
- 哈希索引
2.3.2 逻辑区分
普通索引(index/key)
eg:create index index_id on tb_student(id);唯一索引(unique)
eg:create unique index index_id on tb_student(id);- 主键索引(primary key)
空间索引(spatial)
eg:create spatial index index_line on tb_student(line);全文索引(fulltext)
eg:create fulltext index index_info on tb_students(info); info:char varchar text 等类型
2.3.3 实际使用区分
单列索引
create index index_addr on tb_student(address(4));多列索引
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、提高插入数据的速度
转载自:https://segmentfault.com/a/1190000042197913