高性能Mysql第三版（一）

我是小配菜

已于 2023-11-26 18:34:55 修改

阅读量473

点赞数 1

文章标签： mysql sql

于 2023-11-25 12:00:00 首次发布

本文链接：https://blog.csdn.net/weixin_51966599/article/details/133822379

版权

学习目标：

高性能Mysql第3版

学习内容：

MySQL架构与历史
Mysql基座测试
服务器性能
Schema与数据类型优化
创建高性能的索引
查询性能优化
Mysql高级特性
Explain

文章目录

学习目标：
- 高性能Mysql第3版
学习内容：
1 Mysql逻辑架构

1 Mysql逻辑架构

1.1 Mysql逻辑架构

在这里插入图片描述
客户端这方面明显不是Mysql独有的，大多数都是基于C/S架构，即 Client/Server 架构，服务器端程序使用的mysqld，所以不论是客户端进程还是服务器端进程是采用哪种方式进行通信，最后实现的效果都是: 客户端进程向服务端进程发送一段SQL语句，服务端进程处理后向客户端进程返回一段处理文本内容

第二层结构包含了Mysql的核心功能，包含查询解析、分析、优化、缓存等功能、并且跨储存引擎的功能都在这层实现的，比如存储过程，触发器，视图等功能

第三层包括了存储引擎，也就是底层数据存取操作实现的部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统一样。

1.1.1 连接管理与安全性

连接管路何安全性可以这样理解，服务器内部每个Clinet都会连接的一个线程，这个连接的查询就会被此线程单独执行，并且这些线程都是轮流运行在某一个CPU内核(多核CPU)或者CPU中，服务器缓存了线程，就不需要在重新创建和销毁线程了(线程的复用机制)
当clients(application program连接到了MySQL服务器。服务器需要对它进行认证(Authenticate)。认证是基于用户名，主机，以及密码,如果使用到了SSL连接方式，就要用到X.509证书，clients一连接上，服务器就验证它的权限

1.1.2 性能优化和执行

MySQL会解析查询，并创建了一个内部数据结构（解析树）。然后对其进行各种优化。这些优化包括了，查询语句的重写，读表的顺序，索引的选择等等。但优化要考虑的问题不止这些还要从多方面的环境去考虑，比如要从硬件，系统配置，数据库表结构，SQL及索引方面

	硬件： CPU、内存、存储、网络设备等
	系统配置： 服务器系统、数据库服务参数等
	数据库表结构：高可用、分库分表、读写分离、存储引擎、表设计等
	Sql及索引： sql语句、索引使用等

我们要优化的话就不止对一个单纯的环境进行优化并且会有很大的风险
但在优化时要一定考虑系统的数据安全性和处理高性能方面
附上链接 Mysql性能优化

1.2 并发控制

只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。本章目的讨论 MySQL 在两个层面的并发控制：服务器层与存储引擎层。

存储引擎层：存储引擎都可以实现自己的锁策略和锁粒度，不同存储引擎实现锁机制方式不同。

并发控制一般都会涉及到事务。MySQL服务层不管理事务，事务是由存储引擎实现的。同个事务中避免使用多种存储引擎，在处理并发控制问题我们可以使用读写锁，处理并发时读或者写时可以加读锁(共享锁)和写锁(排他锁)也可以通过Mysql的事务隔离级别来解决并发问题或者MVCC

1.2.1 锁粒度

为了解决加锁后如果系统出现了花大量的时间来管理锁而不是存储数据，避免资源的消耗会影响系统的性能,为了让系统有更多的时间来存取数据Mysql锁粒度提供了两种锁策略分别是行级锁(row lock)和表锁(table lock)
表锁: 开销小，加锁快；不会出现死锁；锁定力度大

行锁: 开销大，加锁慢；会出现死锁；锁定粒度小

InnoDB行锁和表锁都支持、MyISAM只支持表锁！

InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB使用表锁也就是说，InnoDB的行锁是基于索引的！

1.3 事务

事务是指一组SQL语句，他们被当作一个独立的工作单元来执行,要么全部执行成功，要让全部执行失败，在这过程中发生错误的话，已经执行的语句都要被撤销，回滚到事务开始前的状态,
Mysql中的事务具有ACID特性，原子性、一致性、隔离性、持久性

原子性：事务中的所有操作要么全部完成，要么全部不完成.
一致性：事务执行前后，数据库的状态必须保持一致。
隔离性：多个事务并发执行时，每个事务都应该感觉不到其他事务的存在。
持久性：事务完成后，对数据库的修改应该永久保存。

事务的ACID特性可以确保数据库不会弄丢你的数据，而根据线上环境中的应用逻辑中要完全实现这点很难，因为一个完全兼容并实现ACID的数据库系统，需要更强的CPU处理能力，更大的内存和磁盘空间,所以很多情况下不会完全遵守ACID的特性，来为系统节省开销, 这因为如此存储引擎就可以发挥优势了，我们就可以根据业务是否需要事务处理，来选择合适的存储引擎了，对于一些不需要事务类的查询应用可以选择非事务型的存储引擎，可以获取更高的性能了，即便存储引擎不支持事务，但可以通过 lock tables 提供一些保护

1.3 .1 隔离级别

为了达到事务的四大特性，Mysql数据库定义了四种事务隔离级别:
READ-UNCOMMITTED（读未提交）：最低的隔离级别，允许脏读，也就是可能读取到其他会话中未提交事务修改的数据，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED（读已提交）： 只能读取到已经提交的数据。Oracle 等多数数据库默认都是该级别（不重复读），可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ（可重复读）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE（可串行化）：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事
务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。MySQL 默认采用的 REPEATABLE_READ 隔（可重复读）离级别。

1.3.2 死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁

1.3.3事务日志

事务日志可以帮助我们提供事务的效率，使用事务日志，存储引擎在修改表的数据时只需要修改内存拷贝，在把该修改行为记录到硬盘文件中的事务日志，而不是每次都将修改的数据本身持久到磁盘内存中附上链接

1.4 多版本并发控制(MVCC)

MVCC可以说是行级锁中的一个变种，它在很多情况下避免了加锁操作，因此开销更低，虽然Mysql Oracle PosgreSql 实现的机制不同，但大多数都实现了非阻塞的读操作，写操作也只锁定必要的行。MVCC的实现，其实是通过数据某个时间点的快照来实现的，也就是说不管你执行多少时间，每次看到的数据都是一致的, 每个事务对同一张表，同一时段看到的数据有可能不一样，前面提到MVCC实现的机制不同，典型的有乐观/悲观并发控制，我们可以通过InnoDb的行为来说明MVCC是怎么工作的，MVCC通过在每一行数据后面加上两个隐藏的列来实现：一个是创建时间戳，一个是删除时间戳。当一个事务开始时，它会记录当前的系统版本号，并且只能看到在该版本号之前已经提交的数据。如果其他事务在该版本号之后修改了同一行数据，那么该事务就需要回滚并重新开始。并且只有不可重复读和读已提交的隔离级别下才能兼容MVCC，
MVCC具体操作
Select
innodb会根据两个条件检查每行记录，
1. Innodb会查找早于当前事务多版本的数据行(行的版本号会小于等于事务的版本号)来保证事务开始之前已经存在了
2. 行的删除版本没定义
只有符合这两个二条件，才能返回查询结果
Insert
Innodb为插入的数据保存当前系统版本号作为行版本号
delete
innodb为删除每一行保存当前版本号作为行删除标识
update
innodb插入一条新记录，会保存当前系统版本号行系统版本号

1.5 Mysql存储引擎

在文件系统中，Mysql将每个数据库保存为数据目录下的一个子目录，创表时，Mysql会在数据库子目录创建一个和表同名的.frm文件来保存表的定义。
可以使用 show table status (mysql5.0版本以后可以使用 informmation_schema)命令显示表的相关信息。
使用 show TABLE status可以查看当前数据库中所有表的基本信息，包括表名、引擎类型、行数、创建时间等。例如，可以使用以下语句查看表 mytable 的基本信息：

SHOW TABLE STATUS LIKE 'mytable';

使用 information_schema 可以查看更加详细的表结构信息，包括列名、数据类型、约束条件等。例如，可以使用以下语句查看表 mytable 的列信息：

SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_KEY, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable';'''

select TABLE_NAME from information_schema.TABLES  where  TABLE_SCHEMA='test' limit 5,1

1.5.1 InnoDB概述

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子（由一系列的数据文件组成），在Mysql4.1以后的版本中，InnoDB可以将每个表的数据和索引存放在单独的文件中，InnoDB也可以使用一些设备来作为表空间的存储介质。
InnoDB采用的是MVCC来支持高并发的，并实现了四个管理级别，默认值是REPEATABLE READ(可重复度)，并且通过间接锁来防止幻读的出现，间接锁使得InnoDB不仅仅锁定了查询涉及的行，还会对索引中的间接锁进行锁定，用来防止幻影行的插入.

1.5.2 MyISAM概述

Mysql5.1版本之前默认的是MySAM存储引擎，为什么在Mysql5.1之后的版本中不推荐使用MySIM了？
除了不支持事务和行级锁和外键外最主要的原因是崩溃后无法安全恢复数据，在存储方面MySAM会将表存储在两个文件中(数据文件、索引文件)分别以.MYD和.MYI为扩展名。
MySAM是最早的存储引擎之一，它包含了很多年的特性，可以满足用户的一些实际需求

加锁与并发：对整张表加锁。读时会对需读到的所有表加共享锁，写入时则对表加排他锁。
修复：对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，（非崩溃恢复）
索引特性：对BLOB和TEXT等长字段，也可基于其前500个字符创建索引；支持全文索引，支持复杂的查询
延迟更新索引键（Delayed Key Write）：不立刻将修改的索引数据写入磁盘，而是写到内存中的键缓冲区，只在清理键缓冲区或者关闭表的时候将对应的索引块写入到磁盘。

1.6 Mysql压缩表

表在创建并导入数据后，不会进行改操作的场景，就可以使用MyISAM压缩表，可以使用myisapack对MyISAM表进行压缩，压缩后可以减少磁盘的I/O提高查询性能

1.7 Mysql其他存储引擎

Archive引擎：支持INSET和SELECT操作，支持行级锁和缓冲区，但是每次SELECT查询都要全表扫描，所以Archive表适合日志和数据采集的作用.

Blackhole引擎：会丢弃所用的插入数据，服务器会记录Blackhole表的日志，所用一般用于数据复制的备库.

CSV引擎：可将普通的CSV文件作为MySQL的表来处理,可在数据库运行时拷入或者拷出文件。可以将Excel等电子表格软件中的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL中打开使用,可以作为一种数据交换的机制.

1.8 第三方存储引擎

OLTP类引擎：支持MVCC、ACID、事务
Percona的XtraDB存储引擎是基于InnoDB引擎的一个改进版本，主要改进点在性能方面，可以兼容的读写InnoDB的数据文件和所用查询

TokuDB引擎使用了一种新的叫做分形树(Fractal Trees)的索引数据结构。该结构是缓存无关的，因此即使其大小超过内存性能也不会下降，也就没有内存生命周期和碎片的问题

1.8.1 选择合适的引擎

无特定需求，使用默认InnoDB.。有特殊需求，如只涉及insert/select，考虑使用MyISAM。其他场景结合存储引擎特性考虑

1.8.2 转换表的引擎

三种方式：
直接ALTER TABLE: 语法简单，但消耗系统的所有I/O能力
导出与导入：先导出结构和数据，改表名和存储引擎，再执行。（相当于新建表）
创建与查询（CREATE & SELECT）: 先创建新表结构，再用INSERT SELECT语法复制数据。（还是新建表方法）

1.9 Mysql的数据线

版本3.23 （2001）: 引入MylSAM代EISAM引擎
版本4.0 （2003）: 支持新的浯法，重写了复制，lnnoDB成为标配，查询缓存
版本4.1 （2005）: 引入了更多新的语法
版本5.0 （2006）: 一些“企业级"特性：视图、触发器、存储过程和存储函数
版本5.1 （2008）:入了分区、基于行的复制；事务存储弓摩
版本5.5 （2010）: 改善、性能、扩展性、复制、分区、对Windows的支持
版本5.6：查询优化器的改进

1.10 Mysql的开发模式

开发测试的版本只是为了获取数据和反馈，GA版本才是可用的版本。
Mysql遵循GPL协议，全部的源代码(除了一些商业版本插件)都会开放给社区，

1.11 总结

mysql分层的结构，上层是服务器层和查询执行引擎，下层是存储引擎。服务器层通过API调用存储引擎层，不同存储引擎对数据的存储方式，以及API的实现方式不同。
存储引擎发展简述：ISAM -> MyISAM -> InnoDB

2 基准测试

2.1 为什么需要基准测试

基准测试是对针对系统设计的一种压力测试，可以测试当前系统在各种运行时的情况，比如可以测试出系统的在什么压力下到达系统的扩展瓶颈和，验证出不同的硬件、软件、操作系统的配置会对我们读写数据的性能由什么帮助，最后证明我们买的新设备配置是否正常。

2.2 基准测试的策略

集成式：
要测试整个应用系统，包含Web服务器，应用代码、网络和数据，不过整体的应用基准测试很难搞，至少本博主没有测试过
单组件式:
针对某个组件的测试，可以是Mysql、Redis、服务器等

2.3 测试指标

看吞吐量、响应时间和延迟、并发行、可扩展性。
吞吐量：单位时间内的事务处理数。
响应时间和延迟：测试任务的整体时间。
并发行：很多人都有个误解，本人也有这样的误解，常常把它表示成多少个用户量在同一时间浏览一个Web站点，经常使用的指标是多少会话，可是HTTP协议是无状态的，大多数用户只是在简单的读取浏览器上显示的信息，这并不是Web服务器的并发性，而且Web服务器的并发现并不等于数据库的并发性，但在不同环节下的并发性Web服务器的高并发，一般也会导致数据库的高并发，所以并发性基准测试要注意的是正在工作中的并发操****作，就是工作中的核心线程和连接数，但我们并发增加时，需要测试吞吐量(下降)和响应时间(变长)，如果是这样就有可能无法处理峰值压力。
可扩展性：理想状态下给系统一倍的资源(比如提升硬件、服务器等)，获取两倍的吞吐量，

2.4 设计和规划基准测试

首先需要大量的生产数据集快照、如果针对查询可以在高峰期间的一小时内进行记录测试

2.5 获取系统性能和状态

在执行基准测试前，需要收集系统的一些信息(CPU使用率、磁盘I/O、网络流量统计等)，在测试前最好为基准测试建立一个目标，用来保存测试结果、配置文件、测试指标、脚本等。
收集Mysql测试数据的脚本，

2.6 获取基于结果测试

获得准确测试结果的最好办法，是回答一些关于基准测试的基本问题：是否选择了正砖
的基准测试？是否为问题收集了相关的数据？是否采用了错误的测试标准;
（基于MySQL的默认配置的测试没有什么意义，因为默认配置是基于消耗很少内存的极小应用的，所以MySQL之前一直被认为是小型应用专用数据库）

2.7 运行基准测试并分析结果

1.可以使用自动化(易操作、易分析、易采取)
2。QPS:数据库每秒查询次数； TPS: 数据库每秒执行事务数

2.8 基准测试工具

集成式测试工具(针对整个应用系统)
ab：测试Http服务器每秒可处理请求数
http_load：同为Http服务器，比ab更灵活
JMeter：Java应用程序，加载其他应用并测试其性能
单组件式测试工具（针对MySQL和基于MySQL的系统进行测试）
mysqlslap：模拟服务器负载，输出计时信息
MySQL Benchmark Suite：在不同数据库服务器上进行比较测试
Database Test Suite：工业标准测试
Percona's TPCC-MySQL Tool：评估大压力时MySQL的一些行为
sysbench：多线程系统压测工具，评估系统性

2.9 基准测试案例

1 http_load

基于Linux平台的软件。
安装地址
完成后将文件解压
tar xzvfhttp_load-12mar2006.tar.gz
进入文件编译（需要C环境）
make install
写一个文件，里面放入的为要测试的网址
如下为书中给出的网址
http://www.mysqlperformanceblog.com/
http://www.mysqlperformanceblog.com/page/2/
http://www.mysqlperformanceblog.com/mysql-patches/
http://www.mysqlperformanceblog.com/mysql-performance-presentations/
http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/
然后可以执行如下命令：
./http_ load -parallel 并发数目 -seconds 时长文件名
./http_ load -rate 频率 -seconds 时长文件名

-parallel ：并发进程数。
-fetches ：总计访问次数
-rate ：每秒访问频率
-seconds ：总计访问时间

2 sysbench
sysbench可以执行多种类型的基准测试，它不仅设计用来测试数据库的性能，也可以
测试运行数据库的服务器的性能

Percona的TPCC-MySQL测试工具

尽管sysbench的测试很简单，并且结果也具有可比性，但毕竟无法模拟真实的业务压
力。相比而言，TPC-C测试则能模拟真实压力。

2.10 总结

首先正确地描述问题，之后选择合适的基准测试来回答问题。设置基准测试的持续时间和参数，运行测试，收集数据，分析结果数据，

3 服务性能优化解析

3.1 性能优化简介

因为关于性能问题5个人回答都会有不同的答案，所以本章是把**性能定义成响应时间，这里有有个原则我们是通过任务和时间来测量性能的，而不是就是搞资源来测量性能，因为数据库服务器的目的是执行SQL语句，所以它关注的是查询或者语句，哪如何优化性能了，我们可以假设下就是在一定工作负责的情况下降低响应时间,比如：很多人认为性能优化是降低CPU的利益率，那就可以减少对资源的使用率，但这里有个误区，资源是用来消耗并来工作中的，所以有时会消耗更多的资源是可以加快查询速度，但是那是老版本的Mysql的InnoDB引擎了，如果升级后那么CPU利用率会上升的很快，这不会代表性能出现了问题，只是说明了InnoDB对资源的利用率上升了，因为升级后会出现一些bug，所以不能用CPU利用率来度量目标，如果想要降低响应时间，就要理解服务器执行查询为什么需要怎么多时间，后在解决对获取查询结果不必要的工作，就引出了无法度量就无法优化,在优化是很少人会把精力放在精确的测量上，性能优化 = 测量 + 修改，完成一项任务需要执行时间+等待时间， 所以任务时间 = 执行时间 + 等待时间。

3.1.1 通过性能剖析进行优化

性能剖析是种方法，用于测量和分析时间花费在哪里。
两个步骤：
1、测量任务所花费的时间
2、对结果进行统计和排序
性能剖析工具的工作方式基本相同：在任务开始时启动计时器，在任务结束时停止计时器，两者相减得到响应时间。
讨论两种类型的性能剖析：基于执行时间的分析和基于等待的分析。

3.1.2 理解性能剖析

MySQL的性能剖析(profile)将会将最重要的任务展示在前面，但有时候没显示出来的信息也很重要，

值得被优化的查询：优化的成本大于收益，就要停止优化
异常情况：某些功能即使没有出现在性能剖析的报告中也需要优化，因为每次执行它都非常慢，会严重影响用户体检
未知的未知：“丢失的时间”，任务的总时间和实际测量到的时间之间的差。
被掩藏的细节：性能剖析会隐藏很多信息，而且无法表达全部情况：直方图、百分比、标准差、偏差指数等（较大波动因取平均值会被隐藏）

3.2 对应用程序进行性能解析

Mysql服务器的性能解析推荐系统自上而下进行，这样可以追踪用户发起到服务器响应的整个流程,
虽然性能问题大多数情况下都和数据库有关，但应用导致的性能问题也不少。
性能瓶颈可见的因素：
外部资源：比如调用了外部的Wb服务或者搜索引擎。
应用需要处理大量的数据：比如分析一个超大的XML文件。
在循环中执行昂贵的操作：比如滥用正则表达式。
使用了低效的算法：比如使用暴力搜索算法(naive search algorithm)来查找列表中的项。
建议在所有的新项目中都考虑包含性能剖析的代码。（性能剖析收益 >> 成本）,建议使用new Relic

3.3 剖析Mysql查询

MySQL查询性能剖析两种方式：
1. 剖析整个数据库服务器、
使用慢查询日志和通用日志用来记录执行服务器上的所用的查询，但建议用慢查询日志好点，因为可以在业务高峰期内记录一个小时的查询，如果业务没在高峰期，也可以在短时间内捕获需要优化的低效SQL语句，生成剖析报告pt-query-digest
2. 对这些查询进行单独的剖析。
使用show profile(比较稳定的查询剖析工具)，默认是禁用的，但可以通过 set profiling =1(可以在服务器上进行会话/全局级别) 进行修改，
SHOW STATUS命令返回了一些计数器。既有服务器级别的全局计数器
使用慢查询和Performance Schema

3.3.1 使用性能剖析

好的剖析报告能够将潜在的问题显示出来，但最终的解决方案还需要用户来决定（尽管报告可能会给出建议）。

优化查询时，用户需要对服务器如何执行查询有较深的了解。剖析报告能够尽可能多地收集需要的信息、给出诊断问题的正确方向，以及为其他诸如EXPLAIN等工具提供基础信息。

3.4 诊断间歇性问题

间歇性的问题比如系统偶尔停顿或者慢查询，很难诊断。只有在问题发生的地方通过观察资源的使用情况，并尽可能地测量出数据，才能避免在没有问题的地方耗费精力。

3.4.1 解决间接性问题的方法

单条查询问题：
指服务器整体没有问题，只是某条查询变慢。
服务器问题：
指系统里的所用功能和接口查询突然表慢，又突然表好。如果出现了服务器问题，可以试试升级Mysql版本。
那如何判断是是单查询问题和服务器问题，可以通过三种技术。

使用 show global status

它是已较高的效率执行show global status 命令来获取数据，有问题时，可以通过某些计算器(如：Threads_running,Threads_connected、Questions和Queries)来发现。
列：

$ mysqladmin ext -i1 | awk ' /Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

这命令可以每秒捕获一次 show global status 的数据，并输出给awk计算出结果：每秒的查询数、Threads_connected、Threads_running，并可以长时间运行后将结果绘制出图像来，更加准确的定位问题

3.5 使用show processlist

这个方法可以不断地捕获show processlist的输出，来观察大量的线程是否处于正常状态的特性，在使用show processlist命令时可以在结尾加上\G来显示输出结果

$ mysql -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn

3.6 使用查询日志

如果通过查询日志发现问题，需要开启查询日志并在全局设置long_query_time为0，并确认连接都采用了新的设置，附上链接

3.7 理解发现的问题（Making sense of the findings）

可视化的数据最具有说服力。诊断问题时先使用前两种开销低的方法：SHOW STATUS和SHOW PROCESSLIST，并结合绘图展示。
在尝试解决其他人提出的问题之前，要先明确两件是。并记录下来

问题是什么，一定要清晰的描述出来，但花时间去解决一个错误的问题是常有的事情(注：在公司里不可能给你花时间用在错误的方法上，所以一般是先解决问题，然后自己在花时间复盘下)
为解决这个问题用过什么方法，做个什么操作

3.8 捕获诊断数据

当出现间歇性问题时，需要尽可能多地收集所有数据，而不只是问题出现时的数据。需要搞清楚两件事：

3.8.1 诊断触发器

选择一个参数的适当阀值，做为收集数据的开始时间点。

选择一个合适的阈值很重要，既要足够高，以确保在正常时不会被触发；又不能太高，要确保问题发生时不会错。

3.8.2 需要收集什么样的数据

现在需要收集什么样的数据呢？尽可能收集所有能收集的数据，包括系统的状态、CPU利用率、磁盘使用率和可用空间、ps的输出采样，内存利用率，以及可以从Mysql获取信息，如status processlist,
show innodb status。可以用pt-collect工具来收集数据，一般都是通过pt-stalk来调用，因为要收集重要数据，一般都是用root权限，启动后会自动收集30秒的数据，然后退出

3.8.3 解释结果数据

如果已经正确地设置好触发条件，并且长时间运行pt-stalk，则只需要等待足够长的时间来捕获几次问题，就能够得到大量的数据来进行筛选。

3.9 其他剖析工具

基本在工作中用到的不多，除非在一些特定的场景下，但可以了解下。

3.9.1 使用user_statistics表

Percona Server 和MaiaDB都引入了一些对象级别的统计表 informtaion_schema，本是由Google开发的，在一些情况下DBA很少有开发话语权，那就可以通过这些表实现对数据库的测量和统计就可有强制执行的策略，并且可查找到用的索引最多和最少的表，也可以找到没用过的索引，考虑要不要删除，在Performmance Schema中有很多类似的功能。
在这里插入图片描述

3.9.2 使用strace

strace工具可以调查系统调用的情况。有好几种可以使用的方法，其中一种是计算系统调用的时间并打印出来
在这里插入图片描述

3.10 总结

基本知识点

定义性能最有效的是响应时间
无法测量就无法优化，所以优化就要整体的响应时间测量
测量的开始点是应用程序，而不是数据库。
很多系统无法完整的测量，并且结果也会有错误，但可以饶过一些限制，等到一些好的结果
正在的测量需要产生大量分析的数据，可以用剖析器，并且要把重要的问题发到最前面。
优化和提升是两回事，当提升的成本超过收益时、应该停止优化
注意自己的直觉，不能完成凭借经验主义，还要注意到数据的方面
解决性能问题的方法，首先是要澄清问题，然后选择合适的技术来答问题。（pt-query-digest工具）

4 Schema与数据类型优化

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要执行的查询语句来设计
schema,这往往需要权衡各种因素。在这三章中学习的是逻辑设计，物理设计，查询执行他们之间的关系。

4.1 选择可优化的数据类型

Mysql支持的数据类型很多，选择合适的数据类型可以获取更高的性能。主要原则可以从这几方面选择
1、更小的通常好
一般情况下，可以使用正存储最小数据类型，比如 tinyint, unsigned ，为什么要更小的因为更小的通常更快，占用更少的内存、cpu缓存、磁盘IO。
2、简单就好
简单的数据类型的操作可以减少CPU的周期，
3 尽量避免NULL
很多表包含null并且有的列以null为默认属性，但系统并不需要保存NULL值，并且如果查询中有包含有NULL的列，对Mysql就很难优化，因为NULL会加重对索引的计算，所以通常把列指定为NOT NULL。

4.1.1 整数类型

4.1.2 整数

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。
例如TINYINT.UNSIGNED可以存储的范围是0~255，而TINYINT的存储范围是**-128-127**。

4.1.3 实数

实数：FLOAT、DOUBLE、DECIMAL，FLOAT4个字节，DOUBLE8个字节。DECIMAL字节可变，最多65个数字。

4.1.4 字符串类型

VARCHAR和CHAR是两种最主要的字符串类型
VARCHAR：长度可变，节省空间，但UPDATE时可能会导致行空间变大，导致额外工作。VARCHAR的长度字段最大为2字节，故VARCHAR最大长度为65536.（）。
CHAR: 长度固定，空间可能浪费，但不易产生碎片，效率也高。
VARCHAR(5)和VARCHAR(200)在存储时效果一样，但在查询时由于内存会按长列分配固定长度内存，所以查询效率不一致。

4.1.5 日期和时间类型

dataTime和TimeStamp是最常用的时间类型。
dataTime可以存储范围比较大的值，边界是1001-9999年，精度为秒，默认封装类型YYYYMMDDHHMMSS，默认8个字节存储空间。不会根据地区改变时间
TimeStamp保存范围为1970年1月1日，存储4个字节存储空间，会根据不同的时区显示不同的时间。
通常尽量使用TimeStamp，因为它比dataTime的空间效率更高。

4.1.6 位数据类型

Mysql的位数据类型是比较紧凑的存储数据，从技术上说是字符串类型。
bit:mysql5.0之前和tinyint是同类型，bit类型可以存储多个true和false值。
set:如果需要保存N个true/false，可以合并到set数据类型中。这些了解即可

4.1.7 选择标识符

物理主键，最好使用整数, 可使用AUTO_INCREMEN

4.1.8 特殊类型数据

IPv4地址，经常被VARCHAR（15）来存储IP地址，但它们实际上是32位无符号整数，不是字符串。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

4.2 MySQL schema设计中的陷阱

有一些问题是由MySQL的实现机制导致的：
太多的列：
太多的关联：EAV(实体-属性-值)设计模式并不是一个很好的模式，因为Mysql限制了每个关联操作只能是61张表，但EAV模式的数据库有可能会超过这个限制，所以为了查询执行的效率好建议单个查询做到12表以内的查询
全能的枚举
变相的枚举
非此发明（Not Invent Here）的NULL：不一定需要NULL值，可以通过某种特殊值来做位代替

4.3 范式和反范式

范式：

第一范式：列不可再分
第二范式：有主键，且所有非主键属性都依赖主键，且不能依赖主键部分（针对联合主键）
第三范式：属性不依赖其他非主键属性，即无传递性
范式优点：数据简洁、无冗余，修改快

范式缺点：关联查询效率低下。

完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端地使用。

4.4 缓存表和汇总表

在数据量大的表按某些条件汇总数据至小表。
缓存表：每次获取查询比较慢的数据表。
汇总表：保存使用group by语句聚合数据的表。
当重建汇总表和缓存表时，要保证数据在操作时依然可用。通过使用“影子表”来实现，“影子表”指的是一张在真实表“背后”创建的表。当完成了建表操作后，通过一个原子的重命名操作切换影子表和原表。例如，如果需要重建my_summary，则可以先创建my_summary_new，然后填充好数据，最后和真实表做切换：temp = a , a = b , b =temp

4.4.1 物化视图

物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新

4.4.2 计数器表

有一个计数器表，只有一行数据，记录网站的点击次数：

CREATE TABLE hit_couter (
cnt int unisgned not null
)ENGINE=InnoDB;
update hit_counter set cnt = cnt+1;

要获得更高的并发更新性能，也可以将计数器保存在多行中，每次随机选择一行进行更新。然后预先在这张表增加100行数据。现在选择一个随机的槽（slot）进行更新：

	CREATE TABLE hit_counter ( 
    slot tinyint unsigned not null primary key, cnt int unsigned not null
) ENGINE=InnoDB;
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
SELECT SUM(cnt) FROM hit_counter;

4.5 加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大间题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作很费时间。一般来说，Alter table操作有可能会导致Mysql服务中断，对一般的场景可以使用两种技巧：1、在另一个不提供服务的服务器上执行Alter table操作，然后和提供服务的主库进行切换。2、影子拷贝，插件结构相同的新表，然后通过重命名和删除操作进行表交换；t通过工具 online schema change 实现影子拷贝操作。

4.5.1 只修改.frm文件

修改表的.frm文件是很快的，但MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重建表：
建完新表后，直接交换新表与旧表的.frm文件

4.5.2 快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引、载入数据，然后重新启用索引：

ALTER TABLE test.load.data DISABLE KEYS;
-- Lood data
ALTER TABLE test.load_data ENABLE KEYS;

这个办法对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效。MyISAM会在内存中构造唯一索引，并且为载入的每一行检查唯一性。一旦索引的大小超过了有效内存大小，载入操作就会变得越来越慢。

4.6 总结

尽量避免过度设计
使用小而简单的合适数据类型，避免使用NULL值。
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
尽量使用整型定义标识列。
小心使用ENUM和SET。最好避免使用BIT。
范式是好的，但是反范式有时也是必需的，并且能带来好处。

5 创建高性能的索引

索引优化应该是对查询性能优化最有效的手段了。

5.1 索引基础

索引可以包含一个或多个列，如果包含多个列那么就要满足最左匹配原则
如：索引为（index_A，index_B，index_C）那么查询时也要保证必须先有A的值，再有B的值。例子：select * from table where index_A = ‘xiao’ and index_B= ‘sql’

5.1.1 索引的类型

目前大部分数据库系统都是采用了B-Tree何和+Tree，所以要深入了解这两种类型，其他的索引类型了解即可。

5.1.2 B-Tree

B-Tree的索引结构通常意味着所有的值都是按顺序执行的，并且每个叶子节点相同，所以B-Tree是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。
叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页，树的深度和表的大小又直接相关。所以很适合查找范围数据。但不适合插入和修改操作，因为执行这些操作它的指针需要移动，当我们的树中的根节点很深时，那边移动指针的代价就很大。所以这时就用到B+Tree

5.1.3 B+Tree

B+Tree是在B-Tree的基础上进行了优化，把B-Tree储存的指针，改成每个节点就储存关键字，并且插入和删除操作只需要进行节点的分裂和合并，不需要移动指针，因此效率更高。

5.1.4 哈希索引

哈希索引基于哈希表实现，对于每一行数据，储存引擎都会对所有的索引列计算出一个哈希码，所有一般用在精确匹配索引列的查询场景。只有Menory引擎才支持哈希索引，其他引擎都不支持。如果多个列的哈希值都相同的话，索引会以链表的方式储存在哈希条目中。

CREATE TABLE testhash 
(
fname VARCHAR(50)NOT NULL,
1name VARCHAR(50)NOT NULL,
KEY USING HASH(fname)
)
ENGINE=MEMORY;

select * from texthash;

查询的数据如下:
在这里插入图片描述

在这里插入图片描述
MySQL先计算’Peter’的哈希值，并使用该值寻找对应的记录指针。因为f(‘Peter’)=
8784,所以MySQL在索引中查找8784，可以找到指向第3行的指针，最后一步是比较
第三行的值是否为’Peter’,以确保就是要查找的行。同时InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，

5.1.5 空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。(了解就好…)

5.1.6 全文索引

**全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。**它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。(下章节介绍…)

5.1.7 其他索引类别

TokuDB使用分形树索引（fractal tree index）

ScaleDB使用Patricia tries…

5.2 索引的优点和缺点

索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变为顺序I/O。
缺点
当表中的数据发生变化时，索引也要更新，这会增大写操作的开销
索引只能加速特定类型的查询，对于其他类型的查询可能没有任何帮助。

5.3 高性能的索引策略

独立的列：指不是表达式的一部分，也不是函数的参数，负责Mysql就不会使用索引
此语句中不会使用索引actor_id:,因为它是表达式的一种。

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5

5.3.1 索引选择性和前缀索引

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，选择性高的索引可以在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

前缀索引是指将列截取开头合适长度后的最为索引，是一种能使索引更小、更快的有效办法，一般用到的场景是Text,blob,varchar三种类型，前两种是必须使用前缀索引的，最后一种看情况，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

5.3.2 多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0和更新版本引入了一种叫“索引合并”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。

5.3.3 选择合适的索引列顺序

关于选择性和基数的经验法则值得肯定，但不能忘记排序、分组和范围条件等其他因素造成的影响。

5.3.4 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引是在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起，所以一个表只能有一个聚簇索引。

选择哪个索引作为聚簇索引?

InnoDB将通过主键聚集数据
如果没有定义主键，InnoDB会选择一个唯一的非空索引代替
如何没有唯一非空索引，InnoDB会隐式定义一个主键来作为聚簇索引

聚簇索引的优缺点:

优点：
1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
2.聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：

1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

5.3.5 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。作用可以避免根据索引得到的地址后，再回表查询。

 -- 索引为（A）
select A from table where A >= 6;
 
-- 索引为（A, B, C）
select A, C from table where A = 6 and B =7;

Using index 说明索引覆盖了查询结果，避免了回表查询
在这里插入图片描述

5.3.6 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。如果Explan出来的type列为 ‘index’,就说明了Mysql使用了索引扫描来做排序(不要和Extra列的’Using index’搞混了)。同时只有索引的列的顺序和order by 之句中的顺序一样Mysql才能使用索引对结果进行排序。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者J0IN子句中对这些列指定了常量，就可以“弥补”索引的不足。

最好能直接使用索引的排序，避免额外排序操作。

5.3.7 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。

5.3.8 冗余和重复索引

重复索引：相同的列上按照相同的顺序创建相同的索引类型
冗余索引：冗余索引和重复索引有一些不同。如果创建了索引(AB),再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(A,)也可以当作索A来使用。
不要有重复索引，根据需求可适当创建冗余索引。

5.3.9 未使用的索引

删除不会用到的索引，可以使用Percona Server 或者Maria DB打开userstats服务器变量(默认关闭的)然后先运行一下，在通过information_schema_index_statistics查看每个索引的使用频率。
第二种使用Percona ToolKit 中的pt-index-usage 工具来读取查询日志，对日志每条查询使用Explan操作，后打印用关于索引和查询的报告。

5.3.10 索引和锁

索引可以减少不必要数据读取，从一个角度较少了锁竞争。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。
如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句，Using where 说明服务器层还需额外过滤数据。
在这里插入图片描述

5.4 索引案例学习

5.4.1 支持多种过滤条件

现在需要看看哪些列拥有很多不同的取值，哪些列在WERE子句中出现得最频繁。在有更多不同值的列上创建索引的选择性会更好。一般来说这样做都是对的，因为可以让MySQL更有效地过滤掉不需要的行。

5.4.2 避免多个范围条件

5.4.3 优化排序

5.5 维护索引和表

日常中还要对表和索引进行维护，维护表有三个主要的目的，找到并恢复损话的表，维护准确的索引统计信息，减少碎片

5.5.1 找到并修复损坏的表

表损坏（corruption）是很糟糕的事情。会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。

CHECK TABLE检查表是否损坏，通常能够找出大多数的表和索引的错误。

REPAIR TABLE修复损坏的表。也可通过一个不做任何操作（no-op）的ALTER操作来重建表：ALTER TABLE innodb_tbl ENGINE=INNODB;

InnoDB一般不会出现损坏，如果出现了损坏，那么一定是发生了严重的错误，需要立刻调查一下原因。

5.5.2　更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。

records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。MyISAM返回精确值，InnoDB返回估算值
info()，该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。
如果表没有统计信息，或者统计信息不准确，可以通过运行ANALYZE TABLE来重新生成统计信息。

InnoDB会在几个场景触发索引统计信息的更新。可以通过innodb_stats_on_metadata关闭自动生成统计信息。

一旦关闭索引统计信息的自动更新，那么就需要周期性地使用ANALYZE TABLE来手动更新。否则，索引统计信息就会永远不变。

5.5.3　减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。有三种类型的数据碎片。

行碎片（Row fragmentation）：指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
行间碎片（Intra-row fragmentation）：行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响
剩余空间碎片（Free space fragmentation）：剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。
对于MyISAM表，这三类碎片化都可能发生**。但InnoDB不会出现短小的行碎片**，InnoDB会移动短小的行并重写到一个片段中。但在B+Tree索引中已经可以帮组我们解决数据的碎片的问题了。

可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。

5.6 总结

MySQL中多数情况会使用B-Tree索引。回顾一下B-Tree索引。

在选择索引和编写利用索引的查询时有三个原则要记住：

单行访问是很慢的。使用索引可以创建位置引用以提升效率。
按顺序访问范围数据是很快的。（无磁盘I/O, 也没有排序和分组）
索引覆盖查询是很快的。（直接从索引获取结果，无需再回表查询）

总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。

6 查询性能优化

好的表结构和适合的索引，对于高性能是必不可少的，但这些还不够还需要合理的设计查询。这章会从查询设计原则开始(也会发现效率不高的情况首先要考虑的一些因素)，理解Mysql如果执行查询，并明白高效和低效的原因在那。

6.1 为什么查询速度会慢

在写SQL查询时，真正重要的是响应时间，例：如果把查询看作是一个任务，那么它由一系列的子任务组成，每个任务都会消耗一些时间，如果要优化查询，实际上是优化子任务，要么就消除一些子任务，要么减少子任务的执行次数。查询的生命周期中执行是最重要的阶段，一般花费时间最多的地方就在这个阶段，因为查询需要在不同的地方花费时间（网络，CPU计算，生成信息，执行计划等）,总结出来就是访问并不需要的数据大多和扫描到额外的数据，

6.2 慢查询基础: 优化数据访问

优化数据访问：
查询时减少访问的数据量，
mysql服务层是否存在分析大量的数据行

6.2.1 向数据库请求了不需要的数据?

我们写的查询语句时要注意几点：

查询不需要的记录：常认为Mysql会返回我需要的数据，但实际上Mysql是先返回全部结果集后进行计算，可以使用limit
多表关联时返回全部列：避免使用select *,真要需要的时候也要考虑提供的原型中是否需要这些字段，如果全部需要就可以加上select *,这样的好处是可以避免一些列被修改和提高代码的服用性，但不要忘记它的代价是会消耗点数据库资源
重复查询相同的数据：解决方案，初次查询时把数据缓存起来，需要时就取

6.2.2 Mysql是否扫描了额外的数据

衡量查询开销的三个指标

响应时间: 服务时间(处理sql查询总共花了多长时间)和排队时间（没有执行查询的等待时间）
扫描的行数: 查看扫描行数能说明找到需要的数据效率高不高
返回的行数：返回的行数有可能不一致，
查看explain 中的type访问类型(ALL) 全表扫描，Extra Using where 通过where 条件筛选出来返回的行数
这三个指标可以在慢日志里，可以查看慢日志找出扫描行数多的查询。
一般在Mysql能够使用下面三种方式应用where条件，从好到坏为
1、索引中使用了where条件过滤不匹配的记录，在存储引擎层完成的
2、使索引覆盖扫描(Extar 列出现 Using index)，从索引中过滤不需要的记录返回命中的结果，在Mysql服务器层完成的
3、从数据返数据（Extar 列出现 Using where），)然后过滤掉不满足的条件

6.3 重构查询的方式

在优化有问题的查询时，目标是找到一个更优的方法获取实际需要的结果。有时候可以让查询转换成另外一种写法获取结果，但是性能更好。

6.3.1 一个复杂查询还是多个简单查询

设计查询要考虑的问题，是否需要将一个复杂查询分成多个简单的查询，
最开始时强调数据库尽可能的完成工作，查询解析和优化是一件代价很高的事情，Mysql从设计方面连接和断开都是轻量级的。

6.3.2 切分查询

将大查询切分成多个小查询，每个查询功能完成一样，只完成一小部分，每次返回一小部分查询结果。
删除旧的数据是一个很好的例子，定期清除大量数据时，用一个大的语句一次性完成的话，就有可能需要一次锁住很多数据，占满事务日志，消耗更多的资源，如果将一个大的Delete 语句切分成多个较小的查询就能更小的影响Mytysql性能，
比如：

delete from messages where created<DATA_SUB(now(),interval 3 month)，

可以改为

rows affected  = 0
do{
rows_affected = do_query(
"DELETE FROM messages WHERE created < DATE_SUB(NOW（）,INTERVAL 3 MONTH) LIMIT 10000)
}while rows_affected >0

6.3.3 分解关联查询

很多时候都会用到对关联查询进行分解，如：

select * from tag JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post  ON tag_pos.post_id = post.id
where tag.tag = 'mysql'

可以分解成

select * from tag where  tag = 'mysql';
select * from tag_post where tag_id = 1234
select * from post where  post.id in (,,,)

用分解关联查询的方式重构查询有如下好处

让缓存效率更高
执行单个查询减少锁的竞争
在应用层做关联，更容易做好高性能和可扩展
使用In() 代替关联查询，可以让Mysql 按照 id顺序进行查询

6.4查询执行基础

客户端发送一条查询给服务器。
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端。
在这里插入图片描述

6.4.1 Mysql clien / server 通信协议

通信协议是“半双工”的，意味着任何一个时刻，要么是由服务器向客户端发送数据，要么就是客户端向服务器发送数据，这两个动作不饿同时发送
查询状态
可以使用show full processlits 命令
max_allowed_packet参数决定客户端请求长度。
SHOW FULL PROCESSLIST命令可查看连接状态：

Sleep：线程正在等待客户端发送新的请求。
Query：线程正在执行查询或者正在将结果发送给客户端。
Locked：在MySQL服务器层，该线程正在等待表锁。
Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集都复制到一个临时表中
Sorting result：线程正在对结果集进行排序。
Sending data：：线程可能在多个状态之间传送数据、或者在生成结果集、或者在向客户端返回数据。

6.4.1 查询缓存

解析一个查询语句之前，如果查询缓存是打开的，MySQL会优先检查查询是否命中查询缓存。检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询只要有一个字节不同，那么就不会匹配结果，如果当前查询刚好命中查询缓存，那么在查询结果之前Mysql会检查一次用户权限

6.4.2 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，Mysql在根据这个执行计划和出处引擎进行交互。
语法解析和预处理：
Mysql通过关键字将Sql语句进行解析，并生产对于的解析树，在解析阶段中Mysql会对我们的Sql语法进行解析是否有误，其中会包含验证关键字或者对应的顺序，而预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。
查询优化器：
SQL查询解析合法了，那么就会交给优化器将其转化为执行计划，一条Sql查询可以有很多种执行方式，最后会返回同样的结果，优化器的作用就是找到最好(成本最小)的执行计划。可以通过查看当前会话的last_query_cost t的值获取当前Mysql计算查询的成本、

select Sql_NO_cache count(*) from  sakila.film_actor;

在这里插入图片描述

show status like 'last_query_cost'

在这里插入图片描述

这个结果表示Mysql的优化器需要做1045个数据页的随机查询才能完成上面的查询，这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、分布情况。
MySQL优化器选择错误的执行计划

统计信息不准确： mysql依赖存储引擎提供的统计信息来评估成本，当有时统计信息的偏差很大，比如 innodb的MVCC架构。
Mysql的最优解可能和我们想的不一样：我们自己希望执行时间和响应时间尽可能的短，但是Mysql只是基于优化器来选择执行计划。
MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度
优化器策略：
1、静态优化（编译时优化）: 直接对解析树优化，比如可以在where中使用另一种等价形式、
2、动态优化（运行时优化）跟查询的上下文有关或者其他因素，如where中的取值

Mysql能够处理的类型：
重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行，优化器会按优化方式重新排序

将外连接转化成内连接：MySQL能够识别并重写查询OUTER JOIN语句，可以调整关联顺序，让外连接等价于一个内连接。
使用等价变换规则：MySQL可以使用一些等价变换来简化并规范表达式，合并和减少一些比较，移除一些恒成立和一些恒不成立的判断。
优化COUNT()、MIN()和MAX()：索引和列是否可为空通常可以帮助MySQL优化这类表达式。
预估并转化为常数表达式：当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行
子查询优化：MySQL在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。
提前终止查询：在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。
等值传播：如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上。
列表IN()的比较：在很多数据库系统中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。

数据和索引的统计信息：MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息（页面、索引基数、数据行、索引长度等），优化器根据这些信息来选择一个最优的执行计划。

MySQL如何执行关联查询：并不仅仅是一个查询需要到两个表匹配才叫关联，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联.

执行计划：MySQL的执行计划是一棵左测深度优先的树.

关联查询优化器：MySQL优化器最重要的一部分就是关联查询优化。多表关联的时候有多种不同的关联顺序来获得执行结果。关联查询优化器会选择一个代价最小的关联顺序.

排序优化：应避免排序或者尽可能避免对大量数据进行排序（filesort）。数据量小于“排序缓冲区”则使用内存，如果内存不够排序则分块后排序，将各块的排序结果存放在磁盘上然后合并（merge）

两次传输排序（旧版本使用）：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
单次传输排序（新版本使用）：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果

6.4.3 查询执行引擎

相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单地根据执行计划给出的指令逐步执行。（服务层按步骤调用存储引擎接口获取需要的数据）

6.4.4 返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。
如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。

MySQL将结果集返回客户端是一个增量、逐步返回的过程。

6.5 MySQL查询优化器的局限性

MySQL查询优化器对少部分查询不适用，而我们可以通过改写查询让MySQL高效地完成工作。

6.5.1　关联子查询

关联子查询会被优化器转换成exist语句或连接语句，但转换后的语句执行效率反而可能会变低

-- 原sql
SELECT * FROM sakila.film WHERE film_id IN (SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);
 
-- 转换后等效exist语句
SELECT * FROM sakila.film WHERE EXISTS (SELECT * FROM sakila.film_actor WHERE actor_id = 1AND film_actor.film_id = film.film_id);
 
-- 转换后的内连接语句
SELECT film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id = 1;

6.5.2　UNION的限制

有时,Mysql无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

– 原sql，查询量大但返回结果却很少，优化器无法处理

(SELECT first name,last name FROM sakila.actor ORDER BY last name)
UNION ALL
(SELECT first name,last name FROM sakila.customer ORDER BY last name)
LIMIT 20;

这条sql查询会先先等到actor, custormer的结果集然后在从临时表取出前20条数据，可以通过union 的两个子查询中加水limit 20 来优化减少查询命中的响应时间

(SELECT first name,last name FROM sakila.actor ORDER BY last name limit 20)
UNION ALL
(SELECT first name,last name FROM sakila.customer ORDER BY last name limit 20)
LIMIT 20;

union all 与 union 的区别和效率

6.5.3 索引合并优化

在5.0和更新的版本中，当WHERE子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行

6.5.4　等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的IN()列表，而MySQL优化器发现存在WHERE、ON或者USING的子句，将这个列表的值和另一个表的某个列相关联。那么优化器会将IN()列表都复制应用到关联的各个表中，但如果这个列表非常大，则会导致优化和执行都会变慢。

6.5.5　并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性，但是MySQL做不到，不要花时间去尝试寻找并行执行查询的方法。

6.5.6　哈希关联

MySQL并不支持哈希关联——MySQL的所有关联都是嵌套循环关联。

6.5.7　最大值和最小值优化

对于MIN()和MAX()查询，MySQL的优化做得并不好。如果该字段上并没有索引，MySQL将会进行一次全表扫描，然后再排序
不过可以尝试改写sql

-- 获取名字为PENELOP记录对应的最下actor_id
 
-- 原sql， 全表扫描后，再分组获取最小值
SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOPE';
 
-- 使用索引项actor_id，获取的第一条即满足条件
SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' LIMIT 1;

6.5.8 在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。
原sql语句，把两个表中的数据记录到cont字段中，Mysql提示不能在 FROM 子句中指定目标表‘ outer _ tbl’进行更新

UPDATE tbl AS outer_tbl set cnt = (select count(*) from 
tb1 inner_tb1 where inner_tb1.type = outer_th1.type)

可以通过使用生成表的形式来绕过上面的限制，因为MySQL只会把这个表当作一个临时表来处理。
改sql语句

update tb1  inner join (
select type,count(1)  as cnt from tb1  group by type
) as  der  using(type) set tb1.cnt =  der.cnt;

unsing的概念

6.6　查询优化器的提示（hint）

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hit)来控制
最终的执行计划附上链接

6.7　优化特定类型的查询

介绍如何优化特定类型的查询

6.7.1　优化COUNT()查询

count(*) = count(1): 获取所有行数
count(column): 获取该列有值的数量（排除NULL）
MyISAM会将总行数存在常量中，故没有任何条件的count(*)在该引擎中非常快，可以利用这个特性做点优化：
可以了解下Mysql5.6版本innodb对count()的优化，不过在mysql5.6以上的版本已经解决了

6.7.2　优化关联查询

这个话题基本都在上一章，但要特别注意的情况

确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列C关联的时候，如果优化器的关联顺序是B、A,那么就不需要在
B表的对应列上建上索引。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积，不同类型的关联可能会生成不同的结果等

6.7.3　优化子查询

尽可能使用关联查询代替子查询。

6.7.4　优化GROUP BY和DISTINCT

在Mysql中无法使用索引时，可通过group by的两种策略来完成，使用临时表或者文件排序来做分组。可以通过使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。
优化GROUP BY WITH ROLLUP：最好的办法是尽可能的将WITH ROLLUP功能转移到应用程序中处理。

6.7.5　优化LIMIT分页

在偏移量非常大的时候，例如可能是LIMIT 1000,20这样的查询，这时MySQL需要查询10 020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。

-- 原sql
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
 
-- 优化后，先根据索引项film_id筛选数据，再做关联获取需要的数据
SELECT film.film_id, film.descriptionFROM sakila.filmINNER JOIN (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5) AS lim USING(film_id);

有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得到对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：

-- 也可直接将limit语句转换为范围查询，前提是该字段是索引项
SELECT film_id, description FROM sakila.film WHERE position BETWEEN 50 AND 54 ORDER BY position;

关于offset 问题其实是Mysql扫描大量不需要的行然后在抛弃掉，可以通过使用书签记录上次取数据的位置，那下次就可以直接从改书签的位置开始扫描，这样就可以避免offset问题。

-- 原sql 通过租借记录做翻页，那可以根据最新一条租借记录向后追湖
select * from sakila.rental  order by rental_id  desc limit 20;
 -- 假设上面查询返回的主键为16049 - 16030  的租借 改 sql 
select * from  sakila.rental where  rental_id < 16030   order by rental_id desc limit 20;

6.7.6　优化SQL_CALC_FOUND_ROWS

分页的时候，一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示（hint），这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。MySQL只有在扫描了所有满足条件的行以后，才会知道行数，所以加上这个提示以后，不管是否需要，MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。

一个更好的设计是将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么我们每次查询时都是用LIMIT返回21条记录并只显示20条，如果第21条存在，那么我们就显示“下一页”按钮，否则就说明没有更多的数据，也就无须显示“下一页”按钮了。

另一种做法是先获取并缓存较多的数据——例如，缓存1000条——然后每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略，如果结果集少于1000，就可以在页面上显示所有的分页链接，因为数据都在缓存中，所以这样做性能不会有问题。如果结果集大于1000，则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮。这两种策略都比每次生成全部结果集再抛弃掉不需要的数据的效率要高很多。

有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值（实际上Google的搜索结果总数也是个近似值）。当需要精确结果的时候，再单独使用COUNT（*）来满足需求，这时如果能够使用索引覆盖扫描则通常也会比SQL_CALC_FOUND_ROWS快得多。

6.7.7　优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询）。

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。

6.7.8　静态查询分析

Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。这像是给MySQL所有的查询做一次全面的健康检查，它能检测出许多常见的问题。

6.7.9　使用用户自定义变量

用户自定义变量如果能够用好，发挥其潜力，可以写出非常高效的查询语句。

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。可以使用下面的SET和SELECT语句来定义它们：

SET @one := 1;
SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

然后可以在任何可以使用表达式的地方使用这些自定义变量：

SELECT ... WHERE col<=@last_week;

在了解自定义变量的强大之前，我们再看看它自身的一些属性和限制，看看在哪些场景下我们不能使用用户自定义变量。

使用自定义变量的查询，无法使用查询缓存。
不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中
5.0之前的版本，是大小写敏感的，所以要注意代码在不同MySQL版本间的兼容性问题
赋值符号:=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号
优化排名语句
用户自定义变量一个重要特性是你可以在给一个变量赋值的同时使用这个变量。换句话说，用户自定义变量的赋值具有“左值”特性。

来看一个更复杂的用法：先编写一个查询获取演过最多电影的前10位演员，然后根据他们的出演电影次数做一个排名，如果出演的电影数量一样，则排名相同。我们先编写一个查询，返回每个演员参演电影的数量：

在这里插入图片描述
再把排名加上去，这里看到有四名演员都参演了35部电影，所以他们的排名应该是相同的。

使用三个变量来实现：一个用来记录当前演员参演的电影数量，一个用来记录前一个演员的电影数量，一个用来记录当前的排名。只有当前演员参演的电影的数量和前一个演员不同时，排名才变化。

在这里插入图片描述

避免重复查询刚刚更新的数据
例如，我们的一个客户希望能够更高效地更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么。简单地，可以用下面的代码来实现：

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1; 
SELECT lastUpdated FROM t1 WHERE id = 1;

使用变量，我们可以按如下方式重写查询：

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW(); 
SELECT @now;

上面看起来仍然需要两个查询，但这里的第二个查询无须访问任何数据表，所以会快非常多。

统计更新和插入的数量
当使用了INSERT ON DUPLICATE KEY UPDATE（插入转更新）的时候，如果想知道到底插入了多少行数据，到底有多少数据是因为冲突而改写成更新操作的？实现办法的本质如下：

INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );

当每次由于冲突导致更新时对变量@x自增一次。然后通过对这个表达式乘以0来让其不影响要更新的内容。另外，MySQL的协议会返回被更改的总行数，所以不需要单独统计这个值。
用户自定义变量的其他用处

用户自定义变量能够做的有趣的事情：

查询运行时计算总数和平均值。
模拟GROUP语句中的函数FIRST()和LAST()。
对大量数据做一些数据计算。
编写一个样本处理函数，当样本中的数值超过某个边界值的时候将其变成0。
模拟读/写游标。

6.9　总结

理解查询是如何被执行的以及时间都消耗在哪些地方，这依然是前面我们介绍的响应时间的一部分。

优化通常都需要三管齐下：不做、少做、快速地做。

除了这些基础的手段，包括查询、表结构、索引等，MySQL还有一些高级的特性可以帮助你优化应用，例如分区，分区和索引有些类似但是原理不同。

MySQL还支持查询缓存，它可以帮你缓存查询结果，当完全相同的查询再次执行时，直接使用缓存结果。

7 Mysql的高级特性

7.1 分区表

分区表本身是独立的逻辑表，底层是由多个物理子表组成，索引也是按照分区的子表定义的，而没有全局索引，实现分区的代码是通过底层表的句柄对象(Handler Object)的封装。
大致流程对于分区表的请求，都会通过句柄对象转化成存储引擎接口的调用，所以分区对SQL是封闭的，对应用是透明的。

MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，
可以用到分区的场景：

表非常大无法全部发放在内存中或者在表的最后部分是热点数据，其他都是历史数据。
要批量删除大量数据可以用清除整个分区的方式（分区表更容易维护）
可以备份和恢复独立的分区

分区的限制：
1 一个表最多只能有1024个分区
2 分区表达式必须是整数
3 字段中有主键和唯一索引的列，必须包含进去

7.1.1 分区表的原理

如前所述，分区表由多个相关的底层表实现，这些底层表也是由句柄对象（Handler
object)表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和
管理普通表一样（所有的底层表都必须使用相同的存储引擎）。
分区表上的操作逻辑 select insert delete update 操作

SELECT查询：当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

INSERT操作：当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。

DELETE操作：当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。

UPDATE操作：当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

7.1.2　分区表的类型

MySQL支持多种分区表。我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。
例如，下表就可以将每一年的销售额存放在不同的分区里：

CREATE TABLE sales ( 
    order_date DATETIME NOT NULL, 
    -- Other columns omitted 
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) ( 
    PARTITION p_2010 VALUES LESS THAN (2010), 
    PARTITION p_2011 VALUES LESS THAN (2011), 
    PARTITION p_2012 VALUES LESS THAN (2012), 
    PARTITION p_catchall VALUES LESS THAN MAXVALUE );

partition分区子句中可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个常数。

7.1.3　如何使用分区表

假设我们希望从一个非常大的表中查询出一段时间的记录，而这个表中包含了很多年的历史数据，数据是按照时间排序的，比如：我们希望查询最近几个月的数据，大约有5亿条记录，硬件设备方面都是平常的那种，原表的数据中有5TB的数据，这个数据超过了内存。这种大数据量的情况下肯定不鞥走扫描全表了，维护成本大高，同时也不能大依赖索引，因为会产生大量的索引碎片，最终走一次查询会产生千万条的随机I/O，程序会直接崩掉，所以为了保证大数据量的可扩展性，一般有下面两个策略：

全量扫描数据，不要任何索引：可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。

索引数据，并分离热点：如果数据有明显的“热点”,而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。

7.1.4　什么情况下会出问题

上面的两个分区策略都基于两个非常重要的假设：查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代价。这两个假设在某些场景下会有问题，介绍一些可能会遇到的问题：
NULL值会使分区过滤无效： 有两种策略可以解决：
1、对null使用其他符号来代替，
2 创建第一个分区，如果插入的数据都是有效的，那么第一个分区就是空的检测的成本很低（Mysql5.5版本已弃用）
分区列和索引列不匹配：
选择分区的成本可能很高：
打开并锁住所有底层表的成本可能很高：
维护分区的成本可能很高：

7.1.5　查询优化

分区最大的优点就是优化器可以根据分区函数来过滤一些分区。可以使用EXPLAIN PARTITION来观察优化器是否执行了分区过滤。
例如，执行以下查询语句：

EXPLAIN  PARTITIONS
SELECT * FROM mytable WHERE date = '2021-01-01';

将会显示类似以下的结果：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: p20210101,p20210102,p20210103    // 访问的分区
         type: const
possible_keys: date_idx
          key: date_idx
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using where; Using MRR

其中，partitions列显示了查询语句访问的分区列表，type列为const表示使用了索引查询，在key列显示了使用的索引名称，filtered列显示了执行分区过滤后查询结果的行数占总行数的百分比。如果分区过滤得当，该值将为100%，否则可能会较低。

7.1.6　合并表（在Mysql5.0版本之后被删除了）

合并表（Merge table）是一种早期的、简单的分区实现，和分区表相比有一些不同的限制，并且缺乏优化。分区表严格来说是一个逻辑上的概念，用户无法访问底层的各个分区，对用户来说分区是透明的。但是合并表允许用户单独访问各个子表。

7.2　视图

视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是MySQL从其他表中生成的。
MySQL可以使用两种办法中的任何一种来处理视图：
1、逐层查询(临时表算法（TEMPTABLE）)：这种方法通过在视图上进行查询，然后再使用该查询结果创建另一个视图来处理视图。

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

2、内嵌查询（合并算法（MERGE 会尽可能地使用合并算法））： 这种方法使用 SELECT 语句中的子查询来处理视图，这个子查询可以在 FROM，JOIN 和 WHERE 子句中使用。

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM (
   SELECT column1, column2, …
   FROM table_name
   WHERE condition
) AS view_table;

如果是采用临时表算法实现的视图，EXPLAIN中会显示为派生表（DERIVED）
在这里插入图片描述

7.2.1　可更新视图

可更新视图（updatable view）是指可以通过更新这个视图来增删改视图涉及的相关表。
更新视图限制：
1、视图定义中包含了GROUP BY、UNION.、聚合函数，以及其他一些特殊情况，就不能被更新了。
2、更新视图的查询也可以是一个关联语句，但是有一个限制，被更新的列必须来自同一个表中。
3、使用临时表算法实现的视图都无法被更新。

7.2.2　视图对性能的影响

合理利用视图可提高性能：使用视图实现基于列的权限控制，却不需要真正的系统中创建权限，因此没有额外的开销。

使用临时表算法实现的视图，在某些时候性能会很糟糕（虽然可能比直接使用等效查询
语句要好一点）。MySQL以递归的方式执行这类视图，先会执行外层查询，即使外层查
询优化器将其优化得很好，但是MySQL优化器可能无法像其他的数据库那样做更多的
内外结合的优化。外层查询的WHERE条件无法“下推”到构建视图的临时表的查询中，
临时表也无法建立索引

7.2.3 视图的限制

MySQL还不支持物化视图（物化视图是指将视图结果数据存放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中）。但可以使用构建缓存表或者汇总表的办法来模拟物化视图和索引。
如果打算重新修改一个视图，并且没法找到视图的原始的创建语句的话，可以通过使用视图的.frm文件的最后一行获得一些信息。如果有FILE权限，甚至可以直接使用SQL语句中的L0AD_FILE() 来读取.frm中的视图创建信息。再加上一些字符处理工作，就可以获得一个完整的视图创建语句了，

7.3　外键约束

1、同步更新则可使用外键

2、数值约束，可使用触发器

3、只是简单约束，应用程序内约束即可

7.4　在MySQL内部存储代码

MySQL允许通过触发器、存储过程、函数、事件的形式来存储代码。
这四种存储代码都使用特殊的SQL语句扩展，它包含了很多过程处理语法，例如循环和条件分支等，存储过程和存储函数都可以接收参数然后返回值，但是触发器和事件却不行。
在MySQL内部存储代码最常用的可能是SQL语言和存储过程。
例：以下是一个简单的存储过程的例子，其目的是统计一个表中某个字段的平均值：

DELIMITER $$
CREATE PROCEDURE calculate_avg_value()
BEGIN
  SELECT AVG(column_name) FROM table_name;
END $$
DELIMITER ;

该存储过程的名称是calculate_avg_value，它没有传入任何参数，通过SELECT AVG(column_name)语句计算了table_name表中column_name字段的平均值。最后，用DELIMITER语句将分隔符设置为$$，并用END语句结束存储过程代码块

存储代码的优点：
1、它在服务器内部执行，离数据最近，节省带宽和网络延迟。
2、它可以简化代码的维护和版本更新。

存储代码的缺点：
1、存储代码效率方面比其他的程序代码要差点
2、MySQL并没有什么选项可以控制存储程序的资源消耗

7.4.1 存储过程和函数

一般在数据量较大、访问频繁、需求复杂的应用场景才会用到存储过程和存储函数
下面是一个简单的存储过程的例子：

CREATE PROCEDURE get_customer_orders(IN cust_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = cust_id;
-- 调用存储过程
call get_customer_orders(1);
END;

上面的存储过程接受一个参数cust_id，然后根据该参数从orders表中查询该客户的订单。

下面是一个简单的存储函数的例子：

CREATE FUNCTION get_customer_order_total(IN cust_id INT)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(price) INTO total FROM orders WHERE customer_id = cust_id;
    RETURN total;
    -- 调用存储函数
SELECT get_customer_order_total(1);
END;

上面的存储函数接受一个参数cust_id，然后根据该参数从orders表中查询该客户的订单总价，并返回结果。在这个例子中，我们还使用了一个DECLARE语句来声明一个变量total，用于保存订单总价。

7.4.2　触发器

触发器可以让你在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作。可以在MySQL中指定是在SQL语句执行前触发还是在执行后触发。
触发器的限制：
1、对每一个表的每一个事件，最多只能定义一个触发器。
2、Mysql只能基于行触发。
如果仅考虑性能，那么MySQL触发器的实现中对服务器限制最大的就是它的“基于行
的触发”设计。因为性能的原因，很多时候无法使用触发器来维护汇总和缓存表。使用
触发器而不是批量更新的一个重要原因就是，使用触发器可以保证数据总是一致的。

CREATE TRIGGER `myInsert` AFTER INSERT ON `teacher` FOR EACH ROW BEGIN
update student SET name = 'triggerName' WHERE	id = 1;
END;

当新行插入teacher表时，触发器将更新student表中id为1的记录的name字段的内容。这个触发器在每次插入新的数据时都会触发，并且只会影响student表中id为1的记录

7.4.3　事件

Mysql中的事件类似于Linux的定时任务。我们可以通过创建事件，指定Mysql在某个时段执行Sql代码。用到的场景包含定时备份数据库、清理无用数据。

CREATE EVENT eventDemo ON SCHEDULE EVERY 1 WEEK DO CALL getInfo(1);

这条SQL语句创建了一个名为eventDemo的定时任务，每周执行一次，执行的操作是调用一个名为getInfo的存储过程，传入参数1。具体解释如下：

CREATE EVENT：创建一个事件。
eventDemo：事件的名称。
ON SCHEDULE EVERY 1 WEEK：定时任务每周执行一次。
DO CALL：执行的操作为调用一个存储过程。
getInfo(1)：调用名为getInfo的存储过程，并传递参数1。

总的来说，这条语句用于创建一个定时任务，以便在每周指定的时间点自动执行指定的存储过程以完成特定的操作。

7.5　游标

游标（Cursor）是一种用于对结果集进行逐行处理的机制。在MySQL中，通过游标，可以在存储过程或函数中逐行处理结果集，并对每一行进行特定的操作。

游标的基本操作包括：声明游标、打开游标、获取游标中的数据、关闭游标、释放游标等。
例，用于遍历一个名为 employees 的表中的数据，并将每一行的 employee_id 和 employee_name 列的值输出：

CREATE PROCEDURE myProcedure()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE employeeId INT;
    DECLARE employeeName VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT employee_id, employee_name FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO employeeId, employeeName;
    
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Do something with employeeId and employeeName
        SELECT CONCAT('Employee ID: ', employeeId, ', Employee Name: ', employeeName);

    END LOOP;

    CLOSE cur;
END;

在上述示例中，首先通过 DECLARE cur CURSOR FOR SELECT employee_id, employee_name FROM employees; 声明了名为 cur 的游标，该游标用于遍历 employees 表中的数据。然后通过 OPEN cur; 打开游标，进入一个名为 read_loop 的循环中，通过 FETCH cur INTO employeeId, employeeName; 获取游标中的下一行数据并存储到 employeeId 和 employeeName 变量中，然后可以对这些变量进行特定操作。最后通过 CLOSE cur; 关闭游标并释放其内存空间。

需要注意的是，使用游标会增加数据库系统的负担，因此应该尽量避免在大型的数据集上使用游标，以免影响系统性能。

7.6　绑定变量

当创建一个绑定变量SQL时，客户端向服务器发送了一个SQL语句的原型。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄。

绑定变量的SQL，使用问号标记可以接收参数的位置，当真正需要执行具体查询的时候，则使用具体值代替这些问号。

例如，假设有一个Web应用程序允许用户根据年龄范围查询某个城市的人口数量。程序通过接收用户输入的 min_age 和 max_age 参数，并将它们绑定到一个SQL查询语句中，如下所示

SELECT COUNT(*) FROM population
WHERE city = 'New York'
AND age >= :min_age
AND age <= :max_age;

在上述查询中，:min_age 和 :max_age 是绑定变量，它们的值会在查询执行时从程序中的变量中动态地获取。

使用绑定变量的好处是可以避免SQL注入攻击，并且可以提高查询的性能，因为查询计划可以在查询第一次执行时就被缓存下来。

绑定变量的限制包括：

1、绑定变量只能在查询中使用。

2、绑定变量是会话级别的，所以连接之间不能共用绑定变量句柄

3、绑定变量的名称必须以冒号开头。

7.7　用户自定义函数

用户自定义函数是指用户自己定义的函数，可以在SQL查询中使用。用来提高查询效率。

例子：

假设我们需要查询一个订单的总价，可以定义一个用户自定义函数来计算订单的总价：

CREATE FUNCTION calculate_total_price(order_id INT) RETURNS DECIMAL
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(price * quantity) INTO total FROM order_items WHERE order_id = order_id;
    RETURN total;
END;

在上面的例子中，我们定义了一个名为calculate_total_price的函数。这个函数接受一个订单号作为参数，并返回一个DECIMAL类型的值，表示该订单的总价。

使用这个函数可以很方便地查询订单的总价，例如：

SELECT order_id, calculate_total_price(order_id) AS total_price FROM orders;

这将返回所有订单的订单号和总价。

优点：

提高查询效率：用户自定义函数可以优化查询过程，提高查询效率，并减少数据冗余。
提高代码复用性：用户自定义函数可以在多个查询中重复使用，提高代码复用性。
扩展SQL功能：用户自定义函数可以扩展SQL的功能，使得它能够处理更加复杂的查询问题。

缺点：

复杂度增加：用户自定义函数的编写需要一定的编程知识和技能，对于普通用户来说可能比较困难。
可移植性问题：不同数据库管理系统对用户自定义函数的支持程度可能不同，这可能导致可移植性问题。
安全问题：用户自定义函数可能会受到SQL注入攻击等安全问题的影响，需要特别注意安全问题。

总之，用户自定义函数是一种非常有用的SQL扩展方式，可以在一定程度上提高查询效率，但需要注意安全问题和可移植性问题，并且需要投入一定的开发精力来编写。

7 .8　插件

MySQL还支持各种各样的插件。这些插件可以在MySQL中新增启动选项和状态值，还可以新增INFORMATION_SCHEMA表，或者在MySQL的后台执行任务，等等。简单的插件列表：

存储过程插件： 存储过程插件可以帮你在存储过程运行后再处理一次运行结果。这
后台插件： 后台插件可以让你的程序在MySQL中运行，可以实现自己的网络监听、执行自己的定期任务。
INFORMATION_SCHEMA插件： 提供新的内存INFORMATION_SCHEMA表。
全文解析插件：这个插件提供一种处理文本的功能，可以根据自己的需求来对一个文档进行分词，所以如果给定一个PDF文档目录，可以使用这个插件对这个文档进行分词处理。也
审计插件： 审计插件在查询执行的过程中的某些固定点被调用，所以它可以用作（例如）记录MySQL的事件日志。
认证插件： 认证插件既可以在MySQL客户端也可在它的服务器端，可以使用这类插件来扩展MySQL的认证功能，例如可以实现PAM和LDAP认证。

7.9　字符集和校对

字符集是指一种从二进制编码到某类字符符号的映射，校对”是指一组用于某个字符集的排序规则，

每种字符集都可能有多种校对规则，并且都有一个默认的校对规则。每个校对规则都是针对某个特定的字符集的，和其他的字符集没有关系。校对规则和字符集总是一起使用的，所以后面我们将这样的组合也统称为一个字符集。

MySQL的设置可以分为两类：创建对象时的默认值、在服务器和客户端通信时的设置。

7.10 全文索引

MySQL提供了全文搜索的功能，使用全文索引可以快速地在大型数据集中进行复杂的文本搜索，全文索引跟普通的索引不同，普通的索引只支持单个值的搜索，而全文索引可以支持多个单词或短语的搜索，需要使用FULLTEXT关键字来定义一个全文索引，然后在where条件中使用MATCH AGAINST语句来进行搜索。

假设我们有一个包含文章内容的表article，我们可以为该表的content列创建全文索引，然后使用MATCH AGAINST语句进行搜索。

首先，我们需要在content列上创建全文索引：

ALTER TABLE article ADD FULLTEXT(content);

然后，我们可以使用MATCH AGAINST语句进行搜索，如：

SELECT * FROM article WHERE MATCH(content) AGAINST('MySQL full-text search');

以上语句将会搜索包含MySQL full-text search这个短语的文章，并将搜索结果返回。

需要注意的是，MATCH AGAINST语句中的搜索条件可以是一个或多个完整的单词（不需要使用通配符），也可以是一个或多个短语（需要使用双引号包围）。此外，还可以使用特殊字符进行高级搜索，如+、-、*等。

7.10.1 布尔全文索引

布尔全文索引它支持使用布尔运算符进行搜索，例如AND、OR、NOT等。与普通的全文索引不同，布尔全文索引可以对多个关键词进行精确匹配。

SELECT * FROM article WHERE MATCH(content) AGAINST('+MySQL -full-text' IN BOOLEAN MODE);

以上语句将返回包含MySQL关键词但不包含full-text关键词的文章

布尔全文索引的优点在于可以进行更准确的搜索，可以通过使用布尔运算符精确匹配多个关键词，但是，它的缺点在进行布尔全文索引搜索时，需要特别注意关键词之间的逻辑关系。

7.11　分布式（XA）事务

分布式事务是指跨越多个节点或者多个数据库的一种事务处理机制。在分布式环境下，不同的节点或者数据库之间需要进行协调和通信，以确保事务的一致性和完整性。X/Open公司制定了XA事务规范，它是分布式事务协议的一种标准，支持在分布式环境下实现原子性、一致性、隔离性和持久性等ACID属性。

XA事务是通过两阶段提交（2PC）实现的。在2PC协议中，事务被分为两个阶段：准备阶段和提交阶段。
在准备阶段，协调者节点（也称为事务管理器）会向参与者节点（也称为资源管理器）发送prepare请求，参与者节点会把事务的执行结果写入一个“prepare（装备） log”，并返回一个prepared消息。在所有参与者节点都返回prepared消息后，协调者节点会发送commit请求。

在提交阶段中，参与者节点会将prepare log中的执行结果提交并删除，然后返回commit消息。当所有参与者节点都返回commit消息后，协调者节点才能向客户端返回commit消息，否则会向所有参与者节点发送rollback请求，回滚事务操作。

分布式（XA）事务适用于需要在多个事务性数据源之间进行操作的场景。比如
1、分布式系统中的事务处理
2、金融、电商、物流等领域的业务处理
3、多系统集成：在多个系统集成的场景下

7.12 查询缓存

查询缓存指的是查询完整的select查询结果，MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，MySQL会立刻返回结果，跳过了解析、优化和执行阶段。

7.12.1 MySQL如何判断缓存命中

MySQL判断缓存命中的方法很简单：缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包括了如下因素，即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能会影响返回结果的信息。但有个缺陷如果查询语句中包含一些不确定的结果，例如包含 NOW()或者 data()的查询不会被缓存，事实上，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysq1库中的系统表，或者任何包含列级别权限的表，都不会被缓存。

7.12.2　查询缓存如何使用内存

查询缓存是完全存储在内存中的。

除了查询结果之外，需要缓存的还有维护相关的数据，这些管理维护数据结构大概需要40KB的内存资源。

除此之外，MySQL用于查询缓存的内存被分成一个个的数据块，数据块是变长的。每一个数据块中，存储了自己的类型、大小和存储的数据本身，还外加指向前一个和后一个数据块的指针，当服务器启动的时候，它先初始化查询缓存需要的内存。这个内存池初始是一个完整的空闲块。这个空闲块的大小就是你所配置的查询缓存大小再减去用于维护元数据的数据结构所消耗的空间。

当有查询结果需要缓存的时候，MySQL先从大的空间块中申请一个数据块用于存储结果。这个数据块需要大于参数query_cache_min_res_unit的配置，即使查询结果远远小于此，仍需要至少申请query_cache_min_res_unit空间。

因为需要先锁住空间块，然后找到合适大小数据块，所以相对来说，分配内存块是一个非常慢的操作。MySQL尽量避免这个操作的次数。当需要缓存一个查询结果的时候，它先选择一个尽可能小的内存块，然后将结果存入其中。如果数据块全部用完，但仍有剩余数据需要存储，那么MySQL会申请一块新数据块继续存储结果数据。当查询完成时，如果申请的内存空间还有剩余，MySQL会将其释放，并放入空闲内存部分。
在这里插入图片描述

7.12.3　什么情况下查询缓存能发挥作用

只有当缓存带来的资源节约大于其本身的资源消耗时才会给系统带来性能提升。

7.12.4　如何配置和维护查询缓存

旦理解查询缓存工作的原理，配置起来就很容易了。只有很少的参数可供配置：

query_cache_type：是否打开查询缓存。可以设置成OFF、ON或DEMAND。
query_cache_size：查询缓存使用的总内存空间，单位是字节。这个值必须是1024的整数倍。
query_cache_min_res_unit：在查询缓存中分配内存块时的最小单位。
query_cache_limit：MySQL能够缓存的最大查询结果。如果查询结果大于这个值，则不会被缓存。
query_cache_wlock_invalidate：如果某个数据表被其他的连接锁住，是否仍然从查询缓存中返回结果。这个参数默认是OFF。

7.12.5　InnoDB和查询缓存

因为InnoDB有自己的MVCC机制，所以相比其他存储引擎，InnoDB和查询缓存的交互要更加复杂

事务是否可以访问查询缓存取决于当前事务ID，以及对应的数据表上是否有锁。每一个InnoDB表的内存数据字典都保存了一个事物ID号，如果当前事务ID小于该事务ID，则无法访问查询缓存。

原则上，在InnoDB的MVCC架构下，当某些修改不影响其他事务读取一致的数据时，是可以使用查询缓存的。InnoDB就做了一个简化，让所有有加锁操作的事务都不使用任何查询缓存。

但是，InnoDB 存储引擎和查询缓存之间存在着一些冲突。在使用 InnoDB 存储引擎时，由于它使用了行级锁和 MVCC 等机制来保证事务的隔离性和并发性能，查询缓存将无法使用。这是因为每个查询可能会访问不同的数据行，而查询缓存只能缓存完全相同的查询结果，所以InnoDB存储引擎不建议用查询缓存来提高性能

7.12.6　通用查询缓存优化

用多个小表代替一个大表对查询缓存有好处。
批量写入时只需要做一次缓存失效，所以相比单条写入效率更好
避免缓存不必要的查询结果。如果某些查询结果很少被使用，可以将其从缓存中删除，以便给更常用的查询结果腾出空间。
可以增加查询缓存的大小，以便缓存更多的查询结果。可以通过修改 query_cache_size 参数来设置缓存大小

7.13　总结

回顾一下MySQL高级特性。

分区表

分区表是一种粗粒度的、简易的索引策略，适用于大数据量的过滤场景。最适合的场景是，在没有合适的索引时，对其中几个分区进行全表扫描，或者是只有一个分区和索引是热点，而且这个分区和索引能够都在内存中；限制单表分区数不要超过150个，并且注意某些导致无法做分区过滤的细节，分区表对于单条记录的查询并没有什么优势，需要注意这类查询的性能。

视图

对好几个表的复杂查询，使用视图有时候会大大简化问题。当视图使用临时表时，无法将WHERE条件下推到各个具体的表，也不能使用任何索引，需要特别注意这类查询的性能。如果为了便利，使用视图是很合适的。

外键

外键限制会将约束放到MySQL中，这对于必须维护外键的场景，性能会更高。不过这也会带来额外的复杂性和额外的索引消耗，还会增加多表之间的交互，会导致系统中更多的锁和竞争。外键可以被看作是一个确保系统完整性的额外的特性，但是如果设计的是一个高性能的系统，那么外键就显得很臃肿了。很多人在更在意系统的性能的时候都不会使用外键，而是通过应用程序来维护。抱歉，您没有提供第七章的主题或内容，无法为您提供总结。请提供更多信息。抱歉，您没有提供第七章的主题或内容，无法为您提供总结。请提供更多信息。

存储过程

MySQL本身实现了存储过程、触发器、存储函数和事件，老实说，这些特性并没什么特别的。而且对于基于语句的复制还有很多问题。通常，使用这些特性可以帮你节省很多的网络开销——很多情况下，减少网络开销可以大大提升系统的性能。在某些经典的场景下你可以使用这些特性（例如中心化业务逻辑、绕过权限系统，等等），但需要注意在MySQL中，这些特性并没有别的数据库系统那么成熟和全面。

绑定变量

当查询语句的解析和执行计划生成消耗了主要的时间，那么绑定变量可以在一定程度上解决问题。因为只需要解析一次，对于大量重复类型的查询语句，性能会有很大的提高。另外，执行计划的缓存和传输使用的二进制协议，这都使得绑定变量的方式比普通SQL语句执行的方式要更快。

插件

使用C或者C++编写的插件可以让你最大程度地扩展MySQL功能。插件功能非常强大，我们已经编写了很多UDF和插件，在MySQL中解决了很多问题。

字符集

字符集是一种字节到字符之间的映射，而校对规则是指一个字符集的排序方法。很多人都使用Latin1（默认字符集，对英语和某些欧洲语言有效）或者UTF-8。如果使用的是UTF-8，那么在使用临时表和缓冲区的时候需要注意：MySQL会按照每个字符三个字节的最大占用空间来分配存储空间，这可能消耗更多的内存或者磁盘空间。注意让字符集和MySQL字符集配置相符，否则可能会由于字符集转换让某些索引无法正常使用。

全文索引

在本书编写的时候只有MyISAM支持全文索引，不过据说从MySQL 5.6开始， InnoDB也将支持全文索引。MyISAM因为在锁粒度和崩溃恢复上的缺点，使得在大型全文索引场景中基本无法使用。这时，我们通常帮助客户构建和使用Sphinx来解决全文索引的问题。

XA事务

很少有人用MySQL的XA事务特性。除非真正明白参数innodb_support_xa的意义，否则不要修改这个参数的值，并不是只有显式使用XA事务时才需要设置这个参数。InnoDB和二进制日志也是需要使用XA事务来做协调的，从而确保在系统崩溃的时候，数据能够一致地恢复。

查询缓存

完全相同的查询在重复执行的时候，查询缓存可以立即返回结果，而无须在数据库中重新执行一次。根据我们的经验，在高并发压力环境中查询缓存会导致系统性能的下降，甚至僵死。如果一定要使用查询缓存，那么不要设置太大内存，而且只有在明确收益的时候才使用。那该如何判断是否应该使用查询缓存呢？建议使用Percona Server，观察更细致的日志，并做一些简单的计算。还可以查看缓存命中率（并不总是有用）、“INSERTS和SELECT比率”（这个参数也并不直观）、或者“命中和写入比率”（这个参考意义较大）。查询缓存是一个非常方便的缓存，对应用程序完全透明，无须任何额外的编码，但是，如果希望有更高的缓存效率，我们建议使用memcached或者其他类似的解决方案。

附录D Explain

EXPLAIN关键字用于分析MySQL执行查询的方式，并返回一个解释结果集。
调用EXPLAIN,只需要在查询中的 select 关键字之前增加EXPLAIN。

EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

执行以上语句后，MySQL会返回一个结果集，包含了查询执行计划的详细信息。该结果集中的每一行对应查询执行计划中的一步操作，你可以通过分析这些数据来确定查询的性能瓶颈以及优化的方向

id: 查询执行计划中每个操作的唯一标识，
select_type: 操作类型，如简单查询、联合查询等，
table: 操作涉及的表名，
type: 表访问的方式，如全表扫描、索引扫描等，
possible_keys: 可能使用的索引，
key: 实际使用的索引，
key_len: 实际使用的索引长度，
ref: 与索引比较的列或常量值，
rows: 预估的结果集行数，
Extra: 额外的信息，如排序、临时表等。
通过分析这些信息，你可以更好地理解查询的执行过程，并且采取相应的优化措施，例如创建合适的索引、优化查询语句等，以提高MySQL查询的性能。

EXPLAIN案例

EXPLAIN命令执行后，会输出查询执行计划的各个参数列。下面我们介绍每个参数列的作用，并结合一个示例来说明。

假设我们有一个包含两个表的数据库：students和scores。其中，students表包含学生个人信息，scores表包含学生每门课的成绩。现在需要查询某个学生的总分和平均分。

我们可以通过以下语句来完成查询：

EXPLAIN SELECT s.*, SUM(sc.score) AS total_score, AVG(sc.score) AS avg_score
FROM students s JOIN scores sc ON s.id = sc.student_id
WHERE s.name = 'Tom';

该查询使用了JOIN和WHERE操作，需要对多表进行关联和过滤。我们通过EXPLAIN命令来查看该查询的执行计划：

id  select_type table   partitions  type    possible_keys   key     key_len ref             rows    filtered    Extra
1   SIMPLE      s       NULL        ref     PRIMARY         PRIMARY 4       const           1       100.00      Using index
1   SIMPLE      sc      NULL        ref     student_id_idx  student_id_idx 4   exam.s.id       1       100.00      Using index condition

下面我们具体介绍每个参数列的作用：

id：每个子查询或分段查询都有一个唯一的标识符，可以根据此标识符区分不同的查询。
select_type：表示查询的类型，它的值包含以下几种：
- SIMPLE：简单查询，不包含子查询或者UNION查询等。
- PRIMARY：最外层的查询，包含多个子查询或者UNION查询等。
- UNION：UNION查询。
- DEPENDENT UNION：UNION查询，依赖于外部查询中的值。
- SUBQUERY：子查询。
- DEPENDENT SUBQUERY：依赖于外部查询的子查询。
- DERIVED：派生表，包含子查询和UNION查询等。
table：表示查询使用的数据库表。
partitions：表示查询使用的分区。
type：表示连接类型，它的值包含以下几种：
- NULL MySQL不访问任何表，索引，直接返回结果
- system：表只有一行。
- const：通过索引或者主键定位到唯一一条记录。
- eq_ref：使用唯一索引或者主键的 JOIN 查询，对于每个匹配到的行只有一条。
- ref：使用普通索引的JOIN查询。
- range：使用索引范围检索数据。
- index：使用索引进行全表扫描。
- all：进行全表扫描。
- 结果值从最好到最坏以此是：null > system > const > eq_ref > ref > range > index > ALL
- 一般来说，我们需要保证查询至少达到 range 级别，最好达到ref 。
possible_keys：表示所有可能使用的索引，可用于优化查询性能。
key：表示实际使用的索引。
key_len：表示索引的长度。
ref：表示使用索引的哪个列或者常量值来与表中的数据进行匹配。
rows：表示需要扫描的行数，也就是MySQL估算出来的大致扫描行数。
filtered：表示结果集的过滤比例（百分比）。
Extra：表示额外的信息，比如
- Using index：该查询使用了覆盖索引。
- Using temporary：该查询需要创建临时表以完成查询，通常出现在ORDER BY和GROUP BY查询中。
- Using filesort：该查询需要对结果集进行文件排序。
- Using where：该查询使用了WHERE条件过滤数据。
- Using join buffer：该查询使用了连接缓存。
- Using index condition：该查询使用了索引条件过滤数据。
- Impossible where：该查询的WHERE条件不可能被满足，例如WHERE 1=0。
- Select tables optimized away：该查询的结果可以直接从索引中获取，无需访问表。
  通过上面的查询结果，我们可以分析出以下信息：
查询中使用了两个表：students和scores。
查询使用了索引加速查询，其中students表使用了PRIMARY索引，scores表使用了student_id_idx索引。
查询使用了连接缓存，可以有效提高查询性能。
查询的结果集非常小，使用了索引加速和连接缓存等优化技术，执行效率和性能比较高。

总之，通过EXPLAIN命令，我们可以了解查询的执行计划和优化方案，以便更好地优化查询性能和效率。

EXPLAIN的两个变种

分别是EXPLAIN EXTENOED 和EXPLAIN PRATITION 前者的作用可以让我们知道查询优化器是如何转换语句的，后者会显示将访问的分区

假设有一个包含百万条记录的user表，现在我们想查询age在25岁以下的用户，且按照id降序排序。我们可以使用以下SQL语句进行查询：

EXPLAIN EXTENDED 
SELECT * FROM user 
WHERE age < 25 
ORDER BY id DESC;

执行完以上语句后，我们可以使用SHOW WARNINGS命令查看查询执行计划信息。输出的结果类似下面这样：

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------+
| 1  | SIMPLE      | user  | NULL       | range | age           | age  | 4       | NULL | 5000 | 50.00    | Using where; Backward index scan; Descending |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------+

从输出结果可以看出，查询使用了age列的索引，扫描了5000行记录。Using where说明查询使用了WHERE过滤条件，Backward index scan说明索引被反向扫描，Descending说明排序是降序的。这些信息可以帮助我们更好地理解查询的执行过程，发现潜在的性能瓶颈，并在必要时优化查询语句。

注：在Mysql5.7以前的版本中，想要显示 partitions 需要使用 explain partitions 命令；想要显示filtered 需要使用 explain extended 命令。在5.7版本后，默认explain直接显示partitions和 filtered中的信息。