这篇文章主要给大家分享的是mysql 查询优化的内容，对mysql做查询优化还是很有必要的，但是有一些原则和一些注意事项大家要注意，下面我们就一起来学习一下mysql 查询优化吧。

    一、优化的思路和原则有哪些

    1、优化更需要优化的查询

 高并发的低消耗（相对）的查询 对整个系统影响远大于低并发高消耗的查询。

    2、定位优化对象的性能瓶颈

 在拿到一条需要优化的查询时，我们首先要判断出这个查询的瓶颈到底是IO还是CPU。到底是数据库访问消耗多还是数据的运算（如分组排序）消耗多。

    3、明确优化的目标

 了解数据库目前整体状态，就能知道数据库所能承受的最大压力，也就是我们知道最悲观状况；
 要把握该查询相关的数据库对象信息，我们就能知道最理想和最糟糕状态下需要消耗多少资源；
 要知道该查询在应用系统中的地位，我们可以分析出改查询可以占用系统资源的比例，也能够知道该查询的效率对客户的体验影响有多大。

    4、从Explain入手

    Explain能够告诉你这个查询在数据库中是一个什么样的执行计划来实现的。首先我们需要有个目标，通过不断调整尝试，再借助Explain来验证结果是否满足自己的需求，直到得到预期的结果。

    5、永远用小结果集驱动大结果集

 很多人喜欢在SQL优化的时候说用“小表驱动大表”，这个说法是不严谨的。因为大表经过where条件过滤后返回的结果集并不一定就比小表所返回的结果集大，这个时候还用大表驱动小表，就会得到相反的性能效果。
 这样的结果也非常容易理解，在 MySQL 中的 Join，只有 Nested Loop 一种 Join 方式，也就是MySQL 的 Join 都是通过嵌套循环来实现的。驱动结果集越大，所需要循环的此时就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑 IO 很少，循环次数多了，总量自然也不可能很小，而且每次循环都不能避免的需要消耗CPU，所以 CPU 运算量也会跟着增加。所以，如果我们仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就是需要的嵌套循环中带来更多的循环次数，反之，所需要的循环次数就会更少，总体 IO 量和 CPU 运算量也会少。而且，就算是非 Nested Loop 的 Join 算法，如 Oracle 中的 Hash Join，同样是小结果集驱动大的结果集是最优的选择。
 所以，在优化 Join Query 的时候，最基本的原则就是“小结果集驱动大结果集”，通过这个原则来减少嵌套循环中的循环次数，达到减少 IO 总量以及 CPU 运算的次数。尽可能在索引中完成排序

    6、只取出自己需要的字段（Columns）

 对于任何查询，返回的数据都是需要通过网络数据包传输给客户端，如果取出的Column越多，需要传输的数据量自然会越大，不论从网络带宽还是网络传输缓冲区来看，都是一种浪费。

    7、仅仅使用最有效的过滤条件

 举个例子一个用户表user有id和nick_name等字段，索引是id和nike_name两个索引，下面是两个查询语句

#1
select * from user where id = 1 and nick_name = 'zs';
#2
selet * from user where id = 1

 两个查询得到结果是一样的，但是第一个语句用到的索引占用空间是比第二个语句大很多的。占用空间大也代表着要读取的数据量也更多。，也就是说2的查询语句才是最优查询。

    8、避免复杂的join查询

 我们的查询语句所涉及到的表越多，所需要锁定的资源就越多。也就是说，越复杂的 Join 语句，所需要锁定的资源也就越多，所阻塞的其他线程也就越多。相反，如果我们将比较复杂的查询语句分拆成多个较为简单的查询语句分步执行，每次锁定的资源也就会少很多，所阻塞的其他线程也要少一些。
 可能很多人会有疑问，将复杂 Join 语句分拆成多个简单的查询语句之后，那不是我们的网络交互就会更多了吗？网络延时方面的总体消耗也就更大了啊，完成整个查询的时间不是反而更长了吗？是的，这种情况是可能存在，但也并不是肯定就会如此。我们可以再分析一下，一个复杂的查询语句在执行的时候，所需要锁定的资源比较多，可能被别人阻塞的概率也就更大，如果是一个简单的查询，由于需要锁定的资源较少，被阻塞的概率也会小很多。所以 较为复杂的连接查询也有可能在执行之前被阻塞而浪费更多的时间。而且我们的数据库所服务的并不是单单这一个查询请求，还有很多很多其他的请求，在高并发的系统中，牺牲单个查询的短暂响应时间而提高整体处理能力也是非常值得的。优化本身就是一门平衡与取舍的艺术，只有懂得取舍，平衡整体，才能让系统更优。

    二、利用 Explain和Profiling

    1、Explain使用

    各种信息展示

    2、Profiling使用

    该工具可以获取一条Query在整个执行过程中多种资源消耗情况，如CPU,IO,IPC,SWAP等，以及发生PAGE FAULTS, CONTEXT SWITCHE等等，同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置。

    1、开启profiling参数 1-开启，0-关闭

#开启profiling参数 1-开启，0-关闭set profiling=1;SHOW VARIABLES LIKE '%profiling%';

    2、然后执行一条Query

    3、获取系统保存的profiling信息

show PROFILES;

    4、通过QUERY_ID获取profile的详细信息（下面以获取CPU和IO为例）

show profile cpu, block io for QUERY 7;

    三、合理利用索引

    1、什么是索引

 简单来说，在关系型数据库中，索引是一种单独的，物理的对数据库表中一列或者多列的值进行排序的一种存储结构。就像书的目录，可以根据目录中的页码快速找到需要的内容。
 在MySQL中主要有四种类型索引，分别是：B-Tree索引，Hash索引，FullText索引，R-Tree索引，下面主要说一下我们常用的B-Tree索引，其他索引可以自行查找资料。

    2、索引的数据结构

 一般来说，MySQL中的B-Tree索引的物理文件大多数都是以平衡树的结构来存储的，也就是所有实际需要存储的数据都存储于树的叶子节点，二到任何一个叶子节点的最短路径的长度都是完全相同的。MySQL中的存储引擎也会稍作改造，比如Innodb存储引擎的B-Tree索引实际上使用的存储结构是B Tree,在每个叶子节点存储了索引键相关信息之外，还存储了指向相邻的叶子节点的指针信息，这是为了加快检索多个相邻的叶子节点的效率。
 在Innodb中，存在两种形式的索引，一种是聚簇形式的主键索引，另外一种形式是和其他存储引擎（如MyISAM）存放形式基本相同的普通B-Tree索引，这种索引在Innodb存储引擎中被称作二级索引。

 图示中左边为 Clustered 形式存放的 Primary Key，右侧则为普通的 B-Tree 索引。两种索引在根节点和 分支节点方面都还是完全一样的。而 叶子节点就出现差异了。在主键索引中，叶子结点存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数据以主键值有序的排列。而二级索引则和其他普通的 B-Tree 索引没有太大的差异，只是在叶子结点除了存放索引键的相关信息外，还存放了 Innodb 的主键值。
 所以，在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过二级索引来访问数据的话，Innodb 首先通过二级索引的相关信息，通过相应的索引键检索到叶子节点之后，需要再通过叶子节点中存放的主键值再通过主键索引来获取相应的数据行。
 MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的二级索引的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在叶子节点上面除了存放索引键信息之外，再存放能直接定位MyISAM 数据文件中相应的数据行的信息（如 Row Number），但并不会存放主键的键值信息。

    3、索引的利弊

    优点： 提高数据的检索速度，降低数据库的IO成本；
    缺点：查询需要更新索引信息带来额外的资源消耗，索引还会占用额外的存储空间

    4、如何判断是否需要建立索引

 上面说了索引的利弊，我们知道索引并不是越多越好，索引也会带来副作用。那么我们该怎么判断是否需要建立索引呢？
    1、 较频繁的作为查询条件的字段应该创建索引；
    2、更新频繁的字段不适合建立索引；
    3、唯一性太差的不适合创建索引，如状态字段；
    4、不出现在where中的字段不适合创建索引；

    5、单索引还是组合索引？

 在一般的应用场景，只要不是其中某个过滤字段在大多数场景下都能过滤90%以上的数据，而且其他的过滤字段会频繁更新，我一般更倾向于创建组合索引，尤其是在并发量较高的场景下更是如此。因为并发量搞的时候，即使我们为每个查询节省很少IO消耗，但因为执行量非常大，所节省的资源总量还是很大的。
 但是我们创建组合索引并不是说查询条件中的所有字段都要放在一个索引中，我们应该让一个索引被多个查询所利用，尽量减少索引的数量，以此来减少更新的成本和存储成本。
 MySQL为我们提供了一个减少优化索引自身的功能，那就是“前缀索引”。也就是我们可以仅仅使用某个字段的前面部分内容作为索引键来索引该字段,减少索引所占用的空间和提高索引的访问效率。当然前缀索引只适合前缀比较随机重复很少的字段。

    6、索引的选择

    1、对于单键索引 ，尽量针对当前查询过滤最好的索引；
    2、在选择组合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中排列越靠前越好；
    3、在选择组合索引的时候，尽量选择可以能够包含当前查询的where字句中更多字段的索引；
    4、尽可能通过分析统计信息和调整查询的写法来达到选择合适的的索引来减少通过人为Hint控制索引的选择，以为这样后期维护成本会很高。

    7、MySQL索引的限制

    1、MyISAM存储引擎索引键长总和不能超过1000字节；
    2、BLOB和TEXT类型字段只能创建前缀索引；
    3、MySQL不支持函数索引；
    4、使用 != 或者<>时候，MySQL索引无法使用；
    5、过滤字段使用函数运算后，MySQL索引无法使用；
    6、jion语句中近字段类型不一致的时候，MySQL索引无法使用；
    7、使用like如果是前匹配（如：’