MySQL 索引为什么失效?什么情况使用索引?
前言
MySql 保证查询速度最重要的是什么,那当然是索引了,昨天本人对此算是有了一个清晰的认识。 事情是这样的,前两天刚搞了一个配置双数据源,postgresql 和 mysql。 postgre是其他公司的,然后需求是当postgre数据库连接失败的时候,连接本地数据库,然后昨天这个就出问题了,MySQL执行那是异常的慢,同样的sql,postgre用200毫秒,mysql直接干到了10秒,当时我是震惊的,一度对mysql产生了怀疑 !!!∑(゚Д゚ノ)ノ,后来仔细一看没加索引ヽ(ー_ー)ノ。因为刚开始图省事,直接把人家的建表语句直接复制过来了,也没改动(这种行为大家一定要引以为戒哈),加完索引直接快了几十倍,超越postgre,然后痛定思痛,决定还是要好好复习下,怎么能犯这么低端的错误嘞!
什么是索引
就像是一本书的目录,有了目录,你就不需要,一页一页去查找字节想要查的内容了,而是通过目录快速的定位到你要查询的数据,就是这么简单 (✪ω✪)
创建索引
使用 CREATE INDEX 创建索引
CREATE INDEX 为已有的表创建索引,但是用这个不能创建主键索引
CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
<索引名>
:指定索引名。一个表可以创建多个索引,但每个索引在该表中的名称是唯一的。<表名>
:指定要创建索引的表名。<列名>
:指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。<长度>
:可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小,节省索引列所占的空间。在某些情况下,只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节(MyISAM 和 InnoDB 表的最大上限为 1000 个字节),如果索引列的长度超过了这个上限,就只能用列的前缀进行索引。另外,BLOB 或 TEXT 类型的列也必须使用前缀索引。ASC|DESC
:可选项。ASC
指定索引按照升序来排列,DESC
指定索引按照降序来排列,默认为ASC
。
使用 ALTER TABLE 创建索引
这个和 CREATE INDEX 差不多,但是这个可以创建所有索引哦,CREATE INDEX 是创建索引,ALTER TABLE 是修改表结构的同时,来创建索引,大家品一品。。创建索引可能会有限制,但是修改结构肯定是没有滴
ALTER TABLE TABLE_NAME //拼接下面的这些 ADD INDEX [<索引名>] [<索引类型>] (<列名>,…) # 添加一个普通索引 ADD PRIMARY KEY [<索引类型>] (<列名>,…) # 添加一个主键索引 ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…) # 添加一个唯一索引 ADD FOREIGN KEY [<索引名>] (<列名>,…) # 添加一个外键索引
使用 CREATE TABLE 创建索引
在创建数据库表的时候,就创建好索引,如果出现后期频繁修改索引这种情况,一般就是设计时,没有设计好,大家在设计时一定要考虑周全一些
CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…) # 创建新表的同时创建该表的主键 KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…) # 创建新表的同时创建该表的索引 UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…) # 创建新表的同时创建该表的唯一性索引 FOREIGN KEY <索引名> <列名> # 创建新表的同时创建该表的外键
索引的类型
MySQL索引的类型不多,常用的更加滴少,像俺工作几年,大部分用的都是普通索引(除了主键哈),其他的很少用。不知道,大家最常用的是什么索引呢,欢迎留言 (^_−)☆
主键索引 Primary
一张表只能有一个主键索引,不允许重复、不允许为 NULL;
普通索引 Normal
这是 mysql 中最基本的索引了,没有任何限制,如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
唯一索引 Unique
与普通索引类似,但是它会强制限制列的值必须唯一,允许有null值
全文索引 Full Text
一般用在需要全文检索的列之上,检索长文本效果最好,短文本的话还是用普通索引比较好,
哪些列适合创建索引
- 在经常被搜索的列上,可以极大提高搜索速度
- 在经常用作(JOIN)连接的列上(昨天就是碰到的这个问题,多表连接的字段都没加索引,导致查询巨慢)
- 在经常(>,<,=,in等等范围查询)中使用索引,因为索引会进行排序,指定范围是连续的,可以极大提升查询速度。
- 在经常 group by 和 order by 的字段上加索引,提高分组,排序的速度。
- 这一条也是众所周知的,就是在 where 后面的条件上加索引,当然是在常用的条件下加,索引是有开销的,不能乱加。
哪些列不适合创建索引
- 查询很少的列,不怎么根据这个列查询的话,极度不推荐加索引。
- 对于那些重复值比较多,或者大多没有值的列,也推荐加,重复值比较多的话,并不能明显提升查询速度,反而会增加开销。
- 对于那些定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大,要么取值很少。
为什么这些列不推荐加索引,如果每个列全加上索引,查询不会很快嘛
查询快,但是增、删、改的效率,就回被严重影响,因为当增加删除修改的时候,是需要维护索引的,除了这一点,索引还会占用一定的物理空间
索引失效
- 索引不能存储null值
- 查询的时候使用is null不能使用索引,会全表扫描
- 查询的时候使用not exists不能使用索引,会全表扫描
- 查询的时候使用not in不能使用索引,会全表扫描 总结:not 有关都不会走索引,会扫描全表 为什么不能使用null呢? 因为索引是有序的,null值放在索引的哪里这就是一个问题了,当然有解决方案,可以直接写空串,或者用一个特定的值替代null值
- 不适合重复数据较多的列
这个简单解释下,现在有个用户表,有一万条数据,然后我们在性别字段上加个索引,性别只有 男 or 女,然后这张表的所有性别数据都是女,当查询的时候,数据库中会首先在索引中查询一遍,然后去表中查询,因为都是女,所以就全表扫描了一遍。 如果不加索引的话,那么只会全表扫描一下,效率肯定是优于上面这种情况的。
- 通过前缀模糊查询 like '%XX'或者like '%XX%'
这个就比较好理解了,如果前缀是模糊的,肯定需要一个一个看,然后才能找到正确的,这种情况就会造成全表扫描
- 如果MySQL预计全表更快的话,也不会使用索引
- 如果条件中有 or ,只要 or 条件中有一个条件没有创建索引,也不会使用索引(因为没有索引的那个列按理说是要进行全表扫描的),如果想用 or,又想让索引生效,那就在条件上都加上索引。
- 索引列使用了函数也会使索引失效
- 索引参与计算也会使索引失效
- 类型隐式转换
假如我们在数据库存储的类型是varchar,但是我们执行查询语句的时候,直接写select name from user where id=123,注意这个id=123,123没有被 '' 包起来,所以是int类型,在这种情况下,也会使索引失效
- 两列做比较也会使索引失效
比如表中有存储 数学成绩 和 语文成绩,我们要查询 数学成绩 优于 语文成绩的学生,这种情况索引也不会生效,即使两个字段都有索引。
- 不等于比较 != <>
有可能不走索引,但也不全是,一般来说当查询结果集占比比较小时,会走索引,占比比较大时不会走索引。此处与结果集与总体的占比有关。使用 不等 操作的时候要慎重。
- order by导致索引失效,排序是会扫描全表排序呀
本篇就讲到这里了,索引知识点还有一部分没有讲到,下篇继续
转载自:https://juejin.cn/post/7116886082297266190