SQL 必知必会
陈旸
清华大学计算机博士
73338 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 50 讲
第一章:SQL语法基础篇 (19讲)
SQL 必知必会
15
15
1.0x
00:00/00:00
登录|注册

26丨索引的使用原则:如何通过索引让SQL查询效率最大化?

在使用联合索引的时候要注意最左原则
索引列尽量设置为NOT NULL约束
使用LIKE进行模糊查询的时候,前面不能是%
在WHERE子句中,如果在OR前的条件列进行了索引,而在OR后的条件列没有进行索引
对索引使用函数
索引进行了表达式计算
频繁更新的字段
字段中有大量重复数据
表记录太少
WHERE条件里用不到的字段不需要创建索引
做多表JOIN连接操作时
DISTINCT字段需要创建索引
UPDATE、DELETE的WHERE条件列
需要经常GROUP BY和ORDER BY的列
频繁作为WHERE查询条件的字段
字段的数值有唯一性的限制
什么情况下索引失效
什么时候不需要创建索引
什么情况下使用索引?
索引的使用原则

该思维导图由 AI 生成,仅供参考

我之前讲了索引的使用和它的底层原理,今天我来讲一讲索引的使用原则。既然我们的目标是提升 SQL 的查询效率,那么该如何通过索引让效率最大化呢?
今天的课程主要包括下面几个部分:
什么情况下使用索引?当我们进行数据表查询的时候,都有哪些特征需要我们创建索引?
索引不是万能的,索引设计的不合理可能会阻碍数据库和业务处理的性能。那么什么情况下不需要创建索引?
创建了索引不一定代表一定用得上,甚至在有些情况下索引会失效。哪些情况下,索引会失效呢?又该如何避免这一情况?

创建索引有哪些规律?

创建索引有一定的规律。当这些规律出现的时候,我们就可以通过创建索引提升查询效率,下面我们来看看什么情况下可以创建索引:
1. 字段的数值有唯一性的限制,比如用户名
索引本身可以起到约束的作用,比如唯一索引、主键索引都是可以起到唯一性约束的,因此在我们的数据表中,如果某个字段是唯一性的,就可以直接创建唯一性索引,或者主键索引。
2. 频繁作为 WHERE 查询条件的字段,尤其在数据表大的情况下
在数据量大的情况下,某个字段在 SQL 查询的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。
我之前列举了 product_comment 数据表,这张数据表中一共有 100 万条数据,假设我们想要查询 user_id=785110 的用户对商品的评论。
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

本文总结了如何通过索引让SQL查询效率最大化的原则。首先,文章介绍了何时需要创建索引,包括字段具有唯一性限制、频繁作为WHERE查询条件的字段、需要经常GROUP BY和ORDER BY的列、UPDATE、DELETE的WHERE条件列以及DISTINCT字段。其次,文章强调了创建索引的规律,包括对字段创建索引以提升查询效率,并且在多表JOIN连接操作时需要注意连接表的数量、对WHERE条件和用于连接的字段创建索引的原则。最后,文章通过具体的案例分析了创建索引对SQL查询效率的影响,强调了合理创建索引对提升数据库查询性能的重要性。文章还提到了避免索引失效的方法,包括避免对索引进行表达式计算、避免对索引使用函数、注意在WHERE子句中的条件列是否都进行了索引等。总的来说,文章强调了合理创建和使用索引对提升数据库查询性能的重要性,同时也指出了避免索引失效的方法,为读者提供了实用的技术指导。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《SQL 必知必会》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(60)

  • 最新
  • 精选
  • wusiration
    索引失效,因为使用了date函数。改成SELECT comment_id, comment_text, comment_time FROM product_comment WHERE comment_time BETWEEN DATE('2018-10-01 10:00:00') AND DATE('2018-10-02 10:00:00')

    作者回复: Good Job

    2019-08-09
    9
    50
  • Yuhui
    老师您好!请教一下如何查找“不经常使用的“索引呢?谢谢!

    作者回复: 你可以查看下MySQL中的performance_schema.table_io_waits_summary_by_index_usage数据表,它表明了每个索引进行统计的I/O等待事件,其中COUNT_STAR代表了事件的次数。过滤掉一些系统表,查看下数据表中有哪些索引不经常使用,具体的SQL语句:SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,COUNT_STAR FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA != 'mysql' AND OBJECT_SCHEMA != 'performance_schema'

    2019-09-19
    35
  • 佚花
    关于like. %在左边,即使有索引,也会失效. 只有当%在右边时,才会生效

    作者回复: 对的 如果%在左侧就是前模糊匹配,有索引也会失效。所以字符串进行匹配的时候,需要最左侧是个明确的字符才能使用上索引。

    2019-08-21
    8
  • haer
    索引失效,因为使用了date函数

    作者回复: 对的

    2019-08-09
    7
  • Vackine
    关于关系型数据库模型介绍的论文,老师有推荐的么✨

    作者回复: 一本经典的书《Towards a Logical Reconstruction of Relational Database Theory》 另外关系型数据库里面也有不同的使用场景,比如关于图像检索的 《Chabot: Retrieval from a Relational Database of Images》 查询XML的: 《Storing and querying ordered XML using a relational database system》

    2019-08-09
    5
  • Goal
    老师,今天文章中的“product_comment”表结构和数据,是从哪里导入的呢? 个人感觉,本课程用到的所有表都可以放到一个统一的地方,比如之前的 GitHub上面,方便我们统一下载。

    作者回复: 因为文件大于100M,就先放到百度网盘:https://pan.baidu.com/s/1LBEAm50DDP9AjErLtGplLg 提取码:32ep 多谢建议,我把它也放到GitHub上,大于100M的给出百度网盘的链接

    2019-08-13
    4
  • ABC
    索引会失效,因为使用了date函数。 如果修改的话,可以用between和and,对查询条件进行转换。 例如:currtime between date('2018-01-10 10:00:00) and date('2018-02-10 12:00:00') 手机回复,没有实际运行,如有错误请老师指正,谢谢

    作者回复: Good Job

    2019-08-09
    4
  • rike
    “按照 user_id 进行评论分组,同时按照评论时间降序的方式进行排序”,执行对应的sql后,报错。望大神验证一下,不要误导付费学习的读者。

    作者回复: 应该是可以运行的,rike同学再试试

    2019-12-27
    3
    3
  • 抢小孩子糖吃
    老师 如果我们给女儿国的性别加上了索引 我们查看男性的话会快很多 但如果我们有时需要查看男性 有时需要查看女性 还适合在性别上建索引吗 查看女性的时候优化器会选择用这个索引找数据吗

    作者回复: 这个看需求,对于某个疾病(发病率很低)的正样本,可以选择创建索引。如果性别男女都需要查看,可以不建索引,对于女性,优化器也不会使用索引进行查找。

    2019-11-23
    3
  • niemo
    老师 您好,sql条件执行顺序不是从右到左么?所有在使用联合索引的时候,把最左的索引写在where条件的最右边,这样理解对么?

    作者回复: SQL执行顺序: SELECT DISTINCT player_id, player_name, count(*) as num #顺序5 FROM player JOIN team ON player.team_id = team.team_id #顺序1 WHERE height > 1.80 #顺序2 GROUP BY player.team_id #顺序3 HAVING num > 2 #顺序4 ORDER BY num DESC #顺序6 LIMIT 2 #顺序7

    2019-08-09
    3
收起评论
显示
设置
留言
60
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部