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

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

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

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

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

优化的流程

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

性能提升按照顺序越来越低,同时代价越来越大。

优化分为两种  

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

1. show processlist;(查看每个与数据库连接的session状态)

# 展示前100条线程
show processlist;


# 如果想列出全部线程,请使用
show full processlist; 


# 杀死某个线程
kill 2297717;

结果查看

Id       # ID标识;要kill一个语句的时候用
User     # 当前连接用户
Host     # 显示这个连接从哪个ip的哪个端口上发出
db       # 使用的数据库名
Command  # 连接状态, 一般是休眠(sleep); 查询(query); 连接(connect);
Time     # 连接持续时间,单位是秒
State    # 显示当前sql语句的状态
Info     # 显示这个sql语句

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

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

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

5. kill id; # 杀掉有问题的连接 Id是 show processonlist; 的Id

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

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

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

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

4. 调整索引或语句本身    

Mysql的数据库存储引擎

  • InnoDB存储引擎
  • MyISAM存储引擎

什么是InnoDB

什么是MySIAM

两者的区别:

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

存储优化

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

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

索引的类型

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

什么是 索引失效?

索引失效是:当需要查询的时候,建立的索引没有使用,导致的全表扫描,造成的效率低下!

如何解决呢?


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

表结构优化:

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

表拆分:      

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

表分区:

特殊说明:
上述文章均是作者实际操作后产出。烦请各位,请勿直接盗用!转载记得标注原文链接:www.zanglikun.com
第三方平台不会及时更新本文最新内容。如果发现本文资料不全,可访问本人的Java博客搜索:标题关键字。以获取最新全部资料 ❤