※食用指南：文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记，笔记整理比较粗糙，主要目的自存为主，记录完整的学习过程。（图片超级多，慎看！）
【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！
https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2
目录：
1、INDEXING FOR HIGH PERFORMANCE——高效的索引
8、ORDER OF COLUMNS IN COMPOSITE INDEXES——复合索引中列的顺序
9、WHEN INDEXES ARE IGNORED——当索引无效时
10、USING INDEXES FOR SORTING——使用索引排序
第十三章：高效的索引
1、INDEXING FOR HIGH PERFORMANCE——高效的索引
如何使用索引来获得高性能
在大型数据库和高流量网站中，索引非常重要，可以显著提高查询的性能
索引的工作原理、如何创建索引加快查询速度
每个开发人员和数据库管理员都必须学习和理解
2、INDEXES——索引
索引：本质上是数据库引擎用来快速查找数据的数据结构
🔺类比电话薄
使用电话薄可以快速找到某人电话号码，因为按照了联系人的姓氏和名字进行了排序，不需要查看电话薄中的每一个联系人
数据库管理系统使用索引查找数据就是类似的原理
假设想找位于CA的顾客
没有没有索引时，用语句扫面顾客表中的所有记录，如果只有几百或者几天条记录的小型表来说倒也还好，随着表得更大，查询成本急剧增加
通过在state列创建索引来加速查询（一个按state排序的顾客电话薄）
这个索引中只有客户所在的state和顾客表里记录的引用
MySQL可以使用这个索引快速找到相应的顾客，然后从表中读取这些记录
比扫面或读取顾客表中的每条记录快了很多
多是情况下，索引很小，足以放入内存
从内存中读取数据总比从磁盘中读取数据来得快，索引帮助我们快速找到数据，但是用他们不是免费的
使用索引的代价：
增加数据库的大小，索引必须永久存储在表旁边
每次添加、更新或删除记录时，MySQL必须更新对应的索引，这会影响我们正常操作的性能
因此应该为性能关键的查询保留索引
许多开发人员在设计表时就添加列索引，不应该基于表来创建索引，而是基于查询创建索引，因为使用索引的终极目的就是为了加快运行较慢的查询
基于表的设计添加索引，像是在解决一个根本不存在的问题，所以不要盲目在表上添加索引
因为这会增加数据库的大小，并降低正常运行的速度
索引内部通常被存储为二叉树（用表更易可视化和理解）
计算机科学专业学的基础的数据结构，不需要理解就能完成数据库索引
3、CREATING INDEXES——创建索引
记得先执行一下附件文件
①先查看要安插的列的信息
增加EXPLAIN，可以看到具体描述：选择类型、表、分区、类型
当type为ALL时，意味着MySQL会做一个全表扫描，也就是读取或扫描这张表中的每一条记录
rows可以看到扫描的记录条数，说明顾客表有1010条记录
因为目前没有在state列安插索引，为了查找CA的顾客，MySQL必须扫描表中的所有记录
②在state放一个索引来加速查询
用idx或ix作为名字的前缀，下划线后指定要放置索引的列名称，（）中键入想要放置索引的列名称
实践中，一定要给索引一个便于理解的名称（idx1、idx2不好理解）
type不再显示全部，即不会再做全面扫描
rows中得到112条记录
possible_keys：为了执行这个查询可能会考虑的几个索引，可能存在多个索引，MySQL会挑选执行性能最佳的索引，这就是索引的可能的键或索引
key实际使用的索引或键
练习：
确认下数据
创建索引
4、VIEWING INDEXES——查看索引
①查看索引
PRIMARY主键：聚集索引（Clustered Index）
每当我们在表中添加主键，MySQL自动创建一个索引，可以根据它们的id快速查找记录
Collation：数据在索引中的排序方式（A为升序，D为降序）
Cardinality：基数表示索引中唯一值的估计量
②想要获得更精确的值
ANALYZE TABLE：为这张表重新生成有关于表的统计信息
更加精确的数值
🔺目前表中有3个索引
主键（聚集索引）：每个表最多1个聚集索引
两个二级索引（Secondary Idexes）：严格意义上来说，每当创建二级索引时，MySQL自动将id或主键列纳入到二级索引中
比如idx_points有二级索引，但这个索引里面有两个值，每个顾客的积分和id（虽然看不到id，但其实被存储在了索引里）
Index_type：BTREE（Binary Tree）所有索引都是二叉树
查看Orders表的索引
一个主键，3个放在外键列上的二级索引
每当我们为两张表创建一组关系的时候，MySQL会自动为外键创建索引，可以快速链接表
可以在导航面板中找到它们
点击索引可以在下面看到索引的各种属性
Type：二叉树
Unique：因为一个state可以有很多名顾客
Visible：已启用
Columns：被置于state列上
5、PREFIX INDEXES——前缀索引
如果想要在上面创建索引的列是一个字符串列（CHAR、VARCHAR、TEXT、BLOB）
索引可能会占用大量的空间，且无法达到很好的性能，索引越小越好，搜索更快
不想在把索引中包含整个列，只想包含列的前几个字符或列前缀，这样索引更小
在顾客表的姓名列上创建索引
姓名列是一个字符串列，在括号中可以指定索引中要包含的字符数
CHAR、VARCHAR括号中的内容是可选的，但TEXT、BLOB列就必须要写
假设只想在索引中包含前20个字符
❗为什么用前20个字符
必须观察数据来找到最佳的字符数，想要包含足够的字符，足以唯一地识别每位顾客
如果只包含一个字符，就会有很多顾客以ABC开头，MySQL无法通过姓氏快速找到某位顾客
目前的总记录，尝试不同的前缀长度，测试能得到多少唯一值
先查看首字母的有多少个
字符5、10差不太多，只多了30多个唯一值
因此5是最佳的字符，已经可以唯一识别表中大多数姓氏
6、FULL TEXT INDEXES——全文索引
全文索引：在应用程序中制作快速灵活的搜索引擎
打开脚本，创建该数据库（运行、刷新）
假设要建一个博客网站，将为用户开放搜索博文的功能
React是一个很通行的JavaScript库，可以用来搭建前端应用程序
Redux是一个常与React应用程序一起使用的库
假设有人登陆了博客，搜索了react redux
❗如何找到关于react redux的文章
%代表了任意数量的字符，可以在搜索短语前后添加任意数量的字符
问题一
列上没有索引，随着文章越多，搜索越慢
可以用上节学的前缀索引，但也只能包含标题或正文列的前几个字符
如果这个搜索短句react redux在列前缀的后面，索引不会包含搜索短句，那么MySQL就得做全表扫描
问题二
只会返回完全按照react redux两个单词顺序排序的关键词的文章，不会返回只有react 或redux，或react redux的文章（谷歌也不会值返回像这样精确组合或完全一致的搜多记录，会返回相关，顺序随意的）
综上，以上语句查询对搭建搜索引擎用处不大
此时可以用全文索引，可以在应用程序里打造快速强大的搜索引擎，包含整个字符串列，不只是存储前缀
会忽略任何停止词：in、on、the等等
本质上存储了一套单词列表，对每个单词又存储了一列这些单词会出现的行或记录
使用两个内置函数来支持全文索引
会返回所有标题或正文中包含一个或这两个关键字的文章，可以按任何顺序，也可以被一个或多个单词分隔开
全文索引的优点：相关性得分（一个介于0到1的浮点数，0表示没有相关性）
MySQL基于若干因素，包含了搜索短语的每一行计算相关性得分
可以看到第一篇文章的相关性得分是0.9，可以看出结果是按照相关性得分降序排序的
全文索引有两种模式
①自然语言模式：默认情况的模式
②布尔模式：可以包括或排除某些单词
寻找包含react但不包含redux的行
也可以要求包含某个单词
标题和正文里都必须含有from这个单词
返回结果中react和from的顺序也不一样
搜索确切短语：返回所有标题或正文里准确包含这个短语的文章
可以在应用程序中搭建搜索引擎，尤其是要在篇幅很长的字符串列中搜索（报纸文章、博客文章、产品描述），对于名字、地址这类短的字符串列使用前缀索引就好
7、COMPOSITE INDEXES——复合索引
一个主索引（聚焦索引），两个二级索引
假设想找state在CA且积分大于1000的顾客
①使用EXPLAIN观察MySQL如何执行这个查询
不管有多少索引，MySQL最多只会选一个索引
搜索行数112，通过这个索引可以把范围缩小到state在CA的顾客
随后还必须扫描所有CA顾客并查看他们的积分
因为state索引中并没有每位顾客的积分点
如果CA有1000万个顾客，上面的索引只做了一般的工作，快速定位CA顾客
AND后面需要从磁盘中读取数据，顾客表越大，速度越慢
②在state和points列创建复合索引
复合索引：允许对多列建立索引
现实中多数情况，应该用复合索引，因为一个查询可以有多个筛选器
🔺初学者常犯错误：为每列创建单独的索引，不能帮我们获得最佳性能，还占用很多空间，每次修改表中数据时，必须更新这些索引，索引越多，写入操作越慢
MySQL会自动将表的主键包含在每个二级索引中，这些单个列的索引会浪费很多空间
❗索引中应该包含多少列
MySQL中，一个索引最多可以包含16列，通常4-6列之间能达到很好的性能，但这不是惯例或最佳实践，应该根据查询和拥有的数据量进行实验
删除state和points列上的两个索引
8、ORDER OF COLUMNS IN COMPOSITE INDEXES——复合索引中列的顺序
①更常用的列放到前面
如果有5个查询，大多数或全部都是按state查找顾客，把state放在最前面就很合理，有助于缩小搜索范围
②基数更大的列放到前面
基数表示索引中唯一值的数量
例如性别的基数唯一值是2，如果表中国有一百万条记录，假设性别是均匀分布的
如果在复合索引中，把性别放在最前面可以把搜索范围从一百万表变成五十万顾客
如果把state放在最前面，表中有48个唯一的州，每个州大约有两万名顾客，可以把搜索范围缩小更少的记录
③考虑查询本身
假设想得到位置CA，姓氏以A打头的顾客
先看下这两列的基数
如果不管查询本身，last_name更适合排在前面，可以把表分成更小的部分
在姓氏列和州列上创建一个复合索引
如果把last_name放前面，MySQL必须扫描所有以A打头的姓氏，还要在里面找位于CA的顾客
以A打头的可能有好几十万人时，这样效率很低
如果顺序对调，很快获取CA的记录，再搜索以A打头的顾客
基于这个查询中用到的常数值的数字，如果是其他state的顾客
感叹那好强制MySQL使用别的索引
综上，按理应该用last_name排前面，但是state只找CA，约束性更强
所以把state排前面效率更高
❗如果要找几个state
变成了两个范围
显然第一个更好
但现实来讲，寻找指定state的顾客，比寻找任意state或者以A开头的顾客更贴近现实
目的是添加索引来优化性能关键的查询，而不是哪个随便的索引
❗如果把state筛选器移除，只想按照姓氏查看顾客
移除后的查询，这个索引就没有效率，因为MySQL会检查每个state，然后查看last_nam
如果没有任何筛选器，可以在last_nam列上放一个单列索引，更实用
总结：
更常用的列放到前面
基数更大的列放到前面
考虑查询本身
理解MySQL会如何使用不同的索引执行查询，无法创造出一个能够加速所有查询的复合索引，随着系数越大，需要好几个在不同顺序的列上的索引
9、WHEN INDEXES ARE IGNORED——当索引无效时
示例一
MySQL自动选了一个索引来满足查询，但行数有1010
像是做了全表扫描，但并没有，类型值还是索引，也就是全索引扫描
比表扫描快，因为不涉及从磁盘读取每个记录
优化方式必须重写查询，尽可能最好的方式利用索引
拆分查询：选择位于CA的顾客，再和另一个选择所有超过1000点积分的顾客的查询联合
这个索引并不是一个可以用于按积分搜索顾客的理想索引，因为积分是索引的第二列
如果用这个索引查询，MySQL必须查看每个州，在选择超过1000点的顾客
加快这个查询的第二部分，要在积分列上面创建单独的索引
1代表第一列SELECT子句，2代表第二列SELECT子句
两个加起来640条，比直接用OR运算符查询提升非常显著
示例二
做了一个全索引扫描，即使在积分列上有一个索引
为了满足这个查询，MySQL还是读取了索引中的每条记录
因为points+10，只要在表达式中用到了列，MySQL就无法以最优方式利用索引
把points+10列单独放一边，重写表达式
如果想让MySQL利用索引，始终得把列单独提出来
10、USING INDEXES FOR SORTING——使用索引排序
前面内容都是用索引过滤数据，也可以用索引对数据进行排序
目前的索引
写一个查询，按顾客所在的state对其进行排序
在列上添加索引时，MySQL会获取该列中的所有值，对其排序，并存储再索引中
所以这里的idx_state_points复合索引，已经对这些顾客按state排序过来
没有在possible_keys中看到idx_state_points这个键（原因不明）
MySQL按顺序读取了记录，并扫描了整个索引
如果用别的不在索引中的列进行排序
ALL说明做了一个全表扫描
filersort（外部排序）和文件没有什么关系，只是以表中为数据排序的一种算法的名称
只需知道，外部排序时很耗费的一种操作
通常来说，除非真的必要，不要给数据排序，可以看看是否能设计一个索引避免作排序操作
SHOW STATUS语句：查看服务器变量
last query cost（上一次查询的成本）
当我们使用索引排序时
可以看出外部排序操作非常昂贵，几乎是从索引获取数据贵10倍
因此，最好设计索引，用于筛选数据和排序数据
实际中可能没有那么简单，因为查询、WHERE子句、ORDER BY子句会存在一些难度，取决于列和顺序，MySQL可能会决定不采用索引为数据排序
基本规则：ORDER BY子句中的列的顺序，应该与索引中列的顺序相同
按state和points排序，做了一次索引扫描
中间加上一列部署索引中的，做了一次全表扫描
当以不同方向排序
这个查询一部分使用索引，一部分使用外部排序操作
因为两列的复合索引，先用state，在按points降序排序，所以MySQL无法利用这个索引完成查询，因为排序方式不一样
如果设置同一方向，backward index scan（反向索引间隙）
和按升序排序的成本完全相同
如果是基于两列的索引，A列、B列
可以按A排序、可以按A和B排序，可以按同样的列降序排序
不能混淆方向，也不能在中间添加一列
如果按一个索引的第二列排序，也是一个浪费资源的索引
因为目前顾客是按state排序的，在每个state下面再按points排序，所以不能用索引为顾客按points排序
可以来到一个特定的分区，或者说特定的state，将顾客按积分排序
11、COVERING INDEXES——覆盖索引
以上的内容都是SELECT customer_id
如果选择所有
我们放在state和points列上的复合索引包含了关于每个顾客的三条信息：id、state、points
AGAIN：每当创建一个二级索引，MySQL会自动将主键包含在第二索引中
所以如果选择三个中任何一个列，MySQL可以完整使用索引满足查询
覆盖索引：一个包含所有满足查询需要的数据的索引
使用这个索引，就可以在不读取表的情况下执行查询（已经是最快）
所以设计索引时，先看看WHERE子句、常用的列、包含在索引中的列，可以缩小范围
之后查看ORDER BY 子句中的列，看是否在索引中包含这些列
最后查看SELECT子句中使用的列
如果包含了这些列，就会得到一个覆盖索引
12、INDEXES MAINTENANCE——维护索引
处理索引时注意问题
重复索引：同一组列上且顺序一致的索引（ABC、ABC）
MySQL不会阻止创建重复的索引，将分别保留每个重复索引
有时无意间创建了重复索引，尤其在没有查看现有索引就创建了新索引时容易发生
因此，记得在创建新索引之前检查现有索引
多余索引：
如果在两列（A、B）上有一个索引，在列A上创建另一个索引，就会被判定为多余索引，因为原来的索引也可以优化包含列A的查询
如果在B列和A列上创建了索引，或者只有B列，就不算多余，因为这个索引可以满足不同的查询
当有人在不查看现有索引的情况下就创建新索引时，就会出现多余索引
重中之重：创建新索引之前，先查看现有索引，看看是否有能用得上的索引
索引维护非常重要，要确保删除重复索引、多余索引和未使用的索引
————TBC
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
