第5章 创建高性能索引
索引基础
在MySQL中，索引是在存储引擎层而不是服务器层实现的，并没有统一的索引标准，不同的存储引擎索引工作的方式是不一样的。
索引类型
1.B-Tree 索引 ： 可用于全值匹配、最左前缀匹配、列前缀匹配、范围值匹配、精确匹配某一列并范围匹配另外一列、只访问索引的查询
-  NULL DEFAULT NULL COMMENT 'age',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'name',
`year` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'year',
INDEX `demo`(`age`, `name`, `year`) USING BTREE COMMENT 'demo'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
故意使用不按索引顺序查询：
mysql> explain extended SELECT * FROM `student` where `name` = 1 and `age` = 1;
实际: key:demo 使用到了索引
如何查看Mysql优化之后的SQL：
## 仅在服务器环境下
explain extended SELECT * FROM `student` where `name` = 1 and `age` = 1;
## 再执行
show warnings;
## 结果如下：
/* select##1 */ select `mytest`.`student`.`age` AS `age`,`mytest`.`student`.`name` AS `name`,`mytest`.`student`.`year` AS `year` from `mytest`.`student` where ((`mytest`.`student`.`age` = 1) and (`mytest`.`student`.`name` = 1))
可以发现真正执行的SQL是 age在前，name在后
对于仅使用year字段的查询结果呢？依然显示 -> key:demo 使用到了索引
总结：
- 写代码时对于索引顺序不必有
太多顾虑 - 优化SQL时候不仅仅只看 key参数是否使用索引，还需要注意type，ref等等
- Mysql 优化器
并不是一定改变SQL的参数查询顺序，而是以它的判定方式选择它认为的最有效的查询
索引的优点
正常的索引数据结构可以有其独特的优点，比如哈希索引不支持顺序查询，而B-Tree，或者 B+Tree则可以，总结B-Tree系列索引优点
- 减少服务器需要扫描的数据量；
- 避免排序和临时表；
- 将随机I/O变为顺序I/O；
高性能的索引策略
独立的列
索引在使用中不可嵌入表达式或者方法 错误示范： select * from where eid + 1 < 10;
前缀索引和索引的选择性
如果索引很长的字符串列，会导致索引变得很大并且很慢。一个处理策略是模拟哈希索引，另一个办法是索引开始的一部分字符串的值，即前缀索引。
索引的选择性指不重复的索引值和数据表中记录总数(##T)的比值。取值在为(1/ ##T~1]。选择性越高则查询效率越高。唯一索引的选择性是1，是最好的索引选择性，性能也是最好的。
前缀索引的优点是可以节约索引空间，提高索引效率。缺点是降低了索引的选择性，同时也无法使用前缀索引做ORDER BY 或 GROUP BY 操作，无法使用前缀索引做覆盖扫描。
使用前缀索引时，确定前缀长度的依据是：计算前缀索引的选择性，使前缀索引选择性接近完整列的选择性
多列索引
错误做法：为where字段的每一个列创建独立的索引，或者按照错误的顺序创建多列索引
在explain中，如果发现索引合并，实际上说明了表上的索引建的不够好：
- 当需要进行AND操作时，其实说明了需要建立一个包含所有相关列的多列索引；
- 当需要进行OR操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上；
- 优化器不会把这些计算到”查询成本“中，这会使得查询成本被低估，可能还不如全表扫描然后union。
选择合适的索引顺序
不考虑排序和分组时：将选择性最高的列放在前面通常是很好的（选择性即有效区分数据）
但，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关，这就意味着可能需要
根据那些运行频率最高的查询来调整索引列的顺序
例如：一张订单表，需要查询的字段有两个，一个是地区ID，一个是用户ID，简单思考就一定能发现用户ID的区分度应该是比地区
ID要高的多（相同用户ID的数据要远远少于相同地区ID），因此建立索引应该是（用户ID，地区ID）
聚簇索引
聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助
索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找
聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引， 聚簇索引默认是主键，
如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似
oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添
加我们想要的聚簇索引，最后恢复设置主键即可
聚簇的数据的优点：
- 可以把相关的数据保存在一起。如电子邮箱中，根据用户ID来聚簇数据，这样只要读取少数的数据页就可以获取某个用户的全部邮件。如果没有使用聚簇索引，可能每一封邮件导致一次磁盘I/O；
- 访问数据更快。聚簇索引将索引和数据报错在同一个B-Tree中，因此获取数据通常比非聚簇索引更快；
- 使用覆盖索引扫描的查询可以直接使用页节点的主键值
聚簇索引的缺点：
- 聚簇索引能够提高I/O的密集程度，但如果所有的数据全都放在内存中，那么访问顺序就没那么重要了，聚簇索引也就没什么优势了
- 插入速度严重依赖于插入顺序。按照主键顺序插入是最快的。如果不是先找主键顺序加载数据，那么加载完成后最好用OPTIMIZE TABLE命令重新组织一下表
- 更新聚簇索引列的代价很高
- 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临”页分裂”的问题
- 举措索引可能导致全表扫描变慢，尤其是行比较稀疏，或者页分裂导致数据存储不连续的时候
- 二级索引（非聚簇索引）可能比想象的大，因为二级索引的叶子节点包含了引用行的主键列
- 二级索引访问需要两次索引查找，而不是一次（第一次获得主键，第二次根据主键值去聚簇索引中查找对应的行）
在InnoDB表中按主键顺序插入行
如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理健作为主键。最简单的是使用AUTO INCREMENT自增
列，这样可以保证数据行是顺序写入的，对于主键做关联操作也是最好的
最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用，例如UUID
用UUID作为主键的坏处：
- 写入的目标也可能已经疏导磁盘并从缓存中移除，或者还没有被加载到缓存中。InnoDB不得不先找到并且从磁盘读取目标页到内存，这导致了大量的随机I/O
- 因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，会导致移动大量数据，一次插入最少需要修改三个页而不是一个页
- 由于频繁的页分裂，页会变的稀疏并被不规则填充，所以最终数据会有碎片
- UUID较长，不利于作为索引
字符类型作为主键的通用替代方案：美团分布式ID生成项目， 雪花ID
覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为：覆盖索引
覆盖索引的好处：
- 索引条目远小于数据行大小，能够极大地提高性能，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量
- 因为索引是按照值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘中读取每一行数据的I/O要少的多
如果不覆盖索引，则会产生回表查询， 先定位主键值，再定位行记录，它的性能较扫一遍索引树更低
使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描
如果EXPLAIN出来的type列的值为index，则说明使用了索引扫描排序
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引无法覆盖所有的列，那就不得不扫描
一条索引记录就回表查询一次对应的行，这基本上属于随机I/O，因此按索引顺序读取数据的速度通常比顺序地全表扫描要慢
压缩（前缀压缩）索引
MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某种情况下可以极大地提高性能。默认只压缩
字符串，但通过参数设置也可以对整数压缩 ， MyISAM存储引擎不过多深究
冗余和重复索引
重复索引：
- MySQL允许在相同列上创建多个索引，但这样需要单独维护重复的索引，并且优化查询的时候也需要逐个进行考虑，会影响性能，应该避免这么做
冗余索引
- 如果已经创建了索引（A, B），在创建索引（A），那么就是冗余索引，因为它只是前一个索引的前缀
- 冗余索引通常发生在表添加新索引的时候。如增加一个新的索引(A, B)，而没有扩展已有索引(A)，导致(A)成为冗余索引。或者将索引扩展为(A, 主键ID)，对InnoDB来说，主键已经包含在二级索引中了，因此也是冗余的
索引和锁
索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好
处：
- 虽然InnoDB的行锁的效率很高，内存使用也很少，但是锁定行的时候依然会带来额外开销
- 锁定需要的行会增加所争用并减少并发性
InnoDB只有在访问行的时候才会对其加锁。而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引
擎能够过滤掉不需要的行时才有效，如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层之后，MySQL服务
器才能应用Where子句，这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。
Explain的Extra列显示“Using Where”，说明MySQL服务器将存储引擎返回行之后再应用where过滤条件。此时where子句以前的数据全都被加锁
InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排它锁（写锁）
InnoDB的行锁是建立在索引的基础之上的，行锁锁的是索引，不是数据，所以提高并发写的能力要在查询字段添加索引
维护索引和表
使用正确的类型创建了表并加上了合适的索引后，还需要维护表和索引来确保它们正常工作，目的如下：
- 找到并修复损坏的表
- 维护准确的索引统计信息
- 减少碎片
找到并修复损坏的表
可以通过CHECK TABLE检查是否发生了表错误
可以用REPAIR TABLE或者一个不作任何操作的ALTER操作来修复表
更新索引统计信息
- records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录
- info()，返回各种类型的数据，包括索引的基数（每个键值有多少条记录）
减少索引和数据的碎片
B-Tree索引可能导致碎片化，会导致查询效率降低。有三类数据碎片
- 行碎片：数据行被存储在多个片段中
- 行间碎片：逻辑上顺序的页，或者行在磁盘上不是顺序存储的
- 剩余空间碎片化：数据也中有大量的空余空间
对于MyISAM表，三类碎片都可能发生，InnoDB不会出现短小的碎片行，会移动短小的行并重写到一个片段中
总结
选择索引以及利用索引查询时的三个原则：
- 单行访问是很慢的。最好读取的块中包含尽可能多需要的行，使用索引可以创建位置引用以提升效率
- 按顺序访问范围数据是很快的，原因如下：
- 顺序I/O不需要多次磁盘寻道，比随机I/O快
- 如果服务器能够按顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了
- 索引覆盖查询是很快的
现实使用中，很难做到每一个查询都有完美的索引，这时候需要根据需求有所取舍地创建合适的索引，而非根据惯例一刀切
第6章 查询性能优化
对于高性能数据库操作，只靠设计最优的库表结构、建立最好的索引是不够的，还需要合理的设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。查询优化、索引优化、库表结构优化需要齐头并进，一个不落。
为什么查询速度会慢
通常来说，查询的生命周期大致可以按照顺序来看：从客户端»服务器»在服务器上进行解析»生成执行计划»执行»返回结果给客户端。其中执行可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。
慢查询基础：优化数据访问
查询性能低下的最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。
1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
2.确认MySQL服务器层是否在分析大量超过需要的数据行。
是否向数据库请求了不需要的数据
请求多余的数据会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU内存和资源。这里有一些典型案例：
1、查询不需要的记录：例如在新闻网站中取出100条记录，但是只是在页面上显示10条。实际上MySQL会查询出全部的结果，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。
2、多表关联时返回全部列，例如：
3、总是取出全部的列：每次看到SELECT *的时候都需要怀疑是不是真的需要返回全部的列？取出全部列，会主优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO、内存和CPU的消耗。如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。
4、重复查询相同的数据：不要不断地重复执行相同的查询，然后每次都返回完全相同的数据。当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然更好。
MySQL是否在扫描额外的记录
对于MySQL，最简单的衡量查询开销的三个指标有：响应时间、扫描的行数、返回的行数。这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。
响应时间
响应时间是两个部分之和：服务时间和排队时间，一般常见和重要的等待是IO和锁等待。
扫描的行数和返回的行数
分析查询时，查看该查询扫描的行数是非常有帮助的。一定程度上能够说明该查询找到需要的数据的效率高不高。理想的情况下扫描的行数和返回的行数应该是相同的。当然这只是理想情况。一般来说扫描的行数对返回的行数的比率通常很小，一般在1：1到10：1之间。
扫描的行数和访问类型
MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。
在EXPLAIN语句的TYPE列返回了访问类型。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。索引让MySQL以最高效、扫描行最少的方式找到需要的记录。
一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：
1、在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
2、使用索引覆盖扫描（在extra列中出现了using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须回表查询记录。
3、从数据表中返回数据，然后过滤不满足条件的记录（在extra列中出现using where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
重构查询方式
一个复杂查询还是多个简单查询
MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询也是很有必要的。
切分查询
有时候对于一个大查询我们需要“分而治之”，对于删除旧数据，如果用一个大的语句一次性完成的话，则可能需要一次性锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。例如我们需要每个月运行一次下面的查询：
那么可以用类似下面的办法来完成同样的工作：
分解关联查询
乍一看这样做并没有什么好处，但其有如下优势：
1、让缓存的效率更高。对MySQL的查询缓存来说，如果关联中的某个表发生了变化 ，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么该表的查询缓存能重复利用 。
2、将查询后，执行单个查询可以减少锁的竞争。
3、查询性能也有所提升，使用IN（）代替关联查询，可以让MySQL按照ID顺序进行查询，这比随机的关联要更高效。
查询执行基础
希望MySQL能够能更高的性能运行查询时，最好的办法就是弄清楚MySQL是如何优化和执行查询的。
MySQL客户端/服务端通信协议
MySQL客户端和服务器之间的通信协议是“半双工”的，在任何一个时刻，要么由服务器向客户端向服务端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
一旦客户端发送了请求，它能做的事情就只是等待结果了，如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误，所以参数max_allowed_packet就特别重要。相反，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后主服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果然后粗暴地断开连接，都不是好主意。这也是必要的时候需要在查询中加上limit限制的原因。
换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。
当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集时，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能尽早开始处理这些数据，就能大大减少内在的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。PHP的 mysql_query()，此时数据已经到了PHP的缓存中，而mysql_unbuffered_query()不会缓存结果。
查询状态：可以使用SHOW FULL PROCESSLIST命令查看查询的执行状态。Sleep、Query、Locked、Analyzing and statistics、Copying to tmp table[on disk]、Sorting result、Sending data
查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这是检查是通过一个对大小写敏感的哈希查找实现的。如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。如果权限没有问题，MySQL会跳过执行阶段，直接从缓存中拿到结果并返回给客户端。
查询优化处理
查询生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。
1、语法解析器和预处理首先MySQL通过关键字将SQL语句进行解析，并生成一棵解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。例如是否使用错误的关键字，或者使用关键字的顺序是否正确，引号是否能前后正确匹配等。
2、预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名看它们是否有歧义。
3、一下步预处理会验证权限。
查询优化器：一条语句 可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。成本的最小单位是随机读取一个4K的数据页的成本，并加入一些因子来估算某引动操作的代价。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。
这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。
当然很多原因会导致MySQL优化器选择错误的执行计划：例如统计信息不准确或执行计划中的成本估算不等同于实际执行的成本。
MySQL如何执行关联查询：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到一个表中寻找匹配的行，依次下去直到找到的有匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。（嵌套循环关联）
执行计划：MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED，再执行SHOW WARNINGS，就可以看到重构出的查询。
MySQL的执行计划是一棵左侧深度优先的树。
不过，如果有超过n个表的关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”。实际上，当需要关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式。
排序优化：无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。如果需要排序的数据量小于排序缓冲区，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排序的块进行合并，最手返回排序结果。
MySQL有两种排序方法：
两次传输排序（旧版），读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。显然是两次传输，特别是读取排序后的数据时（第二次）大量随机I/O，所以两次传输成本高。
单次传输排序（新版），一次读取出所有需要的或SQL查询指定的列，然后根据排序列，排序，直接返回排序后的结果。顺序I/O，缺点：如果列多，额外占用空间。
MySQL在进行文件排序时需要使用的临时存储空间可能会比想象的要大得多，因为MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够以容纳其中最长的字符串。
在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表时就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段会有Using filesort。除此之外的所有情况，MySQL都会将关联的结果存放在一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下Extra字段可以看到Using temporary;Using filesort。如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。
查询执行引擎
相对于查询优化，查询执行简单些了，MySQL只根据执行计划输出的指令逐步执行。指令都是调用存储引擎的API来完成，一般称为 handler API，实际上，MySQL优化阶段为每个表都创建了一个 handler 实例，用 handler 实例获取表的相关信息（列名、索引统计信息等）。
存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像搭积木一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条件的下一条目的功能，有了这两个功能就可以完成全索引扫描操作。
返回结果给客户端
查询执行的最后一个阶段就是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，例如该查询影响到的行数。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。
这样处理有两个好处：服务端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让MySQL客户端第一时间获得返回的结果。
当然，优化器存在其局限性，以及某些特定的优化类型，有兴趣的可以在书中找到答案。
MySQL查询执行器的局限性
- 子查询相对糟糕(不是绝对),如子查询用in
- 联表查询与子查询根据场景不同有不同优势
- MySQL无法将限制条件下推到子查询
- 索引合并优化
- MySQL无法利用多核特性并行执行查询
- MySQL并不支持哈希关联, MariaDB已经实现了真正的哈希关联
- 松散索引扫描,无法按照不连续的方式扫描一个索引
- 最大值最小值函数的优化一般
- 不允许同一张表上同时查询和更新, 如update set 等于 select 自己.解决方法,可以通过关联临时表
查询执行器的提示（hint）
- 设置查询优化器参数,可以阅读官方手册
- 一般除非需要,修改查询优化器参数会提高维护成本
优化特定类型查询
- 关联查询: on的列加索引; 使用group by和order by 只使用一个表的列可以利用索引
- 优化LIMIT分页: 尽量使用覆盖索引
- 子查询: 尽量使用关联查询替换
- 静态查询分析: Percona Toolkit中的pt-query-advisor能解析查询日志,分析查询模式
- 使用用户自定义变量: 无法使用查询缓存,可能被优化器优化掉
第7章 MySQL高级特性
分区表
应用
- 表非常大无法全部放在内存中,或者只在表的最后部分有热点数据其他均是历史数据
- 分区表的数据更容易维护
- 分区表的数据可以分布在不同的物理设备上
- 使用分区表避免某些瓶颈,如InnoDB单个索引的互斥访问
- 备份和恢复独立分区,对于大数据集效果较好
限制
- 一个表最多1024个分区
- 分区表达式必须是整数或返回整数的表达式
- 如果分区字段有主键或唯一索引列,那么所有主键列和唯一索引都必须包含进来
- 分区表中无法使用外键约束
原理
- 分区表由多个相关的底层表实现,存储引擎管理它们跟管理普通表一样
- select 查询: 分区层打开并锁住所有底层表,优化器判断是否过滤分区,在调用存储引擎api访问各个分区数据
- insert: 分区层打开并锁住所有底层表,确定分区,写入
- delete: 分区层打开并锁住所有底层表,确定数据所在分区,删除
- update: 分区层打开并锁住所有底层表,确定分区,取出数据,更新,确定分区,写入
- 打开并锁住所有底层表: 如果存储引擎实现行级锁如InnoDB,则会在分区层释放表锁
分区表类型
- 根据范围进行分区: 每个分区储存落在某个范围的记录
- 根据键值进行分区,减少InnoDB互斥量竞争
- 使用数学模函数进行分区,然后将数据轮询放入不同的分区,适用于只想保留几天的数据
使用
- 问题回顾:数据量很大时,除非是索引覆盖查询,否则数据库需要根据索引扫描回表查询,产生大量的随机IO,数据库响应时间很大
- 全量扫描数据不要索引,根据分区定位数据位置
- 索引数据,分离热点. 将热点数据单独放在一个分区
- NULL值会使分区过滤无效: 分区表达式接收NULL值并将其放到第一个分区导致查询时多查分区.解决方法:创建第一个无用分区存放NULL值数据
- 分区列和索引列不匹配,查询无法进行分区过滤
- 选择分区成本高,插入大量数据时都需要扫描分区定义找到分区
- 打开并锁住所有底层表的成本可能很高
- 维护分区的成本很高,同alter一样创建临时表然后拷贝数据
- 所有分区都必须使用相同的存储引擎
查询优化
- 在where条件带入分区列
- 创建分区时可以使用表达式,但是查询时只能在使用分区列本身进行比较时才能过滤分区,而不能根据表达式的值过滤分区
视图
视图本身是一个虚拟表,不存放任何数据,不能对视图创建触发器
算法
- 合并算法: 将存放的视图sql和用户发起的查询sql合并后执行
- 临时表算法: 由存放的视图sql先创建临时表后根据用户的查询sql查询返回
可更新视图
- 可以通过更新视图更新相关表, 所有临时表算法实现的视图都无法更新
视图对性能的影响
- 一般情况视图不能提升性能,在某些情况下可以帮助提升性能,需要做比较详细的测试
- 视图还可以实现基于列的权限控制不用真正创建列权限
视图的限制
- 不保存视图定义的原始sql语句
- 查看视图创建的语句,可以通过使用视图的.frm文件的最后一行获取一些信息
外键约束
- InnoDB强制外键使用索引
- 查询需要额外访问一些表,需要额外的锁容易导致一些死锁
- 如果使用外键做约束,通常在应用程序里实现会更好
内部存储代码
优点
- 离数据最近,节省带宽和网络延迟
- 帮助提升安全性,应用程序可以通过存储过程访问那些没有权限的表
- 服务器端可以缓存存储过程的执行计划
- 维护方便,便于分工
缺点
- 调试困难,难以定位问题
- 存储代码效率相对差
- 增加维护复杂性,存储过程会给数据库服务器增加额外压力
- 存在安全隐患,没有什么选项可以控制存储程序的资源消耗,所以一个小错误可能直接把服务器拖死
存储过程和函数
- 优化器无法评估存储函数的执行成本
- 每个连接都有独立的存储过程的执行计划缓存,多个连接调用同一个存储过程会浪费缓存空间反复缓存同样的执行计划
触发器
- 每个表的每个事件只能一个
- MySQL只支持基于行的触发,如果变更的数据集非常庞大的化效率会很低
- 触发器的问题很难排查
- 可能导致死锁和锁等待
- 实现一些约束,系统维护任务及更新反范式化数据的时候会比较有用
事件
- 类似Linux的定时任务
游标
- MySQL在服务器端提供只读的,单向的游标
- 一个存储过程中可以有多个游标,也可以嵌套
绑定变量
- 创建一个绑定变量sql时客户端向服务器发送了一个sql语句原型
- 服务器端解析并存储这个sql语句的部分执行计划返回客户端一个sql语句处理句柄
- 可以使用问号作为sql的占位,在使用sql接口执行时赋予变量值
插件
- 存储过程插件
- 后台插件: 如Percona Server中包含的Handler-Socket
- INFORMATION_SCHEMA插件
- 全文解析插件: 可以对文档进行分词处理
- 审计插件: 可以用作记录事件日志
- 认证插件: 扩展认证功能
字符集和校对
- 字符集是指一种从二进制编码到某类字符符号的映射
- 校对是指一组用于某个字符集的排序规则
创建对象时的默认设置
- 服务器,数据库,表都有默认的字符集和校对规则,这是一个逐层继承的默认设置
- 创建数据库时根据character_set_server设置来设定默认字符集
服务器和客户端通信设置
- 服务端总是假设客户端按照character_set_client设置的字符来传输数据和sql语句
- 服务器端收到sql语句后根据character_set_connection转换成字符串
- 服务器端返回数据时会将其转换为character_set_result
选择字符集和校对规则
- 极简原则: 先为服务器选择合理的字符集在根据实际情况让某些列选择合适的字符集
对查询的影响
- 不同字符集和校对规则之间的转换会带来额外的开销
- 排序查询要求的字符集与服务器数据的字符集相同时才能利用索引进行排序
- 当两个字符集不同列关联两个表时,MySQL会尝试转换其中一个列的字符集
全文索引
- 自然语言的全文索引: 相关度是基于匹配的关键词个数及关键词在文档中出现的次数,整个索引中出现次数越少的词语匹配的相关度越高
- 布尔全文索引: 只有MyISAM才能使用
- 平时没接触过,有兴趣者请自行google
分布式XA事务
- 事务协调器保证所有事务参与者完成工作,通知所有事务提交
- 内部XA事务: 存储引擎提交的同时,需要将提交的信息写入二进制日志
- 外部XA事务: XA事务是一种在多个服务器之间同步数据的方法,如果由于不能使用MySQL本身的复制或者性能并不是瓶颈可以尝试使用
查询缓存
- 查询缓存系统会跟踪查询中涉及的每个表,如果表发生变化则缓存数据失效
- 缓存存放在一个引用表中,通过一个哈希值引用,哈希值包括查询本身,查询数据库等信息
- 当sql语句和客户端发送过来的其他原始信息,任何字符上的不同都会导致缓存不命中
- 打开查询缓存对读和写都会带来额外的消耗
- InnoDB事务修改表时,会将这个表对应的查询缓存都设置失效
- 查询缓存被发现是一个影响服务器扩展性的因素
- 如果缓存了大量的查询结果,那么失效操作可能会造成系统僵死.因为靠一个全局锁保护,所有该操作都要等锁
- 减少碎片, 选择合适的query_cache_min_res_unit可以减少内存浪费
- 对于写密集型的应用,直接禁用更好
- 高并发环境也不适合.只有明确缓存的好处才使用
- 查询缓存的替代方案: 客户端缓存
第8章 优化服务器设置
- MySQL有大量的可以修改的参数,但不应该随便修改.应该将更多时间花在schema的优化,索引,查询设计上
- 配置文件路径: 通常在/etc/my.cnf
- 不建议动态修改变量,因为可能导致意外的副作用
- 通过基准测试迭代优化
- 具体配置项设置请参照官网手册,这里只提及部分
配置内存使用
- 确定可使用内存上限
- 每个连接使用多少内存,如排序缓冲和临时表
- 确定操作系统内存使用量
- 把剩下的分配给缓存,如InnoDB缓存池
配置MySQL的I/O行为
- 有些配置项影响如何同步数据到磁盘及如何恢复操作,这对性能影响很大,而且表现了性能和数据安全之间的平衡
InnoDB I/O配置
- 重要配置: InnoDB日志文件大小,InnoDB怎样刷新日志缓冲,InnoDB怎样执行I/O
- InnoDB使用日志减少提交事务时开销,不用每个事务提交时把缓冲池的脏块刷到磁盘中
- 事务日志可以把随机IO变成顺序IO,同时如果发生断电,InnoDB可以重放日志恢复已经提交的事务
- sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘
- 把二进制日志放到一个带有电池保护的写缓存的RAID卷可以极大的提升性能
MyISAM的I/O配置
- 因为MyISAM表每次写入都会将索引变更刷新到磁盘
- 批量操作时,通过设置delay_key_write可以延迟索引写入,可以提升性能
- 配置MyISAM怎样尝试从损坏中恢复
配置MySQL并发
InnoDB并发配置
- 如果在InnoDB并发方面有问题,解决方案通常是升级服务器
- innodb_thread_concurrency: 限制一次性可以有多少线程进入内核(根据实践取合适值)
- innodb_thread_sleep_delay: 线程第一次进入内核失败等的时间,如果还不能进入则放入等待线程队列
- innodb_commit_concurrency: 控制有多少线程可以在同一时间提交
- 使用线程池限制并发: MariaDB已经实现
MyISAM并发配置
- concurrency_insert: 配置MyISAM打开并发插入
其他
- 基于工作负载的配置: 利用工具分析并调整配置
- max_connections: 保证服务器不会因应用程序激增的连接而不堪重负
- 安全和稳定的设置: 感兴趣者请自行google
- 高级InnoDB设置: 感兴趣者请自行google
- InnoDB两个重要配置: innodb_buffer_pool_size和innodb_log_file_size
第9章 操作系统和硬件优化
（详略）