MySQL进阶 | 索引性能调优 | 数据入门到精通

无知的我正在复习MySQL进阶知识。。。。

笔记特点是 我重新整理了涉及资料的一些语言描述、排版，而使用了自己比较容易理解的描述、同样是回答了一些常见关键问题

如果有遇到有任何无法进展问题或者疑惑的地方，应该在讨论区留言 或者 其他途径以寻求及时的帮助，以加快学习效率 或者 培养独立解决问题的能力、扫清盲点、补充细节

目录 索引索引 介绍索引 作用索引 结构结构类型二叉树B-TreeB+TreeB+Tree——在MySQLHash——在MySQL 索引 分类索引 分类思考题 索引 语法索引 性能分析SQL执行频率慢查询日志profileexplain 索引 使用规则最左前缀法则范围查询索引失效情况索引列运算字符串不加引号模糊查询or连接条件数据分布影响 SQL 提示覆盖索引&回表查询前缀索引单列索引&联合索引 索引 设计原则

索引 索引 介绍

🕜 是什么

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

索引 作用

🕜举例说明

假如要执行的SQL语句为 ： select * from user where age = 45;

无索引情况

在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很

低。

有索引情况

如果针对于这张表建立了索引（假设索引结构就是二叉树）那么，会对age这个字段建立一个二叉树的索引结构。如下图

此时在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

补充注意： 这里只是假设索引的结构是二叉树，介绍一下索引的大概原理，只是一个示意图，并不是索引的真实结构，索引的真实结构，后面会详细介绍。

🕜优点

提高数据检索效率，降低数据库的IO成本通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗=>就是提高了查询和排序效率

缺点

索引列也是要占用空间的。其存放在.MYI、.ibd为后缀的文件中。

（在业务场景中，这个问题可以忽略，这是因为磁盘很便宜）

索引降低了更新的速度（比如 INSERT、UPDATE、DELETE）。这是因为在进行这些 *** 作时，也要维护索引的结构（就是树）

索引 结构 结构类型

索引结构	描述
B+Tree	最常见的索引类型。大部分引擎都支持B+树索引
Hash	底层数据结构是用哈希表实现。在应用场景中，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)	空间索引是 MyISAM 引擎的一个特殊索引类型。在应用场景中，主要用于地理空间数据类型，通常使用较少
Full-Text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式——类似于 Lucene, Solr, ES

🕜引擎支持情况

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

二叉树

🕜B+ Tree 意义——用二叉树和红黑树举例说明

🕜引出问题 如果主键是顺序插入的，则会形成一个单向链表 。如下图。

根据上文，得出会存在以下缺点：

当顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。这是因为每个父结点只允许最多两个孩子

🕜解决上一个缺陷的办法是 引入红黑树

引出问题 当数据量大时，层级较深，检索速度慢

所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree B-Tree

🕜是什么

是一种多叉路衡查找树相对于二叉树，B树每个节点可以有多个分支，即多叉。

举例说明

以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针

🕜构造 B-Tree——举例说明

可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html。如下

选择最大度数为5（就是任意结点指针数不能超过5）

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88120 268 250 。

然后观察一些数据插入过程中，节点的变化情况。

可以知道其特点

5阶的B树，每一个节点最多存储4个key，对应5个指针。一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂——就是移动到上一层的结点中，原本的结点分类成两份而形成下一层。在B树中，非叶子节点和叶子节点都会存放数据。注意 结点的值是有序的。发生裂变后会自动排序 B+Tree

🕜是什么——举例说明

是B-Tree的变种，以一颗最大度数（max-degree）为4（4阶）的b+tree为例。结构如下

可以知道 B-Tree 相比

所有的元素、元素数据 都在存放在叶子结点中叶子结点 形成了一个单向链表

🕜构造B+Tree——举例说明

可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

选择最大度数为5（就是任意结点指针数不能超过5）

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。

然后观察一些数据插入过程中，节点的变化情况。

所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。（每次裂变中间元素不仅向上移动，还在仍然存在于形成的下一层） B+Tree——在MySQL

🕜是什么

在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+Tree。结构如下

结点存放在数据块中（就是页）

优点

提高区间访问的性能，利于排序。

Hash——在MySQL

🕜是什么

采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。结构如下

hash冲突的结构

——如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

🕜特点

Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）无法利用索引完成排序操作查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引支持hash索引的是Memory存储引擎。InnoDB中具有自适应hash功能。这是因为hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

思考题： 为什么InnoDB存储引擎选择使用B+tree索引结构?

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

索引 分类 索引 分类

🕜常规分类

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

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

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

🕜聚集索引和二级索引的具体结构如下

聚集索引的叶子节点下挂的是数据表中这一行的数据。二级索引的叶子节点下挂的是该字段值对应的主键值。

🕜聚集索引选取规则

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

🕜二级索引选取规则

除了聚集索引（只能有一个）之外，其他都是选取的都是二级索引

🕜当执行如下的SQL语句时，具体的查找过程是什么样子的

①先到二级索引查找。由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②再根据二级索引找到的主键值去聚焦索引查找。由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③最终找到后，拿到这一行的数据，直接返回。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式

思考题

🕜以下两条SQL语句，那个执行效率高? 为什么?

A. select * from user where id = 10 ;

B. select * from user where name = ‘Arm’ ;

//备注: id为主键，name字段创建的有索引；

解答：

A 语句的执行性能要高于B 语句。

这是因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

🕜InnoDB主键索引的B+tree高度为多高呢？

假设:

一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，如果主键类型为bigint，占用字节数为8。

高度为2（n为key的数量）

非叶子结点的大小：n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170

1171* 16 = 18736 //@16是最大结点个数；@1171是指针最大数量（也就是下一层最大结点数）

也就是说，如果树的高度为2，则可以存储 18000 多条记录。

高度为3

1171 * 1171 * 16 = 21939856

也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

索引 语法

🕜语法

创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);
如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引

查看索引
SHOW INDEX FROM table_name;

删除索引
DROP INDEX index_name ON table_name;

案例说明

-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);

-- phone手机号字段的值非空，且唯一，为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);

-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);

-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);

-- 删除索引
drop index idx_user_email on tb_user;

索引 性能分析

🕜意义

主要优化查询，也就是主要优化索引

SQL执行频率

🕜意义

为了知道查询 *** 作在 *** 作中占比，如果占比不大，那么就优化侧重就要对应地调整

🕜 *** 作

查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：
-- session 是查看当前会话 ; 
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

或
SHOW SESSION STATUS LIKE 'Com_______';

例
show global status like 'Com_______';

多少个下划线代表多少个字符 慢查询日志

🕜意义

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

🕜 *** 作

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

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

#更改后记得重启MySQL服务
systemctl restart mysql

#查看慢查询日志开关状态：
show variables like 'slow_query_log';

#日志文件位置
/var/lib/mysql/localhost-slow.log

profile

🕜意义

能在做SQL优化时帮我们了解时间都耗费在哪里。

🕜 *** 作

#通过 have_profiling 参数，能看到当前MySQL 是否支持 profile  *** 作
SELECT @@have_profiling;

#profiling 默认关闭，可以通过set语句在session/global级别开启 profiling
SET profiling = 1;

#查看所有语句的耗时：
show profiles;

#查看指定query_id的SQL语句各个阶段的耗时
show profile for query query_id;

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

explain

🕜意义

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

🕜语法

#直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;

🕜案例说明

🕜EXPLAIN 各字段含义

索引 使用规则 最左前缀法则

🕜前提

如果索引关联了多列（联合索引），要遵守最左前缀法则

🕜最左前缀法则指的是

所有的字段索引生效。在执行查询语句时，查询条件的值是从索引的最左列开始，并且不跳过索引中的列。后面的字段索引失效。查询条件的值是从索引的最左列开始，如果跳跃某一列，虽然前面的索引依然会生效，但是后面的字段索引失效。 范围查询 在联合索引中，出现范围查询（<, >）条件，在其出现位置的右侧的所有列索引失效。解决办法是 替换为>=或者<=来规避索引失效问题。 索引失效情况 索引列运算

在索引列上进行运算 *** 作，索引将失效。

如：explain select * from tb_user where substring(phone, 10, 2) = '15';

字符串不加引号

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

如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号

模糊查询

模糊查询中

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

如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。

or连接条件

用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。

数据分布影响

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

SQL 提示

🕜是什么

是优化数据库的一个重要手段

🕜 *** 作

简单来说，就是在SQL语句中加入一些人为的提示来达到优化 *** 作的目的。

例如，使用索引
explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
#use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改

指定不使用哪个索
explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";

指定必须使用哪个索引
explain select * from tb_user force index(idx_user_pro) where profession="软件工程";
#force是无论如何都强制使用该索引

覆盖索引&回表查询

🕜是什么

尽量使用覆盖索引，减少 select *。// 那么什么是覆盖索引呢？查询使用了索引，并且需要返回的列，在该索引中已经全部能找到

🕜举例说明

🕜explain 中 extra 字段含义：

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

结合上图实际情况

由于第一条SQL要查找的字段都是使用了索引，所以其值在二次索引直接得到由于第二条SQL要查找的字段“name”没有使用索引，所以该字段值需要回表查询得到

🕜覆盖索引&回表查询 过程 举例说明

id是主键，是一个聚集索引。name字段建立了普通索引，是一个二级索引（辅助索引）

执行SQL : select * from tb_user where id = 2;

根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

执行SQL：selet id,name from tb_user where name = ‘Arm’;

虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高

执行SQL：selet id,name,gender from tb_user where name = ‘Arm’;

由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。

面试题：一张表，有四个字段（id, username, password, status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案？

select id, username, password from tb_user where username='itcast';

解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引

前缀索引

🕜问题

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率

🕜解决办法

此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

🕜语法

create index idx_xxxx on table_name(columnn(n));

示例:

为tb_user表的email字段，建立长度为5的前缀索引

create index idx_email_5 on tb_user(email(5));

🕜前缀长度 如何选择？

可以根据索引的选择性来决定，索引选择性越高则查询效率越高。@选择性是指 不重复的索引值（基数）和 数据表的记录总数的比值比如 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

🕜求选择性公式

select count(distinct email) / count(*) from tb_user; #@distinct 先除重再统计


select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

show index 里面的sub_part可以看到接取的长度

🕜 前缀索引 查询流程图

注意在这次中，不是第一次回表查询就返回查询到的数据，而是继续比较还未比较完的字段值——对比下一个结点如果相同，那么继续查找如果不相同，那么返回查询到的数据。这是因为这意味着不可能存在其他前缀索引相同的行数据 单列索引&联合索引

🕜是什么

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

🕜在业务场景中

如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况（此时phone、name都建立了索引，但不是联合索引）
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';
结果是只会用到phone索引字段

注意事项

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。有时候联合索引存在，但是系统仍然自动选择单列索引查询，这时就需要自己指定索引类型了。

🕜联合索引查询 过程图

索引 设计原则 针对于数据量较大（比如超过了100万条数据），且查询比较频繁的表建立索引。针对于常作为查询条件（where）、排序（order by）、分组（group by） *** 作的字段建立索引尽量选择“区分度高”（如身份z号等等）的列作为索引，尽量建立唯一索引。这是因为区分度越高，使用索引的效率越高。（区分度低 如姓名、性别、状态、逻辑删除）如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引尽量使用联合索引，减少单列索引。这是因为查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率要控制索引的数量，索引并不是多多益善。这是因为索引越多，维护索引结构的代价就越大，会影响增删改的效率如果索引列不能存储NULL值，需要在创建表时使用NOT NULL约束它。这是因为当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询