Mysql事務及索引講解

一、數(shù)據(jù)庫索引概述；二、數(shù)據(jù)庫索引分類；三、Mysql數(shù)據(jù)庫管理索引；四、數(shù)據(jù)庫事務概述；五、數(shù)據(jù)庫事務特性；六、Mysqk數(shù)據(jù)庫管理事務；一、數(shù)據(jù)庫索引概述；概述：索引是一種特殊的文件，包含著對數(shù)據(jù)表中所有記錄的引用指針，如書本的目錄類似，能夠加快數(shù)據(jù)庫的查詢速度，更高效率的管理數(shù)據(jù)庫；作用：設置合適的索引后，數(shù)據(jù)庫利用各種定位設置，可以大大的增加數(shù)據(jù)的查詢速度；

當表很大時，或者查詢涉及到多個表時，使用索引可以加快查詢速度甚至上千倍；

可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本；

通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性；缺點：增加了數(shù)據(jù)庫的存儲空間；

在插入和修改數(shù)據(jù)的同時會增加更多的時間，因為索引也要一同變化；依據(jù)：表的主鍵必須有索引，主鍵索引中的值時唯一的；

數(shù)據(jù)量超過300行的表應該有索引，不然會眼中影響數(shù)據(jù)庫的查詢性能；

唯一性太差的字段不適合建立索引，比如一列中的1000行都是同一個字段；

經(jīng)常與其他表進行交互的表，其連接列中必須設置索引；

更新太過于頻發(fā)的字段不建議設置為索引列；

經(jīng)常出現(xiàn)在sql語句中的where中，建議設置為索引列；

索引建議設置在小字段上，較大的字段上不建議設置為索引；二、數(shù)據(jù)庫索引分類；普通索引：最基本的索引，無唯一性之類的限制；唯一性索引：索引列中的所有值只能出現(xiàn)一次，如身份證號碼列；主鍵索引：是一種唯一性奪銀，在一個表中創(chuàng)建主鍵后，則自動創(chuàng)建主鍵索引；單列索引和多列索引：可以為單列或者多列，列中可能有重復的值，例如表中的性別列；三、Mysql數(shù)據(jù)庫管理索引；準備數(shù)據(jù)庫和表：mysql>createdatabaselinuxfan;QueryOK,1rowaffected(0.01sec)mysql>showdatabases;+--------------------+|Database

|+--------------------+|information_schema||

linuxfan

||mysql

||performance_schema||sys

|+--------------------+mysql>uselinuxfan;Databasechangedmysql>createtableit(崗位char(16),姓名char(16),身份證號char(48),學歷char(16),工資int);QueryOK,0rowsaffected(0.00sec)mysql>insertintoitvalues('網(wǎng)絡工程師','張三',,'高中','5500');QueryOK,1rowaffected(0.00sec)mysql>insertintoitvalues('網(wǎng)絡工程師','李四',,'?？?,'12000');QueryOK,1rowaffected(0.00sec)mysql>insertintoitvalues('java工程師','王五',,'初中','10000');QueryOK,1rowaffected(0.00sec)mysql>insertintoitvalues('網(wǎng)絡工程師','劉備',,'高中','15000');QueryOK,1rowaffected(0.00sec)mysql>insertintoitvalues('ui工程師','關羽',,'本科','7000');QueryOK,1rowaffected(0.00sec)mysql>select*fromit;+-----------------+--------+--------------------+--------+--------+|崗位

|姓名

|身份證號

|學歷

|工資

|+-----------------+--------+--------------------+--------+--------+|網(wǎng)絡工程師

|張三

高中

|

5500||網(wǎng)絡工程師

|李四

?？?/p>

|

12000||java工程師

|王五

初中

|

10000||網(wǎng)絡工程師

|劉備

高中

|

15000||ui工程師

|關羽

本科

|

7000|+-----------------+--------+--------------------+--------+--------+普通索引語法：createindex索引名稱

on表名(列名);mysql>createindexputongonit(工資);QueryOK,5rowsaffected(0.00sec)mysql>showindexfromit;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|it

|

1|putong

|

1|工資

|A

|

NULL|

NULL|NULL

|YES

|BTREE

|

|

|唯一性索引語法：createuniqueindex

索引名稱

on表名(列名);mysql>createuniqueindexweiyionit(身份證號);QueryOK,5rowsaffected(0.00sec)mysql>showindexfromit;+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|Table|Non_unique|Key_name|Seq_in_index|Column_name

|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|it

|

0

|weiyi

|

1|身份證號

|A

|

NULL|

NULL|NULL

|YES

|BTREE

|

|

||it

|

1

|putong

|

1|工資

|A

|

NULL|

NULL|NULL

|YES

|BTREE

|

|

|+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+mysql>insertintoitvalues('網(wǎng)絡工程師','張飛',,'小學','7800');ERROR1062(23000):Duplicateentryforkey'weiyi'mysql>select*fromit;+-----------------+--------+--------------------+--------+--------+|崗位

|姓名

|身份證號

|學歷

|工資

|+-----------------+--------+--------------------+--------+--------+|網(wǎng)絡工程師

|張三

高中

|

5500||網(wǎng)絡工程師

|李四

專科

|

12000||java工程師

|王五

初中

|

10000||網(wǎng)絡工程師

|劉備

高中

|

15000||ui工程師

|關羽

本科

|

7000|+-----------------+--------+--------------------+--------+--------+創(chuàng)建主鍵：createtable表名

(字段一,字段二,primarykey(列名));mysql>createtableit2(namechar(16),idint,PRIMARYKEY(name));QueryOK,0rowsaffected(0.01sec)多列索引：createindex索引名稱

on表名(列名一,列名二);mysql>createindexduolieonit(工資,學歷);QueryOK,5rowsaffected(0.01sec)mysql>showindexfromit;+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|Table|Non_unique|Key_name|Seq_in_index|Column_name

|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|it

|

0|weiyi

|

1|身份證號

|A

|

NULL|

NULL|NULL

|YES

|BTREE

|

|

||it

|

1|putong

|

1|工資

|A

|

NULL|

NULL|NULL

|YES

|BTREE

|

|

||it

|

1|duolie

|

1|工資

|A

|

NULL|

NULL|NULL

|YES

|BTREE

|

|

||it

|

1|duolie

|

2|學歷

|A

|

NULL|

NULL|NULL

|YES

|BTREE

|

|

|+-------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+查看索引：showindexfrom表名;四、數(shù)據(jù)庫事務概述；概述：事務是一種機制，一個操作序列，包含了一組數(shù)據(jù)庫操作指令，并且將多個操作指令當作一個整體，一同向系統(tǒng)提交或者回滾的操作，事務保證了數(shù)據(jù)的一致性，如支付寶中轉(zhuǎn)賬的操作，轉(zhuǎn)賬的賬戶和收錢的賬戶，兩個操作組成一個完整的事務；五、數(shù)據(jù)庫事務特性；原子性：事務是一個完整的操作，數(shù)據(jù)必須處于一致的狀態(tài)，事務中的操作，要么全部成功，要么全部失敗，此特性就是原子性；一致性：當事務執(zhí)行完畢后，數(shù)據(jù)必須處于一致狀態(tài)，在開始事務時數(shù)據(jù)保持一致狀態(tài)，執(zhí)行事務的過程中事務可以出現(xiàn)不一致的情況，事務完畢后，數(shù)據(jù)必須保證一致狀態(tài)；隔離性：多個事務之間互不依靠，互補影響，相對獨立；持久性：事務處理完畢后，在系統(tǒng)中發(fā)生的變化是永久性的，會永久的保留在數(shù)據(jù)庫中；六、Mysqk數(shù)據(jù)庫管理事務；管理事務的三個命令：begin：開始事務，后邊有多條數(shù)據(jù)庫操作語句開始執(zhí)行；commit：開始提交一個事務，對應前邊的begin操作，將事務處理的結(jié)果保存到數(shù)據(jù)文件中；rollback：開始回滾一個事務，在begin和commit之間，將事務中的全部語句撤回，恢復到執(zhí)行begin之前的數(shù)據(jù)狀態(tài)；管理事務：mysql>uselinuxfan;Databasechangedmysql>showtablestatuswherename="it";

##確保it表存儲引擎為InnoDB，如若不是按以下命令修改；mysql>altertableitengine=InnoDB;mysql>select*fromit;+-----------------+--------+--------------------+--------+--------+|崗位

|姓名

|身份證號

|學歷

|工資

|+-----------------+--------+--------------------+--------+--------+|網(wǎng)絡工程師

|張三

高中

|

5500||網(wǎng)絡工程師

|李四

?？?/p>

|

12000||java工程師

|王五

初中

|

10000||網(wǎng)絡工程師

|劉備

高中

|

15000||ui工程師

|關羽

本科

|

7000|+-----------------+--------+--------------------+--------+--------+mysql>

begin;

##開始事務QueryOK,0rowsaffected(0.00sec)mysql>insertintoitvalues('php工程師','曹操',,'本科','9000');QueryOK,1rowaffected(0.00sec)mysql>

commit;

##提交事務QueryOK,0rowsaffected(0.00sec)mysql>select*fromit;+-----------------+--------+--------------------+--------+--------+|崗位

|姓名

|身份證號

|學歷

|工資

|+-----------------+--------+--------------------+--------+--------+|網(wǎng)絡工程師

|張三

高中

|

5500||網(wǎng)絡工程師

|李四

專科

|

12000||java工程師

|王五

初中

|

10000||網(wǎng)絡工程師

|劉備

高中

|

15000||ui工程師

|關羽

本科

|

7000||php工程師

|曹操

本科

|

9000|+-----------------+--------+--------------------+--------+--------+6rowsinset(0.00sec)mysql>

begin;

##開始事務QueryOK,0rowsaffected(0.00sec)mysql>insertintoitvalues('php工程師','趙云',,'?？?,'9500');QueryOK,1rowaffected(0.00sec)mysql>select*fromit;

##未手動提交事務，但仍然能夠看到數(shù)據(jù)，因為在當前已經(jīng)開啟的事務中可以看見，退出連接后，則自動提交事務+-----------------+--------+--------------------+--------+--------+|崗位

|姓名

|身份證號

|學歷

|工資

|+-----------------+--------+--------------------+--------+--------+|網(wǎng)絡工程師

|張三

高中

|

5500||網(wǎng)絡工程師

|李四

?？?/p>

|

12000||java工程師

|王五

初中

|

10000||網(wǎng)絡工程師

|劉備

高中

|

15000||ui工程師

|關羽

本科

|

7000||php工程師

|曹操

本科

|

9000||php工程師

|趙云

?？?/p>

|

9500|+-----------------+--------+--------------------+--------+--------+mysql>exitBye[root@lwh~]#mysql-uroot-p123456mysql>uselinuxfan;Databasechangedmysql>select*fromit;+-----------------+--------+--------------------+--------+--------+|崗位

|姓名

|身份證號

|學歷

|工資

|+-----------------+--------+--------------------+--------+--------+|網(wǎng)絡工程師

|張三

高中

|

5500||網(wǎng)絡工程師

|李四

?？?/p>

|

12000||java工程師

|王五

初中

|

10000||網(wǎng)絡工程師

|劉備

高中

|

15000||ui工程師

|關羽

本科

|

7000||php工程師

|曹操

本科

|

9000||php工程師

|趙云

?？?/p>

|

9500|+-----------------+--------+--------------------+--------+-----