MySQL索引(理论部分)

150讲轻松搞定Python网络爬虫

1.索引前言

1.1 什么是索引？

索引在MySQL中也叫作“键”或者“key”(primary key,unique key,index key)，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少io次数，加速查询(其中的primary key和unique key,除了有加速查询的效果之外，还要约束的效果)。
索引优化应该是对查询性能优化最有效的手段了，索引能够轻易将查询性能提高好几个数量级。索引就好像字典中的音序表，如果要查询某个字，如果不适用音序表，则需要从几百页中逐页去查。
**强调：**一旦为表创建了索引，以后的查询最好先查索引，在根据索引定位的结果去找数据

1.2 索引的原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。拿书的目录来类比：
书的目录占不占页数？这个页数是不是也要存到硬盘里面，也占用硬盘空间。你再想，你在没有数据的情况下先建索引或者说目录快，还是已经存在好多的数据了，然后再去建索引哪个快？肯定是没有数据的时候快，(写书的时候肯定是按照目录或者说大纲来写的，如果写一会书在创建目录就会很慢)因为如果已经有了很多数据了，你在去根据这些数据建立索引，是不是要将数据全部遍历一遍，然后根据数据建立索引。
你在想，索引建立好之后再添加数据快，还是没有索引的时候添加数据快？索引是用来干什么的？是用来加速查询的，那么对于写入数据肯定会造成负面影响也就是降低效率了，因为你但凡加入一些新的数据，都需要把索引或者说书的目录重新做加工，所以索引虽然会加快查询，但是会降低写入的效率。

索引的影响

1. 在表中有大量数据的前提下，创建索引速度会很慢
2. 在索引创建完毕后，对表的查询性能会大幅度提升，但是写性能会降低

本质：都是通过不断的缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们就可以总是用同一种查找方式来锁定数据。

1.3 MySQL为什么要有索引呢？

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在实际生产环境中，我们遇到最多的也是最容易出问题的，并且是一些复杂的查询操作，因此对查询语句的优化就是重中之重了，而索引就是以此为目的的。

1.4 索引可以多加么？

索引是应用程序设计和开发的一个重要方面。但是如果索引太多，应用程序的性能就会收到影响，而索引太少，又会对查询性能产生影响，因此找到两者之前的平衡点对于应用程序的性能至关重要。

2. 索引的数据结构

索引是如何做到减少IO，加速查询的呢？
任何⼀种数据结构都不是凭空产⽣的，⼀定会有它的背
景和使⽤场景，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在⼀个很⼩的数量级，最好是常数数量级。那么我们就想到如果⼀个⾼度可控的多路搜索树是否能满⾜需求呢？就这样，b+树应运⽽⽣（B+树是通过⼆叉查找树，再由平衡⼆叉树，B树演化⽽来(因知识过于复杂没有完全掌握，不做详细展开)

　如上图，是⼀颗b+树，最上层是树根，中间的是树枝，最下⾯是叶⼦节点这⾥只说⼀些重点，浅蓝⾊的块我们称之为⼀个磁盘块或者叫做⼀个block块，这是操作系统⼀次IO往内存中读的内容，⼀个块对应四个扇区，可以看到每个磁盘块包含⼏个数据项（深蓝⾊所示，⼀个磁盘块⾥⾯包含多少数据，⼀个深蓝⾊的块表示⼀个数据，其实不是数据，后⾯有解释）和指针（⻩⾊所示，看最上⾯⼀个，p1表示⽐上⾯深蓝⾊的那个17⼩的数据的位置在哪，看它指针指向的左边那个块，⾥⾯的数据都⽐17⼩，p2指向的是⽐17⼤⽐35⼩的磁盘块），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示⼩于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示⼤于35的磁盘块。真实的数据存在于叶⼦节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。⾮叶⼦节点只不存储真实的数据，只存储指引搜索⽅向的数据项，如17、35并不真实存在于数据表中。

2.1 b+树的查找过程

如果要查找数据项29，那么⾸先会把磁盘块1由磁盘加载到内存，此时发⽣⼀次IO，在内存中⽤⼆分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为⾮常短（相⽐磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发⽣第⼆次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发⽣第三次IO，同时内存中做⼆分查找找到29，结束查询，总计三次IO。真实的情况是3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提⾼将是巨⼤的，如果没有索引，每个数据项都要发⽣⼀次IO，那么总共需要百万次的IO，显然成本⾮常⾮常⾼。除了叶⼦节点，其他的树根啊树枝啊保存的就是数据的索引，他们是为你建⽴这种数据之间的关系⽽存在的。

2.2 b+树的性质

1. 索引字段尽量要小(这就是为什么id一般都设为主键 ，聚集索引的原因)：
   通过上⾯的分析，我们知道IO次数取决于b+数的⾼度h或者说层级，这个⾼度或者层级就是你每次查询数据的IO次数，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N⼀定的情况下，m越⼤，h越⼩；⽽m = 磁盘块的⼤⼩ / 数据项的⼤⼩，磁盘块的⼤⼩也就是⼀个数据⻚的⼤⼩，是固定的，如果数据项占的空间越⼩，数据项的数量越多，树的⾼度越低。这就是为什么每个数据项，即索引字段要尽量的⼩，⽐如int占4字节，要⽐bigint8字节少⼀半。这也是为什么b+树要求把真实的数据放到叶⼦节点⽽不是内层节点，⼀旦放到内层节点，磁盘块的数据项会⼤幅度下降，导致树增⾼。当数据项等于1时将会退化成线性表。

如果你每个子叶节点只存两个数据的情况下，你想多加两个数据怎么办呢？

　所以我们需要将树建的越低越好，因为每个磁盘块的⼤⼩是⼀定的，那么意味着我们单个数据库⾥⾯的单个数据的⼤⼩越⼤越好还是越⼩越好，你想啊，你现在叶⼦节点的磁盘块，两个数据就沾满了，你数据要是更⼤的话，你这⼀个磁盘块就只能放⼀个数据了亲，这样随着你数据量的增⼤，你的树就越⾼啊，我们应该想办法让树的层数低下来，效率才⾼啊，所以我们应该让每个数据的⼤⼩尽可能的⼩，那就意味着，你每个磁盘块存的数据就越多，你树的层级就越少啊，树就越低啊，对不对。并且数据的数量越⼤，你需要的磁盘块越多，磁盘块越多，你需要的树的层级就越⾼，所以我们应该尽可能的⽤更少的磁盘块来装更多的数据项，这样树的⾼度才能降下来，怎么才能装更多的数据项啊，当然是你的数据项越⼩，你的磁盘块盛放的数据量就越多了，所以如果⼀张表中有很多的字段，我们应该⽤什么字段来建⽴索引啊，如果你有id字段、name字段、描述信息字段等等的，你应该⽤哪个来建⽴索引啊，当然是id字段了，你想想对不对，因为id是个数字，占⽤空间最少啊。

2. 索引的最左匹配特性：
   当b+树的数据项是复合的数据结构，⽐如(name,age,sex)的时候，b+数是按照从左到右的顺序来建⽴搜索树的，⽐如当(张三,20,F)这样的数据来检索的时候，b+树会优先⽐较name来确定下⼀步的所搜⽅向，如果name相同再依次⽐较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下⼀步该查哪个节点，因为建⽴搜索树的时候name就是第⼀个⽐较因⼦，必须要先根据name来搜索才能知道下⼀步去哪⾥查询。⽐如当(张三,F)这样的数据来检索时，b+树可以⽤name来指定搜索⽅向，但下⼀个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是⾮常重要的性质，即索引的最左匹配特性。

3. 聚集索引与非聚集索引(辅助索引)

数据库中的b+树索引可以分为聚集索引(clustered index) 和辅助索引(secondary index)，聚集索引与非聚集索引相同点是：不管是聚集索引还是非聚集索引，其内部都是b+树形式，即高度是平衡的，叶子节点存放着所有的数据。

聚集索引与辅助索引不同的是：叶⼦结点存放的是否是⼀整⾏的信息

1.聚集索引

#InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。⽽聚集索引（clustered index）就
是按照每张表的主键构造⼀棵B+树，同时叶⼦结点存放的即为整张表的⾏记录数据，也将聚集索引的叶⼦结
点称为数据⻚。聚集索引的这个特性决定了索引组织表中数据也是索引的⼀部分。同B+树数据结构⼀样，每
个数据⻚都通过⼀个双向链表来进⾏链接。

#如果未定义主键，MySQL取第⼀个唯⼀索引（unique）⽽且只含⾮空列（NOT NULL）作为主键，
InnoDB使⽤它作为聚簇索引。

#如果没有这样的列，InnoDB就⾃⼰产⽣⼀个这样的ID值，它有六个字节，⽽且是隐藏的，使其作为聚集
索引。
#由于实际的数据⻚只能按照⼀棵B+树进⾏排序，因此每张表只能拥有⼀个聚集索引。在多少情况下，查询
优化器倾向于采⽤聚集索引。因为聚集索引能够在B+树索引的叶⼦节点上直接找到数据。此外由于定义了数
据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

聚集索引的好处之⼀：它对主键的排序查找和范围查找速度⾮常快，叶⼦节点的数据就是⽤户所要查询
的数据。如⽤户需要查找⼀张表，查询最后的10位⽤户信息，由于B+树索引是双向链表，所以⽤户可以
快速找到最后⼀个数据⻚，并取出10条记录


聚集索引的好处之⼆：范围查询（range query），即如果要查找主键某⼀范围内的数据，通过叶⼦节
点的上层中间节点就可以得到⻚的范围，之后直接读取数据⻚即可
示例


2.非聚集索引(辅助索引)
详解：
就是我们在查询的时候，where后⾯需要写id之外的其他字段名称来进⾏查询，⽐如说是where
name=xx，没法⽤到主键索引的效率，怎么办，就需要我们添加辅助索引了，给name添加⼀个辅助索
引。
表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为⾮聚集索引）（unique key
啊、index key啊），与聚集索引的区别是：辅助索引的叶⼦节点不包含⾏记录的全部数据。
叶⼦节点存放的是对应的那条数据的主键字段的值，除了包含键值以外，每个叶⼦节点中的索引⾏中还
包含⼀个书签（bookmark），其实这个书签你可以理解为是⼀个{‘name字段’，name的值，主键id值}
的这么⼀个数据。该书签⽤来告诉InnoDB存储引擎去哪⾥可以找到与索引相对应的⾏数据。如果我们
select 后⾯要的是name，我们直接就可以在辅助索引的子叶节点找到对应的name值，⽐如：select
name from tb1 where name=‘xx’；这个xx值你直接就在辅助索引的子叶节点就能找到，这种我们也可
以称为覆盖索引。 如果你select后⾯的字段不是name，例如：select age from tb1 where
name=‘xx’；也就是说，我通过辅助索引的子叶节点不能直接拿到age的值，需要通过辅助索引的子叶
节点中保存的主键id的值再去通过聚集索引来找到完整的⼀条记录，然后从这个记录⾥⾯拿出age的
值，这种操作有时候也成为回表操作，就是从头再回去查⼀遍，这种的查询效率也很⾼，但是⽐覆盖索
引低⼀些，再说⼀下昂，再辅助索引的子叶节点就能找到你想找的数据可称为覆盖索引。

注意(辅助索引要点)：

1. 覆盖索引：利用辅助索引直接查询辅助索引的值(
   直接就可以在辅助索引的子叶节点找到)，效率比回表操作高

2. 回表操作:利用辅助索引查询别的值(通过辅助索引的子叶节点中保存的主键id值再去通过聚集索引来找到完整的一条记录，然后从这条记录中拿出你要的值),效率低一些

再看看下⾯的解释：
由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的ᬀ助索引的书签就是相应⾏数据的聚集索
引键或者称为主键的值。如下图

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只
能有⼀个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶⼦级别
的指针获得只想主键索引的主键，然后再通过主键索引来找到⼀个完整的⾏记录，这种查找的效率也是⾮常⾼。

举例来说
如果在⼀棵⾼度为3的ᬀ助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的⾼度同样为3，那么还需要对聚集索引树进⾏3次查找，最终找到⼀个完整的⾏数据所在的⻚，因此⼀共需要6次逻辑IO访问才能得到最终的⼀个数据⻚。

下期记录代码部分