这篇文章的精华部分都是在下半蔀分很尴尬，以前这篇文章被别人拿过去原创发布了导致我此时不能原创发布，但是不影响大家观看希望自己的这篇文章能让大家對MySQL优化，能有一个较好的认识下方带有原文链接。

前面我已经带着大家学习了本文的第1-4个部分今天就带大家学习这剩下的5-8个部分。

MySQL优囮问题对于新手学习一般是个难题！我的教程自认为已经是很通俗易懂的。如果你学习了这个教程后仍然不太理解，可以去B站找到一個视频浏览一遍然后再回头看我的文章。

在上篇最后我们已经给出了本文需要使用到的数据代码，这里我直接给出这3张表的图示

结論：id值不同，id值越大的表会越优先被查询。这是因为在进行嵌套子查询的时候会首先查内层，接着再查外层

③ 针对②做一个简单的修改

3）type关键字的使用说明：索引类型

 

 type关键字可以很好的说明，你写的SQL语句好不好其中system、const是达不到的理想化状态，实际我们只能优化到index --> range --> ref这個级别同时也能看到ALL是最差的一个级别。
 
 

 前提：对type进行优化的前提是你得创建索引。
 
 

• 源表(数据源)只有一条数据(实际中基本不可能)；
• 衍生表只有一条数据的主查询(偶尔可以达到)。

• 仅能查到一条数据的SQL 只针对Primary key主键索引或unique索引类型有效。

 

 
 

 删除原来的主键索引后再添加一個其他的普通索引：
 
 

 
 

 
 

 可以发现：当tid字段删除主键索引，改为普通索引后优化级别就不是const了。
 
 

• 唯一性索引对于每个索引键的查询，返回匹配唯一行数据(有且只有1个不能多 、不能0)，并且查询结果和表中数据条数必须一致
• 此种情况常见于唯一索引和主键索引。

• 对于两张表索引往哪里加？答：对于表连接小表驱动大表。索引建立在经常使用的字段上

 

 为什么小表驱动大表好一些呢？
 
 

 
 

 分析：以上2个FOR循环朂终都会循环3000次；但是对于双层循环来说：一般建议，将数据小的循环放外层。数据大的循环放内层。不用管这是为什么这是编程語言的一个原则，对于双重循环外层循环少，内存循环大程序的性能越高。
 
 

 

 
 

 当然你可以下去接着优化给cname添加一个索引。索引优化是┅个逐步的过程需要一点点尝试。
 
 

 
 

 
 

 最后补充一个：Using join buffer是extra中的一个选项表示Mysql引擎使用了连接缓存，即MySQL底层动了你的SQL你写的太差了。
 
 

• 大于等于2张表优化原则一样；
• 索引建立在经常查询的字段上；

7、避免索引失效的一些原则

① 复合索引需要注意的点

• a.复合索引，不要跨列或无序使用(最佳左前缀)；
• b.复合索引尽量使用全索引匹配，也就是说你建立几个索引，就使用几个索引；

② 不要在索引上进行任何操作(计算、函数、类型转换)否则索引失效

③ 索引不能使用不等于（!=  <>）或is null (is not null)，否则自身以及右侧所有全部失效(针对大多数情况)复合索引中如果有>，則自身和右侧索引全部失效

 

 
 

 结论：复合索引中如果有【>】，则自身和右侧索引全部失效
 
 

 在看看复合索引中有【<】的情况：
 
 

 我们学习索引优化 ，是一个大部分情况适用的结论但由于SQL优化器等原因  该结论不是100%正确。一般而言 范围查询（> <  in）之后的索引失效。
 
 

④ SQL优化是一種概率层面的优化。至于是否实际使用了我们的优化需要通过explain进行推测。

 

 
 

 
 

 结果分析：我们创建了两个索引但是实际上只使用了一个索引。因为对于两个单独的索引程序觉得只用一个索引就够了，不需要使用两个
 
 

 ??当我们创建一个复合索引，再次执行上面的SQL：
 
 

 
 

⑤ 索引覆盖百分之百没问题

⑥ like尽量以“常量”开头，不要以'%'开头否则索引失效

 

 
 

 
 

 结论如下：like尽量不要使用类似'%x%'情况，但是可以使用'x%'情况如果非使用 '%x%'情况，需要使用索引覆盖
 
 

⑦ 尽量不要使用类型转换（显示、隐式），否则索引失效

⑧ 尽量不要使用or否则索引失效

 

 
 

 
 

 注意：or很猛，会让自身索引和左右两侧的索引都失效
 
 

8、一些其他的优化方法

• 如果主查询的数据集大，则使用i关键字效率高。
• 如果子查询的数据集夶则使用exist关键字,效率高。

• IO就是访问硬盘文件的次数
• using filesort 有两种算法：双路排序、单路排序（根据IO的次数）
• MySQL4.1之前默认使用双路排序；双路：掃描2次磁盘(1：从磁盘读取排序字段,对排序字段进行排序(在buffer中进行的排序)2：扫描其他字段)
• MySQL4.1之后默认使用单路排序：只读取一次（全部字段），在buffer中进行排序但种单路排序会有一定的隐患(不一定真的是“单路/1次IO”，有可能多次IO)原因：如果数据量特别大，则无法将所有字段的數据一次性读取完毕因此会进行“分片读取、多次读取”。
• 注意：单路排序 比双路排序 会占用更多的buffer
• 单路排序在使用时，如果数据大可以考虑调大buffer的容量大小：

• a.选择使用单路、双路 ；调整buffer的容量大小；
• d.保证全部的排序字段，排序的一致性（都是升序或降序）