1.为什么要调优
由于某些语句执行效率缓慢,导致无法得到及时响应
2.简单调优准备
打开慢查询日志
show variables like 'slow_query_log'
show variables like '%log%'(查看变量设置)
set global slow_query_log_file = 'path'
set global log_queries_not_using_indexes =on
set global long_query_time = 1(大于1秒的查询记录下来)
explain 从句分析查询性能(具体参数意义可自行查询)
3.调优方式
1)SQL语句优化
针对max函数可以用索引优化,覆盖索引
count优化 筛选条件需要增加or null(以免出现数据不一致情况)
子查询优化 优化成join查询 防止重复数据(1对多。需要增加去重关键字distinct)
group by优化 (默认使用临时表,所以优化方式是不要使用临时表还有文件表) inner join ( a group by a.id ) as c using(id),即建立相关索引
limit 优化 时常伴随 order by 所以需要进行文件过滤 :使用主键或者索引列进行order by操作 返回上次查询的id 然后进行id过滤、
2)索引优化
建立合适的索引
where从句 group by从句 order by从句 on从句出现的列适合增加索引
索引的字段越小越好
离散度大的列放到联合索引前面
索引优化sql的方法(增加索引有利于查询,不利于写入,避免重复索引,冗余索引,索引变更会带来问题,需要注意)
3)数据表结构优化
选择合适的数据类型
存下数据的最小类型
使用简单的数据类型
尽可能的使用notnull定义字段
尽量少用text类型,非用不可的时候考虑分表
范式化和反范式化(第三范式)
反范式化 : 空间换时间
删除异常 ,更新异常 方式: 表拆分
表结构的垂直拆分
把原来有很多列的表拆分成多个表
解决了表宽度的问题
把大字段独立存放到一张表中
把不常用字段单独存放到表中
把常用字段存放在一起
表结构的水平拆分
解决表的数据量大
表结构相同
数据均分方法(HASH)
问题:跨表进行数据查询,统计及后台报表操作(解决方法:前后台查询分开)
4)系统优化
增加tcp支持队列数
net.ipv4.tcp_max_syn_backlog=65535
减少断开连接时,资源回收
net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10
打开文件数限制
关闭防火墙
(硬件防火墙)
5)服务器硬件优化