Mysql索引、视图、存储过程和函数、触发器、存储引擎（一）

Mysql高级，数据库优化

一、知识点

1、索引

创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)
查看索引
show index 1 from table_name;
删除索引
DROP INDEX index_1 name ON tbl_name;

设计原则：

• 对查询频次较高，且数据量比较大的表建立索引。

• 索引字段的选择，最佳候选列应当从where子句的条件中提取

• 使用唯一索引，区分度越高，使用索引的效率越高。

• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。

• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。

• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

  创建复合索引:
  CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
  就相当于
  对name 创建索引 ;
  对name , email 创建了索引 ;
  对name , email, status 创建了索引 ;

2、视图

创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
查看视图
show views;
删除视图
DROP VIEW view_name ;

视图相对于普通的表的优势主要包括以下几项。
**简单：**使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤
好的复合条件的结果集。
**安全：**使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但
是通过视图就可以简单的实现。
**数据独立：**一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表
修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

3、存储过程和函数

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

3.1存储过程

创建存储过程
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end ;
调用存储过程
call procedure_name() ;
删除存储过程
DROP PROCEDURE [1 IF EXISTS] sp_name ；

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能。

知识小贴士
DELIMITER
该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

例子1：
DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;//声明countnum是一个int类型
select count(*) into countnum from city;//复制,也可使用set countnum=10;
select countnum;
END$
DELIMITER ;


例子2（if条件）：
根据定义的身高变量，判定当前身高的所属的身材类型
180 及以上 ----------> 身材高挑
170 - 180 ---------> 标准身材
170 以下 ----------> 一般身材
//in输入out输出

delimiter $
create procedure pro_test5(in height int , out description varchar(100))
begin
if height >= 180 then
set description='身材高挑';
elseif height >= 170 and height < 180 then
set description='标准身材';
else
set description='一般身材';
end if;
end$
delimiter ;

//调用
call pro_test5(168, @description)$
select @description$


例子3（case条件）
给定一个月份, 然后计算出所在的季度
delimiter $
create procedure pro_test9(month int)
begin
declare result varchar(20);
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 = '第三季度';
when month >= 10 and month <=12 then
set result = '第四季度';
end case;
select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ;
end$
delimiter ;

知识小贴士
@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量

三种循环
题目：计算从1加到n的值

例子1（while）
---------------------------------------
while search_condition do
statement_list
end while;
---------------------------------------
delimiter $
create procedure pro_test8(n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num = num + 1;
end while;
select total;
end$
delimiter ;


例子2（repeat）
语法结构
---------------------------------------
REPEAT
statement_list
UNTIL search_condition
END REPEAT;
----------------------------------------
delimiter $
create procedure pro_test10(n int)
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
until n=0
end repeat;
select total ;
end$
delimiter ;


例子3（loop）
---------------------------------------
[begin_label:] LOOP
statement_list
END LOOP [end_label]
----------------------------------------
退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现,leave用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。下面是一个使用 LOOP 和 LEAVE 的简单例子 , 退出循环：

delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
declare total int default 0;
ins: LOOP
IF n <= 0 then
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END$
delimiter ;

3.2存储函数

语法结构
CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;

例子：
定义一个存储过程, 请求满足条件的总记录数 ;
delimiter $
create function count_city(countryId int)
returns int
begin
declare cnum int ;
select count(*) into cnum from city where country_id = countryId;
return cnum;
end$
delimiter ;

调用：
select count_city(1);
select count_city(2);

4.触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集
合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持
行级触发，不支持语句级触发。

语法结构
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] -- 行级触发器
begin
trigger_stmt ;
end;


例子1：
DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on table_name1
for each row
begin
insert into table_name2 (...)
values(...);
end $
DELIMITER ;

二、存储引擎

1、 最常用的存储引擎

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是
MyISAM，5.5之后就改为了InnoDB。另外两种 MEMORY、MERGE ， 了解即可。

1.1 InnoDB

是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

具有事务控制
start transaction;
insert into goods_innodb(id,name)values(null,'Meta20');
commit;

1.2MyISAM

MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发
性要求不是很高，那么选择这个存储引擎是非常合适的。