mysql sql优化

mysql sql优化

(show processlist; 查看数据库正在进行的操作)

1. show status 查看SQL执行频率

  show [session|global] status;

  session:显示当前连接的统计结果;

  global:显示从上次启动到现在的统计结果;

  show status like 'com_%';

  com_select:执行select操作的次数;

  com_insert:执行insert操作的次数;

  com_update:执行update操作的次数;

  com_delete:执行delete操作的次数;

  com_commit:事务提交的次数;

  com_rollback:事务回滚的次数;

  slow_queries:慢查询的次数(show variables like 'long_query_time' 可查看指定的慢查询时间)

2.explain 分析SQL执行计划(在SQL语句前加 explain)

    select_type 的取值:

        simple:简单表,即不使用表连接或子查询

        primary:主查询,即外层的查询

        union:union中的第二个或者后面的查询语句

        subquery:子查询中的第一个select

    type(访问类型):

        all,index,range,ref,eq_ref,const,system,null(从左至右,性能由最差到最好)

        all:全表扫描

        index:索引全扫描

        range:索引范围扫描

        ref:使用非唯一索引扫描或唯一索引的前缀扫描

        eq_ref:使用唯一索引;多表连接中使用primary key或unique index作为关联条件

        const/system:单表中最多一个匹配行(例如,primary key 查询或是 unique index 查询)

        null:不访问表或索引,直接能够得到结果

    explain extended    加  show warnings 查看优化器对SQL的改写

        使用: explain extended select * ....;   show warnings;

3.show profile 查看SQL执行过程
    select @@have_profiling; //查看mysql是否支持profile
    select @@profiling; //查看profiling状态(默认关闭)
    set profiling=1; //开启profiling

     show profiles; //查看执行列表

      show profile for query Query_ID;  //根据执行列表的 Query_ID 查看对应SQL的具体执行耗时

4. show status like 'handler_read%'; 查看索引使用情况

    Handler_read_first:此选项表明SQL是在做一个全索引扫描,注意是全部,而不是部分,所以说如果存在WHERE语句,这个选项是不会变的。如果这个选项的数值很大,既是好事 也是坏事。说它好是因为毕竟查询是在索引里完成的,而不是数据文件里,说它坏是因为大数据量时,简便是索引文件,做一次完整的扫描也是很费时的。

    Handler_read_key:此选项数值如果很高,那么恭喜你,你的系统高效的使用了索引,一切运转良好。

    Handler_read_next:此选项表明在进行索引扫描时,按照索引从数据文件里取数据的次数。

   Handler_read_prev:此选项表明在进行索引扫描时,按照索引倒序从数据文件里取数据的次数,一般就是ORDER BY … DESC。

  Handler_read_rnd:简单的说,就是查询直接操作了数据文件,很多时候表现为没有使用索引或者文件排序。

  Handler_read_rnd_next:此选项表明在进行数据文件扫描时,从数据文件里取数据的次数。(数值高,说明查询运行低效)

5.优化表

  分析表

    analyze table tableName; (对MyISAM、BDB、InnoDB表有用)

  检查表

    check table tableName;(对MyISAM、InnoDB表有用,也可检查视图)

  优化表

    optimize table tableName;(对MyISAM、BDB、InnoDB表有用)该命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费。

    alter table tableName engine=innodb; 删除大量数据后,InnoDB表可以通过alter table 但是不修改引擎的方式来回收不用的空间。

6.常用SQL优化

(1)大批量插入数据

①InnoDB表按主键顺序保存,将导入的数据按照主键顺序排序,可提高效率;

②导入数据时确认数据无误,关闭唯一性校验,可提高导入效率;set unique_checks=0(关闭) set unique_checks=1(开启)

③导入数据时关闭自动提交,可提高效率,导入后再开启;set autocommit=0(关闭) set autocommit=1(开启)

(2)order by语句优化

①尽量减少额外的排序,通过索引直接返回有序数据;

②a. 适当增大max_length_for_sort_data的值,让mysql选择更优化的filesort排序算法(mysql通过比较max_length_for_sort_data和Query语句取出的字段总大小选择排序算法);

b.适当增大sort_buffer_size排序区,尽量让排序在内存中完成,不通过创建临时表在文件中进行;

c.max_length_for_sort_data过大会造成CPU利用率过低、磁盘I/O过高,sort_buffer_size过大会导致服务器SWAP严重,

(3)group by 语句不需要排序时指定order by null可以节省时间;

(4)考虑使用连接查询代替嵌套查询

(5)or 条件会使用独立索引,但不使用复合索引

(6)分页查询优化

①按照索引分页后回表(此方法避免了全表扫描):

select id,name from user_table order by age limit 5,10;

改写为:

select a.id, a,name from user_table a inner join( select id from user_table order by age 5,10)b on a.id=b.id;

②根据上一页最后编号继续查询(部分条件下使用:排序字段无重复,排序字段与编号字段一致)

select id,name from user_table order by id limit 5,10;

改写为:
 select id,name from user_table where id >39 order by id limit 10;

7.使用SQL提示

(1)use index(推荐mysql使用的索引)

     select count(*) from user_table use index (id);

(2)ignore index (忽略索引)

   select count(*) from user_table ignore index (id);

(3)force index (强制使用索引)

   select count(*) from user_table force index (id);

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值