MYSQL-琛ㄥ垎鍖恒€佹煡鐪嬪垎鍖?杞_mysql

鍘熸枃鍦板潃锛歨ttp://blog.csdn.net/feihong247/article/details/7885199

聽

涓€銆伮犅犅犅犅犅犅爉ysql鍒嗗尯绠€浠?/h4>

鏁版嵁搴撳垎鍖?/p>

鏁版嵁搴撳垎鍖烘槸涓€绉嶇墿鐞嗘暟鎹簱璁捐鎶€鏈€傝櫧鐒跺垎鍖烘妧鏈彲浠ュ疄鐜板緢澶氭晥鏋滐紝浣嗗叾涓昏鐩殑鏄负浜嗗湪鐗瑰畾鐨凷QL鎿嶄綔涓噺灏戞暟鎹鍐欑殑鎬婚噺浠ョ缉鍑弒ql璇彞鐨勫搷搴旀椂闂达紝鍚屾椂瀵逛簬搴旂敤鏉ヨ鍒嗗尯瀹屽叏鏄€忔槑鐨勩€?/p>

MYSQL鐨勫垎鍖轰富瑕佹湁涓ょ褰㈠紡锛氭按骞冲垎鍖哄拰鍨傜洿鍒嗗尯

聽

姘村钩鍒嗗尯锛圚orizontalPartitioning锛?/p>

杩欑褰㈠紡鐨勫垎鍖烘槸瀵规牴鎹〃鐨勮杩涜鍒嗗尯锛岄€氳繃杩欐牱鐨勬柟寮忎笉鍚屽垎缁勯噷闈㈢殑鐗╃悊鍒楀垎鍓茬殑鏁版嵁闆嗗緱浠ョ粍鍚堬紝浠庤€岃繘琛屼釜浣撳垎鍓诧紙鍗曞垎鍖猴級鎴栭泦浣撳垎鍓诧紙1涓垨澶氫釜鍒嗗尯锛夈€?br> 鎵€鏈夊湪琛ㄤ腑瀹氫箟鐨勫垪鍦ㄦ瘡涓暟鎹泦涓兘鑳芥壘鍒帮紝鎵€浠ヨ〃鐨勭壒鎬т緷鐒跺緱浠ヤ繚鎸併€傛按骞冲垎鍖轰竴瀹氳閫氳繃鏌愪釜灞炴€у垪鏉ュ垎鍓层€傚父瑙佺殑姣斿骞翠唤锛屾棩鏈熺瓑銆?/p>

聽

鍨傜洿鍒嗗尯锛圴erticalPartitioning锛?/p>

杩欑鍒嗗尯鏂瑰紡涓€鑸潵璇存槸閫氳繃瀵硅〃鐨勫瀭鐩村垝鍒嗘潵鍑忓皯鐩爣琛ㄧ殑瀹藉害锛屼娇鏌愪簺鐗瑰畾鐨勫垪琚垝鍒嗗埌鐗瑰畾鐨勫垎鍖猴紝姣忎釜鍒嗗尯閮藉寘鍚簡鍏朵腑鐨勫垪鎵€瀵瑰簲鎵€鏈夎銆?br> 鍙互鐢?showvariables like ‘%partition%’;

鍛戒护鏌ヨ褰撳墠鐨刴ysql鏁版嵁搴撶増鏈槸鍚︽敮鎸佸垎鍖恒€?/p>

鍒嗗尯鐨勪綔鐢細鏁版嵁搴撴€ц兘鐨勬彁鍗囧拰绠€鍖栨暟鎹鐞?/p>

鍦ㄦ壂鎻忔搷浣滀腑锛宮ysql浼樺寲鍣ㄥ彧鎵弿淇濇姢鏁版嵁鐨勯偅涓垎鍖轰互鍑忓皯鎵弿鑼冨洿鑾峰緱鎬ц兘鐨勬彁楂樸€?/p>

鍒嗗尯鎶€鏈娇寰楁暟鎹鐞嗗彉寰楃畝鍗曪紝鍒犻櫎鏌愪釜鍒嗗尯涓嶄細瀵瑰彟澶栫殑鍒嗗尯閫犳垚褰卞搷锛屽垎鍖烘湁绯荤粺鐩存帴绠＄悊涓嶇敤鎵嬪伐骞查銆?/p>

mysql浠?.1鐗堟湰寮€濮嬫敮鎸佸垎鍖恒€傛瘡涓垎鍖虹殑鍚嶇О鏄笉鍖哄垎澶у皬鍐欍€傚悓涓〃涓殑鍒嗗尯琛ㄥ悕绉拌鍞竴銆?/p>

浜屻€伮犅犅犅犅犅犅爉ysql鍒嗗尯绫诲瀷

鏍规嵁鎵€浣跨敤鐨勪笉鍚屽垎鍖鸿鍒欏彲浠ュ垎鎴愬嚑澶у垎鍖虹被鍨嬨€?/p>

RANGE 鍒嗗尯锛?/p>

鍩轰簬灞炰簬涓€涓粰瀹氳繛缁尯闂寸殑鍒楀€硷紝鎶婂琛屽垎閰嶇粰鍒嗗尯銆?/p>

LIST 鍒嗗尯锛?/p>

绫讳技浜庢寜RANGE鍒嗗尯锛屽尯鍒湪浜嶭IST鍒嗗尯鏄熀浜庡垪鍊煎尮閰嶄竴涓鏁ｅ€奸泦鍚堜腑鐨勬煇涓€兼潵杩涜閫夋嫨銆?/p>

HASH鍒嗗尯锛?/p>

鍩轰簬鐢ㄦ埛瀹氫箟鐨勮〃杈惧紡鐨勮繑鍥炲€兼潵杩涜閫夋嫨鐨勫垎鍖猴紝璇ヨ〃杈惧紡浣跨敤灏嗚鎻掑叆鍒拌〃涓殑杩欎簺琛岀殑鍒楀€艰繘琛岃绠椼€傝繖涓嚱鏁板彲浠ュ寘鍚玀ySQL涓湁鏁堢殑銆佷骇鐢熼潪璐熸暣鏁板€肩殑浠讳綍琛ㄨ揪寮忋€?/p>

KEY
鍒嗗尯锛氱被浼间簬鎸塇ASH鍒嗗尯锛屽尯鍒湪浜嶬EY鍒嗗尯鍙敮鎸佽绠椾竴鍒楁垨澶氬垪锛屼笖MySQL鏈嶅姟鍣ㄦ彁渚涘叾鑷韩鐨勫搱甯屽嚱鏁般€傚繀椤绘湁涓€鍒楁垨澶氬垪鍖呭惈鏁存暟鍊笺€?/p>

澶嶅悎鍒嗗尯锛?/p>

鍩轰簬RANGE/LIST 绫诲瀷鐨勫垎鍖鸿〃涓瘡涓垎鍖虹殑鍐嶆鍒嗗壊銆傚瓙鍒嗗尯鍙互鏄?HASH/KEY 绛夌被鍨嬨€?/p>

聽

涓夈€伮犅犅犅犅犅犅爉ysql鍒嗗尯琛ㄥ父鐢ㄦ搷浣滅ず渚?/h4>

浠ラ儴闂ㄥ憳宸ヨ〃涓轰緥瀛愶細

1)聽聽聽聽聽聽 鍒涘缓range鍒嗗尯

聽

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date,

salary int

)

partition by range(salary)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than maxvalue

);

浠ュ憳宸ュ伐璧勪负渚濇嵁鍋氳寖鍥村垎鍖恒€?/p>

聽

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(year(birthdate))

(

partition p1 values less than (1980),

partition p2 values less than (1990),

partition p3 values less than maxvalue

);

浠?/span>year(birthdate)琛ㄨ揪寮忥紙璁＄畻鍛樺伐鐨勫嚭鐢熸棩鏈燂級浣滀负鑼冨洿鍒嗗尯渚濇嵁銆傝繖閲屾渶鍊煎緱娉ㄦ剰鐨勬槸琛ㄨ揪寮忓繀椤绘湁杩斿洖鍊笺€?/p>

聽

2)聽聽聽聽聽聽 鍒涘缓list鍒嗗尯

聽

create table emp

(empno聽 varchar(20) not null ,

empname varchar(20),

deptno聽 int,

birthdate date not null,

salary int

)

partition by list(deptno)

(

partition p1 values in聽 (10),

partition p2 values in聽 (20),

partition p3 values聽 in聽 (30)

);

浠ラ儴闂ㄤ綔涓哄垎鍖轰緷鎹紝姣忎釜閮ㄩ棬鍋氫竴鍒嗗尯銆?/p>

聽

3)聽聽聽聽聽聽 鍒涘缓hash鍒嗗尯

HASH鍒嗗尯涓昏鐢ㄦ潵纭繚鏁版嵁鍦ㄩ鍏堢‘瀹氭暟鐩殑鍒嗗尯涓钩鍧囧垎甯冦€傚湪RANGE鍜孡IST鍒嗗尯涓紝蹇呴』鏄庣‘鎸囧畾涓€涓粰瀹氱殑鍒楀€兼垨鍒楀€奸泦鍚堝簲璇ヤ繚瀛樺湪鍝?涓垎鍖轰腑锛涜€屽湪HASH鍒嗗尯涓紝MySQL 鑷姩瀹屾垚杩欎簺宸ヤ綔锛屼綘鎵€瑕佸仛鐨勫彧鏄熀浜庡皢瑕佽鍝堝笇鐨勫垪鍊兼寚瀹氫竴涓垪鍊兼垨琛ㄨ揪寮忥紝浠ュ強鎸囧畾琚垎鍖虹殑琛ㄥ皢瑕佽鍒嗗壊鎴愮殑鍒嗗尯鏁伴噺銆?/p>

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by hash(year(birthdate))

partitions 4;

4)聽聽聽聽聽聽 鍒涘缓key鍒嗗尯

鎸夌収KEY杩涜鍒嗗尯绫讳技浜庢寜鐓ASH鍒嗗尯锛岄櫎浜咹ASH鍒嗗尯浣跨敤鐨勭敤鎴峰畾涔夌殑琛ㄨ揪寮忥紝鑰孠EY鍒嗗尯鐨勫搱甯屽嚱鏁版槸鐢盡ySQL 鏈嶅姟鍣ㄦ彁渚涳紝鏈嶅姟鍣ㄤ娇鐢ㄥ叾鑷繁鍐呴儴鐨勫搱甯屽嚱鏁帮紝杩欎簺鍑芥暟鏄熀浜庝笌PASSWORD()涓€鏍风殑杩愮畻娉曞垯銆傗€淐REATE TABLE …PARTITION BY KEY鈥濈殑璇硶瑙勫垯绫讳技浜庡垱寤轰竴涓€氳繃HASH鍒嗗尯鐨勮〃鐨勮鍒欍€傚畠浠敮涓€鐨勫尯鍒湪浜庝娇鐢ㄧ殑鍏抽敭瀛楁槸KEY鑰屼笉鏄疕ASH锛屽苟涓擪EY鍒嗗尯鍙噰鐢ㄤ竴涓垨澶氫釜 鍒楀悕鐨勪竴涓垪琛ㄣ€?/p>

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by key(birthdate)

partitions 4;

聽

聽

5)聽聽聽聽聽聽 鍒涘缓澶嶅悎鍒嗗尯

聽

range – hash(鑼冨洿鍝堝笇)澶嶅悎鍒嗗尯

聽

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by hash(year(birthdate))

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

range- key澶嶅悎鍒嗗尯

聽

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by range(salary)

subpartition by key(birthdate)

subpartitions 3

(

partition p1 values less than (2000),

partition p2 values less than maxvalue

);

list – hash澶嶅悎鍒嗗尯

CREATE TABLE emp (

empno varchar(20) NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by hash(year(birthdate))

subpartitions 3

(

PARTITION p1 VALUES in聽 (10),

PARTITION p2 VALUES in聽 (20)

)

;

list – key 澶嶅悎鍒嗗尯

聽

CREATE TABLE empk (

empno varchar(20) NOT NULL,

empname varchar(20) ,

deptno int,

birthdate date NOT NULL,

salary int

)

PARTITION BY list (deptno)

subpartition by key(birthdate)

subpartitions 3

(

PARTITION p1 VALUES in聽 (10),

PARTITION p2 VALUES in聽 (20)

)

;

6)聽聽聽聽聽聽 鍒嗗尯琛ㄧ殑绠＄悊鎿嶄綔

鍒犻櫎鍒嗗尯:

alter table emp drop partition p1;

涓嶅彲浠ュ垹闄ash鎴栬€卥ey鍒嗗尯銆?/p>

涓€娆℃€у垹闄ゅ涓垎鍖猴紝alter table emp drop partition p1,p2;

聽

澧炲姞鍒嗗尯:

alter table emp add partition (partition p3 values less than (4000));

alter table empl add partition (partition p3 values in (40));

聽

鍒嗚В鍒嗗尯:

Reorganizepartition鍏抽敭瀛楀彲浠ュ琛ㄧ殑閮ㄥ垎鍒嗗尯鎴栧叏閮ㄥ垎鍖鸿繘琛屼慨鏀癸紝骞朵笖涓嶄細涓㈠け鏁版嵁銆傚垎瑙ｅ墠鍚庡垎鍖虹殑鏁翠綋鑼冨洿搴旇涓€鑷淬€?/p>

alter table te

reorganize partition p1 into

(

partition p1 values less than (100),

partition p3 values less than (1000)

); —-涓嶄細涓㈠け鏁版嵁

聽

鍚堝苟鍒嗗尯:

Merge鍒嗗尯锛氭妸2涓垎鍖哄悎骞朵负涓€涓€?br>alter table te

reorganize partition p1,p3 into

(partition p1 values less than (1000));

—-涓嶄細涓㈠け鏁版嵁

聽

閲嶆柊瀹氫箟hash鍒嗗尯琛?

Alter table emp partition by hash(salary)partitions 7;

—-涓嶄細涓㈠け鏁版嵁

閲嶆柊瀹氫箟range鍒嗗尯琛?

Alter table emp partitionbyrange(salary)

(

partition p1 values less than (2000),

partition p2 values less than (4000)

); —-涓嶄細涓㈠け鏁版嵁

聽

鍒犻櫎琛ㄧ殑鎵€鏈夊垎鍖?

聽

Alter table emp removepartitioning;–涓嶄細涓㈠け鏁版嵁

聽

閲嶅缓鍒嗗尯锛?/strong>

杩欏拰鍏堝垹闄や繚瀛樺湪鍒嗗尯涓殑鎵€鏈夎褰曪紝鐒跺悗閲嶆柊鎻掑叆瀹冧滑锛屽叿鏈夊悓鏍风殑鏁堟灉銆傚畠鍙敤浜庢暣鐞嗗垎鍖虹鐗囥€?/p>

ALTER TABLE emp rebuild partitionp1,p2;

聽

浼樺寲鍒嗗尯锛?/strong>

濡傛灉浠庡垎鍖轰腑鍒犻櫎浜嗗ぇ閲忕殑琛岋紝鎴栬€呭涓€涓甫鏈夊彲鍙橀暱搴︾殑琛岋紙涔熷氨鏄锛屾湁VARCHAR锛孊LOB锛屾垨TEXT绫诲瀷鐨勫垪锛変綔浜嗚澶氫慨鏀癸紝鍙互浣跨敤鈥淎LTER TABLE … OPTIMIZE PARTITION鈥濇潵鏀跺洖娌℃湁浣跨敤鐨勭┖闂达紝骞舵暣鐞嗗垎鍖烘暟鎹枃浠剁殑纰庣墖銆?/p>

ALTER TABLE emp optimize partition p1,p2;

聽

鍒嗘瀽鍒嗗尯锛?/strong>

璇诲彇骞朵繚瀛樺垎鍖虹殑閿垎甯冦€?/p>

ALTER TABLE emp analyze partition p1,p2;

聽

淇ˉ鍒嗗尯锛?/strong>

淇ˉ琚牬鍧忕殑鍒嗗尯銆?/p>

ALTER TABLE emp repairpartition p1,p2;

聽

妫€鏌ュ垎鍖猴細

鍙互浣跨敤鍑犱箮涓庡闈炲垎鍖鸿〃浣跨敤CHECK TABLE 鐩稿悓鐨勬柟寮忔鏌ュ垎鍖恒€?/p>

ALTER TABLE emp CHECK partition p1,p2;

杩欎釜鍛戒护鍙互鍛婅瘔浣犺〃emp鐨勫垎鍖簆1,p2涓殑鏁版嵁鎴栫储寮曟槸鍚﹀凡缁忚鐮村潖銆傚鏋滃彂鐢熶簡杩欑鎯呭喌锛屼娇鐢ㄢ€淎LTER TABLE … REPAIR PARTITION鈥濇潵淇ˉ璇ュ垎鍖恒€?/p>

聽

聽

銆恗ysql鍒嗗尯琛ㄧ殑灞€闄愭€с€?/p>

1.聽聽聽聽聽聽鍦?.1鐗堟湰涓垎鍖鸿〃瀵瑰敮涓€绾︽潫鏈夋槑纭殑瑙勫畾锛屾瘡涓€涓敮涓€绾︽潫蹇呴』鍖呭惈鍦ㄥ垎鍖鸿〃鐨勫垎鍖洪敭锛堜篃鍖呮嫭涓婚敭绾︽潫锛夈€?/p>

聽

CREATE TABLE emptt (

empno varchar(20) NOT NULL聽 ,

empname varchar(20)锛?/p>

deptno int,

birthdate date NOT NULL,

salary int ,

primary key (empno)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

杩欐牱鐨勮鍙ヤ細鎶ラ敊銆侻ySQL Database Error: A PRIMARY KEY must include allcolumns in the table’s partitioning function锛?/p>

CREATE TABLE emptt (

empno varchar(20) NOT NULL聽 ,

empname varchar(20) ,

deptno int(11),

birthdate date NOT NULL,

salary int(11) ,

primary key (empno锛?span style=”color: maroon;”>salary)

)

PARTITION BY range (salary)

(

PARTITION p1 VALUES less than (100),

PARTITION p2 VALUES less than (200)

);

鍦ㄤ富閿腑鍔犲叆salary鍒楀氨姝ｅ父銆?/p>

聽

2.聽聽聽聽聽聽MySQL鍒嗗尯澶勭悊NULL鍊肩殑鏂瑰紡

濡傛灉鍒嗗尯閿墍鍦ㄥ垪娌℃湁notnull绾︽潫銆?/p>

濡傛灉鏄痳ange鍒嗗尯琛紝閭ｄ箞null琛屽皢琚繚瀛樺湪鑼冨洿鏈€灏忕殑鍒嗗尯銆?/p>

濡傛灉鏄痩ist鍒嗗尯琛紝閭ｄ箞null琛屽皢琚繚瀛樺埌list涓?鐨勫垎鍖恒€?/p>

鍦ㄦ寜HASH鍜孠EY鍒嗗尯鐨勬儏鍐典笅锛屼换浣曚骇鐢烴ULL鍊肩殑琛ㄨ揪寮弇ysql閮借鍚屽畠鐨勮繑鍥炲€间负0銆?/p>

涓轰簡閬垮厤杩欑鎯呭喌鐨勪骇鐢燂紝寤鸿鍒嗗尯閿缃垚NOT NULL銆?/p>

聽

3.聽聽聽聽聽聽鍒嗗尯閿繀椤绘槸INT绫诲瀷锛屾垨鑰呴€氳繃琛ㄨ揪寮忚繑鍥濱NT绫诲瀷锛屽彲浠ヤ负NULL銆傚敮涓€鐨勪緥澶栨槸褰撳垎

鍖虹被鍨嬩负KEY鍒嗗尯鐨勬椂鍊欙紝鍙互浣跨敤鍏朵粬绫诲瀷鐨勫垪浣滀负鍒嗗尯閿紙 BLOB or TEXT 鍒楅櫎澶栵級銆?/p>

聽

4.聽聽聽聽聽聽瀵瑰垎鍖鸿〃鐨勫垎鍖洪敭鍒涘缓绱㈠紩锛岄偅涔堣繖涓储寮曚篃灏嗚鍒嗗尯,鍒嗗尯閿病鏈夊叏灞€绱㈠紩涓€璇淬€?/p>

5.聽聽聽聽聽聽鍙湁RANG鍜孡IST鍒嗗尯鑳借繘琛屽瓙鍒嗗尯锛孒ASH鍜孠EY鍒嗗尯涓嶈兘杩涜瀛愬垎鍖恒€?/p>

6.聽聽聽聽聽聽涓存椂琛ㄤ笉鑳借鍒嗗尯銆?/p>

聽

鍥涖€伮犅犅犅犅犅犅犺幏鍙杕ysql鍒嗗尯琛ㄤ俊鎭殑鍑犵鏂规硶

1.聽聽聽聽 show create table 琛ㄥ悕
鍙互鏌ョ湅鍒涘缓鍒嗗尯琛ㄧ殑create璇彞

2.聽聽聽聽 show table status
鍙互鏌ョ湅琛ㄦ槸涓嶆槸鍒嗗尯琛?

3.聽聽聽聽 鏌ョ湅information_schema.partitions琛?
select
聽 partition_name part,聽
聽 partition_expression expr,聽
聽 partition_description descr,聽
聽 table_rows聽
from information_schema.partitions聽 where
聽 table_schema = schema()聽
聽 and table_name=’test’;聽
鍙互鏌ョ湅琛ㄥ叿鏈夊摢鍑犱釜鍒嗗尯銆佸垎鍖虹殑鏂规硶銆佸垎鍖轰腑鏁版嵁鐨勮褰曟暟绛変俊鎭?/p>

4.聽聽聽聽 explain partitions select璇彞
閫氳繃姝よ鍙ユ潵鏄剧ず鎵弿鍝簺鍒嗗尯锛屽強浠栦滑鏄浣曚娇鐢ㄧ殑.

聽

浜斻€伮犅犅犅犅犅犅犲垎鍖鸿〃鎬ц兘姣旇緝

1.聽聽聽聽 鍒涘缓涓ゅ紶琛? part_tab(鍒嗗尯琛?,no_part_tab(鏅€氳〃)

CREATE TABLEpart_tab

( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null)

PARTITION BYRANGE(year(c3))

(PARTITION p0VALUES LESS THAN (1995),

PARTITION p1 VALUESLESS THAN (1996) ,

PARTITION p2 VALUESLESS THAN (1997) ,

PARTITION p3 VALUESLESS THAN (1998) ,

聽PARTITION p4 VALUES LESS THAN (1999) ,

PARTITION p5 VALUESLESS THAN (2000) ,

PARTITION p6 VALUESLESS THAN (2001) ,

PARTITION p7 VALUESLESS THAN (2002) ,

PARTITION p8 VALUESLESS THAN (2003) ,

PARTITION p9 VALUESLESS THAN (2004) ,

PARTITION p10VALUES LESS THAN (2010),

PARTITION p11VALUES LESS THAN (MAXVALUE) );

CREATE TABLE no_part_tab

( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null)锛?/p>

聽

2.聽聽聽聽 鐢ㄥ瓨鍌ㄨ繃绋嬫彃鍏?00涓囨潯鏁版嵁

CREATE PROCEDUREload_part_tab()

聽聽聽 begin

聽聽聽 declare v int default 0;

聽聽聽 while v < 8000000

聽聽聽 do

聽聽聽聽聽聽聽 insert into part_tab

聽聽聽聽聽聽聽 values (v,’testingpartitions’,adddate(‘1995-01-01’,(rand(v)*36520)mod 3652));

聽聽聽聽聽聽聽聽 set v = v + 1;

聽聽聽 end while;

end;

insert into no_part_tab聽 select * frompart_tab;

3.聽聽聽聽 娴嬭瘯sql鎬ц兘

鏌ヨ鍒嗗尯琛細

selectcount(*) from part_tab where c3 > date ‘1995-01-01’and c3 < date ‘1995-12-31’;

+———-+
| count(*) |
+———-+
|聽聽 795181 |
+———-+
1 row in set (2.62 sec)

鏌ヨ鏅€氳〃锛?/p>

selectcount(*) from part_tab where c3 > date ‘1995-01-01’and c3 < date ‘1995-12-31’;

+———-+
| count(*) |
+———-+
|聽聽 795181 |
+———-+
1 row in set (7.33 sec)

鍒嗗尯琛ㄧ殑鎵ц鏃堕棿姣旀櫘閫氳〃灏?0%銆?/p>

聽

4.聽聽聽聽 閫氳繃explain璇彞鏉ュ垎鏋愭墽琛屾儏鍐?/p>

mysql>explain select count(*) from part_tab where c3 > date ‘1995-01-01’and c3 < date ‘1995-12-31’;

+—-+————-+———-+——+—————+——+———+——+———+————-+

| id |select_type | table聽聽聽 | type |possible_keys | key聽 | key_len | ref聽 | rows聽聽聽| Extra聽聽聽聽聽聽 |

+—-+————-+———-+——+—————+——+———+——+———+————-+

|聽 1 | SIMPLE聽聽聽聽聽 | part_tab | ALL聽 | NULL聽聽聽聽聽聽聽聽聽 | NULL | NULL聽聽聽 | NULL | 7980796 | Using where |

+—-+————-+———-+——+—————+——+———+——+———+————-+

1 rowin set

聽

mysql>explain select count(*) from no_part_tab where c3 > date ‘1995-01-01’and c3 < date ‘1995-12-31’;

+—-+————-+————-+——+—————+——+———+——+———+————-+

| id |select_type | table聽聽聽聽聽聽 | type |possible_keys | key聽 | key_len | ref聽 | rows聽聽聽| Extra聽聽聽聽聽聽 |

+—-+————-+————-+——+—————+——+———+——+———+————-+

|聽 1 | SIMPLE聽聽聽聽聽 | no_part_tab | ALL聽 | NULL聽聽聽聽聽聽聽聽聽 | NULL | NULL聽聽聽 | NULL | 8000206 | Using where |

+—-+————-+————-+——+—————+——+———+——+———+————-+

1 rowin set

mysql >

鍒嗗尯琛ㄦ墽琛屾壂鎻忎簡7980796琛岋紝鑰屾櫘閫氳〃鍒欐壂鎻忎簡8000206琛屻€?/p>