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

[活动/公告]DBA专家门诊一期:索引与sql优化

级别: 荣誉会员
发帖
1326
云币
32448
— 本帖被 xiaofanqie 执行置顶操作(2014-12-19) —
亲爱的云友, <aaT,J8%[  
       11月份的RDS产品经理访谈推出后,大家反应非常火爆,很多云友都提了数据运维方面的问题。为了更好更多帮助大家,RDS正式推出数据库运维系列专题:DBA专家门诊。我们邀请阿里集团资深数据库专家坐诊RDS版块,与广大RDS用户和数据库用户进行深度交流、学习。 3p HI+a  
3D@3jyo:  
DBA专家门诊一期:索引与sql优化 (:E@kpK  
做过数据库管理的同学都知道,sql性能很大程度上决定着数据库系统的工作能力,很多业务可能会因为sql写的不好,缺失索引等导致数据库系统性能低下,甚至导致故障发生,我们本期的门诊专题就是索引与sql优化。 4Lk<5Ho  
Z~94<*LEp  
无论你是否使用RDS,只要你有相关的问题和疑惑都可跟帖提问,主任医师将会针对云友的问题和大家交流学习。门诊结束后,所有提问的朋友,每人都将获赠1本《凌云》杂志  第四期,赶快留下您的问题吧! ~V?z!3r-)  
提问时间:即日起——12月23日15:00 1|G\&T   
答疑时间:12月23日15:00-17:00  _fn7-&6  
提问范围:索引与sql优化 *mj=kJ7(  
 ^ b5+A6?  
本期主任医师介绍: IOxtuR  
本期的DBA专家门诊主任医师 玄惭,阿里云集团资深DBA,2010年加入阿里巴巴集团DBA团队,负责淘宝,天猫等数据库系统的运维工作,精通MySQL,MSSQL,Oracle,对数据库运维,调优具有丰富的经验,2012年开始负责阿里云RDS产品运维至今,对RDS数据库系统具有很深刻的了解。 \5<Z[#{  
d#d&CJAfr  
后期我们的门诊也会开设其它专题,有兴趣的朋友请持续关注我们的门诊专题 。 fsEQ4xN'  
p]y.N)a  
凌云杂志: G,<d;:  
级别: 架构狮
发帖
1438
云币
1888

只看该作者 沙发  发表于: 2014-12-16
围观
级别: 研究猿
发帖
3357
云币
4596

只看该作者 板凳  发表于: 2014-12-16
索引我一般都是只有主键,这玩意儿,是不是越少越好
发帖
80
云币
73
只看该作者 地板  发表于: 2014-12-17
回2楼啊里新人的帖子
在日常的业务开发中,常见使用到索引的地方大概有两类: gT$WG$^i  
第一类.做业务约束需求,比如需要保证表中每行的单个字段或者某几个组合字段是唯一的,则可以在表中创建唯一索引; d v[.u{#tP  
比如:需要保证test表中插入user_id字段的值不能出现重复,则在设计表的时候,就可以在表中user_id字段上创建一个唯一索引: %aK[Yvo6  
CREATE TABLE `test` ( RMT9tXe*5  
  `id` int(11) NOT NULL AUTO_INCREMENT, Y<VX.S2kf  
  `user_id` int(11) NOT NULL, m?B=?;B9#  
  `gmt_create` datetime DEFAULT NULL, :.XlAQR~b  
  PRIMARY KEY (`id`), wzwv>@}  
  UNIQUE KEY `uk_userid` (`user_id`) y'odn ;  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; m[2[9 bQ0  
vnQFq  
第二类.提高SQL语句执行速度,可以根据SQL语句的查询条件在表中创建合适的索引,以此来提升SQL语句的执行速度; lqL5V"2Y  
此过程好比是去图书找一本书,最慢的方法就是从图书馆的每一层楼每一个书架一本本的找过去;快捷一点的方法就是先通过图书检索来确认这一本书在几楼那个书架上,然后直接去找就可以了;当然创建这个索引也需要有一定的代价,需要存储空间来存放,需要在数据行插入,更新,删除的时候维护索引: H+Bon=$cE!  
例如: 85Yi2+8f4  
\:1$E[3v  
CREATE TABLE `test_record` ( .u#Hg'oP  
  `id` int(11) NOT NULL AUTO_INCREMENT, ciml:"nQ  
  `user_id` int(11) NOT NULL, U= f9b]Y  
  `gmt_create` datetime DEFAULT NULL, XdV>6<gf{  
  PRIMARY KEY (`id`) mkyYs[  
) ENGINE=InnoDB AUTO_INCREMENT=5635996 DEFAULT CHARSET=utf8 S$fS|N3]%  
该表有500w的记录,我需要查询20:00后插入的记录有多少条记录:  XA;PWl5!  
f:~G)  
mysql> select count(*) from test_record where gmt_create>'2014-12-17 20:00:00'; T3 ie-G@<  
+----------+ ';Nc;9  
| count(*) | hN=kU9@knC  
+----------+ zNKB'hsK  
|        1 | {[Ri:^nHgL  
+----------+ b' M"To@  
1 row in set (1.31 sec) OQ_< Vxz  
可以看到查询耗费了1.31秒返回了1行记录,如果我们在gmt_create字段上添加索引: *#}=>, v  
`V V >AA5  
mysql> alter table test_record add index ind_gmt_create(gmt_create); (VN'1a (  
Query OK, 0 rows affected (21.87 sec) Ar/P%$Zfq  
Records: 0  Duplicates: 0  Warnings: 0 7i xG{yu  
mysql> select count(*) from test_record where gmt_create>'2014-12-17 20:00:00'; sB *dv06b0  
+----------+ J0R{|]W8  
| count(*) | )hO%W|  
+----------+ *lLCH,  
|        1 | B\rY\  
+----------+ e> 9X  
1 row in set (0.01 sec) kn>qX{W  
查询只消耗了0.01秒中就返回了记录. ]Bf1p  
总的来说,为SQL语句(select,update,delete)创建必要的索引是必须的,这样虽然有一定的性能和空间消耗,但是是值得,尤其是在大并发的请求下,大量的数据被扫描造成系统IO和CPU资源消耗完,进而导致整个数据库不可服务.
本帖最近评分记录: 1 条评分 云币 +1
rds-pd 云币 +1 玄惭大师看到大家的问题已经安奈不住寂寞了,大家赶紧提问啊。 2014-12-18
RDS DBA
级别: 攻城狮
发帖
688
云币
532
只看该作者 4楼 发表于: 2014-12-17
级别: 菜鸟
发帖
54
云币
133

只看该作者 5楼 发表于: 2014-12-18
ReDBA专家门诊一期:索引与sql优化
我想问个问题,怎么学好数据库,如果简单的sql,我会写。但是复杂了,就不知道怎么写了。其实也算是知道怎么写,但是就是理不清楚。不知道怎么优化。写出来也不知道是不是对的,对性能有没有什么影响之内的。还有就是,数据库的约束相关的比如外键之内的是使用数据库管理好还是程序控制好点。
发帖
318
云币
386
只看该作者 6楼 发表于: 2014-12-18
玄惭大师看到大家的问题已经安奈不住寂寞了,大家赶紧提问啊。
级别: 小白
发帖
17
云币
54
只看该作者 7楼 发表于: 2014-12-18
ReDBA专家门诊一期:索引与sql优化
上期的奖品还没发呢吧
级别: 荣誉会员
发帖
1326
云币
32448
只看该作者 8楼 发表于: 2014-12-18
回 7楼(bdplang) 的帖子
亲,还没收到你的联系地址和电话,无法发出奖品啊。 `XbV*{7  
请将你的联系电话、地址、姓名通过短消息的方式发给我。
级别: 分析狮
发帖
1559
云币
5022
只看该作者 9楼 发表于: 2014-12-19
我有一个问题想问问,现在在做一个与图书有关的项目,其中有一个功能是按图书书名搜索相似图书列表,问题不难,但是想优化一下,有如下问题想请教一下: R&s\h"=*  
1、在图书数据库数据表的书名字段里,按图书书名进行关键字搜索,如何快速搜索相关的图书? H9T'{R*FC  
      现在由于数据不多,直接用的like模糊查找验证功能而已; rc]`PV  
IqmoWn3  
2、如何按匹配的关键度进行快速排序?比如搜索“算法”,有一本书是《算法》,另一本书是《算法设计》,要求前者排在更前面。 I)X33X,  
     现在的排序是根据数据表中的主键序号id进行的排序,没有达到想要的效果。 x AR9* <-  
VSa#X |z  
谢谢了~~~~ ^_^
级别: 小白
发帖
1
云币
1
只看该作者 10楼 发表于: 2014-12-19
ReDBA专家门诊一期:索引与sql优化
请教一下有关模糊查询的优化,有没有什么比较成熟的好的策略?
级别: 程序猿
发帖
257
云币
1277

只看该作者 11楼 发表于: 2014-12-19
难得大师出现。我想问下,sql优化一般从那几个方面入手?多表之间的连接方式:Nested Loops,Hash Join 和 Sort Merge Join,是不是Hash Join最优连接?
发帖
80
云币
73
只看该作者 12楼 发表于: 2014-12-19
怎么学好数据库是一个比较大题目,数据库不仅仅是写SQL那么简单,即使知道了SQL怎么写,还需要很清楚的知道这条SQL他大概扫描了多少数据,返回多少数据,是否需要创建索引。 "hbCP4  
至于SQL优化是一个比较专业的技术活,但是可以通过学习是可以掌握的,你可以把一条sql从执行不出来优化到瞬间完成执行,这个过程的成就感是信心满满的。 p.ks jD  
学习的方法可以有以下一些过程: wni^qs.i@3  
1、自己查资料,包括书本,在线文档,google,别人的总结等等,试图自己解决 A)5;ae  
2、多做实验,证明自己的想法以及判断 (i1q".  
3、如果实在不行,再去论坛问,或者问朋友 b_&KL_vo{|  
4、如果问题解决了,把该问题的整个解决方法记录下来,以备后来的需要 6)h~9iK  
5、多关注别人的问题,或许以后自己就遇到了,并总是试图去多帮助别人 >53Hqzm&  
6、习惯从多个方面去考虑问题,并且养成良好的总结习惯
H#+2l?D:"  
VE $Kdo^  
M669G;w(K  
下面是一些国内顶级数据库专家学习数据库的经验分享给大家: 3+4U?~^k*  
.gJv})Vi  
http://www.eygle.com/archives/2005/08/ecinieoracleouo.html SR$?pJh D%  
g9.y`o}c  
其实学习任何东西都是一样,没有太多的捷径可走,必须打好了坚实的基础,才有可以在进一步学习中得到快速提高。 M/abd 7q  
王国维在他的《人间词话》中曾经概括了为学的三种境界,我在这里套用一下: = j!nt8]8  
古今之成大事业、大学问者,罔不经过三种之境界。 kZK1{  
"昨夜西风凋碧树。独上高楼,望尽天涯路。"此第一境界也。 Jwtt&" c0.  
"衣带渐宽终不悔,为伊消得人憔悴。"此第二境界也。 "Q.KBX v/  
"众里寻他千百度,蓦然回首,那人却在灯火阑珊处。"此第三境界也。
"<$JU@P  
学习Oracle,这也是你必须经历的三种境界。 ;39~G T  
第一层境界是说,学习的路是漫漫的,你必须做好充分的思想准备,如果半途而废还不如不要开始。 "MPr'3  
这里,注意一个"尽"字,在开始学习的过程中,你必须充分阅读Oracle的基础文档,概念手册、管理手册、备份恢复手册等(这些你都可以在http://tahiti.oracle.com 上找到);OCP认证的教材也值得仔细阅读。打好基础之后你才具备了进一步提升的能力,万丈高楼都是由地而起。 -%_vb6u  
第二层境界是说,尽管经历挫折、打击、灰心、沮丧,也都要坚持不放弃,具备了基础知识之后,你可以对自己感兴趣或者工作中遇到的问题进行深入的思考,由浅入深从来都不是轻而易举的,甚至很多时候你会感到自己停滞不前了,但是不要动摇,学习及理解上的突破也需要时间。 !<UdG+iV  
第三次境界是说,经历了那么多努力以后,你会发现,那苦苦思考的问题,那百思不得其解的算法原理,原来答案就在手边,你的思路豁然开朗,宛如拨云见月。这个时候,学习对你来说,不再是个难题,也许是种享受,也许成为艺术。 nkO4~p  
所以如果你想问我如何速成,那我是没有答案的。 n.b_fkZNr  
不经一番寒彻骨,哪得梅花扑鼻香。 7csMk5NU'<  
当然这三种境界在实际中也许是交叉的,在不断的学习中,不断有蓦然回首的收获。 ,ieew`  
我自己在学习的过程中,经常是采用"由点及面法"。 (,j ~s{  
当遇到一个问题后,一定是深入下去,穷究根本,这样你会发现,一个简单的问题也必定会带起一大片的知识点,如果你能对很多问题进行深入思考和研究,那么在深处,你会发现,这些面逐渐接合,慢慢的延伸到oracle的所有层面,逐渐的你就能融会贯通。这时候,你会主动的去尝试全面学习Oracle,扫除你的知识盲点,学习已经成为一种需要。 eAK=ylF;  
由实践触发的学习才最有针对性,才更能让你深入的理解书本上的知识,正所谓:"纸上得来终觉浅,绝知此事要躬行"。实践的经验于我们是至为宝贵的。 FwpTQix!  
如果说有,那么这,就是我的捷径。 w gkY \Q  
想想自己,经常是"每有所获,便欣然忘食",兴趣才是我们最好的老师。 P_F0lO  
Oracle的优化是一门学问,也是一门艺术,理解透彻了,你会知道,优化不过是在各种条件之下做出的均衡与折中。 AkEt=vI  
内存、外存;CPU、IO...对这一切你都需要有充分的认识和相当的了解,管理数据库所需要的知识并不单纯。 X?z CB  
作为一个数据库管理人员,你需要做的就是能够根据自己的知识以及经验在各种复杂情况下做出快速正确的判断。当问题出现时,你需要知道使用怎样的手段发现问题的根本;找到问题之后,你需要运用你的知识找到解决问题的方法。 ,CP&o  
这当然并不容易,举重若轻还是举轻若重,取决于你具备怎样的基础以及经验积累。 c{{RP6o/j=  
在网络上,Howard J. Rogers最近创造了一个新词组:Voodoo Tuning,用以形容那些没有及时更新自己的知识技能的所谓的Oracle技术专家。由于知识的陈旧或者理解的肤浅,他们提供的很多调整建议是错误的、容易使人误解的,甚至是荒诞的。他们提供的某些建议在有些情况下也许是正确的,如果你愿意回到Oracle5版或者6版的年代;但是这些建议在Oracle7.0,8.0 或者 Oracle8i以后往往是完全错误的。 ?hOv Y)  
后来基于类似问题触发了互联网内Oracle顶级高手的一系列深入讨论,TOM、Jonathan Lewis、HJR等人都参与其中,在我的网站上(www.eygle.com )上对这些内容及相关链接作了简要介绍,有兴趣的可以参考。 `G<|5pe  
HJR给我们提了很好的一个提示:对你所需要调整的内容,你必须具有充分的认识,否则你做出的判断就有可能是错误的。 B42qiV2/k  
这也是我想给自己和大家的一个建议:学习和研究Oracle,严谨和认真必不可少 `OHdo$Y9  
当然你还需要勤奋,我所熟悉的在Oracle领域有所成就的技术人员,他们共同的特点就是勤奋。 CP LsSv5  
如果你觉得掌握的东西没有别人多,那么也许就是因为,你不如别人勤奋。 h/NI5   
要是你觉得这一切过于复杂了,那我还有一句简单的话送给大家:不积跬步,无以至千里。学习正是在逐渐积累过程中的提高。 mc|8t0+1`  
现在Itpub给我们提供了很好的交流场所,很多问题都可以在这里找到答案,互相讨论,互相学习。这是我们的幸运,我也因此非常感谢这个网络时代。 /& o<kY  
|5(un/-C  
参考书籍:如果是一个新人可以先买一些基本的入门书籍,比如MySQL:《深入浅出MySQL——数据库开发、优化与管理维护》,在进阶一点的就是《高性能MySQL(第3版) xsRu~'f  
oracle的参考书籍:http://www.eygle.com/archives/2006/08/oracle_fundbook_recommand.html Z"d21D~h9`  
blmY=/]  
T5+ (Fz  
]DK.4\^  
L YMb)=u]  
最后建议不要在数据库中使用外键,让应用程序来保证。 n7,LfO#  
-"x25~k!?F  
DxzNg_E]  
[ 此帖被玄惭在2014-12-19 16:36重新编辑 ]
RDS DBA
发帖
80
云币
73
只看该作者 13楼 发表于: 2014-12-19
Re:回 9楼(千鸟) 的帖子
我有一个问题想问问,现在在做一个与图书有关的项目,其中有一个功能是按图书书名搜索相似图书列表,问题不难,但是想优化一下,有如下问题想请教一下: 1、在图书数据库数据表的书名字段里,按图书书名进行关键字搜索,如何快速搜索相关的图书?   现在由于数据不多,直接用的like模糊查找验证功能而已; =2yg:D  
PgYq=|]`  
V=g<3R&  
如果数据量不大,是可以在数据库中完成搜索的,可以在搜索字段上创建索引,然后进行搜索查询: p|b&hgA  
umD[4aP~;  
CREATE TABLE `book` ( BXf.^s{H  
  `book_id` int(11) NOT NULL AUTO_INCREMENT, <M4Qc12jP  
  `book_name` varchar(100) NOT NULL, `]xot8  
  ............................. ,`lVB#|  
  PRIMARY KEY (`book_id`), 9 O/l{  
  KEY `ind_name` (`book_name`) z a^s%^:yK  
) ENGINE=InnoDB mVBF2F<4  
select book.*  from book , (select book_id from book where book_name like '%算法%')  book_search_id  where book.book_id=book_search_id.book_id; CdN,R"V0$@  
但是当数据量变得很大后,就不在适合了,可以采用一些其他的第三方搜索技术比如sphinx; yb(zyGe  
!hJ% :^ xL  
dU~DlaEy(  
2、如何按匹配的关键度进行快速排序?比如搜索“算法”,有一本书是《算法》,另一本书是《算法设计》,要求前者排在更前面。 +|w-1&-  
现在的排序是根据数据表中的主键序号id进行的排序,没有达到想要的效果。 *\i<+~I@l  
kM`!'0kt  
S}rW=hO  
P|S'MS';:  
root@127.0.0.1 : test 15:57:12> select book_id,book_name from book_search where book_name like '%算%' order by book_name; G1d!a6>  
+---------+--------------+ 96.A8o  
| book_id | book_name    | esM< .  
+---------+--------------+ W1UG\d`2  
|       2 | 算法       | p?X`f#  
|       1 | 算法设计 |
RDS DBA
发帖
80
云币
73
只看该作者 14楼 发表于: 2014-12-19
回 10楼(大黑豆) 的帖子
模糊查询分为半模糊和全模糊,也就是: 8VLr*83~8  
select * from book where name like 'xxx%';(半模糊) `@{qnCNQ  
select * from book where name like '%xxx%';(全模糊) 9-+6Ed^2  
C!SB5G>OH  
半模糊可以可以使用到索引,全模糊在上面场景是不能使用到索引的,但可以进行一些改进,比如: q_8qowu"  
select book.*  from book , (select book_id from book where book_name like '%算法%')  book_search_id   BCya5!uy  
where book.book_id=book_search_id.book_id; '}c0:,5  
注意这里book_id是主键,同时在book_name上创建了索引 l_LfVON  
%@U<|9 %ua  
上面的sql语句可以利用全索引扫描来完成优化,但是性能不会太好;特别在数据量大,请求频繁的业务场景下不要在数据库进行模糊查询; (jkjj7a  
非得使用数据库的话 ,建议不要在生产库进行查询,可以在只读节点进行查询,避免查询造成主业务数据库的资源消耗完,导致故障. J4te!,  
|pr~Ohz  
可以使用一些开源的搜索引擎技术,比如sphinx. p/WEQ2   
[ 此帖被玄惭在2014-12-19 16:34重新编辑 ]
RDS DBA
发表主题 回复主题
« 返回列表
«1234»
共4页
上一主题下一主题

限100 字节
批量上传需要先选择文件,再选择上传
 
验证问题: ECS是阿里云提供的什么服务? 正确答案:云服务器
上一个 下一个
      ×
      全新阿里云开发者社区, 去探索开发者的新世界吧!
      一站式的体验,更多的精彩!
      通过下面领域大门,一起探索新的技术世界吧~ (点击图标进入)