有关索引的机制中我们已经在《话说Mysql索引机制》 中介绍过，在这篇文章中主要想要讲述的是Mysql的常见引擎以及索引的分类、常规的索引优化建议与背后的底层机制。关于优化建议与底层机制的时候主要还是介绍原来的话说Mysql索引机制来说的，所以可以先看一下前一篇文章！
一、Mysql中的引擎与索引分类
个人理解引擎就是Mysql一种系统的环境或者说就像是汽车的引擎，这个引擎我们是可以替换的，然而不同的汽车引擎会给汽车提供一些不同的特性，这个特性就可以类比于我们的索引。下面首先先从Mysql的结构上入手说一下引擎在Mysql中所谓的位置吧：
Mysql的层级可以分为连接层、业务逻辑处理层（存过过程、函数、以及临时表等都是在一层实现的）、数据存储引擎层（常见的引擎有MyISAM、InnoDB），不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。这些引擎都也是可插拔的，在我们建表的时候是可以指定具体的引擎，现在默认的引擎都是Innodb。最下面的是数据存储层。关于MyISAM、InnoDB的区别，知乎的一篇文章讲的很好：
- MyISAM使用的是表级锁、Innodb使用的行级锁；由于MySIAM锁的粒度更大所以产生资源竞争的可能性就更大，他更适合与对查询要求更高的表；如果一个表对修改的要求更高则首选的应该是Innodb引擎，他对事务的支持也更好。当我们考虑一个临时存储表的时候可以考虑MEMORY引擎
- 常见的这两种引擎索引中使用的数据结构都是B+树，不同之处在于MyISAM的索引节点中存储的是数据的地址而Innodb索引的叶子节点中存储的是数据——也就是我们在Mysql的底层存储结构中分析的那种情况。因为MyISAM中的索引与数据是分开存储的，所以他的索引也叫作非聚簇索引而Innodb中的索引与数据是存储在一起的所以他们的索引结构也叫作聚簇索引。
二、索引优化
1、全值匹配我最爱&最佳左前缀
在前面的文章中我们介绍了索引的结构，在符合索引中，相当于是多层级排序。在尚硅谷的课程中老师是以梯子为比喻讲解的，想想确实还是挺形象的。但是这个结构我感觉更形象的是可以比作是在全国范围内找一个人。我们可以从省、市、县/区、镇、村来定位，我们最终想要查找的数据就是人。到了村之后如果是普通的人（村长、或书记；相当于是页结构中的最大值、最小值），还需要在村子的范围内寻找。这些人的身份证号是一样的，所以他们是不可能重复的。村中大队的概念就又像是业结构中组的概念，只不过是业结构中的“主键”与组有关联的——主键是相连的。但是我们村子中是没有这个关联的。
上面的这个比喻还有一点不同的就是如果我们省略了省级单位，直接从市开始定位，在行政区域看来实际上还是可以较快定位的。然而索引中不是，他会直接导致崩塌。从本质上来说是因为他们一个是只有一个上级（洛阳市对应的只有河南省），而索引上的并没有这个关系，他们是平级,（我承认到这里我也说迷糊了）我们可以说成是在哪一个省区都有这个
2、不在索引上做任何操作
如果索引与目标值是需要通过某种装换关系才能得到的话，那么我们通过索引上存储的列并不能够直接的比对出来值，而是需要经过计算的。那么这就是一种模糊的情况，所以我们需要去遍历所有的列去计算，从而得出是否是我们所需要参照的值。对索引的操作，其中包括一个较为隐藏的就是索引字符段的转换。
3、范围或是不确定条件会导致索引失效
前面通过固定值去查找的时候我们的寓意是去找到一个或是多个固定的值，只要将我们要找的数据都找到了就可以，所以是不需要全表扫描的。但是当是范围或是不确定的时候，既有可能就会造成全表扫描。但是也未必，如果我们的最找要找的值是在索引的前半部分，并且是可以根据排序大小确定范围的，那么个人推测那就是不需要全表的，如果如果要是位于后半部分的，那就是需要的。前边的是查找比对后边的是读取。
上面加黑的这一段话是自己的猜测但是表名是错误的，mysql还是存在索引优化机制的，同时在查询类型中还存在一个range的类型
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TyfFkV5f-1647091258170)(F:\typroa\aimages\image-20220312142039247.png)]](https://img-blog.csdnimg.cn/05e59916523d4644a557f9fcf55caf69.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA56CB5rak55Sf,size_20,color_FFFFFF,t_70,g_se,x_16)
关于不确定值查询的还有一种情况就是模糊匹配，如果左侧也是有通配符的时候那么索引就会失效，如果要是只有右侧有通配符的话索引还是可以起作用的。下面是关于or的使用情况，还是那句话Mysql没有那么憨批，我使用的是mysql8，不同的版本情况会有不同。
4、尽量使用覆盖索引
使用覆盖索引的话通常来说我们是创建了二级索引的，在二级索引的结构中会存在索引列中的值，如果要是select中的值都是存在于索引结构中的那么我们就不需要在回表一次进行数据读取，直接在索引结构中就可以读取了。
覆盖索引值用到半与完全没有用到应该是一样的效果吧，毕竟都是还需要在去磁盘或是缓冲池中读的
表中一些关键值要设置默认值而不能直接为空，这样不利于索引使用
Mysql中的单引号与双引号有什么区别呢
5、联表查询被驱动表需要建立索引
联合查询通常是我们使用join的语句，关于join的时候那一张表是驱动表就是我们在前面join相关文章中说的主表，被驱动表就是利用主表的信息再去检索的表。当不是inner的时候驱动表不论如何都是需要进行全表扫描的，但是被驱动表确实根据on中的条件去检索所以说尽可能的在被驱动表中建立合适的索引，提高关联的一个速率。当使用inner的时候，Mysql会自动的选择小的结果集作为驱动表。
6、? 子查询中关于inner join与in、exist的使用
有索引的情况下用inner join最好，其次是in、exists；无索引的情况下如果是小表驱动大表因为join方式需要distinct没有索引的distinct的消耗性能较大，所以exists性能最佳，in其次join性能最差。如果要是无索引同时又是大表驱动小表in与exists是相近的，如果使用inner join由于使用了join buffer所以会快很多，如果是left join则最慢
7、使用group by代替distinct
group by是可以利用上索引的，但是distinct是不能够使用上索引的，通常情况下他们两个可以达到同样的效果，所以使用group by的效果会更好一些。这里本来是在cmd中进行验证但是cmd突然就没了，所以就不再演示。
8、limit使用索引覆盖确定取值范围
limit就是去符合条件的一定范围内的数据，如果要是直接使用select语句产寻，那么我们会取出多余的我们并不需要的数据，并且在之后再进行取舍。那么既然有一部分本来就是不需要的那么我们就将不需要的那一部分数据不从磁盘中取出。基于这个思想我们的优化策略就是先利用索引覆盖从查询出我们最终需要读取数据的一个主键，将查询主键的查询作为子查询再去回表读取我们最终想要的数据。
9、与group by关联的having中的条件尽量写到where
在mysql的机制中where的执行顺序先于group by自然也先于having，如果数据可以尽早的可以在where中进行过滤那是从一定程度上会减少IO的次数的。同时group by的一个实现机制就是先排序后分类当无法使用索引的排序时那就使用需要用到缓存进行排序，这时候我们可以通过max_length_for_sort_data、sort_buffer_size参数来调大这个内存，从而来提升一个效率。
10、order by
order by的排序如果可以使用上索引的话当然好，需要注意的与我们上面说的查询都也是相似的都需要避免索引失效。这里我们主要来说一下索引不存在的情况。在mysql4.1之前使用的双路排序，大体的思想就是先读取排序字段，在buffer中排好之后再根据排好的顺序再去磁盘中读取数据。这种情况数据明显使用了两遍IO，但是总的来说他使用的buffer相对较少。Mysql4.1之后出现的是单路排序就是将所有符合条件的数据读取到buffer之后在进行排序，但是由于读取到磁盘上的数据更多了所以对磁盘的占用也就更多。这时候我们一定要注意移除select中不必要的字段，这样可以减少buffer的占用，实在不行的时候还可以通过修改参数max_length_for_sort_data、sort_buffer_size来调整缓存区域的大小。不过当需要排序的数据多的超过了buffer能提供的最大的大小时候就会创建临时的文件，这时候性能将会极大的降低，单路排序也就未必由于双路排序了。