索引¹

1 索引概述

1.1. 介绍

索引（index）是帮助MySQL 高效获取数据的数据结构 (有序) 。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1.2. 演示

备注：上述二叉树索引结构只是一个示意图，并不是真实的索引结构

1.3. 索引优缺点

优点
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
缺点
- 占用磁盘空间
- 提高了查询的效率，降低了写操作的速度（INSERT UPDATE DELETE）

2 索引结构

2.1. 常见索引结构对比

2.2. 存储引擎和索引类型

平时说的索引，没有特别说明，都指 B+树结构组织的索引

2.3. 索引数据结构的进化

二叉树、红黑树
二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。红黑树：大数据量情况下，层级较深，检索速度慢。
B-Tree（多路平衡查找树）
以一棵最大度数（max-degree）为5（5阶）的b-tree为例（每个节点最多存储4个key，5个指针）
B-Tree演示
插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 250
参考网站
B+Tree
以一棵最大度数（max-degree）为4（4阶）的b+tree为例
B+Tree演示
插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 250
参考网站
对比B-Tree
1. 所有数据都会出现在叶子节点
2. 叶子节点形成一个单向链表
MySQL中B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能

2.4. Hash索引

Hash
哈希索引就是采用hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中
如果两个（或多个）键值，映射到一个相同的槽位上，它们就产生了hash冲突（也称hash碰撞），可以通过链表来解决。
hash索引特点
- hash索引只能用于对等比较(==、in)，不支持范围查询(between、>、<等)
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以，效率通常要高于B+Tree索引
存储引擎支持
在MySQL中，支持hash索引的是 Memory 引擎
InnoDB中具有自适应的hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

2.5. 为什么InnoDB存储引擎选择使用B+Tree索引结构？

相对于二叉树和红黑树，层级更少，搜索效率更高
对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
相对于Hash索引，B+Tree支持范围匹配及排序操作

3 索引分类

3.1. 分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

3.2. InnoDB 的索引分类

根据索引的存储形势分两类

分类	含义	特点
聚集索引（Clustered Index）	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有且只有一个
二级索引（辅助索引）（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以有多个

聚集索引选取的规则：
如果存在主键，主键索引就是聚集索引
不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个 rowid 作为隐藏的聚集索引

回表查询

3.3. InnoDB主键索引的B+Tree高度多少?

假设：
一行数据大小为1k，一页中可以存16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8
当高度为2时：
n *8 + (n+1) * 6 = 16 * 1024 => n ≈ 1,170
1171 * 16 = 18,736
当高度为3时：
1171 * 1171 * 16 = 21,939,856

4 索引语法

4.1. 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX 索引名 ON 表名 (字段1,字段2,...);

4.2. 查看索引

SHOW INDEX FROM 表名;

4.3. 删除索引

DROP INDEX 索引名 ON 表名;

5 SQL性能分析

5.1. SQL执行频率

MySQL 客户端连接成功后，可通过指令查询数据库的操作次数
SHOW GLOBAL  STATUS  LIKE 'Com_______'

5.2. 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志

更改慢查询配置
- MySQL 默认关闭慢查询日志，需要在配置文件（/etc/my.cnf）中配置
  1 2 3 4 # 开启MySQL慢查询日志开关 slow_query_log=1 # 设置慢查询的判断阈值为 2秒 long_query_time=2
- 配置完后重启MySQL服务
- 慢查询日志记录/var/lib/mysql/localhost-slow.log
查询是否配置成功
```
SHOW VARIABLES LIKE 'slow_query_log';
```
日志示例

5.3. profile

profile详情
show profiles 能够在做SQL优化是帮助我们了解时间都消耗到哪里去了
- 查看MySQL数据库是否支持 profile操作
```
SHOW @@have_profiling;
```
- 默认 profiling是关闭的，可通过set 语句在 session/global 级别开启profiling
```
-- 查看profiling
SELECT @@profiling;
-- 设置profiling
SET profiling=1;
```
profile的使用
- 查看每条sql的执行情况
```
SHOW profiles;
```
- 查看指定 query_id 的SQL语句各个阶段耗时
```
SHOW profile FOR query query_id;
```
- 查看指定 query_id 的SQL语句CPU使用情况
```
SHOW profile CPU FOR query query_id;
```

5.4. explain执行计划

概述
EXPLAIN 或者 DESC 命令获取MySQL如何执行 SELECT 语句信息，包括在 SELECT 语句执行过程中表如何连接和连接顺序

语法

-- 直接在查询语句前加上关键字 EXPLAIN/DESC
EXPLAIN 查询语句;

EXPLAIN 字段含义
- Id
  select查询的序列号，表示查询中执行select子句或者是操作表的顺序
  id大的先被执行，id相同按查询出的顺序从上往下执行
- select_type
  表示select的类型，常见的取值有 SINPLE（简单表，即不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
- type
  表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
- possible_key
  显示可能应用在这张表上的索引，一个或多个
- key
  实际使用的索引，如果为NULL，则没有使用索引
- key_len
  表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确度的前提下，长度越短约好
- rows
  MySQL认为必须要执行查询的行数，在InnoDB引擎中，是一个估计值，可能并不总是准确的
- filtered
  表示返回结果的行数占读取行数的百分比，filtered 的值越大约好
- Extra
  额外信息

6 索引使用

6.1. 验证索引效率

创建索引前
创建索引后

6.2. 索引使用原则

最左前缀法则
最左前缀法则：查询从索引的最左列开始，并且不跳过索引中的列，如果跳过某一列，索引将部分失效（后面的字段索引失效）
如果索引了多列（联合索引），要遵守最左前缀法则。
范围查询
联合索引中，出现范围查询（<、>）,范围查询右侧的列索引失效
规避：业务允许的情况下尽量使用`>=`、`<=`
索引列运算
不要在索引列上进行运算操作，否则索引将失效
```
EXPLAIN SELECT * FROM tb_user WHERE SUBSTRING(10,2) = '15';
```
字符串不加引号
字符串类型字段使用时，不加引号，索引将失效
模糊查询
尾部模糊匹配，索引不会失效
头部模糊匹配，索引会失效
OR连接
or 连接的查询条件都有索引索引才会生效
规避：为没有索引的 or 查询条件建立索引
数据分布影响
当查询的数据占全表比重较大则不走索引

SQL提示

SQL提示是优化数据库的重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

use index（建议使用指定索引）

explain select * from tb_user use index(idx_user_pro) where profession='软件工程';

ignore index（不要使用指定索引）

explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';

force index（必须使用指定索引）

explain select * from tb_user force index(idx_user_pro) where profession='软件工程';

覆盖索引&回表
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少 select *
提示：
1. using index condition：查找使用了索引，但是需要回表查询
2. using where;using index ：查找使用了索引，查询列在索引能找到，不需要回表查询
前缀索引

当字段类型为字符串（varchar、text 等）时，有时候需要索引很长的字符串，索引会变的很大，浪费磁盘IO，影响查询效率。
可将字符串的一部分前缀，建立索引
建立后索引 sub_part 列 = 字段前缀长度

语法

CREATE INDEX 索引名 ON 表名(字段名(字段前缀长度));

确定前缀长度
可根据索引的选择性来决定
选择性计算：不重复索引值（基数）/表的记录总数
值越大约好，唯一索引值为 1
```
SELECT COUNT(DISTINCT email/COUNT(*) FROM tb_user;
SELECT COUNT(DISTINCT SUBSTRING(email(1,5))/COUNT(*) FROM tb_user;
```
前缀索引执行流程
单列索引与联合索引
单列索引：即一个索引只包含单个列
联合索引：即一个索引只包含多个列
在业务场景中，如果存在多个查询条件，建议使用联合索引
多条件联合查询时，MySQL优化器会评估哪个字段的索引更高，会选择该索引完成本次查询
联合索引

7 索引设计原则

7.1. 原则

对数据量大（百万级及以上），且查询频繁的表建立索引
对常作为查询条件（WHERE）、排序（ORDER BY）、分组（GROUP BY）操作的字段建立索引
尽量选择区分度高的列作为索引（如：唯一索引），索引效率高
如果是字符串型字段，字段长度较长的话，可针对字段的特点，使用前缀索引
尽量使用联合索引，减少单列索引，尽量使用索引覆盖，避免回表
要控制索引数量，索引越多，维护索引结构的代价也就越大，影响删改的效率
如果索引列不能存储NULL，请在创建表时用 NOT NULL 约束它。当优化器知道每列是否包含NULL时，它可以更好的确定哪个索引最有效地用于查询

参考黑马程序员 ↩

索引

索引1

1 索引概述

1.1. 介绍

1.2. 演示

1.3. 索引优缺点

2 索引结构

2.1. 常见索引结构对比

2.2. 存储引擎和索引类型

2.3. 索引数据结构的进化

2.4. Hash索引

2.5. 为什么InnoDB存储引擎选择使用B+Tree索引结构？

3 索引分类

3.1. 分类

3.2. InnoDB 的索引分类

3.3. InnoDB主键索引的B+Tree高度多少?

4 索引语法

4.1. 创建索引

4.2. 查看索引

4.3. 删除索引

5 SQL性能分析

5.1. SQL执行频率

5.2. 慢查询日志

5.3. profile

5.4. explain执行计划

6 索引使用

6.1. 验证索引效率

6.2. 索引使用原则

7 索引设计原则

7.1. 原则

相关文章

MySQL概述

SQL

MySQL函数

索引¹