likes
comments
collection
share

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

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

前言

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数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。

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

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

索引失效

  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 索引为什么失效？什么情况使用索引？