step1 索引介绍
官方介绍索引是帮助MySQL高效获取数据的数据结构。通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
优势:
①检索:可以提高数据检索的效率,降低数据库的IO成本,类似于书的目录。
②排序:通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
-
被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些;
-
如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多;
-
where 索引列在存储引擎层被处理;
劣势: 索引会占据磁盘空间; 索引虽然会提高查询效率,但是会降低更新表的效率;比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件;
step2 索引的分类
1.单列索引
一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。
2.组合索引 *
一个索包含多个列。
3.全文索引(mysql支持差)
主要用来查找文本中的关键字,而不是直接与索引中的值相比较,fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配;fulltext索引配合match against操作使用,而不是一般的where语句加like;它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。
4.空间索引(未深入研究)
MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。
5.位图索引 (Oracle)
mysql目前不支持位图索引;
step3 索引的使用
1.创建索引
-
单列索引之普通索引
CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length));
-
单列索引之唯一索引
CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);
-
单列索引之全文索引
CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column);
-
组合索引
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;
2.删除索引
DROP INDEX index_name ON table;
3.查看索引
SHOW INDEX FROM table_name \G;
step4 索引原理分析
1.索引存储结构
-
索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引;
-
MyISAM和InnoDB存储引擎:只支持B+ TREE索引, 也就是说默认使用BTREE,不能够更换;
-
MEMORY/HEAP存储引擎:支持HASH和BTREE索引;
(数据结构示例:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html)
BTREE图示
B树是为了磁盘或其它存储设备而设计的一种多叉(相对于二叉,B树每个内结点有多个分支,即多叉)平衡查找树。( 多叉平衡)
-
B树的高度一般都是在2-4这个高度,树的高度直接影响IO读写的次数;
-
如果是三层树结构,支撑的数据可以达到20G,如果是四层树结构,支撑的数据可以达到几十T;
B和B+的区别:
B树和B+树的最大区别在于非叶子节点是否存储数据的问题。
BTREE是非叶子节点和叶子节点都会存储数据,查找数据需要遍历所有的节点;
B+TREE只有叶子节点才会存储数据,而且存储的数据都是在一行上,默认排列好顺序(自左向右、从大到小),数据都有指针指向,也就是有顺序。所以B+TREE检索速度快;
2.非聚集索引(MyISAM)
索引与数据不在同一个文件中,在索引文件中找到地址,根据地址在数据文件中找到数据,即为非聚集索引。
2.1主键索引
非聚集索引的主键下存储的是地址。
2.2辅助索引(次要索引)
非聚集索引的辅助索引下存储也是地址。
3.聚集索引(InnoDB)
索引与数据在同一个文件中,即在同一棵树上。
-主键:(InnoDB引擎中可以不建立主键,会出现警告)
-
创建主键
一定要创建主键,提高性能;
-
没创建主键(没有主键无法实现存储)
-1 mysql找唯一字段作为主键;
-2 没有唯一字段,mysql自动生成一个伪列作为主键;
注:在设计是不要建立联合主键,性能非常低;从原理上可以分析出联合主键的排序非常复杂,所有消耗大、效率低。
3.1主键索引
数据全部显示存储在叶子节点上;
3.2辅助索引(次要索引)
辅助索引中存储的是主键值;
4.回表
select * from t where name='Alice' ,此语句查询的执行过程是:
① 没建立索引
-
遍历整张表来查找结果,即全表扫描;
② 给name做了索引,就形成了Secondary Key索引树
-
A开头在左边查找,会很快查找到'Alice'下的主键18,然后到主键索引树下查找主键为'18'下的所有内容;(select * 查找所有)
查找两个索引树的过程就是回表;
select id,name from t where name='Alice' 此语句不会产生回表,在一个树上完成查询,提升查询效率。
5.索引覆盖
在一棵树上完成查询工作,在回表中举例的查询语句只能实现id,name查询不回表;利用组合索引,完成索引覆盖;
将name和score做为组合索引:
select * from t where name='Alice' 在此树上可以查询到所有数据,就不回表了,即索引能够覆盖到所有的数据;