1.MySQL体系架构

1.1.MySQL的分支与变种

MySQL变种有好几个，主要有三个久经考验的主流变种：Percona Server，MariaDB和 Drizzle。它们都有活跃的用户社区和一些商业支持，均由独立的服务供应商支持。同时还有几个优秀的开源关系数据库，值得我们了解一下。

1.1.1.Drizzle

Drizzle是真正的MySQL分支，而且是完全开源的产品，而非只是个变种或增强版本。它并不与MySQL兼容不能简单地将MySQL后端替换为Drizzle。

Drizzle与MySQL有很大差别，进行了一些重大更改，甚至SQL语法的变化都非常大，设计目标之一是提供一种出色的解决方案来解决高可用性问题。在实现上，Drizzle清除了一些表现不佳和不必要的功能，将很多代码重写，对它们进行了优化，甚至将所用语言从C换成了C++。

此外，Drizzle另一个设计目标是能很好的适应具有大量内容的多核服务器、运行Linux的64位机器、云计算中使用的服务器、托管网站的服务器和每分钟接收数以万计点击率的服务器并且大幅度的削减服务器成本。

1.1.2.MariaDB

在Sun收购MySQL后，Monty Widenius，这位MySQL的创建者，因不认同MySQL开发流程而离开Sun。他成立了Monty程序公司，创立了MariaDB。MariaDB的目标是社区开发，Bug修复和许多的新特性实际上，可以将MariaDB视为MySQL的扩展集，它不仅提供MySQL提供的所有功能，还提供其他功能。MariaDB是原版MySQL的超集，因此已有的系统不需要任何修改就可以运行。

诸如Google，Facebook、维基百科等公司或者网站所使用了MariaDB。不过Monty公司不是以赢利为目的，而是由产品驱动的，这可能会带来问题，因为没有赢利的公司不一定能长久维持下去。

1.1.3.Percona Server

由领先的MySQL咨询公司Percona发布，Percona公司的口号就是“The Database Performance Experts”，Percona的创始人也就是《高性能MySQL》书的作者。

Percona Server是个与MySQL向后兼容的替代品，它尽可能不改变SQL语法、客户端/服务器协议和磁盘上的文件格式。任何运行在MySQL上的都可以运行在Percona Server上而不需要修改。切换到Percona Server只需要关闭MySQL和启动PerconaServer，不需要导出和重新导入数据。

Percona Server有三个主要的目标：透明，增加允许用户更紧密地查看服务器内部信息和行为的方法。比如慢查询日志中特别增加的详细信息；性能，Percona Server包含许多性能和可扩展性方面的改进，还加强了性能的可预测性和稳定性。其中主要集中于InnoDB；操作灵活性，Percona Server使操作人员和系统管理员在让MySQL作为架构的一部分而可靠并稳定运行时提供了很多便利。

一般来说，Percona Server中的许多特性会在后来的标准MySQL中出现。

国内公司阿里内部就运行了上千个Percona Server的实例。

1.2.MySQL的替代

1.2.1.Postgre SQL

PostgreSQL称自己是世界上最先进的开源数据库，同时也是个一专多长的全栈数据库。最初是1985年在加利福尼亚大学伯克利分校开发的。

PostgreSQL 的稳定性极强，在崩溃、断电之类的灾难场景下依然可以保证数据的正确；在高并发读写，负载逼近极限下，PostgreSQL的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，表现的非常稳定，而 MySQL 明显出现一个波峰后下滑；

PostgreSQL多年来在GIS(地理信息)领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PostgreSQL有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多。所以总的来说，PostgreSQL更学术化一些，在绝对需要可靠性和数据完整性的时候，PostgreSQL是更好的选择。但是从商业支持、文档资料、易用性，第三方支持来说，MySQL无疑更好些。

1.2.2.SQLite

SQLite是世界上部署最广泛的数据库引擎，为物联网（IoT）下的数据库首选，并且是手机，PDA，甚至MP3播放器的下的首选。SQLite代码占用空间小，并且不需要数据库管理员的维护。SQLite没有单独的服务器进程，提供的事务也基本符合ACID。当然，简单也就意味着功能和性能受限。

2.MySql基础

2.1.MySQL体系架构

可以看出MySQL是由连接池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。

连接池

由于每次建立建立需要消耗很多时间，连接池的作用就是将这些连接缓存下来，下次可以直接用已经建立好的连接，提升服务器性能。

管理工具和服务

系统管理和控制工具，例如备份恢复、Mysql复制、集群等

SQL接口

接受用户的SQL命令，并且返回用户需要查询的结果。比如select ... from就是调用SQL接口

解析器

SQL命令传递到解析器的时候会被解析器验证和解析。解析器主要功能：1、将SQL语句分解成数据结构，后续步骤的传递和处理就是基于这个结构的。2、将SQL语句分解成数据结构，后续步骤的传递和处理就是基于这个结构的。

优化器

查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。

缓存器

查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

存储引擎(后面会细讲)

文件系统(后面会细讲)

2.1.1.连接层

当MySQL启动（MySQL服务器就是一个进程），等待客户端连接，每一个客户端连接请求，服务器进程会创建一个线程专门处理与这个客户端的交互。当客户端与该服务器断开之后，不会立即撤销线程，只会把他缓存起来等待下一个客户端请求连接的时候，将其分配给该客户端。每个线程独立，拥有各自的内存处理空间。

以下命令可以查看最大的连接数：

show VARIABLES like '%max_connections%'

连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客户端对某个数据库某个表的某个操作）

2.1.2.Server层(SQL处理层)

这一层主要功能有：SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需提供的功能（引擎需对外提供接口）），例如：存储过程、触发器、视图等。

当然作为一个SQL的执行流程如下：

1.如果是查询语句（select语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）

2.解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；

3.优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

2.1.2.1.缓存（了解即可）

show variables like '%query_cache_type%'   -- 默认不开启

show variables like '%query_cache_size%'  --默认值1M

SET GLOBAL query_cache_type = 1; --会报错

query_cache_type只能配置在my.cnf文件中！

缓存在生产环境建议不开启，除非经常有sql完全一模一样的查询

缓存严格要求2次SQL请求要完全一样，包括SQL语句，连接的数据库、协议版本、字符集等因素都会影响

从8.0开始，MySQL不再使用查询缓存，那么放弃它的原因是什么呢？

MySQL查询缓存是查询结果缓存。它将以SEL开头的查询与哈希表进行比较，如果匹配，则返回上一次查询的结果。进行匹配时，查询必须逐字节匹配，例如 SELECT * FROM e1; 不等于select * from e1;

此外，一些不确定的查询结果无法被缓存，任何对表的修改都会导致这些表的所有缓存无效。因此，适用于查询缓存的最理想的方案是只读，特别是需要检查数百万行后仅返回数行的复杂查询。如果你的查询符合这样一个特点，开启查询缓存会提升你的查询性能。

随着技术的进步，经过时间的考验，MySQL的工程团队发现启用缓存的好处并不多。

首先，查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能有改善，因此无法预测其性能。

其次，查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。

通过基准测试发现，大多数工作负载最好禁用查询缓存(5.6的默认设置)：按照官方所说的：造成的问题比它解决问题要多的多，弊大于利就直接砍掉了。

2.1.3.存储引擎层

从体系结构图中可以发现，MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构和实际文件读写的实现，每个存储引擎开发者可以按照自己的意愿来进行开发。需要特别注意的是，存储引擎是基于表的，而不是数据库。

插件式存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。由于MySQL数据库的开源特性，用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎。若用户对某一种存储引擎的性能或功能不满意，可以通过修改源码来得到想要的特性，这就是开源带给我们的方便与力量。

由于MySQL数据库开源特性，存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。有些第三方存储引擎很强大，如大名鼎鼎的InnoDB存储引擎（最早是第三方存储引擎，后被Oracle收购)，其应用就极其广泛，甚至是MySQL数据库OLTP(Online Transaction Processing在线事务处理）应用中使用最广泛的存储引擎。

2.1.3.1.MySQL官方引擎概要

InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。如果要学习存储引擎，InnoDB也是一个非常好的值得花最多的时间去深入学习的对象，收益肯定比将时间平均花在每个存储引擎的学习上要高得多。所以InnoDB引擎也将是我们学习的重点。

MylSAM存储引擎

在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。尽管MyISAM引擎不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小、可以忍受修复（repair）操作，则依然可以继续使用MyISAM（但请不要默认使用MyISAM，而是应当默认使用InnoDB)。但是MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。MyISAM很容易因为表锁的问题导致典型的的性能问题。

Mrg_MylSAM

Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。

Archive引擎

Archive存储引擎只支持INSERT和SELECT操作，在MySQL 5.1之前也不支持索引。Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用，这类应用做数据分析时往往需要全表扫描。或者在一些需要更快速的INSERT操作的场合下也可以使用。Archive引擎不是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。

Blackhole引擎

Blackhole引擎没有实现任何的存储机制，它会丢弃所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。但这种引擎在应用方式上有很多问题，因此并不推荐。

CSV引擎

CSV引擎可以将普通的CSV文件(逗号分割值的文件）作为MySQL的表来处理，但这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。可以将Excel等的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL 中打开使用。同样，如果将数据写入到一个CSV引擎表，其他的外部程序也能立即从表的数据文件中读取CSV格式的数据。因此CSV引擎可以作为一种数据交换的机制，非常有用。

Federated引擎

Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如Microsoft SQL Server和 Oracle的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

Memory 引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表(以前也叫做HEAP表）是非常有用的。Memory表至少比MyISAM 表要快一个数量级，因为每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率，不需要进行磁盘I/O。所以Memory表的结构在重启以后还会保留，但数据会丢失。

Memory表支持 Hash索引，因此查找操作非常快。虽然Memory表的速度非常快，但还是无法取代传统的基于磁盘的表。Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR 列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。

NDB集群引擎

使用MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing 的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（(MySQL Cluster)。

2.1.3.2.值得了解的第三方引擎

Percona的 XtraDB存储引擎

基于InnoDB引擎的一个改进版本，已经包含在Percona Server和 MariaDB中，它的改进点主要集中在性能、可测量性和操作灵活性方面。XtraDB可以作为InnoDB的一个完全的替代产品，甚至可以兼容地读写InnoDB的数据文件，并支持InnoDB的所有查询。

TokuDB引擎

使用了一种新的叫做分形树(Fractal Trees)的索引数据结构。该结构是缓存无关的，因此即使其大小超过内存性能也不会下降，也就没有内存生命周期和碎片的问题。TokuDB是一种大数据（Big Data)存储引擎，因为其拥有很高的压缩比，可以在很大的数据量上创建大量索引。现在该引擎也被Percona公司收购。

Tips ：分形树，是一种写优化的磁盘索引数据结构。分形树的写操作（Insert/Update/Delete）性能比较好，同时它还能保证读操作近似于B+树的读性能。据测试结果显示， TokuDB分形树的写性能优于InnoDB的B+树，读性能略低于B+树。分形树核心思想是利用节点的MessageBuffer缓存更新操作，充分利用数据局部性原理，将随机写转换为顺序写，这样极大的提高了随机写的效率。

Infobright

MySQL默认是面向行的，每一行的数据是一起存储的，服务器的查询也是以行为单位处理的。而在大数据量处理时，面向列的方式可能效率更高，比如HBASE就是面向列存储的。

Infobright是最有名的面向列的存储引擎。在非常大的数据量（数十TB)时，该引擎工作良好。Infobright是为数据分析和数据仓库应用设计的。数据高度压缩，按照块进行排序，每个块都对应有一组元数据。在处理查询时，访问元数据可决定跳过该块，甚至可能只需要元数据即可满足查询的需求。但该引擎不支持索引，不过在这么大的数据量级，即使有索引也很难发挥作用，而且块结构也是一种准索引 (quasi-index)。Infobright需要对MySQL服务器做定制，因为一些地方需要修改以适应面向列存储的需要。如果查询无法在存储层使用面向列的模式执行，则需要在服务器层转换成按行处理，这个过程会很慢。Infobright有社区版和商业版两个版本。

2.1.3.3.选择合适的引擎

这么多存储引擎，我们怎么选择?大部分情况下，InnoDB都是正确的选择，所以在MySQL 5.5版本将InnoDB作为默认的存储引擎了。对于如何选择存储引擎，可以简单地归纳为一句话:“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎”。比如，MySQL中只有MyISAM支持地理空间搜索。

当然，如果不需要用到InnoDB的特性，同时其他引擎的特性能够更好地满足需求，也可以考虑一下其他存储引擎。举个例子，如果不在乎可扩展能力和并发能力，也不在乎崩溃后的数据丢失问题，却对InnoDB的空间占用过多比较敏感，这种场合下选择MyISAM就比较合适。

除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一系列复杂的问题，以及一些潜在的bug和边界问题。存储引擎层和服务器层的交互已经比较复杂，更不用说混合多个存储引擎了。至少，混合存储对一致性备份和服务器参数配置都带来了一些困难。

2.1.3.4.表引擎的转换

有很多种方法可以将表的存储引擎转换成另外一种引擎。每种方法都有其优点和缺点。常用的有三种方法

ALTER TABLE

将表从一个引擎修改为另一个引擎最简单的办法是使用ALTER TABLE 语句。下面的语句将mytable的引擎修改为InnoDB :

ALTER TABLE mytable ENGINE = InnoDB;

上述语法可以适用任何存储引擎。但需要执行很长时间，在实现上，MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的I/O能力，同时原表上会加上读锁。所以，在繁忙的表上执行此操作要特别小心。

导出与导入

还可以使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在相同的表名，即使它们使用的是不同的存储引擎。

CREATE和 SELECT

先创建一个新的存储引擎的表，然后利用INSERT…SELECT语法来导数据:

CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;

如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交操作。

2.1.3.5.检查MySQL的引擎

看我的MySQL现在已提供什么存储引擎:

show engines;

看我的MySQL当前默认的存储引擎:

show variables like '%storage_engine%';

2.1.3.6.MyISAM和InnoDB比较

2.2.MySQL中的目录和文件

2.2.1.bin目录

在MysQL的安装目录下有一个特别特别重要的bin目录，这个目录下存放着许多可执行文件。

其他系统中的可执行文件与此的类似。这些可执行文件都是与服务器程序和客户端程序相关的。

2.2.1.1.启动MySQL服务器程序

在UNIX系统中用来启动MySOL服务器程序的可执行文件有很多，大多在MySQL安装目录的bin目录下。

mysqld

mysqld这个可执行文件就代表着MySOL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。但这个命令不常用。

mysqld_safe

mysqld safe是一个启动脚本，它会间接的调用mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。另外,使用mysqld_safe启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错日志，这样可以方便我们找出发生错误的原因。

mysql.server

mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了

就像这样:

mysql.server start

需要注意的是，这个mysql.server文件其实是一个链接文件，它的实际文件是support-files/mysql.server，所以如果在bin目录找不到，到support-files下去找找，而且如果你愿意的话，自行用ln命令在bin创建一个链接。

另外，我们还可以使用mysql.server命令来关闭正在运行的服务器程序，只要把start参数换成stop就好了:

mysql.server stop

mysqld_multi

其实我们一台计算机上也可以运行多个服务器实例，也就是运行多个NySQL服务器进程。mysql_multi可执行文件可以对每一个服务器进程的启动或停止进行监控。

2.2.1.2.客户端程序

在我们成功启动MysTL服务器程序后，就可以接着启动客户端程序来连接到这个服务器喽， bin目录下有许多客户端程序，比方说mysqladmin、mysqldump、mysqlcheck等等。

我们常用的是可执行文件mysql，通过这个可执行文件可以让我们和服务器程序进程交互，也就是发送请求，接收服务器的处理结果。

mysqladmin执行管理操作的工具，检查服务器配置、当前运行状态，创建、删除数据库、设置新密码。

mysqldump数据库逻辑备份程序。

mysqlbackup备份数据表、整个数据库、所有数据库，一般来说mysqldump备份、mysql还原。

2.2.2.启动选项和参数

2.2.2.1.配置参数文件

当MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等。在默认情况下，MySQL实例会按照-定的顺序在指定的位置进行读取，用户只需通过命令mysql --help|grep my.cnf来寻找即可。

当然，也可以在启动MySQL时，指定配置文件（非yum安装）：

这个时候，就会以启动时指定的配置文件为准。

MySQL数据库参数文件的作用和Oracle数据库的参数文件极其类似，不同的是，Oracle实例在启动时若找不到参数文件，是不能进行装载(mount）操作的。MySQL稍微有所不同，MySQL实例可以不需要参数文件，这时所有的参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值。

MySQL数据库的参数文件是以文本方式进行存储的。可以直接通过一些常用的文本编辑软件进行参数的修改。

2.2.2.2.参数的查看和修改

可以通过命令show variables查看数据库中的所有参数，也可以通过LIKE来过滤参数名，前面查找数据库引擎时已经展示过了。从 MySQL 5.1版本开始，还可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找，推荐使用命令 show variables，使用更为简单，且各版本的 MySQL数据库都支持。

参数的具体含义可以参考MySQL官方手册：

dev.mysql.com/doc/refman/…

但是课程中遇到的参数会进行讲解。

MySQL数据库中的参数可以分为两类:动态(dynamic）参数和静态(static）参数。同时从作用范围又可以分为全局变量和会话变量。

动态参数意味着可以在 MySQL实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读(read only)的。

全局变量（GLOBAL）影响服务器的整体操作。

会话变量（SESSION/LOCAL）影响某个客户端连接的操作。

举个例子，用default_storage_engine来说明，在服务器启动时会初始化一个名为default_storage_engine，作用范围为GLOBAL的系统变量。之后每当有一个客户端连接到该服务器时，服务器都会单独为该客户端分配一个名为default_storage_engine，作用范围为SESSION的系统变量，该作用范围为SESSION的系统变量值按照当前作用范围为GLOBAL的同名系统变量值进行初始化。

可以通过SET命令对动态的参数值进行修改。

SET的语法如下：

set [global || session ] system_var_name= expr
或者
set [@@global. || @@session.] system_var_name= expr
比如：
set read_buffer_size=524288;
set session read_buffer_size=524288;
set @@global.read_buffer_size=524288;

MySQL所有动态变量的可修改范围，可以参考MySQL官方手册的 Dynamic System Variables 的相关内容：

dev.mysql.com/doc/refman/…

对于静态变量，若对其进行修改，会得到类似如下错误:

2.2.3.数据目录

我们知道像InnoDB、MyIASM这样的存储引擎都是把表存储在磁盘上的，而操作系统用来管理磁盘的那个东东又被称为文件系统，所以用专业一点的话来表述就是:像InnoDB、MyISAM这样的存储引擎都是把表存储在文件系统上的。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统。

2.2.3.1.确定MySQL中的数据目录

那说了半天，到底MySQL把数据都存到哪个路径下呢?其实数据目录对应着一个系统变量datadir，我们在使用客户端与服务器建立连接之后查看这个系统变量的值就可以了：

show variables like 'datadir';

当然这个目录可以通过配置文件进行修改，由我们自己进行指定。

2.2.3.2.数据目录中放些什么？

MySOL在运行过程中都会产生哪些数据呢?当然会包含我们创建的数据库、表、视图和触发器等用户数据，除了这些用户数据，为了程序更好的运行，MySQL也会创建一些其他的额外数据

2.2.3.2.1.数据库在文件系统中的表示

create database lijin charset=utf8;

每当我们使用CREATE DATABASE语句创建一个数据库的时候，在文件系统上实际发生了什么呢?其实很简单，每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，我们每当我们新建一个数据库时，MySQL会帮我们做这两件事儿:

1．在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹)。

2．在该与数据库名同名的子目录下创建一个名为db.opt的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是个啥。

比方说我们查看一下在我的计算机上当前有哪些数据库︰

可以看到在当前有5个数据库，其中mysqladv数据库是我们自定义的，其余4个数据库是属于MySQL自带的系统数据库。我们再看一下数据目录下的内容:

当然这个数据目录下的文件和子目录比较多，但是如果仔细看的话，除了information_schema这个系统数据库外，其他的数据库在数居目录下都有对应的子目录。这个information_schema比较特殊，我们后面再讲它的作用。

2.2.3.2.2.表在文件系统中的表示

我们的数据其实都是以记录的形式插入到表中的，每个表的信息其实可以分为两种:

1.表结构的定义

2．表中的数据

表结构就是该表的名称是啥，表里边有多少列，每个列的数据类型是啥，有啥约束条件和索引，用的是啥字符集和比较规则各种信息，这些信息都体现在了我们的建表语句中了。为了保存这些信息，InnoDB和MyIASM这两种存储引擎都在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件，文件名是这样:表名.frm

比方说我们在lijin数据库下创建一个名为test的表:

那在数据库mysqladv对应的子目录下就会创建一个名为test.frm的用于描述表结构的文件。这个后缀名为.fm是以二进制格式存储的。

那表中的数据存到什么文件中了呢?在这个问题上，不同的存储引擎就产生了有所不同，下边我们分别看一下InnoDB和MyISAM是用什么文件来保存表中数据的。

2.2.3.2.3.lnnoDB是如何存储表数据的

InnoDB的数据会放在一个表空间或者文件空间（英文名: table space或者file space)的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件〈不同表空间对应的文件数量可能不同)。每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里。表空间有好几种类型。

系统表空间(system tablespace)

这个所谓的系统表空间可以对应文件系统上一个或多个实际的文件，默认情况下，InnoDB会在数据目录下创建一个名为ibdata1(在你的数据目录下找找看有木有)、大小为12M的文件，这个文件就是对应的系纳表空间在文件系统上的表示。

这个文件是所谓的自扩展文件，也就是当不够用的时候它会自己增加文件大小，当然，如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，那可以在MySQL启动时配置对应的文件路径以及它们的大小，我们也可以把系统表空间对应的文件路径不配置到数据目录下，甚至可以配置到单独的磁盘分区上。

需要注意的一点是，在一个MySQL服务器中，系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，我们表中的数据都会被默认存储到这个系统表空间。

独立表空间(file-per-table tablespace)

在MySQL5.6.6以及之后的版本中，InnoB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个.ibd的扩展名而已，所以完整的文件名称长这样:表名.ibd。

比方说假如我们使用了独立表空间去存储lijin数据库下的test表的话，那么在该表所在数据库对应的lijin目录下会为test表创建这两个文件:

test.frm和test.ibd

其中test.ibd文件就用来存储test表中的数据和索引。当然我们也可以自己指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数

innodb_file_per_table控制，比如说我们想刻意将表数据都存储到系统表空间时，可以在启动MySQL服务器的时候这样配置:

[server]

innodb_file_per_table=0

当imodb_file_per table的值为0时，代表使用系统表空间;当innodb_file_per table的值为1时，代表使用独立表空间。不过inmodb_file_per_table参数只对新建的表起作用，对于已经分配了表空间的表并不起作用。

其他类型的表空间

随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间(general tablespace) ,undo表空间(undotablespace)、临时表空间〈temporary tablespace)等。

2.2.3.2.4.MyISAM是如何存储表数据的

在MyISAM中的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件。而且和InnoDB不同的是，MyISA并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下。

test_myisam表使用MyISAM存储引擎的话，那么在它所在数据库对应的lijin目录下会为myisam表创建三个文件:

其中test_myisam.MYD代表表的数据文件，也就是我们插入的用户记录; test_myisam.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中。

2.2.3.3.日志文件

在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志、Undo日志等等，日志文件记录了影响MySQL数据库的各种类型活动。

常见的日志文件有：错误日志（error log）、慢查询日志（slow query log）、查询日志（query log）、二进制文件（bin log）。

错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息

用户可以通过下面命令来查看错误日志文件的位置：

show variables like 'log_error'\G;

当MySQL不能正常启动时，第一个必须查找的文件应该就是错误日志文件，该文件记录了错误信息。

慢查询日志

慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。

我们已经知道慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。但是默认值为关闭的，需要我们手动开启。

show VARIABLES like 'slow_query_log';

set GLOBAL slow_query_log=1;

开启1，关闭0

但是多慢算慢？MySQL中可以设定一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志中。long_query_time参数就是这个阈值。默认值为10，代表10秒。

show VARIABLES like '%long_query_time%';

当然也可以设置

set global long_query_time=0;

默认10秒，这里为了演示方便设置为0

同时对于运行的SQL语句没有使用索引，则MySQL数据库也可以将这条SQL语句记录到慢查询日志文件，控制参数是：

show VARIABLES like '%log_queries_not_using_indexes%';

开启1，关闭0（默认）

show VARIABLES like '%slow_query_log_file%';

查询日志

查看当前的通用日志文件是否开启

show variables like '%general%'

开启通⽤⽇志查询： set global general_log = on;
关闭通⽤⽇志查询：set global general_log = off;

sele

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。

默认文件名：主机名.log

二进制日志（binlog）

二进制日志记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录，还包含语句所执⾏的消耗的时间，MySQL的⼆进制⽇志是事务安全型的

二进制日志的几种作用：

恢复（recovery）：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制文件进行point-in-time的恢复

复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行实时同步

审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击

log-bin参数该参数用来控制是否开启二进制日志，默认为关闭

如果想要开启二进制日志的功能，可以在MySQL的配置文件中指定如下的格式：

“name”为二进制日志文件的名称

如果不提供name，那么数据库会使用默认的日志文件名（文件名为主机名，后缀名为二进制日志的序列号），且文件保存在数据库所在的目录（datadir下）

--启用/设置二进制日志文件(name可省略)

log-bin=name;

配置以后，就会在数据目录下产生类似于：

bin_log.00001即为二进制日志文件；bin_log.index为二进制的索引文件，用来存储过往产生的二进制日志序号，通常情况下，不建议手动修改这个文件。

二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动。开启这个选项会对MySQL的性能造成影响，但是性能损失十分有限。根据MySQL官方手册中的测试指明，开启二进制日志会使性能下降1%。

查看binlog是否开启

show variables like 'log_bin';

mysql安装目录下修改my.cnf

log_bin=mysql-bin
binlog-format=ROW
server-id=1
expire_logs_days =30

2.2.3.3.其他的数据文件

除了我们上边说的这些用户自己存储的数据以外，数据文件下还包括为了更好运行程序的一些额外文件，当然这些文件不一定会放在数据目录下，而且可以在配置文件或者启动时另外指定存放目录。

主要包括这几种类型的文件:

·服务器进程文件。

我们知道每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL服务器会把自己的进程ID写入到一个pid文件中。

socket文件

当用UNIX域套接字方式进行连接时需要的文件。

·默认/自动生成的SSL和RSA证书和密钥文件。

1.MySQL中的系统库

1.1.系统库简介

MySQL有几个系统数据库，这几个数据库包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息，我们现在稍微了解一下。

performance_schema

这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

information_schema

这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些是一些描述性信息，称之为元数据。

sys

这个数据库通过视图的形式把information_schema和performance_schema结合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。

mysql

主要存储了MySQL的用户账户和权限信息，还有一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

1.2.performance_schema

1.2.1.什么是performance_schema

MySQL的performance_schema 是运行在较低级别的用于监控MySQL Server运行过程中的资源消耗、资源等待等情况的一个功能特性，它具有以下特点。

**运行在较低级别：**采集的东西相对比较底层，比如磁盘文件、表I/O、表锁等等。

•　performance_schema提供了一种在数据库运行时实时检查Server内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关数据。

•　performance_schema通过监视Server的事件来实现监视其内部执行情况，“事件”就是在Server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断Server中的相关资源被消耗在哪里。一般来说，事件可以是函数调用、操作系统的等待、SQL语句执行的阶段[如SQL语句执行过程中的parsing（解析）或sorting（排序）阶段]或者整个SQL语句的集合。采集事件可以方便地提供Server中的相关存储引擎对磁盘文件、表I/O、表锁等资源的同步调用信息。

•　当前活跃事件、历史事件和事件摘要相关表中记录的信息，能提供某个事件的执行次数、使用时长，进而可用于分析与某个特定线程、特定对象（如mutex或file）相关联的活动。

•　performance_schema存储引擎使用Server源代码中的“检测点”来实现事件数据的收集。对于performance_schema实现机制本身的代码没有相关的单独线程来检测，这与其他功能（如复制或事件计划程序）不同。

收集到的事件数据被存储在performance_schema数据库的表中。对于这些表可以使用SELECT语句查询，也可以使用SQL语句更新performance_schema数据库中的表记录（比如动态修改performance_schema的以“setup_”开头的配置表，但要注意，配置表的更改会立即生效，这会影响数据收集）。

•　performance_schema的表中数据不会持久化存储在磁盘中，而是保存在内存中，一旦服务器重启，这些数据就会丢失（包括配置表在内的整个performance_schema下的所有数据）。

1.2.2.performance_schema使用

通过上面介绍，相信你对于什么是performance_schema这个问题了解得更清晰了。下面开始介绍performance_schema的使用。

1.2.3.检查当前数据库版本是否支持

performance_schema被视为存储引擎，如果该引擎可用，则应该在

INFORMATION_SCHEMA.ENGINES表或show engines语句的输出中可以看到它的Support字段值为YES，如下所示。

select * from INFORMATION_SCHEMA.ENGINES;
show engines;

当我们看到performance_schema对应的Support字段值为YES时，就表示当前的数据库版本是支持performance_schema的。但确认了数据库实例支持performance_schema存储引擎就可以使用了吗？NO，很遗憾，performance_schema在MySQL 5.6及之前的版本中默认没有启用，在MySQL 5.7及之后的版本中才修改为默认启用。

mysqld启动之后，通过如下语句查看performance_schema启用是否生效（值为ON表示performance_schema已初始化成功且可以使用了；值为OFF表示在启用performance_schema时发生某些错误，可以查看错误日志进行排查）。

show variables like 'performance_schema';

（如果要显式启用或关闭 performance_schema ，则需要使用参数performance_schema=ON|OFF来设置，并在my.cnf中进行配置。注意 : 该参数为只读参数，需要在实例启动之前设置才生效）

现在，可以通过查询INFORMATION_SCHEMA.TABLES表中与performance_schema存储引擎相关的元数据，或者在performance_schema库下使用show tables语句来了解其存在哪些表。

使用show tables语句来查询有哪些performance_schema引擎表。

现在，我们知道了在当前版本中，performance_schema库下一共有87个表，

那么这些表都用于存放什么数据呢？我们如何使用它们来查询数据呢？先来看看这些表是如何分类的。

1.2.4.performance_schema表的分类

performance_schema库下的表可以按照监视的不同维度进行分组，例如：按照不同的数据库对象进行分组、按照不同的事件类型进行分组，或者按照事件类型分组之后，再进一步按照账号、主机、程序、线程、用户等进行细分。

下面介绍按照事件类型分组记录性能事件数据的表。

•　语句事件记录表：记录语句事件信息的表，包括：events_statements_current（当前语句事件表）、events_statements_history（历史语句事件表）、events_statements_history_long（长语句历史事件表）以及一些summary表（聚合后的摘要表）。其中，summary表还可以根据账号（account）、主机（host）、程序（program）、线程（thread）、用户（user）和全局（global）再进行细分。

show tables like 'events_statement%';

•　等待事件记录表：与语句事件记录表类似。

show tables like 'events_wait%';

•　阶段事件记录表：记录语句执行阶段事件的表，与语句事件记录表类似。

show tables like 'events_stage%';

•　事务事件记录表：记录与事务相关的事件的表，与语句事件记录表类似。

show tables like 'events_transaction%';

•　监视文件系统层调用的表：

show tables like '%file%';

•　监视内存使用的表：

show tables like '%memory%';

•　动态对performance_schema进行配置的配置表：

show tables like '%setup%';

现在，我们已经大概知道了performance_schema中主要表的分类，但如何使用这些表来提供性能事件数据呢？

1.2.5.performance_schema简单配置与使用

当数据库初始化完成并启动时，并非所有的instruments（在采集配置项的配置表中，每一项都有一个开关字段，或为YES，或为NO）和consumers（与采集配置项类似，也有一个对应的事件类型保存表配置项，为YES表示对应的表保存性能数据，为NO表示对应的表不保存性能数据）都启用了，所以默认不会收集所有的事件。

可能你想检测的事件并没有打开，需要进行设置。可以使用如下两条语句打开对应的instruments和consumers，我们以配置监测等待事件数据为例进行说明。

打开等待事件的采集器配置项开关，需要修改setup_instruments 配置表中对应的采集器配置项。

update setup_instruments set enabled='yes',timed='yes' where name like 'wait%';

打开等待事件的保存表配置项开关，修改setup_consumers 配置表中对应的配置项。

update setup_consumers set enabled='yes' where name like 'wait%';

配置好之后，我们就可以查看Server当前正在做什么了。可以通过查询events_waits_current表来得知，该表中每个线程只包含一行数据，用于显示每个线程的最新监视事件（正在做的事情）。

_current表中每个线程只保留一条记录，且一旦线程完成工作，该表中就不会再记录该线程的事件信息了。_history表中记录每个线程已经执行完成的事件信息，但每个线程的事件信息只记录10条，再多就会被覆盖掉。*_history_long表中记录所有线程的事件信息，但总记录数量是10000行，超过会被覆盖掉。

summary表提供所有事件的汇总信息。该组中的表以不同的方式汇总事件数据（如：按用户、按主机、按线程等汇总）。

1.2.6.查看最近执行失败的SQL语句

使用代码对数据库的某些操作（比如：使用Java的ORM框架操作数据库）报出语法错误，但是代码并没有记录SQL语句文本的功能，在MySQL数据库层能否查看到具体的SQL语句文本，看看是否哪里写错了？这个时候，大多数人首先想到的就是去查看错误日志。很遗憾，对于SQL语句的语法错误，错误日志并不会记录。

实际上，在performance_schema的语句事件记录表中针对每一条语句的执行状态都记录了较为详细的信息，例如：events_statements_表和events_statements_summary_by_digest表（events_statements_表记录了语句所有的执行错误信息，而events_statements_summary_by_digest表只记录了语句在执行过程中发生错误的语句记录统计信息，不记录具体的错误类型，例如：不记录语法错误类的信息）。下面看看如何使用这两个表查询语句发生错误的语句信息。

首先，我们模拟一条语法错误的SQL语句，使用events_statements_history_long表或events_statements_history表查询发生语法错误的SQL语句：

然后，查询events_statements_history表中错误号为1064的记录

select * from events_statements_history where mysql_errno=1064\G

如果不知道错误号是多少，可以查询发生错误次数不为0的语句记录，在里边找到SQL_TEXT和MESSAGE_TEXT字段（提示信息为语法错误的就是它）。

1.2.7.查看最近的事务执行信息

我们可以通过慢查询日志查询到一条语句的执行总时长，但是如果数据库中存在着一些大事务在执行过程中回滚了，或者在执行过程中异常中止，这个时候慢查询日志就爱莫能助了，这时我们可以借助performance_schema的events_transactions_*表来查看与事务相关的记录，在这些表中详细记录了是否有事务被回滚、活跃（长时间未提交的事务也属于活跃事务）或已提交等信息。

首先需要进行配置启用，事务事件默认并未启用

update setup_instruments set enabled='yes',timed='yes' where name like 'transaction%';

update setup_consumers set enabled='yes' where name like '%transaction%';

现在我们开启一个新会话（会话2）用于执行事务，并模拟事务回滚。

查询活跃事务，活跃事务表示当前正在执行的事务事件，需要从events_transactions_current表中查询。

下图中可以看到有一条记录，代表当前活跃的事务事件。

会话2中回滚事务：

查询事务事件当前表（events_transactions_current）和事务事件历史记录表（events_transactions_history）

可以看到在两表中都记录了一行事务事件信息，线程ID为30的线程执行了一个事务，事务状态为ROLLED BACK。

但是当我们关闭会话2以后，事务事件当前表中（events_transactions_current）的记录就消失了。

要查询的话需要去（events_transactions_history_long）表中查

1.2.8.小结

当然performance_schema的用途不止我们上面说到过的这些，它还能提供比如查看SQL语句执行阶段和进度信息、MySQL集群下复制功能查看复制报错详情等等。

具体可以参考官网：MySQL :: MySQL 5.7 Reference Manual :: 25 MySQL Performance Schema

1.3.sys系统库

1.3.1.sys使用须知

sys系统库支持MySQL 5.6或更高版本，不支持MySQL 5.5.x及以下版本。

sys系统库通常都是提供给专业的DBA人员排查一些特定问题使用的，其下所涉及的各项查询或多或少都会对性能有一定的影响。

因为sys系统库提供了一些代替直接访问performance_schema的视图，所以必须启用performance_schema（将performance_schema系统参数设置为ON），sys系统库的大部分功能才能正常使用。

同时要完全访问sys系统库，用户必须具有以下数据库的管理员权限。

如果要充分使用sys系统库的功能，则必须启用某些performance_schema的功能。比如：

启用所有的wait instruments：

CALL sys.ps_setup_enable_instrument('wait');

启用所有事件类型的current表：

CALL sys.ps_setup_enable_consumer('current');

注意： performance_schema的默认配置就可以满足sys系统库的大部分数据收集功能。启用所有需要功能会对性能产生一定的影响，因此最好仅启用所需的配置。

1.3.2.sys系统库使用

如果使用了USE语句切换默认数据库，那么就可以直接使用sys系统库下的视图进行查询，就像查询某个库下的表一样操作。也可以使用db_name.view_name、db_name.procedure_name、db_name.func_name等方式，在不指定默认数据库的情况下访问sys 系统库中的对象（这叫作名称限定对象引用）。

在sys系统库下包含很多视图，它们以各种方式对performance_schema表进行聚合计算展示。这些视图大部分是成对出现的，两个视图名称相同，但有一个视图是带 x $前缀的.$

host_summary_by_file_io和 x$host_summary_by_file_io

代表按照主机进行汇总统计的文件I/O性能数据，两个视图访问的数据源是相同的，但是在创建视图的语句中，不带x $前缀的视图显示的是相关数值经过单位换算后的数据（单位是毫秒、秒、分钟、小时、天等），带 x$ 前缀的视图显示的是原始的数据（单位是皮秒）。

1.3.3.查看慢SQL语句慢在哪里

如果我们频繁地在慢查询日志中发现某个语句执行缓慢，且在表结构、索引结构、统计信息中都无法找出原因时，则可以利用sys系统库中的撒手锏：sys.session视图结合performance_schema的等待事件来找出症结所在。那么session视图有什么用呢？使用它可以查看当前用户会话的进程列表信息，看看当前进程到底再干什么，注意，这个视图在MySQL 5.7.9中才出现。

首先需要启用与等待事件相关功能：

call sys.ps_setup_enable_instrument('wait');
call sys.ps_setup_enable_consumer('wait');

然后模拟一下：

一个session中执行

select sleep(30);

另外一个session中在sys库中查询：

select * from session where command='query' and conn_id !=connection_id()\G

查询表的增、删、改、查数据量和I/O耗时统计

select * from schema_table_statistics_with_buffer\G

1.3.4.小结

除此之外，通过sys还可以查询查看InnoDB缓冲池中的热点数据、查看是否有事务锁等待、查看未使用的，冗余索引、查看哪些语句使用了全表扫描等等。

具体可以参考官网：MySQL :: MySQL 5.7 Reference Manual :: 26 MySQL sys Schema

1.4.information_schema

1.4.1.什么是information_schema

information_schema提供了对数据库元数据、统计信息以及有关MySQL Server信息的访问（例如：数据库名或表名、字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典或系统目录。

在每个MySQL 实例中都有一个独立的information_schema，用来存储MySQL实例中所有其他数据库的基本信息。information_schema库下包含多个只读表（非持久表），所以在磁盘中的数据目录下没有对应的关联文件，且不能对这些表设置触发器。虽然在查询时可以使用USE语句将默认数据库设置为information_schema，但该库下的所有表是只读的，不能执行INSERT、UPDATE、DELETE等数据变更操作。

针对information_schema下的表的查询操作可以替代一些SHOW查询语句（例如：SHOW DATABASES、SHOW TABLES等）。

注意：根据MySQL版本的不同，表的个数和存放是有所不同的。在MySQL 5.6版本中总共有59个表，在MySQL 5.7版本中，该schema下总共有61个表，

在MySQL 8.0版本中，该schema下的数据字典表（包含部分原Memory引擎临时表）都迁移到了mysql schema下，且在mysql schema下这些数据字典表被隐藏，无法直接访问，需要通过information_schema下的同名表进行访问。

information_schema下的所有表使用的都是Memory和InnoDB存储引擎，且都是临时表，不是持久表，在数据库重启之后这些数据会丢失。在MySQL 的4个系统库中，information_schema也是唯一一个在文件系统上没有对应库表的目录和文件的系统库。

1.4.2.information_schema表分类

Server层的统计信息字典表

（1）COLUMNS

•　提供查询表中的列（字段）信息。

（2）KEY_COLUMN_USAGE

•　提供查询哪些索引列存在约束条件。

•　该表中的信息包含主键、唯一索引、外键等约束信息，例如：所在的库表列名、引用的库表列名等。该表中的信息与TABLE_CONSTRAINTS表中记录的信息有些类似，但TABLE_CONSTRAINTS表中没有记录约束引用的库表列信息，而KEY_COLUMN_USAGE表中却记录了TABLE_CONSTRAINTS表中所没有的约束类型。

（3）REFERENTIAL_CONSTRAINTS

•　提供查询关于外键约束的一些信息。

（4）STATISTICS

•　提供查询关于索引的一些统计信息，一个索引对应一行记录。

（5）TABLE_CONSTRAINTS

•　提供查询与表相关的约束信息。

（6）FILES

•　提供查询与MySQL的数据表空间文件相关的信息。

（7）ENGINES

•　提供查询MySQL Server支持的引擎相关信息。

（8）TABLESPACES

•　提供查询关于活跃表空间的相关信息（主要记录的是NDB存储引擎的表空间信息）。

•　注意：该表不提供有关InnoDB存储引擎的表空间信息。对于InnoDB表空间的元数据信息，请查询INNODB_SYS_TABLESPACES表和INNODB_SYS_DATAFILES表。另外，从MySQL 5.7.8开始，INFORMATION_SCHEMA.FILES表也提供查询InnoDB表空间的元数据信息。

（9）SCHEMATA

•　提供查询MySQL Server中的数据库列表信息，一个schema就代表一个数据库。

Server层的表级别对象字典表

（1）VIEWS

•　提供查询数据库中的视图相关信息。查询该表的账户需要拥有show view权限。

（2）TRIGGERS

•　提供查询关于某个数据库下的触发器相关信息。

（3）TABLES

•　提供查询与数据库内的表相关的基本信息。

（4）ROUTINES

•　提供查询关于存储过程和存储函数的信息（不包括用户自定义函数）。该表中的信息与mysql.proc中记录的信息相对应（如果该表中有值的话）。

（5）PARTITIONS

•　提供查询关于分区表的信息。

（6）EVENTS

•　提供查询与计划任务事件相关的信息。

（7）PARAMETERS

•　提供有关存储过程和函数的参数信息，以及有关存储函数的返回值信息。这些参数信息与mysql.proc表中的param_list列记录的内容类似。

Server层的混杂信息字典表

（1）GLOBAL_STATUS、GLOBAL_VARIABLES、SESSION_STATUS、

SESSION_VARIABLES

•　提供查询全局、会话级别的状态变量与系统变量信息。

（2）OPTIMIZER_TRACE

•　提供优化程序跟踪功能产生的信息。

•　跟踪功能默认是关闭的，使用optimizer_trace系统变量启用跟踪功能。如果开启该功能，则每个会话只能跟踪它自己执行的语句，不能看到其他会话执行的语句，且每个会话只能记录最后一条跟踪的SQL语句。

（3）PLUGINS

•　提供查询关于MySQL Server支持哪些插件的信息。

（4）PROCESSLIST

•　提供查询一些关于线程运行过程中的状态信息。

（5）PROFILING

•　提供查询关于语句性能分析的信息。其记录内容对应于SHOW PROFILES和SHOW PROFILE语句产生的信息。该表只有在会话变量 profiling=1时才会记录语句性能分析信息，否则该表不记录。

•　注意：从MySQL 5.7.2开始，此表不再推荐使用，在未来的MySQL版本中删除，改用Performance Schema代替。

（6）CHARACTER_SETS

•　提供查询MySQL Server支持的可用字符集。

（7）COLLATIONS

•　提供查询MySQL Server支持的可用校对规则。

（8）COLLATION_CHARACTER_SET_APPLICABILITY

•　提供查询MySQL Server中哪种字符集适用于什么校对规则。查询结果集相当于从SHOW COLLATION获得的结果集的前两个字段值。目前并没有发现该表有太大的作用。

（9）COLUMN_PRIVILEGES

•　提供查询关于列（字段）的权限信息，表中的内容来自mysql.column_priv列权限表（需要针对一个表的列单独授权之后才会有内容）。

（10）SCHEMA_PRIVILEGES

•　提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息，该表中的信息来自mysql.db表。

（11）TABLE_PRIVILEGES

•　提供查询关于表级别的权限信息，该表中的内容来自mysql.tables_priv表。

（12）USER_PRIVILEGES

•　提供查询全局权限的信息，该表中的信息来自mysql.user表。

10.2.4　InnoDB层的系统字典表

（1）INNODB_SYS_DATAFILES

•　提供查询InnoDB所有表空间类型文件的元数据（内部使用的表空间ID和表空间文件的路径信息），包括独立表空间、常规表空间、系统表空间、临时表空间和undo空间（如果开启了独立undo空间的话）。

•　该表中的信息等同于InnoDB数据字典内部SYS_DATAFILES表的信息。

（2）INNODB_SYS_VIRTUAL

•　提供查询有关InnoDB虚拟生成列和与之关联的列的元数据信息，等同于InnoDB数据字典内部SYS_VIRTUAL表的信息。该表中展示的行信息是与虚拟生成列相关联列的每个列的信息。

（3）INNODB_SYS_INDEXES

•　提供查询有关InnoDB索引的元数据信息，等同于InnoDB数据字典内部SYS_INDEXES表中的信息。

（4）INNODB_SYS_TABLES

•　提供查询有关InnoDB表的元数据信息，等同于InnoDB数据字典内部SYS_TABLES表的信息。

（5）INNODB_SYS_FIELDS

•　提供查询有关InnoDB索引键列（字段）的元数据信息，等同于InnoDB数据字典内部SYS_FIELDS表的信息。

（6）INNODB_SYS_TABLESPACES

•　提供查询有关InnoDB独立表空间和普通表空间的元数据信息（也包含了全文索引表空间），等同于InnoDB数据字典内部SYS_TABLESPACES表的信息。

（7）INNODB_SYS_FOREIGN_COLS

•　提供查询有关InnoDB外键列的状态信息，等同于InnoDB数据字典内部

SYS_FOREIGN_COLS表的信息。

（8）INNODB_SYS_COLUMNS

•　提供查询有关InnoDB表列的元数据信息，等同于InnoDB数据字典内部

SYS_COLUMNS表的信息。

（9）INNODB_SYS_FOREIGN

•　提供查询有关InnoDB外键的元数据信息，等同于InnoDB数据字典内部SYS_FOREIGN表的信息。

（10）INNODB_SYS_TABLESTATS

•　提供查询有关InnoDB表的较低级别的状态信息视图。 MySQL优化器会使用这些统计信息数据来计算并确定在查询InnoDB表时要使用哪个索引。这些信息保存在内存中的数据结构中，与存储在磁盘上的数据无对应关系。在InnoDB内部也无对应的系统表。

InnoDB层的锁、事务、统计信息字典表

（1）INNODB_LOCKS

•　提供查询InnoDB引擎中事务正在请求的且同时被其他事务阻塞的锁信息（即没有发生不同事务之间锁等待的锁信息，在这里是查看不到的。例如，当只有一个事务时，无法查看到该事务所加的锁信息）。该表中的内容可用于诊断高并发下的锁争用信息。

（2）INNODB_TRX

•　提供查询当前在InnoDB引擎中执行的每个事务（不包括只读事务）的信息，包括事务是否正在等待锁、事务什么时间点开始，以及事务正在执行的SQL语句文本信息等（如果有SQL语句的话）。

（3）INNODB_BUFFER_PAGE_LRU

•　提供查询缓冲池中的页面信息。与INNODB_BUFFER_PAGE表不同，INNODB_BUFFER_PAGE_LRU表保存有关InnoDB缓冲池中的页如何进入LRU链表，以及在缓冲池不够用时确定需要从中逐出哪些页的信息。

（4）INNODB_LOCK_WAITS

•　提供查询InnoDB事务的锁等待信息。如果查询该表为空，则表示无锁等待信息；如果查询该表中有记录，则说明存在锁等待，表中的每一行记录表示一个锁等待关系。在一个锁等待关系中包含：一个等待锁（即，正在请求获得锁）的事务及其正在等待的锁等信息、一个持有锁（这里指的是发生锁等待事务正在请求的锁）的事务及其所持有的锁等信息。

（5）INNODB_TEMP_TABLE_INFO

•　提供查询有关在InnoDB实例中当前处于活动状态的用户（只对已建立连接的用户有效，断开的用户连接对应的临时表会被自动删除）创建的InnoDB临时表的信息。它不提供查询优化器使用的内部InnoDB临时表的信息。该表在首次查询时创建。

（6）INNODB_BUFFER_PAGE

•　提供查询关于缓冲池中的页相关信息。

（7）INNODB_METRICS

•　提供查询InnoDB更为详细的性能信息，是对InnoDB的performance_schema的补充。通过对该表的查询，可用于检查InnoDB的整体健康状况，也可用于诊断性能瓶颈、资源短缺和应用程序的问题等。

（8）INNODB_BUFFER_POOL_STATS

•　提供查询一些InnoDB缓冲池中的状态信息，该表中记录的信息与SHOW ENGINEINNODB STATUS语句输出的缓冲池统计部分信息类似。另外，InnoDB缓冲池的一些状态变量也提供了部分相同的值。

InnoDB层的全文索引字典表

（1）INNODB_FT_CONFIG

（2）INNODB_FT_BEING_DELETED

（3）INNODB_FT_DELETED

（4）INNODB_FT_DEFAULT_STOPWORD

（5）INNODB_FT_INDEX_TABLE

InnoDB层的压缩相关字典表

（1）INNODB_CMP和INNODB_CMP_RESET

•　这两个表中的数据包含了与压缩的InnoDB表页有关的操作状态信息。表中记录的数据为测量数据库中的InnoDB表压缩的有效性提供参考。

（2）INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET

•　这两个表中记录了与InnoDB压缩表数据和索引相关的操作状态信息，对数据库、表、索引的每个组合使用不同的统计信息，以便为评估特定表的压缩性能和实用性提供参考数据。

（3）INNODB_CMPMEM和INNODB_CMPMEM_RESET

•　这两个表中记录了InnoDB缓冲池中压缩页的状态信息，为测量数据库中InnoDB表压缩的有效性提供参考。

1.4.3.information_schema应用

查看索引列的信息

INNODB_SYS_FIELDS表提供查询有关InnoDB索引列（字段）的元数据信息，等同于InnoDB数据字典中SYS_FIELDS表的信息。

INNODB_SYS_INDEXES表提供查询有关InnoDB索引的元数据信息，等同于InnoDB数据字典内部SYS_INDEXES表中的信息。

INNODB_SYS_TABLES表提供查询有关InnoDB表的元数据信息，等同于InnoDB数据字典中SYS_TABLES表的信息。

假设需要查询lijin库下的InnoDB表order_exp的索引列名称、组成和索引列顺序等相关信息，

则可以使用如下SQL语句进行查询

SELECT
	t. NAME AS d_t_name,
	i. NAME AS i_name,
	i.type AS i_type,
	i.N_FIELDS AS i_column_numbers,
	f. NAME AS i_column_name,
	f.pos AS i_position
FROM
	INNODB_SYS_TABLES AS t
JOIN INNODB_SYS_INDEXES AS i ON t.TABLE_ID = i.TABLE_ID
LEFT JOIN INNODB_SYS_FIELDS AS f ON i.INDEX_ID = f.INDEX_ID
WHERE
	t. NAME = 'lijin/order_exp';

结果中的列都很好理解，唯一需要额外解释的是i_type(INNODB_SYS_INDEXES.type)，它是表示索引类型的数字ID：

0 =二级索引

1=集群索引

2 =唯一索引

3 =主键索引

32 =全文索引

64 =空间索引

128 =包含虚拟生成列的二级索引。

1.5.Mysql中mysql系统库

1.5.1.权限系统表

因为权限管理是DBA的职责，所以对于这个部分的表，我们大概了解下即可。在mysql系统库中，MySQL访问权限系统表，放在mysql库中，主要包含如下几个表。

•　user：包含用户账户、全局权限和其他非权限列表（安全配置字段和资源控制字段）。

•　db：数据库级别的权限表。该表中记录的权限信息代表用户是否可以使用这些权限来访问被授予访问的数据库下的所有对象（表或存储程序）。

•　tables_priv：表级别的权限表。

•　columns_priv：字段级别的权限表。

•　procs_priv：存储过程和函数权限表。

•　proxies_priv：代理用户权限表。

提示：

要更改权限表的内容，应该使用账号管理语句（如： CREATE USER 、 GRANT 、 REVOKE等）来间接修改，不建议直接使用DML语句修改权限表。

(grant，revoke语句执行后会变更权限表中相关记录，同时会更新内存中记录用户权限的相关对象。dml语句直接修改权限表只是修改了表中权限信息，需要执行flush privileges;来更新内存中保存用户权限的相关对象)

1.5.2.统计信息表

持久化统计功能是通过将内存中的统计数据存储到磁盘中，使其在数据库重启时可以快速重新读入这些统计信息而不用重新执行统计，从而使得查询优化器可以利用这些持久化的统计信息准确地选择执行计划（如果没有这些持久化的统计信息，那么数据库重启之后内存中的统计信息将会丢失，下一次访问到某库某表时，需要重新计算统计信息，并且重新计算可能会因为估算值的差异导致查询计划发生变更，从而导致查询性能发生变化）。

如何启用统计信息的持久化功能呢？当innodb_stats_persistent = ON时全局的开启统计信息的持久化功能，默认是开启的，

show variables like 'innodb_stats_persistent';

如果要单独关闭某个表的持久化统计功能，则可以通过ALTER TABLE tbl_name STATS_PERSISTENT = 0语句来修改。

1.5.2.1.innodb_table_stats

innodb_table_stats表提供查询与表数据相关的统计信息。

select * from innodb_table_stats where table_name = 'order_exp'\G

database_name：数据库名称。

•　table_name：表名、分区名或子分区名。

•　last_update：表示InnoDB上次更新统计信息行的时间。

•　n_rows：表中的估算数据记录行数。

•　clustered_index_size：主键索引的大小，以页为单位的估算数值。

•　sum_of_other_index_sizes：其他（非主键）索引的总大小，以页为单位的估算数值。

1.5.2.2.innodb_index_stats

innodb_index_stats表提供查询与索引相关的统计信息。

select * from innodb_index_stats where table_name = 'order_exp';

表字段含义如下。

•　database_name：数据库名称。

•　table_name：表名、分区表名、子分区表名。

•　index_name：索引名称。

•　last_update：表示InnoDB上次更新统计信息行的时间。

•　stat_name：统计信息名称，其对应的统计信息值保存在stat_value字段中。

•　stat_value：保存统计信息名称stat_name字段对应的统计信息值。

•　sample_size：stat_value字段中提供的统计信息估计值的采样页数。

•　stat_description：统计信息名称stat_name字段中指定的统计信息的说明。

从表的查询数据中可以看到：

•　stat_name字段一共有如下几个统计值。

■　size：当stat_name字段为size值时，stat_value字段值表示索引中的总页数量。

■　n_leaf_pages：当stat_name字段为n_leaf_pages值时，stat_value字段值表示索引叶子页的数量。

■　n_diff_pfxNN：NN代表数字（例如01、02等）。当stat_name字段为n_diff_pfxNN值时，stat_value字段值表示索引的first column（即索引的最前索引列，从索引定义顺序的第一个列开始）列的唯一值数量。例如：当NN为01时，stat_value字段值就表示索引的第一个列的唯一值数量；当NN为02时，stat_value字段值就表示索引的第一个和第二个列组合的唯一值数量，依此类推。此外，在stat_name = n_diff_pfxNN的情况下，stat_description字段显示一个以逗号分隔的计算索引统计信息字段的列表。

•　从index_name字段值为PRIMARY数据行的stat_description字段的描述信息“id”中可以看出，主键索引的统计信息只包括创建主键索引时显式指定的列。

•　从index_name字段值为u_idx_day_status数据行的stat_description字段的描述信息“insert_time,order_status,expire_time”中可以看出，唯一索引的统计信息只包括创建唯一索引时显式指定的列。

•　从index_name字段值为idx_order_no数据行的stat_description字段的描述信息“order_no,id”中可以看出，普通索引（非唯一的辅助索引）的统计信息包括了显式定义的列和主键列。

注意，上述的描述中出现的诸如叶子页，索引的最前索引列等等，这些东西在索引章节有讲解，这里不再阐述。

1.5.3.日志记录表

MySQL的日志系统包含：普通查询日志、慢查询日志、错误日志（记录服务器启动时、运行中、停止时的错误信息）、二进制日志（记录服务器运行过程中数据变更的逻辑日志）、中继日志（记录从库I/O线程从主库获取的主库数据变更日志）、DDL日志（记录DDL语句执行时的元数据变更信息。在MySQL 5.7中只支持写入文件中，在MySQL 8.0中支持写入innodb_ddl_log表中。在MySQL5.7中，只有普通查询日志、慢查询日志支持写入表中（也支持写入文件中）,可以通过log_output=TABLE设置保存到mysql.general_log表和mysql.slow_log表中，其他日志类型在MySQL 5.7中只支持写入文件中。

1.5.3.1. general_log

general_log表提供查询普通SQL语句的执行记录信息，用于查看客户端到底在服务器上执行了什么SQL语句。

缺省不开启

show variables like 'general_log';

开启

set global log_output='TABLE'; -- 'TABLE,FILE'表示同时输出到表和文件
set global general_log=on;
show variables like 'general_log';

任意执行一个查询后

select * from mysql.general_log\G

1.5.3.2. slow_log

slow_log表提供查询执行时间超过long_query_time设置值的SQL语句、未使用索引的语句（需要开启参数log_queries_not_using_indexes=ON）或者管理语句（需要开启参数log_slow_admin_statements=ON）。

show variables like 'log_queries_not_using_indexes';
show variables like 'log_slow_admin_statements';

开启

set global log_queries_not_using_indexes=on;
set global log_slow_admin_statements=on;
show variables like 'log_queries_not_using_indexes';
show variables like 'log_slow_admin_statements';

我们已经知道慢查询日志可以帮助定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。但是默认值为关闭的，需要我们手动开启。

show VARIABLES like 'slow_query_log';

set GLOBAL slow_query_log=1;

开启1，关闭0

show VARIABLES like '%long_query_time%';

当然也可以设置

set global long_query_time=0;

默认10秒，这里为了演示方便设置为0

然后我们测试一把，随便写一个SQL

select * from mysql.slow_log\G

1.5.4.InnoDB中的统计数据

我们前边唠叨查询成本的时候经常用到一些统计数据，比如通过SHOW TABLE STATUS可以看到关于表的统计数据，通过SHOW INDEX可以看到关于索引的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？

1.5.4.1 统计数据存储方式

InnoDB提供了两种存储统计数据的方式：

永久性的统计数据，这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。

非永久性的统计数据，这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

MySQL给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中。

SHOW VARIABLES LIKE 'innodb_stats_persistent';

不过最近的MySQL版本都基本不用基于内存的非永久性统计数据了，所以我们也就不深入研究。

不过InnoDB默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。怎么做到的呢？我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);

ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

当STATS_PERSISTENT=1时，表明我们想把该表的统计数据永久的存储到磁盘上，当STATS_PERSISTENT=0时，表明我们想把该表的统计数据临时的存储到内存中。如果我们在创建表时未指定STATS_PERSISTENT属性，那默认采用系统变量innodb_stats_persistent的值作为该属性的值。

1.5.4.2 基于磁盘的永久性统计数据

当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里：

SHOW TABLES FROM mysql LIKE 'innodb%';

可以看到，这两个表都位于mysql系统数据库下边，其中：

innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。

innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

innodb_table_stats

直接看一下这个innodb_table_stats表中的各个列都是干嘛的：

database_name 数据库名

table_name 表名

last_update 本条记录最后更新时间

n_rows表中记录的条数

clustered_index_size 表的聚簇索引占用的页面数量

sum_of_other_index_sizes 表的其他索引占用的页面数量

我们直接看一下这个表里的内容：

SELECT * FROM mysql.innodb_table_stats;

几个重要统计信息项的值如下：

n_rows的值是10350，表明order_exp表中大约有10350条记录，注意这个数据是估计值。

clustered_index_size的值是97，表明order_exp表的聚簇索引占用97个页面，这个值是也是一个估计值。

sum_of_other_index_sizes的值是81，表明order_exp表的其他索引一共占用81个页面，这个值是也是一个估计值。

n_rows统计项的收集

InnoDB统计一个表中有多少行记录是这样的：

按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。

可以看出来这个n_rows值精确与否取决于统计时采样的页面数量，MySQL用名为innodb_stats_persistent_sample_pages的系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。该值设置的越大，统计出的n_rows值越精确，但是统计耗时也就最久；该值设置的越小，统计出的n_rows值越不精确，但是统计耗时特别少。所以在实际使用是需要我们去权衡利弊，该系统变量的默认值是20。

InnoDB默认是以表为单位来收集和存储统计数据的，我们也可以单独设置某个表的采样页面的数量，设置方式就是在创建或修改表的时候通过指定STATS_SAMPLE_PAGES属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

如果我们在创建表的语句中并没有指定STATS_SAMPLE_PAGES属性的话，将默认使用系统变量innodb_stats_persistent_sample_pages的值作为该属性的值。

clustered_index_size和sum_of_other_index_sizes统计项的收集牵涉到很具体的InnoDB表空间的知识和存储页面数据的细节，我们就不深入讲解了。

innodb_index_stats

直接看一下这个innodb_index_stats表中的各个列都是干嘛的：

desc mysql.innodb_index_stats;

字段名描述

database_name 数据库名

table_name 表名

index_name 索引名

last_update 本条记录最后更新时间

stat_name 统计项的名称

stat_value 对应的统计项的值

sample_size 为生成统计数据而采样的页面数量

stat_description 对应的统计项的描述

innodb_index_stats表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么，别着急，我们直接看一下关于order_exp表的索引统计数据都有些什么：

SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'order_exp';

先查看index_name列，这个列说明该记录是哪个索引的统计信息，从结果中我们可以看出来，PRIMARY索引（也就是主键）占了3条记录，idx_expire_time索引占了6条记录。

针对index_name列相同的记录，stat_name表示针对该索引的统计项名称，stat_value展示的是该索引在该统计项上的值，stat_description指的是来描述该统计项的含义的。我们来具体看一下一个索引都有哪些统计项：

n_leaf_pages：表示该索引的叶子节点占用多少页面。

size：表示该索引共占用多少页面。

n_diff_pfxNN：表示对应的索引列不重复的值有多少。其中的NN长得有点儿怪呀，啥意思呢？

其实NN可以被替换为01、02、03... 这样的数字。比如对于u_idx_day_status来说：

n_diff_pfx01表示的是统计insert_time这单单一个列不重复的值有多少。

n_diff_pfx02表示的是统计insert_time,order_status这两个列组合起来不重复的值有多少。

n_diff_pfx03表示的是统计insert_time,order_status,expire_time这三个列组合起来不重复的值有多少。

n_diff_pfx04表示的是统计key_pare1、key_pare2、expire_time、id这四个列组合起来不重复的值有多少。

对于普通的二级索引，并不能保证它的索引列值是唯一的，比如对于idx_order_no来说，key1列就可能有很多值重复的记录。此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。

对于主键和唯一二级索引则没有这个问题，它们本身就可以保证索引列值的不重复，所以也不需要再统计一遍在索引列后加上主键值的不重复值有多少。比如u_idx_day_statu和idx_order_no。

在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，sample_size列就表明了采样的页面数量是多少。

对于有多个列的联合索引来说，采样的页面数量是：innodb_stats_persistent_sample_pages × 索引列的个数。

当需要采样的页面数量大于该索引的叶子节点数量的话，就直接采用全表扫描来统计索引列的不重复值数量了。所以大家可以在查询结果中看到不同索引对应的size列的值可能是不同的。

定期更新统计数据

随着我们不断的对表进行增删改操作，表中的数据也一直在变化，innodb_table_stats和innodb_index_stats表里的统计数据也在变化。MySQL提供了如下两种更新统计数据的方式：

开启innodb_stats_auto_recalc。

系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据，它的默认值是ON，也就是该功能默认是开启的。每个表都维护了一个变量，该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新innodb_table_stats和innodb_index_stats表。不过自动重新计算统计数据的过程是异步发生的，也就是即使表中变动的记录数超过了10%，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行计算。

再一次强调，InnoDB默认是以表为单位来收集和存储统计数据的，我们也可以单独为某个表设置是否自动重新计算统计数的属性，设置方式就是在创建或修改表的时候通过指定STATS_AUTO_RECALC属性来指明该表的统计数据存储方式：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0);

当STATS_AUTO_RECALC=1时，表明我们想让该表自动重新计算统计数据，当STATS_AUTO_RECALC=0时，表明不想让该表自动重新计算统计数据。如果我们在创建表时未指定STATS_AUTO_RECALC属性，那默认采用系统变量innodb_stats_auto_recalc的值作为该属性的值。

手动调用ANALYZE TABLE语句来更新统计信息

如果innodb_stats_auto_recalc系统变量的值为OFF的话，我们也可以手动调用ANALYZE TABLE语句来重新计算统计数据，比如我们可以这样更新关于order_exp表的统计数据：

ANALYZE TABLE order_exp;

ANALYZE TABLE语句会立即重新计算统计数据，也就是这个过程是同步的，在表中索引多或者采样页面特别多时这个过程可能会特别慢最好在业务不是很繁忙的时候再运行。

手动更新innodb_table_stats和innodb_index_stats表

其实innodb_table_stats和innodb_index_stats表就相当于一个普通的表一样，我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引的统计数据。比如说我们想把order_exp表关于行数的统计数据更改一下可以这么做：

步骤一：更新innodb_table_stats表。

步骤二：让MySQL查询优化器重新加载我们更改过的数据。

更新完innodb_table_stats只是单纯的修改了一个表的数据，需要让MySQL查询优化器重新加载我们更改过的数据，运行下边的命令就可以了：

FLUSH TABLE order_exp;

1. MySQL的执行原理

1.1.单表访问之索引合并

我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并/index merge，具体的索引合并算法有下边三种。

1.1.1.Intersection合并

Intersection翻译过来的意思是交集。这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询：

SELECT * FROM order_exp WHERE order_no = 'a' AND expire_time = 'b';

假设这个查询使用Intersection合并的方式执行的话，那这个过程就是这样的：

从idx_order_no二级索引对应的B+树中取出order_no='a'的相关记录。
从idx_expire_time二级索引对应的B+树中取出expire_time='b'的相关记录。

二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中id值的交集。

按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来，返回给用户。

为啥不直接使用idx_order_no或者idx_expire_time只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢？这里要分析一下两种查询执行方式之间需要的成本代价。

只读取一个二级索引的成本：

1.按照某个搜索条件读取一个二级索引

2.根据从该二级索引得到的主键值进行回表操作

3.然后再过滤其他的搜索条件

读取多个二级索引之后取交集成本：

1.按照不同的搜索条件分别读取不同的二级索引

2.将从多个二级索引得到的主键值取交集

3.最后根据主键值进行回表操作。

虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

所以MySQL在某些特定的情况下才可能会使用到Intersection索引合并，哪些情况呢？

1.1.1.1.等值匹配

二级索引列必须是等值匹配的情况

对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

而下边这两个查询就不能进行Intersection索引合并：

SELECT * FROM order_exp WHERE order_no> 'a' AND expire_time = 'a'

SELECT * FROM order_exp WHERE order_no = 'a' AND insert_time = 'a';

第一个查询是因为对order_no进行了范围匹配

第二个查询是因为insert_time使用到的联合索引u_idx_day_status中的order_status和expire_time列并没有出现在搜索条件中，所以这两个查询不能进行Intersection索引合并。

1.1.1.2.主键列可以是范围匹配

比方说下边这个查询可能用到主键和u_idx_day_status进行Intersection索引合并的操作：

SELECT * FROM order_exp WHERE id > 100 AND expire_time = 'a';

因为主键的索引是有序的，按照有序的主键值去回表取记录有个专有名词，叫：Rowid Ordered Retrieval，简称 ROR 。

而二级索引的用户记录是由索引列 + 主键构成的，所以根据范围匹配出来的主键就是乱序的，导致回表开销很大。

那为什么在二级索引列都是等值匹配的情况下也可能使用Intersection索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。

Intersection索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很容易。

当然，上边说的两种情况只是发生Intersection索引合并的必要条件，不是充分条件。也就是说即使符合Intersection的条件，也不一定发生Intersection索引合并，这得看优化器的心情（判断）。

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

1.1.2.Union合并

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引，比方说这样：

SELECT * FROM order_exp WHERE order_no = 'a' OR expire_time = 'b'

Intersection是交集的意思，这适用于使用不同索引的搜索条件之间使用AND连接起来的情况；Union是并集的意思，适用于使用不同索引的搜索条件之间使用OR连接起来的情况。与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并：

1.1.2.1.等值匹配

分析同Intersection合并

1.1.2.2.主键列可以是范围匹配

分析同Intersection合并

1.1.1.3.使用Intersection索引合并的搜索条件

就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比方说这个查询：

SELECT * FROM order_exp WHERE insert_time = 'a' AND order_status = 'b' AND expire_time = 'c'
OR (order_no = 'a' AND expire_time = 'b');

优化器可能采用这样的方式来执行这个查询：

1、先按照搜索条件order_no = 'a' AND expire_time = 'b'从索引idx_order_no和idx_expire_time中使用Intersection索引合并的方式得到一个主键集合。

2、再按照搜索条件 insert_time ='a' AND order_status = 'b' AND expire_time = 'c'从联合索引u_idx_day_status中得到另一个主键集合。

3、采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

当然，查询条件符合了这些情况也不一定就会采用Union索引合并，也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

1.1.3.Sort-Union合并

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才可能被用到，比方说下边这个查询就无法使用到Union索引合并：

SELECT * FROM order_exp WHERE order_no< 'a' OR expire_time> 'z'

这是因为根据order_no<'a'从idx_order_no索引中获取的二级索引记录的主键值不是排好序的，

同时根据expire_time> 'z'从idx_expire_time索引中获取的二级索引记录的主键值也不是排好序的，但是order_no< 'a'和expire_time> 'z''这两个条件又特别让我们动心，所以我们可以这样：

1、先根据order_no< 'a'条件从idx_order_no二级索引中获取记录，并按照记录的主键值进行排序

2、再根据expire_time>'z'条件从idx_expire_time二级索引中获取记录，并按照记录的主键值进行排序

3、因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

当然，查询条件符合了这些情况也不一定就会采用Sort-Union索引合并，也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Sort-Union索引合并后进行访问的代价比全表扫描更小时才会使用Sort-Union索引合并。

1.1.4.联合索引替代Intersection索引合并

SELECT * FROM order_exp WHERE order_no= 'a' And expire_time= 'z';

这个查询之所以可能使用Intersection索引合并的方式执行，还不是因为idx_order_no和idx_expire_time是两个单独的B+树索引，要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢，就像这样：

ALTER TABLE order_exp drop index idx_order_no;
ALTER TABLE order_exp drop idx_expire_time;
ALTER TABLE add index idx_order_no_expire_time(order_no,expire_time);

这样我们把idx_order_no, idx_expire_time都干掉，再添加一个联合索引idx_order_no_expire_time，使用这个联合索引进行查询简直是又快又好，既不用多读一棵B+树，也不用合并结果。

1.2.连接查询

搞数据库一个避不开的概念就是Join，翻译成中文就是连接。使用的时候常常陷入下边两种误区：

**误区一：**业务至上，管他三七二十一，再复杂的查询也用在一个连接语句中搞定。

**误区二：**敬而远之，上次慢查询就是因为使用了连接导致的，以后再也不敢用了。

所以我们来学习一下连接的原理，才能在工作中用好SQL连接。

1.2.1.连接简介

1.2.1.1.连接的本质

为了方便讲述，我们建立两个简单的演示表并给它们写入数据：

CREATE TABLE e1 (m1 int, n1 char(1));
CREATE TABLE e2 (m2 int, n2 char(1));
INSERT INTO e1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO e2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。

所以我们把e1和e2两个表连接起来的过程如下图所示：

这个过程看起来就是把e1表的记录和e2的记录连起来组成新的更大的记录，所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为 笛卡尔积 。

因为表e1中有3条记录，表e2中也有3条记录，所以这两个表连接之后的笛卡尔积就有3×3=9行记录。

在MySQL中，连接查询的语法很随意，只要在FROM语句后边跟多个表名就好了，比如我们把e1表和e2表连接起来的查询语句可以写成这样：

 SELECT * FROM e1, e2;

1.2.1.2.连接过程简介

我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的笛卡尔积就有100×100×100=1000000行数据！所以在连接的时候过滤掉特定记录组合是有必要的，在连接查询中的过滤条件可以分成两种，比方说下边这个查询语句：

SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';

涉及单表的条件

e1.m1 > 1是只针对e1表的过滤条件

e2.n2< 'd'是只针对e2表的过滤条件。

涉及两表的条件

比如类似e1.m1 = e2.m2，这些条件中涉及到了两个表。

看一下携带过滤条件的连接查询的大致执行过程在这个查询中我们指明了这三个过滤条件：

e1.m1 > 1
e1.m1 = e2.m2
e2.n2 < 'd'

那么这个连接查询的大致执行过程如下：

确定驱动表(t1)

首先确定第一个需要查询的表，这个表称之为 驱动表 。单表中执行查询语句只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是说之前从执行计划中找const、ref、ref_or_null、range、index、all等等这些执行方法中选取代价最小的去执行查询）。

此处假设使用e1作为驱动表，那么就需要到e1表中找满足e1.m1 > 1的记录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询e1表的访问方法就设定为all，也就是采用全表扫描的方式执行单表查询。

遍历驱动表结果，到被驱动表(t2)中查找匹配记录

针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到e2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。

因为是根据e1表中的记录去找e2表中的记录，所以e2表也可以被称之为 被驱动表 。上一步骤从驱动表中得到了2条记录，所以需要查询2次e2表。

此时涉及两个表的列的过滤条件e1.m1 = e2.m2就派上用场了

当e1.m1 = 2时，过滤条件e1.m1 =e2.m2就相当于e2.m2 = 2，所以此时e2表相当于有了e2.m2 = 2、e2.n2 < 'd'这两个过滤条件，然后到e2表中执行单表查询。

当e1.m1 = 3时，过滤条件e1.m1 =e2.m2就相当于e2.m2 = 3，所以此时e2表相当于有了e2.m2 = 3、e2.n2 < 'd'这两个过滤条件，然后到e2表中执行单表查询。

所以整个连接查询的执行过程就如下图所示：

也就是说整个连接查询最后的结果只有两条符合过滤条件的记录：

从上边两个步骤可以看出来，这个两表连接查询共需要查询1次e1表，2次e2表。

当然这是在特定的过滤条件下的结果，如果我们把e1.m1 > 1这个条件去掉，那么从e1表中查出的记录就有3条，就需要查询3次e2表了。也就是说在两表连接查询中， 驱动表只需要访问一次，被驱动表可能被访问多次 。

1.2.1.3.内连接和外连接

为了大家更好理解后边内容，我们创建两个有现实意义的表，并插入一些数据：

CREATE TABLE student (
    number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(5) COMMENT '姓名',
    major VARCHAR(30) COMMENT '专业',
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '客户信息表';

CREATE TABLE score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩',
    PRIMARY KEY (number, subject)
) Engine=InnoDB CHARSET=utf8 COMMENT '客户成绩表';

两张表插入以下数据

现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了（因为score中没有姓名信息，所以不能单纯只查询score表）。连接过程就是从student表中取出记录，在score表中查找number相同的成绩记录，所以过滤条件就是student.number = socre.number，整个查询语句就是这样：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1,score AS s2 WHERE s1.number = s2.number;

从上述查询结果中我们可以看到，各个同学对应的各科成绩就都被查出来了，可是有个问题，yan同学，也就是学号为20200904的同学因为某些原因没有参加考试，所以在score表中没有对应的成绩记录。

如果老师想查看所有同学的考试成绩，即使是缺考的同学也应该展示出来，但是到目前为止我们介绍的连接查询是无法完成这样的需求的。我们稍微思考一下这个需求，其本质是想： 驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集 。为了解决这个问题，就有了内连接和外连接的概念：

对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的内连接。

对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：

左外连接 ，选取左侧的表为驱动表。

右外连接 ，选取右侧的表为驱动表。

可是这样仍然存在问题，即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。

这就犯难了，怎么办？把过滤条件分为两种就可以就解决这个问题了，所以放在不同地方的过滤条件是有不同语义的：

WHERE子句中的过滤条件

WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

ON子句中的过滤条件

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

需要注意的是，这个ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把ON子句放到内连接中，MySQL会把它和WHERE子句一样对待，也就是说：内连接中的WHERE子句和ON子句是等价的。

一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

左（外）连接的语法

左（外）连接的语法还是挺简单的，比如我们要把e1表和e2表进行左外连接查询可以这么写：

SELECT * FROM e1 LEFT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];

其中中括号里的OUTER单词是可以省略的。

对于LEFTJOIN类型的连接来说：

我们把放在左边的表称之为外表或者驱动表

右边的表称之为内表或者被驱动表。

所以上述例子中e1就是外表或者驱动表，e2就是内表或者被驱动表。需要注意的是，对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件。了解了左（外）连接的基本语法之后，再次回到我们上边那个现实问题中来，看看怎样写查询语句才能把所有的客户的成绩信息都查询出来，即使是缺考的考生也应该被放到结果集中：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number;

从结果集中可以看出来，虽然yan并没有对应的成绩记录，但是由于采用的是连接类型为左（外）连接，所以仍然把她放到了结果集中，只不过在对应的成绩记录的各列使用NULL值填充而已。

右（外）连接的语法

右（外）连接和左（外）连接的原理是一样的，语法也只是把LEFT换成RIGHT而已：

SELECT * FROM e1
RIGHT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条件];

只不过驱动表是右边的表e2，被驱动表是左边的表e1。

内连接的语法

内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集，一种最简单的内连接语法，就是直接把需要连接的多个表都放到FROM子句后边。其实针对内连接，MySQL提供了好多不同的语法：

SELECT * FROM e1 [INNER | CROSS] JOIN e2 [ON 连接条件] [WHERE 普通过滤条件];

也就是说在MySQL中，下边这几种内连接的写法都是等价的：

SELECT * FROM e1 JOIN e2;

SELECT * FROM e1 INNER JOIN e2;

SELECT * FROM e1 CROSS JOIN e2;

上边的这些写法和直接把需要连接的表名放到FROM语句之后，用逗号,分隔开的写法是等价的：

SELECT * FROM e1, e2;

再说一次，由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。

我们前边说过，连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的。而对于内连接来说，由于凡是不符合ON子句或WHERE子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。

但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

1.2.2.MySQL对连接的执行

复习了连接、内连接、外连接这些基本概念后，我们需要理解MySQL怎么样来进行表与表之间的连接，才能明白有的连接查询运行的快，有的却慢。

1.2.2.1.嵌套循环连接（Nested-LoopJoin）

我们前边说过，对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。

对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。

如果有3个表进行连接的话，那么首先两表连接得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，可以用伪代码表示一下这个过程就是这样：

for each row in e1 {   #此处表示遍历满足对e1单表查询结果集中的每一条记录，N条
    for each row in e2 {   #此处表示对于某条e1表的记录来说，遍历满足对e2单表查询结果集中的每一条记录，M条
            for each row in t3 {   #此处表示对于某条e1和e2表的记录组合来说，对t3表进行单表查询，L条
            if row satisfies join conditions, send to client
        }
    }
}

这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接（ Nested-Loop Join ），这是最简单，也是最笨拙的一种连接查询算法，时间复杂度是O（N * M * L）。

1.2.2.2.使用索引加快连接速度

我们知道在嵌套循环连接的步骤2中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，那速度肯定会很慢很慢。

但是查询e2表其实就相当于一次单表查询，我们可以利用索引来加快查询速度。回顾一下最开始介绍的e1表和e2表进行内连接的例子：

SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < 'd';

我们使用的其实是嵌套循环连接算法执行的连接查询，再把上边那个查询执行过程表回顾一下：

查询驱动表e1后的结果集中有两条记录，嵌套循环连接算法需要对被驱动表查询2次：

当e1.m1 = 2时，去查询一遍e2表，对e2表的查询语句相当于：

SELECT * FROM e2 WHERE e2.m2 = 2 AND e2.n2 < 'd';

当e1.m1 = 3时，再去查询一遍e2表，此时对e2表的查询语句相当于：

SELECT * FROM e2 WHERE e2.m2 = 3 AND e2.n2 < 'd';

可以看到，原来的e1.m1 = e2.m2这个涉及两个表的过滤条件在针对e2表做查询时关于e1表的条件就已经确定了，所以我们只需要单单优化对e2表的查询了，上述两个对e2表的查询语句中利用到的列是m2和n2列，我们可以在e2表的m2列上建立索引。

因为对m2列的条件是等值查找，比如e2.m2= 2、e2.m2 = 3等，所以可能使用到ref的访问方法，假设使用ref的访问方法去执行对e2表的查询的话，需要回表之后再判断e2.n2 < d这个条件是否成立。

在n2列上建立索引，涉及到的条件是e2.n2 < 'd'，可能用到range的访问方法，假设使用range的访问方法对e2表的查询的话，需要回表之后再判断在m2列上的条件是否成立。

假设m2和n2列上都存在索引的话，那么就需要从这两个里边儿挑一个代价更低的去执行对e2表的查询。当然，建立了索引不一定使用索引，只有在二级索引 + 回表的代价比全表扫描的代价更低时才会使用索引。

另外，有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null或者range这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是index(索引覆盖)的访问方法来查询被驱动表。

1.2.2.3.基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。

现实生活中的表成千上万条记录都是少的，几百万、几千万甚至几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。

而采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。

所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

所以MySQL提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。使用join buffer的过程如下图所示：

最最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录。

这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（ Block Nested-Loop Join ）算法。

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。

show variables like 'join_buffer_size' ;

当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录。

MySQL学习记录2