MySQL索引

Atomic
190 阅读9分钟

MYSQL技术栈

索引

定义

MYSQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

  • 索引是一种数据结构
  • MYSQL中常见的索引分为B +树索引和哈希索引，它们采用不同的数据结构算法
  • 索引以文件形式持久化到磁盘上的。

语法

创建

 CREATE [UNIQUE] INDEX indexName ON mytable(username(length))

 ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)

查看

  SHOW INDEX FROM table_name

删除

 DROP INDEX [indexName] ON mytable

修改

如果要修改索引的话需先删除原来的索引，然后重新创建新的索引

优劣势

优势

  • 提高数据检索效率，降低数据库IO成本
  • 降低数据排序的成本，降低CPU的消耗

劣势

  • 索引需要以文件形式保存在磁盘上，如果索引过多的就需要占用磁盘的磁盘资源
  • 提高SELECT速度的同时，也会降低INSERT，UPDATE和DELETE效率，因为插入和更新数据时需要额外的维护索引，带来资源的消耗

索引分类

数据结构角度

  • B +树索引
  • 哈希索引
  • 全文全文索引
  • R树索引

物理存储角度

  • 聚集索引

    索引文件与数据文件不分离，如INNODB的主键索引。

  • 非聚集索引

    也叫辅助索引，索引文件与数据文件分离，索引中不保存具体的行数据。

逻辑角度

  • 主键索引

    主键索引是一种特殊的唯一索引，永久有空值

  • 普通索引或者单列索引

    每个索引只包含个别列，一个表可以有多个单列索引

  • 多列索引（复合索引，联合索引）

    复合索引指多个分段上创建的索引，只有在查询条件中使用了创建索引时的第一个分段，索引才会被使用。遵循最左匹配原则。

  • 唯一索引或非唯一索引

    索引的值是否是唯一的；也可以创建唯一的联合索引。

索引结构

InnoDB存储引擎中有每一页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K，8K，16K，在MySQL中可通过如下命令查看页的大小：show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间通常没有这么大，因此InnoDB每次申请磁盘空间时都会是多个地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时是否一个页面中的每条数据都能进行定位数据记录的位置，这将减少磁盘I / O次数，提高查询效率。

B树索引

B-Tree中索引每个索引包含多个索引键值，并包含这些键值所在的行数据；其次还保存相邻键值范围内部的数据的子指针指针。如果在查找过程中匹配上了键值，则可以找到相应的行数据返回。

  • 若直接匹配上，直接就找到了行数据，不用再往下搜索。
  • 索引的每个变量空间有限，如果保存了行数据，那么能够保存的键值数量就少了。这可能导致在数据量大的情况下整个整个树的深度会变大，查找数据时向下搜索的次数变多，也就是进行磁盘IO的次数增加，这会大大影响查询的效率。

B +树索引

B + Tree是在B-Tree基础上的一种优化。

相比较B-Tree，B +树索引的特点如下：

  1. 这大大增加了每一页可以保存的键值数量，树的深度会降低，通常是B + tree的深度为2〜4，磁盘IO次数减少，查询效率提高。
  2. 通常在B + Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子数组，而且所有叶子例程（即数据字节）。 ）之间是一种链式环结构。因此可以对B + Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根上方开始，进行随机查找。
  1. 非叶子官员只存储键值信息；
  2. 所有叶子遥控器之间都有一个链指针；
  3. 数据记录都存放在叶子子系统中

InnoDB主键索引与辅助索引的结构

InnoDB和MyISAM的索引都是通过B +树实现的，所以，它的数据都是保存在叶子例程上的，非叶子例程只保存键值信息。

InnoDB的含有中聚集索引状语从句：非聚集索引，MyISAM数据的英文全非聚集索引。

InnoDB的数据文件本身就是根据主键按照B +树形成的一个文件结构，所以，数据文件就是主键索引，由于这个文件还保存了所有的行数据，也就是聚集索引。

InnoDB的数据文件本身就是主键索引文件，也可以称为聚集索引。

主键索引：

我们知道InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树上面中同时放置索引和数据，如下图所示大部分的叶子有三行数据，对应于数据表中的id，stu_id，name数据项。

通过主键索引，我们可以直接找到数据行。

辅助（非主键）索引：

我们以名称列建立辅助索引。

如果我们通过这个索引查找到了名称= Json的数据，那么我们得到的只是改行数据的主键，而不能在此时获得该行的其他信息。这时候我们需要根据刚才得到的主键，到主键索引（数据文件）中去查找相应的行数据，这就是回表查询。

上述通过name查到行数据的过程经历了两次查找。

InnoDB索引结构需要注意的点：

  1. 数据文件本身就是索引文件
  2. 表数据文件本身就是按B + Tree组织的一个索引结构文件
  3. 聚集索引中叶例程包含了完整的数据记录
  4. InnoDB表必须要有主键，并且推荐使用整型自增主键

那为什么推荐使用整型自增主键而不是选择UUID？

  1. 在查找的过程中需要对键值进行比较计算，UUID是长字符串，计算的过程较早数据复杂，因此推荐整型数据类型做主键，节省内存资源
  2. 在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏开头子树的结构； UUID主键很容易出现这样的情况，B +树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间
  3. 自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续； UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行区间范围查询

InnoDB和MyISAM的索引比较：

  1. 都是B +树实现的。
  2. InnoDB是聚集索引，MyISAM是非聚集索引。InnoDB必须要有主键。
  3. MyISAM的辅助索引，叶子副本保存的不是主键而是数据行的物理地址。

哈希索引

主要就是通过Hash算法（常见的Hash算法有直接定址法，平方取中法，折叠法，除数取余法，随机数法），将数据库数据转换成定长的Hash值，与这条数据的行指针一并存入哈希表的对应位置；如果发生哈希冲突（两个不同关键字的哈希值相同），则在对应的哈希键下以链表形式存储。

检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应的Hash表对应位置取出数据即可，如果发生Hash冲突，则需要在取值时进行筛选。目前使用哈希索引的数据库并不多，主要有内存等。

MySQL现有的内存引擎和NDB引擎支持哈希索引。

为何不推荐哈希索引？

因为哈希索引索引是哈希表，哈希表是一种以键值存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引而B + Tree是一种多路平衡查询树，所以他的例程是天然有序的（左子序列小于父）的，则需要全表扫描。所以，哈希索引仅适用于等值查询的场景。报表，父例程小于右子例程），所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希冲突问题。

覆盖索引

覆盖索引（Covering Index），或者叫索引覆盖，也就是平时所说的不需要回表操作。

  • 就是select的数据列只用从索引中就能够取得，不必重新读取数据行，MySQL可以利用索引返回select列表中的变量，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
  • 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必重新读取整个行。一个索引包含（覆盖）满足查询结果的数据就叫做覆盖索引。

合理使用索引

适合建立索引的情况

  • 主键自动建立唯一索引
  • 继续作为查询条件的细分
  • 查询中与其他表关联的细分，外键关系建立索引
  • 单键/组合索引的选择问题，高并发下潜在创造组合索引
  • 查询中排序的划分，排序通过索引访问显着提高排序速度
  • 查询中统计或分组长度

不适合建立索引的情况

  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布均匀的表分区，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
  • 不断更新的细分不适合创建索引（会加重IO负担）
  • 其中条件里用不到的海滨不创造索引

本文使用 mdnice 排版

avatar
文章
阅读
粉丝