Mysql基本优化

哇塞大嘴好帅(DaZuiZui)

于 2022-04-27 20:08:37 发布

阅读量948

点赞数 1

分类专栏： Mysql 文章标签： mysql java

本文链接：https://blog.csdn.net/qq_40102411/article/details/124459396

版权

Mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Mysql基本优化

0.sql语句优化

0.1最好保证小表驱动大表

for(int i = 0 ; i< 5 ;i++){
    for(int j = 0 ; j < 1000 ;j++){
        
    }
}

数据库连接和释放时很消耗性能的，通过上面例子我们可以的得出只进行了5次小表驱动大表

如果在小表驱动大表用exists效率要高于in

select * from A where exists(select 1 from b where B.id = A.id)

如果迫不得已出现了大表驱动小表的情况那么就用in效率要高于exists

总结：

因为in会优先查里面的表，然后在查外面的，然后做匹配。

exists会将外面的表先查了，在跟里面的表数据做匹配。

所以确保了小表驱动大表

0.2索引的一些优化

之前的文章有介绍过

https://blog.csdn.net/qq_40102411/article/details/124438570?spm=1001.2014.3001.5502

0.3 order by 关键字优化

使用order by子句尽量使用index方式排序，避免使用file sort方式排序，尽可能在索引列上完成排序操作，只有满足最佳做前缀法则才会使用index方式排序。如果不在索引列上，

filesort有两种算法，双路排序、单路排序

**双路排序：**在Mysql4.1之前使用的是双路排序，字面的意思就是两次扫描磁盘，最终得到的数据，通过读取行指针和order by列，对他们进行排序，然后扫描已经排序的列表，按照列表中值重新从列表中读取对应的数据输出。

总结一句话就是：通过2次扫描磁盘然后得到最终数据，然后再buffer进行排序，然后在从磁盘取其他字段

去一批数据要对磁盘进行2次扫描，总所周知，IO是很耗时的，所以在mysql4.1以后，出现了第二种改进的算法就是单路排序

**单路排序算法：**从磁盘读取查询需要的所有列，然后按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它这样效率会快一点避免二次排序。并且把随机的IO变成了顺序IO，但是它会使用更多的空间，因为他把每一行都保存在了内存中。

总体来说单路排序要比双路好多了，在sort_buffer中，单路排序比双路排序要占用很多内存，因为单路排序是把所有字段都取出来，所以有可能去除的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建临时文件，多路合并），排序完了再取sort——buffer容量大小，从而多次IO。

解决方法

优化策略就是

增大sort_buffer_size参数的设置

增大sort_buffer_size的参数好处就是不管使用什么算法都会提高效率，但是他的坏处是要根据系统的能力取提高，因为这个参数是针对每个线程的。

增大max_length_for_sort_data参数的设置

提高这个参数的好处是会增加用改进算法的概率，但是如果设置的太高，数据总容量会超出sort_buffer_size的概率就会增大。明显症状就是搞得磁盘IO活动和低的处理器使用率。

所以当order by 使用select *的时候是一个大忌，因为Query的字段大小综合小于max_length_for_sort_data且排序字段不是text or blob类型的时候，会使用单路排序，否则会使用多路，虽然说两种算法的数据可能超出sort_buffer的容量，超出以后会创建tmp文件进行合并排序，导致多次I/O,但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size .

总总结：

Mysql的两种排序方式就是文件排序或者扫描有序索引的排序。

Mysql能为排序和查询使用相同的索引

0.4 group by关键字的优化

它基本和order by一致，group by是先排序后进行分组，也是按照最佳左前缀法则当无法使用索引列增大max_length_for_sort_data和sort_buffer_size,where高于having，能写在where里面限定就不要去having

1.慢查询的开启并捕获，

Mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值（阈值）得sql，则会被记录到慢查询日志中。

默认情况下Mysql数据库没有开启慢查询日志，如果不是调优需要的话，一般不建议启动该参数，因为开启会造成性能影响

开启慢查询

set global slow_query_log = 1;

重启mysql会失效

默认的阈值为10秒

# 设置阈值
set global long_query_time =3 #设置为3秒
# 查看当前系统下的慢sql
show global statuys like ‘%Slow_queries%’

mysql慢查询分析工具mysqldumpslow

# 返回记录集合最多的10个sql
mysqldumpslow -s r -t 10 日志地址
# 访问次数最多的10个sql
mysqldumpslow -s c -t 10 日志地址
# 得到按时间排序的前10条里面含有左连接的查询语句
mssqldumpslow -s t -t 10 -g "left join" 日志地址

我们可以通过访问文档来查看mysqldumpslow的帮助信息

mysqldumpslow --help

s:  按照什么方式排序
c:  访问次数
l:  锁定时间
r:  返回记录
t:  查询时间
al: 平均锁定时间
ar: 平均返回记录数
at: 平均查询时间
t:  返回前面多少条
g:  后面搭配一个正则，大小写不敏感

2.explain+慢sql分析，

https://blog.csdn.net/qq_40102411/article/details/124417548

3.show profile查询sql在mysql服务器里面的执行细节和生命周期情况

#1. 查看当前mysql版本是否支持、
#2. 开启show profile
set profiling = on； 
#3. 运行sql
#4. 查看结果 show profiles
show profiles
#5. 诊断sql
show profile cpu,block io for query 上一步前端的sql数字号码

如果诊断sql出现了

converting heap to myisam 查询结果太大了，内存不够了往磁盘上搬了

creating tem table 创建临时表

copying to tmp table on disk 把内存中临时表复制到磁盘最危险！！！

locked 出现锁的情况了