MySQL索引原理

目录

• 内容概述
• 内容详细
  • 1.什么是索引？
  • 2.使用索引的好处
  • 3.索引分类
  • 4.索引的数据结构
    • 二叉查找树
    • 平衡二叉树
    • B树
    • B+树
  • 5.索引管理
    • 创建/删除索引的语法
    • 索引示例
  • 6.正确的使用索引
    • 范围问题
    • 查询优化神器-explain

内容概述

1.什么是索引？
2.使用索引的好处
3.索引分类
4.索引的数据结构
5.索引管理
6.正确的使用索引

内容详细

1.什么是索引？

索引是存储引擎中的一种数据结构，或者说数据的组织方式，又称为键key，是存储引擎用于快速找到记录的一种数据结构。

为数据建立索引好比为字典创建音序表，如果要查字，不用音序表的话查询非常困难，建立索引就是这个道理

索引是数据的组织方式，将数据按索引规定的结构组织成一种树型结构，该树叫B+树

2.使用索引的好处

在生产环境中我们遇到最多且容易出问题的是一些比较复杂的查询操作，所以我们要对查询语句进行优化，要加速查询的话，就需要用到索引。

关于索引：
索引并不是越多越好，并且最好提前创建索引

# 索引多了可能会影响到数据库写操作，硬盘IO变高
如果某一张表的ibd文件中创建了很多索引树，意味着进行写操作时（update语句），多个索引树都要发生变化，从而导致硬盘IO变高。

索引的本质就是不断地缩小数据的范围选出最终想要的结果，同时将随机事件变成顺序事件。

知识储备:
# 磁盘的预读
计算机访问一个地址的数据时，与其相邻的数据也会很快被访问到。
每一次IO读取的数据我们称为一页（page），具体一页有多大数据跟操作系统有关，一般为4K到8K，也就是读取一页内的数据时，实际才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

查询优化：
# SQL层面
添加适当的索引
优化SQL语句的逻辑，尽量使查询所涉及的行变少

# 架构层面
MySQL集群
主从复制
读写分离
MySQL集群前加入Redis缓存

# 补充：
等值查询：	where语句 =
范围查询：	where语句 < > ...

3.索引分类

#===== B+树索引（InnoDB存储引擎默认）
聚集索引：即主键索引，primary key
用途：
1.加速查找
2.约束（不为空、不能重复）

唯一索引：unique
用途：
1.加速查找
2.约束（不能重复）

普通索引：index
用途：
1.加速查找

联合索引：
primary key(id,name):联合主键索引
unique（id,name):联合唯一索引
index(id,name):联合普通索引

#===== HASH索引（哈希索引，查询单条块，范围查询较慢）
原理：
将数据打散再去查询
InnoDB和Myisam都不支持，设置完还是B树
Memory存储引擎支持

# 值得注意的是，InnoDB存储引擎的内存中的架构中包含一个自适应哈希索引，这个自适应哈希索引是InnoDB为了加速查询性能而自动创建的

#===== FULLTEXT：全文索引 （只可以用在MyISAM引擎中）
通过关键字的匹配来进行查询，，类似于like的模糊匹配
like + %在文本比较少时是合适的，但对于大量的文本数据检索会非常慢
全文索引在大量的数据面前能比like块得多，但是准确度很低

#===== RTREE：R树索引
RTREE在mysql很少使用，仅支持geometry数据类型
geometry数据类型一般填写经纬度那样的数据
支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
RTREE范围查找很强，但Btree也不弱

# 不同的存储引擎支持的索引类型也不一样
InnoDB支持事务，支持行级别锁定，支持B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory不支持事务，支持表级锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB支持事务，支持行级别锁定，支持Hash索引，不支持B-tree、Full-text 等索引；

Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

4.索引的数据结构

InnoDB存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的

二叉查找树

提取每一条记录的id值作为key值，value为本行完整记录

id	user
10	zs
7	ls
13	ww
5	zl
8	xw
12	xm
17	dy

以key值的大小为基础构建二叉树，如上图所示
二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。
顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找id=12的用户信息
select * from user where id=12;

查找流程如下：
1.将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点
2.继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左字节点作为当前节点
3.把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=1>2,name=xm

# 利用二叉树我们需要3次即可找到匹配的数据，如果在表中一条条的查找的话，我们需要6次才能找到。

平衡二叉树

我们回到二叉查找树的特点上，只论二叉查找树，它的特点只是：任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。
# 但某种情况下二叉查找树变成了链表，这种现象会导致二叉查找树变得不平衡，也就是高度太高，从而导致查找效率的不稳定。

为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度不能超过1。
下图进行对比：

由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。

具体的调整方式这里就不介绍了。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

B树

平衡二叉树会因为数据量变大而导致树的高度变高，在查找数据时会进行很多次的磁盘IO，查找数据的效率也会变得极低。

综上，我们要在平衡二叉树的基础上，把更多的节点放入一个磁盘块中，那么平衡二叉树的弊端也就解决了，即构建一个单节点可以存储多个键值对的平衡树，这就是B树

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的节点，子节点的个数一般为阶。

上图中的B树为3阶B树，高度也会很低，基于这个特性，B树查找数据读取磁盘的次数就会很少，数据库的查找效率也会比平衡二叉树高很多。

# B树的不足：对于范围查询，或者说排序操作，B树也不能做得很好。

B+树

B+树是对B树的进一步优化。让我们先来看下B+树的结构图：

1.B+树非叶子节点non-leaf node上是不存储数据的，仅存储键，而B树的非叶子加点中不仅存储键，也会存储数据。B+树之所以这么做的意义在于：树一个节点就是一个页，而数据库中页的大小是固定的，innodb存储引擎默认一页为16KB，所以在页大小固定的前提下，能往一个页中放入更多的节点，相应的树的阶数（节点的子节点树）就会更大，那么树的高度必然更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

2.B+树的阶数是等于键的数量的，例如上图，我们的B+树中每个节点可以存储3个键，3层B+树存可以存储3*3*3=27个数据。所以如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。而一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO，真是屌炸天的设计。

3、因为B+树索引的所有数据均存储在叶子节点leaf node，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
而且B+树中各个页之间也是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。

通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

5.索引管理

创建/删除索引的语法

# 方法一：创建表时
create table 表名 (
    字段名1 数据类型 [完整性约束条件...],
    字段名2 数据类型 [完整性约束条件...],
    [unique | fulltext | spatial] index | key
    [索引名] (字段名[(长度)] [asc | desc])
    );
    
案例：
mysql> create table t11 (id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t11\G
*************************** 1. row ***************************
       Table: t11
Create Table: CREATE TABLE `t11` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

# 方法二：create在已存在的表上创建索引
create [unique | fulltext | spatial ] index 索引名 on 表名 (字段名[(长度)] [asc | desc]);

案例：
mysql> create unique index index12 on t12 (id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t12\G
*************************** 1. row ***************************
       Table: t12
Create Table: CREATE TABLE `t12` (
  `id` int(11) DEFAULT NULL,
  UNIQUE KEY `index12` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

# 方法三：alter table 在已存在的表上创建索引
alter table 表名 add [unique | fulltext | spatial] index 索引名 (字段名[(长度)] [asc | desc]);

案例：
mysql> create table t13(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t13 add unique index index13 (id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t13\G
*************************** 1. row ***************************
       Table: t13
Create Table: CREATE TABLE `t13` (
  `id` int(11) DEFAULT NULL,
  UNIQUE KEY `index13` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

# 删除索引
drop index 索引名 on 表名字；
alter table 表名字 drop index 索引名字；

# 删除主键索引
alter table 表名字 drop primary key;

案例：
mysql> drop index index13 on t13;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t13\G
*************************** 1. row ***************************
       Table: t13
Create Table: CREATE TABLE `t13` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

# 查看索引
方法一：
mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

方法二：
mysql> show index from t12\G
*************************** 1. row ***************************
        Table: t12
   Non_unique: 0
     Key_name: index12
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

索引示例

# 主键索引（聚集索引）
# 创建主键索引

mysql> alter table student add primary key pri_id(id);
mysql> create table student(id int not null, primary key(id)); 
mysql> create table student(id int not null primary key auto_increment comment '学号');
# 注意：
database 可以写为 schema
index 可以写为 key

# 唯一键索引
# 创建唯一键索引

mysql> alter table country add unique key uni_name(name);
mysql> create table student(id int unique key comment '学号');
mysql> create unique key index index_name on table_name(id);

# 普通索引（辅助索引）
# 普通索引的创建

mysql> alter table student add index idx_gender(gender);
mysql> create index index_name on table_name (column_list);

# 创建前缀索引
按照该列数据的前n个字母创建索引
mysql> alter table student add index idx_name(name(4));

# 全文索引
# 针对content做了全文索引：
CREATE TABLE t1 (
id int NOT NULL AUTO_INCREMENT,
title char(255) NOT NULL,
content text,
PRIMARY KEY (id),
FULLTEXT (content));

查找时：
select * from table where match(content) against('想2查询的字符串');

6.正确的使用索引

并不是创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们添加索引时，必须遵循以下问题。

范围问题

条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like

# 如果没有明确指定到底找哪个字段的值，而是指定了一个范围，如果这个范围比较大，那么则跟全表扫描没有多大区别

where语句后跟< > != 或 between...and...或 like
# like关键字当使用%前置的时候，是无法命中索引的，进而查询速度很慢，所以企业中应尽量避免使用%前置

查询优化神器-explain

explain命令具体用法和字段含义可以参考官网explain-output,这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外）。所以优化语句基本都是在优化rows。

字段详解
1.id：
包含一组数字，表示查询中执行select子句或操作表的顺序

Example（id相同，执行顺序由上至下）
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id越大，优先级越高，越先执行。

2.select_type
表示查询中每个select子句的类型（简单or复杂）
a. SIMPLE：查询中不包含子查询或者UNION
b. 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c. 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d. 在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
e. 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f. 从UNION表获取结果的SELECT被标记为：UNION RESULT

SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。
DEPENDENT意味着select依赖于外层查询中发现的数据。
UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。

第一行：id列为1，表示第一个select，select_type列的primary表 示该查询为外层查询，table列被标记为<derived3>，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。
第二行：id为3，表示该查询的执行次序为2（ 4 => 3），是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。
第三行：select列表中的子查询，select_type为subquery，为整个查询中的第二个select。
第四行：select_type为union，说明第四个select是union里的第二个select，最先执行。
第五行：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。

3、 type   
表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index,  range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

ALL：Full Table Scan，    

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

4、table
显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

5、possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

6、Key      key列显示MySQL实际决定使用的键（索引）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。（注：索引是否命中）

7、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好  

8、ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 

9、rows
 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
 
10、Extra
该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。