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 );
写回答
-
叶正盛，花名斗佛，2010年加入阿里巴巴，目前是阿里巴巴集团数据产品负责人，数据库技术爱好者。个人比较关注数据库、web、java、云计算、大数据、产品设计等领域。ReDBA专家门诊三期：性能诊断优化DBA的俞月 妹子很给力啊，佩服2015-05-05 16:44:59赞同 展开评论 打赏 -
-
2015-05-05 10:34:29赞同 展开评论 打赏 -
-
-
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赞同 展开评论 打赏 -
-
数据存储与数据库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赞同 展开评论 打赏 -
-
oracle数据库技术支持服务 www.yundba.com 云库管有oracle的吗2015-04-27 10:49:19赞同 展开评论 打赏 -
-
-
-
-
-
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赞同 展开评论 打赏 -
-
-
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关文章
相关电子书
更多
