MySQL索引添加与删除方法实际示例
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">1. 添加索引</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.1 创建表时添加索引</a></li><li><a href="#_lab2_0_1">1.2 使用 ALTER TABLE 添加索引</a></li><li><a href="#_lab2_0_2">1.3 使用 CREATE INDEX 语句</a></li></ul><li><a href="#_label1">2. 删除索引</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_3">2.1 使用 ALTER TABLE 删除索引</a></li><li><a href="#_lab2_1_4">2.2 使用 DROP INDEX 语句</a></li></ul><li><a href="#_label2">3. 查看索引信息</a></li><ul class="second_class_ul"></ul><li><a href="#_label3">4. 索引类型说明</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_5">4.1 不同类型的索引</a></li></ul><li><a href="#_label4">5. 实际示例</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">6. 最佳实践和注意事项</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_6">6.1 索引设计建议</a></li><li><a href="#_lab2_5_7">6.2 注意事项</a></li><li><a href="#_lab2_5_8">6.3 性能监控</a></li></ul></ul></div><p>在 MySQL 中，索引是提高查询性能的重要工具。以下是关于 MySQL 索引添加和删除的详细说明：</p><p class="maodian"><a name="_label0"></a></p><h2>1. 添加索引</h2>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1.1 创建表时添加索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- 创建表时添加普通索引
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)-- 唯一索引
);
-- 创建表时添加复合索引
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)-- 复合索引
);</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>1.2 使用 ALTER TABLE 添加索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- 添加普通索引
ALTER TABLE users ADD INDEX idx_username (username);
-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
-- 添加主键索引（如果表没有主键）
ALTER TABLE users ADD PRIMARY KEY (id);
-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_user_date (user_id, order_date);
-- 添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);</pre></div>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>1.3 使用 CREATE INDEX 语句</h3>
<div class="jb51code"><pre class="brush:sql;">-- 创建普通索引
CREATE INDEX idx_username ON users (username);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);
-- 创建复合索引
CREATE INDEX idx_user_date ON orders (user_id, order_date);
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles (content);</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>2. 删除索引</h2>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>2.1 使用 ALTER TABLE 删除索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- 删除普通索引
ALTER TABLE users DROP INDEX idx_username;
-- 删除唯一索引
ALTER TABLE users DROP INDEX idx_email;
-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;</pre></div>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>2.2 使用 DROP INDEX 语句</h3>
<div class="jb51code"><pre class="brush:sql;">-- 删除索引
DROP INDEX idx_username ON users;
-- 删除唯一索引
DROP INDEX idx_email ON users;</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>3. 查看索引信息</h2>
<div class="jb51code"><pre class="brush:sql;">-- 查看表的索引信息
SHOW INDEX FROM users;
-- 查看创建表的语句（包含索引信息）
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';</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>4. 索引类型说明</h2>
<p class="maodian"><a name="_lab2_3_5"></a></p><h3>4.1 不同类型的索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- 普通索引（最基本的索引，没有唯一性限制）
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);
-- 复合索引（多列组合的索引）
CREATE INDEX idx_name ON table_name (col1, col2, col3);
-- 全文索引（用于全文搜索）
CREATE FULLTEXT INDEX idx_name ON table_name (text_column);
-- 空间索引（用于地理数据）
CREATE SPATIAL INDEX idx_name ON table_name (spatial_column);</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>5. 实际示例</h2>
<div class="jb51code"><pre class="brush:sql;">-- 创建示例表
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);
-- 复合索引
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;</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>6. 最佳实践和注意事项</h2>
<p class="maodian"><a name="_lab2_5_6"></a></p><h3>6.1 索引设计建议</h3>
<div class="jb51code"><pre class="brush:sql;">-- 为经常查询的列创建索引
CREATE INDEX idx_frequently_queried ON table_name (frequently_queried_column);
-- 为外键列创建索引
CREATE INDEX idx_foreign_key ON child_table (parent_id);
-- 为WHERE子句中的列创建索引
CREATE INDEX idx_where_condition ON table_name (column_used_in_where);
-- 为ORDER BY和GROUP BY的列创建索引
CREATE INDEX idx_order_group ON table_name (column_used_for_ordering);</pre></div>
<p class="maodian"><a name="_lab2_5_7"></a></p><h3>6.2 注意事项</h3>
<ul><li><strong>不要过度索引</strong>：每个索引都会增加写操作的开销</li><li><strong>选择合适的前缀长度</strong>：对于长文本列</li></ul>
<div class="jb51code"><pre class="brush:sql;">CREATE INDEX idx_name ON table_name (column_name(10)); -- 前缀索引</pre></div>
<ul><li><strong>考虑索引选择性</strong>：高选择性的列更适合建索引</li><li><strong>定期维护索引</strong>：使用 <code>ANALYZE TABLE</code> 更新索引统计信息</li></ul>
<p class="maodian"><a name="_lab2_5_8"></a></p><h3>6.3 性能监控</h3>
<div class="jb51code"><pre class="brush:sql;">-- 检查索引使用情况
EXPLAIN SELECT * FROM users WHERE username = 'john';
-- 查看未使用的索引（需要开启性能模式）
SELECT * FROM sys.schema_unused_indexes;</pre></div>
<p>通过合理使用索引，可以显著提高 MySQL 数据库的查询性能，但需要根据具体的查询模式和数据特征来设计和维护索引。</p>
頁:
[1]