PHP
·
发表于 5年以前
·
阅读量：8303
**Ò»£¬ *ÖÇø¸ÅÄi **
_ÖÇø_ÔÊÐi¸u¾ÝÖ¸¶¨µÄ¹aeÔo£¬¿çÎļþϵͳÖÅaµ¥¸o±iµÄ¶a¸o²¿Ö¡£±iµÄ²»Í¬²¿ÖÔÚ²»Í¬µÄλÖñ»´ae´¢Îªµ¥¶ÀµÄ±i¡£MySQL´Ó5.1.3¿ªÊ¼Ö§³ÖPartition¡£
ÖÇøºÍÊÖ¶¯Ö±i¶Ô±È
ÊÖ¶¯*Ö±i** *ÖÇø**
¶aÕÅÊý¾Ý±i Ò»ÕÅÊý¾Ý±i
Öظ´Êý¾ÝµÄçÏÕ Ã»ÓÐÊý¾ÝÖظ´µÄçÏÕ
дÈe¶aÕűi дÈeÒ»Õűi
ûÓÐͳһµÄÔ¼ÊøÏÞÖÆ Ç¿ÖƵÄÔ¼ÊøÏÞÖÆ
MySQLÖ§³ÖRANGE£¬LIST£¬HASH£¬KEY*ÖÇøÀaÐÍ£¬ÆaÖÐÒÔRANGE×iΪ³£Óãº
*¶þ£¬ÖÇøÄÜ×oʲô**
*Èý£¬ÖÇøµÄÏÞÖÆ(½ØÖ¹5.1.44°ae**)
• Ö»ÄܶÔÊý¾Ý±iµÄÕuÐÍÁнøÐÐÖÇø£¬»oÕßÊý¾ÝÁпÉÒÔͨ¹ýÖÇøº¯Êýת»¯³ÉÕuÐÍÁÐ
• ×i´o*ÖÇøÊýÄ¿²»Äܳ¬¹ý1024
• Èç¹uº¬ÓÐΨһË÷Òý»oÕßÖ÷¼u£¬Ôo*ÖÇøÁбØÐe°uº¬ÔÚËuÓеÄΨһË÷Òý»oÕßÖ÷¼uÔÚÄÚ
• ²»Ö§³ÖÍa¼u
• ²»Ö§³ÖÈ«ÎÄË÷Òý£¨fulltext£(C)
*ËÄ£¬Ê²Ã´Ê±ºoʹÓÃÖÇø**
• º£Á¿Êý¾Ý±i
• Àuʱi¿iËٵIJeѯ£¬¿ÉÒÔ²ÉÓÃARCHIVE+PARTITIONµÄ½Ê½¡£
• Êý¾Ý±iË÷Òý´oÓÚ*þÎñÆ÷ÓÐЧÄÚ´ae
• ¶ÔÓÚ´o±i£¬ÌرðÊÇË÷ÒýÔ¶Ô¶´oÓÚþÎñÆ÷ÓÐЧÄÚ´aeʱ£¬¿ÉÒÔ²»ÓÃË÷Òý£¬´ËʱÖÇøЧÂÊ»a¸uÓÐЧ¡£
*Îa£¬ÖÇøʵÑe**
ʵÑeÒ»£º****
ʹÓà US Bureau of Transportation Statistics*¢²¼µÄÊý¾Ý(CSV¸ñʽ).Ä¿Ç°, °uÀ¨ 1.13 ÒÚÌo¼Ç¼£¬7.5 GBÊý¾Ý5.2 GBË÷Òý¡£Ê±¼a´Ó1987µ½2007¡£
þÎñÆ÷ʹÓÃ4GBÄÚ´ae£¬ÕaÑuÊý¾ÝºÍË÷ÒýµÄ´oС¶¼³¬¹ýÁËÄÚ´ae´oС¡£ÉeÖÃΪ4GBÔÒoÊÇÊý¾Ý²Ö¿a´oСԶԶ³¬¹ý¿ÉÄÜÄÚ´aeµÄ´oС£¬¿ÉÄÜ´i¼¸TB¡£¶ÔÆÕͨOLTPÊý¾Ý¿aÀ´Ëµ£¬Ë÷Òý»º´aeÔÚÄÚ´aeÖУ¬¿ÉÒÔ¿iËÙ¼iË÷¡£Èç¹uÊý¾Ý³¬³oÄÚ´ae´oС£¬ÐeҪʹÓò»Í¬µÄ½Ê½¡£
´´½¨ÓÐÖ÷¼uµÄ±i£¬ÒoΪͨ³£±i¶¼»aÓÐÖ÷¼u¡£±iµÄÖ÷¼uÌ«´oµ¼ÖÂË÷ÒýÎÞ¨¶ÁÈeÄÚ´ae£¬ÕaÑuÒ»°aÀ´Ëµ²»ÊǸßЧµÄ£¬Òaζ×ÅÒª¾³£ÃÎÊ´ÅÅÌ£¬ÃÎÊËÙ¶ÈÍeÈ«È¡¾oÓÚÄaµÄ´ÅÅ̺ʹ¦ÀiÆ÷¡£Ä¿Ç°ÔÚÉe¼ÆºÜ´oµÄÊý¾Ý²Ö¿aÀi£¬ÓÐÒ»ÖÖÆÕ±eµÄ×o¨ÊDz»Ê¹ÓÃË÷Òý¡£ËuÒÔÒ²»a±È½ÏÓкÍûÓÐÖ÷¼uµÄÐÔÄÜ¡£
²aÊÔ½¨£º
ʹÓÃÈýÖÖÊý¾ÝÒýe¢MyISAM, InnoDB, Archive.
¶ÔÓÚÿһÖÖÒýe¢, ´´½¨Ò»¸o´øÖ÷¼uµÄδÖÇø±i (³ýÁËarchive) ºÍÁ½¸oÖÇø±i£¬Ò»¸o°´ÔÂÒ»¸o°´Äe¡£ÖÇø±iÖÇø*½Ê½ÈçÏ£º
CREATE TABLE by_year (
d DATE
)
PARTITION BY RANGE (YEAR(d))
(
PARTITION P1 VALUES LESS THAN (2001),
PARTITION P2 VALUES LESS THAN (2002),
PARTITION P3 VALUES LESS THAN (2003),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
CREATE TABLE by_month (
d DATE
)
PARTITION BY RANGE (TO_DAYS(d))
(
PARTITION P1 VALUES LESS THAN (to_days(¡®2001-02-01¡a)), ¡ª January
PARTITION P2 VALUES LESS THAN (to_days(¡®2001-03-01¡a)), ¡ª February
PARTITION P3 VALUES LESS THAN (to_days(¡®2001-04-01¡a)), ¡ª March
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
ÿһ¸o¶¼ÔÚ mysqlþÎñÆ÷Éϵĵ¥¶ÀµÄʵÀýÉϲaÊÔ, ÿʵÀýÖ»ÓÐÒ»¸o¿aÒ»¸o±i¡£Ã¿ÖÖÒýe¢, ¶¼»aÆo¶¯þÎñ, ÔËÐвeѯ²¢¼Ç¼½a¹u, È»ºo¹Ø±ÕþÎñ¡£þÎñʵÀýͨ¹ýMySQL Sandbox´´½¨¡£
¼ÓÔØÊý¾ÝµÄÇe¿oÈçÏ£º
ID Òýe¢ ÊÇñÖÇø Êý¾Ý ´oС ±¸×¢ *¼ÓÔØʱ¼a ()**
1 MyISAM none 1.13ÒÚ 13 GB with PK 37 min
2 MyISAM by month 1.13ÒÚ 8 GB without PK 19 min
3 MyISAM by year 1.13ÒÚ 8 GB without PK 18 min
4 InnoDB none 1.13ÒÚ 16 GB with PK 63 min
5 InnoDB by month 1.13ÒÚ 10 GB without PK 59 min
6 InnoDB by year 1.13ÒÚ 10 GB without PK 57 min
7 Archive none 1.13ÒÚ 1.8 GB no keys 20 min
8 Archive by month 1.13ÒÚ 1.8 GB no keys 21 min
9 Archive by year 1.13ÒÚ 1.8 GB no keys 20 min
ÔÚdual-XeonþÎñÆ÷ÉÏ
ΪÁ˶ԱÈ*ÖÇøÔÚ´oµÄºÍСµÄÊý¾Ý¼¯ÉϵÄЧ¹u£¬´´½¨ÁËÁiÍa9¸oʵÀý£¬Ã¿Ò»¸o°uº¬ÂÔСÓÚ2GBµÄÊý¾Ý¡£
²eѯÓi¾aÓÐÁ½ÖÖ
SELECT COUNT(*)
FROM table_name
WHERE date_column BETWEEN start_date and end_date
SELECT column_list
FROM table_name
WHERE column1 = x and column2 = y and column3 = z
¶ÔÓÚµÚÒ»ÖÖ²eѯ£¬´´½¨²»Í¬µÄÈÕÆڶΧµÄÓi¾a¡£¶ÔÓÚÿһ¸o¶Î§£¬´´½¨Ò»×e¶iÍaµÄÏaͬ¶Î§ÈÕÆڵIJeѯ¡£Ã¿¸oÈÕÆڶΧµÄµÚÒ»¸o²eѯÊÇÀa²eѯ£¬Òaζ×ÅÊǵÚÒ»´ÎÃuÖУ¬ËaeºoµÄÔÚͬÑu¶Î§ÄڵIJeѯÊÇů²eѯ£¬Òaζ×ÅÖÁÉÙ²¿Ö±»»º´ae¡£²eѯÓi¾aÔÚthe ForgeÉÏ¡£
½a¹u£º
1**´øÖ÷¼uµÄ*ÖÇø±i**
µÚÒ»¸o²aÊÔʹÓø´ºÏÖ÷¼u£¬¾ÍÏñÔʼÊý¾Ý±iʹÓõÄÒ»Ñu¡£Ö÷¼uË÷ÒýÎļþ´iµ½5.5 GB. ¿ÉÒÔ¿´³o£¬ÖÇø²»½oûÓÐÌa¸ßÐÔÄÜ£¬Ö÷¼u»¹¼o»ºÁ˲Ù×÷¡£ÒoΪÈç¹uʹÓÃÖ÷¼uË÷Òý²eѯ£¬¶øË÷ÒýÓÖ²»ÄܶÁÈeÄÚ´ae£¬Ôo±iÏֺܲi¡£ÌaʾÎÒÃÇÖÇøºÜÓÐÓ㬵«ÊDZØÐeʹÓõõ±¡£
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+
| ״̬ | myisam ²»ÖÇø | myisam ÔÂÖÇø | myisam Äe*ÖÇø |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+
| cold | 2.6574570285714 | 2.9169642 | 3.0373419714286 |
| warm | 2.5720722571429 | 3.1249698285714 | 3.1294000571429 |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+
ARCHIVEÒýe¢
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+
| ״̬ | archive²»ÖÇø | archiveÔÂÖÇø| archiveÄe*ÖÇø |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+
| cold | 249.849563 | 1.2436211111111 | 12.632532527778 |
| warm | 235.814442 | 1.0889786388889 | 12.600520777778 |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¨C+
×¢ÒaARCHIVEÒýe¢ÔÂ*ÖÇøµÄÏiӦʱ¼a±ÈʹÓÃMyISAMºÃ¡£
2**²»´øÖ÷¼uµÄ*ÖÇø±i**
ÒoΪÈç¹uÖ÷¼uµÄ´oС³¬³oÁË¿ÉÓõÄkey buffer£¬ÉoÖÁÈ«²¿ÄÚ´ae£¬ËuÓÐʹÓÃÖ÷¼uµÄ²eѯ¶¼»aʹÓôÅÅÌ¡£ÐµĽʽֻʹÓÃÖÇø£¬²»ÒªÖ÷¼u¡£ÐÔÄÜÓÐÏÔÖøµÄÌa¸ß¡£
°´ÔÂ*ÖÇø±iµÃµ½ÁË70%-90%µÄÐÔÄÜÌa¸ß¡£
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª¡ª+
| ״̬ | myisam ²»ÖÇø | myisam ÔÂÖÇø | myisam Äe*ÖÇø |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª¡ª+
| cold | 2.6864490285714 | 0.64206445714286 | 2.6343286285714 |
| warm | 2.8157905714286 | 0.18774977142857 | 2.2084743714286 |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª¡ª+¡ª¡ª¡ª¡ª¡ª¡ª+
ΪÁËʹÇø±ð¸uÃ÷ÏÔ, ÎÒʹÓÃÁËÁ½¸o´o¹aeÄ£²eѯ£¬¿ÉÒÔÀuÓÃÖÇøµÄÖÇøÏu³ý¹¦ÄÜ¡£
SELECT year(FlightDate) as y, count(*)
FROM flightstats
WHERE FlightDate BETWEEN ¡°2001-01-01¡a and ¡°2003-12-31¡a
GROUP BY y
SELECT date_format(FlightDate,¡±%Y-%m¡±) as m, count(*)
FROM flightstats
WHERE FlightDate BETWEEN ¡°2001-01-01¡a and ¡°2003-12-31¡a
GROUP BY m
½a¹uÏÔʾ°´ÔÂÖÇø±iÓÐ30%-60%£¬°´ÄeÖÇø±iÓÐ15%-30%ÐÔÄÜÌaÉý¡£
+¡ª¡ª¡ª-+¡ª¡ª¡ª¨C+¡ª¡ª¡ª¨C+¡ª¡ª¡ª¨C+
| query_id | ²»Ö | ÔÂÖ | Äe*Ö |
+¡ª¡ª¡ª-+¡ª¡ª¡ª¨C+¡ª¡ª¡ª¨C+¡ª¡ª¡ª¨C+
| 1 | 97.779958 | 36.296519 | 82.327554 |
| 2 | 69.61055 | 47.644986 | 47.60223 |
+¡ª¡ª¡ª-+¡ª¡ª¡ª¨C+¡ª¡ª¡ª¨C+¡ª¡ª¡ª¨C+
´¦ÀiÆ÷ÒoËØ
µ±ÒÔÉϲaÊÔÔÚ¼ÒÓûu(Intel Dual Core 2.3 MHz CPU)ÉϲaÊÔµÄʱºo¡£¶ÔÓÚÔÀ´µÄ¶ÔÓÚdual Xeon 2.66 MHzÀ´Ëµ£¬¢ÏÖÐÂþÎñÆ÷¸u¿i£¡¡£
Öظ´ÉÏÃaeµÄ²aÊÔ£¬ÁiÈ˳Ծª:
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¨C+
|״̬ | myisam ²»ÖÇø |myisam ÔÂÖÇø| myisam Äe*ÖÇø |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¨C+
| cold | 0.051063428571429 | 0.6577062 | 1.6663527428571 |
| warm | 0.063645485714286 | 0.1093724 | 1.2369152285714 |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¨C+
myisam ²»ÖÇø´øÖ÷¼uµÄ±i±ÈÖÇø±i¸u¿i. ÖÇø±iµÄ±iÏÖºÍÔÀ´Ò»Ñu£¬µ«Î´ÖÇø±iÐÔÄÜÌa¸ßÁË£¬Ê¹µÃÖÇøÏԵò»±ØÒª¡£¼ÈÈ»ÕąþÎñÆ÷Ëƺo³aÖÀuÓÃÁËË÷ÒýµÄºÃ´¦£¬ÎÒÔÚÖÇø±iµÄ*ÖÇøÁÐÉϼÓÈeÁËË÷Òý¡£
create table flightstats (
AirlineID int not null,
UniqueCarrier char(3) not null,
Carrier char(3) not null,
FlightDate date not null,
FlightNum char(5) not null,
TailNum char(8) not null,
ArrDelay double not null,
ArrTime datetime not null,
DepDelay double not null,
DepTime datetime not null,
Origin char(3) not null,
Dest char(3) not null,
Distance int not null,
Cancelled char(1) default ¡®n',
primary key (FlightDate, AirlineID, Carrier, UniqueCarrier, FlightNum, Origin, DepTime, Dest)
)
create table flightstats (
AirlineID int not null,
UniqueCarrier char(3) not null,
Carrier char(3) not null,
FlightDate date not null,
FlightNum char(5) not null,
TailNum char(8) not null,
ArrDelay double not null,
ArrTime datetime not null,
DepDelay double not null,
DepTime datetime not null,
Origin char(3) not null,
Dest char(3) not null,
Distance int not null,
Cancelled char(1) default ¡®n',
KEY (FlightDate)
)
PARTITION BY RANGE ¡
½a¹uÊÇÈÃÈËÂuÒaµÄ,µÃµ½35% ÐÔÄÜÌa¸ß¡£
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¡ª-+
|״̬ | myisam ²»ÖÇø |myisam ÔÂÖÇø | myisam Äe*ÖÇø |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¡ª-+
| cold | 0.075289714285714 | 0.025491685714286 | 0.072398542857143 |
| warm | 0.064401257142857 | 0.031563085714286 | 0.056638085714286 |
+¡ª¡ª¨C+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¡ª-+¡ª¡ª¡ª¡ª¡ª¡ª-+
½aÂÛ£º
1. **ʹÓñi*ÖÇø²¢²»ÊÇÐÔÄÜÌa¸ßµÄ±£Ö¤¡£ËuÒÀÀµÓÚÒÔÏÂÒoËØ£º**
2. **ÔÚÓ¦Óõ½Éu²uϵͳǰÔËÐлu×¼²aÊÔºÍÐÔÄܲaÊÔ**
ÒÀÀµÓÚÄaµÄÊý¾Ý¿aµÄÓÃ;£¬Äa¿ÉÄܵõ½¾Þ´oµÄÐÔÄÜÌa¸ßÒ²¿ÉÄÜÒ»ÎÞËu»ñ¡£Èç¹u²»Ð¡ÐÄ£¬ÉoÖÁÓпÉÄÜ»a½µµÍÐÔÄÜ¡£
±ÈÈ磺һ¸oʹÓÃÔÂÖÇøµÄ±i£¬ÔÚ×ÜÊǽøÐÐÈÕÆڶΧ²eѯʱ¿ÉÒԵõ½¼«ÓŵÄËٶȡ£µ«Èç¹uûÓÐÈÕÆÚ²eѯ£¬ÄÇô»a½øÐÐÈ«±iɨÃe¡£
ÖÇø¶ÔÓÚº£Á¿Êý¾ÝÐÔÄÜÌa¸ßÊÇÒ»¸o¹Ø¼uµÄ¹¤¾ß¡£Ê²Ã´²ÅÊǺ£Á¿µÄÊý¾ÝÈ¡¾oÓÚ²¿ÊðµÄÓ²¼þ¡£Ã¤Ä¿Ê¹ÓÃÖÇø²»Äܱ£Ö¤Ìa¸ßÐÔÄÜ£¬µ«ÊÇÔÚÇ°ÆÚ»u×¼²aÊÔºÍÐÔÄܲaÊԵİiÖuÏ£¬¿ÉÒÔ³ÉΪÍeÃÀµÄ½a¾o*½°¸¡£
3. **Archive *±i¿ÉÒÔ³ÉΪһ¸oºÜºÃµÄÕÛÖÔ½°¸
Archive ±iÖÇøºo¿ÉÒԵõ½¾Þ´oµÄÐÔÄÜÌa¸ß¡£µ±È»Ò²ÒÀÀµÓÚÄaµÄÓÃ;£¬Ã»ÓÐÖÇøʱÈκβeѯ¶¼ÊÇÈ«±iɨÃe¡£Èç¹uÄaÓв»ÐeÒª±a¸uµÄÀuÊÊý¾Ý£¬»¹Òª½øÐа´Ê±¼aµÄÖÎoͳ¼Æ£¬Ê¹ÓÃArchiveÒýe¢ÊǼ«¼ÑµÄÑ¡Ôñ¡£Ëu»aʹÓÃ10-20£¥µÄÔ¿Õ¼a£¬¶ÔÓÚ¾Û¼¯²eѯÓбÈMyISAM /InnoDB±i¸uºÃµÄÐÔÄÜ¡£
ËaȻһ¸oºÜºÃµÄÓÅ»¯µÄ*ÖÇøMyISAM ±iÐÔÄÜ¿ÉÄܺÃÓÚ¶ÔÓ¦µÄArchive±i, µ«ÊÇÐeÒª10±¶µÄ¿Õ¼a¡£
ʵÑe¶þ£º****
1.½¨Á½¸o±i£¬Ò»¸o°´Ê±¼a×Ö¶ÎÖÇø£¬Ò»¸o²»ÖÇø¡£
CREATE TABLE part_tab
(
c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL
) engine=myisam
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );
create table no_part_tab
(c1 int(11) default NULL,
c2 varchar(30) default NULL,
c3 date default NULL) engine=myisam;
2.½¨Ò»¸o´ae´¢¹ý³Ì, ÀuÓøùý³ÌÏoÁ½¸o±i²aÈe¸÷8°ÙÍoÌo²»Í¬Êý¾Ý¡£
delimiter //
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 8000000
do
insert into part_tabvalues (v,'testing partitions',adddate(¡®1995-01-01¡a,(rand(v)*36520) mod 3652));
set v = v + 1;
end while;end
//
È»ºoÖ´ÐÐ
mysql> delimiter ;
mysql> call load_part_tab();
Query OK, 1 row affected (8 min 17.75 sec)
mysql> insert into no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
3.¿ªÊ¼¶ÔÕaÁ½±iÖеÄÊý¾Ý½øÐмoµ¥µÄ*¶Î§²eѯ°É¡£²¢ÏÔʾִÐйý³Ì½aÎo£º
mysql> select count(*) from no_part_tab where c3 > date ¡®1995-01-01¡a and c3 < date ¡®1995-12-31¡a;
+¡ª¡ª¡ª-+
| count(*) |
+¡ª¡ª¡ª-+
| 795181 |
+¡ª¡ª¡ª-+
1 row in set (38.30 sec)
mysql> select count(*) from part_tab where c3 > date ¡®1995-01-01¡a and c3 < date ¡®1995-12-31¡a;
+¡ª¡ª¡ª-+
| count(*) |
+¡ª¡ª¡ª-+
| 795181 |
+¡ª¡ª¡ª-+
1 row in set (3.88 sec)
mysql> explain select count(*) from no_part_tab where c3 > date ¡®1995-01-01¡a and c3 < date ¡®1995-12-31¡a\G
1. row
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALLpossible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where1 row in set (0.00 sec)
mysql> explain partitions select count(*) from part_tab where
-> c3 > date ¡®1995-01-01¡a and c3 < date ¡®1995-12-31¡a\G1. row
id: 1select_type: SIMPLE
table: part_tabpartitions: p1
type: ALLpossible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where1 row in set (0.00 sec)
´ÓÉÏÃae½a¹u¿ÉÒÔ¿´³o£¬Ê¹ÓñiÖÇø±ÈÇÖÇøµÄ¼oÉÙ90£¥µÄÏiӦʱ¼a¡£ÃuÁi½aÎoExplain³ÌÐo¿ÉÒÔ¿´³oÔÚ¶ÔÒÑÖÇøµÄ±iµÄ²eѯ¹ý³ÌÖнo¶ÔµÚÒ»¸o*ÖÇø½øÐÐÁËɨÃe£¬ÆaÓaÌø¹ý¡£½øÒ»²½²aÊÔ£º
¨C Ôo¼ÓÈÕÆÚ*¶Î§
mysql> select count(*) from no_part_tab where c3 > date ¡®-01-01¡aand c3 < date ¡®1997-12-31¡a;
+¡ª¡ª¡ª-+
| count(*) |
+¡ª¡ª¡ª-+
| 2396524 |
+¡ª¡ª¡ª-+
1 row in set (5.42 sec)
mysql> select count(*) from part_tab where c3 > date ¡®-01-01¡aand c3 < date ¡®1997-12-31¡a;
+¡ª¡ª¡ª-+
| count(*) |
+¡ª¡ª¡ª-+
| 2396524 |
+¡ª¡ª¡ª-+
1 row in set (2.63 sec)
¨C Ôo¼ÓδË÷Òý×ֶβeѯ
mysql> select count(*) from part_tab where c3 > date ¡®-01-01¡aand c3 < date
¡®1996-12-31¡a and c2='hello';
+¡ª¡ª¡ª-+
| count(*) |
+¡ª¡ª¡ª-+
| 0 |
+¡ª¡ª¡ª-+
1 row in set (0.75 sec)
mysql> select count(*) from no_part_tab where c3 > date ¡®-01-01¡aand c3 < da
te ¡®1996-12-31¡a and c2='hello';
+¡ª¡ª¡ª-+
| count(*) |
+¡ª¡ª¡ª-+
| 0 |
+¡ª¡ª¡ª-+
1 row in set (11.52 sec)
½aÂÛ£º