记录些MySQL题集（13）

MySQL 索引建立与使用指南

一、MySQL各索引的优劣分析

引入索引机制后，能够给数据库带来的优势很明显：

• • ①整个数据库中，数据表的查询速度直线提升，数据量越大时效果越明显。

• • ②通过创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。

• • ③在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。

• • ④连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。

• • ⑤索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。

• • ⑥从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。

同时也会带来一系列弊端，如：

• • ①建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。

• • ②写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。

• • ③写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。

当然，但对数据库整体来说，索引带来的优势会大于劣势。不过也正由于索引存在弊端，因此索引不是越多越好，合理建立索引才是最佳选择。

1.1、主键索引存在的陷阱

主键一般都是使用自增ID，但这是为什么呢？有人可能会回答自增ID不会重复，确保了主键唯一性。这样也确实没错，但不会重复的又不仅仅只有自增ID，比如我使用随机的UUID也不会重复，为何不使用UUID呢？

众所周知，一张表中大多数情况下，会将主键索引以聚簇的形式存在磁盘中，上篇文章也聊到过，聚簇索引在存储数据时，表数据和索引数据是一起存放的。同时，MySQL默认的索引结构是B+Tree，也就代表着索引节点的数据是有序的。

此时结合上面给出的一些信息，主键索引是聚簇索引，表数据和索引数据在一块、索引结构是有序的，那再反推前面给出的疑惑，为何不使用UUID呢？因为UUID是无序的，如果使用UUID作为主键，那么每当插入一条新数据，都有可能破坏原本的树结构，如下：

索引维护

比如上图中的灰色节点，是一条新插入的数据，此时经过计算后，应该排第二个位置，那就代表着后面的三个节点需要移动，然后给灰色节点挪出一个位置存储，从而确保索引的有序性。

由于主键索引是聚簇索引，因此上述案例中，当后续节点需要挪动时，也就代表着还需要挪动表数据，如果是偶尔需要移动还行，但如果主键字段值无序，那代表着几乎每次插入都有可能导致树结构要调整。

但使用自增ID就不会有这个问题，所有新插入的数据都会放到最后。

因此大家数据表的主键，最好选用带顺序性的值，否则有可能掉入主键索引的“陷阱”中。

1.2、联合索引存在的矛盾

为了多条件查询时的效率更高，一般都会同时对多个字段建立联合索引，但之前也聊到过，联合索引存在一个致命的问题，比如在用户表中，通过id、name、age三个字段建立一个联合索引，此时来了一条查询SQL，如下：

SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18";

而这条SQL语句是无法使用联合索引的，为什么呢？因为查询条件中，未包含联合索引的第一个字段，想要使用联合索引，那么查询条件中必须包含索引的第一个字段，如下：

SELECT * FROM `zz_user` WHERE name = "竹子" AND id = 6;

上面这条SQL才是能命中多列索引的语句，因此在建立索引时也需要考虑这个问题，确保建立出的联合索引能够命中率够高。

1.3、前缀索引存在的弊端

前缀索引的特点是短小精悍，我们可以利用一个字段的前N个字符创建索引，以这种形式创建的索引也被称之为前缀索引，相较于使用一个完整字段创建索引，前缀索引能够更加节省存储空间，当数据越多时，带来的优势越明显。

不过前缀索引虽然带来了节省空间的好处，但也正由于其索引节点中，未存储一个字段的完整值，所以MySQL也无法通过前缀索引来完成ORDER BY、GROUP BY等分组排序工作，同时也无法完成覆盖扫描等操作。

1.4、全文索引存在的硬伤

之前做模糊查询时，通常都会使用like%语法，不过这种方式虽然能够实现效果，但随着表越来越大，数据越来越多时，其性能会出现明显下降，而全文索引的推出则能够完美解决该问题，可以利用全文索引代替like%语法实现模糊查询，它的性能会比like%快上N倍。

全文索引虽然可以实现模糊查询，但也存在一系列硬伤。

①由于全文索引是基于分词实现的，所以对一个字段建立全文索引后，MySQL会对该字段做分词处理，这些分词结果也会被存储在全文索引中，因此全文索引的文件会额外的大。

②由于全文索引对每个字段值都会做分词，因此当修改字段值后，分词是需要时间的，所以修改字段数据后不会立马自动更新全文索引，此时需要咱们写存储过程，并调用它手动更新全文索引中的数据。

③除开上述两点外，全文索引最大的硬伤在于对中文支持不够友好，类似于英文可以直接通过符号、空格来分词，但中文呢？一个词语来形容就是博大精深，无法精准的对一段文字做分词，因此全文索引在检索中文时，存在些许精准度问题。

因此如果你项目规模较大，通常再引入ElasticSearch、Solr、MeiliSearch等搜索引擎是一个更佳的选择。

1.5、唯一索引存在的快慢问题

唯一索引有个很大的好处，就是查询数据时会比普通索引效率更高，因为基于普通索引的字段查询数据，例如：

SELECT * FROM TABLE_XX WHERE COLUMN_XX = "XX";

假设COLUMN_XX字段上建立了一个普通索引，此时基于这个字段查询数据时，当查询到一条COLUMN_XX = "XX"的数据后，此时会继续走完整个索引树，因为可能会存在多条字段值相同的数据。

但如果COLUMN_XX字段上建立的是唯一索引，当找到一条数据后就会立马停下检索，因此本身建立唯一索引的字段值就具备唯一性。

因此唯一索引查询数据时，会比普通索引快上一截，但插入数据时就不同了，因为要确保数据不重复，所以插入前会检查一遍表中是否存在相同的数据。但普通索引则不需要考虑这个问题，因此普通索引的数据插入会快一些。

1.6、哈希索引的致命问题

哈希索引，也就是数据结构为Hash类型的索引，不过估计大家接触的比较少，毕竟创建索引时都默认用的B+树结构。但要比起查询速度，哈希索引绝对是MySQL中当之无愧的魁首。因为采用哈希结构的索引，会以哈希表的形式存储索引字段值，当基于该字段查询数据时，只需要经过一次哈希计算就可获取到数据。

但哈希结构的致命问题在于无序，也就是无法基于哈希索引的字段做排序、分组等工作。

因此如果你确定一个表中，不会做排序这类的工作，那可以适当选用哈希结构作为索引的数据结构，它会给你带来意想不到的性能收益~

二、建立索引的正确姿势

在实际项目场景中，当SQL查询性能较慢时，我们常常会有一个疑惑：表中哪个字段建立一个索引能带来最大的性能收益呢？一般来说，判断字段是否要添加的索引的依据，是看这个字段是否被经常当做查询条件使用，但也不能光依靠这一个依据来判断，比如用户表中的性别字段，就会经常被用做查询条件，但如果对性别字段建立一个索引，那对查询的性能提升并不大，因为性别就两个值：男/女（不包含泰国在内），那对其建立索引，索引文件中就只会有两个索引节点，大致情况如下：

性别索引

这种情况下，为性别建立一个索引，带来的性能收益显然不是太大。同时，上图中给出的案例，也不是索引真正的样子，如果表中存在主键索引或聚簇索引，对其他字段建立的索引，都是次级索引，也被称为辅助索引，其节点上的值，存储的并非一条完整的行数据，而是指向聚簇索引的索引字段值。

如果基于辅助索引查询数据，最终数据会以何种方式被检索出来，这里就牵扯到MySQL中的一个新概念，也就是SQL执行时的回表问题。

2.1、索引查询时的回表问题

什么叫做回表呢？意思就是指一条SQL语句在MySQL内部，要经过两次查询过程才能获取到数据。这是跟索引机制有关的，先来看看索引在MySQL内部真正的面貌：

表中索引结构

在上图用户表中，基于ID字段先建立了一个主键索引，然后又基于name字段建立了一个普通索引，此时MySQL默认会选用主键索引作为聚簇索引，将表数据和主键索引存在同一个文件中，也就是主键索引的每个索引节点，都直接对应着行数据。而基于name字段建立的索引，其索引节点存放的则是指向聚簇索引的ID值。

在这种情况下，假设有一条下述SQL，其内部查询过程是啥样的呢？

SELECT * FROM `zz_user` WHERE name = "子竹";

首先会走name字段的索引，然后找到对应的ID值，然后再基于查询到的ID值，再走ID字段的主键索引，最终得到一整条行数据并返回。

在这个案例中，一条查询SQL经历了两次查询才获取到数据，这个过程则被称之为回表。

回表动作会导致额外的查询开销，因此尽量可以基于主键做查询，如果实在需要使用非主键字段查询，那么尽量要写明查询的结果字段，而并非使用*。

当然，实际情况中建立联合索引，利用索引覆盖特性，从而避免使用辅助索引，这样也能够消除回表动作。

2.2、建立索引时需要遵守的原则

前面说过一点，当建立索引仅考虑一个字段是否被经常用于查询是不够的，往往一个合适的索引需要更为细致与长远的思考，例如使用多个字段建立是否会更好？创建其他类型的索引性能是否会更佳？下面我们就一起来看看建立索引时，需要遵守的一些原则：

• • ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。

• • ②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。

• • ③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。

• • ④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。

• • ⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。

• • ⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。

• • ⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。

• • ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

同时，除开上述一些建立索引的原则外，在建立索引时还需有些注意点：

• • ❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。

• • ❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。

• • ❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。

• • ❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。

• • ❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。

• • ❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。

• • ❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

2.3、联合索引的最左前缀原则

   首先在讲最左前缀原则之前，先看看上述给出的一条原则：

• • ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

对于这一点是为什么呢？比如此时基于X、Y、Z字段建立了一个联合索引，实际上也相当于建立了三个索引：X，X、Y，X、Y、Z，因此只要查询中使用了这三组字段，都可以让联合索引生效。

但如若查询中这三个字段不以AND形式出现，而是单独作为查询条件出现，那单值索引性能会好一些，但三个不同的索引，维护的代价也会高一些。

其实联合索引的最左前缀原则，道理很简单的，就是组成联合索引的多个列，越靠左边优先级越高，同时也只有SQL查询条件中，包含了最左的字段，才能使用联合索引，例如：

-- 基于上面的哪个X、Y、Z联合索引
SELECT * FROM tb WHERE Y = "..." AND Z = "...";

上面这条SQL就显然并不会使用联合索引，因为不符合最左前缀原则，最左侧的X字段未曾被使用。也正由于MySQL在使用联合索引时会遵循最左前缀原则，所以才在前面建立索引的建议中给出了一条：

• • ❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。

因为将查询频率越高的字段放首位，就代表着查询时命中索引的几率越大。同时，MySQL的最左前缀原则，在匹配到范围查询时会停止匹配，比如>、<、between、like这类范围条件，并不会继续使用联合索引，举个栗子：

SELECT * FROM tb WHERE X="..." AND Y > "..." AND Z="...";

当执行时，虽然上述SQL使用到X、Y、Z作为查询条件，但由于Y字段是>范围查询，因此这里只能使用X索引，而不能使用X、Y或X、Y、Z索引。

对于一条查询SQL是否用到了索引，或者一条查询SQL到底用了那个索引，其实可以通过MySQL自带的explain工具分析。

最后再来一个简单的栗子，加深一下对于联合索引的认知：

select * from user where name = '竹子';
select * from user where name = '竹子' and age = 18;

A. create index index_name on user(name);
   create index index_name on user(age);

B. create index index_name on user(name,age);

比如上述这个案例中，对于这两条SQL选第一种方式创建索引，还是第二种呢？答案是B，因为两条sql完全能够利用到第二个创建的联合索引。

select * from user where name = '竹子' and age = 18;
select * from user where  age = 18 and name = '竹子';

同时选B建立联合索引后，如上两条SQL都会利用到上面创建的联合索引，SQL是否走索引查询跟where后的条件顺序无关，因为MySQL优化器会优化，对SQL查询条件进行重排序。

三、索引失效与使用索引的正确姿势

其实索引本身是一把双刃剑，用的好能够给我们带来异乎寻常的查询效率，用的不好则反而会带来额外的磁盘占用及写入操作时的维护开销。

3.1、索引失效的那些事儿

SELECT * FROM `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
+---------+-----------+----------+----------+---------------------+

ALTER TABLE `zz_users` ADD PRIMARY KEY `p_user_id`(`user_id`);
ALTER TABLE `zz_users` ADD KEY `unite_index`(`user_name`,`user_sex`,`password`);

此时对这张用户表，分别创建两个索引，第一个是基于user_id创建的主键索引，第二个是使用user_name、user_sex、password三个字段创建的联合索引。

但想要查看一条SQL是否使用了索引，需要用到一个自带的分析工具ExPlain，下面简单介绍一下。

3.1.1、执行分析工具 - ExPlain

当在一条SQL前加上explain命令，执行这条SQL后会列出所有的执行方案：

EXPLAIN SELECT * FROM `zz_users`;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | zz_users | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+

• • id：这是执行计划的ID值，这个值越大，表示执行的优先级越高。

• • select_type：当前查询语句的类型，有如下几个值：

  • • simple：简单查询。

  • • primary：复杂查询的外层查询。

  • • subquery：包含在查询语句中的子查询。

  • • derived：包含在FROM中的子查询。

• • table：表示当前这个执行计划是基于那张表执行的。

• • type：当前执行计划查询的类型，有几种情况：

  • • all：表示走了全表查询，未命中索引或索引失效。

  • • system：表示要查询的表中仅有一条数据。

  • • const：表示当前SQL语句的查询条件中，可以命中索引查询。

  • • range：表示当前查询操作是查某个区间。

  • • eq_ref：表示目前在做多表关联查询。

  • • ref：表示目前使用了普通索引查询。

  • • index：表示目前SQL使用了辅助索引查询。

• • possible_keys：执行SQL时，优化器可能会选择的索引（最后执行不一定用）。

• • key：查询语句执行时，用到的索引名字。

• • key_len：这里表示索引字段使用的字节数。

• • ref：这里显示使用了那种查询的类型。

• • rows：当前查询语句可能会扫描多少行数据才能检索出结果。

• • Extra：这里是记录着额外的一些索引使用信息，有几种状态：

  • • using index：表示目前使用了覆盖索引查询（稍后讲）。

  • • using where：表示使用了where子句查询，通常表示没使用索引。

  • • using index condition：表示查询条件使用到了联合索引的前面几个字段。

  • • using temporary：表示使用了临时表处理查询结果。

  • • using filesort：表示以索引字段之外的方式进行排序，效率较低。

  • • select tables optimized away：表示在索引字段上使用了聚合函数。

只需要记住里面的type字段即可，all表示走全表扫描，const、ref...表示通过索引查询。

3.1.2、查询中带有OR会导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE user_id = 1 OR user_name = "熊猫";

例如上述这条SQL，其中既包含了主键索引的字段，又包含了联合索引的第一个字段，按理来说是会走索引查询的对吗？但看看执行结果：

or导致索引失效

从结果中可看到type=ALL，显然并未使用索引来查询，也就代表着，虽然所有查询条件都包含了索引字段，但由于使用了OR，最终导致索引失效。

3.1.3、模糊查询中like以%开头导致索引失效

众所周知，使用like关键字做模糊查询时，是可以使用索引的，那来看看下述这条SQL：

EXPLAIN SELECT * FROM `zz_users` WHERE user_name LIKE "%熊";

在这条SQL中以联合索引中的第一个字段作为了查询条件，此时会使用索引吗？看看结果：

like%*导致索引失效

结果中显示依旧走了全表扫描，并未使用索引，但like不以%开头，实际上是不会导致索引失效的，例如：

%结尾

在这里以%结尾，其实可以使用联合索引来检索数据，并不会导致索引失效。

3.1.4、字符类型查询时不带引号导致索引失效

-- 先插入一条user_name = 1111 的数据
INSERT INTO `zz_users` VALUES(4,"1111","男","4321","2022-09-17 23:48:29");

EXPLAIN SELECT * FROM `zz_users` WHERE user_name = 111;

上述这条SQL按理来说是没有半点问题的，目前是符合联合索引的最左匹配原则的，但来看看结果：

不带引号对比

从结果中很明显的可以看出，由于user_name是字符串类型的，因此查询时没带引号，竟然直接未使用索引，导致了索引失效（上面也放了对比图，大家可以仔细看看区别）。

3.1.5、索引字段参与计算导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE user_id - 1 = 1;

上面这条SQL看着估计有些懵，但实际上很简单，就是查询ID=2的数据，理论上因为查询条件中使用了主键字段，应该会使用主键索引，但结果呢？

索引字段参与计算

由于索引字段参与了计算，所以此时又导致了索引失效，因此大家要切记，千万不要让索引字段在SQL中参与计算，也包括使用一些聚合函数时也会导致索引失效，其根本原因就在于索引字段参与了计算导致的。

这里的运算也包括+、-、*、/、!.....等一系列涉及字段计算的逻辑。

3.1.6、字段被用于函数计算导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE SUBSTRING(user_name,0,1) = "竹子";

上述中，我们使用SUBSTRING函数对user_name字段进行了截取，然后再用于条件查询，此时看看执行结果：

函数计算

很显然，并未使用索引查询，这也是意料之中的事情，毕竟这一条和3.1.5的原因大致相同，索引字段参与计算导致失效。

3.1.7、违背最左前缀原则导致索引失效

EXPLAIN SELECT * FROM `zz_users` WHERE `user_sex` = "男" AND `password` = "1234";

上述这条SQL中，显然用到了联合索引中的性别和密码字段，此时再看看结果：

违背最左匹配

由于违背了联合索引的最左前缀原则，因为没使用最左边的user_name字段，因此也导致索引失效，从而走了全表查询。

3.1.8、不同字段值对比导致索引失效

从一张表中查询出一些值，然后根据这些值去其他表中筛选数据，这个业务也是实际项目中较为常见的场景，下面为了简单实现，就简单用姓名和性别模拟一下字段对比的场景：

EXPLAIN SELECT * FROM `zz_users` WHERE user_name = user_sex;

按理来说，因为user_name属于联合索引的第一个字段，所以上述这条SQL中规中矩，理论上会走索引的，但看看结果：

字段对比

显然，这个场景也会导致索引无法使用，因此之后也要切记这点。

3.1.9、反向范围操作导致索引失效

一般来说，如果SQL属于正向范围查询，例如>、<、between、like、in...等操作时，索引是可以正常生效的，但如果SQL执行的是反向范围操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等操作时，就会出现问题，例如：

EXPLAIN SELECT * FROM `zz_users` WHERE user_id NOT IN(1,2,3);

上述SQL的意思很简单，也就是查询user_id不是1,2,3的数据，这里是基于主键索引字段user_id查询的，但会走索引吗？来看看结果：

范围查询对比

结果也很明显，使用NOT关键字做反向范围查询时，并不会走索引，索引此时失效了，但是做正向范围查询时，索引依旧有效。

并非所有的正向范围操作都会走索引，例如IS NULL就不会走，它的反向操作：IS NOT NULL同样不会走。

3.1.10、索引失效小结

在MySQL中还有一种特殊情况会导致索引失效，也就是当走索引扫描的行数超过表行数的30%时，MySQL会默认放弃索引查询，转而使用全表扫描的方式检索数据，因此这种情况下走索引的顺序磁盘IO，反而不一定有全表的随机磁盘IO快。

还有一点要牢记：关于索引是否会失效，实际上也跟索引的数据结构、MySQL的版本、存储引擎的不同有关，例如一条SQL语句在B+Tree索引中会导致索引失效，但在哈希索引中却不会（好比IS NULL/IS NOT NULL），这种情况在不同版本、不同引擎中都有可能会体现出来。

3.2、使用索引的正确姿势

   其实到这里，对于如何使用索引才是正确的呢？总结如下：

• • ①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。

• • ②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。

• • ③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。

• • ④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。

• • ⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。

• • ⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。

• • ⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。

• • ⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。

实际上无非就是根据前面给出的索引失效情况，尽量让自己编写的SQL不会导致索引失效即可，写出来的SQL能走索引查询，那就能在很大程度上提升数据检索的效率。

3.2.1、索引覆盖

由于表中只能存在一个聚簇索引，一般都为主键索引，而建立的其他索引都为辅助索引，包括联合索引也例外，最终索引节点上存储的都是指向主键索引的值，拿前面的用户表为例：

SELECT * FROM `zz_users` WHERE `user_name`="竹子" AND `user_sex`="男";

虽然这条SQL会走联合索引查询，但是基于联合索引查询出来的值仅是一个指向主键索引的ID，然后会拿着这个ID再去主键索引中查一遍，这个过程之前聊过，被称为回表过程。

那么回表问题无法解决吗？必须得经过两次查询才能得到数据吗？答案并非如此。

比如假设此时只需要user_name、user_sex、password这三个字段的信息，此时SQL语句可以更改为如下情况：

SELECT 
    `user_name`,`user_sex`,`password`
FROM 
    `zz_users` 
WHERE 
    `user_name` = "竹子" AND `user_sex` = "男";

此时将SQL更改为查询所需的列后，就不会发生回表现象，因为此时所需的user_name、user_sex、password三个字段数据，在联合索引中完全包含，因此可以直接通过联合索引获取到数据。

但如果查询时用*，因为联合索引中不具备完整的一行数据，只能再次转向聚簇索引中获取完整的行数据，因此到这里大家应该也明白了为什么查询数据时，不能用*的原因，这是因为会导致索引覆盖失效，造成回表问题。

当然，再来提一点比较有意思的事情，先看SQL：

EXPLAIN SELECT 
    `user_name`,`user_sex`
FROM 
    `zz_users`
WHERE 
    `password` = "1234" AND `user_sex` = "男";

比如上述这条SQL，显然是不符合联合索引的最左前缀匹配原则的，但来看看执行结果：

索引覆盖

这个结果是不是很令你惊讶，通过EXPLAIN分析的结果显示，这条SQL竟然使用了索引，这是什么原因呢？也是因为索引覆盖。

一句话概述：就是要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖。

3.2.2、索引下推

索引下推是MySQL5.6版本以后引入的一种优化机制，还是以之前的用户表为例，先来看一条SQL语句：

INSERT INTO `zz_users` VALUES(5,"竹竹","女","8888","2022-09-20 22:17:21");

SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男";

首先为了更加直观的讲清楚索引下推，因此先再向用户表中增加一条数据。然后再来看看后面的查询SQL，这条SQL会使用联合索引吗？答案是会的，但只能部分使用，因为联合索引的每个节点信息大致如下：

{
    ["熊猫","女","6666"] : 1,
    ["竹子","男","1234"] : 2,
    ["子竹","男","4321"] : 3,
    ["1111","男","4321"] : 4,
    ["竹竹","女","8888"] : 5
}

由于前面使用的是模糊查询，但%在结尾，因此可以使用竹这个字作为条件在联合索引中查询，整个查询过程如下：

• • ①利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。

• • ②返回索引节点存储的值「2、5」给Server层，然后去逐一做回表扫描。

• • ③在Server层中根据user_sex="男"这个条件逐条判断，最终筛选到「竹子」这条数据。

为什么user_sex="男"这个条件不在联合索引中处理呢？因为前面是模糊查询，所以拼接起来是这样的：竹x男，由于这个x是未知的，因此无法根据最左前缀原则去匹配数据，最终这里只能使用联合索引中user_name字段的一部分，后续的user_sex="男"还需要回到Server层处理。

那什么又叫做索引下推呢？也就是将Server层筛选数据的工作，下推到引擎层处理。

以前面的案例来讲解，MySQL5.6加入索引下推机制后，其执行过程是什么样子的呢？

• • ①利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。

• • ②根据user_sex="男"这个条件在索引节点中逐个判断，从而得到「竹子」这个节点。

• • ③最终将「竹子」这个节点对应的「2」返回给Server层，然后聚簇索引中回表拿数据。

相较于没有索引下推之前，原本需要做「2、5」两次回表查询，但在拥有索引下推之后，仅需做「2」一次回表查询。

索引下推在MySQL5.6版本之后是默认开启的，可以通过命令set optimizer_switch='index_condition_pushdown=off|on';命令来手动管理。

3.2.3、MRR(Multi-Range Read)机制

Multi-Range Read简称为MRR机制，这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施，那什么叫做MRR优化呢？

一般来说，在实际业务中我们应当尽量通过索引覆盖的特性，减少回表操作以降低IO次数，但在很多时候往往又不得不做回表才能查询到数据，但回表显然会导致产生大量磁盘IO，同时更严重的一点是：还会产生大量的离散IO，下面举个例子来理解。

SELECT * FROM `zz_student_score` WHERE `score` BETWEEN 0 AND 59;

上述这条SQL所做的工作很简单，就是在学生成绩表中查询所有成绩未及格的学生信息，假设成绩字段上存在一个普通索引，那思考一下，这条SQL的执行流程是什么样的呢？

• • ①先在成绩字段的索引上找到0分的节点，然后拿着ID去回表得到成绩零分的学生信息。

• • ②再次回到成绩索引，继续找到所有1分的节点，继续回表得到1分的学生信息。

• • ③再次回到成绩索引，继续找到所有2分的节点......

• • ④周而复始，不断重复这个过程，直到将0~59分的所有学生信息全部拿到为止。

那此时假设此时成绩0~5分的表数据，位于磁盘空间的page_01页上，而成绩为5~10分的数据，位于磁盘空间的page_02页上，成绩为10~15分的数据，又位于磁盘空间的page_01页上。此时回表查询时就会导致在page_01、page_02两页空间上来回切换，但0~5、10~15分的数据完全可以合并，然后读一次page_01就可以了，既能减少IO次数，同时还避免了离散IO。

而MRR机制就主要是解决这个问题的，针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。

那MRR机制具体是怎么做的呢？MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。

SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';

可以通过上述这条命令开启或关闭MRR机制，MySQL5.6及以后的版本是默认开启的。

3.2.4、Index Skip Scan索引跳跃式扫描

在讲联合索引时，咱们提到过最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

但跳跃扫描究竟是怎么实现的呢？

比如此时通过(A、B、C)三个列建立了一个联合索引，此时有如下一条SQL：

SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`;

按理来说，这条SQL既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的，但思考一个问题，这条SQL中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏。？因此MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，比如上述这条SQL则会重构成如下情况：

SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";

其实也就是MySQL优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍，一句话来概述就是：虽然你没用第一个字段，但我给你加上去，今天这个联合索引你就得用，不用也得给我用。

MySQL几经转手后，最终归到了Oracle旗下，因此跳跃扫描机制仅是Oracle公司：从Oracle搬到了“自己的MySQL”上而已。

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发等。

其实这个跳跃性扫描机制，只有在唯一性较差的情况下，才能发挥出不错的效果，如果你联合索引的第一个字段，是一个值具备唯一性的字段，那去重一次再拼接，几乎就等价于走一次全表。

最后，可以通过通过set @@optimizer_switch = 'skip_scan=off|on';命令来选择开启或关闭跳跃式扫描机制。当然，该参数仅限MySQL8.0以上的版本，如果在此之下的版本暂时就不用考虑了。

关于索引的查询命令：

• • show status like '%Handler_read%';查看当前会话的索引使用情况。

• • show global status like 'Handler_read%';：查询全局索引使用情况。

MySQL引擎：InnoDB

MySQL是一款支持拔插式引擎的数据库，在开发过程中你可以根据业务特性，从支持的诸多引擎中选择一款适合的，例如MyISAM、InnoDB、Merge、Memory(HEAP)、BDB(BerkeleyDB)、Example、Federated、Archive、CSV、Blackhole等。

存储引擎

不过虽然各款引擎都各有千秋，但其中最为常用的就只有MyISAM、InnoDB这两款引擎。

MyISAM引擎是MySQL官方基于早期的ISAM引擎改良而来的，由于其不错的性能表现，再加上丰富的特性支持（全文索引、压缩机制、空间索引/函数等），在MySQL5.5版本之前，也一直是MySQL默认的存储引擎。

但随着时间慢慢推移，开始主推使用InnoDB作为表的引擎，甚至到了MySQL5.6及以后版本中，直接用InnoDB代替了MyISAM，作为了MySQL默认的存储引擎，这是啥原因呢？

一、MyISAM引擎 vs InnoDB引擎

以最经典、最主流的MySQL5.7版本为例，两款引擎各自支持的特性如下：

MyISAM引擎特性

InnoDB引擎特性

2.1、磁盘文件的对比

磁盘文件

其中使用MyISAM引擎的表：zz_myisam_index，会在本地生成三个磁盘文件：

• • zz_myisam_index.frm：该文件中存储表的结构信息。

• • zz_myisam_index.MYD：该文件中存储表的行数据。

• • zz_myisam_index.MYI：该文件中存储表的索引数据。

从这里可得知一点：MyISAM引擎的表数据和索引数据，会分别放在两个不同的文件中存储。

而反观使用InnoDB引擎的表：zz_innodb_index，在磁盘中仅有两个文件：

• • zz_innodb_index.frm：该文件中存储表的结构信息。

• • zz_innodb_index.ibd：该文件中存储表的行数据和索引数据。

2.2、索引支持的对比

因为MyISAM引擎在设计之初，会将表分为.frm、.MYD、.MYI三个文件放在磁盘存储，表数据和索引数据是分别放在.MYD、.MYI文件中，所以注定了MyISAM引擎只支持非聚簇索引。而InnoDB引擎的表数据、索引数据都放在.ibd文件中存储，因此InnoDB是支持聚簇索引的。

为啥索引数据和表数据分开存储就不支持聚簇索引呢？

非聚簇和聚簇

聚簇索引的要求是：索引键和行数据必须在物理空间上也是连续的，而MyISAM表数据和索引数据，分别位于两个磁盘文件中，这也就注定了它无法满足聚簇索引的要求。

一种引擎支不支持聚簇索引很重要，这涉及到了后面的很多技术实现，而MyISAM把表数据和索引数据分开存了，也就意味着MyISAM相较于InnoDB来说，这小子天生就带有缺陷。

但不支持聚簇索引也有好处，也就是无论走任何索引，都只需要一遍查询即可获得数据，而InnoDB引擎的表中，如果不走聚簇（主键）索引查询数据，走其他索引的情况下，都需要经过两遍（回表）查询才能获得数据。

2.3、事务机制的对比

InnoDB引擎中有两个自己专享的日志，即undo-log、redo-log，先来说说undo-log日志，InnoDB在MySQL启动后，会在内存中构建一个undo_log_buffer缓冲区，同时在磁盘中也有相应的undo-log日志文件。

那undo缓冲区和磁盘文件有啥用呢？

事务提交与回滚

一条写入类型的SQL语句，在正式执行前都会先记录redo-log、undo-log日志，undo-log中会记录变更前的旧数据，当一个事务提交时，MySQL会正常的将数据落盘，而当一个事务碰到rollback命令需要回滚时，就会找到undo-log中记录的旧数据，接着用来覆盖变更过的新数据，以此做到将数据回滚到变更前的“样貌”。

使用InnoDB存储引擎的表，可以借助undo-log日志实现事务机制，支持多条SQL组成一个事务，可以保证发生异常的情况下，组成这个事务的SQL到底回滚还是提交。而MyISAM并未设计类似的技术，在启动时不会在内存中构建undo_log_buffer缓冲区，磁盘中也没有相应的日志文件，因此MyISAM并不支持事务机制。

一个引擎是否支持事务，这点尤为重要，因为业务开发过程中，咱们需要关注数据的安全性，拿最为经典的下单为例，用户把钱都付了，总不能由于程序Bug，然后不给用户新增订单、物流信息吧？再不济至少也要把钱退回给用户，因此就需要用到事务机制来保证原子性。

而MyISAM不支持事务，也就意味着当用户付钱之后，如果程序出现了异常，就会导致用户付的钱不会退回，订单信息也不会生成，因为程序都抛异常了，自然不会继续往下执行增加订单、物流信息的SQL语句。

所以，如果表结构用了MyISAM引擎，想要解决这类问题，就只能在客户端做事务补偿，比如上面这个情况，当用户付钱后执行出现异常了，就在客户端中记录一下，然后再向MySQL发送一条相应的反SQL，以此来保障数据的一致性。

2.4、故障恢复的对比

前面简单的聊了undo-log日志，InnoDB借助它保证了事务的原子性，接着再来看看redo-log日志，InnoDB在启动时，同样会在内存中构建一个redo_log_buffer缓冲区，在磁盘中也会有相应的redo-log日志文件，所以当一条或多条SQL语句执行成功后，不论MySQL在何时宕机，只要这个事务提交了，InnoDB引擎都能确保该事务的数据不会丢失，也就以此保障了事务的持久性。

InnoDB引擎由于redo-log日志的存在，因此只要事务提交，机器断电、程序宕机等各种灾难情况，都可以用redo-log日志来恢复数据。但MyISAM引擎同样没有redo-log日志，所以并不支持数据的故障恢复，如果表是使用MyISAM引擎创建的，当一条SQL将数据写入到了缓冲区后，SQL还未被写到bin-log日志，此时机器断电、DB宕机了，重启之后由于数据在宕机前还未落盘，所以丢了也就无法找回。

从这一点来说，MyISAM并没有InnoDB引擎可靠，在InnoDB中只要事务提交，它就能确保数据永远不丢失，但MyISAM不行。这就好比咱们去银行存钱，去InnoDB银行存，你只需要把钱送到它那里，它就能确保你的财产安全，但如若去MyISAM银行存钱，你必须要把钱送到银行的保险库中才行，否则有可能会因为在送往保险库的过程中“丢失”财产。

2.5、锁粒度的对比

锁的实现粒度其实跟索引有关，大家应该都知道，MySQL的存储引擎中，MyISAM仅支持表锁，而InnoDB同时支持表锁、行锁，但为啥MyISAM引擎不支持行锁呢？

select *from zz_students;
+------------+--------+------+--------+
| student_id | name   | sex  | height |
+------------+--------+------+--------+
|1|竹子|男|185cm|
|...|....|..|.....|
+------------+--------+------+--------+

上述这张学生表中，假设使用的是MyISAM引擎，同时对student_id字段建立了主键索引，name字段建立了普通索引，sex、height字段建立了联合索引，此时先不管索引合不合理，以目前情况为例，来推导一下MyISAM表为啥无法实现行锁。

这张表中存在三个索引，那在本地的.MYI索引文件中，肯定存在三颗B+树，同时由于MyISAM不支持聚簇索引，所以这三个索引是平级的，每棵B+树的索引键，都直接指向.MYD数据文件中的行数据地址。

假设MyISAM要实现行锁，当要对一行数据加锁时，可以锁定一棵树中某一个数据，但无法锁定其他树的行数据，啥意思呢？举个例子：

select * from zz_students where student_id = 1 for update;

这条SQL必然会走主键索引命中数据，那假设此时对主键索引树上，ID=1的数据加锁，接着再来看一种情况：

select * from zz_students where name = "竹子" for update;

此时这条SQL又会走name字段的普通索引查询数据，那此时又对普通索引树上的「竹子」数据加锁。

到这里，发现问题没有？上面的案例中，MyISAM如果想要实现行锁，就会遇到这个问题，基于不同索引查询数据时，可能会导致一行数据上加多个锁！这样又会导致多条线程同时操作一个数据，所以又会因为多线程并发执行的原因，造成脏读、幻读、不可重复读这系列问题出现。

但InnoDB引擎呢？因为支持聚簇索引，表中就算没有显式定义主键，内部依旧会用一个隐藏列来作为聚簇索引的索引字段，所以InnoDB表中的索引，是有主次之分的，所有的次级索引，其索引值都存储聚簇索引的索引键，因此想要对一行数据加锁时，只需要锁定聚簇索引的数据即可。

-- 通过主键索引查询数据
select * from zz_students where student_id = 1 for update;
-- 通过普通索引查询数据
select * from zz_students where name = "竹子" for update;

依旧是前面的这个例子，通过主键索引查询的SQL语句，会直接定位到聚簇索引的数据，然后对ID=1的数据加锁。而第二条通过普通索引查询数据的SQL语句，经过查询后会得到一个值：ID=1，然后会拿着这个ID=1的值再去回表，在聚簇索引中再次查询ID=1的数据，找到之后发现上面已经有线程加锁了，当前线程就会阻塞等待上一个线程释放锁。

看到这里，相信大家也就理解了我前面说的那句：“MyISAM由于不支持聚簇索引，因此对比InnoDB来说，这小子天生存在缺陷”的含义。

因为MyISAM引擎不支持聚簇索引，所以无法实现行锁，出现多条线程同时读写数据时，只能锁住整张表。而InnoDB由于支持聚簇索引，每个索引最终都会指向聚簇索引中的索引键，因此出现并发事务时，InnoDB只需要锁住聚簇索引的数据即可，而不需要锁住整张表，因此并发性能更高。

同时，InnoDB引擎构建的缓冲区中，会专门申请一块内存作为锁空间，同时再结合InnoDB支持事务，所以InnoDB是基于事务来生成锁对象，相较于SQL Server的行锁来说，InnoDB的行锁会更节约内存。

2.6、并发性能的对比

MyISAM仅支持表锁，InnoDB同时支持表锁、行锁，由于这点原因，其实InnoDB引擎的并发支持性早已远超MyISAM了，毕竟锁的粒度越小，并发冲突的概率也就越低，因此并发支撑就越高。

但是InnoDB不仅仅只满足于此，为了提升读-写并存场景下的并发度，InnoDB引擎又基于undo-log日志的版本链+事务快照，又推出了MVCC多版本并发控制技术，因此对于读-写共存的场景支持并发执行。

但MyISAM只支持表锁，因此当一条SQL在写数据时，其他SQL就算是来读数据的，也需要阻塞等待，为啥呢？因为写数据时需要加排他锁，这是一种独占类型的锁，会排斥一切尝试获取锁的线程，反过来也是同理，当一条线程在读数据时，另一条线程来写数据，依旧会陷入阻塞等待，毕竟写数据要获取排他锁，也就意味着整张表只允许这一个线程操作。

2.7、内存利用度的对比

InnoDB几乎将内存开发到了极致，虽然InnoDB不像Memory引擎那样完全基于内存运行，但它将所有能够在内存完成的操作，全部都放在了内存中完成，无论是读写数据、维护索引结构也好，记录日志也罢，各类操作全部都在内存完成。

只要你机器的内存够大，为缓冲池分配的内存够多，MySQL在线上运行的时间够久，InnoDB甚至会将磁盘中的所有数据，全部载入内存，然后所有客户端的读写请求，基本上无需再走磁盘来完成，都采用异步IO的方式完成，即先写内存+后台线程刷写的方式执行，后台线程的刷盘动作，对客户端而言不会有任何感知，在写完内存之后就会直接向客户端返回。

因为随着时代的进步，计算机硬件也在不断改进，虽然磁盘由起初的机械磁盘，演化到了如今的固态磁盘（SSD），但内存的发展更为迅猛，DDR1、DDR2、DDR3、DDR4、DDR5不断迭代，内存频率从起初的100~200MHz，到800MHz、1000MHz....、2400MHz、4800MHz....甚至到现在的上万兆赫，慢慢的内存读写速率遥遥领先于磁盘，所以再基于磁盘执行业务SQL，其效率虽然不低，但对整个业务系统而言依旧是较慢的。

而InnoDB引擎的创始人Heikki Tuuri早早想到了这点，通过缓冲池结合异步IO技术，活生生将一款基于磁盘的引擎，演变成了半内存式的引擎。反观MyISAM引擎，内部虽然也有缓冲池以及异步IO技术，但对内存的开发度远不足于InnoDB引擎，运行期间大量操作依旧会走磁盘完成。

其实这也不能怪MyISAM引擎，而是由于它出身的原因导致的，因为MySQL官方最初以为：MySQL Server + MyISAM这套组合能长久不衰，所以很多功能都放在了MySQL Server中实现，比如：

InnoDB缓冲池的数据页，可以当做数据缓存使用，如果数据页中有的数据，可以直接从内存中读取返回，而MyISAM则没有相应实现，完全依赖于MySQL Server的「查询缓存」做到这个功能。

InnoDB引擎专门设计了redo-log日志，可以用于故障恢复，而MyISAM也没有类似的实现，而是企图通过MySQL Server的bin-log日志实现这个功能。

InnoDB创造了一个插入缓冲区，也就是后来的写入缓冲区，用于减少写操作执行时磁盘IO，MyISAM引擎同样没有相应实现，而是依赖于MySQL Server在工作线程中设计的bulk_insert_buffer批量插入缓冲区来实现类似的功能。

类似于上述的情况，在MyISAM引擎中还有不少，例如Key Buffer等。

其实除开上述列出的几个对比项外，还有是否支持外键的对比、删除数据时的区别等。

二、为什么InnoDB代替了MyISAM？

经过上述的一系列对比后，对于为何使用InnoDB替换了MyISAM引擎的原因，总结：

• • ①存储方式：MyISAM引擎会将表数据和索引数据分成两个文件存储。

• • ②索引支持：因为MyISAM引擎的表数据和索引数据是分开的，因此不支持聚簇索引。

• • ③事务支持：由于MyISAM引擎没有undo-log日志，所以不支持多条SQL组成事务并回滚。

• • ④故障恢复：MyISAM引擎依靠bin-log日志实现，bin-log中未写入的数据会永久丢失。

• • ⑤锁粒度支持：因为MyISAM不支持聚簇索引，因此无法实现行锁，所有并发操作只能加表锁。

• • ⑥并发性能：MyISAM引擎仅支持表锁，所以多条线程出现读-写并发场景时会阻塞。

• • ⑦内存利用度：MyISAM引擎过于依赖MySQL Server，对缓冲池、异步IO技术开发度不够。

上述这些MyISAM不支持的，InnoDB引擎全都支持，也正由于这方方面面的原因，InnoDB引擎开始崭露锋芒，而作为MySQL亲生子的MyISAM自此之后跌落神坛，最终到了MySQL5.6版本时，MyISAM彻底让出了MySQL默认存储引擎的宝座。

MySQL官方费尽心血打造的MyISAM虽然败给了InnoDB，但自从将默认的存储引擎替换成InnoDB后，由于其丰富的特性，支持事务机制、支持行级锁、可靠的故障恢复机制、优异的并发性能支持、超高的内存利用度.....等一系列优点，这使得MySQL在数据库市场的占用率直线上升。

更换默认存储引擎后的下一个版本，即MySQL5.7，在其中优化了更换引擎带来的一些遗留问题，也成为了MySQL数据库有史以来最受欢迎的版本，在MySQL发布的众多版本中一直保持统治地位，基本上只有多年后发布的MySQL8.0版本才能与之聘美。

三、MyISAM引擎真的一无是处吗？

3.1、统计总数的优化

   一般来说，在日常业务开发过程中，咱们有一个操作会经常在数据库中进行，即：

select count(*) from `table_name`;

好比要统计订单数、平台用户总数、会员数.....各类需求，基本上都会在数据库中执行count()操作，对于count()统计行数的操作，在MyISAM引擎中会记录表的行数，也就是当执行count()时，如果表是MyISAM引擎，则可以直接获取之前统计的值并返回。

但这个特性在InnoDB引擎中是不具备的，当你在InnoDB中统计一张表的总数时，会触发全表扫描，InnoDB会一行行的去统计表的行数。

但是MyISAM的这个特性也仅仅只适用于统计全表数据量，如果后面跟了where条件：

select count(*) from `table_name` where xxx = "xxx";

如果是这种情况，那InnoDB、MyISAM的工作模式是相同的，先根据where后的条件查询数据，再一行行统计总数。

3.2、删除数据/表的优化

   当使用delete命令清空表数据时，如下：

delete from `table_name`;

MyISAM会直接重新创建表数据文件，而InnoDB则是一行行删除数据，因此对于清空表数据的操作，MyISAM比InnoDB快上无数倍。同时MyISAM引擎的表，对于delete过的数据不会立即删除，而且先隐藏起来，后续定时删除或手动删除，手动强制清理的命令如下：

optimize table `table_name`;

这样做有一点好处，就是当你误删一张表的大量数据时，只要你手速够快，手动将本地的.MYD、.MYI文件拷贝出去，就可以直接基于这两个数据文件恢复数据，而不需要通过日志或第三方工具修复数据。

3.3、CRUD速度更快

因为InnoDB支持聚簇索引，因此整个表数据都会和聚簇索引一起放在一颗B+树中存储，就算当你没有定义主键时，InnoDB也会定义一个隐式字段ROW_ID来作为聚簇索引字段，这也就意味着：在InnoDB的表中，这个聚簇索引你不要也得要。

聚簇索引带来的好处很明显，可以借助它来实现行级别的锁，但凡事有利有弊，鱼和熊掌不可兼得。

当查询数据时，如果在基于非聚簇索引查找数据，就算查到了也需要经过一次回表才能得到数据，同时插入数据、修改数据时，都需要维护聚簇索引和非聚簇索引之间的关系。

一句话来概述就是：InnoDB的聚簇索引，会影响读写数据的性能。

而MyISAM引擎中，所有已创建的索引都是非聚簇索引，每个索引之间都是独立的，在索引中存储的是直接指向行数据的地址，而并非聚簇索引的索引键，因此无论走任何索引，都仅需一次即可获得数据，无需做回表查询。

同时写数据时，也不需要维护不同索引之间的关系，毕竟每个索引都是独立的，因此MyISAM在理论上，读写数据的效率会高于InnoDB引擎。

不过理论终归是理论，放在实际的生产环境中，这条理论是行不通的？

3.4、MyISAM真的比InnoDB快吗？

如果是对比单个客户端连接的读写性能，那自然MyISAM远超于InnoDB引擎，毕竟InnoDB需要维护聚簇索引，而MyISAM因为每个索引都是独立的，因此插入表数据时都是直接追加在表数据文件的末尾即可，而且修改数据也不需要维护其他索引和聚簇索引的关系。

但把测试的环境换到多个客户端连接的场景时，会出现不同的现象，先看看官网上的测试图：

性能测试

观察上图可明显发现，随着连接数的增加，工作线程会不断增加，CPU使用核数也会不断增加，而InnoDB的性能会逐步上升，但MyISAM引擎基本上没有太大变化，基本上从头到尾一直都很低，这是啥原因造成的呢？答案是由于锁机制导致的。

之前聊到过，MyISAM引擎仅支持表锁，也就意味着无论有多少个客户端连接到来，对于同一张表永远只能允许一条线程操作，除非多个连接都是在读数据，才不会相互排斥。

反观InnoDB引擎，由于支持行锁，所以并发冲突很小，在高并发、多连接的场景中，性能会更加出色，而MyISAM引擎基本上在并发读写场景中，一张表只允许单线程操作，因此并发冲突很大，吞吐量会因此严重下降。

但如果以单连接的方式测试，确实MyISAM会远超InnoDB，毕竟单个连接意味着只有一条线程，一条线程就不会出现锁竞争，表锁会一直由这条线程持有。

3.5、MyISAM的压缩机制

如今的数据库随着业务发展，数据量的增长一天一个新变化，时间不断推移，数据只会越来越大，这时就很容易出现以下两个问题：

• • IO瓶颈：DB数据量过大，导致内存无法载入太多数据，会触发大量磁盘IO，让DB整体性能降低。

• • 磁盘空间不足：随着业务的发展，部署数据库的机器磁盘无法存储数据，需要不断扩容硬件。

而MyISAM引擎为了解决这个问题，可以通过myisampack工具对数据表进行压缩，压缩的效果至少能让数据缩小一半，但压缩后的数据只可读，不可写。

到了MySQL5.7版本中，该特性也被移植到了InnoDB引擎中，相关的压缩参数如下：

• • innodb_compression_level：调整压缩的级别，可控范围在1~9，越高压缩效果越好，但压缩速度也越慢。

• • innodb_compression_failure_threshold_pct：当压缩失败的数据页超出该比例时，会加入数据填充来减小失败率，为0表示禁止填充。

• • innodb_compression_pad_pct_max：一个数据页中最大允许填充多少比例的空白数据。

• • innodb_log_compressed_pages：控制是否对redo-log日志的数据也开启压缩机制。

• • innodb_cmp_per_index_enabled：是否对索引文件开启压缩机制。

3.6、MyISAM引擎的适用场景

什么场景下，适合选用MyISAM引擎呢？

结合MyISAM引擎的特性而言，它适用于一些不需要事务、并发冲突低、读操作多的表，例如文章表、帖子表、字典表....

但实际上这种表在一个系统中占比很少，但有一种场景时，特别适合使用MyISAM引擎，即MySQL利用主从架构，实现读写分离时的场景，一般从库会承载select请求，而主库会承载insert/update/delete请求。读写分离的场景中，从库的表结构可以改为MyISAM引擎，因为基于MyISAM的索引查询数据，不需要经过回表查询，速度更快！

同时，由于做了读写分离，因此从库上只会有读请求，不会存在任何外部的写请求，所以支持并发读取。

而且从库的数据是由后台线程来从主库复制的，因此从库在写入数据时，只会有少数几条线程执行写入工作，因而造成的冲突不会太大，不会由于表锁引起大量阻塞。

3.7、关于引擎的一些命令

• • show create table table_name：查看一张表的存储引擎。

• • create table .... ENGINE=InnoDB：创建表时指定存储引擎。

• • alter table table_name set ENGINE=MyISAM：修改一张表的存储引擎。

还有一条批量修改一个库所有表的存储引擎命令，如下：

mysql_convert_table_fromat --user=user_name --password=user_pwd --engine=MyISAM database_name;

使用时需要使用root账户来执行，最后跟上数据库的名字即可。

四、MySQL引擎篇总结

其实在MySQL还有另外一款引擎比较有特色，也就是Memory引擎，这款引擎在MySQL启动之后会完全基于内存工作，对比Redis这类K-V数据库，Memory引擎则是关系型的内存引擎，在有些场景下也会带来意想不到的额外收获。

MySQL能够崛起的根本原因，也在于它的引擎是支持可拔插式的，并且同一个数据库中，对于不同业务属性的表，可以选用、设置不同的存储引擎，这样能够集百家之长。相较于SQL Server、Oracle等这类数据库，功能更加多样化。

但并非所有MySQL引擎都具备优良的特性，不同引擎之间对数据的存储方式、查询数据的速度、支持的并发度也不同，虽然MySQL的可拔插式引擎，造就了MySQL特性的多样化，但其中各类引擎也参差不齐，所以如若对各款引擎没有太过深入的研究，最好还是根据业务在InnoDB、MyISAM两者之间做抉择！

最后，虽然MySQL的一个数据库中支持使用多种存储引擎，但也不要盲目使用，毕竟使用的存储引擎越多，对于每个引擎可分配的资源也就越少，拿典型的内存资源为例，如果一个库中使用了七八种引擎，那内存资源需要划分给这七八个引擎，这必然会导致各引擎之间相互影响，从而降低MySQL的整体吞吐量。