MySQL的万字总结

MySQL是一个开源的，线程关系型数据库，其名字来源于创作者Monty的女儿的名字：My

在MySQL中，一般数据库，指的是存放数据的文件，而数据库实例指的是用来执行SQL的进程。

以下总结，都是基于MySQL InnoDB存储引擎。

该总结内容来源于：《MySQL技术内幕：InnoDB存储引擎》，《高性能MySQL》3th,《MySQL架构优化实践》

MySQL 的结构

MySQL是一个基于插件式存储引擎的数据库。在MySQL的体系结构中，提供了统一的标准管理和服务支持，而存储引擎则是底层的物理结构实现，每个开发者都可以按照自己的意愿进行开发。

在MySQL中，存储引擎是基于表的，而不是数据库
CREATE TABLE `t_test` (
  ...
) ENGINE=InnoDB

从上图中，我们可以看出，MySQL体系中包含以下几个重要的部分：

连接池（Connection Pool）：用来管理MySQL的连接，缓存连接
管理服务和组件（Management Services）: 系统管理和控制工具，比如备份恢复，MySQL同步，集群等
SQL接口（SQL Interface）：用来接受SQL命令，返回用户需要查询的结果
解析器（Parser）：按照SQL语法解析SQL命令
优化器（Optimizer）: 查询优化器，优化传入的SQL命令
缓存器（Caches & Buffers）：缓存，用来缓存一部分热点数据，比如表缓存，记录缓存等
插件式存储引擎（Pluggable Storage Engines）：真正执行查找，存储的数据引擎，不同的引擎支持的操作不同，MySQL的存储引擎是基于表的。
物理文件（File System）：持久化的一些数据，包括存储的数据，Redo,Undo,Index…

存储引擎：InnoDB

InnoDB是由第三方开发，后来被官方收的一个存储引擎，InnoDB是MySQL在OLTP中应用最为广泛的存储引擎，其具有以下特性：

☆通过多版本并发（MVCC）实现高并发性能
☆支持SQL标准的4种隔离级别，默认为Repatable级别
☆通过间隙锁（Next-Key locking）来避免幻读
提供插入缓冲（insert-buffer）
支持二次写（double write）
自适应哈希索引（adaptive hash index）
预读（read ahead）
…

☆ 表示作为开发应该重点掌握的

插入缓冲：

如果每执行一次SQL,就刷新一下磁盘，则会存在很大的性能问题，因此InnoDB将所有的IO操作都放在了缓冲中进行，缓冲以页为单位。

缓冲虽然解决了性能问题，但是又带来了持久性的问题，如果数据还没来得及刷盘，系统就崩溃了，这样会造成数据丢失，因此InnoDB使用了Redo日志，Redo日志的写入是顺序写入，因此不会对性能带来太大的影响。

执行一次IO过程如下：执行SQL -> 记录Redo日志 -> 持久化Redo日志 -> 更新插入缓冲

MySQL 的文件

日志文件

Error Log ：

错误日志文件，错误日志文件记录了MySQL启动，运行，关闭等记录，同时包含一些警告信息，当发现MySQL有异常的时候，应该第一时间查看错误日志文件。

SHOW VARIABLES LIKE 'log_error'

Slow Log :

慢查询日志可以监控执行超过指定时间的SQL，从而记录到日志中，默认情况下MySQL并不启动慢查询日志，用户需要手工将这个参数设置为ON:

SHOW VARIABLES LIKE 'log_slow_queries';  //查询是否开启慢查询日志

ShOW VARIABLES LIKE 'long_query_time';    //查询慢日志的阈值，默认10s

SHOW VARIABLES LIKE 'log_queries_not_using_indexes'; //记录所有没有使用索引的SQL

SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes';  //设置没有记录索引的SQL的运行次数阈值，只有超过这个阈值才记录
SHOW VARIABLES LIKE 'log_output';         //看出日志输出格式

如果数据量过多，可以设置log_output格式为TABLE，MySQL会将数据记录到slow_log表中

查询日志:

查询日志记录了所有对MySQL数据库的所有请求信息，无论这些请求是否得到了正确的执行。

Binary Log :

二进制日志记录了MySQL数据库执行的所有的更改操作，但是不包括SELECT和SHOW等操作。通过二进制日志，可以达到以下几种功能：

恢复：通过二进制日志恢复数据
复制：在主从同步的时候，通过二进制日志，将主数据库信息同步到从数据库中
审计：通过二进制日志，可以统计操作，查看是否存在SQL注入

Binary Log默认没有启动，需要手动启动

Redo Log

Redo Log是存在InnoDB下的重做日志文件，主要用处在于记录InnoDB存储引擎的事务日志，同时用来保证事务的一致性。

正如前面所说，当进行IO的时候，会先写入Redo，然后再写入缓冲中。

Redo VS bin log:

二进制日志文件记录的是MySQL所有数据库有关的日志记录，是MySQL服务层面的，不管底层使用什么存储引擎，都会存在，而Redo Log只有InnoDB才会存在，是基于存储引擎层面的

同时二进制日志文件记录的是逻辑日志，而Redo Log记录的是关于每个页的物理更改情况

二进制日志文件仅在事务提交前提交，只写磁盘一次，而对于Redo来说，随着事务的进行，会不断的有日志写入到重做日志文件中

redo log是循环使用的，因此如果超过一定的数据量，会被覆盖，而bin log则不会重复使用

Undo Log

undo log主要应用在事务当中，undo log中记录的是用户操作的反向逻辑操作，当事务运行出错或者需要回滚的时候，可以读取undo log中的内容执行进行回滚。undo log的持久性保证也是通过redo log来实现，也就是undo log的产生会伴随着redo log的产生。

表结构定义文件

MySQL中，将表的结构定义存放在以frm为后缀的文件中。

在InnoDB中，所有的数据存储结构如下：

表空间:

表空间是InnoDB存储引擎逻辑的最高层，所有的数据都存放在表空间中。

在InnoDB中，表空间可以设置为共享表空间和独立表空间。

共享表空间：所有数据存放在同一个表空间中
独立表空间：每张表的数据单独放在一个表空间中，（仅仅是数据，索引，插入缓冲单独存放，回滚信息，事务信息等还是放在原来的共享表空间中）

段：

表空间由各个段组成，一般分为数据段，索引段，回滚段等。

区：

区是由连续页组成的空间，每个区大小1MB,多个区便组成了一个段。

页：

多个页组成了一个区，一般来说，一页的大小为16K,因此64个页组成了一个区，可以通过参数设置页的大小为4K，8K,16K等，页是数据文件管理的最小单位，也是文件空间分配的最小单位

InnoDB中常见的页有：

数据页(B-tree Node)
undo页（undo Log Page）
系统页（System Page）
…

MySQL 索引

在InnoDB中，主要包含3种索引：

B+树索引
全文索引
哈希索引

B+树索引

在InnoDB中，索引通过B+树来实现，B+树作为索引的优点：

非叶子节点不存储数据，使得每页能存储更多索引，减少IO的次数
叶子节点存在相互索引的指针，便于范围查找
性能稳定，每次查询都需要通过相同的次数才能获取到需要的数据

需要知道的是，在InnoDB中，B+树的节点对应的是表结构中的页(Page)，也就是每次IO都是读取数据页（B-Tree Node）一页的数据。

InnoDB的这种按照索引的结构组织数据的数据表被称为索引组织表，索引组织表对应的索引被称为聚集索引，指的是索引和数据在逻辑上存储顺序相同，索引组织表的有点如下：

数据的相关性大，因为数据都是按照主键顺序存放，因此需要查找可主键相关的数据时，只需要从磁盘读取少量数据就能获取全部相关信息。
数据访问更快，因为索引和数据存放在一起，因此在查找完索引后，就能直接获取到数据
使用索引覆盖扫描的查询可以直接使用叶节点的主键值

索引组织表的缺点如下：

插入速度严重依赖于插入顺序，如果插入的数据不是有顺的，则会导致InnoDB随机IO磁盘。
更新聚集索引的代价很高，因为数据是按照聚集索引的顺序存放，因此会强制移动所有被更新的行
聚集索引可能会导致全表扫描变慢
聚集索引会导致非聚集索引访问数据需要进行两次查找

辅助索引

辅助索引也叫二级索引，是非聚集索引，其内部结构也是一颗B+树，不过和聚集索引不同的是，聚集索引的叶子节点存储的是数据，而辅助索引叶子节点存放的是聚集索引的key.这样就会带来一个问题就是对于辅助索引的查找，会存在一个二次回表的操作。

比如

 SELECT * FROM t_test WHERE name='小红';   //主键为`id`

InnoDB会先在name辅助索引上查找对应的id,然后再通过id在聚集索引中查找具体的数据。

如果一个B+树为3层，则此次查找就需要经过6次IO

索引的管理

查看索引： SHOW INDEX FROM t_rule;，返回结果如下：

Table: 对应的表
Non_unique: 是否非唯一
Key_name: 索引名
Seq_in_index: 索引中，该列的位置
Cloum_name: 索引的列的名称
Collection: 索引存放方式，一般都是A,表示是B+树
Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality与表的行数比应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。
Sub_part：是否是列的部分被索引。如果看idx_b这个索引，这里显示100，表示只对b列的前100字符进行索引。如果索引整个列，则该字段为NULL。
Packed：关键字如何被压缩。如果没有被压缩，则为NULL。
Null：是否索引的列含有NULL值。
Index_type：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都

索引的使用

不是对于任何查询都适合添加索引的，InnoDB会判定索引的Cardinality值，当该值与数据库的行的比值过小时，InnoDB会忽略此索引,比如性别
索引不是越多越好，索引对于插入和修改存在一定的性能影响，因此对于索引的添加需要有一定的考量
最好在创建表的时候就创建索引，MySQL在线DDL会阻塞数据库
由于B+树的特殊性，因此对于联合索引，需要遵循最左匹配原则。
同理，由于辅助索引的二次回表的特性，对于辅助索引name,等效于name,id（id为主键）
可以利用索引覆盖原理，来避免二次回表，索引覆盖就是需要查询的字段，都在索引中能找到
如果对于数据库来说，二次回表查找的数据过多，导致性能低于全表扫描，MySQL可能会放弃使用索引

自适应Hash索引

在MySQL中，对于等值查询，如果达到一定的条件，MySQL会在内部建立Hash索引优化查询速度，这种索引由MySQL自动建立，自己维护，因此又叫做自适应哈希（AHI,Auto Hash Index）索引，自适应哈索引建立的条件如下：

需要对这个也得连续访问模式是一样的，比如where a=xxx 和where a=xxx and b=xxx属于两种访问模式
以同一个模式访问了100次
此页通过该模式访问了N次，其中N=页中记录数/16

InnoDB存储引擎官方文档显示，启用AHI后，读取和写入速度可以提高2倍，辅助索引的连接操作性能可以提高5倍。AHI的设计思想是数据库自优化，不需要DBA对数据库进行手动调整。

MySQL 的事务

事务：是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）

事务满足四大特性：

原子性 (Atomicity)
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
一致性 (Consistency)
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。
隔离性 (Isolation)
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性 (Durability)
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响

其中，ACID中，最终的目的都是为了保证一致性（C），而原子性，隔离性，持久性都是达到一致性的方式。

ACD

在InnoDB中，ACD则通过数据库的redo log和undo log来完成，其中redo log成为重做日志，用来保证原子性和持久性，而undo log则用来保证事务的一致性。

隔离性

InnoDB的隔离性则通过锁和MVCC来实现，由于完整的隔离性，只能通过互斥锁来实现，而互斥锁带来的问题便是性能急剧下降，因此处于对性能的妥协和结合日常业务的使用，根据加锁的程度不同，又将隔离性分为以下四个级别：

读未提交(READ UNCOMMITTED)：会出现脏读的问题
读已提交(READ COMMITTED)：不会出现脏读，但是会出现不可能重复读的问题
可重复读(REPEATABLE READ)：不会出现不可重复读，但是会出现幻读的问题
串行化(SERIALIZABLE)：不会出现幻读问题

脏读

对于脏读，便是当前事务读取了其他事务未提交的数据，这明显是错误的，因为如果其他事务如果回滚，则会带来逻辑上的错误。解决脏读的方法也很简单，可以将事务中修改的数据缓存起来，等真正提交的时候再提交(InnoDB通过MVCC解决)

不可重复读

当解决了脏读问题后，在某些业务场景中，可能还会出现一种情况，就是对于同一个事务中，如果两次相同的条件的读取，却返回了不同的结果，这样对于某些业务场景来说，可能是不能接受的，解决不可重复读的简单粗暴的方案就是，每读取一行数据，就将这行数据锁定，不让其他事务修改，这样便避免了不可重复读的问题，但是由于添加了一定的锁，因此可能会带来一定的性能问题。（InnoDB通过MVCC解决）

幻读

幻读是对于两次相同的查询，返回了原本不存在的数据。为什么要区分幻读和不可重复读呢，具体的原因便是不可重复读能够标记需要锁定哪些行，而幻读需要锁定的是原本不存在的行，因此加锁的维度不一样，一般来说，解决不可重复读，只用锁定一些被读取的行即可，而想要解决幻读，则需要添加范围锁，甚至是表锁，这样就变成每个事物的进行，都需要串行化排队。(InnoDB通过MVCC加上Next-key Lock解决)

MySQL 锁和 MVCC

前面说到了InnoDB的事务的隔离性通过锁和MVCC实现，接下来详细介绍锁和MVCC：

想要明白MySQL的锁，需要首先明白对于数据访问，主要分为两类：

读取数据
修改数据

修改数据控制

一般来说，对于修改数据，一般的都是通过加锁来实现，在InnoDB中，锁主要分为以下几种：

按锁定的数据量区分
- 表锁：锁定整个数据表
- 行锁：锁住单个数据行
按锁的类型分
- 共享锁：共享锁可以和共享锁兼容，但是不能和排他锁兼容，也被称作为读锁
- 排他锁：排他锁会排斥其他任何锁，也被称作为写锁
- 意向共享锁：在InnoDB中主要为表锁，用来标记这张表某些行存在共享锁，便于加标锁的时候判断
- 意向排他锁：同上，标记这张表中存在排他锁
一般来说，修改数据(Insert,Update,Delete)默认都是添加的排他锁
当然，对于读取数据，也可以通过手动加锁的方式来实现:
- 共享锁： SELECT... LOCK IN SHARE MODE
- 排他锁： SELECT... FOR UPDATE
而对于读，InnoDB则通过多版本并发控制（Multi Version Concurrency Control，MVCC） 实现。

读取数据控制

在日常数据库的操作中，可能存在对同一个事务，多次读取的数据，又被其他的连接修改，这样便会存在数据不一致的情况，而InnoDB则通过undo日志，记录了同一行数据的不同时刻的值，用来解决上述问题，这样的方式便是多版本并发控制.

MVCC

mvcc：多版本并发控制，主要通过undo log记录每行数据的不同版本的值，从而实现对于不同级别的读，不用加锁，而通过标记事务版本号，读取到对应版本的数据，从而实现了数据隔离。

InnoDB 通过调整生成ReadView的时间与判断策略，从而实现了RC和RR隔离级别。

ReadView记录了一些关键的事务版本号，每次执行通过对比版本号的大小，选择需要读取的数据版本，主要属性如下：

up_limit_id: 活跃事务列表trx_ids中最小的事务ID,如果当前版本号小于up_limit_id，则表示此事务一定已经提交
low_limit_id：当前最大的事务号+1，如果当前版本号大于low_limit_id，则表示此事务是在当前事务之后创建的
trx_ids：当前活跃的事务id，表示当前事务创建的时候，其他已经开始但是还未提交的事务

假设当前事务需要读取一个数据行，当前数据的版本号为trx_id，则：

如果trx_id < up_limit_id, 那么表明“最新修改该行的事务”在“当前事务”创建快照之前就提交了，所以该记录行的值对当前事务是可见的。则直接返回此数据。
如果 trx_id >= low_limit_id, 那么表明“最新修改该行的事务”在“当前事务”创建快照之后才修改该行，所以该记录行的值对当前事务不可见。
如果 up_limit_id <= trx_id < low_limit_id, 表明“最新修改该行的事务”在“当前事务”创建快照的时候可能处于“活动状态”或者“已提交状态”，则需要依据当前隔离级别而定。

对于InnoDB来说，RR和RC产生Read View的区别在于：

RC: 读已提交，在事务中每次读的时候，都会重新生成一个ReadView

RR: 不可重复读，在事务第一次读的时候，会生成一个ReadView，并一直保持到事务结束

简单来说，对于RC来说，由于是每次读都会生成一个ReadView，因此ReadView中已提交的事务是实时更新的，因此会读取到其他事务已经读取的数据，同时也能隔离掉未提交的数据。

对于RR来说，当第一次读取数据后，ReadView就一直保持着以前的版本号，因此也就保证了后面的数据能和第一次读的数据相同。

MVCC 中的当前读和快照读

通过MVCC可以读取不同版本的数据，解决了脏读和不可重复读的问题。同时，基于这种版本控制数据的读取，也能解决一部分的幻读问题：简单的两次SELECT返回的数据永远是相同的。

按道理来说，通过MVCC，当前事务是读取不到其他事务在其后面插入/删除的数据的。然后，由于MVCC的特殊性，也带来了另外一种特殊的幻读：

SELECT * FROM t_student WHERE id =1;               //当前事务.....1

INSET INTO t_student (id,name) VALUES (1,"dcc");   //其他事务.....2

INSET INTO t_student (id,name) VALUES (1,"dcs");   //当前事务.....3

其中，①和③为当前事务执行的操作，②为另外一个事务执行的操作。

可以预见的是，③会执行失败，对于当前事务来说，明明查询了当前数据库中不存在id=1的数据，为什么插入的时候还是报id冲突呢？这样的现象也算是产生了幻读。

产生这样的问题的原因在于由于存在多版本数据控制，因此在InnoDB中，会将数据的读取分为两种：

当前读：读取的永远都是最新的数据。INSERT/DELETE/UPDATE/SELECT…FOR UPDATE/SELECT…IN SHARE MODE
快照读：读取的可能是某个数据的快照版本。 SELECT…FROM

对于所有的数据修改操作，都是当前读，因为如果修改的是历史版本，那不符合逻辑。

而对于普通的SELECT,则是前面的说的快照读。快照读和当前读带来的问题便是信息不一致，也就导致了上述举例中的幻读问题,解决上面的幻读问题也很简单，添加一个FOR UPDATE加锁即可。

但是使用FOR UPDATE又会带来另外一个问题：

SELECT * FROM t_student WHERE id > 4 FOR UPDATE;    //当前事务 ①

INSET INTO t_student (id,name) VALUES (1,"dcc");   //其他事务 ②

SELECT * FROM t_student WHERE id > 4 FOR UPDATE;   //当前事务 ③

由于使用了当前读，会导致当前事务能读取到其他事务已经提交的数据，这样不仅再次带来了幻读问题，还会带来不可重复读问题。

InnoDB给的解决方案便是Next Key Lock，Next Key Lock是行锁与间隙锁的结合：

Record Lock：锁定当前数据行
Gap Lock : 间隙锁，锁定一个范围，但是不包含记录本身
Next-Key Lock: Gap Lock+Record Lock,锁定范围以及记录本身。

当使用Next-Key Lock的时候，InnoDB会根据索引范围，锁定住查询的范围。

比如：

SELECT * FROM t_student WHERE id > 4 FOR UPDATE;

则InnoDB会锁定(4,+∞)的所有行，当其他事务需要插入此范围的值的时候，会被阻塞直到当前事务提交。

对于Next-Key Lock的理解，需要注意以下几点：

想要通过InnoDB在RR隔离级别完全解决幻读问题，需要通过Next-Key Lock
在RR级别中，普通的SELECT不会添加Next-Key Lock,需要显示添加排他锁
对于唯一只的查询，Next-Key Lock会降级为Record Lock

关于锁的注意事项：

虽然InnoDB支持行锁( Record Lock),但是由于InnoDB内部是B+树来实现，因此锁定一个数据行其实是锁定了通过索引来实现的。因此如果在需要锁定的时候数据行中没有走索引的话，行锁则会退化成表锁。

MySQL 执行过程

SELECT id,count(sex) as number 
FROM t_test 
WHERE name='aaa' 
GROUP BY class 
HAVING  times>1
ORDER BY name 
LIMIT 2;

想要理解MySQL的执行过程，可以尝试自己设计一个DB的时候，应该怎么做：

首先，应该明确数据来源，因此需要解析FROM，找到对应的数据表
找到数据表后，下一步便是通过过滤条件，获取具体的数据行，因此需要解析WHERE
拿到具体的数据行之后，接下来需要处理数据行，因此需要解析GROUP BY
通过GROUP BY分组之后，就可以将同一个组的数据进行聚合，因此解析count(),sum(),arg()等
通过聚合函数之后，则可以将聚合之后的数据进行过滤，因此解析HAVING
处理完数据行之后，接下来查看用户需要的数据列，因此解析SELECT
剩下的数据便是用户真真需要的数据，但是SQL可能会对数据进行排序等，因此处理 DISTINCT以及ORDER
最后，排序完成后，查看用户是否需要返回指定的行，因此处理LIMIT

总结出来执行顺序便是：

FROM->WHERE->GROUP BY->count()->HAVING->SELECT->DISTINCT->ORDER->LIMIT

MySQL 优化方案

对于MySQL来说，想要优化MySQL的时候，就需要理解前面的知识点。

优化MySQL，可以从各个方面入手：

语法

一般情况下，应该尽量使用可以正确存储数据的最小数据类型，更小意味着更快。
例如：整型比字符操作代价更低，因为字符串涉及到字符集和校对规则问题
通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值

对于MySQL来说，可以为NULL的列更难优化，因此NULL会影响索引，索引统计和值的比较等，可以为NULL的列会使用更多的存储空间。当对可为NULL的列建立索引的时候，每个索引记录都需要额外使用一个直接。
对于浮点类型，优先选择FLOAT,DOUBLE,只有在真正需要DECIMAL的时候才使用DECIMAL,如果数据量过大的时候，可以考虑使用BIGINT代替DECIMAL
注意使用VARCHAR

由于VARCHAR类型是一个边长字符串，因此如果这个字符串长度会频繁变化的话，会给MySQL带来内存碎片的问题，同时其在临时表和排序时可能导致悲观的按最大长度分配内存。
定义表的时候，对于浮点型不要指定进度，对于整数，不要指定宽度

在MySQL中对浮点数指定精度会使得MySQL悄悄选择不同的数据类型，或者在存储时对值进行取舍。而这些精度定义是非标准的，因此最好的做法就是只指定数据类型，不指定精度。
注意count(*)和count(字段)的区别在于前者会统计NULL，而后者不会统计NULL

索引

说到MySQL优化，可能第一想法就是加索引，因为使用索引能将性能提高几个数量级，但是索引不是万能的，有时候有些场景可能并不适合使用索引，使用索引，首先需要遵从下面的原则：

联合索引需要遵从最左匹配原则
对于很长的值，则可以选择使用前缀索引
通常来说，对于联合索引，将选择性更高的索引放在前面可以更好的避免随机IO

索引覆盖：

可以将需要获取的数据都添加索引，从而实现仅仅通过索引就能获取需要的数据，避免二次回表。例如：

SELECT name,sex FROM t_student WHERE name='dcc';

正常来说，如果只建立了name的索引，那么InnoDB再通过二级索引name查找到dcc后，会获取聚集索引id，然后再通过id查找聚集索引来获取具体的数据行。这就是二次回表。

而如果建立的索引是(name,sex)，那么在查找到name为dcc的节点后，就能直接通过二级索引找到sex的值，这样就避免了二次回表，从而带来性能的提升。

使用索引覆盖能解决一些不好优化的问题：

SELECT id FROM t_student WHERE name LIKE '%cc%';

对于MySQL,这种通配符开头的模糊查询，是不会走索引的。

但是如果把name加上索引，又由于id是主键，因此上述查询可以直接通过索引覆盖就能完成，因此可以通过索引覆盖是的上述语句走索引。

延迟关联：

明白了索引覆盖的原理后，后续一些优化就可以通过索引覆盖来实现：

SELECT * FROM t_student WHERE name LIKE '%cc%';

对比这条SQL和上面的SQL，唯一的区别就是需要获取的数据不同，SELECT *如果列比较多的话，很可能无法实现索引覆盖，那对于这样的查询，应该如何优化呢？

我们可以使用延迟关联：

SELECT * FROM t_student s JOIN (SELECT id FROM t_student WHERE name LIKE '%cc%') AS s1 ON  s.id = s1.id;

通过前面的例子我们知道，子语句是会走索引的，而外层查询id是主键，也是会走索引的，因此这条SQL就被完全优化成走SQL的索引。

延迟关联只是一种优化思路，其实在MySQL5.6 推出ICP(默认打开)后，有些情况就可以直接通过ICP解决，因为ICP会自动将where过滤条件下沉到存储引擎，存储引擎则可以直接通过索引过滤掉相应的数据。但是ICP也有相应的限制，关于ICP后续详细介绍

索引的调试：

对于一个查询，是否走了索引，索引的类型是什么，走的联合索引还是部分索引，这些都需要结合SQL进行分析，而在MySQL中，最好分析的索引的方法便是查看执行计划：EXPLAIN

EXPLAIN SELECT * FROM t_student WHERE class='17' AND name LIKE '%cc%';

该SQL返回的字段如下：

id： 1
select_type: SIMPLE
table: t_student
partions: NULL
type: ref
possible_keys:idx_class
key:idx_class
key_len:43
ref:const
rows:749
filterd:11.11
Extra: using index

这里简单说下几个重要的字段：

type: 表示查询的方式，常见的值有：
- ALL:全表扫描
- index: 索引扫描
- range: 选择索引的范围进行扫描
- ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
- eq_ref: 类似ref,区别在于使用的是唯一索引
- const,system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。例如where id=xxx
Key: MySQL实际使用的索引
rows:表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
Extra:
- Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
- Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
- Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
- Using join buffer：强调在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
- Using index condition: 使用了ICP

MySQL 自带的优化

在MySQL中，也自带一些优化方案，这些优化策略都是MySQL自带了，了解他们可以避免一些无效优化：

Change Buffer

前面说过，对于聚集索引，存在的一个缺点就是如果插入的数据是无序的，那么可能导致InnoDB随机写磁盘，这样会带来性能问题。

因此在InnoDB中，使用了Change Buffer，Change Buffer会将插入的数据暂时缓存，然后将里面的数据进行排序，最后再进行插入/修改，这样就能减少插入离散数据带来的性能问题。

但是不要过分依赖Change Buffer，如果数据量过大，则可能导致Change Buffer被装满，那么后面的数据依然是离散的，因此如果要插入大量的数据，可以考虑先在代码中排序，然后再插入。

ICP

ICP,(Index Condition PushDown,索引条件下推)：原本对于MySQL来说，有些过滤条件不会推送到存储引擎，因此对于一条查询:

SELECT * FROM employees WHERE first_name='Anneke' AND last_name LIKE '%Preusig'

MySQL会先将first_name='AnneKe'下推到InnoDB，InnoDB则通过索引查找所有符合要求的数据并返回给MySQL,MySQL拿到数据后，再过滤不满足last_name LIKE '%Preusig'的条件。

这样带来的问题就是存储引擎上推的数据比较大，并且经过了两次过滤。

当开启ICP过后，MySQL会将两个条件都下推给InnoDB，InnoDB可以直接通过索引过滤到所有的数据，直接返回正确的结果。

使用执行过程分析的时候，如果使用了ICP,则Extra 会返回Using index condition

ICP的生效条件如下：

当sql需要全表访问时，ICP的优化策略可用于range, ref, eq_ref, ref_or_null类型的访问数据方法。
支持InnoDB和MyISAM表。
ICP只能用于二级索引，不能用于主索引。
并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
当sql使用覆盖索引时，不支持ICP优化方法。

MRR

MRR,(Multi-Range Read Optimization) ,MRR和Chaneg Buffer有异曲同工之妙，在没有开启MRR的时候，如果通过聚集索引读取数据，那样是顺序查找，性能比较高，但是对于二级索引的顺序查找，如果再经过二次回表，可能会变成离散IO:

SELECT * FROM t_student WHERE birthday<2019

如上述SQL,birthday是二级索引，id为主键索引。

在InnoDB中，会首先通过birthday的索引查询满足birthday<2019的主键id，由于查询是按照birthday排序的，比如2019,2018,2017,2016，因此返回的主键id则可能是无序的，比如199,1,200,23,3,5040,188,那么再通过id获取所有的数据的时候，就变成了离散读取：

MRR的思想就很简单，开启MRR之后，MySQL会将所有返回的主键先进行排序，然后再进行回表。这样就避免了离散读取的问题。

MySQL 的结构

存储引擎：InnoDB

MySQL 的文件

日志文件

表结构定义文件

MySQL 索引

B+树索引

辅助索引

索引的管理

索引的使用

自适应Hash索引

MySQL 的事务

ACD

隔离性

脏读

不可重复读

幻读

MySQL 锁和 MVCC

修改数据控制

读取数据控制

MVCC

MVCC 中的当前读和快照读

MySQL 执行过程

MySQL 优化方案

语法

索引

MySQL 自带的优化

Change Buffer

ICP

MRR

发布者

dengchengchao

MySQL 的结构

存储引擎：InnoDB

MySQL 的文件

日志文件

表结构定义文件

MySQL 索引

B+树索引

辅助索引

索引的管理

索引的使用

自适应Hash索引

MySQL 的事务

ACD

隔离性

脏读

不可重复读

幻读

MySQL 锁 和 MVCC

修改数据控制

读取数据控制

MVCC

MVCC 中的当前读和快照读

MySQL 执行过程

MySQL 优化方案

语法

索引

MySQL 自带的优化

Change Buffer

ICP

MRR

发布者

dengchengchao

MySQL 锁和 MVCC