526互联

MySQL_索引

发布时间 2023-11-24 18:23:52作者: Espre-sso

索引的分类

  1. 按数据结构：B+树，Hash，Full-text。
  2. 按物理存储：聚簇（主键），二级（辅助）。
  3. 字段特性：主键，唯一，普通，前缀。
  4. 字段个数：单列，联合。

按数据结构-B+树索引

除此之外还有：Hash，Full-text
回表：
要查找2个B+树才能找到数据（二级索引-得到主键值-主键索引-得到数据）。
覆盖索引：
在二级索引的B+树中就能查询到结果。（在二级索引树中找主键）
SELECT id FROM table WHERE name='张三';

按物理存储

聚簇索引： 叶子节点存放数据。
二级索引： 叶子节点存放主键值。（可能回表 or 覆盖索引）

按字段特性

  1. 主键索引：创建表时一起创建。不允许空，只能有一个。建表时：PRIMARY KEY
  2. 唯一索引：值必须唯一，可以为空，可以有多个。建表时：UNIQUE KEY，建表后：CREATE UNIQUE INDEX index_name ON
  3. 普通索引：建表时：INDEX，建表后：CREATE INDEX index_name ON
  4. 前缀索引：建表时：INDEX(column_name(Length))，建表后：CREATE INDEX index_name ON

按字段个数

  1. 单列索引：
  2. 联合索引（复合索引）：
    1. 最左匹配原则。（a，b，c）联合索引，where c=1 and a=2; 中c会索引失效，因为没有用到b。b，c是全局无序，局部有序的。利用索引的前提是索引里的key是有序的。因为有查询优化器，所以a的顺序不重要。
    2. 联合索引的最左匹配原则，（a，b）在遇到范围查询的时候（< , > ），就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询后面的字段无法用到联合索引，注意：对于<= , >= ,BETWEEN, like前缀匹配的范围查询，并不会停止匹配。（原因：> ：扫描的边界条件a>2，与b无关。>=：扫描的边界条件：a=2 AND b=1，与b有关）
    3. 索引下推：在联合索引的遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。MySQL5.6之后。例如WHERE a>1 AND b=2; 看b是否=2，直接在联合索引中判断，而不是回主键索引中判断。
    4. 索引区分度：建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的SQL使用到。（因为MySQL有一个查询优化器，查询优化器发现某个值出现在表的数据行中的很高的时候（惯用百分比界限是30%），它一般会忽略索引，进行全表扫描）
    5. 联合索引进行排序：SELECT * FROM table WHERE status=1 ORDER BY time acs；怎么通过索引提高查询效率？给status和time建立一个联合索引，这样可以避免发生Using filesort，因为通过status筛选后的数据是按照time排好序的。

什么时候适用索引？

  1. 字段有唯一性限制。
  2. 经常用WHERE查询的字段。
  3. 经常用于GROUP BY和ORDER BY的字段。

什么时候不需要创建索引？

  1. WHERE，GROUP BY，ORDER BY里用不到的字段。
  2. 字段中存在大量重复数据。
  3. 表数据太少。
  4. 经常更新的字段。

有什么优化索引的方式？

  1. 前缀索引优化：减少索引字段的大小。
    局限：
    1. ORDER BY 无法使用前缀索引。
    2. 无法将前缀索引用作覆盖索引。
  2. 覆盖索引优化：不用查询出包含整行记录的所有信息，也就减少了大量IO操作。
  3. 主键索引最好是自增的：插入记录是追加操作，不需要移动数据。（如果是非自增，随机插入，页分裂，内存碎片）
  4. 索引最好设置为not null：因为可以使索引更加复杂，null没意义但是会占用物理空间。
  5. 防止索引失效：

    1. 左模糊匹配 OR 左右模糊匹配。

    2. 在查询条件时对索引进行计算、函数、类型转换。

    3. 联合索引未遵循最左匹配原则。

    4. WHERE子句中，OR前是索引列，OR后不是索引列。
      TIP：可以通过explain查看执行计划，重点看type字段（找到所需数据使用的扫描方式是什么）

      1. All 全表扫描
      2. index 全索引扫描
      3. range 索引范围扫描
      4. ref 非唯一索引扫描
      5. eq_ref 唯一索引扫描
      6. const 结果只有一条的主键或唯一索引扫描
        通常使用c级别及以上的扫描方式。
        e，f的区别：e用于多表联查，f用于与常量进行比较。

      TIP：也要关注extra字段：
      a. Using filesort 需要排序（GROUP BY），但是无法通过索引进行排序时，使用排序算法进行排序。效率低。
      b. Using temporary 使用了临时表保存中间结果。（ORDER BY，GROUP BY）效率低。
      c. Using index 使用了覆盖索引，避免了回表。效率不错。
      d. Using index condition 使用了索引下推，直接在存储引擎层进行过滤，再返回给Server层，减少回表次数。

从数据页的角度看B+树

InnoDB是按数据页（16KB）为单位读写数据的。
数据页结构：
文件头，页头，最大、最小记录，用户记录，空闲时间，页目录，文件尾 7部分

  1. 文件头：有2个指针，指向上一个数据页和下一个数据页。
  2. 用户记录：数据页中的记录按照主键顺序组成单向链表。
  3. 页目录：快速索引用户记录的作用。
    创建页目录的过程：
    1. 将所有记录分组。
    2. 每个组的最后一条记录，它的头信息记录n_owned(该组一共有多少条记录)。
    3. 页目录存储每组最后一条记录的地址偏移量（也称为槽slot）。
      通过槽查找记录的过程：
      1.通过二分法定位到在哪个槽 -> 2.遍历槽中所有记录，找到对应的记录。
      Q：如果槽内数据很多，查找的时间复杂度会不会变成O(n)？
      A：不会，因为每个分组的记录条数是有规定的（第1个分组1条，最后一个分组1-8条，剩下的分组4-8条）

聚簇索引和二级索引

按物理存储分类。

  1. 聚簇索引：叶子节点存放的是实际数据。
    因为表的数据是存放在聚簇索引的叶子节点里，所以InnoDB存储一定会为表创建一个聚簇索引。且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。
    创建聚簇索引：
    1. 如果有主键，默认使用主键。
    2. 如果没有主键，选择第一个不包含NULL值的唯一列。
    3. 如果上述两种情况都不满足，InnoDB自动生成一个隐式自增id。
  2. 二级索引：叶子节点存放的是主键值。
    1. 回表：某个查询语句使用了二级索引，但查询的数据不是主键值，这时二级索引找到主键值后，需要到聚簇索引中获得数据行。要查询2个B+树。
    2. 覆盖索引：查询的数据是主键值时，不需要再去聚簇索引查。只要查一个B+树。

为什么MySQL使用B+树作索引？

Q：怎样的索引的数据结构是好的？
  1. 少的磁盘IO。（操作系统：1个扇区512B，Linux1个块4KB 8个扇区，操作系统读写的最小单位是块）
  2. 高效的范围查找。
InnoDB存储引擎使用B+树的原因：
  1. 非叶子节点仅存放索引，更矮胖，磁盘IO更少。
  2. 大量冗余节点（非叶子节点是冗余索引），插入、删除效率高。不会像B树那样发生复杂的树的变化。
  3. B+树的叶子节点通过双向链表连接起来，有利于范围查询。

MySQL单表不要超过2000W行，靠谱吗？

靠谱！Total =x^(z-1) *y

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

一个页16KB，x存索引1280条，y存数据15行，z层数3，计算大约2000W
2000W只是推荐值，超过这个值可能会使B+树层数更高，影响查询性能。

联合索引-最左匹配-索引截断-索引下推

联合索引（a，b，c） 查询条件 WHERE a=1 AND c=2 属于索引截断
不同版本处理方式不一样

  1. MySQL5.5：a会走索引，找到主键值后，开始回表，到主键索引读取数据行给Server层，在Sever层再比对c的值。
  2. MySQL5.6之后：索引下推，在存储引擎层进行索引遍历的过程中，对索引中包含的字段进行判断，直接过滤掉不满足条件的记录，再返回给Server层，减少回表的次数。（用explain查看时，Extra=Using index condition 表示使用了索引下推功能）

索引失效有哪些？

  1. 左模糊匹配like %xx，左右模糊匹配like "%xx%"。
  2. 对索引列使用函数。
  3. 对索引列进行表达式计算。
  4. 索引列发生隐式类型转换。MySQL遇到字符串和数字比较时，自动把字符串转换成数字，然后再进行比较，如果此时字符串是索引列，那么索引列会发生隐式类型转换（通过CAST函数实现的），等同于对索引列使用了函数。
  5. 联合索引没有遵循最左匹配原则。
  6. WHERE字句中，OR前是索引列，OR后不是索引列。（解决：将OR后条件列也设置为索引列，此时explain可以发现type = index merge，意思是对条件列a和b分别进行了扫描，然后将这两个结果集进行了合并，这样的好处是避免了全表扫描）

MySQL使用 like "%xx" ，索引一定会失效吗？

不一定
关键要看数据表中的字段，如果数据库表中的字段只有主键+二级索引，那么即使使用了左模糊匹配，也不会走全表扫描 type=all ，而是走全扫描二级索引树 type=index
Q：为什么选择全扫描二级索引树，而不是聚簇索引树？
A：1.因为二级索引树叶子节点包括(索引值+主键值)，查二级索引B+树就能查到全部结果了，这就是覆盖索引。2.二级索引树记录的东西少，只有(索引值+主键值)，聚簇索引记录的东西就多了(事务id，回滚指针...)3.再加上这个SELECT *不用回表
TIP:
这个数据表加了非索引字段，执行相同的查询语句后，走的就是全表扫描了。
与之相似：
联合索引不遵循最左匹配原则，如果数据库表中的字段都是索引的话，也会走全扫描二级索引树 type=index

count(*)和count(1)有什么区别？哪个性能最好？

按照性能排序：
count(*) = count(1) > count(主键字段) > count(字段)

A：count()是什么？

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

count(主键字段)的执行过程是怎样的？

Server层维护一个count的变量，Server层循环向InnoDB读取一条记录，如果(主键字段)不为空，count+1，循环结束后，将count变量的值发送给客户端。

  1. 只有主键索引：循环遍历聚簇索引。读取id值（假设主键值是id），如果不是NULL，count+1。
  2. 有二级索引：遍历二级索引。（因为成本小）
count(1)的执行过程？

和count(主键值)类似，但由于1恒不为NULL，所以不用读取记录。

  1. 只有主键索引：循环遍历聚簇索引，不用读取。
  2. 有二级索引：遍历二级索引。
count(*)的执行过程？

等于count(0)，和count(1)的执行过程基本一样。（0恒不为NULL）
count(*)的优化：有多个二级索引的时候，优先使用key_len最小的二级索引进行扫描。

count(字段)的执行过程？

会采用全表扫描，效率最差。

为什么通过遍历的方式来计数？
  1. InnoDB需要通过遍历的方式，而MyISAM不需要，直接读取数据表中的row_count，O(1)，这个值通过表锁保证一致性。
  2. InnoDB支持事务，同一时刻的多个查询，count返回多少行是不确定的（由于MVCC），所以count函数需要扫描表。
  3. 当带上WHERE条件语句后，MyISAM和InnoDB就没有区别了，都需要扫描表。
如何优化count(*)？

就算创建了二级索引，数据量大的时候，执行一次count(*)耗时也是很长的。
解决办法：

  1. 近似值。使用show table status或explain（执行explain的效率是很高的，因为它不会真正的去查询）。
  2. 额外表保存计数值。