DBA专家门诊三期:性能诊断优化-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

DBA专家门诊三期:性能诊断优化

belle.zhoux 2015-04-23 14:44:11 27863
各位朋友,如在数据库性能诊断及优化过程中有什么疑问,请回复问题,会有DBA大牛为您解答!




一、发现问题:
RDS 提供了自定义报警功能,当实例的CPU,IOPS,连接数飙高,性能遇到瓶颈,手机就可以接收到报警短信了。
这个时候,如何去定位问题,解决问题呢?


二、定位问题:
实例的资源遇到瓶颈,一般首先需要排查的就是业务上的SQL。可以按照如下思路定位:
1. 控制台上提供了慢SQL统计功能;


2. 登录DMS(控制台右上角“登录数据库”), 执行show processlist, 当processlist的状态为以下情况时,就值得关注了:    
    Creating tmp table:正在创建临时表以存放部分查询结果。
    Copying to tmp table on disk:由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。
    Locked:被其他查询锁住了。
    Sending data :正在处理SELECT查询的记录,同时正在把结果发送给客户端。
    Sorting result: 排序操作,group by、order by。
    Waiting for table metadata lock: 等待元数据锁。
3. 时不时的Explain一把,看看SQL的执行计划。例如:
mysql> explain SELECT * FROM `users` WHERE domain = '' AND power >= 2 LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 22755 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
重点关注结果中的type 、Possible_keys、Key、extra字段。
    Type:如果为ALL,表示需要全表扫描,SQL需要优化了;
    Possible_keys:该查询可以利用的索引。如果没有任何索引可以使用,就会显示成null;
    Key:优化器从possible_keys 中所选择使用的索引;
    Rows:优化器通过系统收集到的统计信息估算出来的结果集记录条数;如果ROWS特别大,值得关注了;
    Extra:查询中每一步实现的额外细节信息,出现Using filesort, Using temporary时,值得关注了;


4. 控制台上执行实时诊断,可以自动化的生成SQL优化建议,包括添加索引的语句。


三、优化原则:
1. 简化SQL,快速执行,无阻塞,简单SQL比复杂SQL更高效;
2. 仅仅使用最有效的过滤条件,索引字段不是越多越好;
3. 只取出自己需要的 Columns,避免使用select *;
4. 覆盖索引可以直接返回结果,无须扫描数据;
    例如:select id,status from tab where id=2 ,建立组合索引(id,status),这个索引包含(或者说覆盖)所有需要查询的字段的值,MySQL利用索引返回select列表中的字段,而不必根据索引再次回表读取数据页。
5. 不仅仅是select,delete/update语句也需要建索引;
6. 尽可能在索引中完成排序(order by, group by的优化);
7. 尽量少用子查询,改写成多表JOIN;
8. 多表JOIN,永远用小结果集驱动大的结果集;
9. 索引列不能是表达式的一部分,也不能是函数的参数。
    下面两例中及时在id, gmt_created上建立索引,也会导致索引失效。
    select id from tab where id+1=5;
    select id,value from tab where to_days(now())-to_days(gmt_created) <=10;
    应该养成简化where条件的习惯,始终将索引列单独放在比较符号的一侧。正确的写法是:
    select id from tab where id = 5-1;
    select id,value from tab where gmt_created >= DATA_SUB(now(),interval 10 day );



SQL 存储 关系型数据库 MySQL 数据库 数据库管理 索引 RDS
分享到
取消 提交回答
全部回答(19)
滑动查看更多
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题
推荐课程