数据库中90%程序员面试会遇到的索引优化问题有哪些

共计 4151 个字符，预计需要花费 11 分钟才能阅读完成。

这篇文章主要介绍数据库中 90% 程序员面试会遇到的索引优化问题有哪些，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

关于索引，分为以下几点来讲解（技术文）：

索引的概述(什么是索引, 索引的优缺点)

索引的基本使用（创建索引）

索引的基本原理（面试重点）

索引的数据结构（B 树，hash）

创建索引的原则（重中之重，面试必问！敬请收藏！）

百万级别或以上的数据如何删除

一、索引的概述

1）什么是索引？

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，索引就相当于目录。当你在用新华字典时，帮你把目录撕掉了，你查询某个字开头的成语只能从第一页翻到第一千页。累！把目录还给你，则能快速定位！

2）索引的优缺点：

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。，且通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。但是，索引也是有缺点的：索引需要额外的维护成本；因为索引文件是单独存在的文件, 对数据的增加, 修改, 删除, 都会产生额外的对索引文件的操作, 这些操作需要消耗额外的 IO, 会降低增 / 改 / 删的执行效率。

二、索引的基本使用（真技术文）

1）创建索引：（三种方式）

第一种方式：

第二种方式：使用 ALTER TABLE 命令去增加索引：

ALTER TABLE 用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

其中 table_name 是要增加索引的表名，column_list 指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名 index_name 可自己命名，缺省时，MySQL 将根据第一个索引列赋一个名称。另外，ALTER TABLE 允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用 CREATE INDEX 命令创建

CREATE INDEX 可对表增加普通索引或 UNIQUE 索引。（但是，不能创建 PRIMARY KEY 索引）

三、索引的基本原理（不想像别的文章那样一大堆篇幅废话）

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

索引的原理很简单，就是把无序的数据变成有序的查询

  1、把创建了索引的列的内容进行排序

  2、对排序结果生成倒排表

  3、在倒排表内容上拼上数据地址链

  4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

四、索引的数据结构（b 树，hash）

1）B 树索引

mysql 通过存储引擎取数据，基本上 90% 的人用的就是 InnoDB 了，按照实现方式分，InnoDB 的索引类型目前只有两种：BTREE（B 树）索引和 HASH 索引。B 树索引是 Mysql 数据库中使用最频繁的索引类型，基本所有存储引擎都支持 BTree 索引。通常我们说的索引不出意外指的就是（B 树）索引（实际是用 B + 树实现的，因为在查看表索引时，mysql 一律打印 BTREE，所以简称为 B 树索引）

查询方式：

主键索引区:PI(关联保存的时数据的地址)按主键查询,

普通索引区:si(关联的 id 的地址, 然后再到达上面的地址)。所以按主键查询, 速度最快

B+tree 性质：

1.）n 棵子 tree 的节点包含 n 个关键字，不用来保存数据而是保存数据的索引。

2.）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.）所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

4.）B+ 树中，数据对象的插入和删除仅在叶节点上进行。

5.）B+ 树有 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点。

2）哈希索引（好技术文）

简要说下，类似于数据结构中简单实现的 HASH 表（散列表）一样，当我们在 mysql 中用哈希索引时，主要就是通过 Hash 算法（常见的 Hash 算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的 Hash 值，与这条数据的行指针一并存入 Hash 表的对应位置；如果发生 Hash 碰撞（两个不同关键字的 Hash 值相同），则在对应 Hash 键下以链表形式存储。当然这只是简略模拟图。

ps：关于数据结构，有兴趣深入的朋友可以关注我后查看【数据结构】专题，这里不做详细讲解。

五、创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1）最左前缀匹配原则，组合索引非常重要的原则，mysql 会一直向右匹配直到遇到范围查询 (、、between、like) 就停止匹配，比如 a = 1 and b = 2 and c 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为 text、image 和 bit 的数据类型的列不要建立索引。

百万级别或以上的数据如何删除（真好技术文）

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件, 所以当我们对数据的增加, 修改, 删除, 都会产生额外的对索引文件的操作, 这些操作需要消耗额外的 IO, 会降低增 / 改 / 删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询 MySQL 官方手册得知删除数据的速度和创建的索引数量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）

然后删除其中无用数据（此过程需要不到两分钟）

删除完成后重新创建索引 (此时数据较少了) 创建索引也非常快，约十分钟左右。

与之前的直接删除绝对是要快速很多，更别说万一删除中断, 一切删除会回滚。那更是坑了。

常用的数据库索引优化语句

使用如下的表 tb_test 作为示例进行说明：

create table tb_test
id int not null,
age int not null, 
name varchar(30) not null,
addr varchar(50) not null
create unique index idx1_tb_test on tb_test(id);
create index idx2_tb_test on tb_test(name);
create index idx3_tb_test on tb_test(addr);

索引优化建议

1. 对索引列进行计算

例如，我们想要将表 tb_test 中 id 大于 100 的数据记录中的 age 和 name 查找出来。

正确的 SQL 语句是：

select age,name from tb_test where id   1*100;

不建议采用的 SQL 语句是：

select age,name from tb_test where id/100   1;

2. 对索引列进行拼接

例如，我们想要将表 tb_test 中 name 为“zhou”、addr 为“CQ”的记录中的 id 和 age 查找出来。

正确的 SQL 语句是：

select id,age from tb_test where name= zhou  and addr= CQ

不建议采用的 SQL 语句是：

select id,age from tb_test where concat(name,  ‘,addr) = ‘zhou CQ

3. 在索引列上 is null 或 is not null 的使用

例如，我们想要将表 tb_test 中 id 大于等于“0”的记录中的 age 查找出来。

正确的 SQL 语句是：

select age from tb_test where id  = 0;

不建议采用的 SQL 语句是：

select age from tb_test where id is not null;

4. 在索引列上 or 的使用

例如，我们想要将表 tb_test 中 id 等于 101 或 102 的记录中的 age 和 name 查找出来。

正确的 SQL 语句 (使用 union) 是：

select age,name from tb_test where id = 101 union select age,name from tb_test where id = 102;

不建议采用的 SQL 语句 (使用 or) 是：

select age,name from tb_test where id = 101 or id = 102;

5. 尽可能避免索引列在 like 的首字符使用通配符

例如，我们想要将表 tb_test 中 name 匹配“zho”的记录中的 id 和 age 查找出来。

正确的 SQL 语句是：

select id,age from tb_test where name like ‘zho%

不建议采用的 SQL 语句是：

select id,age from tb_test where name like ‘%ho%

6. 复合索引的使用

如果我们建立的索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。

例如，我们在表 tb_test 上新建了如下索引：

create index idx4_tb_test on tb_test(id,name,addr);

以上索引 idx4_tb_test 相当于建立了 index(id)、index(id,name)、index(id,name,addr) 这 3 个索引。在 SQL 语句的 where 条件中单独使用 name 或 addr 时不会使用到该索引，必须使用 id 时才会使用到该索引。

在我们编写的 SQL 语句中，不正确地使用索引列可能会导致索引不被使用，而进行全表扫描，极大地降低了数据库的性能。因此，学习正确的索引的使用方法实在是很有必要的。

以上是“数据库中 90% 程序员面试会遇到的索引优化问题有哪些”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！