MySQL索引失效情况
MySQL版本：8.0.17。表结构和数据,主键索引id、唯一索引telephone、联合索引union(addr,age,name)
DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(3) DEFAULT NULL COMMENT '年龄',
`name` varchar(11) DEFAULT NULL COMMENT '姓名',
`addr` varchar(50) DEFAULT NULL COMMENT '地址',
`telephone` varchar(20) DEFAULT NULL COMMENT '手机号',
PRIMARY KEY (`id`),
UNIQUE KEY `unique` (`telephone`),
KEY `union` (`addr`,`age`,`name`),
KEY `union_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `demo` */
LOCK TABLES `demo` WRITE;
insert into `demo`(`id`,`age`,`name`,`addr`,`telephone`) values (1,18,'张三','北京市','110'),(2,16,'王小二','南京市','112'),(3,27,'黄四郎','上海市','114'),(4,36,'懒羊羊','青青草原','119'),(5,6,'小灰灰','青青草原','985'),(6,12,'孙悟空','花果山',NULL),(7,66,'玉皇大帝','天庭','510');
UNLOCK TABLES;
前导模糊查询，如xxx like '%xxx',但如xxx like 'xxx%'是可以用到索引的。
explain select * from demo where telephone like '%10';
explain select * from demo where telephone like '1%';
##### 这里还有一种特殊情况的后置`%`也不会用到索引，就是like 'AA%',表中该列有很多AA开头的，应该是`mysql`自己会选择合适的方式，具体还得explain研究
数据类型出现隐式转换
explain select * from demo where telephone = 110;
explain select * from demo where telephone = '110';
varchar写成int类型，mysql有个类型转换规则就是将“字符转成数字”，所以以上sql就等价于这样：
EXPLAIN SELECT* FROM demo WHERE CAST(telephone AS SIGNED)= 110
int写成varchar,按道理说这个应该也是调用函数转的，母鸡，求大佬评论指导
对列进行运算，如+ - * /之类的
EXPLAIN SELECT * FROM demo WHERE age + 1 = 19;
EXPLAIN SELECT * FROM demo WHERE age = 19 - 1;
违反最左前缀原则（注：当时这个还未添加union_age这个索引）
EXPLAIN SELECT * FROM demo WHERE addr='北京市' AND NAME = '张三' AND age = 18;
EXPLAIN SELECT * FROM demo WHERE NAME = '张三' AND age = 18;
在索引列使用函数
EXPLAIN SELECT * FROM demo WHERE LEFT(telephone,3)='110'
使用or，or左右俩端不都是索引列
EXPLAIN SELECT * FROM demo WHERE age = 16 AND telephone = '15' OR NAME = '1'
EXPLAIN SELECT * FROM demo WHERE age = 16 AND telephone = '15' OR addr = '1'
is null、is not null、<>、!=情况，我测试的是有的会用索引，有的不用，待考证，具体数据在文章末尾
#使用了索引
EXPLAIN SELECT * FROM demo WHERE telephone IS NULL;
#使用了索引
EXPLAIN SELECT * FROM demo WHERE telephone IS NOT NULL;
#使用了索引
EXPLAIN SELECT * FROM demo WHERE age IS NULL;
#使用了索引
EXPLAIN SELECT * FROM demo WHERE age IS NOT NULL
#使用了索引 唯一索引
EXPLAIN SELECT * FROM demo WHERE telephone != '';
#使用了索引 唯一索引
EXPLAIN SELECT * FROM demo WHERE telephone <> '';
#么有使用索引 联合索引
EXPLAIN SELECT * FROM demo WHERE age != '';
#么有使用索引 联合索引
EXPLAIN SELECT * FROM demo WHERE age <> '';
#么有使用索引 联合索引
EXPLAIN SELECT * FROM links WHERE NAME IS NOT NULL;
#么有使用索引 联合索引
EXPLAIN SELECT * FROM links WHERE NAME IS NOT NULL;
左连接查询或者右连接查询查询关联的字段编码格式不一样
最难的，mysql自己认为不走索引比较快的情况，我水平不够，无法预判~~~
#test建表sql
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`telephone` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tele` (`telephone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `test` */
LOCK TABLES `test` WRITE;
UNLOCK TABLES;
###需要回表，回表：demo表中telephone索引树上只记录了telephone数据列，我们查的是*，所以需要根据主键回表去查询全部列
#explain select * from demo left join test on demo.`telephone` = test.`telephone`
EXPLAIN SELECT demo.`telephone` FROM demo LEFT JOIN test ON demo.`telephone` = test.`telephone`
思考题
links建表语句
DROP TABLE IF EXISTS `links`;
CREATE TABLE `links` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` datetime(6) DEFAULT NULL,
`update_time` datetime(6) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`logo` varchar(1023) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`priority` int(11) DEFAULT '0',
`team` varchar(255) DEFAULT NULL,
`url` varchar(1023) NOT NULL,
PRIMARY KEY (`id`),
KEY `links_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=219 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `links` */
LOCK TABLES `links` WRITE;
insert into `links`(`id`,`create_time`,`update_time`,`description`,`logo`,`name`,`priority`,`team`,`url`) values (65,'2021-04-29 11:22:24.991000','2021-05-12 13:51:31.237000','专业修路由器20年！','https://www.nextserein.com/upload/2021/05/头像1-1cd945bcfe9d421c9304e5d8dffa3f0a.jpeg','dzy',2,'<div class=\"note primary\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">?️ 朋友 —— 你的春日好运正在派件，请保持心情舒畅</div>','https://www.nextserein.com'),(67,'2021-04-29 12:13:48.126000','2021-05-07 12:12:38.826000','是你呀，晓果冻！','http://halo.chenmx.net/upload/2021/04/a371aacd989450c9c1592c465581fc8b-ed0b4a938e434d908f235e0a44d8d090.jpg','晓果冻',3,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://halo.chenmx.net'),(68,'2021-05-07 12:22:04.297000','2021-05-07 12:22:34.592000','Dream it possible, make it possible','https://cdn.jsdelivr.net/gh/Sanarous/files/images/avatar.jpeg','Sanarous',4,'<div class=\"note warning\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 大佬 —— 没有不劳而获的工作，更没有坐享其成的收获</div>','https://bestzuo.cn'),(69,'2021-05-09 16:18:45.900000','2021-05-09 16:19:01.473000','分享web前端相关的技术文章，会记录日常生活中的琐事和大家一起分享。','https://cdn.leader755.com/public/wx_avatar.png','leader755 Blogs',5,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.leader755.com/'),(72,'2021-05-10 22:04:39.519000','2021-05-10 22:04:39.519000','人生如逆旅,我亦是行人,但愿初相遇,不负有心人','https://huangdf.xyz/avatar','柒月是ni的谎言',8,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://huangdf.xyz/'),(73,'2021-05-12 16:37:49.830000','2021-05-22 11:48:15.697000','快乐地记人记事\n','https://www.amazingk.cn/upload/2021/04/20210404211159-3ebb9a2454c944cfb70ef289a6b2b400.jpg','K’Blog',100,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.amazingk.cn'),(75,'2021-05-16 11:09:44.386000','2021-05-16 11:21:29.577000','一个专注于技术分享的博客平台','https://images.chenmx.net/blog/1604885496928.png','蘑菇博客',11,'<div class=\"note warning\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 大佬 —— 没有不劳而获的工作，更没有坐享其成的收获</div>','http://www.moguit.cn'),(106,'2021-05-18 13:18:23.592000','2021-05-22 11:48:40.951000','一念花开，一念花落~','https://www.wujunchao.top/wp-content/uploads/2021/04/IU.jpg','春天和爱情の樱花',100,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.wujunchao.top'),(109,'2021-05-27 15:15:56.445000','2021-05-27 15:15:56.445000','描述： 不只是一杯咖啡！','https://368368.cappuccinoj.cn/c00c67a2005660b8c2e963f4d7aa05ef.jpg','Cappuccino',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://cappuccinoj.cn'),(138,'2021-06-07 18:52:03.528000','2021-06-07 18:52:03.528000','绿衣捧砚催题卷,红袖添香伴读书。','https://applyset.xyz/upload/2021/06/Logo_40-a432f569997d45298135455361526b03.png','知兮寒兮',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://applyset.xyz'),(140,'2021-06-23 15:46:00.780000','2021-06-23 15:46:00.780000','近乎永恒不变者，唯你我头上的，同一片星天','https://www.shuigod.com/upload/2020/08/%E5%BE%AE%E4%BF%A1%E5%9B%BE%E7%89%87_20200811095259-afee533a9de44e559f2c71011e59b1df.jpg','fv_tk',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.shuigod.com'),(141,'2021-06-23 15:47:24.759000','2021-06-23 15:47:24.759000','一名非典型程序员','http://www.buukle.top/upload/2021/03/logo-bb9cfa9de19947f0bbda5a315212e11e.png','布壳儿',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','http://www.buukle.top/'),(142,'2021-06-23 17:15:15.634000','2021-06-23 17:15:15.634000','寒冻三尺非此界，烈日焦灼正午时！','https://whooc.com/logo','风呼呼',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://whooc.com/'),(143,'2021-06-25 10:51:54.361000','2021-06-25 10:51:54.361000','知道的越多，不知道的就越多。','https://www.zhhc.cc/upload/2021/05/B7FF5365-0101-4DEE-92E7-AA65CCB7182A-dc7225097d1842aead2dae76085c4aa7.jpeg','ZHC',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.zhhc.cc'),(144,'2021-06-27 08:33:12.510000','2021-06-27 08:33:12.510000','人生的三大错觉之一：TA喜欢我','https://www.daadn.cn/avatar','蛋挞博客',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.daadn.cn'),(145,'2021-06-30 07:23:20.054000','2021-06-30 07:23:20.054000','你的压力来源于无法自律，只是假装努力，现状跟不上内心欲望，所以你焦虑又恐慌。——杨不易|?','https://www.yangbuyi.top/avatar','杨不易呀',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.yangbuyi.top/'),(171,'2021-07-06 11:35:02.664000','2021-07-06 11:35:02.664000','','https://ronaldoxzb.com/upload/2021/07/1-cb92dc0184d44fcd99102b90dec651a9.jpeg','ronaldoxzb',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://ronaldoxzb.com/'),(203,'2021-07-06 15:43:32.608000','2021-07-06 15:43:32.608000','梅干菜你个小酥饼哦。','https://img.zeekling.cn/images/2020/02/23/logo.th.png','小令童鞋',0,'<div class=\"note warning\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 大佬 —— 没有不劳而获的工作，更没有坐享其成的收获</div>','https://www.zeekling.cn'),(207,'2021-07-06 16:02:10.424000','2021-07-06 16:02:10.424000','技术小白的小世界','https://cdn.xn2001.com/blog/avatar.jpg','乐心湖\'s Blog',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.xn2001.com'),(213,'2021-07-21 19:41:08.323000','2021-07-21 19:46:49.054000','吾生也有涯，而知也无涯。','https://simplestark.top/upload/2021/03/10032-8ed3782d0fc64616a87ca1812b62197f.jpg','SimpleStark',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://simplestark.top/'),(214,'2021-07-21 20:21:07.127000','2021-07-21 20:21:07.127000','学而不厌 不耻下问','https://lordblog.cn/avatar','@小佑_',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://lordblog.cn/'),(215,'2021-07-21 20:30:58.073000','2021-07-21 20:30:58.073000','蜉蝣朝生而暮死，而尽其乐','http://halo-blog-1259543051.cos.ap-chengdu.myqcloud.com/halo-blog/wanwuLog_1615189726914.jpg','城北徐公',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://cbxg.icu/'),(218,'2021-08-02 17:16:24.603000','2021-08-02 17:16:24.603000','提桶跑路的郊区工厂打工人。','https://www.996workers.icu/upload/2021/07/69796042-8238546f3c0345509e6d36b186bf13c7.jpg','996 worker',0,'<div class=\"note info\" style=\"width:100%;font-size:19px;font-family: cursive,Noto Serif SC,sans-serif;margin: 30px 0 10px 0;\">? 邻居 —— 相逢一醉是前缘，风雨散、飘然何处</div>','https://www.996workers.icu');
UNLOCK TABLES;
评论区