如何创建索引
在创建表的时候创建索引:
其中关键字 INDEX
可以用 KEY
替代,INDEX
前面可以加上索引的类型 UNIQUE | FULLTEXT | SPATIAL | NORMAL
,默认是 NORMAL
CREATE TABLE `student` (
`id` bigint NOT NULL,
`name` varchar(30),
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`) -- 对 name 字段加上名为 idx_name 的索引
)
对已经存在的表添加索引:
ALTER TABLE student ADD INDEX idx_name (`name`)
创建索引的注意事项
选择合适的字段创建索引
并不是每个字段都适合创建索引,索引的创建也是要消耗磁盘空间的,并不是说索引就越多越好
1. 尽量选择不为 null 的字段建立索引
对于 Mysql 来说,含有空值的列很难进行查询优化,如果频繁查询的值又避免不了为 null,可以考虑使用 0,1 或者空字符这些短字符串进行替代。如果索引的字段可以为 NULL,索引的效率会下降很多,因为它们使得索引、索引的统计信息以及比较运算更加复杂
2. 选择区分度大列作为索引
举个例子,一张学生表里面有个字段是性别 sex
, 它里面存的是男和女,各占约百分之五十,那这样即便你建立了索引,用哪个值都是搜出一半来,那这样还不如不建索引
MySQL有一个查询优化器,查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描
惯用的百分比界线是"30%"。(匹配的数据量超过一定限制的时候查询器会放弃使用索引(这也是索引失效的场景之一哦)
3. 被频繁查询字段、作为查询条件
以上字段都可以考虑设计上索引
例:对于学生表(student
)和成绩表(grade
),假设有这么一条语句被频繁执行:
-- 下班准备去幸福小区,爆破成绩不及格的学生
select name, age from student a
left join grade b on a.id = b.student_id
where a.address like '%幸福小区%' and b.grade < 60
对于 student
表,可以建立索引的字段有 name、age、address、birthday
,对于 grade
表,在 student_id
建立索引,可以大大提高连表查询的速度
4. 被用于 group by
,order by
的字段
将 order by
后面的字段建立索引,这样在查询的时候就不需要再去做一次排序了,因为我们都已经知道了建立索引之后在B+树中的记录都是排序好的
group by 和 order by
其实是类似,所以将这两个放在一起说了
因为在group by
的时候也要先根据 group by
后面的字段排序,然后在执行聚合操作
如果 group by
后面的字段没有排序,那么这个时候MySQL是需要先进行排序的,这样就会产生临时表,一个排好序的临时表,然后再在临时表中执行聚合操作,这样子当然效率很低了,如果 group by
后面的字段已经建立了索引,那么MySQL
就不需要再去排序,也就不会产生临时表
5. 频繁更新的字段创建索引要慎重
SQL优化中,合理的建立索引算是基本操作了。虽然索引能带来查询上的效率提升,但是索引的成本也是不小的,索引会占用磁盘空间,并且降低添加、删除、和更新的速度。如果一个字段不被经常查询,但是又经常被更新,那么便不应该在这个字段上加索引
创建索引的其他建议
1. 尽量创建联合索引而不是单列索引
因为索引是需要占用磁盘空间的,可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多,索引过多,那么当这个表的数据达到一个体量后,索引占用的空间也是很多的,且修改索引时,耗费的时间也是较多的。如果是联合索引,多个字段在一个索引上,那么将会节约很大磁盘空间,且修改数据的操作效率也会提升
2. 避免冗余索引
冗余索引指的是索引的功能相同,能够命中索引(a, b)就肯定能命中索引(a) ,那么索引(a)就是冗余索引。如(name,city )和(name )这两个索引就是冗余索引,能够命中前者的查询肯定是能够命中后者的 在大多数情况下,都应该尽量扩展已有的索引而不是创建新索引
3. 考虑在字符串类型的字段上使用前缀索引代替普通索引
前缀索引仅限于字符串类型,较普通索引会占用更小的空间,所以可以考虑使用前缀索引带替普通索引
使用索引的注意事项
索引并不是说创建好之后就可不管,良好的索引需要开发人员的长期维护
首先索引对于中到大型表索引都是非常有效的,但是特大型表的话维护开销会很大,不适合建索引,对于小型表来说,查询全部数据的时间可能比遍历索引的时间还要短,不需要创建索引
其次需要删除长期未使用的索引也是有必要的,不使用的索引不仅占用着磁盘空间,而且会造成不必要的性能损耗,Mysql5.7 后可以通过查询 sys.schema_unused_indexes
表来获取长期未使用的索引
会使索引失效的几种情况
1. 在查询条件中对索引列使用函数或进行运算
如果是已经建立好的索引的字段在使用的时候执行了函数操作,那么这个索引就使用不到了
因为MySQL
为该索引维护的B+树就是基于该字段原始数据的,如果正在使用过程中加了函数,MySQL
就不会认为这个是原来的字段,那肯定不会走索引了
如果非要让函数走索引,可以在创建索引的时候把函数带上
例:
SELECT * FROM student WHERE round(age) = 2;
上面语句会使索引 idx_age
失效,如果非要让这条语句走索引,可以这样创建:
create index idx_age_round on student(round(age));
其次,在 where
中对索引列进行了运算,也会使索引失效
2. 在 like
条件中使用 % 开头
select * from student where name like '%bc%'
就上面的语句来说,索引是不生效的,想要索引生效,需要把前缀的 %
去掉
3. 使用了 or
条件
如果想要让 or
条件走索引,需要把 or
条件中的所有字段加上索引
4. 最左匹 配原则
最左匹配原则就是指在联合索引中,如果你的 sql 语句中用到了联合索引中的最左边的索引,那么这条 sql 语句就可以利用这个联合索引去进行匹配,当遇到范围查询(>、<、between、like)就会停止匹配