【MySQL学习笔记】MySQL的索引

1、索引概述

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

索引的优点：
1- 提高数据检索的效率，降低数据库的IO成本；
2- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

索引的缺点：
1- 索引列也是要占用空间的。
2- 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

2、 索引的数据结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

存储引擎对索引的支持情况

2.1 B+Tree索引结构

B+Tree的每个节点存储多个key与指针，假设key的个数为n，则指针的个数为n+1。key用于控制范围，指针指向下一个节点。
在B+Tree中，所有的元素都会出现在叶子结点，且数据只存储在叶子结点，所有的叶子结点形成单向链表。非叶子结点不存储数据，只起到索引的作用。这就使结点中的指针数量增加，树的高度降低。

而MySQL对经典B+Tree进行了优化，增加了一个指向相邻叶子结点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
B+Tree结构示意图：

MySQL索引数据结构的B+Tree：

2.2 Hash索引结构

哈希索引就是采用一定的Hash算法，将键值换算成新的Hash值，映射到对应的槽位上，然后存储在Hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

hash索引在存储的时候是没有顺序的
hash索引特点：
1- hash索引只能用于对等比较（in，=），不支持范围查询（between，>，<，…）
2- 无法利用索引完成排序操作
3- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引
由于hash索引在存储的时候是没有顺序的，导致特点1，2。

在MySQL中，支持hash索引的是Memory存储引擎，而InnoDB中具有自适应Hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

2.3 InnoDB选择B+Tree的原因

为什么InnoDB存储引擎选择使用B+Tree索引结构，而不选择二叉树，红黑树，B-Tree，hash表？
首先要分析二叉树、红黑树、B-Tree存在的问题。
二叉树问题：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
红黑树问题：虽然解决了二叉树形成链表的问题，但本质还是二叉树，大数据量情况下，层级深，检索速度慢。
B-Tree问题：在MySQL中，每个节点存储在一页中，页的大小固定为16k，B树节点中不仅存储了键值，还存储了数据，导致存储的键值减少，指针也减少。要保存同样大小的数据，只能增加树的高度，导致性能降低。
hash表：只能满足等值查询，不支持范围匹配。无法对数据进行排序操作。

因此可以得到以 下三点：
1- 相对于二叉树，红黑树，B+Tree层级少，搜索效率高。
2- 对于B-Tree，无论是叶子结点还是非叶子结点，都会保存数据，而每个节点是保存在一个页中，这样导致一页中存储的键值减少，指针跟着减少，要保存同样大小的数据，只能增加树的高度，导致性能降低；而B+Tree在B-Tree的基础上进行优化，只在叶子结点上存储数据，并且在相邻的叶子结点之间增加指针，形成双向链表，提高性能。
3- Hash索引只能支持等值操作，而B+Tree还支持范围匹配及排序操作。

3、索引分类

索引主要分为四类：

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

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

4、索引的语法

-- 创建索引
-- [unique | fulltext]：指定索引类型，可选
-- index_name：定义索引名，自定义
-- index_col：创建索引的字段，可选多个，如果同时指定多个，则称为联合索引，如果只指定一个，则称为单列索引
create [unique | fulltext] index index_name on table_name (index_col,...);
-- 创建索引时，索引默认是升序排序的，也可以指定索引的排序方式。指定索引的排序方式一般是考虑 order by 优化
create [unique | fulltext] index index_name on table_name (index_col1 asc, index_col2 desc,...);

-- 查看索引
show index from table_name;

-- 删除索引
-- 从 table_name 表中删除 index_name 索引
drop index index_name on table_name;

5、SQL性能分析

SQL性能分析主要是为SQL优化做准备，因此需要明确要为哪类SQL进行优化。一般而言，主要对DQL（也就是查询语句）这类SQL进行优化，对增删改的优化是次要的。

5.1 SQL执行频率

分析SQL执行频率。
MySQL客户端连接成功后，通过下面的命令可以提供服务器状态信息：

-- 查看服务器状态信息
-- session：指当前会话
-- global：指定全局
show [session | global] status;

通过如下指令，可以查看当前数据库的insert、update、delete、select的访问频次：

-- 查看当前数据库增删改查的访问频次
-- Com后面是下划线，一个下划线代表一个字符
-- insert --> com_insert
-- update --> com_update
-- delete --> com_delete
-- select --> com_select
show global status like 'Com_______';

5.2 慢查询日志

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

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢查询日志开关
slow_query_log = 1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2

配置完毕之后，重新启动MySQL服务器进行测试，查看慢查询日志文件中记录的信息 localhost-slow.log，可以知道哪些SQL执行耗时过长，针对这些SQL进行优化。

5.3 profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

-- 查看是否支持profile操作
select @@have_profiling;

默认profiling是关闭的，可以通过 set 语句在 session/global 级别开启profiling：

-- 开启profiling
set [session | global] profiling = 1;

在开启profiling后就可以执行一系列业务SQL操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条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 语句执行过程中表如何连接和连接的顺序。

-- 直接在 select 语句之前加上关键字 explain / desc
explain select 字段列表 from 表名 where 条件;

执行计划会返回很多信息（id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra），下面一一介绍：

explain 执行计划各字段含义：

• id：select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序（ id 相同，执行顺序从上到下，id 不同，值越大，越先执行）。
• select_type：表示 select 的类型，常见的取值有 simple （简单表，即不使用表连接或者子查询）、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 最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则。
最左前缀法则：查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。
要满足最左前缀法则，只需要最左列存在，不关心位置。
举例：
若创建如下联合索引：

-- 在 user 表中根据 name，age，address 这三个字段创建联合索引
-- 最左列为 name ，要使用该索引，则需要存在 name 的条件
create index idx_user_name_age_address on user(name, age, address);

那么不同的查询语句使用 idx_user_name_age_address 索引有多种情况：

-- 使用 idx_user_name_age_address 索引，并且使用到了索引的全字段
select * from user where name = '张三' and age = 18 and address = '北京';

-- 使用 idx_user_name_age_address 索引，但是没有使用address字段，因为sql语句中没有 address 条件
select * from user where name = '张三' and age = 18;

-- 使用 idx_user_name_age_address 索引，只使用了 name 字段，因为sql语句中没有 age 和 address 条件
select * from user where name = '张三';

-- 使用 idx_user_name_age_address 索引，但只使用了 name 字段，因为跳跃了 age 这一列，使后面的字段失效
select * from user where name = '张三' and address = '北京';

-- 使用 idx_user_name_age_address 索引，并且使用了全字段，虽然 name 位置不在第一个，但是存在 name 字段条件
select * from user where age = 18 and address = '北京' and name = '张三';

-- 未使用 idx_user_name_age_address 索引，因为不满足最左前缀法则，条件没有 name 字段。
select * from user where age = 18 and address = '北京';

6.2 范围查询

联合索引中，出现范围查询（<，>），范围查询右侧的列索引失效。
举例：
若创建如下联合索引：

-- 在 user 表中根据 job，age，address 这三个字段创建联合索引
-- 最左列为 job，要使用该索引，则需要存在 job 的条件
create index idx_user_job_age_address on user(job, age, address);

分析如下 sql 语句的索引使用情况：

-- 使用 idx_user_job_age_address 索引，但未使用 address 字段，这是因为 age > 25 为范围查询，范围查询右侧的列索引失效
select * from user where job = '程序员' and age > 25 and address = '北京';

-- 使用 idx_user_job_age_address 索引，并且使用了所有的字段，这是因为 age >= 25 规避了范围查询
select * from user where job = '程序员' and age >= 25 and address = '北京';

从上面的案例中可以受到启发，在项目开发中，在满足业务需求的情况下，尽量使用 >=，<=，而不是用 >，<。

6.3索引失效情况

索引列运算：不要在索引列上进行运算操作，否则索引将失效。

字符串不加引号：字符串类型字段使用时，不加引号，索引将失效。

模糊查询：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

or连接的条件：用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。只有 or 两侧都有索引时，才会生效。

数据分布影响：如果MySQL评估使用索引比全表扫描慢，则不使用索引。

6.4 SQL提示

SQL 提示是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。这个提示是指开发者给MySQL的提示。
语法：
在表后面加 use/ignore/force index(指定index)

-- use index：建议使用指定索引，但MySQL可以根据实际情况分析，是否使用该索引
explain select * from user use index(idx_user_name) where name = '张三';

-- ignore index: 忽略索引，不使用指定索引
explain select * from user ignore index(idx_user_name) where name = '张三';

-- force index：强制使用指定索引
explain select * from user force index(idx_user_name) where name = '张三';

6.5 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到）。减少使用 select * 的情况。

覆盖索引是研究返回字段对查询性能的影响。

研究如下几条 SQL，他们的 where 条件相同，但返回的字段不同：
前提：创建了如下几个索引

-- 在 user 表上给 job，age，gender 字段创建了联合索引 idx_user_job_age_gender 
create index idx_user_job_age_gender on user(job, age, gender);
-- 在 user 表上给 name 字段创建索引 idx_user_name 
create index idx_user_name on user(name);

分析 SQL：

-- 执行计划 extra 字段提示：using where; using index
-- 因为建立了 job，age，gender 这三个字段联合索引，联合索引中包含了查询返回的 id 和 job 数据，只需要一次二级查询即可。
explain select id, job from user where job = '程序员' and age = 25 and gender = 1;

-- 执行计划 extra 字段提示：using where; using index
-- 因为建立了 job，age，gender 这三个字段联合索引，联合索引中包含了查询返回的 id，job，age，gender 数据，只需要一次二级查询即可。
explain select id, job, age, gender from user where job = '程序员' and age = 25 and gender = 1;

-- 执行计划 extra 字段提示：using index condition
-- 虽然建立了 job，age，gender 这三个字段联合索引，但是索引列中不包含 name 字段，查询返回 name 字段需要根据 id 进行回表查询，性能降低。
explain select id, job, age, gender, name from user where job = '程序员' and age = 25 and gender = 1;

-- 执行计划 extra 字段提示：using index condition
-- * 通配表中所有字段，而 idx_user_job_age_gender 索引中只包含 id，job，age，gender数据，因此需要回表查询。
explain select * from user where job = '程序员' and age = 25 and gender = 1;

知识补充：
using index condition：查找使用了索引，但是需要回表查询数据。
using where; using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。
using where; using index 的性能高于 using index condition

6.6 前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

建立前缀索引的语法：

-- idx_xxx：自定义索引名
-- table_name：表名
-- column：字段，一般为varchar，text等类型
-- n：前缀长度，截取字符串的长度
create index idx_xxx on table_name(column(n))

如何确定前缀？
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
可以通过下面的 sql 来计算选择性;

-- 这里以 user 表中的 email 字段为例，使用 email 的全部字符计算选择性
select count(distinct email) / count(*) from user;
-- 截取 email 字段的前5个字符计算选择性
select count(distinct substring(email, 1, 5)) / count(*) from user;

6.7 单列索引与联合索引

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

7、索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少使用单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也越大，会影响增删改的效率。
7. 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。