两大类索å¼
使ç¨çåå¨å¼æï¼MySQL5.7 InnoDB
èç°ç´¢å¼
* å¦æ表设置äºä¸»é®ï¼å主é®å°±æ¯èç°ç´¢å¼
* å¦æ表没æ主é®ï¼åä¼é»è®¤ç¬¬ä¸ä¸ªNOT NULLï¼ä¸å¯ä¸ï¼UNIQUEï¼çåä½ä¸ºèç°ç´¢å¼
* 以ä¸é½æ²¡æï¼åä¼é»è®¤å建ä¸ä¸ªéèçrow_idä½ä¸ºèç°ç´¢å¼
InnoDB çèç°ç´¢å¼çå¶åèç¹åå¨çæ¯è¡è®°å½ï¼å ¶å®æ¯é¡µç»æï¼ä¸ä¸ªé¡µå å«å¤è¡æ°æ®ï¼ï¼InnoDB å¿ é¡»è¦æè³å°ä¸ä¸ªèç°ç´¢å¼ã
ç±æ¤å¯è§ï¼ä½¿ç¨èç°ç´¢å¼æ¥è¯¢ä¼å¾å¿«ï¼å 为å¯ä»¥ç´æ¥å®ä½å°è¡è®°å½ã
æ®éç´¢å¼
æ®éç´¢å¼ä¹å«äºçº§ç´¢å¼ï¼é¤èç°ç´¢å¼å¤çç´¢å¼ï¼å³éèç°ç´¢å¼ã
InnoDBçæ®éç´¢å¼å¶åèç¹åå¨çæ¯ä¸»é®ï¼èç°ç´¢å¼ï¼çå¼ï¼èMyISAMçæ®éç´¢å¼åå¨çæ¯è®°å½æéã
示ä¾
建表
mysql> create table user(
-> id int(10) auto_increment,
-> name varchar(30),
-> age tinyint(4),
-> primary key (id),
-> index idx_age (age)
-> )engine=innodb charset=utf8mb4;
id å段æ¯èç°ç´¢å¼ï¼age å段æ¯æ®éç´¢å¼ï¼äºçº§ç´¢å¼ï¼
å¡«å æ°æ®
insert into user(name,age) values('å¼ ä¸',30);
insert into user(name,age) values('æå',20);
insert into user(name,age) values('çäº',40);
insert into user(name,age) values('åå
«',10);
mysql> select * from user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | å¼ ä¸ | 30 |
| 2 | æå | 20 |
| 3 | çäº | 40 |
| 4 | åå
« | 10 |
+----+--------+------+
ç´¢å¼åå¨ç»æ
id æ¯ä¸»é®ï¼æ以æ¯èç°ç´¢å¼ï¼å ¶å¶åèç¹åå¨çæ¯å¯¹åºè¡è®°å½çæ°æ®
èç°ç´¢å¼ï¼ClusteredIndexï¼
age æ¯æ®éç´¢å¼ï¼äºçº§ç´¢å¼ï¼ï¼éèç°ç´¢å¼ï¼å ¶å¶åèç¹åå¨çæ¯èç°ç´¢å¼ççå¼
æ®éç´¢å¼ï¼secondaryIndexï¼
å¦ææ¥è¯¢æ¡ä»¶ä¸ºä¸»é®ï¼èç°ç´¢å¼ï¼ï¼ååªéæ«æä¸æ¬¡B+æ å³å¯éè¿èç°ç´¢å¼å®ä½å°è¦æ¥æ¾çè¡è®°å½æ°æ®ã
å¦ï¼select * from user where id = 1;
èç°ç´¢å¼æ¥æ¾è¿ç¨
å¦ææ¥è¯¢æ¡ä»¶ä¸ºæ®éç´¢å¼ï¼éèç°ç´¢å¼ï¼ï¼éè¦æ«æ两次B+æ ï¼ç¬¬ä¸æ¬¡æ«æéè¿æ®éç´¢å¼å®ä½å°èç°ç´¢å¼çå¼ï¼ç¶å第äºæ¬¡æ«æéè¿èç°ç´¢å¼çå¼å®ä½å°è¦æ¥æ¾çè¡è®°å½æ°æ®ã
å¦ï¼select * from user where age = 30;
1. å
éè¿æ®éç´¢å¼ age=30 å®ä½å°ä¸»é®å¼ id=1
2. åéè¿èéç´¢å¼ id=1 å®ä½å°è¡è®°å½æ°æ®
æ®éç´¢å¼æ¥æ¾è¿ç¨ç¬¬ä¸æ¥
æ®éç´¢å¼æ¥æ¾è¿ç¨ç¬¬äºæ¥
å表æ¥è¯¢
å éè¿æ®éç´¢å¼çå¼å®ä½èç°ç´¢å¼å¼ï¼åéè¿èç°ç´¢å¼çå¼å®ä½è¡è®°å½æ°æ®ï¼éè¦æ«æ两次索å¼B+æ ï¼å®çæ§è½è¾æ«ä¸éç´¢å¼æ æ´ä½ã
ç´¢å¼è¦ç
åªéè¦å¨ä¸æ£µç´¢å¼æ ä¸å°±è½è·åSQLæéçææåæ°æ®ï¼æ éå表ï¼é度æ´å¿«ã
ä¾å¦ï¼select id,age from user where age = 10;
å¦ä½å®ç°è¦çç´¢å¼
常è§çæ¹æ³æ¯ï¼å°è¢«æ¥è¯¢çå段ï¼å»ºç«å°èåç´¢å¼éå»ã
1ãå¦å®ç°ï¼select id,age from user where age = 10;
explainåæï¼å 为ageæ¯æ®éç´¢å¼ï¼ä½¿ç¨å°äºageç´¢å¼ï¼éè¿ä¸æ¬¡æ«æB+æ å³å¯æ¥è¯¢å°ç¸åºçç»æï¼è¿æ ·å°±å®ç°äºè¦çç´¢å¼
2ãå®ç°ï¼select id,age,name from user where age = 10;
explainåæï¼ageæ¯æ®éç´¢å¼ï¼ä½nameåä¸å¨ç´¢å¼æ ä¸ï¼æ以éè¿ageç´¢å¼å¨æ¥è¯¢å°idåageçå¼åï¼éè¦è¿è¡å表åæ¥è¯¢nameçå¼ãæ¤æ¶çExtraåçNULL表示è¿è¡äºå表æ¥è¯¢
为äºå®ç°ç´¢å¼è¦çï¼éè¦å»ºç»åç´¢å¼idx_age_name(age,name)
drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);
explainåæï¼æ¤æ¶å段ageånameæ¯ç»åç´¢å¼idx_age_nameï¼æ¥è¯¢çå段idãageãnameçå¼ååé½å¨ç´¢å¼æ ä¸ï¼åªéæ«æä¸æ¬¡ç»åç´¢å¼B+æ å³å¯ï¼è¿å°±æ¯å®ç°äºç´¢å¼è¦çï¼æ¤æ¶çExtraå段为Using index表示使ç¨äºç´¢å¼è¦çã
åªäºåºæ¯éå使ç¨ç´¢å¼è¦çæ¥ä¼åSQL
å ¨è¡¨countæ¥è¯¢ä¼å
mysql> create table user(
-> id int(10) auto_increment,
-> name varchar(30),
-> age tinyint(4),
-> primary key (id),
-> )engine=innodb charset=utf8mb4;
ä¾å¦ï¼select count(age) from user;
使ç¨ç´¢å¼è¦çä¼åï¼å建ageå段索å¼
create index idx_age on user(age);
åæ¥è¯¢å表ä¼å
åæå¨æè¿°ç´¢å¼è¦ç使ç¨çä¾åå°±æ¯
ä¾å¦ï¼select id,age,name from user where age = 10;
使ç¨ç´¢å¼è¦çï¼å»ºç»åç´¢å¼idx_age_name(age,name)å³å¯
å页æ¥è¯¢
ä¾å¦ï¼select id,age,name from user order by age limit 100,2;
å 为nameå段ä¸æ¯ç´¢å¼ï¼æ以å¨å页æ¥è¯¢éè¦è¿è¡å表æ¥è¯¢ï¼æ¤æ¶Extra为Using filesortæ件æåºï¼æ¥è¯¢æ§è½ä½ä¸ã
使ç¨ç´¢å¼è¦çï¼å»ºç»åç´¢å¼idx_age_name(age,name)
æç« æ¥æºï¼å¼ å¾·Talk_https://developer.aliyun.com/article/744281