MySql高级（上）之索引优化分析和EXPLAIN关键字

1、MySql的逻辑架构简介

1、1 整体架构图

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同的场景中应用并发挥良好的作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎

1、1、1 连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接，服务器也会为安全接入的每个客户端验证它所具有操作权限。

1、1、2服务层

• Management Services & Utilities :系统管理和控制工具
• SQL Interface：sql接口，接受用户的sql命令，并且返回用户需要查询的结果，比如select from 就是调用SQL Interface
• Oarser：解析器。SQL命令传递到解析器的时候就会被解析器验证和解析
• Optimizer：查询优化器。SQL语句在查询之前会使用查询优化器进行优化，比如有where条件时，优化器来决定先投影还是先过滤
• Cache 和 Buffer：查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取 数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存， 权限缓存等

1、1、3 引擎层

存储引擎层，存储引擎层真正负责了Mysql中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

1、1、4 存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成于存储引擎的交互

1、2 show profile

利用show profile可以查看sql的执行周期

1、2、1 开启profile

查看profile是否开启？：show variable like’%profiling%’;

如果没有开启，可以执行set profiling=1开启！

1·、2、2 使用profile

执行show profiles命令，可以查看最近的几次查询

根据Query_ID,可以进一步执行show profile cpu,block io for query Query_id 来查看sql的具体执行步骤

1、2、3 大致的查询流程

mysql的查询流程大致是：
mysql客户端通过协议与mysql服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器会先访问查询缓存（query cache）–它存储SELECT语句以及相应的查询结果集，如果某个查询结果已经位于缓存中，服务器就不知再对查询进行解析、优化、以及执行。他仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法接洗和预处理：首先musql通过关键字将SQL语句进行解析，并生成一颗对应的"解析树"。mysql解析器将使用mysql语法规则验证和解析查询；预处理则根据一些mysql规则进一步查询解析数是否合法
查询优化器当解析树被认为是合法的了，并且由优化器将其转换成执行计划，一条查询语句可以有很多种执方式，最后都是返回i相同的结果，优化器的作用就是找到这其中最好的执行计划。
然后mysql默认使用的BTREE索引，并且一个大致方向是：无论怎么折腾sql，至少在目前来说，mysql最多用到表中的一个索引。

1、2、4 SQL的执行顺序

手写的顺序：

真正的执行顺序：
随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同的执行顺序产生的性能消耗不同而动态执行顺序，下面是进厂出现的查询顺序：

1、2、4 MyISAM和InnoDB

show engines:查看所有的数据库引擎

show variables like ‘%storage_engine%’ 查看默认的数据库引擎

2、索引的优化分析

2、1 索引的概念

2、1、1 是什么？

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质；索引是数据结构，可以简单理解为排好序的快速查找数据结构
在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引，下图就是一种可能的索引方式示例：

左边是数据表，一共有七条数据，最左边是数据记录的物理地址，为了加快Gol2的查找，可以维护一个右边所示的二叉查找树，每个节点包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

2、1、2 优缺点

优点：

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

劣势

• 虽然索引大大提升了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATA和DELETE，因为更新表达的时候，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整以内更新所带来的键值变化后的索引信息
• 实际上索引也是一张表，该表保存了主键与所引字段，并指向实体表的记录，所以索引列也是要占用空间的。

2、2 Mysql的索引

2、2、1 Btree 索引

MySQL使用的是Btree索引

【初始化介绍】
一颗b树，浅蓝色的块我们称之为一个磁盘，可以看到每个磁盘块中包含几个数据项和指针（黄色所示）
如磁盘块1中包含数据项17和35 包含指针P1、P2、P3
P1这个指针指向小于17的磁盘块，P2指向大于17但是小于35的磁盘块，P3表示大于35的磁盘块
但是真实的数据只有3、5、9、10、13、15、28、29、36、68、75、79、90、99
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如