概述

索引（index）是帮助数据库高效获取数据的数据结构（有序） 简言之：索引是数据结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引（见下图用于举例说明的二叉搜索树，如果不使用索引，那么查找的时间复杂度就是O(N)[即顺序查找]，而在二叉搜索树中的查找时间复杂度是O(logN)

使用索引的好处

（1）提高数据检索效率 降低数据库查找数据的IO成本（为了查找到目标行数据 会多次执行IO操作）

（2）通过索引列对数据进行排序，降低数据排序的成本（降低CPU的消耗）

使用索引的代价

（1）牺牲部分存储空间（牺牲部分存储空间 相对硬盘来讲可以忽略不计 因为硬盘的存储空间相对用于存储索引的空间来说大很多）

（2）降低了增删数据的速度 因为在增删数据的时候不仅要增删数据还要连带维护索引（降低增删数据的速度也可以忽略不计 因为DQL的频率远高于DML的频率）

数据结构

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

（1）B+Tree索引 最常见的索引类型，大部分存储引擎都支持B+树索引

（2）Hash索引 底层数据结构是哈希表实现的，只有精确匹配索引列的查询才有效 不支持范围查询

（3）R-tree(空间索引) 空间索引是MyISAM引擎的一个特殊索引类型 主要用于地理空间数据类型 通常使用较少

（4）Full-text(全文索引) 是一种通过建立倒排索引 快速匹配文档的方式 类似于Lucene，Solr，ES

通常提起索引的数据结构，都是指B+Tree

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

（1）相对于二叉树，层级更少，IO次数少，搜索效率高

（2）对于B-tree，无论是叶子结点还是非叶子结点，都会保存数据，这样会导致一页中存储的键值减少，指针跟着减少，要同样保存大量的数据，只能增加树的高度，导致性能降低

（3）相对于Hash索引，B+Tree支持范围匹配及排序操作

索引类型

根据常用类型分类

主键索引 PRIMARY 针对表中主键创建索引 默认自动创建，只能有一个

唯一索引 UNIQUE 避免同一个表中数据列中的重复值

常规索引 快速定位特定数据

全文索引 FULLTEXT 全文索引查找的是文本中的关键词，而不是比较索引中的值

根据索引的存储形式（在InnoDB存储引擎中）

回表查询：先走二级索引拿到行数据的ID，再根据聚簇索引查询到对应的ID所对应的行数据

（1）聚集索引（Clustered Index）：将数据存储与引擎放到了一块 索引结构的叶子结点保存了行数据（必须有，并且只有一个，其实就是主键索引）

（2）二级索引（Secondary Index）：将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键（如常规索引）

思考1：SQL的执行效率

以下哪个SQL的执行效率更高？为什么？

（1）SELECT * FROM user WHERE id = 10; （id为主键）

（2）SELECT * FROM user WHERE name = ‘Jack’;（备注：name字段创建有索引）

第一个执行的效率会更高，因为第二个查询会遍历两个索引（先根据建立在name上的二级索引查询到行数据的key，再根据行数据的key在聚簇索引上找到对应的行数据并返回），而第一个直接根据聚簇索引的key查询到对应的行数据

思考2：InnoDB主键索引的B+Tree高度为多高？

根据既定的知识，B+Tree在存储2000万数据量的数据的时候树高在3以内

如果存储的数据超过2000万行，则树高可能会超过3层，需要考虑分库分表（运维知识）

索引语法

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过show [session当前会话|global全局] status命令可以提供服务器状态信息

可以通过如下指令，查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

SHOW GLOBAL STATUS LIKE 'Com______'; // 7个下划线(字符)

从执行频率上得出的结论是：在SQL优化中，优化的重点一般是查询语句

慢查询日志

查看慢查询日志有没有开启：

mysql> show variables like 'slow_query_log';

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

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

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息：

#重启mysql
systemctl restart mysqld
#查看日志文件中记录的信息
cat /var/lib/mysql/localhost-slow.log

show profiles

准备工作

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

select @@have_profiling;

默认profile操作是关闭的：select @@profiling;

如果需要使用profile操作，则需要将其打开：set profiling = 1;

再次查看是否打开：

使用方法

执行一系列的业务SQL操作，然后通过如下指令查看指令的执行耗时：

（1）查看每一条SQL的耗时基本情况：show profiles;

（2）查看指定query_id的SQL语句各个阶段的耗时情况：show profile for query query_id;

（3）查看指定query_id的SQL语句的CPU使用情况：show profile cpu for query query_id;

explain执行计划

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

使用Explain可以查看sql的性能瓶颈信息，并根据结果进行sql的相关优化。在select 语句前加上explain关键字，执行的时候并不会真正执行sql语句，而是返回sql查询语句对应的执行计划信息。

当然如果select语句的from后面有一个子查询的话，就会执行子查询了并把结果放到一个临时表中。

假设现在有三张表：

# 直接在select语句之前加上关键字explain或者desc
EXPLAIN select * from student s, course c, sc where s.no = sc.s_no AND sc.c_no = c.id;

各个列的含义如下：

索引使用规则

索引对于查询效率的提升

假设一张表存储了1000万行数据，按照id（primary key）查询某一条数据所用的时间约为0.01秒（通过二级索引查找）

而按照没有索引的name字段查询此条数据所用的时间达到约20秒！（全表扫描）

可见索引对查询效率的提升是惊人的！建立索引提升查询效率是必然的选择！

最左前缀法则

如果索引多列（联合索引），要遵循最左前缀法则。最左前缀法则指的是查询要从索引的最左列开始，并且不跳过索引中间的列。如果跳过了某一列，后面的字段索引将失效。

假设当前索引的建立语句为：

CREATE INDEX profession_age_status ON tb_user (profession, age, status );

select * from tb_user where age = 31 and status = '0' and profession = '软件工程'; # 遵循
select * from tb_user where profession = '软件工程' and age = 31; # 遵循
select * from tb_user where profession = '软件工程'; # 遵循
select * from tb_user where age = 31 and status = '0'; # 不遵循（执行计划不会使用联合索引）

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效，而范围查询中如果带了等号=，则不会产生影响

#在这种情况中 status对应的索引将会失效 索引长度为profession和age之和
select * from tb_user where profession = '软件工程' and age < 31 and status = '0';
#在这种情况中 status对应的索引不会失效 索引长度为三个索引之和
select * from tb_user where profession = '软件工程' and age <= 31 and status = '0';

索引失效的情况

情况1 对索引字段进行函数运算

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

explain select * from tb_user where substring(phone, 10, 2) = '15'; # 这种情况下phone对应的索引将会失效

情况2 查询时没有对字符串加单引号’'

select * from tb_user where phone = 15611112222; # 这种情况下phone对应的索引将会失效

情况3 头部模糊查询

# 这种情况下profession索引正常起作用
select * from tb_user where profession like '软件%'; 
# 这种情况下profession索引失效
select * from tb_user where profession like '%软件'; 

情况4 OR连接的使用

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到

explain select * from tb_user where id = 10 or age = 23; # 正常
# 如果age没有索引则phone的索引不会被使用
explain select * from tb_user where phone = '15611112222' or age = 23; 

情况5 数据分布的影响

如果在某条查询语句中使用到了索引 并且MySQL检测到不使用索引的查询效率更快 那么这个时候索引就不会被使用 即索引失效

# 具体走不走索引起决于数据的分布情况 第一条语句下如果表中数据全部为null则不会走索引 而如果只有少量数据为null则会走索引
explain select * from tb_user where profession is null; 
explain select * from tb_user where profession is not null;

SQL提示

指定要使用的索引（建议）：use index(idx_user_pro)

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

忽略要使用的索引（建议）：ignore index(idx_user_pro)

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

强制要使用的索引（建议）：forceindex(idx_user_pro)

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

覆盖索引&回表查询

覆盖索引（只走二级索引就能查到所有的数据）SQL优化的时候要考虑避免回表查询

前缀索引

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

单列&组合索引

单列索引：即一个索引只包含单个列

联合索引：即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑对于查询字段建立索引时，建立联合索引而非单列索引

联合索引的查询流程

联合索引性能相对更高

索引设计原则

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