阿里云
阿里云多端小程序中小企业获客首选
发表主题 回复主题
  • 22339阅读
  • 6回复

【干货汇总】DBA专家门诊三期

发帖
251
云币
250
RDS《DBA专家门诊三期-性能诊断优化》 分享已告一段落,DBA专家俞月也就帖子中用户在MySQL管理中遇到的问题进行解答,汇总如下: ?>_.~b ~  
F-Ea85/K@4  
J}x5Ko@  
Q1(cyb): SELECT DISTINCT  
LoginId,SubId FROM TB WHERE 1 ORDER BY Visit ASC LIMIT 8888, 10  
排序方式有多种,Visit 、ID、Count等,是不是有一种排序就要建一个“覆盖索引”? 还需要单列给Visit 、ID、Count、SubId、LoginId建索引吗?
O6pjuhMx  
A1(俞月)这个SQL等价于: select LoginId, SubId from TB where 1 group by LoginId, SubId ORDER BY Visit ASC LIMIT 8888, 10 YpL}R#  
单纯就这条SQL而言,ORDER BY Visit ASC 是没必要的,因为select选出的字段中没有Visit字段,建议添加组合索引(LoginId,SubId)。 oAL-v428  
假如SQL是 select LoginId,SubId,Visit from TB where 1 group by LoginId, SubId ORDER BY Visit ASC LIMIT 8888, 10 ,-b9:]{L  
这里可以建议组合索引(LoginId,SubId) 或者(LoginId,SubId,Visit zehF/HBzE  
Visit字段添加到索引中,仍旧避免不了排序,上面SQL的执行过程是: 7mN?;X33  
1. select LoginId,SubId,Visit from TB where 1 group by LoginId, SubId uzo}?X#  
2. ORDER BY Visit ASC LIMIT 8888, 10 jmIP c3O0  
第一步中的group by (或者distinct)可以利用到索引(LoginId,SubId)避免临时表,排序 1/m/Iw@  
下面是测试案例: pmS=$z;I  
mysql> show create table tb\G nRGH58  
*************************** 1. row *************************** >C i=H(8vN  
     Table: tb & mWq'h  
Create Table: CREATE TABLE `tb` ( K(p1+ GHC  
  `id` int(11) DEFAULT NULL, roKiSE`  
  `LoginId` int(11) DEFAULT NULL, + $-a:zx`l  
  `SubId` int(11) DEFAULT NULL, ~EN@$N^h  
  `vist` int(11) DEFAULT NULL, FKQnz/  
  KEY `idx1` (`id`), jsR1jou6  
  KEY `login_subId` (`LoginId`,`SubId`), =k_u5@.Z  
  KEY `login_subId_vist` (`LoginId`,`SubId`,`vist`) GA|q[<U  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |qr[*c3$1  
1 row in set (0.00 sec) =$'Zmb [D  
*w _j;  
6/z}-;,W'  
mysql> explain select LoginId, SubId, vist from tb where 1 group by LoginId,SubId ; ReRRFkO"2  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+ 1pn167IQL  
| id | select_type | table | type  | possible_keys                | key         | key_len | ref  | rows | Extra | rQWft r^  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+ kO.rgW82  
|  1 | SIMPLE      | tb    | index | login_subId,login_subId_vist | login_subId | 10      | NULL |    9 | NULL  |  lG{J  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+ UJZa1p@L  
1 row in set (0.00 sec) pM=vW{"I/  
2vXMrh\  
k\[2o  
第二步中将步骤1中的结果集按照 Visit 字段排序,这一步的排序不可避免。 0L6L_;o  
上面提到了将visit字段加入到组合索引中,是为了构建覆盖索引。避免返回到数据行去查询。 j7W_%Yk|E  
Fg~,1[8w<  
对于您提到的问题,排序方式有多种,Visit 、ID、Count等,是不是有一种排序就要建一个“覆盖索引”? O$$N{  
distinct/group by的优化,关键是要利用索引,避免distinct/group by时的创建临时表,排序。 oqE -q\!H  
将order by的字段加入到组合索引中,目的是为了直接从二级索引字段获取到结果集,避免再去查数据行。 lA1R$  
加了覆盖索引,索引字段变长,查询性能变好,同时也会导致占用空间、插入性能变慢。 Re2kD/S3  
所以是否去创建覆盖索引,还是需要依旧您的具体业务而定的。 j]P|iL  
还需要单列给Visit 、ID、Count、SubId、LoginId建索引吗? }}]Y mf  
不需要给单列Visit 、ID、Count、SubId、LoginId建索引,这里在order by 的字段上建立索引,还是不能避免排序。 mZ5UaSG  
ug{F?LW[  
-------------------------------------------------------------------------------------------------------------------------------------------------------- 9\;EX  
Q2(cyb): 关于分页 limit 134557, 10优化,网上能找到的资料都是用WHERE id >或 id< 取一些范围定位,但是不实用啊,例如评分1-5分排序分页就不能用了 qg|+BIi Uz  
A2(俞月)普通limit M,N的翻页写法,往往在越往后翻页的过程中速度越慢,原因 mysql会读取表中的前M+N条数据,M越大,性能就越差: ,lUo@+  
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在索引中完成 N>z8\y  
这种优化的根本出发点,是减少在数据页中的扫描量。 1Tl("XV3  
覆盖索引,也是一种优化思路,出发点就是直接从二级索引中直接获取查询结果。 HP8pEo0Y  
$~-j-0 \m  
e p^0Cd/  
-------------------------------------------------------------------------------------------------------------------------------------------------------- cD>o(#x]  
Q3(cyb): SELECT DISTINCT )CB?gW  
LoginId,   SubId     FROM  T_Query   WHERE 1   ORDER BY fenshu DESC   LIMIT 61630, 10 zb4g\H 0  
fenshu 这一列不是主键,类似于京东商品、按评价数、按销量等不同方式排序。 LY>JE6zTt  
该表是基于其它表建立的查询表,总记录大约20万,这个是翻页查询SQL \uaJw\EZ  
A3(俞月)这条SQL从实现的功能而言,其实没必要加ORDER BY fenshu DESC 的。 ff0B*0  
distinct只能返回它的目标字段,而无法返回其它字段。 所以在SELECT DISTINCT LoginId, SubId FROM T_Query中取出的是 LoginId,SubId不重复的行。也就是说,必须LoginId和SubId都相同才会被排除。 4rx|6NV6  
做个测试: J0oR]eT}  
mysql> select * from tb; +2g3%c0}  
+------+---------+-------+------+ XC$~!  
| id   | LoginId | SubId | vist | c1/x,1LnMf  
+------+---------+-------+------+ v:>P;\]r9M  
|    1 |     123 |    21 |   78 | HlO+^(eX  
|    2 |      43 |    71 |   78 | aZ^lI 6@+4  
|    3 |      43 |    21 |   78 | aJF`rLm  
|    2 |      43 |    71 |   78 | i!iODt3k  
|    3 |      43 |    21 |   78 | s6egd%r  
|    2 |      43 |    71 |   78 | 3k/Mig T  
|    5 |      73 |    21 |   78 | o]Ki+ U  
|    2 |      55 |    67 |   78 | zM'-2,  
|    1 |      98 |    21 |   78 | mvw:E_  
+------+---------+-------+------+ }u&JX  
9 rows in set (0.01 sec) L_~G`Rb3  
s,RS}ek~|  
oMPQkj;  
mysql> select distinct LoginId,SubId from tb ; phdN9<Z  
+---------+-------+  fwEi//1  
| LoginId | SubId | '3Ri/V,  
+---------+-------+ .DQ]q o]OG  
|      43 |    21 | u *< (B  
|      43 |    71 | %9lxE[/  
|      55 |    67 | q mB@kbt  
|      73 |    21 | .EcMn  
|      98 |    21 | e#('`vGB  
|     123 |    21 | [h1{{Nb#ez  
+---------+-------+   ;p9D2&  
6 rows in set (0.00 sec) yi OF&  
>W<5$.G  
1<83MO;  
mysql> select distinct LoginId,SubId from tb where 1 order by vist; m+g>s&1H  
+---------+-------+ xpM~* Gpm  
| LoginId | SubId | tTgW^&B  
+---------+-------+ `9-Zg??8r  
|      43 |    21 | <Va>5R_d<  
|      43 |    71 | `Nn?G  
|      55 |    67 | 0"`skYJ@  
|      73 |    21 | 5 %Gf?LyO  
|      98 |    21 | @-}]~|<  
|     123 |    21 | Ei-OuDM;)  
+---------+-------+ l&B'.6XKs  
6 rows in set (0.00 sec) 0h#M)Ft  
9x!y.gx  
vknFtpx  
这里如果建了(LoginId,SubId)即可避免distinct的创建临时表,避免排序。 Ox Z:5ps  
mysql> explain select distinct LoginId,SubId from tb; LnGSYrx1  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+ $}$@)!-  
| id | select_type | table | type  | possible_keys                | key         | key_len | ref  | rows | Extra       | U4gJ![>5j  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+ w C"%b#(}  
|  1 | SIMPLE      | tb    | index | login_subId,login_subId_vist | login_subId | 10      | NULL |    9 | Using index | CCOg1X_  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+ k 9rnT)YU  
1 row in set (0.00 sec) Z.pw!mu"  
=6&D4~R  
JuSS5_&  
所以,对于您给出的SQL,我的建议是改写SQL为: 3G^Ed)JvE  
SELECT DISTINCT dL(|Y{4  
LoginId, SubId -::%9D}P|  
FROM LnS >3$t*  
  T_Query +`-a*U94  
LIMIT 61630, 10 (+dRD] |T  
并添加索引( LoginId, SubId),这里一方面可以利用到索引避免临时表、排序;另一方面其实也是覆盖索引。 DT4RodE$  
"6Uj:9  
~b0qrjF;O  
如果您发现去掉ORDER BY fenshu DESC 不符合您的业务需求,那么就需要考虑一下distinct的用法是否正确? select出来的结果集是否是您真实需要的。 A#&qoZ(C  
另外需要提到一点: R"`{E,yj  
您发给我的这张表,索引用法有点问题,建了很多不必要的索引。 VAGMI+ -  
假如建了(A),(A,B),(A,B,C)三个索引,其实(A),(A,B)都是不需要的。 _`>7 Q) ,7  
<9a_wGs  
-------------------------------------------------------------------------------------------------------------------------------------------------------- XWpnZFjE  
Q4(华夏一剑): mysql如何实现like '%民生%' 这样的有效索引查询。 GK?R76d  
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 WG}CPkj  
2、同时创建索引: create index tb_news_title on tb_news(title); [[h)4H{T  
3、有10万条数据,实现按标题title的模糊查询如: select id,title from news where title like '%民生%'; QYXx7h r=$  
4、我知道: select id,title from news where title like '民生%'; 索引是有效的。 可是select id,title from news where title like '%民生%'; 索引就无效了。 8p{  
有什么好的办法解决标题title的模糊查询。 ID#qKFFW  
A4(俞月)在您给的例子中,select id, title from tb_news where title like '%mal%'; 是可以走上索引的,并且是覆盖索引。 j%jd@z ]@  
innodb表的二级索引上存储了主键值,上面的SQL语句只需要查询id(主键字段)和title,所以扫描二级索引字段就可以获取到结果,不要再返回主键索引读取数据了。 GD{fXhgk  
w*%$ lhp!  
Juqn X  
mysql> explain select id, title from tb_news where title like '%mal%'; h!av)nhM  
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+ zy6(S_j  
| id | select_type | table   | type  | possible_keys | key           | key_len | ref  | rows | Extra                    | ^@L l(?  
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+ p6*a1^lU6  
|  1 | SIMPLE      | tb_news | index | NULL          | tb_news_title | 203     | NULL |    5 | Using where; Using index | /3 Ix,7  
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+ MI'l4<>u  
1 row in set (0.00 sec) tO>OD#  
(L:Fb  
0k 0c   
而类似这种,select * from tb_news where title like '%mal%';会走全表扫描。 &9w%n  
2vdQ&H4  
vs)I pV(  
mysql> explain select * from tb_news where title like '%mal%'; iSz@E&[X  
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+ 'h6} cw+K  
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       | :CST!+)o  
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+ |;u%JW$4  
|  1 | SIMPLE      | tb_news | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where | yb{{ z@  
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+ ^x"c0R^  
1 row in set (0.00 sec) :Mu8W_  
通过覆盖索引可以获得性能上的一定优化,但是在数据量特别大,请求频繁的业务场景下不要在数据库进行模糊查询;   +bc#GzVF  
非得使用数据库的话 ,建议不要在生产库进行查询,可以在只读节点进行查询,避免查询造成主业务数据库的资源消耗完,导致故障; a$zm/  
可以使用MySQL自带的全文检索,或者一些开源的搜索引擎技术,比如sphinx. @23R joK  
kH8$nkeev  
Ik-E4pxKo  
#w,WwL!  
[ 此帖被belle.zhoux在2015-05-07 12:13重新编辑 ]
本帖最近评分记录: 1 条评分 云币 +1
xiaofanqie 云币 +1 您的帖子很精彩!希望很快能再分享您的下一帖! 2015-05-07
级别: 荣誉会员
发帖
1326
云币
32448
只看该作者 沙发  发表于: 2015-05-07
您的帖子很精彩!希望很快能再分享您的下一帖!
级别: 菜鸟
发帖
52
云币
93
只看该作者 板凳  发表于: 2015-05-09
Re【干货汇总】DBA专家门诊三期
级别: 小白
发帖
1
云币
1
只看该作者 地板  发表于: 2015-05-11
Re【干货汇总】DBA专家门诊三期
级别: 新人
发帖
4
云币
4
只看该作者 4楼 发表于: 2015-05-24
Re【干货汇总】DBA专家门诊三期
mark,好贴,受益匪浅
级别: 新人
发帖
1
云币
1
只看该作者 5楼 发表于: 2015-11-12
Re【干货汇总】DBA专家门诊三期
visit字段加到索引中是能避免排序的,但是要注意字段的顺序,排序字段要放在最前面 KN, 4@4  
CREATE TABLE `tb` ( B5`;MQJ  
  `id` int(11) DEFAULT NULL, u){S$</  
  `LoginId` int(11) DEFAULT NULL, `]{Psc6_=  
  `SubId` int(11) DEFAULT NULL, O 6]u!NqG  
  `visit` int(11) DEFAULT NULL, hBy*09Sv  
  KEY `idx1` (`id`), (&c,twa~  
  KEY `login_subId` (`LoginId`,`SubId`),  mn`5pha  
  KEY `login_subId_visit` (`visit`,`LoginId`,`SubId`) m1y `v"  
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 3'^S3W%  
3?^NN|xg  
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra #ovM(Mld  
'1', 'SIMPLE', 'TB', 'index', NULL, 'login_subId_visit', '15', NULL, '1', 'Using index' e5L 1er;6  
级别: 新人
发帖
1
云币
1
只看该作者 6楼 发表于: 2017-03-29
发表主题 回复主题
« 返回列表上一主题下一主题

限100 字节
如果您在写长篇帖子又不马上发表,建议存为草稿
 
验证问题: ECS是阿里云提供的什么服务? 正确答案:云服务器
上一个 下一个
      ×
      全新阿里云开发者社区, 去探索开发者的新世界吧!
      一站式的体验,更多的精彩!
      通过下面领域大门,一起探索新的技术世界吧~ (点击图标进入)