`

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 使用中间表提高统计查询效率

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

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

 

 

分享到:
评论

相关推荐

    mysql基础知识和mysql优化整理

    mysql基础知识和mysql优化整理,mysql基础知识和mysql优化整理,

    mysql优化笔记+资料

    详细介绍了mysql的优化方法,资料包里包含word文档,ppt和图片

    mysql优化精华整理文档

    mysql优化精华整理文档 总共15页 很不错 word格式

    MySQL高级优化_整理.docx

    MySQL高级优化_整理.docx;mysql优化文档;mysql优化;

    Mysql 优化技巧总结(自己整理)

    介绍了MYSQL的优化方法,以及常用工具的使用方法。...大家都反映分数要的太高了,自己整理了MYSQL的优化。希望能得到一些积分来下载其它的学习资源,为了方便大家现在免分放送了,大家可以互相学习。

    MySQL优化笔记-整理版

    里面结合了我整理的笔记希望对大家有用,请大家多多支持

    mysql知识点整理收藏

    mysql知识点整理收藏 MySql语句优化.pptx

    mysql优化笔记.doc

    对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/...

    MySQL优化笔记-整理版.doc

    Q: 为什么别人问你MySQL优化的知识 总是没有底气. A: 因为你只是回答一些大而化之的调优原则, 比如:”建立合理索引”(什么样的索引合理?) “分表分库”(用什么策略分表分库?) “主从分离”(用什么中间件?) 并没有从...

    mysql优化.pdf

    数据库优化方案整理总结,适合新手看看,包括查询优化,数据库索引优化等等。数据库优化方案整理总结,适合新手看看,包括查询优化,数据库索引优化等等。

    mysql优化笔记(加强版)

    里面有十八哥的mysql优化笔记 还有我自己整理的大牛讲课的mysql优化方式和技巧(有图片和文档 ) 个人认为比较全面 可以为面试提问mysql优化问题做准备

    mysqlquery优化[整理].pdf

    mysqlquery优化[整理].pdf

    Mysql数据库优化笔记(细节决定成败)

    对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/...

    mysql笔记,mysql优化图解

    对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/...

    计算机后端-PHP视频教程. mysql优化整理版.doc

    计算机后端-PHP视频教程. mysql优化整理版.doc

    mysql优化整理文档

    我平时收集的一些资源,大家有空可以下载使用,欢迎大家

    MYSQL读书笔记整理

    mysql的InnoDB学习 mysql的语法优化 mysql的其他

Global site tag (gtag.js) - Google Analytics