MySQL的优化

目录

一、expain分析计划

 二、show profile分析sql

三、索引的优化

四、sql的优化

---

一、expain分析计划

显示执行sql的相关信息

explain sql语句;

ID字段

表示的是查询中执行select子句或者是操作表的顺序。有以下三种情况

1、 id相同表示加载表的顺序是从上到下

explain select * from user ， role where user.id = role.id;

2、ID的值越大，优先级越高，表就越先执行

3、id有不同，也有不同，同时存在时；ID相同的可以认为是一组

select _type字段

 type字段

访问类型

 table字段

显示这一步所访问数据库中表名称有时不是真实的表名，有可能是简称。

rows字段

这一步所扫描的行数。

key

possible_keys：显示可能应用在这张表的索引，一个或多个。

key：实际所用的索引，如果为null，则没有使用索引。

key_len：表示索引中使用的字节数该值为索引字段最大可能长度，并非实际使用长度，在不实际损失精确性的前提下，长度越短越好。

extra字段

其他的额外的执行计划学习，在该列显示。

 二、show profile分析sql

该命令能帮助我们了解时间都耗费在哪里。

查看当前MySQL是否支持profile

select @@have_profiling;

打开profile功能

set profiling = 1;

查看耗时

show profiles;

trace分析优化器

打开trace ， 设置格式为json，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

set optimizer_trace = "enabled=on" , end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;

查看MySQL是如何执行sql的

select * from information_schema.optimizer_trace \G;

三、索引的优化

避免索引 失效应用--全值匹配

查询字段要与索引子段一致。

最左前缀法则

如果创建组合索引为

则以下情况均有效

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效

 只有name的索引有效而address无效

尽量使用覆盖索引（查询字段用索引字段），避免select *

需要从原表中即磁盘上读取数据

从索引树中就可以查询 所有数据

explain 的extra字段信息

用or分割开的条件。如果or前的条件中的使用索引，而后面的列中没有索引，那么涉及到的索引都不会用到

以%开头的like模糊查询，索引失效

其他匹配原则

1、如果MySQL评估使用索引比全表慢，则不使用索引。

2、is null ， is not null ，有时有效，有时索引失效（根据数据）。

3、使用in时索引有效，而not in 无效。

4、单列索引和复合索引，尽量使用复合索引。

四、sql的优化

大批量数据加载优化

1、当通过load向表中添加数据时，尽量保证文件中的主键有序，这样可以提高执行效率。

当主键保持有序和无序时的时间相差很大。

--加载大量数据
load data local infile '数据所在的路径' into table 表名 fields terminated by '数据间的分隔符' lines terminated by '\n'; 

2、 关闭唯一性校验

set UNIQUE_CHECKS=0;

记得在用完后打开。

优化insert语句

将多段insert语句合成一段，如

insert int tb values (1,'zhangsan'),(2,'lisi'),(3,'xiao');

 用事务，如

begin;
insert int tb values (1,'zhangsan')
insert int tb values (1,'zhangsan')
insert int tb values (1,'zhangsan')
commit; 

插入时主键尽量有序

优化order by

当对要排序的字段设置索引后则要求

1. order by后面的多个排序的字段尽量排序方式相同。
2. order by后面的多个排序的字段顺序尽量和组合索引字段顺序一致。

当没有设置索引时则可以适当提高sort_buffer_size 和 max_length_for_sort_data 系统变量，来增加排序区的大小，以提高排序的效率。

尽量查询时采用多表查询

优化limit查询

1、在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

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

由于ID为主键索引所以在查找连接时会很快

2、当主键自增长时，也是用到的是主键索引

select * from tb where  id > 900000 limit 10;