MySQL 高级 - 索引，视图与存储过程

索引

优势

• 加快检索效率，降低数据库IO成本
• 降低数据排序的成本，降低CPU的消耗

劣势

• 需要额外维护一张表，表中存储着主键与索引字段，订并指向实体类的记录，所以索引也需要额外的空间
• 索引加快了查询的速度，但是降低了表更新的速度，如Insert，Update，Delete。

索引结构

索引	InnoDB	MyISAM	Memory
B-tree	支持	支持	支持
Hash	不支持	不支持	支持
R-tree	不支持	支持	不支持
Full-text	支持	支持	不支持

• B-tree：最常见的索引类型，大部分索引都支持B-tree索引
• Hash：只有Memory引擎支持
• R-tree：空间引擎是MyISAM 的一个特殊的索引类型，主要用于地理空间的数据类型
• Full-text：主要用于全文索引
• 聚集索引，复合索引，前缀索引，唯一索引默认使用B+tree索引

B-tree

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下:

• 树中每个节点最多包含m个孩子。
• 除根节点与叶子节点外,每个节点至少有[ceil(m/2)]个孩子。
• 若根节点不是叶子节点,则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由n个key与n+1个指针组成,其中[ceil(m/2)-1]<=n<= m-1
  B树比二叉数效率更高，因为层级更小，搜索次数更小。

B+tree

B+Tree为BTree的变种，B+Tree与BTree的区别为:

• n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
• B+Tree的叶子节点保存所有的key信息,依key大小顺序排列。
• 所有的非叶子节点都可以看作是key的索引|部分。
  MySQL中对经典的B+tree索引进行了优化，增加了指向相邻叶子结点的链表指针，使叶子节点形成了双向循环链表，提高了区间访问的性能。

索引分类

• 单列索引：一个索引只包含单列，一个表可以包含多个单列索引。
• 唯一索引：索引列的值必须唯一，但允许有控制。
• 复合索引：一个索引包含多个列。

语句

创建

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name, ...);

alter table tb_name add primary key (col_name, ...);

alter table tb_name add index (col_name, ...);

alter table tb_name add unique index_name (col_name, ...);

alter table tb_name add fulltext index_name (col_name, ...);

查看

show index from table_name;

删除

drop index index_name on table_name;

索引设计原则

• 查询频率高且数据量较大的表建立索引。
• 索引字段的选择，最佳候选列应当从where 子句的条件中提取，如果where 子句中的组合比较多，应挑选最常用，过滤效果最好的字段组合。
• 使用唯一索引，区分度越高，使用索引的效率越高。
• 索引并非越多越好，维护索引也需要一定的代价。
• 使用短索引，索引储存在硬盘中，索引字段长度越小，单个块中存储的字段越多，IO效率提升越大。
• 利用最左前缀，N个列组合而成的组合索引，那么相当于创建了N个索引。如果where自居使用了组成该索引的前几个字段，那么查询SQL可以利用组合索引提升效率。

创建复合索引:
CREATE INDEX idx_ name_ email_ status ON tb seller (NAME , email, STATUS);
就相当于
对name创建索引;
对name，emai1 创建了索引;
对name，emai1, status 创建了索引;

描述表结构

desc table_name;

视图

视图是虚拟表，但是存在通过视图更新源表的情况。

创建

CREATE VIEW <视图名> AS <SELECT语句>; 

查询

select col_name,... from view_name; 

删除

drop view view_name;

存储过程

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

创建存储过程

create procedure procedure_name (params,...)
begin
	--sql 语句
end;

查看存储过程

select name from mysql.proc where db='db_name';

show proc status;

show proc status;

show create proc proc_name;  --查看创建语句

删除存储过程

drop proc if exists proc_name;

调用存储过程

call proc_name;