关于mysql优化的一点心得-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_41553626/article/details/143301211

最近在做一些sql优化方面的工作，今天遇到了一个大概100行往上的sql，之前由于数据量不大，并没有过慢，但是最近数据量上来之后越来越慢，所以开始做一些优化，个人觉得优化分为四个维度，sql写法、索引写法、主从复制，读写分离、分库分表。

一、sql写法

这一方面主要就是阿里的sql写法规范，大家可以看一下下面的链接

阿里开发手册之MY SQL规范篇 - baivfhpwxf - 博客园

总结几点我遇到过得：

1.禁止使用select*，仅查询需要的列，减少数据传输量，提高效率。

2.小表驱动大表：在JOIN操作中，确保小结果集驱动大结果集，减少Nested Loop的循环次数。

3.连表不要超过三个表，超过3表，那么如果优化力度做的不够，那么由于多表的嵌套查询，性能会极差，且如果需要对项目进行服务拆分和数据迁移时，由于表之间的关联关系过深，服务拆分和数据迁移会有极大的改造成本，可以将关联的业务放在业务层去写，当然如果数据量真的不大，那也无所谓。

4.连表如果不是必须要用left join或者right join，尽量用inner join，

inner join只返回两个表中匹配的行，即两个表中都存在的行。这意味着返回的结果集中只包含符合条件的数据，可以减少不必要的数据冗余。

left join和right join会返回左表或右表中的所有行，即使在另一个表中没有匹配的行。这可能导致结果集中包含很多空值，增加了数据冗余和处理的复杂性。

inner join的执行效率通常比left join和right join高，因为它只需要比较两个表中的匹配行。而left join和right join需要比较整个左表右表中的所有行，增加了查询的开销。

在数据规模较大的情况下，left join和right join可能会导致结果集非常庞大，占用大量的存储空间和网络带宽。而inner join可以减少结果集的大小，提高查询的效率和性能。

二、索引写法

说到索引，就一定要会explain，在sql前面加上explain，查看运行状态，主要是几个字段，type->数据访问/读取操作类型（ALL、index、range、ref、eq_ref、const/system、NULL）

possible_keys->揭示哪一些索引可能有利于高效的查找

key->显示mysql决定采用哪个索引来优化查询

key_len-> 显示mysql在索引里使用的字节数

第二点，要关注sql写法，注意是否利用到索引，是否写了导致索引失效的sql写法，

关于索引失效的写法主要就是，

函数运算：如果在 WHERE 子句中对索引列进行了函数运算，索引就会失效。例如， WHERE UPPER(column_name) = 'VALUE' ，这会导致索引失效，因为无法利用索引快速定位满足条件的行。
最左前缀原则：在使用联合索引是，where语句中表达式要按照联合索引顺序写
类型转换：如果在 WHERE 子句中对索引列进行了类型转换，索引也会失效。例如， WHERE column_name = 1 ，如果 column_name 是字符串类型，那么 MySQL 将对 1 进行隐式类型转换，导致索引失效。
模糊查询：如果在 WHERE 子句中对索引列进行了模糊查询（例如使用通配符 % ），索引通常会失效。例如， WHERE column_name LIKE '%value%' ，这会导致索引失效，因为无法利用索引快速定位满足条件的行。
范围查询右侧表达式:在例如：age>18 and name ='jsds' 这种情况下，name字段可能索引失效
索引列参与计算：如果在 WHERE 子句中对索引列进行了计算，索引也会失效。例如， WHERE column_name + 1 = 10 ，这会导致索引失效，因为无法直接利用索引进行计算。
OR 条件：如果 WHERE 子句中包含 OR 条件，并且这些条件不是索引列的前缀，那么索引可能会失效。例如， WHERE column_name = 'value' OR other_column = 'other_value' ，这会导致索引失效。
NULL 值问题：如果索引列包含了大量的 NULL 值，并且查询条件涉及到了这些 NULL 值，那么索引可能会失效。因为 NULL 值不会被索引存储，所以查询 NULL 值通常需要进行全表扫描。