MySQL架构

在介绍MySQL之前，我们先了解一下MySQL的架构，分别从宏观和细节两个方面出发。

宏观

从宏观上来看，MySQL Server 端的架构分为两层，分别是：SQL 层，以及存储引擎层。其中SQL层负责处理与SQL相关的业务，存储引擎层负责数据的存储。

架构图

实现细节

• 初始化模块：启动时进行初始化操作
• 连接管理模块：启动连接的监听
• 连接进程模块：相当于连接池
• 用户模块：用户的管理及鉴权
• 命令分发器：分发命令，query，command
• 命令解析器：即parser，对命令进行解析
• 访问控制模块：根据用户模块的用户信息，以及数据库自身特有的约束信息，来实现对用户的访问控制
• 表管理模块：维护表的定义文件，即管理.frm文件
• 存储引擎接口模块：存储引擎的高度抽象，正因为此模块，才有了MySQL可插拔的存储引擎的特色。

存储引擎 Innodb 与 Myisam 对比

索引

在我们的生活中有很多索引应用的例子，比如，字典，数据目录；它们的原理都是通过缩小数据检索的范围，以便更快的获得目标数据；同样的数据库的索引也是同样的道理，但是却别字典等情况复杂的多，除了我们常见的等值查询以外，还有复杂的范围查询(in,><,like)；就这样B+Tree应运而生，它的特点就是树的高度可控，即把每次查询数据需要的磁盘IO控制在一个较小的数据量级。

建索引的几大原则

• 最左前缀原则，mysql会从左到右一直匹配知道遇到范围查询(>，<, between ,like)
• = , in可以乱序，例如 where a=1 and b=2 and c=3 假如我们创建的索引是针对c的，那么查询优化器会将sql优化成索引可识别的形式
• 选择区分度高的列建立索引，可以参考 rate=count(distinct (col)) / count(*) ，rate表示不重复比例，区分度越高，扫描到的数据越少，唯一性的键 rate=1
• 索引列不能参与计算，保持索引列的简洁纯净。
• 尽量扩展索引，不要新建索引。

SQL执行计划

我们可以利用SQL执行计划，来分析慢SQL，对SQL进行一定程度的优化，使用起来也很简单，在查询语句前加上explain即可。

我们一起来看以下示例，各项数据内容如下：

示例

• id : 选定执行计划中查询的序列号
• select_type : 所使用的查询类型，主要包含以下几种类型
  • DEPENDENT SUBQUERY：子查询中内层的第一个SELECT，依赖于外部查询的结果集
  • DEPENDENT UNION：子查询中的UNION，且为UNION中第二个SELECT开始，后面所有的SELECT，同样依赖于外部查询结果集
  • PRIMARY：子查询中的最外层查询，注意不是主键查询
  • SIMPLE：除子查询或者UNION外的其他查询
  • SUBQUERY：子查询中内层的第一个SELECT，且不依赖于外部查询的结果集
  • UNCACHEABLE SUBQUERY：无法缓存结果集的子查询
  • UNION：UNION中第二个SELECT开始，后面所有的SELECT；第一个SELECT为PRIMARY
  • UNION RESULT: UNION中的合并结果
• table 数据库中访问的表
• type 告诉我们对表的访问时何种形式
  • all ： 全表扫描
  • const：读常量
  • eq_ref：最多只会匹配一个结果，一般是主键或者唯一索引的查询
  • fulltext：全文索引检索
  • index：全索引扫描
  • index_merge：查询中使用两个或多个索引，然后对索引结果merge之后再读取表数据
  • index_subquery：子查询中返回结果字段是一个索引（或者组合索引），但不是主键或者唯一索引
  • range：索引范围扫描
  • ref：Join 语句中被驱动表的索引引用查询
  • ref_or_null：与ref的唯一区别就是在ref之外再增加一个空值查询
  • system：系统表，表中只有一行数据
  • unique_subquery：子查询中返回结果字段是一个主键或者唯一索引

由好到差的顺序依次是：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，all

• possible_keys：该查询可以利用的索引，如果没有显示null
• key：从possible_keys选取的key
• key_len：被选中使用索引的索引键长度
• ref：列出是通过常量，还是某个表的某个字段来查找key的
• rows：结果集记录条数
• extra：查询中每一步额外实现的细节信息
  • Distinct：查找distinct值
  • Full scan on NULL key：子查询中一种优化方式，主要在遇到通过索引无法访问NULL
    值时使用
  • Impossible WHERE noticed after reading const table：MySQL Query Optimizer通过收集到的信息判断不可能存在结果
  • No tables：Query语句中使用from dual，或者不包含任何from 子句
  • Not exists：在某些左连接中MySQL Query Optimizer通过改变原有Query而组成的优化方法，可以部分减少数据访问次数
  • Range checked for each record：查询优化器发现没有好的索引可以使用，尝试使用range 或者 index_merge 访问方法来索取行
  • Select tables optimized away：使用聚合函数访问某个存在索引的字段时，查询优化器会直接一次定位到所需的数据行来完成整个查询；前提是没有使用GROUP，例如使用MIN()，MAX()的时候
  • Using filesort：当我们的语句中包含ORDER BY，无法通过索引完成操作时，不得不使用排序算法来实现
  • Using index：所需数据只用在index下就可全部获得，而不需到表中取数据
  • Using index for group-by：类似于Using Index，不同的是使用了GROUP BY或者 DISTINCT。
  • Using temporary：使用临时表时，extra会显示Using temporary；常见于GROUP BY 和 ORDER BY 等操作中
  • Using WHERE with pushed condition：仅在NDBCluster中才会出现的信息，而且还需要通过打开 Condition Pushdown 优化功能才能被访问。