快捷導(dǎo)航

MySQL中索引添加與刪除的操作方法詳解

更新時間：2025年11月10日 10:24:20 作者：Java 碼農(nóng)

MySQL索引是提高查詢性能的關(guān)鍵,文章詳細(xì)介紹了如何添加和刪除索引,包括使用ALTER TABLE和CREATE INDEX語句,還討論了索引類型、查看索引信息、最佳實踐和注意事項,感興趣的朋友跟隨小編一起看看吧

1. 添加索引

1.1 創(chuàng)建表時添加索引

-- 創(chuàng)建表時添加普通索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),  -- 普通索引
    UNIQUE INDEX idx_email (email)  -- 唯一索引
);
-- 創(chuàng)建表時添加復(fù)合索引
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    INDEX idx_user_date (user_id, order_date)  -- 復(fù)合索引
);

1.2 使用 ALTER TABLE 添加索引

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_username (username);
-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
-- 添加主鍵索引（如果表沒有主鍵）
ALTER TABLE users ADD PRIMARY KEY (id);
-- 添加復(fù)合索引
ALTER TABLE orders ADD INDEX idx_user_date (user_id, order_date);
-- 添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);

1.3 使用 CREATE INDEX 語句

-- 創(chuàng)建普通索引
CREATE INDEX idx_username ON users (username);
-- 創(chuàng)建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);
-- 創(chuàng)建復(fù)合索引
CREATE INDEX idx_user_date ON orders (user_id, order_date);
-- 創(chuàng)建全文索引
CREATE FULLTEXT INDEX idx_content ON articles (content);

2. 刪除索引

2.1 使用 ALTER TABLE 刪除索引

-- 刪除普通索引
ALTER TABLE users DROP INDEX idx_username;
-- 刪除唯一索引
ALTER TABLE users DROP INDEX idx_email;
-- 刪除主鍵索引
ALTER TABLE users DROP PRIMARY KEY;

2.2 使用 DROP INDEX 語句

-- 刪除索引
DROP INDEX idx_username ON users;
-- 刪除唯一索引
DROP INDEX idx_email ON users;

3. 查看索引信息

-- 查看表的索引信息
SHOW INDEX FROM users;
-- 查看創(chuàng)建表的語句（包含索引信息）
SHOW CREATE TABLE users;
-- 通過信息模式查看索引
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'users';

4. 索引類型說明

4.1 不同類型的索引

-- 普通索引（最基本的索引，沒有唯一性限制）
CREATE INDEX idx_name ON table_name (column_name);
-- 唯一索引（確保列值的唯一性）
CREATE UNIQUE INDEX idx_name ON table_name (column_name);
-- 主鍵索引（特殊的唯一索引，不允許NULL值）
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
-- 復(fù)合索引（多列組合的索引）
CREATE INDEX idx_name ON table_name (col1, col2, col3);
-- 全文索引（用于全文搜索）
CREATE FULLTEXT INDEX idx_name ON table_name (text_column);
-- 空間索引（用于地理數(shù)據(jù)）
CREATE SPATIAL INDEX idx_name ON table_name (spatial_column);

5. 實際示例

-- 創(chuàng)建示例表
CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10,2),
    PRIMARY KEY (id)
);
-- 添加各種索引
-- 單列索引
ALTER TABLE employees ADD INDEX idx_last_name (last_name);
-- 唯一索引
ALTER TABLE employees ADD UNIQUE INDEX idx_email (email);
-- 復(fù)合索引
ALTER TABLE employees ADD INDEX idx_dept_hire (department_id, hire_date);
-- 查看索引
SHOW INDEX FROM employees;
-- 刪除索引
ALTER TABLE employees DROP INDEX idx_last_name;
DROP INDEX idx_email ON employees;

6. 最佳實踐和注意事項

6.1 索引設(shè)計建議

-- 為經(jīng)常查詢的列創(chuàng)建索引
CREATE INDEX idx_frequently_queried ON table_name (frequently_queried_column);
-- 為外鍵列創(chuàng)建索引
CREATE INDEX idx_foreign_key ON child_table (parent_id);
-- 為WHERE子句中的列創(chuàng)建索引
CREATE INDEX idx_where_condition ON table_name (column_used_in_where);
-- 為ORDER BY和GROUP BY的列創(chuàng)建索引
CREATE INDEX idx_order_group ON table_name (column_used_for_ordering);

6.2 注意事項

不要過度索引：每個索引都會增加寫操作的開銷
選擇合適的前綴長度：對于長文本列

CREATE INDEX idx_name ON table_name (column_name(10)); -- 前綴索引

考慮索引選擇性：高選擇性的列更適合建索引
定期維護(hù)索引：使用 ANALYZE TABLE 更新索引統(tǒng)計信息

6.3 性能監(jiān)控

-- 檢查索引使用情況
EXPLAIN SELECT * FROM users WHERE username = 'john';
-- 查看未使用的索引（需要開啟性能模式）
SELECT * FROM sys.schema_unused_indexes;

通過合理使用索引，可以顯著提高 MySQL 數(shù)據(jù)庫的查詢性能，但需要根據(jù)具體的查詢模式和數(shù)據(jù)特征來設(shè)計和維護(hù)索引。

到此這篇關(guān)于MySQL中索引添加與刪除的操作方法詳解的文章就介紹到這了,更多相關(guān)mysql索引添加與刪除內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片