mysql索引介紹

索引概述

在 MySQL 中，索引是一種特殊的數(shù)據(jù)結(jié)構(gòu)，它能夠顯著提高數(shù)據(jù)庫的查詢效率。簡(jiǎn)單來說，索引就像是書籍的目錄，通過目錄可以快速找到所需內(nèi)容的位置，而不必逐頁翻閱整本書。在數(shù)據(jù)庫中，索引能夠幫助數(shù)據(jù)庫系統(tǒng)快速定位到表中符合條件的數(shù)據(jù)行，從而減少了數(shù)據(jù)的掃描量，提高了查詢速度。

索引本質(zhì)上是一種排序好的數(shù)據(jù)結(jié)構(gòu)，它存儲(chǔ)了表中某些列的值以及這些值對(duì)應(yīng)的物理地址。當(dāng)執(zhí)行查詢時(shí)，數(shù)據(jù)庫系統(tǒng)首先在索引中查找符合條件的值，然后根據(jù)索引中記錄的物理地址直接訪問對(duì)應(yīng)的數(shù)據(jù)行，這樣就避免了全表掃描，大大提高了查詢效率。

索引作用

索引在數(shù)據(jù)庫中起著至關(guān)重要的作用，主要體現(xiàn)在以下幾個(gè)方面：

• 提高查詢效率：這是索引最主要的作用。通過索引，數(shù)據(jù)庫系統(tǒng)可以快速定位到符合條件的數(shù)據(jù)行，減少了數(shù)據(jù)的掃描量，從而顯著提高查詢速度。特別是在處理大量數(shù)據(jù)時(shí)，索引的優(yōu)勢(shì)更加明顯。
• 加速排序和分組操作：在進(jìn)行排序（ORDER BY）和分組（GROUP BY）操作時(shí)，如果相關(guān)列上有索引，數(shù)據(jù)庫系統(tǒng)可以直接利用索引的有序性進(jìn)行排序和分組，而不必進(jìn)行額外的排序操作，從而提高了這些操作的效率。
• 保證數(shù)據(jù)的唯一性：唯一索引和主鍵索引可以保證表中數(shù)據(jù)的唯一性。當(dāng)向表中插入數(shù)據(jù)時(shí)，數(shù)據(jù)庫系統(tǒng)會(huì)自動(dòng)檢查索引列的值是否已經(jīng)存在，如果存在則會(huì)拒絕插入，從而保證了數(shù)據(jù)的唯一性。
• 減少 I/O 操作：由于索引通常比表數(shù)據(jù)小得多，因此在查詢時(shí)可以將索引加載到內(nèi)存中，減少了磁盤 I/O 操作，提高了查詢性能。

索引分類

MySQL 提供了多種類型的索引，每種索引都有其特定的應(yīng)用場(chǎng)景。

下面介紹幾種常見的索引類型：

普通索引

普通索引是最基本的索引類型，它沒有任何特殊的限制。普通索引可以創(chuàng)建在單個(gè)列上，也可以創(chuàng)建在多個(gè)列上（組合索引）。創(chuàng)建普通索引的目的主要是為了提高查詢效率。

創(chuàng)建普通索引的語法如下：

CREATE INDEX index_name ON table_name(column_name);
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... INDEX index_name (column_name) );

唯一索引

唯一索引與普通索引類似，但它的主要特點(diǎn)是索引列的值必須唯一，即不允許有重復(fù)值。唯一索引可以包含 NULL 值，但每個(gè) NULL 值被視為唯一的。唯一索引通常用于確保數(shù)據(jù)的唯一性，例如用戶表中的郵箱地址、手機(jī)號(hào)碼等字段。

創(chuàng)建唯一索引的語法如下：

CREATE UNIQUE INDEX index_name ON table_name(column_name);
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... UNIQUE INDEX index_name (column_name) );

主鍵索引

主鍵索引是一種特殊的唯一索引，它必須唯一標(biāo)識(shí)表中的每一行數(shù)據(jù)，且不能包含 NULL 值。每個(gè)表只能有一個(gè)主鍵索引。主鍵索引通常用于定義表的主鍵，例如用戶表中的用戶 ID 字段。

創(chuàng)建主鍵索引的語法如下：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);
CREATE TABLE table_name ( column1 datatype PRIMARY KEY, column2 datatype, ... );

組合索引（最左前綴）

組合索引是指在多個(gè)列上創(chuàng)建的索引。組合索引的查詢效率更高，但它的使用需要遵循 “最左前綴” 原則。最左前綴原則是指在使用組合索引進(jìn)行查詢時(shí)，MySQL 會(huì)從左到右依次使用索引中的列，直到遇到第一個(gè)不滿足條件的列為止。

例如，在列（col1, col2, col3）上創(chuàng)建組合索引，查詢條件為WHERE col1 = 'value1' AND col2 = 'value2'時(shí)，會(huì)使用組合索引；但查詢條件為WHERE col2 = 'value2' AND col3 = 'value3'時(shí)，不會(huì)使用組合索引，因?yàn)闆]有從最左邊的列開始。

創(chuàng)建組合索引的語法如下：

CREATE INDEX index_name ON table_name(column1, column2, column3);
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ... INDEX index_name (column1, column2, column3) );

全文索引

全文索引是一種特殊的索引類型，它主要用于在文本數(shù)據(jù)中進(jìn)行全文搜索。全文索引可以快速定位包含特定關(guān)鍵詞的文本數(shù)據(jù)，適用于大量文本數(shù)據(jù)的搜索場(chǎng)景，如文章內(nèi)容搜索、產(chǎn)品描述搜索等。

需要注意的是，全文索引只適用于 MyISAM 和 InnoDB 存儲(chǔ)引擎，并且只能用于 CHAR、VARCHAR 和 TEXT 類型的列。

創(chuàng)建全文索引的語法如下：

CREATE FULLTEXT INDEX index_name ON table_name(column_name);
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... FULLTEXT INDEX index_name (column_name) );

使用全文索引進(jìn)行搜索的語法如下：

SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_keyword');

創(chuàng)建索引的原則

在數(shù)據(jù)庫中創(chuàng)建索引時(shí)，需要遵循一定的原則，以確保索引的有效性和合理性。以下是一些創(chuàng)建索引的基本原則：

• 選擇合適的列創(chuàng)建索引：應(yīng)該在經(jīng)常用于查詢條件、排序和連接的列上創(chuàng)建索引。例如，在 WHERE 子句中經(jīng)常出現(xiàn)的列、ORDER BY 子句中使用的列以及 JOIN 操作中涉及的列。
• 避免在小表上創(chuàng)建過多索引：對(duì)于數(shù)據(jù)量較小的表，全表掃描的效率可能比使用索引更高，因此不需要?jiǎng)?chuàng)建過多的索引。過多的索引會(huì)增加數(shù)據(jù)插入、更新和刪除的開銷，同時(shí)也會(huì)占用更多的存儲(chǔ)空間。
• 控制索引的數(shù)量：每個(gè)表的索引數(shù)量不宜過多，一般建議不超過 5-6 個(gè)。過多的索引會(huì)影響數(shù)據(jù)庫的性能，特別是在數(shù)據(jù)插入、更新和刪除操作頻繁的情況下。
• 注意組合索引的順序：在創(chuàng)建組合索引時(shí)，要根據(jù)最左前綴原則合理安排列的順序。通常將最常作為查詢條件的列放在最左邊，依次類推。
• 避免在頻繁更新的列上創(chuàng)建索引：頻繁更新的列會(huì)導(dǎo)致索引頻繁重建，從而影響數(shù)據(jù)庫的性能。因此，應(yīng)該避免在頻繁更新的列上創(chuàng)建索引。
• 對(duì)較長(zhǎng)的字符串列使用前綴索引：對(duì)于較長(zhǎng)的字符串列（如 TEXT、VARCHAR 類型），可以只對(duì)前綴部分創(chuàng)建索引，這樣可以減少索引的大小，提高索引的效率。創(chuàng)建前綴索引的語法如下：

CREATE INDEX index_name ON table_name(column_name(length));

其中，length 表示前綴的長(zhǎng)度。

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

確定針對(duì)該表的操作是大量的查詢操作還是大量的增刪改操作

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

索引建立的原則

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

• 在經(jīng)常用作過濾器的字段上建立索引;
• 在SQL語句中經(jīng)常進(jìn)行GROUPBY、ORDERBY的字段上建立索引
• 在不同值較少的字段上不必要建立索引,如性別字段;
• 對(duì)于經(jīng)常存取的列避免建立索引;

在經(jīng)常存取的多個(gè)列上建立復(fù)合索引,但要注意復(fù)合索引的建立順序要按照

• 使用的頻度來確定;
• 缺省情況下建立的是非簇集索引,但在以下情況下最好考慮簇復(fù)集索引,如含有限數(shù)目(不是很少)唯一的列;進(jìn)行大范圍的查詢;充分的利用索引可以減少表掃描I/0的次數(shù),有效的避免對(duì)整表的搜索。
• 當(dāng)然合理里的索引要建立在對(duì)各種查詢的分析和預(yù)測(cè)中,也取決于DBA所設(shè)計(jì)的數(shù)據(jù)庫結(jié)構(gòu)

查看索引

在 MySQL 中，可以使用以下幾種方法查看表中的索引：

使用 SHOW INDEX 語句：

SHOW INDEX FROM table_name;

該語句會(huì)返回表中所有索引的詳細(xì)信息，包括索引名稱、索引類型、索引所在的列等。

使用 SHOW CREATE TABLE 語句：

SHOW CREATE TABLE table_name;

該語句會(huì)返回創(chuàng)建表的 SQL 語句，其中包含了表的結(jié)構(gòu)和索引信息。

查詢 information_schema.statistics 表：

SELECT * FROM information_schema.statistics WHERE table_name = 'table_name';

該語句會(huì)返回表中所有索引的詳細(xì)信息，包括索引名稱、索引類型、索引所在的列等。

刪除索引

在 MySQL 中，可以使用以下語法刪除索引：

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP PRIMARY KEY;

需要注意的是，刪除索引會(huì)影響依賴該索引的查詢性能，因此在刪除索引之前，應(yīng)該仔細(xì)評(píng)估其影響。

mysql事務(wù)

事務(wù)概述

在 MySQL 中，事務(wù)是一組不可分割的 SQL 語句序列，這些語句要么全部執(zhí)行成功，要么全部執(zhí)行失敗。事務(wù)是數(shù)據(jù)庫管理系統(tǒng)中實(shí)現(xiàn)數(shù)據(jù)一致性和完整性的重要機(jī)制。

事務(wù)具有四個(gè)基本特性，通常稱為 ACID 特性：

• 原子性（Atomicity）：事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中的所有操作要么全部成功，要么全部失敗。如果事務(wù)中的任何一個(gè)操作失敗，整個(gè)事務(wù)將被回滾到初始狀態(tài)。
• 一致性（Consistency）：事務(wù)必須使數(shù)據(jù)庫從一個(gè)一致性狀態(tài)轉(zhuǎn)換到另一個(gè)一致性狀態(tài)。也就是說，事務(wù)執(zhí)行前后，數(shù)據(jù)庫的完整性約束必須得到滿足。例如，在轉(zhuǎn)賬操作中，無論事務(wù)執(zhí)行成功還是失敗，兩個(gè)賬戶的總金額必須保持不變。
• 隔離性（Isolation）：多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾。每個(gè)事務(wù)都應(yīng)該感覺不到其他事務(wù)的存在，就好像它們是串行執(zhí)行的一樣。
• 持久性（Durability）：一旦事務(wù)提交成功，它對(duì)數(shù)據(jù)庫所做的修改就會(huì)永久保存下來，即使數(shù)據(jù)庫發(fā)生故障也不會(huì)丟失。

mysql事務(wù)處理的倆種方法

MySQL 提供了兩種處理事務(wù)的方法：隱式事務(wù)和顯式事務(wù)。

隱式事務(wù)

隱式事務(wù)是指 MySQL 自動(dòng)為每條 SQL 語句創(chuàng)建一個(gè)事務(wù)，并在語句執(zhí)行完成后自動(dòng)提交或回滾。在隱式事務(wù)模式下，每條 SQL 語句都是一個(gè)獨(dú)立的事務(wù)，它們之間沒有任何關(guān)聯(lián)。

MySQL 默認(rèn)采用隱式事務(wù)模式。在這種模式下，每條 SQL 語句執(zhí)行后都會(huì)自動(dòng)提交，除非發(fā)生錯(cuò)誤時(shí)會(huì)自動(dòng)回滾。例如：

INSERT INTO users (name, age) VALUES ('John', 30);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

在上面的例子中，每條 SQL 語句都是一個(gè)獨(dú)立的事務(wù)，它們之間沒有任何關(guān)聯(lián)。如果第一條 INSERT 語句執(zhí)行成功，而第二條 UPDATE 語句執(zhí)行失敗，第一條語句的修改不會(huì)被回滾。

隱式事務(wù)適用于不需要保證多個(gè) SQL 語句原子性的場(chǎng)景，例如簡(jiǎn)單的查詢和單條數(shù)據(jù)的插入、更新操作。

顯式事務(wù)

顯式事務(wù)是指用戶手動(dòng)控制事務(wù)的開始、提交和回滾。在顯式事務(wù)模式下，用戶可以將多條 SQL 語句組合成一個(gè)事務(wù)，確保這些語句要么全部執(zhí)行成功，要么全部執(zhí)行失敗。

在 MySQL 中，可以使用以下語句來管理顯式事務(wù)：

• START TRANSACTION 或 BEGIN：用于開始一個(gè)事務(wù)。
• COMMIT：用于提交事務(wù)，將事務(wù)中所有的修改永久保存到數(shù)據(jù)庫中。
• ROLLBACK：用于回滾事務(wù)，撤銷事務(wù)中所有的修改，將數(shù)據(jù)庫恢復(fù)到事務(wù)開始前的狀態(tài)
• BEGIN或START TRANSACTION:顯式地開啟一個(gè)事務(wù);
• ROLLBACK:又可以使用ROLLBACK WORK,不過二者是等價(jià)的?；貪L會(huì)結(jié)束用戶的事務(wù),并撤銷正在進(jìn)行的所有未提交的修改;
• SAVEPOINT identifier:SAVEPOINT允許在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn),一個(gè)事務(wù)中可以有多個(gè)SAVEPOINT;
• RELEASE SAVEPOINT identifier:刪除一個(gè)事務(wù)的保存點(diǎn),當(dāng)沒有指定的保存點(diǎn)時(shí),執(zhí)行該語句會(huì)拋出一個(gè)異常;
• ROLLBACK TO identifier:把事務(wù)回滾到標(biāo)記點(diǎn);
• SET TRANSACTION:用來設(shè)置事務(wù)的隔離級(jí)別。InnoDB存儲(chǔ)引擎提供事務(wù)的隔離級(jí)別 READ UNCOMMITTED 、READ COMMITTED、REPEATABLE READ 和SERIALIZABLE。

下面是一個(gè)顯式事務(wù)的示例：

START TRANSACTION;
INSERT INTO users (name, age) VALUES ('John', 30);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
INSERT INTO transactions (user_id, amount, type) VALUES (1, 100, 'withdrawal'); COMMIT;

簡(jiǎn)單介紹msyql事務(wù)處理主要方法（倆種）

用BEGIN,ROLLBACK,COMMIT來實(shí)現(xiàn)

• BEGIN開始一個(gè)事務(wù)
• ROLLBACK事務(wù)回滾
• COMMIT 事務(wù)確認(rèn)

直接用SET來改變MySQL的自動(dòng)提交模式

SET AUTOCOMMIT=0禁止自動(dòng)提交
SET AUTOCOMMIT=1開啟自動(dòng)提交

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論