如题所述
使ç¨ç´¢å¼æé«æ¥è¯¢é度
1.åè¨
å¨webå¼åä¸ï¼é¡µé¢æ¨¡æ¿ï¼ä¸å¡é»è¾(å æ¬ç¼åãè¿æ¥æ± )åæ°æ®åºè¿ä¸ä¸ªé¨åï¼æ°æ®åºå¨å ¶ä¸è´è´£æ§è¡SQLæ¥è¯¢å¹¶è¿åæ¥è¯¢ç»æï¼æ¯å½±åç½ç«é度æéè¦çæ§è½ç¶é¢ãæ¬æ主è¦é对MySqlæ°æ®åºï¼ååä¸ççµå大æï¼å¼åäºæ·å®ææ¯çè®®ï¼èæ·å®ç°å¨å»IOE(I代表IBMç缩åï¼å³å»IBMçåå¨è®¾å¤åå°åæº;Oæ¯ä»£è¡¨Oracleç缩åï¼ä¹å³å»Oracleæ°æ®åºï¼éç¨MySQLåHadoopæ¿ä»£ç解å³æ¹æ¡ï¼;Eæ¯ä»£è¡¨EMC2,å³å»EMC2ç设å¤æ§,ç¨PC
Serveræ¿ä»£EMC2)ï¼å¤§ééç¨MySqlé群ï¼è®©MySqlå次æ为èç¼çææï¼èä¼åæ°æ®çéè¦ä¸æ¥å°±æ¯ç´¢å¼ç建ç«ï¼å¯¹äºmysqlä¸åºç°çæ ¢æ¥è¯¢ï¼æ们å¯ä»¥éè¿ä½¿ç¨ç´¢å¼æ¥æåæ¥è¯¢é度ãç´¢å¼ç¨äºå¿«éæ¾åºå¨æ个åä¸æä¸ç¹å®å¼çè¡ãä¸ä½¿ç¨ç´¢å¼ï¼MySQLå°è¿è¡å ¨è¡¨æ«æï¼ä»ç¬¬1æ¡è®°å½å¼å§ç¶å读å®æ´ä¸ªè¡¨ç´å°æ¾åºç¸å ³çè¡ã
2.mysqlç´¢å¼ç±»ååå建
常ç¨çç´¢å¼ç±»åæ
(1)主é®ç´¢å¼
å®æ¯ä¸ç§ç¹æ®çå¯ä¸ç´¢å¼ï¼ä¸å 许æ空å¼ãä¸è¬æ¯å¨å»ºè¡¨çæ¶ååæ¶å建主é®ç´¢å¼ï¼
å¤å¶ä»£ç
代ç å¦ä¸:
CREATE TABLE user(
id int unsigned not
null auto_increment,
name varchar(50) not null,
email varchar(40) not
null,
primary key (id)
);
(2)æ®éç´¢å¼
è¿æ¯æåºæ¬çç´¢å¼ï¼å®æ²¡æä»»ä½éå¶ãå建æ¹å¼ï¼
å¤å¶ä»£ç
代ç å¦ä¸:
create index idx_name on
user(
name(20)
);
mysqlæ¯æåç¼ç´¢å¼ï¼ä¸è¬å§åä¸ä¼è¶ è¿20个å符ï¼æ以æ们è¿é建ç«ç´¢å¼çæ¶åéå®äºé¿åº¦20ï¼è¿æ ·å¯ä»¥èçç´¢å¼æ件大å°
(3)å¯ä¸ç´¢å¼
å®ä¸åé¢çæ®éç´¢å¼ç±»ä¼¼ï¼ä¸åçå°±æ¯ï¼ç´¢å¼åçå¼å¿ é¡»å¯ä¸ï¼ä½å 许æ空å¼ãå¦ææ¯ç»åç´¢å¼ï¼ååå¼çç»åå¿ é¡»å¯ä¸ãå建æ¹å¼ï¼
å¤å¶ä»£ç
代ç å¦ä¸:
CREATE UNIQUE INDEX idx_email ON
user(
email
);
(4)å ¨æç´¢å¼
MySQLæ¯æå ¨æç´¢å¼åæç´¢åè½ãMySQLä¸çå ¨æç´¢å¼ç±»å为FULLTEXTçç´¢å¼ã
FULLTEXT ç´¢å¼ä» å¯ç¨äº MyISAM表ï¼
å¤å¶ä»£ç
代ç å¦ä¸:
CREATE TABLE articles (
id INT
UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);
mysql> SELECT *
FROM articles WHERE MATCH (title,body) AGAINST ('database');
æ¥è¯¢ç»æï¼
+----+-------------------+------------------------------------------+
|
id | title | body
|
+----+-------------------+------------------------------------------+
|
5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 |
MySQL Tutorial | DBMS stands for DataBase ...
|
+----+-------------------+------------------------------------------+
2 rows in set (0.00
sec)
MATCH()å½æ°å¯¹äºä¸ä¸ªå符串æ§è¡èµæåºå çèªç¶è¯è¨æç´¢ãä¸ä¸ªèµæåºå°±æ¯1å¥1个æ2个å å«å¨FULLTEXTå çåãæç´¢å符串ä½ä¸ºå¯¹AGAINST()çåæ°è被ç»å®ã对äºè¡¨ä¸çæ¯ä¸è¡,
MATCH() è¿åä¸ä¸ªç¸å ³å¼ï¼å³, æç´¢å符串å
MATCH()表ä¸æå®åä¸è¯¥è¡æåä¹é´çä¸ä¸ªç¸ä¼¼æ§åº¦éã
(5)å¤åç´¢å¼
å¤å¶ä»£ç
代ç å¦ä¸:
CREATE TABLE test (
id INT NOT
NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT
NULL,
PRIMARY KEY (id),
INDEX name
(last_name,first_name)
);
nameç´¢å¼æ¯ä¸ä¸ªå¯¹last_nameåfirst_nameçç´¢å¼ãç´¢å¼å¯ä»¥ç¨äºä¸ºlast_nameï¼æè 为last_nameåfirst_nameå¨å·²ç¥èå´å æå®å¼çæ¥è¯¢ãå æ¤ï¼nameç´¢å¼ç¨äºä¸é¢çæ¥è¯¢ï¼
SELECT
* FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE
last_name='Widenius' AND first_name='Michael';
ä½æ¯ä¸è½ç¨äºSELECT * FROM test WHERE
first_name='Michael';è¿æ¯å 为MySQLç»åç´¢å¼ä¸ºâæå·¦åç¼âçç»æ,ç®åçç解就æ¯åªä»æå·¦é¢çå¼å§ç»åã
3.å¨ä»ä¹æ åµä¸ä½¿ç¨ç´¢å¼
(1)为æç´¢å段建索å¼,å¦æå¨ä½ ç表ä¸ï¼æ个åæ®µä½ ç»å¸¸ç¨æ¥åæç´¢ï¼é£ä¹ï¼è¯·ä¸ºå ¶å»ºç«ç´¢å¼å§ãä¸è¬æ¥è¯´ï¼å¨WHEREåJOINä¸åºç°çåéè¦å»ºç«ç´¢å¼ä»¥æé«æ¥è¯¢é度ã
ä¾å¦ä»fps表ï¼è¡¨ä¸ænameå段ï¼ä¸æ£ç´¢å§å为"ææ¦"ç人ï¼
ä¸é¢ç¨explainæ¥è§£éæ§è¡å»ºç«ç´¢å¼åæªå»ºç«ç´¢å¼çåºå«ï¼
a.æªå»ºç«ç´¢å¼å
å¤å¶ä»£ç
代ç å¦ä¸:
explain select name from fps where
name="ææ¦";
[SQL] select name from fps where name="ææ¦";
å½±åçæ°æ®æ :
0
æ¶é´: 0.003ms
b.建ç«ç´¢å¼å
å¤å¶ä»£ç
代ç å¦ä¸:
create index idx_name on
fps(
name
);
explain select name from fps where
name="ææ¦";
[SQL] select name from fps where name="ææ¦";
å½±åçæ°æ®æ :
0
æ¶é´: 0.001ms
(2)ä¸é¢æ们就æ¥ççè¿ä¸ªEXPLAINåæç»æçå«ä¹ã
tableï¼è¿æ¯è¡¨çååã
typeï¼è¿æ¥æä½çç±»åãä¸é¢æ¯MySQLææ¡£å ³äºrefè¿æ¥ç±»åç说æï¼
â对äºæ¯ä¸ªæ¥èªäºåé¢ç表çè¡ç»åï¼æææå¹é ç´¢å¼å¼çè¡å°ä»è¿å¼ 表ä¸è¯»åãå¦æèæ¥åªä½¿ç¨é®çæ左边çåç¼ï¼æå¦æé®ä¸æ¯
UNIQUEæPRIMARY
KEYï¼æ¢å¥è¯è¯´ï¼å¦æèæ¥ä¸è½åºäºå ³é®åéæ©å个è¡çè¯ï¼ï¼å使ç¨refãå¦æ使ç¨çé®ä» ä» å¹é å°éè¡ï¼è¯¥èæ¥
ç±»åæ¯ä¸éçãâ
å¨æ¬ä¾ä¸ï¼ç±äºç´¢å¼ä¸æ¯UNIQUEç±»åï¼refæ¯æ们è½å¤å¾å°çæ好è¿æ¥ç±»åã
å¦æEXPLAINæ¾ç¤ºè¿æ¥ç±»åæ¯âALLâï¼èä¸ä½ 并ä¸æ³ä»è¡¨éé¢éæ©åºå¤§å¤æ°è®°å½ï¼é£ä¹MySQLçæä½æçå°é常ä½ï¼å 为å®è¦æ«ææ´ä¸ªè¡¨ãä½ å¯ä»¥å å ¥æ´å¤çç´¢å¼æ¥è§£å³è¿ä¸ªé®é¢ãé¢ç¥æ´å¤ä¿¡æ¯ï¼è¯·åè§MySQLçæå说æã
possible_keysï¼
å¯è½å¯ä»¥å©ç¨çç´¢å¼çååãè¿éçç´¢å¼ååæ¯å建索å¼æ¶æå®çç´¢å¼æµç§°ï¼å¦æç´¢å¼æ²¡ææµç§°ï¼åé»è®¤æ¾ç¤ºçæ¯ç´¢å¼ä¸ç¬¬ä¸ä¸ªåçåå
ï¼å¨æ¬ä¾ä¸ï¼å®æ¯âidx_nameâï¼ã
Keyï¼
å®æ¾ç¤ºäºMySQLå®é 使ç¨çç´¢å¼çååãå¦æå®ä¸ºç©ºï¼æNULLï¼ï¼åMySQLä¸ä½¿ç¨ç´¢å¼ã
key_lenï¼
ç´¢å¼ä¸è¢«ä½¿ç¨é¨åçé¿åº¦ï¼ä»¥åè计ã
refï¼
å®æ¾ç¤ºçæ¯åçååï¼æåè¯âconstâï¼ï¼MySQLå°æ ¹æ®è¿äºåæ¥éæ©è¡ãå¨æ¬ä¾ä¸ï¼MySQLæ ¹æ®ä¸ä¸ªå¸¸ééæ©è¡ã
rowsï¼
MySQLæ认为çå®å¨æ¾å°æ£ç¡®çç»æä¹åå¿ é¡»æ«æçè®°å½æ°ãæ¾ç¶ï¼è¿éæçæ³çæ°åå°±æ¯1ã
æ¬ä¾ä¸æªç´¢å¼åéåçè®°å½æ°ä¸º1041ï¼è建ç«ç´¢å¼å为1
Extraï¼
è¿éå¯è½åºç°è®¸å¤ä¸åçé项ï¼å ¶ä¸å¤§å¤æ°å°å¯¹æ¥è¯¢äº§çè´é¢å½±åãå¨æ¬ä¾ä¸ï¼MySQLåªæ¯æéæ们å®å°ç¨using
whereï¼using indexåå¥éå¶æç´¢ç»æéã
4.æ常ç¨çåå¨å¼æï¼
(1)Myisamåå¨å¼æï¼æ¯ä¸ªMyisamå¨ç£çä¸åå¨æä¸ä¸ªæ件ãæ件åé½å表åç¸åï¼æ©å±ååå«ä¸º.frmï¼åå¨è¡¨å®ä¹ï¼ã.MYDï¼åå¨æ°æ®ï¼ã.MYIï¼åå¨ç´¢å¼ï¼ãæ°æ®æ件åç´¢å¼æ件å¯ä»¥æ¾ç½®å¨ä¸åç®å½ï¼å¹³ååå¸ioï¼è·å¾æ´å¿«çé度ã对åå¨å¤§å°æ²¡æéå¶ï¼MySQLæ°æ®åºçæ大ææ表尺寸é常æ¯ç±æä½ç³»ç»å¯¹æ件大å°çéå¶å³å®ç,
(2)InnoDBåå¨å¼æï¼å ·ææ交ãåæ»ãå¥æºæ¢å¤è½åçäºå¡å®å ¨ãä¸Myisamç¸æ¯ï¼InnoDBçåæçå·®ä¸äºå¹¶ä¸ä¼å ç¨æ´å¤çç£ç空é´ä»¥ä¿çæ°æ®åç´¢å¼ã
(3)å¦ä½éæ©åéçå¼æ
ä¸é¢æ¯å¸¸ç¨åå¨å¼æéç¨çç¯å¢ï¼
Myisamï¼å®æ¯å¨Webãæ°æ®ä»å¨åå ¶ä»åºç¨ç¯å¢ä¸æ常使ç¨çåå¨å¼æ;
InnoDBï¼ç¨äºäºå¡å¤çåºç¨ç¨åºï¼å ·ææ´å¤ç¹æ§ï¼å æ¬ACIDäºå¡ç¹æ§ã
1.åè¨
å¨webå¼åä¸ï¼é¡µé¢æ¨¡æ¿ï¼ä¸å¡é»è¾(å æ¬ç¼åãè¿æ¥æ± )åæ°æ®åºè¿ä¸ä¸ªé¨åï¼æ°æ®åºå¨å ¶ä¸è´è´£æ§è¡SQLæ¥è¯¢å¹¶è¿åæ¥è¯¢ç»æï¼æ¯å½±åç½ç«é度æéè¦çæ§è½ç¶é¢ãæ¬æ主è¦é对MySqlæ°æ®åºï¼ååä¸ççµå大æï¼å¼åäºæ·å®ææ¯çè®®ï¼èæ·å®ç°å¨å»IOE(I代表IBMç缩åï¼å³å»IBMçåå¨è®¾å¤åå°åæº;Oæ¯ä»£è¡¨Oracleç缩åï¼ä¹å³å»Oracleæ°æ®åºï¼éç¨MySQLåHadoopæ¿ä»£ç解å³æ¹æ¡ï¼;Eæ¯ä»£è¡¨EMC2,å³å»EMC2ç设å¤æ§,ç¨PC
Serveræ¿ä»£EMC2)ï¼å¤§ééç¨MySqlé群ï¼è®©MySqlå次æ为èç¼çææï¼èä¼åæ°æ®çéè¦ä¸æ¥å°±æ¯ç´¢å¼ç建ç«ï¼å¯¹äºmysqlä¸åºç°çæ ¢æ¥è¯¢ï¼æ们å¯ä»¥éè¿ä½¿ç¨ç´¢å¼æ¥æåæ¥è¯¢é度ãç´¢å¼ç¨äºå¿«éæ¾åºå¨æ个åä¸æä¸ç¹å®å¼çè¡ãä¸ä½¿ç¨ç´¢å¼ï¼MySQLå°è¿è¡å ¨è¡¨æ«æï¼ä»ç¬¬1æ¡è®°å½å¼å§ç¶å读å®æ´ä¸ªè¡¨ç´å°æ¾åºç¸å ³çè¡ã
2.mysqlç´¢å¼ç±»ååå建
常ç¨çç´¢å¼ç±»åæ
(1)主é®ç´¢å¼
å®æ¯ä¸ç§ç¹æ®çå¯ä¸ç´¢å¼ï¼ä¸å 许æ空å¼ãä¸è¬æ¯å¨å»ºè¡¨çæ¶ååæ¶å建主é®ç´¢å¼ï¼
å¤å¶ä»£ç
代ç å¦ä¸:
CREATE TABLE user(
id int unsigned not
null auto_increment,
name varchar(50) not null,
email varchar(40) not
null,
primary key (id)
);
(2)æ®éç´¢å¼
è¿æ¯æåºæ¬çç´¢å¼ï¼å®æ²¡æä»»ä½éå¶ãå建æ¹å¼ï¼
å¤å¶ä»£ç
代ç å¦ä¸:
create index idx_name on
user(
name(20)
);
mysqlæ¯æåç¼ç´¢å¼ï¼ä¸è¬å§åä¸ä¼è¶ è¿20个å符ï¼æ以æ们è¿é建ç«ç´¢å¼çæ¶åéå®äºé¿åº¦20ï¼è¿æ ·å¯ä»¥èçç´¢å¼æ件大å°
(3)å¯ä¸ç´¢å¼
å®ä¸åé¢çæ®éç´¢å¼ç±»ä¼¼ï¼ä¸åçå°±æ¯ï¼ç´¢å¼åçå¼å¿ é¡»å¯ä¸ï¼ä½å 许æ空å¼ãå¦ææ¯ç»åç´¢å¼ï¼ååå¼çç»åå¿ é¡»å¯ä¸ãå建æ¹å¼ï¼
å¤å¶ä»£ç
代ç å¦ä¸:
CREATE UNIQUE INDEX idx_email ON
user(
);
(4)å ¨æç´¢å¼
MySQLæ¯æå ¨æç´¢å¼åæç´¢åè½ãMySQLä¸çå ¨æç´¢å¼ç±»å为FULLTEXTçç´¢å¼ã
FULLTEXT ç´¢å¼ä» å¯ç¨äº MyISAM表ï¼
å¤å¶ä»£ç
代ç å¦ä¸:
CREATE TABLE articles (
id INT
UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);
mysql> SELECT *
FROM articles WHERE MATCH (title,body) AGAINST ('database');
æ¥è¯¢ç»æï¼
+----+-------------------+------------------------------------------+
|
id | title | body
|
+----+-------------------+------------------------------------------+
|
5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 |
MySQL Tutorial | DBMS stands for DataBase ...
|
+----+-------------------+------------------------------------------+
2 rows in set (0.00
sec)
MATCH()å½æ°å¯¹äºä¸ä¸ªå符串æ§è¡èµæåºå çèªç¶è¯è¨æç´¢ãä¸ä¸ªèµæåºå°±æ¯1å¥1个æ2个å å«å¨FULLTEXTå çåãæç´¢å符串ä½ä¸ºå¯¹AGAINST()çåæ°è被ç»å®ã对äºè¡¨ä¸çæ¯ä¸è¡,
MATCH() è¿åä¸ä¸ªç¸å ³å¼ï¼å³, æç´¢å符串å
MATCH()表ä¸æå®åä¸è¯¥è¡æåä¹é´çä¸ä¸ªç¸ä¼¼æ§åº¦éã
(5)å¤åç´¢å¼
å¤å¶ä»£ç
代ç å¦ä¸:
CREATE TABLE test (
id INT NOT
NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT
NULL,
PRIMARY KEY (id),
INDEX name
(last_name,first_name)
);
nameç´¢å¼æ¯ä¸ä¸ªå¯¹last_nameåfirst_nameçç´¢å¼ãç´¢å¼å¯ä»¥ç¨äºä¸ºlast_nameï¼æè 为last_nameåfirst_nameå¨å·²ç¥èå´å æå®å¼çæ¥è¯¢ãå æ¤ï¼nameç´¢å¼ç¨äºä¸é¢çæ¥è¯¢ï¼
SELECT
* FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE
last_name='Widenius' AND first_name='Michael';
ä½æ¯ä¸è½ç¨äºSELECT * FROM test WHERE
first_name='Michael';è¿æ¯å 为MySQLç»åç´¢å¼ä¸ºâæå·¦åç¼âçç»æ,ç®åçç解就æ¯åªä»æå·¦é¢çå¼å§ç»åã
3.å¨ä»ä¹æ åµä¸ä½¿ç¨ç´¢å¼
(1)为æç´¢å段建索å¼,å¦æå¨ä½ ç表ä¸ï¼æ个åæ®µä½ ç»å¸¸ç¨æ¥åæç´¢ï¼é£ä¹ï¼è¯·ä¸ºå ¶å»ºç«ç´¢å¼å§ãä¸è¬æ¥è¯´ï¼å¨WHEREåJOINä¸åºç°çåéè¦å»ºç«ç´¢å¼ä»¥æé«æ¥è¯¢é度ã
ä¾å¦ä»fps表ï¼è¡¨ä¸ænameå段ï¼ä¸æ£ç´¢å§å为"ææ¦"ç人ï¼
ä¸é¢ç¨explainæ¥è§£éæ§è¡å»ºç«ç´¢å¼åæªå»ºç«ç´¢å¼çåºå«ï¼
a.æªå»ºç«ç´¢å¼å
å¤å¶ä»£ç
代ç å¦ä¸:
explain select name from fps where
name="ææ¦";
[SQL] select name from fps where name="ææ¦";
å½±åçæ°æ®æ :
0
æ¶é´: 0.003ms
b.建ç«ç´¢å¼å
å¤å¶ä»£ç
代ç å¦ä¸:
create index idx_name on
fps(
name
);
explain select name from fps where
name="ææ¦";
[SQL] select name from fps where name="ææ¦";
å½±åçæ°æ®æ :
0
æ¶é´: 0.001ms
(2)ä¸é¢æ们就æ¥ççè¿ä¸ªEXPLAINåæç»æçå«ä¹ã
tableï¼è¿æ¯è¡¨çååã
typeï¼è¿æ¥æä½çç±»åãä¸é¢æ¯MySQLææ¡£å ³äºrefè¿æ¥ç±»åç说æï¼
â对äºæ¯ä¸ªæ¥èªäºåé¢ç表çè¡ç»åï¼æææå¹é ç´¢å¼å¼çè¡å°ä»è¿å¼ 表ä¸è¯»åãå¦æèæ¥åªä½¿ç¨é®çæ左边çåç¼ï¼æå¦æé®ä¸æ¯
UNIQUEæPRIMARY
KEYï¼æ¢å¥è¯è¯´ï¼å¦æèæ¥ä¸è½åºäºå ³é®åéæ©å个è¡çè¯ï¼ï¼å使ç¨refãå¦æ使ç¨çé®ä» ä» å¹é å°éè¡ï¼è¯¥èæ¥
ç±»åæ¯ä¸éçãâ
å¨æ¬ä¾ä¸ï¼ç±äºç´¢å¼ä¸æ¯UNIQUEç±»åï¼refæ¯æ们è½å¤å¾å°çæ好è¿æ¥ç±»åã
å¦æEXPLAINæ¾ç¤ºè¿æ¥ç±»åæ¯âALLâï¼èä¸ä½ 并ä¸æ³ä»è¡¨éé¢éæ©åºå¤§å¤æ°è®°å½ï¼é£ä¹MySQLçæä½æçå°é常ä½ï¼å 为å®è¦æ«ææ´ä¸ªè¡¨ãä½ å¯ä»¥å å ¥æ´å¤çç´¢å¼æ¥è§£å³è¿ä¸ªé®é¢ãé¢ç¥æ´å¤ä¿¡æ¯ï¼è¯·åè§MySQLçæå说æã
possible_keysï¼
å¯è½å¯ä»¥å©ç¨çç´¢å¼çååãè¿éçç´¢å¼ååæ¯å建索å¼æ¶æå®çç´¢å¼æµç§°ï¼å¦æç´¢å¼æ²¡ææµç§°ï¼åé»è®¤æ¾ç¤ºçæ¯ç´¢å¼ä¸ç¬¬ä¸ä¸ªåçåå
ï¼å¨æ¬ä¾ä¸ï¼å®æ¯âidx_nameâï¼ã
Keyï¼
å®æ¾ç¤ºäºMySQLå®é 使ç¨çç´¢å¼çååãå¦æå®ä¸ºç©ºï¼æNULLï¼ï¼åMySQLä¸ä½¿ç¨ç´¢å¼ã
key_lenï¼
ç´¢å¼ä¸è¢«ä½¿ç¨é¨åçé¿åº¦ï¼ä»¥åè计ã
refï¼
å®æ¾ç¤ºçæ¯åçååï¼æåè¯âconstâï¼ï¼MySQLå°æ ¹æ®è¿äºåæ¥éæ©è¡ãå¨æ¬ä¾ä¸ï¼MySQLæ ¹æ®ä¸ä¸ªå¸¸ééæ©è¡ã
rowsï¼
MySQLæ认为çå®å¨æ¾å°æ£ç¡®çç»æä¹åå¿ é¡»æ«æçè®°å½æ°ãæ¾ç¶ï¼è¿éæçæ³çæ°åå°±æ¯1ã
æ¬ä¾ä¸æªç´¢å¼åéåçè®°å½æ°ä¸º1041ï¼è建ç«ç´¢å¼å为1
Extraï¼
è¿éå¯è½åºç°è®¸å¤ä¸åçé项ï¼å ¶ä¸å¤§å¤æ°å°å¯¹æ¥è¯¢äº§çè´é¢å½±åãå¨æ¬ä¾ä¸ï¼MySQLåªæ¯æéæ们å®å°ç¨using
whereï¼using indexåå¥éå¶æç´¢ç»æéã
4.æ常ç¨çåå¨å¼æï¼
(1)Myisamåå¨å¼æï¼æ¯ä¸ªMyisamå¨ç£çä¸åå¨æä¸ä¸ªæ件ãæ件åé½å表åç¸åï¼æ©å±ååå«ä¸º.frmï¼åå¨è¡¨å®ä¹ï¼ã.MYDï¼åå¨æ°æ®ï¼ã.MYIï¼åå¨ç´¢å¼ï¼ãæ°æ®æ件åç´¢å¼æ件å¯ä»¥æ¾ç½®å¨ä¸åç®å½ï¼å¹³ååå¸ioï¼è·å¾æ´å¿«çé度ã对åå¨å¤§å°æ²¡æéå¶ï¼MySQLæ°æ®åºçæ大ææ表尺寸é常æ¯ç±æä½ç³»ç»å¯¹æ件大å°çéå¶å³å®ç,
(2)InnoDBåå¨å¼æï¼å ·ææ交ãåæ»ãå¥æºæ¢å¤è½åçäºå¡å®å ¨ãä¸Myisamç¸æ¯ï¼InnoDBçåæçå·®ä¸äºå¹¶ä¸ä¼å ç¨æ´å¤çç£ç空é´ä»¥ä¿çæ°æ®åç´¢å¼ã
(3)å¦ä½éæ©åéçå¼æ
ä¸é¢æ¯å¸¸ç¨åå¨å¼æéç¨çç¯å¢ï¼
Myisamï¼å®æ¯å¨Webãæ°æ®ä»å¨åå ¶ä»åºç¨ç¯å¢ä¸æ常使ç¨çåå¨å¼æ;
InnoDBï¼ç¨äºäºå¡å¤çåºç¨ç¨åºï¼å ·ææ´å¤ç¹æ§ï¼å æ¬ACIDäºå¡ç¹æ§ã
温馨提示:答案为网友推荐,仅供参考