MySQL 索引、试图、存过、函数、触发器
来源:互联网 发布:win7优化精简教程 编辑:程序博客网 时间:2024/05/21 18:30
一、索引
1、 索引概述
MyISAM 和InnoDB 存储引擎的表默认创建的都是BTREE 索引。MySQL 目前还不支持函数索引,但是支持前缀索引,即对索引字段的前N 个字符创建索引。MySQL 中还支持全文本(FULLTEXT)索引,该索引可以用于全文搜索。全文索引总是对整个列进行的,不支持局部(前缀)索引。默认情况下,MEMORY 存储引擎使用HASH 索引,但也支持BTREE 索引。
使用短索引。如果对字符串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200)列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘IO 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
InnoDB 表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,可以有效地减少索引的磁盘占用,提高索引的缓存效果。
创建新索引的语法为:
mysql> ? CREATE INDEX
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html.
URL: http://dev.mysql.com/doc/refman/5.1/en/create-index.html
2、BTREE 索引与HASH 索引
MEMORY 存储引擎的表可以选择使用BTREE 索引或者HASH 索引,两种不同类型的索引各有其不同的适用范围。HASH 索引有一些重要的特征需要在使用的时候特别注意:
只用于使用=或<=>操作符的等式比较。
优化器不能使用HASH 索引来加速ORDER BY 操作。
MySQL 不能确定在两个值之间大约有多少行。如果将一个MyISAM 表改为HASH 索引的MEMORY 表,会影响一些查询的执行效率。
只能使用整个关键字来搜索一行。
而对于BTREE 索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中'pattern'不以通配符开始)操作符时,都可以使用相关列上的索引。
二、试图
MySQL 视图的定义有一些限制,例如,在FROM 关键字后面不能包含子查询,这和其他数据库是不同的
创建视图的语法为:
mysql> ? create view
Name: 'CREATE VIEW'
Description:
Syntax:
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。这个选项与Oracle 数据库中的选项是类似的,其中:
LOCAL 是只要满足本视图的条件就可以更新;
CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新。
如果没有明确是LOCAL 还是CASCADED,则默认是CASCADED。
三、存储过程和函数
存储过程和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN、OUT、INOUT 类型,而函数的参数只能是IN 类型的。
MySQL 的存储过程和函数中允许包含DDL 语句,也允许在存储过程中执行提交(Commit,即确认之前的修改)或者回滚(Rollback,即放弃之前的修改),但是存储过程和函数中不允许执行LOAD DATA INFILE 语句。
和视图的创建语法稍有不同,存储过程和函数的CREATE 语法不支持使用CREATE ORREPLACE 对存储过程和函数进行修改,如果需要对已有的存储过程或者函数进行修改,需要执行ALTER 语法。
创建、修改存储过程或者函数的语法:
mysql> ? CREATE PROCEDURE
Name: 'CREATE PROCEDURE'
Description:
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
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] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement
下面对characteristic 特征值的部分进行简单的说明。
LANGUAGE SQL:说明下面过程的BODY 是使用SQL 语言编写,这条是系统默认的,为今后MySQL 会支持的除SQL 外的其他语言支持的存储过程而准备。
[NOT] DETERMINISTIC:DETERMINISTIC 确定的,即每次输入一样输出也一样的程序,NOT DETERMINISTIC 非确定的,默认是非确定的。当前,这个特征值还没有被优化程序使用。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:这些特征值提供子程序使用数据的内在信息,这些特征值目前只是提供给服务器,并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS SQL 表示子程序不包含读或写数据的语句。NO SQL 表示子程序不包含SQL 语句。READS SQL DATA 表示子程序包含读数据的语句,但不包含写数据的语句。MODIFIES SQL DATA 表示子程序包含写数据的语句。如果这些特征没有明确给定,默认使用的值是CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER }:可以用来指定子程序该用创建子程序者的许可来执行,还是使用调用者的许可来执行。默认值是DEFINER。
COMMENT 'string':存储过程或者函数的注释信息。
查看存储过程或者函数的状态:SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
查看存储过程或者函数的定义:SHOW CREATE {PROCEDURE | FUNCTION} sp_name
除了以上两种方法,我们还可以查看系统表来了解存储过程和函数的相关信息,通过查看information_schema. Routines 就可以获得存储过程和函数的包括名称、类型、语法、创建人等信息。
四、触发器
创建触发器的语法如下:
mysql> ? CREATE TRIGGER
Name: 'CREATE TRIGGER'
Description:
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
其中trigger_time 是触发器的触发时间,可以是BEFORE 或者AFTER,BEFORE 的含义指在检查约束前触发,而AFTER 是在检查约束后触发。
而trigger_event 就是触发器的触发事件,可以是INSERT、UPDATE 或者DELETE。
现在触发器还只支持行级触发的,不支持语句级触发。
可以通过执行SHOW TRIGGERS 命令查看触发器的状态、语法等信息,但是因为不能查询指定的触发器,所以每次都返回所有的触发器的信息,使用起来不是很方便,具体语法如下:
mysql> show triggers \G
另外一个查看方式是查询系统表的information_schema.triggers 表,这个方式可以查询指定触发器的指定信息,操作起来明显方便很多:
mysql> desc triggers;
MySQL 的触发器是按照BEFORE 触发器、行操作、AFTER 触发器的顺序执行的,其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作,那么会整个作为一个事务被回滚(Rollback),但是如果是对非事务表进行的操作,那么已经更新的记录将无法回滚,这也是设计触发器的时候需要注意的问题。
1、 索引概述
MyISAM 和InnoDB 存储引擎的表默认创建的都是BTREE 索引。MySQL 目前还不支持函数索引,但是支持前缀索引,即对索引字段的前N 个字符创建索引。MySQL 中还支持全文本(FULLTEXT)索引,该索引可以用于全文搜索。全文索引总是对整个列进行的,不支持局部(前缀)索引。默认情况下,MEMORY 存储引擎使用HASH 索引,但也支持BTREE 索引。
使用短索引。如果对字符串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200)列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘IO 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
InnoDB 表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,可以有效地减少索引的磁盘占用,提高索引的缓存效果。
创建新索引的语法为:
mysql> ? CREATE INDEX
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html.
URL: http://dev.mysql.com/doc/refman/5.1/en/create-index.html
2、BTREE 索引与HASH 索引
MEMORY 存储引擎的表可以选择使用BTREE 索引或者HASH 索引,两种不同类型的索引各有其不同的适用范围。HASH 索引有一些重要的特征需要在使用的时候特别注意:
只用于使用=或<=>操作符的等式比较。
优化器不能使用HASH 索引来加速ORDER BY 操作。
MySQL 不能确定在两个值之间大约有多少行。如果将一个MyISAM 表改为HASH 索引的MEMORY 表,会影响一些查询的执行效率。
只能使用整个关键字来搜索一行。
而对于BTREE 索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中'pattern'不以通配符开始)操作符时,都可以使用相关列上的索引。
二、试图
MySQL 视图的定义有一些限制,例如,在FROM 关键字后面不能包含子查询,这和其他数据库是不同的
创建视图的语法为:
mysql> ? create view
Name: 'CREATE VIEW'
Description:
Syntax:
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。这个选项与Oracle 数据库中的选项是类似的,其中:
LOCAL 是只要满足本视图的条件就可以更新;
CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新。
如果没有明确是LOCAL 还是CASCADED,则默认是CASCADED。
三、存储过程和函数
存储过程和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN、OUT、INOUT 类型,而函数的参数只能是IN 类型的。
MySQL 的存储过程和函数中允许包含DDL 语句,也允许在存储过程中执行提交(Commit,即确认之前的修改)或者回滚(Rollback,即放弃之前的修改),但是存储过程和函数中不允许执行LOAD DATA INFILE 语句。
和视图的创建语法稍有不同,存储过程和函数的CREATE 语法不支持使用CREATE ORREPLACE 对存储过程和函数进行修改,如果需要对已有的存储过程或者函数进行修改,需要执行ALTER 语法。
创建、修改存储过程或者函数的语法:
mysql> ? CREATE PROCEDURE
Name: 'CREATE PROCEDURE'
Description:
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
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] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement
下面对characteristic 特征值的部分进行简单的说明。
LANGUAGE SQL:说明下面过程的BODY 是使用SQL 语言编写,这条是系统默认的,为今后MySQL 会支持的除SQL 外的其他语言支持的存储过程而准备。
[NOT] DETERMINISTIC:DETERMINISTIC 确定的,即每次输入一样输出也一样的程序,NOT DETERMINISTIC 非确定的,默认是非确定的。当前,这个特征值还没有被优化程序使用。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:这些特征值提供子程序使用数据的内在信息,这些特征值目前只是提供给服务器,并没有根据这些特征值来约束过程实际使用数据的情况。CONTAINS SQL 表示子程序不包含读或写数据的语句。NO SQL 表示子程序不包含SQL 语句。READS SQL DATA 表示子程序包含读数据的语句,但不包含写数据的语句。MODIFIES SQL DATA 表示子程序包含写数据的语句。如果这些特征没有明确给定,默认使用的值是CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER }:可以用来指定子程序该用创建子程序者的许可来执行,还是使用调用者的许可来执行。默认值是DEFINER。
COMMENT 'string':存储过程或者函数的注释信息。
查看存储过程或者函数的状态:SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
查看存储过程或者函数的定义:SHOW CREATE {PROCEDURE | FUNCTION} sp_name
除了以上两种方法,我们还可以查看系统表来了解存储过程和函数的相关信息,通过查看information_schema. Routines 就可以获得存储过程和函数的包括名称、类型、语法、创建人等信息。
四、触发器
创建触发器的语法如下:
mysql> ? CREATE TRIGGER
Name: 'CREATE TRIGGER'
Description:
Syntax:
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
其中trigger_time 是触发器的触发时间,可以是BEFORE 或者AFTER,BEFORE 的含义指在检查约束前触发,而AFTER 是在检查约束后触发。
而trigger_event 就是触发器的触发事件,可以是INSERT、UPDATE 或者DELETE。
现在触发器还只支持行级触发的,不支持语句级触发。
可以通过执行SHOW TRIGGERS 命令查看触发器的状态、语法等信息,但是因为不能查询指定的触发器,所以每次都返回所有的触发器的信息,使用起来不是很方便,具体语法如下:
mysql> show triggers \G
另外一个查看方式是查询系统表的information_schema.triggers 表,这个方式可以查询指定触发器的指定信息,操作起来明显方便很多:
mysql> desc triggers;
MySQL 的触发器是按照BEFORE 触发器、行操作、AFTER 触发器的顺序执行的,其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作,那么会整个作为一个事务被回滚(Rollback),但是如果是对非事务表进行的操作,那么已经更新的记录将无法回滚,这也是设计触发器的时候需要注意的问题。
2 0
- MySQL 索引、试图、存过、函数、触发器
- mysql学习3:索引,触发器,试图
- MYSQL创建存储过程,函数,触发器,索引
- Mysql-触发器、存储过程、索引、事物、函数
- mysql存过学习
- mysql中写存储过程,试图,触发器
- mysql索引视图触发器
- mysql 索引,视图,触发器
- Mysql 聚合函数,内外连接 , 视图 ,索引,触发器, 存储过程
- mysql数据库学习及存过实例
- MySQL优化之表索引、视图、内置函数、预处理、事务处理、存储、触发器
- MySQL——索引 视图 存储过程 触发器 游标 表复制 内置函数 预处理
- MySQL 系列(三)你不知道的 视图、触发器、存储过程、函数、事务、索引、语句
- mysql 存储过程、变量、函数、触发器、游标、视图、查询、索引、事务
- mysql高级包含索引建立优化_函数_存储过程_触发器_及游标
- MYSQL数据库学习----索引和触发器
- MySQL数据库(主键、索引、外键、触发器...)
- oracle 索引,视图,存储过程,触发器 函数
- 相机模型详解
- 证明:素数对中间的数能被6整除
- IOS吐槽狂人:UITabelView全局表头和每个分区的表头和大小,设置区分表头的标题
- jquery省市县三级地址联动
- Android 的搜索机制 II-提供更复杂的搜索功能
- MySQL 索引、试图、存过、函数、触发器
- localStorage本地存储
- 大端模式与小端模式、网络字节顺序与主机字节顺序 (经典)
- GetKeyState()和GetAsyncKeyState()的区别
- MySQL 常用维护命令
- 如何将android studio项目转换成eclipse
- 2049: [Sdoi2008]Cave 洞穴勘测
- 关于导入GPUimage到工程以及简单使用
- android判断当前系统的语言