当前位置：首页 > 问答 > 正文

MySQL里怎么弄索引啊，创建和删掉那些东西的事儿讲讲

你得知道索引是个啥,打个比方，一本书如果没有目录，你想找某个内容，就得一页一页地翻，非常慢，索引就像这本书的目录，它能帮你快速定位到你想找的数据，不用再把整张表的数据从头到尾扫一遍了，给合适的字段加上索引，能大大加快查询速度。

创建索引

创建索引有好几种方法,你可以根据习惯选一种。

第一种方法,最直接，就是用 CREATE INDEX 语句，这是最标准的创建方式，语法看起来是这样的：

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

比如说,你有一张叫 users 的表，里面有个 email 字段，你经常要根据邮箱地址来查用户信息，那你就可以给这个 email 字段建个索引：

CREATE INDEX idx_email ON users (email);

这里,idx_email 是你自己起的索引名字，最好起个能看出是哪个表哪个字段索引的名字，方便以后管理。ON users (email) 意思就是在 users 表的 email 字段上创建索引。

第二种方法,在创建表的时候，直接把索引也定义好，这通常在你自己设计新表的时候用。

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_username (username)
);

这段代码在创建 users 表的同时，就给 username 字段创建了一个名为 idx_username 的索引，注意，这里用的关键字就是 INDEX。

第三种方法,是通过修改表结构来添加索引，使用 ALTER TABLE 语句，语法是：

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

还是给 users 表的 email 加索引，可以这么写：

ALTER TABLE users ADD INDEX idx_email (email);

这种方法和 CREATE INDEX 效果差不多，用哪个都行。

除了上面这些普通索引,还有两种比较特殊的索引你肯定会碰到。

MySQL里怎么弄索引啊，创建和删掉那些东西的事儿讲讲

一个是主键索引，你设置为主键（PRIMARY KEY）的那个字段，MySQL会自动为它创建一个主键索引，这是最特殊的索引，它要求值必须唯一且不能为空，一张表只能有一个主键索引，通常在建表时这样设置：id INT PRIMARY KEY，索引就自动建好了。

另一个是唯一索引，它和主键索引类似，要求值必须是唯一的，但允许有空值，一张表可以有多个唯一索引，创建方法也和普通索引类似，只是把 INDEX 换成 UNIQUE INDEX，确保邮箱不重复：

CREATE UNIQUE INDEX unique_email ON users (email);

或者用修改表结构的方法：

ALTER TABLE users ADD UNIQUE INDEX unique_email (email);

删除索引

有创建就有删除,当你发现某个索引没用，或者要修改它时，就需要先删掉。

删除索引主要也用两种方法。

MySQL里怎么弄索引啊，创建和删掉那些东西的事儿讲讲

第一种,用 DROP INDEX 语句，语法是：

DROP INDEX 索引名字 ON 表名字;

要删除我们刚才在 users 表上创建的 idx_email 索引，就这么写：

DROP INDEX idx_email ON users;

第二种,还是通过修改表结构 ALTER TABLE 来删，不过这次是用 DROP 子句，写法是：

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

效果和上面完全一样：

ALTER TABLE users DROP INDEX idx_email;

需要注意的事儿

索引虽好,但不能乱用，主要有这么几点要注意：

索引不是免费的：索引就像书的目录，它本身也要占页码，数据库里的索引会占用额外的磁盘空间，当你对表里的数据进行增、删、改操作时，数据库不光要动数据，还得去更新对应的索引，所以会稍微拖慢写入数据的速度，这就是所谓的“以空间换时间”，用占更多空间和牺牲一点写入速度，来换取超高的查询速度。
在哪建索引？：你应该在那些经常出现在 WHERE 子句里的字段上建索引，你老是查 WHERE user_id = 100，那就在 user_id 上建索引，还有用来连接表的字段（外键），以及排序（ORDER BY）和分组（GROUP BY）的字段，建索引也很有用。
索引不一定总生效：有时候你明明建了索引，但查询还是很慢，这可能是因为你的查询语句写得让MySQL没法用索引，在字段上用了函数（WHERE LOWER(name) = 'abc'），或者使用了不等号，或者以通配符开头的模糊查询（LIKE '%abc'），这些都可能导致索引失效，变成全表扫描。
查看索引：你可以用这个命令看看一张表上都有哪些索引：SHOW INDEX FROM 表名字;，它会列出所有索引的名字、类型、关联的字段等信息，非常有用。