MySQL索引语法+使用场景

149次阅读

共计 1851 个字符，预计需要花费 5 分钟才能阅读完成。

MySQL 索引语法

建表时添加索引

建表同时建立单索引

CREATE TABLE t_user1(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX (userName) #关键字INDEX
);

建表同时建立唯一索引（可以是单或多）

CREATE TABLE t_user2(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
UNIQUE INDEX index_userName(userName) #关键字 UNIQUE 和INDEX
);

建表同时建立联合索引

CREATE TABLE t_user3(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX index_userName_password(userName,PASSWORD)
);

给已存在表添加索引

单列索引
CREATE INDEX index_userName ON t_user(userName);
唯一索引
CREATE UNIQUE INDEX index_userName ON t_user(userName);
联合索引
CREATE INDEX index_userName_password ON t_user(userName,PASSWORD);

另一种写法与上面相似 ? 但是有区别

单列索引
ALTER TABLE t_user ADD INDEX index_userName(userName);
唯一索引
ALTER TABLE t_user ADD UNIQUE INDEX index_userName(userName);
联合索引

两种区别：
1、CREATE INDEX 必须提供索引名，对于 ALTER TABLE，将会自动创建，如果你不提供；
2、CREATE INDEX 一个语句一次只能建立一个索引，ALTER TABLE 可以在一个语句建立多个，如：
ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
3、只有 ALTER TABLE 才能创建主键，ADD INDEX 不能；

ALTER TABLE t_user ADD INDEX index_userName_password(userName,PASSWORD);

删除索引

DROP INDEX index_userName ON t_user;
DROP INDEX index_userName_password ON t_user;

各种索引的合理使用

首先要确定优化的目标，在什么样的业务场景下，表的大小等等。如果表比较小的话，可能都不需要加索引。
哪些字段可以建索引？一般都 where、order by 或者 group by 后面的字段。
记录修改的时候需要维护索引，所以会有开销，要衡量建索引之后的得与失（空间 + 维护换时间）。
比如学生表，可以认为 name 的重复度比较小，而 age 的重复度比较大，对于单列索引来说，比较适合建在重读度低的列上。
对于 select from students where name=' 张三’and age=18; 该中情况下：
A. name 和 age 各自单独建立索引:
一般来说 mysql 会选择其中一个索引，name 的可能性比较大，因为 mysq 会统计每个索引上的重复度，选用低重复度的字段。所以不使用 age，否则增加太多成本。
B. name 和 age 的联合索引:
这种索引的切合度最好。但是相对单索引来说，维护的成本大, 索引数据占用的存储空间也要更大。
可是！有必要使用联合索引吗？一般没必要：学校有 10000 个学生，叫谢春花的会超过 5 个吗？5 个找一个比建立联合索引花销小的多。
什么情况下使用联合索引比较好呢？举一个例子，大学修课，需要创建一个关系对应表，有 2 个字段,student_id 和 teacher_id，想要查询某个老师和某个学生是否存在师生关系。
一个学生会选 50 老师，一个老师会带 200 个学生
如果只为 student_id 建立索引的情况下，经过索引会选出 50 条记录，然后在内存中 where 一下，去除其余的老师。
相反如果只为 teacher_id 建立索引，经过索引会选出 200 条记录，然后在内存中 where 一下，去除其余的学生。
两种情况都不是最优的，因为使用索引后范围依然很大，这个时候使用联合索引最合适，通过索引直接找到对应记录，差不多提高了一倍效率。