MySQL数据库性能优化该如何入手

今天小杨给大家分享一篇关于数据库查询优化，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我们程序员需要去关注的事情。无论是小白还是职场的老手，都是必须掌握的一个手段。如果你对此了解不多,这块内容还是好好看看！
第一步：找到那些查询速度慢的语句

开启慢日志查询：这个主要是帮我们收集时间较长的SQL语句，需要在配置文件my.cnf里面设置查询的时间(long_query_time)，以及存储的路径（slow_query_log_file），并对其开启（slow_query_log）。对上面参数配置完成后，执行语句，查看是否开启，下图为开启的结果：<br>

show variables like 'slow_query%';

　　

 

 第二步：对找到的sql进行分析
1、 使用mysql的explain分析语句的状态：

如上图，explain出来的信息有十多列，通过key列的值，我们可以看到使用到的索引，如果没触发到索引的话，可以查看上篇文章，尽可能的触发索引。

2、 如果开启了show profile，该功能默认是关闭的，使用前需开启。

show variables like 'profiling';　

 

SHOW PROFILES

可以通过上面的语句 Duration 列观看耗时

show profile cpu,block io for query id;/*id为show profiles列表中的Query_ID*/　

 

也可以通过上面的语句，查看该语句更为具体的参数进行判断

第三步：找到问题的后对其进行性能的优化
1. 开启MySQL的查询缓存

这个可以有效的提高查询性能，当相同的查询被执行多次的时候，这些查询的结果会被放到缓存中，后续的查询直接返回缓存的结果。但是也有一些查询姿势会导致缓存无效，首先，想要查询缓存需要查询语句一样，另外条件查询中不能使用一些易变的函数，如NOW()，CURDATE()等

2. 为查询的字段建立索引

对经常查询的字段建立索引，当数据量大的时候，通过建立索引可以有效的帮我们提高查询的效率，但是也不是建太多的索引，不仅占磁盘，当我们插入和跟新的时候也需要去维护，影响效率

3. 尽量避免使用 `select *` 以及当查询一条数据的时候 用 `limit 1` 

从数据库读取太多数据的时候，会影响到速度以及网络传输的负载，所以尽量使用具体字段代替，覆盖索引。当我们知道只想要一条数据的时候，当使用 `limit 1`时，数据库引擎会在找到符合数据后停止往下搜索，从而提高效率

4. 字段设计的时候最好使用 `NOT NULL` 

首先NULL本身是需要占据存储空间的，另外一方面，当索引字段可以为NULL的时候，索引的效率会下降，除非你有特定的需要使用到NULL，不然还是尽可能的使用到 NOT NULL

5. 选择正确的存在引擎 MyISAM 和 InnoDB 

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

6. 对表进行水平拆分或者垂直拆分

- 水平拆分：当一张表的数据量大的时候（如登录表，用户表）随着时间的累加，数据量不断累加，这个时候我们可以将表拆分成多张表，对用户的名字或者id进行取模，从而分散存入到不同的表中，减少单表的压力
- 垂直拆分：当表的字段过多的时候，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能，这个时候可以考虑将一些字段拆分到多张表中，可以考虑下三大范式。这样可以降低表的复杂度和字段的数目，从而达到优化的目的。

7. 避免索引的失效

日常中，我们建立了索引了，但是姿势不对也可能让我们全表扫描，总结一些日常的坑

- like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
- where 等式的时候使用了进行函数、算术运算或其他表达式运算
- whre子句中使用!=或<>操作符、null值判断、使用 or 来连接条件，索引失效少用
- 使用联合索引没有遵守最左原则的时候，索引也无效

共同进步，学习分享

欢迎大家关注我的公众号【写代码的小杨】，相关文章、学习资料都会在里面更新，整理的资料也会放在里面。

觉得写的还不错的就点个赞，加个关注呗！点关注，不迷路，持续更新！！！