likes
comments
collection
share

MySQL 索引为什么失效?什么情况使用索引?

作者站长头像
站长
· 阅读数 36

前言

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 byorder by 的字段上加索引,提高分组,排序的速度。
  • 这一条也是众所周知的,就是在 where 后面的条件上加索引,当然是在常用的条件下加,索引是有开销的,不能乱加。

哪些列不适合创建索引

  • 查询很少的列,不怎么根据这个列查询的话,极度不推荐加索引。
  • 对于那些重复值比较多,或者大多没有值的列,也推荐加,重复值比较多的话,并不能明显提升查询速度,反而会增加开销。
  • 对于那些定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大,要么取值很少。

为什么这些列不推荐加索引,如果每个列全加上索引,查询不会很快嘛

查询快,但是增、删、改的效率,就回被严重影响,因为当增加删除修改的时候,是需要维护索引的,除了这一点,索引还会占用一定的物理空间

索引失效

  1. 索引不能存储null值
  • 查询的时候使用is null不能使用索引,会全表扫描
  • 查询的时候使用not exists不能使用索引,会全表扫描
  • 查询的时候使用not in不能使用索引,会全表扫描 总结:not 有关都不会走索引,会扫描全表 为什么不能使用null呢? 因为索引是有序的,null值放在索引的哪里这就是一个问题了,当然有解决方案,可以直接写空串,或者用一个特定的值替代null值
  1. 不适合重复数据较多的列

这个简单解释下,现在有个用户表,有一万条数据,然后我们在性别字段上加个索引,性别只有 男 or 女,然后这张表的所有性别数据都是女,当查询的时候,数据库中会首先在索引中查询一遍,然后去表中查询,因为都是女,所以就全表扫描了一遍。 如果不加索引的话,那么只会全表扫描一下,效率肯定是优于上面这种情况的。

  1. 通过前缀模糊查询 like '%XX'或者like '%XX%'

这个就比较好理解了,如果前缀是模糊的,肯定需要一个一个看,然后才能找到正确的,这种情况就会造成全表扫描

  1. 如果MySQL预计全表更快的话,也不会使用索引
  2. 如果条件中有 or ,只要 or 条件中有一个条件没有创建索引,也不会使用索引(因为没有索引的那个列按理说是要进行全表扫描的),如果想用 or,又想让索引生效,那就在条件上都加上索引。
  3. 索引列使用了函数也会使索引失效
  4. 索引参与计算也会使索引失效
  5. 类型隐式转换

假如我们在数据库存储的类型是varchar,但是我们执行查询语句的时候,直接写select name from user where id=123,注意这个id=123,123没有被 '' 包起来,所以是int类型,在这种情况下,也会使索引失效

  1. 两列做比较也会使索引失效

比如表中有存储 数学成绩语文成绩,我们要查询 数学成绩 优于 语文成绩的学生,这种情况索引也不会生效,即使两个字段都有索引。

  1. 不等于比较 != <>

有可能不走索引,但也不全是,一般来说当查询结果集占比比较小时,会走索引,占比比较大时不会走索引。此处与结果集与总体的占比有关。使用 不等 操作的时候要慎重。

  1. order by导致索引失效,排序是会扫描全表排序呀

本篇就讲到这里了,索引知识点还有一部分没有讲到,下篇继续

MySQL 索引为什么失效?什么情况使用索引?

转载自:https://juejin.cn/post/7116886082297266190
评论
请登录