MySql的不同存储引擎有着不同的实现方式,本文主要是以innodb引擎来实现。innodb引擎的索引主要是以B+tree树来实现。
索引是一种以空间换时间的方式,对于innodb来说,创建索引,会使原本存储表数据的文件增大。
创建索引一般都是为了进行查询,提高查询效率,所以一般都是配合select语句来使用的。
一、索引失效的情况
MySql的优化一般都是根据索引,然后进行优化,而使用索引,要遵守以下条件,否则索引将会失效。
最左前缀法则(要注意的是,最左前缀法则在where后面是没有顺序要求,而在order by有顺序要求,因为在order by中,字段在前面和在后面是有区别的,比如,order by math,english和order by english,math 这是两种不同的排序方式)
使用>或<
字符串不加引号
or条件一方无索引
模糊查询%在前面
表中过滤后,数据仍然很多时。
二、覆盖索引
我们经常能听到别人说,写查询语句的时候,最好不要使用select *。为什么会这么说呢?
其实这跟索引的实现方式有关。索引是以B+tree的形式来实现的,而B+Tree的叶子结点是以链表的形式来进行连接。
假设此时我们有一张student表(主键为id)
![](https://i-blog.csdnimg.cn/blog_migrate/695653470227fc6dcc89752634574db9.png)
聚集索引(主键索引)的B+Tree的叶子结点中存放着id(主键)以及一行的数据,如图:
![](https://i-blog.csdnimg.cn/blog_migrate/9c580a5d10ede527fe28234778f37fc3.png)
而正常索引中,叶子结点中存放着对应数据的主键。假设,我们创建一个name字段的索引,name索引的B+Tree的叶子结点的存放着name的信息,以及对应的主键,如图:
![](https://i-blog.csdnimg.cn/blog_migrate/e6175ac5029dabd055410011d2e33e34.png)
我们执行一条select语句,如:select * from student where name = "王五";因为我们为name创建了索引,并且索引也不符合以上六种索引失效的情况,所以name索引是生效的。
通过name索引,我们找到了王五以及它对应的3号主键,但是我们使用的是select *,查询所有的字段,但我们只查到了name,所以我们要根据3号主键回去聚集索引中查找,然后在聚集索引中找到3号的所有数据,并且输出。而回去聚集索引这个表的过程叫做回表查询。
可以看到我们这时是有两个操作的,一个是在name索引查询,一个是在聚集索引查询,这就降低了效率,所以才会说不推荐使用select *
因此,覆盖索引的意思是,select后面的字段,尽量要把我们创建的索引字覆盖,也可以说是尽量要和索引的字段一致。
不过,如果我们执行的语句是select * from student where id = 1;就不一样,因为走的是聚集索引,此时会直接找到一行数据,所以此时的select * 也不会降低效率。
所以,我们使用的是聚集索引,那么使用*也不会有性能降低。但是,如果我们使用的不是聚集索引,那么select后面的字段,要尽量覆盖索引的字段。