SQL必知必会
陈旸
清华大学计算机博士
立即订阅
10179 人已学习
课程目录
已完结 49 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词丨SQL可能是你掌握的最有用的技能
免费
第一章:SQL语法基础篇 (19讲)
01丨了解SQL:一门半衰期很长的语言
02丨DBMS的前世今生
03丨学会用数据库的方式思考SQL是如何执行的
04丨使用DDL创建数据库&数据表时需要注意什么?
05丨检索数据:你还在SELECT * 么?
06丨数据过滤:SQL数据过滤都有哪些方法?
07丨什么是SQL函数?为什么使用SQL函数可能会带来问题?
08丨什么是SQL的聚集函数,如何利用它们汇总表的数据?
09丨子查询:子查询的种类都有哪些,如何提高子查询的性能?
10丨常用的SQL标准有哪些,在SQL92中是如何使用连接的?
11丨SQL99是如何使用连接的,与SQL92的区别是什么?
12丨视图在SQL中的作用是什么,它是怎样工作的?
13丨什么是存储过程,在实际项目中用得多么?
14丨什么是事务处理,如何使用COMMIT和ROLLBACK进行操作?
15丨初识事务隔离:隔离的级别有哪些,它们都解决了哪些异常问题?
16丨游标:当我们需要逐条处理数据时,该怎么做?
17丨如何使用Python操作MySQL?
18丨SQLAlchemy:如何使用Python ORM框架来操作MySQL?
19丨基础篇总结:如何理解查询优化、通配符以及存储过程?
第二章:SQL性能优化篇 (18讲)
20丨当我们思考数据库调优的时候,都有哪些维度可以选择?
21丨范式设计:数据表的范式有哪些,3NF指的是什么?
22丨反范式设计:3NF有什么不足,为什么有时候需要反范式设计?
23丨索引的概览:用还是不用索引,这是一个问题
24丨索引的原理:我们为什么用B+树来做索引?
25丨Hash索引的底层原理是什么?
26丨索引的使用原则:如何通过索引让SQL查询效率最大化?
27丨从数据页的角度理解B+树查询
28丨从磁盘I/O的角度理解SQL查询的成本
29丨为什么没有理想的索引?
30丨锁:悲观锁和乐观锁是什么?
31丨为什么大部分RDBMS都会支持MVCC?
32丨查询优化器是如何工作的?
33丨如何使用性能分析工具定位SQL执行慢的原因?
34丨答疑篇:关于索引以及缓冲池的一些解惑
35丨数据库主从同步的作用是什么,如何解决数据不一致问题?
36丨数据库没有备份,没有使用Binlog的情况下,如何恢复数据?
37丨SQL注入:你的SQL是如何被注入的?
第三章:认识DBMS (7讲)
38丨如何在Excel中使用SQL语言?
39丨WebSQL:如何在H5中存储一个本地数据库?
40丨SQLite:为什么微信用SQLite存储聊天记录?
41丨初识Redis:Redis为什么会这么快?
42丨如何使用Redis来实现多用户抢票问题
43丨如何使用Redis搭建玩家排行榜?
44丨DBMS篇总结和答疑:用SQLite做词云
第四章:SQL项目实战 (3讲)
45丨数据清洗:如何使用SQL对数据进行清洗?
46丨数据集成:如何对各种数据库进行集成和转换?
47丨如何利用SQL对零售数据进行分析?
结束语 (1讲)
结束语 | 互联网的下半场是数据驱动的时代
SQL必知必会
登录|注册

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

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

创建索引有哪些规律?

创建索引有一定的规律。当这些规律出现的时候,我们就可以通过创建索引提升查询效率,下面我们来看看什么情况下可以创建索引:
1. 字段的数值有唯一性的限制,比如用户名
索引本身可以起到约束的作用,比如唯一索引、主键索引都是可以起到唯一性约束的,因此在我们的数据表中,如果某个字段是唯一性的,就可以直接创建唯一性索引,或者主键索引。
2. 频繁作为 WHERE 查询条件的字段,尤其在数据表大的情况下
在数据量大的情况下,某个字段在 SQL 查询的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。
我之前列举了 product_comment 数据表,这张数据表中一共有 100 万条数据,假设我们想要查询 user_id=785110 的用户对商品的评论。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《SQL必知必会》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(33)

  • 我行我素
    4. 当我们使用 LIKE 进行模糊查询的时候,应该是前面不能是 % 吧
    2019-08-09
    2
    17
  • 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')
    2019-08-09
    1
    9
  • 梦想天空
    老师 您好。使用selet * from T where a<4 or a=9; a有索引,但还是全盘扫描,不知道什么原因
    2019-08-10
    3
    3
  • 黑山老妖
    老师 SELECT user_id, count(*) as num FROM product_comment group by user_id order by comment_time desc limit 100
    这个例子中 对(comment_time,user_id)进行索引 ,老师不是说按照最左原则,索引会失效嘛 为什么还是会起作用,望老师解答 :)
    2019-08-10
    6
    2
  • haer
    索引失效,因为使用了date函数
    2019-08-09
    2
  • 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
    1
  • 佚花
    关于like.
    %在左边,即使有索引,也会失效.
    只有当%在右边时,才会生效

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

    2019-08-21
    1
  • 悟空
    老师,今天文章中的“product_comment”表结构和数据,是从哪里导入的呢?

    个人感觉,本课程用到的所有表都可以放到一个统一的地方,比如之前的 GitHub上面,方便我们统一下载。

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

    2019-08-13
    1
  • ABC
    索引会失效,因为使用了date函数。

    如果修改的话,可以用between和and,对查询条件进行转换。

    例如:currtime between date('2018-01-10 10:00:00) and date('2018-02-10 12:00:00')

    手机回复,没有实际运行,如有错误请老师指正,谢谢
    2019-08-09
    1
  • niemo
    老师 您好,sql条件执行顺序不是从右到左么?所有在使用联合索引的时候,把最左的索引写在where条件的最右边,这样理解对么?
    2019-08-09
    1
  • 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
    1
  • PumpkinPie
    KLOOK校招面试的时候就问了索引,那时候对索引一概不通,这回得在专栏补回来。
    2019-12-04
  • 抢小孩子糖吃
    老师 如果我们给女儿国的性别加上了索引 我们查看男性的话会快很多
    但如果我们有时需要查看男性 有时需要查看女性 还适合在性别上建索引吗
    查看女性的时候优化器会选择用这个索引找数据吗

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

    2019-11-23
  • TONGE
    老师,EXPLAIN 在SQL SERVER上不能用,有哪个是等价的吗在SQL SERVER上?
    2019-11-05
  • Coool
    为什么建立了current_time的索引,查询评论时间在 2018 年 10 月 1 日上午 10 点到 2018 年 10 月 2 日上午 10 点之间的评论还是运行时间好慢?已经将date函数作用在具体时间上,未对current_time进行函数处理。
    2019-10-24
  • Coool
    SELECT
    user_id,
    COUNT(*) AS num
    FROM
    product_comment
    GROUP BY
    user_id
    LIMIT 100;

    未创建索引查询时间:10.148s

    创建user_id索引后查询时间:0.076s
    2019-10-24
  • 雪飞鸿
    一张表有A,B,C,D四个字段,建立覆盖索引(A,B) include(C)
    执行查询:SELECT C FROM Table WHERE A='' AND B=''使用了索引
    查询:SELECT D FROM Table WHERE A='' AND B='' 看执行计划RID Lookup(Heap)消耗占用92%,Index Seek(NonClustered)消耗占8%
    所以,是否可以理解为使不使用索引不仅要看where条件,也要看要查询的字段?
    2019-10-17
  • Ansyear
    很多章节确实比mysql45讲的更清楚更容易理解,也更详细

    作者回复: 感谢!有些地方会给出数据集,让大家做练习,这样会更容易理解

    2019-09-28
  • ruvikvan
    最左原则(a,b,c)在使用(b,c)、(c)也会走索引 ,这是为什么呢老师
    2019-09-19
    3
  • 加载中……
    老师好,请教个问题,有没有经验数据,在索引区分度低于“某个值”(80%)的时候,就不适合在这个列上建立索引了?

    作者回复: 重复值高的字段一般不创建索引,重复率高于10%可以考虑不创建索引,具体情况也要具体分析,比如通常我们都不应该给性别字段创建索引,因为重复度高,但是如果数据集是女人国,男性比例只有0.01%,而且我们想要查询哪些为男性的情况,此时我们使用索引进行查询效率会更高。这种情况也适用于某种疾病的查询,是否患有某个罕见病的数值为0或者1,且疾病的发病率(数值=1的情况)往往很低,但是如果我们想要进行查询哪些人患病了这种疾病,将这个字段创建索引进行查询效率高,因为我们想要查找的这个人群区分度还是很高的。

    2019-08-30
收起评论
33
返回
顶部