索引的本质是什么？

索引是什么？

  • 索引（Index）是帮助Mysql高校获取数据的数据结构，在Mysql种，数据最终存储在硬盘中。
  • 在关系数据库中，索引是一种单独的、物理的、对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句。
  • 单列索引的节点关键字就是索引的列名

什么是聚集索引？

  • 聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，与非聚集索引相比，聚集索引有着更快的检查速度。
  • 在InnoDB中，只有主键是聚集索引，其他的索引都是非聚集索引。

什么是稀疏索引？

  • 文件只为索引码的某些值建立索引项。
  • 在稀疏索引中，不会为每个搜索关键字创建索引记录。此处的索引记录包含搜索键和指向磁盘上数据的实际指针。要搜索记录，我们首先按索引记录进行操作，然后到达数据的实际位置。如果我们要寻找的数据不是我们通过遵循索引直接到达的位置，那么系统将开始顺序搜索，直到找到所需的数据为止。
  • MyISAM不管是主键索引、唯一键索引或者普通索引，其索引都属于稀疏索引
  • InnoDB非主键索引是稀疏索引，非主键索引存储相关键位和它对应的主键值，包含两次查找

like ‘xxx%’ 一定会用到索引么？

  • 会用到索引，但并不是所有的like都会用到索引，情况如下
    • like 'xxx%' 这样百分号在右侧的使用到索引
    • like '%xxx' 这样百分号在左侧的则全局遍历
    • 没有使用百分号的使用到索引

为什么MySql要默认使用B+Tree，而不是B-Tree，AVL？

  • 二叉搜索树

    • 在数据递增的时候并没有产生优化效果，甚至出现了内存浪费

  • 平衡二叉搜索树

    • Mysql读取一页为16384byte，即16KB，而每次从磁盘中加载数据只有一个节点的不到1k的数据，造成了极大的IO浪费

  • B-Tree（多路搜索树/多叉平衡查找树）

    • 单个节点的关键字由一个变成了多个，同时分叉也由两个变成了多个；B-Tree是绝对平衡树。
    • B-Tree由于单个节点可以有多个关键字，减少了IO次数，解决了单次搜索IO数据浪费的问题

  • B+Tree（关键字个数 ：路数 = 1 : 1）

    • 非叶子节点不再存储数据，只作为索引使用；而叶子节点只存储数据，不存在指针；这样较少了单个节点占用的空间，因此单次IO可以传输的数据更多，IO吞吐能力更强
    • 其实就是通过双开区间变为左闭右开去间来实现的。
    • 所有的数据都在叶子节点，并且叶子节点都是相邻链接的，因此可以保证单次IO操作获取的数据都是需要数据的相关数据；所以基于索引的扫表操作更快，基于索引的排序更强

为什么不建议写select * from 进行查询？

  • 不需要的列会增肌数据传输时间和网络开销
  • 对于无用的大字段，比如varchar、blob、text等，会增加io操作
  • 失去mysql优化器“覆盖索引”策略优化的可能性
    • 使用select *无法利用索引类型的单表查询。在mysql中不同的查询条件，经mysql优化器优化后可能会利用不同的查询类型。
    • 连接查询时，select * 无法进入缓冲池
      mysql中连接查询的原理是先对驱动表进行查询操作，然后再用从驱动表得到的数据作为条件，逐条的到被驱动表进行查询。
      • 什么是驱动表？
        1. 当连接查询没有where条件时，左连接查询时，前面的表是驱动表，后面的表是被驱动表，右连接时相反；内连接查询时，哪张表的数据较少，哪张表就是驱动表
        2. 当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表
    • 每次驱动表加载一条数据到内存中，然后被驱动表所有的数据都需要往内存中加载一遍进行比较。效率很低，所以mysql中可以指定一个缓冲池的大小，缓冲池大的话可以同时加载多条驱动表的数据进行比较，放的数据条数越多性能io操作就越少，性能也就越好。所以，如果此时使用select * 放一些无用的列，只会白白的占用缓冲空间。浪费本可以提高性能的机会。

最左匹配原则你怎么去理解它？

联合索引的节点关键字按照最左匹配原则：

  • 假如第一个关键字存在，则进行索引

  • 如果第一个关键字相同，则查询第二个关键字

  • 一次类推

  • 假如第一个关键字不存在，则把第一个关键字匹配的全部遍历

  • 假如第一个关键字不存在，则不使用该索引

select * from user where age = 20 and name = 'aaa';

在索引

create index index_name (name, age);

中依旧可以用，因为Mysql的优化器会自动将其调换位置，但是在编写sql是还是建议将条件语句和索引对应。

为什么建议主键ID是递增的，和B+Tree有什么关联？为什么不能是UUID？

  • 因为B+Tree是天然有序的，因此当主键ID是递增的时候，递加增价叶子节点的效率相比插入更高。
  • UUID是varchar类型的，为32字节，单个节点的存储空间更大，会减小IO的吞吐能力。
  • UUID是一个随机生成的唯一数字，因此在B+Tree中为插入操作，效率更低

你是如何理解三星索引的？

  • 第一星：索引将相关的列放到一起，即在一系必要的列上建立索引，不必为在where条件里面的列都建立索引。

    • 即索引中将相关需要查询的记录都放到一块

  • 第二星：索引中的数据列顺序和查找中排列顺序一致。通常将选择性最高的列放到索引的最前列。

  • 第三星：索引中的列包含了查询中需要的全部列。索引包含查询所需要的数据列，不再进行全表查表（聚簇索引、覆盖索引）。

  • 同时符合上述三个条件就记为三星索引。

  • 列的离散型（也叫离散因子/选择性）：列的不重复的数量比总列数

    • 建立的索引除了主键索引和唯一索引之外，建立的索引列的离散型1：10比较好

      当列的离散型较差时，Mysql默认进行全部遍历

为什么Innodb引擎要求一定要建立主键索引？

  • 因为Innodb引擎的非主键引擎最终的查询结果是主键，最后还是通过主键索引完成的索引。

mysql的存储引擎

mysql典型的存储引擎有两种，在5.x（大致是这个，具体不记得的，查了下，是5.5）版本之前用的是MyISAM，后来的新版本用的是Innodb

  • MyISAM
    • 文件系统：
      • frm文件：描述表结构的文件
      • MYD文件：存放表具体记录的数据
      • MYI文件：存储索引
    • 索引结构：B+Tree
    • 索引：
      • 当建立的是主键索引时，会在MYI文件中建立树结构存储主键值、叶子节点存储数据地址的B+Tree，当在其中查到数据所在位置后会进入MYD文件按址查找。（其实也不是什么地址，严格地说叫索引码，但是作用和地址一样）
      • 当建立的是非主键索引时，和主键索引相同，会在MYI文件中建立树结构存储非主键值（关键字）、叶子节点存储数据地址的B+Tree，当在其中查到数据所在位置后会进入MYD文件按址查找。
      • image-20201209130045368
  • InnoDB
    • 文件系统：
      • frm文件：描述表结构的文件
      • ibd文件：存储索引和数据的文件
    • 索引结构：B+Tree
    • 索引：
      • 当建立的主键索引时，叶子节点存储的就是索引指向的数据，可以直接使用；
      • 当建立的是非主键索引时，叶子节点存储的是主键值，拿到主键值后，再通过主键索引查找数据；（这也是不建议建立非主键索引的原因）
      • image-20201209130938496
    • 两者的区别是什么（两者的区别挺多的，暂时写这么多吧，后面可能会单独开一篇文章来写这个问题）
      • InnoDB支持事务，MyISAM不支持事务
      • InnoDB支持外键，而MyISAM不支持
      • InnoDB是聚集索引，而MyISAM是稀疏索引
      • InnoDB不保存表的具体行数，使用count查询行数时需要全表扫描，而MyISAM使用一个变量保存的行数，不需要全表扫描
      • InnoDB最小的锁粒度是行锁，MyISAM最小的锁粒度是表锁。
      • MyISAM相对简单，效率上优于InnoDB，小型应用更加友好。
      • MyISAM表保存成文件形式，跨平台使用更加方便。
      • MyISAM支持全文类型索引，InnoDB不支持

Mysql的执行流程（Server层）

  • 从客户端点击执行SQL语句
  • 到连接器进行权限验证
  • 检查查询缓存是否有结果，有则返回，没有继续向下
  • 到分析器进行词法和语法分析
  • 到优化器进行语句优化以及索引选择
  • 到执行器再次进行权限验证然后查询范围结果
  • 将结果放入查询缓存

image-20201209133425923

常见的sql十大优化策略

  • 尽量全职匹配
  • 最佳左前缀法则
  • 不在索引列上做任何操作
  • 范围条件放最后
  • 覆盖索引尽量用
  • 不等要慎用
  • null/not
  • like查询要当心
  • 字符类型加引号
  • or改union有奇效

其他

Q.E.D.

If you don't come, I will snow.