mysql如何添加索引 mysql創(chuàng)建索引的三種方法詳解

4610

在mysql中，為表添加索引有三種主要方法：1.使用create index語(yǔ)句，適用于已存在的表，語(yǔ)法為create index index_name on table_name (column_list)；2.通過(guò)alter table add index語(yǔ)句，同樣用于已有表，語(yǔ)法為alter table table_name add index index_name (column_list)，還可添加唯一索引或主鍵；3.在create table語(yǔ)句中直接定義索引，這是最佳實(shí)踐，可在建表時(shí)規(guī)劃好主鍵、唯一索引和普通索引。這三種方式分別適用于不同場(chǎng)景，create index和alter table適用于對(duì)現(xiàn)有表進(jìn)行優(yōu)化，而create table則適合提前規(guī)劃索引結(jié)構(gòu)，提升查詢效率并減少后期修改帶來(lái)的開(kāi)銷(xiāo)。

mysql如何添加索引 mysql創(chuàng)建索引的三種方法詳解

在mysql中，為表添加索引是提升查詢性能最直接也最有效的方法之一。核心上，這可以通過(guò)三種主要途徑實(shí)現(xiàn)：使用獨(dú)立的CREATE INDEX語(yǔ)句、通過(guò)ALTER TABLE命令修改現(xiàn)有表結(jié)構(gòu)來(lái)添加，以及在創(chuàng)建新表時(shí)就直接定義好索引。理解并恰當(dāng)運(yùn)用這些方法，能讓你的數(shù)據(jù)庫(kù)查詢效率得到質(zhì)的飛躍。

mysql如何添加索引 mysql創(chuàng)建索引的三種方法詳解

解決方案

mysql如何添加索引 mysql創(chuàng)建索引的三種方法詳解

為MySQL表添加索引，主要有以下三種方法：

  1. 使用 CREATE INDEX 語(yǔ)句 這是最直接的索引創(chuàng)建方式，通常用于給已存在的表添加索引。它是一個(gè)獨(dú)立的sql語(yǔ)句，專門(mén)用于索引操作。

    mysql如何添加索引 mysql創(chuàng)建索引的三種方法詳解

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

    例如，為users表的email列創(chuàng)建一個(gè)名為idx_email的普通索引：

    CREATE INDEX idx_email ON users (email);

  2. 使用 ALTER TABLE ADD INDEX 語(yǔ)句 這種方法也是針對(duì)已存在的表，通過(guò)修改表結(jié)構(gòu)的方式來(lái)添加索引。在實(shí)際工作中，它和CREATE INDEX的最終效果類(lèi)似，但語(yǔ)法上是作為ALTER TABLE命令的一部分。

    ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

    你也可以添加其他類(lèi)型的索引，比如唯一索引或主鍵：

    -- 添加唯一索引 ALTER TABLE users ADD UNIQUE INDEX uniq_username (username);  -- 添加主鍵（如果表中還沒(méi)有主鍵） ALTER TABLE products ADD PRIMARY KEY (product_id);

  3. 在 CREATE TABLE 語(yǔ)句中定義索引 這是在表創(chuàng)建之初就規(guī)劃好索引的最佳實(shí)踐。在定義表結(jié)構(gòu)的同時(shí)，直接聲明所需的索引，包括主鍵、唯一索引和普通索引。

    CREATE TABLE table_name (     column1 datatype PRIMARY KEY, -- 主鍵     column2 datatype UNIQUE,     -- 唯一索引     column3 datatype,     column4 datatype,     INDEX index_name (column3, column4) -- 普通索引 );

    例如，創(chuàng)建一個(gè)orders表，并在創(chuàng)建時(shí)就定義好主鍵和普通索引：

    CREATE TABLE orders (     order_id int PRIMARY KEY AUTO_INCREMENT,     user_id INT,     order_date DATETIME,     total_amount DECIMAL(10, 2),     INDEX idx_user_order_date (user_id, order_date) );

為什么我的查詢這么慢？——索引的價(jià)值與選擇

我剛開(kāi)始接觸數(shù)據(jù)庫(kù)的時(shí)候，也經(jīng)常遇到那種跑個(gè)查詢要等半天的場(chǎng)景，當(dāng)時(shí)真的非常抓狂。后來(lái)才明白，很多時(shí)候并不是數(shù)據(jù)量有多大，而是數(shù)據(jù)庫(kù)在茫茫數(shù)據(jù)中“大海撈針”了。索引的價(jià)值就在于此，它就像一本書(shū)的目錄，能讓數(shù)據(jù)庫(kù)系統(tǒng)快速定位到需要的數(shù)據(jù)行，而不是逐行掃描整個(gè)表。

想象一下，你有一張幾百萬(wàn)行的用戶表，如果想根據(jù)用戶名查找某個(gè)用戶，沒(méi)有索引，數(shù)據(jù)庫(kù)就得一行一行地找，直到找到為止。但如果username列上有一個(gè)索引，數(shù)據(jù)庫(kù)就能通過(guò)索引的B+樹(shù)結(jié)構(gòu)，像查字典一樣，迅速找到對(duì)應(yīng)的用戶名所在的數(shù)據(jù)頁(yè)，大大減少了磁盤(pán)I/O和CPU的消耗。這種從“分鐘級(jí)”到“毫秒級(jí)”的性能提升，體驗(yàn)過(guò)一次就再也回不去了。

不過(guò)，索引也不是萬(wàn)能藥，它就像一把雙刃劍。雖然能加速查詢，但也會(huì)增加寫(xiě)入（插入、更新、刪除）操作的開(kāi)銷(xiāo)，因?yàn)槊看螖?shù)據(jù)變動(dòng)，索引也需要同步更新。而且，索引本身也需要占用存儲(chǔ)空間。所以，選擇哪些列加索引，加什么類(lèi)型的索引，這都是需要深思熟慮的。通常我會(huì)考慮以下幾點(diǎn)：

  • 查詢頻率高且條件明確的列：比如WHERE子句、JOIN條件、ORDER BY和GROUP BY中經(jīng)常出現(xiàn)的列。
  • 列的區(qū)分度（Cardinality）：如果一個(gè)列的值重復(fù)率很高（比如性別），那索引的效果可能就不太好。我更傾向于給那些值比較分散、唯一性強(qiáng)的列加索引。
  • 復(fù)合索引的順序：對(duì)于多列索引（復(fù)合索引），列的順序非常重要。遵循“最左前綴原則”，把查詢中最常用的列放在前面。
  • 避免過(guò)度索引：不是所有列都需要索引，小表、經(jīng)常變動(dòng)的列、區(qū)分度低的列，加索引可能弊大于利。

現(xiàn)有表如何優(yōu)化？——使用ALTER TABLE ADD INDEX

對(duì)于已經(jīng)投入使用的系統(tǒng)，我們經(jīng)常會(huì)遇到需要對(duì)現(xiàn)有表進(jìn)行性能優(yōu)化的情況。這時(shí)候，ALTER TABLE ADD INDEX就成了我的首選工具。它的優(yōu)勢(shì)在于，你不需要重新創(chuàng)建整個(gè)表，只需簡(jiǎn)單地執(zhí)行一條命令，就能為指定的列添加索引。

例如，你有一個(gè)products表，product_name列經(jīng)常用于搜索，但之前沒(méi)有加索引，導(dǎo)致查詢速度慢。那么，你可以這樣操作：

ALTER TABLE products ADD INDEX idx_product_name (product_name);

這條命令會(huì)通知MySQL在products表的product_name列上創(chuàng)建一個(gè)名為idx_product_name的普通索引。

需要注意的是，對(duì)于非常大的表，ALTER TABLE操作可能會(huì)導(dǎo)致表被鎖定一段時(shí)間，這會(huì)影響線上業(yè)務(wù)的正常運(yùn)行。在MySQL 5.6及更高版本中，引入了Online DDL特性，允許在添加索引時(shí)盡量減少或避免表鎖定，但這依然需要根據(jù)具體的MySQL版本、存儲(chǔ)引擎（InnoDB通常支持Online DDL更好）和操作類(lèi)型來(lái)判斷。在生產(chǎn)環(huán)境執(zhí)行這類(lèi)操作前，我總會(huì)先在測(cè)試環(huán)境模擬一遍，評(píng)估其對(duì)業(yè)務(wù)的影響。如果表真的非常大，我甚至?xí)紤]采用一些更高級(jí)的技巧，比如先在一個(gè)臨時(shí)表上創(chuàng)建索引，再進(jìn)行表交換，以達(dá)到幾乎無(wú)停機(jī)添加索引的目的。

新建表時(shí)如何未雨綢繆？——CREATE TABLE中的索引定義

我個(gè)人在設(shè)計(jì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)時(shí)，總是盡可能地在CREATE TABLE階段就把索引規(guī)劃好。這就像蓋房子前就把水電線路圖畫(huà)好，而不是等房子蓋好了再鑿墻布線。這種“未雨綢繆”的做法，不僅能讓表結(jié)構(gòu)更清晰，也能避免后期修改表結(jié)構(gòu)帶來(lái)的額外開(kāi)銷(xiāo)和潛在風(fēng)險(xiǎn)。

在CREATE TABLE語(yǔ)句中定義索引，語(yǔ)法上非常直觀。你可以直接在列定義后面加上PRIMARY KEY、UNIQUE，或者在表定義的最后加上INDEX或KEY來(lái)創(chuàng)建普通索引。

比如，我們要?jiǎng)?chuàng)建一個(gè)存儲(chǔ)文章的articles表，通常會(huì)根據(jù)文章ID進(jìn)行查詢，也可能根據(jù)作者ID和發(fā)布日期進(jìn)行組合查詢：

CREATE TABLE articles (     article_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '文章ID，主鍵',     title VARCHAR(255) NOT NULL COMMENT '文章標(biāo)題',     author_id INT NOT NULL COMMENT '作者ID',     publish_date DATETIME NOT NULL COMMENT '發(fā)布日期',     content TEXT COMMENT '文章內(nèi)容',     INDEX idx_author_date (author_id, publish_date) COMMENT '作者ID和發(fā)布日期組合索引' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '文章表';

在這個(gè)例子中：

  • article_id INT PRIMARY KEY AUTO_INCREMENT：直接定義了article_id為主鍵，它自帶唯一性和非空約束，是最高效的索引。
  • INDEX idx_author_date (author_id, publish_date)：創(chuàng)建了一個(gè)名為idx_author_date的復(fù)合索引，覆蓋了author_id和publish_date兩列。這意味著，如果我需要查詢某個(gè)作者在某個(gè)日期范圍內(nèi)的文章，這個(gè)索引就能派上大用場(chǎng)。

這種方式的好處在于，表一旦創(chuàng)建，索引就已經(jīng)就位，無(wú)需額外的DDL操作。這對(duì)于后續(xù)的開(kāi)發(fā)和部署流程來(lái)說(shuō)，無(wú)疑是更簡(jiǎn)潔、更高效的選擇。它強(qiáng)制你在設(shè)計(jì)階段就考慮查詢模式，從而構(gòu)建出更健壯、性能更優(yōu)的數(shù)據(jù)庫(kù)結(jié)構(gòu)。

以上就是

? 版權(quán)聲明
THE END
數(shù)據(jù)庫(kù)
# 工具# 數(shù)據(jù)庫(kù)# mysql# ai# 性能優(yōu)化# sql# 為什么# sql語(yǔ)句# table# int
喜歡就支持一下吧
點(diǎn)贊10 分享