MySQL 学习笔记(索引)

MySQL 学习笔记

• 索引
  • 什么是索引
  • 为什么要使用索引
  • 索引有哪些优缺点
  • 索引使用场景
    • 索引覆盖
  • 索引有哪几种类型
  • 索引数据结构
    • B树索引
    • B＋树索引
    • 哈希索引
      • 哈希索引的限制
    • 空间数据索引（R-Tree）
    • 全文索引
  • 索引的优点
  • 高性能的索引策略
    • 索引使用原则
    • 独立的列
    • 前缀索引和索引选择性
    • 多列索引
• 查询性能优化
  • 为什么查询速度会慢
    • 慢查询基础：优化数据访问
    • 重构查询的方式

索引

什么是索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

为什么要使用索引

在对数据库进行查找数据的过程中主要有以下三类需求：

• 根据某个值精确快速查找
• 根据区间的上下限来快速查找此区间的数据
• 查询符合条件的记录并根据某些字段进行排序

使用索引可以解决我们这些问题

索引有哪些优缺点

• 索引的优点

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

• 索引的缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
  • 空间方面：索引需要占物理空间。

索引使用场景

• where
• order by
• join

索引覆盖

如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。

索引有哪几种类型

• 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

• 全文索引： 是目前搜索引擎使用的一种关键技术

  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

索引数据结构

先了解一下平衡二叉树

平衡二叉树可用于查找，且其查找的时间复杂度近似 O(log2n),但是可以用平衡二叉树作为索引的结构吗？

答案是不能。

因为数据库表的数据通常是很多的，正常都是存放在磁盘上的。而磁盘的速度相比内存的速度是慢很多倍的，所以要尽量减少读取磁盘的次数，通过从内存读取数据来提高速度。

那么，如何将尽量多且有效的索引数据放到内存中呢？

这里有两个问题要解决：

1、尽量多

读取磁盘数据的时候，都是按磁盘块来读取的（局部性原理与磁盘预读），并不是一条一条的读。在使用树这种结构作为索引的数据结构时，我们每查找一次数据就需要从磁盘中读取一个树节点，也就是对应的一个磁盘块，所以如果我们能把尽量多的数据放到磁盘块中，那么每次读取的数据就会较多。

而平衡二叉树是每个节点只存储一个键值和数据，也就是说，存储的时候，每个磁盘块只存储一个键值和数据。

那如果存储了海量的数据，可以想象平衡二叉树的节点将会非常多，树高也会极其高，在查找数据的时候就会进行很多次磁盘 IO，效率将会极低。

所以平衡二叉树无法解决存储尽量多的索引到内存中这个问题。

2、有效的索引数据

我们所说的平衡二叉树，指的是逻辑结构上的平衡二叉树，其物理实现是数组。所以在逻辑相近的节点上，其物理位置可能相差会很远。因此，每次读取的磁盘页数据，很多可能是用不上的，即有效的索引数据并不多，所以在查找过程中还是要进行许多次的磁盘读取操作。

所以平衡二叉树也无法解决这个问题。

所以，能解决这两个问题的数据结构 —— B 树就被发明出来了。

B树索引

B 树（Balance Tree），即平衡树的意思。B 树是从平衡二叉树演化而来，B树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。也正因每个节点存储着非常多个关键字，树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。B 树的结构示例如下图所示：

由于 B 树的每一个节点，即每一个磁盘块存储的数据较多，所以一定程度上解决了上文提到的存储尽量多的索引的问题。也一定程度上的解决了存储尽量多的有效索引的问题。

但是，B 树只是一定程度上的解决了问题，我们需要更好的解决问题。即能不能的做到存储更多的有效的索引呢？

答案是可以。这时候就就需要 B+ 树闪亮登场了。

B＋树索引

在 B+ 树中，非叶子节点上是不存储数据的，仅存储键值。

因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16 KB，如果不存储数据，那么节点就可以存储更多的键值，相应的树的阶树就会更大，对于同样的数据量来说，需要的树高就会变低，树会更矮胖，如此一来查找数据的时候进行磁盘的 IO 次数就会减少，提升查询效率。

由于 B+ 树的阶数等于键值数量，假设 B+ 树的一个节点可以存储 1000 个键值，那么 3 层的 B+ 树 可以存储 1000 x 1000 x 1000 = 10亿个数据。并且一般根节点是常驻内存的，所以查找 10 亿个数据，只需要 2 次磁盘 IO。

B+ 这个特点很好的解决了上文提到的存储尽量多的索引数据的问题，并且查询效率也高。

B+ 树的叶子节点中的索引数据是按顺序排列的，并且叶子节点间是通过双向链表进行连接的。

这个特点使 B+ 树在实现范围查找，排序查找，分组查找等操作时变得异常简单。而 B 树由于数据分散在各个节点，要实现这些操作很不容易。

由于索引数据是按顺序排序的，即每次读取了数据页的时候，里面的索引数据大部分都是需要用的，所以也很好的解决了上文提到的如何存储尽量多的有效的索引数据的问题。

InnoDB的索引使用的是B+树实现，B+树对比B树的好处：

• IO次数少：B+树的中间结点只存放索引，数据都存在叶结点中，因此中间结点可以存更多的数据，让索引树更加矮胖；
• 范围查询效率更高：B树需要中序遍历整个树，只B+树需要遍历叶结点中的链表；
• 查询效率更加稳定：每次查询都需要从根结点到叶结点，路径长度相同，所以每次查询的效率都差不多

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配查询索引所有列的查询才有效。

散列表（哈希表）是根据是一种根据(key, value)直接进行访问的数据结构，它通过哈希函数将 key 值映射到散列表对应的位置上，查找效率非常高。

哈希索引的限制

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较查询，包括 =、 IN()、 <⇒(注意 <> 和 <⇒ 是不同的操作)。
• 访问哈希索引的数据非常快，除非有很多哈希冲突。哈希冲突时使用链表来解决哈希冲突。
• 如果哈希冲突很多的话，一些所以维护操作的代价也会很高。冲突越多，代价越大。

空间数据索引（R-Tree）

MyISAM 表支持空间索引，可以用作地理数据存储。空间索引会从所有唯独来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用 MySQL 的 GIS 相关函数如 MBRCONTAINS() 等来维护数据。

全文索引

• 全文索引时一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值
• 全文索引更类似于搜索引擎做的事情，而不是简单的 WHERE 条件匹配
• 全文索引适用于 MATCH AGAINST 操作，而不是普通的 WHERE 条件查询

索引的优点

索引可以快速定位到表的指定位置；可以用作 ORDER BY 和 GROUP BY 操作；某些查询只使用索引就能够完成全部查询。

索引的三个优点：

• 索引大大减少了服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机 I/O 变为顺序 I/O 。

高性能的索引策略

正确地创建和使用索引时实现高性能查询的基础。

索引使用原则

• 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
• 较频繁作为查询条件的字段才去创建索引
• 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
• 定义有外键的数据列一定要建立索引
• 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
• 对于定义为text、image和bit的数据类型的列不要建立索引

独立的列

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

应该养成简化 WHERE 条件的习惯，始终将索引列单独放在比较符合的一侧。

前缀索引和索引选择性

当索引很长的字符列，会让索引变得大且慢，一个策略是前面提到过的模拟哈希索引。

通常可以索引开始的部分字符，可以大大节约索引空间，从而提高索引效率。但这样会降低索引的选择性。

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数的比值(T)，范围从 1/总数 到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于 BLOB、 TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以是的前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。

为了觉得前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

多列索引

一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

在多个列上山里独立的单列索引大部分情况下并不能提高 MySQL 的查询性能。 MySQL 5.0 和更新版本引入了一种“索引合并”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

查询性能优化

查询优化、索引优化、库表结构优化需要齐头并进，一个不落。

为什么查询速度会慢

查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划、锁等待（互斥等待）等操作.

慢查询基础：优化数据访问

查询性能低下最基本的原因是访问的数据太多。

对于低效的查询，下面两步分析总是有效的：

• 确认应用程序是否在检索大量超过需要的数据。访问了太多的行，有时也可能访问太多的列。
• 确认 MySQL 服务器层是否在分析大量超过需要的数据行。

重构查询的方式

在优化有问题的查询时，目标应该是找到一个更优的方法获取实际需要的结果—​而不一定总是需要从 MySQL 获取一模一样的结果集。

• 一个复杂查询还是多个简单查询
• 切分查询
• 分解关联查询