mysql索引原理

      mysql读取数据，遵从的是局部性原理，即mysql读取数据是以页（默认12kb）为单位读取的，可以一定程度上避免频繁的读取IO。如select * form t where id=2 ，需要先通过IO从磁盘以页为单位读取文件到内存中，对页中的数据进行分析，返回最终的数据。

     局部性原理：当需要读取1kb的数据时会将其附近的数据一块读取，以减少磁盘的IO。

     页中的数据不是链表排序的原因：当链表过大时数据的查找效率低下。

     mysql索引为B+的原因：在目录页中，根据对应的索引可以迅速定位到数据存在的页；随后通过磁盘io，读取对应的叶子节点，根据页中的目录可以迅速定位数据。

     当使用辅助索引查找数据时，如果需要的数据不能完全在辅助索引的结构中存在，此时需要再次读取主键索引模型来查找数据。

     索引默认为从小到大的存储规则，如果为char类型则使用对应的字符编码集进行排序。

    mysql5.8版本才支持索引的降序，5.7只支持索引的升序

    索引最左原则：t(a,b) 的索引，单独的a索引可用，a-b的索引可用，但是b索引不可用

    当列的数据越大时，页上的数据量越小，这也就是为什么需要将大字段单独建表的原因。

    普通索引模型下，一页的数据量一般比主键模型下的数据量大，但是信息却不完整

 

 

 

 

2.mysql创建表时为何需要创建主键索引

     mysql在创建表时，如果当前表没有主键索引时，假定存在唯一索引则使用该索引来存储数据，如果没有索引则在表中创建了一个隐藏列用于存储数据，存在主键索引时则使用主键索引来存储数据。这就是为什么创建表时一般要创建主键索引的原因。

3.mysql数据的存储结构

     mysql中的数据是以页为单位存储的。当需要读取文件时，以页为单位从磁盘读取文件到内存中。

4.主键索引的数据存储结构

     页目录上的数据为，对应页上的最小值-对应页的地址。叶子节点为数据的存储结构，对应的索引-其他数据。

 

5.普通索引与主键索引下的数据结构

       当为主键索引的数据结构时，id-其他的列数据，此时为聚集索引，即列的数据全部在索引上。

       当为普通索引的数据结构时，索引数据-主键索引数据，此时为非聚集索引。如在t(a,b)联合索引下产生的数据结构，该结构中只包含了a,b,id列的数据，其他列的数据不存在。

 

 

6.几种sql的分析

     a.select * from t where id=1 

使用主键索引查找并读取数据        type-const

     b.select * from t

使用主键索引模型下，从左到右的全表读取数据，all

t表中id为主键索引，存在联合索引(a,b)，c为普通列

select * from t                         ALL 全表扫描

select * from t where id=1     const 主键索引

select * from t where id>1     ALL 全表扫描

select b from t where a=1     ref 辅助索引中获取数据

select c from t where a=1     ref 辅助索引定位+主键索引中获取数据

select b from t where a>1     range 辅助索引中获取数据，范围扫描

select c from t where a>1    

ALL 对主键索引的全表扫描。原因：若从辅助索引定位从主键索引获取数据则需要多次查询

 

select b from t where a>1 order by a desc