【MySQL数据库】索引简介与索引失效的5大情况

目录

前言

索引的概念与功能

索引的语法

使用索引与不使用索引的对比

索引的优缺点

索引失效的n大情况

小结

---

前言

在如今这个信息大爆炸的时代，每天都会产生很多的数据，我们在网上浏览到的信息、发布的信息、下载的信息，这些信息加起来可能有上千万行的数据。这时候如果没有一个良好的仓库开管理和存储这些信息的话，那是非常可怕的。数据库就是这样一个仓库，我们平时在网上浏览到的网页，看到的视频都是存储在数据库当中的，所以对于一个程序员来说，熟练掌握数据库的操作和原理是非常重要的。

今天我就给大家总结了一些与MySQL数据库中索引相关的知识点。

索引的概念与功能

索引的概念：索引是为了提高查询效率而存在的一种机制，就像是一本书的目录。

索引的功能：索引能够缩小扫描范围以提高查询效率，减少系统开销。

索引的语法

创建索引：create index 索引名 on 表名(字段名);

删除索引：drop index 索引名 on 表名;

使用索引与不使用索引的对比

比如所我们现在有一张表：

      

 

我们来看看不使用索引时查询一共查询了多少行数据：

这里的explain这个命令表示解释这条SQL语句的相关信息，

这里显示一共查询了11行数据（rows = 11），扫描类型是全表扫描（type = ALL），

我们再来看看使用索引时查询一共查询了多少行数据：

我们先来创建一个索引：

     

 

再来查询一次：

 

这里显示只查询了1行数据（rows = 1），扫描类型是索引扫描（type = ref），

从这显然可以看出，使用索引的查询效率要比不使用索引查询的效率高很多，

索引的优缺点

索引的优点：

1）索引能够很好地提高系统性能，提高用户的查询效率。

2）因为索引有唯一性，可以通过索引来保证数据库中每一行数据的唯一性。

3）因为索引能够提高查询效率，所以索引能够提高表与表之间连接的速度。

当然了，所有的事物都不是十全十美的，索引虽然有很多的好处，但还是有其缺点和局限性的。索引的缺点可以总结为以下3点：

索引的缺点：

       1）因为索引在MySQL主句库当中是以对象的形式存储的，所以索引必定要占用一定的磁盘空间。

       2）当数据量非常庞大时，索引的创建和维护也是要耗费非常大的时间的。

       3）每次执行CRU操作时，索引都要动态维护，降低了数据维护的速度。

索引失效的5大情况

第1种情况：

模糊匹配当中以“%”开头：

这样子即使name字段已经添加了索引也不会使用索引匹配，因为‘%“表示匹配任意多个字符，这与索引的定位功能相矛盾。

第2种情况：

使用or的时候：

要想在使用or的时候是索引生效，那就必须要是or两边的字段都有索引，只要有其中一个字段没有索引，索引就都会失效。所以这就是为什么在查询的时候不建议使用or的原因。

第3种情况：

使用复合索引的时候，没有使用左侧的列查找，

复合索引就是两个字段或者更多的字段联合起来添加一个索引。

比如我们来创建一个复合索引：

语法格式：create index 索引名 on 表名(字段名1，字段名2，字段名3……);

我们先来使用左侧的列来查找：

可以看出，这里是使用了索引的

我们再来看看不使用左侧的列的情况：

 

显然这里没有使用索引。

第4种情况：

在where当中索引列参加了运算，

我们先来给年龄这个字段单独添加一个索引：

我们再来查询一次：

显然，这样一来就不会使用索引了。

第5中情况：

在where当中索引列使用了函数：

 

显然这里没有使用索引。

其实索引失效的情况还有很多，但是要一一列举的话就太冗长了，因为篇幅原因这里就只列举其中最常见的5中，其他的情况这里就不再一一列举了。

小结

-索引能够提高用户的查询效率

-索引的优点是：提高用户的查询效率、可以保证每行数据的唯一性、提高表的连接速度

-索引的缺点是：索引的存储会占用磁盘空间、索引的创建和维护要消耗时间、会降低数据维护的速度

-索引失效的5大情况：

1、模糊匹配当中以“%”开头

2、使用or的时候

3、使用复合索引的时候，没有使用左侧的列查找

4、在where当中索引列参加了运算

5、在where当中索引列使用了函数

好啦，今天的分享就到这里了，我今后还会继续分享更多与数据库有关的知识啦，希望大家能多多支持！