说到Mysql优化,必须明确三点。

第一、不是所有的优化都是有效的。

第二、系统的稳定业务逻辑可用性往往比性能优化更重要。

第三、优化事各个部门的合作。

程序员一般是通过优化sql语句 加索引等方式 进行调优

优化的流程

sql语句与索引优化 –> 数据表优化 –> 系统配置优化 –> 硬件提升优化

性能递增顺序越来越低,同时代价越来越大。

优化分为两种  

  • 应急优化
  • 常规优化

首先说应急优化(查看当前阻塞的sql session):         

1. show processlist(查看链接session状态)            

2. explain(分析查询计划),show index from table(分析索引)            

3. 通过执行计划判断,索引问题(有没有、合不合理)或者语句本身问题            

4. show status like ‘%lock%’; # 查询锁状态

5. SESSION_ID; # 杀掉有问题的session

接下来常规调优 (通过查看慢日志,针对性能差的sql进行优化)           

1. 查看slowlog,分析slowlog,分析出查询慢的语句。            

2. 按照一定优先级,进行一个一个的排查所有慢语句。            

3. 分析top sql,进行explain调试,查看语句执行时间。            

4. 调整索引或语句本身    

索引优化(为什么加索引可以优化?)

索引的类型

Mysql的数据库存储引擎:InnoDB存储引擎MyISAM存储引擎

什么是InnoDB

InnoDB存储可以提交、回滚、崩溃恢复,但是写操作效率会底下,并占用更多的资源与内存以保留数据和索引
    1、提供ACID(原子性,一致性,隔离性,持久性),实现标准的数据库隔离级别
    2、使用count(*)会扫描整个表,才能计算出来多少行
    3、使用行锁,粒度更小,写操作,不会锁定全部表,多线程效率更高,即使存在更新、插入性能也比较好
    4、清表比较慢(是一条一条处理数据),先把操作写入事务日志,然后再删除,所以清表的时候 最好直接drop,再建新表

什么是MySIAM

 不支持事务,不支持外键,查询、插入可以选择这个存储引擎
        表将存储再三个文件中
            1) frm:存储表定义(表结构等信息)  
            2) MYD(MYData),存储数据
            3) MYI(MYIndex),存储索引  
        提供修复工具,使用CHECK TABEL来检测表健康,可使用REPAIR TABLE来修复
        支持全文索引(Mysql5.6之前只有MySIAM支持)

两者的区别:

        1、MySIAM不支持事务,不安全,但是InnoDB是线程安全的
        2、MySIAM锁的粒度是表,而InnoDB支持行级锁定,所以InnoDB多线程时,速度更快
        3、MySIAM不支持外键,InnoDB支持外键
        4、MySIAM相对简单,效率高于InnoDB,小型应用可以考虑MySIAM,

什么是 索引失效?


        1、使用like 比如查询姓名name = '%aaa'不会失效,但是name = 'aaa%',就会导致索引失效
        2、使用or,如果必须要使用or,那就让or上面所有字段加上索引
        3、 字符串类型的数据查找不加‘’,就会导致索引失效
        4、 查询的有null值

存储优化

         1、禁用索引:插入记录时,Mysql会为每个记录加入索引,如果数据量很大,那么就会严重影响速度。数据插入完成后,可以在开启索引。
         2、禁用唯一性检查:插入记录时,如果有唯一性检查,如果数据量很大,就会严重影响速度。当大量数据插入后,在开启唯一性检查就行。
         3、禁用外键检查: 同禁用唯一性检查一样。
         4、批量插入数据:一条Sql插入多个记录。
         5、禁止自动提交:把事务的自动提交关掉,数据插入完成再打开事务的自动提交。SET autocommit 
= 0;    0是禁用自动提交,1是开启自动提交

表结构优化:

         1、 尽量将字段定义为非空,如果一旦有空值,将来极其容易出现索引失效的全表扫描。
         2、使用小的数据类型,比如
         3、合理使用冗余字段
         4、表字段不要太多

表拆分:      

  •  垂直拆分(将表中的字段分成多个表)需要使用冗余字段(使用join),确定事务不好控制、查询起来较为麻烦
  • 水平拆分  (将一个大表的数据拆分成多个相同表结构的数据) 是常见的分库分表,数据量大的时候,维护时间边长

表分区:

还是那句话,你只看到了大佬轻松解决问题的光鲜,却不记得当初人家死磕Bug的痛苦。

万物皆入轮回,谁也躲不掉!

以上文章,均是我实际体验,写出来的笔记资料,不会出现全文盗用别人文章!烦请各位,支持下原创,请勿直接盗用,有条件的可以开通个会员支持下,谢谢!!!

Java Code Study » Mysql的优化

招聘、单纯问问题,都可以联系我

加我QQ 公司缺人,拉我入伙