Skip to content

Latest commit

 

History

History
131 lines (107 loc) · 6.46 KB

MysqlIndex.md

File metadata and controls

131 lines (107 loc) · 6.46 KB

一. MySQL索引

1. 索引作用

加快查找数据的速度

  • 减少服务器扫描行数。
  • 避免进行排序和分组，以及避免创建临时表
  • 将随机 I/O 变为顺序 I/O。

2. 应用场景

  1. 适用中大型表；
  2. 不适用小表和超大表。因为小表没必要建索引，超大表的维护代价太高。

3. 聚簇索引 VS 非聚簇索引

聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录。

InnoDB引擎中主键使用的是聚簇（主）索引，非主键索引用非聚簇（辅助）索引。 MyISAM不管是主键还是非主键索引使用的都是非聚簇索引。

  • 聚簇索引： 叶子节点 data 域记录着完整的数据记录。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
  • 非聚簇索引： 叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

二. MySQL的索引类型和原理

MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等，它们的原理如下：

1. B+ Tree索引

B+ Tree索引是MySQL存储引擎的默认索引。 可以用于查找，分组，排序。

1.1 B+ Tree 特点

B+ Tree 是一棵平衡M叉查找树。

1.2 B+ Tree VS B Tree

  1. B+ Tree中叶子节点会记录所有的值，非叶子节点的作用是指定范围，不会保存值。
  2. B Tree中的非叶子节点会记录值。
  • B+ Tree的优点:
    • 顺序访问叶子节点，减少单次查询的磁盘访问次数，来提高区间查询的性能。

1.3 B+ Tree VS 红黑树

  1. 更少的查找次数 红黑树是二叉树，导致同样数量的data，红黑树的高度会大于B+树。
  2. 减少磁盘寻道 因为磁盘不是严格读取数据，而是会有预读的情况。顺序读取的过程中不需要磁盘寻道，速度更快。

2. 哈希索引

哈希索引是基于哈希表实现的，只支持等值比较的查询，不支持任何范围查询。 哈希索引中会存储每行记录的哈希值和数据的指针，不存储字段值。

2.1 优缺点

  1. 优点：速度快，只需要很小索引就可以为比较长的key创建索引。
  2. 缺点：
  3.  无法排序，不支持范围查询：因为不是按照值的存储顺序存储的

  4.  不支持匹配查找：因为存储的是哈希值

  5.  不能利用部分索引键查询

  6.  哈希冲突比较多的时候，维护代价高

2.2 InnoDB的自适应哈希索引

当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

  • 特点：由InnoDB决定是否创建，用户无法控制或者配置，可以选择关闭该功能。

2.3 创建自定义的哈希索引

如果引擎不支持哈希索引，可以自己创建。

  • 应用场景：索引key比较大，只需要精准查找。 例如，需要存储大量的URL，并根据URL进行查找。 这时可以
  1. 在建表的时候建一个url_crc字段； 注意，不要用SHA1和MD5作为哈希函数，因为加密后长度太长；可以用CRC32，但是如果表比较长，可以自己实现一个64位哈希算法。
  2. 触发器，在插入和更新时维护url_crc字段；
  3. 避免冲突: select的时候,where条件中除了带哈希值，还需要带对应的列值,如下
select * from user where crc = CRC32('https://waiting.com') and url = 'https://waiting.com'

3. 全文索引

3.1 什么是全文索引

全文索引用于查找文本中的关键词，而不是直接比较是否相等。

3.2 为什么引入全文索引

  • 文本比较少情况下，可以用like + % 实现模糊查询；
  • 大量文本检索的时候，需要用全文索引，因为他的速度是like的N倍。

3.3 全文索引的条件

  • 引擎：MyISAM 或者 InnoDB(version>5.6)
  • 支持全文索引的数据类型：char、varchar、text

MySQL 5.6.4版本以后的InnoDB和所有的MyISAM 版本支持全文索引。 利用倒排索引实现的。

4. 空间数据索引

空间数据索引会从所有维度来索引数据，主要用于地理数据存储。 MyISAM 存储引擎支持空间数据索引（R-Tree）。

三. 索引优化

1. 各列的含义如下:

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type: SELECT 查询的类型.
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息

select_type

select_type 表示了查询的类型, 它的常用取值有:

  • SIMPLE, 表示此查询不包含 UNION 查询或子查询
  • PRIMARY, 表示此查询是最外层的查询
  • UNION, 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • SUBQUERY, 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY, 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

type

type 判断此次查询是全表扫描还是索引扫描等.

type 类型的性能比较, 不同的type类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system 其中：

  • ALL 是全表扫描,是速度最慢的.
  • index 是扫描了所有的索引, 比 ALL 类型稍快.
  • 后面的几种类型都是利用了索引来查询数据,可以过滤大部分数据, 因此效率比较高.

extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

  • Using filesort 当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
  • Using index "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.