第四章 Schema与数据类型优化
4.1选择优化的数据类型
（1）更小通常更好。
（2）简单就好。使用Mysql内建的类型存储日期和时间，而不是用字符串。用整型存储IP地址。
（3）尽量避免NULL。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引，索引统计和值比较都更复杂。会使用更多的存储空间，在MySQL里面需要特殊处理。当可为NULL的列被被索引时，每个索引记录需要一个额外的字节。
通常把可为NULL修改为NOT NULL带来的性能提升比较小，一般不优先优化这个。
TIMESTAMP和DATETIME:都可以存储相同类型的数据，时间和日期，精确到秒，但是TIMESTAMP占用DATETIME一般的存储空间，会根据时区变化，并且有特殊的自动更新能力，但是允许的时间范围要小得多。
4.1.1整数类型
TINYINT(8位）、SMALLINT(16位)、MEDIUMINT(24位)、INT(32位)、BIGINT(64位)
如果加上UNSIGNED属性，表示不允许有负数，可以使正数的上限提高一倍。
指定宽度,如INT(11）、INT(20),对于应用是没有意义的，不会限制范围，只会改变显示的位数。
4.1.2实数类型
实数是带小数部分的数字。
DECIMAL(可以精确计算)、FLOAT和DOUBLE（近似计算）
浮点型和DECIMAL都可以指定精度。
尽量只在对小数进行精确计算的时候才采用DECIMAL.在数据量大的时候也可以将数据乘以一个很大的倍数，存到BIGINT类型，这样可以避免浮点型计算不精确以及DECIMAL精确计算代价高的问题。
4.1.3字符串类型
字符串的字符集，排序规则都会影响性能。
VARCHAR和CHAR
VARCHAR存储可变长字符串，比定长类型更节省空间，使用1或2个额外字节记录字符串的长度。缺点就是更新的时候会使行变得比原来长，如果一个行占用的空间增长，在页内没有更多空间，InnoDB引擎的处理是分裂页使行可以放进页内。会保留字符串末尾的空格。
CHAR是定长字符串。存储和检索时会删除末尾空格。
CHAR适合存储很短的字符串，如密码的MD5值。经常变更的字符串，因为CHAR类型不易产生碎片。
BINARY和VARBINARY存储二进制字符串（字节码）。
使用VARCHAR(5)和VARCHAR(200)存储‘hello’的空间开销是一样的，但是更长的列会消耗更多的内存，如果在使用内存临时表进行排序和操作时会消耗跟更多的内存。
BLOB（二进制）和TEXT（字符）类型：
存储很大的数据的字符串类型。
查询的时候尽量避免使用BLOB和TEXT的列作为条件，如果确实要用，应该使用部分字符串 SUBSTRING(列，长度)。如果EXPLAIN分析里面有Using temporary说明查询用到了隐式临时表。
可以使用枚举ENUM代替会经常重复字符串的列，实际上会存储为整数。排序也是使用整数排序的。
4.1.4日期和时间类型
DATETIME:从1001年到9999年，精度为秒，格式YYYYMMDDHHMMSS，与时区无关，使用8个字节的存储空间。如2008-01-16 22:37:08
TIMESTAMP：保存1970年1月1日以来的秒数。只占4个字节，范围比DATETIME小。使用FROM_UNIXTIME()函数可以把UNIX时间戳转换成日期，UNIX_TIMESTAMP()函数把日期转为UNIX时间戳。也以DATETIME的格式显示时间，但只是显示格式上的区别。TIMESTAMP可以设置一些特殊属性，插入时可以默认插入当前时间戳。
无特殊行为，尽量使用TIMESTAMP,空间效率更高。
如果要显示微妙级别的，可以使用自己的存储格式，用BIGINT类型存储微妙级别的时间戳。
4.1.5位数据类型
BIT:MySQL把BIT当做字符串类型，是包含二进制0，1的字符串，直接检索时得到的是二进制字符串对应的ASCII码的字符，如果包含数字的上下文，则得到的是二进制字符串对应的数字。如 b'00111001',直接查结果是57对应的字符‘9’，如果查‘a+0’,则结果是57.
这种机制比较迷惑，所以应该尽量避免使用这种类型。
4.1.6选择标识符
在关联的表中，关联的列使用相同的数据类型，避免操作时的隐式转换或者直接出错，导致性能问题。
整数类型是标识列最好的选择，并且可以使用自增AUTO_INCREMENT.
应该避免使用字符串作为标识列，很消耗空间，比数字类型慢。
对于随机产生的字符串，如MD5(),SHA1(),UUID()产生的字符串，这些函数产生的新值会分布在很大的空间内，这会导致INSERT和一些SELECT语句变得很慢。
4.2MySQL schema设计中的陷阱
4.3范式和反范式
范式化的schema有优点，比如更新操作要快，重复数据更少，可以减少DISTINCT和group by操作。缺点是稍微复杂的查询可能都需要至少一次关联，这样代价高，并且容易使索引失效。
例子：假如有一个网站，允许用户发送信息，并且一些是付费用户，查询付费用户的最近10条消息。
范式化schema的查询：关联查询
反范式化schema，比如将accout_type和published字段合并到message表中，创建复合索引（account_type,published）便可以不用关联，高效的查询。
4.3.3混用范式化和反范式化
在不同的表中存储相同的特定列，有利于高效查询，但又不至于完全反范式化的插入和删除问题。
4.4汇总表和缓存表
在同一张表中保存衍生的冗余数据可以提升性能。
缓存表指可以简单的从schema其他表中获取的数据的表。
汇总表保存的是GROUP BY语句聚合数据的表。
例子：获取24小时内获取的消息数，通过实时维护message_cnt字段会效率很低，实时计算统计是很昂贵的操作，可以每小时生成一个汇总表，以每小时的汇总表为基础，查询。
缓存表是对优化搜索和检索查询语句很有效。
4.4.1物化视图
预先计算并存储在磁盘上的表，可以通过各种策略进行刷新和更新。
4.4.2计数器表
单独创建一张计数表，缓存朋友数量，网站点击数量等，可以更快的查询并且不会让缓存失效。
为了获得更高的并发性能，可以预先在表上增加100行数据，每次更新的额时候随机选择一个槽进行更新。
为了提升读查询的速度，经常会需要建一些额外的索引，增加冗余列，甚至创建缓存表和汇总表。虽然写操作变慢了，但更加显著的提高了读操作的性能。
4.5加快ALTER TABLE操作的速度
对大表，alter table的操作可能需要数个小时甚至数天才能完成。一般来说，alter table操作会导致mysql服务中断，解决方法有：先在一台不服务的机器上完成修改表的操作，再和主库进行切换；另一种方法是影子拷贝，创建一张表，通过重命名和删表操作交换两张表。
如果是只改变某个列的默认值，可以使用alter column的操作直接修改.frm文件里列的默认值，不用拷贝整个表。
4.5.1只修改.frm文件
在某些情况下，只修改.frm文件可以达到修改表的目的，而不用复制整张表：
比如：移除一个列的自增属性、增加修改ENUM或SET常量
步骤：1、创建相同结构的额空表，并进行修改2、执行FLUSH TABLE WITH READ LOCK,关闭所有正在使用的表，禁止任何表打开3、交换.frm文件4、执行UNLOCK.
4.5.2快速创建MyISAM索引
创建新的表加入索引，获取读锁，重命名交换，释放读锁，重建索引
4.6总结
1、使用小而简单的数据类型
2、关联条件中使用的列用相同的类型
3、注意可变长字符串
4、尽量使用整型定义标识列
5、小心使用ENUM和SET,避免使用BIT
6、混用范式和反范式
7、ALTER TABLE 的一些快速的方法。
第五章 创建高性能的索引
索引是存储引擎用于快速找到记录的一种数据结构。索引优化是对查询性能优化最好的手段，索引能够轻易将查询性能提高几个数量级。
在在索引中找到对应值，再根据匹配的索引记录找到对应的数据行。
索引可以包含一个或多个列的值，如果包含多个列，列的顺序㛑十分重要，因为MySQL只能高效的使用索引的最左前缀列。
即便是使用ORM框架也需要关心索引。
5.1索引基础
5.1.1索引类型
B-Tree索引
所有的值都是按照顺序存储的，每一个叶子页到根的距离相同。
存储引擎不需要再扫描全表，从索引的根节点开始搜索，根据指针往下层节点找。叶子节点的指针指向的是被索引的数据，而不是其他节点页。
索引对于多个值的排序的依据是定义索引时的列的顺序。
可以使用索引的查询类型：
1、全值匹配：包含索引中的所有列
2、匹配最左前缀
3、匹配列前缀
4、匹配范围值
5、精确匹配某一列并范围匹配另一列
6、只访问索引的查询（覆盖索引）
索引除了安值查找之外还可以用于查询中的ORDER BY操作。
使用索引的限制：
1、如果不是按照索引的最左列开始查找，则无法使用索引。
2、不能跳过索引中的列
3、如果查询中有某个列是范围查询，则其右边的所有列无法用索引优化查找。
哈希索引
空间数据索引
不会使用
全文索引
查找文本中的关键词，不是直接比较索引中的值。
5.2索引的优点
1、索引大大减少了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机IO变为顺序IO.
对非常小的表，全表扫描更合适，对中大型表，索引非常有效，特大表，索引的建立和使用代价也会增长。
5.3高性能的索引策略
正确创建和使用索引是实现高性能查询的基础。
5.3.1独立的列
索引列不能是表达式的一部分，也不能是函数的参数。
5.3.2前缀索引和索引选择性
有时需要索引很长的字符列，会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，提高索引效率，但同时也会降低索引的选择性。选择性只不重复的索引值与数据表记录总数的比值。索引选择性越高，查询的效率越高。因为选择性高的索引可以过滤掉更多的行。唯一索引的选择性是1.性能最好。
对于BLOB、TEXT或者很长的VARCHAR,必须使用列前缀索引，通常也是足以满足查询性能的。
保证选择性的同时又不能太长。可以先找最常见值的列表，然后和最常见的前缀列表进行比较。
逐步调试，找到合适的前缀长度达到比较好的选择性。
前缀索引能使索引更小更快，但缺点是无法用前缀索引做order by和group by,也无法用前缀索引做覆盖扫描。
5.3.3多列索引
为每个列创建独立的索引是不正确的，大部分情况不能提高查询性能。
在MySQL5.0之后查询可以同时使用多个单列索引进行扫描，并将结果合并。OR,AND
索引合并是一种优化优化，但也说明了当前的索引建的很糟糕。
5.3.4选择合适的索引列顺序
正确的顺序依赖于使用该索引的查询，并且同时要考虑如何满足排序和分组的需要。
经验法则是，当不需要考虑排序和分组的时候，将选择性最高的列放在前面通常是很好的。
根据哪些运行频率最高的查询来调整索引列的顺序，这种情况索引的选择性是最高的。
5.3.5聚簇索引
聚簇索引不是一种单独的索引类型而是一种数据存储方式，InnoDB的聚簇索引在同一结构中保存了BTree索引和数据行。
当表有聚簇索引时，数据行实际上就是存放在索引的叶子页中。一个表只能有一个聚簇索引。InnoDB通过主键聚集索引。
使用聚簇索引的优点：
1、减少磁盘IO
2、数据访问更快
3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值
缺点：
1、如果数据都在内存里，都是随机IO,访问顺序没那么重要，就没必要用聚簇索引了。
2、插入的速度严重依赖于插入的顺序，按照主键顺序插入是最快的方式。
3、代价高，会强制InnoDB将每个被更新的行移动到新的位置。
4、基于聚簇索引的表在插入新行或者主键被更新需要移动行时可能会导致“页分裂”的问题。将一行插入到一个已满的页时，存储引擎会将该页分裂成两个页面，页分裂会导致表占用更多的磁盘空间。
5、可能导致全表扫描变慢，行稀疏的时候。
6、二级索引更大，因为二级索引的叶子节点包含了引用行的主键。
7、二级索引需要两次索引。因为二级索引的叶节点存的是主键值，再通过主键值到聚簇索引中找对应的行。
避免随机的聚簇索引，使用UUID做聚簇索引会很糟糕。因为这会导致随机插入，使数据没有聚集特性。按照顺序插入，可以直接插到后面，随机的插入花销更大。
随机插入的缺点：
1、写入的目标页可能已经刷到磁盘，导致要重新从磁盘读取目标页，导致了大量的随机IO
2、写入顺序是乱的，InnoDB不得不频繁做页分裂操作，导致数据的移动，降低效率
3、页分裂会导致碎片的产生
所以，应该尽可能的按照主键顺序插入数据。
顺序主键在高并发的负载下会产生争用。主键的上界会成为热点。并发插入会导致间隙锁竞争，另外一个是自增的锁机制。这个问题要重新设计表或者更改innodb_autoinc_lock_mode配置。
5.6.3覆盖索引
通过索引直接获取列数据，不用读取数据行。对于二级索引，可以减少对主键的二次查询。
如果使用了覆盖索引，EXPLAIN分析会显示Using index
如果索引不能覆盖查询所需的全部列，那就不得不扫描一条记录就回表查询一次对应的行。
覆盖索引的陷阱：MySQL查询优化器会判断是否有一个索引能进行覆盖，如果不能覆盖，就会找数据行。
例子：
查询所有列，无法使用覆盖索引，like关键字在使用索引时也只支持最左前缀匹配。
巧妙设置索引，重写查询。将索引覆盖三个数据列（artist,title,prod_id）
这种歌方法叫延迟关联，延迟了对列的访问。第一阶段，在from子句，通过覆盖索引查找所有的符合条件的prod_id,然后根据prod_id在外层查询匹配所有的列值，虽然不能索引覆盖整个查询，但比完全无法使用索引覆盖好。
因为二级索引的叶子节点存储了主键，所以二级索引可以对主键列做覆盖查询。
5.3.7 使用索引扫描做排序
MySQl有两种方法生成有序的结果：1、排序操作；2、按照索引顺序扫描
如果使用了按索引顺序扫描，那么EXPLAIN的type的结果就是index，这和extra项里的Using index不一样，Using index是说明使用了覆盖索引。
只有当索引的列和顺序和Order by子句中的顺序完全一致，并且所有列的排序方向（ASC,DESC）都一样时。这样可以使用索引对结果进行排序。如果需要关联多个表时，order by子句引用的字段必须全为第一个表的字段才能做索引排序。order by也要满足最左前缀
有种特殊情况就是如果order by不满足最左前缀，但是排序列的前导列是常量，这样也可以使用索引来排序。
5.3.8压缩索引
5.3.9冗余和重复索引
重复索引是在相同的列上按照相同的顺序创建相同类型的索引。
冗余索引就是如果有了复合索引（col1,col2），那个索引（col1）就是冗余的。
（col1,ID）这种也是冗余的，因为二级索引包含了主键列。
where A=5 order by ID.可以用索引（A）排序,但不能用索引（A,B）排序。
5.3.10未使用的索引
5.3.11索引和锁
索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对行加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。如果在存储引擎层不能过滤掉的行到了MySQL服务层就不能避免锁定行了。
extra出现using where 表明MySQL服务器将存储引擎返回行后再应用where过滤。底层存储引擎只根据id<5,过滤了其他的行，返回给服务层的是id<5的行，服务层再用后面的where条件再进行过滤，所以id=1的行也是被加上了排他锁的。
说明即使使用了索引也会导致多余的行锁定，如果不适用索引，全表扫描，那将导致锁定所有的行。InnoDB在二级索引上使用读锁，即共享锁，在主键索引，即查找聚簇索引是使用写锁，即排他锁。
5.4索引案例学习
案例：一个在线交友网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色等，网站必须支持上面这些特征的各种组合来搜索用户，还允许根据用户最后在线时间、其他会员的评分等对用户进行排序和对结果限制。
首先要考虑用索引排序还是先检索数据再排序，用索引排序将会严格限制索引和排序的设计。
5.4.1支持多种过滤条件
首先，国家和性别这两列选择性都不高，但是在查询中会经常用到。可以将（性别，国家）作为索引前缀。这与我们之前说的选择性地的列不适合索引冲突了吗？其实当我们如果不需要选择性别，我们可以使用IN的操作，来让查询跳过性别这一项，同时能用到索引之后的列。比如可以在查询条件里加入SEX IN('m','f')，让查询选择到该索引，这样写不会过滤任何行，但是加上这一列的条件会让MySQL匹配索引的最左前缀。
最常用的范围查询的列放在索引的最后
当设计索引时，应该考虑表上所有的选项，不要只为现有的查询考虑需要哪些索引。应该同时优化查询和索引找到最佳的平衡。
5.6总结
选择索引和利用这些索引时的三个原则：
1、单行访问是很慢的。
2、按顺序访问范围数据是很快的，因为顺序IO不需要多次磁盘寻道，其次按照顺序读取数据不需要额外的排序操作。
3、索引覆盖查询是很快的，因为不需要回表查找行。
如何判断一个系统创建的索引是否合理？
按照响应时间对查询进行分析，找出消耗时间最长的查询，分析查询是否扫描了太多行，是否做了额外排序或使用了临时表，是否使用了随机IO访问数据，或者是否有太多的回表查询哪些不在索引中的列的操作。
第六章查询性能优化
查询优化、索引优化、库表结构优化要齐头并进。
6.1为什么查询速度会慢
真正重要的是响应时间。查询由一系列子任务组成，优化查询，要么消除一些子任务，要么减少子任务的次数，要么让子任务更快运行。
查询的生命周期：从客户端，到服务器，解析，生成执行计划，执行，返回客户端。
执行里面包括了存储引擎的调用，以及调用后数据的处理（排序、分组等）。
查询要在不同的地方花费时间，包括网络、CPU计算、生成统计信息和执行计划，等待锁等。在存储引擎检索数据的时候，需要操作内存、CPU,内存不足的时候，IO也要消耗更多的时间。
6.2慢查询基础
对低效的查询，主要分析：1、确定应用程序是否检索了大量超过需要的数据；2、确定MySQL服务器是否分析了大量超过需要的数据行。
6.2.1是否向服务器请求了不需要的数据
查询了不需要的记录
比如说，只查询某一页数据，但是实际查询了所有的，只显示第一页。解决办法是加LIMIT.
多表关联时返回了全部的列
返回多个表的全部列是不需要的。
总是取出全部列
每次都select *,首先可能有的列不是必须的，其次查询所有列将不能使用覆盖索引优化查询。
如果清楚这样做的代价，并使用了其他的解决方法，也是可以的。比如使用了缓存的机制，查询了所有列有时也会带来方便。
重复查询相同的数据
比如用户头像的URL,可以不用每次都查询，可以使用缓存。
6.2.2MySQL是否在扫描额外的记录
对MySQL，简单的衡量查询开销的三个指标：
1、响应时间；2、扫描的行数；3、返回的行数
这三个指标会记录到慢查询日志里面。
响应时间
响应时间=服务时间（真正查询使用的时间）+排队时间（等待IO,等待锁的时间）
扫描的行数和返回的行数
理性状态，扫描行和返回行应该是1比1，但是通常做一个关联查询时必须要扫描多行才能得到一行结果，比值在1:1到10:1，甚至更大。
扫描的行数和访问类型
访问类型就是EXPLAIN测试中的type的类型：扫描的范围从大到小的顺序是：全表扫描（ALL）、索引扫描(index)、范围扫描(range)、唯一索引扫描(ref)、常数引用(const)等。
索引可以让存储引擎扫描更少的数据行，以提高查询性能.
MySQL使用where条件有三种方式，从好到坏：
1、在索引中使用where条件，这样在存储引擎层就能过滤不匹配的记录，使扫描的数据行减少。
2、使用索引覆盖扫描返回记录，直接在索引中过滤过滤不需要的记录，而且不用回表再查询，这是在MySQL服务层实现的。
3、从数据表返回数据，再再MySQL服务层使用where条件过滤，这是最差的情况，扫描的行数是最多的，在extra选项，会出现using where.
6.3重构查询的方式
1、可以将查询改变一种写法，让他返回一样的结果但是性能更好。
2、在业务层修改代码，使用另一种查询方式。达到一样的目的。
6.3.1一个复杂查询还是多个简单查询
6.3.2切分查询
6.3.3分解关联查询
对关联查询进行分解，将一个关联查询分解成多个单表的查询会有以下的好处：
1、更有效的使用缓存。关联查询一但有表发生变化，缓存将失效。分成多个单表查询可以尽可能保留一些可用的缓存。
2、查询分解之后，执行单个查询可以减少锁的竞争。
3、在应用层做关联，更容易对数据库进行拆分，更容易做到高性能和可扩展。
4、减少冗余的查询
在很多场景下，通过重构查询将关联放在应用程序里会更加高效。
6.4查询执行的基础
执行查询的过程：先查缓存，缓存没有的话到解析器，解析SQL,预处理器，到查询优化器，生成执行计划，再到存储引擎调用API返回结果。
6.4.1MySQL客户端/服务器通信协议
“半双工”
MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能早点结束，早点释放响应的资源。
当需要返回一个很大的结果集时，缓存到内存会花费很多时间并消耗内存，这时可以直接处理，不用缓存记录结果，但是缺点是，服务端要一直等待查询完成。
查询状态
查询线程的状态：
Sleep:等待客户端发送请求
Query:正在执行查询或发送结果给客户端
locked:等待表锁，等待InnoDB的行锁不会体现在线程状态里
Analyzing and statistics:统计信息生成执行计划
Copying to tmp table [on disk]:将查询结果放到临时表，通常GROUP BY ,文件排序，或者UNION操作.
Sorting result:线程对查询结果排序
Sending data：可能在多个状态发送数据，或者在生成结果集，或者向客户端返回结果。
6.4.2查询缓存
如果查询命中缓存，如果权限没有问题，将不执行后面的步骤，直接从缓存中拿结果返回。
6.4.3查询优化处理
将SQL转换成一个执行计划，与存储引擎交互。包括解析SQL、预处理、优化SQL执行计划
语法解析器和预处理：生成一颗解析数，语法规则验证和解析查询，预处理会验证权限
查询优化器
一条查询会有多种执行计划，优化器找到最好的一种。优化器会预测成本，依次来选择，计算成本的时候不会考虑缓存。
MySQL的优化器选择的最优与我们想要的最优可能不一样，而且它不会考虑并发执行的查询对当前查询的影响。优化器的策略有静态优化和动态优化。
MySQL可以处理的优化类型：
1、重新定义关联表的顺序
2、将外连接转化为内连接
3、使用等价变换规则
4、优化count(*)、min()和max()
5、预估并转化为常数表达式
6、覆盖索引扫描
7、子查询优化
8、提前终止查询：LIMIT
9、等值传播
10、列表IN()的比较：IN()和多个OR意思是相同的，但是MySQL对IN()的处理是先对IN（）列表排序，再使用二分查找确定是否满足，时间复杂度logn,比OR的n快。
数据和索引的统计信息
数据和索引的额统计信息是在存储引擎里的，生成执行计划时要向存储引擎获取这些信息。
MySQL如何执行关联查询
UNION的例子。
先将单个查询放到一个临时表，然后再重新读出临时表数据完成UNION查询，执行嵌套循环关联。
执行计划
生成一颗指令树，执行指令树返回结果。
关联查询优化器
选择代价最小的关联顺序，进行更少的循环嵌套和回溯操作。
排序优化
应避免排序或避免对大量数据排序。
如果不能用索引生成排序结果，就要执行排序filesort。
如果数据量小于“排序缓冲区”，就直接在内存快速排序，如果大于缓冲区，要先分块，各个排序然后合并，再返回。
MySQL的两种排序算法：
（1）两次传输排序（旧版本）：读取航指针和需要排序的列，排序完了再回表读数据行。第二次回表是随机IO,效率低。
（2）单次传输排序（新版本使用）：先读取所有查询需要的列，对需要排序的列排序，然后直接返回。只有一次顺序IO.但会占用更多的空间。可以设置参数max_length_for_sort_data.只要所有列不超过这个空间，使用单次传输是更有效的，否则也会出现分块，分别排序的操作，降低效率。
MySQL在进行文件排序时需要的临时存储空间比想象的要大得多。
关联查询中如果group by子句的所有列都来自关联的第一个表，那么MySQL在关联查询第一个表时就会filesort,除此之外是放到一个临时表，在关联结束后再filesort,这时extra会有Using temporary，Using filesort.
查询执行引擎
执行计划生成后，调用存储引擎的handler API接口，逐步执行
返回结果给客户端
查询结果返回给客户端，如果查询可以被缓存，那么也会把结果存放到缓存里
6.5MySQL查询优化器的局限性
6.5.1关联子查询
如何用好关联子查询
6.5.2UNION的限制
6.5.3索引合并优化
6.5.4等值传递
6.5.5并行执行
MySQL无法利用多核特性来并行执行查询
6.5.6哈希关联
MySQL不支持
6.5.7松散索引扫描
MySQL不支持
6.5.8最大值和最小值优化
例如
first_name 没有建索引，将会全表扫描，对主键扫描，理论上找到第一条满足条件的就返回了最小值，因为主键是按大小排序的。但是MySQL还是会做全表扫描。
可以换一种写法：
虽然不能再SQL语句上看到我们查询的意图，但是可以减少扫描的行数。
6.5.9同一个表上的查询与更新
改成生成表的形式
6.7优化特定类型的查询
6.7.1优化count()查询
COUNT()的作用
统计不为NULL的列值的数量或者统计行数。
如果COUNT()的括号中有列和列的表达式，则表示的是统计表达式有值的结果数。
COUNT（*）不会扩展到所有的列，会直接统计行数。
如果MySQL知道某个列不可能为NULL,就会把COUNT（列）优化为COUNT（*）
MyISAM对COUNT（*）的性能优势只体现在没有where条件的情况。
有时查询id>某个值，可以转换成查询id<某个值的查询来转换，减少扫描的行数。
6.7.2优化关联查询
1、确保ON或者Using子句中的列上有索引。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
2、确保任何group by和order by中的表达式只涉及到一个表中的列，这样才有可能用到索引。
3、升级的时候注意
6.7.3优化子查询
子查询尽可能的用关联查询代替
6.7.4优化GROUP BY 和DISTINCT
如果无法只用索引，group by使用两种策略完成：使用临时表或者文件排序做分组。
如果需要对关联查询做分组，并且是按照查找表的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高，例如：
这么做的前提是查询的列是直接依赖于分组的列，并且是唯一的，不会影响结果。
在group by之后不要排序，默认不写order by的话会导致默认的排序，所以可以加上order by null,分组后不再排序。
“延迟关联”：让MySQL先扫描尽可能少的页面，再回原表查询所有列。
6.7.7优化UNION查询
第七章MySQL高级特性
7.1分区表
分区表是一个独立的逻辑表，底层由多个物理子表组成。
分区的一个主要目的就是将数据按照一个较粗的粒度分在不同的表中。这样可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变的很方便。
分区表的限制：
1、一个表最多只能有1024个分区
2、如果分区表中有主键或者唯一索引的列，那么所有的唯一索引列都必须包含进来
3、分区表无法使用外键约束。
7.1.1分区表的原理
7.1.2分区表的类型
最多的是根据范围分区，分区表达式可以是列也可以是包含列的表达式。
MySQL还支持键值、哈希和列表分区。
7.1.3如何使用分区表
在数据量超大的时候，B-Tree索引就无法起作用的，除非是覆盖索引，否则根据索引回表查询将差生大量的随机IO,索引的维护成本也非常大。所以要分区，以非常小的代价定位到一小片区域，在这片区域可以顺序扫描，可以建索引，可以全部缓存到内存。
因为分区无须额外的数据结构记录分区有哪些数据，分区也不需要精确定位每一条数据位置，所以代价非常低。
为保证大数据量的可扩展性，有两种策略：
1、全量扫描数据，不要任何索引
2、索引数据，并分离热点。将这部分数据单独放在一个分区中，让这个分区的数据有机会缓存到内存中。这样查询只用访问一个很小的分区表，能够使用索引，也能有效使用缓存。
7.1.4什么情况下会出问题
上面的两个分区策略基于两个假设：查询能够过滤大量的分区，分区本身代价很小。
但是在一下情况会出问题：
1、NULL值会使分区过滤无效
因为分区表达式的值可以是NULL,有第一个分区是专门存分区表达式是NULL或者非法值的地方。所以查找分区的时候会找到两个分区，一个是第一个分区，一个是符合过滤条件的分区。这时，入股第一个分区非常大，没有索引就会代价很大。为了解决可以创建一个无用的分区，这样即使要检测它也是空的，MySQL5.5之后可以直接使用列本身分区。
2、分区列和索引列不匹配
3、扫描分区的索引可能很高
4、打开并锁住底层表的成本可能很高
5、维护分区的成本可能很高
7.1.5查询优化
分区给查询优化带来了新的思路，用粗粒度的索引使查询扫描更少的行。很重要的一点就是查询的时候要带上分区列，即使多余也要带上。如果过滤条件是列的表达式，那也不能过滤分区。
即便在创建分区的时候使用了列的表达式，但是查询的时候必须使用列来过滤
7.1.6合并表
7.2视图
视图本身是虚拟的表，不存放任何数据，它返回的数据是MySQL从其他表中生成的。
实现视图最简单的方法就是将select的结果放在临时表中，当访问视图的时候直接仿问这个临时表就可以了。
MySQL可以使用两种方法处理视图，合并算法和临时表算法。如果可能尽量使用合并算法，如果使用了临时表算法，EPLAIN中会显示派生表（DERIVED）
7.2.1可更新视图
可以通过更新视图来更新涉及的表。
7.2.2视图对性能的影响
某些情况可以提升性能
7.2.3视图的限制
不会保存视图定义的原始SQL
7.3外键约束
InnoDB是MySQL中唯一支持外键的内置存储引擎。
使用外键是有成本的。在每次修改数据时都要在另一张表执行一次查找操作，虽然InnoDB强制外键索引，但是还是会有开销，如果外键的选择性很低，效率将更低。
如果想要确保两个相关的表始终有一致的数据，使用外键会比在应用程序中检查一致性性能要高的多。此外外键在相关的数据的删除和更新上也比在应用层中维护要高效，不过外键操作时逐行进行的。
外键约束会在查询时会对另外的表的记录加锁，这样会导致额外的锁等待，可能导致一些死锁，并且难以排查，有时可以用触发器代替外键。对于相关数据的同时更新，外键更加合适，但如果只是约束数值，可以用触发器，也可以直接用ENUM类型。
如果只是用外键约束，那可以不用外键，在应用层实现约束。外键会带来很大的额外消耗。
7.4在MySQL内部存储代码
在MySQL里面可以用触发器、存储过程、函数的形式存储代码。
优点：1、存储代码就在服务器内部执行，离数据最近，可以节省带宽和网络延迟
2、代码重用，方便统一业务规则
3、简化代码维护 和更新
4、提升安全，做更细粒度的权限控制。通过存储过程访问那些没有权限的表。
缺点：
1、编写存储代码没有很好的工具
2、存储过程中的代码功能要比应用层代码差
3、给部署带来复杂性
4、消耗服务器资源，调试困难
7.4.1存储过程和函数
7.4.2触发器
可以让你在Insert、Update或者Delete的时候执行一些特殊操作，可以指定触发器在SQL之前还是之后触发，可以读取或者改变SQL语句所影响的数据。所以可以用触发器实现一些强制限制，或者一些业务逻辑。用触发器设计简单的业务逻辑可以减少客户服务端之间的通信，提高性能。
但是触发器，一个表的一个事件只能有一个触发器，而且触发器是针对于行的触发，就是一条记录。
在InnoDB表上的触发器是在同一个事务中完成的，她的执行操作是原子的，与原SQL操作同时成功或失败。可以用触发器做一些数据更改的日志的记录操作。但是在维护数据一致性的时候，还是不要使用触发器，小心MVCC,稍不小心就会得到错误的结果。
7.4.3事件
CALL调用。
7.5游标
7.6绑定变量
7.7用户自定义函数
7.8插件
7.9字符集和校对
字符集是指一种从二进制编码到某类字符符号的映射。每个字符集可能有多种校对规则
7.10全文索引
关键字匹配进行查询过滤，非精确过滤。
自然语言的全文索引
MATCH AGAINST
布尔全文索引
7.11分布式事务（XA）
分布式事务让存储引擎级别的ACID可以扩展到数据库层面。甚至可以扩展到多个数据库之间。需要两个阶段提交实现：
1、是需要一个事务协调器保证所有事务的参与者都完成准备工作
2、协调器收到准备好的消息，通知所有事务提交。
7.11.1内部分布式事务
跨存储引擎
7.11.2外部分布式事务
是一种在多个服务器之间同步数据的方法
7.12查询缓存
可以缓存查询的执行计划。
MySQL还可以缓存select的查询结果。查询缓存会跟踪查询中涉及的表，一旦表变化，缓存将失效。这种实现代价小。
查询缓存被发现是一个影响服务器扩展性的因素。很多时候默认应该关闭缓存，或者配置一个很小的缓存空间（几十兆）
7.12.1MySQL如何判断缓存命中
缓存放在一个引用表，通过哈希值引用，哈希值包含查询、数据库、版本等可能会影响返回结果的信息。
如果查询中包含任何不确定的函数，那么在查询缓存中是不可能找到缓存结果的。
缓存对于读写都有性能影响，然后查询缓存的操作是一个排他的操作，会带来额外的消耗。
在事务运行时会限制缓存的使用。
7.12.2查询缓存如何使用内存
完全存储在内存中。缓存的管理维护的数据结构大概花40KB的内存资源。服务器启动的时候先初始化缓存需要的内存，当有查询结果需要缓存的时候，MySQL无法为每个查询结果分配合适的空间。而且分配空间的操作会锁住内存块。
7.12.3什么情况下缓存能发挥作用
打开或者关闭系统缓存，来比较系统效率。
7.12.5InnoDB和查询缓存
InnoDB有自己的MVCC.
只有大于表的计数器的事务才可以使用查询缓存。
InnoDB让所有有加锁操作的事务都不使用任何查询缓存。
7.12.6通用查询缓存优化
第十章复制
MySQL的复制功能是构建基于MySQL的大规模、高性能应用的基础。
可以为服务器配置一个或多个备库的方式，进行数据同步，有利于实现高可用性、可扩展性、容灾恢复、备份以及数据仓库等工作。
1、复制如何工作
2、复制服务搭建
3、复制的配置以及管理优化
10.1复制概述
MySQL有两种复制方式：基于行的复制和基于语句的复制。都是在主库上记录二进制日志，在备库上异步重现，实现异步的数据复制。
