MySQL架构、存储索引、缓存、索引优化

MySQL的架构详解

MySQL的架构，如图所示，MySQLC/S架构，从用户发起SQL请求，到相关操作完成；大致分为9部分

1、Connectors（连接器）

可提供Navicat C API、JDBC、ODBC、NET、PHP、Perl、Python、Ruby、Cobol等连接MySQL；
ODBC（Open DataBase Connection）：开放数据库互联
JDBC：主要用于java语言利用较为底层的驱动连接数据库

2、Connection Pool（线程池）

MySQL是一个单进程多线程的应用，当用户通过Connector向MySQL Server发起请求连接时，通过线程池（Connection Pool）建立一个用户连接，此连接会话将会一直存在，用户可以通过此会话，发对应的SQL语句到服务端，服务器收到SQL语句后，将对语句完成执行；

线程池实现了以下几个功能：

Authentication：认证；用户发来的账号密码是否正确要完成认证功能
Thread Reuse：线程重用；当一个用户连接进来以后，要用一个线程来响应他，而后当用户退出这个线程有可能并非被销毁，而是把它清理完以后，重新收归到线程池中的空闲线程中去，以完成所谓的线程重用
Connection Limits：连接线程数量限制；eg：最多容纳100线程，一旦到达此上限后续到达的连接请求则只能排队或者拒绝连接；
Check Memory：检查内存，是指与连接线程相关的内存
Caches：必要时的线程缓存

3、SQL Inerface

SQL Interface是MySQL的外壳，无论用户是通过哪一种连接器发过来的基本的SQL请求（DML和DDL两种语句）。SQL Interface是一个完完整整的SQL命令解释器，还要提供完备的SQL接口应该具备的功能。
例如：支持所谓的过程式编程；支持代码块实现例如存储过程，存储函数，触发器必要时还要部署铺设实现所谓的一个关系型数据库应该具备的基本组件例如视图等…
SQL Interface做完词法分析，句法分析之后，需要分析语句如何执行，需要Parser实现

4、Parser分析器

Parser是专门的分析器，并不分析语法错误（语法是否正确是由SQL Inerface实现的），做整个语句的执行分析，查询翻译，把一个查询语句转化为对应能够在本地执行的特定操作；根据用户请求的对象（库、表、字段、字段中的数据等）检查是否有权限去访问；用户认证通过，并不代表它可以访问数据库上的所有数据
MySQl认证大概分为两个阶段：

接入MySQL时，需要验证账号，密码（Authentication）
验证完成后，还需要分析用户是否有权限获取他期望的数据（Objective Privilege的检测过程）
生成多个执行树，多种路径中肯定会有一个最优的

5、Optimizer优化器

Optimizer就是分析衡量哪一种方法的开销，代价最小，开销的计算要依赖于索引的各种内部组件来进行评估，得到的是一个近似值，还要考虑到当前MySQL内部实现资源访问时的统计数据。此外，优化还包括一种功能，一旦选择完一条路径之后，这条路径实现的开销，一旦Optimizer发现有更优的实现目的方式，将会改写用户的SQL语句，优化完之后将会交与存储引擎执行：Pluggable Storage Engine

6、Caches & Buffers

 Metadata cache 缓存对象元信息及统计信息；
　 Query cache 共享在内存中的完全一样的查询语句；如果完全相同的查询在缓存命中，MySQL会直接从缓存中去检索结果；
　 key cache 缓存表索引；如果索引数据量小，它将缓存索引结构和叶子节点（存储索引数据）；如果索引很大，它只会缓存索引结构

7、Pluggable Storage Engines

存储引擎负责把具体分析非结果，完后对磁盘上文件路径访问的转换，因此，如果要查询某数据，索引完成后要访问某一行，行数据都存储在磁盘块上，因此存储引擎要把它映射为磁盘块，并把磁盘块加载到内存中
此外，对于文件系统而言，数据的存储都是以磁盘块（block存储的），尽管如此，MySQL在实现数据组织时，并不依赖与磁盘，而是把磁盘块再一次组织成更大一级的逻辑单位（例如：LVM，PE，LE），这个逻辑单位称为MySQL的数据块（Data Block），最终对于关系型数据库，它的数据是按行存储的，一般而言一行数据都是存储在一起的，因此MySQL在内部有一个数据块叫做Data Block，整个存取过程是以Data Block以最小单位进行的。需要将整个块加载至内存中，MySQL再从其中挑出某一行，返回给查询者

常见的存储引擎：MyISAM、InnoDB

8、File system & Files & Logs

File system：NTFS、UTS、EXT2/3、NFS、SAN、NAS

Files & Logs：

redo log 重做日志
undo log 撤销日志
data 真正数据文件
index 索引文件
binary log 二进制日志文件
error log 错误日志
query log 查询日志
slow query log 慢查询日志

数据库系统的三种视图：

物理视图（数据文件）：对应的文件系统上存储为一个一个文件，MySQL的常见文件有：Redo，Undo，Data，Index，Binary，Error，Query and slow

逻辑视图：在SQL Inerface上通过存储引擎把底下的Data，Index映射为一个一个关系型数据库应该具备的组成部分，eg：表.一张表在底层看见的是一个数据文件（Data Block组成），
最终映射为磁盘上的文件系统Block，而后再次映射为本地扇区的存储。但是，整个MySQL需要
把他们转换映射成一个二维关系表的形式，这整个需要依赖于SQL Interface以及存储引擎
共同实现，所以，这个层次所提供给用户的就是逻辑视图。这个层次提供给用户的就是逻辑视图：把底层数据文件映射成为了关系型数据库的组件，（逻辑视图就在SQL Interface和
Storage Engines上）

用户视图：每一类用户只有一部分数据的访问权限，它也只能看到一部分数据的访问权限

9、Management Services & Utilities

常用的MysqL管理工具：

Backup & Recovery：备份恢复工具
Security：安全工具
Replication：复制工具
Cluster：集群服务
Administration Configuration：管理配置工具
Migration：迁移工具
Metadata：负责管理MySQL元数据的工具

存储引擎

MySQL的常见的存储引擎

14:35:11 (root@(none)) [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA     | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES    | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO     | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO     | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO     | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO     | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO     | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO     | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO     | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | <null>       | <null> | <null>     |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+

9 rows in set
Time: 0.017s

目前基本上只用MyISAM、InnoDB

MyISAM与InnoDB的比较

官网存储引擎说明

MyISAM

MyIsam的存储文件有三个，后缀名分别是.frm、.MYD、MYI，其中.frm是表的定义文件，.MYD是数据文件，.MYI是索引文件。MyIsam只支持表锁，不支持事务。MyIsam由于有单独的索引文件，在读取数据方面的性能很高。Myisam是以堆结构进行组织数据，其表容易损坏

特点

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键索引
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5前默认的数据库引擎

适用场景

只读（或者写较少）、表较少（可以接受长时间进行修复操作）

引擎文件

  tb1_name.frm 存放表的定义 file user*查看
　  tb1_name.MYD 数据文件
　  tb1_name.MYI 索引文件

实例

pwd
/data/mysql/data/hellodb
ll
total 360K
-rw-r----- 1 mysql mysql   61 Mar  2 15:29 db.opt
-rw-r----- 1 mysql mysql 8.5K Mar  2 15:29 classes.frm
-rw-r----- 1 mysql mysql  172 Mar  2 15:29 classes.MYD
-rw-r----- 1 mysql mysql 2.0K Mar  2 15:29 classes.MYI

15:40:24 (root@(none)) [hellodb]> show create table classes;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| classes | CREATE TABLE `classes` (\n  `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,\n  `Class` varchar(100) DEFAULT NULL,\n  `NumOfStu` smallint(5) unsigned DEFAULT NULL,\n  PRIMARY KEY (`ClassID`)\n) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set
Time: 0.009s

InnoDB

InnoDB的存储文件有两个，后缀名分别是.frm和.idb，其中 .frm是表的定义文件，而.idb是数据文件。InnoDB中存在表锁和行锁，不过行锁是在命中索引的情况下才会起作用。InnoDB支持事务，且支持四种隔离级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读

特点

行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5开始为默认的数据库引擎

引擎文件

• 共享表空间：所有Innodb表的数据和索引都放在同一表空间中

ibdata1、ibdata2 数据文件，存放在定义好的数据目录下（/path/to/path/datadir/）
tb1_name.frm 表格式定义，存放在每个表对应的目录下（/path/to/path/datadir/dbname/）

• 独立表空间：file-per-table表空间；每个表单独使用一个表空间存储表的数据和索引

tb1_name.frm 表格式定义，存放在每个表对应的目录下（/path/to/path/datadir/dbname/）
tb1_name.ibd 数据文件，存储数据和索引，与.frm文件存放在同级目录下（/path/to/path/datadir/dbname/）

采用File-Per-Table的优缺点如下：

优点： 可以方便回收删除表所占的磁盘空间。如果使用系统表空间的话，删除表后空闲空间只能被InnoDB数据使用。TRUNCATE TABLE操作会更快。可以单独拷贝表空间数据到其他数据库(使用transportable tablespace特性)，可以更方便的观测每个表空间数据的大小

缺点： fsync操作需要作用的多个表空间文件，比只对系统表空间这一个文件进行fsync操作会多一些IO操作。此外，mysqld需要维护更多的文件描述符

查看是否启用file-per-table

16:08:45 (root@(none)) [(none)]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set
Time: 0.010s

在MySQL5.7及MariaDB5.5以后，默认File-Per-Table为开启的

之前的版本想可以通过修改innodb_file_per_table=ON来启用，或者在配置文件中添加相关选项

vim /etc/my.cnf
##################################################################
[mysqld]
# 设置默认的存储引擎
default_storage_engine=InnoDB
# 使用此选项，每个表都有ibd文件，每个表都有独立的文件更好管理
innodb_file_per_table
##################################################################

# 重启数据库服务
systemctl restart mariadb

其他存储引擎

Performance_Schema: Performance_Schema数据库使用；performance_schema使用的存储引擎

Memory: 将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎；information_schema使用的存储引擎

MRG_MyISAM: 使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库

Archive: 为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区

Federated联合: 用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境

BDB: 可替代InnoDB的事务引擎，支持COMMIT、 ROLLBACK和其他事务特性

Cluster/NDB: MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性

CSV: CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换

BLACKHOLE: 黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储

example: “stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

管理存储引擎

查看MySQL支持的存储引擎

16:39:14 (root@(none)) [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA     | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES    | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO     | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO     | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO     | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO     | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO     | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO     | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO     | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | <null>       | <null> | <null>     |
+--------------------+---------+----------------------------------------------------------------+--------------+--------+------------+

9 rows in set
Time: 0.009s

查看当前默认的存储引擎

16:40:24 (root@(none)) [(none)]> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set
Time: 0.010s

查看库中所有表使用的存储引擎

show table status from <db_name>;

查看库中指定表的存储引擎

-- 方法一：
use <db_name>;
show table status like '<tb_name>';

-- 方法二：
show create table <db_name.tb_name>;

设置表的存储引擎

create table <tb_name>(....) ENGINE=InnoDB;
alter table <tb_name> ENGINE=InnoDB;

修改默认的存储引擎

vim /etc/my.cnf
##################################################################
[mysqld]
# 设置默认的存储引擎
default_storage_engine=InnoDB
##################################################################

MySQL中的系统数据库

mysql 数据库

MySQL的核心数据库，类似于SQL Server中的master库，主要负责存储数据库的用户、权限设置、函数、存储过程、关键字等MySQL自己需要使用的控制和管理信息

performance_schema 数据库

MySQL5.5开始新增的数据库，主要用于收集数据库服务器性能参数，库里表的存储引擎为Performance_Schema，用户不能创建存储引擎为Performance_Schema的表

information_schema 数据库

MySQL5.0之后，一个虚拟内存数据库，物理上并不存在，information_schema数据库类似于“数据字典”，提供了访问数据库元数据的方式，即数据的数据，比如数据库库名或表名，列类型，访问权限（更加细化的访问方式）

sys 数据库

MySQL5.7之后新增的数据库，库中所有数据源来自performance_schema；目标是把performance_schema的复杂度降低，让DBA能更好阅读这个库里的内容；让DBA更快的了解DB运行情况

MySQL服务器配置及状态

在这里将MySQL中的命令行选项、服务器选项、系统变量、系统状态变量做个说明

官方相关文档：
MySQL5.7
MariaDB

注意⚠️

其中有些参数支持运行时修改，会立即生效
有些参数不支持动态修改，只能通过修改配置文件，并重启服务生效
有些参数作用域为全局的，为所有会话设置
有些可以为每个用户提供单独（会话）设置

命令行选项及服务器选项

命令行选项

在启动mysqld服务时，可以添加的参数选项，具体可用的参数，可以通过帮助命令查看：mysqld --verbose --help

实例

# 在启动mysqld时候，可以添加如下参数：
/usr/local/mysql_5_6_9/bin/mysqld \
--basedir=/usr/local/mysql_5_6_9 \
--datadir=/usr/local/mysql_5_6_9/data \
--plugin-dir=/usr/local/mysql_5_6_9/lib/plugin \
--user=mysql \
--log-error=mysql_master232.err \
--open-files-limit=10240 \
--pid-file=/usr/local/mysql_5_6_9/data/mysql_master232.pid \
--port=13306

服务器选项

在启动mysqld服务时，通过指定添加了相关参数选项的配置文件；一般配置文件存放在/etc/my.cnf

实例

vim /path/to/path/my.cnf
##################################################################
[mysqld]
datadir=/usr/local/mysql_5_6_9/data
server-id=1
port=13306
log-bin=/usr/local/mysql_5_6_9/log/master-mysql-bin
skip-name-resolve=on
binlog_format=mixed
log-slave-updates=1
expire_logs_days=7
max_binlog_size=100M
sync_binlog=1
symbolic-links=0
open_files_limit = 10240
back_log = 600
max_connections = 3000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet = 256M
thread_cache_size = 300
query_cache_size = 64M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
tmp_table_size = 256M
max_heap_table_size = 256M
expire_logs_days = 7
key_buffer_size = 2048M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
lower_case_table_names = 1
innodb-file-per-table =ON
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 64G
innodb_thread_concurrency = 6
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 60
# innodb_rollback_on_timeout = 1
innodb_log_files_in_group = 3
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
# wait_timeout=120
# interactive_timeout=100
# lock_wait_timeout=60

[mysql_safe]
socket=/usr/local/mysql_5_6_9/mysql.sock
log-error=/usr/local/mysql_5_6_9/log/mysql_error.log
##################################################################

服务器系统变量

服务器系统变量分为全局、会话两种；全局变量作用在所有，会话只作用在当前连接的会话

获取系统变量：

-- 只查看全局变量
show global variables;

-- 查看所有变量，包括全局及会话
show variables;

-- 查看指定的系统变量，可以添加通配符作为模糊匹配
show variables like 'var_name';

-- 查看选项和部分变量
$ mysqladmin -p variables

修改服务器变量的值

SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@global.} system_var_name
  | [SESSION | @@session. | @@] system_var_name
}

-- 修改全局变量：仅对修改后新创建的会话有效，对已建立的会话无效
SET GLOBAL system_var_name=value;

-- 修改会话变量：
SET system_var_name=value;

服务器状态变量

服务器状态变量分为全局、会话两种；
状态变量**（只读）**：用于保存mysqld运行中的统计数据的变量，不可更改

-- 查看全局状态变量
show global status;

-- 查看状态变量（包含全局及会话）
show status;

实例

-- 查看当前页大小
16:40:33 (root@(none)) [(none)]> show status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set
Time: 0.009s

-- 查看当前会话查询的次数
16:41:16 (root@(none)) [(none)]> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 3     |
+---------------+-------+
1 row in set
Time: 0.009s

-- 查看全局查询的次数
16:51:39 (root@(none)) [(none)]> show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 95    |
+---------------+-------+
1 row in set
Time: 0.009s

服务器变量SQL_MODE

SQL_MODE 对其设置可以完成一些约束检查的工作，可分别进行全局设置或当前会话的设置

官方相关文档：
MySQL5.7
MariaDB

常见MODE

NO_AUTO_CREATE_USER 禁止GRANT创建密码为空的用户
NO_ZERO_DATE 在严格模式，不允许使用’0000-00-00’的时间
ONLY_FULL_GROUP_BY 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
NO_BACKSLASH_ESCAPES 反斜杠’\'作为普通字符而非转义字符
PIPES_AS_CONCAT 将||视为连接操作符而非’或’运算符
TRADITIONAL 所有约束的集合

查看当前sql_mode

17:01:36 (root@(none)) [(none)]> show variables like 'sql_mode'\G
***************************[ 1. row ]***************************
Variable_name | sql_mode
Value         | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1 row in set
Time: 0.002s

修改sql_mode

17:06:02 (root@(none)) [(none)]> show variables like 'sql_mode'\G
***************************[ 1. row ]***************************
Variable_name | sql_mode
Value         | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1 row in set
Time: 0.002s
17:06:26 (root@(none)) [(none)]> select classid,avg(age) from students where classid is not null group by classid;
(1046, 'No database selected')
17:06:38 (root@(none)) [(none)]> select classid,avg(age) from hellodb.students where classid is not null group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1       | 20.5000  |
| 2       | 36.0000  |
| 3       | 20.2500  |
| 4       | 24.7500  |
| 5       | 46.0000  |
| 6       | 20.7500  |
| 7       | 19.6667  |
+---------+----------+
7 rows in set
Time: 0.010s
17:06:59 (root@(none)) [(none)]> select classid,avg(age),stuid from hellodb.students where classid is not null group by classid;
(1055, "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hellodb.students.StuID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

-- 修改sql_mode的值
-- 注意：sql_mode的值为多个选项，所以在添加或删除的时候，需要设置所有需要的添加的选项
17:08:28 (root@(none)) [(none)]> set sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected
Time: 0.001s
17:08:32 (root@(none)) [(none)]> show variables like 'sql_mode'\G
***************************[ 1. row ]***************************
Variable_name | sql_mode
Value         | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1 row in set
Time: 0.002s
17:08:38 (root@(none)) [(none)]> select classid,avg(age),stuid from hellodb.students where classid is not null group by classid;
+---------+----------+-------+
| classid | avg(age) | stuid |
+---------+----------+-------+
| 1       | 20.5000  | 2     |
| 2       | 36.0000  | 1     |
| 3       | 20.2500  | 5     |
| 4       | 24.7500  | 4     |
| 5       | 46.0000  | 6     |
| 6       | 20.7500  | 9     |
| 7       | 19.6667  | 8     |
+---------+----------+-------+
7 rows in set
Time: 0.009s

查询缓存 Query Cache

查询缓存的原理

查询执行路径

MySQL整个查询执行的过程，总体分为5个步骤：

客户端向MySQL服务器发送一条查询请求
服务器首先检查查询缓存，如果命中缓存，则立刻返回缓存中的结果，否则进入到下一个阶段
服务器进行SQL解析，预处理、再由优化器生成对应的执行计划
MySQL根据执行计划，调用存储引擎的API来执行查询
将结果返回给客户端，同时缓存查询结果

查询缓存原理

缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先去查询缓存，判断是否存在可用记录集，判断标准：与缓存的SQL语句，是否完全一致，需要区分大小写

查询缓存的优缺点

不需要对SQL语句做任何的分析和执行，当然语法解析必须通过在先，直接从Query Cache中获取查询结果，提高查询性能
查询缓存的判断的规则，不够智能，也即提高了查询缓存的使用门康，降低效率
查询缓存的使用，会增加检查和清理Query Cache中记录的开销

不会被缓存的查询语句

查询语句中加了SQL_NO_CACHE参数，指明查询结果不需要缓存
查询语句中含有获得值的函数，包含：自定义函数，eg：now()、curdate()、get_lock()、rand()、convert_tz()等
对系统数据库的查询：mysql、information_schema查询语句中使用session级别变量或存储过程中的局部变量
查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句，查询语句中类似SELECT....INTO导出数据的语句
对临时表的查询操作
存在警告信息的查询语句
不涉及任何表或视图的查询语句
某用户只有列级别权限的查询语句
事务隔离级别为Serializable时，所有查询语句都不能缓存

查询缓存相关的服务器变量

query_cache_min_res_unit：查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
query_cache_limit：单个查询结果能缓存的最大值，单位字节，默认为1M，对于查询结果过大而无法缓存的语句，建议使用SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
query_cache_wlock_invalidate：如果某表被其他的会话锁定，是否仍然可以从查询缓存中返回结果，默认值为OFF，表示可以在表被其他会话锁定的场景中继续从缓存返回数据；ON则表示不允许
query_cache_type：是否开启缓存功能，取值为ON、OFF、DEMAND

SELECT语句的缓存控制

SQL_CACHE：显式指定存储查询结果于缓存之中
SQL_NO_CACHE：显式查询结果不予缓存
query_cache_type 参数变量

  query_cache_type的值为OFF或0时，查询缓存功能关闭
　  query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
　  query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定的SQL_CACHE的SELECT语句才会缓存；其他均不予缓存

查询缓存相关的状态变量

show global status like 'Qcache%';

Qcache_free_blocks 处于空闲状态Query Cache中内存Block数
Qache_total_blocks Query Cache中总Block，当Qcache_free_blocks相对此值较大时，可能用内存碎片，执行FLUSH QUERY CACHE清理碎片
Qache_free_memory 处于空闲状态的Query Cache内存总量
Qache_hits Query Cache命中次数
Qcache_inserts 向Query Cache中插入新的Query Cache的次数，即没有命中的次数
Qcache_lowmem_prunes 记录因为内存不足而被移除查询缓存的查询数
Qcache_not_cached 没有被cache的SQL数，包括无法被cache的SQL以及由于query_cache_type设置的不会被cache的SQL语句
Qcache_queries_in_cache 在Query Cache中的SQL数量

查询的优化

命中率和内存使用率估算

查询缓存中内存块的最小分配单位query_cache_min_res_unit

(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache

查询缓存命中率

Qcache_hits / (Qcache_hits + Qcache_inserts) *100%

查询缓存内存使用率

(query_cache_size - Qcache_free_memory) / query_cache_size * 100%

参考文献：

MySQL逻辑架构图简介
MySQL底层架构原理，工作流程和存储引擎的数据结构讲解
MySQL 架构及优化原理 详解
MySQL InnoDB存储引擎大观
MySql系列之Innodb存储引擎