mysql小结-索引及优化

索引

How MySQL Uses Indexes

全文索引

• MySQL支持全文索引和搜索：
  • MySQL中的全文索引是type的索引 FULLTEXT。
  • 全文索引只能使用InnoDB或MyISAM表格，并且只能用于创建CHAR，VARCHAR或 TEXT列。
  • MySQL提供了一个内置的全文ngram解析器，该解析器支持中文，日文和韩文（CJK），以及一个用于日语的可安装的MeCab全文分析器插件。第12.9.8节“ ngram全文解析器”和 第12.9.9节“ MeCab全文解析器插件”中概述了解析差异。
  • 甲FULLTEXT索引定义可以在被给予CREATE TABLE的语句中创建表时，或者稍后使用添加ALTER TABLE或 CREATE INDEX。
  • 对于大型数据集，将数据加载到没有FULLTEXT索引的表中然后再创建索引比将数据加载到具有现有FULLTEXT索引的表中要快得多。

全文搜索分为三种：

• 自然语言搜索将搜索字符串解释为自然人类语言中的短语（自由文本中的短语）。除双引号（“）字符外，没有特殊的运算符。适用停用词列表。有关停用词列表的更多信息，请参见第12.9.4节“全文停用词 "。

  如果指定了IN NATURAL LANGUAGE MODE修饰符或未指定修饰符，则全文搜索是自然语言搜索 。有关更多信息，请参见 第12.9.1节“自然语言全文本搜索”。

• 布尔搜索使用特殊查询语言的规则解释搜索字符串。该字符串包含要搜索的单词。它还可以包含指定要求的运算符，以使匹配行中的单词必须存在或不存在，或者其权重应高于或低于平常。某些常见单词（停用词）将从搜索索引中省略，并且如果出现在搜索字符串中则不匹配。该IN BOOLEAN MODE修饰符指定一个布尔搜索。有关更多信息，请参见 第12.9.2节“布尔全文本搜索”。

• 查询扩展搜索是对自然语言搜索的修改。搜索字符串用于执行自然语言搜索。然后，将搜索返回的最相关行中的单词添加到搜索字符串中，然后再次执行搜索。该查询返回第二个搜索中的行。该IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION或WITH QUERY EXPANSION修改指定的查询扩展搜索。有关更多信息，请参见 第12.9.3节“具有查询扩展的全文本搜索”。

哈希索引

只有memony引擎显示支持哈希索引

• 仅用于使用=or <=> 运算符的相等比较 （但非常快），不用于比较运算符
• 优化器无法使用哈希索引来加快ORDER BY操作速度
• 仅整个键可用于搜索行

组合索引

mysql的联合索引的B+树

最左匹配

like会不会走索引

根据最左匹配原则：

where like '%ssss%' ;不走索引

where like 'ssss%'； 走索引

where like '%ssss'； 不走索引

有number name age adds四个列时，组合索引 idx (number age adds)

explain select * from bigtables where number = 101 and age >18 and adds =18;
走联合索引number的索引,Using index condition

mysql> explain select * from bigtables where number = 101 and age >18 and adds =18;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | bigtables | NULL       | range | idx           | idx  | 10      | NULL |  104 |    10.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

explain select * from bigtables where number = 101 and age like '1%' and adds =18;
走联合索引number的索引

mysql> explain select * from bigtables where number = 101 and age like '1%' and adds =18;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | bigtables | NULL       | ref  | idx           | idx  | 5       | const |  106 |     1.11 | Using index condition |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

explain select * from bigtables where number = 101 and age like '%9' and adds =18;
走联合索引number的索引

mysql> explain select * from bigtables where number = 101 and age like '%9' and adds =18;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | bigtables | NULL       | ref  | idx           | idx  | 5       | const |  106 |     1.11 | Using index condition |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

explain select * from bigtables where age like '%9' and adds =18;
不走索引

mysql> explain select * from bigtables where age like '%9' and adds =18;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | bigtables | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1010 |     1.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

explain select * from bigtables where adds =18;
不走索引

mysql> explain select * from bigtables where adds =18;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | bigtables | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1010 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

B tree和B+ tree

。。。

单列索引

• 聚集索引。。。

• 非聚集索引。。。

外键

为什么不推荐使用外键约束

foreign-key-optimization

索引覆盖

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中，可以直接使用索引查询而不需要回表。这就是索引覆盖，通过使用覆盖索引，可以减少搜索树的次数，是常用的性能优化手段。

mysql> explain select number, age, adds from bigtables where  number = 101;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | bigtables | NULL       | ref  | idx           | idx  | 5       | const |  106 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

两个单列索引，查询也能触发索引覆盖

组合索引也可以触发索引覆盖

索引下推

对于bigtables表，我们现在有（name,number）联合索引
如果现在有一个需求，查出名称中以“w”开头且number小于等于101的信息，语句如下：

"select * from bigtables where name like 'w%' and number> 10;".

语句有两种执行可能：

• 1、根据（name,number）联合索引查询所有满足名称以“w”开头的索引，然后回表查询出相应的全行数据，然后再筛选出满足number小于等于101的用户数据。

• 2、根据（name,number）联合索引查询所有满足名称以“w”开头的索引，然后直接再筛选出number小于等于101的索引，之后再回表查询全行数据。

明显的，第二种方式需要回表查询的全行数据比较少，这就是mysql的索引下推。
mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制

SET optimizer_switch = 'index_condition_pushdown=off';

注意点：
1、innodb引擎的表，索引下推只能用于二级索引。

就像之前提到的，innodb的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

2、索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引。

假设表t有联合索引（a,b）,下面语句可以使用索引下推提高效率
select * from t where a > 2 and b > 10;

参考: mysql索引篇之覆盖索引、联合索引、索引下推

优化小细节

数据库操作层面

1 不要使用表达式

虽然执行计划中有可能会判断走索引，但是 rows依然是全表扫描了，1010条

mysql> explain select * from bigtables where number+1  = 103;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | bigtables | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1010 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from bigtables where number = 103;
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | bigtables | NULL       | ref  | idx           | idx  | 5       | const |   98 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2 使用索引扫描进行排序时

为排序使用索引Order By优化

mysql默认的排序是asc。

多个排序时，一个逆序，其他asc的时候也不会走索引排序，而同时都desc时候，不会走索引排序

或者一个是desc，也不会走索引排序。Using filesort

mysql> explain select * from abc2 where c >1 order by c desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | abc2  | NULL       | index | NULL          | idx_abc | 72      | NULL |    3 |    33.33 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

3 union all, in, or

or：

number,age为联合索引：
mysql> explain select * from bigtables where number = 103 or age = 19;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | bigtables | NULL       | ALL  | idx           | NULL | NULL    | NULL | 1010 |    19.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

a b c为单列索引：
mysql> explain select * from abc where b = 2 or c = 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | abc   | NULL       | ALL  | idx_b,idx_c   | NULL | NULL    | NULL |   10 |    23.50 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

union all执行计划分多步执行：

mysql> explain select * from abc  union all  select * from abc2\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: abc
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_abc
      key_len: 14
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: abc2
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_abc
      key_len: 72
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

ERROR: 
No query specified

in ：

mysql> explain select * from bigtables where number in (101);
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | bigtables | NULL       | ref  | idx           | idx  | 5       | const |  106 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from bigtables where number in (101,102);
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | bigtables | NULL       | ALL  | idx           | NULL | NULL    | NULL | 1010 |    21.68 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

4 类型转换可能会造成索引失效

ex: name 为varchar 时，where name = 123 会造成索引失效

MySQL小结：锁-3.1.2 行锁变表锁

5 对于数据库中不常用数据可以进行备份或者压缩操作，减少数据库压力

6 使用explain进行检查

EXPLAIN Output Format

rows:

官网中有一句话：

rows (JSON name: rows)

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.
//对于InnoDB表，此数字是估计值，可能并不总是准确的。

type

type	JSON名称	含义
id	select_id	该SELECT标识符
select_type	None	该SELECT类型
table	table_name	输出行表
partitions	partitions	匹配的分区
type	access_type	联接类型
possible_keys	possible_keys	可能的索引选择
key	key	实际选择的索引
key_len	key_length	所选键的长度
ref	ref	与索引比较的列
rows	rows	估计要检查的行
filtered	filtered	按表条件过滤的行百分比
Extra	None	附加信息

Extra:

Using index condition: 通过访问索引并首先对其进行测试以确定是否读取完整的表行来读取表。除非有必要，否则索引信息将用 索引下推 读取整个表行

Using index：仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。

Using filesort：MySQL必须额外进行一遍，以找出如何按排序顺序检索行。排序是通过根据联接类型遍历所有行并存储与该WHERE子句匹配的所有行的排序键和指向该行的指针来完成的。然后对键进行排序，并按排序顺序检索行。

Using join buffer (Block Nested Loop)， Using join buffer (Batched Key Access)：来自较早联接的表被部分读取到联接缓冲区中，然后从缓冲区中使用它们的行来执行与当前表的联接。

Using temporary：为了解决该查询，MySQL需要创建一个临时表来保存结果。如果查询包含GROUP BY和 ORDER BY子句以不同的方式列出列，通常会发生这种情况。

Using where：WHERE子句用于限制来匹配下一个表或发送到客户端的行。

7 查看查询语句每一个步骤的时间

show-profile

官网中有一句话：该SHOW PROFILE和 SHOW PROFILES语句被取消，并将在未来的MySQL版本中删除，改用性能模式；

The SHOW PROFILE and SHOW PROFILES statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead; see Section 25.19.1, “Query Profiling Using Performance Schema”.

示例：

SET profiling = 1;

select * from abc;

SHOW PROFILE FOR QUERY 1; //  SHOW PROFILES;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000056 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000029 |
| init                 | 0.000019 |
| System lock          | 0.000011 |
| optimizing           | 0.000006 |
| statistics           | 0.000012 |
| preparing            | 0.000009 |
| executing            | 0.000003 |
| Sending data         | 0.000053 |
| end                  | 0.000006 |
| query end            | 0.000009 |
| closing tables       | 0.000009 |
| freeing items        | 0.000014 |
| cleaning up          | 0.000013 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

8 数据库性能监控 Performance Schema

使用性能模式进行查询分析

9 数据库参数设置：线程池等。。。

数据库设计层面

1 索引一定要加适量

创建索引会使查询操作变得更加快速,但是会降低增加、删除、更新操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新;

2 考虑前缀索引

前缀索引是一种能使索引更小，更快的有效办法

注意：MySQL中无法使用前缀索引进行ORDER BY和GROUP BY，也无法用来进行覆盖扫描

MySQL前缀索引和索引选择性

3 更新十分频繁，数据区分度不高的字段不推荐建立索引

• sex: 男，女
• 更新会变动B+树，更新频繁的字段建立索引会大大降低数据库性能

4 创建索引的列，不允许为null

5 当需要表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

6 表设计时字段优化

• 尽可能选择小的数据类型 ex: tinyint
• 字段不要过多，取名见名思义，加注释，预留字段供扩展

MySQL数据库表结构设计优化技巧

全表扫描

table-scan-avoidance

使用EXPLAIN分析SQL时，当列出执行计划表中type字段值为ALL时，代表需要全表扫描 ，mysql会在一下情况使用全表扫描：

• 所扫描的数据表非常小，因此全表扫描的速度要远快于使用索引， 对于少于10行且行长较短的表
• 在ON或WHERE子句中没有可以使用的索引列
• 在使用索引列与常量值比较，并且MySQL服务已经通过索引树计算出常量值覆盖了大部分表数据，此时使用全表扫描是比较快的。
• 当前正在使用一个对于其他列来说基数较低的索引(每个索引值在另一列都有多行行对应)，在这种情况下，使用索引可能会引起较多的索引查询，MySQL认为全表扫描比较快。

避免全表扫描

对于小型表，表扫描通常是合适的，并且对性能的影响可以忽略不计。对于大型表，使用以下操作可以帮助优化器对于是否进行全表扫描做出正确的判断：

• 使用ANALYZE TABLE tbl_name语句来更新被扫描表中索引的分布。

• 使用FORCE INDEX 语句来强制优化器对该表放弃全表扫描而使用索引，如下：

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;

force index()指令可以指定本次查询使用哪个索引。

mysql优化器会计算出一个合适的索引，但是这个索引不一定是最好的。force index()指令可以避免MySql优化器用到了一个低效的索引。

• 设置启动参数–max-seeks-for-key=1000或使用参数SET max_seeks_for_key=1000来通知优化器如果没有索引扫描会导致过千的索引查找。

join

执行联接时从其他表中检索行。如果声明相同的类型和大小，MySQL可以更有效地在列上使用索引。在这种情况下， VARCHAR与 CHAR被认为是相同的，如果它们被声明为相同的大小。例如， VARCHAR(10)和 CHAR(10)是相同的大小，但是 VARCHAR(10)和 CHAR(15)不是。

• 如果ORDER BYand GROUP BY子句中的所有列 都来自同一表，则在连接时优先使用该表。
• 如果存在一个ORDER BY子句和另一个GROUP BY子句，或者如果 ORDER BY或GROUP BY 包含连接队列中第一个表以外的表中的列，则会创建一个临时表。

压缩如何用于InnoDB表

MySQL在著名的zlib库的帮助下实现了压缩，该 库实现了LZ77压缩算法。这种压缩算法在CPU利用率和数据大小减小方面都是成熟，可靠且有效的。该算法是 “ 无损的 ”，因此始终可以从压缩形式中重建原始的未压缩数据。LZ77压缩通过查找在要压缩的数据内重复的数据序列来工作。数据中的值模式决定了压缩的程度，但是典型的用户数据通常压缩50％或更多。

由于B树页面经常更新，因此需要特殊对待。重要的是，最小化B树节点的分割次数，以及最小化解压缩和重新压缩其内容的需求。

MySQL使用的一种技术是以未压缩的形式维护B树节点中的一些系统信息，从而促进某些就地更新。例如，这允许对行进行删除标记和删除，而无需任何压缩操作。

另外，MySQL试图避免在更改索引页时不必要的解压缩和重新压缩。在每个B树页面中，系统保留一个未压缩的 ’ modification log '以记录对该页面所做的更改。小记录的更新和插入可以写入此修改日志，而无需完全重建整个页面。

当修改日志的空间用完时，InnoDB解压缩页面，应用更改并重新压缩页面。如果再压缩失败（称为一个的情况 压缩破坏），B-树节点被分割，并重复该过程，直到更新或插入成功。

MYISAM

MyISAM简介

每个MyISAM表都以三个文件存储在磁盘上。这些文件的名称以表名开头，并具有扩展名以指示文件类型。一个.frm 文件存储表格式。数据文件的 扩展名为.MYD（MYData）。索引文件的扩展名为.MYI （MYIndex）。

要明确指定要使用MyISAM 表，使用ENGINE表选项进行指示：

CREATE TABLE t (i INT) ENGINE = MYISAM;

MyISAM 表具有以下特征：

• 所有数据值都先以低字节存储。这使数据机和操作系统独立。二进制可移植性的唯一要求是机器使用二进制补码带符号整数和IEEE浮点格式。这些要求已在主流机器中广泛使用。二进制兼容性可能不适用于有时具有特殊处理器的嵌入式系统。

  先存储低字节数据没有明显的速度损失；表行中的字节通常是未对齐的，按顺序读取未对齐的字节所需的处理要多于反向顺序。而且，与其他代码相比，服务器中获取列值的代码不是时间紧迫的。

• 所有数字键值都先存储高字节，以实现更好的索引压缩。

• 在支持大文件的文件系统和操作系统上，支持大文件（文件长度最大为63位）。

• 表格中 最多只能 有（2^32）2（1.844E + 19）行MyISAM。

• 每个MyISAM 表的最大索引数为64。

  每个索引的最大列数为16。

• 最大密钥长度为1000个字节。也可以通过更改源代码并重新编译来更改。对于密钥超过250字节的情况，将使用比默认值1024字节更大的密钥块大小。

• 当按排序顺序插入行时（如使用 AUTO_INCREMENT列时），索引树将被拆分，以便高级节点仅包含一个键。这样可以提高索引树中的空间利用率。

• AUTO_INCREMENT 支持每个表 对一列的内部处理。MyISAM 自动更新此列的 INSERT和 UPDATE操作。这样可使 AUTO_INCREMENT列更快（至少10％）。序列顶部的值在删除后不会重复使用。（当一AUTO_INCREMENT列定义为多列索引的最后一列时，确实会重复使用从序列顶部删除的值。）AUTO_INCREMENT可以使用ALTER TABLE或 myisamchk重置该 值 。

• 将删除与更新和插入混合在一起时，动态大小的行的碎片化要少得多。通过自动组合相邻的已删除块并通过扩展块（如果删除下一个块）来完成此操作。

• MyISAM支持并发插入：如果表在数据文件的中间没有空闲块，则可以INSERT在其他线程从表读取数据 的同时向其中添加新行。由于删除行或使用比当前内容更多的数据更新动态长度行，可能会出现空闲块。当所有可用块用完（填充）时，以后的插入将再次并发。请参见 第8.11.3节“并发插入”。

• 您可以将数据文件和索引文件放在不同物理设备上的不同目录中，以使用DATA DIRECTORY和的INDEX DIRECTORY表选项来提高速度CREATE TABLE。请参见第13.1.18节“ CREATE TABLE语句”。

• BLOB并且 TEXT列可以建立索引。

• NULL索引列中允许使用值。每个密钥占用0到1个字节。

• 每个字符列可以具有不同的字符集。请参见 第10章，字符集，整理，Unicode。

• MyISAM索引文件中 有一个标志，指示表是否已正确关闭。如果 使用系统变量集启动 mysqldmyisam_recover_options，MyISAM则在打开表时会自动对其进行检查，如果未正确关闭表，则会对其进行修复。

• 如果使用该--update-state 选项运行 myisamchk，则将表标记为已选中。myisamchk --fast仅检查那些没有此标记的表。

• myisamchk --analyze存储部分键以及整个键的统计信息。

• myisampack可以包装BLOB和 包装 VARCHAR。

MyISAM 还支持以下功能：

• 支持真实VARCHAR类型；一VARCHAR列开始与存储在一个或两个字节的长度。
• 带VARCHAR列的表可能具有固定或动态的行长。
• 表格中VARCHAR和 的长度之 和 CHAR可能不超过64KB。
• 任意长度限制UNIQUE。

InnoDB

InnoDB简介
InnoDB是一种兼顾了高可靠性和高性能的通用存储引擎。在MySQL 5.7中，InnoDB是默认的MySQL存储引擎。除非配置了其他默认存储引擎，否则发出CREATE TABLE不带ENGINE= 子句的语句将创建一个InnoDB表。

InnoDB的主要优势

• 它的DML操作遵循 ACID模型，并 具有具有 提交，回滚和 崩溃恢复 功能的事务， 以保护用户数据。 第14.2节“ InnoDB和ACID模型”。
• 行级锁定和Oracle风格的一致读取可提高多用户并发性和性能。第14.7节“ InnoDB锁定和事务模型”。
• InnoDB表格将您的数据安排在磁盘上，以基于主键优化查询 。每个 InnoDB表都有一个称为聚集索引的主键索引，该索引 组织数据以最小化主键查找的I / O。第14.6.2.1节“聚集索引和二级索引”。
• 维护数据 完整性， InnoDB支持 FOREIGN KEY约束。使用外键检查插入，更新和删除，以确保它们不会导致不同表之间的不一致。 第13.1.18.6节“外键约束”。

参考资料：

MySQL索引背后的数据结构及算法原理

涉及到的数据库表结构为：

mysql> show index from bigtables;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bigtables |          0 | PRIMARY  |            1 | id          | A         |        1010 |     NULL | NULL   |      | BTREE      |         |               |
| bigtables |          1 | idx      |            1 | number      | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| bigtables |          1 | idx      |            2 | age         | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| bigtables |          1 | idx      |            3 | adds        | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> show index from abc;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abc   |          0 | PRIMARY  |            1 | a           | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| abc   |          1 | idx_a    |            1 | a           | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| abc   |          1 | idx_b    |            1 | b           | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| abc   |          1 | idx_c    |            1 | c           | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)