1. # 原文链接 https://blog.csdn.net/uuqaz/article/details/123990120
    3. CREATE TABLE `t_user` (
    4.   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
    5.   `id_no` VARCHAR(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
    6.   `username` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
    7.   `age` INT(11) DEFAULT NULL COMMENT '年龄',
    8.   `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    9.   PRIMARY KEY (`id`),
    10.   KEY `union_idx` (`id_no`,`username`,`age`),
    11.   KEY `create_time_idx` (`create_time`)
    12. ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    15. -- 删除历史存储过程
    16. DROP PROCEDURE IF EXISTS `insert_t_user`
    18. -- 创建存储过程
    19. DELIMITER $$
    20. CREATE PROCEDURE insert_t_user(IN limit_num INT)
    21. BEGIN
    22. DECLARE i INT DEFAULT 10;
    23. DECLARE id_no VARCHAR(18) ;
    24. DECLARE username VARCHAR(32) ;
    25. DECLARE age TINYINT DEFAULT 1;
    26. WHILE i < limit_num DO
    27. SET id_no = CONCAT("NO", i);
    28. SET username = CONCAT("Tom",i);
    29. SET age = FLOOR(10 + RAND()*2);
    30. INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());
    31. SET i = i + 1;
    32. END WHILE;
    33. END $$
    34. -- 调用存储过程
    35. CALL insert_t_user(100);
    1. # 查看索引
    2. SHOW INDEX FROM t_user;
    4. # 在联合索引的场景下，查询条件不满足最左匹配原则。
    5. EXPLAIN SELECT * FROM t_user WHERE id_no = '1002'; # 复合索引
    6. EXPLAIN SELECT * FROM t_user WHERE id_no = '1002' AND username = 'Tom2'; # 复合索引
    8. EXPLAIN SELECT * FROM t_user WHERE id_no = '1002' AND age = 12; # 只走了id_no
    9. EXPLAIN SELECT * FROM t_user WHERE username = 'Tom2' AND age = 12; # 不走, 不满足最左前缀原则
    12. # 索引列参与了函数处理，会导致全表扫描，索引失效。
    13. EXPLAIN SELECT * FROM t_user WHERE SUBSTR(id_no,1,3) = '100';
    15. # 错误的Like使用：模糊查询时（like语句），模糊匹配的占位符位于条件的首部
    16. EXPLAIN SELECT * FROM t_user WHERE id_no LIKE '%00%';
    18. # 类型隐式转换
    19. EXPLAIN SELECT * FROM t_user WHERE id_no = 1002;
    20. # 特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会参数转化为int类型，虽然使用了单引号或双引号：
    21. EXPLAIN SELECT * FROM t_user WHERE id = '2';
    22. DESC t_user;
    24. # 查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效
    25. EXPLAIN SELECT * FROM t_user WHERE id = 2 OR username = 'Tom2';
    27. # 两列数据做比较，即便两列都创建了索引，索引也会失效。
    28. EXPLAIN SELECT * FROM t_user WHERE id > age;
    30. # 查询条件使用is null时正常走索引，使用is not null时，不走索引。
    31. EXPLAIN SELECT * FROM t_user WHERE id_no IS NULL;
    32. EXPLAIN SELECT * FROM t_user WHERE id_no IS NOT NULL;
    34. # in、exists、not in、not exists、between and. 
    35. EXPLAIN SELECT * FROM t_user WHERE id IN (2,3);
    36. EXPLAIN SELECT * FROM t_user WHERE id_no IN ('1001','1002');
    37. EXPLAIN SELECT * FROM t_user u1 WHERE EXISTS (SELECT 1 FROM t_user u2 WHERE u2.id  = 2 AND u2.id = u1.id);
    38. EXPLAIN SELECT * FROM t_user WHERE id_no BETWEEN '1002' AND '1003';
    39. # 查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。
    40. EXPLAIN SELECT * FROM t_user WHERE id NOT IN (2,3);
    41. EXPLAIN SELECT * FROM t_user WHERE id_no NOT IN('1002' , '1003');
    42. # 查询条件使用not exists时，索引失效。
    43. EXPLAIN SELECT * FROM t_user u1 WHERE NOT EXISTS (SELECT 1 FROM t_user u2 WHERE u2.id  = 2 AND u2.id = u1.id);
    45. # order by导致索引失效
    46. EXPLAIN SELECT * FROM t_user ORDER BY id_no ;
    47. # 特例主键使用order by时，可以正常走索引。
    48. EXPLAIN SELECT * FROM t_user ORDER BY id DESC;