MySQL深度解析笔记（事务和索引）

MySQL5.7学习的笔记，如果有什么问题欢迎留言谈论，另外因为内容过多且有些还没深入了解所以可能记得不全后续会边学边补上

简单了解MySQL的架构体系

为什么要先了解架构？因为后面的事务、索引都是和架构相关的，而且在实操中可以对应架构体系中的每一层进行优化。
在网上找到了一个图，如下大致为MySQL的架构体系：

下面是另外一个图（这个相对上面的图简略些）：

整个流程还是很好理解的：

• 首先是客户端发送请求到，MySQL服务端进行建立连接，然后server中的连接器需要判断用户名和密码是否正确，正确则通过否则返回。
• SQL为结构化查询语言，说到底就是字符串，我们向服务端发送字符串，服务端就要对该字符串进行分析，所以分析器就是做这件事情的。这里就包含词法分析和语法分析，词法分析顾名思义就是对sql语句中的每个词的正确性进行判断，语法分析就是对整句话的语法进行分析，比如你输入select * 就没了，后面就缺少from哪张表就会报错。（另外语法分析完后还会将sql映射成AST抽象语法树）
• 另外在我们sql语句执行之前，server中的优化器已经帮我们先优化了。比如有时候我们创建了索引，但是实际运行sql语句时却没有使用到索引，这正是优化器帮我们进行了优化，优化器认为不使用索引更加合适。（比如说现在有很少的数据，并且查询的结果在这堆数据中占比很高，就可能出现这样的结果。）
  优化器有两种：
  ①基于规则的优化器（RBO）：它有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说RBO对数据不“敏感”。
  ②基于代价的优化器（CBO)： CBO是计算各种可能“执行计划”的“代价”，即COST，从中选用COST最低的执行方案，作为实际运行方案。
  现在大部分主流的数据库都是用CBO。
• 接下来就是执行器，执行器是直接跟存储引擎进行数据交互的。数据交互就涉及到IO的问题，也是要重视的一个组件。比我们常说查询尽量不用select *来查询表的所有列，而是select 具体的属性，这是因为如果select * 进行查询的话，执行器要到存储引擎进行取数据，而像InonDB存储引擎是将数据存到硬盘中的，而select *就要到硬盘中取所有列的数据，性能会比select 具体列要低很多，而且硬盘的存取速度没有内存快。
• 另外这里有一个缓存组件（mysql8移除该模块），这个组件存那些之前已经查询过的数据，如果下一次客户端所需的数据在缓存中已经有了，就没必要再按上面的流程进行查询。但是缓存组件是需要占用内存空间的，而且重复查询同个数据的需求比较低（缓存的命中率低），这样我们就会经常的更新缓存的数据，这些来说性能更加不好，这也是MySQL8为什么把它移除的原因，但是如果说你有一些常用到的数据，并且不会需要经常修改，放到里面性能还是很快的。

存储引擎

这里MySQL的存储引擎是可插拔的，也就是说你可以在MySQL服务中插入别的存储引擎。
这里主要讲InnoDB、MyISAM、MEMORY，用的比较多的还是InnoDB、MyISAM。

1. 不同的存储引擎在数据文件上它所对应的组织形式是不一样的。存储引擎是在表级别，而不是在库级别，即同个数据库中的不同表的存储引擎可以不一样。
2. 索引和事务都有区别。如上图中Transactions列中可以知道只用InnoDB支持事务。
3. 锁也不一样。InnoDB即支持表锁也支持行锁。但是MyISAM只支持表锁。
   后续在讲事务锁索引的时候再细讲。

事务（只用InnoDB才支持事务）

为什么要引入事务？事务是构成单一逻辑工作单元的操作集合，保证了数据的一致性。

数据库事务4种隔离级别及7种传播行为
事务分类：扁平事务、带保存点的扁平事务、链式事务、嵌套事务。

事务四大特性及原理

事务的4大特性（ACID）：

• 原子性(Atomicity):事务中的所有操作作为一个整体像原子一样不可分割，要么全部
  成功,要么全部失败。
• 一致性(Consistency):事务的执行结果必须使数据库从一个一致性状态到另一个一
  致性状态。一致性状态是指:1.系统的状态满足数据的完整性约束(主码,参照完整
  性,check约束等) 2.系统的状态反应数据库本应描述的现实世界的真实状态,比如转
  账前后两个账户的金额总和应该保持不变。
• 隔离性(Isolation):并发执行的事务不会相互影响,其对数据库的影响和它们串行执行
  时一样。比如多个用户同时往一个账户转账,最后账户的结果应该和他们按先后次序
  转账的结果一样。
• 持久性(Durability):事务一旦提交,其对数据库的更新就是持久的。任何事务或系统
  故障都不会导致数据丢失。

保证事务特性和下面的知识点有关：

1. 锁：共享锁、排它锁、独占锁、自增锁、间隙锁
2. 日志：MySQL到底有多少种日志类型需要我们记住的，主要还是redo和undo，undo保证了事务原子性，同时实现多版本并发控制（MVCC）。MVCC举个例子：A事务对某个表进行查询操作，B事务在此之前更新这个表，则A事务查寻更改之前的数据，之前的数据就放在undo里面。这就是为什么事务隔离级别中Read Committed不能防止可重复度和 Repeatable Read可以防止可重复度。

原子性实现原理：Undo log（回滚日志）

① Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo Log来实现多版本并发控制（MVCC），也即一致非锁定读。
② 在操作任何数据之前，首先将数据备份到一个地方（在这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
③ 注意undo log是逻辑日志，可以理解为：
- 当delete一条记录是，undo log中会记录一条对应的insert记录
- 当insert一条记录是，undo log中会记录一条对应的delete记录
- 当update一条记录时，它记录一条对应相反的update记录

MySQL中的逻辑日志：拿InnoDB来举例子，我们都知道InnoDB引擎的数据文件有且仅有一个，后缀为ibd文件，所以索引数据和实际的数据等等都存在同一个文件，可想而知文件会很大，特别是数据表特别多的情况下，所以我们在读取文件记录的时候不会读取整个文件并且硬件条件也不允许【文件数据是存在硬盘上的，读取后会存在内存中，假如整个文件数据为1T，你不可能有1T的内存】，而是读取某一部分。内存和硬盘进行数据交互是以‘页’为单位的，页为逻辑上的概念，一般大小为4kb或8kb等4kb整数倍【InnoDB存储引擎每一次读取为16kb】，这个值和硬盘的扇区【512B】有关系。实际生产环境中，某个时刻会有多条记录或事务在进行并发执行，这就出现每个并发操作会对同一个页中的不同数据进行操作【例如事务A对这个页中id为1的数据进行修改，事务B对这个页中id为2的数据进行修改】，所以我们不能一整个页为整体去记录undo log日志，因为这样的话只能记录到一条数据操作日志，其他的就没法被记录到了。所以我们只能以当行操作去记录undo log日志【如上面的事务A要记录一条undo log日志记录，事务B则记录另外一条undo log日志记录】。所以逻辑日志就是指实际操作的并不是记录实际存储的一个物理页，而是记录每个页中每一行数据的更新信息。

持久性实现原理：Redo log（前滚日志/重做日志）

①从上面的MySQL架构中我们知道，当我们对MySQL中的数据进行修改时，并不是立马写到磁盘中，而是先写到缓存中，再写到磁盘中（fsync）。这时假设写到缓存中但是还没来得及写到磁盘中，系统就发生了断电，这时候重启系统数据也会丢失，很明显数据也不一致了。
这时候有一个概念叫WAL: Write-Ahead Logging 预写日志系统，数据库中一种高效的日志算法，对于非内存数据库而言，磁盘I/O操作是数据库效率的一大瓶颈。在相同的数据量下，采用WAL日志的数据库系统在事务提交时，磁盘写操作只有传统的回滚日志的一半左右，大大提高了数据库磁盘I/O操作的效率，从而提高了数据库的性能。
关于wal预写日志系统这里举个例子：假设你开了一个饭店，然后你有一个记账本，这个记账本十分厚有100万个人的记录，在你中午饭点时候饭店很多人你很忙，这时候有个人忘记带钱了要赊账，而你也不可能在这么忙的时候去100万个人的记账本中找出那个人，这时候你就可以弄一个小黑板，然后把那个人记下来，等有空余时间的时候再把小黑板中的记录记到记账本中。这里的小黑板就是相当于数据库事务中Redo log前滚日志。当然如果小黑板上面的记录记满了的话也要及时的把数据整理到记账本中。
②和Undo Log相反，Redo Log记录的是新数据的备份。==在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。==当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态（innodb_flush_log_at_trx_commit）。Redo Log是物理日志，它实际操作的是记录实际存储的一个物理页。

数据存储过程：
如下图所示，向数据库写数据的时候会先写到用户空间（当前进程内存中），在写到内核空间（操作系统内存），最后才写到磁盘中

Redo Log（前滚日志/重做日志）中innodb_flush_log_at_trx_commit参数说明：

• 0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。
  当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
• 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。
  当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。
• 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。
  当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
  

undo和redo对比总结：以当前时间点来区分，undo log是保持之前数据的状态，redo log是保持之后数据的状态。

隔离性实现原理：锁

①事务具有隔离性，理论上来说事务之间的执行不应该相互产生影响，其对数据库的影响应该和它们串行执行时一样。
②然而完全的隔离性会导致系统并发性能很低，降低对资源的利用率，因而实际上对隔离性的要求会有所放宽，这也会一定程度造成对数据库一致性要求降低。
③SQL标准为事务定义了不同的隔离级别，从低到高依次是：
读未提交（READ UNCOMMITTED）：对事务处理的读取没有任何限制，不推荐
读已提交（READ COMMITTED）
可重复读（REPEATEBLE READ：MySQL默认的隔离级别
串行化（SERIALIZABLE）

这篇文章讲的很详细，这里就不再扩展了：脏读、幻读和不可重复读 + 事务隔离级别
另外这一篇是代码演示：MYSQL演示关系型数据库的隔离级别

MySQL锁机制

• 在MySQL中，锁可以分为两类：
  共享锁：共享锁定是将对象数据别为只读形式，不能进行更新，所以也成为读取锁定（select 。。。。lock in share mode）；
  排它锁：排他锁定是当前执行INSERT/UPDATE/DALETE的时候，其它事务不能读取该数据，因此也成为写入锁定（例：select 。。。。for update）。
  （如果不加上面的锁，则MySQL默认情况下为一致性非锁定读；加了上面的锁则为一致性锁定读。
  另外InnoDB存储引擎和MyISAM存储引擎的锁是有区别的，InnoDB加的是行锁也可以加表锁，MyISAM加的是表锁，对于InnoDB存储引擎来说如果不通过索引条件查询的时候，则默认为表锁。）

• 排他锁和共享锁兼容性问题：两个都用共享锁的sql语句不会冲突，都能运行；其中有一个sql语句先使用排他锁的话，就会导致另外一条sql语句被锁死

兼容性	排他锁	共享锁
排他锁	不兼容	不兼容
共享锁	不兼容	兼容

• 基于锁的并发控制流程
  ①事务根据自己对数据项进行的操作类型申请相应的锁(读申请共享锁,写申请排他锁) 。
  ②申请锁的请求被发送给锁管理器。锁管理器根据当前数据项是否已经有锁以及申请的和持有的锁是否冲突决定是否为该请求授予锁。
  ③若锁被授予,则申请锁的事务可以继续执行;若被拒绝,则申请锁的事务将进行等待,直到锁被其他事务释放。

• 锁的粒度：锁定对象的大小是锁的粒度，分别有记录、表、数据库三个粒度

• 另外数据库也会有死锁的情况：如下图所视（这里先设定了sql语句不自动提交，即写完sql语句后还需要commit才会隐式的提交事务），因为该表有主键，所以sql语句执行是行锁，即对每一个行记录进行加锁（或者说每个索引；主键是特殊的索引）
  ①这时候左边第一条sql语句先对id为1的记录进行查询，但还没提交，右边第一条sql语句对id为2的记录进行查询也是没提交。
  ②这时候左边第二条sql语句对id为2的记录进行查询，这时候会先卡住，然后再去右边输入第二条sql语句对id为1的记录进行查询（对应第一个图），这是因为id为2的记录已经被右边sql语句先排他锁了。
  ③这时候到右边输入第二条sql语句对id为1的记录进行查询，这时候左边的第二条sql语句就会显示出结果，右边第二条sql语句则报错（对应第二个图），这是因为发生了死锁了然后InnoDB引擎对死锁有检测机制：会优先回滚undo log日志量比较少的，所以右边的undo log日志量比较少所以就回滚并爆出一个错误提示，而左边因为右边回滚了导致所释放所以id为2的语句获得行锁进而得到结果。

MySQL对死锁检测有两种方式：
第一种为超时等待，如果发生死锁，在一定时间内不能两方都不释放锁的话，会自动把undo log日志量少的先把锁释放掉。
第二种为等待图，图有结点和边组成，每个结点就代表一个事务，当这个图有回环的情况时候就代表发生了死锁，就会回滚。图有两种遍历规则：广度优先遍历和深度优先遍历，等待图就用深度优先遍历进行检测。

• 另外有一种锁叫间隙锁，这种锁比较少被提及到，间隙锁主要是来解决幻读这种情况的。举个例子有三行数据，分别为2、4和7。这三行数据用的就是行锁，而三行数据之间范围的数据就为间隙锁，如（-∞，2）、(2，4)、（4，7）和（7，+∞）用的就是间隙锁，用了间隙锁后就不能往中间这些范围插入数据，防止幻读现象出现。此外如果用的是唯一索引，则间隙锁蜕化为行锁。（有一种叫next key的锁，它表示行锁+间隙锁）

• 扩展：除了锁可以实现并发控制之外，还有其他策略：
  基于时间戳的并发控制
  基于有效性检查的并发控制
  基于快照隔离的并发控制

一致性实现原理

在事务的四个特点中，一致性是事务的根本追求，而在某些情况下会对事务的一致性造成破坏：
- 事务的并发执行
- 事务故障或系统故障
数据库系统通过并发控制技术和日志恢复技术来避免这种情况的发生
- 并发控制技术保证了事务的隔离性,使数据库的一致性状态不会因为并发执行的操作被破坏。
- 日志恢复技术保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失,保证了事务的持久性。

索引

索引大致内容如下：

• 索引的数据结构。
• 索引的分类。
• 索引的技术名词：回表、最左匹配、索引覆盖、索引下推
• 索引的优化

索引是什么？
索引帮助MySQL高效获取数据的数据结构。
索引存储在文件系统中。
索引的文件存储形式与存储引擎有关。（InnoDB存储引擎文件为idb文件，MyISAM存储引擎文件有两个，一个叫myd放数据文件，一个叫myi放索引文件）
索引文件的结构：hash（Memory存储引擎）、二叉树、B树、B+树（大部分数据库）。另外InnoDB存储引擎可以转化为自适应的hash表（即MySQL内部有判断机制会将B+树转换会hash表，但是该过程用户无法干预）。

索引的数据结构

索引的结构主要为B+树（官网上写了B树，但其实是B+树，另外还有hash索引和R-Tree，但是主要还是B+树）
那MySQL索引为什么选择用B+树，而不用别的数据结构，例如红黑树，二叉树或B树呢？
为什么MySQL数据库索引选择使用B+树？ 、 为什么Mysql用B+树做索引而不用B树 这两篇篇文章说的很详细，可以看一下。
这里大致说一下：

• 用hash表的索引格式：
  hash取模存储数据的时候可能照成有些桶的链表会特别长，而有些则很短，即存储散列不均匀，导致存储的浪费。
  利用hash存储的话需要将所有的数据文件添加到内存，比较耗费内存空间；
  如果所有的查询都是等值查询，那么hash确实很快，但是在企业或者实际工作环境中范围查找的数据更多，而不是等值查询，因此hash就不太合适了。
• 二叉树和红黑树的索引格式：无论是二叉树还是红黑树都会因为树的深度过深而造成io次数变多，影响数据读取的效率。（数据结构中各种树）
• B树的索引格式：
  B树相对上面的索引格式更加合适，B树有以下的特点
  优点：
  1.所有键值分布在整棵树中
  2.搜索有可能在非叶子结点结束，在关键字全集内做一次查找，性能逼近二分查找
  3.每个节点最多拥有M个子树
  4.根节点至少有2个子树
  5.分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
  6.所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列
  缺点：
  1.每个节点都有key，同时也包含data，而每个页存储空间是有限的，如果data比较大的话会导致每个节点存储key数量变小
  2.当存储的数据量很大的时候会导致深度较大，增大查询时磁盘io次数，进而影响查询性能

B树如下图所视，但是B树存取数据量还是不够大。

• B+树的索引格式：
  1.B+树每个节点可以包含更多的节点，这个做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将数据范围变为多个区间，区间越多，数据检索越快。
  2.非叶子节点存储key，叶子节点存储key和数据。
  3.叶子节点两两指针互相连接（符合磁盘的预读特性·），顺序查询性能更高。B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构，因此可以对B+树进行两种查找运算，另一种是从根节点开始，进行随机查找。
  
  

B+树的增删

了解B+树的增删之前先了解一下聚创索引和非聚簇索引：MySQL聚簇索引和非聚簇索引的理解
聚簇索引就有点像字典中按拼音排序的目录，目录的先后排序位置是和正文中先后的排序是一样的（例如：目录中拼音a在排第一，正文中拼音为a的文字也拍在最前面）。
而非聚簇索引就是除了拼音排序外的其他排序，例如偏旁部首的排序。他们正文的实际顺序和目录中的顺序不一致

然后是B树和B+树的增删（图有点多就不画了，这一篇已经讲的很详细了，而且不难理解）： B树和B+树的插入、删除图文详解

然后还要注意一点InnoDB和MyISAM虽然索引都是B+树结构，但是还是有区别的，如下图所视：InnoDB的叶子节点data是直接存数据（上文说过InnoDB存储引擎文件结构只有一个文件）；而MyISAM的叶子节点Data是存储地址值（MyISAM存储引擎文件结构有两个文件，一个叫MYD为数据文件，一个叫MYI为索引文件）

注意：

1. InnoDB是通过B+树结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么就会选唯一键，如果没有唯一键，那么会生成一个6字节的row id作为主键（上文说的聚簇索引）
2. 如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫做回表（非聚簇索引）
   

索引的分类

索引有以下的分类：主键索引、辅助索引、唯一索引、全文索引、组合索引

• 主键索引：数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

• 辅助索引：也叫普通索引或二级索引，最基本的索引类型，没有唯一性之类的限制。例如一个表中有ID号和NAME两个属性，ID号为主键，NAME为普通列，给NAME添加索引就是辅助索引。但是要注意的是，辅助索引的叶子节点数据块放的不是实际的行记录，而是主键，然后通过主键到主键索引中找实际的行记录。（叫做回表，上面的B+树增删的第一个链接：MySQL聚簇索引和非聚簇索引的理解，里面最后一个图和例子就是回表，不懂可以看一下）

  这里用两条语句，用上面说的例子：
  select * from table where name =a（要回表）
  select id from table where name=a（不需要回表，速度更快）
  

  上面的第二条SQL语句为覆盖索引：在查询辅助索引的时候，如果叶子节点中保存的刚好是要查询的字段数据，那么此时就叫做索引覆盖，换句话说查询列要被所建的索引覆盖。

• 组合索引：多个属性组成一个索引，即为组合索引。
  组合索引相关的知识点： 最左匹配原则。
  索引下推：
  select * from table where name=？and age=？其中name和age为组合索引。
  在MySQL5.6之前的解决方案为先根据name把所有的数据查询回来，然后再server层进行age字段的数据筛选。
  在MySQL5.6及以上版本则从存储引擎拉取数据的时候，会根据name，age两个字段做筛选，将符合条件的拉去回，这就是索引下推。详细一点可以看上面的链接。

  扩展：谓词下推
  直接一点看个例子：select t1.name,t2.name from t1 join t2 on t1.id=t2.id;这里有两个表进行连接查询，每个表有100个列。
  这时候有个问题思考一下，有两种执行方法，思考一下哪一种执行的更快？
  1、先把两张表按照id字段做关联，然后取出name字段。
  2、先把所有需要用到的字段查询出来，然后在做关联。
  答案是第2个，这里100个列为重点，也就是说除了name和id外还有98个属性，那么如果用第一种方法做关联时就会涉及到很多多余的数据，而第二种方法取出需要用的字段再关联相对会好很多。

• 唯一索引：不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。
  唯一索引和主键索引的区别：
  (1)对于主健/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；
  (2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；
  (3)主健可作外健，唯一索引不可；
  (4)主健不可为空，唯一索引可以；
  (5)主健也可是多个字段的组合；
  (6)主键与唯一索引不同的是：
  主键索引有not null属性；
  主键索引每个表只能有一个。

• 全文索引：这个用的比较少，大概了解一下。

MySQL的优化策略

MySQL explain详解

MySQL explain详解
explain select XXX from XXX这里有个关键字叫‘explain’，它是用来SQL语句的执行计划。

这里讲几个关键的列，具体可以看上面的链接：

• id：表示查询语句执行的顺序，有多少条select语句就有多少个id。语句的执行顺序也是按照id的顺序来的。
• type：表示语句的关联类型或访问类型。即MySQL决定如何查找表中的行。
  依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL（每个代表什么具体看链接）
• possible_keys：这一列显示查询可能使用哪些索引来查找。
• key：这一列显示mysql实际采用哪个索引来优化对该表的访问。
• Extra：一些额外的信息。
  1.distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了
  2.Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。（上文的覆盖索引）
  3.Using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
  4.Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
  5.Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

MySQL调优

1. 性能监控

• 使用show profile查询剖析工具，可以指定具体的type，具体如下：
  
  从上面的图可以看出我们查询的时候显示的时间为0.00秒，并不是真的0秒，而是时间过小无法显示
  这时候我们就要先把profiling开启，然后再进行查询一条语句。
  
  并用show profile显示出具体过程所需要的时间，左边为执行这条语句的过程，右边为具体多长时间。
  
  另外可以用上面的语句查看更多信息。
  *注意：官网显示show profile 在后续版本可能将会被弃用，提倡用performance schema

• 使用performance schema来更加容易监控MySQL
  performance schema是单独的一个库，这个库的存储引擎就是performance schema，它里面展示MySQL当前性能监控的东西（里面有87张表，这方面一般是由专业的DBA数据库管理员来做）。

• 使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征

2. schema与数据类型优化
   即在设计表时候应当就要对优化，而不是等到写SQL语句才来优化

• 数据类型的优化
• 合理使用范式和反范式
• 主键的选择
  代理主键：设置id值和业务无关则为代理主键
  自然主键：设置id值和业务相关则为自然主键，例如id号就是学生学号
• 字符集的选择
• 存储引擎的选择
• 适当的数据冗余
• 适当的拆分

3. 执行计划（上问explain已经提及到，或者自行百多MySQL执行计划）
4. 通过索引进行优化，上文已经说了很多关于索引的知识点，这里在进行简单总结和补充
   
5. 查询优化

• 查询慢的原因：网络、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间
• 优化数据访问：
  ①查询性能低下的主要原因是访问的数据太大，某些查询不可避免的需要筛选大量的数据，我们可以通过减少范文数据量的方式进行优化：一、确认应用程序是否在检索大量超过需要的数据；二、确认mysql服务层是否在分析大量超过需要的数据行。
  ②是否向数据库请求了不需要的数据：一、查询不需要的记录；二、多表关联时返回了全部列；三、总是取出全部列；四、重复查询相同的数据
• 执行的优化：查询缓存；查询优化处理。
• 优化特定类型的查询：
  ①优化count（）查询；②优化关联查询；③优化子查询；④优化limit分页：优化此类查询的最简单的方法是尽可能地使用覆盖索引，而不是查询所有的列；⑤优化union查询：除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高；⑥推荐使用用户自定义变量

6. 分区表

• 分区表的应用场景
• 分区表的限制
• 分区表的原理
• 分区表的类型
• 如何使用分区表
• 在使用分区表的时候要注意的问题

7. 服务器参数设置

• general：通用参数
• character：字符集参数
• connection：连接参数
• log：日志参数
• cache：缓存参数
• INNODB：InnoDB的相关参数

扩展：MySQL集群相关知识点，主从复制、读写分离、分库分表（后续学习）