MySQL数据库学习笔记（三）——索引和触发器

目录

MySQL存储引擎

索引

索引分类

索引设计原则

创建索引的方式

触发器

创建触发器

查看触发器

删除触发器

---

MySQL存储引擎

在介绍MySQL索引之前，有必要介绍一下MySQL数据库中的存储引擎；不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，下面介绍三种常见、常用的。

注：MySQL5.5.5之后InnoDB存储引擎为默认存储引擎。

InnoDB存储引擎	MyISAM存储引擎	MEMORY存储引擎
支持提交、回滚和崩溃恢复事务；锁定在行级	拥有较高的插入、查询速度，但不支持事务	每个表多达32个索引，每个索引16列
为处理巨大数据量的性能设计，特别适合处理多重并发的更新请求	每个表最大索引数为64个	不支持BLOB和TEXT列
支持外键完整性约束	BLOB和TEXT列可以被索引
支持自动增加列AUTO_INCREMENT属性	NULL值被允许在索引列中

 

MySQL常见存储引擎比较

索引

索引：索引是对数据库中一列或者多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

MySQL中索引的存储类型有两种：BTREE（B+树索引）和HASH（哈希索引）；MyISAM和InnoDB存储引擎只支持BTREE索引。

索引的优点

1. 通过创建唯一索引，可以保证数据表中每一行数据的而唯一性。
2. 可以大大的加快数据的查询速度，这是创建索引的主要原因。
3. 实现数据的参考完整性方面，可以加速表和表之间的连接。
4. 在使用分组和排序子句进行数据查询时，减少相应时间。

索引的缺点：

1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2. 索引需要磁盘空间，除数据表占数据空间之外，每一个索引还要占用一定的物理空间，如果有大量索引，索引文件可能比数据文件更快达到文件最大值。
3. 当对表中的数据进行增加、删除和修改时，索引也需要维护。

索引分类

普通索引(Normal)	基本索引类型，允许在定义索引的列中插入重复和空值
唯一索引(UNIQUE)	索引列的值必须唯一，但允许有空值；若还是组合索引，则列值的组合必须唯一；主键索引是一种特殊的唯一索引，不允许有空值。
全文索引(FULL TEXT)	类型为FULLTEXT，在定义索引的列上进行全文查找，允许这些列中插入重复和空值。全文索引可以在CHAR、VACHAR、TEXT类型上创建。（只有MyISMA存储引擎支持）
空间索引(SPATIAL)	对空间数据类型GEOMETRY 、POINT、LINESTRING、POLYGON建立的索引，创建索引的列必须声明为NOT NULL（只有MyISMA存储引擎支持）
单列索引	即索引中只包含单个列，一张表可以有多个单列索引。
组合索引	即索引中有多列，由多列组合而成的索引

 

索引设计原则

1. 索引占据磁盘空间，不宜过多，且表中索引过多会对DELETE、UPDATE等语句的性能有影响。
2. 避免对不必要字段添加索引，对查询较多列创建索引。
3. 数据量少的表最好不使用索引，数据量较少使用索引没有效果。（当你的程序需要优化，或者sql查询执行缓慢的时候，一般就是考虑建立索引的时候。）
4. 当索引列的列值具有唯一性时，使用唯一索引。
5. 在频繁进行排序或分组的列上建立索引。

创建索引的方式

查看某张表的索引：SHOW INDEX FROM 表名；

创建普通索引：ALTER TABLE 表名 ADD INDEX  索引名 (加索引的列) 

创建聚合索引：ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列1,加索引的列2) 

删除某张表的索引：DROP INDEX 索引名 ON 表名;

创建唯一索引：ALTER TABLE 表名 ADD  UNIQUE INDEX  索引名 (加索引的列) 

创建全文索引：ALTER TABLE 表名 ADD  FULLTEXT INDEX 索引名 (加索引的列) 

创建空间索引：ALTER TABLE 表名 ADD  SPATIAL  INDEX 索引名 (加索引的列) 

索引的使用注意事项

1.多列索引遵循最左前缀原则，使用多列索引时查询字段必须包含第一个索引列。如：索引由idcard,name,photo组成，查询字段必需包含idcard；不然不走索引。

2.使用LIKE字段时，第一个字符为%时不会使用索引；只有”%”不在第一位时索引才会生效。如：LIKE  ‘%x%’不会走索引。

3.使用OR关键字时，两边查询字段均需为索引才会使用索引。

4.使用不等于（<,>,!=）的时候无法使用索引，会导致索引失效。

5.is null或者is not null 也会导致无法使用索引。

触发器

触发器：嵌入到MySQL的一段程序，由事件来触发某个操作，包含INSERT、UPDATE、DELETE

创建触发器

CREATE   TRIGGER  触发器名字 触发时机[before、after]  触发事件 ON 表名 FOR EACH ROW 触发器程序体。

例子：

CREATE TRIGGER  add_role  AFTER INSERT ON  sys_user  FOR  EACH  ROW

BEGIN

INSERT  INTO  sys_role  (name,roleid) VALUES (New.user_name,1);

END  

当sys_user表有记录插入时在用户权限表中插入一天新数据给用户赋予默认权限。

NEW和OLD  用来表示新增的数据和原来数据表中的数据

NEW关键字一般用在插入和修改时

OLD关键字一般用在删除和修改时

查看触发器

SHOW TRIGGERS

Trigger: 触发器名称

Event：触发事件（INSERT、 UPDATE 、DELETE）

Table:触发器所作用的表名称

Statement:触发器程序体

Timing:触发时机

删除触发器

DROP  TRIGGER [数据库名] 触发器名

数据库名是可选的，如果从当前数据库删除可省略。

注：

1. 对于相同表，相同事件只能创建一个触发器。
2. 即时删除不使用的触发器，否则容易造成数据混乱。