最全MySQL8.0实战教程 22 MySQL的优化 22.7 使用索引优化【4 避免索引失效应用 - 其他匹配原则】

一时失言乱红尘 2024-03-25 12:32 40阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  22 MySQL的优化
         *   *  22.7 使用索引优化【4 避免索引失效应用 - 其他匹配原则】

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 22 MySQL的优化 ####

##### 22.7 使用索引优化【4 避免索引失效应用 - 其他匹配原则】 #####

-- 1、范围查询右边的列，不能使用索引 。
    -- 根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。
    explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市';

![在这里插入图片描述][a423c13f5320466dacd9bd4ee0dff44b.png_pic_center]

用到address的话应该是813.

-- 2、不要在索引列上进行运算操作， 索引将失效。
    explain select * from tb_seller where substring(name,3,2)='科技';

![在这里插入图片描述][9dae741de12648cfb5a1cc16a20ff2cd.png_pic_center]

-- 3、字符串不加单引号，造成索引失效。 
    explain select * from tb_seller where name='小米科技' and status = 1 ;

![在这里插入图片描述][a3729c99100c4c5e83d3d6ebfe471292.png_pic_center]

不加单引号不影响查询，是因为MySQL会自动进行优化。

-- 4、尽量使用覆盖索引，避免select *
    -- 需要从原表及磁盘上读取数据
    explain select * from tb_seller where name='小米科技'  and address='北京市';  -- 效率低

![在这里插入图片描述][b267acad02bd4e3dbeda9f41e33ac6bd.png_pic_center]

不用\*

![在这里插入图片描述][8dfd211734864be596097e2a03771d99.png_pic_center]

-- 从索引树中就可以查询到所有数据
    explain select name from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
    explain select name,status,address from tb_seller where name='小米科技'  and address='北京市';  -- 效率高

![在这里插入图片描述][61616657455e46599c305324dbbbee72.png_pic_center]

![在这里插入图片描述][a0f9f4f9a5bc44509dc4d64765dab1a0.png_pic_center]

-- 如果查询列，超出索引列，也会降低性能。
    explain select name,status,address,password from tb_seller where name='小米科技'  and address='北京市';  -- 效率低

![在这里插入图片描述][5a38d30923564bdeb3540798909c1a3b.png_pic_center]

Extra解释：

*  using index：使用覆盖索引的时候就会中现
 *  using where:在查找使用索引的情况下，需要回表去查询所需的数据
 *  using index condition:查找使用了索引，但是需要回表查询数据
 *  using index;using where:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

-- 用or分割开的条件， 那么涉及的索引都不会被用到。
    explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'; 
    explain select * from tb_seller where name='黑马程序员' or address = '西安市';  
    explain select * from tb_seller where name='黑马程序员' or status = '1';

![在这里插入图片描述][61818172f4f343bebe7c2622af5d3762.png_pic_center]

![在这里插入图片描述][85529cf93a244b9e928d5b4f214de88f.png_pic_center]

![在这里插入图片描述][d7a82d700f164028b363d92ee91aa2ee.png_pic_center]

-- 以%开头的Like模糊查询，索引失效。
    explain select * from tb_seller where name like '科技%'; -- 用索引
    explain select * from tb_seller where name like '%科技'; -- 不用索引
    explain select * from tb_seller where name like '%科技%';-- 不用索引
    -- 弥补不足,不用*，使用索引列
    explain select name from tb_seller where name like '%科技%';

![在这里插入图片描述][f0c5515d69694968b75c81b2755bf08f.png_pic_center]

![在这里插入图片描述][eb0432b4d22e40b9afa34460ad0df5f3.png_pic_center]

--  1、如果MySQL评估使用索引比全表更慢，则不使用索引。
    -- 这种情况是由数据本身的特点来决定的
    create index index_address on tb_seller(address);

![在这里插入图片描述][70e6e645103f48e3b4fb4fff4bad20ee.png_pic_center]

explain select * from tb_seller where address = '北京市'; -- 没有使用索引
    explain select * from tb_seller where address = '西安市'; -- 没有使用索引

![在这里插入图片描述][837827b0ad1641168a01242acf6e8d40.png_pic_center]

--  2、is  NULL ， is NOT NULL  有时有效，有时索引失效。
    create index index_nickname on tb_seller(nickname);
    explain select * from tb_seller where nickname is NULL;  -- 索引有效
    explain select * from tb_seller where nickname is not NULL; -- 无效

![在这里插入图片描述][74283c9f59634f23b483c32c599a695e.png_pic_center]

![在这里插入图片描述][94fd4350ceb448529c6f2cada85ada7a.png_pic_center]

-- in 走索引，not in 索引失效
    explain select * from tb_seller where nickname in('阿里小店','百度小店');
    explain select * from tb_seller where nickname not in('阿里小店','百度小店');

![在这里插入图片描述][7eda5bb6528942008757cc973f8ad052.png_pic_center]

比较特殊的是如果是主键索引，不管是in 还是 not in 都会使用索引

![在这里插入图片描述][b120d7410a4f46f89ba9ede923ee7934.png_pic_center]

create index idx_seller_name_sta_addr on tb_seller(name,status,address);
    /*
    	之前我们搞这个组合索引
    	其实等价于：
    		name
    		name + status
    		name + status + address
    */

-- 如果一张表有多个单列索引，即使where中都使用了这些索引列，则只有一个最优索引生效【最优由优化器决定】
    drop index idx_seller_name_sta_addr on tb_seller;

![在这里插入图片描述][80df13114427414d9f61fed3d16254ef.png_pic_center]

先把这个组合索引删掉，并创建三个单列索引

-- 创建三个单列索引
    create index index_name on tb_seller(name);
    create index index_status on tb_seller(status);
    create index index_address on tb_seller(address);

![在这里插入图片描述][eda4c0c45c9648ab8ed074f47bd203eb.png_pic_center]

explain select * from tb_seller where name='小米科技' and status='1' and address='西安市';

三个只用了一个

![在这里插入图片描述][29ea634d54e34ab999dab58ea813626a.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[a423c13f5320466dacd9bd4ee0dff44b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/d43b5c3ea7b74c578ec67380f8d231c1.png
[9dae741de12648cfb5a1cc16a20ff2cd.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5d499392fe7f4b5a87fd388354cc4ff6.png
[a3729c99100c4c5e83d3d6ebfe471292.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/920b653e1ad249cdbc38d760e50fbb23.png
[b267acad02bd4e3dbeda9f41e33ac6bd.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/8fa7d51472844950b6d4c471c6480e6c.png
[8dfd211734864be596097e2a03771d99.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/48ec15f5ea8d4e2093e75eb8701b1da5.png
[61616657455e46599c305324dbbbee72.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c6d372c8297946bbb0f3df8db8fcd179.png
[a0f9f4f9a5bc44509dc4d64765dab1a0.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/052a0d10af5842a199ccbb29f0c73c0e.png
[5a38d30923564bdeb3540798909c1a3b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a8577662b10e440aa611f28b51d8d2b8.png
[61818172f4f343bebe7c2622af5d3762.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/32e1691788ae4d9aabb0733cf887204d.png
[85529cf93a244b9e928d5b4f214de88f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/79c31ee71906466ebdc23a22e08ac5ab.png
[d7a82d700f164028b363d92ee91aa2ee.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/28cb1d981fc0458cb3430d5dc22ee88b.png
[f0c5515d69694968b75c81b2755bf08f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/632e6639db2047e69db85ac357bbd25e.png
[eb0432b4d22e40b9afa34460ad0df5f3.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/658643dbd17c4f779fcf41dad0d09bfc.png
[70e6e645103f48e3b4fb4fff4bad20ee.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/9c3d87163bc546cf8436e8e11cdd47ff.png
[837827b0ad1641168a01242acf6e8d40.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5fee80955c174aefa734105a4b528743.png
[74283c9f59634f23b483c32c599a695e.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/035cda3b302a4ca68e456904eacd7a12.png
[94fd4350ceb448529c6f2cada85ada7a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/027ab9e482394f10b4e30c50aecc5932.png
[7eda5bb6528942008757cc973f8ad052.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/8ff862384ad043c1b2d7cb2f9cc94b5b.png
[b120d7410a4f46f89ba9ede923ee7934.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/fc442bca6134484d8696fcd171d52bd6.png
[80df13114427414d9f61fed3d16254ef.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5877f446c0814eaf928f0663b7b3c5d3.png
[eda4c0c45c9648ab8ed074f47bd203eb.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ba5064d2579941c8b9d7b235623449f9.png
[29ea634d54e34ab999dab58ea813626a.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/97b721ac08894b6b974527e0120c50e8.png