阿里云
发表主题 回复主题
  • 20456阅读
  • 6回复

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

发帖
251
云币
250
RDS《DBA专家门诊三期-性能诊断优化》 分享已告一段落,DBA专家俞月也就帖子中用户在MySQL管理中遇到的问题进行解答,汇总如下: <&W3\/xx  
uiHlaMf  
|a^ydwb  
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建索引吗?
\k;raQR4t*  
A1(俞月)这个SQL等价于: select LoginId, SubId from TB where 1 group by LoginId, SubId ORDER BY Visit ASC LIMIT 8888, 10 Xb6@;G"  
单纯就这条SQL而言,ORDER BY Visit ASC 是没必要的,因为select选出的字段中没有Visit字段,建议添加组合索引(LoginId,SubId)。 4 Ag+  
假如SQL是 select LoginId,SubId,Visit from TB where 1 group by LoginId, SubId ORDER BY Visit ASC LIMIT 8888, 10 Gg,,qJO  
这里可以建议组合索引(LoginId,SubId) 或者(LoginId,SubId,Visit f_;3|i  
Visit字段添加到索引中,仍旧避免不了排序,上面SQL的执行过程是: T5Pc2R  
1. select LoginId,SubId,Visit from TB where 1 group by LoginId, SubId Hi.JL  
2. ORDER BY Visit ASC LIMIT 8888, 10 ~{+J~5!;<H  
第一步中的group by (或者distinct)可以利用到索引(LoginId,SubId)避免临时表,排序 w8> T ~Mv  
下面是测试案例: |L]dJ<  
mysql> show create table tb\G q+ )KY  
*************************** 1. row *************************** xbn+9b  
     Table: tb Za=<euc7  
Create Table: CREATE TABLE `tb` ( E 8,53$  
  `id` int(11) DEFAULT NULL, Z\dILt:#z  
  `LoginId` int(11) DEFAULT NULL, 17};I7  
  `SubId` int(11) DEFAULT NULL, 9';0vrFeM  
  `vist` int(11) DEFAULT NULL, O#u)~C?)8  
  KEY `idx1` (`id`), fI"`[cA"]  
  KEY `login_subId` (`LoginId`,`SubId`), '@pav>UPD  
  KEY `login_subId_vist` (`LoginId`,`SubId`,`vist`) iW |]-Ba\  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ncS^NH(&  
1 row in set (0.00 sec) M~ *E!  
C"0gAN  
(y(V,kXwa8  
mysql> explain select LoginId, SubId, vist from tb where 1 group by LoginId,SubId ; /B}]{bcp$  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+ s'/.ea V_  
| id | select_type | table | type  | possible_keys                | key         | key_len | ref  | rows | Extra | CR, Y%0vQ  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+ >@EQarD  
|  1 | SIMPLE      | tb    | index | login_subId,login_subId_vist | login_subId | 10      | NULL |    9 | NULL  | ?%;uR#4  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+ sy>Pn  
1 row in set (0.00 sec) ;B1}so1]  
k^ZUOWmU|  
%f:'A%'Qb  
第二步中将步骤1中的结果集按照 Visit 字段排序,这一步的排序不可避免。 ,ew<T{PL  
上面提到了将visit字段加入到组合索引中,是为了构建覆盖索引。避免返回到数据行去查询。 h]MSjC.X  
<u^41  
对于您提到的问题,排序方式有多种,Visit 、ID、Count等,是不是有一种排序就要建一个“覆盖索引”? *NXwllrci  
distinct/group by的优化,关键是要利用索引,避免distinct/group by时的创建临时表,排序。 HjV^6oP  
将order by的字段加入到组合索引中,目的是为了直接从二级索引字段获取到结果集,避免再去查数据行。 *23  
加了覆盖索引,索引字段变长,查询性能变好,同时也会导致占用空间、插入性能变慢。 tP3H7Yl! g  
所以是否去创建覆盖索引,还是需要依旧您的具体业务而定的。 %81tVhg  
还需要单列给Visit 、ID、Count、SubId、LoginId建索引吗? nq6]?ZJ  
不需要给单列Visit 、ID、Count、SubId、LoginId建索引,这里在order by 的字段上建立索引,还是不能避免排序。 dB_\,%vAd  
<`M Hra8  
-------------------------------------------------------------------------------------------------------------------------------------------------------- Odbjl[>k  
Q2(cyb): 关于分页 limit 134557, 10优化,网上能找到的资料都是用WHERE id >或 id< 取一些范围定位,但是不实用啊,例如评分1-5分排序分页就不能用了 CTPn'P=\C  
A2(俞月)普通limit M,N的翻页写法,往往在越往后翻页的过程中速度越慢,原因 mysql会读取表中的前M+N条数据,M越大,性能就越差: L^ +0K}eD  
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在索引中完成 =Oo*7|Z  
这种优化的根本出发点,是减少在数据页中的扫描量。 zIdQ^vm8Q  
覆盖索引,也是一种优化思路,出发点就是直接从二级索引中直接获取查询结果。 }n:-nB4  
kMI\GQW  
v $ pA Rt  
-------------------------------------------------------------------------------------------------------------------------------------------------------- RNi&OG(  
Q3(cyb): SELECT DISTINCT  *riGi  
LoginId,   SubId     FROM  T_Query   WHERE 1   ORDER BY fenshu DESC   LIMIT 61630, 10 8#kFS@  
fenshu 这一列不是主键,类似于京东商品、按评价数、按销量等不同方式排序。 ^2=Jv.2{|  
该表是基于其它表建立的查询表,总记录大约20万,这个是翻页查询SQL +<3tv&"  
A3(俞月)这条SQL从实现的功能而言,其实没必要加ORDER BY fenshu DESC 的。 L(8Q%oX%o  
distinct只能返回它的目标字段,而无法返回其它字段。 所以在SELECT DISTINCT LoginId, SubId FROM T_Query中取出的是 LoginId,SubId不重复的行。也就是说,必须LoginId和SubId都相同才会被排除。 Zr$PSp}  
做个测试: R;;)7|;~  
mysql> select * from tb; A\SbuRty  
+------+---------+-------+------+ $@K+yOq+u  
| id   | LoginId | SubId | vist | e?f[t*td  
+------+---------+-------+------+ FE_n+^|k<  
|    1 |     123 |    21 |   78 | _L: /2  
|    2 |      43 |    71 |   78 | xt3IR0  
|    3 |      43 |    21 |   78 | xQmk2S` y  
|    2 |      43 |    71 |   78 | Y %<B,3  
|    3 |      43 |    21 |   78 | :$9 4y{  
|    2 |      43 |    71 |   78 | ~gX@2!D5k  
|    5 |      73 |    21 |   78 | G1P m!CM=  
|    2 |      55 |    67 |   78 | ?=PQQx2_*u  
|    1 |      98 |    21 |   78 | KUly"B  
+------+---------+-------+------+ ?rv+ydR/q  
9 rows in set (0.01 sec) ^&cI+xZ2Y  
gzC\6ca  
SJy?^  
mysql> select distinct LoginId,SubId from tb ; - 6  
+---------+-------+ _`;6'}]s  
| LoginId | SubId | F<^,j7@  
+---------+-------+ ,:,c kul  
|      43 |    21 | sN;U,{  
|      43 |    71 | 40:YJ_n  
|      55 |    67 | %*/?k~53  
|      73 |    21 | ^K;,,s;0  
|      98 |    21 | ; 4S#6#  
|     123 |    21 | {yfG_J  
+---------+-------+   Qp-nr]  
6 rows in set (0.00 sec) 0TVO'$Gvi  
bJe^x;J9  
&[kwM3 95  
mysql> select distinct LoginId,SubId from tb where 1 order by vist; ['sIR+c%'O  
+---------+-------+ %r|fuwwJO  
| LoginId | SubId | A70_hhP  
+---------+-------+ S"}FsS;k<?  
|      43 |    21 | l~uRZLx  
|      43 |    71 | %3kqBH!d  
|      55 |    67 | K` U\+AE  
|      73 |    21 | (s"_NUj6  
|      98 |    21 | IG#=}q  
|     123 |    21 | nYE_WXY3V  
+---------+-------+ rl6vt*g  
6 rows in set (0.00 sec) wRLkO/Fw  
}TYCF@  
Gz6FwU8L  
这里如果建了(LoginId,SubId)即可避免distinct的创建临时表,避免排序。 M/B_-8B_D  
mysql> explain select distinct LoginId,SubId from tb; &5zUk++  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+ =3& WH0  
| id | select_type | table | type  | possible_keys                | key         | key_len | ref  | rows | Extra       | }F@`A?k  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+ q>H!?zi\Hy  
|  1 | SIMPLE      | tb    | index | login_subId,login_subId_vist | login_subId | 10      | NULL |    9 | Using index | 44S<(Re  
+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+ D./e|i?  
1 row in set (0.00 sec) Imv kB~8N  
4jar5Mz  
mu:Q2t^  
所以,对于您给出的SQL,我的建议是改写SQL为: 5oORwOP  
SELECT DISTINCT A03PEaZO  
LoginId, SubId b;S~`PL  
FROM =Ybbh`$<  
  T_Query FJ#V"|}  
LIMIT 61630, 10 F\>`j   
并添加索引( LoginId, SubId),这里一方面可以利用到索引避免临时表、排序;另一方面其实也是覆盖索引。 drN^-e  
_}Z*%sT  
<Z#u_:5@  
如果您发现去掉ORDER BY fenshu DESC 不符合您的业务需求,那么就需要考虑一下distinct的用法是否正确? select出来的结果集是否是您真实需要的。 I80.|KIv  
另外需要提到一点: c) 1m4SB@  
您发给我的这张表,索引用法有点问题,建了很多不必要的索引。 a eP4%h  
假如建了(A),(A,B),(A,B,C)三个索引,其实(A),(A,B)都是不需要的。 U,g!KN3P  
>.M `Fz.  
-------------------------------------------------------------------------------------------------------------------------------------------------------- T@k&YJ  
Q4(华夏一剑): mysql如何实现like '%民生%' 这样的有效索引查询。 spFsrB  
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 ,g^Bu {?  
2、同时创建索引: create index tb_news_title on tb_news(title); b_][Jye&P  
3、有10万条数据,实现按标题title的模糊查询如: select id,title from news where title like '%民生%'; (|bht0  
4、我知道: select id,title from news where title like '民生%'; 索引是有效的。 可是select id,title from news where title like '%民生%'; 索引就无效了。 1!=$3]l0Lj  
有什么好的办法解决标题title的模糊查询。 U:8^>_  
A4(俞月)在您给的例子中,select id, title from tb_news where title like '%mal%'; 是可以走上索引的,并且是覆盖索引。 &Vl,x/  
innodb表的二级索引上存储了主键值,上面的SQL语句只需要查询id(主键字段)和title,所以扫描二级索引字段就可以获取到结果,不要再返回主键索引读取数据了。 qf7.Sh  
(<1DPpy95O  
LscAsq<H<  
mysql> explain select id, title from tb_news where title like '%mal%'; Sqfa,3?L  
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+ d9sgk3K  
| id | select_type | table   | type  | possible_keys | key           | key_len | ref  | rows | Extra                    | @|5B  
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+ 3)GXu>) t  
|  1 | SIMPLE      | tb_news | index | NULL          | tb_news_title | 203     | NULL |    5 | Using where; Using index | l<v /T  
+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+ kqHh@]Z0'  
1 row in set (0.00 sec) V+D "_  
J"[OH,/_  
>) 5rOU  
而类似这种,select * from tb_news where title like '%mal%';会走全表扫描。 Kji}2j'a  
6x -PGq  
7N'F]x  
mysql> explain select * from tb_news where title like '%mal%'; )P|Ql-rE4  
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+ BH*vsxe  
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       | 8N|*n"`}  
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+ /:%^Vh3XF  
|  1 | SIMPLE      | tb_news | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where | uomFE(  
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+ :  l]>nF4  
1 row in set (0.00 sec) 0<i~XN0g  
通过覆盖索引可以获得性能上的一定优化,但是在数据量特别大,请求频繁的业务场景下不要在数据库进行模糊查询;   g"zk14'  
非得使用数据库的话 ,建议不要在生产库进行查询,可以在只读节点进行查询,避免查询造成主业务数据库的资源消耗完,导致故障; XY%8yII6  
可以使用MySQL自带的全文检索,或者一些开源的搜索引擎技术,比如sphinx. 2RM1-j ($  
/$q;-/DnTZ  
Ppx*  
GWP"i77y0s  
[ 此帖被belle.zhoux在2015-05-07 12:13重新编辑 ]
本帖最近评分记录: 1 条评分 云币 +1
xiaofanqie 云币 +1 您的帖子很精彩!希望很快能再分享您的下一帖! 2015-05-07
级别: 荣誉会员
发帖
1326
云币
32446
只看该作者 沙发  发表于: 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字段加到索引中是能避免排序的,但是要注意字段的顺序,排序字段要放在最前面 cBiv=!n  
CREATE TABLE `tb` ( :>;-uve8'  
  `id` int(11) DEFAULT NULL, 6D6=5!l  
  `LoginId` int(11) DEFAULT NULL, pu,/GBG_  
  `SubId` int(11) DEFAULT NULL, ;"wU+  
  `visit` int(11) DEFAULT NULL, -9\O$I-3  
  KEY `idx1` (`id`), +7^{T:^ht  
  KEY `login_subId` (`LoginId`,`SubId`), q$0^U{j/  
  KEY `login_subId_visit` (`visit`,`LoginId`,`SubId`) p;U[cGHC  
) ENGINE=InnoDB DEFAULT CHARSET=utf8; `yNNpSdS1  
,`B*rCOa  
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra hQ#'_%:  
'1', 'SIMPLE', 'TB', 'index', NULL, 'login_subId_visit', '15', NULL, '1', 'Using index' |C\g3N-  
级别: 新人
发帖
1
云币
1
只看该作者 6楼 发表于: 2017-03-29
发表主题 回复主题
« 返回列表上一主题下一主题

限100 字节
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容
 
验证问题: ECS是阿里云提供的什么服务? 正确答案:云服务器
上一个 下一个