数据库索引失效秘诀（应试必备）

最新推荐文章于 2024-07-21 21:22:59 发布

wangyuan9826

最新推荐文章于 2024-07-21 21:22:59 发布

阅读量554

点赞数

分类专栏： drainage 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/wangyuan9826/article/details/121625226

版权

drainage 专栏收录该内容

4 篇文章 8 订阅

订阅专栏

谈到数据库索引，我们大家都不陌生，但如何的高效，快速的创建符合自己需要的索引呐，这也是非常关键的。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。在平常的面试中也是必面内容。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

首先我们来回顾一下创建/删除索引的方式，通常有以下几种方式供我们使用。

方式一：直接创建
CREATE INDEX indexName ON table_name (column_name)
注意：如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
方式二：创建唯一索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
方式三：修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)


查看索引
SHOW INDEX FROM table_name\G

删除索引
DROP INDEX [indexName] ON mytable;

以上是简单常用的几种创建/查看/修改/删除索引的方式。

那么如何高效的使用索引，避免索引失效呐，下面就是我要给大家说的重点内容。

首先，我么大家先谨记一个口诀：“模型数空运最快”，接下来一一为大家解读每一个关键字的来龙去脉。

一： “模”：模糊查询以%开头，索引失效。例如：

SELECT * FROM test WHERE name LIKE '%模';

二： “型”：数据类型。类型不匹配，如字段类型为int，where条件用varchar，索引失效。例如：

SELECT * FROM test WHERE age= '25';

三： “数”：函数。对索引的字段使用内部函数，索引失效。这种情况下应该建立基于函数的索引。例如：

SELECT * FROM test WHERE DATE(birthday) = '2021-11-30';

因MySQL5.7不支持函数索引，所以需要修改SQL写法来实现走索引，例如：

select * from test where birthday>='2021-11-30' and birthday<date_add('2021-11-30',INTERVAL 1 day);

MySQL8.0的索引特性增加了函数索引。其实MySQL5.7中推出了虚拟列的功能，而MySQL8.0的函数索引也是依据虚拟列来实现的。例如：

alter  table test add key idx_birthday((date(birthday))); --   注意里面字段的括号

四： “空”：是NULL的意思。索引不存储空值，如果不设置索引列是not null，数据库会认为索引列有可能存在空值，索引失效。例如：

索引失效
SELECT * FROM test WHERE name IS NULL
索引生效
SELECT * FROM test WHERE name IS NOT NULL

五：“运”：运算。对索引列进行（+，-，*，/，!, ！=, <>，or，not）等运算，索引失效。例如：

SELECT * FROM test WHERE age - 1 = 22;

六：“最”：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，索引失效。

七：“快”：全表扫描更快。如果数据库预计使用全表扫描要比使用索引快，则不使用索引。注：查询结果的记录数量小于表中记录一定比例的时候。这个主要是由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B 的话索引就没有优势了。对于大多数数据库来说，这个比例是10%（oracle，postgresql等），即先对结果数量估算，如果小于这个比例用索引，大于的话即直接表扫描。