作者:曹理鹏
原文地址:https://icocos.github.io
面试知识,数据库索引优化
1.什么问题?
索引有什么代价?哪些场景下你需要建索引?或者有时候反过来问,哪些场景下不推荐建索引。
建好索引之后,怎么才能最高效地利用索引?或者反过来问,请说出一个无法有效利用已建索引的案例。
2.索引的好处?
快速查询数据。
3.代价是什么?
索引需要占硬盘空间,这是空间方面的代价。
一旦插入新的数据,就需要重新建索引,这是时间上的代价。
4.不同场景,不同对待。
4.1 场景一
数据表规模不大,就几千行,即使不建索引,查询语句的返回时间也不长,这时建索引的意义就不大。当然,若就几千行,索引所占的空间也不多,所以这种情况下,顶多属于“性价比”不高。
4.2 场景二
某个商品表里有几百万条商品信息,同时每天会在一个时间点,往其中更新大概十万条左右的商品信息,现在用where语句查询特定商品时(比如where name = ‘XXX’)速度很慢。为了提升查询效率可以建索引,但当每天更新数据时,又会重建索引,这是要耗费时间的。这时就需要综合考虑,甚至可以在更新前删除索引,更新后再重建。
4.3 场景三
因为在数据表里ID值都不相同,所以索引能发挥出比较大的作用。相反,如果某个字段重复率很高,如性别字段,或者某个字段大多数值是空(null),那么不建议对该字段建索引。
5. 建立索引原则
一定是有业务需求了才会建索引。比如在一个商品表里,我们经常要根据name做查询,如果没有索引,查询速度会很慢,这时就需要建索引。但在项目开发中,如果不经常根据商品编号查询,那么就没必要对编号建索引。
最后再强调一次,建索引是要付出代价的,没事别乱建着玩,同时在一个表上也不能建太多的索引。
具体的例子来看索引的正确用法
语句一:select name from 商品表。不会用到索引,因为没有where语句。
语句二:select * from 商品表 where name = ‘Java书’,会用到索引,如果项目里经常用到name来查询,且商品表的数据量很大,而name值的重复率又不高,那么建议建索引。
语句三:select * from 商品表 where name like ‘Java%’ 这是个模糊查询,会用到索引,请大家记住,用like进行模糊查询时,如果第一个就是模糊的匹配符,比如where name like ‘%java’,那么在查询时不会走索引。在其他情况下,不论用了多少个%,也不论%的位置,只要不出现在第一个位置,那么都能用到索引。
学生成绩表里有两个字段:姓名和成绩。现在对成绩这个整数类型的字段建索引。
第一种情况:当数字型字段遇到非等值操作符时,无法用到索引。比如:
select name from 学生成绩表 where 成绩>95 , 一旦出现大于符号,就不能用到索引,为了用到索引,我们应该改一下SQL语句里的where从句:where 成绩 in (96,97,98,99,100)
第二种情况: 如果对索引字段进行了某种左值操作,那么无法用到索引。
能用到索引的写法:select name from 学生成绩表 where 成绩 = 60
不能用到索引的写法:select name from 学生成绩表 where 成绩+40 = 100
第三种情况: 如果对索引字段进行了函数操作,那么无法用到索引。
比如SQL语句:select * from 商品表 where substr(name) = ‘J’,我们希望查询商品名首字母是J的记录,可一旦针对name使用函数,即使name字段上有索引,也无法用到。
非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据。
不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。
其他
文末
欢迎关注个人微信公众号:Coder编程
欢迎关注Coder编程公众号,主要分享数据结构与算法、Java相关知识体系、框架知识及原理、Spring全家桶、微服务项目实战、DevOps实践之路、每日一篇互联网大厂面试或笔试题以及PMP项目管理知识等。更多精彩内容正在路上~
也分享一些杂文~
文章收录至
微信公众号
Github: https://github.com/CoderMerlin/coder-programming
Gitee: https://gitee.com/573059382/coder-programming
欢迎关注并star~
网友评论