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);