MySQL中的索引如何优化

63次阅读
没有评论

共计 3147 个字符,预计需要花费 8 分钟才能阅读完成。

这篇文章主要介绍了 MySQL 中的索引如何优化的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇 MySQL 中的索引如何优化文章都会有所收获,下面我们一起来看看吧。

使用索引优化

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的 MySQL 的性能优化问题。

数据准备

use world;
 
 
create table tb_seller(sellerid varchar(100),
 name varchar(100),
 nickname varchar(50),
 password varchar(60),
 status varchar(1),
 address varchar(100),
 createtime datetime,
 primary key(sellerid)
 
 
insert into tb_seller values(alibaba , 阿里巴巴 , 阿里小店 , e10adc3949ba59abbe057f20f883e , 1 , 北京市 , 2088-01-01 12:00:00),
 (baidu , 百度科技有限公司 , 百度小店 , e10adc3949ba59abbe057f20f883e , 1 , 北京市 , 2088-01-01 12:00:00),
 (huawei , 华为科技有限公司 , 华为小店 , e10adc3949ba59abbe057f20f883e , 0 , 北京市 , 2088-01-01 12:00:00),
 (itcast , 传智播客教育科技有限公司 , 传智播客 , e10adc3949ba59abbe057f20f883e , 1 , 北京市 , 2088-01-01 12:00:00),
 (itheima , 黑马程序员 , 黑马程序员 , e10adc3949ba59abbe057f20f883e , 0 , 北京市 , 2088-01-01 12:00:00),
 (luoji , 罗技科技有限公司 , 罗技小店 , e10adc3949ba59abbe057f20f883e , 1 , 北京市 , 2088-01-01 12:00:00),
 (oppo , oppo 科技有限公司 , oppo 官方旗舰店 , e10adc3949ba59abbe057f20f883e , 0 , 北京市 , 2088-01-01 12:00:00),
 (ourpalm , 掌趣科技股份有限公司 , 掌趣小店 , e10adc3949ba59abbe057f20f883e , 1 , 北京市 , 2088-01-01 12:00:00),
 (qiandu , 千度科技 , 千度小店 , e10adc3949ba59abbe057f20f883e , 2 , 北京市 , 2088-01-01 12:00:00),
 (sina , 新浪科技有限公司 , 新浪官方旗舰店 , e10adc3949ba59abbe057f20f883e , 1 , 北京市 , 2088-01-01 12:00:00),
 (xiaomi , 小米科技 , 小米官方旗舰店 , e10adc3949ba59abbe057f20f883e , 1 , 西安市 , 2088-01-01 12:00:00),
 ( yijia , 宜家家居 , 宜家官方旗舰店 , e10adc3949ba59abbe057f20f883e , 1 , 北京市 , 2088-01-01 12:00:00 
 
--  创建组合索引
create index index_seller_name_sta_addr on tb_seller(name,status,address);

避免索引失效应用 - 全值匹配

该情况下,索引生效,执行效率高。

--  避免索引失效应用 - 全值匹配
--  全值匹配,和字段匹配成功即可,和字段顺序无关
explain select * from tb_seller ts where name = 小米科技  and status = 1  and address = 北京市 
 
explain select * from tb_seller ts where status = 1  and name = 小米科技  and address = 北京市

避免索引失效应用 - 最左前缀法则

该情况下,索引生效, 执行效率高。

--  避免索引失效应用 - 最左前缀法则
--  如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始,并且不跳过索引中的列
explain select * from tb_seller ts where name= 小米科技 -- key_lem:403
explain select * from tb_seller ts where name= 小米科技  and status = 1 -- key_lem:410
explain select * from tb_seller ts where status = 1  and name= 小米科技  ;-- key_lem:410,依然跟顺序无关
 
--  违反最左前缀法则,索引失效
explain select * from tb_seller ts where status = 1 --  违反最左前缀法则,索引失效
 
--  如果符合最左前缀法则,但是出现跳跃某一列,只有最左列索引生效
explain select * from tb_seller where name= 小米科技  and address= 北京市 -- key_lem:403

避免索引失效应用 - 其他匹配原则

该情况下,索引生效,执行效率高。

1、情况一

--  避免索引失效应用 - 其他匹配原则
--  范围查询右边的列,不能使用索引
explain select * from tb_seller where name=  小米科技  and status  1  and address= 北京市 -- key_lem:410, 没有使用 status 这个索引
--  不要在索引列上进行运算操作,索引将失效。explain select * from tb_seller where substring(name,3,2) = 科技 --  没有使用索引
--  字符串不加单引号,造成索引失效。explain select * from tb_seller where name= 小米科技  and status = 1 ;-- key_lem:403,没有使用 status 这个索引

2、情况二

explain 中的 extra 列

extra 含义 using filesort 说明 mysq| 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,称为“文件排序 , 效率低。using  temporary 需要建立临时表 (temporary table) 来暂存中间结果,常见于 order by 和 group by; 效率低 using  indexSQL 所需要返回的所有列数据均在一棵索引树上,避免访问表的数据行,效率不错 using where 在查找使用索引的情况下,需要回表去查询所需的数据 using index condition 查找使用了索引,但是需要回表查询数据 using index;using where 查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据

但是再加有个 password

 3、情况三

4、情况四

MySQL 中的索引如何优化

MySQL 中的索引如何优化

MySQL 中的索引如何优化

5、如果 MySQL 评估使用索引比全表更慢,则不使用索引。is NULL , is NOT NULL 有时有效, 有时索引失效。in 走索引,not in 索引失效。单列索引和复合索引,尽量使用符合索引

MySQL 中的索引如何优化

MySQL 中的索引如何优化

MySQL 中的索引如何优化

验证

MySQL 中的索引如何优化

创建了单一的三个索引,最后面 where 全使用了但 explain 显示只用了 index_name

关于“MySQL 中的索引如何优化”这篇文章的内容就介绍到这里,感谢各位的阅读!相信大家对“MySQL 中的索引如何优化”知识都有一定的了解,大家如果还想学习更多知识,欢迎关注丸趣 TV 行业资讯频道。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-13发表,共计3147字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)