æªå»ºç«ç´¢å¼
å½æ°æ®è¡¨æ²¡æ设计ç¸å ³ç´¢å¼æ¶ï¼æ¥è¯¢ä¼æ«æå ¨è¡¨ã
sqlå¤å¶ä»£ç create table test_temp
(
test_id int auto_increment
primary key,
field_1 varchar(20) null,
field_2 varchar(20) null,
field_3 bigint null,
create_date date null
);
explain
select * from test_temp where field_1 = 'testing0';
建议
æ¥è¯¢é¢ç¹æ¯æ°æ®è¡¨å段å¢å åéçç´¢å¼ã
æ¥è¯¢ç»æéæ¯å表ä¸ç大é¨åæ°æ®
å½æ°æ®åºæ¥è¯¢å½ä¸ç´¢å¼æ¶ï¼æ°æ®åºä¼é¦å å©ç¨ç´¢å¼åçå¼å®ä½å°å¯¹åºçæ°æ®èç¹ãè¿ä¸ªæ°æ®èç¹ä¸è®°å½äºå¯¹åºæ°æ®è¡çè¡æ è¯ç¬¦ï¼Row Identifierï¼ãç¶èï¼å¦ææ¥è¯¢éè¦è·å该è¡å ¶ä»åçæ°æ®ï¼å°±éè¦è¿è¡å表æä½ã
å¨å表æä½ä¸ï¼æ°æ®åºä¼ä½¿ç¨è¡æ è¯ç¬¦å次访é®æ°æ®èç¹æç£çä¸çå®é æ°æ®è¡ï¼ä»¥è·åå®æ´çæ°æ®ãè¿ä¸ªè¿ç¨è¢«ç§°ä¸ºå表ãå表æä½å¯è½ä¼å¢å é¢å¤çç£ç访é®åæ°æ®æ£ç´¢çå¼éï¼å æ¤ï¼å¨æäºæ åµä¸ï¼å½MySQLå¤æå表æéçèµæºå¤§äºç´æ¥æ«æå ¨è¡¨æ¶ï¼å®å¯è½éæ©ä¸èµ°ç´¢å¼ï¼èæ¯æ§è¡å ¨è¡¨æ«æã
建议
- ç´¢å¼è¦çï¼é æ èèå建å å«æ¥è¯¢æéåçç´¢å¼ï¼æ¥è¯¢ç»æéå ¨é¨è¢«ç´¢å¼è¦çï¼æ éå表ã
- è°æ´æ¥è¯¢è¯å¥ï¼æ¥è¯¢å¿ è¦çåã使ç¨Joinè¯å¥ä¼åæ¥è¯¢è¯å¥ï¼åå°å表次æ°ã
- å½è¡¨æ°æ®éè¾å¤§æ¶ï¼éèèå ¶ä»åå¨æå¡ã
使ç¨å½æ°ãéå¼è½¬æ¢
使ç¨å½æ°
éå¼è½¬æ¢
æ°æ®åå¤:
sqlå¤å¶ä»£ç SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
`description` text,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`type` tinyint NOT NULL COMMENT 'ååç±»å',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of products
-- ----------------------------
BEGIN;
INSERT INTO `products` VALUES (1, 'Product A', 10.99, 'This is the description for Product A', '2023-08-11 03:47:06', '2023-08-11 03:49:24', 1);
INSERT INTO `products` VALUES (2, 'Product B', 19.99, 'This is the description for Product B', '2023-08-11 03:47:07', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (3, 'Product C', 5.99, 'This is the description for Product C', '2023-08-11 03:47:07', '2023-08-11 03:49:25', 3);
INSERT INTO `products` VALUES (4, 'Product D', 8.49, 'This is the description for Product D', '2023-08-11 03:47:07', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (5, 'Product E', 15.99, 'This is the description for Product E', '2023-08-11 03:47:07', '2023-08-11 03:49:25', 2);
INSERT INTO `products` VALUES (6, 'Product F', 12.99, 'This is the description for Product F', '2023-08-11 03:47:08', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (7, 'Product G', 7.99, 'This is the description for Product G', '2023-08-11 03:47:08', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (8, 'Product H', 9.99, 'This is the description for Product H', '2023-08-11 03:47:08', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (9, 'Product I', 14.99, 'This is the description for Product I', '2023-08-11 03:47:09', '2023-08-11 03:49:24', 2);
INSERT INTO `products` VALUES (10, 'Product J', 11.99, 'This is the description for Product J', '2023-08-11 03:47:09', '2023-08-11 03:49:24', 2);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
# å¢å ç´¢å¼
ALTER TABLE products
ADD INDEX idx_type (type);
å¤ç°:
sqlå¤å¶ä»£ç explain
select * from products where type in ('1','2');
ç±äºtypeæ¯tinyintç±»åï¼å æ¤ï¼ä»¥ä¸SQLçæ为:
sqlå¤å¶ä»£ç SELECT * FROM products WHERE type in CAST('1' AS tinyint,'2' as tinyint);
ç±äºä½¿ç¨äºCAST()å½æ°ï¼ä¼å¯¼è´ä¸èµ°ç´¢å¼çç°è±¡ã
è¿æä¸ç§æ åµæ¯ï¼å¨å ³èæ¥è¯¢æ¶ï¼é©±å¨è¡¨å ³èå段两è æåºè§åä¸ä¸è´æ¶ä¹ä¼å¯¼è´ä¸èµ°ç´¢å¼ã
å ³äºéå¼è½¬æ¢æ´å¤è¯¦ç»å 容å¯ä»¥åè:
æµ æ MySQL çéå¼è½¬æ¢
in/not in <>æ¡ä»¶å¯¼è´ä¸èµ°ç´¢å¼
inãnot inã<>ä¸èµ°ç´¢å¼çåå æ¯ç¸ä¼¼çï¼ä»¥ä¸åºäºinè¯å¥åæã
inæ¡ä»¶å¯¼è´ä¸èµ°ç´¢å¼çæ åµï¼
inæ¡ä»¶è¿å¤
sqlå¤å¶ä»£ç explain
select * from products where type in (1,2,3,4,5,6,7);
å¦æ IN æ¡ä»¶ä¸å å«å¤ªå¤çå¼ï¼è¶ åºäºæ°æ®åºç®¡çç³»ç»çéå¶ï¼å®å¯è½ä¼éæ©ä¸ä½¿ç¨ç´¢å¼ã
建议ï¼
- å½inæ¡ä»¶ä¸çæ°æ®æ¯è¿ç»æ¶ï¼å¯ä»¥ä½¿ç¨between and代æ¿inã
- åèæ²»ä¹ï¼å°ä¸æ¬¡æ¥è¯¢å为å¤æ¬¡æ¥è¯¢ï¼æåå并éã
- 使ç¨UNIONè¯å¥ï¼ç±»ä¼¼æ¹æ¡ä¸ï¼åªä¸è¿è¯¥æ¹æ¡æ¯å¨SQLå±é¢å®æã
sqlå¤å¶ä»£ç SELECT column1, column2, ...
FROM your_table
WHERE column IN (value1, value2, ..., valueN)
UNION
SELECT column1, column2, ...
FROM your_table
WHERE column IN (valueN+1, valueN+2, ..., valueM)
ç»è®¡ä¿¡æ¯ä¸åç¡®
sqlå¤å¶ä»£ç SHOW ENGINE INNODB STATUS;
该å½ä»¤ä¼æ¥è¯¢åºMySQL Inndbåå¨å¼æçæä½æ åµï¼ä¿¡æ¯å å«Innodbåç§ç»è®¡ä¿¡æ¯:
- Insertsï¼å·²æå ¥çè¡æ°ã
- Updatesï¼å·²æ´æ°çè¡æ°ã
- Deletesï¼å·²å é¤çè¡æ°ã
- Readsï¼å·²è¯»åçè¡æ°ã
innodb表çç»è®¡ä¿¡æ¯å¹¶ä¸æ¯å®æ¶ç»è®¡æ´æ°ï¼å¦æç»è®¡ä¿¡æ¯åå®é çç´¢å¼ä¿¡æ¯å·®å¼å¾å¤§ï¼å°±ä¼å¯¼è´ä¼åå¨è®¡ç®å个索å¼ææ¬åï¼ååºéé¢æçéæ©ãåºç°è¿ç§ç°è±¡çåºæ¯æ¯ï¼å½æ大éæ°æ®å¨çæ¶é´å è½åºæ¶ï¼Innodbè¿æ²¡æ´æ°ç»è®¡ç¸å ³ä¿¡æ¯ï¼æ¤æ¶æ¥äºä¸ä¸ªæ¥è¯¢ï¼MySQLä¼åºäºåå²æ°æ®ååºé误çå¤æï¼å½å表æ°æ®éå°ï¼ä¸èµ°ç´¢å¼æ´é«æã
建议
åºäºæ¤é®é¢ç解å³æ¹æ¡æ¯ï¼æå¨æ´æ°ç¸å ³ç»è®¡æ°æ®ã
likeè¯å¥
likeè¯å¥æ æ³å½ä¸ç´¢å¼çæ åµï¼
- å导éé 符ï¼%value
- éé 符å¨å符串çä¸é´ï¼value%value
- éé 符"_"åºç°å¨å¼å¤´
建议
- å°½éé¿å å¨æ¨¡å¼çå¼å¤´ä½¿ç¨å导éé 符 %
- å¦ææ æ³é¿å 第ä¸ç§ï¼æ ¹æ®å®é ä¸å¡åæ¥è¯¢è¯å¥èè使ç¨åç¼ç´¢å¼
- å°éé 符 % æ¾å¨æ¨¡å¼çæ«å°¾ï¼ä»¥ä¾¿è¿è¡åç¼å¹é ã
- å¦æéè¦å¨æ¨¡å¼çä¸é´ä½¿ç¨éé 符 %ï¼å¯ä»¥èè使ç¨å ¨ææç´¢å¼ææå ¶ä»æ´éå模å¼å¹é çææ¯ã
- 对äºåºå®é¿åº¦ç模å¼å¹é ï¼å¯ä»¥èè使ç¨å ¶ä»æä½ç¬¦ï¼å¦ = æ <>