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