003 mysql索引

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' 在此树上可以查询到所有数据，就不回表了，即索引能够覆盖到所有的数据；