校招面试数据库原理知识复习总结二之索引

zhangkai__

已于 2022-09-18 20:21:46 修改

阅读量289

点赞数 1

文章标签：数据库面试 java mysql

于 2022-08-24 20:38:00 首次发布

本文链接：https://blog.csdn.net/zhangkai__/article/details/126512205

版权

文章目录

Mysql索引与性能优化知识总结

Mysql索引与性能优化知识总结

1.定义和特点

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。

MyISAM和InnoDB存储引擎只支持BTREE索引；

MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点主要有以下几条：

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的主要原因。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

增加索引也有许多不利的方面，主要表现在如下几个方面：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。（损耗时间）
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。（损耗空间）
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。（索引应该建立在不频繁更新的表上）

2. 索引分类

MySQL的索引可以分为以下几类：

普通索引和唯一索引
普通索引是MySQL中的基本索引类型，在定义索引的列中允许插入重复值和空值。
唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引是一种特殊的唯一索引，不允许有空值。
单列索引和组合索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段
时，索引才会被使用。使用组合索引时遵循最左前缀集合。
全文索引
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，在这些索引列中允许插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。
空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是
GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。 MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。必须使用 GIS 相关的函数来维护数据。

3. 索引创建

在创建表的时候创建索引：

使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引

CREATE TABLE table_name [col_name data_type]
--其中，UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；----INDEX与KEY为同义词，两者作用相同，用来指定创建索引。
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length])
[ASC|DESC]
--eg：
CREATE TABLE t1 (
id INT NOT NULL,
name CHAR(30) NOT NULL,
--在id字段上创建非空索引UniqIdx
UNIQUE INDEX UniqIdx(id)
);

在已存在的表上创建索引

在已经存在的表中创建索引，可以使用ALTER TABLE语句或者CREATEINDEX语句。

ALTER TABLE table_name ADD
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...)
[ASC|DESC]
--eg:bookId字段上建立名称为UniqidIdx的唯一索引
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx (bookId);

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (col_name [length],...) [ASC|DESC]
--eg：
CREATE UNIQUE INDEX UniqidIdx ON book (bookI\d);

4. 索引使用场合以及合理性

建议按照如下的原则来创建索引：

当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整
性，以提高查询速度。
在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有
多个，可以在这些列上建立组合索引。

建议按照如下的原则来设计索引：

避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整
性，以提高查询速度。

5. 判断数据库的索引是否生效

可以使用EXPLAIN语句查看索引是否正在使用。

EXPLAIN SELECT * FROM book WHERE year_publication=1990;

EXPLAIN语句将为我们输出详细的SQL执行信息，其中：
possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
key行是MySQL实际选用的索引。

如果possible_keys行和key行都包含year_publication字段，则说明在查询时使用了该索引。

6. 索引失效

可以采用以下几种方式，来避免索引失效：

使用组合索引时，需要遵循**“最左前缀”原则**；
不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
尽量使用覆盖索引（只访问索引列的查询），减少 select * 覆盖索引能减少回表次数；
MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；
LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；
字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；
少用or，用它来连接时会索引失效。

7.索引实现原理

在Innodb下主键索引是聚集索引，在MyISAM下主键索引是非聚集索引，都是采用B+树作为底层结构，因为B+树索引在数据库中的一个特点就是高扇出性

7.1MyISAM索引实现：(非聚簇索引)

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址 。
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

主键索引如下形式：

在这里插入图片描述

辅助索引：在Col2上建立一个辅助索引，则此索引的结构如下图所示

在这里插入图片描述

7.2 InnoDB索引实现

主键索引（聚簇索引）

在InnoDB中，==表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。==这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

在这里插入图片描述

辅助索引（非聚簇索引）

InnoDB的辅助索引data域存储相应记录主键的值而不是地址 。

在这里插入图片描述

聚簇索引和非聚簇索引的区别

聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引；二级索引的叶子节点存放的是主键值或指向数据行的指针。

由于节子节点(数据页)只能按照一颗B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引

Innodb和MyISAM的索引图

在这里插入图片描述

8. 索引重构

表上频繁发生update,delete操作；
表上发生了alter table …move操作（move操作导致了rowid变化）。

在这里插入图片描述

9. 面试问题：

9.1MySQL的Hash索引和B树索引有什么区别？

参考答案

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。
B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
它们有以下的不同：
- hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
- hash索引不支持使用索引进行排序，原理同上
- hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
- hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

9.2 select in语句中如何使用索引？

参考答案

索引是否起作用，主要取决于字段类型：

如果字段类型为字符串，需要给in查询中的数值与字符串值都需要添加引号，索引才能起作用。
如果字段类型为int，则in查询中的值不需要添加引号，索引也会起作用。
IN的字段，在联合索引中，按以上方法，也会起作用。

9.3 模糊查询语句中如何使用索引

参考答案
在MySQL中模糊查询 mobile like ‘%8765’ ，这种情况是不能使用 mobile 上的索引的，那么如果需要根据手机号码后四位进行模糊查询，可以用以下方法进行改造。

我们可以加入冗余列（MySQL5.7之后加入了虚拟列，使用虚拟列更合适，思路相同），比如mobile_reverse，内部存储为 mobile 的倒叙文本，如 mobile为17312345678，那么 mobile_reverse存储 87654321371，为 mobile_reverse 列建立索引，查询中使用语句 mobile_reverse like reverse(’%5678’) 即可。

reverse 是 MySQL 中的反转函数，这条语句相当于 mobile_reverse like ‘8765%’ ，这种语句是可以使用索引的。

9.4 B+树，B树，红黑树对比

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，这是因为使用 B+ 树访问磁盘数据有更高的性能。

（一）B+ 树有更低的树高

平衡树的树高 O(h)=O(log_dN)O(h)=O(log dN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多。B树的每个节点存储了key和data，key是一条数据记录的键值，是唯一的，data存储的是数据记录除key以外的数据。

B+树只在叶子节点存储data数据，这样非叶子节点就能存储更多的key。所以B+树相较于B树来说更加的矮胖，因为索引树很大不能一次IO读取进内存，树的深度越浅，查找数据时IO的次数就越少，效率就更快。B+树的每个叶子节点的指针指向相邻的叶子节点，构成一个有序链表，可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树叶子节点指针为null，则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

（二）磁盘访问原理

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。

（三）磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

10. 索引优化

独立的列
在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
多列索引
在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。
索引列的顺序
让选择性最强的索引列放在前面。索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。
前缀索引
对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。
覆盖索引
索引包含所有需要查询的字段的值。

具有以下优点：

索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

11. 查询性能优化

使用 Explain 进行分析
Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有：
- select_type : 查询类型，有简单查询、联合查询、子查询等
- key : 使用的索引
- rows : 扫描的行数
- Extra：额外附加信息，主要确认是否出现 Using filesort、Using temporary 这两种情况。
优化数据访问
1. 减少请求的数据量
  - 只返回必要的列：最好不要使用 SELECT * 语句。
  - 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
  - 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。
2. 减少服务器端扫描的行数
  最有效的方式是使用索引来覆盖查询。
重构查询方式
1. 切分大查询
  一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
```
DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
```
1. 分解大连接查询
  将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：
  - 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
  - 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
  - 减少锁竞争；
  - 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
  - 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。
```
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
#优化后
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);
```
优化子查询：
- 使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
- 子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。
- 在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，性能会更好。

12.插入性能优化

对于InnoDB引擎的表，常见的优化方法如下：

禁用唯一性检查
插入数据之前执行 set unique_checks=0 来禁止对唯一索引的检查，数据导入完成之后再运行set unique_checks=1 。这个和MyISAM引擎的使用方法一样。
禁用外键检查
插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。
禁用自动提交
插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

13. MySql慢查询优化

开启慢查询日志：
MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开，也可以在MySQL服务启动的时候使用 –log-slow-queries[=file_name] 启动慢查询日志。
启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。
分析慢查询日志：
直接分析mysql慢查询日志，利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。
常见慢查询优化：

索引没起作用的情况
在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。
MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。
优化数据库结构
对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经
常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
分解关联查询
很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。
优化LIMIT分页
当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。优化此类查询的一个最 简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

14. 表中包含几千万条数据该怎么办

建议按照如下顺序进行优化：

优化SQL和索引；
增加缓存，如memcached、redis；
读写分离，可以采用主从复制，也可以采用主主复制；
使用MySQL自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的；
做垂直拆分，即根据模块的耦合度，将一个大的系统分为多个小的系统；
做水平拆分，要选择一个合理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部
的表。