关于MySql的索引原理以及优化原则

系统管理员 2022-02-27 15:06 215阅读 0赞

在面试或者实际的工作中，我们都会遇到会需要优化MySql的场景，那么优化MySql的原理到底是什么呢。

曾经看过一篇公众号文章，具体阐述了MySql的优化原理，所以我的绝大部分见解是基于这篇文章之上，其他部分是自己参考了其他的博客包括查询了大量的资料获得的经验。

首先盗一张图来看下MySql的优化原理吧，方便大家更方便的理解MySql，以下是MySql的运行图片

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xteDEyNTI1NA_size_16_color_FFFFFF_t_70][]

根据当前的图片我们可以根据他的执行过程来进行对应的优化操作。

1.避免使用select \*或者尽量使用limit 1这种限量级的语句

原因：从上图我们可以看到，MySql是的通信协议是“半双工”的，意思就是如果服务器向MySql发出请求之后，MySql必须接收到服务器的所有请求参数以后，才可以对数据进行处理操作，然后再返回给服务器端，也就是说我们不能在边请求的过程中边获得数据。所以当我们查询语句很长的时候，需要设置max\_allowed\_packet参数，但是如果查询语句真的特别长，服务器端可能会因为无法接受过多数据而导致抛出异常。当我们对MySql发起请求的时候，MySql必须返回我们查询的所有结果，不能只返回前几条结果。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 `SELECT * `以及加上 `LIMIT `限制的原因之一

2.查询的缓存设置

原因：从上面的图中可以看到，所有打进MySql的查询第一步都是会从缓存中查询数据，如果缓存命中会直接从缓存中获取数据的。但是并不是一味的设置缓存就能提高我们的查询效率的。既然是缓存，那肯定会有失效的，缓存和失效时都会带来性能上的额外消耗，并且缓存命中的要求比较严格，比如第一次查询和第二次查询时，字段顺序发生改变会导致缓存无法命中；使用MySql的一些函数特性，比如now()，current\_date()等一些函数也会导致缓存无法命中。所以当我们的业务场景下缓存带来的收益效果大于其性能消耗时，可以考虑引入缓存的配置。

3.数据类型优化

原因：选择数据类型只要遵循**小而简单**的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用 `DATETIME`来存储时间，而不是使用字符串

4.一些优化sql的操作

比如优化联表查询，在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用 `JOIN`有更好的性能。还有比如用UNION ALL去代替UNION，除非我们需要用到去重。如果没有 `ALL`关键字，MySQL会给临时表加上 `DISTINCT`选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高

5.存储引擎的选择

读操作多并且不考虑事物的情况下可以选择MyISAM，否则的话就只能选择InnoDB，这部分读者可以自己去查询相关资料，网上有很多文章介绍了区别

5.索引

索引我放到最后一个说是有原因的，因为索引涉及的东西太多了。

首先需要考虑一个问题：我们需要在哪个列上加索引，如果单列查询，那索引肯定是加在对应的列上，如果多列索引呢，因为索引是遵循“最左原则”的，索引的字段在最左边才会效率最高。比如我们有 select \* from user where name = "urey" and age = "25"，我们可以通过执行下面的语句，看哪一列的语句最接近于1那么就可以将此列放至最左边并加入索引

`select count(distinct name)/count(*) as name_selectivity, count(distinct age)/count(*) as age_selectivity,count(*) from user`

关于索引的最左原则：select \* from user where name = "urey" and age = "25"  还是这句语句，MySql在查询的时候会先查询 name = “urey” 的所有数据，而后在前者的结果中再查询age = 25的数据，因此如果age 上加入索引的话，效率会很低下。

关于索引还有一个就是我们常说的为什么索引常用的是B+树呢，他的查找效率肯定是低于HASH的，为什么不用HASH呢。

原理：因为B+树是多路搜索树，在查找单条数据的时候，哈希查找的时间复杂度为O(1)，确实比B+树会快很多，但是B+树的优势在于他的所有数据都存储在子节点之上，并且子节点之间是按照链表顺序相连的，比如我们需要查询20-30的数据的时候，B+树会先从多路中查询20的子节点位置，而到30相当于就是limit10，此时B+树会遍历链表向后查询10个数字，不需要再去从跟节点进行查找，所以在查询多数据情况下时，B+树的威力就体现出来了。

MySql方面自己也没有太多的了解深入，文中可能会涉及许多问题，所以有不对的地方欢迎指出一起讨论。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xteDEyNTI1NA_size_16_color_FFFFFF_t_70]: /images/20220227/60ebf51caad54d76b060042ee034116d.png