MySQL——索引

索引

提高效率：

​ 1.16kb大小的page，使用局部性原理，提高数据命中率，减少IO次数；

​ 2.Linux内核使用了IO子系统，对IO请求进行排序，使得可以支持连续访问，减少磁头的摆动；

​ 3.MySQL的InnoDB引擎使用了B+树索引结构，这颗树是“矮胖型”的，注定了每条支路途径节点数量少，需要加载到内存中的page页较少，减少了IO次数；

​ 4.使用了页目录这样的结构，从数据的结构方式上提高了索引效率；

​ 索引的核心是为了提高数据库性能的，但是查询速度的提高是以牺牲插入、删除、更新等速度为代价的，这些写操作增加了大量的IO，提高了海量数据的检索；

​ MySQL在启动时预先开辟好了大块的空间，表的CURD操作就体现在了内存级，然后会定期的将数据刷新到外设，如磁盘中，索引操作也是在内存当中进行的；

​ 提高索引效率的方式本质上就是改变数据的组织方式或者是修改算法本身；索引就是将数据的结构进行了重构来优化索引的效率；

一、索引的分类

​ 常见的索引分为了：主键索引、唯一索引、普通索引、全文索引；

二、索引的认识

​ 不创建索引的情况下，从数据量为800万表中的查找一个信息需要花费几秒钟的时间，速度太慢，为了解决这种情况需要创建索引；

2.1创建索引方法

​ 这个过程就是在mysql中创建数据结构；

​ 当创建好索引之后，在进行查询时速度就得到了提升；

alter table 相关表名 add index(属性);

三、MySQL与磁盘

​ 对数据进行持久化，进行IO操作一定是要对数据进行落盘的；

​ 磁盘中扇区大小固定靠的是扇区密度不同实现；

​ MySQL提供了数据存储服务，数据存储在磁盘中，但是磁盘作为一个机械设备，读写效率是比较慢的，而且外设IO效率本来就很低，所以提高效率很重要；

​ 在MySQL中创建的数据库文件或者是表结构一定是Linux中的文件；这些文件一定也是在磁盘当中进行保存，并且保存在每一个扇区当中，由于数据库文件较大，所以需要使用多个扇区进行保存；

3.1磁盘随机访问与连续访问

​ 随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。

​ 连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。

​ 将所有的IO请求进行排序，然后使得进行连续访问，这样就变相地提高了IO效率，并且提高了磁盘的使用寿命；

四、MySQL与系统

​ MySQL是一个应用层软件，当启动了之后就是一个进程，所以MySQL是在操作系统之上运行着的；

​ MySQL为了提高IO的效率，所以MySQL进行IO的基本单位是16kb(值得是InnoDB存储引擎的IO大小)又叫做page，这个IO指的是MySQL与操作系统之间的IO，而真正写入磁盘中时，是操作系统与磁盘进行IO，基本单位为4kb，将文件缓冲区中的数据进行落盘；

​ 使用fsync系统调用，将文件缓冲区中的数据刷新到磁盘中；

show global status like 'innoDB_page_size';大小为16384个字节

​ MySQL中的数据以page16kb为单位存储在磁盘中；CURD操作需要进行计算，找到对应操作位置；而计算就一定会经过CPU，将数据移动到内存中，当在内存中的数据处理完成之后，就会将数据进行落盘，以page4kb的大小进行IO；MySQL在启动时就开辟了一大块空间叫做Buffer Pool方便进行16kb的缓存，和磁盘进行IO交互，利用计算机的局部性原理，有效地减少IO次数，减少磁头的摆动；

五、索引测试

​ 创建一个具有主键但是不自增，且存储引擎为InnoDB的表，进行全列乱序插入时，最后查询显示出来的结果是有序的；即MySQL会根据主键对数据进行排序；有序可以设计出高效的查询算法；

​ Buffer Pool中会存在大量的16kb大小的page，所以就一定需要将大量的page进行管理；这就需要对page进行先描述再组织，所以会生成一个page结构体管理page的属性；

​ 由于page间或者是page内部都是使用的链式结构，（内部是单链表，page间是双链表）只能线性地遍历查找，所以查询起来的效率就很低；所以提高效率就需要从page之间或者是page内部进行处理；

对于单页情况：

​ 为了解决上述的查找效率低下的问题，就在page结构中引入了引入了页目录这样的kv子结构(每一个目录项都是由键值和指针构成)，牺牲少量的数据空间，提高了查询的效率，本质上是一种以空间换取时间的做法；本来是要遍历大量的数据，最后就变成了遍历少量的目录就可以快速定位到查找位置；

​ 而MySQL将数据设置成有序的，是为了方便引入页目录；

对于多页情况：

​ 由于一个page的大小是16kb，当数据量过大时就需要使用多个page来进行存储；但是也是存在线性遍历导致查询效率低下的问题，这时就需要引入新的page但是不存数据，只是存页目录来管理正常page的字段；如：每个page的起始数据编号和指针(子page的第一个页内目录项)；这样就可以管理1000多个子page；

​ 最后索引的方式就是，先查特殊page对应的页目录，找到子page，然后根据子page查找到对应的数据记录；

​ 如果效率还是低，那就继续向上添加页目录page；

六、B+树

​ 如上的结构就是，B+树结构；即MySQL中索引的方式统一使用的是B+树结构进行管理；

​ B+树由目录页和数据页构成，叶子节点是数据页，非叶节点是目录页；

注意：

​ 1.并不是所有的结构都使用的是B+树结构，也有哈希结构；

​ 2.常见的引擎都是使用的B+树；

​ 3.B+树只有叶子节点使用链表的形式进行了集连，其他节点是没有的；

​ 4.只有叶子节点保存了数据，这样非叶节点(目录页)就可以存放更多的页目录项，管理更多的数据页，所以这棵树的特点就是层数低，但是层内节点数多，使得途径的路上节点较少；即需要加载到内存中的节点数较少，减少了磁盘和内存的IO次数，提高效率；从IO角度考虑，IO次数减少，从算法的角度考虑是使用页目录可以快速查询；

​ 5.对表进行CURD操作时，就是对该结构进行操作，对于没有设置主键的结构，自动生成隐藏列，默认就是主键，按插入顺序决定，所以也是支持B+树索引的；

​ 总之InnoDB引擎进行创建表并且数据存储，一定是使用的B+树结构，每个表都会对应一个B+树；

​ B+树的叶子节点是使用的链表进行的集连；MySQL选择了B+树是因为MySQL期望的是查询一段范围而不用对同一个范围内的值进行重复的遍历，只是需要找到起始数据之后进行线性遍历即可；

七、不选择其他数据结构的原因

​ 对于链表，需要进行线性遍历，索引效率太慢；

​ 对于二叉搜索树，由于其特点是瘦高型，所以会进行大量的IO，IO效率不行，而还可能会退化到线性结构；

​ 对于AVL树或者是红黑树，由于也是瘦高型，注定了IO效率不会太高；

​ 对于Hash，在MySQL中是支持的，只不过InnoDB和MyISAM引擎不支持，因为尽管可以快速查找到某一个具体的数据，但是对于范围查找就不支持了，换句话说就是每次查找都需要进行遍历，而B+树则是可以找到一段范围，不需要进行重复的遍历；

​ InnoDB和MyISAM引擎使用的是B+树，内存级的MEMORY/HEAP可以使用Hash或者是B+树；

​ 对于B树，由于B树的每一个page都包含页目录和数据记录，还有叶子节点没有使用链表进行集连，导致了单个page里面保存的目录项变少了，这样就会导致B树相对与B+树会更高更瘦，也就是IO次数会更多，另一方面没有进行叶子节点的集连就需要进行重复的遍历树结构，效率就会下降；

八、聚簇索引与非聚簇索引

​ 前面的使用B+树并且叶子节点存数据的索引方式是InnoDB引擎的存储方式，这种存储方案叫做聚簇索引；

​ MyISAM引擎使用的也是B+树，但是叶子节点不直接存放数据，没有将索引和数据存放在一起，而是转为存放数据的地址，这种存储方案叫做非聚簇索引；

​ InnoDB创建表之后会形成两个文件，.frm存放的是表结构，.ibd另一个存放的是数据和索引；

​ 而MyISAM创建表之后会形成三个文件，.frm一个存放表结构，.MYD存放数据，.MYI存放索引；

​ MySQL除了创建主键索引之外，还可以给其他列创建索引，这种索引叫做辅助(普通)索引；

​ 对于MyISAM创建主键索引和创建普通索引是么有差别的，无非就是主键不可重复，而非主键可以重复；在MyISAM是可以创建多个B+树的，每一个树都可以叫做一个索引，索引的本质就是数据结构；

​ 而InnoDB使用的是聚簇索引，所以对于普通索引，叶子节点存放的是主键键值，没必要存放多份数据，浪费空间；先根据普通索引确定主键键值，然后根据主键索引，再找到数据记录，获得所有的数据信息，这种行为就叫做回表查询；

​ 创建多个索引会创建多个B+树；需要注意的是没有指明主键也会创建B+树，创建索引，因为MySQL会创建隐藏列，当没有指明主键是就会将隐藏列设置为主键，只不过主动创建的列没有设置成主键，所以只能进行线性遍历；

九、索引的操作

9.1创建主键索引

方式一：创建表时，直接指明主键就完成了主键索引的创建；

方式二：在创建表的最后，使用primary key(属性名)的方式实现主键的指明；

方式三：表已经创建完成但没有添加主键，此时需要修改表结构指明某一个属性为主键；

alter table 表名 add primary key(属性名);

9.2查看索引的方式

方式一：

show keys from 表名\G

方式二：

show index from 表名\G

        Table: test1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 

9.3创建唯一键索引

​ 在构建唯一键约束的时候其实也会创建唯一键索引，索引名称默认会以列名为名称；

方式一：直接在创建表时设置成唯一键；

方式二：在创建表的最后使用unique(属性名)设置成唯一键；

方式三：表已经创建完毕，此时使用alter table 表名 add unique(睡醒)，添加唯一键约束；

9.4删除索引

9.4.1删除主键索引

alter table 表名 drop primary key；

9.4.2删除其他键索引

alter table 表名 drop index 索引名;
drop index 索引名 on 表名；

9.5普通索引的创建

方式一：在创建表的最后，使用index(属性名)，将指定列设置成索引；

方式二：在创建表完成之后，使用alter table 表名 add index(属性名)，指定成普通索引；

方式三：创建索引，create index 索引名 on 表名(对应属性);这个方式主要是用来给索引起名字；

9.6复合索引

​ 在普通索引指明属性的时候，可以指明多个属性构建一个索引；

​ 复合索引就是普通索引；

​ 索引会从最左开始进行匹配，当使用复合索引中的某一列查找时，会根据索引最左匹配原则进行匹配，当要返回索引时，这种情况叫做索引覆盖；即所谓的索引覆盖覆盖的就是主键值；

​ 索引最左原则就是，索引的内容必须包含索引的最左侧属性；

9.7全文索引

​ 当需要查找一列内部的某些字段时，就需要使用全文索引；MySQL支持全文索引，但是要求存储引擎为MyISAM,而且默认支持的是英文版；

创建全文索引：

​ 在创建表的最后，添加fulltext(属性名1，属性名2)；

​ 可以使用explain工具查看一下是否SQL语句是否使用了索引；

使用全文索引：

select *from 表名 where match(对应属性1，对应属性2) against('database')；