Mysql分析优化查询的方式

一：查询语句分析

1.通过create index idx_colunmsName on tableName(columns)为某个表的某些字段创建索引，注意主键和唯一键都会自动创建索引；

如为表student的name和class字段创建联合索引：create index idx_name_class on student(name,class);

2.通过explain或describe来分析查询语句的效率和一些配置属性（临时表也是用这个方法且不需要加temporary关键字）：

如：describe select* from student where uid=1;会输出如下几列：

1.id列，整个查询中select的位置；

2.select_type列，查询类型，一般都是simple；

3.table列，此查询对应的表；

4.type列，连接类型，该列中存储很多值，范围从const到all；

5.possible_keys列，很重要，指此查询语句里可能用到的索引；（注意，where条件里如果是两个判断是and，那么如果第一个

6.key列，很重要，指出此查询里实际用到的索引；（注意：并不是where里的字段有分配索引就会用到，如为name分配了索引在查询时也不一定会用，如两个条件or的情况，因此这个参数可以来看此select的索引是否生效）

补充：可以为一个字段创建多个索引，也可以为多个字段创建联合索引；

7.rows列，很重要，用来查看执行此查询所需要检验的 行数，越小越好；

8.key_len列，重要，使用的索引的长度，一般越小越好；

9.extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

extra 项	说明
Using filesort	表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”
Using temporary	表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

二：单表查询优化

1.先通过show variables like '%query_cache';显示的have_query_cache判断是否配置了高速缓存功能；

配置了不代表启用了，可以通过：set global query_cache_size = 102760448; 和set global query_cache_limit = 2097152; 和 set global query_cache_size = 600000;来开启高速缓存功能；

2.查询时用高速缓存功能：select sql_cache * from tableName;

三：多表查询优化

1）将多个分步查询合到一起执行

1.优化前：select name from class where sid=(select uid from student where name='silentdoer');

2.优化后：select cls.name from class as cls, student as stud where cls.sid=stud.uid and stud.name='silentdoer';

3.优化原理：将分步查询的结果整合成一个查询（即1里的要先执行后面的子查询然后再执行左边的查询），这样就不需要再执行多个单独查询（这个有点像C#的Linq的延时查询），从而提高了多表查询的效率；

 

2）将多个分步查询通过@var变量的形式缓存起来再执行第二步查询提高可读性和一定程度优化效率，和1）不一样的是这里有聚合函数故不能直接合成一个查询语句

1.优化前：select name from student where age > (select avg(age) from student);

2.优化后：select @average:=avg(age) from student;  select name from student where age > @average;

3.优化原理：2的方式能提高sql语句的可读性，试想一下假设1方式里如果继续嵌套子查询那理解起来就复杂多了；

速率的提高是依赖服务器变量能够一定程度上优化查询；

 

3）使用临时表优化查询

如存在这样的情况：student表数据量很大，那么直接对这张表的某个字段执行聚合函数如avg(score)那么会长时间占用大量资源；

因此可以引进临时表，将student的部分数据提取到这个临时表里（临时表可以只存score字段），然后通过insert into将student的记录的一部分存入临时表；

然后对此表执行avg，保存结果，然后再继续提取后面的记录，最终将一次性庞大的计算分解成多个小计算，最后在对这些小计算的结果做avg处理即可得到最终的目的；

1.创建临时表：create temporary table score_temp(id bigint primary key auto_increment not null, score int(11) not null);

2.将student的记录逐步存入临时表：insert into score_temp(score) select score from student limit 0, 1000;（下一次是1001, 2000）

3.对score_temp的score进行avg(score)得到结果缓存，然后清空临时表（可通过truncate table score_temp;来清空），继续...limit 1001, 2000;

4.对每次avg的结果再avg得到真正需要的结果；

注：临时表在关闭连接时会被删除；

四、线上问题排查

可以用show full PROCESSLIST;来查询最近的操作，其中主要字段的含义分别是（如果是Navicat可以Tool->服务器监控->MySQL自动刷新来监控，还可以右键点 结束进程 来杀掉某个查询）：

1.command表示这个process正在做的事情，比如Quering；

2.time表示这个process持续的时间；

如果要杀掉某个数据库连接可以用kill processlist_id;

可以用下面的语句来获得那些超时了的连接所组成的kill id语句和其他一些信息（所以show processlist还可以用select* from information_schema.processlist来代替）；

select concat('kill ', id, ';') as command, db, command as opr_type, time, state, info as `sql`
from information_schema.processlist
where lower(command) != 'sleep'
and time > 60  # 单位是秒
order by time desc

　　

还可以用select* from information_schema.INNODB_LOCKS来看加锁情况

lock_type来查看是行锁还是表锁；