項目九索引和事務任務目標任務一創(chuàng)建和查看索引任務二處理事務教學目標能力目標◎能應用索引有效提高數(shù)據(jù)檢索性能；◎能使用事務機制實現(xiàn)數(shù)據(jù)的一致性。知識目標◎了解索引作用，掌握建立和查看管理索引的語法應用；◎了解事務特性，掌握事務處理操作語句應用。學習重點◎索引的建立與查看；◎事務的提交與回滾，保證數(shù)據(jù)的一致性。學習難點◎并發(fā)控制的鎖使用。任務一創(chuàng)建和查看索引創(chuàng)建和查看索引

主要內(nèi)容:索引概念和類型；查看索引創(chuàng)建索引刪除索引重點難點:索引的創(chuàng)建索引概念和類型索引概念索引是為了加速檢索而創(chuàng)建的一種存儲結(jié)構(gòu)；索引是針對一個表而建立的，它是由除存放表的數(shù)據(jù)頁面以外的索引頁面組成，每個索引頁面中的行都包含邏輯指針，以指針指向存儲在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針，通過該指針可以直接檢索到數(shù)據(jù)，從而加速物理數(shù)據(jù)的檢索。索引優(yōu)缺點優(yōu)點：當經(jīng)常查詢索引列中的數(shù)據(jù)時，需要在表上創(chuàng)建索引，可以提高檢索效率；缺點：索引將占用磁盤空間，并且降低添加、刪除和更新行的速度，所以索引也不是建立越多越好。索引概念和類型（1）普通索引這是MySQL中最基本的索引類型，允許索引列由重復值，可以用在查詢條件字段或排序字段上建立索引。（2）唯一性索引唯一索引要求組成該索引的字段或字段組合不能存在重復值，要求具有唯一性，允許空值。通常用在不允許重復的字段上建立唯一索引，以避免重復值的錄入。（3）主鍵索引主鍵索引是在創(chuàng)建主鍵時自動建立的，是唯一索引的特殊類型。主鍵索引字段不允許空值。索引概念和類型（4）復合索引使用一個以上的字段組合建立索引，這種索引稱為復合索引。（5）全文索引全文索引的索引類型為FULLTEXT,可以用在CHAR、VARCHAR或TEXT類型的列上創(chuàng)建。主要用于文章等大量文本文字中檢索字符串信息。（6）空間索引空間索引時對空間數(shù)據(jù)類型的字段上建立索引，如POINT等，索引字段不允許空?？臻g索引只能在MyISAM存儲引擎的表中創(chuàng)建。查看索引語法：SHOWINDEXFROMtbl_name說明：查看表全部記錄信息例9-1：查看db_shop數(shù)據(jù)庫中g(shù)oods表中的索引文件mysql>USEdb_shop;mysql>SHOWINDEXFROMgoods\G創(chuàng)建索引語法：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtbl_name({col_name[(length)]|(expr)}[ASC|DESC])說明：·UNIQUE|FULLTEXT|SPATIAL：分別表示唯一索引、全文索引和空間索引，省略默認是普通索引；·index_name是定義的索引名；·tbl_name是建立索引的表；·col_name是要建立索引的字段。創(chuàng)建普通索引語法：CREATEINDEX索引名ONtbl_name(索引字段列表)例9-2在商品表的數(shù)量字段建立普通索引。mysql>USEdb_shop;mysql>

CREATEINDEXix_amountONgoods(amount);mysql>SHOWINDEXFROMgoods\G創(chuàng)建唯一索引語法：CREATEUNIQUEINDEX索引名ONtbl_name(索引字段列表)說明：創(chuàng)建唯一索引可以在建立表時通過添加UNIQUE約束時建立，也使用CRAETEUNIQUEINDEX語句建立。第一種方法前面創(chuàng)建表時已經(jīng)做過。例9-3給staffer_rec_bak表的用戶名建立一個唯一索引。mysql>USEdb_shop;mysql>CREATEUNIQUEINDEXstaffer_rec_ixONstaffer_rec_bak(username);創(chuàng)建復合索引復合索引：就是指定多個字段作為索引列的索引。例9-4建立員工表建立性別和出生日期（降序）的索引mysql>USEdb_shop;mysql>CREATEINDEXix_stafferONstaffer(sex,birthdayDESC);考考你按下列操作實施完成db_shopping數(shù)據(jù)庫索引的建立和查看。1.復制商品表和記錄，復制的表名為goods_rec_bak;執(zhí)行語句如下：2.為goods_rec_bak表建立商品類型的普通索引ix_goods_type；執(zhí)行語句如下：3.為goods_rec_baka表建立商品名稱的唯一索引iq_goods_name；4.為goods_rec_baka表建立供應商編號（升序）和商品數(shù)量（降序）的索引ix_sup_id_amount；5.查看goods_rec_baka表建立的索引文件信息，觀察iq_goods_name索引參數(shù)；任務二處理事務處理事務

主要內(nèi)容:基本概念；事務特性和類型事務處理操作 重點難點:事務處理操作基本概念事務（transaction）是一個數(shù)據(jù)處理操作序列作為一個整體來執(zhí)行的一種機制。這些操作是一個不可分割的邏輯工作單元，事務更新操作要么都執(zhí)行，要么都不執(zhí)行。通過事務的整體性可以保證數(shù)據(jù)的一致性。事務特性事務是作為并發(fā)控制的最小控制單元，具備以下ACID四個特性：原子性（Atomicity）：事務是一個完整的操作，具有原子特性，不可分割。事務的所有操作作為一個整體提交或回滾，要么全部完成，要么全部不完成。不會結(jié)束在中間某個環(huán)節(jié)。事務在執(zhí)行過程中發(fā)生錯誤，會被回滾（Rollback）到事務開始前的狀態(tài)，就像這個事務從來沒有執(zhí)行過一樣。一致性（Consistency）：事務完成后，數(shù)據(jù)庫數(shù)據(jù)總是從一個一致的狀態(tài)到另一個一致的狀態(tài)，不允許中間狀態(tài)的存在。事務特性隔離性（Isolation）：數(shù)據(jù)庫允許多個并發(fā)事務同時對其數(shù)據(jù)進行讀寫和修改，但所有并發(fā)事務是彼此隔離的。一個事務看到的數(shù)據(jù)要么是其他事務修改前的狀態(tài)，要么是其他事務修改完成的狀態(tài)，這個事務不能看到其他事務正在修改的數(shù)據(jù)。持久性（Durability）：事務處理結(jié)束后，對數(shù)據(jù)的修改就是永久的，一旦事務被提交，事務就永久地保持在數(shù)據(jù)庫中，不能被回滾。即便系統(tǒng)發(fā)生故障也不會丟失。事務類型MySQL事務類型：一種是系統(tǒng)提供的自動提交事務；另一種是用戶定義的顯式事務。自動提交事務在MySQL命令行的默認設置下，事務都是自動提交的，一條語句就構(gòu)成了一個事務，即執(zhí)行SQL語句完成后要么被提交，要么回滾。例如CREATETABLE、ALTERTABLE、SELECT、INSERT、UPDATE、DELETE、DROP、TRUNCATETABLE、GRANT、REVOKE等語句的執(zhí)行都是自動提交事務。事務類型顯式事務顯式事務是用戶顯式定義事務啟動和結(jié)束。MySQL默認是自動提交事務，每句SQL語句就是一個事務。顯示事務可以使用2種方法開啟：方法一：使用BEGIN或STARTTRANSACTION開啟一個事務，同時用事務提交或事務回滾來結(jié)束事務。方法二：執(zhí)行命令：SETAUTOCOMMIT=0，來禁止當前會話的自動提交，用：SETAUTOCOMMIT=1，來恢復當前會話自動提交；事務類型查看事務提交狀態(tài)值。SHOWVARIABLESLIKE'autocommit';例如：查看事務提交模式mysql>SHOWVARIABLESLIKE'autocommit';#開啟事務顯式模式mysql>SETAUTOCOMMIT=0;事務處理操作在MySQL中定義事務處理的語句主要有下面3個處理語句。1）語法：BEGIN;或STARTTRANSACTION;說明：開啟事務。需要注意的是MySQL中是不允許事務嵌套的，開啟一個新的事務后，前面的事務會自動提交。2）語法：COMMIT說明：提交事務，使得事務中對數(shù)據(jù)庫進行的所有修改成為永久性的，同時結(jié)束當前會話事務，釋放連接時占用的資源。事務處理操作3）語法：ROLLBACK說明：回滾事務。撤銷正在進行的所有未提交的操作，數(shù)據(jù)狀態(tài)回滾到事務開始前，同時結(jié)束當前會話事務，并釋放事務占用的資源。事務處理操作例9-6向db_shop數(shù)據(jù)庫的部門信息表Department中插入3行記錄，一條用STARTTRANSACTION;開啟事務，添加后用COMMIT提交結(jié)束事務；第二條為自動提交事務；第三條用SETAUTOCOMMIT=0;關閉自動提交事務，啟動顯式事務提交模式，添加后使用ROLLBACK回滾結(jié)束事務。執(zhí)行語句如下,觀察數(shù)據(jù)變化：事務處理操作mysql>USEdb_shop;mysql>STARTTRANSACTION;#開啟事務mysql>INSERTINTOdb_shop.departmentvalues(100,'技術(shù)部2','38490123','技術(shù)業(yè)務');mysql>COMMIT;#結(jié)束事務mysql>INSERTINTOdb_shop.departmentvalues(101,'技術(shù)部3','38490123','技術(shù)業(yè)務');#在默認的自動提交事務模式下mysql>SETAUTOCOMMIT=0;#啟動顯式事務，之后為事務的開始mysql>INSERTINTOdb_shop.departmentvalues(101,'促銷部','38490125','促銷業(yè)務');mysql>ROLLBACK;#結(jié)束事務mysql>SELECT*FROMdb_shop.department;mysql>SETAUTOCOMMIT=1;#恢復事務自動提交模式說明：可以看到COMMIT的記錄永久保存，不在顯式事務模式的記錄會自動提交，不能回滾；在顯示提交模式的事務可以回滾，回滾后的記錄沒有添加入數(shù)據(jù)表。事務處理操作保存點建立事務保存點（SAVEPOINT），事務可以回滾到事務保存點而不影響事務保存點創(chuàng)建前的操作,不需要放棄整個事務。保存點有創(chuàng)建、回滾、刪除三個相關操作。事務中創(chuàng)建一個保存點。語法格式：SAVEPOINTsavepoint_name說明：savepoint_name為事務保存點名稱。一個事務中可以有多個事務保存點；事務處理操作事務回滾到某個事務保存點。語法格式：ROLLBACKTOsavepoint_name說明：回滾到保存點并不結(jié)束事務，仍然需要COMMIT;或者ROLLBACK;語句來結(jié)束事務。刪除一個事務的保存點語法格式：RELEASESAVEPOINTsavepoint_name事務處理操作例9-7創(chuàng)建一個事務，向do_shop數(shù)據(jù)庫的部門信息表Department中插入一行記錄，設置保存點，然后在事務中修改該記錄，回滾事務到保存點（記錄回到修改前的狀態(tài)），然后COMMIT。執(zhí)行以下語句，觀察數(shù)據(jù)變化：事務處理操作mysql>USEdb_shop;mysql>STARTTRANSACTION;mysql>INSERTINTOdepartmentvalues(101,'技術(shù)部3','S0000002','技術(shù)總監(jiān)');mysql>SAVEPOINTpoint1;mysql>UPDATEdepartmentSETdept_phone='S0000001'WHEREid=101;mysql>SELECT*FROMdb_shop.department;mysql>ROLLBACKTOSAVEPOINTpoint1;mysql>SELECT*FROMdb_shop.department;mysql>COMMIT;mysql>SELECT*FROMdb_shop.department;考考你1.為避免前面創(chuàng)建的觸發(fā)器影響，復制一份商品表名為goods_rec_bak。2.為避免前面創(chuàng)建的觸發(fā)器影響，復制一份詳細訂單表名為item_rec_bak。3.開啟事務，在item_rec_bak表添加一條記錄，并在goods_rec_bak中修改相應商品數(shù)量。4.提交事務，并查看數(shù)據(jù)是否變化。5.開啟事務，在item_rec_bak中插入2條記錄，并回滾，查看數(shù)據(jù)是否變化。任務實施按下列操作實施完成db_shopping數(shù)據(jù)庫事務應用。1.為避免前面創(chuàng)建的觸發(fā)器影響，復制一份商品表名為goods_rec_bak，執(zhí)行語句如下：mysql>CREATETABLEIFNOTEXISTSgoods_rec_bakASSELECT*FROMgoods;2.為避免前面創(chuàng)建的觸發(fā)器影響，復制一份詳細訂單表名為item_rec_bak，執(zhí)行語句如下：mysql>CREATETABLEIFNOTEXISTSitem_rec_bakASSELECT*FROMitem;3.開啟事務，在item_rec_bak表添加一條記錄，并在goods_rec_bak中修改相應商品數(shù)量，執(zhí)行語句