MySQL为数据表建立索引的原则

时间:2022-05-23 小小茶花女人气:0

面试题：

索引是什么？
索引的优点？
索引的缺点？
在建立索引的时候都有哪些需要考虑的因素呢?
为数据表建立索引的原则有哪些？
什么是索引覆盖？
非聚簇索引一定会回表查询吗?

1. 索引是什么？

索引是帮助我们实现快速查询数据库的数据结构。
在很多时候，表中存放的记录非常的多，需要用到好多的数据页来存放这些记录，在很多页中查找记录可以分为2个步骤：

1、定位到记录所在的页

2、从所在页内查找相应的记录

在没有索引的情况下，无论是根据主键列还是其他列的值进行查找，由于我们不能快速的定位到记录所在的页，所以只能从第一页沿着双向链表一直往下找，因为需要遍历所有的数据页，这种方式超级消耗性能。因此人们期望一种能高效完成搜索的方法，因此就出现了索引，索引就是一种数据结构。

2. 索引的优点?

索引可以让服务器快速的定位到表的指定位置（索引大大减少了服务器需要需要扫描的数据量）。最常见的B-Tree索引，按照顺序存储数据，所以mysql可以来做order by和group by操作（索引可以帮助服务器避免排序和临时表）。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起（索引可以将随机IO变为顺序IO）。因为索引中存储了实际的列值，所以某些查询值使用索引就能完成全部查询。因此，总结下来的索引的优点为：

1、索引大大减少了服务器需要需要扫描的数据量；

2、索引可以帮助服务器避免排序和临时表；

3、索引可以将随机IO变为顺序IO;

3. 索引的缺点?

1、空间上的代价：

建立索引需要占用物理空间

InnoDB存储引擎默认使用的B+树索引，每建立一个索引，都要为它建立一颗B+树，每一颗B+树的每一个节点都是一个数据页，一个数据页默认会占用16KB的存储空间，而一颗很大的B+树由许多数据页组成，这将占用很大的一片存储空间。

2、时间上的代价：

因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长

每当对表中的数据进行增删改查操作时，都需要修改各个B+树索引，B+树中的每层节点都按照索引列的值从小到大的顺序组成了双向链表，无论是叶子节点中的记录还是非叶子节点中的记录都按照索引列的值从小到大的顺序排成了单向链表。而增删改查操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行页面分裂、页面回收等操作，以维护节点和记录的顺序。

在执行查询语句前，首先要生成一个执行计划，一般情况下，一条查询语句在执行过程中最多使用一个二级索引，因此，在生成执行计划时需要计算使用不同索引执行查询时所需的成本，最后选取成本最小的那个索引执行查询，如果建立了太多的索引，可能会导致成本分析耗时太多，从而影响查询语句的执行性能。

4. 在建立索引的时候,都有哪些需要考虑的因素呢?

在创建和使用索引是应该注意下列事项：

只为用于搜索、排序、分组的列创建索引；
当列中不重复值的个数在总记录条数中的占比很大时，才为列建立索引；
索引列的类型尽量小；
可以只为索引列前缀创建索引，以减少苏呕心占用的存储空间；
尽量使用覆盖索引进行查询，以毕淼回表操作带来的性能损耗；
让索引列以列名的形式单独出现在搜索条件中；
为了尽可能少的让聚簇索引发生页面分裂的情况，建议让主键拥有anto_increment属性;
定位并删除表中的冗余和重复索引；

建立一张数据库表：

create table single_table(
	id int not auto_increment, 
	key1 varchar(100),         
	key2 int,
	key3 varchar(100),
	key_part1 varchar(100),
	key_part2 varchar(100),
	key_part3 varchar(100),
    common_field varchar(100),
	primary key(id),          # 聚簇索引
	key idx_key1(key1),       # 二级索引
	unique key uk_key2(key2), # 二级索引，而且该索引是唯一二级索引
	key idx_key3(key3),       # 二级索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二级索引，也是联合索引
)Engine=InnoDB CHARSET=utf8;

1、只为用于搜索、排序、分组的列创建索引

我们只为出现在where子句中的列、order by或group by子句中的列、连接子句中的连接列创建索引。仅出现在查询列表中的列就没有必要建立索引了。

比如下面的查询语句：

select common_field,key_part3 from single_table where key1='a';

我们只需要为出现在where子句中的key1建立索引就可以了，而查询列表中的common_field、key_part3这两个列就没有必要建立索引了。

2、索引列的类型尽量小

在定义表结构时，要显式的指定列的类型。以整数类型为例，有tingint、mediumint、int、bigint这几种，他们占用的存储空间的大小依次递增，他们能表示的整数范围当然也是依次递增。如果想要对某个整数类型的列建立索引，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如使用int就不要用bigint，因为数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存放更多的记录，磁盘IO带来的性能损耗也就越小，读写效率也就越高。

这个建议对表的主键来说更加适用，因为不仅聚簇索引会存储主键值，所有的二级索引的节点都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着能节省更多的存储空间。

3、为列前缀建立索引

一个字符串其实是由若干个字符组成的，假如字符串很长，那么存储这个字符串就会占用很多的存储空间。在为这个字符串所在的列建立索引时，就需要在对应的B+树中，把列的完整字符串存储起来，字符串越长，在索引中占用的存储空间越大。

索引列的字符串前缀其实也是排好序的，所以索引的设计人员设计了一个方案，即只将字符串的前几个字符存放到索引中，也就是说二级索引的记录中只保留字符串的前几个字符。

比如可以这样修改idx_key1索引，让索引中只保留字符串的前10个字符：

alert table single_table drop index idx_key1;
alert table single_table add index idx_key1(key1(10));

然后再执行下面的查询语句：

select * from single_table where key1='abcdefghijklmn';

由于在idx_key1的二级索引记录中只保留字符串的前10个字符，所以我们只能定位到前缀为‘abcdefghij'的二级索引记录，在扫描这些二级索引记录时再判断它们是否满足key1='abcdefghijklmn'条件，当列中存储的字符串包含的字符较多时，这种为列前缀建立索引的方式可以明显减少索引大小。

不过，在只对前缀建立索引的情况下，下面这个查询语句就不能使用索引来完成排序需求了：

select * from single_table order by key1 limit 10;

因为二级索引idx_key1中不包含完整的key1列信息，所以在仅使用idx_key1索引执行查询时，无法对key1列前10个字符相同但其余字符不同的记录进行排序，也就是说，只为列前缀建立索引的方式无法支持使用索引进行排序的需求。

4、覆盖索引

为了彻底告别回表操作带来的性能损耗，建议最好在查询列表中只包含索引列，比如这个查询语句：

select key1,id from single_table where key1>'a' and key1<'c';

由于我们只查询key1列和id列的值，所以在使用idx_key1索引来扫描('a','c')区间中的二级索引时，可以直接从获取到的二级索引记录中读出key1列和id列的值，而不需要再通过id值到聚簇索引中执行回表操作了，这样就省去了回表操作带来的性能损耗。

我们把这种索引中已经包含了所有需要读取的列的查询方式称为覆盖索引。如果索引的叶子节点中已经包含要查询的数据，那么还有必要再回表查询呢？如果一个索引包含所有需要查询的字段的值，就称为覆盖索引。

排序操作也优先使用覆盖索引进行查询，比如下面这个查询语句：

select key1 from single_table order by key1;

虽然这个查询语句中没有limit子句，但是由于可以采用覆盖索引，所以查询优化器会直接使用idx_key1索引进行排序，而不需要执行回表操作。

当然，如果业务需要查询索引列以外的列，还是要以保证业务需求为重，如无必要，最好把业务中需要的列放在查询列表中，而不是以简单的*替代。

5、让索引列以列名的形式在搜索条件中单独出现

在下面这两个查询语句中，搜索条件中的语义是一样的：

select * from single_table where key2*2<4;
select * from single_table where key2<4/2;

在第一个查询语句的搜索条件中，key2列并不是以单独列名的形式出现的，而是以key2*2这样的形式表现的，MySQL并不会尝试简化key2*2<4表达式，而是直接认为这个搜索条件不能形成合适的扫描区间来减少需要扫描的记录数量，所以该查询语句只能以全表扫描的方式来执行。

在第二个查询语句的搜索条件中，key2列并是以单独列名的形式出现的，MySQL可以分析出key2<2，这样可以减少需要扫描的记录数量。所以MySQL可能使用uk_key2来执行查询。

所以，如果想让某个查询使用索引来执行，请让索引列以列名的形式单独出现在搜索条件中。

6、新插入记录时主键大小对效率的影响

我们知道，对于一个使用InnoDB存储引擎的表来说，在没有显式创建索引时，表中的数据实际上存储在聚簇索引的叶子节点中，而且B+树的每一层数据页以及页面中的记录都是按照主键值从小到大的顺序排序的，如果新插入记录的主键值是依次递增的话，每插满一个数据页就换到下一个数据页继续插入，如果新插入记录的主键值忽大忽小，就比较麻烦了。

假设某个数据页存储的聚簇索引记录已经满了，它存储的主键值在1~100之间，此时如果再插入一提哦啊主键值为9的记录，因为这个数据页已经满了，新纪录应该插入到哪里呢？

我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的一些页中，页分裂意味着性能损耗，所以如果想尽量避免这种无谓的性能损耗，最好让插入记录的主键值依次递增。即让主键拥有auto_increment属性，MySQl会自动为新插入的记录生成递增的主键值。

7、冗余和重复索引

针对single_table表，可以单独针对key_part1列建立一个idx_key_part1索引：

alert table single_table and index idx_key_part1(key_part1);

而此时我们已经有了一个针对key_part1、key_part2、key_part3列建立的联合索引idx_key_par。idx_key_part索引的二级索引记录本身就是按照key_part1列的值排序的，此时再单独为key_part1列建立一个索引其实是没必要的，我们可以把整个新建的idx_key_part1索引看作一个冗余索引，该冗余索引是没有必要的。

有时，我们可能会对同一个列建立多个索引，比如下面两个添加索引的语句：

alert table single_table add unique key uk_id(id);
alert table single_table add index idx_id(id);

我们针对id列又建立了一个唯一二级索引uk_id，还建立了一个普通二级索引idx_id，可是id列本身就是single_table表的主键，InnoDB自动为该列建立了聚簇索引，此时uk_id和idx_id就是重复的，这种重复索引应该避免。

总结

本篇文章就到这里了，希望能够给你带来帮助，也希望您能够多多关注的更多内容!

加载全部内容