一、架构设计层面优化。
MySQL是一个磁盘IO访问非常频繁的关系型数据库,在高并发和高性能的场景中,MySQL数据库必然会承受巨大的并发压力。在此时,我们优化的方式主要可以分为这么几个部分:
1、搭建MySQL主从集群,单个MySQL服务器容易导致单点故障,一旦服务器宕机了,就会导致使用MySQL数据库的应用全部无法响应,我认为,无论是主从集群,或者是主主集群都可以保证MySQL的高可用性;
2、读写分离的设计,在读多写少的场景中,通过读写分离这种方案,可以避免读写冲突导致的性能问题;
3、引入分库、分表的机制,通过分库的方式可以降低单个服务器的IO压力;通过分表的方式可以降低单表的数据量,从而提升SQL的查询效率;
4、对于热点数据,我们可以使用非关系型数据库来进行缓存,比如说Redis、MongoDB等,他们可以很好的缓解MySQL的访问压力,同时还能提升数据的检索性能。
二、程序配置优化。
1、MySQL本身也可以进行一些简单的优化,我们可以通过MySQL的my.cnf配置文件来完成,比如说MySQL5.7版本,默认的最大连接数是151个,这个值可以在my.cnf中修改。
2、bin log日志默认是不开启的,我们也可以在这个文件中去修改开启。
3、设置缓存池BufferPool默认大小的配置等。
三、慢sql的优化。
SQL优化又可以分为3个步骤:
1、慢SQL的定位和排查,我们可以通过慢查询日志和慢查询日志工具分析得到有问题的SQL列表;
2、执行计划分析,针对慢的SQL,我们可以使用关键字explain来查看当前SQL的执行计划,可以重点关注type、key、rows、filter等字段,从而定位该SQL执行慢的根本原因,再去有的放矢的进行优化;
3、使用show profile工具,show profile工具是MySQL提供的可以用来分析当前会话中SQL语句资源消耗情况的工具,可以用于SQL调优的测量,在当前会话中,默认情况下,show profile是关闭状态,打开之后会保存最近15次的运行结果,针对运行慢的SQL,通过profile工具,进行详细分析可以得到SQL执行过程中所有资源的开销情况,比如IO开销、CPU开销、内存开销等等。
四、sql语句的优化。
1、SQL的查询一定要基于索引来进行数据扫描;
2、避免索引列上使用函数或者运算符,这样会导致索引失效;
3、where查询条件的like,一定要把%放到右侧;
4、使用索引扫描,联合索引中的列从左往右,命中的越多越好;
5、尽可能使用SQL语句用到的索引来完成排序,避免使用文件排序的方式;
6、查询语句里面我们尽量少用*,而是用具体的有效列代替。