MySQL优化

2020-06-17 本文已影响0人洛美萨斯

1.查询优化

1.1小表驱动大表

image-20200512174213505

emp有500000条数据,empnos有7条数据,emp是大表,empnos是小表

image-20200512175240591

image-20200511172852730

1.2order by关键字优化

表字段多的时候，避免使用select *

image-20200511175643225

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

image-20200511180402492

使用表test03,简历联合索引idx_test03_c1234(c1,c2,c3,c4)

image-20200512183458561

order by使用索引最左前缀

image-20200512182202978

如果where使用索引的最左前缀定义为常量,则order by能使用索引

image-20200512182448571

不能使用索引排序

排序不一致
image-20200512183207797
丢失最左前缀索引
image-20200512183220342
丢失中间索引,索引中断
image-20200512183233368
使用了非索引字段进行排序
image-20200512183244339
使用了in(相当于范围查询)
image-20200512183256565

order引起索引失效的各种情况:

image-20200512183148547

filesort的两种算法:单路排序与双路排序

双路排序:MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取行指针和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

但是双路排序会扫描两次磁盘，磁盘IO是非常消耗性能的，所以后面被单路排序取代。

单路排序:从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间。

但是单路排序算法可能会导致一个问题：如果数据量过大，一次读取不完，就会导致读取的次数比双路排序多。

因为读取操作是在 sort_buffer 中，如果数据量过大，超出了 sort_buffer 的容量，导致每次只能读取 sort_buffer 容量大小的数据进行排序，排完再取，导致多次IO。

优化策略

增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置

1.3group by

同order by

group by 实质是先排序后分组,遵照索引键的最佳左前缀

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

where高于having,能写在where限定的条件就不要去having限定了。

2.慢查询日志

查看慢查询是否开启及日志存放位置: show variables like '%slow_query_log%';

开启慢查询日志: set global slow_query_log=1;

image-20200511224122389

查看慢查询阈值: show variables like 'long_query_time';

设置阈值为5s: set global long_query_time=5;

image-20200511224305509

测试:

image-20200512214511048

查看日志: cat /www/server/data/mysql-slow.log

image-20200512214533710

3.show profiles

mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量

官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

分析步骤：

是否支持，看看当前的SQL版本是否支持
开启功能，默认是关闭，使用前需要开启:

查看是否开启: show variables like 'profiling';
image-20200512220411099
开启: set profiling=on;
image-20200512220515182
运行SQL
image-20200512220928439
查看结果，show profiles；
image-20200512220946991
诊断SQL，show profile cpu,block io for query 上一步前面的问题SQL 数字号码；

对上面第六句sql进行cpu资源占用,磁盘IO进行具体分析

show profile cpu,block io for query 6;
image-20200512221201424
日常开发需要注意的结论
- converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
- Creating tmp table 创建临时表:拷贝数据到临时表,用完再删除
- Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
- locked