一.MySQL索引介紹

????????索引是一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數(shù)據(jù)所在行的物理地址。在數(shù)據(jù)十分大的時候，索引可以大大加快查詢的速度。這是因為使用索引后可以不用掃描全表來定位某行的數(shù)據(jù)，而是先通過索引表找到該行數(shù)據(jù)對應(yīng)的物理地址然后訪問相應(yīng)的數(shù)據(jù)。索引的作用類似于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。

1.概述

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

2.索引作用

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

3.索引分類

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

（1）普通索引

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

創(chuàng)建普通索引

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

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

修改表格式添加索引

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

創(chuàng)建表結(jié)構(gòu)時，同時創(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 表名（索引字段（長度））；

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

alter table 表名 add unique 索引名（索引字段（長度））

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

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

（3）主鍵索引

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

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

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

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

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

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

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

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

（5）全文索引

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

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

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)建索引語法：

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

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

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

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

索引建立的原則：

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

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

5.查看索引

查看索引的方法有三個：

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ù)。比如說，在人員管理系統(tǒng)中，要刪除一個人員，即需要刪除人員的基本資料，又需要刪除和該人員相關(guān)的信息，如信箱，文章等等。這樣，這些數(shù)據(jù)庫操作語句就構(gòu)成一個事務(wù)。

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

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

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

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

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

1. 原子性（Atomicity）

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

2. 一致性（Consistency）

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

3. 隔離性（Isolation）

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

4. 持久性（Durability）

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

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

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

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

（1）用BEGIN，ROLLBACKCOMMIT來實現(xiàn)

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

（2）直接用SET來改變MySQL的自動提交模式

1. SETAUTOCOMMIT=0禁止自動提交
2. SETAUTOCOMMIT=1開啟自動提交

注意：

在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫引擎的數(shù)據(jù)庫或表才支持事務(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;     //開始事務(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;     //開始事務(wù) 
mysql>insert into kgc_transaction_test values(3); 
mysql>rollback;     //回滾 
mysql> select * from kgc_transaction_test;     //因為回滾所以數(shù)據(jù)沒有插入