mysql view index_index_view_procedure_func.md

##索引

索引用于**快速**找出某个列中一特定值的行，如不使用索引，MySQL必须从第1条记录开始然后读完整个表知道找出相关的行。表越大，费时越多，如表中查询的列有一个索引，便可快速到达一个位置去搜寻数据文件。注意如果需要访问大部分行，则顺序读取更快，此时应该避免磁盘搜索。

MySQL列都可以被索引，对相关列使用索引很好的提高SELECT的性能。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎对每个表至少支持16个索引，总长度至少256字节，大多存储引擎有更高限制。

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引，MySQL支持前缀索引(对索引字段的前N个字符创建索引)，其长度和存储引擎相关。

>前缀的限制以字节为单位，而CREATE TABLE语句中的前缀长度解释为字符，因此使用多字节字符集的列指定前缀长度时一定要注意。

MySQL的MyISAM引擎支持全文索引，可用于全文搜索。但只限于CHAR、VARCHAR和TEXT列，不支持局部索引。默认情况下MEMORY存储引擎用HASH索引，但也支持BTREE索引。

索引在创建表的同时创建，也可以随时增加新的索引，创建索引：

```

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[USING index_type]

On tb1_name(index_col_name,...)

index_col_name:

col_name[(length)][ASC|DESC]

```

也可以使用`ALTER TABLE`语法增加索引，语法与`CREATE INDEX`类似。

删除索引：`DROP INDEX index_name ON tb1_name`

索引设计原则：

- 选择最合适的索引列，通常为出现在WHERE字句中的列。

- 使用唯一索引，使用容易区分的各行而不是只有"M"和"F"之类。

- 使用短索引，尽量指定前缀长度。

- 利用最左前缀，多个索引从最左边的索引开始匹配行。

- 不要过度索引

####BTREE索引与HASH索引

HASH索引特性：

- 只用于使用=或<=>操作符的等式比较

- 优化器不能使用HASH索引加速ORDER BY操作

- 只能使用整个关键字来索引一行

- 使用范围查性能不好

对于BTREE使用索引则不受操作符的影响。

##视图

视图是虚拟表，对使用视图的用户基本上透明，视图是在使用时候动态生成的，相对于表的优势：

- 简单 已经是筛选好的表的结果

- 安全 只能访问被允许的结果集

- 数据独立 一旦视图的结构确定，可以屏蔽表结构改变的影响

创建视图需要`CREATE VIEW`的权限，并对查询涉及的列有`SELECT`权限，如使用`CREATE OR REPLACE`或者`DROP`修改视图，那么还需要该视图的`DROP`权限

```

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED | NERGE TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH | CASCADED | LOCAL | CHECK OPTION]

EG:

create or replace view stall_list_view as

select s.staff_id,s.first_name,s.last_name,a.address

from staff as s,address as a

where s.address_id=a.address_id

```

以下类型的视图不可更新：

- 包含聚合函数(SUM、MIN、MAX、COUNT)、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL

- 常量视图

- SELECT中包含子查询

- JION

- FROM一个不能更新的视图

- WHERE字句的子查询引用了FROM字句中的表

WITH [CACSCADED |LOCAL] CHECK OPTION决定了是否允许更新数据使记录不再满足视图的条件，其中：

- LOCAL只需满足本视图的条件就可以更新

- CASCADED则必须满足所有针对该视图的所有视图的条件才可以更新

可以一次删除一个或者多个视图

```

DROP VIEW [IF EXISTS] view_name [,view_name]...[RESTRICT|CASCADE]

```

查看视图通过以下方式都可以：

```

SHOW TABLES

SHOW TABLE STATUS

SHOW CREATE STATUS [FROM db_name] [LIKE 'pattern']

查看系统表information_schema.views

select * from views where table_name='viewname'\G

```

##存储过程和函数

存储过程是事先进过编译并存储在一个数据库中的一段SQL语句的集合，存储过程和函数的区别是函数必须有返回值，存储过程没有，存储过程的参数可用IN、OUT、INOUT类型；而函数必须使用IN类型。

操作存储过程前需要确认用户是否具有相应的权限。创建需要CREATE ROUTINE权限、修改或删除需要使用ALTER ROUTINE权限，执行需要EXECUTE权限。

创建、修改存储过程或函数语法：

```

CREATE PROCEDURE sp_name([proc_parameter[,...]])

[characteristic...] routine_body

CREATE FUNCTION sp_name([func_parameter[,...]])

RETURNS type

[characteristic ...] routine_body

proc_parameter:

[IN |OUT |INOUT] param_name type

func_parameter:

param_name type

type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

|[NOT] DELERMINISTIC

|{CONTAINS SQL |NO SQL|READS SQL DATA|MODIFIES SQL DATA}

|SQL SECURITY {DEFINER|INVOKER}

|COMMENT 'string'

routine_body:

Valid SQL procudure statement or statements

修改

ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]

haracteristic:

{CONTAINS SQL |NO SQL|READS SQL DATA|MODIFIES SQL DATA}

|SQL SECURITY {DEFINER|INVOKER}

|COMMENT 'string'

调用

CALL sp_name([parameter[,...]])

MySQL存储过程和函数中可以包含DDL，可以执行Commit或者Rollback，但不允许LOAD DATA INFILE

eg:

DELIMITER $$

CREATE PROCEDURE film_in_sock(IN p_film_id INT,IN p_store_id INT,OUT p_file_count INT)

READ SQL DATA

BEGIN

SELECT inventory_id

FROM inventory

WHERE film_id=p_film_id

AND store_id=p_store_id

AND inventory_in_stock(inventory_id);

SELECT FOUND_ROWS() INTO p_film_count;

END $$

DELIMITER ;

```

对characteristic特征值部分说明：

一键复制

编辑

Web IDE

原始数据

按行查看

历史