1．存储引擎

1.1MySQL体系结构

1.2存储引擎介绍

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

基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果

没有指定将自动选择默认的存储引擎(InnoDB)。

1．建表时指定存储引擎

CREATE TABLE表名(
字段1 字段1类型 [COMMENT字段1注释]
......
字段n 字段n类型 [COMMENT字段n注释]
) ENGINE = INNODB [COMMENT表注释];

2．查询当前数据库支持的存储引擎

show engines;

1.3存储引擎特点

上面我们介绍了什么是存储引擎，以及如何在建表时如何指定存储引擎，接下来我们就来介绍下来上面重点提到的三种存储引擎 InnoDB、MyISAM、Memory的特点。

1.3.1 InnoDB

1）．介绍

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

2）．特点

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

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

  • 支持外键FOREIGNKEY约束，保证数据的完整性和正确性;

3）．文件

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

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

show variables like innodb_file_per_table

当innodb_file_per_table参数开启时代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。我们直接打开MySQL的

数据存放目录：{MySQL安装目录}\Data ,这个目录下有很多文件夹，不同的文件夹代表不同的数据库，我们直接打开一个数据库对应的文件夹。例如：

每一个ibd文件就对应一张表，该文件是基于二进制存储的，不能直接用记事本打开，我们可以使用mysql提供的一个指令ibd2sdi，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

4）．逻辑存储结构

  • 表空间：InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个segment段.

  • 段：表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。

  • 区：区是表空间的单元结构，每个区的大小为1M。默认情况下,InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。

  • 页：页是组成区的最小单元，页也是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5个区。

  • 行：InnoDB存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段（后面会详细介绍）。

1.3.2 MyISAM

1）．介绍

MyISAM是MySQL早期的默认存储引擎。

2）．特点

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

3）．文件

xxx.sdi：存储表结构信息

xxx.MYD::存储数据

xxx.MYI：存储索引

1.3.3 Memory

1）．介绍

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

临时表或缓存使用。

2）．特点

内存存放

hash索引 (默认)

3）.文件

xxx.sdi：存储表结构信息

2．索引

2.1索引概述

2.1.1 介绍

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

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

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

2.2.2 特点

2.2 索引结构

2.2.1 概述

2.2.2二叉树

如果选择二叉树作为索引结构，会存在以下缺点：

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

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

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数

据，最终形成的数据结构也是一颗平衡的二叉树，结构如下：

由此红黑树可以解决上面的问题1。但是，由于红黑树也是一颗二叉树，所以也会存在一个缺点：

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

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

2.2.3 B-Tree

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

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

个指针

特点:

  • 5阶的B树，每一个节点最多存储4个keY，对应5个指针。

  • 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。

  • 在B树中，非叶子节点和叶子节点都会存放数据。

2.2.4 B+Tree

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

下其结构示意图：

最终我们看到，B+Tree与B-Tree相比，主要有以下三点区别:

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

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

  • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

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

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点

的链表指针，就形成了带有顺序指针的B+Treé，提高区间访问的性能，利于排序。

2.2.5 Hash

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

1）．结构

哈希索引l就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

2）．特点

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

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

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

3）．存储引擎支持

在MySQL中，支持hash索引l的是Memory存储引擎。而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引|擎根据B+Tree索引l在指定条件下自动构建的。

2.3索引分类

2.3.1 索引分类

在MySQL数据库，将索引I的具体类型主要分为以下几类：主键索引(PRIMARY)、唯一索引(UNIQUE)、常规索引、全文索引(FULLTEXT)。

2.3.2聚集索引&二级索引

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

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引l。

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

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

由于聚集索引的叶子节点下挂的是这一行的数据，二级索引的叶子节点下挂的是该字段值对应的主键值，所以在一定条件下就会产生回表查询

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

2.4 索引语法

CREATE[ UNIQUE 丨 FULLTEXT ] INDEX index_name ON table_name(
index col name,...);

2）．查看索引

SHOW INDEX FROM TABLE_NAME;

3）．删除索引

DROP INDEX index_name ON table_name;

2.5 SQL性能分析

2.5.1 SQL执行频率

MySQL客户端连接成功后，通过如下命令可以提供服务器状态信息，查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:

-- session是查看当前会话
-- global是查询全局数据;
show [session I global] status LIKE 'Com_______'

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

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

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

2.5.2 慢查询日志

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

SQL语句的日志。MySQL的慢查询日志默认没有开启，我们可以使用以下命令查看一下系统变量 slow_query_log

-- 查看slow_query_log是否开启
show variables like 'slow_query_log';

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

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

配置完毕之后，重新启动MySQL服务器

2.5.3 profile详情

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

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

-- 查看当前MySQL是否支持profile操作
SELECT @@have_profiling ;
-- 查看profile操作是否开启
SELECT @@profiling
-- 开启profiling
SET profiling = 1;

开启后执行一系列SQL后使用如下命令查看详情

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的sQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的sQL语句cPU的使用情况
show profile cpu for query query_id;

2.5.4 explain

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

-- 直接在select语句之前加上关键字 explain 或 desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；