mysql优化点整理

优化sql一般步骤：

 

      1.通过show (session 或者 global) status 来查看( 当前连接 或者 数据库上次开机以来 )的服务器状态信息，默认是session

        例如：

                show status like '%com_%' : com_XXX表示XXX语句执行的总次数，这总次数是针对所有引擎的总和

                show status like '%innodb_%': 这里面针对的就是innodb引擎的一些统计总和，例如innobb_rows_read:select的行数总和

        其他有用参数查看：

                 show status like 'Connections'  :尝试连接mysql的次数

                 show status like 'Uptime':       服务器工作时间，如果是myisam引擎，则需要进行碎片整理

                 show status like 'slow_queries': 慢查询的sql次数

 

 

      2.定位执行效率较低的sql语句：有两种方法

        第一种：使用慢日志定位 （必须先开启了慢查询）

                      show variables like "long_query_time";  // 查看多少秒算慢查询，默认是10秒

                      set long_query_time = 0.5                      // 设置本次连接中的慢查询为0.5秒，便于调试

                      show status like "slow_queries";             // 显示慢查询的条数

                      查看慢查询的日志文件，从中分析查询慢的sql语句

 

        第二种：1. 使用show processlist命令查看当前mysql正在进行的“线程”，包括线程的状态、是否锁表等，也就能实时了解sql的执行情况

       2. show processlist中的status字段表示当前sql执行的状态情况，比较重要

       3. 对于“线程的设置”查看命令：

          show variables like 'thread%';

          其中，thread_cache_size：“线程池”中存放的“最大连接线程数”，默认为0，该值的设置一般与物理内存有关，物理内存>3G的，设置为64，计算“线程的失效率”： thread_created/connections来衡量 thread_cache_size的设置是否合适

          thread_concurrency：线程的并发数

          thread_handing：        线程池处理连接的方式

          thread_stack：             每个连接被创建的时候,mysql分配给它的“内存”  .这个值一般认为默认就可以应用于大部分场景了,除非必要非则不要动它

 

4. 查看数据库服务器的线程运行情况：

   show global status like 'thread%';

   其中，Threads_created：表示创建过的线程数，如果发现该值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器

 

 

      3.使用explain或desc来分析执行效率较低的sql语句

      4.分析后应该对sql进行相应的补救措施，例如添加索引、优化字段类型和索引使用情况等等

      

      5.通过查询索引参数来查看库的索引使用情况：

         5.1 show status like 'Handler_read%'

         5.2 Handler_read_key：     表示一个行被索引值读取的次数（针对增加了索引后的分析情况），如果索引“有正在工作”，则该值应该是很高的，如果低，则表示即使建立了索引性能也提高不好  

         5.3 Handler_read_rnd_next：表示在数据文件中读取下一行的请求次数，该值“越高”则表示“查询效率低下”，应该使用添加索引来优化。如果增加了索引，还是不高则表示增加了索引性能提高不好

 

      6.定期分析和检查表 （由于执行期间会进行表锁，所以一般在数据库不繁忙时候进行优化）

         analyze table 表名

check table 表名

 

      7. 定期优化表（由于执行期间会进行表锁，所以一般在数据库不繁忙时候进行优化）

         optimize table 表名  该命令只对myisam、innodb、bdb引擎有效，举例myisam引擎的表a，其a.MYD文件（存储数据的文件）会不断增大，即使是删除表的部分数据，该文件依然是之前的大小，这是由于碎片存在的原因，需要通过optimize 操作了清理碎片从而减少表的大小

 

 

常用的sql优化

 

      1.优化大批量插入数据

        1.1 myisam引擎的表：禁用索引，插入数据时，mysql会根据表的索引对插入的数据进行排序，如果插入大量数据时，需要先禁用索引，插入完毕后再启用

              禁用：alter table 表名 disable keys

              开启：alter table 表名 enable keys

            对应新创建的表，可以先插入数据后再建立索引

 

        1.2 innodb的表：其根据主键来排序的，所以在导入数据之前可以对数据针对主键字段来排序，再进行导入

            关闭唯一性校验（set unique_checks = 0），导入数据后，再开启 ( set unique_checks  = 1 )

 

        1.3 能够使用load data infile 语句导入数据的就不要用insert into 语句

    

      2.优化insert语句

        2.1 同一个客户端insert数据，应该使用values后跟多值的语句

2.2 不同客户端insert数据，使用 insert delayed into ，delayed参数表示让该插入立即执行，但是其实数据是被存放在内存的队列中排队，并没有真正的立即执行，而是等到mysql空闲了再执行，使用该参数，mysql会立即返回ok状态给客户端，提高响应速度，但是坏处是并不能返回自增id，以及如果系统崩溃加mysql未来得及执行排队中的sql，会导致数据丢失

            其中一种应用场景：处理mysql由于“并发插入”造成的"堵塞问题",在多线程并发的情况下，同时抢占mysql的资源，而mysql的myisam引擎默认是表锁的，当a进程锁表进行插入时候，b进程只能等待，这样就容易造成堵塞

 

 

      3.优化group by语句

        默认情况下，mysql是会对group by 字段1,字段2···进行排序，也就等同于加上了order by，如果想在没有使用order by避免排序的消耗，需要加上 order by null

例如：select id, sum(money) from sales group by id order by null  通过explain查看extra选项并没有using filesort

 

 

优化数据库设计

 

        4.1 使用procedure analyse() 分析已有的表的字段状况，根据max_value、max_length等选项来优化字段的类型，用法：select * from 表名 procedure analyse();

        

4.2 拆分表来提高表的访问效率，针对myisam的拆分有：

   垂直拆分：将主列与次列分成两个表存放，缺点是查询所有数据时候需要关联查询

   水平拆分：数据列本身的数据意义拆分，比如将近3个月的数据列存在一个表，3个月前的存在另一个表

 

4.3 逆规范化：规范化越高，所要关联的表就越多，从而导致表之间的连接频繁，而表之间的连接操作是性能较低的操作，直接影响了查询效率，所以，如果通过在需要去关联的表中添加“冗余的字段”来避免关联连接，可以提高查询效率

   逆规范技术一般有：

       增加冗余列：指在多个表中具有相同的列，它常用来在查询时候避免表连接操作

       增加派生列：指增加的列来自其它表的数据，有其他表的中的数据通过计算生成，它可以避免表连接操                            作和使用计算函数

       重新组表：   指如果需要经常查看多个表连接出来的结果数据，则可以把这多个表组合成一个表来提高                              查询性能

 

        4.4 使用中间表提高统计查询效率

        中间表复制源表的部分数据，并且与源表隔离，在中间表上做统计操作并不会影响源表

        在中间表增加索引等优化手段，可以提高统计效率