【Mysql底层】基础夯实（一）

最新推荐文章于 2024-10-02 12:42:27 发布

CHris~s07

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量722

点赞数 13

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43643315/article/details/138821055

版权

Mysql架构和历史

1.1Mysql逻辑架构

添加图片注释，不超过 140 字（可选）

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个进程，连接的查询在单独的线程中执行。

1.1.2优化与执行

Mysql会解析查询，创建内部数据结构，对其进行各种优化。对于select语句，服务器会先检查查询缓存，找到直接返回结果集。

1.2并发控制

1.2.1读写锁

共享锁（读锁）：互相不阻塞

排它锁（写锁）：排他的，一个写锁会阻塞其他读锁和写锁

1.2.2锁力度

表锁：会锁定整张表。对表进行写操作前，需要首先获得写锁，这会阻塞其他用户的读写操作。

行级锁：最大程度支持并发，只在存储引擎层实现。

1.3事务

事务内的语句要么全部执行成功，要么全部执行失败。

原子性：整个事务的所有操作要么全部提交成功，要不全部失败回滚。

一致性：数据库总是总一个一致性状态到另一个一致性状态。

隔离性：事务所做的修改在最终提交以前对其他事务是不可见的。

持久性：事务提交后所做的修改会永久保存到数据库中。

1.3.1隔离级别

未提交读：事务中的修改及时没有提交对其他事务也是可见的。事务可以读到未提交的数据（脏读）。

提交读：一个事务从开始到提交之前所做的修改对其他事务都是不可见的。（不可重复读）。

可重复读：解决了脏读问题，保证同一个事务多次读取同样的记录结果是一致的。无法解决幻读问题。

可串行化：最高的隔离级别。强制事务串行执行。

添加图片注释，不超过 140 字（可选）

1.3.2死锁

两个或多个事务在同一资源相互占用。InnoDB目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。

1.3.3事务日志

可以提高事务的效率，使用事务日志存储引擎在修改表的数据时只需要修改其内存拷贝再把修改行为记录到持久在硬盘上的事务日志中。

1.3.4 MYSQL中的事务

自动提交

默认采用自动提交模式。

1.4多版本并发控制

MVCC是行级锁的变种，很多情况下避免了加锁操作。是通过保存数据在某个时间节点的快照来实现的。

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列，一个保存创建时间一个保存行过期或删除时间来实现的。（存的是版本号）每开始一个新的事务系统版本号会递增。

添加图片注释，不超过 140 字（可选）

1.5 MySQL的存储引擎

1.5.1InnoDB存储引擎

InnoDB的数据存在表空间中。

1.5.2MyISAM存储引擎

MyISAM将表存储在两个文件中，数据文件和索引文件。

MyISAM特性

加锁与并发：对整张表加锁而不是针对行。读取时加共享锁，写入时加排他锁。查询时也可以写入数据。

修复：可以手工或者紫红执行检查和修复操作。

索引特性：支持全文索引。

延迟跟小滚索引键：指定了DELAY_KEY_WRITE选项，每次修改执行完成时不会立即将修改的索引写入磁盘，会写到内存中的键缓冲区。

创建表导入数据后不会进行修改操作适合用MyISAM压缩表。

1.5.3MySQL内建的其他存储引擎

Archive引擎：支持行级锁和专用缓冲区

Blackhole引擎

CSV引擎：将csv文件作为表处理

Federated引擎：访问其他Mysql服务器的代理

Memory引擎：快速访问数据，并且数据不会被修改，重启以后允许丢失可以使用

Merge引擎：由多个MyISAM表合并的虚拟表。

NDB集群引擎

1.5.5选择合适的引擎

大部分情况InnoDB都是正确选择。

需要应用事务选择InnoDB，不需要事务并且是select insert操作可以选MyISAM。

需要在线热备份，选择InnoDB。

MyISAM崩溃后发生损坏的概率必InnoDB高得多，恢复速度慢。

实时记录日志到MySQL中，这类对插入速度要求很高可以选用MyISAM或者Archive。

读多写少的业务如果不介意崩溃恢复的问题可以选用MyISAM。

涉及订单处理，必须要支持事务，最好选用InnoDB。

CD-ROM或者DVD-ROM的应用可以选用MyISAM或者MyISAM压缩表，这样表可以在不同介质相互拷贝。

1.5.6转换表的引擎

Alter table

添加图片注释，不超过 140 字（可选）

将数据从原表复制到一张新的表中，会消耗系统所有的I/O能力，原表回加读锁。

导出与导入

可以使用mysqldump工具将数据导出到文件，然后修改文件中的create table语句的存储引擎选项。

创建与查询

添加图片注释，不超过 140 字（可选）

数据量大做分批处理

添加图片注释，不超过 140 字（可选）

第四章 Schema与数据类型优化

4.1选择优化的数据类型

更小的通常更好；简单（数据类型）就好；尽量给避免null；

4.1.1整数类型

数字有两种类型：整数和实数，存整数可以用TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,分别使用8，16，24，32，64位存储空间。MySQL可以为整数类型指定宽度，利润也INT(11)，对大多数应用是没有意义的，不会限制值的范围，只是规定教会工具显示字符的个数。

4.1.2实数类型

实数是带有小数的数字。FLOAT、DOUBLE支持使用标准的浮点运算进行近似计算。DECIMAL类型可以用于存储精确的小数。

4.1.3字符串类型

VARCHAR和CHAR类型

VARCHAR：存储可变长字符串，需要使用一个或两个额外字节记录字符串的长度。

适用场景：字符串的最大长度比平均长度长很多；列的更新很少；使用例如UTF-8这样的复杂字符集，每个字符都使用不同的字节数进行存储。

CHAR：定长；根据定义的字符串长度分配足够的空间。存储CHAR时MYSQL会删除所有的末尾空格。

适用场景：存储很短的字符串，或者所有值都接近同一个长度；经常变更的数据；非常短的列。

BINARY和VARBINARY：存储二进制字符串

BLOB和TEXT类型：为了存储很大的数据而设计的字符串类型。当BLOB和TEXT值太大时，InnoDB会使用专门的外部存储区域，每个值在行内存储指针，在外部存储区域存储实际的值。

BLOB存储的是二进制数据，没有排序规则或字符集；TEX类型有字符集和排序规则。

使用枚举代替字符串类型：MySQL在存储枚举十分紧凑，在内部会将每个值在列表中的位置保存为整数。缺点：字符串是固定的，对可变的字符串不适合。

4.1.4日期和时间类型

DATETIME:可以保存大范围的值，使用8个字节的存储空间。

TIMESTAMP：使用4个字节的存储空间，只能表示从1970到2038年。

4.1.5位数据类型

BIT：可以在一列中存储一个或多个true、flase值。Mysql把bit当做字符串类型而不是数字类型。

Set：如果需要保存很多true、false值，可以考虑合并这些列到set类型。缺点：改变列的定义代价很高，需要alter table；一般无法在set上通过索引查找。可以把8个位包装到一个TINYINT中，并且按位操作来使用来代替set。但比较难写查询语句。

4.1.6 选择标识符

标识列的数据类型应该和关联表中对应列一致。

整数类型：通常是最好选择，很快且可以使用自增。

Enum和set类型：适合存储固定信息如有序的状态，产品类型、性别。

字符串类型：尽量避免，很消耗空间且比数字慢。随机生成的字符串的值会遍布很大范围会导致插入和查询变慢。

4.1.7特殊类型数据

4.2 Mysql schema设计中的陷阱

太多的列：Mysql存储引擎工作时需要在服务层和存储引擎层通过行缓冲格式拷贝数据，然后在服务层将缓冲内容解码成各个列，代价很高。Myisam的定长结构实际上与服务层的行结构刚好匹配不需要转换。

太多的关联：单个查询最好在12个表以内关联。

全能的枚举：防止过度枚举。

变相的枚举：

添加图片注释，不超过 140 字（可选）

尽量避免使用null，但有些情况null也可以使用。

4.3范式和反范式

4.3.1范式的优点和缺点

添加图片注释，不超过 140 字（可选）

缺点：需要做关联。

4.3.2反范式化的优点和缺点

优点避免关联

4.3.3混用范式化和反范式化

最常见的反范式化数据是复制或者缓存，在不同表中存储相同的特定列。

4.4缓存表和汇总表

缓存表存储那些可以简单的从schema其他表获取数据的表。

汇总表保存的是使用group by聚合的数据的表。

重建汇总表和缓存表时要保证数据在操作时依然可用，需要使用“影子表”—在完成建表操作后可以通过一个原子的重命名操作切换影子表和原表。

添加图片注释，不超过 140 字（可选）

4.4.1物化视图

添加图片注释，不超过 140 字（可选）

4.4.2 计数器表

推荐创建独立的表存储计数器。

添加图片注释，不超过 140 字（可选）

4.5 加快ALTER TABLE操作的速度

4.5.1只修改.frm文件

不需要重建表：移除一个列的自增属性；增加、移除、或更改enum和set常量。

为想要的表建一个新的.frm文件替换掉原来那张表。

添加图片注释，不超过 140 字（可选）

4.5.4 快速创建MyISAM索引

可以先禁用索引、载入数据、再重新启用索引。但对唯一索引无效，可以先删除所有的唯一索引，增加新的列后重新创建删除掉的索引。

4.6总结

添加图片注释，不超过 140 字（可选）

第五章创建高性能的索引

建立多个索引数据怎么存储？

5.1索引基础

5.1.1索引的类型

Mysql索引在存储引擎而不是服务器层实现的。

B-Tree索引：加快访问速度的原因是存储引擎不再需要进行全表扫描来获取需要的数据直接从索引的根节点开始搜索。根节点存放了指向子节点的指针，从这些指针向下查找，通过比较节点页的值和要查找的值可以找到合适的指针进入下层节点。子节点存放数据。

添加图片注释，不超过 140 字（可选）

索引对如下查询有效：

全值匹配：和索引中的所有列进行匹配。

匹配最左前缀：只是用索引第一列。

匹配列前缀：匹配某一列的值的开头部分。

匹配范围值

精确匹配某一列并范围匹配另外一个列

只访问索引的查询

Order by子句满足前面的类型这个索引也可以满足排序需求

B-Tree索引限制：不是从索引的最左列查找，无法使用索引；不能跳过索引中的列；如果有某个列的范围查询，其右边的列无法使用索引优化查找。

哈希索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效。对每一行数据存储引擎会对所有的索引列计算出一个哈希码。哈希索引将所有的哈希码存储在索引中，在哈希表中保存指向每个数据行的指针。缺点：哈希索引只包含哈希值和指针不存储字段值，不能用索引中的值来避免读取行；数据不是按照索引值顺序存储的，无法用于排序；不支持部分索引列匹配查找；只支持等值比较查询；存在哈希冲突时会逐行比较直至找到符合条件的行；哈希冲突较多维护成本很高。

InnoDB有自适应哈希索引，当某些索引值被频繁使用会在B树基础上在创建哈希索引。

创建自定义哈希索引

添加图片注释，不超过 140 字（可选）

创建触发器维护索引

添加图片注释，不超过 140 字（可选）

为了避免冲突必须在where里加入哈希值和对应的列值。

全文索引：查找文本中的关键词。

5.2索引的优点

可以让服务器更快定位到表的指定位置。减少服务器需要扫描的数据量；避免排序和临时表；将随机IO变为顺序IO。

5.3 高性能的索引策略

5.3.1独立的列

索引列不能是表达式的一部分

添加图片注释，不超过 140 字（可选）

5.3.2前缀索引和索引选择性

遇到很长的字符列，可以索引开始的部分字符，节省了索引空间，但可能会降低选择性。BLOB、TEXT、很长的VARCHAR列必须用前缀索引。但MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

多列索引

当服务器出现多个索引做相交操作（多个and），通常要一个包含所有相关列的多列索引；

当服务器出现多个索引做联合操作（多个or）需要消耗大量资源在缓存排序、合并上；

在explain中看到索引合并应该好好检查查询和表结构。

5.3.4 选择合适的索引列顺序

不考虑分组和排序时，可以将选择性高的列放到索引最前列。

5.3.5 聚簇索引

是一种数据存储方式。表有聚簇索引时，数据行实际上存在索引的叶子页中。

优点：可以把数据保存在一起；数据访问更快使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：如果数据全在内存中访问的顺序就不重要了，聚簇索引就失去了优势；插入速度严重依赖于插入顺序；跟新聚簇索引列代价高，会移动行到新位置；插入新行或者主键被更新导致移动行可能会导致页分裂；可能会导致全表扫描变慢；二级索引可能很大，且需要两次索引查找。（二级索引叶子节点保存的不是指向行的物理位置而是行的主键值）

添加图片注释，不超过 140 字（可选）

InnoDB和MyISAM的数据分布对比

添加图片注释，不超过 140 字（可选）

InnoDB存了整个表，不只有索引。InnoDB的二级索引寸的是主键值，无需在移动行时更新这个指针。

添加图片注释，不超过 140 字（可选）

在InnoDB表中按之间顺序插入行

没有数据需要聚集可以定义一个自增列做代理主键，保证数据顺序写入。

使用UUID做主键，导致随机插入，速度下降，时间变长。在插入前先找到并从磁盘读取到目标页中，导致大量随机io；乱序写入，不得不频繁做页分裂；页分裂会让页变稀疏，产生碎片。

5.3.6覆盖索引

一个索引包含所有需要查询字段的值称为覆盖索引，查询时只需要扫描索引无需回表。

优点：索引条目通常小于数据行大小，所以只需要读取索引会极大减少数据访问量。索引是按照列值顺序存储的，对于io密集型的范围查询会比随机从磁盘读取每一行的数据io少得多；一些存储引擎MyISAM在内存中只缓存索引，数据依赖操作系统来缓存；InnoDB的聚簇索引，可以避免对主键索引的二次查询。

Mysql只能用B树来做覆盖索引，不是所有的存储引擎都支持索引覆盖。

5.3.7 使用索引扫描来做排序

Mysql生成有序结果可以通过排序或者按索引顺序扫描。Explain出来的type列的值为index说明用索引扫描来做排序。最好让一个索引既满足排序有用于查找行。当索引列的顺序和orderby子句顺序完全一致，且列的排序方向都一样时，才能用索引对结果排序。多表关联只有当orderby子句引用的字段全部为第一个表时才能用索引排序。前导列为常量是order不用子句可以不满足最左前缀的要求。

添加图片注释，不超过 140 字（可选）

不能使用索引做排序的情况：

使用不同的排序方向，但索引列都是正序；

Orderby子句引用了一个不在索引中的列；

Where和orderby中的列无法组成索引的最左前缀；

索引列第一列为范围条件，无法使用其余索引列；

5.3.8压缩索引

Myisam使用前缀压缩来减少索引的大小。先保存索引块的第一个值，然后将其他值和第一个值进行比较得到相同前缀字节和剩余不同后缀部分，将这部分存储起来即可。压缩索引节省空间，io密集型应用好处较大。

5.3.9冗余和重复索引

重复索引是指在相同列按照相同顺序创建想同类型的索引，应该避免。

添加图片注释，不超过 140 字（可选）

冗余索引：如创建（A,B）索引再创建（A）索引。Innodb主键列已经是二级索引。

5.3.10未使用的索引

用不到的索引建议删除。

5,3,11索引和锁

索引可以让锁锁定更少的行。Innodb只有在访问行的时候才会对其加锁，索引减少了innodb的访问次数，减少了锁的数量。及时使用索引也会锁住一些不需要的数据。

5.4索引案例学习

5.4.1支持多种过滤条件

哪些列选择性比较好、在where里出现频繁可以优先选择。可以使用in（，）来覆盖不在where子句中的列；尽可能将范围查询放到索引后面。

5.4.2避免多个范围条件

部分可用in（）避免

5.4.3优化排序

添加图片注释，不超过 140 字（可选）

通过延迟关联、索引覆盖查询需要的主键再根据主键返回想要的行。

5.5维护索引和表

5.5.1 找到并修复损坏的表

Check table检查表损坏

Repair table修复损坏的表或通过一个不作任何操作的alter重建表

添加图片注释，不超过 140 字（可选）

5.5.2更新索引统计信息

Records_in_range()通过传入两个边界值获取在这个范围里大概有多少记录

Info()返回各种类型的数据，包括索引基数

表没有统计信息，或信息不准确会导致优化器做出错误决定。

添加图片注释，不超过 140 字（可选）

5.5.3 减少索引和数据碎片

碎片化的索引会以很差或者无须存储在磁盘上。B树索引可能会碎片化，表的数据存储也可能。

行碎片：数据行被存储在多个地方的多个片段中。

行间碎片：逻辑上顺序的页或者行在磁盘上不是顺序存储的。对全表扫描、聚簇索引有很大影响。

剩余空间碎片：数据页有大量剩余空间，导致服务器读到大量不需要的数据。

Optimize table或者导出导入的方式来重新整理数据。不支持Optimize table可以用个不作任何操作的alter重建表。

添加图片注释，不超过 140 字（可选）

5.6总结

单行访问很慢；

按顺序访问范围数据很快；

索引覆盖查询很快；

编写语句应该尽可能选择合适的索引，尽可能使用数据原生顺序避免额外排序，尽可能使用索引覆盖。

第六章查询性能优化

6.1为什么查询速度会慢

查询：从客户端到服务器，然后在服务器上解析，生成执行计划，执行，返回结果给客户端。

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

低效查询分析：访问了过多的行或列；mysql服务器层是否在分析大量超过需要的数据行。

6.2.1是否向数据库请求量不需要的数据

查询不需要的记录：mysql会先返回全部结果集再计算。如查询前面几条数据有效方法是查询后加limit。

多表关联时返回全部列

总是取出全部列

重复查询相同的数据

6.2.2 mysql是否在扫描额外的记录

很亮查询开销的三个指标：响应时间、扫描的行数、放回的行数。

响应时间：服务时间与排队时间之和。服务时间是数据库处理这个查询真正花的时间，排队时间是服务器因为等待某些资源而没有真正执行查询的时间。

扫描的行数和返回的行数：分析查询时查看扫描行数很有帮助。

扫描的行数和访问类型：explain语句中的type列反映了访问类型有全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用等，这些速度从慢到快、扫描行数从小到大。如果查询无法找到合适的访问类型最好的办法是增加索引。

一般Mysql能够使用如下三种方式、应用where条件从好到坏依次为：在索引中用where过滤不匹配的记录，在存储引擎完成；使用索引覆盖扫描直接从索引中过滤不需要的记录并返回结果，在服务层完成；从表中返回数据，然后过滤，在服务层完成。

如果发现需要扫描大量的数据只返回少量的行可以1.使用索引覆盖扫描2.改变库表结构，如只用汇总表3.重写查询。

6.3重构查询方式

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

数据返回给客户端慢，查询快

6.3.2切分查询

将大查询切分成小查询，如删除旧数据，一次性删除可能会锁住很多数据，占满整个事务日志、耗尽系统资源、阻塞很多查询。

6.3.3分解关联查询

好处：让缓存效率更高，有些数据如果在缓存中可以直接用；可以减少锁竞争；更容易做到高性能和可扩展；查询效率更能会提升；减少冗余数据查询；相当于在应用中做了哈希关联比mysql的嵌套循环关联有时要快。

6.4查询执行的基础

添加图片注释，不超过 140 字（可选）

6.4.1mysql客户端/服务器通信协议

客户端与服务器时“半双工”的同一时刻只能有一个发送数据一个接收数据。大多数连接mysql的库函数都可以获得全部的结果集并缓存到内存里，也可以设置不缓存。

查询状态

MySQL连接（线程）有一个状态：

Sleep 线程等待客户端发送新的请求

Query 正在执行查询或者正在将结果发送给客户端

Locked 该线程正在等待锁

Analyzing and statistics 正在收集存储引擎的统计信息，生成执行计划

Copying to tmp table 正在执行查询，并将结果集都复制到一个临时表中，一般是在做group by 或文件操作或union。

Sorting result 正在对结果集排序

Sending data 可能在多个状态之间传送数据，或者生成结果集、或在向客户端返回数据。

6.4.2查询缓存

解析查询前如果缓存打开，会优先检查缓存，通过哈希查找实现。命中后会检查权限，没有问题直接将数据返回客户端。

6.4.3 查询优化处理

查询后要把sql转化成执行计划，mysql再依照这个执行计划和存储引擎进行交互。

语法解析器和预处理：通过关键词将sql语句解析，成生一个解析树。

查询优化器：找到最好的执行计划，mysql使用基于成本的优化器，选择成本最小的执行。

导致mysql优化器选择错误的执行计划原因：统计信息不准确；执行计划中的成本估算不等同于实际执行的成本；mysql基于成本模型选择最优，并不一定是最快的；mysql从不考虑其他并发执行；mysql不是任何时候都是基于成本的优化，有时也会基于一些固定规则如全文索引；mysql不会考虑不受其控制的操作的成本。

优化器策略分两种：静态、动态优化。静态优化直接对解析树进行分析、优化。动态优化和查询的上下文有关。静态优化只需要做一次，动态优化每次执行都需要重新评估。

Mysql能够处理的优化类型：

重新定义关联表的顺序

将外连接转化为内连接

使用等价变换规则

优化count（）、min（）、max（）使用这种类型的优化在expalin上可以看到select tables optimized away。

预估并转化为常数表达式当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。

覆盖索引扫描当索引中的列包含所有查询中需要使用到列的时候，可以直接用索引返回需要的数据无需查询对应的行。

子查询优化有些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询对数据进行访问。

提前中止查询发现满足查询需求时立刻中止查询，发现不成立的条件也立刻返回

等值传播如果两个列通过等式关联，那么mysql能把其中一个列的where条件传递到另一列上。

列表in（）的比较 mysql将in（）列表的数据进行排序，然后通过二分查找来确定类表中的值是否满足条件，复杂度O（logn）

数据和索引的统计信息

服务层没有任何统计信息，mysql查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息（每个表有多少页面，索引有多少页面、索引的计数、数据行和索引长度、索引的分布信息）。

Mysql如何执行关联查询

对于union查询，mysql先将单个查询结果放到临时表中，再读出临时表完成union查询。

Mysql对任何关联都执行嵌套循环关联查询。先查找第一个表，在嵌套查询第二个表再回溯到上一个表。

添加图片注释，不超过 140 字（可选）

执行计划

Mysql生成查询的一棵指令树，通过存储引擎执行完成这棵指令树并返回结果。

添加图片注释，不超过 140 字（可选）

关联查询优化器

关联查询优化器通过评估不同顺序时的成本选择一个代价最小的关联顺序。Straight_join可以重写查询，让优化器按照你认为最优的关联顺序执行。

排序优化

尽可能避免排序或对大数据量进行排序。排序数据量小于排序缓冲区，会使用内存进行快速排序，内部不够会将数据分块，对独立的块使用快排进行排序，并将各个块的排序结果存放在磁盘上，将各个排好序的块进行合并返回结果。

Mysql有两种排序算法

两次传输排序：读取行指针和需要排序的字段进行排序，再根据排序顺序结果读取所需要的数据行。

单次传输排序：读取查询所需要的所有列，再根据给定的列进行排序，最后直接返回排序结果。

关联查询需要排序时会分成两种情况，如果order by子句的所有列都来自关联的第一个表，那么mysql在关联处理第一个表进行文件排序 explain会出现using filesort，其余情况会先将关联结果存放到临时表，在所有的关联都结束后在进行文件排序，explain的extra会出现using temporary using filesort

6.4.4 查询执行引擎

解析和优化阶段，mysql会生成查询对应的执行计划，根据执行计划完成整个查询。在根据执行计划执行的过程中，有大量操作需要通过调用存储引擎实现的接口。Handler api接口实现，查询中的每个表都由一个handler实例表示。Mysql在优化阶段就为每个表创建handler实例，优化器可以根据这些接口获取表的相关信息（表的列名、索引统计信息等）。

6.4.5 返回结果给客户端

查询最后一个阶段是返回结果给客户端。如查询可以缓存，那么mysql在这个阶段也会将结果存放到缓存中。Mysql返回结果集是一个增量逐步返回的过程。好处：无需存储太多结果。

6.5mysql查询优化器的局限性

6.5.1关联子查询

In加子查询性能很差，可以用exists。

如何用好关联子查询：

当返回结果中只有一个表中的某些列时，关联查询也许效率会更好；不要听取“子查询”的绝对真理，应该用测试验证子查询对执行计划和响应时间的假设。

6.5.2 UNION的限制

有时，mysq无法将限制条件从外层下推到内层，下列sql会将有结果联合起来再取出20条。

添加图片注释，不超过 140 字（可选）

6.5.3 索引合并优化

5.0之后where包含多个复杂条件时mysql能够访问单个表的多个索引以交叉过滤的方式来定位需要的行。

6.5.4等值传递

某些时候等值传递可能会有一些意想不到的额外消耗，例如哟个很大的in列表。Mysql发现where、on、或者using子句会将这个列表的值和另一个表的某个列相关联，优化器会将in（）列表都复制应用到关联各个表中。

6.5.5并行执行

Mysql无法使用

6.5.6哈希关联

Mysql不支持哈希关联，所有的关联都是嵌套关联，可以通过过哈希索引来实现哈希关联。

6.5.7松散索引扫描

Mysql无法使用松散扫描

添加图片注释，不超过 140 字（可选）

5.0后有些查询可以使用松散索引扫描，explain中extra显示using index for group-by表示可以使用松散索引。

6.5.8最大值和最小值

添加图片注释，不超过 140 字（可选）

First_name没有索引，要做一次全表扫描。

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

不允许对同一张表同时进行查询和更新。可以用生成表的形式，子查询会在update之前完成。

添加图片注释，不超过 140 字（可选）

6.6查询优化器提示

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

HIGH_PRIORITY和LOW_PRIORITY:告诉哪些语句优先级高，哪些优级低。HIGH_PRIORITY会将select语句调度到在在等待表锁修改数据的语句前；还可用于insert；LOW_PRIORITY会让语句一直处于等待状态；只对使用表锁的存储引擎有效。

DELAYED：对insert和replace有效，会将使用该提示的语句立即返回给客户端，将插入的行数据放到缓冲区中，在表空闲时写入。

STRAIGHT_JOIN:放置在select语句的select关键字后，也可以放置在任何两个关联表的名字之间。用法一是让查询中所有的表按照在语句中出现的顺序进行关联；方用法二是固定其前后两个表的关联顺序。

SQL_SMALL_RESULT和SQL_BIG_RESULT：只对select有效，告诉优化器group by和distinct查询如何使用临时表及排序，SQL_SMALL_RESULT告诉优化器结果集很小，可以将结果集放到内存中的索引临时表避免排序，SQL_BIG_RESULT结果集很大，建议用磁盘临时表做排序。

SQL_BUFFER_RESULT：将查询结果放入一个临时表，尽可能快地释放表锁。

SQL_CACHE和SQL_NO_CACHE：告诉mysql这个结果集是否应该缓存在查询缓存中。

SQL_CALC_FOUND_ROWS:让结果集包含更多的信息。会计算除去limit子句后这个查询要返回的结果集总数。

FOR UPDATE和LOCK IN SHARE MODE：会对符合查询条件的数据行加锁。

USING INDEX、IGNORE INDEX 和 FORCE INDEX：告诉优化器使用或不适用哪些索引来查询记录。

6.7优化特定类型的查询

6.7.1优化COUNT（）查询

Count（）的作用：1.统计某个列的数量，或者行数。统计列时要求列值非空。2.统计结果集的行数。如count（*）

没有任何条件的count（*）非常快，可以直接利用存储引擎获得这个值。

使用近似值：可以使用近似值，explain出来的估计得行数也可以选择。

6.7.2优化关联查询

确保on或者using子句中的列有索引。