开发者社区> 问答> 正文

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

各位朋友,如在数据库性能诊断及优化过程中有什么疑问,请回复问题,会有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 );



展开
收起
belle.zhoux 2015-04-23 14:44:11 28707 0
19 条回答
写回答
取消 提交回答
  • 叶正盛,花名斗佛,2010年加入阿里巴巴,目前是阿里巴巴集团数据产品负责人,数据库技术爱好者。个人比较关注数据库、web、java、云计算、大数据、产品设计等领域。
    ReDBA专家门诊三期:性能诊断优化
    DBA的俞月 妹子很给力啊,佩服
    2015-05-05 16:44:59
    赞同 展开评论 打赏
  • 牛B,学习了
    2015-05-05 11:46:40
    赞同 展开评论 打赏
  • 如果有对多表关联有疑问,可以看看这篇帖子里的优化思路:
    http://bbs.aliyun.com/read/239751.html?spm=5176.7189909.0.0.Fs8YqY
    2015-05-05 10:34:29
    赞同 展开评论 打赏
  • 淘宝打折网:http://www.shazhekou.com/  
    2015-05-04 15:24:40
    赞同 展开评论 打赏
  • ReDBA专家门诊三期:性能诊断优化

    知道了吗
    2015-05-03 19:09:37
    赞同 展开评论 打赏
  • mysql如何实现like民生这样的有效索引查询。
    mysql如何实现like '%民生%' 这样的有效索引查询。

    1、我创建一个表:
    CREATE TABLE `tb_news` (
      `id` bigint(20) NOT NULL auto_increment,
      `title` varchar(100) default NULL,
      `content` mediumtext,
      `keywords` varchar(50) default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312
    2、同时创建索引:
    create index tb_news_title on tb_news(title);
    3、有10万条数据,实现按标题title的模糊查询如:
    select id,title from news where title like '%民生%';
    4、我知道:
    select id,title from news where title like '民生%';
    索引是有效的。
    可是select id,title from news where title like '%民生%';
    索引就无效了。
    有什么好的办法解决标题title的模糊查询。

    感谢希望给予回复!

    -------------------------

    回23楼俞月的帖子
    多谢您的指导!
    2015-05-02 21:16:48
    赞同 展开评论 打赏
  • 撒分,发红包了,恭喜!
    2015-04-29 00:02:44
    赞同 展开评论 打赏
  • 数据存储与数据库
    Re回4楼cyb的帖子
    distinct 可以认为是group by的特例。


    SELECT DISTINCT
      LoginId,   SubId FROM   TB WHERE 1 ORDER BY Visit ASC LIMIT 8888, 10


    这个SQL等价于: select LoginId, SubId from TB where 1 group by LoginId, SubId ORDER BY Visit ASC LIMIT 8888, 10
    单纯就这条SQL而言, ORDER BY Visit ASC 是没必要的,因为select选出的字段中没有 Visit字段,建议添加组合索引( LoginId, SubId )。


    假如SQL是 select LoginId,SubId,Visit from TB where 1 group by LoginId, SubId ORDER BY Visit ASC LIMIT 8888, 10
    这里可以建议组合索引( LoginId, SubId) 或者( LoginId, SubId,Visit
    Visit字段添加到索引中,仍旧避免不了排序,上面SQL的执行过程是:
    1. select LoginId,SubId,Visit from TB where 1 group by LoginId, SubId
    2. ORDER BY Visit ASC LIMIT 8888, 10
    第一步中的group by (或者distinct)可以利用到索引( LoginId, SubId )避免临时表,排序
    下面是测试案例:

    mysql> show create table tb\G
    *************************** 1. row ***************************
           Table: tb
    Create Table: CREATE TABLE `tb` (
      `id` int(11) DEFAULT NULL,
      `LoginId` int(11) DEFAULT NULL,
      `SubId` int(11) DEFAULT NULL,
      `vist` int(11) DEFAULT NULL,
      KEY `idx1` (`id`),
      KEY `login_subId` (`LoginId`,`SubId`),
      KEY `login_subId_vist` (`LoginId`,`SubId`,`vist`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    mysql> explain select LoginId, SubId, vist from tb where 1 group by LoginId,SubId ;
    +----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+
    | id | select_type | table | type  | possible_keys                | key         | key_len | ref  | rows | Extra |
    +----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+
    |  1 | SIMPLE      | tb    | index | login_subId,login_subId_vist | login_subId | 10      | NULL |    9 | NULL  |
    +----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+
    1 row in set (0.00 sec)



    第二步中将步骤1中的结果集按照 Visit 字段排序,这一步的排序不可避免。


    上面提到了将visit字段加入到组合索引中,是为了构建覆盖索引。避免返回到数据行去查询。


    对于您提到的问题,
    排序方式有多种,Visit 、ID、Count等,是不是有一种排序就要建一个“覆盖索引”?
    distinct/group by的优化,关键是要利用索引,避免distinct/group by时的创建临时表,排序。
    将order by的字段加入到组合索引中,目的是为了直接从二级索引字段获取到结果集,避免再去查数据行。
    加了覆盖索引,索引字段变长,查询性能变好,同时也会导致占用空间、插入性能变慢。
    所以是否去创建覆盖索引,还是需要依旧您的具体业务而定的。




    还需要单列给Visit 、ID、Count、SubId、LoginId建索引吗?
    不需要给单列 Visit 、ID、Count、SubId、LoginId建索引,这里在order by 的字段上建立索引,还是不能避免排序。


    -------------------------

    Re回5楼cyb的帖子


    普通limit M,N的翻页写法,往往在越往后翻页的过程中速度越慢,原因 mysql会读取表中的前M+N条数据,M越大,性能就越差:
    select  *  from t where sellerid=100 limit 100000,20 优化写法: select t1.* from  t t1,             (select id from t  sellerid=100 limit 100000,20) t2 where t1.id=t2.id; 优化后的翻页写法,先查询翻页中需要的N条数据的主键id,在根据主键id 回表查询所需要的N条数据,此过程中查询N条数据的主键ID在索引中完成


    这种优化的根本出发点,是减少在数据页中的扫描量。
    覆盖索引,也是一种优化思路,出发点就是直接从二级索引中直接获取查询结果。


    您也可以将您的业务SQL发一下,一起来看看如何优化。



    -------------------------

    回15楼cyb的帖子
    T_Query 是一个视图对吧?
    可否将T_Query的建表语句发一下呢?

    -------------------------

    Re回15楼cyb的帖子



    SELECT DISTINCT
      LoginId,
      SubId
    FROM
      T_Query
    WHERE 1
    ORDER BY fenshu DESC
    LIMIT 61630, 10


    这条SQL从实现的功能而言,其实没必要加ORDER BY fenshu DESC 的。
    distinct只能返回它的目标字段,而无法返回其它字段。 所以在SELECT DISTINCT LoginId, SubId FROM T_Query中取出的是 LoginId,SubId不重复的行。也就是说,必须LoginId和SubId都相同才会被排除。


    做个测试:

    mysql> select * from tb;
    +------+---------+-------+------+
    | id   | LoginId | SubId | vist |
    +------+---------+-------+------+
    |    1 |     123 |    21 |   78 |
    |    2 |      43 |    71 |   78 |
    |    3 |      43 |    21 |   78 |
    |    2 |      43 |    71 |   78 |
    |    3 |      43 |    21 |   78 |
    |    2 |      43 |    71 |   78 |
    |    5 |      73 |    21 |   78 |
    |    2 |      55 |    67 |   78 |
    |    1 |      98 |    21 |   78 |
    +------+---------+-------+------+
    9 rows in set (0.01 sec)



    mysql> select distinct LoginId,SubId from tb ;
    +---------+-------+
    | LoginId | SubId |
    +---------+-------+
    |      43 |    21 |
    |      43 |    71 |
    |      55 |    67 |
    |      73 |    21 |
    |      98 |    21 |
    |     123 |    21 |
    +---------+-------+
    6 rows in set (0.00 sec)



    mysql> select distinct LoginId,SubId from tb where 1 order by vist;
    +---------+-------+
    | LoginId | SubId |
    +---------+-------+
    |      43 |    21 |
    |      43 |    71 |
    |      55 |    67 |
    |      73 |    21 |
    |      98 |    21 |
    |     123 |    21 |
    +---------+-------+
    6 rows in set (0.00 sec)


    这里如果建了(LoginId,SubId)即可避免distinct的创建临时表,避免排序。

    mysql> explain select distinct LoginId,SubId from tb;
    +----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+
    | id | select_type | table | type  | possible_keys                | key         | key_len | ref  | rows | Extra       |
    +----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+
    |  1 | SIMPLE      | tb    | index | login_subId,login_subId_vist | login_subId | 10      | NULL |    9 | Using index |
    +----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+
    1 row in set (0.00 sec)


    所以,对于您给出的SQL,我的建议是改写SQL为:

    SELECT DISTINCT
      LoginId,
      SubId
    FROM
      T_Query
    LIMIT 61630, 10
    并添加索引( LoginId, SubId),这里一方面可以利用到索引避免临时表、排序;另一方面其实也是覆盖索引。




    如果您发现去掉ORDER BY fenshu DESC 不符合您的业务需求,那么就需要考虑一下distinct的用法是否正确? select出来的结果集是否是您真实需要的。




    另外需要提到一点:
    您发给我的这张表,索引用法有点问题,建了很多不必要的索引。
    假如建了(A),(A,B),(A,B,C)三个索引,其实(A),(A,B)都是不需要的。

    -------------------------

    Re回20楼华夏一剑的帖子
    在您给的例子中,select id, title from tb_news where title like '%mal%'; 是可以走上索引的,并且是覆盖索引。
    innodb表的二级索引上存储了主键值,上面的SQL语句只需要查询id(主键字段)和title,所以扫描二级索引字段就可以获取到结果,不要再返回主键索引读取数据了。

    mysql> explain select id, title from tb_news where title like '%mal%';
    +----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+
    | id | select_type | table   | type  | possible_keys | key           | key_len | ref  | rows | Extra                    |
    +----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+
    |  1 | SIMPLE      | tb_news | index | NULL          | tb_news_title | 203     | NULL |    5 | Using where; Using index |
    +----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+
    1 row in set (0.00 sec)


    而类似这种,select * from tb_news where title like '%mal%';会走全表扫描。

    mysql> explain select * from tb_news where title like '%mal%';
    +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
    +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | tb_news | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
    +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)


    通过覆盖索引可以获得性能上的一定优化,但是在数据量特别大,请求频繁的业务场景下不要在数据库进行模糊查询;  
    非得使用数据库的话 ,建议不要在生产库进行查询,可以在只读节点进行查询,避免查询造成主业务数据库的资源消耗完,导致故障;
    可以使用MySQL自带的全文检索,或者一些开源的搜索引擎技术,比如sphinx.  




    2015-04-28 10:27:57
    赞同 展开评论 打赏
  • ReDBA专家门诊三期:性能诊断优化
    不懂,直接点就可以了吧
    2015-04-27 19:23:42
    赞同 展开评论 打赏
  • oracle数据库技术支持服务 www.yundba.com 云库管
    有oracle的吗
    2015-04-27 10:49:19
    赞同 展开评论 打赏
  • 支持一下。。。
    2015-04-27 04:33:19
    赞同 展开评论 打赏
  • ReDBA专家门诊三期:性能诊断优化
      
    2015-04-27 01:07:52
    赞同 展开评论 打赏
  • 回5楼cyb的帖子
    利用缓存 扔内存速度飞快 前提要把缓存全部建立起来 数据量内存占用比较厉害
    2015-04-26 02:23:35
    赞同 展开评论 打赏
  • 回楼主belle.zhoux的帖子
    学习学习 。
    2015-04-25 23:57:52
    赞同 展开评论 打赏
  • -------------------------

      

    2015-04-25 11:14:31
    赞同 展开评论 打赏
  • cyb
    ReDBA专家门诊三期:性能诊断优化
    EXPLAIN  SELECT DISTINCT
      LoginId,
      SubId
    FROM
      TB
    WHERE 1
    ORDER BY Visit ASC
    LIMIT 8888, 10

    排序方式有多种,Visit 、ID、Count等,是不是有一种排序就要建一个“覆盖索引”?
    还需要单列给Visit 、ID、Count、SubId、LoginId建索引吗?

    -------------------------

    ReDBA专家门诊三期:性能诊断优化
    关于分页 limit 134557, 10优化,网上能找到的资料都是用WHERE id >或 id< 取一些范围定位,但是不实用啊,例如评分1-5分排序分页就不能用了。

    -------------------------

    回16楼俞月的帖子
    是个普通的表,不是视图,建表语句短消息方式发给你了。

    -------------------------

    回19楼俞月的帖子
    谢谢啊。历史遗留问题,我按你的建议删除再重建吧。
    2015-04-25 00:44:32
    赞同 展开评论 打赏
  • ReDBA专家门诊三期:性能诊断优化
    第一次来
    2015-04-24 13:35:52
    赞同 展开评论 打赏
  • ReDBA专家门诊三期:性能诊断优化
    闲暇之余逛论坛,竟然还有这样一篇干货,很受用,顶一个。
    2015-04-23 22:34:39
    赞同 展开评论 打赏
  • ReDBA专家门诊三期:性能诊断优化
    赞一个,期待更多的分享。
    2015-04-23 17:43:51
    赞同 展开评论 打赏
滑动查看更多
问答排行榜
最热
最新

相关电子书

更多
SQL智能诊断优化产品SQLess蚂蚁最佳实践 立即下载
阿里巴巴数据库智能优化系统探索与实践 立即下载
数据在PG中的升华 立即下载