1、mysql数据量大时，添加索引，后查询速度变快（至上而下：B+树）
数据库是存储到磁盘上的，需要按照一定的数据结构去存储。
数据结构：
数组：数组的大小确认，经常需要扩容，插入数据太过频繁
链表：查询效率慢，因为需要遍历全表
hash：hash的致命缺点是无序，所以就不能操作order by
树（二叉树）：查找数据时，读取根节点，小则从左找，大则从右找，每次读取一个数据，IO次数太多，树的层次太高，所以不合适
B-树（也称B树，又叫平衡多路查找树）：满足预读性原理，一个节点存储多个数据，根据节点能找到下一个磁盘块，还能立马找到下一个磁盘块，3层就能找到百万级的数据，索引和数据都在节点上，数据占用空间，一页中把数据加上，存储不了多少东西，层高，IO就多
B+树：满足预读性原理，数据都在叶子节点上，节点只放id和指针，存储id就存储很多（id自带索引，索引就是B+树的结构），层就低了，IO就少了。数据都在叶子节点，权标遍历时比较方便。
B树的查找分两步，首先查找节点，由于B树通常是在磁盘上存储的所以这步需要进行磁盘IO操作。第二步是查找关键字，当找到某个节点后将该节点读入内存中然后通过顺序或者折半查找来查找关键字。若没有找到关键字，则需要判断大小来找到合适的分支继续查找。
mysql是基于磁盘的数据库系统，索引是以索引文件的形式存储到磁盘上的，索引查找过程中要产生磁盘I/O消耗，相当于内存存取，
但是需求是需要减少查询过程中磁盘IO的存取次数，
B+树没有数据就能存储更多的id，每个节点的范围就更大了，B+树节点增加了链指针，加强了区间访问，可使用范围区间查找等，B树的每个节点都是key和data在一起，则无法区间查找
2、磁盘I/O
磁头（head）
磁道（track）
柱面（cylinder）
扇区（sector）
圆盘（platter）
图2中磁盘是一个 3个圆盘6个磁头，7个柱面（每个盘片7个磁道） 的磁盘，图2中每条磁道有12个扇区，所以此磁盘的容量为6712*512字节。即：存储容量 ＝ 磁头数 × 磁道(柱面)数 × 每道扇区数 × 每扇区字节数 （每个磁片有上下两面，每个磁面对应一个磁头）
3、索引
3.1、 索引原理
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
***面试题：你们公司的数据库怎么优化，有没有做过优化？
id自带索引，什么是索引？ B+树形的结构就叫索引，字符串可以作为id,但是不好，比如:uuid，自增id维护索引的成本低
3.2、磁盘IO、预读原理
扇区为磁盘的最小存储单元(512字节)，当需要从磁盘读取数据时，系统会将逻辑地址发给磁盘，磁盘的控制电路将逻辑地址转换为物理地址（哪个磁道，哪个扇区）这时候就会有磁头的机械运动，磁头找到相应磁道–寻道，时间成为寻道时间，磁头找到对应磁道的对应扇区，成为旋转时间。 这是很大的时间消耗，所以系统一般采取预读的方式，减少磁盘I/O。
磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。
内存比磁盘的读写速度要快很多，但内存容量要远小于磁盘，数据，程序的执行要调入内存后才能执行，所以内存和磁盘要经常进行I/O操作，I/O操作是个费事的过程。
系统是分页读取和存储的 一般一页为4KB，每次读取和存取的最小单元为一页，预读即在读取的起始地址连续读取多个页面（现在不需要的页面也读取了，这样以后用时就不用再读取，当一个页面用到时，大多数情况下，它周围的页面也会被用到。
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
3.3、索引的数据结构
任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。
如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块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并不真实存在于数据表中。
3.4、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，显然成本非常非常高。
3.5、b+树性质
1.索引字段要尽量的小：
2.索引的最左匹配特性（即从左往右匹配）
3.6、Mysql索引管理
（1）、功能
#1. 索引的功能就是加速查找
#2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
（2）、MySQL的索引分类
（3）、索引分类
1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用
各个索引的应用场景
（4）、 索引的两大类型hash与btree（为其指定索引类型）
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
3.7、什么是索引？为什么要建立索引？
索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
查找数据不需要一条一条的从头到尾去找，只需要按照B+树的结构自上而下去找，就是按照索引的方式去查找数据
***一个表必须要有id，去建立B+树的结构，如果没有id，会按照唯一约束字段或者行号去建立B+树的结构（索引是需要维护的）
***用uuid做的不好：每页磁盘数据装满时，插入数据时，随机插入，重新组合排序，维护成本大
4、索引的结构
4.1、有了索引怎么查询？
（1）、从索引里自上而下查询
（2）、走到叶子节点查询到id
（3）、根据id去聚簇索引中查询真正的数据，叫做回表
（4）、如果你要的数据索引都有了，不需要回表，叫做索引覆盖
4.2、聚簇索引和非聚簇索引
（1）、聚簇索引：就是按照每张表的主键构造一颗B+树（有序），同时叶子节点中存放的就是整张表的行记录数据，每张表只能拥有一个聚簇索引
（2）、非聚簇索引：在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。比如以数据中其他列（如：name）建立索引，又造了一个B+树结构，索引放的都是name，叶子节点的数据放的id，通过id回到聚簇索引中去查找真正的数据
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
