数据库索引

索引

为什么索引能提高查询速度

数据 存储、查询原理

• 数据在磁盘 存储结构类似链表
  查询需要 线性搜索（Linear Search）的过程，
  平均需要访问 N/2 的数据块

索引 存储、查询原理

1. 选择数据中的一个字段 作为索引字段。
2. 创建额外的一个索引数据结构，包含索引字段的值 以及 指向相关记录的指针，
3. 然后对这个索引结构 根据索引字段的值进行排序

• 索引将无序的数据变成相对有序的，
  运用二分查找（Binary Search），这样只要访问log2 (N)的数据块

查询数据时 会把所有记录读取出来,然后与查询条件对比. I/O开销大时间长

• 索引能提高查询速度
• 索引会降低DML语句速度

索引数据结构：B树索引,B+树索引,Hash索引

参考: https://zhuanlan.zhihu.com/p/27700617

1. 平衡二叉树
   基于二分法原理, 左小右大
2. B树
   平衡多路查找树（查找路径/子节点 不只两个，区分多个段）
3. B+树
   只拿 叶子节点 保存指针数据，每次查询的次数都一样
   层级更少, 让查询速度更加稳定
4. hash索引：查询单条快，范围查询慢

• 主流的还是使用B+树索引
• 哈希索引 没办法利用索引完成排序, 不支持最左匹配原则, 不支持范围查询,
  在有大量重复键值情况下，哈希索引有哈希碰撞问题效率也是极低。

索引种类：聚集索引/主键索引, 非聚集索引/二级索引

• 聚集索引
  1. 以主键创建的索引
  2. 在叶子节点存储的是表中的数据
• 非聚集索引
  1. 以非主键创建的索引
  2. 在叶子节点存储的是主键和索引列 (拿到主键再查找其他数据 这个过程叫做回表)
  3. 可以多个列来创建索引*(联合索引)

回表, 覆盖索引

非聚集索引的叶子节点存储的是主键+索引列值,最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢

如果要查询的数据和索引列对应匹配, 就不用再次查询, 这就是覆盖索引

联合索引-最左匹配原则

多个列创建索引, 涉及到哪个列会走索引，哪个列不走索引的问题

1. 索引只能用于查找key是否存在（相等）不能是范围匹配
2. 从左向右匹配,以最左边的为起点任何连续的索引都能匹配, 遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配
3. mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。

如多列索引(a, b, c, d)

a = 1 and b = 2 and c > 3 and d = 4
--会命中a、b、c，无法命中d

a = 1 and c = 3
--会命中 a

b = 2
c = 3
d = 4
--没有从最左边开始，没有命中索引，用的是全表扫描 

• 二级索引 / 辅助索引
  二级索引的叶子节点存储的数据是主键，通过二级索引，可以定位主键的位置
  • 唯一索引(Unique Key)
    不能出现重复的数据，但是允许数据为NULL， 可以有多个
  • 普通索引(Index)
    为了快速查询数据，可以有多个
  • 前缀索引(Prefix)
    只对文本的前几个字符创建索引，只适用于字符串类型
  • 全文索引(Full Text)
    检索大文本数据中的关键字的信息

相关sql语句

查看索引

show index from {table_name};

解释函数explain

看我们的SQL是否走索引

explain sql语句;
 
+----+-------------+---------+------------+-------+---------------+--------+-----------+-------------+------------+-------------------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key    |  key_len  |    ref      |    rows    |   filtered        | Extra |
+----+-------------+---------+------------+-------+---------------+--------+-----------+-------------+------------+-------------------+-------+
|  1 | 简单/复杂查询 | 查询的表 | NULL       | 操作类型| 所用到的索引    |采用的索引| 索引字段长度|索引哪一列被使用|估算读取的行数|按表条件过滤的行百分比| 补充信息|

• type
  　　type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > req_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能够达到ref。

类型	说明
all	将遍历全表 以找到匹配的行
index	遍历索引树
range	检索给定范围的行【一般是在where语句中出现between、<、>、in等的查询，只需要开始于索引的某一点，结束于另一点】
ref	非唯一性索引扫描【匹配某个单独值的所有行】
eq_ref	唯一性索引扫描【每个索引键，表中只有一条记录与之匹配，主键/唯一索引】
const	只会有一行匹配，mysql会把这个查询转换为一个常量，即通过索引一次就找到了数据
system	表只有一行记录

• possible_keys
  列出所有 涉及到查询字段的索引，但不一定被查询使用

• key
  实际使用的索引