mysql 的sql优化

最新推荐文章于 2025-04-01 16:43:39 发布

ECHO_FOLLOW_HEART

最新推荐文章于 2025-04-01 16:43:39 发布

阅读量679

点赞数

分类专栏： mysql 文章标签： mysql sql优化

本文链接：https://blog.youkuaiyun.com/ECHO_FOLLOW_HEART/article/details/56672432

版权

mysql 专栏收录该内容

11 篇文章

订阅专栏

效率低的sql定位

show [session|global] status命令可以提供服务器状态信息，可以知道读写比率；回滚操作的情况，太频繁说明程序有问题

Slow_queries：慢查询的次数

定位执行效率较低的sql语句

通过慢查询日志定位那些执行效率较低的sql语句，用–log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的sql语句的日志文件。
慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前mysql在进行的线程，包括线程的状态，是否锁表等，可以实时地查看sql的执行情况，同时对一些锁表操作进行优化。

通过explain分析低效sql的执行计划

explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id=b.customer_id and email=’JANE.BENNETT@sakilacustomer.org’\G

名称	说明
select_type	simple(简单表，即不使用连表或者子查询);primary(主查询，即外层的查询)；union(union中的第二个或者后面的查询语句)；subquery(子查询中的第一个select)等。
table	输出结果集的表
type	表示mysql在表中找到所需行的方式，或者访问类型，常见类型按照性能最差到最好排序all, index, range, ref, eq_ref, const或者system, null。all表示全表扫描；index表示索引全扫描；range表示索引范围扫描；ref表示使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行；eq_ref和ref的区别在于eq_ref是唯一索引；const或者system表示单表中最多有一个匹配行，可以被优化器在当前查询中当作常量来处理；null类型，mysql不用访问表或者索引，直接就能得到结果
possible_keys	表示查询时可能使用的索引
key	表示实际使用的索引
key_len	使用到索引字段的长度
rows	扫描行的数量
Extra	执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

explain extended（更加清晰易读）和explain partitions(可以看分区)

show profile

查看当前mysql是否支持profile

select @@have_profiling;

默认profiling是关闭的，可以通过set语句在Session级别开启profiling

set profiling=1;

查看所有sql的执行时间和id

show profiles

查看某条sql语句的详细执行过程中线程的每个状态和消耗时间, 可以确定出那个状态耗费的时间最多。

show profile for query 4;

mysql支持进一步选择all,cpu,block io,context switch,page faults等明细类型来查看mysql在使用什么资源上耗费了过高的时间。比如查看cpu的耗费时间

show profile cpu for query 4;

通过trace分析优化器如何选择执行计划

打开trace，设置格式为json

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

设置内存大小

set optimizer_trace_max_mem_size=1000000;

前面的工具分析是为了确定问题，确定问题后，就要采取相应的优化措施。确定问题

优化措施

大批量插入数据

当用load命令导入数据的时候，适当的设置可以提高导入的速度。对于MyISAM存储引擎的表，可以通过以下方式快速地导入大量的数据

alter table tbl_name disable keys;
loading the data
alter table tbl_name enable keys;
disable keys和enable keys用来打开或者关闭MyISAM表非唯一索引的更新。在导入大量的数据到一个非空的MyISAM表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的MyISAM表，默认就是先导入数据然后才创建索引的，所以不用进行设置。对于InnoDB，这种方式并不能提高导入数据的效率。
在导入数据前执行set unique_checks=0,关闭唯一性检验，在导入结束后执行set unique_chechs=1，恢复唯一性检验，可以提高导入的效率。
如果应用使用自动提交的方式，建议在导入前执行set autocommit=0,关闭自动提交，导入结束后再执行set autocommit=1，打开自动提交，也可以提高导入的效率。

优化insert语句

如果同时从同一客户插入很多行，应尽量使用多个值表的insert语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗。
如果从不同客户端插入很多行，可以通过使用insert delayed语句得到更高的速度。delayed的含义是让insert语句马上执行
将索引文件和数据文件分在不同的磁盘上存放
如果进行批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提高速度，但是这只能对MyISAM表使用
当从一个文本文件装载一个表时，使用load data infile。这通常比使用很多insert语句快20倍。

优化order by 语句

mysql的两种排序方式

通过有序索引顺序扫描直接返回有序数据
通过对返回数据进行排序

优化目标

尽量减少额外的排序，通过索引直接返回有序数据
where条件和order by使用相同的索引
order by 的顺序和索引顺序相同

以下情况不使用索引

select * from tbl_name order by field1_part1 desc, field1_part2 asc;混合使用asc和desc
select * from tbl_name where field2=constant order by field1; 用于查询行的关键字与order by中所使用的不相同
select * from tbl_name order by field1, field2;对不同的关键字使用order by

Filesort优化

Filesort两种排序算法

两次扫描算法：首先根据条件取出排序字段和行指针信息，之后在排序区sort buffer中排序。如果排序区sort buffer不够，则在临时表temporary table中存储排序结果。第一次获取排序字段和行指针信息，第二次根据行指针获取记录。优点是排序的时候内存开销较少。
一次扫描算法：一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销较大，排序效率比两次扫描算法要高。

mysql通过系统变量max_length_for_sort_data的大小和query语句取出的字段总大小来判断使用那种排序算法。如果max_length_for_sort_data更大则使用一次扫描，否则使用二次扫描。

Filesort优化

适当加大系统变量max_length_for_sort_data的值，能够让mysql选择更优化的Filesort排序算法。当然，假如max_length_for_sort_data设置过大，会造成cpu利用率过低和磁盘i/o过高。
适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；当然也不能无限制加大sort_buffer_size排序区，因为sort_buffer_size从参数是每个线程独占的，设置过大，会导致服务器swap严重。
尽量只使用必要的字段，而不是选择所有字段。

优化group by 语句
对于不需要排序的结果，可以指定order by null禁止排序
优化嵌套查询

有些情况下，子查询可以被更有效率的连接替代

优化or条件

对于含有or的查询字句，如果要利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引。

优化分页查询

场景描述limit 1000,20。此时mysql排序出前1020条记录后仅仅需要返回第1001到1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。

优化思路

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

select a.film_id, a.desc from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id = b.film_id

添加一个参数last_page_record，用来记录上一页最后一行的编号。
```
select * from payment where rental_id < last_page_record order by rental_id desc limit 10
```
此优化只适合排序字段不会出现重复值的特定环境，如果排序字段出现大量重复值，可能会丢失部分记录，不适合这种优化方式。