Msyql优化

Msyql优化

一、 定位低效率执行SQL-慢查询日志

– 查看当前会话SQL执行类型的统计信息

show session status like 'com_______';

– 查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息

show global status like 'com_______';

– 查看针对InnoDB引擎的统计

show status like 'Innodb_rows_%';

– 查看慢日志配置信息

show variables like '%slow_query_log%';

– 开启慢日志查询

set global slow_query_log = 1;  --  0表示关闭

– 查看慢日志记录SQL的最低阈值时间
– SQL的执行时间>=10秒，则算慢查询

show variables like '%long_query_time%';

– 例子
select sleep(10);

– 临时修改慢查询日志记录SQL的最低阈值时间

set globla long_query_time = 5; 

二、 查询SQL动态执行状态

– show proccesslist 查看客户端短连接服务器的线程执行状态信息

show processlist;

三、explain分析执行计划

explain select level,score_grade,
	round(count(score_grade)/total,3) ratio
from (select uid,level,score_grade,
	count(score_grade) over(partition by uid) total
from
(select uid,exam_id ,score ,level,
	case 
		when score >=0 and score<60 then '差'
		when score >=60 and score<75 then '中'
		when score >=75 and score<90 then '良'
		when score >=90 and score<=100 then '优'
		else null
	end score_grade
from exam_record
left join  user_info ui  
using(uid)
where score is not null) t1)t2
group by level,score_grade
order by level desc,ratio desc
;

Explain执行计划之ID

– id相同表示加载表的顺序是从上而下
– id不同，id值越大优先级越高，越先被执行

– Explain执行计划之select_type
– SIMPLE：没有子查询和union

– PRIMARY :主查询 子查询中的最外层查询

– SUBQUERY : 在select和where中包含子查询

– DERIVED : 在from中包含子查询，被标记为衍生表

– UNION : 若第二个select出现在UNION之后，则标记为UNOIN；若UNION包含在FROM子句的子查询中，外层selec 将被标记为：DERIVED

Explain执行计划之type

– all:全表扫描 最差的一种查询方式
– NULL:不访问任何表，直接返回结果
– system :查询系统表，直接从内存读取，不会从磁盘读取 5.7及以上版本不再显示system，直接显示all
– const： 命中主键或者唯一索引；被连接的部分是一个常量值
– eq_ref；左表有主键，而且左表的每一行和右表的每一行刚好匹配
– ref : 左表有普通索引，和右表配置是可能会匹配多行
– range：范围查询 where uid>3
– index ： 把索引列的全部数据都扫描

Explain执行计划之其他指标字段

– using filesort: 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，成为“文件排序”，效率低。
– using temporay: 需要建立临时表（temporay table）来暂存中间结果，常见于order by 和group by ;效率低
– using index : SQL所需要返回的所有列数据均在一颗索引树上，避免访问表的数据行，效率不错。

四、show profile 分析SQL

– 查看当前MySQL是否支持profile

select @@have_profiling;

– 如果不支持，设置打开

set profiling = 1;

– 查看执行命令的时间

show profiles;

show profile for query 13;

– 查看cpu的耗费时间

show profile cpu for query 25;

五、 trace分析优化器执行计划

set optimize_trace = "enabled=on",end_markers_in_json=on;
set optimize_trace_max_mem_size=1000000;

– doc终端执行

select * from information_schema.OPTIMIZER_TRACE ot \G;

六、 索引优化

创建组合索引

create index idx_seller_name_sta_addr on table(name,staus,address);

七、避免索引失效

避免索引失效之全值匹配:

和索引字段全部成功匹配，无顺序

避免索引失效之最左前缀法则：

组合索引中的最左边的字段(name)存在，索引才生效;若跳跃某一个子段（name->address），只有最左列(name)索引生效;

避免索引失效之其他匹配原则：

1范围查询右边的列不能使用索引（address索引失效）

select * from table where name='小米科技' and status >'1' and adress='北京'

2不要索引列上进行运算操作(name索引失效)

select * from table where substring(name,2,3)='科技';

3字符串不加单引号，造成索引失效(status索引失效)

select * from table where name = '小米科技' and status= 1;

4尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *(从磁盘读取数据)

select name from table where name='小米科技';

5用or分隔开的条件,则涉及的索引都不会被用到

select name from table where name='小米科技' or status = '1';

6以%开头的Like模糊查询，索引失效 可以使用select name（索引列），弥补不足

select * from table where name like '%科技';

7如果MySQL评估使用索引比全表更慢，则不使用索引

8is null ,is not null 有时候有效，有时索引失效（谁的数据少谁有效）

select * from table where name is null; -- 有效
select * from table where name is not null ; -- 无效

9 in走索引，not in 不走索引，如果是主键索引或者唯一索引，都使用

10尽量使用组合索引（如果一个表有多个单列索引，即使where中都是用了这些索引列，则只有一个最优索引生效）

八、SQL优化

大批量插入数据

1.主键顺序插入

2.关闭唯一性校验：在导入数据之前，如果表中有唯一索引，在插入时会对每一个数据就行校验

set unique_checks=0;
set unique_checks=1;

1） 优化insert语句

1.减少客户端连接

insert into table values(1,'tom'),(2,'tom'),(3,'tom');

2.手动开启事务，在事务中进行数据插入

begin;
insert into table values(1,'tom');
insert into table values(2,'tom');
insert into table values(3,'tom');
commit;

3.有序插入

2）优化order by语句

1、两种排序方式 filesort/using index

create index idx_emp_age on emp(age,salary)

explain select * from emp order by age; -- using filesort
explain select age from emp order by age; -- using index

order by后面的多个排序字段要求尽量排序方式相同

explain select id,age from emp order by age,id;

order by后面的多个排序字段顺序尽量和组合索引字段顺序一致

explain select id,age from emp order by salary,age; -- using index;using filesort

2、fielsort优化

两次扫描算法；会有产生磁盘IO操作，效率低
一次扫描算法：效率高
扩大max_length_for_sort_data 和 sort_buffer_size 的值，会优先使用一次扫描

优化子查询

子查询可以被join替代（因为子查询需要在内存中创建临时表）

3）优化limit查询

1.在索引上完成排序分页操作，最后根据主键关联原表进行查询

select * 
from table a ,(select id from table order by id limit (90000,10)) b where a.id = b.id;

2.把limit查询转换成某个位置的查询，适用于主键自增的表

select * from table where id>90000 limit 10;