一.MySQL索引介紹

????????索引是一個(gè)排序的列表，在這個(gè)列表中存儲(chǔ)著索引的值和包含這個(gè)值的數(shù)據(jù)所在行的物理地址。在數(shù)據(jù)十分大的時(shí)候，索引可以大大加快查詢的速度。這是因?yàn)槭褂盟饕罂梢圆挥脪呙枞韥?lái)定位某行的數(shù)據(jù)，而是先通過(guò)索引表找到該行數(shù)據(jù)對(duì)應(yīng)的物理地址然后訪問(wèn)相應(yīng)的數(shù)據(jù)。索引的作用類似于圖書的目錄，可以根據(jù)目錄中的頁(yè)碼快速找到所需的內(nèi)容。

1.概述

???????當(dāng)數(shù)據(jù)保存在磁盤類存儲(chǔ)介質(zhì)上時(shí)，它是作為數(shù)據(jù)塊存放。這些數(shù)據(jù)塊是被當(dāng)作一個(gè)整體來(lái)訪問(wèn)的，這樣可以保證操作的原子性。硬盤數(shù)據(jù)塊存儲(chǔ)結(jié)構(gòu)類似于鏈表，都包含數(shù)據(jù)部分，以及一個(gè)指向下一個(gè)節(jié)點(diǎn)（或數(shù)據(jù)塊）的指針，不需要連續(xù)存儲(chǔ)。
? ? ? 記錄集只能在某個(gè)關(guān)鍵字段上進(jìn)行排序，所以如果需要在一個(gè)無(wú)序字段上進(jìn)行搜索，就要執(zhí)行一個(gè)線性搜索（LinearSearch）的過(guò)程，平均需要訪問(wèn)N/2的數(shù)據(jù)塊，N是表示所占據(jù)的數(shù)據(jù)塊數(shù)日。如果這個(gè)字段是一個(gè)非主鍵字段（也就是說(shuō)，不包含唯一的訪問(wèn)入口）那么需要在N個(gè)數(shù)據(jù)塊上搜索整個(gè)表格空間
? ? ? 但是對(duì)于一個(gè)有序字段，可以運(yùn)用二分查找（BinarySearch），這樣只需要訪間log2（N）的數(shù)據(jù)塊。這就是為什么數(shù)據(jù)表使用索引后性能可以得到本質(zhì)上提高的原因。
? ? ? 素引是對(duì)記錄集的多個(gè)字段進(jìn)行排序的方法。在一張表中為一個(gè)字段創(chuàng)建一個(gè)索引，將創(chuàng)建另外一個(gè)數(shù)據(jù)結(jié)構(gòu)，包含字段數(shù)值以及指向相關(guān)記錄的指針，然后對(duì)這個(gè)索引結(jié)構(gòu)進(jìn)行排序，允許在該數(shù)據(jù)上進(jìn)行二分法排序。
? ? ? 使用索引的副作用是需要額外的磁盤空間。對(duì)于MyISAM引而言，這些索引是被統(tǒng)一保存在一張表中的。如果很多字段都建立了索引，那么會(huì)占用大量的磁盤空間，這個(gè)文件將很快到達(dá)底層文件系統(tǒng)所能夠支持的大小限制

2.索引作用

• 設(shè)置了合適的索引之后，數(shù)據(jù)庫(kù)利用各種快速定位技術(shù)，能夠大大加快查詢速度，這是創(chuàng)建索引的最主要的原因。
• 當(dāng)表很大或查詢涉及到多個(gè)表時(shí)，使用索引可以成千上萬(wàn)倍地提高查詢速度。
• 可以降低數(shù)據(jù)庫(kù)的IO成本，并且索引還可以降低數(shù)據(jù)庫(kù)的排序成本。
• 通過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)表中每一行數(shù)據(jù)的唯一性。
• 可以加快表與表之間的連接。
• 在使用分組和排序時(shí)，可大大減少分組和排序的時(shí)間。???????

3.索引分類

????????從物理存儲(chǔ)的角度來(lái)劃分，索引分為聚族索引和非聚族索引兩種，聚族索引是按照數(shù)據(jù)存放的物理位置為順序的，而非聚族索引就不一樣了；聚索引能提高多行檢索的速度，而非聚族索引對(duì)于單行的檢索更快
? ? 從邏輯的角度來(lái)劃分，索引分為普通索引、唯一索引、主鍵索引、組合索引和全文索引。

（1）普通索引

普通索引是最基本的索引，它沒(méi)有任何限制，也是大多數(shù)情況下用到的索引。

創(chuàng)建普通索引

mysql> create index index_name on users(user_name(20));

備注：index索引，on后面跟要?jiǎng)?chuàng)建索引的表名，表名括號(hào)內(nèi)跟該表內(nèi)標(biāo)準(zhǔn)型字段名以及它的字符長(zhǎng)度。?

修改表格式添加索引

alter table 表名 add index 索引名（用于索引的字段）

創(chuàng)建表結(jié)構(gòu)時(shí)，同時(shí)創(chuàng)建索引

CREATE TABLE table01 ( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , 
time int(10) NULL DEFAULT NULL , 
PRIMARY KEY (id), 
INDEX index_table01_title (title(11)) 
);

（2）唯一索引

? ? ? ? 唯一索引與普通索引類似，不同的就是：唯一索引的索引列的值必須唯一，但允許有空值（注意和主鍵不同）。如果是組合索引，則列值的組合必須唯一。唯一索引創(chuàng)建方法和普通索引類似。

創(chuàng)建唯一索引

create unique index 索引名 on 表名（索引字段（長(zhǎng)度））；

修改表結(jié)構(gòu)的時(shí)候添加唯一索引

alter table 表名 add unique 索引名（索引字段（長(zhǎng)度））

創(chuàng)建表的時(shí)候同時(shí)創(chuàng)建唯一索引?

create table 表名 （
id int（10），
name char（20），
pwd char（50），
unique index 索引名（ip）
）；

（3）主鍵索引

? ? ? ? 主鍵索引是一種特殊的唯一索引，一個(gè)表只能有一個(gè)主鍵，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引。

create table t2(
id int(10),
name char(20),
age char(2),
pwd char(50),
primary key (id)
);

（4）組合索引（最左前綴）

????????平時(shí)用的SQL查詢語(yǔ)句一般都有比較多的限制條件，所以為了進(jìn)一步榨取MySQL的效率，就要考慮建立組合索引。在組合索引的創(chuàng)建中，有兩種場(chǎng)景，即為單列索引和多列索引。下面通過(guò)一個(gè)場(chǎng)景來(lái)具體說(shuō)明單列索引和多列索引
????????在一個(gè)user用戶表中，有name，age，sex三個(gè)字段，分別分三次建立了INDEX普通索引。那么在select * from user where name="" AND age="" AND sex="";數(shù)據(jù)查詢語(yǔ)句中就會(huì)分別檢索三條索引，雖然掃描效率有所提升，但卻還未達(dá)到最優(yōu)。這個(gè)時(shí)候就需要使用到組合索引（即多列索引）

create table user(
name char(20),
age int(3),
sex tinyint(1),
index user(name,age,sex)
);

? ? ? ? 在MySQL中，有一個(gè)知識(shí)點(diǎn)叫最左原則。下面的select語(yǔ)句的where條件是依次從左往右執(zhí)行的。

select * from user where name="" and age="" and sex="";

? ? ? ? 若使用的是組合索引index user（name，age，sex）。在查詢中，name，age，sex的順序必須如組合索引中一致排序，否則索引將不會(huì)生效，所以一般在建立索引時(shí)，要先想好響應(yīng)的查詢業(yè)務(wù)，盡量避免雖然有索引，但是使用不上的問(wèn)題。

（5）全文索引

? ? ? ? 對(duì)于較大的數(shù)據(jù)集，將資料輸入一個(gè)沒(méi)有FULLTEXT索引的表中，然后創(chuàng)建索引，其速度比把資料輸入現(xiàn)在FULLTEXT索引的速度更快。不過(guò)切記對(duì)于大容量的數(shù)據(jù)表，生成全文索引是一個(gè)非常消耗時(shí)間，非常消耗硬盤空間的做法。

創(chuàng)建表的全文索引語(yǔ)法

CREATE TABLE table ( 
id int(11) NOT NULL AUTO_INCREMENT , 
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , 
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , 
time int(10) NULL DEFAULT NULL , 
PRIMARY KEY (id), 
FULLTEXT (content) 
);

修改表結(jié)構(gòu)添加全文索引：

ALTER TABLE article ADD FULLTEXT index_content(content);

直接創(chuàng)建索引語(yǔ)法：

mysql>CREATE FULLTEXT INDEX index_content ON article(content);

4.創(chuàng)建索引的原則依據(jù)

數(shù)據(jù)庫(kù)建立索引的原則：

• 確定針對(duì)該表的操作是大量的查詢操作還是大量的增刪改操作；
• 嘗試建立索引來(lái)幫助特定的查詢。檢查自己的 sql 語(yǔ)句，為那些頻繁在 where 子句中出現(xiàn)的字段建立索引；
• 嘗試建立復(fù)合索引來(lái)進(jìn)一步提高系統(tǒng)性能。修改復(fù)合索引將消耗更長(zhǎng)時(shí)間，同時(shí)復(fù)合索引也占磁盤空間；
• 對(duì)于小型的表，建立索引可能會(huì)影響性能；
• 應(yīng)該避免對(duì)具有較少值的字段進(jìn)行索引；
• 避免選擇大型數(shù)據(jù)類型的列作為索引。 ???????

索引建立的原則：

索引查詢是數(shù)據(jù)庫(kù)中重要的記錄查詢方法，要不要建立索引以及在那些字段上建立索引都要和實(shí)際數(shù)據(jù)庫(kù)系統(tǒng)的查詢要求結(jié)合來(lái)考慮，下面給出實(shí)際生產(chǎn)環(huán)境中的一些通用的原則：

• 表的主鍵、外鍵必須有索引。因?yàn)橹麈I具有唯一性，外鍵關(guān)聯(lián)的是子表的主鍵，查詢時(shí)可以快速定位。
• 記錄數(shù)超過(guò)300行的表應(yīng)該有索引。如果沒(méi)有索引，需要把表遍歷一遍，會(huì)嚴(yán)重影響數(shù)據(jù)庫(kù)的性能。
• 經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引。
• 唯一性太差的字段不適合建立索引。
• 更新太頻繁地字段不適合創(chuàng)建索引。
• 經(jīng)常出現(xiàn)在 where 子句中的字段，特別是大表的字段，應(yīng)該建立索引。索引應(yīng)該建在選擇性高的字段上。
• 索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長(zhǎng)字段，不要建索引。

5.查看索引

查看索引的方法有三個(gè)：

1. show create table 表名；
2. show index from 表名；
3. show keys tables 表名；

?6.刪除索引

索引的刪除方法有兩種：

1. drop index 索引名 on 表名；
2. alter table 表名 drop index 索引名；

???????二.MySQL事務(wù)

????????MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說(shuō)，在人員管理系統(tǒng)中，要?jiǎng)h除一個(gè)人員，即需要?jiǎng)h除人員的基本資料，又需要?jiǎng)h除和該人員相關(guān)的信息，如信箱，文章等等。這樣，這些數(shù)據(jù)庫(kù)操作語(yǔ)句就構(gòu)成一個(gè)事務(wù)。

? ? ? ? 在MySQL中只有使用了Innodb數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)。

? ? ? ? 事務(wù)處理可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的SQL語(yǔ)句要么全部執(zhí)行，要么全部不執(zhí)行。

????????是一個(gè)不可分割的工作邏輯單元，在數(shù)據(jù)庫(kù)系統(tǒng)上執(zhí)行并發(fā)操作時(shí)，事務(wù)是最小的控制單元

????????適用于多用戶同時(shí)操作的數(shù)據(jù)庫(kù)系統(tǒng)的場(chǎng)景，如銀行、保險(xiǎn)公司及證券交易系統(tǒng)等???????

一般來(lái)說(shuō)事務(wù)是必須滿足4個(gè)條件：原子性（不可分割性），一致性，隔離性，持久性。

1. 原子性（Atomicity）

   • 一個(gè)事務(wù)中的所有操作被視為一個(gè)單獨(dú)的工作單元，即不可分割。這意味著事務(wù)中的所有操作要么全部成功完成，要么全部失敗回滾，不會(huì)出現(xiàn)只完成部分操作的情況。
   • 如果事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤或被中斷，系統(tǒng)將撤銷事務(wù)中已經(jīng)執(zhí)行的所有操作，將數(shù)據(jù)庫(kù)狀態(tài)回滾到事務(wù)開(kāi)始前的狀態(tài)，以確保數(shù)據(jù)的一致性和完整性。

2. 一致性（Consistency）

   • 在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞。
   • 這意味著事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)轉(zhuǎn)換到另一個(gè)一致性狀態(tài)。換句話說(shuō)，事務(wù)的執(zhí)行不會(huì)破壞數(shù)據(jù)的完整性和業(yè)務(wù)規(guī)則。
   • 例如，在一個(gè)轉(zhuǎn)賬操作中，從一個(gè)賬戶扣除的金額必須等于在另一個(gè)賬戶中添加的金額，以確保賬戶總金額的一致性。

3. 隔離性（Isolation）

   • 事務(wù)在并發(fā)執(zhí)行時(shí)，彼此之間是不可見(jiàn)的，即一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)所干擾。
   • 數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改，但隔離性確保了這些操作之間的獨(dú)立性。一個(gè)事務(wù)對(duì)數(shù)據(jù)的修改在最終提交之前對(duì)其他事務(wù)是不可見(jiàn)的。
   • 事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）、讀提交（Read committed）、可重復(fù)讀（Repeatable read）和串行化（Serializable）。這些級(jí)別提供了不同程度的并發(fā)控制和數(shù)據(jù)一致性保證。

4. 持久性（Durability）

   • 一旦事務(wù)被提交，其對(duì)數(shù)據(jù)的修改就是永久性的，即使系統(tǒng)發(fā)生故障也不會(huì)丟失。
   • 為了確保持久性，DBMS（數(shù)據(jù)庫(kù)管理系統(tǒng)）通常會(huì)將事務(wù)的修改寫入到磁盤上的穩(wěn)定存儲(chǔ)介質(zhì)中，并在系統(tǒng)恢復(fù)時(shí)重新應(yīng)用這些修改以恢復(fù)數(shù)據(jù)的一致性狀態(tài)。

事務(wù)的控制語(yǔ)句???????

1. begin transaction?或?start transaction：用于開(kāi)始一個(gè)新的事務(wù)。
2. commit：提交事務(wù)。將事務(wù)中的所有更改永久地保存到數(shù)據(jù)庫(kù)中。
3. rollback：回滾事務(wù)。撤銷事務(wù)中所做的所有更改，并將數(shù)據(jù)庫(kù)恢復(fù)到事務(wù)開(kāi)始之前的狀態(tài)。
4. savepoint：在事務(wù)中設(shè)置一個(gè)保存點(diǎn)。允許在后續(xù)的回滾操作中只撤銷到指定的保存點(diǎn)。
5. rollback to savepoint：將事務(wù)回滾到指定的保存點(diǎn)。撤銷從上一個(gè)保存點(diǎn)（或事務(wù)開(kāi)始）到當(dāng)前點(diǎn)之間的所有更改。
6. set transaction：設(shè)置事務(wù)的屬性，如隔離級(jí)別、訪問(wèn)模式等。
7. lock table?和?unlock table：鎖定和解鎖表。用于在事務(wù)中控制對(duì)表的并發(fā)訪問(wèn)。
8. set autocommit：設(shè)置自動(dòng)提交模式。當(dāng)自動(dòng)提交模式打開(kāi)時(shí)，每個(gè)單獨(dú)的SQL語(yǔ)句都被視為一個(gè)單獨(dú)的事務(wù)，并在執(zhí)行后自動(dòng)提交，值為1開(kāi)啟自動(dòng)提交，0關(guān)閉自動(dòng)提交。
9. set transaction isolation level：設(shè)置事務(wù)的隔離級(jí)別。不同的隔離級(jí)別提供了不同程度的并發(fā)控制和數(shù)據(jù)一致性保證

?MySql事務(wù)處理主要有兩種方法

（1）用BEGIN，ROLLBACKCOMMIT來(lái)實(shí)現(xiàn)

1. BEGIN開(kāi)始一個(gè)事務(wù)
2. ROLLBACK事務(wù)回滾
3. COMMIT事務(wù)確認(rèn)

（2）直接用SET來(lái)改變MySQL的自動(dòng)提交模式

1. SETAUTOCOMMIT=0禁止自動(dòng)提交
2. SETAUTOCOMMIT=1開(kāi)啟自動(dòng)提交

注意：

在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)。

mysql> SET AUTOCOMMIT=0;mysql>use auth; 
mysql>CREATE TABLE kgc_transaction_test( id int(5)) engine=innodb;
mysql>select * from kgc_transaction_test; mysql>begin;     //開(kāi)始事務(wù) 
mysql>insert into kgc_transaction_test value(1); 
mysql> insert into kgc_transaction_test value(2); 
mysql> commit;      //提交事務(wù) 
mysql>select * from kgc_transaction_test;mysql>begin;     //開(kāi)始事務(wù) 
mysql>insert into kgc_transaction_test values(3); 
mysql>rollback;     //回滾 
mysql> select * from kgc_transaction_test;     //因?yàn)榛貪L所以數(shù)據(jù)沒(méi)有插入