一、mysql架构
1. mysql逻辑架构
mysql服务器逻辑架构图
（1）最上层的服务并不是Mysql所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。主要负责连接处理，身份验证，安全性等。
（2）第二层架构师Mysql的核心部分，大多数mysql的核心服务都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数，同时，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
（3）第三层包含了存储引擎，存储引擎负责mysql中数据的存储和提取。存储引擎不会去解析sql，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。
（1）连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个cpu核心或者cpu中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。
当客户端连接到mysql服务器时，服务器需要对其进行认证，认证基于用户名，原始主机信息和密码。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询权限。
（2）优化与执行
mysql会解析查询，并创建内部数据结构，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。
优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询时有影响的，优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。
对于select语句，在解析查询之前，服务器会先检查查询缓存，如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。
2. 并发控制
（1）读写锁
如果某个客户正在读取邮箱，同时另外一个用户试图删除编号为25的邮件，会产生什么结果？
结论是不确定，读的客户可能会报错退出，也可能读取到不一致的邮箱数据。
解决这类经典问题的方法是并发控制，在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（S锁）和排他锁（X锁），也叫读锁和写锁。
a. 读锁
读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一资源，而互不干扰。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不是加X锁，知道T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
b. 写锁
写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
（2）锁粒度
一个提高共享资源并发性的方式就是让锁定对象更有选择性，尽量只锁定需要修改的部分数据，而不是所有的资源。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。
a.表锁
表锁是mysql中最基本的锁策略，并且是开销最小的策略，它会锁定整张表。一个用户在对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表得所有读写操作。
b.行锁
行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。在InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。
3. 事务
如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该语句。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行，也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。
（1）事务的ACID
ACID表示原子性、一致性、隔离性和持久性，一个运行良好的事务处理系统，必须具备这些标准特征。
a. 原子性：
一个事务必须被视为一个不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
b. 一致性：
数据库总是从一个一致性的状态转换到另外一个一致性的状态。即如果事务执行过程中系统崩溃，也不会有任何损失，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。
c. 隔离性：
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。具体要看数据库引擎的隔离级别来定。
d. 持久性：
一旦事务提交，则其所做的修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。
（2）事务的隔离级别
a. 未提交读：
在该隔离级别，从字面意思上便可以看出，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。除非真的有非常必要的理由，在实际应用中一般很少使用。
例如，用户A向用户B转账100元，对应的SQL命令如下：
update account set money = money + 100 where name =’B’; (此时A通知B)
update account set money= money - 100 where name= ’A’;
当只执行第一条SQL时，A通知B查看账户，B发现钱确实到账了(脏读)，而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B再次查看账户时就会发现钱其实并没有转。
b. 提交读：
在该隔离级别，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
这个级别有时候也叫做不可重复读，因为两次执行同样的查询，可能会得到不一样的结果。大多数数据库系统默认的隔离级别都是提交读，但mysql不是。
c. 可重复读：
该级别保证了同一个事务中多次读取同样的记录的结果是一致的。可重复读解决了脏读的问题，但是无法解决幻读的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，便会产生幻行。InnoDB和XtrDB存储引擎通过多版本并发控制解决了幻读的问题。可重复读是mysql的默认隔离级别。
幻读和不可重复读都是读取了另一条已经提交的事务，所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
d. 可串行化：
可串行化是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读。该隔离级别会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。
（3）死锁
死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象，当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。
为了解决这种问题，数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。
4. 多版本并发控制
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的，这两个列，一个保存了行的创建时间，一个保存了行的过期时间，当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事物开始时刻的版本号会作为事物的版本号，用来和查询到的每行记录的版本号进行比较。MVCC只在可重复读和提交读两个隔离级别下工作，因为不可提交读存在脏读，而串行化会加表锁，自然也不存在行的版本控制问题。
（1）insert
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
（2）update
InnoDB会插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
（3）delete
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
（4）select
a. InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取到的行，要么是在事务开始前就已经存在的，要么是事务自身插入或者修改过的。
b. 行的删除版本要么未定义，要么大于当前事务版本号，这可以确保事务读取到的行，在事务开始之前未被删除。
5. mysql的存储引擎
（1）InnoDB
InnoDB是Mysql默认的事务型引擎，也是最重要、使用最广泛的存储引擎。
InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别，其默认隔离级别是可重复读，并且通过间隙锁策略防止幻读的出现，间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。InnoDB支持行级锁，但是是有条件的，在where条件没有使用主键时，照样会锁全表。InnoDB有很多优点，感兴趣的可以单独去研究一下。
（2）MyISAM
MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。对于只读的数据，或者表比较小、可以忍受修复操作，则可以考虑使用MyISAM。
二、Schema与数据类型优化
1. 选择优化的数据类型
Mysql支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。
a. 更小的通常更好
一般情况下，应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。但是，如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型。
b. 简单就好
简单数据类型的操作通常需要更少的CPU周期，比如整型比字符操作代价更低。
c. 尽量避免NULL
通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。如果查询中包含可为NULL的列，对Mysql来说很难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。
（1）整数类型
如果存储整数，可以使用这几种整数类型：tinyint，smallint，midiumint，int，bigint，分别使用8,16,24,32,64位存储空间。它们可以存储的值的范围从-2(N-1)到2(N-1)-1，其中N是存储空间的位数。
整数类型有可选的unsigned属性，表示不允许负值，这大致可以使正数的上限提高一倍。
mysql可以为整数类型指定宽度，如Int(11)，对大多数应用来说这是没有意义的，它不会限制值的合法范围，只是规定了一些交互工具用来显示字符的个数，对于存储和计算来说，int(1)和Int(20)是相同的。
（2）实数类型
实数是带有小数的数字，浮点和decimal类型都可以指定精度，对于decimal列，可以指定小数点前后所允许的最大位数。浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节，double占用8个字节，和整数一样，能选择的只是存储类型。因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，但是在数据量比较大的时候，可以考虑使用bigint代替decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。
（3）字符串类型
a. vachar和char类型
vachar类型用于存储可变长字符串，是最常见的字符串数据类型，它比定长类型更节省空间，因为它仅使用必要的空间。下面这些情况下使用varchar是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
char适合存储很短的字符串，或者所有值都接近同一个长度，对于经常变更的数据，char也比varchar更好，因为定长的char类型不容易产生碎片。
b. blob和text类型
blob和text都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储，与其他类型不同，mysql把每个blob和text值当做一个独立的对象处理，存储引擎在存储时通常会做特殊处理，当blob和text值太大时，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内存储一个指针，然后再外部存储区域存储实际的值。
blob和text不同的是blob类型存储的是二进制数据，没有排序规则或字符集，而text类型有字符集和排序规则。
（4）日期和时间类型
a. datetime
这个类型能保存大范围的值，从1000年到9999年，精度为秒。
b. timestamp
这个类型保存了从1970年1月1日午夜以来的秒数，只能表示从1970年到2038年。
除了特殊行为之外，通常也应该尽量使用timestamp，因为它比datetime空间效率更高。
2. Mysql schema设计中的陷阱
（1）太多的列
Mysql的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。
（2）太多的关联
MySQL限制了每个关联操作最多只能有61张表，如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。
三、创建高性能的索引
如果想在一本书中找到某个特定主题，一般会先看书的索引，找到对应的页码。在Mysql中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配到的索引记录找到对应的数据行。
索引的类型
（1）B-Tree索引
当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。我们使用术语’B-Tree’，是因为Mysql在create table和其他语句中也使用该关键字。不过，底层的存储引擎也可能使用不同的存储结构，如InnoDB使用的是B+Tree。
a. B树
B树（B-tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以O(log n)的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树。与自平衡二叉查找树不同，B树为系统最优化大块数据的读和写操作。B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度。普遍运用在数据库和文件系统。
定义
B树可以看作是对2-3查找树的一种扩展，M为阶数，阶数表示一个节点最多有多少个孩子节点，它满足以下定义：
根节点的关键字数量范围为：1<= k <=m-1。
非根节点的关键字数量范围：m/2 <= k <= m-1。
每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
所有叶子节点都位于同一层。
每个节点都存有索引和数据。
上图的结构，以根节点中的关键字17为例说明下含义：
17表示一个磁盘文件的文件名(类比数据库中主键id为17的这条记录)；小红方块表示这个17文件内容在硬盘中的存储位置(该记录在硬盘中的物理位置)；p1表示指向17左子树的指针。磁盘块1代表根节点，磁盘块2代表根节点的左子节点，……，大家会发现，每个节点一个磁盘块，也叫磁盘页，因为磁盘读取是采取预读取的策略，即按页为最小单位读取，即使只需要该页中1字节的数据，也会把整个页的数据加载进来，这样在进行节点中关键字比较的时候，1个节点只需1次IO操作，有效减少了磁盘IO，提高了查询的效率。(二叉树因为逻辑上相邻的节点，物理上不一定相邻，所以需要多次IO)
b. B+树
B+树是B树的一种变体，也是一个多路平衡查找树，与B树的区别如下：
每个节点最多含有m个关键字；
所有的叶子节点包含了全部的关键字信息，且叶子节点本身按照关键字顺序相连；(B树的叶子节点并没有包含全部的关键字)；
所有非叶子节点可以看成索引部分，节点中含有其子节点中最大(最小)关键字；(B树中子节点不包含父节点的关键字)；
所有的数据仅存在叶子节点；(B树的每个节点都会存储数据)；
为什么说B+树比B树更适合做文件索引或数据库索引？
B+树的磁盘读写代价更低。B+树的每个节点并不存储关键字的指针信息，因此节点相比较与B树更小。如果把所有节点放在盘块儿中，那么1个盘块儿包含更多的关键字，相对来说IO的读写次数也就降低了。
举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。
B+树查询效率更加稳定。任何关键字的查找必须走一条从根节点到叶子节点的路径，所有关键字的查询路径相同，所以查询效率相当。
B+树叶子节点的链表结构，更方便范围查询。只需要遍历叶子节点，就可以实现整颗树的遍历。而数据库中基于范围的查询是很频繁的，B树在这种查询场景下，效率低下。
但是B树也有优点，其优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。
B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。B-Tree索引对如下类型的查询有效：
（1）全值匹配
全值匹配指的是和索引中的所有列进行匹配。
（2）匹配最左前缀
只使用索引的第一列进行匹配。
（3）匹配列前缀
可以只匹配某一列的开头部分。
（4）匹配范围值
（5）精确匹配某一列并范围匹配另外一列
（6）只访问索引的查询
因为索引树种的节点是有序的，所以除了按值查找之外，索引还可以用于查询order by操作。下面是一些关于B-Tree索引的限制：
（1）如果不是按照索引的最左列开始查找，则无法使用索引。
（2）不能跳过索引中的列。
（3）如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
图片: https://uploader.shimo.im/f/NHyqwIdcKaI9aB3f.png
（2）哈希索引
哈希索引基于哈希表实现的，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：
哈希索引只包含哈希值和行指针，而不存储字段，所以不能使用索引中的值来避免读取行。
哈希索引数据并不是按照索引顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
哈希索引只支持等值比较查询，不支持任何范围查询。
访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突时，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代价也会很高。
因为这些限制，哈希索引只适用于某些特定的场合。只有Memory引擎支持哈希索引。
InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找，这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能。
2. 索引的优点
（1）索引大大减少了服务器需要扫描的数据量。
（2）索引可以帮助服务器避免排序和临时表。
（3）索引可以将随机I/O变为顺序I/O。
3. 高性能的索引策略
（1）独立的列
独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='测试表';
- 独立的列
explain select * from test where id > 5;
2. 表达式的一部分
explain select * from test where id + 1 > 5;
3. 函数参数
explain select * from test where to_days(create_time) > 10;
（2）前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但是这样也会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高，则查询效率越高。
对于BLOB、TEXT或者很长的varchar类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。
alter table test add key (content(7));
前缀索引时一种能使索引更小，更快的有效方法，但另一方面也有其缺点, mysql无法使用前缀索引做order by和group by， 也无法使用前缀索引做覆盖扫描。
（3）多列索引
在多个列上建立独立的单列索引大部分情况下并不能提高mysql的查询性能，mysql5.0之后引入了一种叫索引合并的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。在查询时它能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三种变种：
or条件的联合，and条件的相交，组合前两种情况的联合和相交。
explain select * from clue where clue_no = "1140899516090736641" or mobile = "X6rp903bt2Or+88B2oB0fA==";
索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：
当出现服务器对多个索引做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。
更重要的是，优化器不会把这些计算到查询成本中，这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描。
如果在explain中看到有索引合并，应该好好检查一下查询和表得结构，看是不是已经是最优的。
（4）选择合适的索引列顺序
对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。
（5）聚簇索引
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页上。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。InnoDB通过主键聚集数据。如果没有主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
聚集的数据有一些重要的优点:
可以把相关数据保存在一起。
数据访问更快。
使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。
同时，聚簇所以你也有一些缺点：
插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表中占用更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
非聚簇索引可能比想象的要更大，因为在非聚簇索引的叶子节点包含了引用行的主键列。
非聚簇索引访问需要两次索引查找，而不是一次。
为什么非聚簇索引访问需要两次呢？
因此二级索引叶子节点中保存的不是指向行的物理位置的指针，而是行的主键值，这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中找到对应的行。
（6）覆盖索引
如果一个索引包含所有需要查询的字段的值，我们就称之为覆盖索引。
如果查询只需要扫描索引而无须回表，会带来如下好处：
索引条目通常远远小于数据行大小，所以如果只需要读取索引，那Mysql就会极大地减少数据访问量。
因为索引时按照列值顺序存储的，所以对于I/O密集型的范围查找会比随机从磁盘读取每一行数据的I/O要少得多。
由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用，InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息.
explain select user_id from test where user_id > 1;
四、查询性能优化
1. 为什么查询速度会慢
如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。
2. 优化数据访问
大部分性能低下的查询都可以通过减少访问数据量的方式进行优化，对于低效的查询，我们发现通过下面两个步骤来分析总是很有效:
确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
确认Mysql服务器层是否在分析大量超过需要的数据行。
（1）是否向数据库请求了不需要的数据
查询不需要的记录
多表关联时返回全部列
总是取出全部列
重复查询相同的数据
（2）mysql是否在扫描额外的记录
对于mysql，最简单的衡量查询开销的三个指标如下：
- 响应时间
- 扫描的行数
- 返回的行数
响应时间
响应时间是两个部分之和：服务时间和排队时间，服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间（可能是等待I/O操作完成，也可能是等待行锁等）。
扫描的行数和返回的行数
一般Mysql能够使用如下三种方式应用where条件，从好到坏依次为：
在索引中使用where条件来过滤不匹配的记录，这是在存储引擎层完成的。
使用索引覆盖扫描(在Extra列中出现了using index)来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在mysql服务器层完成的，但无须再回表查询记录。
从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现using where)，这在mysql服务器层完成，mysql需要先从数据表读出记录然后过滤。
如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获得对应行就可以返回结果了。
改变库表结构
重写这个复杂的查询
3. 重构查询的方式
（1）一个复杂查询还是多个简单查询
（2） 切分查询
有时候对于一个大查询我们需要“分而治之”， 将大查询切分成小查询，每个查询功能一样，只完成一小部分，每次只返回一小部分查询结果。
删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住所有数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的
查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小的影响mysql性能，同时还可以减少Mysql复制的延迟。
一次性删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法，同时，需要注意的是，如果每次删除数据后，都暂停一会儿在做下一次删除，这样也可以将服务器上原来一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。
（3）分解关联查询
用分解关联查询的方式重构查询有如下的优势：
a. 让缓存的效率更高
b. 将查询分解后，执行单个查询可以减少锁的竞争
c. 在应用层做关联，可以更容易对数据库进行拆分，更容易坐到高性能和可扩展
d. 查询本身效率也可能会有所提升
e. 可以减少冗余记录的查询
4. 查询执行的基础
当我们向mysql发送一个请求的时候，mysql到底做了些什么：
- 客户端发送一条查询给服务器
- 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果，否则进入下一个阶段
- 服务器进行sql解析、预处理， 再由优化器生成对应的执行计划
- mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询
- 将结果返回给客户端
（1）mysql客户端/服务器通信协议
mysql的客户端和服务器之间的通信协议是“半双工的”， 这意味着，在任何一个时刻，这两个动作不能同时发生，所以我们无法也无须将一个消息切成小块独立来发送。
（2）查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中了查询缓存中的数据，这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存的中的查询即使只有一个字节不同，那也不会匹配缓存结果。
（3）查询优化处理
语法解析器和预处理
首先，mysql通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。
预处理器则会根据一些Mysql规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。
下一步预处理器会验证权限，这通常很快，除非服务器上有非常多的权限配置。
查询优化器
mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
有很多原因会导致mysql优化器选择错误的执行计划，如下所示：
（1）统计信息不准确
mysql依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。
（2）执行计划中的成本估算不等同于实际执行的成本
（3）Mysql的最优可能和你想的最优不一样
（4）mysql从不考虑其他并发执行的查询，这可能会影响到当前查询的速度
（5）mysql也并不是任何时候都是基于成本的优化，有时也会基于一些固定的规则
（6）mysql不会考虑不受其控制的操作的成本，例如执行存储过程中或者用户自定义函数的成本
mysql的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划，优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。
相反，动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如where条件中的取值，索引中条目对应的数据行数等，这需要在每次查询的时候都重新评估，可以认为这是“运行时优化”。
下面是一些mysql能够处理的优化类型：
（1）重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能
（2）将外连接转化成内连接
（3）使用等价变换规则
mysql可以使用一些等价变换来简化并规范表达式，它可以合并和减少一些比较，这可以移除一些恒成立和一些恒不成立的判断。例如 (5=5 and a > 5) 将被改写为a>5。
（4）优化count()、min()、max()
索引和列是否可以为空通常可以帮助mysql优化这类表达式
例如，要找到某一列的最小值，只需要查询对应B-Tree索引最左端的记录，mysql可以直接获取索引的第一行记录，在优化器生成执行计划的时候就可以利用这一点，在B-Tree索引中，优化器会将这个表达式作为一个常数对待。
（5）预估并转化为常数表达式
当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理
（6）覆盖索引扫描
当索引中的列包含所有查询中需要使用的列的时候，mysql就可以使用索引返回需要的数据，而无须查询对应的数据行
（7）子查询优化
mysql在某些情况下可以将子查询准换一种效率更高的形式，从而减少多个查询多次对数据进行访问
（8）提前终止查询
在发现已经满足查询需求的时候，mysql总是能够立刻终止查询
（9）等值传播
如果两个列的值通过等式关联，那么mysql能够把其中一列的where条件传递到另一列上。
（10）列表in()的比较
mysql将in()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(logN)复杂度的操作，等价地转换成or查询的复杂度为O(N)， 因此，对于IN()列表中有大量取值的时候，mysql的处理速度将会更快。
数据和索引的统计信息
因为服务器层没有任何统计信息，所以Mysql查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息，存储引擎则提供给优化器对应的统计信息，包括：每个表获取索引有多少个页面、每个表得每个索引的基数是多少、数据行和索引长度、索引的分布信息等，优化器会根据这些信息来选择一个最优的执行计划。
mysql如何执行关联查询
当前mysql关联执行的策略很简单：mysql对任何关联都执行嵌套循环关联操作，即mysql先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。
执行计划
mysql生成查询的一棵指令树，然后通过存储引擎完成这棵指令树并返回结果。
关联查询优化器
关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树，如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一颗树可能的执行计划树的成本，最后返回一个最优的执行计划。不过，当需要关联的表过多时，优化器会选择贪婪算法的方式查找最优的关联顺序。
（4）查询执行引擎
相对于查询优化阶段，查询执行阶段不是那么复杂：mysql只是简单地根据执行计划给出的指令逐步执行。
（5）返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，mysql仍然会返回这个查询的一些信息，如该查询影响到的行数。
如果查询可以被缓存，那么mysql在这个阶段也会将结果存放在查询缓存中。
mysql将结果集返回客户端是一个增量、逐步返回的过程。
优化特定类型的查询
（1）优化count()查询
如果想知道的是结果集的行数，最好使用count()，如果希望统计某个列值得数量，则使用count(col)。
一个容易产生的误解就是：MyISAM的count()函数总是那么快，不过也是有前提条件的，即只有没有任务where条件的count()才非常快，因为此时无须实际地去计算表得行数。Mysql可以利用存储引擎的特性直接获得这个值。如果mysql知道某列col不可能为Null值，那么Mysql内部会将count(col)表达式优化为count(*)。
（2）优化关联查询
确认on或者using子句的列上有索引。
确保任何的group by和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程。
（3）优化group by 和distinct
在mysql中，当无法使用索引的时候，group by 使用两种策略来完成：
使用临时表或者文件排序来做分组，对于任何查询语句，这两种策略的性能都有可以提升的地方