浅谈MySQL优化的常见手段

引言

最近在项目的测试阶段中，发现有几个接口所调用的SQL语句执行很慢，在压测的时候还经常出现响应超时的状况。为此写下本篇文章，为大家提供一些常见的SQL优化手段。

何如确定SQL语句执行的快慢

使用MySQL自带的分析工具EXPLAIN

使用方法，在SQL语句前添加EXPLAIN关键字。

• 通过观察Key与Key_len检查是否命中了索引，分析索引是否失效。
• 通过Type字段查看SQL是否有进一步的优化空间，检查是否存在全索引扫描和全盘扫描。
• 通过extra建议判断，是否出现了回表的情况，如果出现回表情况更换索引或者添加索引以及更改返回字段来解决回表情况。

常见的SQL优化手段

可以从五个方面来分析

表的设计优化

• 设置合适的数值例如int、bigint等，根据情况选择。
• 选用合适的字符串类型，char为定长效率高，varchar可变长，效率比char稍微差一点。

SQL语句优化

• 查询时避免使用*，确定指明字段。出现回表情况。

如下，在一张表有125000条数据，为word_spell, word_meaning, word_level三个字段添加索引。

查询word_spell为street的数据。

explain select * from words where word_spell='street';

观察结果注意耗费时间162ms

explain select word_meaning from words where word_spell='street';

观察时间，两者相差数倍。

 

• SQL语句要避免索引失效（上篇文章讲解了覆盖索引失效的情况）

避免where字句中对字段进行表达式操作

explain select word_meaning from words where substr(word_spell,0,3)='stre';
替换成
explain select word_meaning from words where word_spell like 'stre%';

•  出现子查询时，使用exists，会调用索引。
• 能用innerjoin就不使用left join、right join。必须要将小表为驱动，内连接会将大表放在外面，把大表放在里面。而左右外连接则不会调整顺序。
• where子句尽量避免NULL的判断，会导致引擎放弃索引从而进行全盘扫描。

MySQL主从复制、读写分离（后续更新主从架构的搭建）

数据库读操作较多，避免写操作所造成的性能影响，采用读写分离的架构。

读写分离架构就是为了解决，数据库的写入而影响查询的效率。

总结

本篇文章记录了作者在项目中使用的SQL调优经历，希望能为大家带来帮助，欢迎大家补充！！！