自学内容网 自学内容网
自学内容网 > 正文

mysql索引的理解

🕗 发布于 2024-12-28 09:49 mysql  数据库  

1、索引是什么？

  1. 索引：简单理解就是我们字典的目录，一个索引可以找得到多个记录。
    1. 作用加快我们数据库的查询速度。
    2. 索引本身较大，往往存储在磁盘的文件里。可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）
    3. 对数据进行排序，降低了排下序成本。
  2. 索引的由浅到深的理解：
    1. 如果数据拍成一条直线，那么二分查找就是最好的方法，所以可以将这些索引改做一颗二叉树，但是会出现所有的数据全部在一边形成链表，所以就需要将二叉树调正为平衡二叉树。
    2. 当数据变多，那么二叉树的高度变得太高。查询需要硬盘做I/O操作，硬盘的速度很慢，这不利于快速查询。由此引入了B树。
    3. 在B树中，每一个节点可以存放多条数据并且数据之间是有序的，并且一个节点分支可以有多个，查询的时候，从根节点开始，做二分查询，依次往下直到查找到对应的索引。
    4. 但是，B树也有缺点：
      1. 当查询数据在根节点附近时，查询速度很快，当数据在叶子节点时，查询速度就会变慢、可能变快，性能不稳定。
      2. 当做范围查找时，由于数据散落在树的各个节点上，所以查询时，需要在不同的节点上做I/o操作。
    5. 最后，引入的B+树就可以解决B树的问题。
      1. B+树将所有的数据全部放在叶子节点上，叶子节点存储索引和数据，非叶子节点存储指向其他索引的指针。这样，一个节点可以有更加多的子节点，树的高度变得更低。I/O次数更少。
      2. 叶子节点使用双向链表进行连接，数据之间是有序的，解决了范围查找。

2、索引的类型？

创建索引：

create index [index_mode] on [name]([mode1，model2]);
index_mode自定义索引名
name表名
mode列名
  1. 主键索引：索引列中的值必须是唯一的，不允许有空值。比如每个表的id字段。
  2. 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
CREATE INDEX index_name
ON table_name (column_name);
// 在表上创建一个简单的索引。允许使用重复的值：
  1. 唯一索引: 索引列中的值必须是唯一的，但是允许为空值。
CREATE UNIQUE INDEX index_name
ON table_name (column_name);
// 在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
  1. 全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。
CREATE FULLTEXT INDEX ft_idx_content ON articles (content);
  1. 空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
CREATE SPATIAL INDEX sp_idx_location ON parks (location);
  1. 前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

3、索引的数据结构？

  1. hash表：以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。
  2. 二叉查找树：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。但是可能出现数据成一条直线的情况。
  3. 平衡二叉树：不会出现左子树很高、右子树很矮的情况。和直线的树。但数据不能太多。不支持范围查找。
  4. B树：B树的节点中存储着多个有序的元素，每个内节点有多个分叉。叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。但是不支持范围查询。
  5. B+树：B+树的最底层叶子节点包含了所有的索引项。叶子节点之间采用双向链表来连接。

4、mysql索引的实现？

  1. 在InnoDB存储引擎中，每个表都存在一个聚簇索引。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：
    1. 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
    2. 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
    3. 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。
    4. 除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值对。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。
    5. 当使用主键索引查询，从主键索引树的根节点开始，知道叶子节点并获得整行的数据。
    6. 当辅助索引查询，从辅助索引树的根节点开始，直到叶子节点并获得主键的值，再到主键索引树中查找该主键的行数据。
  2. 在MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
    1. 查找时，从主键索引树根节点开始，到叶子节点找到磁盘地址。在数据文件中找到对应的整行数据。
    2. 在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

5、什么是回表？

  1. 回表理解就是，当使用辅助索引中的主键值去主键索引树中查找行数的过程。
  2. 回表必然是会消耗性能影响性能。那如何避免呢？

    1. 使用索引覆盖：创建索引时，将所有的字段都设置为索引·。

      SELECT username, email FROM users WHERE username = 'john_doe'; 

CREATE INDEX idx_username_email ON users (username, email);

    2. 使用联合索引：将当前多个单列索引创建为一个联合索引。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，

6、索引失效？

当查询过程中，数据库无法直接使用索引来进行查询，导致全表查询。性能下降。如何避免呢：

  1. 组合索引非最左前缀

描述：在组合索引中，如果查询条件没有从索引的最左边列开始，那么索引将不会被利用。
示例：对于索引(A, B, C)，不从A 开始，查询WHERE B = ?或WHERE B = ? AND C = ?会导致索引失效。

  1. LIKE查询%开头
    描述：当LIKE查询的模式以%开头时，索引将不会被使用，因为数据库无法利用索引来快速定位记录。
    示例：查询SELECT * FROM table WHERE column LIKE '%abc’会导致索引失效。
  2. 字符串未加引号
    描述：如果查询条件中的字符串类型值没有用引号括起来，可能会因为类型不匹配导致隐式转换，从而使索引失效。
  3. 不等比较
    描述：对索引列使用不等比较（如!=或<>）时，索引的使用可能会受到限制，尽管它仍有助于缩小搜索范围。
  4. 索引列运算
    描述：在查询条件中对索引列进行函数运算或计算表达式时，索引将失效，因为数据库无法直接利用索引匹配处理后的值。
    示例：查询SELECT * FROM table WHERE YEAR(date_column) = 2023可能导致索引失效。
  5. OR连接查询
    描述：当查询条件使用OR连接时，如果OR连接的两个条件分别作用于不同的索引列，数据库可能无法同时利用这两个索引。
    示例：查询SELECT * FROM table WHERE column1 = ‘value1’ OR column2 = ‘value2’（假设column1和column2分别属于不同的索引）可能导致索引失效。
  6. 使用范围运算，not in，in > ,<都不行。

原文地址：https://blog.csdn.net/m0_68274160/article/details/144777017

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024