深入理解mysql优化(InnoDB).pdf

深入理解SQL 优化(InnoDB) (个人理解，仅供参考，欢迎争论，共同进步) 郑永涛 本文主要讲解了MySQL 查询优化器、索引的数据结构、锁定机制及实现原理，并根据这些因素探讨高 效的SQL 语句的写法，最后还简单说明了一下MySQL 中的分区表。 大家都知道马航飞机失联这个事件，前后有十多个国家动用大量的卫星、军舰、飞机参与搜救，资源 投入不能说不大，然而遗憾的是：至今已经过去20 多天了，依然没有任何消息。 这是为什么呢？站在不同的立场，可能会给出很多不同的理由，但有一点是大家都赞同的：那就是搜 索范围太广。 我们再来回想一下“汶川地震”这一国难，总理亲自指挥，动用了军队，投入了大量的人力、物力参 与搜救，可以说是举全国之力了，但依然有那么多同胞失去了生命。 这又是为什么呢？一个最直接的原因就是：被搜救的人数太多了。 通过这两个事件，我们来联想一下工作中所使用的数据库管理系统：如果把一个SQL 语句的执行比作 一次搜救任务的话，那么影响这次搜救效率的因素包括哪些呢？ 显而易见，首先是资源投入，相对于数据库系统来说，就是服务器（CPU、内存、硬盘 I/O）、网络等 硬件资源，以及OS、DBMS 等软件资源，但这些因素基本上都是固定的，或者说在一段时间内是固定的，一 旦确定下来，后期几乎不会经常变动。 抛开这些固定的因素，那么如何才能提高SQL 语句的执行效率呢？通过上面两个例子，我们可以总结 出两个结论：  尽量缩小检索范围  尽量减小检索的结果集 这两点也是SQL 语句优化的基本原则，不管任何数据管理库系统，其自身的很多机制其实都是围绕这 两点来进行的，比如：优化器、索引、锁定机制、分区表、SQL 语句的写法、事务、字段类型等等，以及 包括人为的分表、分库、分实例，下面我们就从这些方面来深入探讨学习SQL 语句的优化。 一．优化器 在介绍具体的SQL 语句优化方法之前，首先来了解一下MySQL 查询优化器的工作原理，以及它对SQL 语句会做哪些方面的处理工作。 1. 优化器的定义 任何关系型数据库管理系统内部都存在优化器这一基本功能，比如：Oracle 数据库内部就存在RBO （基 于规则的优化器）、CBO （基于成本的优化器）两种优化器；MySQL 也不例外，自身提供了查询优化器。 简单来说，优化器的主要作用就是根据SQL Parse Tree 和一系列统计信息（Table/Index statistics） 为要执行的SQL 语句选择一个最有效的执行计划 （QEP：Query Execute Plan）。 2. 优化器的工作 MySQL 查询优化器对所提交的SQL 语句到底能做哪些方面的优化呢？我们从以下几个方面来描述：  常量转换 对SQL 语句中的常量进行转换，比如：WHERE col1＝col2 AND col2＝x;依据传递性：如果A=B and B=C，那么A＝C，所以对于上面的表达式，MySQL 查询优化器进行如下优化：WHERE col1=x AND col2=x; 这类操作符还包括：=,,,=,=,,like 等，但对于BETWEEN 操作不进行转换。  无效代码的排除 MySQL 查询优化器会对一些无用的条件进行过滤，比如：WHERE 0=0 AND col1=x;因为第一个条件始 终为TRUE，所以移除该条件不会影响结果集，因此判断为无效代码而排除，变成：WHERE col1=x;再如： WHERE (0=1 AND s1=5) OR s1=7;因为括号内的表达式始终为FALSE，因此也判断为无效代码而排除，变成： WHERE s1=7;有些情况下，甚至可以将整个WHERE 子句排除，如：WHERE 1=1;WHERE子句始终为TRUE，相当 于没有WHERE 条件，因此也会被优化掉。 再说一种情况：如果一个列col 的定义是不允许为NULL 的，那么WHERE col IS NULL 这个条件始终为 FALSE，WHERE col IS NOT NULL 这个条件始终为TRUE，因此这样的表达式也可以从条件表达式中删除。 当然，也有特殊的情况，比如在OUT JOIN 中，被定义为NOT NULL 的列也可能包含NULL 值，这种情况 下，IS NULL 条件是被保留的。 上面只是简单的例子，在实际使用过程中，由于出现无效代码的情况实在是太