存储引擎

MySQL 体系结构

连接层：客户端和连接服务，主要做连接处理、验证授权及相关的安全方案。

↓

服务层：做核心功能服务，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化，部分内置函数的执行。

↓

引擎层（可插拔） 索引在该层实现， InnoDB 是 5.5 版本之后默认的存储引擎。负责 MySQL 中数据的存储和提取，服务器通过 API 和存储引擎进行通信。

↓

存储层：主要是将数据存储在文件系统之上，并完成和存储引擎的交互。

存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，存储引擎也被称为表类型。

创建表时，可以指定存储引擎

CREATE TABLE 表名(
  ...
) ENGINE = INNODB [COMMENT 表注释];

存储引擎特点

InnoDB

它是一种兼顾高可靠性和高性能的通用存储引擎

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

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

支持外键保证数据的完整性和正确性；

逻辑存储结构：表空间、段、区、页、行

MyISAM

MySQL 早期默认存储引擎

特点：不支持事务、不支持外键；

支持表锁、不支持行锁；

访问速度快

Memory

数据存在内存中，断电后数据不在了，只能作为临时表或缓存使用

特点：内存存放，默认使用 hash 索引

主要区别

事务、锁机制、外键

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+Tree 索引	支持	支持	支持
Hash 索引	-	-	支持
全文索引	5.6 版本后支持	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

如何选择

InnoDB：如果对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多更新、删除操作，适合选择它

MyISAM：如果以读操作和插入操作为主，有少量的更新和删除操作，对事务完整性、并发性要求不高，适合选择它。但是真正业务中一般用 MongoDB 来替代了，一般评论可以用 MongoDB。

MEMORY：数据放在内存中，访问速度快，但是缺陷是对表的大小有限制，太大的表放不进内存，而且不能保证数据安全性。一般业务中都会用 Redis 来替代，一般购物车可以用 Redis。

索引（重点）

概念

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

说白了，索引就是一个数据结构，用来提高查找速度。

优缺点

优点	缺点
提高数据检索效率，降低数据库 IO 成本	索引列也是占用空间的（存在磁盘，磁盘很便宜，该缺点一般可以忽略）
通过索引列对数据进行排序，降低数据排 序的成本，降低 CPU 消耗	索引大大提高了查询效率，同时也降低了更新表的速度，如对表进行插入、更新、删除时，效率会降低

索引结构

索引在第三层引擎层实现，在不同的存储引擎会有不同的结构，主要包含：

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

通常说的索引，没特别说明都指的是 B+ Tree

B-Tree

二叉树具有缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢

全往左子树插入形成链表结构：

            36
          34
        33
      32
    23
  22
17

红黑树：大数据量的情况下，层级较深，检索速度慢。（红黑树是 C++的 Set 和 Map 容器的底层实现）

B 树（多路平衡查找树）

以一棵最大度数为 5 (5 阶) 的 b-tree 为例（每个节点最多存储 4 个 key，5 个指针）。

树的度数指的是一个节点的子节点个数

演变过程可以去看这个网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B+ Tree

以一棵最大度数为 4 的 B+ Tree 为例：

和 B-Tree 的区别：

所有的数据都会出现在叶子节点

叶子节点会形成一个单向链表

MySQL 索引数据结构对经典的 B+ Tree 进行了优化，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的 B+ Tree，提高区间访问性能，其实就是变成了循环双向链表。

Hash

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

如果两个或多个键值，映射到了同一个相同的槽位，就产生了 hash 冲突，可以通过链表来解决。

特点：

只能用于对等比较（=， in )，不支持范围查询 (between, < , >)

无法利用索引完成排序

查询效率高，通常只需要一次检索就可以了（没有冲突的情况下），效率通常高于 B+ Tree 索引。

思考

为什么 InnoDB 选择 B+ Tree 索引

相对于二叉树，层级更少，搜索效率更高；

对与 B-Tree，无论叶子还是非叶子节点都会保存数据，当一页中存储的键值减少，指针也跟着减少，要同样保存大量数据时， B-Tree 只能增加树的高度，导致性能降低；

相对 Hash 索引，B+ Tree 支持范围匹配和排序操作；

索引分类

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

在 InnoDB 引擎中，又可以分为下面两种

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

聚集索引和二级索引都是 B+ Tree，只不过聚集索引叶子节点下面挂的是一行的数据，而二级索引叶子节点挂的是主键

聚集索引选取规则：

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

回表查询：先去从二级索引拿到对应的主键，再通过聚集索引去拿到对应的行数据

思考题

1.以下两条 SQL 哪条效率更高？

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

select * from user where id = 10;

select * from user where name = 'Arm';

分析：

第一条只需要走聚集索引，一次扫描即可。

如果根据 name 字段去查找，则需要先走二级索引找到对应的主键，然后再回表查询才能找到对应行数据，需要走两次查询

2. InnoDB 主键索引的 B+Tree 高度有多高？

主键索引是聚集索引，每一个节点都称为一页，而且每页的大小都是固定为 16 k 。 InnoDB 的指针占用 6 个字节的空间，如果主键类型为 bigint， 则占用 8 个字节。

假设：

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

如果树的高度为 2，可以存储多大的数据？

指针永远比键多一个，n 指的是主键个数

n _ 8 + (n+1) _ 6 = 16 * 1024

n 约等于 1170

则一共有 1171 个指针，每一个指针指向下面的子节点，一个子节点可以存储 16 行数据

1171 * 16 = 18736

索引语法

创建索引

一个索引可以关联多个字段，如果一个索引只关联一个字段则称为单列索引，否则称为联合索引

CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (字段名称, ...);

查看索引

SHOW INDEX FROM 表名;

删除索引

DROP INDEX 索引名 ON 表名;

SQL 性能分析工具

SQL 执行频率

MySQL 客户端连接成功后，通过 show [session | global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT 、 UPDATE、DELETE、SELECT 的执行频率：

下面是一个固定语法，7 个下划线

SHOW GLOBAL STATUS LIKE 'Com_______';

慢查询日志

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

查询是否开启慢查询日志开关：

show variables like 'slow_query_log';

MySQL 的慢查询日志默认没有开启， 需要在 MySQL 的配置文件（etc/my.cnf）中进行配置：

# 开启慢查询日志
slow_query_log=1
# 设置慢查询日志超时时间，查询时超过这个时间就会被记录
long_query_time=2

Profile 详情

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

SELECT @@have_profiling;

默认 profiling 是关闭的，可以通过 set 语句在 session/global 级别开启 profiling：

SET profiling = 1;

相关语法

# 查看每一条 SQL 的执行基本耗时情况：
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
desc select 字段列表 from 表名 where 条件;

字段含义

• id： select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序（ id 相同，执行顺序从上到下；id 不同，值越大，越先执行）
• select_type：表示 SELECT 的类型，常见的有 SIMPLE (简单表，即不使用表连接或者子查询)、 PRIMARY（主查询，即外层查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE 之后包含了子查询）等
• type：表示连接类型，性能由好到差的连接类型为 NULL 、system、const、eq_ref、ref、range、index、all；根据主键或者一般的索引来访问查询，一般都是 const 或者 eq_ref， NULL 在业务系统中不太可能出现，除非不访问表。
• possible_key：可能在这张表中用到的索引，一个或多个。
• key：实际使用的索引，如果为 null，说明没用索引
• key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，不损失精度的前提下，越短越好
• rows：MySQL 认为必须要查询的行数，在 innoDB 的表中，是一个估计值，并不总是准确的
• filtered：表示返回结果的行数占需读取行数的百分比，该值越大越好。

索引使用

验证索引效率

• 在未建立索引之前，先执行一个 SQL 语句，查看 SQL 耗时多少

• 针对相关字段建立索引（如果数据量太大，建立索引的时间可能会比较长）

• 然后再执行相同的 SQL 语句，再次查看查询耗时

索引使用原则

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过了索引中的某一列，索引将部分失效（这一列后面的字段索引失效），跟书写查询字段的顺序无关。

范围查询

联合索引中，出现范围查询（>，<），范围查询右侧的列索引失效，规避的方法是不使用 （ >, < ），在业务允许时改成（ >=, <= )。

如下面的 status = '0' 索引将会失效。

select * from user where major = '软件工程' and age > 30 and status = '0';

索引列运算

不要在索引列上进行运算操作，否则索引将失效 ，比如通过调用函数来进行查询。

字符串类型不加引号

比如查询某一个手机号的时候， phone = 后面的手机号不加引号，索引会失效

select * from user where phone = 18577777777

模糊查询

如果是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引将失效，要避免头部加模糊匹配的情况。

select * from user where major like '%工程';

or 连接的条件

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

数据分布影响

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

SQL 提示

优化数据库的一个重要手段，简单说就是人为告诉 MySQL 使用什么索引来达到优化的手段

use index（建议 MySQL 使用该索引，MySQL 还是会评估要不要用）

explain select * from user use index(idx_user_major) where major = '软件工程';

ignore index（忽略使用该索引）

explain select * from user ignore index(idx_user_major) where major = '软件工程';

force index（必须，强制 MySQL 使用该索引）:

explain select * from user force index(idx_user_major) where major = '软件工程';

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少 select _ 这种语法的使用。也就是说你建立了索引的字段，尽可能去在 select 中去查找已经进行索引的列，而不要通过 _ 把所有列的数据返回。

因为如果去查询没进行索引的列时，还会需要回表查询。

进行 explain 查询时，需要关注的是 extra 字段：

using index condition： 查找使用了索引，但是需要回表查询数据

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

思考

一张表有 4 个字段(id，username，password，status) 由于数据量大，需要对下面的 SQL 进行优化，说出最佳优化方案：

select id, username, password from user where username = 'dachui';

针对 username 和 password 建立联合索引即可，这样可以避免回表查询，只需要在二级索引中查询一次即可。如果只针对 username 建立索引的话，二级索引中找不到 password， 还会需要去聚集索引中进行一次回表查询。

前缀索引

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

语法：

n 代表要在字符串的前面几个字符来构建索引

create index 索引名称 on 表名(字段名(n));

前缀长度：

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

select count(distinct email) / count(*) from user;

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

单列索引和联合索引

单列：一个索引只有一个字段

联合：一个索引包含多个字段

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

索引设计原则

1. 针对数据量较大（数据量超过 100W），并且查询频繁的表建立索引

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

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

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

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

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

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