06mysql数据库对象、事务与锁

06数据库对象本章目标了解MySQL的数据库对象重点介绍三种对象索引：是优化数据库系统的性能，加快查询速度视图：隐藏表结构，简化SQL命令事务：处理数据的同步变化数据库对象数据库对象表视图索引事务存储过程触发器索引数据库索引好比是一本书前面的目录，能加快数据库的查询速度。建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。索引对查询的影响只有几十行的数据表，有没有建立索引，还体会不到查询速度上的差异，可是当一个表里有成千上万行数据的时候，差异就非常明显了。在假设有一个表，表里只有一列，由数值1～1000的1000行组成，现在要想查找到数字1000所在的行。如果没有索引，要从第一行开始匹配，若数值不是1000，则转到下一行进行匹配，这样直到第1000行的时候才能找到数字1000所在行，也就是说服务器进行了1000次的运算。而当在该列上创建一个索引后，则可以直接在索引值中找到1000的位置，然后找到1000所指向的行，在速度上比全表扫描至少快了100倍。当执行涉及多个表的连接查询时，索引将更有价值。索引的优点通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。可以大大加快数据的检索速度，这也是创建索引的最主要的原因。可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。索引的缺点创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。索引类型普通索引

这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。唯一性索引

索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。主键索引

有的数据库自动在主键字段上创建唯一索引聚集索引在聚集索引中，表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。创建索引使用CREATEINDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。 CREATE[UNIQUE]INDEXindex_nameONtbl_name(index_col_name,...)index_name:索引名,索引在一个表中名称必须是唯一的。index_col_name:表示创建索引的列名。格式为:col_name[(length)][ASC|DESC]length表示使用列的前length个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小，从而节省磁盘空间。在某些情况下，只能对列的前缀进行索引。另外还可以规定索引按升序（ASC）还是降序（DESC）排列，默认为ASC。如果一条SELECT语句中的某列按照降序排列，那么在该列上定义一个降序索引可以加快处理速度。UNIQUE:表示创建的是唯一索引创建索引使用ALTERTABLE语句修改表，添加索引。

ALTERTABLE

tbl_nameADD[UNIQUE]INDEXindex_name(index_col_name,...)

创建索引在创建表时创建索引在前面两种情况下，索引都是在表创建之后创建的。索引也可以在创建表时一起创建。

CREATETABLEtbl_name (column_definition, [UNIQUE]INDEXindex_name(index_col_name)

)删除索引使用DROPINDEX语句删除索引语法格式：DROPINDEXindex_nameONtbl_nameindex_name为要删除的索引名tbl_name为索引所在的表。查看索引表的查询原理EXPLAIN命令可以查看一条查询命令是否使用了索引多列索引索引可以是单列索引，也可以是多列索引当执行查询的时候，MySQL只能使用一个索引。如果有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于同时在三个列上创建的多列索引。创建索引的原则索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。不适合创建索引的字段同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：对于那些在查询中很少使用或者参考的列不应该创建索引。对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。对于那些定义为text,image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。视图视图以经过定制的方式显示来自一个或多个表的数据视图可以视为“虚拟表”，其内容由查询定义，不占物理存储空间存储在数据库内的只是一条SELECT语句视图的内容可以来自一张表中的数据，也可以是来自于多张表中的数据，创建视图所依据的表称为“基表”

创建视图使用一条查询命令创建视图

CREATEVIEW

view_name

AS

SELECT...FROM...修改视图视图的修改当基表的表结构发生变化时，用户可以根据自己的需要修改视图

ALTERVIEWview_nameAS

SELECT..FROM删除视图视图的删除不再需要某个视图时，可以将其删除以释放资源。删除视图对基表不产生任何影响

DROPVIEW

view_name视图的优点安全性高：隔离基表结构的改变隐藏的数据的复杂性：筛选基表中的行或列，集中用户使用的数据。降低数据库的复杂程度：简化SQL语句的编写将多个物理数据库抽象为一个逻辑数据库通过重命名列，从另一个角度提供数据对用户：结果更容易理解获得数据更容易对开发人员：限制数据检索更容易维护应用程序更方便视图的使用视图可以和基本表一样被查询，但在下列情况时视图的增，删，改操作会受限制由两个以上的基本表导出的视图视图的字段来自字段表达式函数视图定义中有嵌套查询在一个不允许更新的视图上定义的视图关于视图视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据;另一原因是可使复杂的查询易于理解和使用。视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样又在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改(有一定的限制)、删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化，同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图。事务的应用事务处理在各种管理系统中都有着广泛的应用，很多同步数据库操作大都需要用到事务处理。比如说，在人员管理系统中，删除一个人员，即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！示例

deletefromuserinfowhere~~~

deletefrommailwhere~~

deletefromarticlewhere~~如果没有事务处理，在删除的过程中，假设出错了，只执行了第一句，那么其后果是难以想象的！但用事务处理。如果删除出错，只要rollback就可以取消删除操作（其实是只要没有commit就没有确实的执行该删除操作）事务事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作。这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行。事务是一个不可分割的工作逻辑单元。事务特性原子性一致性隔离性持久性原子性组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。换句话说，事务是不可分割的最小单元。比如：银行转帐过程中，必须同时从一个帐户减去转帐金额，并加到另一个帐户中，只改变一个帐户是不合理的。一致性在事务处理执行前后，MySQL数据库是一致的。也就是说，事务应该正确的转换系统状态。比如：银行转帐过程中，要么转帐金额从一个帐户转入另一个帐户，要么两个帐户都不变，没有其他的情况。隔离性一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说，银行转帐过程中，在转帐事务没有提交之前，另一个转帐事务只能处于等待状态。持续性事务处理的效果能够被永久保存下来。反过来说，事务应当能够承受所有的失败，包括服务器、进程、通信以及媒体失败等等。比如：银行转帐过程中，转帐后帐户的状态要能被保存下来。事务分类MYSQL的事务处理主要有两种方法。用startmit来实现

starttrancation开始一个事务

rollback事务回滚

commit

事务确认直接用set来改变mysql的自动提交模式MYSQL默认是自动提交的，也就是提交一QUERY，它就直接执行！可以通过

setmit=0

禁止自动提交

setmit=1开启自动提交

来实现事务的处理。

示例STARTTRANSACTIONUPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='mary'UPDATEbankSETcurrentMoney=currentMoneyWHEREcustomerName='tom'COMMIT事务回滚starttransaction;insertintotab2values(6,7);savepointp3;select*fromtab2;insertintotab2values(7,8);rollbacktop3;commit;关于事务一个事务可以是一条SQL语句，一组SQL语句或整个程序。可以回退insert、update和delete，不能回退create和drop。事务是一个整体。如果其中一步失败了，那整个操作都取消；如果每步都成功了，则整个操作才完成。123同一时刻，多个并发用户同时访问同一个数据时，仅仅通过事务机制，无法保证多用户同时访问同一数据的数据一致性，有必要引入锁机制实现MySQL的并发访问，锁机制是实现多用户并发访问的基石。MySQL锁机制的基础知识锁机制的必要性MyISAM表的表级锁45InnoDB表的行级锁“选课系统”中的行级锁

锁并发用户访问同一数据，锁机制可以避免数据不一致问题的发生。以场景描述2为例。

锁机制的必要性1．锁的粒度锁的粒度是指锁的作用范围。锁的粒度可以分为服务器级锁（server-levellocking）和存储引擎级锁（storage-engine-levellocking）。MyISAM存储引擎支持表锁。InnoDB存储引擎支持表锁以及行级锁。MySQL锁机制的基础知识2．隐式锁与显式锁MySQL锁分为隐式锁以及显式锁。

MySQL自动加锁称为隐式锁。数据库开发人员手动加锁称为显式锁。MySQL锁机制的基础知识3．锁的类型锁的类型包括读锁（readlock）和写锁（writelock），其中读锁也称为共享锁，写锁也称为排他锁或者独占锁。MySQL锁机制的基础知识读锁允许其它MySQL客户机对数据同时“读”，但不允许其它MySQL客户机对数据任何“写”。MySQL锁机制的基础知识写锁不允许其它MySQL客户机对数据同时读，也不允许其它MySQL客户机对数据同时写。MySQL锁机制的基础知识4．锁的钥匙多个MySQL客户机并发访问同一个数据时，如果MySQL客户机A对该数据成功地施加了锁，那么只有MySQL客户机A拥有这把锁的“钥匙”，也就是说：只有MySQL客户机A能够对该锁进行解锁操作。MySQL锁机制的基础知识5．锁的生命周期锁的生命周期是指在同一个MySQL服务器连接内，对数据加锁到解锁之间的时间间隔。

MySQL锁机制的基础知识任何针对MyISAM表的查询操作或者更新操作，都会隐式地施加表级锁，隐式锁的生命周期非常短暂，且不受数据库开发人员的控制。MyISAM表的表级锁有时需要延长表级锁的生命周期，MySQL为数据库开发人员提供了显示地施加表级锁以及显示地解锁的MySQL命令。MyISAM表的表级锁MyISAM表的表级锁注意事项：read与write选项的功能在于施加表级读锁还是表级写锁。MySQL客户机A使用locktables命令可以同时为多个表施加表级锁（包括读锁或者写锁），并且加锁期间，MySQL客户机A不能对“没有锁定的表”进行更新及查询操作，否则将抛出“表未被锁定”的错误信息。MyISAM表的表级锁如果需要为同一个表同时施加读锁与写锁，需要为该表起两个别名，以区分读锁与写锁。MyISAM表的表级锁readlocal与read选项之间的区别在于：如果MySQL客户机A使用read选项为某个MyISAM表施加读锁，加锁期间，MySQL客户机A以及MySQL客户机B都不能对该表进行插入操作。如果MySQL客户机A使用readlocal选项为某个MyISAM表施加读锁，加锁期间，MySQL客户机B可以对该表进行插入操作，前提是新记录必须插入到表的末尾。MyISAM表的表级锁InnoDB提供了两种类型的行级锁，分别是共享锁（S）以及排他锁（X），其中共享锁也叫读锁，排他锁也叫写锁。在查询（select）语句或者更新（insert、update以及delete）语句中，为受影响的记录施加行级锁的方法也非常简单。InnoDB表的行级锁方法1．在查询（select）语句中，为符合查询条件的记录施加共享锁，语法格式如下所示。select*from表where条件语句lockinsharemode;方法2．在查询（select）语句中，为符合查询条件的记录施加排他锁，语法格式如下所示。select*from表where条件语句forupdate;InnoDB表的行级锁方法3．在更新（insert、update以及delete）语句中，InnoDB存储引擎将符合更新条件的记录自动施加排他锁（隐式锁）。即：InnoDB存储引擎自动地为更新语句影响的记录施加隐式排他锁。InnoDB表的行级锁考虑如下场景：MySQL客户机A获得了某个InnoDB表中若干条记录的行级锁，此时MySQL客户机B出于某种原因需要向该表显式地施加表级锁（使用locktables命令即可），MySQL客户机B为了获得该表的表级锁，需要逐行检测表中的行级锁是否与表级锁兼容，而这种检测需要耗费大量的服务器资源。InnoDB表的意向锁试想：如果MySQL客户机A获得该表若干条记录的行级锁之前，MySQL客户机A直接向该表施加一个“表级锁”（这个表级锁是隐式的，也叫意向锁），MySQL客户机B仅仅需要检测自己的表级锁与该意向锁是否兼容，无需逐行检测该表是否存在行级锁，就会节省不少服务器资源。InnoDB表的意向锁MySQL提供了两种意向锁：意向共享锁（IS）和意向排它锁（IX）。InnoDB表的意向锁意向共享锁（IS）：向InnoDB表的某些记录施加行级共享锁时，InnoDB存储引擎会自动地向该表施加意向共享锁（IS）。也就是说：执行“select*from表where条件语句lockin