Mysql进阶：引擎和索引

引擎：

 

InnoDB

1). 介绍

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后， InnoDB 是默认的

MySQL 存储引擎。

2). 特点

DML 操作遵循 ACID 模型，支持事务；

行级锁，提高并发访问性能；

支持外键 FOREIGN KEY 约束，保证数据的完整性和正确性；

3). 文件

xxx.ibd ： xxx 代表的是表名， innoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结

构（ frm- 早期的 、 sdi- 新版的）、数据和索引。

参数： innodb_file_per_table

4). 逻辑存储结构

 

表空间 :

InnoDB 存储引擎逻辑结构的最高层， ibd 文件其实就是表空间文件，在表空间中可以

包含多个 Segment 段。

段 :

表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。 InnoDB 中对于段的管

理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。

区 :

区是表空间的单元结构，每个区的大小为 1M 。 默认情况下， InnoDB 存储引擎页大小为

16K ， 即一个区中一共有 64 个连续的页。

页 :

页是组成区的最小单元， 页也是 InnoDB 存储引擎磁盘管理的最小单元 ，每个页的大小默

认为 16KB 。为了保证页的连续性， InnoDB 存储引擎每次从磁盘申请 4-5 个区。

行 :

InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时

所指定的字段以外，还包含两个隐藏字段 ( 后面会详细介绍 ) 。

MyISAM

1). 介绍

MyISAM 是 MySQL 早期的默认存储引擎。 2). 特点

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

3). 文件

xxx.sdi ：存储表结构信息

xxx.MYD: 存储数据

xxx.MYI: 存储索引

1.3.3 Memory

1). 介绍

Memory 引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为

临时表或缓存使用。

2). 特点

内存存放

hash 索引（默认）

3). 文件

xxx.sdi ：存储表结构信息

1.3.4 区别及特点 特点

 

面试题 :

InnoDB 引擎与 MyISAM 引擎的区别 ?

① . InnoDB 引擎 , 支持事务 , 而 MyISAM 不支持。

② . InnoDB 引擎 , 支持行锁和表锁 , 而 MyISAM 仅支持表锁 , 不支持行锁。

③ . InnoDB 引擎 , 支持外键 , 而 MyISAM 是不支持的。

主要是上述三点区别，当然也可以从索引结构、存储限制等方面，更加深入的回答，具体参

考如下官方文档：

https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

1.4 存储引擎选择 在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据

实际情况选择多种存储引擎进行组合。

InnoDB:

是 Mysql 的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要

求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操

作，那么 InnoDB 存储引擎是比较合适的选择。

MyISAM ：

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完

整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY ：

将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。 MEMORY 的缺陷就是

对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

索引：

不索引全局扫描 加索引进行针对

索引（ index ）是帮助 MySQL 高效获取数据的数据结构 ( 有序 ) 。在数据之外，数据库系统还维护着满足

特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构

上实现高级查找算法，这种数据结构就是索引。

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

 

如果选择二叉树

     作为索引结构，会存在以下缺点：

     顺序插入时，会形成一个链表，查询性能大大降低。

     大数据量情况下，层级较深，检索速度慢。

 由于红黑树

    也是一颗二叉树，所以也会存在一个缺点：

     大数据量情况下，层级较深，检索速度慢

B-Tree，

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

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

个指针：

 

 

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

es/visualization/BTree.html

B+Tree

B+Tree 是 B-Tree 的变种，我们以一颗最大度数（ max-degree ）为 4 （4阶）的 b+tree 为例，来看一

下其结构示意图：

 

上述我们所看到的结构是标准的 B+Tree 的数据结构，接下来，我们再来看看 MySQL 中优化之后的

B+Tree 。

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点 的链表指针，就形成了带有顺序指针的B+Tree ，提高区间访问的性能，利于排序。

Hash：

MySQL 中除了支持 B+Tree 索引，还支持一种索引类型 ---Hash 索引。

1). 结构

哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在

hash 表中

如果两个 ( 或多个 ) 键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为 hash 碰撞），可

以通过链表来解决。

特点

A. Hash 索引只能用于对等比较 (= ， in) ，不支持范围查询（ between ， > ， < ， ... ）

B. 无法利用索引完成排序操作

C. 查询效率高，通常 ( 不存在 hash 冲突的情况 ) 只需要一次检索就可以了，效率通常要高于 B+tree 索

引

存储引擎支持

在 MySQL 中，支持 hash 索引的是 Memory 存储引擎。 而 InnoDB 中具有自适应 hash 功能， hash 索引是

InnoDB 存储引擎根据 B+Tree 索引在指定条件下自动构建的。

 

 

 

 索引分类：

聚集索引&二级索引：

二级索引下面有一个字段指向的Id 当查询到不是二级索引中的数据 那么就进行回表查询根据Id进行查询聚集索引 查询其他的字段  

 

聚集索引选取规则 :

如果存在主键，主键索引就是聚集索引。 如果不存在主键，将使用第一个唯一（ UNIQUE ）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索

引。

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

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

数据的方式，就称之为回表查询。

 

 语法：

SQL性能分析 ：

SQL执行频率：

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

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

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据

库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以

查询为主，那么就要考虑对数据库的索引进行优化了

 

 慢查询日志：

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

SQL 语句的日志。

MySQL 的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log 。

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

# 开启 MySQL 慢日志查询开关

slow_query_log = 1

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

long_query_time = 2

通过慢查询日志，就可以定位出执行效率比较低的 SQL ，从而有针对性的进行优化。

profile 详情：

show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过 have_profiling

参数，能够看到当前 MySQL 是否支持 profile 操作：

SELECT @@have_profiling ;

可以看到，当前 MySQL 是支持 profile 操作的，但是开关是关闭的。可以通过 set 语句在

session/global 级别开启 profiling

SET profiling = 1;

开关已经打开了，接下来，我们所执行的 SQL 语句，都会被 MySQL 记录，并记录执行时间消耗到哪儿去

explain： 

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

-- 直接在 select 语句之前加上关键字 explain / desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

就会显示执行的东西：

Explain 执行计划中各个字段的含义:

 

建立了索引之后，查询性能大大提升。建立索引前后，查询耗时都不是一个数

量级的

最左前缀法则：

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

可以变顺序但是得有值，所以建立联合索引时需要注意那个在最左边的

最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段 ( 即是

第一个字段 ) 必须存在，与我们编写 SQL 时，条件编写的先后顺序无关。

 范围查询 ：

联合索引中，出现范围查询 (>,<) ，范围查询右侧的列索引失效。

当范围查询使用 >= 或 <= 时，走联合索引了

所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

 索引失效情况：

索引列运算：

不要在索引列上进行运算操作， 索引将失效。进行字符串操作等

字符串不加引号：

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

如果字符串不加单引号，对于查询结果，没什么影响，但是数 据库存在隐式类型转换，索引将失效

模糊查询

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

我们发现，在 like 模糊查询中，在关键字后面加 % ，索引可以生效。而如果在关键字

前面加了 % ，索引将会失效。

or 连接条件：

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

最终，我们发现，当 or 连接的条件，左右两侧字段都有索引时，索引才会生效。

数据分布影响 ：

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

因为 MySQL 在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃 索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不 如走全表扫描来的快，此时索引就会失效

SQL提示 ：

我们能不能在查询的时候，自己来指定使用哪个索引呢？ 答案是肯定的，此时就可以借助于

MySQL 的 SQL 提示来完成。 接下来，介绍一下 SQL 提示。

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

1). use index ： 建议 MySQL 使用哪一个索引完成此次查询（仅仅是建议， mysql 内部还会再次进

行评估）。

提示使用 idx_user_pro这个索引

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

2). ignore index ： 忽略指定的索引。用法一样

3). force index ： 强制使用索引。

覆盖索引：

尽量使用覆盖索引，减少 select * 。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并

且需要返回的列，在该索引中已经全部能够找到 。

因为覆盖索引不会进行回表查询 

 前缀索引：

当字段类型为字符串（ varchar ， text ， longtext 等）时，有时候需要索引很长的字符串，这会让

索引变得很大，查询时，浪费大量的磁盘 IO ， 影响查询效率。此时可以只将字符串的一部分前缀，建 立索引，这样可以大大节约索引空间，从而提高索引效率。

1). 语法：

create index idx_xxxx on table_name(column(n)) ；

eg：

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

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

2). 前缀长度：

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，

索引选择性越高则查询效率越高， 唯一索引的选择性是 1 ，这是最好的索引选择性，性能也是最好的。

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

3). 前缀索引的查询流程

 单列索引与联合索引 ：

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

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

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

而非单列索引

就是回表查询的

索引设计原则：

1). 针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（ where ）、排序（ order by ）、分组（ group by ）操作的字段建立索

引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增 删改的效率。

create unique index idx_user_phone_name on tb_user(phone,name);

1 7). 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询。