MySQL索引失效原理是什么? 刺骨的言语ヽ痛彻心扉 2024-04-06 10:39 22阅读 0赞 目录 索引创建原则 避免索引失效 (1)全值匹配,对索引中所有列都指定具体值 (2)最左前缀法则 (3)范围查询右边的列,不能使用索引 (4)不要在索引列上进行运算操作,索引将失效 (5)字符串不加单引号,造成索引失效 (6)用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到 (7)以%开头的Like模糊查询,索引失效。 (8)如果MySQL评估使用索引比全表更慢,则不使用索引。 (9)in 走索引, not in 索引失效 索引创建原则 索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高效的使用索引。 1. 字段内容可识别度不能低于70%,字段内数据唯一值的个数不能低于70% 例如:一个表数据只有50行,那么性别和年龄哪个字段适合创建索引,明显是年龄,因为年龄的唯一值个数比较多,性别只有两个选项 。性别的识别度是50%。 男 女 2. 经常使用where条件搜索的字段,例如user表的id name等字段。 3. 经常使用表连接的字段(内连接、外连接),可以加快连接的速度。 4. 经常排序的字段 order by,因为索引已经是排过序的,这样一来可以利用索引的排序,加快排序查询速度。 \* 注意:那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。因为索引的建立和维护都是需要耗时的 创建表时需要通过数据库去维护索引,添加记录、更新、修改时,也需要更新索引,会间接影响数据库的 效率。 避免索引失效 (1)全值匹配,对索引中所有列都指定具体值 该情况下,索引生效,执行效率高。 explain select \* from tb\_seller where name='小米科技有限公司' and status='1' and address='上海市'; ![f09bfd9d57ef4aea98bc3189c4a6dc3a.png][] (2)最左前缀法则 如果索引了多列,这里指的是复合索引(联合索引),要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。 注意:如果条件中包含了复合索引的全部字段,那么可以不考虑前后顺序。 ![f441bad616324f3f870571fcce43aa86.png][] 匹配最左前缀法则,走索引: 1.explain select \* from tb\_seller where name='小米科技有限公司'; -- key\_len表示索引字段的长度即占字节个数,不同的编码表计算方式不一致 2.explain select \* from tb\_seller where name='小米科技有限公司' and status='1'; 3.explain select \* from tb\_seller where name='小米科技有限公司' and status='1' and address='上海市'; ![d6ca9a6214df47659b4bd5096ceb01fd.png][] 违反最左前缀法则 , 索引失效: 4.explain select \* from tb\_seller where status='1'; 5.explain select \* from tb\_seller where status='1' and address='上海市'; ![fd136f15a2de4fbc9f9a343d63c53cb0.png][] 如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效: 6.explain select \* from tb\_seller where name='小米科技有限公司' and address='上海市'; 注意:上述sql语句跳跃了status这一列,所以上述sql语句只是对索引name生效,key\_len的结果403只是name索引的长度,而address索引字段并没有起到所以效果。 ![64d506a7cf4347b9993de2c942f7b6fd.png][] 注意:如果条件中包含了复合索引的全部字段,那么可以不考虑前后顺序。 explain select \* from tb\_seller where address='上海市' and status='1' and name='小米科技有限公司' ; ![8f385aa6778d4383a6c9e2cb0fd75e5f.png][] (3)范围查询右边的列,不能使用索引 1.explain select \* from tb\_seller where name='小米科技有限公司' and status='1' and address='上海市'; 2.explain select \* from tb\_seller where name='小米科技有限公司' and status>'1' and address='上海市'; -- 只有name和status索引生效 ![809e5bbc62f6486eb47c3d4b262ca389.png][] 根据前面的两个字段name , status 查询是走索引的, 但是最后一个条件address 没有用到索引。 (4)不要在索引列上进行运算操作,索引将失效 1.-- 3 表示索引 2 表示截取2个字符 select \* from tb\_seller where substring(name,3,2)='科技'; 2.explain select \* from tb\_seller where substring(name,3,2)='科技'; ![ee05a1ce3deb49f7be7ef0c8877ec542.png][] (5)字符串不加单引号,造成索引失效 1.explain select \* from tb\_seller where name='小米科技有限公司' and status='1'; 2.explain select \* from tb\_seller where name='小米科技有限公司' and status=1; -- 这里name索引字段生效,status索引字段是无效的 ![14ff4a25940b4ce5ac47dbf2eccdd402.png][] 由于,在查询时,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效。 (6)用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到 示例,name字段是索引列 , 而createtime不是索引列,中间是or进行连接是不走索引的 : 1.explain select \* from tb\_seller where name='传智播客教育科技有限公司' and createtime = '2088-01-01 12:00:00'; 2.explain select \* from tb\_seller where name='传智播客教育科技有限公司' or createtime = '2088-01-01 12:00:00'; ![3495391da51d4d03be106921e76816f8.png][] (7)以%开头的Like模糊查询,索引失效。 如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。 1.explain select \* from tb\_seller where name like '传智播客%'; 2.explain select \* from tb\_seller where name like '%传智播客'; 3.explain select \* from tb\_seller where name like '%传智播客%'; ![14288408890041609ab72f58f318d772.png][] 解决方案 :通过覆盖索引来解决. 1.explain select sellerid from tb\_seller where name like '%传智播客%'; 2.explain select sellerid,name from tb\_seller where name like '%传智播客%'; 3.explain select sellerid,name,status,address from tb\_seller where name like '%传智播客%'; 4.explain select sellerid,name,status,address,password from tb\_seller where name like '%传智播客%'; 说明:解决上述sql语句索引失效情况需要使用覆盖索引,而password子字段无索引,所以索引失效。 ![bcdde8003bb14a968a357f3938dd05ea.png][] (8)如果MySQL评估使用索引比全表更慢,则不使用索引。 1.show index from tb\_seller; -- 查看下索引 2.create index idx\_address on tb\_seller(address); -- 单独创建 address字段为索引 3.explain select \* from tb\_seller where address='北京市'; -- 走索引,反而效率更低,全表扫描 4.explain select \* from tb\_seller where address='上海市'; ![4b5dd71f291d4f90a56651669ebe2dae.png][] (9)in 走索引, not in 索引失效 1.explain select \* from tb\_seller where sellerid in('baidu','huawei','xiaomi'); 2.explain select \* from tb\_seller where sellerid not in('baidu','huawei','xiaomi'); ![4ac7ebc99e54419f8aeb05135a6ceac1.png][] [f09bfd9d57ef4aea98bc3189c4a6dc3a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/5c0b3b864ff442edaebf04ecd82d6a6d.png [f441bad616324f3f870571fcce43aa86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/17ff7614c472468a8150c130687b1136.png [d6ca9a6214df47659b4bd5096ceb01fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/06c3fcf05e8d4d28889846fada2a600a.png [fd136f15a2de4fbc9f9a343d63c53cb0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9c5f4e6bd9ac4efa8eac6b2ce13b3064.png [64d506a7cf4347b9993de2c942f7b6fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/ebaf553539b3455184fbc8a982d1ae6d.png [8f385aa6778d4383a6c9e2cb0fd75e5f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9468257164b3492c94902ebc18609b60.png [809e5bbc62f6486eb47c3d4b262ca389.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/79c68208fbae420e937b69935db28f14.png [ee05a1ce3deb49f7be7ef0c8877ec542.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/f1ade20339ba4b74b0bd86dc658dc7ea.png [14ff4a25940b4ce5ac47dbf2eccdd402.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/5e84fa8e7dae4dc6af1d1a2f356ff85d.png [3495391da51d4d03be106921e76816f8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e8b19523c28a4251bac55ea5e1a603fb.png [14288408890041609ab72f58f318d772.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/c20622d637fa41bda131187b5b438804.png [bcdde8003bb14a968a357f3938dd05ea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/34d28dcf17294d5e89c3ff3c9ab5d183.png [4b5dd71f291d4f90a56651669ebe2dae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/33088ce6f5c8410397eee05d19b88f5e.png [4ac7ebc99e54419f8aeb05135a6ceac1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/709dfd56cc5843e0a04c954dd991e2fb.png
还没有评论,来说两句吧...