MySQL笔记（二）索引

日月卿_宇
120 阅读11分钟

前言

索引是帮助MySQL高效获取数据的排好序的数据结构

索引数据结构

  • 二叉树
  • 红黑树
  • Hash表
  • B-Tree

二叉树

二叉树形式 image.png 二叉树缺点：从1到8输入数据

image.png

红黑树

image.png 又称为二叉平衡树：从1到8输入数据

image.png 如果数据量过大时，树的高度过高

Hash表

  • 对索引的key进行一次hash计算就可以定位出数据存储的位置。
  • 很多时候Hash索引要比B+树索引更高效。
  • 仅能满足“=”,“IN"，不支持范围查询。
  • hash冲突问题。

image.png

image.png

B-Tree

  • 叶节点具有相同的深度，叶节点的指针为空
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增排列

示例：

image.png

B+Tree

  • 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
  • 叶子节点包含所有索引字段
  • 叶子节点用指针连接，提高区间访问的性能

示例： image.png

image.png

MySQL

MySQL采用Hash表和B+Tree两种方式 使用show global status like 'Innodb_page_size'SQL语句查询叶子节点大小

image.png 默认大小为16kb；

存储引擎：

  • Innodb引擎： Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
  • MyISAM引擎： （原本MySQL的默认引擎）不提供事务的支持，也不支持行级锁和外键。
  • MEMORY引擎： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MySQL中存储文件

1、MyISAM引擎 MyISAM存储引擎存储数据文件有三个，分别为myisam.frm、myisam.MYD和myisam.MYI

.frm文件为表结构，.MYD文件为数据，.MYI文件为索引。

MyISAM索引文件和数据文件是分离的（非聚集）

执行sql查询是时，先判断查询条件是否为索引条件，如果为索引条件，先查询.MYI文件，如下图，根据索引查询到数据所在的磁盘文件地址，再到.MYD文件中获取数据。 image.png

2、InnoDB引擎 InnoDB存储引擎存储数据文件有两个，分别为innodb.frm、innodb.idb

.frm文件为表结构，.idb文件为索引和数据

InnoDB索引实现（聚集）

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件
  • 聚集索引-叶节点包含了完整的数据记录
  • 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键不推荐UUID? 答： 因为InnoDB数据存储是以b+Tree模式，有主键就会根据主键创建主键索引，没有主键就会找一列没有重复数据的列来创建为主键索引，如果都没有，MySQL有隐藏列 rowid数据，隐藏列由mysql来维护，但其使用者不能操控mysql隐藏列，所以不如自己创建。
  • 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)

主键索引： image.png 二级索引/一般索引： image.png 通过二级索引查询数据，先从二级索引中查询数据主键。

联合索引

image.png

explain 使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈。

1、千万级数据表如何用索引快速查找

90%的情况进行单表查询，使用主键索引进行单表查询，查询到结果后使用java代码进行处理，再使用主键查询其他数据。

2、如何基于索引B+树精准建立高性能索引

要基于B+树构建高性能的索引，可以采取以下步骤：

  1. 选择适当的索引列：选择经常作为查询条件的列作为索引列。这些列应具有较高的选择性，即唯一性或者重复值较少。
  2. 组合索引：考虑在多个列上创建组合索引，以便在复合查询中提供更好的索引支持。组合索引可以减少索引的数量，减少IO操作和索引维护开销。
  3. 考虑覆盖索引：在创建索引时，尽可能包含需要在查询中选择和过滤的列，以创建覆盖索引。覆盖索引可以避免回表操作，提高查询性能。
  4. 调整索引顺序：对于组合索引，考虑将列的顺序调整为从最经常查询的列开始，以优化索引的使用。
  5. 控制索引的长度：尽量保持索引尽可能小，以减少磁盘空间占用和内存消耗。较长的索引可能会导致索引扫描的数据量增加，并降低查询性能。
  6. 调整页填充因子：根据数据的特点和查询模式，调整B+树的页填充因子。页填充因子决定了每个索引页中的数据项数量，过低的填充因子可能导致索引树过深，增加IO操作的成本，而过高的填充因子可能导致索引页的碎片化，影响查询性能。
  7. 定期维护索引：定期进行索引的碎片整理和重建，以优化索引的物理存储，减少磁盘IO操作。
  8. 监控和优化索引使用情况：通过使用数据库支持的工具，如执行计划，了解索引的使用情况和性能影响，并进行优化调整。根据实际情况，考虑增加或删除索引，或者进行索引性能调优。

需要根据具体的数据库系统和业务场景来选择合适的建索引策略。在实践中，一般需要借助数据库的性能监控和调优工具，并根据实际数据和查询模式的特点进行测试和评估，不断优化索引设计以满足高性能查询的需求。

3、聚集索引与覆盖索引与索引下推是什么

聚集索引（Clustered Index）：

  • 聚集索引是指在关系型数据库管理系统中，根据表的主键或唯一约束创建的索引。它决定了表数据的物理存储顺序，即按照聚集索引的顺序对表进行排序并存储。因此，每个表只能有一个聚集索引。
  • 聚集索引的特点是能够快速定位具有特定主键值（或约束值）的行，因为表的数据按照聚集索引的顺序进行存储。如果查询条件涉及到聚集索引列，查询将更加高效。

覆盖索引（Covering Index）：

  • 覆盖索引是指在查询时，索引本身就能够包含查询需要的所有列，无需回表到原始数据行中进行查找。这种情况下，查询可以直接使用索引中的数据，无需进一步的IO操作，从而提高查询性能。
  • 覆盖索引通常需要创建多列的索引，以确保包含所需的所有列。通过覆盖索引，可以减少对数据行的访问，特别对于大规模数据表和复杂查询，性能提升较为明显。

索引下推（Index Condition Pushdown）：

  • 索引下推是MySQL数据库的一种查询优化技术。它的原理是将查询条件下推到存储引擎层级，并在存储引擎层级上尽早过滤不符合条件的数据，从而减少存储引擎向上层传递的数据量。
  • 当使用索引下推时，MySQL可以在索引遍历的过程中根据查询条件进行数据过滤，只将符合条件的数据返回给上层查询。这样可以减少IO操作和数据传输的开销，提高查询性能。 需要注意的是，聚集索引、覆盖索引和索引下推是不同的概念，虽然它们都与索引和查询性能有关。聚集索引是一种特殊类型的索引，用于定义数据存储顺序；覆盖索引是一种优化技术，用于避免回表操作；索引下推是一种查询优化技术，用于在存储引擎层级上进行数据过滤。它们可以结合使用，以提高数据库的查询性能。

4、联合索引底层数据存储结构又是怎样的

参考上文 前言-MySQL-联合索引 示意图 联合索引采用B+Tree，根据字段从左向右依次进行排序，查找到数据后，数据保存的为主键，再根据主键进行查找。

5、如何使用MySQL查询计划定位线上慢sql问题

使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈。

6、MySQL最左前缀优化原则是什么

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

在联合索引存储排序时，会从左向右依次进行排序，如果不遵循最左原则，则无法从索引中进行查找了，参考联合索引存储示意图。

7、为什么推荐使用自增整型的主键而不是UUID

  • 1、查找数据是UUID（字符串）的比较效率比整型数据比较效率慢；
  • 2、整型存储占用空间比字符串小；
  • 3、UUID并不是自增的，导致插入数据时效率降低，但是有得情况不得不使用UUID，比如分库分表的业务场景，可以使用雪花算法来进行改进。

8、MySQL并发支撑底层Buffer Pool机制详解

  1. 数据页：MySQL将磁盘上的数据划分为固定大小的数据页，通常大小为16KB，默认情况下数据页的大小是由InnoDB存储引擎控制的。每个数据页中存储着数据库中一部分数据。
  2. 缓冲区：Buffer Pool是MySQL中的内存缓冲区，用于存储数据页的副本。通过将磁盘上的数据页加载到内存缓冲区中，可以减少磁盘IO操作，提高数据的访问速度。
  3. 读取操作：当执行查询操作时，MySQL首先会检查Buffer Pool中是否存在所需的数据页。如果数据页已经在Buffer Pool中，则直接从内存中读取数据，避免了磁盘IO操作。如果数据页不在Buffer Pool中，则需要从磁盘读取数据，并将其加载到Buffer Pool中，以供后续的查询使用。
  4. 写入操作：当执行插入、更新或删除操作时，MySQL首先会将数据写入到Buffer Pool的页中，然后再由后台线程将更改的数据异步刷新到磁盘中。通过使用Buffer Pool，MySQL可以延迟磁盘写入操作，从而提高写入操作的性能。同时，MySQL还使用了写入日志（Redo Log）来保证数据的持久性和一致性。
  5. LRU算法：Buffer Pool使用了最近最少使用（LRU）算法来进行页的管理。当Buffer Pool空间不足时，MySQL会使用LRU算法替换最近最少使用的页，以便给新的页腾出空间。
  6. 并发控制：Buffer Pool通过锁和各种并发控制机制来支持并发操作。例如，读取操作可以共享数据页的锁，而写入操作会涉及到更严格的锁机制来保证数据的一致性。

9、阿里内部为什么禁止超过三张表关联查询

实际大数据开发时，90%的情况进行单表查询，使用主键索引进行单表查询，查询到结果后使用java代码进行处理，再使用主键查询其他数据。 即便是java数据处理过多，但是服务水平扩容相对数据库更容易，而且数据库在扩容并不能做到线性增长，扩容到一定程度时性能不会再增长。 多表联合查询时，MySQL底层会进行大量的算法，在高并发大数据量时进行多表联合查询，MySQL底层会进行大量的数据运算，会导致数据库的性能降低，即便是走索引，也会进行很多次的扫描内存。

10、阿里内部MySQL索引优化原则

网上查询Java开发手册。

11、聚集（聚簇）索引和非聚集（非聚簇、稀疏）索引的区别

聚集索引：索引和数据放在同一个文件中 非聚集索引：索引和数据不在同一个文件中。如MyISAM引擎中索引放在.MYI文件中，数据放在.MYD文件中 聚集索引效率高于非聚集索引。

avatar
文章
阅读
粉丝