涓汉鎬荤粨
鑼冨紡鍚嶈瘝瑙ｉ噴
鍑芥暟渚濊禆锛氳嫢鍦ㄤ竴寮犺〃涓紝鍦ㄥ睘鎬э紙鎴栧睘鎬х粍锛塜鐨勫€肩‘瀹氱殑鎯呭喌涓嬶紝蹇呭畾鑳界‘瀹氬睘鎬鐨勫€硷紝閭ｄ箞灏卞彲浠ヨY鍑芥暟渚濊禆浜嶺锛屽啓浣?X 鈫?Y
濡傦細瀛︾敓濮撳悕鍑芥暟渚濊禆浜庡鍙凤紝鍐欎綔 瀛﹀彿 鈫?濮撳悕 浣嗘槸瀛樺湪鐩稿悓濮撳悕锛屾墍浠ュ鍙峰嚱鏁颁笉渚濊禆濮撳悕
鐮?涓€涓〃涓?鍙互鍞竴鍐冲畾涓€涓厓缁勭殑灞炴€р€滈泦鍚堚€濄€傝€屼富閿垯鏄彲浠ュ敮涓€鍐冲畾鍏冪粍鐨勨€樻煇涓睘鎬р€?
渚嬪锛氬湪鎴愮哗琛ㄤ腑锛堝鍙凤紝璇剧▼鍙凤級鍚堣捣鏉ュ彨涓€涓爜锛岃€屽垎寮€鐪嬪鍙锋槸涓婚敭锛岃绋嬪彿涔熸槸涓婚敭
闈炰富灞炴€э細涓嶅睘浜庣爜鐨勫睘鎬?
涓诲睘鎬э細灞炰簬鐮佺殑灞炴€?
鍊欓€夐敭锛氭寚姣忎釜閮戒笉涓€鏍风殑銆侀潪绌虹殑閭ｅ嚑涓睘鎬э紝鏈夌潃娼滃湪鐨勪富閿剰涔夈€? 姣斿涓€涓〃涓殑璇剧▼鍙峰鍙凤紝绯诲埆鍙风瓑绛夛紙澶氫釜鏋勬垚涓€缁勫€欓€夐敭锛夈€?/pre>
View Code
绗竴锛屼簩锛屼笁鑼冨紡瑙ｅ喅鐨勬槸闈炰富灞炴€х殑鍏崇郴銆?BC 鑼冨紡瑙ｅ喅鐨勬槸涓诲睘鎬х殑鍏崇郴锛?
绗竴鑼冨紡 1NF锛氬瓧娈?鍒?涓嶅彲鍐嶅垎锛屾暟鎹簱琛ㄤ腑鐨勬墍鏈夊瓧娈靛€奸兘鏄笉鍙垎瑙ｇ殑鍘熷瓙鍊?渚嬪 鍦板潃灏嗙渷甯傚幙瀛樺叆涓€涓瓧娈靛氨涓嶇鍚堢涓€鑼冨紡锛屾垜浠渶瑕佸皢鐪佸競鍘垮垎鍒瓨鍏ヤ笉鍚岀殑瀛楁
绗簩鑼冨紡锛氫繚璇佹瘡寮犺〃鍙〃绀轰竴浠朵簨鎯?灏辨槸瀹屽叏渚濊禆[id1,id2]->c淇濊瘉姣忓紶琛ㄥ彧琛ㄧず涓€浠朵簨鎯吢狅紝娌℃湁閮ㄥ垎渚濊禆锛涖€愰潪涓诲睘鎬т笉鑳戒緷璧栦簬涓婚敭鐨勪竴閮ㄥ垎锛岃瀹屽叏渚濊禆浜庝富閿€?渚嬪锛氫竴寮犺〃瀛樺湪瀛﹀彿锛屽鍚嶃€佽绋嬨€佹垚缁╋紝涓嶇鍚堢浜岃寖寮忥紙瀛﹀彿鍜岃绋嬬粍鎴愪簡涓€缁勫€欓€夐敭锛屾垚缁╀緷璧栫粍鍊欓€夐敭鐨勮绋嬶紝鍗翠笉渚濊禆瀛﹀彿杩欐槸鍏稿瀷鐨勯儴鍒嗕緷璧栵級
绗笁鑼冨紡锛氭病鏈変紶閫掍緷璧栥€備繚璇佹瘡鍒楅兘鍜屼富閿洿鎺ョ浉鍏炽€愰潪涓诲睘鎬т箣闂寸殑渚濊禆銆戯紙涓嶈兘鏈塧->b->c 杩欑涓€寮犺〃c渚濊禆b鑰宐鍙堜緷璧朼鐨勬儏鍐碉級
渚嬪锛氬晢鍝佸悕锛屽崟浠凤紝鏁伴噺锛屾€婚噾棰濓細聽 聽 鈥滄€婚噾棰濃€濋」鍙互閫氳繃鈥滃崟浠封€濆拰鈥滄暟閲忊€濊繍绠楀緱鍑猴紝瀛樺湪鍑芥暟渚濊禆鍏崇郴锛屼笉婊¤冻3NF锛堟湁鏃跺€欒繕鏄湁蹇呰鐨勶級
BC鑼冨紡锛?瑙ｅ喅閮ㄥ垎涓婚敭渚濊禆浜庨潪涓婚敭閮ㄥ垎銆傛瘡涓〃涓彧鏈変竴涓€欓€夐敭
聽
聽
甯哥敤鐨勫叧绯诲瀷鏁版嵁搴擄細
int(5)鍜寁achar(20)鐨勫尯鍒?鍦╥nt涓暱搴﹀彧鏄笌鏄剧ず鏈夊叧绯伙紝鑰屼笖鍦ㄥ湪闆跺～鍏呯殑鎯呭喌涓嬶細
鍦▃orefill鐨勬儏鍐典笅锛氬瓨鍌ㄧ殑鏁板瓧灏忎簬5浣嶉偅涔堜細鐢ㄩ浂濉厖锛岃秴杩囩瓑浜庝簲浣嶅垯鍘熸牱鏄剧ず
鍦╲achar涓?0鍦╩ysql5.0+鏄?0涓瓧绗︼紝浠ヤ笅鏄?0涓瓧鑺傦紙鐜板湪鍩烘湰涓嶇敤5-鐨勭増鏈簡锛?娉ㄦ剰闀垮害鍙槸鍦ㄦ帶鍒跺彴锛堝windows鐨勯粦绐楀彛鎴栬€厁shell绛夋帶鍒跺彴鏈夋晥锛岀綉椤电瓑娌℃湁鏁堟灉锛?
char 瀹氶暱 瓒呭嚭闀垮害浼氳鎴彇 閫傚悎瀛樺偍寰堢煭鐨勫瓧绗︼紝鎴栬€呮墍鏈夊€奸兘鎺ヨ繎涓€涓暱搴?闀垮害涓嶈冻娲荤敤绌烘牸濉厖
瀵逛簬缁忓父鍙樻洿鐨勬暟鎹甤har姣攙arcahr鏇村ソcahr涓嶅鏄撲骇鐢熺鐗?瀵逛簬闈炲父鐭殑鍒?char姣攙archar鍦ㄥ瓨鍌ㄧ┖闂翠笂鏇存湁鏁堢巼
鍙垎閰嶇湡姝ｉ渶瑕佺殑绌洪棿锛屾洿闀跨殑鍒椾細娑堣€楁洿澶氱殑鍐呭瓨
灏介噺閬垮厤浣跨敤blob銆乼ext绫诲瀷锛屾煡璇細浣跨敤涓存椂琛ㄥ鑷翠弗閲嶇殑鎬ц兘寮€閿€
鏃ユ湡鍜屾椂闂寸被鍨?灏介噺浣跨敤TIMESTAMP姣擠ATETIME绌洪棿鏁堢巼瑕侀珮
杩炴帴鏁版嵁搴擄細
鏈€绠€鍗?mysql -u root -p 瀵嗙爜
甯﹁繙绋嬶細 mysql -h 193.168.124.1 -P 3306 -u root -p 瀵嗙爜 -b
绐楀彛鎿嶄綔鐣岄潰鏃?
閫€鍑簃ysql锛?quit銆乪xit銆乗q
鍙栨秷鎿嶄綔锛?\c
缁撴潫锛?;鎴栬€匼g
鏁版嵁搴撴搷浣滐細
鏌ョ湅褰撳墠鏁版嵁搴撳瓧绗﹂泦: show variables like 'character_set_%';
鏌ョ湅瀛楃闆嗘帓搴忚缃細 show variable like 'collation_%';
鏌ョ湅琛ㄤ俊鎭細 show table status \G;
鏌ョ湅鏁版嵁琛ㄥ瓧绗﹂泦: show table status from 搴撳悕 like '%琛ㄥ悕%';
鏌ョ湅鍒楃殑瀛楃闆? show full columns from 琛ㄥ悕;
鏌ョ湅鏀寔鐨勫瓧绗﹂泦: show cahrset; 鎴栬€?show char set;
鏌ョ湅绯荤粺涓殑瀛楃闆? show character set;
鏌ョ湅鏍″闆? show collation [like '瀛楃涓?]
涓存椂淇敼鏈嶅姟鍣ㄧ骇鍒殑瀛楃闆? set global character_set_server=utf8;
瓒呯畝鍗曠殑璁剧疆瀛楃闆嗭細 set names gbk 浠ｆ浛浜嗚缃甤haracter_set_client,XX_results, XX_connection
淇敼琛ㄧ骇鍒瓧绗﹂泦: alert table table_name default charset utf-8;
鏌ョ湅鏁版嵁搴? show databases [like '%moodl_';];
鍒涘缓鏁版嵁搴? create database dbname [default character set utf8];
杈冨叏: create database if not exists dbname charset=gbk collate gbk_chinese_ci;
娉ㄦ剰锛氬鏋滄湰绾ф病鏈夎缃瓧绗﹂泦锛岄粯璁や负涓婁竴绾х殑瀛楃闆?濡傚湪杩欎釜鏁版嵁搴撳缓琛ㄦ病鏈夎缃瓧绗﹂泦锛岄偅涔堥粯璁ゅ氨鏄繖涓暟鎹簱鐨勫瓧绗﹂泦
鍒犻櫎鏁版嵁搴? drop database [if exists] dbname;
淇敼鏁版嵁搴擄細 alter database dbname charset=gbk collate gbk_chinese_ci;
閫夋嫨鏁版嵁搴? use dbname;
鏌ョ湅閫夋嫨鐨勬暟鎹簱: select database();
鏌ョ湅鐗堟湰: select version();
鍒涘缓鏁版嵁琛? create table [if not exists] table_name(瀛楁鍚?瀛楁绫诲瀷,瀛楁鍚?瀛楁绫诲瀷,....);
鏌ョ湅鏁版嵁琛? show tables [like '鏉′欢'];
鏌ョ湅琛ㄧ粨鏋? desc|describe table_name \G; 鎴栬€卻how columns from 搴撳悕.琛ㄥ悕
鏌ョ湅寤鸿〃璇彞: show create table table_name;
鍒犻櫎鏁版嵁琛? drop table [if exists] table_name;
淇敼琛? alter table 琛ㄥ悕 鍔ㄤ綔锛坅dd,modify,drop,change,rename as锛?淇敼鍐呭;
淇敼琛ㄧ殑瀛楁鍚嶅強绫诲瀷: alter table table_name change 鏃у瓧娈靛悕 鏂板瓧娈靛悕 瀛楁绫诲瀷 绾︽潫鏉′欢;
淇敼琛ㄧ殑瀛楁绫诲瀷: alter table 琛ㄥ悕 modify 瀛楁鍚?瀛楁绫诲瀷; change鍙互淇敼瀛楁鍚峬odify涓嶅彲浠? 娣诲姞瀛楁: alter table 琛ㄥ悕 add 鏂板瓧娈靛悕 瀛楁绫诲瀷 first|after 宸叉湁瀛楁鍚? 鍒犻櫎瀛楁: alter table 琛ㄥ悕 drop 瀛楁鍚?
淇敼琛ㄥ悕: alert table 鏃ц〃鍚?rename as 鏂拌〃鍚?
淇敼琛ㄧ殑瀛楃闆? alter table 琛ㄥ悕 default character set utf8;
娉ㄦ剰:濡傛灉鏀逛簡琛ㄧ殑瀛楃闆嗭紝瀛楁鐨勫瓧绗﹂泦鏃朵笉浼氭敼鍙樼殑锛屽繀椤绘墜鍔ㄥ彇鏇存敼锛侊紒锛?
澶嶅埗琛ㄧ粨鏋? create table 琛ㄥ悕 like 鏃ц〃鍚? 澶嶅埗琛ㄧ粨鏋勫強鏁版嵁锛?create table 琛ㄥ悕 select 瀛楁鍒楄〃鎴? from 鏃ц〃鍚?where鏉′欢 order by瀛愬彞 limit 瀛愬彞
鏌ョ湅榛樿鐨勫紩鎿庯細 show variables like 'default_storage_engine';
鏌ョ湅鎵€鏈夊紩鎿庯細 show engines;
鏁版嵁鎿嶄綔锛?鏁版嵁鎻掑叆锛?insert into table_name(filed1, filed2, ...) values(values1, values2, ...);(瀛楁鍚嶆棤闇€鍔犲紩鍙?鍊奸渶瑕?
insert into table_name set filed1=value1,filed2=value2,...;
insert into table_name values(value1,value2, ...);
insert into 琛ㄥ悕 select *|瀛楁鍒楄〃 from 鍏跺畠琛?where鏉′欢 order by 瀛楁 asc|desc limit 璧峰璁板綍锛岃褰曟暟
鏁版嵁鏌ョ湅: select filed1,filed2,... from table_name where ...;
//浠庡彞椤哄簭
select---form---where---group by---having---order by---limit
鏌ヨ鐨刧roup by having鐨勭敤娉暵?1銆乬roup by 蹇呴』閰嶅悎鑱氬悎鍑芥暟浣跨敤锛?銆佽〃涓煇涓瓧娈靛瓨鍦ㄧ浉鍚屽€肩殑鏃跺€欙紙濡倁ser琛ㄤ腑鐨勭彮绾у瓧娈碉紝閮ㄩ棬瀛楁绛夛級
SELECT *|瀛楁鍚? 鑱氬悎鍑芥暟(瀛楁鍚?
FROM 琛ㄥ悕
WHERE 瀛楁鍚?鏉′欢 鍊?GROUP BY 瀛楁鍚?
渚嬪瓙锛歶ser琛?user_no(鍛樺伐缂栧彿),dept_no(閮ㄩ棬缂栧彿)銆乫rom_time(鍏ヨ亴鏃堕棿) go_time锛堢鑱屾椂闂绰?榛樿9999-01-01琛ㄧず鍦ㄨ亴锛?
姣忎釜閮ㄩ棬鏈夊灏戝悕鍦ㄨ亴鍛樺伐
1銆佺瓫閫夊湪鑱屽憳宸?where go_time='9999-01-01';
2銆佸閮ㄩ棬杩涜鍒嗙粍group by dept_no
3銆佸鍛樺伐杩涜璁℃暟 count(user_no)
SELECT
dept_no as 閮ㄩ棬,
count( user_no) as 浜烘暟
FROM
user_dept
WHERE
go_time = '9999-01-01'
GROUP BY
dept_no
having鏄皢涓婇潰鐨勫垎缁勬煡璇㈠嚭鏉ュ湪杩涜鏋滆繃婊?
鏌ュ嚭鍛樺伐浜烘暟澶т簬30000鐨勯儴闂? departments 涓洪儴闂ㄨ〃;
SELECT
( SELECT d.dept_name FROM departments d WHERE du.dept_no = d.dept_no ) AS 閮ㄩ棬,
count( du.user_no ) AS 浜烘暟
FROM
dept_user as du
WHERE
du.go_time = '9999-01-01'
GROUP BY
du.dept_no
HAVING
count( du.use_no ) > 30000
聽
鏁版嵁鏇存敼: update table_name set filed1=value1, filed2=value2, ... where ...;
鏁版嵁鍒犻櫎: delete from table_name where ...; 涓嶅姞鏉′欢鍏ㄥ垹
鍏ㄩ儴鍒犻櫎鏁版嵁: truncate table user;
鏁版嵁鏇挎崲: replace into 琛ㄥ悕 set 瀛楁1=鍊?锛屽瓧娈?=鍊?锛屸€︹€?鍐茬獊浼氬垹闄ゆ暟鎹厧鐢紒锛?
鍏宠仈鏇存柊:
update tablea,tableb set tablea.c1 =tableb.c1,tablea.c2=tableb.c2 where tablea.id=tableb.id
update a INNER JOIN b ON a.id =b.id SET a.c1=b.c1,a.c2=b.c2 WHERE ...
渚嬪瓙锛氬皢b琛ㄤ腑鐨刟ge>50 鐨刢1 c2 鏇存柊鍒癮琛ㄤ腑瀵瑰簲鐨凜1C2聽 聽 琛ㄤ腑瀛楁锛?A锛坕d sex c1 c2锛?B(id age c1 c2)
update a,b SET a.c1=b.c1,a.c2=b.c2 WHERE a.id=b.id and b.age>50
update a JOIN b ON a.id=b.id SET a.c1=b.c1,a.c2=b.c2 where b.age >50
鍏宠仈鏌ヨ锛?浜ゅ弶杩炴帴锛坈ross join锛?
select * from a,b[,c] 鎴栬€?select * from a cross join b锛坈ross join c锛?鍐呰繛鎺ワ紙inner join 鎴栬€卝oin锛?鍙栧苟闆? 1銆佺瓑鍊艰繛鎺?select * from a inner join b on a.id=b.id
2銆佷笉绛夊€艰繛鎺?select * from a inner join b on a.id>b.id
3銆佽嚜杩炴帴 select * from a as T1 inner join a as T2 ON T1.id=T2.pid
澶栬繛鎺ワ紙left join 鍜宺ight join锛?鍙栬ˉ闆?select * from a left join b on a.id=b.id
鑱斿悎鏌ヨ锛坲nion 鍜?union all锛?union 灏辨槸鎶婂涓粨鏋滈泦鍚堥泦涓竴璧凤紝union鍓嶇殑缁撴灉涓哄熀鍑嗐€傛敞鎰忚仈鍚堟煡璇㈠垪鏁拌鐩哥瓑锛岀浉鍚岀殑鍒楁暟浼氬悎骞?
union all 涓嶄細鍚堝苟閲嶅鏁版嵁 鏁堢巼浣庝簬union
select * from a union select * from b union ...
鍏ㄨ繛鎺ワ紙full join锛?mysql涓嶆敮鎸佸叏杩炴帴 鍙互浣跨敤 left join union right join鑱斿悎浣跨敤
select * from a left join b ON a.id=b.id UNION select * from a right join b ON a.id=b.id
宓屽鏌ヨ锛堟潯浠舵煡璇?瀛愭煡璇級 select * from a where id in(select id from b) 鏁堢巼涓嶉珮
聽
鈶爉ysql浼樺寲锛堢‖浠讹紝杞欢銆侀厤缃紝鏋舵瀯锛?/h1> 鈶ysql瀛楁浼樺寲
鈶ysql绱㈠紩浼樺寲
聽 鏌ヨ璇彞浼樺寲 1銆佸畾浣嶆參鏌ヨsql锛? 鏌ヨ浼樺寲锛?鏌ユ壘鍒嗘瀽鎱㈢殑鍘熷洜銆佷紭鍖栨煡璇㈣繃绋嬩腑鐨勬暟鎹闂€佷紭鍖栭暱鍜岄毦鐨勬煡璇㈣鍙ャ€佷紭鍖栫壒瀹氱被鍨嬬殑鏌ヨ璇彞 鏌ユ壘鍒嗘瀽鎱㈢殑鍘熷洜锛?銆€銆€鈶犺褰曟參鏌ヨ鏃ュ織 闇€瑕佸紑鍚參鏌ヨ鏃ュ織 锛坰low_query_log=on锛変笂闈ysql浼樺寲杩炴帴鏈夎缁嗙殑浠嬬粛 鍒嗘瀽鏌ヨ鏃ュ織 涓嶅缓璁洿鎺ユ墦寮€鎱㈡煡璇㈡棩蹇楃洿鎺ヨ繘琛屽垎鏋愭氮璐规椂闂村拰绮惧姏鍙互浣跨敤pt-query-digest宸ュ叿杩涜鍒嗘瀽 锛坧t-query-digest 瀹夎鍙婁娇鐢?https://www.cnblogs.com/sc-xm/p/4288323.html锛? 鈶℃煡鐪媠qp绱㈠紩鐨勪娇鐢ㄦ儏鍐祍how status like 'Handler_read%';聽 聽 聽 聽#璇︽儏鍦ㄤ紭鍖杝ql绱㈠紩閲屾湁浠嬬粛聽聽 鈶娇鐢?show profile锛坰et profiling=1;寮€鍚級锛屽紑鍚悗浼氬皢鏈嶅姟鍣ㄤ笂鐨勬墽琛岀殑鎵€鏈夎鍙ヤ細妫€娴嬫秷鑰楃殑鏃堕棿锛屽瓨鍒颁复鏃惰〃涓? 姝ラ 榛戠獥鍙ｈ繘鍏ysql set profiling=1 show profiles 鐒跺悗绛塵ysql鐨勮鍙ユ墽琛岋紙杩欓噷鎴戜滑鍙互selet*from wy_area 鎵ц涓€鏉★級 鍐嶆墽琛宻how profiles 鏄剧ず濡備笅锛?mysql> show profiles; +----------+-----------+-----------------------+ | Query_ID | Duration | Query | +----------+-----------+-----------------------+ | 1 | 1.0868975 | select * from wy_area | +----------+-----------+-----------------------+ 1 row in set 鐒跺悗锛歴how profile for query 1;锛堣繖閲岀殑1鏄复鏃惰〃鐨凲uery_ID锛夋煡鐪嬪叿浣撴參鐨勫師鍥? 鈶ｄ娇鐢╯how status; 杩斿洖涓€浜涜鏁板櫒銆俿how global status 鏌ョ湅鏈嶅姟鍣ㄧ骇鍒殑鎵€鏈夎鏁?鏈夋椂鍊欏彲浠ユ洿鍏疯繖浜涜鏁扮寽鍑洪偅鍝簺鎿嶄綔浠ｄ环杈冮珮 鈶や娇鐢?show processlist 鏌ョ湅绾跨▼ 鎵惧埌鏄惁鏈夊ぇ閲忎笉鍋ュ悍鐨勭嚎绋? 鈶ヤ娇鐢╡xplain 鍒嗘瀽鍗曟潯sql璇彞 mysql> explain select * from wy_area; desc select * from wy_area;涔熶竴鏍?+----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | wy_area | ALL | NULL | NULL | NULL | NULL | 3932 | | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set mysql> 聽 2銆佷紭鍖栨楠わ細 鍏堣鏄庡浣曞畾浣嶄綆鏁堢巼鐨剆ql璇彞銆傜劧鍚庢洿鍏穝ql璇彞鍙兘浣庢晥鐨勫師鍥犲仛鎺掓煡锛? 鍏堜粠绱㈠紩鐫€鎵嬶紝濡傛灉绱㈠紩娌℃湁闂鑰冭檻浠ヤ笂鍑犱釜鏂归潰锛?鏁版嵁璁块棶闂锛?闀块毦搴︾殑鏌ヨ闂
杩樻槸涓€浜涚壒瀹氱殑浼樺寲闂
閬垮厤鏌ヨ涓嶉渶瑕佺殑璁板綍锛屼娇鐢╨imit瑙ｅ喅 閬垮厤澶氳〃鍏宠仈鏃剁敤select*杩斿洖鍏ㄩ儴鍒楋紝鎸囧畾闇€瑕佺殑鍒?閬垮厤鎬绘槸鍙栧嚭鍏ㄩ儴鍒楋紝select*浼氳浼樺寲鍣ㄦ棤娉曞畬鎴愮储寮曡鐩栨壂鎻忕殑浼樺寲 閬垮厤閲嶅鏌ヨ鐩稿悓鐨勬暟鎹紝鍙互缂撳瓨鏁版嵁锛屼笅娆＄洿鎺ヨ鍙栫紦瀛?鏄惁鎵弿棰濆鐨勮褰曘€備娇鐢╡xplain鏉ヨ繘琛屽垎鏋愩€傚鏋滃彂鐜版煡璇㈤渶瑕佹壂鎻忓ぇ閲忕殑鏁版嵁浣嗗彧杩斿洖灏戞暟鐨勮鍙互閫氳繃浠ヤ笅浼樺寲锛?1銆佷娇鐢ㄧ储寮曡鐩栨壂鎻忥紝鎶婃墍鏈夌殑鏈夌敤鐨勫垪閮芥斁鍒扮储寮曚腑锛岃繖鏍峰瓨鍌ㄥ紩鎿庝笉闇€瑕佸洖琛ㄨ幏鍙栧搴旇灏卞彲浠ヨ繑鍥炵粨鏋?2銆佺炕閬嶆暟鎹粨鏋勶紝淇敼鏁版嵁琛ㄨ寖寮?3銆侀噸鍐檚ql璇彞锛岃浼樺寲鍣ㄥ彲浠ユ洿浼樼殑鏂瑰紡杩涜鏌ヨ 浼樺寲绱㈠紩鐨勮鍒欙細 浼樺寲闀块毦鐨勮鍙ワ細 1銆佸皢涓€涓ぇ鐨勬煡璇㈠垎涓哄涓皬鐨勭浉鍚岀殑鏌ヨ锛堝垹1000涓囨潯鏁版嵁銆傚彲浠ヤ竴娆″垹闄や竴涓囨殏鍋滀竴浼氬湪鍒犱竴涓囨潯锛岄伩鍏嶉攣琛ㄨ妭鐪佸紑閿€锛?2銆佸垎瑙ｅ叧鑱旀煡璇紝鍙互灏嗕竴鏉″叧鑱旇鍙ュ垎瑙ｆ垚澶氭潯sql鏉ユ墽琛岋紙璁╃紦瀛樼殑鏁堢巼鏇撮珮銆傚崟涓煡璇㈠彲浠ュ噺灏戦攣绔炰簤锛?3銆佸ぇ閲忓鏉傜殑sql璇彞鎷嗗垎鎴愬涓皬鐨剆ql璇彞 4銆佸湪搴旂敤灞傚仛鍏宠仈鍙互鏇村鏄撳鏁版嵁搴撹繘琛屾媶鍒嗭紝鏌ヨ鏁堢巼澶у箙鎻愬崌锛岃緝灏戠殑鍐椾綑璁板綍鏌ヨ 浼樺寲鐗瑰畾绫诲瀷鐨勬煡璇㈣鍙ワ細 浼樺寲count()鏌ヨ count(*)浼氬拷鐣ユ墍鏈夌殑鍒楁瘮count(鍒楀悕)蹇?鍦∕yISAM涓病鏈墂here鏉′欢鐨刢ount(*)闈炲父蹇紝褰撴湁where鏉′欢鏃朵笉涓€瀹氭瘮鍏朵粬鐨勫紩鎿庡揩 鍙互浣跨敤explain鏌ヨ杩戜技鍊兼浛浠ount锛?锛?澧炲姞姹囨€昏〃锛屾瘡娆″鍑忔暟鎹慨鏀规眹鎬昏〃 浣跨敤缂撳瓨 浼樺寲鍏宠仈鏌ヨ锛?纭畾ON鎴栬€卽singu sing(id)绛変环浜巓n a.id=b.id 纭繚group by鍜宱rder by涓彧鏈変竴涓〃涓殑鍒楋紙閬垮厤order by b.id,a.name DESC锛夛紝杩欐牱mysql鎵嶅彲鑳戒娇鐢ㄧ储寮?浼樺寲瀛愭煡璇?灏介噺鐢ㄥ叧鑱旀煡璇㈡潵鏇夸唬瀛愭煡璇? 浼樺寲group by 鍜宒istinct 杩欎袱绉嶆煡璇㈠彲浣跨敤绱㈠紩鏉ヤ紭鍖栵紝鏄渶鏈夋晥鐨勪紭鍖栨柟娉?鍏宠仈鏌ヨ涓紝浣跨敤鏍囧織鍒楄繘琛屽垎缁勭殑鏁堢巼浼氭洿楂?濡傛灉涓嶉渶瑕乬roup by 鎺掑簭 鍙互鍔犱笂 group by null锛宮ysql涓嶅啀杩涜鏂囦欢鎺掑簭锛堝惁鍒欏彲鑳戒細锛?with rollup 瓒呯骇鑱氬悎锛屽彲浠ユ尓鍒板簲鐢ㄧ▼搴忓鐞?浼樺寲limit鍒嗛〉锛毬?limit鏁版嵁閲忓ぇ鐨勬椂鍊欐煡璇㈡晥鐜囦綆 鎴戜滑鍙互鏌ヨ鏃惰褰曚笂娆℃渶澶х殑id limit 0锛?00 where id > 涓婃鐨刬d锛堝彲鑳戒笉澶噯纭級 100涓囨暟鎹煡璇紭鍖栨湁浠嬬粛 浼樺寲union鏌ヨ 鏈塽nion all 鏇夸唬 union 鍦ㄥ簲鐢ㄥ眰闈㈠湪绛涢櫎閲嶅鐨勬暟鎹? 聽 聽 聽 聽 楂樻墿灞曞拰楂樺彲鐢?鍒嗗簱鍒嗚〃鎬荤粨 鍗曞簱鍗曡〃
鍗曞簱鍗曡〃鏄渶甯歌鐨勬暟鎹簱璁捐锛屼緥濡傦紝鏈変竴寮犵敤鎴?user)琛ㄦ斁鍦ㄦ暟鎹簱db涓紝鎵€鏈夌殑鐢ㄦ埛閮藉彲浠ュ湪db搴撲腑鐨剈ser琛ㄤ腑鏌ュ埌銆?
鍗曞簱澶氳〃
闅忕潃鐢ㄦ埛鏁伴噺鐨勫鍔狅紝user琛ㄧ殑鏁版嵁閲忎細瓒婃潵瓒婂ぇ锛屽綋鏁版嵁閲忚揪鍒颁竴瀹氱▼搴︾殑鏃跺€欏user琛ㄧ殑鏌ヨ浼氭笎娓愮殑鍙樻參锛屼粠鑰屽奖鍝嶆暣涓狣B鐨勬€ц兘銆傚鏋滀娇鐢╩ysql, 杩樻湁涓€涓洿涓ラ噸鐨勯棶棰樻槸锛屽綋闇€瑕佹坊鍔犱竴鍒楃殑鏃跺€欙紝mysql浼氶攣琛紝鏈熼棿鎵€鏈夌殑璇诲啓鎿嶄綔鍙兘绛夊緟銆?
鍙互閫氳繃鏌愮鏂瑰紡灏唘ser杩涜姘村钩鐨勫垏鍒嗭紝浜х敓涓や釜琛ㄧ粨鏋勫畬鍏ㄤ竴鏍风殑user_0000,user_0001绛夎〃锛寀ser_0000 + user_0001 + 鈥︾殑鏁版嵁鍒氬ソ鏄竴浠藉畬鏁寸殑鏁版嵁銆?
澶氬簱澶氳〃
闅忕潃鏁版嵁閲忓鍔犱篃璁稿崟鍙癉B鐨勫瓨鍌ㄧ┖闂翠笉澶燂紝闅忕潃鏌ヨ閲忕殑澧炲姞鍗曞彴鏁版嵁搴撴湇鍔″櫒宸茬粡娌″姙娉曟敮鎾戙€傝繖涓椂鍊欏彲浠ュ啀瀵规暟鎹簱杩涜姘村钩鍖哄垎銆?
鍒嗗簱鍒嗚〃瑙勫垯
璁捐琛ㄧ殑鏃跺€欓渶瑕佺‘瀹氭琛ㄦ寜鐓т粈涔堟牱鐨勮鍒欒繘琛屽垎搴撳垎琛ㄣ€備緥濡傦紝褰撴湁鏂扮敤鎴锋椂锛岀▼搴忓緱纭畾灏嗘鐢ㄦ埛淇℃伅娣诲姞鍒板摢涓〃涓紱鍚岀悊锛屽綋鐧诲綍鐨勬椂鍊欐垜浠緱閫氳繃鐢ㄦ埛鐨勮处鍙锋壘鍒版暟鎹簱涓搴旂殑璁板綍锛屾墍鏈夌殑杩欎簺閮介渶瑕佹寜鐓ф煇涓€瑙勫垯杩涜銆?
璺敱
閫氳繃鍒嗗簱鍒嗚〃瑙勫垯鏌ユ壘鍒板搴旂殑琛ㄥ拰搴撶殑杩囩▼銆傚鍒嗗簱鍒嗚〃鐨勮鍒欐槸user_id mod 4鐨勬柟寮忥紝褰撶敤鎴锋柊娉ㄥ唽浜嗕竴涓处鍙凤紝璐﹀彿id鐨?23,鎴戜滑鍙互閫氳繃id mod 4鐨勬柟寮忕‘瀹氭璐﹀彿搴旇淇濆瓨鍒癠ser_0003琛ㄤ腑銆傚綋鐢ㄦ埛123鐧诲綍鐨勬椂鍊欙紝鎴戜滑閫氳繃123 mod 4鍚庣‘瀹氳褰曞湪User_0003涓€?
鍒嗗簱鍒嗚〃浜х敓鐨勯棶棰橈紝鍙婃敞鎰忎簨椤?
1. 鍒嗗簱鍒嗚〃缁村害鐨勯棶棰?
鍋囧鐢ㄦ埛璐拱浜嗗晢鍝?闇€瑕佸皢浜ゆ槗璁板綍淇濆瓨鍙栨潵锛屽鏋滄寜鐓х敤鎴风殑绾害鍒嗚〃锛屽垯姣忎釜鐢ㄦ埛鐨勪氦鏄撹褰曢兘淇濆瓨鍦ㄥ悓涓€琛ㄤ腑锛屾墍浠ュ緢蹇緢鏂逛究鐨勬煡鎵惧埌鏌愮敤鎴风殑璐拱鎯呭喌锛屼絾鏄煇鍟嗗搧琚喘涔扮殑鎯呭喌鍒欏緢鏈夊彲鑳藉垎甯冨湪澶氬紶琛ㄤ腑锛屾煡鎵捐捣鏉ユ瘮杈冮夯鐑︺€傚弽涔嬶紝鎸夌収鍟嗗搧缁村害鍒嗚〃锛屽彲浠ュ緢鏂逛究鐨勬煡鎵惧埌姝ゅ晢鍝佺殑璐拱鎯呭喌锛屼絾瑕佹煡鎵惧埌涔颁汉鐨勪氦鏄撹褰曟瘮杈冮夯鐑︺€?
鎵€浠ュ父瑙佺殑瑙ｅ喅鏂瑰紡鏈夛細
a.閫氳繃鎵〃鐨勬柟寮忚В鍐筹紝姝ゆ柟娉曞熀鏈笉鍙兘锛屾晥鐜囧お浣庝簡銆?
b.璁板綍涓や唤鏁版嵁锛屼竴浠芥寜鐓х敤鎴风含搴﹀垎琛紝涓€浠芥寜鐓у晢鍝佺淮搴﹀垎琛ㄣ€?
c.閫氳繃鎼滅储寮曟搸瑙ｅ喅锛屼絾濡傛灉瀹炴椂鎬ц姹傚緢楂橈紝鍙堝緱鍏崇郴鍒板疄鏃舵悳绱€?
2. 鑱斿悎鏌ヨ鐨勯棶棰?
鑱斿悎鏌ヨ鍩烘湰涓嶅彲鑳斤紝鍥犱负鍏宠仈鐨勮〃鏈夊彲鑳戒笉鍦ㄥ悓涓€鏁版嵁搴撲腑銆?
3. 閬垮厤璺ㄥ簱浜嬪姟
閬垮厤鍦ㄤ竴涓簨鍔′腑淇敼db0涓殑琛ㄧ殑鏃跺€欏悓鏃朵慨鏀筪b1涓殑琛紝涓€涓槸鎿嶄綔璧锋潵鏇村鏉傦紝鏁堢巼涔熶細鏈変竴瀹氬奖鍝嶃€?
4. 灏介噺鎶婂悓涓€缁勬暟鎹斁鍒板悓涓€DB鏈嶅姟鍣ㄤ笂
渚嬪灏嗗崠瀹禷鐨勫晢鍝佸拰浜ゆ槗淇℃伅閮芥斁鍒癲b0涓紝褰揹b1鎸備簡鐨勬椂鍊欙紝鍗栧a鐩稿叧鐨勪笢瑗垮彲浠ユ甯镐娇鐢ㄣ€備篃灏辨槸璇撮伩鍏嶆暟鎹簱涓殑鏁版嵁渚濊禆鍙︿竴鏁版嵁搴撲腑鐨勬暟鎹€?
涓€涓诲澶?
鍦ㄥ疄闄呯殑搴旂敤涓紝缁濆ぇ閮ㄥ垎鎯呭喌閮芥槸璇昏繙澶т簬鍐欍€侻ysql鎻愪緵浜嗚鍐欏垎绂荤殑鏈哄埗锛屾墍鏈夌殑鍐欐搷浣滈兘蹇呴』瀵瑰簲鍒癕aster锛岃鎿嶄綔鍙互鍦∕aster鍜孲lave鏈哄櫒涓婅繘琛岋紝Slave涓嶮aster鐨勭粨鏋勫畬鍏ㄤ竴鏍凤紝涓€涓狹aster鍙互鏈夊涓猄lave,鐢氳嚦Slave涓嬭繕鍙互鎸係lave,閫氳繃姝ゆ柟寮忓彲浠ユ湁鏁堢殑鎻愰珮DB闆嗙兢鐨凲PS.
鎵€鏈夌殑鍐欐搷浣滈兘鏄厛鍦∕aster涓婃搷浣滐紝鐒跺悗鍚屾鏇存柊鍒癝lave涓婏紝鎵€浠ヤ粠Master鍚屾鍒癝lave鏈哄櫒鏈変竴瀹氱殑寤惰繜锛屽綋绯荤粺寰堢箒蹇欑殑鏃跺€欙紝寤惰繜闂浼氭洿鍔犱弗閲嶏紝Slave鏈哄櫒鏁伴噺鐨勫鍔犱篃浼氫娇杩欎釜闂鏇村姞涓ラ噸銆?
姝ゅ锛屽彲浠ョ湅鍑篗aster鏄泦缇ょ殑鐡堕锛屽綋鍐欐搷浣滆繃澶氾紝浼氫弗閲嶅奖鍝嶅埌Master鐨勭ǔ瀹氭€э紝濡傛灉Master鎸傛帀锛屾暣涓泦缇ら兘灏嗕笉鑳芥甯稿伐浣溿€?
鎵€浠ワ紝1. 褰撹鍘嬪姏寰堝ぇ鐨勬椂鍊欙紝鍙互鑰冭檻娣诲姞Slave鏈哄櫒鐨勫垎寮忚В鍐筹紝浣嗘槸褰揝lave鏈哄櫒杈惧埌涓€瀹氱殑鏁伴噺灏卞緱鑰冭檻鍒嗗簱浜嗐€?2. 褰撳啓鍘嬪姏寰堝ぇ鐨勬椂鍊欙紝灏卞繀椤诲緱杩涜鍒嗗簱鎿嶄綔銆?
---------------------------------------------
MySQL浣跨敤涓轰粈涔堣鍒嗗簱鍒嗚〃
鍙互鐢ㄨ鐢ㄥ埌MySQL鐨勫湴鏂?鍙鏁版嵁閲忎竴澶? 椹笂灏变細閬囧埌涓€涓棶棰?瑕佸垎搴撳垎琛?
杩欓噷寮曠敤涓€涓棶棰樹负浠€涔堣鍒嗗簱鍒嗚〃鍛?MySQL澶勭悊涓嶄簡澶х殑琛ㄥ悧?
鍏跺疄鏄彲浠ュ鐞嗙殑澶ц〃鐨?鎴戞墍缁忓巻鐨勯」鐩腑鍗曡〃鐗╃悊涓婃枃浠跺ぇ灏忓湪80G澶?鍗曡〃璁板綍鏁板湪5浜夸互涓?鑰屼笖杩欎釜琛?
灞炰簬涓€涓潪甯告牳鐢ㄧ殑琛?鏈嬪弸鍏崇郴琛?
浣嗚繖绉嶆柟寮忓彲浠ヨ涓嶆槸涓€涓渶浣虫柟寮? 鍥犱负闈复鏂囦欢绯荤粺濡侲xt3鏂囦欢绯荤粺瀵瑰ぇ浜庡ぇ鏂囦欢澶勭悊涓婁篃鏈夎澶氶棶棰?
杩欎釜灞傞潰鍙互鐢▁fs鏂囦欢绯荤粺杩涜鏇挎崲.浣哅ySQL鍗曡〃澶ぇ鍚庢湁涓€涓棶棰樻槸涓嶅ソ瑙ｅ喅: 琛ㄧ粨鏋勮皟鏁寸浉鍏崇殑鎿嶄綔鍩?
鏈笉鍦ㄥ彲鑳?鎵€浠ュぇ椤瑰湪浣跨敤涓兘浼氶潰鐩戠潃鍒嗗簱鍒嗚〃鐨勫簲鐢?
浠嶪nnodb鏈韩鏉ヨ鏁版嵁鏂囦欢鐨凚tree涓婂彧鏈変袱涓攣, 鍙跺瓙鑺傜偣閿佸拰瀛愯妭鐐归攣,鍙互鎯宠€岀煡閬?褰撳彂鐢熼〉鎷嗗垎鎴栨槸娣诲姞
鏂板彾鏃堕兘浼氶€犳垚琛ㄩ噷涓嶈兘鍐欏叆鏁版嵁.
鎵€浠ュ垎搴撳垎琛ㄨ繕灏辨槸涓€涓瘮杈冨ソ鐨勯€夋嫨浜?
閭ｄ箞鍒嗗簱鍒嗚〃澶氬皯鍚堥€傚憿?
缁忔祴璇曞湪鍗曡〃1000涓囨潯璁板綍涓€涓?鍐欏叆璇诲彇鎬ц兘鏄瘮杈冨ソ鐨? 杩欐牱鍦ㄧ暀鐐筨uffer,閭ｄ箞鍗曡〃鍏ㄦ槸鏁版嵁瀛楀瀷鐨勪繚鎸佸湪
800涓囨潯璁板綍浠ヤ笅, 鏈夊瓧绗﹀瀷鐨勫崟琛ㄤ繚鎸佸湪500涓囦互涓?
濡傛灉鎸?100搴?00琛ㄦ潵瑙勫垝,濡傜敤鎴蜂笟鍔?
500涓?100*100 = 50000000涓?= 5000浜胯褰?
蹇冮噷鏈変竴涓暟浜?鎸変笟鍔″仛瑙勫垝杩樻槸姣旇緝瀹规槗鐨?
View Code 閭ｄ箞鍒嗗簱鍒嗚〃澶氬皯鍚堥€傚憿?聽 鍦ㄥ崟琛?000涓囨潯璁板綍涓€涓?鍐欏叆璇诲彇鎬ц兘鏄瘮杈冨ソ鐨? 鎵€浠ュ湪800涓囨潯璁板綍浠ヤ笅, 鏈夊瓧绗﹀瀷鐨勫崟琛ㄤ繚鎸佸湪500涓囦互涓嬪垎搴撳垎琛? 鍒嗗尯鍒嗚〃鐨勫伐浣滃師鐞嗗強浣跨敤鍦烘櫙鍜屼紭缂虹偣 鍒嗗尯锛毬犅?瀵逛簬鐢ㄦ埛鑰岃█锛屽垎鍖鸿〃鏃朵竴涓嫭绔嬬殑閫昏緫琛紝鍗曞簳灞傜殑mysql灏嗗叾鍒嗘垚浜嗗涓墿鐞嗗瓙琛紝杩欏浜庣敤鎴锋潵璇存槸閫忔槑鐨勶紝姣忎竴涓垎鍖洪兘浼氫娇鐢ㄤ竴涓嫭绔嬬殑琛ㄦ枃浠? mysql /|\ / | \ 1 2 3 鍒涘缓琛ㄧ殑鏃跺€欎娇鐢╬arttition by 瀛愬彞瀹氫箟姣忎釜鍒嗗尯瀛樺偍鐨勬暟鎹紙鏌愬瓧娈碉級锛屾墽琛屾煡璇㈢殑鏃跺€欙紝浼樺寲鍣ㄤ細鏍规嵁鍒嗗尯瀹氫箟杩囨护閭ｄ簺娌℃湁鎴戜滑闇€瑕佹暟鎹殑鍒嗗尯锛岃繖鏍风殑鏌ヨ鍙渶瑕佹煡璇㈡墍鏈夋暟鎹湪鍒嗗尯鍗冲彲 鍒嗗尯鐨勪富瑕佺洰鐨勬槸灏嗘暟鎹寜鐓т竴涓緝绮楃殑绮掑害鍒嗗湪涓嶅悓鐨勮〃涓紝杩欐牱鍙互灏嗙浉鍏虫暟鎹斁鍦ㄤ竴璧凤紝濡傛灉鎯充竴娆″垹闄ゆ暣涓垎鍖虹殑鏁版嵁涔熷緢鏂逛究 璇︽儏锛?閫傜敤鍦烘櫙锛?琛ㄩ潪甯稿ぇ鏃犳硶鍏ㄩ儴瀛樺湪鍐呭瓨锛屾垨鑰呭彧鍦ㄨ〃鐨勬渶鍚庢湁鐑偣鏁版嵁锛屽叾浠栫殑閮芥槸鍘嗗彶鏁版嵁 鍒嗗尯琛ㄧ殑鏁版嵁鏇村鏄撶淮鎶わ紝鍙互瀵圭嫭绔嬬殑鍒嗗尯杩涜鐙珛鐨勬搷浣?鍒嗗尯琛ㄧ殑鏁版嵁鍙互鍒嗗竷鍦ㄤ笉鍚岀殑鏈哄櫒涓婏紝浠庤€岄珮鏁堜娇鐢ㄨ祫婧?鍙互浣跨敤鍒嗗尯琛ㄦ潵閬垮厤鏌愪簺鐗规畩鐨勭摱棰?鍙互澶囦唤鍜屾仮澶嶇嫭绔嬬殑鍒嗗尯 缂虹偣锛?1銆佷竴涓〃鏈€澶氬彧鑳芥湁1024涓垎鍖?2銆?.5.1鐗堟湰涓紝鍒嗗尯琛ㄨ〃杈惧紡蹇呴』鏄暣鏁般€?.5鍙互浣跨敤鍒楀垎鍖?3銆佸垎鍖哄瓧娈典腑濡傛灉鏈変富閿拰鍞竴绱㈠紩鍒楋紝閭ｄ箞涓婚敭鍒楀拰鍞竴鍒楅兘蹇呴』鍖呭惈杩涙潵 闄愬埗锛?鍒嗗尯琛ㄤ腑鏃犳硶浣跨敤澶栭敭绾︽潫 闇€瑕佸鐜版湁琛ㄧ粨鏋勮繘琛屾洿鏀?鎵€鏈夊垎鍖洪兘蹇呴』浣跨敤鐩稿悓鐨勫瓨鍌ㄥ紩鎿?鍒嗗尯鍑芥暟涓彲浠ヤ娇鐢ㄧ殑鍑芥暟鍜岃〃杈惧紡浼氭湁涓€浜涢檺鍒?鏌愪簺杩庝翰涓嶆敮鎸佸垎鍖猴紙myisam鍜宨nnodb鏄敮鎸佺殑锛?瀵逛簬myisam鍒嗗尯琛紝涓嶈兘浣跨敤load index into cache 瀵逛簬myisam琛紝浣跨敤鍒嗗尯琛ㄦ椂闇€瑕佹墦寮€鏇村鐨勬枃浠舵弿杩扮 聽 鍒嗗尯绫诲瀷锛?绉? 寤鸿〃鏃跺垱寤哄垎鍖? Range鍒嗗尯锛氭槸瀵逛竴涓繛缁€х殑琛屽€肩爜鑼冨洿杩涜鍒嗗尯濡俰d<100; 100<id<200 CREATE TABLE IF NOT EXISTS `order_range` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '鐢ㄦ埛id',
`store_id` int(10) unsigned NOT NULL COMMENT '鍟嗗簵id',
`customer_surname` varchar(30) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='xx琛? AUTO_INCREMENT=1 partition by range(id)(
partition p0 values less than(5),
partition p1 values less than(10),
partition p2 values less than(15)
);
#灏嗙敤鎴疯〃鍒嗘垚4涓垎鍖猴紝浠ユ瘡300涓囨潯璁板綍涓虹晫闄愶紝姣忎釜鍒嗗尯閮芥湁鑷繁鐙珛鐨勬暟鎹€佺储寮曟枃浠剁殑瀛樻斁鐩綍锛?#涓庢鍚屾椂锛岃繖浜涚洰褰曟墍鍦ㄧ殑鐗╃悊纾佺洏鍒嗗尯鍙兘涔熼兘鏄畬鍏ㄧ嫭绔嬬殑锛屽彲浠ユ彁楂樼鐩業O鍚炲悙閲?CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2 VALUES LESS THAN (9000000)
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
View Code 淇敼锛? List鍒嗗尯锛毬?聽璺烺ange鍒嗗尯绫讳技锛屼笉杩囦粬瀛樻斁鐨勬槸涓€涓鏁ｅ€肩殑闆嗗悎 CREATE TABLE IF NOT EXISTS `order_range` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`store_id` int(10) unsigned NOT NULL COMMENT '鍟嗗簵id',
`customer_surname` varchar(30) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='xx琛? AUTO_INCREMENT=1 partition by range(id)(
PARTITION pNorth VALUES IN (3,5,6,9,17), //鍖楀尯
PARTITION pEast VALUES IN (1,2,10,11,19,20), //涓滃尯
PARTITION pWest VALUES IN (4,12,13,14,18), //瑗垮尯
PARTITION pCentral VALUES IN (7,8,15,16) //涓績鍖?);
//20涓煶鍍忓簵锛屽垎甯冨湪4涓湁缁忛攢鏉冪殑鍦板尯
杩欎娇寰楀湪琛ㄤ腑澧炲姞鎴栧垹闄ゆ寚瀹氬湴鍖虹殑闆囧憳璁板綍鍙樺緱瀹规槗璧锋潵銆備緥濡傦紝鍋囧畾瑗垮尯鐨勬墍鏈夐煶鍍忓簵閮藉崠缁欎簡鍏朵粬鍏徃銆傞偅涔堜笌鍦ㄨタ鍖洪煶鍍忓簵宸ヤ綔闆囧憳鐩稿叧鐨勬墍鏈夎褰曪紙琛岋級鍙互浣跨敤鏌ヨ鈥淎LTER TABLE employees DROP PARTITION pWest锛涒€濇潵杩涜鍒犻櫎锛屽畠涓庡叿鏈夊悓鏍蜂綔鐢ㄧ殑DELETE 锛堝垹闄わ級鏌ヨ鈥淒ELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)锛涒€濇瘮璧锋潵锛岃鏈夋晥寰楀
View Code #鍒嗘垚4涓尯锛屾暟鎹枃浠跺拰绱㈠紩鏂囦欢鍗曠嫭瀛樻斁銆?CREATE TABLE category (
cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY LIST (cid) (
PARTITION p0 VALUES IN (0,4,8,12)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES IN (1,5,9,13)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2 VALUES IN (2,6,10,14)
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3 VALUES IN (3,7,11,15)
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
View Code 聽 Hash鍒嗗尯锛氬鐢ㄦ埛瀹氫箟鐨勮〃杈惧紡鎵€杩斿洖鐨勫€兼潵杩涜鍒嗗尯锛屽彲浠ュ啓鎴怭artitions(鍒嗗尯鏁扮洰)锛屾垨鑰呯洿鎺ヤ娇鐢ㄥ垎鍖鸿鍙ユ瘮濡傦細partition p0 values in...HASH鍒嗗尯涓昏鐢ㄦ潵纭繚鏁版嵁鍦ㄩ鍏堢‘瀹氭暟鐩殑鍒嗗尯涓钩鍧囧垎甯冦€傚湪RANGE鍜孡IST鍒嗗尯涓紝蹇呴』鏄庣‘鎸囧畾涓€涓粰瀹氱殑鍒楀€兼垨鍒楀€奸泦鍚堝簲璇ヤ繚瀛樺湪鍝釜鍒嗗尯涓紱鑰屽湪HASH鍒嗗尯涓紝MySQL 鑷姩瀹屾垚杩欎簺宸ヤ綔锛屼綘鎵€瑕佸仛鐨勫彧鏄熀浜庡皢瑕佽鍝堝笇鐨勫垪鍊兼寚瀹氫竴涓垪鍊兼垨琛ㄨ揪寮忥紝浠ュ強鎸囧畾琚垎鍖虹殑琛ㄥ皢瑕佽鍒嗗壊鎴愮殑鍒嗗尯鏁伴噺 杩欎腑妯″紡鍏佽DBA閫氳繃瀵硅〃鐨勪竴涓垨澶氫釜鍒楃殑Hash Key杩涜璁＄畻锛屾渶鍚庨€氳繃杩欎釜Hash鐮佷笉鍚屾暟鍊煎搴旂殑鏁版嵁鍖哄煙杩涜鍒嗗尯锛屻€備緥濡侱BA鍙互寤虹珛涓€涓琛ㄤ富閿繘琛屽垎鍖虹殑琛? 渚嬪锛屼笅闈㈢殑璇彞鍒涘缓浜嗕竴涓娇鐢ㄥ熀浜庘€渟tore_id鈥濆垪杩涜 鍝堝笇澶勭悊鐨勮〃锛岃琛ㄨ鍒嗘垚浜?涓垎鍖猴細
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4锛?/pre>
#鍒嗘垚4涓尯锛屾暟鎹枃浠跺拰绱㈠紩鏂囦欢鍗曠嫭瀛樻斁
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY HASH (uid) PARTITIONS 4 (
PARTITION p0
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
View Code
聽
Key鍒嗗尯锛氫笌Hash鍒嗗尯绫讳技鍙笉杩囧垎鍖烘敮鎸佷竴鍒楁垨鑰呭鍒楋紝骞朵笖MYSQL鏈嶅姟鍣ㄨ嚜韬彁渚沨ash鍑芥暟
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY KEY (uid) PARTITIONS 4 (
PARTITION p0
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
聽
鍦ㄥ垱寤哄ソ琛ㄧ殑鎯呭喌涓嬫坊鍔犲垎鍖? alter table order_range add partition(
partition p4 less than(20),
partition p5 less than MAXVALUE
);
鏌ョ湅寤鸿〃缁撴瀯锛? show create table 浣犵殑琛ㄥ悕;
鏌ョ湅鏄惁鏀寔鍒嗗尯
SHOW VARIABLES LIKE '%partition%';
琛ㄧ殑鍚勭鍙傛暟鐘舵€? show table status;
鏌ョ湅鍒嗗尯鎯呭喌锛? select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='濉叆浣犺鏌ョ湅鐨勮〃鍚?;
View Code
閫氳繃鏁版嵁瀛楀吀鏌ョ湅鍒嗗尯淇℃伅
select * from information_schema.partitions;
閫氳繃姝よ鍙ユ壂鎻忓垎鍖哄強鍏朵娇鐢ㄦ儏鍐? explain partitions select * from mdl_user;
鏇村璇︾粏MySQL鍒嗗尯锛圥artition锛夊姛鑳?
娉ㄦ剰锛? MYSQL鐨勫垎鍖哄瓧娈碉紝蹇呴』鍖呭惈鍦ㄤ富閿瓧娈靛唴锛堝嵆蹇呴』鐢虫槑primary key锛?/pre>
MySQL 鎶奛ULL瑙嗕负0銆傚鏋滀綘甯屾湜鍥為伩杩欑鍋氭硶锛屼綘搴旇鍦ㄨ璁¤〃鏃朵笉鍏佽绌哄€硷紱鏈€鍙兘鐨勬柟娉曟槸锛岄€氳繃澹版槑鍒椻€淣OT NULL鈥濇潵瀹炵幇杩欎竴鐐?/pre>
鍒嗗簱鍒嗚〃锛?
琛ㄧ殑琛屾暟瓒呰繃500涓囪鎴栬€呭崟琛ㄥ閲忚秴杩?0GB鏃讹紝鏌ヨ灏变細鍙樻參
閫氳繃涓€浜沨ash绠楁硶鎴栬€呭伐鍏峰疄鐜板皢涓€寮犳暟鎹〃鍨傜洿鎴栬€呮按骞宠繘琛岀墿鐞嗗垏鍒?鍒嗚〃鏂瑰紡锛氭按骞冲垎鍓?鍨傜洿鍒嗗壊
姘村钩鍒嗗壊锛堢被浼紈ord鍒嗛〉 琛ㄧ殑缁撴瀯涓€鏍凤級锛歮ysql 姘村钩鍒嗚〃鎶€鏈?
琛ㄥ緢澶э紝鍒嗗壊鍚庡彲浠ラ檷浣庡湪鏌ヨ鏃堕渶瑕佽鐨勬暟鎹拰绱㈠紩鐨勯〉鏁帮紝鍚屾椂涔熼檷浣庝簡绱㈠紩鐨勫眰鏁帮紝鎻愰珮鏌ヨ閫熷害
浣跨敤鍦烘櫙锛?琛ㄤ腑鐨勬暟鎹湰韬氨鏈夌嫭绔嬫€э紝渚嬪琛ㄤ腑鍒嗗埆璁板綍鍝ュ摜鍦板尯鐨勬暟鎹垨鑰呬笉鍚屾椂鏈熺殑鏁版嵁锛岀壒鍒槸鏈変簺鏁版嵁甯哥敤锛屾湁浜涗笉甯哥敤
鐢ㄦ埛琛ㄥ彲浠ユ牴鎹敤鎴风殑鎵嬫満鍙锋杩涜鍒嗗壊濡倁ser183銆乽ser150銆乽ser153銆乽ser189绛夛紝姣忎釜鍙锋灏辨槸涓€寮犺〃
鐢ㄦ埛琛ㄤ篃鍙互鏍规嵁鐢ㄦ埛鐨刬d杩涜鍒嗗壊锛屽姞鍏ュ垎3寮犺〃user0,user1,user2锛屽鏋滅敤鎴风殑id%3=0灏辨煡璇ser0琛紝濡傛灉鐢ㄦ埛鐨刬d%3=1灏辨煡璇ser1琛?瀵逛簬璁㈠崟琛ㄥ彲浠ユ寜鐓ц鍗曠殑鏃堕棿杩涜鍒嗚〃
闇€瑕佹妸鏁版嵁瀛樻斁鍦ㄥ涓粙璐ㄤ笂锛堟渶鏂版暟鎹仛缂撳瓨鎴栬€呮斁鍦ㄤ笉鍚岀殑鏈嶅姟鍣ㄤ笂锛?
澶ц〃缁熻锛氭柊寤轰竴涓〃杩涜缁熻
鍒涘缓涓€涓腑闂磋〃锛屼腑闂磋〃鐨勭粨鏋勫拰鍘熷琛ㄧ粨鏋勪竴鏍凤紝鎴栧瀛楁锛屽皢鍘熷琛ㄤ腑鐨勯儴鍒嗘暟鎹浆绉诲埌涓棿琛紝鐒跺悗瀵逛腑闂磋〃杩涜缁熻銆?
涓棿琛ㄥ鍒舵簮琛ㄩ儴鍒嗘暟鎹紝骞朵笖涓庢簮琛ㄧ浉闅旂锛屽湪涓棿琛ㄤ笂鍋氱粺璁℃煡璇笉浼氬鍦ㄧ嚎搴旂敤浜х敓璐熼潰褰卞搷
涓棿琛ㄤ笂鍙互鐏垫椿鐨勬坊鍔犵储寮曞鍔犱复鏃剁敤鐨勬柊瀛楁锛屼粠鑰岃揪鍒版彁楂樼粺璁℃煡璇㈡晥鐜囧拰杈呭姪缁熻鏌ヨ浣滅敤
姘村钩鍒嗗壊缂虹偣锛?缁欏簲鐢ㄥ鍔犱簡澶嶆潅搴︼紝閫氬父鏌ヨ闇€瑕佸涓〃鍚嶏紝鏌ヨ鎵€鏈夋暟鎹兘闇€瑕乽nion鎿嶄綔
鍦ㄨ澶氭暟鎹簱搴旂敤涓紝杩欑澶嶆潅鎬т細鑽夋灉浠栧甫鏉ョ殑浼樼偣锛屾煡璇㈡椂浼氬鍔犺涓€涓储寮曞眰鐨勭鐩樻鏁?
鍨傜洿鍒嗚〃锛?鎶婁富閿拰涓€浜涘垪鏀惧湪涓€涓〃锛屼富閿拰鍙﹀涓€浜涗笉甯哥敤鐨勫垪鏀惧湪鍙﹀涓€寮犺〃;
濡傦細鐢ㄦ埛琛ㄦ媶鍒嗕负user_info锛坕d锛寀serid锛宯ame锛宲wd锛宻tatus锛夊拰user_base锛坕d锛寀serid锛宯ame锛宎ge锛宼el锛宎dr锛宻ex锛?搴旂敤鍦烘櫙锛?1銆佸鏋滀竴涓〃涓煇浜涘垪甯哥敤锛屽彟澶栦竴浜涗笉甯哥敤
2銆佸彲浠ヤ娇鏁版嵁琛屽彉灏忥紝涓€涓暟鎹〉鑳藉瓨鍌ㄦ洿澶氱殑鏁版嵁銆傛煡璇㈡椂鍑忓皯I/O娆℃暟
鍨傜洿鍒嗚〃缂虹偣锛氱鐞嗗啑浣欙紝鏌ヨ鎵€鏈夌殑鏁版嵁闇€瑕乯oin鎿嶄綔
鍒嗗簱鍒嗚〃缂虹偣锛?鏈変簺鍒嗚〃鐨勭瓥鐣ュ熀浜庡簲鐢ㄥ眰鐨勯€昏緫绠楁硶锛屼竴鏃﹂€昏緫绠楁硶鏀瑰彉锛屾暣涓垎琛ㄧ殑閫昏緫閮戒細鏀瑰彉锛屾墿灞曟€ц緝宸?瀵逛簬鐢ㄧ敤灞傛潵璇达紝閫昏緫绠楁硶鏃犵枒澧炲姞寮€鍙戞垚鏈?
MySQL鐨勪富浠庡鍒跺強璐熻浇鍘熺悊锛?涓讳粠澶嶅埗鍘熺悊锛?鍦ㄤ富搴撲笂鎶婃暟鎹洿鏀硅褰曞埌浜岃繘鍒舵棩蹇?浠庡簱灏嗕富搴撶殑鏃ュ織澶嶅埗鍒拌嚜宸辩殑涓户鏃ュ織
浠庡簱璇诲彇涓户鏃ュ織涓殑浜嬩欢锛屽皢鍏堕噸鏀惧埌浠庡簱鐨勬暟鎹腑
涓讳粠澶嶅埗瑙ｅ喅鐨勯棶棰橈細
鏁版嵁鍒嗗竷锛氶殢鎰忓仠姝㈡垨寮€濮嬪鍒讹紝骞跺湪涓嶅悓鐨勫湴鐞嗕綅缃垎甯冩暟鎹浠?
璐熻浇鍧囪 锛?1銆侀檷浣庡崟涓湇鍔″櫒鍘嬪姏
2銆侀珮鍙敤鍜屾晠闅滃垏鎹紝甯姪搴旂敤绋嬪簭閬垮厤鍗曠偣澶辫触
鍗囩骇娴嬭瘯锛氬彲浠ヤ娇鐢ㄦ洿楂樼殑鐗堟湰鐨刴ysql浣滀负浠庡簱
浣跨敤mycat涓棿浠惰繘琛屽垎搴撳垎琛?
聽
鏁版嵁搴擄紙鍒嗗簱鍒嗚〃锛変腑闂翠欢瀵规瘮
聽
mysql鐨勫畨鍏ㄦ€ф柟妗堬細
1銆佷娇鐢ㄩ澶勭悊璇彞闃叉sql娉ㄥ叆
濡傛灉浠庡鎴风浼犺繃鏉ワ紵id=1 or 1=1閭ｄ箞sql涓?delete from user where id=1 or 1=1 灏嗕細鍒犻櫎鎵€鐢ㄧ殑user鏁版嵁
2銆佸啓鍏ユ暟鎹簱鐨勬暟鎹杩涜鐗规畩瀛楃鐨勮浆涔?3銆佹煡璇㈤敊璇俊鎭笉瑕佽繑鍥炵粰鐢ㄦ埛锛屽皢閿欒鍐欏叆鏃ュ織
myqsl鐨勫叾浠栭樋瀹夊叏璁剧疆
瀹氭湡鍋氭暟鎹浠?涓嶇粰鏌ヨ鐢ㄦ埛鐨剅oot鏉冮檺锛屽悎鐞嗗垎閰嶆潈闄?鍏抽棴杩滅▼璁块棶鏁版嵁搴撶殑鏉冮檺
淇敼root鍙ｄ护锛屼笉鐢ㄩ粯璁ょ殑鍙ｄ护锛屼娇鐢ㄨ緝澶嶆潅鐨勫彛浠?鍒犻櫎澶氫綑鐨勭敤鎴?鏀瑰彉root鐢ㄦ埛鐨勫悕绉?闄愬埗涓€鑸敤鎴锋祻瑙堝叾浠栧簱
闄愬埗鐢ㄦ埛瀵规暟鎹枃浠剁殑璁块棶鏉冮檺
浠庡彞椤哄簭锛? select---form---where---group by---having---order by---limit
where 鏉′欢鍚庤窡鐨勬搷浣滅锛?绠楁湳杩愮畻绗︼細+ - * /
姣旇緝杩愮畻绗︼細> < = >= <= !=鎴栬€?lt;>
閫昏緫杩愮畻绗? and or
鍏朵粬杩愮畻绗︼細
in(value1,value2,...) where id in(1,3,4)鐩稿綋浜巜here id=1 or id=3 or id=4;
between(value1,value2) where id between 1 and 10;鐩稿綋浜巜here id >=1 and id <= 10;
not 鍚﹀畾鍚庨潰鐨勬潯浠?where id not between 1 and 10;鐩稿綋浜?where id>10;
is null
is not nul
like '鏌ヨ鐨勫瓧绗?; 妯＄硦鏌ヨ
%閫氶厤绗?浠绘剰闀垮害鐨勫瓧绗︿覆鍖呮嫭绌哄瓧绗︿覆 %鍊?浠ュ€肩粨灏?鍊? 浠ュ€煎紑濮?%鍊? 鍖呭惈璇ュ€? select * from user where name like %灏硅帀%;
_閫氶厤绗?浠ｈ〃浠绘剰涓€涓瓧绗?
鎷兼帴瀛楃涓诧細 select concat(path.'_','id');
缁熻:
count(filed)缁熻闈?null鐨勪釜鏁?min(filed) 鏈€灏忓€?max(filed) 鏈€澶у€?sum(filed) 姹傚拰
avg(filed) 骞冲潎鏁?
鍙栧€艰寖鍥?2*(瀛楄妭鏁?1)娆℃柟
鏁存暟绫诲瀷锛? tinyint
smallint
mediumint
int
bigint
娴偣:
float(M,D)
double(M,D)
瀹氱偣:
decimal(M,D)
M锛氫唬琛ㄦ€绘湁鏁堜綅鏁帮紝涓嶅寘鎷皬鏁扮偣
D锛氬皬鏁伴儴鍒嗙殑浣嶆暟
瀛楃涓茬被鍨嬶細
cahr(M) 瀹氶暱銆佷細鍒犻櫎灏鹃儴鐨勭┖鏍? varchar(M) 鍙橀暱銆佷細淇濈暀灏鹃儴鐨勭┖鏍? tinytext <= 512
text < 2^16
longtext < 2^32
mediumtext
浜岃繘鍒跺璞″瀷锛宐lob
tinyblob
blob
mediumblob
longblob
鏋氫妇鍨嬶細
enum("A","B") 蹇呴』寮曞彿鍖呰捣鏉?鍙互瀹氫箟65535涓暟鎹」
闆嗗悎绫诲瀷:
set("A","B","C",...) 鏈€澶氬彲浠ュ畾涔?4涓暟鎹」
鏃ユ湡鍨嬶細
Date锛?鏄剧ず鏍煎紡锛歒YYY-MM-DD
time, 鏄剧ず鏍煎紡锛欻H:MM:SS
datetime 鏄剧ず鏍煎紡锛歒YYY-MM-DD HH:MM:SS
year 鏄剧ず鏍煎紡锛歒YYY
timestamp
绾︽潫鏉′欢锛? null 榛樿
not null
default 榛樿鎻掑叆鍊? zerofill 闆跺～鍏? unsigned 鏃犵鍙? auto_increment 鑷 涓€鑸富閿墠鐢?瀛楁鍔犱笂杩欎釜鍚庯紝鍚庨潰蹇呴』璺焢rimary key鎴栬€卽nique
绱㈠紩
1銆佸父瑙勭储寮曪細 index
鍦ㄥ垱寤鸿〃鐨勬椂鍊欏垱寤虹储寮曪細
create table table_name(
id int(11) unsigned not null auto_increment,
name char(32),
age int(11) unsigned,
index 绱㈠紩鍚?name) //缁檔ame瀛楁娣诲姞绱㈠紩
) ENGINE=innoDB DEFAULT CHARSET=utf8;
鍒涘缓瀹岃〃浠ュ悗鍒涘缓绱㈠紩:
create index 绱㈠紩鍚?on 琛ㄥ悕(瀛楁鍚?;
create index now_age on user(age);
鍒犻櫎绱㈠紩: drop index 绱㈠紩鍚?on table_name;
2銆佸敮涓€绱㈠紩锛歶nique key
create table table_name(
id int(11) unsigned not null auto_increment,
name char(32),
age int(11) unsigned,
unique 绱㈠紩鍚?name) //缁檔ame瀛楁娣诲姞绱㈠紩
) ENGINE=innoDB DEFAULT CHARSET=utf8;
鍒涘缓瀹岃〃浠ュ悗鍒涘缓绱㈠紩:
create unique 绱㈠紩鍚?on 琛ㄥ悕(瀛楁鍚?;
create unique now_age on user(age);
鍒犻櫎绱㈠紩: drop unique 绱㈠紩鍚?on table_name;
澶栭敭 foreign key 澶栭敭鍙湁innodb绫诲瀷鐨勫瓨鍌ㄥ紩鎿庢敮鎸?3銆佷富閿储寮? primary key
鍒犻櫎涓婚敭绱㈠紩涓€鑸涓や釜姝ラ锛? 妫€鏌ユ湁娌℃湁auto_increment,鏈夌殑璇濆厛鍒犳帀auto_increment鍐嶅垹鎺塸rimary key
alter table table_name drop primary key
drop foreign key;
4銆佸叏鏂囩储寮曪細Fulltext
5銆佺粍鍚堬紙澶嶅悎锛夌储寮曪細灏嗗涓垪缁勫悎鍦ㄤ竴璧峰垱寤虹储寮?
涓婚敭绱㈠紩鍜屽敮涓€绱㈠紩鐨勫尯鍒細
涓€涓〃鍙湁涓€涓富閿储寮曪紝鍙互鏈夊涓敮涓€绱㈠紩
涓婚敭绱㈠紩涓€瀹氭槸鍞竴绱㈠紩锛屽敮涓€绱㈠紩涓嶆槸涓婚敭绱㈠紩
涓婚敭鍙互涓庡閿瀯閫犲弬鐓у畬鏁存€х害鏉燂紝闃叉鏁版嵁涓嶄竴鑷?鍒涘缓琛ㄧ洿鎺ュ埗瀹氱储寮?INDEX [indexName] (fieldname)
CREATE TABLE `user` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` char(80) NOT NULL DEFAULT '',
`classid` int(20) NOT NULL DEFAULT '',
`aihao` set('2','4','3','1') NOT NULL DEFAULT '4,1' COMMENT '闆嗗悎鍨?,
`sex` enum('girl','boy') NOT NULL DEFAULT 'boy' COMMENT '鏋氫妇鍨?,
PRIMARY KEY (`id`), 涓婚敭绱㈠紩
constraint `myclassid` foreign key(`classid`) references `class_info` (`id`),
UNIQUE KEY `name` (`name`), 鍞竴绱㈠紩
KEY `myclassid` ('classid') index鏅€氱储寮? KEY `test` ('classid ','sex','aihao') 澶嶅悎鏅€氱储寮?) ENGINE=MyISAM DEFAULT CHARSET=utf8;
聽
娉ㄦ剰浜嬮」
Myisam锛?.0瑕佸簾寮冧簡锛夛細鏁版嵁鎿嶄綔蹇€熺殑涓€绉嶅紩鎿庯紝涓嶆敮鎸佷簨鍔″鐞嗗姛鑳斤紝鏀寔鍏ㄦ枃妫€绱€傛枃浠朵繚瀛樺湪鏁版嵁搴撳悕绉颁负鐩綍鍚嶇殑鐩綍涓紝鏈?涓枃浠躲€?5.1- 榛樿MyISAM銆傛敮鎸佸叏鏂囩储寮曞帇缂╃┖闂村嚱鏁般€備笉鏀寔浜嬬墿鍜岃绾ч攣锛屼笉鏀寔宕╂簝鍚庣殑瀹夊叏鎭㈠
Innodb锛氬姛鑳藉己澶х殑涓€绉嶅紩鎿庯紝鏀寔浜嬪姟澶勭悊鍔熻兘锛屼笉鏀寔鍏ㄦ枃妫€绱€傛枃浠朵繚瀛樺湪涓や釜鍦版柟锛屼竴涓槸鍦ㄦ暟鎹簱鍚嶇О涓虹洰褰曞悕鐨勭洰褰曚腑瀛樻斁琛ㄧ粨鏋勬枃浠讹紝瀹冪殑鏁版嵁鏄繚瀛樺湪涓€涓叡鏈夌殑鏂囦欢涓殑锛屽彲浠ラ€氳繃閰嶇疆鍒嗗紑銆傚涓婚敭鐨勬煡璇㈡€ц兘楂樹簬鍏朵粬绫诲瀷鐨勫瓨鍌ㄥ紩鎿庛€傚唴閮ㄥ仛浜嗗緢澶氫紭鍖栦粠纾佺洏璇诲彇鏁版嵁鏃惰嚜鍔ㄥ湪鍐呭瓨鏋勫缓hash绱㈠紩銆傚嚭鍟婂鏁版嵁鏃惰嚜鍔ㄦ瀯寤烘彃鍏ョ紦鍐插尯銆傞€氳繃涓€浜涙満鍒跺拰宸ュ叿鏀寔鐪熸鐨勭儹澶囦唤銆傛敮鎸佸穿婧冨悗鐨勫畨鍏ㄦ仮澶嶃€傛敮鎸佽绾ч攣銆傛敮鎸佸閿?
鍏变韩閿侊紙璇婚攣锛?鎺掍粬閿侊紙鍐欓攣锛?
閿佺矑搴︼細 琛ㄩ攣锛堣繖浼氶樆濉炲叾浠栫敤鎴峰璇ヨ〃鐨勬墍鏈夎鍐欐搷浣?鍙湪鎺掍粬閿佹椂鐢熸晥锛?琛岄攣锛堣繖浼氶樆濉炲叾浠栫敤鎴峰璇ヨ〃姝ｅ湪鍐欏叆琛岀殑璇诲啓鎿嶄綔 鍙湪鎺掍粬閿佹椂鐢熸晥锛?
瀛樺偍杩囩▼锛氫负浠ュ悗鐨勪娇鐢ㄨ€屼繚瀛樼殑涓€鏉℃垨澶氭潯mysql璇彞鐨勯泦鍚堛€傚瓨鍌ㄨ繃绋嬪氨鏄湁涓氬姟閫昏緫涓氬姟娴佺▼鐨勯泦鍚堬紝鍙互鍦ㄥ瓨鍌ㄨ繃绋嬩腑鍒涘缓琛ㄣ€佹洿鏂版暟鎹€佸垹闄ょ瓑绛?鍦烘櫙锛氶€氳繃鎶婂鐞嗗皝瑁呭湪瀹规槗浣跨敤鐨勫崟鍏冧腑锛岀畝鍖栧鏉傜殑鎿嶄綔锛屼繚璇佹暟鎹殑涓€鑷存€?
瑙﹀彂鍣細鎻愪緵缁欑▼搴忓憳鍜屾暟鎹垎鏋愬憳鏉ヤ繚璇佹暟鎹畬鏁存€х殑涓€绉嶆柟娉曪紝浠栨槸涓庤〃浜嬩欢鐩稿叧鐨勭壒娈婄殑瀛樺偍杩囩▼
浣跨敤鍦烘櫙锛氬彲浠ラ€氳繃鏁版嵁搴撲腑鐨勭浉鍏宠〃瀹炵幇绾ц仈鏇存敼锛屽疄鏃剁洃鎺ф煇寮犺〃涓殑鏌愪釜瀛楁鐨勬洿鏀硅€岄渶瑕佷綔鍑虹浉搴旂殑澶勭悊銆傛煇浜涗笟鍔＄紪鍙风殑鐢熸垚銆傛互鐢ㄤ細閫犳垚鏁版嵁搴撳強搴旂敤绋嬪簭鐨勭淮鎶ゅ洶闅?
鐗圭偣
MyISAM InnoDB Memory MERGE NDB
瀛樺偍闄愬埗 鏈?64TB 鏈?娌℃湁 鏈?浜嬪姟瀹夊叏 鏀寔
閿佹満鍒?琛ㄩ攣 琛岄攣 琛ㄩ攣 琛ㄩ攣 琛岄攣
B鏍戠储寮?鏀寔 鏀寔 鏀寔 鏀寔 鏀寔
鍝堝笇绱㈠紩 鏀寔 鏀寔
鍏ㄦ枃绱㈠紩 鏀寔
闆嗙兢绱㈠紩 鏀寔
鏁版嵁缂撳瓨 鏀寔 鏀寔 鏀寔
绱㈠紩缂撳瓨 鏀寔 鏀寔 鏀寔 鏀寔 鏀寔
鏁版嵁鍙帇缂?鏀寔
绌洪棿浣跨敤 浣?楂?N/A 浣?浣?鍐呭瓨浣跨敤 浣?楂?涓瓑 浣?楂?鎵归噺鎻掑叆鐨勯€熷害 楂?浣?楂?楂?楂?鏀寔澶栭敭 鏀寔
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb4 |瀹㈡埛绔缃?| character_set_connection | utf8mb4 |瀹㈡埛绔缃?| character_set_database | latin1 |杩欎釜鏄綋鍓嶆墍鍦ㄧ殑鏁版嵁搴撳瓧绗﹂泦锛涢粯璁ょ殑鏄痩atin1瀛楃闆?| character_set_filesystem | binary |
| character_set_results | utf8mb4 |瀹㈡埛绔缃?| character_set_server | utf8mb4 |
| character_set_system | utf8 |character_set_system鏄釜鍙鏁版嵁涓嶈兘鏇存敼
| character_sets_dir | D:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
character_set_client銆乧haracter_set_connection銆乧haracter_set_results 鏄鎴风杩炴帴mysql鏃堕€夋嫨鐨勫瓧绗﹂泦锛岀劧鍚庢湇鍔″櫒灏变細鍘昏缃繖涓変釜鐨勫€? 濡傛灉鎴戜滑鎯冲憡璇塵ysql server鑷繁鏈杩炴帴鎯充娇鐢╨atin1锛屽垯鍛戒护琛屼笅鍙互濡備笅鍐欐硶锛? mysql -uroot -h 192.168.2.11 [email聽protected] --default-character-set=latin1
涓存椂鏇存敼瀛楃闆嗭細
SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_database = utf8;
SET character_set_results = utf8;
SET character_set_server = utf8;
姘镐箙鏇存敼榛樿瀛楃闆嗭細
浠巑y.ini涓嬫墜锛堟爣绛句笅娌℃湁鐨勬坊鍔狅紝鏈夌殑淇敼锛?
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
default-character-set=utf8
淇敼鏈嶅姟鍣ㄧ骇鍒殑瀛楃闆?
涓存椂淇敼锛歴et global character_set_server=utf8;
姘镐箙淇敼锛?my.ini銆乵y.conf 鍦╗mysqld]鍚庡姞涓奵haracter_set_server=utf8
聽
flush 璇硶
FLUSH [LOCAL | NO_WRITE_TO_BINLOG] flush_option [, flush_option] ...
flush_option聽鍖呭惈锛?flush logs : 鍒锋柊浜岃繘鍒舵棩蹇楁枃浠?
flush PRIVILEGES锛氬埛鏂版潈闄愶紝淇敼鏉冮檺鎴栧瘑鐮佸悗锛岄渶瑕佷娇鐢ㄥ埌璇ュ懡浠?
flush tables锛氬叧闂墍鏈夎〃锛屽苟娓呯┖缂撳瓨涓殑绫诲 銆?
flush tables with read lock:鍏抽棴鎵€鏈夋墦寮€鐨勮〃锛屽苟涓斿鎵€鏈塂B涓殑琛ㄦ坊鍔犱竴涓閿侊紝
鐩村埌鏄剧ず鎵цunlock tables 銆傝鍛戒护甯哥敤璇浠芥暟鎹€?
flush master 锛氬垹闄ゆ墍鏈変簩杩涘埗鏃ュ織绱㈠紩鏂囦欢涓殑浜岃繘鍒舵棩蹇楁枃浠讹紝閲嶇疆浜岃繘鍒舵棩蹇楁枃浠剁殑绱㈠紩鏂囦欢涓虹┖锛?
鍒涘缓涓€涓柊鐨勪簩杩涘埗鏃ュ織鏂囦欢锛屾棭璧风増鏈敮鎸?,鐜扮増鏈腑浣跨敤reset master 鏇夸唬銆?鍜宲urge鐨勫熀鏈竴鏍枫€?鍖哄埆锛歱urege 浠呬粎娓呯┖浜岃繘鍒舵棩蹇楁枃浠?
reset master 娓呯┖浜岃繘鍒舵棩蹇楀苟涓旈噸缃簩杩涘埗鏃ュ織绱㈠紩鏂囦欢
flush status :閲嶇疆澶у鏁扮姸鎬佸彉閲忓埌0 鍙湪褰撹皟璇曟煡璇㈡椂锛屾偍鎵嶅簲璇ヤ娇鐢ㄦ椤?
flush Query Cache:閲嶆暣鏌ヨ缂撳瓨锛屾秷闄ゅ叾涓殑纰庣墖锛?**鑳斤紝浣嗗苟涓嶅奖鍝嶆煡璇㈢紦瀛樹腑鐨勭幇鏈夋暟鎹?/pre>
聽
甯歌鏁版嵁搴撳父鐢ㄦ暟鎹被鍨嬨€侀粯璁ょ鍙?/h1> 聽 杞浇浜?https://www.cnblogs.com/lichihua/p/9302652.html
![PAT 1089 Insert or Merge[难][图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)