mysql索引介紹

索引概述

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

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

索引作用

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

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

索引分類

MySQL 提供了多種類型的索引，每種索引都有其特定的應用場景。

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

普通索引

普通索引是最基本的索引類型，它沒有任何特殊的限制。普通索引可以創(chuàng)建在單個列上，也可以創(chuàng)建在多個列上（組合索引）。創(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) );

唯一索引

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

創(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) );

主鍵索引

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

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

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

組合索引（最左前綴）

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

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

創(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ù)中進行全文搜索。全文索引可以快速定位包含特定關鍵詞的文本數(shù)據(jù)，適用于大量文本數(shù)據(jù)的搜索場景，如文章內(nèi)容搜索、產(chǎn)品描述搜索等。

需要注意的是，全文索引只適用于 MyISAM 和 InnoDB 存儲引擎，并且只能用于 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) );

使用全文索引進行搜索的語法如下：

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

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

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

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

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

其中，length 表示前綴的長度。

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

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

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

索引建立的原則

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

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

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

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

查看索引

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

使用 SHOW INDEX 語句：

SHOW INDEX FROM table_name;

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

使用 SHOW CREATE TABLE 語句：

SHOW CREATE TABLE table_name;

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

查詢 information_schema.statistics 表：

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

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

刪除索引

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

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

需要注意的是，刪除索引會影響依賴該索引的查詢性能，因此在刪除索引之前，應該仔細評估其影響。

mysql事務

事務概述

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

事務具有四個基本特性，通常稱為 ACID 特性：

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

mysql事務處理的倆種方法

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

隱式事務

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

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

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

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

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

顯式事務

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

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

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

下面是一個顯式事務的示例：

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;

簡單介紹msyql事務處理主要方法（倆種）

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

• BEGIN開始一個事務
• ROLLBACK事務回滾
• COMMIT 事務確認

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

SET AUTOCOMMIT=0禁止自動提交
SET AUTOCOMMIT=1開啟自動提交

總結(jié)

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

最新評論