关于MySQL优化

概念：

        在应用的开发过程中，由于初期数据量小，开发人员写SQL语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须要对他们进行优化

        MySQL的优化方式有很多，大致可以从以下几点来优化SQL：

        1.从设计上优化SQL

        2.从查询上优化

        3.从索引上优化

        4.从存储上优化

MySQL的优化：

       1. 查看SQL执行频率

        MySQL客户端连接成功后，通过show [session][global]status命令可以查看服务器状态信息，通过查看状态信息可以查看对当前数据库的主要操作类型

 查询结果显示进行SQL查询操作是最多的，因此之后的优化是以SQL查询为主导的

       

        2.定位低效率执行SQL-慢查询日志

可以通过以下两种方式定位执行效率较低的SQL语句：

        慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句

        show processlist:该命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化

最低阈值时间默认为10秒，如果SQL的执行时间大于等于10秒则算为慢查询，将该操作记录到慢日志中去

        3.定位低效率执行SQL-查询SQL动态执行状态

 

        4.Explain分析执行计划-基本使用

        通过以上步骤查询到效率低的SQL语句后，可以通过EXPAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序

关于explain的字段解释：

        

        5.Explain的分析执行计划-id

        id字段是select查询的序列号，是一组数字，表示的是查询中执行selecy子句或者是操作表的顺序，id情况有三种：

        a.id相同表示加载表的顺序是从上到下：

         b.id不同，id的值越大，优先级越高，越先被执行

         c.id有相同，也有不同，同时存在

           id相同的可以认为是一组，从上往下顺序执行，在所有的组中，id的值越大，优先级越高，越先执行

 

        6.Expalin分析执行计划-select_type

        表示select的类型，常见的取值如下表所示：

 

        7.Explain分析执行计划-type

        type显示的是访问类型，是较为重要的一个指标，可以取值为：

 结果值从最好到最坏依次是：system>const>eq_ref>ref>range>index>ALL

 注意：system查询系统表，表示直接从内存读取数据而不会从磁盘读取，但是5.7及以上版本不再显示system，直接显示ALL

       8.Explain分析执行计划-其他指标字段

        a.Explain之table：

                显示这一步所访问数据库中表名称，有时不是真实的表名

        b.Explain之rows：

                扫描行的数量

        c.Explain之key：

                possible_keys:显示可能应用在这张表的索引，一个或者多个

                key:实际使用的索引，如果为NULL,则没有使用索引

                key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下长度越短越好

        d.Explain之extra:

                其他的额外的执行计划信息，在该列展示：

 

        9.MySQL的优化-show file分析SQL：

        MySQL从5.0.37版本开始增加了对show profiles和show profile语句的支持，show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

        通过have_profile参数，能够看到当前MySQL是否支持profile:

         通过profile,我们能够更清楚的了解SQL执行的过程，首先可以执行如下的操作：

         执行完上述命令之后，再执行show profiles指令，来查看SQL语句执行的耗时：

         通过show profile for query query_id语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

         在获取到最消耗时间的线程状态后，MySQL支持进一步选择all,cpu,block io,context switch,page faults等明细类型来查看MySQL在使用什么资源上耗费了更高的时间，例如：选择查看CPU的耗费时间：

 

        10.MySQL的优化-查看trace优化器

        MySQL5.6提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A计划而不是选择B计划

        打开trace，设置格式为JSON,并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示

        最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：