mysql(4):性能分析和性能优化
性能分析
慢查询日志分析
①查询慢查询日志的状态
show global variables like '%slow_query_log%';
②开启慢查询日志(当mysql重启时会重置)
set global slow_query_log;
③查询mysql默认限制慢sql语句的上限时间值
show variables like '%long_query_time%'; # 默认10s
④设置long_query_time的值
set global long_query_time=3; # 推荐使用config而不是这条命令
⑤显示慢sql的条数
show global status like '%Slow_queries%';
分析慢查询日志的工具
mysqldumpslow
explain查看执行计划
之前好像讲过了。
profile性能分析
①查询当前的profile状态
show variables like 'profiling';
②打开此功能
set profiling=on;
③查询所有sql执行的耗时时间
show profiles; # 这里会有sql编号
④显示具体那一条的sql语句的具体状态(sql语句执行的生命周期)
show profile [all|cpu,block io] for query sql编号;
如果在状态中出现converting HEAP to MyISAM或Creating tmp table或Copying to tmp table to disk或locked这四个字段其中的一个或一个以上,说明此sql出现问题的原因。
性能优化
服务器层面优化
innodb_buffer_pool_size设置
调优参考计算方法:
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 则考虑增大 innodb_buffer_pool_size, 建议使用物理内存的75%
val < 95% 则考虑减小 innodb_buffer_pool_size, 建议设置为:Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024 * 1024 * 1024)
设置命令:
set global innodb_buffer_pool_size = 2097152; //缓冲池字节大小,单位kb,如果不设置,默认为128M
设置要根据自己的实际情况来设置,并不是设置越大越好,可能设置的数值太大体现不出优化效果,反而造成系统的swap空间被占用,导致操作系统变慢,降低sql查询性能。
内存预热
默认情况,只有某条数据被读取一次,才会缓存在 innodb_buffer_pool。所以,数据库刚刚启动,需要进行数据预热,将磁盘上的所有数据缓存到内存中。数据预热可以提高读取速度。
innodb_log_file_size设置
推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
选择SSD磁盘提高读写能力
SQL设计层面优化
中间表的设计
冗余字段的设计
拆表字段
把常用字段放在主表,不常用字段放在子表,两张表一对一关联。
拆表数据(分库分表)
拆分数据源,可分为拆库和拆表。
拆库的要求是所有数据可通过某一个唯一标识聚合到一起。而这种拆分方式,分为两种:hash拆分和冷热隔离。
hash拆分
比如某个库是用来存储用户多维度信息的,可通过用户id聚合到一起,这种库的特点是多表有共同的聚合id字段。
这种方式的拆库,需要同时操作多个库,比如mod 10
取值,那需要同时操作10个库,每个库的链接请求都是同量的,同时跨库操作也是十分麻烦的,事务就不用说了,维护成本也是成倍增加的。
优点是风险分散,某一个库挂了,其他库不受影响,整体服务受影响的比例也会降低。
冷热隔离
冷热隔离,是指把冷热数据区分开,这种隔离是基于数据访问的时效性,比如新闻资讯,越近的数据被访问概率越大。冷热的区分期间,或以时间来分割,或以id来分割。这种隔离的特点是,一core多non-core,重点维护core数据,而non-core随着时间推移,越早的数据被访问的概率越小,甚至可被直接归档。这样维护的数据会是极少的几个数据库。
SQL语句优化
limit优化
- 延迟加载(分页)
select id from test where id = 最大id limit 10;
- 做索引:对于有where条件,又想走索引用limit的,必须设计一个索引,将where放第一位,limit用到的主键放第2位,而且只能select主键。
索引优化
如何创建索引并正确使用索引组合
联合索引优化策略:(选择索引列的顺序)
- 经常会被使用到的列优先
- 选择性高的列优先
- 宽度小的列优先
order by group by与索引设计的联系
order by在两种情况下满足Using Index排序:
- 最左前缀法则,使用的复合索引为最左前列;
- 使用的where子句与order by子句条件组合满足索引最左前列;
group by与order by遵循的原则基本一致。除了:能在where限定的条件就不要在having中限定。
当无法使用索引列时,group by与order by需要增加max_length_for_sort_data与sort_buffer_size参数设置。(因为单路与双路排序的原因:当数据量大且多单路排序一次完不成时,就会再次执行,最终将结果合并,这样可能导致,两路以上的排序。过多的路排序导致IO流操作频繁)
其他优化项
- join语句优化:尽可能减少Join语句中的内循环次数,"永远是小结果集驱动大的结果集";保证Join语句中的被驱动表上join条件字段已经被索引;
- 最左前缀法则
- 范围条件(range)右边的所有条件全失效。
- 使用<、<=、>、>=等条件操作导致全表扫描失效。
- 一般最好写like查询的条件是"字符串%",而"%字符串%"与"%字符串"这两种形式是会导致索引失效。用“覆盖索引:建立的索引与查询的字段最好查询个数与顺序上最好完全一致”解决;
- 查询条件varchar类型的字符串不加单引号会导致索引失效。
- 少用or,用它来链接查询的是否也会导致索引失效。
- 索引列上无计算操作,不然也会到索引失效。
最新文章
- php 函数汇总
- 如何在Chrome39添加360抢票王插件
- APP测试流程
- iOS:个性化UITextView(缩进,行距,铺满)
- 【翻译】Kinect v2程序设计(C++) Color篇
- URAL 1069 Prufer Code(模拟)
- Ubuntu install mysql
- Valgrind: memcheck of memleak/mem-uninitialization; massif usage
- mui项目实时更新
- maven 构建参数和命令
- Robot Framework--ride使用说明2
- 恢复制作了系统盘的U盘
- 第四章&#160;栈与队列(d)队列接口与实现
- DOM系列基础知识
- np.random.rand均匀分布随机数和np.random.randn正态分布随机数函数使用方法
- 2018.08.06 bzoj1503: [NOI2004]郁闷的出纳员(非旋treap)
- ssh 通过跳板机连接到远程服务器
- php替换str_replace的使用方法,支持多个替换
- c#的static什么时候使用
- LA 3938 动态最大连续和(线段树)
热门文章
- webpack4从安装到使用入过的那些坑
- 字符串问题----去掉字符串中连续出现K个0的子串
- 题解 AT4556 【12/22】
- 解决linux 终端UnicodeDecodeError: &#39;utf-8&#39; codec can&#39;t decode byte 0xce in position 0: invalid continuation byte
- JS 百度地图-右键菜单
- linux基础之CentOS启动流程
- cf 手机短信问题
- Java期末考试冲刺总结
- 《NVM-Express-1_4-2019.06.10-Ratified》学习笔记(6.15)-- 写命令
- nginx中部署前端,后端打成jar包运行