欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

查询速度慢，优化 sql 和索引

最编程 2024-07-19 22:50:12
...

模拟大量数据表、脚本

explain执行计划属性含义

1，id值越大越先执行，id相同顺序执行
2，select_type:simple、primary、subquery、derived衍生、union、union result

select_type 含义 sql
simple 简单的select查询，查询中不包含子查询或者union explain select * from employees join dept_emp on employees.emp_no= dept_emp.emp_no where employees.emp_no=10;
primary 子查询中，最外层的查询 explain SELECT * FROM employees WHERE employees.emp_no = (SELECT MAX(emp_no) FROM employees);
subquery 子查询 explain SELECT * FROM employees WHERE employees.emp_no = (SELECT MAX(emp_no) FROM employees);
derived from列表中包含了子查询，mysql会递归执行这些子查询，把结果放在临时表中 explain select * from employees join (SELECT MAX(emp_no) t1 FROM employees) temp on employees.emp_no= temp.t1;
union 查询中包含union表示并集并且去重，union all不去重 explain select * from employees where first_name = ‘xyz’ union select * from employees where last_name = ‘xyz’;
union result union去重结果 explain select * from employees where first_name = ‘xyz’ union select * from employees where last_name = ‘xyz’;

3，type访问类型

type 含义 举例
null mysql不访问任何表，索引，直接返回结果 explain select now();
system 系统表，少量数据，不需要磁盘io，mysql5.7显示all
const 命中主键primary key或者唯一unique索引；被连接部分是一个常量const值 explain select * from employees where emp_no=3;
eq_ref 前表的每一行，后表只有一行被扫描。1join查询，2命中主键或非空唯一索引，3等值连接 explain select * from dept,emp where emp.id=dept.id;
ref 非唯一性索引扫描，返回匹配某个单独值的所有行。
range where之后出现between，<，>，in等操作 explain select * from employees where emp_no<=3;
index 需要扫描索引上全部数据。 explain select emp_no,first_name from employees;
all 全表扫描

4，extra额外的执行信息

extra 含义 举例
using filesort 排序字段为非索引字段，或者组合索引中非左索引倒序 explain select emp_no,first_name from employees order by birth_date desc;
using temporary 需要建立临时表暂存中间结果，常见于order by和group by explain select count(*),last_name from employees group by last_name;
using index 二级索引字段和主键满足查询要求，不需要回表，效率好 explain select count(*),first_name from employees group by first_name;

show profiles

MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。
MySQL调优–03—show profiles 和 show processlist

索引优化

1，组合索引
1.1，组合索引，只要字段匹配成功即可，与字段顺序无关
1.2，组合索引，最左匹配
1.3，组合索引中，非最左字段使用范围查询，会导致组合索引中范围字段后边的所有字段索引不生效。

2，不要在索引列上进行运算操作，索引将失效

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

3，字符串不加单引号，导致索引失效。因为mysql优化器会进行类型转换
4，尽量使用覆盖索引，避免select *，会导致回表
5，or前后需要都使用索引，引起索引合并，索引才能生效。如果一侧使用索引会导致索引失效。多表查询中哪怕or两边都带有索引也会导致索引失效。遇到or尽量用union或者union all代替。
6，以%开头的like模糊查询，导致索引失效。
7，索引列数值重复概率大，mysql以为使用索引比全表扫描慢，会不使用索引。
8，is null或者is not null会根据值null或者非null比例判断是否值得使用索引。
9，普通索引中，in走索引，not in索引失效。主键索引in和not in都走索引。
10，多个普通索引字段，只会生效一个，其他索引会失效。如果条件中包含几个字段，尽量使用组合索引。

sql优化

order by优化：

1，查询字段尽量使用索引字段和主键，减少回表。
2，order by排序字段有多个时，尽量要么同时递增，要么同时递减。
3，排序前后顺序尽量和组合索引字段顺序一致。
4，如果必须使用到filesort时，max_length_for_sort_data大小和query语句取出的字段总大小，来判断一次扫描还是两次扫描，可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序效率。

子查询优化：

子查询会生成临时表，效率低于连接join查询。

limit优化：

比如 limit 100000,10，mysql需要排序前100010记录，仅仅返回最后10条记录，其他记录丢弃，查询排序代价非常大。
方式1：索引上完成排序分页操作，最后根据主键关联回原表所需要的其他列数据。

select * from employees join
    (select emp_no from employees
where first_name like 'x%' order by birth_date desc
limit 100000,10) as temp
         where employees.emp_no=temp.emp_no;

方式2：只适用于主键自增。

select * from employees
where first_name like 'x%' and emp_no>100000 order by emp_no asc
limit 10;

sql优化 涉及到索引失效和慢查询

对慢查询的优化：
在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们.
慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?
所以优化也是针对这三个方向来的,
首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.

上一篇： 在软考论文中探讨需求跟踪矩阵的实现与应用

下一篇： 廸昇25天學習營第30天 | MindNLP ChatGLM-6B StreamChat

推荐阅读