【数据库-索引】正确使用索引实验及查询性能分析

摘要：随着数据库的数据量逐渐增大，查询数据的性能会越来越差。此时需要针对查询频繁的表建立索引，索引是一种高效获取数据的数据结构，但是索引也不是越多越好，索引越多，维护索引结构的代码就越大，会影响增删改查的效率。因此，本文主要研究索引在几种场景下的正确使用，并通过几种性能分析的方式，分析其查询性能，结果说明，使用正确使用索引的情况下，查询性能有大幅度的提高。

1、索引的概念与分类

2、索引的结构及原理

3、索引的性能分析

4、索引的设计原则及使用场景

设计原则1：针对数据量较大，且查询比较频繁的表才建立索引。

设计原则2：针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

• 使用场景1：查询字段为单列字段（针对1000w数据的表tb_bigdata）

select name from tb_bigdata where name='张先生1'; 

不使用索引：5.462秒

根据name建立普通索引

create index idx_name on tb_bigdata(name);

数据量大时建立索引的时间也比较长：24秒

使用索引下的查询结果：

设计原则3：尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

查询索引：

show index from tb_bigdata;

id是tb_bigdata表的主键索引，建表时默认建立主键索引，可见主键索引的效率比普通索引的高。

• 使用场景2：查询字段为多列字段

首先删除刚才建立的单列索引：

drop index idx_name on tb_bigdata;

查看场景2的查询性能：

select * from tb_bigdata where birth =1981 and name='张先生71';

不使用索引：

设计原则4：如果存在多个查询条件，尽量使用联合索引，减少单列索引，因为联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

覆盖索引：查询使用了索引，并且需要返回的列，在该索引中全部都能找到。尽量使用覆盖索引，减少select *。

上面sql优化成：

select id,birth,name from tb_bigdata where birth =1981 and name='张先生71';

单列索引与联合索引对比：

• 对于多个查询字段的索引（name，birth），如果只有单列索引，那么mysql最终也只能走其中一个，此时是需要回表查询的。

使用单列索引：idx_name

结果：8ms

• 如果查询使用的是联合索引，具体的结构如下，再加上使用覆盖索引，避免回表查询。
  使用联合索引：idx_birth_name：

create index idx_birth_name on tb_bigdata(birth,name);

设计原则5：最左前缀法则：如果使用了联合索引，查询sql中的条件必须存在索引最左边的列（顺序无关），否则索引全部失效；而且中间不能跳过索引中的某一列，否则该列后面的字段将失效。

对于索引：idx_birth_name_sex;

explain select * from tb_bigdata where birth =1981 and name='张先生71' and sex='男';

explain select * from tb_bigdata where birth =1981 and name='张先生71';

explain select * from tb_bigdata where birth =1981;

这三组结果说明，由于查询sql中都包含索引的第一列（birth），因此索引都会生效，但是索引长度不同，可推断出birth索引长度为5，name索引长度为152，sex索引长度为152。

explain select * from tb_bigdata where name='张先生71' and sex='男';

查询条件不包含索引最左列，索引失效

查询条件中包含索引最左列，但是中间跳过了索引的某一列（name），从索引长度可看出name以及后面索引字段将失效。

explain select * from tb_bigdata where birth =1981 and sex='男';

联合索引的原理：

5、索引失效场景