MySQL学习[2] ——MySQL索引

二、MySQL索引

2.1 什么是索引？

索引的界说就是资助存储引擎快速获取数据的一种数据结构，索引是数据的目录。利用空间换取时间，能够极大加速数据库的查询服从。
索引和数据一样，位于MySQL的存储引擎层。
2.2 索引的分类

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
  
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
  
• 按「字段特性」分类：主键索引、唯一索引、平凡索引、前缀索引。
  
• 按「字段个数」分类：单列索引、联合索引。
  

2.2.1 按数据结构分类

索引本身是通过一种数据结构来实现的，根据数据结构的差别，可以分为：B+树索引、Hash索引、Full-Text索引等。每种存储引擎支持的索引类型也差别，InnoDB中默认利用B+树作为索引的数据结构。
2.2.2 按物理存储分类

从物理存储的角度，索引分为**聚簇索引（主键索引）、二级索引（辅助索引）**，这两个的紧张区别在于：

• 主键索引的B+树叶子节点中存放的是实际数据，所有的记载都存放在主键索引的B+树叶子节点中；
  
• 二级索引的B+树的叶子节点存放的是主键值，必要再通过主键去查找实际数据。
  

2.2.3 按字段特性分类

从字段特性分类，索引可以分为：主键索引、唯一索引、平凡索引、前缀索引。

• 主键索引：建立在主键上的索引。一张表只能有一个主键索引（渐渐只能有一个），不能重复，不允许空值NULL
  
• 唯一索引：UNIQUE字段上建立的索引。一张表可以有多个唯一索引，允许存在空值
  
• 平凡索引：在平凡字段上建立的索引，对字段没什么要求
  
• 前缀索引：对字符类型（char、varchar、binary、varbinary）的字段的前几个字符建立的索引，而不是在整个字段上建立的索引
  

2.2.4 按字段个数分类

从字段个数上来看，索引分为单列索引、联合索引。

• 单列索引：建立在单个列上的索引，如主键索引；
  
• 联合索引：建立在多个列上的索引
  

2.3 为什么利用B+树作为索引？

2.3.1 B+Tree 索引的存储和查询的过程

B+树是一种**多叉搜刮树，只有叶子节点才会存放实际数据，非叶子节点只会存放索引**，每个节点中数据是按照主键的顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在**叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表**。
数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点看成一次磁盘 I/O 操作。B+Tree 存储千万级的数据只必要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多必要 3-4 次磁盘 I/O，所以**B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询服从很高，由于纵然在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。**
会先检二级索引中的 B+Tree 的索引值，找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。
不外，当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到（说明查询的数据就是索引），这时就不消再查主键索引查，这种在二级索引的 B+Tree 就能查询到效果的过程就叫作「覆盖索引」，也就是只必要查一个 B+Tree 就能找到数据。
2.3.2 InnoDB为什么利用B+树作为索引？

B+树相对于B树、二叉树、Hash表的优势：

• B+树 vs B树
  与B树相比，B+树只在叶子节点中存放数据，因此B+树单个节点可以存放更多的索引数据，可以使得树的层数更小。即能够在雷同的I/O次数下，查询到更多的节点。
  另外，B+树的叶子节点通过双链表连接，很得当MySQL中的范围查询，B树无法做到这一点。
  
• B+树 vs 二叉树
  二叉树的一个父节点只能有两个子节点，搜刮复杂度为O(logN)，当节点许多时会导致二叉树的层数很高，检索到目标数据所经历的磁盘I/O次数更多。
  对于B+树最大子节点个数为d个，实际应用中就保证了纵然数据达到千万级，B+树的高度依然在3~4层左右。
  
• B+树 vs Hash表
  Hash表在做等值查询时服从非常快，搜刮复杂度为O(1)。但是Hash表很难做范围查询。因此B+Tree 索引要比 Hash 表索引有着更广泛的适用场景。
  

2.4 联合索引是怎么发挥作用的？

2.4.1 什么是联合索引？

通过将多个字段组合成一个索引，这个索引就叫做联合索引。
联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 雷同的情况下再按 name 字段比较。这是**最左匹配原则，按照最左优先的方式举行索引的匹配。在利用联合索引举行查询的时间，假如不遵照「最左匹配原则」，联合索引会失效**，这样就无法利用到索引快速查询的特性了。
2.4.2 最左匹配原则

创建了一个 (a, b, c) 联合索引，假如查询条件是以下这几种，就可以匹配上联合索引：

1. where a=1；
2. where a=1 and b=2 and c=3；
3. where a=1 and b=2；

复制代码

  由于有查询优化器，所以 a 字段在 where 子句的顺序并不紧张，但在理论上必要用尺度化写法：按照顺序。
  但是，假如查询条件是以下这几种，由于不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

1. where b=2；
2. where c=3；
3. where b=2 and c=3；

复制代码

这是由于**利用索引的条件是索引里的 key 是有序的**，对于联合索引，在保证 a 字段相等时，才能使得b有序。
2.4.3 联合索引的范围查询

当联合索引中对某个字段利用了范围查询时，会导致联合索引到这个「范围查询」就会停止匹配。也就是范围查询的字段（及之前）可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。这也是由于**利用索引的条件是索引里的 key 是有序的**决定的。
   联合索引的最左匹配原则，在碰到范围查询（如 >、<）的时间，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配
  2.4.4 联合索引的索引下推

由于无法继续联合索引的匹配，在MySQL早期版本，必要「回表」。通过联合索引中范围查询之前的字段快速定位到符合条件的索引条目对应的主键值（假如是覆盖索引可以直接得到数据）。 利用获取到的主键值回到原始数据表中，进一步读取必要的其他列数据。这个步调必要再次访问数据表，称为 “回表”。
显然，回表会带来肯定的性能损失。因此在 MySQL 5.6 引入了**索引下推优化**，可以在联合索引遍历过程中，对联合索引中包罗的字段先做判定，直接过滤掉不满足条件的记载，减少回表次数。
2.4.5 联合索引的索引区分度

由于「最左匹配原则」，建立联合索引的顺序对索引服从有很大的影响。实际开辟工作中，必要把区分度大的字段排在前面，可以很早地过滤掉大部分不符合的数据。区分度就是某个字段 column 差别值的个数「除以」表的总行数，计算公式如下：

2.5 什么时间必要/不必要索引？

索引紧张目标是加快查询，但也不可制止地增长了内存占用和维护数据库表的难度，应有选择的利用。
2.5.1 索引的优缺点

长处：

• 可以大幅度加快数据的查询速度（大部分情况下比全表扫描快得多）；
  
• 唯一性索引可能保证该字段中数据的唯一性；
  

缺点：

• 带来了额外的空间斲丧。一个索引就是一颗B+树，B+树的每一个节点就是一个16KB的页，占用内存大；
  
• 索引的创建和维护会耗费许多时间，对表举行增删改时对应必要修改索引中的内容，降低了服从。
  

2.5.2 什么时间必要创建索引

• 表很大，数据许多，全表扫描性能差；
  
• 频繁用于 WHERE 查询条件的字段；
  
• 频繁利用排序（order by）或分组（group by）的字段；
  
• 必要保证唯一性的字段。
  

2.5.3 什么时间不必要创建索引

• 表数据比较小，全局扫描也不会有太大延迟；
  
• 很少用于 WHERE 查询条件、排序（order by）或分组（group by）的字段；
  
• 经常必要更新的字段最好不要创建索引，带来维护的性能瓶颈；
  
• 存在大量重复数据的字段（区分度不大，如性别等）。
  

2.6 优化索引的方法？

• 前缀索引优化：对于字符串，可以通过字符串的前几个字符建立索引而不是整个，可以减小索引字段的大小；
  
• 覆盖索引优化：尽量让查询的字段都在索引中，这样可以制止回表，减少大量的I/O操作；
  
• 主键索引最好是自增的：每次插入一条新记载，都是追加操作，不必要移动数据，不会产生页分裂；
  
• 索引最好是NOT NULL：索引列存在NULL值时会导致优化器更加难以优化查询的执行，且NULL值会占用物理空间
  
• 防止索引失效：制止写出索引失效的查询语句
  

   常见扫描类型的执行服从从低到高的顺序为：
  

• All（全表扫描）；
  
• index（全索引扫描）；
  
• range（索引范围扫描）；
  
• ref（非唯一索引扫描）；
  
• eq_ref（唯一索引扫描）；
  
• const（效果只有一条的主键或唯一索引扫描）。
  

  2.6 索引失效有哪些？

查询条件用上了索引列，查询过程不肯定都用上索引，接下来再一起看看哪些情况会导致索引失效，而发生全表扫描。
2.6.1 对索引利用左模糊或左右模糊匹配

利用左大概左右模糊匹配的时间，也就是 like %xx 大概 like %xx% 这两种方式都会造成索引失效。**由于索引 B+ 树是按照「索引值」有序分列存储的，只能根据前缀举行比较。**利用带有左模糊的匹配时，无法确定前缀，则无法根据顺序查找，只能全局扫描。
2.6.2 对索引利用函数或表达式计算

在查询条件中对索引举行表达式计算，也是无法走索引的。例如：

1. select * from user where length(name)=6;  # 函数
2. selecr * from user where id + 1 = 10;          # 表达式，改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了。

复制代码

原因是建立的**索引是对字段的原始值的**，而不是对函数值或表达式计算后的值（id + 1），后面得到的值固然无法利用索引匹配。
2.6.3 对索引隐式类型转换

例如，在表中phone的类型是varchar，但是查询语句如下：

1. select * from user where phone = 1300000001;

复制代码

这样就**导致字段phone发生了隐式类型转换，则无法通过索引匹配**，而是全局扫描。
   特例：从字符串转换成整型不会。由于MySQL 在碰到字符串和数字比较的时间，会主动把字符串转为数字，然后再举行比较。所以其作用对象不是字段，而是整型。
  2.6.4 利用联合索引但不满足最左匹配

创建了联合索引，在利用时必要满足最左匹配原则，否则索引会失效，举行全局扫描。
2.6.5 WHERE字句中的OR

在 WHERE 子句中，假如在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
这是由于 OR 的含义就是**两个只要满足一个即可，因此只有一个条件列是索引列是没故意义的**，只要有条件列不是索引列，就会举行全表扫描。
2.7 MySQL 单表不要超过 2000W 行，靠谱吗？

   

• 非叶子节点内指向其他页的数量为 x
  
• 叶子节点内能容纳的数据行数为 y
  
• B+ 数的层数为 z
  

  MySQL的表数据是以页（记载是行）的形式存放的，页在磁盘中不肯定一连。 页的空间是 16K ,并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等，大概必要1K左右的空间，剩下的15K用来存放数据。
索引页中紧张记载的是主键与页号，主键我们假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte。
所以 x=15*1024/12≈1280 行。
叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。但是叶子节点中存放的是真正的行数据，这个影响的因素就会多许多，好比，字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少。
暂时按一条行数据 1k 来算，那一页就能存下 15 条，y = 15*1024/1000 ≈15。
Total =x^(z-1) *y，已知 x=1280，y=15：

• 假设 B+ 树是两层，那就是 z = 2， Total = （1280 ^1 ）*15 = 19200
  
• 假设 B+ 树是三层，那就是 z = 3， Total = （1280 ^2） *15 = 24576000 （约 2.45kw）
  

一样平常 B+ 数的层级最多也就是 3 层，能够存储两千多万行的数据。假如数据过多，导致三层的B+树无法完全存储，就会导致**B+树的层数增长，则在查询时会导致多一次的I/O操作，造成性能降落**。
2.8 count(*) 和 count(1) 有什么区别？哪个性能最好？

2.8.1 count()聚合函数比较

count() 是一个聚合函数，函数的参数不但可以是字段名，也可以是其他任意表达式，该函数作用是**统计符合查询条件的记载中，函数指定的参数不为 NULL 的记载有多少个**。

• count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。
  
• count(主键字段)必要遍历索引读取主键值，根据主键值是否为NULL来增长计数；而count(*) 和 count(1) 虽然也遍历索引，不必要读取任何字段的值，所以速度更快。
  
• count(平凡字段) 由于该字段不是索引，所以必须通过全局扫描的方式，性能最差。
  

2.8.2 为什么必要遍向来计数？

MyISAM 引擎时，执行 count 函数**只必要 O(1 )复杂度**，这是由于每张 MyISAM 的数据表都有一个 meta 信息有存储了row_count值，由表级锁保证一致性，所以直接读取 row_count 值就是 count 函数的执行效果。
InnoDB存储引擎是支持事务的，同一个时候的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表**“应该返回多少行”也是不确定的（差别版本效果差别）**，所以无法像 MyISAM一样，只维护一个 row_count 变量。
2.8.2 怎样优化count(*)？

• 近似值：利用 show table status 大概 explain 命令来表举行估算
  
• 额外维护一个表，来统计计数；
  

资料参考

内容大多参考自：图解MySQL先容 | 小林coding (xiaolincoding.com)

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。