一、MySQL 索引

1. 索引基礎(chǔ)認(rèn)知

索引是數(shù)據(jù)庫中排好序的索引值 + 數(shù)據(jù)行物理地址的列表，作用類似圖書目錄，能通過二分查找快速定位數(shù)據(jù)，避免全表掃描，大幅提升查詢效率；但索引會(huì)占用額外磁盤空間，且增刪改操作會(huì)因維護(hù)索引變慢，需按需創(chuàng)建。

補(bǔ)充知識(shí)點(diǎn)

• MySQL 默認(rèn)索引結(jié)構(gòu)是 B + 樹，查詢效率極高。
• 索引會(huì)加快查詢，但會(huì)減慢增刪改。
• 小表、重復(fù)值多的字段不適合建索引。

2. 索引的分類

（1）物理分類：聚簇索引 & 非聚簇索引

• 聚簇索引：按數(shù)據(jù)物理存儲(chǔ)位置排序，多行檢索速度快（InnoDB 主鍵默認(rèn)是聚簇索引）
• 非聚簇索引：索引與數(shù)據(jù)物理存儲(chǔ)分離，單行檢索速度快（MyISAM 默認(rèn)非聚簇索引）

補(bǔ)充知識(shí)點(diǎn)

• 一張表只能有一個(gè)聚簇索引（主鍵）。
• 非聚簇索引需要回表查詢，效率略低。

（2）邏輯分類（核心常用）

表格

3. 索引的創(chuàng)建（標(biāo)準(zhǔn)格式 + 代碼）

（1）普通索引

標(biāo)準(zhǔn)命令格式

CREATE INDEX 索引名 ON 表名(字段名);
ALTER TABLE 表名 ADD INDEX 索引名(字段名);

代碼

-- 直接創(chuàng)建
CREATE INDEX index_name ON table_name(column(length));
-- 修改表結(jié)構(gòu)創(chuàng)建
ALTER TABLE table_name ADD INDEX index_name (column(length));
-- 建表時(shí)創(chuàng)建
CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` char(255) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX index_name (title(50))
);

補(bǔ)充知識(shí)點(diǎn)

• 普通索引無約束，只用于加速查詢。
• 前綴索引只索引前 N 個(gè)字符，節(jié)省空間。

（2）唯一索引

標(biāo)準(zhǔn)命令格式

CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(字段名);

代碼

-- 直接創(chuàng)建
CREATE UNIQUE INDEX index_name ON table_name(column(length));
-- 修改表結(jié)構(gòu)創(chuàng)建
ALTER TABLE table_name ADD UNIQUE index_name (column(length));
-- 建表時(shí)創(chuàng)建
CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` char(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE indexName (title(50))
);

補(bǔ)充知識(shí)點(diǎn)

• 唯一索引值不可重復(fù)，但允許 NULL。
• 一個(gè)表可以創(chuàng)建多個(gè)唯一索引。

（3）主鍵索引

標(biāo)準(zhǔn)命令格式

CREATE TABLE 表名(
  字段名 類型 PRIMARY KEY AUTO_INCREMENT
);

代碼

CREATE TABLE `table_name` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` char(255) NOT NULL,
  PRIMARY KEY (`id`)
);

補(bǔ)充知識(shí)點(diǎn)

• 主鍵索引不能為空、不能重復(fù)。
• 一張表只能有一個(gè)主鍵。

（4）組合索引

標(biāo)準(zhǔn)命令格式

CREATE INDEX 索引名 ON 表名(字段1,字段2,字段3);

代碼

CREATE TABLE user(
  `name` varchar(9),
  `age` int(3),
  `sex` tinyint(1),
  INDEX user_index(name, age, sex)
);
-- 有效查詢
SELECT * FROM user WHERE name = '張三' AND age = 20 AND sex = 1;
-- 無效查詢
SELECT * FROM user WHERE age = 20 AND sex = 1;

補(bǔ)充知識(shí)點(diǎn)

• 組合索引遵循最左前綴原則，必須從第一個(gè)字段開始匹配。
• 建一個(gè)組合索引 ≈ 建多個(gè)單列索引。

（5）全文索引

標(biāo)準(zhǔn)命令格式

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(字段名);
CREATE FULLTEXT INDEX 索引名 ON 表名(字段名);

代碼

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` char(255) NOT NULL,
  `content` text NULL,
  PRIMARY KEY (`id`),
  FULLTEXT (content)
);
ALTER TABLE article ADD FULLTEXT index_content(content);
CREATE FULLTEXT INDEX index_content ON article(content);

補(bǔ)充知識(shí)點(diǎn)

• 全文索引比 LIKE 快很多，專門用于文本搜索。
• 只支持 CHAR、VARCHAR、TEXT 類型。

4. 索引的查看與刪除

（1）查看索引

標(biāo)準(zhǔn)命令格式

SHOW INDEX FROM 表名;

代碼

SHOW INDEX FROM tablename;
SHOW KEYS FROM tablename;
SHOW INDEX FROM renyuan\G;

（2）刪除索引

標(biāo)準(zhǔn)命令格式

DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;

代碼

ALTER TABLE 表名 DROP INDEX 索引名;
DROP INDEX 索引名 ON 表名;

5. 創(chuàng)建索引的核心原則

1. 查多增刪改少的表適合建索引，小表無需建索引
2. WHERE、JOIN、GROUP BY、ORDER BY 字段優(yōu)先建索引
3. 低基數(shù)字段（性別、狀態(tài)）不建索引
4. 大字段用前綴索引
5. 組合索引嚴(yán)格遵循最左前綴原則

二、MySQL 事務(wù)

1. 事務(wù)基礎(chǔ)認(rèn)知

事務(wù)是一組不可分割的操作，要么全部成功，要么全部回滾，保證數(shù)據(jù)安全。

• 只有 InnoDB 支持事務(wù)
• 只管理 INSERT/UPDATE/DELETE

補(bǔ)充知識(shí)點(diǎn)

• 事務(wù)用于保證數(shù)據(jù)一致性，如轉(zhuǎn)賬、訂單、支付。
• 事務(wù)失敗會(huì)自動(dòng)回滾，不會(huì)產(chǎn)生臟數(shù)據(jù)。

2. 事務(wù)的 ACID 四大特性

• 原子性：不可分割，全成功或全失敗
• 一致性：數(shù)據(jù)前后規(guī)則不變
• 隔離性：并發(fā)事務(wù)互不干擾
• 持久性：提交后永久生效

補(bǔ)充知識(shí)點(diǎn)

• ACID 是事務(wù)的核心標(biāo)準(zhǔn)。
• 原子性保證操作安全，一致性保證數(shù)據(jù)不亂。

3. 事務(wù)的隔離級(jí)別

InnoDB 默認(rèn)：可重復(fù)讀（REPEATABLE READ）

1. 讀未提交：臟讀
2. 讀提交：不可重復(fù)讀
3. 可重復(fù)讀：幻讀
4. 串行化：無問題，但性能低

補(bǔ)充知識(shí)點(diǎn)

• 臟讀：讀到未提交的數(shù)據(jù)
• 不可重復(fù)讀：同一事務(wù)兩次查詢結(jié)果不同
• 幻讀：查詢時(shí)突然多 / 少了數(shù)據(jù)

4. 事務(wù)控制語句（格式 + 代碼）

標(biāo)準(zhǔn)命令格式

BEGIN;            -- 開啟事務(wù)
COMMIT;           -- 提交
ROLLBACK;         -- 回滾
SAVEPOINT 名稱;    -- 保存點(diǎn)
SET AUTOCOMMIT=0; -- 關(guān)閉自動(dòng)提交

代碼

BEGIN;
COMMIT;
ROLLBACK;
SAVEPOINT sp1;
ROLLBACK TO sp1;
RELEASE SAVEPOINT sp1;
SET AUTOCOMMIT=0;
SET AUTOCOMMIT=1;

補(bǔ)充知識(shí)點(diǎn)

• MySQL 默認(rèn)自動(dòng)提交，執(zhí)行 SQL 立即生效。
• 手動(dòng)事務(wù)必須手動(dòng) COMMIT 才會(huì)保存。

事務(wù)實(shí)操示例

CREATE DATABASE IF NOT EXISTS kgc;
USE kgc;
CREATE TABLE test( id int(5)) engine=innodb;
BEGIN;
INSERT INTO test VALUE(1);
INSERT INTO test VALUE(2);
COMMIT;
BEGIN;
INSERT INTO test VALUES(3);
ROLLBACK;

三、補(bǔ)充：庫 & 表操作（格式 + 知識(shí)點(diǎn)）

1. 數(shù)據(jù)庫操作

標(biāo)準(zhǔn)格式

CREATE DATABASE 庫名;
USE 庫名;
DROP DATABASE 庫名;
SHOW DATABASES;

代碼

CREATE DATABASE IF NOT EXISTS test;
USE test;

補(bǔ)充知識(shí)點(diǎn)

• 必須 USE 庫名，否則無法操作表。
• IF NOT EXISTS 防止重復(fù)創(chuàng)建報(bào)錯(cuò)。

2. 數(shù)據(jù)表操作

標(biāo)準(zhǔn)格式

CREATE TABLE 表名(字段 類型 約束) ENGINE=InnoDB;
DESC 表名;
SHOW TABLES;

代碼

CREATE TABLE user(
    id INT PRIMARY KEY AUTO_INCREMENT
) ENGINE=InnoDB;
DESC user;

補(bǔ)充知識(shí)點(diǎn)

• 必須用 InnoDB 才能用事務(wù)和索引。
• DESC 表名：快速查看表結(jié)構(gòu)。

四、最終總結(jié)

1. 索引 = 查詢加速，有固定創(chuàng)建 / 刪除格式
2. 事務(wù) = 數(shù)據(jù)安全，要么全成要么全滾
3. 操作表前必須 USE 庫名
4. 表引擎必須是 InnoDB
5. 索引和事務(wù)要按需使用，平衡性能與安全

到此這篇關(guān)于MySQL 索引與事務(wù)核心知識(shí)點(diǎn)全解的文章就介紹到這了,更多相關(guān)myslq索引與事務(wù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！