é®é¢
æè¿ï¼å¨ mysql æµè¯æå·¦åç¼ååï¼åç°äºåªå¤·ææçäºæ ãæ ¹æ®æå·¦åç¼ååï¼æ¬æ¥åºè¯¥ç´¢å¼å¤±æï¼èµ°å ¨è¡¨æ«æçï¼ä½æ¯ï¼å´åç°å¯ä»¥æ£å¸¸èµ°ç´¢å¼ã
表ç»æå¦ä¸ï¼ Mysql çæ¬ 5.7.22ï¼ï¼
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user` (`name`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
INSERT INTO user(`id`, `name`, `age`, `address`) VALUES (1, 'zs', 12, 'beijing');
表ä¸æ»å ±æå个å段ã id 为主é®ï¼è¿æä¸ä¸ªç± nameï¼ageï¼address ç»æçèåç´¢å¼ã åå¨å¼æ为 InnoDBï¼å¹¶æå ¥ä¸æ¡æµè¯æ°æ®ã
æ ¹æ®æå·¦åç¼ååï¼ä»¥ä¸ sql ï¼è¯å®ä¼ä½¿ç´¢å¼å¤±æçãï¼è¥ä¸ææå·¦åç¼ååï¼ç¨åä¼è®²~ï¼
EXPLAIN select * from user where address='beijing';
ç¶èç»æï¼å´æ¯è®©äººå¤§å¤±ææãå¦ä¸ï¼éè¿æ¥çæ§è¡è®¡åï¼åç°å®èµ°ç´¢å¼äºã
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISPrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdsATOfd3bkFGazxCMx8VesATMfhHLlN3XnxCMwEzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5SN5EDOxkjZzATNwkDZ4IGOxYzX2UDNxcTMwEzLcFTMxIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLzM3Lc9CX6MHc0RHaiojIsJye.png)
è¿å°±è®©æé常çæäºï¼é¾ä¸ææå·¦åç¼ååæ¯éçï¼åæè ï¼æ¯ Mysql éççæ¬å级ï¼å·²ç»æºè½å°ä¸éè¦ care æå·¦åç¼ååäºåï¼
ç®å½
带çè¿ä¸ªçé®ï¼æ们ä¸æ¢ç©¶ç«ãå¨è¿ä¹åéè¦äºè§£ä¸äºåç½®ç¥è¯ãæ¬ç¯æç« ç®å½å¦ä¸ï¼
- ä»ä¹æ¯èéç´¢å¼åéèéç´¢å¼ï¼
- ä»ä¹æ¯å表æ¥è¯¢ï¼
- ä»ä¹æ¯ç´¢å¼è¦çï¼
- æå·¦åç¼åå
- é®é¢è§£æ
æ£æ
ç±äºï¼ç°å¨åºæ¬ä¸é½æ¯ç¨ç InnoDBå¼æï¼æ以ä¸é¢é½ä»¥ InnoDB为ä¾ï¼MyISAM 顺带æä¸ä¸ã
æ们ç¥é Mysql åºå±æ¯ç¨ B+ æ æ¥åå¨ç´¢å¼çï¼ä¸æ°æ®é½åå¨å¶åèç¹ãå¯¹äº InnoDB æ¥è¯´ï¼å®ç主é®ç´¢å¼åè¡è®°å½æ¯åå¨å¨ä¸èµ·çï¼å æ¤å«åèéç´¢å¼ï¼clustered index)ã
PSï¼MyISAM çè¡è®°å½æ¯åç¬åå¨çï¼ä¸åç´¢å¼å¨ä¸èµ·ï¼å æ¤ MyISAMä¹å°±æ²¡æèéç´¢å¼ã
é¤äºèéç´¢å¼ï¼å ¶å®ç´¢å¼é½å«åéèéç´¢å¼ï¼secondary indexï¼ãå æ¬æ®éç´¢å¼ï¼å¯ä¸ç´¢å¼çã
å¦å¤éè¦æ³¨æï¼å¨ InnoDB ä¸æä¸åªæä¸ä¸ªèéç´¢å¼ãå®æä¸ç§æ åµï¼
- è¥è¡¨åå¨ä¸»é®ï¼å主é®ç´¢å¼å°±æ¯èéç´¢å¼ã
- è¥ä¸åå¨ä¸»é®ï¼åä¼æ第ä¸ä¸ªé空çå¯ä¸ç´¢å¼ä½ä¸ºèéç´¢å¼ã
- å¦åï¼å°±ä¼éå¼çå®ä¹ä¸ä¸ª rowid ä½ä¸ºèéç´¢å¼ã
为äºæ¹ä¾¿ç解ï¼ä¸è¾¹ä»¥ InnoDB ç主é®ç´¢å¼åæ®éç´¢å¼ä¸ºä¾ï¼çä¸å®ä»¬çåå¨ç»æã
å建ä¸å¼ 表ï¼ç»æå¦ä¸ï¼å¹¶æ·»å å æ¡è®°å½ï¼å¼ ä¸ï¼æåï¼çäºï¼åä¸ï¼ï¼
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_stu` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
insert into student(id,name,age) values(1,'zs',12);
insert into student(id,name,age) values(5,'ls',14);
insert into student(id,name,age) values(9,'ww',12);
insert into student(id,name,age) values(11,'sq',13);
å¨ InnoDB ä¸ï¼ä¸»é®ç´¢å¼çå¶åèç¹åå¨çæ¯ä¸»é®åè¡è®°å½ï¼èæ®éç´¢å¼çå¶åèç¹åå¨çæ¯ä¸»é®ï¼å¯¹äº MyISAMæ¥è¯´ä¸»é®ç´¢å¼çå¶åèç¹åå¨çæ¯ä¸»é®å对åºè¡è®°å½çæéï¼æ®éç´¢å¼çå¶åèç¹åå¨çæ¯å½åç´¢å¼åå对åºè¡è®°å½çæéï¼ã
å æ¤ï¼id 为èéç´¢å¼ï¼name 为éèéç´¢å¼ãå®ä»¬å¯¹åºç B+ æ ç»æå¦ä¸å¾æ示ï¼
ä»ä¸è¾¹çç´¢å¼åå¨ç»æï¼æ们å¯ä»¥çå°ï¼å¨ä¸»é®ç´¢å¼æ ä¸ï¼éè¿ä¸»é®å°±å¯ä»¥ä¸æ¬¡æ§æ¥åºæ¥æ们æéè¦çæ°æ®ï¼é度é常çå¿«ã
å 为主é®åè¡è®°å½å°±åå¨å¨ä¸èµ·ï¼å®ä½å°äºä¸»é®ï¼ä¹å°±å®ä½å°äºæè¦æ¾çè®°å½ï¼å½åè¡çææå段é½å¨è¿ï¼è¿ä¹æ¯ä¸ºä»ä¹æ们说ï¼å¨å建表çæ¶åï¼æ好æ¯å建ä¸ä¸ªä¸»é®ï¼æ¥è¯¢æ¶ä¹å°½éç¨ä¸»é®æ¥æ¥è¯¢ï¼ã
对äºæ®éç´¢å¼ï¼å¦ä¾åä¸ç nameï¼åéè¦æ ¹æ® name çç´¢å¼æ ï¼éèéç´¢å¼ï¼æ¾å°å¶åèç¹å¯¹åºç主é®ï¼ç¶ååéè¿ä¸»é®å»ä¸»é®ç´¢å¼æ æ¥è¯¢ä¸éï¼æå¯ä»¥å¾å°è¦æ¾çè®°å½ãè¿å°±å« å表æ¥è¯¢ã
以å¦ä¸ sql 为ä¾ã
select * from student where name='zs';
å®éè¦æ¥è¯¢ä¸¤éç´¢å¼æ ã
- éè¿éèéç´¢å¼å®ä½å°ä¸»é® id=1ã
- éè¿èéç´¢å¼å®ä½å°ä¸»é®id为1ï¼å¯¹åºçè¡è®°å½ã
å®çæ¥è¯¢è¿ç¨å¾å¦ä¸ï¼
对äºä¸è¾¹çå表æ¥è¯¢æ¥è¯´ï¼æ çä¼éä½æ¥è¯¢æçãé£ä¹ï¼æçç«¥éå°±ä¼é®äºï¼æ没æä»ä¹åæ³ï¼è®©å®ä¸å表å¢ï¼
çæ¡å½ç¶æ¯æäºï¼å°±æ¯ç´¢å¼è¦çã
ä½ä¸ºç´¢å¼è¦çï¼å°±æ¯å¨ç¨è¿ä¸ªç´¢å¼æ¥è¯¢æ¶ï¼ä½¿å®çç´¢å¼æ ï¼æ¥è¯¢å°çå¶åèç¹ä¸çæ°æ®å¯ä»¥è¦çå°ä½ æ¥è¯¢çææå段ï¼è¿æ ·å°±å¯ä»¥é¿å å表ã
è¿æ¯ä»¥ä¸è¾¹ç表为ä¾ï¼ç°å¨ zs 对åºçç´¢å¼æ ä¸è¾¹ï¼åªæå®æ¬èº«å主é®çæ°æ®ï¼å¹¶ä¸è½è¦çå° age å段ãé£ä¹ï¼æ们就å¯ä»¥å建èåç´¢å¼ï¼å¦ KEY(name,age)ã并ä¸ï¼æ¥è¯¢çæ¶åï¼æ¾å¼çååºèåç´¢å¼å¯¹åºçå段ï¼nameåageï¼ã
å建èåç´¢å¼å¦ä¸ï¼
KEY `idx_stu` (`name`,`age`)
æ¥è¯¢è¯å¥ä¿®æ¹å¦ä¸ï¼
-- è¦çèåç´¢å¼ä¸çå段
select id,name,age from student where name='zs' and age=12;
è¿æ ·ï¼å½æ¥è¯¢ç´¢å¼æ çæ¶åï¼å°±ä¸ç¨å表ï¼å¯ä»¥ä¸æ¬¡æ§æ¥åºææçå段ã对åºçç´¢å¼æ ç»æå¦ä¸ï¼
PSï¼å¾ä¸ï¼èåç´¢å¼ä¸çå段ï¼nameï¼ageï¼é½åºè¯¥åºç°å¨ç´¢å¼æ ä¸çï¼è¿é为äºç»å¾æ¹ä¾¿ï¼ä¸å æ°æ®é太å°ï¼æ²¡æç»åºæ¥ãåªè¡¨ç°åºäºï¼å¶åèç¹åå¨äºææçèåç´¢å¼å段ã
æå·¦åç¼ååï¼é¡¾åæä¹ï¼å°±æ¯æ左边çä¼å ãæçæ¯èåç´¢å¼ä¸ï¼ä¼å èµ°æ左边åçç´¢å¼ãå¦ä¸è¡¨ä¸ï¼nameåageçèåç´¢å¼ï¼ç¸å½äºåå»ºäº name ååç´¢å¼å (name,age)èåç´¢å¼ãå¨æ¥è¯¢æ¶ï¼where æ¡ä»¶ä¸è¥æ name å段ï¼åä¼èµ°è¿ä¸ªèåç´¢å¼ã
对äºå¤ä¸ªå段çèåç´¢å¼ï¼ä¹åçãå¦ index(a,b,c) èåç´¢å¼ï¼åç¸å½äºåå»ºäº a ååç´¢å¼ï¼(a,b)èåç´¢å¼ï¼å(a,b,c)èåç´¢å¼ã
为äºéªè¯æå·¦åç¼ååï¼æ们éè¦å¯¹åæ¥ç表ç»æè¿è¡æ¹é ãåæ·»å 两个å段(address,sex)ï¼ç¶åå建ä¸åçèåç´¢å¼(name,age,address)ã
drop table student;
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`sex` int(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_stu` (`name`,`age`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into student(id,name,age,address,sex) values(1,'zs',12,'beijing',1);
insert into student(id,name,age,address,sex) values(5,'ls',14,'tianjin',0);
insert into student(id,name,age,address,sex) values(9,'ww',12,'shanghai',1);
insert into student(id,name,age,address,sex) values(11,'sq',13,'hebei',1);
æ¥ç表æ°æ®å¦ä¸ï¼
åå«ç¨ä¸ç§æ¹å¼ï¼ä½¿ä¹ç¬¦åæå·¦åç¼ååã
explain select * from student where name='zs';
explain select * from student where name='zs' and age=12;
explain select * from student where name='zs' and age=12 and address='beijing';
ç¶åæ¥çå®ä»¬çæ§è¡è®¡åå¦ä¸ï¼
å¯ä»¥çå°ï¼æç»é½èµ°äºç´¢å¼ãç°å¨ï¼ä¿®æ¹ sql å¦ä¸ï¼å¦ä½ï¼
explain select * from student where address='beijing';
å¦æ们ææï¼è¿ä¸ç¬¦åæå·¦åç¼ååï¼å æ¤ç´¢å¼å¤±æï¼èµ°äºå ¨è¡¨æ«æã
PSï¼æå±æèï¼è¥ sql æ¹ä¸ºå¦ä¸ï¼ä¼å¯¼è´å ¨è¡¨æ«æåï¼ï¼èªå·±å¨æå°è¯å¦)
explain select * from student where name='zs' and address='beijing';
å°ç°å¨ä¸ºæ¢ï¼æ们åç°æå·¦åç¼ååä¸åæ£å¸¸ãç¶ååå°æå¼å§æåºçé®é¢ï¼ä¸ºä»ä¹è¿ä¸ªååå°±ä¸çæäºå¢ï¼ï¼å建çèåç´¢å¼ï¼è¿æ sql è¯å¥é½æ¯ä¸æ ·çåï¼ï¼
å«çæ¥ï¼è¿è®°å¾åé¢æ们说çç´¢å¼è¦çåï¼ è¿æ¬¡ï¼æ们å©ç¨ç´¢å¼è¦çåçï¼åªæ¥è¯¢ç¹å®çå段ï¼åªæ主é®åèåç´¢å¼å段ï¼ã
explain select id,name,age,address from student where address='beijing';
åæ¥çæ§è¡è®¡åï¼
é®é¢æ¥äºï¼æ¤æ¶è¿åäºæå·¦åç¼ååï¼ä½æ¯ç¬¦åè¦çç´¢å¼ï¼ä¸ºä»ä¹å°±èµ°ç´¢å¼äºå¢ï¼
æ们对æ¯ä¸ä¸ï¼è¥ç¨æå·¦åï¼åä¸ç¨æå·¦åï¼å®ä»¬çæ§è¡è®¡åæä½ä¸åã
ä¼åç°ï¼è¥ä¸ç¬¦åæå·¦åç¼ååï¼å type为 indexï¼è¥ç¬¦åï¼å type 为 refã
index 代表çæ¯ä¼å¯¹æ´ä¸ªç´¢å¼æ è¿è¡æ«æï¼å¦ä¾åä¸çï¼æå³å addressï¼å°±ä¼å¯¼è´æ«ææ´ä¸ªç´¢å¼æ ã
ref 代表 mysql ä¼æ ¹æ®ç¹å®çç®æ³æ¥æ¾ç´¢å¼ï¼è¿æ ·çæçæ¯ index å ¨æ«æè¦é«ä¸äºãä½æ¯ï¼å®å¯¹ç´¢å¼ç»ææä¸å®çè¦æ±ï¼ç´¢å¼åæ®µå¿ é¡»æ¯æåºçãèèåç´¢å¼å°±ç¬¦åè¿æ ·çè¦æ±ï¼
èåç´¢å¼å é¨å°±æ¯æåºçï¼æ们å¯ä»¥æå®çè§£ä¸ºç±»ä¼¼äº order by name,age,address è¿æ ·çæåºè§åãä¼å æ ¹æ® name æåºï¼è¥name ç¸åï¼åæ ¹æ® age æåºï¼ä¾æ¬¡ç±»æ¨ã
æ以ï¼è¿ä¹è§£éäºï¼ä¸ºä»ä¹æ们è¦éµå®æå·¦åç¼ååãå½æå·¦åæåºæ¶ï¼æå¯ä»¥ä¿è¯å³è¾¹çç´¢å¼åæåºã
éèæ±å ¶æ¬¡ï¼è¥ä¸ç¬¦åæå·¦åç¼ååï¼ä½æ¯ç¬¦åè¦çç´¢å¼ï¼å°±å¯ä»¥æ«ææ´ä¸ªç´¢å¼æ ï¼ä»èæ¾å°è¦çç´¢å¼å¯¹åºçåï¼é¿å äºå表ï¼ã
è¥ä¸ç¬¦åæå·¦åç¼ååï¼ä¸ä¹ä¸ç¬¦åè¦çç´¢å¼ï¼å½¢å select *ï¼ï¼åéè¦æ«ææ´ä¸ªç´¢å¼æ ãå®æä¹åï¼è¿éè¦åå表ï¼æ¥è¯¢å¯¹åºçè¡è®°å½ã
æ¤æ¶ï¼æ¥è¯¢ä¼åå¨ï¼å°±ä¼è®¤ä¸ºï¼è¿æ ·ç两次æ¥è¯¢ç´¢å¼æ ï¼è¿ä¸å¦å ¨è¡¨æ«ææ¥çå¿«(å 为èåç´¢å¼æ¤æ¶ä¸ç¬¦åæå·¦åç¼ååï¼è¦æ¯æ®éååç´¢å¼æ¥è¯¢æ ¢çå¤)ãå æ¤ï¼æ¤æ¶å°±ä¼èµ°å ¨è¡¨æ«æã
æç«¥éå°±è¦é®äºï¼ä½ å¨è¿åºè¯äºä¸å¤§å ï¼è¿æ¯æ²¡æ解çæåççæå ï¼ï¼ï¼
ä¸ç¶ï¼å ¶å®ä¸è¾¹çåæ就已ç»è§£çäºãæ们ä»ç»è§å¯æå¼å§ç user 表ï¼åæ¤æ¶ç student 表æä»ä¹ä¸åã
user 表ä¸ï¼å student 表ç¸æ¯ï¼å°äº sex å段ãä½æ¯ï¼å®ä»¬æ建ç«çèåç´¢å¼å´æ¯ä¸æ ·ç KEY(name,age,address)ã
æ以ï¼å¨ user ä¸ï¼æ们æåç sql è¯å¥å°±çåäº ,
-- æåçsql
EXPLAIN select * from user where address='beijing';
-- çåäº
EXPLAIN select id,name,age,address from user where address='beijing';
è¿ä¸ªç»æå°±æ¯æ们ä¸è¾¹è®¨è®ºçæ åµï¼ä¸ç¬¦åæå·¦åç¼ååï¼ä½æ¯ç¬¦åç´¢å¼è¦çãè¿ç§æ åµï¼æ¯ä¼èµ°ç´¢å¼çã
ç»è®º
é£ä¹ï¼ç»è®ºä¹å°±åºæ¥äºã并ä¸æ¯æå·¦åç¼åå失æäºï¼ä¹ä¸æ¯ Mysql åçæ´æºè½äºï¼èæ¯æ¤æ¶å建ç表ç»æï¼ä»¥åæ¥è¯¢ç sql è¯å¥æ°å¥½ç¬¦åäºç´¢å¼è¦çèå·²ãççæ¯èæä¸åº ï¼ï¼