MySQL读书笔记（高性能MySQL），后续更新

1.MySQL架构

１.１MySQL逻辑架构

MySQL逻辑架构图

• 最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。

• 第二层为核心层，包含查询解析，分析，优化，缓存以及所有的内置函数，所有跨存储引擎的功能都在这实现。

• 第三层包含存储引擎，存储引擎负责MySQL中数据的存储与提取。服务器通过API与存储和引擎进行通信。这些接口屏蔽了不同存储引擎间的差距，使得这些差异对上层的查询透明。存储引擎API包含十几个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但存储引擎不会去解析SQL（InnoDB例外，它会解析外键定义，因为MySQL服务器本身没实现该功能），不同存储引擎间也不会相互通信，只是简单的响应上层服务器的请求。

１.１.１连接管理与安全性

• 每个客户端拥有一个线程，一个连接的查询只会在这个单独得线程中操作。服务器会缓存线程（MySQL５.５及以上提供了API，支持线程池插件），不需要为每个连接都新建线程。

１.１.２优化与执行

• MySQL会解析查询，并创建内部数据结构（解析树），对其进行优化，包括重写查询，读取表顺序，选择合适索引等。用户可通过hint提示优化器，影响它的决策过程。

优化器不关心表使用的存储引擎，但存储引擎对优化查询有影响。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。

对于select语句，在解析查寻前，服务器会先检查查询缓存，如果找得到对应查询，会直接返回查询缓存中的结果集。

１.２并发控制

两个进程对数据库中的同一个数据进行操作（增删改）

１.２.１读写锁

虽然读取数据不会导致数据的改变，但是多个线程时，防止其他线程修改操作导致读取不准确，所以读取也要加锁。

解决方法：加锁系统，共享锁（读锁），排他锁（写锁）；大多数情况锁的内部管理都是透明的

１.２.２锁粒度

尽量只锁定需要修改的部分；更理想的方式是只对会修改的数据片进行加锁。任何时候，锁定的数据量越少，并发程度越高，在没有冲突的前提下。

但是锁也要消耗资源，获得锁，检查锁解除，释放锁都会消耗系统开销。

所谓所策略，就是在系统安全和锁开销之间寻求平衡，这种平衡当然会影响性能。

大多数据库在表上施加行级锁（为提供尽量好的性能，实现复杂）

MySQL有多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度；锁管理在存储引擎设计中非常重要，锁粒度的固定将影响不同场景的性能以及对特定应用场景的支持。好在MySQL支持多个存储引擎架构，不需要单一的通用解决方案；下为重要的两种锁策略。

• 表锁

对整张表进行加锁，用户的所有操作都要获得锁；写锁比读锁优先级高

• 行级锁

表中每条数据加锁，并发程度最高；在存储引擎中实现，服务器层没有实现

1.3事务

概念：一组原子性的SQL查询；事务内的查询要么全部成功，有一条失败则全部失败；

ACID：

• 原子性（atomicity）执行事务中的全部SQL，不能部分
• 一致性（consistency）从一个一致的状态转移到另一个一致状态A减少B增加（当A减少发生失败，A回退，B保持不变）
• 隔离性（isolation）事务所做的修改在提交之前其他事务不可见
• 持久性（durability）事务一旦提交，所做的修改就会永久保存在数据库

1.3.1隔离级别

• 未提交读（脏读）

事务的修改操作在未提交时其他事务也可见，性能比其他级别不高，好处也几乎没有，实际应用很少。

• 提交读

大多数数据库系统的默认隔离级别都是提交读（MySQL不是），一个事务的操作在提交前其他事务不可见。有时也叫不可重复读，以为执行两次相同查询可能结果不一样。

• 可重复读

可重复读解决了主从复制时同步binlog不一致问题，可重复读解决了脏读的问题，但无法解决幻读（Phantom Read）问题。所谓幻读指在读取某个范围内数据时，另一个事务又在该范围内插入了新记录，当前事务再次读取该范围内数据时，会产生幻行（Phantom Row）。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC）解决了该问题。可重复读时MySQL的默认隔离级别。

• 可串行化（Serializable）

最高隔离级别，强制事务串行，即对读取的每一行数据都加锁，性能很低，极端情况使用。

1.3.2死锁

• ​ 概念：两个或多个事务相互请求锁定对方所占用的资源，导致恶性循环

  事务1

UPDATE StockPrice SET close = 45.50 WHERE stocked = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19-80 WHERE stocked = 3 and date = '2002-05-02';
COMMIT;

​ 事务2

UPDATE StockPrice SET high = 20.12 WHERE stocked = 3 and date = '2OO2-O5-O2';
UPDATE StockPrice SET high = 47.20 WHERE stocked = 4 and date = '2OO2-O5-O1';
COMMIT;

​ 两个事务执行了第一条命令，尝试加锁下一条指令，发现被对方锁定，就都等待对方释放，陷入死循环。

• 解决方案：InnoDB存储引擎，检测到循环依赖立即返回错误；但查询时间达到锁等待超时的设定后放弃锁请求（这种做法不好）。InnoDB中处理死锁的方法是将持有最少排它锁的事务回滚。

​ 锁的行为是和存储引擎相关的，死锁产生有双重原因，一个是事务本身对数据操作冲突，一个是存储引擎锁策略实现的方式导致。

1.3.3事务

• 日志可提高事务效率。

• 修改表时只修改内存拷贝，再把修改的行为记录到持久在硬盘的事务日志中，而不用每次都将修改的数据持久化到磁盘。

• 日志采用追加的方式，且写日志的操作是磁盘一小块区域的顺序IO，比随机IO要快的多。

• 事务日志持久后，内存中被修改的数据慢慢刷回磁盘（通常称为预写式日志，修改数据需要两次写磁盘）

• 如果数据的修改已记录到日志，但数据未写到磁盘，系统崩溃，存储引擎能在重启时恢复这部分数据

1.3.4MySQL中的事务

​ MySQL提供了两种事务型存储引擎：InnoDB和NDB Cluster。另外还有第三方存储引擎也支持事务（XtraDB和PBXT等）

• 自动提交

  1》MySQL默认采用自动提交模式，如果不显示开始一个事务，每个SQL都会被当做一个事务进行提交。在当前连接中可以通过autocommit变量来启用（1）或禁用（0）自动提交。对于非事务型的表，autocommit不会有影响。

  2》数据定义（如alter，table）和一些语句（如lock tables）会强制commit。

  3》MySQL可通过set transaction Isolation level （read commit）手动设置隔离级别，新的隔离级别在下个事务开始生效。

• 在事务中混合使用存储引擎

  1. MySQL服务器层不管事务，由存储引擎管；所以在同一事物中使用多个引擎是不可靠的
  2. 混合使用事务型与非事务型的表在回滚操作中会出现问题，非事务型无法回滚，可能造成不一致问题
  3. 大多情况下对非事务型表的操作不会有提示

• 隐式和显式锁定

  1. InnoDB为两段锁协议；
     隐式锁定：事务执行过程中随时都可以锁定，锁只有在提交或回滚时才会释放
     显式锁定：

     select ... lock in share mode
     select ... for update
     

  2. MySQL也支持lock tables 和 unlock tables语句，这是在服务器层实现，与存储引擎无关。但这不能代替事务管理

1.4多版本并发控制

​ 概念：可以认为MVCC是行级锁的一个变种，但它在很多情况下都避免了加锁操作；非阻塞的读操作，写操作也只锁定必要的行。

​ 实现：通过保存数据在某个时间点的快照，使得无论执行多长时间，每个事物看到的数据都是一致的。

​ 分类：典型有乐观并发控制和悲观并发控制

​ InnoDB简化版的MVCC实现：

1. 在每行记录后保存两个隐藏的列，一个为行创建时间，一个为行过期时间（或删除时间），不过存储的并不是实际值而是系统版本号。

2. 每开始一个新事物，版本号会递增。事务开始时的版本号作为事务的版本号，用来和查询到的每行事务的版本号比较

3. 在repeatable read 隔离级别下MVCC具体的操作
   select：只查找行版本号小于或等于事务版本号的记录（确保读取的数据是已存在或已修改过）；行的删除版本号要么未定义要么大于当前事务版本号（确保读取的数据不是已被其他事务删除的）
   insert：保存当前版本号为行版本号
   delete：当前版本号为删除标识
   update（删除，后插入）：当前版本号为插入的行版本号，为删除的行标识

   MVCC只在repeatable read和read commit两个级别下工作。其他两个级别和MVCC不兼容。

1.5MySQL存储引擎

​ 文件系统中，MySQL将数据库（schema）保存为数据目录的一个子目录。创建表时MySQL会在数据库子目录下创建一个与表同名的.frm文件保存表定义。不同存储引擎保存数据和索引的方式是不同的，但表的定义在服务层统一处理。

1.5.1InnoDB存储引擎

​ MySQL默认存储引擎，它被设计用来处理大量短期事务（正常提交，很少回滚的事务）。InnoDB的性能崩溃自动恢复特性使得其很流行

• ​ InnoDB概览
  ​ 1.数据存储在表空间中（InnoDB管理的一个黑盒子）它由由一系列数据文件组成。
  ​ 2.MVCC实现高并发，并实现四个隔离级别（默认级别为可重复读），并通过间隙锁防止幻读出现。间隙锁还会对索引中间进行锁定，防止幻行插入。
  ​ 3.基于聚簇索引（对主键查询有很高的性能，不过它的二级索引必须包含主键列，若主键列很大，其他所有索引都会很大。因此若索引较多，主键应尽可能小）建立。InnoDB平台独立。
  ​ 4.内部优化，可预测性预读（加速磁盘读取数据），自适应哈希索引（加速读操作），插入缓冲区（加速插入操作）等优化方式
  ​ 5.支持热备份

1.5.2MyISAM存储引擎

​ MySQL5.1之前的版本，MyISAM作为默认存储引擎，提供了大量特性，包括全文索引，压缩，空间函数等，但MyISAM不支持事务及行级锁，而且崩溃无法恢复，对于只读的数据，或者表较小，可以忍受恢复操作，可以使用MyISAM。

存储

​ MyISAM会将表存储在两个文件中，数据文件和索引文件，分别以.MYD和.MYI为扩展名。
​ MyISAM表可以包含动态和静态行（具体根据表的定义）。可以存储的记录行数受限于磁盘空间

MyISAM特性

​ 1》加锁与并发：对整张表加锁
​ 2》修复：MyISAM表可手动或自动执行检查和修复操作（这里的修复和事务恢复以及崩溃恢复时不一样的），执行修复可能造成数据丢失，且修复操作很慢。
​ SQL操作：

check table mytable
repair table mytable

​ 3》索引特性：对于blob及text长字段，可基于前500个字符创建索引。支持全文索引，支持复杂查询。
​ 4》延迟更新索引键：创建MyISAM表时，若指定delay_key_write选项，在每次修改完成时，不会立即写入磁盘，而会写到内存中的键缓冲区，只有在关闭缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。但遇到崩溃需要修复操作。

MyISAM性能

MyISAM设计简单，数据以紧密格式存储，在某些场景下性能很好。但表锁是其典型性能问题。

5.创建高性能的索引

• 索引（在MySQL中也叫键）是存储引擎用于快速查询记录的一种数据结构。
• 索引对良好的性能非常关键，尤其是对于大量数据的表
• 索引优化一般为查询性能优化的最好手段

5.1索引基础

​ 存储引擎先在索引中找到值，然后返回所有包含该值得数据行

​ 索引可以包含一个或多个列值。若包含多列，列的顺序也十分重要，因为MySQL只能高效的使用最左前缀列

5.1.1索引类型

​ 索引在存储引擎中实现，所以没有统一标准，不同的存储引擎的索引工作方式可能不一样，同种索引底层实现也有可能不同。

1. B-Tree索引
B-Tree（很多存储引擎使用的是B+Tree）数据结构来存储数据。
根节点中存放的是指向子页节点的指针，其定义了子页节点中值得上限的下限。叶子节点指针指向的是被索引的数据。
树的深度和表的大小直接相关
B树对索引列是顺序组织存储的

CREATE TABLE People (
last_name varchar(so) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m','f')not null,
key(last_name, first^name, dob)
);

索引对值得排序根据建表时定义索引的列的顺序

可以使用B树索引的查询类型：

》全值匹配：为索引中的所有列进行匹配

》匹配最左前缀：若索引为(A,B,C)则(A),(A,B),(A,B,C)的查询都会使用索引，(A,C)这种情况在筛选出条件A时可以用到索引，C列就不行。其他情况会失效

》匹配范围值：只匹配某一列值得开头部分（如前面J开头姓的人）

》精确匹配某列范围匹配另一列：如姓为Allen，名K开头

因为B树索引是有序的，所以可以用于查询操作中的order by（按顺序查找）

B树索引限制：

• 索引必须按照最左列开始查找

• 不能跳过索引中的列，查询中使用索引必须保持和建立索引时的列顺序一致（如无法直接查找名为Bill的人）

• 若查询中有某列的范围查询，则其右边的所有列无法使用索引优化查找

2. 哈希索引

基于哈希表实现，只有精确匹配所有列的查询才有效。对于每一行数据，存储引擎会对所有的列计算一个哈希码。哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

CREATE TABLE testhash (
fname VARCHAR(50) NOT NULL,
Iname VARCHAR(5O) NOT NULL,
KEY USING HASH(fname)
)ENGINE=MEMORY；

若表中数据

fname	lname
Arjen	Lentz
Baron	Schwartz
Peter	Zaitsev
Vadim	Tkachenko

若有假象哈希函数f（），他返回下面值

f(‘Arjen’) = 2323
f(‘Baron’) = 7437
f(‘Peter’) = 8784
f(‘Vadim’) = 2458

则哈希索引的数据结构如下

注意：槽的编号是顺序的，但数据行不是

看下面查询

select lname from testhash where fname='Peter'

过程：MySQL先计算’Peter’哈希值，并通过该哈希值找到对应记录的指针，通过指针找到数据行，在比较该行数据是否为‘Peter’ 保证准确性。因为索引只用存储哈希值，所以索引结构紧凑，哈希索引查找也非常快。

哈希索引限制

》哈希索引只包含哈希码和行指针，没有字段值，所以不能用索引避免读取行。

》哈希索引不按照索引值顺序存储，不能用于排序

》哈希索引不支持部分索引匹配列查找，因为哈希索引始终使用全部索引列计算哈希值

》哈希索引只支持等值比较查询，包括=，in()，<=>，也不支持范围查询where price>100

》当出现哈希冲突，存储引擎必须遍历链表中所有行指针，找出所有符合条件的行

》哈希冲突过多，索引维护代价很高

创建自定义哈希索引

可以删除某个字段的索引，使用CRC32做哈希索引，这样就会通过哈希码查询速度会更快。缺陷是需要手动维护哈希冲突。

3. 空间数据索引

MyISAM支持空间索引，这类索引无前缀查询。

4.全文索引

特殊类型索引，它查找文本关键字，不比较索引中的值。适用于match against操作，而不是普通的where条件操作。

5.其他索引类别

InnoDB的聚簇索引，覆盖索引

5.2索引的优点

一星索引：索引将相关记录放在一起

二星索引：索引中的数据顺序和查找中的排列顺序一致

三星索引：索引中的列包含了全部查询需要的列

1. 减少服务器需要扫描的数据量

2. 帮助服务器避免排序和临时表

3. 将随机IO变为顺序IO

   索引并不总是最好的工具，对于特大型的表，索引的创建使用代价随之增长，更好的办法是分区

5.3高性能索引策略

正确创建和使用索引是高性能查询的基础

5.3.1独立的列

select actor_id form actor where actor_id+1=5;

此时无法启用actor_id列索引，虽然很明显actor_id等于4，但MySQL无法解析这个方程式，我们应当简化where条件

5.3.2前缀索引和索引选择性

索引很长的字符列：哈希索引；索引部分开始的字符（虽然能提高效率，但降低索引的选择性，指不重复的索引数比总记录数）。

一般情况某个列前缀的索引选择性也足够高。对于BLOB，TEXT，varchar类型，MySQL不允许索引这些列的完整长度

所以要平衡选择性与索引长度（节约空间）

5.3.3多列索引

在多个列上建单独的索引大部分情况下不能提升查询性能。MySQL5.0及以后引入了索引合并的策略，一定程度上能通过多个单列索引定位指定行。

索引有时候是一种优化的结果，更多时候表明了表上的索引建的很糟糕

5.3.4选择合适的索引列顺序

不考虑排序和分组时，选择性高的列放在前面通常是很好的，此时仅用于优化where条件查找

然而性能也和查询值的分布有关，所以需要考虑那些运行频率最高的查询来调整索引列顺序

5.3.5聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖实现方式，InnoDB的聚簇索引是在同一个结构中保存了B树索引和数据行。

表中有聚簇索引，它的数据行存放在索引的叶子节点中，一个表只能有一个聚簇索引。

叶节点包含了行全部数据，节点页只包含索引列。

InnoDB通过主键聚集数据，若没有定义主键，InnoDB会选择唯一的非空索引代替。若没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。

聚簇索引主要优点：

• 可以把相关数据保存在一起
• 数据访问更快
• 使用覆盖索引(5.3.6)扫描的查询可以直接使用叶节点中的主键值

主要缺点：

• 聚簇索引提高的是IO密集型应用的性能，若数据全在内存中，就无优势

• 插入速度严重依赖于插入顺序。如果不是按照主键顺序加载数据，加载完最好optimize table组织下表

• 更新聚簇索引的代价很高

• 在插入行或主键更新需移动时会有页分裂问题（当前页已满，存储引擎会将该页分裂成两个页面容纳该行）

• 可能导致全表索引变慢，尤其是行稀疏，或者由页分裂导致数据存储不连续时

• 二级索引（非聚簇索引。二级索引：叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。）比想象的大，因为二级索引的叶子节点包含了引用行的主键列

二级索引叶子节点保存的不是指向行物理位置指针，而是行的主键值，存储引擎通过这个值去聚簇索引中查找对应的行。

5.3.6覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称为覆盖索引

查询只扫描索引而无需回表好处：

• 索引条目数远小于数据行大小，如果只需要读取索引，MySQL就会极大减少数据访问量，这对缓存负载十分重要。
• 因为索引按照列值顺序存储，对于IO密集型查询会更快
• 一些存储引擎在内存中只缓存索引，数据则依赖操作系统缓存，因此访问数据需要系统调用，这会在大量数据访问时导致严重性能问题
• 由于InnoDB聚簇索引，覆盖索引对其表特别有用。InnoDB在二级索引：在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询则可避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引，空间索引，全文索引等都不存储索引列的值，索引MySQL都用B树做覆盖索引。

5.3.7使用索引扫描来做排序

MySQL有两种方式生成有序的结果：通过有序操作；按照索引顺序扫描。

只有当索引的列顺序和order by子句的顺序完全一致时，并且所有列的排序方向（正或倒序）都一样时，MySQL才能使用索引对结果做排序。

如果需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。

order by子句和select查询限制是一样的：需要满足索引的最左前缀的要求，否则MySQL无法利用索引排序

5.3.8压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小。默认只压缩字符串，也可设置压缩整数。

MyISAM压缩索引块：先保存索引块的第一个值，然后其他值和第一个值比较得到相同的前缀字节数和剩余不同的后缀部分，把不同部分存储即可。例如索引块中第一个值为‘perform’第二个值为‘performance’，那么第二个值得前缀压缩后的值类似‘7,ance’这样的形式。

压缩索引带来的弊端是操作更慢。

5.3.9冗余和重复索引

重复索引指在相同列上按照相同顺序的同类型的索引。应避免。

冗余索引指创建的索引被包含在已有索引的左前缀中（针对B树索引），如创建索引（A,B），索引（A）便是冗余索引，但索引（B,A）（B）都不是冗余索引。

5.3.10未使用的索引

建议删除

5.3.11索引和锁

索引可以让查询锁定更少的行，对性能有好处

5.6总结

MySQL中大多使用B树索引，其他类型索引只适用于特殊目的。

在选择索引和利用索引查询时，三个原则

• 单行访问是很慢的。使用索引可以创建位置引用提升效率
• 按顺序访问范围数据是很快的
• 索引覆盖查询是很快的

总的来说，编写查询应尽可能选择合适的索引以避免单行查找，尽可能使用记录本身的数据而避免排序操作，并尽可能使用索引覆盖查询（三星索引）

6.查询性能优化

6.1为什么查询速度会慢

如果把查询看做任务，那么他有一系列子任务组成，每个子任务都会消耗一定的时间，优化查询要么消除一些子任务，要么减少一些子任务的执行次数。

通常查询的生命周期大致为：客户端，服务器，服务器上解析，生成执行计划，执行，返回结果至客户端，其中执行为最重要的阶段，包含了大量检索数据存储引擎的调用，以及结果的数据处理包装等。

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

6.2.1避免以下

• 查询不需要的列
• 多表关联返回全部列
• 总是去除全部列
• 重复查询相同数据

6.2.2减少扫描额外的行

使用索引覆盖扫描

改变库表结构

重写复杂的查询

6.3重构查询的方式

6.3.1分解复杂查询

MySQL内部每秒能够扫描内存中上百万行的数据，相比之返回客户端的时间就慢多了；将大的复杂查询拆分为小的简单查询是很有必要的

6.3.2切分查询

对于大的查询需要分解为小的查询，每个查询功能完全一样，每次只返回一小部分查询结果。

删除数据就是一个好的例子。定期清理大量数据，若由一个语句一次性完成，则可能需要锁住很多数据，占满事务日志，耗尽系统资源，阻塞很多小但重要的查询。将大的delete分解为小的delete可以尽量小的影响MySQL的性能

6.3.3分解关联查询

优势：

缓存效率更高

执行单个查询减少锁竞争

应用层做关联，更容易对数据库进行拆分，更容易做到高性能和可扩展

查询本身效率也有所提升

可以减少冗余记录的查询

6.4查询执行的基础

6.4.2查询缓存

​ 解析一个查询语句前，如果查询缓存是打开的，MySQL会优先检查这个查询是否命中查询缓存中的数据，如果命中，在返回查询结果前，MySQL会检查一次用户权限。这仍然是无需解析SQL的，因为查询缓存中已经存放了当前查询需要访问表的信息。如果权限没有问题，MySQL会直接从缓存中拿结果给客户端。

6.4.3查询优化处理

查询生命周期的下一步是将SQL生成一个执行计划，MySQL再根据这个执行计划和存储引擎交互。这包括多个子阶段：解析SQL，预处理，优化SQL执行计划。这个过程有任何错误都有可能终止执行。

• 语法解析和预处理
  根据SQL生成解析树，判断是否有语法错误，根据MySQL规则更进一步检查语法树是否合法，例如表和列是否存在，别名是否歧义

• 查询优化器

  • 现在语法树是合法的了，并且有优化器将其转换为执行计划。优化器的作用就是找到查询的最好执行计划。
  • MySQL使用的是基于成本的优化器，它尝试预测查询在某种执行计划下的成本，并选择最小的一个。最初成本最小单位为随机读一个4k页的成本，后来计算公式变得更加复杂，并且引入一些因子来估算操作代价，如执行一次where比较的成本
  • 优化器是一个非常复杂的部件，他使用了很多策略来生成一个最优的执行计划。优化策略简单分为两种，静态和动态；静态直接通过解析树分析（如代数变换where条件等），可认为是编译时优化。静态优化在第一次完成后就一直有效。动态优化和查询的上下文有关，也可能和其他因素有关，可认为是运行时优化。动态优化每次执行完都要重新评估。
    MySQL能处理的一些优化类型
    • 重定义关联表顺序
    • 将外连接转化为内连接
    • 使用等价变换(例如不等式中的)
    • 优化count(),min(),max()（索引和列是否可为空可以帮助优化这类问题）
    • 预估并转换为常数表达式
    • 覆盖索引扫描
    • 子查询优化
    • 提前终止查询
    • 等值传播
    • 列表in()比较

• 数据和索引统计信息
  存储引擎保存数据和索引的统计信息。MySQL查询优化器在生成执行计划时要向存储引擎获取相应的统计信息，包括每个表有或者索引有多少页面，每个表的每个索引基数是多少，数据行和索引长度，索引的分部信息等。优化器根据这些信息选择最优的执行计划

• MySQL如何执行关联查询
  MySQL认为任何一次查询都是一次“关联”，不仅仅包括两表匹配，每一个查询，每一个片段（子查询，单表匹配）都是关联。
  当前MySQL的关联执行策略：现在一个表中取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的。然后根据查到的行，返回查询需要的列。MySQL会尝试在最后一个关联表中找到所有匹配的行如果最后一个关联表无法找到更多的行以后，MySQL会返回到上一层次的关联表，看能否找到更多的匹配记录，以此类推迭代执行。

• 执行计划

  MySQL不会生成查询字节码来执行查询，会生成查询的一棵指令树，然后通过存储引擎执行返回结果。最终的执行计划包含了重构查询的全部信息

• 关联查询优化器
  他决定了多个表关联时的顺序，关联查询优化器通过不同顺序的成本选择一个关联顺序。优化器会尝试在所有顺序中选择一个最小成本，不过如果可能的情况太多，优化器将使用贪婪搜索的方式查找最优。

• 排序优化
  当不能使用索引生成排序结果的时候，MySQL需要自己排序，数据量小在内存，大在磁盘，统一称为文件排序。
  如果要排序的数量小于排序缓冲区，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，为每个独立的块使用“快速排序”，并将每个排序的块存放在磁盘上，然后对排好的块进行合并。
  两次传输排序：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取需要的行
  单次传输排序：先读取查询所要的所有列，再根据给定的列进行排序，最后直接返回排序结果

6.4.4查询执行引擎

MySQL根据执行计划给出的指令逐步执行。其中大量的操作需要通过调研存储引擎实现的接口完成，即“handler API”。查询中每一个表由一个handler实例表示，实际上在优化阶段就为每个表创建了handler实例，便于优化器获得表数据

6.4.5返回结果给客户端

MySQL将结果集返回客户端是一个增量，逐步返回的过程。例如关联操作，一旦服务器处理完最后一个表，开始生成第一条结果，MySQL就逐步像客户端返回结果集，这样服务器不用因存储太多结果而消耗内存，客户端也能第一时间收到消息。

6.5MySQL查询优化器的局限性

6.5.1关联子查询

​ 示例：

select * 
from sakila.film
where film_id in(
	select file_id from sakila.film_actor
    where actor_id =1
);

MySQL会将查询改写成这样

select * 
from sakila.film
where exists(
	select * from sakila.film_actor where actor_id=1
    and film_actor.film)id =film.film_id
);

这时，子查询需要根据film_id来关联外部表film，因为需要film_id字段，所以MySQL认为无法先执行这个子查询。

改进

select film.* from sakila.film
	inner join sakila.film_actor using (film_id)
where actor_id=1

另一个优化的办法是使用函数group_count()在in()中构造一个由逗号分隔的列表。有时这比上面的使用关联改写更快

6.5.2union的限制

有必要通过limit限制取出的记录数，以免union的临时表过大

6.5.3索引合并优化

当where子句中包含多个复杂条件时，MySQL能够访问单个表的多个索引以合并交叉过滤的方式来定位查找的行。

6.5.4等值传递

6.5.5并行执行

MySQL补发利用多核特性来执行并行查询

6.5.6哈希关联

6.5.7松散索引扫描

无法按照不连续的方式扫描一个索引。

6.5.8最大值和最小值优化

6.5.9无法在一个表上同时进行查询和更新

6.6查询优化器hint提示

若对优化器选择的执行计划不满意，可以使用优化器的提示来控制最终的执行计划。

6.7优化特定类型查询

6.7.1优化count（）查询

count（）作用：可统计某个列值得数量（列值非空），和行数

select count(*) 
from world.City 
where id>5

• 优化

select (select count(*) from world.City) - count(*) 
from world.City 
where id<=5

因为查询优化阶段会把子查询当常数处理

• 使用近似值

6.7.2优化关联查询

• 确保on或者using子句中有索引。创建索引的时候就要考虑关联顺序
• 确保orderby 和groupby中表达式只涉及一列
• 升级MySQL注意关联语法

6.7.4优化group by和distinct

6.7.5优化limit分页

6.7.6 优化SQL_CALC_FOUND_ROWS

6.7.7优化union查询