| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> mysql >> ge ren jing yan zong jie :MySQL shu ju ku you hua ji qiao ji jin

ge ren jing yan zong jie :MySQL shu ju ku you hua ji qiao ji jin

2008-07-10 05:54:11 编辑來源:互联网 评论
 
 
 
本文为【个人经验总结:MySQL数据库优化技巧集锦】的拼音翻译版
  yi 、 wo men ke yi qie ying gai you hua shen me ?
  ying jian
  cao zuo xi tong / ruan jian ku
  SQL fu wu qi ( she zhi he cha xun )
  ying yong bian cheng jie kou (API)
  ying yong cheng xu
  er 、 you hua ying jian
  ru guo ni xu yao pang da de shu ju ku biao (>2G), ni ying gai kao lv shi yong 64 wei de ying jian jie gou , xiang Alpha、Sparc huo ji jiang tui chu de IA64。 yin wei MySQL nei bu shi yong da liang 64 wei de zheng shu ,64 wei de CPU jiang ti gong geng hao de xing neng 。
  dui da shu ju ku , you hua de ci xu yi ban shi RAM、 kuai su ying pan 、CPU neng li 。
  geng duo de nei cun tong guo jiang zui chang yong de jian ma ye mian cun fang zai nei cun zhong ke yi jia su jian ma de geng xin 。
  ru guo bu shi yong shi wu an quan (transaction-safe) de biao huo you da biao bing qie xiang bi mian chang wen jian jian cha , yi tai UPS jiu neng gou zai dian yuan gu zhang shi rang xi tong an quan guan bi 。
  dui yu shu ju ku cun fang zai yi ge zhuan yong fu wu qi de xi tong , ying gai kao lv 1G de yi tai wang 。 yan chi yu tun tu liang tong yang chong yao 。
  san 、 you hua ci pan
  wei xi tong 、 cheng xu he lin shi wen jian pei bei yi ge zhuan yong ci pan , ru guo que shi jin xing hen duo xiu gai gong zuo , jiang geng xin ri zhi he shi wu ri zhi fang zai zhuan yong ci pan shang 。
  di xun dao shi jian dui shu ju ku ci pan fei chang chong yao 。 dui yu da biao , ni ke yi gu ji ni jiang xu yao log( xing shu )/log( suo yin kuai chang du /3*2/( jian ma chang du + shu ju zhi zhen chang du ))+1 ci xun dao cai neng zhao dao yi xing 。 dui yu you 500000 xing de biao , suo yin Mediun int lei xing de lie , xu yao log(500000) / log(1024/3*2/(3 + 2))+1=4 ci xun dao 。 shang shu suo yin xu yao 500000*7*3/2=5.2M de kong jian 。 shi ji shang , da duo shu kuai jiang bei huan cun , suo yi da gai zhi xu yao 1-2 ci xun dao 。
  ran er dui yu xie ru ( ru shang ), ni jiang xu yao 4 ci xun dao qing qiu lai zhao dao zai na li cun fang xin jian ma , er qie yi ban yao 2 ci xun dao lai geng xin suo yin bing xie ru yi xing 。
  dui yu fei chang da de shu ju ku , ni de ying yong jiang shou dao ci pan xun dao su du de xian zhi , sui zhe shu ju liang de zeng jia cheng N log N shu ju ji di zeng 。
  jiang shu ju ku he biao fen zai bu tong de ci pan shang 。 zai MySQL zhong , ni ke yi wei ci er shi yong fu hao lian jie 。
  tiao lie ci pan (RAID 0) jiang ti gao du he xie de tun tu liang 。
  dai jing xiang de tiao lie (RAID 0+1) jiang geng an quan bing ti gao du qu de tun tu liang 。 xie ru de tun tu liang jiang you suo jiang di 。
  bu yao dui lin shi wen jian huo ke yi hen rong yi di chong jian de shu ju suo zai de ci pan shi yong jing xiang huo RAID( chu le RAID 0)。
  zai Linux shang , zai yin dao shi dui ci pan shi yong ming ling hdparm -m16 -d1 yi qi yong tong shi du xie duo ge shan qu he DMA gong neng 。 zhe ke yi jiang xiang ying shi jian ti gao 5~50%。
  zai Linux shang , yong async ( mo ren ) he noatime gua zai ci pan (mount)。
  dui yu mou xie te ding ying yong , ke yi dui mou xie te ding biao shi yong nei cun ci pan , dan tong chang bu xu yao 。
  si 、 you hua cao zuo xi tong
  bu yao jiao huan qu 。 ru guo nei cun bu zu , zeng jia geng duo de nei cun huo pei zhi ni de xi tong shi yong jiao shao nei cun 。
  bu yao shi yong NFS ci pan ( hui you NFS suo ding de wen ti )。
  zeng jia xi tong he MySQL fu wu qi de da kai wen jian shu liang 。( zai safe_mysqld jiao ben zhong jia ru ulimit -n #)。
  zeng jia xi tong de jin cheng he xian cheng shu liang 。
  ru guo ni you xiang dui jiao shao de da biao , gao su wen jian xi tong bu yao jiang wen jian da sui zai bu tong de ci dao shang (Solaris)。
  shi yong zhi chi da wen jian de wen jian xi tong (Solaris)。
  xuan ze shi yong na zhong wen jian xi tong 。 zai Linux shang de Reiserfs dui yu da kai 、 du xie dou fei chang kuai 。 wen jian jian cha zhi xu ji miao zhong 。
  wu 、 xuan ze ying yong bian cheng jie kou
  PERL
  ke zai bu tong de cao zuo xi tong he shu ju ku zhi jian yi zhi 。
  shi yi kuai su yuan xing 。
  ying gai shi yong DBI/DBD jie kou 。
  PHP
  bi PERL yi xue 。
  shi yong bi PERL shao de zi yuan 。
  tong guo sheng ji dao PHP4 ke yi huo de geng kuai de su du 。
  C
  MySQL de yuan sheng jie kou 。
  jiao kuai bing fu yu geng duo de kong zhi 。
  di ceng , suo yi bi xu fu chu geng duo 。
  C++
  jiao gao ceng ci , gei ni geng duo de shi jian lai bian xie ying yong 。
  reng zai kai fa zhong
  ODBC
  yun xing zai Windows he Unix shang 。
  ji hu ke zai bu tong de SQL fu wu qi jian yi zhi 。
  jiao man 。MyODBC zhi shi jian dan de zhi tong qu dong cheng xu , bi yong yuan sheng jie kou man 19%。
  you hen duo fang fa zuo tong yang de shi 。 hen nan xiang hen duo ODBC qu dong cheng xu na yang yun xing , zai bu tong de ling yu huan you bu tong de cuo wu 。
  wen ti cheng dui 。Microsoft ou er huan hui gai bian jie kou 。
  bu ming lang de wei lai 。(Microsoft geng tui chong OLE er fei ODBC)
  ODBC
  yun xing zai Windows he Unix shang 。
  ji hu ke zai bu tong de SQL fu wu qi jian yi zhi 。
  jiao man 。MyODBC zhi shi jian dan de zhi tong qu dong cheng xu , bi yong yuan sheng jie kou man 19%。
  you hen duo fang fa zuo tong yang de shi 。 hen nan xiang hen duo ODBC qu dong cheng xu na yang yun xing , zai bu tong de ling yu huan you bu tong de cuo wu 。
  wen ti cheng dui 。Microsoft ou er huan hui gai bian jie kou 。
  bu ming lang de wei lai 。(Microsoft geng tui chong OLE er fei ODBC)
  JDBC
  li lun shang ke zai bu tong de cao zuo xi tong he shi ju ku jian yi zhi 。
  ke yi yun xing zai web ke hu duan 。
  Python he qi ta
  ke neng bu cuo , ke wo men bu yong ta men 。
  liu 、 you hua ying yong
  ying gai ji zhong jing li jie jue wen ti 。
  zai bian xie ying yong shi , ying gai jue ding shen me shi zui chong yao de :
  su du
  cao zuo xi tong jian de ke yi zhi xing
  SQL fu wu qi jian de ke yi zhi xing
  shi yong chi xu de lian jie 。.
  huan cun ying yong zhong de shu ju yi jian shao SQL fu wu qi de fu zai 。
  bu yao cha xun ying yong zhong bu xu yao de lie 。
  bu yao shi yong SELECT * FROM table_name...
  ce shi ying yong de suo you bu fen , dan jiang da bu fen jing li fang zai zai ke neng zui huai de he li de fu zai xia de ce shi zheng ti ying yong 。 tong guo yi yi zhong mo kuai hua de fang shi jin xing , ni ying gai neng yong yi ge kuai su “ ya mo kuai ” ti dai zhao dao de ping jing , ran hou hen rong yi di biao chu xia yi ge ping jing 。
  ru guo zai yi ge pi chu li zhong jin xing da liang xiu gai , shi yong LOCK TABLES。 li ru jiang duo ge UPDATES huo DELETES ji zhong zai yi qi 。
  qi 、 ying gai shi yong ke yi zhi de ying yong
  Perl DBI/DBD
  ODBC
  JDBC
  Python( huo qi ta you pu bian SQL jie kou de yu yan )
  ni ying gai zhi shi yong cun zai yu suo you mu de SQL fu wu qi zhong huo ke yi hen rong yi di yong qi ta gou zao mo ni de SQL gou zao 。www.mysql.com shang de Crash-me ye ke yi bang zhu ni 。
  wei cao zuo xi tong /SQL fu wu qi bian xie bao zhuang cheng xu lai ti gong que shao de gong neng 。
  ba 、 ru guo ni xu yao geng kuai de su du , ni ying gai :
  zhao chu ping jing (CPU、 ci pan 、 nei cun 、SQL fu wu qi 、 cao zuo xi tong 、API huo ying yong ) bing ji zhong quan li jie jue 。
  shi yong gei yu ni geng kuai su du / ling huo xing de kuo zhan 。
  zhu jian le jie SQL fu wu qi yi bian neng wei ni de wen ti shi yong ke neng zui kuai de SQL gou zao bing bi mian ping jing 。
  you hua biao bu ju he cha xun 。
  shi yong fu zhi yi huo de geng kuai de xuan ze (select) su du 。
  ru guo ni you yi ge man su de wang luo lian jie shu ju ku , shi yong ya suo ke hu / fu wu qi xie yi 。
  bu yao hai pa shi ying yong de di yi ge ban ben bu neng wan mei di yi zhi , zai ni jie jue wen ti shi , ni zong shi ke yi zai yi hou you hua ta 。
  jiu 、 you hua MySQL
  tiao xuan bian yi qi he bian yi xuan xiang 。
  wei ni de xi tong xun zhao zui hao de qi dong xuan xiang 。
  tong du MySQL shen kao shou ce bing yue du Paul DuBios de 《MySQL》 yi shu 。( yi you zhong wen ban - yi zhu )
  duo yong EXPLAIN SELECT、SHOW VARIABLES、SHOW STATUS he SHOW PROCESSLIST。
  le jie cha xun you hua qi de gong zuo yuan li 。
  you hua biao de ge shi 。
  wei hu ni de biao (myisamchk、CHECK TABLE、 OPTIMIZE TABLE)
  shi yong MySQL de kuo zhan gong neng yi rang yi qie kuai su wan cheng 。
  ru guo ni zhu yi dao le ni jiang zai hen duo chang he xu yao mou xie han shu , bian xie MySQL UDF han shu 。
  bu yao shi yong biao ji huo lie ji de GRANT, chu fei ni que shi xu yao 。
  gou mai MySQL ji shu zhi chi yi bang zhu ni jie jue wen ti :)
  shi 、 bian yi he an zhuang MySQL
  tong guo wei ni de xi tong tiao xuan ke neng zui hao de bian yi qi , ni tong chang ke yi huo de 10-30% de xing neng ti gao 。
  zai Linux/Intel ping tai shang , yong pgcc(gcc de ben teng xin pian you hua ban ) bian yi MySQL。 ran er , er jin zhi dai ma jiang zhi neng yun xing zai Intel ben teng CPU shang 。
  dui yu yi zhong te ding de ping tai , shi yong MySQL shen kao shou ce shang tui jian de you hua xuan xiang 。
  yi ban di , dui te ding CPU de yuan sheng bian yi qi ( ru Sparc de Sun Workshop) ying gai bi gcc ti gong geng hao de xing neng , dan bu zong shi zhe yang 。
  yong ni jiang shi yong de zi fu ji bian yi MySQL。
  jing tai bian yi sheng cheng mysqld de zhi xing wen jian ( yong --with-mysqld-ldflags=all-static) bing yong strip sql/mysqld zheng li zui zhong de zhi xing wen jian 。
  zhu yi , ji ran MySQL bu shi yong C++ kuo zhan , bu dai kuo zhan zhi chi bian yi MySQL jiang ying de ju da de xing neng ti gao 。
  ru guo cao zuo xi tong zhi chi yuan sheng xian cheng , shi yong yuan sheng xian cheng ( er bu yong mit-pthreads)。
  yong MySQL ji zhun ce shi lai ce shi zui zhong de er jin zhi dai ma 。
  shi yi 、 wei hu
  ru guo ke neng , ou er yun xing yi xia OPTIMIZE table, zhe dui da liang geng xin de bian chang xing fei chang chong yao 。
  ou er yong myisamchk -a geng xin yi xia biao zhong de jian ma fen bu tong ji 。 ji zhu zai zuo zhi qian guan diao MySQL。
  ru guo you sui pian wen jian , ke neng zhi de jiang suo you wen jian fu zhi dao ling yi ge ci pan shang , qing chu yuan lai de ci pan bing kao hui wen jian 。
  ru guo yu dao wen ti , yong myisamchk huo CHECK table jian cha biao 。
  yong mysqladmin -i10 precesslist extended-status jian kong MySQL de zhuang tai 。
  yong MySQL GUI ke hu cheng xu , ni ke yi zai bu tong de chuang kou nei jian kong jin cheng lie biao he zhuang tai 。
  shi yong mysqladmin debug huo de you guan suo ding he xing neng de xin xi 。
  shi er 、 you hua SQL
  yang SQL zhi chang , qi ta shi qing jiao you ying yong qu zuo 。 shi yong SQL fu wu qi lai zuo :
  zhao chu ji yu WHERE zi ju de xing 。
  JOIN biao
  GROUP BY
  ORDER BY
  DISTINCT
  bu yao shi yong SQL lai zuo :
  jian yan shu ju ( ru ri qi )
  cheng wei yi zhi ji suan qi
  ji qiao :
  ming zhi di shi yong jian ma 。
  jian ma shi he sou suo , dan bu shi he suo yin lie de cha ru / geng xin 。
  bao chi shu ju wei shu ju ku di san fan shi , dan bu yao dan xin rong yu xin xi huo zhe ru guo ni xu yao geng kuai de su du , chuang jian zong jie biao 。
  zai da biao shang bu zuo GROUP BY, xiang fan chuang jian da biao de zong jie biao bing cha xun ta 。
  UPDATE table set count=count+1 where key_column=constant fei chang kuai 。
  dui yu da biao , huo xu zui hao ou er sheng cheng zong jie biao er bu shi yi zhi bao chi zong jie biao 。
  chong fen li yong INSERT de mo ren zhi 。
  shi san 、 bu tong SQL fu wu qi de su du cha bie ( yi miao ji )
  tong guo jian ma du qu 2000000 xing : NT Linux
  mysql 367 249
  mysql_odbc 464
  db2_odbc 1206
  informix_odbc 121126
  ms-sql_odbc 1634
  oracle_odbc 20800
  solid_odbc 877
  sybase_odbc 17614
  cha ru 350768 xing : NT Linux
  mysql 381 206
  mysql_odbc 619
  db2_odbc 3460
  informix_odbc 2692
  ms-sql_odbc 4012
  oracle_odbc 11291
  solid_odbc 1801
  sybase_odbc 4802
  
  zai shang shu ce shi zhong ,MySQL pei zhi 8M gao su huan cun yun xing , qi ta shu ju ku yi mo ren an zhuang yun xing 。
  shi si 、 chong yao de MySQL qi dong xuan xiang
  back_log ru guo xu yao da liang xin lian jie , xiu gai ta 。
  thread_cache_size ru guo xu yao da liang xin lian jie , xiu gai ta 。
  key_buffer_size suo yin ye chi , ke yi she cheng hen da 。
  bdb_cache_size BDB biao shi yong de ji lu he jian ma gao su huan cun 。
  table_cache ru guo you hen duo de biao he bing fa lian jie , xiu gai ta 。
  delay_key_write ru guo xu yao huan cun suo you jian ma xie ru , she zhi ta 。
  log_slow_queries zhao chu xu hua da liang shi jian de cha xun 。
  max_heap_table_size yong yu GROUP BY
  sort_buffer yong yu ORDER BY he GROUP BY
  myisam_sort_buffer_size yong yu REPAIR TABLE
  join_buffer_size zai jin xing wu jian ma de lian jie shi shi yong 。
  shi wu 、 you hua biao
  MySQL yong you yi tao feng fu de lei xing 。 ni ying gai dui mei yi lie chang shi shi yong zui you xiao de lei xing 。
  ANALYSE guo cheng ke yi bang zhu ni zhao dao biao de zui you lei xing :SELECT * FROM table_name PROCEDURE ANALYSE()。
  dui yu bu bao cun NULL zhi de lie shi yong NOT NULL, zhe dui ni xiang suo yin de lie you qi chong yao 。
  jiang ISAM lei xing de biao gai wei MyISAM。
  ru guo ke neng , yong gu ding de biao ge shi chuang jian biao 。
  bu yao suo yin ni bu xiang yong de dong xi 。
  li yong MySQL neng an yi ge suo yin de qian zhui jin xing cha xun de shi shi 。 ru guo ni you suo yin INDEX(a,b), ni bu xu yao zai a shang de suo yin 。
  bu zai chang CHAR/VARCHAR lie shang chuang jian suo yin , er zhi suo yin lie de yi ge qian zhui yi jie sheng cun chu kong jian 。CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
  dui mei ge biao shi yong zui you xiao de biao ge shi 。
  zai bu tong biao zhong bao cun xiang tong xin xi de lie ying gai you tong yang de ding yi bing ju you xiang tong de lie ming 。
  shi liu 、MySQL ru he ci cun chu shu ju
  shu ju ku yi mu lu cun chu 。
  biao yi wen jian cun chu 。
  lie yi bian chang huo ding chang ge shi cun chu zai wen jian zhong 。 dui BDB biao , shu ju yi ye mian xing shi cun chu 。
  zhi chi ji yu nei cun de biao 。
  shu ju ku he biao ke zai bu tong de ci pan shang yong fu hao lian jie qi lai 。
  zai Windows shang ,MySQL zhi chi yong .sym wen jian nei bu fu hao lian jie shu ju ku 。
  
  shi qi 、MySQL biao lei xing
  HEAP biao : gu ding xing chang de biao , zhi cun chu zai nei cun zhong bing yong HASH suo yin jin xing suo yin 。
  ISAM biao :MySQL 3.22 zhong de zao qi B-tree biao ge shi 。
  MyIASM:IASM biao de xin ban ben , you ru xia kuo zhan :
  er jin zhi ceng ci de ke yi zhi xing 。
  NULL lie suo yin 。
  dui bian chang xing bi ISAM biao you geng shao de sui pian 。
  zhi chi da wen jian 。
  geng hao de suo yin ya suo 。
  geng hao de jian ma tong ji fen bu 。
  geng hao he geng kuai de auto_increment chu li 。
  lai zi Sleepcat de Berkeley DB(BDB) biao : shi wu an quan ( you BEGIN WORK/COMMIT|ROLLBACK)。
  shi ba 、MySQL xing lei xing ( zhuan zhi IASM/MyIASM biao )
  ru guo suo you lie shi ding chang ge shi ( mei you VARCHAR、BLOB huo TEXT),MySQL jiang yi ding chang biao ge shi chuang jian biao , fou ze biao yi dong tai chang du ge shi chuang jian 。
  ding chang ge shi bi dong tai chang du ge shi kuai hen duo bing geng an quan 。
  dong tai chang du xing ge shi yi ban zhan yong jiao shao de cun chu kong jian , dan ru guo biao pin fan geng xin , hui chan sheng sui pian 。
  zai mou xie qing kuang xia , bu zhi de jiang suo you VARCHAR、BLOB he TEXT lie zhuan yi dao ling yi ge biao zhong , zhi shi huo de zhu biao shang de geng kuai su du 。
  li yong myiasmchk( dui ISAM,pack_iasm), ke yi chuang jian zhi du ya suo biao , zhe shi ci pan shi yong lv zui xiao , dan shi yong man su ci pan shi , zhe fei chang bu cuo 。 ya suo biao chong fen di li yong jiang bu zai geng xin de ri zhi biao
  shi jiu 、MySQL gao su huan cun ( suo you xian cheng gong xiang , yi ci xing fen pei )
  jian ma huan cun :key_buffer_size, mo ren 8M。
  biao huan cun :table_cache, mo ren 64。
  xian cheng huan cun :thread_cache_size, mo ren 0。
  zhu ji ming huan cun : ke zai bian yi shi xiu gai , mo ren 128。
  nei cun yang she biao : mu qian jin yong yu ya suo biao 。
  zhu yi :MySQL mei you xing gao su huan cun , er rang cao zuo xi tong chu li 。
  er shi 、MySQL huan cun qu bian liang ( fei gong xiang , an xu fen pei )
  sort_buffer:ORDER BY/GROUP BY
  record_buffer: sao miao biao 。
  join_buffer_size: wu jian lian jie
  myisam_sort_buffer_size:REPAIR TABLE
  net_buffer_length: dui yu du SQL yu ju bing huan cun jie guo 。
  tmp_table_size: lin shi jie guo de HEAP biao da xiao 。
  
  er shi yi 、MySQL biao gao su huan cun gong zuo yuan li
  mei ge MyISAM biao de da kai shi li (instance) shi yong yi ge suo yin wen jian he yi ge shu ju wen jian 。 ru guo biao bei liang ge xian cheng shi yong huo zai tong yi tiao cha xun zhong shi yong liang ci ,MyIASM jiang gong xiang suo yin wen jian er shi da kai shu ju wen jian de ling yi ge shi li 。
  ru guo suo you zai gao su huan cun zhong de biao dou zai shi yong , huan cun jiang lin shi zeng jia dao bi biao huan cun chi cun da xie 。 ru guo shi zhe yang , xia yi ge bei shi fang de biao jiang bei guan bi 。
  ni ke yi tong guo jian cha mysqld de Opened_tables bian liang yi jian cha biao huan cun shi fou tai xiao 。 ru guo gai zhi tai gao , ni ying gai zeng da biao gao su huan cun 。
  er shi er 、MySQL kuo zhan / you hua - ti gong geng kuai de su du
  shi yong you hua de biao lei xing (HEAP、MyIASM huo BDB biao )。
  dui shu ju shi yong you hua de lie 。
  ru guo ke neng shi yong ding chang xing 。
  shi yong bu tong de suo ding lei xing (SELECT HIGH_PRIORITY,INSERT LOW_PRIORITY)
  Auto_increment
  REPLACE (REPLACE INTO table_name VALUES (...))
  INSERT DELAYED
  LOAD DATA INFILE / LOAD_FILE()
  shi yong duo xing INSERT yi ci cha ru duo xing 。
  SELECT INTO OUTFILE
  LEFT JOIN, STRAIGHT JOIN
  LEFT JOIN , jie he IS NULL
  ORDER BY ke zai mou xie qing kuang xia shi yong jian ma 。
  ru guo zhi cha xun zai yi ge suo yin zhong de lie , jiang zhi shi yong suo yin shu jie jue cha xun 。
  lian jie yi ban bi zi cha xun kuai ( dui da duo shu SQL fu wu qi yi ru ci )。
  LIMIT
  SELECT * from table1 WHERE a > 10 LIMIT 10,20
  DELETE * from table1 WHERE a > 10 LIMIT 10
  foo IN ( chang shu lie biao ) gao du you hua 。
  GET_LOCK()/RELEASE_LOCK()
  LOCK TABLES
  INSERT he SELECT ke tong shi yun xing 。
  UDF han shu ke zhuang zai jin yi ge zheng zai yun xing de fu wu qi 。
  ya suo zhi du biao 。
  CREATE TEMPORARY TABLE
  CREATE TABLE .. SELECT
  dai RAID xuan xiang de MyIASM biao jiang wen jian fen ge cheng hen duo wen jian yi tu po mou xie wen jian xi tong de 2G xian zhi 。
  Delay_keys
  fu zhi gong neng
  er shi er 、MySQL he shi shi yong suo yin
  dui yi ge jian ma shi yong >, >=, =, <, <=, IF NULL he BETWEEN
  SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
  SELECT * FROM table_name WHERE key_part1 IS NULL;
  dang shi yong bu yi tong pei fu kai shi de LIKE
  SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
  zai jin xing lian jie shi cong ling yi ge biao zhong ti qu xing shi
  SELECT * from t1,t2 where t1.col=t2.key_part
  zhao chu zhi ding suo yin de MAX() huo MIN() zhi
  SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
  yi ge jian ma de qian zhui shi yong ORDER BY huo GROUP BY
  SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
  zai suo you yong zai cha xun zhong de lie shi jian ma de yi bu fen shi jian
  SELECT key_part3 FROM table_name WHERE key_part1=1
  er shi san 、MySQL he shi bu shi yong suo yin
  ru guo MySQL neng gu ji chu ta jiang ke neng bi sao miao zheng zhang biao huan yao kuai shi , ze bu shi yong suo yin 。 li ru ru guo key_part1 yun yun fen bu zai 1 he 100 zhi jian , xia lie cha xun zhong shi yong suo yin jiu bu shi hen hao :
  SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
  ru guo shi yong HEAP biao qie bu yong = sou suo suo you jian ma bu fen 。
  zai HEAP biao shang shi yong ORDER BY。
  ru guo bu shi yong jian ma di yi bu fen
  SELECT * FROM table_name WHERE key_part2=1
  ru guo shi yong yi yi ge tong pei fu kai shi de LIKE
  SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
  sou suo yi ge suo yin er zai ling yi ge suo yin shang zuo ORDER BY
  SELECT * from table_name WHERE key_part1 = # ORDER BY key2
  er shi si 、 xue hui shi yong EXPLAIN
  dui yu mei yi tiao ni ren wei tai man de cha xun shi yong EXPLAIN!
  mysql> explain select t3.DateOfAction, t1.TransactionID
  -> from t1 join t2 join t3
  -> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
  -> order by t3.DateOfAction, t1.TransactionID;
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
  | table | type | possible_keys | key | key_len | ref | rows | Extra |
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
  | t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort |
  | t2 | ref | ID | ID | 4 | t1.TransactionID | 13 | |
  | t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 | |
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
  ALL he fan wei lei xing ti shi yi ge qian zai de wen ti 。
  er shi wu 、 xue hui shi yong SHOW PROCESSLIST
  shi yong SHOW processlist lai fa xian zheng zai zuo shen me :
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+
  | Id | User | Host | db | Command | Time | State | Info |
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+
  | 6 | monty | localhost | bp | Query | 15 | Sending data | select * from station,station as s1 |
  | 8 | monty | localhost | | Query | 0 | | show processlist |
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+
  zai mysql huo mysqladmin zhong yong KILL lai sha si liu diao de xian cheng 。
  er shi liu 、 ru he zhi xiao MySQL jie jue yi tiao cha xun
  yun xing xiang lie ming ling bing shi tu nong ming bai qi shu chu :
  SHOW VARIABLES;
  SHOW COLUMNS FROM ...\G
  EXPLAIN SELECT ...\G
  FLUSH STATUS;
  SELECT ...;
  SHOW STATUS;
  er shi qi 、MySQL fei chang bu cuo
  ri zhi
  zai jin xing hen duo lian jie shi , lian jie fei chang kuai 。
  tong shi shi yong SELECT he INSERT de chang he 。
  zai bu ba geng xin yu hao shi tai chang de xuan ze jie he shi 。
  zai da duo shu xuan ze / geng xin shi yong wei yi jian ma shi 。
  zai shi yong mei you chang shi jian chong tu suo ding de duo ge biao shi 。
  zai yong da biao shi (MySQL shi yong yi ge fei chang jin cou de biao ge shi )。
  er shi ba 、MySQL ying bi mian de shi qing
  yong shan diao de xing geng xin huo cha ru biao , jie he yao hao shi chang de SELECT。
  zai neng fang zai WHERE zi ju zhong de lie shang yong HAVING。
  bu shi yong jian ma huo jian ma bu gou wei yi er jin xing JOIN。
  zai bu tong lie lei xing de lie shang JOIN。
  zai bu shi yong = pi pei zheng ge jian ma shi shi yong HEAP biao 。
  zai MySQL jian kong cheng xu zhong wang ji zai UPDATE huo DELETE zhong shi yong yi tiao WHERE zi ju 。 ru guo xiang zhe yang zuo , shi yong mysql ke hu cheng xu de --i-am-a-dummy xuan xiang 。
  er shi jiu 、MySQL ge zhong suo ding
  nei bu biao suo ding
  LOCK TABLES( suo you biao lei xing shi yong )
  GET LOCK()/RELEASE LOCK()
  ye mian suo ding ( dui BDB biao )
  ALTER TABLE ye zai BDB biao shang jin xing biao suo ding
  LOCK TABLES yuan xu yi ge biao you duo ge du zhe he yi ge xie zhe 。
  yi ban WHERE suo ding ju you bi READ suo ding gao de you xian ji yi bi mian rang xie ru fang gan deng 。 dui yu bu chong yao de xie ru fang , ke yi shi yong LOW_PRIORITY guan jian zi rang suo ding chu li qi you xuan du qu fang 。
  UPDATE LOW_PRIORITY SET value=10 WHERE id=10;
  
  san shi 、 gei MySQL geng duo xin xi yi geng hao di jie jue wen ti de ji qiao
  zhu yi ni zong neng qu diao ( jia zhu shi )MySQL gong neng yi shi cha xun ke yi zhi :
  SELECT /*! SQL_BUFFER_RESULTS */ ...
  SELECT SQL_BUFFER_RESULTS ...
  jiang qiang zhi MySQL sheng cheng yi ge lin shi jie guo ji 。 zhi yao suo you lin shi jie guo ji sheng cheng hou , suo you biao shang de suo ding yun bei shi fang 。 zhe neng zai yu dao biao suo ding wen ti shi huo yao hua hen chang shi jian jiang jie guo chuan gei ke hu duan shi you suo bang zhu 。
  SELECT SQL_SMALL_RESULT ... GROUP BY ...
  gao su you hua qi jie guo ji jiang zhi bao han hen shao de xing 。
  SELECT SQL_BIG_RESULT ... GROUP BY ...
  gao su you hua qi jie guo ji jiang bao han hen duo xing 。
  SELECT STRAIGHT_JOIN ...
  qiang zhi you hua qi yi chu xian zai FROM zi ju zhong de ci xu lian jie biao 。
  SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
  qiang zhi MySQL shi yong / hu lue lie chu de suo yin 。
  san shi yi 、 shi wu de li zi
  MyIASM biao ru he jin xing shi wu chu li :
  mysql> LOCK TABLES trans READ, customer WRITE;
  mysql> select sum(value) from trans where customer_id=some_id;
  mysql> update customer set total_value=sum_from_previous_statement
  where customer_id=some_id;
  mysql> UNLOCK TABLES;
  BDB biao ru he jin xing shi wu :
  mysql> BEGIN WORK;
  mysql> select sum(value) from trans where customer_id=some_id;
  mysql> update customer set total_value=sum_from_previous_statement
  where customer_id=some_id;
  mysql> COMMIT;
  zhu yi ni ke yi tong guo xia lie yu ju hui bi shi wu :
  UPDATE customer SET value=value+new_value WHERE customer_id=some_id;
  san shi er 、 shi yong REPLACE de li zi
  REPLACE de gong neng ji xiang INSERT, chu le ru guo yi tiao lao ji lu zai yi ge wei yi suo yin shang ju you yu xin ji lu xiang tong de zhi , na me lao ji lu zai xin ji lu cha ru qian ze bei shan chu 。 bu shi yong
  SELECT 1 FROM t1 WHERE key=#
  IF found-row
  LOCK TABLES t1
  DELETE FROM t1 WHERE key1=#
  INSERT INTO t1 VALUES (...)
  UNLOCK TABLES t1;
  ENDIF
  er yong
  REPLACE INTO t1 VALUES (...)
  san shi san 、 yi ban ji qiao
  shi yong duan zhu jian 。 lian jie biao shi shi yong shu zi er fei zi fu chuan 。
  dang shi yong duo bu fen jian ma shi , di yi bu fen ying gai shi zui chang yong de bu fen 。
  you yi wen shi , shou xian shi yong geng duo chong fu de lie yi huo de geng hao di jian ma ya suo 。
  ru guo zai tong yi tai ji qi shang yun xing MySQL ke hu he fu wu qi , na me zai lian jie MySQL shi ze shi yong tao jie zi er bu shi TCP/IP( zhe ke yi ti gao xing neng 7.5%)。 ke zai lian jie MySQL fu wu qi shi bu zhi ding zhu ji ming huo zhu ji ming wei localhost lai zuo dao 。
  ru guo ke neng , shi yong --skip-locking( zai mou xie OS shang wei mo ren ), zhe jiang guan bi wai bu suo ding bing jiang ti gao xing neng 。
  shi yong ying yong ceng ha xi zhi er fei chang jian ma :
  SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
  col_1='constant' AND col_2='constant'
  zai wen jian zhong bao cun xu yao yi wen jian xing shi fang wen de BLOB, zai shu ju ku zhong zhi bao cun wen jian ming 。
  shan chu suo you xing bi shan chu yi da bu fen xing yao kuai 。
  ru guo SQL bu gou kuai , yan jiu yi xia fang wen shu ju de jiao di ceng jie kou 。
  san shi si 、 shi yong MySQL 3.23 de hao chu
  MyISAM: ke yi zhi de da biao ge shi
  HEAP: nei cun zhong de biao
  Berkeley DB: zhi chi shi wu de biao 。
  zhong duo ti gao de xian zhi
  dong tai zi fu ji
  geng duo de STATUS bian liang
  CHECK he REPAIR biao
  geng kuai de GROUP BY he DISTINCT
  LEFT JOIN ... IF NULL de you hua
  CREATE TABLE ... SELECT
  CREATE TEMPORARY table_name (...)
  lin shi HEAP biao dao MyISAM biao de zi dong zhuan huan
  fu zhi
  mysqlhotcopy jiao ben
  san shi wu 、 zheng zai ji ji kai fa de chong yao gong neng
  gai jin shi wu chu li
  shi bai an quan de fu zhi
  zheng wen sou suo
  duo ge biao de shan chu ( zhi hou wan cheng duo ge biao de geng xin )
  geng hao de jian ma huan cun
  yuan zi RENAME (RENAME TABLE foo as foo_old, foo_new as foo)
  cha xun gao su huan cun
  MERGE TABLES
  yi ge geng hao de GUI ke hu cheng xu【原文】【汉音对照
 
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
yi 、 wo men ke yi qie ying gai you hua shen me ? ying jian cao zuo xi tong / ruan jian ku SQL fu wu qi ( she zhi he cha xun ) ying yong bian cheng jie kou (API) ying yong cheng xu er 、 you hua ying jian ru guo ni xu yao pang da de shu ju ku biao (>2G), ni ying gai kao lv shi yong 64 wei de ying jian jie gou , xiang Alpha、Sparc huo ji jiang tui chu de IA64。 yin wei MySQL nei bu shi yong da liang 64 wei de zheng shu ,64 wei de CPU jiang ti gong geng hao de xing neng 。 dui da shu ju ku , you hua de ci xu yi ban shi RAM、 kuai su ying pan 、CPU neng li 。 geng duo de nei cun tong guo jiang zui chang yong de jian ma ye mian cun fang zai nei cun zhong ke yi jia su jian ma de geng xin 。 ru guo bu shi yong shi wu an quan (transaction-safe) de biao huo you da biao bing qie xiang bi mian chang wen jian jian cha , yi tai UPS jiu neng gou zai dian yuan gu zhang shi rang xi tong an quan guan bi 。 dui yu shu ju ku cun fang zai yi ge zhuan yong fu wu qi de xi tong , ying gai kao lv 1G de yi tai wang 。 yan chi yu tun tu liang tong yang chong yao 。 san 、 you hua ci pan wei xi tong 、 cheng xu he lin shi wen jian pei bei yi ge zhuan yong ci pan , ru guo que shi jin xing hen duo xiu gai gong zuo , jiang geng xin ri zhi he shi wu ri zhi fang zai zhuan yong ci pan shang 。 di xun dao shi jian dui shu ju ku ci pan fei chang chong yao 。 dui yu da biao , ni ke yi gu ji ni jiang xu yao log( xing shu )/log( suo yin kuai chang du /3*2/( jian ma chang du + shu ju zhi zhen chang du ))+1 ci xun dao cai neng zhao dao yi xing 。 dui yu you 500000 xing de biao , suo yin Mediun int lei xing de lie , xu yao log(500000) / log(1024/3*2/(3 + 2))+1=4 ci xun dao 。 shang shu suo yin xu yao 500000*7*3/2=5.2M de kong jian 。 shi ji shang , da duo shu kuai jiang bei huan cun , suo yi da gai zhi xu yao 1-2 ci xun dao 。 ran er dui yu xie ru ( ru shang ), ni jiang xu yao 4 ci xun dao qing qiu lai zhao dao zai na li cun fang xin jian ma , er qie yi ban yao 2 ci xun dao lai geng xin suo yin bing xie ru yi xing 。 dui yu fei chang da de shu ju ku , ni de ying yong jiang shou dao ci pan xun dao su du de xian zhi , sui zhe shu ju liang de zeng jia cheng N log N shu ju ji di zeng 。 jiang shu ju ku he biao fen zai bu tong de ci pan shang 。 zai MySQL zhong , ni ke yi wei ci er shi yong fu hao lian jie 。 tiao lie ci pan (RAID 0) jiang ti gao du he xie de tun tu liang 。 dai jing xiang de tiao lie (RAID 0+1) jiang geng an quan bing ti gao du qu de tun tu liang 。 xie ru de tun tu liang jiang you suo jiang di 。 bu yao dui lin shi wen jian huo ke yi hen rong yi di chong jian de shu ju suo zai de ci pan shi yong jing xiang huo RAID( chu le RAID 0)。 zai Linux shang , zai yin dao shi dui ci pan shi yong ming ling hdparm -m16 -d1 yi qi yong tong shi du xie duo ge shan qu he DMA gong neng 。 zhe ke yi jiang xiang ying shi jian ti gao 5~50%。 zai Linux shang , yong async ( mo ren ) he noatime gua zai ci pan (mount)。 dui yu mou xie te ding ying yong , ke yi dui mou xie te ding biao shi yong nei cun ci pan , dan tong chang bu xu yao 。 si 、 you hua cao zuo xi tong bu yao jiao huan qu 。 ru guo nei cun bu zu , zeng jia geng duo de nei cun huo pei zhi ni de xi tong shi yong jiao shao nei cun 。 bu yao shi yong NFS ci pan ( hui you NFS suo ding de wen ti )。 zeng jia xi tong he MySQL fu wu qi de da kai wen jian shu liang 。( zai safe_mysqld jiao ben zhong jia ru ulimit -n #)。 zeng jia xi tong de jin cheng he xian cheng shu liang 。 ru guo ni you xiang dui jiao shao de da biao , gao su wen jian xi tong bu yao jiang wen jian da sui zai bu tong de ci dao shang (Solaris)。 shi yong zhi chi da wen jian de wen jian xi tong (Solaris)。 xuan ze shi yong na zhong wen jian xi tong 。 zai Linux shang de Reiserfs dui yu da kai 、 du xie dou fei chang kuai 。 wen jian jian cha zhi xu ji miao zhong 。 wu 、 xuan ze ying yong bian cheng jie kou PERL ke zai bu tong de cao zuo xi tong he shu ju ku zhi jian yi zhi 。 shi yi kuai su yuan xing 。 ying gai shi yong DBI/DBD jie kou 。 PHP bi PERL yi xue 。 shi yong bi PERL shao de zi yuan 。 tong guo sheng ji dao PHP4 ke yi huo de geng kuai de su du 。 C MySQL de yuan sheng jie kou 。 jiao kuai bing fu yu geng duo de kong zhi 。 di ceng , suo yi bi xu fu chu geng duo 。 C++ jiao gao ceng ci , gei ni geng duo de shi jian lai bian xie ying yong 。 reng zai kai fa zhong ODBC yun xing zai Windows he Unix shang 。 ji hu ke zai bu tong de SQL fu wu qi jian yi zhi 。 jiao man 。MyODBC zhi shi jian dan de zhi tong qu dong cheng xu , bi yong yuan sheng jie kou man 19%。 you hen duo fang fa zuo tong yang de shi 。 hen nan xiang hen duo ODBC qu dong cheng xu na yang yun xing , zai bu tong de ling yu huan you bu tong de cuo wu 。 wen ti cheng dui 。Microsoft ou er huan hui gai bian jie kou 。 bu ming lang de wei lai 。(Microsoft geng tui chong OLE er fei ODBC) ODBC yun xing zai Windows he Unix shang 。 ji hu ke zai bu tong de SQL fu wu qi jian yi zhi 。 jiao man 。MyODBC zhi shi jian dan de zhi tong qu dong cheng xu , bi yong yuan sheng jie kou man 19%。 you hen duo fang fa zuo tong yang de shi 。 hen nan xiang hen duo ODBC qu dong cheng xu na yang yun xing , zai bu tong de ling yu huan you bu tong de cuo wu 。 wen ti cheng dui 。Microsoft ou er huan hui gai bian jie kou 。 bu ming lang de wei lai 。(Microsoft geng tui chong OLE er fei ODBC) JDBC li lun shang ke zai bu tong de cao zuo xi tong he shi ju ku jian yi zhi 。 ke yi yun xing zai web ke hu duan 。 Python he qi ta ke neng bu cuo , ke wo men bu yong ta men 。 liu 、 you hua ying yong ying gai ji zhong jing li jie jue wen ti 。 zai bian xie ying yong shi , ying gai jue ding shen me shi zui chong yao de : su du cao zuo xi tong jian de ke yi zhi xing SQL fu wu qi jian de ke yi zhi xing shi yong chi xu de lian jie 。. huan cun ying yong zhong de shu ju yi jian shao SQL fu wu qi de fu zai 。 bu yao cha xun ying yong zhong bu xu yao de lie 。 bu yao shi yong SELECT * FROM table_name... ce shi ying yong de suo you bu fen , dan jiang da bu fen jing li fang zai zai ke neng zui huai de he li de fu zai xia de ce shi zheng ti ying yong 。 tong guo yi yi zhong mo kuai hua de fang shi jin xing , ni ying gai neng yong yi ge kuai su “ ya mo kuai ” ti dai zhao dao de ping jing , ran hou hen rong yi di biao chu xia yi ge ping jing 。 ru guo zai yi ge pi chu li zhong jin xing da liang xiu gai , shi yong LOCK TABLES。 li ru jiang duo ge UPDATES huo DELETES ji zhong zai yi qi 。 qi 、 ying gai shi yong ke yi zhi de ying yong Perl DBI/DBD ODBC JDBC Python( huo qi ta you pu bian SQL jie kou de yu yan ) ni ying gai zhi shi yong cun zai yu suo you mu de SQL fu wu qi zhong huo ke yi hen rong yi di yong qi ta gou zao mo ni de SQL gou zao 。[url]www.mysql.com shang de Crash-me ye ke yi bang zhu ni 。[/url] wei cao zuo xi tong /SQL fu wu qi bian xie bao zhuang cheng xu lai ti gong que shao de gong neng 。 ba 、 ru guo ni xu yao geng kuai de su du , ni ying gai : zhao chu ping jing (CPU、 ci pan 、 nei cun 、SQL fu wu qi 、 cao zuo xi tong 、API huo ying yong ) bing ji zhong quan li jie jue 。 shi yong gei yu ni geng kuai su du / ling huo xing de kuo zhan 。 zhu jian le jie SQL fu wu qi yi bian neng wei ni de wen ti shi yong ke neng zui kuai de SQL gou zao bing bi mian ping jing 。 you hua biao bu ju he cha xun 。 shi yong fu zhi yi huo de geng kuai de xuan ze (select) su du 。 ru guo ni you yi ge man su de wang luo lian jie shu ju ku , shi yong ya suo ke hu / fu wu qi xie yi 。 bu yao hai pa shi ying yong de di yi ge ban ben bu neng wan mei di yi zhi , zai ni jie jue wen ti shi , ni zong shi ke yi zai yi hou you hua ta 。 jiu 、 you hua MySQL tiao xuan bian yi qi he bian yi xuan xiang 。 wei ni de xi tong xun zhao zui hao de qi dong xuan xiang 。 tong du MySQL shen kao shou ce bing yue du Paul DuBios de 《MySQL》 yi shu 。( yi you zhong wen ban - yi zhu ) duo yong EXPLAIN SELECT、SHOW VARIABLES、SHOW STATUS he SHOW PROCESSLIST。 le jie cha xun you hua qi de gong zuo yuan li 。 you hua biao de ge shi 。 wei hu ni de biao (myisamchk、CHECK TABLE、 OPTIMIZE TABLE) shi yong MySQL de kuo zhan gong neng yi rang yi qie kuai su wan cheng 。 ru guo ni zhu yi dao le ni jiang zai hen duo chang he xu yao mou xie han shu , bian xie MySQL UDF han shu 。 bu yao shi yong biao ji huo lie ji de GRANT, chu fei ni que shi xu yao 。 gou mai MySQL ji shu zhi chi yi bang zhu ni jie jue wen ti :) shi 、 bian yi he an zhuang MySQL tong guo wei ni de xi tong tiao xuan ke neng zui hao de bian yi qi , ni tong chang ke yi huo de 10-30% de xing neng ti gao 。 zai Linux/Intel ping tai shang , yong pgcc(gcc de ben teng xin pian you hua ban ) bian yi MySQL。 ran er , er jin zhi dai ma jiang zhi neng yun xing zai Intel ben teng CPU shang 。 dui yu yi zhong te ding de ping tai , shi yong MySQL shen kao shou ce shang tui jian de you hua xuan xiang 。 yi ban di , dui te ding CPU de yuan sheng bian yi qi ( ru Sparc de Sun Workshop) ying gai bi gcc ti gong geng hao de xing neng , dan bu zong shi zhe yang 。 yong ni jiang shi yong de zi fu ji bian yi MySQL。 jing tai bian yi sheng cheng mysqld de zhi xing wen jian ( yong --with-mysqld-ldflags=all-static) bing yong strip sql/mysqld zheng li zui zhong de zhi xing wen jian 。 zhu yi , ji ran MySQL bu shi yong C++ kuo zhan , bu dai kuo zhan zhi chi bian yi MySQL jiang ying de ju da de xing neng ti gao 。 ru guo cao zuo xi tong zhi chi yuan sheng xian cheng , shi yong yuan sheng xian cheng ( er bu yong mit-pthreads)。 yong MySQL ji zhun ce shi lai ce shi zui zhong de er jin zhi dai ma 。 shi yi 、 wei hu ru guo ke neng , ou er yun xing yi xia OPTIMIZE table, zhe dui da liang geng xin de bian chang xing fei chang chong yao 。 ou er yong myisamchk -a geng xin yi xia biao zhong de jian ma fen bu tong ji 。 ji zhu zai zuo zhi qian guan diao MySQL。 ru guo you sui pian wen jian , ke neng zhi de jiang suo you wen jian fu zhi dao ling yi ge ci pan shang , qing chu yuan lai de ci pan bing kao hui wen jian 。 ru guo yu dao wen ti , yong myisamchk huo CHECK table jian cha biao 。 yong mysqladmin -i10 precesslist extended-status jian kong MySQL de zhuang tai 。 yong MySQL GUI ke hu cheng xu , ni ke yi zai bu tong de chuang kou nei jian kong jin cheng lie biao he zhuang tai 。 shi yong mysqladmin debug huo de you guan suo ding he xing neng de xin xi 。 shi er 、 you hua SQL yang SQL zhi chang , qi ta shi qing jiao you ying yong qu zuo 。 shi yong SQL fu wu qi lai zuo : zhao chu ji yu WHERE zi ju de xing 。 JOIN biao GROUP BY ORDER BY DISTINCT bu yao shi yong SQL lai zuo : jian yan shu ju ( ru ri qi ) cheng wei yi zhi ji suan qi ji qiao : ming zhi di shi yong jian ma 。 jian ma shi he sou suo , dan bu shi he suo yin lie de cha ru / geng xin 。 bao chi shu ju wei shu ju ku di san fan shi , dan bu yao dan xin rong yu xin xi huo zhe ru guo ni xu yao geng kuai de su du , chuang jian zong jie biao 。 zai da biao shang bu zuo GROUP BY, xiang fan chuang jian da biao de zong jie biao bing cha xun ta 。 UPDATE table set count=count+1 where key_column=constant fei chang kuai 。 dui yu da biao , huo xu zui hao ou er sheng cheng zong jie biao er bu shi yi zhi bao chi zong jie biao 。 chong fen li yong INSERT de mo ren zhi 。 shi san 、 bu tong SQL fu wu qi de su du cha bie ( yi miao ji )   tong guo jian ma du qu 2000000 xing : NT Linux mysql 367 249 mysql_odbc 464   db2_odbc 1206   informix_odbc 121126   ms-sql_odbc 1634   oracle_odbc 20800   solid_odbc 877   sybase_odbc 17614     cha ru 350768 xing : NT Linux mysql 381 206 mysql_odbc 619   db2_odbc 3460   informix_odbc 2692   ms-sql_odbc 4012   oracle_odbc 11291   solid_odbc 1801   sybase_odbc 4802   zai shang shu ce shi zhong ,MySQL pei zhi 8M gao su huan cun yun xing , qi ta shu ju ku yi mo ren an zhuang yun xing 。 shi si 、 chong yao de MySQL qi dong xuan xiang back_log ru guo xu yao da liang xin lian jie , xiu gai ta 。 thread_cache_size ru guo xu yao da liang xin lian jie , xiu gai ta 。 key_buffer_size suo yin ye chi , ke yi she cheng hen da 。 bdb_cache_size BDB biao shi yong de ji lu he jian ma gao su huan cun 。 table_cache ru guo you hen duo de biao he bing fa lian jie , xiu gai ta 。 delay_key_write ru guo xu yao huan cun suo you jian ma xie ru , she zhi ta 。 log_slow_queries zhao chu xu hua da liang shi jian de cha xun 。 max_heap_table_size yong yu GROUP BY sort_buffer yong yu ORDER BY he GROUP BY myisam_sort_buffer_size yong yu REPAIR TABLE join_buffer_size zai jin xing wu jian ma de lian jie shi shi yong 。 shi wu 、 you hua biao MySQL yong you yi tao feng fu de lei xing 。 ni ying gai dui mei yi lie chang shi shi yong zui you xiao de lei xing 。 ANALYSE guo cheng ke yi bang zhu ni zhao dao biao de zui you lei xing :SELECT * FROM table_name PROCEDURE ANALYSE()。 dui yu bu bao cun NULL zhi de lie shi yong NOT NULL, zhe dui ni xiang suo yin de lie you qi chong yao 。 jiang ISAM lei xing de biao gai wei MyISAM。 ru guo ke neng , yong gu ding de biao ge shi chuang jian biao 。 bu yao suo yin ni bu xiang yong de dong xi 。 li yong MySQL neng an yi ge suo yin de qian zhui jin xing cha xun de shi shi 。 ru guo ni you suo yin INDEX(a,b), ni bu xu yao zai a shang de suo yin 。 bu zai chang CHAR/VARCHAR lie shang chuang jian suo yin , er zhi suo yin lie de yi ge qian zhui yi jie sheng cun chu kong jian 。CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10))) dui mei ge biao shi yong zui you xiao de biao ge shi 。 zai bu tong biao zhong bao cun xiang tong xin xi de lie ying gai you tong yang de ding yi bing ju you xiang tong de lie ming 。 shi liu 、MySQL ru he ci cun chu shu ju shu ju ku yi mu lu cun chu 。 biao yi wen jian cun chu 。 lie yi bian chang huo ding chang ge shi cun chu zai wen jian zhong 。 dui BDB biao , shu ju yi ye mian xing shi cun chu 。 zhi chi ji yu nei cun de biao 。 shu ju ku he biao ke zai bu tong de ci pan shang yong fu hao lian jie qi lai 。 zai Windows shang ,MySQL zhi chi yong .sym wen jian nei bu fu hao lian jie shu ju ku 。 shi qi 、MySQL biao lei xing HEAP biao : gu ding xing chang de biao , zhi cun chu zai nei cun zhong bing yong HASH suo yin jin xing suo yin 。 ISAM biao :MySQL 3.22 zhong de zao qi B-tree biao ge shi 。 MyIASM:IASM biao de xin ban ben , you ru xia kuo zhan : er jin zhi ceng ci de ke yi zhi xing 。 NULL lie suo yin 。 dui bian chang xing bi ISAM biao you geng shao de sui pian 。 zhi chi da wen jian 。 geng hao de suo yin ya suo 。 geng hao de jian ma tong ji fen bu 。 geng hao he geng kuai de auto_increment chu li 。 lai zi Sleepcat de Berkeley DB(BDB) biao : shi wu an quan ( you BEGIN WORK/COMMIT|ROLLBACK)。 shi ba 、MySQL xing lei xing ( zhuan zhi IASM/MyIASM biao ) ru guo suo you lie shi ding chang ge shi ( mei you VARCHAR、BLOB huo TEXT),MySQL jiang yi ding chang biao ge shi chuang jian biao , fou ze biao yi dong tai chang du ge shi chuang jian 。 ding chang ge shi bi dong tai chang du ge shi kuai hen duo bing geng an quan 。 dong tai chang du xing ge shi yi ban zhan yong jiao shao de cun chu kong jian , dan ru guo biao pin fan geng xin , hui chan sheng sui pian 。 zai mou xie qing kuang xia , bu zhi de jiang suo you VARCHAR、BLOB he TEXT lie zhuan yi dao ling yi ge biao zhong , zhi shi huo de zhu biao shang de geng kuai su du 。 li yong myiasmchk( dui ISAM,pack_iasm), ke yi chuang jian zhi du ya suo biao , zhe shi ci pan shi yong lv zui xiao , dan shi yong man su ci pan shi , zhe fei chang bu cuo 。 ya suo biao chong fen di li yong jiang bu zai geng xin de ri zhi biao shi jiu 、MySQL gao su huan cun ( suo you xian cheng gong xiang , yi ci xing fen pei ) jian ma huan cun :key_buffer_size, mo ren 8M。 biao huan cun :table_cache, mo ren 64。 xian cheng huan cun :thread_cache_size, mo ren 0。 zhu ji ming huan cun : ke zai bian yi shi xiu gai , mo ren 128。 nei cun yang she biao : mu qian jin yong yu ya suo biao 。 zhu yi :MySQL mei you xing gao su huan cun , er rang cao zuo xi tong chu li 。 er shi 、MySQL huan cun qu bian liang ( fei gong xiang , an xu fen pei ) sort_buffer:ORDER BY/GROUP BY record_buffer: sao miao biao 。 join_buffer_size: wu jian lian jie myisam_sort_buffer_size:REPAIR TABLE net_buffer_length: dui yu du SQL yu ju bing huan cun jie guo 。 tmp_table_size: lin shi jie guo de HEAP biao da xiao 。   er shi yi 、MySQL biao gao su huan cun gong zuo yuan li mei ge MyISAM biao de da kai shi li (instance) shi yong yi ge suo yin wen jian he yi ge shu ju wen jian 。 ru guo biao bei liang ge xian cheng shi yong huo zai tong yi tiao cha xun zhong shi yong liang ci ,MyIASM jiang gong xiang suo yin wen jian er shi da kai shu ju wen jian de ling yi ge shi li 。 ru guo suo you zai gao su huan cun zhong de biao dou zai shi yong , huan cun jiang lin shi zeng jia dao bi biao huan cun chi cun da xie 。 ru guo shi zhe yang , xia yi ge bei shi fang de biao jiang bei guan bi 。 ni ke yi tong guo jian cha mysqld de Opened_tables bian liang yi jian cha biao huan cun shi fou tai xiao 。 ru guo gai zhi tai gao , ni ying gai zeng da biao gao su huan cun 。   er shi er 、MySQL kuo zhan / you hua - ti gong geng kuai de su du shi yong you hua de biao lei xing (HEAP、MyIASM huo BDB biao )。 dui shu ju shi yong you hua de lie 。 ru guo ke neng shi yong ding chang xing 。 shi yong bu tong de suo ding lei xing (SELECT HIGH_PRIORITY,INSERT LOW_PRIORITY) Auto_increment REPLACE (REPLACE INTO table_name VALUES (...)) INSERT DELAYED LOAD DATA INFILE / LOAD_FILE() shi yong duo xing INSERT yi ci cha ru duo xing 。 SELECT INTO OUTFILE LEFT JOIN, STRAIGHT JOIN LEFT JOIN , jie he IS NULL ORDER BY ke zai mou xie qing kuang xia shi yong jian ma 。 ru guo zhi cha xun zai yi ge suo yin zhong de lie , jiang zhi shi yong suo yin shu jie jue cha xun 。 lian jie yi ban bi zi cha xun kuai ( dui da duo shu SQL fu wu qi yi ru ci )。 LIMIT SELECT * from table1 WHERE a > 10 LIMIT 10,20 DELETE * from table1 WHERE a > 10 LIMIT 10 foo IN ( chang shu lie biao ) gao du you hua 。 GET_LOCK()/RELEASE_LOCK() LOCK TABLES INSERT he SELECT ke tong shi yun xing 。 UDF han shu ke zhuang zai jin yi ge zheng zai yun xing de fu wu qi 。 ya suo zhi du biao 。 CREATE TEMPORARY TABLE CREATE TABLE .. SELECT dai RAID xuan xiang de MyIASM biao jiang wen jian fen ge cheng hen duo wen jian yi tu po mou xie wen jian xi tong de 2G xian zhi 。 Delay_keys fu zhi gong neng er shi er 、MySQL he shi shi yong suo yin dui yi ge jian ma shi yong >, >=, =, <, <=, IF NULL he BETWEEN SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5; SELECT * FROM table_name WHERE key_part1 IS NULL; dang shi yong bu yi tong pei fu kai shi de LIKE SELECT * FROM table_name WHERE key_part1 LIKE 'jani%' zai jin xing lian jie shi cong ling yi ge biao zhong ti qu xing shi SELECT * from t1,t2 where t1.col=t2.key_part zhao chu zhi ding suo yin de MAX() huo MIN() zhi SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10 yi ge jian ma de qian zhui shi yong ORDER BY huo GROUP BY SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3 zai suo you yong zai cha xun zhong de lie shi jian ma de yi bu fen shi jian SELECT key_part3 FROM table_name WHERE key_part1=1 er shi san 、MySQL he shi bu shi yong suo yin ru guo MySQL neng gu ji chu ta jiang ke neng bi sao miao zheng zhang biao huan yao kuai shi , ze bu shi yong suo yin 。 li ru ru guo key_part1 yun yun fen bu zai 1 he 100 zhi jian , xia lie cha xun zhong shi yong suo yin jiu bu shi hen hao : SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90 ru guo shi yong HEAP biao qie bu yong = sou suo suo you jian ma bu fen 。 zai HEAP biao shang shi yong ORDER BY。 ru guo bu shi yong jian ma di yi bu fen SELECT * FROM table_name WHERE key_part2=1 ru guo shi yong yi yi ge tong pei fu kai shi de LIKE SELECT * FROM table_name WHERE key_part1 LIKE '%jani%' sou suo yi ge suo yin er zai ling yi ge suo yin shang zuo ORDER BY SELECT * from table_name WHERE key_part1 = # ORDER BY key2 er shi si 、 xue hui shi yong EXPLAIN dui yu mei yi tiao ni ren wei tai man de cha xun shi yong EXPLAIN! mysql> explain select t3.DateOfAction, t1.TransactionID -> from t1 join t2 join t3 -> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID -> order by t3.DateOfAction, t1.TransactionID; +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+ | t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort | | t2 | ref | ID | ID | 4 | t1.TransactionID | 13 | | | t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 | | +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+ ALL he fan wei lei xing ti shi yi ge qian zai de wen ti 。 er shi wu 、 xue hui shi yong SHOW PROCESSLIST shi yong SHOW processlist lai fa xian zheng zai zuo shen me : +----+-------+-----------+----+---------+------+--------------+-------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------+-----------+----+---------+------+--------------+-------------------------------------+ | 6 | monty | localhost | bp | Query | 15 | Sending data | select * from station,station as s1 | | 8 | monty | localhost | | Query | 0 | | show processlist | +----+-------+-----------+----+---------+------+--------------+-------------------------------------+ zai mysql huo mysqladmin zhong yong KILL lai sha si liu diao de xian cheng 。 er shi liu 、 ru he zhi xiao MySQL jie jue yi tiao cha xun yun xing xiang lie ming ling bing shi tu nong ming bai qi shu chu : SHOW VARIABLES; SHOW COLUMNS FROM ...\G EXPLAIN SELECT ...\G FLUSH STATUS; SELECT ...; SHOW STATUS; er shi qi 、MySQL fei chang bu cuo ri zhi zai jin xing hen duo lian jie shi , lian jie fei chang kuai 。 tong shi shi yong SELECT he INSERT de chang he 。 zai bu ba geng xin yu hao shi tai chang de xuan ze jie he shi 。 zai da duo shu xuan ze / geng xin shi yong wei yi jian ma shi 。 zai shi yong mei you chang shi jian chong tu suo ding de duo ge biao shi 。 zai yong da biao shi (MySQL shi yong yi ge fei chang jin cou de biao ge shi )。 er shi ba 、MySQL ying bi mian de shi qing yong shan diao de xing geng xin huo cha ru biao , jie he yao hao shi chang de SELECT。 zai neng fang zai WHERE zi ju zhong de lie shang yong HAVING。 bu shi yong jian ma huo jian ma bu gou wei yi er jin xing JOIN。 zai bu tong lie lei xing de lie shang JOIN。 zai bu shi yong = pi pei zheng ge jian ma shi shi yong HEAP biao 。 zai MySQL jian kong cheng xu zhong wang ji zai UPDATE huo DELETE zhong shi yong yi tiao WHERE zi ju 。 ru guo xiang zhe yang zuo , shi yong mysql ke hu cheng xu de --i-am-a-dummy xuan xiang 。 er shi jiu 、MySQL ge zhong suo ding nei bu biao suo ding LOCK TABLES( suo you biao lei xing shi yong ) GET LOCK()/RELEASE LOCK() ye mian suo ding ( dui BDB biao ) ALTER TABLE ye zai BDB biao shang jin xing biao suo ding LOCK TABLES yuan xu yi ge biao you duo ge du zhe he yi ge xie zhe 。 yi ban WHERE suo ding ju you bi READ suo ding gao de you xian ji yi bi mian rang xie ru fang gan deng 。 dui yu bu chong yao de xie ru fang , ke yi shi yong LOW_PRIORITY guan jian zi rang suo ding chu li qi you xuan du qu fang 。 UPDATE LOW_PRIORITY SET value=10 WHERE id=10; san shi 、 gei MySQL geng duo xin xi yi geng hao di jie jue wen ti de ji qiao zhu yi ni zong neng qu diao ( jia zhu shi )MySQL gong neng yi shi cha xun ke yi zhi : SELECT /*! SQL_BUFFER_RESULTS */ ... SELECT SQL_BUFFER_RESULTS ... jiang qiang zhi MySQL sheng cheng yi ge lin shi jie guo ji 。 zhi yao suo you lin shi jie guo ji sheng cheng hou , suo you biao shang de suo ding yun bei shi fang 。 zhe neng zai yu dao biao suo ding wen ti shi huo yao hua hen chang shi jian jiang jie guo chuan gei ke hu duan shi you suo bang zhu 。 SELECT SQL_SMALL_RESULT ... GROUP BY ... gao su you hua qi jie guo ji jiang zhi bao han hen shao de xing 。 SELECT SQL_BIG_RESULT ... GROUP BY ... gao su you hua qi jie guo ji jiang bao han hen duo xing 。 SELECT STRAIGHT_JOIN ... qiang zhi you hua qi yi chu xian zai FROM zi ju zhong de ci xu lian jie biao 。 SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2 qiang zhi MySQL shi yong / hu lue lie chu de suo yin 。 san shi yi 、 shi wu de li zi MyIASM biao ru he jin xing shi wu chu li : mysql> LOCK TABLES trans READ, customer WRITE; mysql> select sum(value) from trans where customer_id=some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> UNLOCK TABLES; BDB biao ru he jin xing shi wu : mysql> BEGIN WORK; mysql> select sum(value) from trans where customer_id=some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> COMMIT; zhu yi ni ke yi tong guo xia lie yu ju hui bi shi wu : UPDATE customer SET value=value+new_value WHERE customer_id=some_id; san shi er 、 shi yong REPLACE de li zi REPLACE de gong neng ji xiang INSERT, chu le ru guo yi tiao lao ji lu zai yi ge wei yi suo yin shang ju you yu xin ji lu xiang tong de zhi , na me lao ji lu zai xin ji lu cha ru qian ze bei shan chu 。 bu shi yong SELECT 1 FROM t1 WHERE key=# IF found-row LOCK TABLES t1 DELETE FROM t1 WHERE key1=# INSERT INTO t1 VALUES (...) UNLOCK TABLES t1; ENDIF er yong REPLACE INTO t1 VALUES (...) san shi san 、 yi ban ji qiao shi yong duan zhu jian 。 lian jie biao shi shi yong shu zi er fei zi fu chuan 。 dang shi yong duo bu fen jian ma shi , di yi bu fen ying gai shi zui chang yong de bu fen 。 you yi wen shi , shou xian shi yong geng duo chong fu de lie yi huo de geng hao di jian ma ya suo 。 ru guo zai tong yi tai ji qi shang yun xing MySQL ke hu he fu wu qi , na me zai lian jie MySQL shi ze shi yong tao jie zi er bu shi TCP/IP( zhe ke yi ti gao xing neng 7.5%)。 ke zai lian jie MySQL fu wu qi shi bu zhi ding zhu ji ming huo zhu ji ming wei localhost lai zuo dao 。 ru guo ke neng , shi yong --skip-locking( zai mou xie OS shang wei mo ren ), zhe jiang guan bi wai bu suo ding bing jiang ti gao xing neng 。 shi yong ying yong ceng ha xi zhi er fei chang jian ma : SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1='constant' AND col_2='constant' zai wen jian zhong bao cun xu yao yi wen jian xing shi fang wen de BLOB, zai shu ju ku zhong zhi bao cun wen jian ming 。 shan chu suo you xing bi shan chu yi da bu fen xing yao kuai 。 ru guo SQL bu gou kuai , yan jiu yi xia fang wen shu ju de jiao di ceng jie kou 。 san shi si 、 shi yong MySQL 3.23 de hao chu MyISAM: ke yi zhi de da biao ge shi HEAP: nei cun zhong de biao Berkeley DB: zhi chi shi wu de biao 。 zhong duo ti gao de xian zhi dong tai zi fu ji geng duo de STATUS bian liang CHECK he REPAIR biao geng kuai de GROUP BY he DISTINCT LEFT JOIN ... IF NULL de you hua CREATE TABLE ... SELECT CREATE TEMPORARY table_name (...) lin shi HEAP biao dao MyISAM biao de zi dong zhuan huan fu zhi mysqlhotcopy jiao ben san shi wu 、 zheng zai ji ji kai fa de chong yao gong neng gai jin shi wu chu li shi bai an quan de fu zhi zheng wen sou suo duo ge biao de shan chu ( zhi hou wan cheng duo ge biao de geng xin ) geng hao de jian ma huan cun yuan zi RENAME (RENAME TABLE foo as foo_old, foo_new as foo) cha xun gao su huan cun MERGE TABLES yi ge geng hao de GUI ke hu cheng xu
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
华丽的模特拍摄(8)
华丽的模特拍摄(7)
华丽的模特拍摄(6)
华丽的模特拍摄(5)
八里沟
朝阳宫
天下峨眉 云上金顶
老家(四)
 
>>返回首页<<
 
 
 为你推荐
 
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
 
©2005- 王朝网络 版权所有