Mysql查漏补缺

Mysql查漏补缺

存储引擎

数据库使用存储引擎来进行CRUD的操作，不同的存储引擎提供了不同的功能。Mysql支持的存储引擎有InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等，默认使用InnoDB作为存储引擎。

存储引擎列表

功能	MylSAM	MEMORY	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

其中MEMORY，由于存储是在内存，可以把一些临时数据存放进使用这种存储引擎的Mysql中，当然在目前也有类似功能的项目出现，如Redis，Memcache。

索引

索引就是表的目录，相当于书的目录，可以更快的查找到想要的记录。而索引本身，会单独保存在一个文件中。

优化索引

1、数据类型越小越好：数据类型越小，占用的空间更小，处理速度就会变快。

2、数据类型越简单越好：比如整型，相比于字符型，比较起来的复杂度更低。

3、避免NULL：有NULL值的列很难进行查询优化。

不适合索引的场景

1、查询中很少用到的列。比如student表中的性别列。如果使用索引，反而会增加系统的负担。

2、很少有数据值的列。同上。

3、数据类型是text或bit等数据类型占用空间很大或很小的列。

4、不出现在where条件中的字段。

适合索引的场景

1、表的主键和外键。

2、需要和其他表进行连接的。

3、排序的列。

4、范围搜索的列。

索引的优缺点

优点

1、能够提高对表的查询速度。

缺点

1、创建和维护索引麻烦。

2、虽然会提高查询速度，但是会减慢写入速度。因为在insert或update时可能需要重建索引。

索引分类

1、普通索引

create index index_name on table_name(field_name)

2、唯一索引

表示列值唯一，可以有NULL值。

create unique index index_name on table_name(field_name)

3、复合索引

将几个列作为索引来检索。比如建立了(a,b,c)三个列的组合索引，那么查询的时候，支持a|ab|abc来查找，如果使用bc查找，那么则没有使用复合索引。

注意：如果列中有NULL值，将不会包含在索引中，该复合索引则无效。

create index index_name on table_name(field_name,field_name...)

4、主键索引

列值唯一，不能有NULL值，一张表只能有一个主键索引。

alter table table_name add primary key table_name(field_name)

存储过程

存储过程是为了执行一个SQL指令集合，经过编译存在于数据库中。用户可以根据存储过程的名字并给定参数来进行调用。并且存储过程支持返回多个值。

创建

create procedure procedure_name([in/out/inout] 参数名 参数类型...)
begin
	sql语句;
end

由于sql语句中会使用;作为结束符，所以要使用delimiter来重新定义结束符。

删除

drop procedure procedure_name;

高级用法

if条件

if 判断语句 then
sql;
end if;
if 判断语句 then
sql;
else
sql;
end if;

case

case 变量名
when 值 then
sql;
when 值 then
sql;
...
else
sql;
end case;

while...do（先判断后执行）

while 判断语句 do
sql;
end while;

repeat（先运行后判断）

repeat
sql;
until 判断语句
end repeat;