mac中的MySQL事务和索引和存储过程

最新推荐文章于 2024-06-19 08:00:00 发布

原创最新推荐文章于 2024-06-19 08:00:00 发布 · 574 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #事务 #索引 #存储过程

数据库学习专栏收录该内容

14 篇文章

订阅专栏

一、事务

事务主要用于处理操作量大，复杂度高的数据。

比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

1、在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

2、事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

3、事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

1、原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

2、一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

3、隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

4、持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

使用示例：

一、提交

1、开始事务：begin;

2、数据操作：insert，或update，或delete

示例：insert into bookTable (id,name,price) values (1004,'iOS面试指南',9.99);

3、提交事务：commit;

示例：

begin;
insert into bookTable (id,name,price) values (1004,'iOS面试指南',9.99);
commit;

二、回滚

1、开始事务：begin;

2、数据操作：insert，或update，或delete

示例：insert into bookTable (id,name,price) values (1008,'iOS develop',19.00);

3、回滚动事务：rollback;

示例：

begin;
insert，或update，或delete
示例：insert into bookTable (id,name,price) values (1008,'iOS develop',19.00);
rollback;

二、索引

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

MySQL中的索引的存储类型有两种：

1、BTREE，也就是用树来存储该字段；

2、HASH，也就是用Hash值来存储该字段；

索引有四类：

1、单列索引（普通索引，唯一索引，主键索引），即一个索引只包含单个列，一个表可以有多个单列索引。

（1）普通索引：基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

（2）唯一索引：索引列中的值必须是唯一的，但是允许为空值。

（3）主键索引：一种特殊的唯一索引，不允许有空值。

2、组合索引，在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。即一个索引包含多个列。

3、全文索引：在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

4、空间索引：对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。创建空间索引时，使用SPATIAL关键字；且要求引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

索引的优点

对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引的缺点

1、索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2、建立索引会占用磁盘空间的索引文件。

查看索引：show index from 表名;

示例：show index from personTable;

创建索引

1、无索引表添加普通索引create：create index 索引名称 on 表名 (表中创建索引的字段名);

示例：create index indexPerson on personTable (name);

2、无索引表添加普通索引alter...add table 表名 add index 索引名称 (表中创建索引的字段名);

示例：alter table personTable add index indexName (name);

3、新建表时创建唯一索引unique：create table 表名 (字段名, ...,unique 索引名称 (表中创建索引的字段名));

示例：create table bookTable (id int not null,name varchar(20),price double,unique indexName (name));

或唯一索引alter..add unique：alter table 表名 add unique 索引名称 (表中创建索引的字段名);

示例：alter table personTable add unique indexName (name);

5、主键索引primary key：alter table 表名 add primary key 索引名称 (表中创建索引的字段名);

示例：alter table personTable add primary key indexID (id);

6、全文索引fulltext：alter table 表名 add fulltext 索引名称 (表中创建索引的字段名);

示例：alter table personTable add fulltext indexName (name);

7、组合索引：alter table 表名 add index 索引名称 (表中创建索引的字段名1,表中创建索引的字段名2,...);

示例：alter table personTable add index indexName (personID,name);

删除索引

1、alter table 表名 drop index 索引名称;

示例：alter table personTable drop index indexPerson;

2、drop index 索引名称 on 表名;

示例：drop index indexName on personTable;

使用索引

注意：

1、创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

三、存储过程

存储例程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令。

存储过程的优点：

1、增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2、标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

3、较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

4、减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

5、作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

创建存储过程——使用存储过程——删除存储过程。

创建存储过程语法：

声明语句结束符，以区分系统结束符——声明存储过程——begin——语句体——end结束符——恢复系统结束符。

DELIMITER // 

CEATE PROCEDURE demo_in_parameter(IN p_in int) 

BEGIN 

…. 

END

DELIMITER ;

示例：

delimiter ##

create procedure saveNewBook(in bookID int,bookName varchar(20),bookPrice double)

begin

insert into bookTable (id,name,price) values (bookID,bookName,bookPrice);

end##

delimiter ;

调用存储过程：call 存储过程名称（参数）

示例：call saveNewBook(1010,'C语言',29.9);

删除存储过程：drop procedure if exists 存储过程名称;

示例：drop procedure if exists saveNewBook;