关于mysql索引
聚集索引与非聚集索引
如果表的存储引擎配置的是MyISAM,那么在对应的磁盘路径下会生成.frm(存储的是表结构定义的一些数据)和.MYD(存储的表数据)和.MYI(存储的是索引的数据),这个时候表数据和索引是分开存储在两个文件中的,称为非聚集索引
如果表的存储引擎配置的是InnoDB,那么在对应的磁盘路径下会生成.frm(存储的是表结构定义的一些数据)和.ibd(存储的是索引和数据文件的合集),这个时候表数据和主键索引是存储在一起的,所以InnoDB的主键索引称为聚集索引
注意:MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM),系统奔溃后,MyISAM恢复起来更困难
优劣:
- InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;
- InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败;
-
InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。(MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说:InnoDB的B+树主键索引的叶子节点就是数据文件,辅助索引的叶子节点是主键的值;而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。)
- InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快(注意不能加有任何WHERE条件);
- Innodb不支持全文索引,而MyISAM支持全文索引,在涉及全文索引领域的查询效率上MyISAM速度更快高;PS:5.7以后的InnoDB支持全文索引了
- MyISAM表格可以被压缩后进行查询操作
- InnoDB支持表、行(默认)级锁,而MyISAM支持表级锁( InnoDB的行锁是实现在索引上的,而不是锁在物理行记录上。潜台词是,如果访问没有命中索引,也无法使用行锁,将要退化为表锁)
- InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而Myisam可以没有
- Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI( Innodb:frm是表定义文件,ibd是数据文件,Myisam:frm是表定义文件,myd是数据文件,myi是索引文件)
InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。
MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
为了更形象说明这两种索引的区别,我们假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
我们重点关注聚簇索引,看上去聚簇索引的效率明显要低于非聚簇索引,因为每次使用辅助索引检索都要经过两次B+树查找,这不是多此一举吗?聚簇索引的优势在哪?
1 由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。
2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定位,后面会涉及)会随着数据库里数据的修改而发生变化(前面的B+树节点分裂以及Page的分裂),使用聚簇索引就可以保证不管这个主键B+树的节点如何变化,辅助索引树都不受影响。
关于InnoDB的二级索引有个问题需要注意:因为二级索引的二级节点包含了主键值,所以在(A)上的索引就相当与(A,主键id)上的复合索引(排序规则是现根据A排序,A字段相等的在用id主键排序),如果有像where A=5 order by id 这样的查询,会使用到索引扫描来做排序,但是如果将索引扩展为(A,B),则实际变成了(A,B,id主键),那么上面的查询 order by 则无法使用该索引做排序,而只能用文件排序了。
磁盘IO与预读
考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。
数据定位过程(InnoDB)
以MySQL的B+树为例,简单说下几种常见场景下,数据的定位过程。
第一种场景:索引精确查找
注意:B+树的非叶子节点一般是常驻内存的
select * from user_info where id = 23 ;
确定定位条件, 找到根节点Page No, 根节点读到内存(如果内存中存在就不需要从磁盘读到内存), 逐层向下查找, 读取叶子节点Page,通过 二分查找找到记录或未命中。
第二种场景:索引范围查找
注意:叶子节点是根据键值从左到右顺序递增的,节点之间是有双向指针的,在加上磁盘io预读机制,所以读取速度是很快的
select * from user_info where id >= 18 and id < 22 ;
读取根节点至内存, 确定索引定位条件id=18, 找到满足条件第一个叶节点, 顺序扫描所有结果, 直到终止条件满足id >=22。
第三种场景:全表扫描
注意:由于name列没有添加索引,所以查询name会进行全表扫描(InnoDB引擎的表主键索引是和数据存储在一起的,数据在磁盘中的存储数据是根据主键值排序的)
select * from user_info where name = 'abc' ;
直接读取叶节点头结点, 顺序扫描, 返回符合条件记录, 到最终节点结束
第四中场景:二级索引查找
create table table_x(int id primary key, varchar(64) name , key sec_index(name) ) ; select * from table_x where name = 'd' ;
通过二级索引查出对应主键,拿主键回表查主键索引得到数据, 二级索引可筛选掉大量无效记录,提高效率
sql使用or语句会导致索引失效吗解析
网上经常能看到一些文章总结在 mysql 中不能命中索引的各种情况,其中有一种说法就是指使用了 or 的语句都不能命中索引。
这种说法其实是不够正确的,正确的结论应该是,从 mysql5.0 后,如果在 or 连接的字段上都有独立的索引的话,是可以命中索引的,这里就是用到了 index_merge 特性。
在 mysql5.0 版本以前一条 sql 只能选择使用一个索引,而且如果 sql 中使用了 or 关键字,那么已有的索引就会失效,会走全表扫描。因为无论走哪个索引,mysql 都不能一次性查找出符合条件的数据,所以只能放弃索引。
mysql 也是一直在不断升级更新,所以在 mysql5.0 版本后,增加了 index_merge 索引合并这个特性,也因此支持了一条 sql 使用多个索引。
index_merge 核心思想就是先分别使用单个索引查出满足要求的数据,然后再将这些数据合并到一起返回。