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%';

image-20210804222419275
image-20210804222910249

##### 这里还有一种特殊情况的后置`%`也不会用到索引,就是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
    image-20210805150124637
  • int写成varchar,按道理说这个应该也是调用函数转的,母鸡,求大佬评论指导
    image-20210805150405261
    image-20210805150736375

对列进行运算,如+ - * /之类的

EXPLAIN SELECT * FROM demo WHERE age + 1 = 19;
EXPLAIN SELECT * FROM demo WHERE age = 19 - 1;

image-20210805151845032
image-20210805152021245

违反最左前缀原则(注:当时这个还未添加union_age这个索引)

EXPLAIN SELECT * FROM demo WHERE addr='北京市' AND NAME = '张三' AND age = 18;
EXPLAIN SELECT * FROM demo WHERE NAME = '张三' AND age = 18;

image-20210804224420728
image-20210804224533218

在索引列使用函数

EXPLAIN SELECT * FROM demo WHERE LEFT(telephone,3)='110'

image-20210805074756077

使用oror左右俩端不都是索引列

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'

image-20210805161926518

image-20210805162028848

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`

image-20210805164547673

image-20210805164629708

思考题

image-20210809154612819

image-20210809154644800

image-20210809154738646

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;

Q.E.D.


一个热爱生活的95后精神小伙