The following content is a reference of High Performance MySQL, 4th Edition
- 最上层的客户端所包含的服务负责连接处理、身份验证、确保安全等。
- 大多数MySQL的核心功能存在于第二层，包括查询解析、分析、优化、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现：存储过程、触发器、视图等。
- 第三层是存储引擎层。存储引擎负责MySQL中数据的存储和提取。
MySQL do concurrency control at two levels:
- the server level.
- the storage-engine level.
two types of lock:
- Shared lock, or read lock.
- Execusive lock, or write locks.
锁定策略是锁开销和数据安全性之间的平衡，这种平衡会影响性能。
表锁是MySQL中最基本的也是开销最小的锁策略。
表锁有一些变体，可以在某些特定情况下支持某些类型的并发写操作。写锁队列和读锁队列是分开的，但写锁队列的优先级绝对高于读队列。
使用行级锁可以最大程度地支持并发处理（也带来了最大的开销）。
行级锁是在存储引擎而不是服务器中实现的。服务器通常不清楚存储引擎中锁的实现方式。
事务应满足的标准：
- Atomicity。一个事务必须被视为一个不可分割的工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚。
- Consistency。数据库总是从一个一致性状态转换到下一个一致性状态。
- Isolation。通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
- Durability。一旦提交，事务所做的修改就会被永久保存到数据库中。
- Serializable
最高级别的隔离性，对事务所有读、写的数据全都加上读锁、写锁和范围锁即可做到可串行化。
- Repeatable Read
对事务所涉及的数据加读锁和写锁，且一致持有至事务结束，但不再加范围锁。比可串行化弱化的地方在于幻读问题（Phantom Reads），指事务在执行过程中，两个完全相同的范围查询得到了不同的结果集。
具体的数据库实现并不一定会遵照理论去实现。如MySQL/InnoDB的默认隔离级别是可重复读，但它在只读事务中可以完全避免幻读问题，读写事务中，MySQL依旧会出现幻读问题。
- Commited Read
对事务涉及的数据加的写锁会一直持续到事务结束，但加的读锁在查询操作完成后就马上释放。比可重复读弱化的地方在于不可重复读问题（Non-Repeatable Reads），指在事务执行过程中，对同一行数据的两次查询得到了不同的结果。
- Uncommitted Read
对事务涉及的数据只加写锁，会一直持续到事务结束，但完全不加读锁。比已提交读弱化的地方在于脏读问题（Dirty Reads），指在事务执行过程中，一个事务读取到另一个事务未提交的数据。
MySQL中的大多数事务型存储引擎使用的都不是简单的行级锁机制。
MVCC可以被认为是行级锁的一个变种，但是它在很多情况下避免了加锁操作。它们会将行级锁和可以提高并发性的多版本并发控制技术结合使用，因此开销很低。根据其实现方式，不仅实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的工作原理是使用数据在某个时间点的快照实现的。这意味着无论事务运行多长时间，都可以看到数据的一致性视图，也意味着不同的事务可以在同一时间看到同一张表的不同数据。
每个存储引擎实现MVCC的方式都不同。其中一些变体包括乐观并发控制和悲观并发控制。
下图所示的序列图解释了InnoDB对MVCC的实现方式的行为：
InnoDB通过为每个事务在启动时分配一个事务ID来实现MVCC。该ID在事务首次读取任何数据时分配。在该事务中修改记录时，将向Undo日志写入一条说明如何恢复该更改的Undo记录，且事务的回滚指针指向该Undo日志记录。
当不同的会话读取聚簇主键索引记录时，InnoDB 会将该记录的事务 ID 与该会话的读取视图进行比较。如果当前状态下的记录不应可见（更改它的事务尚未提交），则 Undo 日志记录将被跟踪并应用，直到会话达到一个符合可见条件的事务ID。该过程可以一直循环到完全删除这一行的 Undo 记录，然后向读取视图发出这一行不存在的信号。
事务中的记录可以通过在记录的“info flags”中设置“deleted”位来删除。
所有Undo日志写入也都会写入Redo日志，因为Undo日志写入时服务器是服务器崩溃恢复过程的一部分，并且是事务性的。
在记录中保留这些额外信息带来的结果是，大多数读取查询都不再需要获取锁。缺点是存储引擎必须在每一行中存储更多的数据，在检查行时需要做更多的工作，并处理一些额外的内部操作。
MVCC仅适用于 REPEATABLE READ 和 READ COMMITTED 隔离级别。READ UNCOMMITTED 与MVCC不兼容，是因为查询不会读取适合其事务版本的行版本，而是不管怎样都读最新版本。SERIALIZABLE 与MVCC也不兼容，是因为读取会锁定它们返回的每一行。
MySQL被设计用于在任何给定时间只在一个节点上接受写操作。这在管理一致性方面具有优势，但在需要将数据写入多台服务器或多个地区时，会导致需要做出取舍。MySQL提供了一种原生方式来将一个节点执行的写操作分发到其他节点，这被称为复制。源节点伟每个副本提供一个线程，该线程作为复制客户端登录，当写入发生时会被唤醒，发送新数据。
在8.0版本中，MySQL将表的元数据重新设计为一种数据字典，包含在表的.ibd文件中。这使得表结构上的信息支持事务和原子级数据定义更改。
- 尽量使用可以正确存储数据的最小数据类型。
- 简单数据类型的操作通常需要更少的CPU周期。
- 尽量避免NULL。
整数类型有：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT，分别使用8、16、24、32、64位存储空间。可以存储的取值范围从-2^(N-1)到2^(N-1)-1。
FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。
DECIMAL类型用于存储精确的小数。
浮点类型再存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节。
因为需要额外的空间和计算开销，索引应尽量旨在对小数进行精确计算时才使用DECIMAL。但在数据量较大时，可以考虑使用BIGINT代替DECIMAL，将需要存储的数据根据小数的位数乘以相应的倍数即可。
VARCHAR and CHAR types
VARCHAR用于存储可变长字符串。它比定长类型更节省空间。但当MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储。
VARCHAR需要使用1或2个额外字节（列长小于等于255字节为1，否则为2）记录字符串的长度。
VARCHAR的适用场景：
- 字符串列最大长度比平均长度大很多；
- 列的更新很少，碎片不是问题；
- 使用了像UTF-8这样复杂的字符集，每个字符都是用不同的字节数进行存储。
CHAR是定长的。当存储CHAR值时，MySQL会删除所有的末尾空格，更具需要采用空格进行填充以方便比较。
CHAR的适用场景：
- 很短的字符串，或者所有值都接军同一个长度；
- 经常变更的数据。
BLOB and TEXT types
BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
MySQL把每个BLOB和TEXT值当作一个独立的对象处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后再外部区域存储实际的值。
MySQL根据max_sort_length的配置对BLOB和TEXT的前max_sort_length个字符进行排序。
Using ENUM instead of a string type
MySQL会根据列表值的数量将枚举值压缩到一两个字节中，MySQL会在内部将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
枚举的缺点：
- 字符串列表是固定的，添加或删除字符串必须重建整个表。
- 枚举值必须经过查找才能转换成字符串，有一定开销。
MySQL可以使用许多类型来保存日期和时间值。MySQL支持的最小时间粒度为秒，但是MySQL也可以使用微秒级的粒度进行临时计算。
DATETIME 该类型能保存大范围的值，从1001年到9999年，精度为秒。它将日期和时间封装到格式为yyyyMMddHHmmss的整数中，与时区无关。使用8个字节的存储空间。 TIMESTAMP TIMESTAMP类型保存了从1970年1月1日（格林尼治时间）以来的秒数，和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间，范围比DATETIME小得多：只能表示从1970年到2038年。MySQL提供了FROM_UNIXTIME()、UNIX_TIMESTAMP()函数分别进行UNIX时间戳和日期的互相转换。
MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些为类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。
在为标识符选择类型时，不仅需要考虑存储类型，还需要考虑MySQL如何对该类型执行计算和比较。如，MySQL在内部将ENUM和SET类型存储为整数，但在字符串上下文中进行比较时，会将它们转换为字符串。
整数类型
整数类型通常是标识符的最佳选择，因为它们速度快，并且可以递增。
ENUM和SET类型
对于标识符来说，ENUM和SET通常是不好的选择，尽管对某些只包含固定状态或者类型的静态“定义表”来说可能是没有问题的。
字符串类型
如果可能，应避免使用字符串类型作为标识符的数据类型，因为它们很消耗空间，而且通常比整数类型慢。
对于完全“随机”的字符串要非常小心，如MD5()、SHA1()或UUID()生成的字符串。这些很熟生成的新值会减慢INSERT和某些类型的SELECT查询的速度：
- 插入的值会写到索引的随机位置，会使得INSERT查询变慢。
- SELECT查询也会变慢，因为逻辑上相邻的行会广泛分布在磁盘和内存中。
- 对于所有类型的查询，随机值都会导致缓存的性能低下，因为它们会破坏引用的局部性，而这正是缓存的工作原理。如果整个数据集都是“热的”，那么将任何特定部分的数据缓存到内存中都没有好处，而且如果工作集比内存大，缓存就会出现大量刷新和不命中。
MySQL的存储引擎API通过在服务器和存储引擎之间以行缓冲区格式复制行来工作；然后，服务器将缓冲区解码为列。这个过程的代价是非常高的，且转换成本与列数正相关。
一个粗略的经验法则是，如果需要以高并发性快速执行查询，那么每个查询最好少于十几个的表。
避免使用NULL有好处。但遵循这个原则也不要走极端。当表示未知值时，不要太害怕使用NULL。处理NULL并不容易，但通常比其他替代方案更好。
在MySQL中，存储引擎先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。
在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以并没有统一的索引标准：不同的存储引擎的索引工作方式并不一样，也不是所有引擎都支持所有类型的索引。
The B-Tree index of MySQL typically uses a B-Tree data structure to store its data.
Many storage engines actually use a B+Tree index, in which each leaf node contains a link to the next for fast range traversals through nodes.
MySQL在CREATE TABLE和其他语句中使用的是"BTREE"关键字，但存储引擎也可能使用不同的存储结构，如，NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引；InnoDB则使用的是B+Tree。
The general idea of a B-Tree is that all the values are stored in order and each leaf page is the same distance from the root.
叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
B-Tree索引适用于全键值、键值范围或键前缀查找。
Types of queries that can use a B-Tree index:
- 全值匹配：和索引中的所有列进行匹配。
- 匹配最左前缀：和索引中靠前的列进行匹配。
- 匹配最左列前缀：匹配某一列的值的开头部分。
- 匹配范围值：对索引中的列进行范围匹配。
- 精确匹配某一列并范围匹配另外一列：对索引中靠前的列进行全匹配，靠后的列进行范围匹配。
- 只访问索引的查询：查询只访问索引，无须访问数据行。
Limitations of B-Tree indexes:
- 如非按照索引的最左列开始查找，则无法使用索引。
- 不能跳过索引中的列。
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他积累索引类型的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和负数、布尔搜索等。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。关于全文索引的详细讨论在第7章。
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
在MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎的默认索引类型，Memory引擎同时也支持B-Tree索引。
存储引擎使用特定的哈希函数将数据值计算为哈希码，该哈希码用以表示槽（Slot）的索引，每一个槽的指针指向数据行。
Limitations of Hash indexes:
- 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
- 哈希索引数据并不是按照索引值顺序存储的，无法用于排序。
- 哈希索引不支持部分索引列匹配查找，因为哈希索引始终使用所有索引列的全部内容来计算索引值。
- 哈希索引只支持等值比较查询，包括=、IN()、<=>（安全的等值匹配），不支持范围查询。
- 河西冲突很多时，存储引擎必须遍历链表中所有的行指针，查询代价高昂。
- 哈希冲突很多时，一些索引维护操作的代价会很高。
因为上述限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将会非常显著，如“星型”schema。
InnoDB引擎有一个特殊的功能叫“自适应哈希索引”（adaptive hash index）。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能。
Building your own hash indexes. 如果存储引擎不支持哈希索引，则可以模拟向InnoDB一样创建哈希索引，还可以享受一些哈希索引的遍历，例如只需很小的索引就可以为超长的键创建索引。
这样实现的缺陷是需要维护哈希值。可以手动维护，也可以使用触发器实现。
记住必要使用SHA1()和MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量的空间，比较时也会更慢。
MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据，可以有效地使用任意维度来组合查询。MySQL的GIS支持并不完善。
TokuDB的分形树索引是较新开发的数据结构，既有B-Tree的很多优点，也避免了B-Tree的一些缺点。
ScaleDB的Patricia tries。
索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用，根据创建索引的数据结构不同，索引也有一些其他的附加作用。
B-Tree索引由于按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。最后，因为索引中存储了实际的列值，所以某些查询只是用索引就能够完成全部查询。
总结下来，索引有如下三个优点：
- 大大减少了服务器需要扫描的数据量。
- 可以帮助服务器避免排序和临时表。
- 可以将随机I/O变成顺序I/O。
Is an Index the Best Solution? 索引并不总是最好的工具。只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。 对于非常小的表，大部分情况下简单的全表扫描更高效。 对于中大型表，索引就非常有效。 对于特大型表，建立和使用索引的代价将随之增长。此时需要一种技术可以直接区分出查询需要的一组数据，而非一条一条记录地匹配。如分区技术。
如果查询的列不是独立的（索引列是表达式的一部分或函数的参数），则MySQL就不会使用索引。
如下索引就无法使用actor_id列的索引：
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 =5;索引很长的字符列会让索引变得大且慢，一个策略是模拟哈希索引。另一策略是索引字符的前缀，这样可大大节约索引空间，从而提高索引效率。后者会降低索引的选择性（不重复的索引值和数据表的记录总数）。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
前缀索引是一种能使索引更小、更快的有效方法，但也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY。
对于BLOB、TEXT或很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
有时候后缀索引（suffix index）也有其用途。MySQL并不原生地支持反向索引，但可以把字符串反转后存储，并基于此建立前缀索引。
在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。
MySQL5.0和更新版本引入了一种“索引合并”的策略，一定程度上可以使用多个单列索引来定位指定的行，但这种情况下没有哪个独立的单列索引是非常有效的。
索引合并策略的缺点：
- 不适用于相交操作。
- 联合操作需要耗费大量的CPU和内存在算法的缓存、排序和合并操作上。
- 优化器不会把额外工作计算到“查询成本”上，成本被低估，导致该执行计划还不如直接进行全表扫描。
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。
聚族索引并不是一种单独的索引类型，而是一种数据的存储方式。具体的细节依赖于其实现方式，但InnoDB的聚族索引实际上在同一结构中保存了B-Tree索引和数据行。当表有聚族索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。术语“聚族”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚族索引。
因为时存储引擎负责实现索引，因此不是所有的存储引擎都支持聚族索引。
以下主要关注InnoDB引擎的聚族索引。
在InnoDB中，有一列值，专门地被设定为簇族索引，这列值就是主键，当不存在主键时，InnoDB会选择一个唯一非空的列，当后者也不存在时，InnoDB会自己创建一个簇族索引。
簇族索引的优点：
- 可以把相关数据保存在一起。
- 数据访问更快。簇族索引将索引的数据保存在同一个B-Tree中，因此比非簇族索引更快。
- 使用覆盖索引扫描的查询可以直接使用节点中的主键值。
聚族索引的缺点：
- 聚族数据最大限度地提高了I/O密集型应用的性能。但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚族索引也就没什么优势了。
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。那么在加载完成后最好使用OPTMIZE TABLE命令重新组织一下表。
- 更新剧组索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
- 基于聚族索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
- 簇族索引可能导致全表扫描变慢，尤其是是行比较稀疏，或者由于也分裂导致数据存储不连续的时候。
- 二级索引（非簇族索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找，而不是一次。
注：二级索引，叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚族索引中得到完整的一行记录。相比于叶子节点中存储行指针，二级索引存储主键值会占用更多的空间。
- 那为什么要这样设计呢？InnoDB在移动行时，无需维护二级索引，因为叶子节点中存储的是主键值，而不是指针。
- 那么InnoDB有了聚族索引，为什么还要由二级索引呢？聚族索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚族索引，占用的内存空间要少。当我们需要为表建立索引时，如果都是聚族索引，将占用大量内存空间，索引InnoDB中主键所建立的是聚族索引，而唯一索引、普通索引、前缀索引等都是二级索引。
- 为什么一般情况下,我们建表时都会使用一个自增的ID来作为主键？InnoDB中表中的数据是直接存储在主键聚族索引的叶子节点中的，每插入一条记录，其实都是增加一个叶子节点，如果主键是顺序的，只需要把新增的一条记录存储在上一条记录的后面，当页达到最大填充因子的时候，下一条记录就会写入新的野种，这种情况下，主键页就会近似于被顺序的记录填满。若表的主键非顺序ID，而是无规律数据，InnoDB则无法简单地把一行记录插入到索引的最后，而是需要一个合适的位置，甚至产生大量的页分裂并且移动大量数据，在寻找合适位置进行插入时，目标也可能不再内存中，这就导致了大量的随机IO操作，影响插入效率。此外，大量的也分裂会导致大量的内存碎片。
InnoDB和MyISAM的数据分布对比
MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。
索引确实是一种查找数据的高效方式，但MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果一个索引包含所有需要查询的字段的值，我们称之为“覆盖索引”。
覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：
- 索引条目通常远小于数据行大小，极大减少了数据访问量。
- 索引按列值顺序存储，对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
- 某些存储引擎（如MyISAM）在内存中缓存索引，数据则依赖于操作系统来缓存，减少系统调用会极大提升性能。
- 由于InnoDB的聚族索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二级查询。
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。另外，不同的存储引擎对覆盖索引的实现方式也不同，而且不是所有引擎都支持覆盖索引。
MySQL有两种方式可以生成有序的结果：通过排序操作；按索引顺序扫描。
扫描索引本身是很快的，但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引都回表查询一次对应的行。
使用索引排序的要求：
- 索引的列顺序和
ORDER BY子句的顺序完全一致 - 所有列的排序方向（倒序或正序）都一样
- 关联多表时，
ORDER BY子句引用的字段全部为第一张表 - 满足最左前缀
MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。
测试表明，对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。
可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。
MySQL允许在相同列上创建多个索引。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。
重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。
如先创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，此时实际上在相同的列上创建了三个重复的索引。
冗余索引和重复索引有一些不同。如创建了索引（A，B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引（这种冗余只是对B-Tree索引来说的）。另外，其他不同类型的索引也不会是B-Tree索引的冗余索引。
索引可以让查询锁定更少的行。从两方面看这这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争用并减少并发性。
InnoDB只有在访问行的时候才会对其进行枷锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了：InnoDB已经锁定了这些行，到适当的时候才释放。在MySQL 5.1和更新的版本中，InnoDB可以在服务器段过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。
如：
SET AUTOCOMMIT;
BEGIN;
SELECT id FROM `province` WHERE id < 5 AND id <> 1 FOR UPDATE;这条查询仅仅会返回2-4之间的行，但实际上获取了1-4之间的排他锁。InnoDB会锁柱第一行，这是因为MySQL为该查询选择的执行计划是索引范围扫描：
EXPLAIN SELECT id FROM `province` WHERE id < 5 AND id <> 1 FOR UPDATE;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | province | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 4 | 100.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+注意到EXPLAIN的Extra列出现了“Using where”，这表示MySQL服务器将存储引擎返回行以后再应用WHERE过滤条件。
尽可能重用索引而不是建立大量的组合索引，当有不需要用于WHERE查询条件的前缀索引时可以使用IN()的技巧来避免。
尽可能将范围条件列放到索引的最后面。
范围查询和多个等值条件查询的访问效率是不同的。对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于“多个等值条件查询”则没有这样的限制。
对于多个范围条件查询，优化器的特性是影响索引策略的一个很重要的因素。如果未来版本的MySQL能够实现松散索引扫描，就能在一个索引上使用多个范围条件，那就不需要为上面考虑的这类查询使用IN()列表了。
Sorting small sets with filesorts is fast, but what if millions of rows match a query?
We can add special indexes for sorting these low-selectivity cases.
One of the good strategies for optimizing such queries is to use a deferred join, which again is our term for using a covering index to rerieve just the primary key columns of the rows you'll eventually retrieve. Then join this back to the table to retrieve all desired aolumns. This helps minimize the amount of work MySQL must do gathering data that it will only throw away. Here is an example:
SELECT * FROM person INNER JOIN (
SELECT id FROM person
WHERE sex = 'M' ORDER BY birth LIMIT 100000, 10
) AS p USING (id);注：MySQL延迟关联策略，指“延迟了对列的访问”，不直接获取所有需要的列。在查询的第一阶段MySQL使用覆盖索引，再通过覆盖索引查询到的结果到外层匹配需要的所有列值。（引用自 腾讯云专栏文章）
The three goals of table maintenance are finding and fixing corruption, maintaining accurate index statistics, and reducing fragmentation.
The worst thing that can happen to a table is corruption. With the MyISAM storage engine, this often happens due to crashes. However, all storage engines can experience index corruption due to hardware problems or internal bugs in MySQL or the operating system.
Corrupted indexes can cause queries to return incorrect results, raise duplicate-key errors when there is no duplicated value, or even cause lockups and crashes. CHECK TABLE usually catches most table and index errors.
You can fix corrupt tables with the REPAIR TABLE command, but again, not all storage engines support this. In this case you can do a "no-op" ALTER, such as altering a table to use the same storage engine it currently use. Alternatively, you can either use an offline engine-specific repair utility, or dump the data and roload it.
B-tree indexes can become fragmented, which might reduce performance. Fragmented indexes can be poorly filled and/or nonsequential on disk.
By design B-Tree indexes require random disk access to "dive" to the leaf pages, so random access is the rule, not the exception. However, the leaf pages can still perform better if they are physically sequential and tightly packed. If they are not, we say they are fragmented, and range scans or full index scans can be many times slower. This is especially true for index-covered queries.
The table's data storage can also become fragmented. However, data storage fragmentation is more complex than index fragmentation. There are three types of data fragmentation:
Row fragmentation
This type of fragmentation occurs when the row is stored in multiple pieces in multiple locations. Row fragmentation reduces performance even if the query needs only a single row from the index.
Intra-row fragmentation
This kind of fragmentation occurs when logically sequential pages or rows are not stored sequentially on disk. It affects operations such as full table scans and clustered index range scans, which normally benefit from a sequential data layout on disk.
Free space fragmentation
This type of fragmentation occurs when there is a lot of empty space in data pages. It causes the server to read a lot of data it doesn't need, which is wasteful.
Solutions for fixing fragmentation:
- run
OPTIMIZE TABLE - dump and reload data
- rebuild table with a no-op
ALTER TABLE
通常来说，查询的生命周期包括：
- 从客户端到服务器
- 服务器进行解析
- 生成执行计划
- 执行（包括存储引擎的调用以及调用后的数据处理）
- 返回给客户端
Here are a few typical mistakes:
- Fetching more rows than needed
- Fetching all columns from a multitable join
- Fetching all columns
- Fetching the same data repeatedly
The simplest query cost metric are:
- Response time
- Number of rows examined
- Number of rows returned
MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但有时候，将一个大查询分解为多个小查询是很有必要的。
有时候对于一个大查询需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
这样可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少锁的持有时间。
Many high-performance applications use join decomposition.
For example, instead of this simple query:
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';You might run these queries:
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = ...;
SELCT * FROM post WHERE post.id IN (...);Such restructuring can actually give significant performance advantages:
- Caching can be more efficient. Many applications cache "objects" that map directly to tables. If only one of the tables changes frequently,decomposing a join can reduce the number of cache invalidations.
- Executing the queries individually can sometimes reduce lock contention.
- Dosing joins in the application makes it easier to scale the database by placing tables on different servers.
- The queries themselves can be more efficient.
- You can reduce redundant row accesses.
- You can view this technique as manually implementing a hash join instead of nested loops algorithms MySQL uses to execute a join.
MySQL执行查询的过程：
- 客户端发送一条请求给服务器。
- 服务器先检查缓存查询。若命中，立即返回存储在缓存中的结果；否则进入下一步。
- 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
- MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询。
- 将结果返回给客户端。
MySQL客户端和服务器之间的通信协议是“半双工”的。任意时刻要么是客户端向服务器发送数据，要么是服务器向客户端发送数据，两个动作不会同时发生。
这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，没法进行流量控制。一旦一段开始发送消息，另一端要接收完整个消息才能响应它。
客户端用一个单独的数据包将查询结果传给服务器。因此当查询语句很长的时候，参数max_allowed_packet就特别重要了。
相反的，一般服务器相应给用户的数据由多个数据包组成。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。
多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。
- Sleep, the thread is waiting for a new query from the client.
- Query, the thread is either executing the query or sending the result back to the client.
- Locked, the thread is waiting for a table lock to be granted at the server level.
- Analyzing and statistics, the thread is checking storage engine statistics and optimizing the query.
- Copying to tmp table [on disk], the thread is processing the query and copying results to a temporary table, probably for a
GROUP BY, for a filesort, or to satisfy aUNION. - Sorting result, the thread is sorting a result set.
- Sending data, this can mean several things: the thead might be sending data between stages of the query, gnerating the result set, or returning the result set to the client.
Before even parsing a query, MySQL checks for it in the query cache, if the cache is enabled. This operation is a case-sensitive hash lookup. If it won't match, and the query processing will go to the next stage.
If MySQL does find a match in the query cache it must check privileges before returning the cached query. This is possible without parsing the query, because MySQL stores table information with the cached query. If the privileges are OK, MySQL retrieves the stored result from the query cache and sends it to the client, bypassing every other stage in query execution. The query is never parsed, optimized, or executed.
This step turns a SQL into a execution plan for the query engine. It has several substeps: parsing, processing, and optimization. Errors (for example, syntax errors) can be raised at any point in the process.
The parser and the preprocessor
To begin, MySQL's parser breaks the query into tokens and builds a "parse tree" from them. The parser uses MySQL's SQL grammar to interpret and validate the query. For instance, it ensure that the tokens in the query are valid and in the proper order, and it checks for mistakes such as quoted strings that aren't terminated.
The preprocessor then checks the resulting parse tree for additional semantics that the parser can't resolve. For example, it checks that tables and columns exist, and it resolves names and aliases to ensure that column references aren't ambiguous.
Next, the preprocessor checks privileges.
The query optimizer
The parse tree is now valid and ready for the optimizer to turn it into a query execution plan. A query can often executed many different ways and produce the same result. The optimizer's job is to find the best option.
MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来变得更加复杂，且引入了一些“因子”来估算某些操作的代价，如当执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本。
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM person;
+----------+
| COUNT(*) |
+----------+
| 5462 |
+----------+
mysql> SHOW STATUS LIKE 'Last_query_cost';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| Last_query_cost |1040.399000 |
+-----------------+------------+这个结果表明大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列统计信息得来的：每个表或者索引的页面个数、索引的基数（不同索引值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。
有很多原因会导致MySQL优化器选择错误的执行计划，如：
- 统计信息不准确
- 执行计划中的成本估算不等同于实际执行的成本
- MySQL的最优可能和你想的最优不一样
- MySQL从不考虑其他并发执行的查询，这可能影响到当前查询的速度
- MySQL也并不是任何时候都是基于成本的优化
- MySQL不会考虑不受其控制的操作的成本，如执行存储过程或用户自定义函数的成本
- 优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划
MySQL优化策略可以简单分为两种：
- 静态优化，可以直接对解析树进行分析，并完成优化
- 动态优化，与查询上下文有关，也可能和很多其他因素有关，如WHERE条件中的取值、索引中条目对应的数据行数等。
MySQL能够处理的优化器类型：
- 重新定义关联表的顺序。数据表的关联并不总是按照查询中执行的顺序进行。
- 将外连接转化为内连接。并不是所有的
OUTER JOIN语句都必须以外连接的方式执行。 - 使用等价变换规则。MySQL可以使用一些等价变换来简化并规范表达式。
- 优化
COUNT()、MIN()和MAX()。索引和列是否可为空通常可以帮助MySQL优化这类表达式。 - 预估并转化为常数表达式。
- 覆盖索引扫描。当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行。
- 子查询优化。MySQL在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据的访问。
- 提前终止查询。在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。
- 等值传播。若两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一个列上。
- 列表
IN()的比较。在很多数据库系统中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。MySQL则将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(log n)复杂度的操作，前者复杂度为O（n）。
复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。
复制常见用途：
- 数据分发。在不同的地理位置分布数据备份。
- 读流量扩展。通过MySQL的复制可以将读操作分布到多台服务器上，实现对读密集型应用的优化。
- 备份。复制是一项有助于备份的有价值的技术，但副本不是备份，也不能取代备份。
- 分析和报告。为报告/分析查询使用专用的副本是一项很好的策略，可以很好地隔离此类查询产生的压力。
- 高可用性和故障切换。复制有助于避免MySQL成为应用程序中的单点故障。
- MySQL升级测试。先使用一个更高版本的MySQL作为副本，确保查询能够在此副本上按照预期执行，再升级所有的实例。
复制步骤：
- 源端把数据更改记录到二进制日志中，称之为“binary log events”。
- 副本将源上的日志复制到自己的中继日志中。
- 副本读取中继日志中的事件，将其重放到副本数据之上。
三种不同的二进制日志格式用于复制：
- 基于语句的。副本从中继日志读取到事件并执行时，实际上是重新执行在源端执行过的SQL语句。这种格式的优点是简单且紧凑。最大的弊端是会遇到某些具有“不确定性”的SQL语句问题。假设有一条语句删除了一张有1000行记录的表中的100行，但没有用ORDER BY子句，就会导致源和副本上的执行结果不一致。
- 基于行的。将事件写入二进制日志，该事件包含了该行记录发生了什么改变。这可能会让二进制日志的大小发生巨大的增长。
- 混合模式。该模式试图结合前两者格式的优点。这种模式下，默认使用基于语句的格式，仅在需要时才切换到基于行的格式。这也会导致二进制日志中出现不可预测的事件。
在MySQL 5.6之前，副本必须跟踪连接到源时读取的二进制日志文件和日志位置。当副本从该二进制日志中读取事件时，它每次都会向后推进日志位点。如果此时发生故障，如源服务器崩溃了，必须从备份中重建数据。那么问题来了：在源端，若二进制日志位点重新开始，怎么能重新将副本连接到源库？若指向的位点太早，则副本就会获得重复的事件；若指向的位点太晚，则会漏掉事件。
为了解决这个问题，MySQL新增了另一种跟踪复制位点的方法：全局事务标识符（GTID）。使用GTID，源服务器提交的每个事务都会被分配一个唯一标识符。此标识符是由server_uuid和一个递增的事务编号组成的。当事务被写入二进制日志时，GTID也随之被写入。当副本SQL线程提交事务时，它也会将GTID标记为执行完成。
通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询了。但是，如果你希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。
全文索引有着自己独特的语法。没有索引也可以工作，如果有索引效率会更高。
全文索引可以支持各种字符内容的搜索（包括CHAR、VARCHAR和TEXT类型），也支持自然语言搜索和布尔搜索。
自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会搜索，即使不再停用词列表中出现，如果一个词语在超过50%的记录中都出现了，那么自然语言搜索将不会搜索这类词语。
全文索引的语法和普通查询略有不同。可以根据WHERE子句中的MATCH AGAINST来区分查询是否使用全文索引。
我们来看一个示例：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('dbms database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+和普通查询不同，这类查询自动按照相似度进行排序。在使用全文索引进行排序的时候，MySQL无法再使用索引排序。所以如果不想使用文件排序的话，那么就不要在查询中使用ORDER BY子句。
在布尔搜索中，用户可以在查询中自定义某个被搜索的词语的相关性。布尔搜索通过停用词列表过滤掉那些“噪声”词，此外，布尔搜索还要求搜索关键词长度必须大于ft_min_word_len，同时小于ft_max_word_len。搜索返回的结果是未经排序的。
当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。
Common modifiers for Boolean full-text searches
| Example | Meaning |
|---|---|
| dinosaur | 包含“dinosaur”的行rank值更高 |
| ~dinosaur | 包含“dinosaur”的行rank值更低 |
| +dinosaur | 行记录必须包含“dinosaur” |
| -dinosaur | 行记录可以不包含“dinosaur” |
| dino* | 包含以“dino”开头的单词的行rank值更高 |
还可以使用其他的操作，例如使用括号分组。基于此，就可以构造出一些复杂的搜索查询。
继续使用articles表作为示例：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+dbms database' IN BOOLEAN MODE);
+----+----------------+------------------------------+
| id | title | body |
+----+----------------+------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+----------------+------------------------------+全文索引的日常维护通常能够大大提升性能。“双B-Tree”的特殊结构、在某些文档中比其他文档要包含多得多的关键字，这都使得全文索引比起普通索引有更多的碎片问题。所以经常使用OPTIMIZE TABLE来减少碎片。如果应用是I/O密集型的，那么定期地进行全文索引重建可以让性能提升很多。
如果希望全文索引能够高效地工作，还需要保证索引缓存足够大，从而保证所有的全文索引都能够缓存在内存中。通常，可以为全文索引设置单独的键缓存（Key Cache），保证不会被其他的索引缓存挤出内存。
提供一个好的停用词表也很重要。默认的停用词表对常用英语来说可能还不错，但如果是其他语言或者某些专业文档就不合适了，例如技术文档。
忽略一些太短的单词也可以提升全文索引的效率。但同时也会降低搜索的精确度。
We can use EXPLAIN to get information about the query execution plan, and how to interpret the output. The EXPLAIN command is the main way to find out how the query optimizer decides to execute queries. This feature has limitations and doesn't always tell the truth, but its output is the best information available.
To use EXPLAIN, simply add the word EXPLAIN just before the SELECT keyword in your query.
Here's the simplest possible EXPLAIN result:
mysql> EXPLAIN SELECT 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+There's one row in the output per table in the query. The meaning of "table" is fairly broad here: it can mean a subquery, a UNION result, and so on.
There is a important variations on EXPLAIN:
EXPLAIN PARTITIONshows the partitions the query will access, if applicable. If the query contains a subquery in theFROMclause, MySQL actually executes the subquery, places its result into a temporary table, and then finishes optimizing the outer query. It has to process such subqueries before it can optimize the outer query fully, which it must do forEXPLAIN.
MySQL explains only SELECT queries, not stored routine calls or INSERT,UPDATE,DELETE, or any other statements. However, you can rewrite some non-SELECT queries to be EXPLAIN-able.
For example, here is a UPDATE statement:
mysql> UPDATE actor
-> JOIN film_actor USING (actor_id)
-> SET actor.last_update = film_actor.last_update;We can convert it into a SELECT query like this:
mysql> EXPLAIN SELECT film_actor.last_update, actor.last_update
-> FROM actor
-> JOIN film_actor USING (actor_id);It's often good enough to help you understand what a query will do.