数据库——索引

索引

索引是存储引擎用于快速查找记录的一种数据结构，需要额外开辟空间和数据维护工作

        按照物理存储方式：聚簇和非聚簇

MyISAM：frm元数据文件、myd数据、myi索引数据，MyISAM都是非聚簇索引，数据和索引分别存放

InnoDB：frm元数据文件、ibd数据和索引，innodb存储引擎支持聚簇索引

        按照数据结构：B+树、hash等

        按照逻辑：主键索引、普通索引、唯一索引、空间索引（针对空间类型的数据）和全文索引

B-tree

B树是一种自平衡的多叉查找树，在一个B树种最多可以开m个叉（m>=2）称为m阶B树

·定义任意非叶子节点最多有m个儿子

·根节点可以有m-1个键值【节点中存储了具体的数据key-value】，根节点的儿子数为[2,m]

·除去根节点以外的非叶子节点儿子数为[m/2,m]，非叶子节点的关键字个数为儿子数-1

·所有的叶子节点位于同一层

·利用k个关键字将节点拆分为k+1段，分别指向k+1个儿子，同时需要满足查找树的大小关系

B-Tree的特点：

·关键字集合分布在整个树中

·任何一个关键字出现且只能出现在一个节点中

·搜索可能在非叶子节点就直接结束，其搜索性能等价于在关键字全集内进行一次二分查找O(logN)

·B树在插入删除数据时会破坏B树的性质，所以插入删除时需要对数据进行分裂、合并、转移等操作以保持B树性质

B+树

就是在B树的基础上添加了顺序访问指针

·有n个子树的节点包含有n个关键字

·所有的关键字全部存储在叶子节点上，且叶子节点根据关键字从小到大的顺序连接

·非叶子节点可以看作索引部分，节点中仅仅包含有其子树中最大或者最小的关键字

B+树的查找过程类似B树，只是查找时，如果非叶子节点上的关键字等于给定值时，并不终止，而是继续沿着指针指到叶子节点位置。因此不管在B+树中是否查找成功，每次查找都时走了一条从根到叶子的全路径

B+树的特点：

·所有关键字都存储在叶子节点上，且链表中的关键字恰好是有序的

·不可能是非叶子节点命中，所有查询的执行时间稳定

·非叶子节点相当于叶子节点的索引，叶子节点相当于存储数据的存储层

·非常适合文件索引系统

MySQL索引

索引实际上就是关键字和数据的映射关系，关键字是从数据中提取的用于标识、检索数据的特定内容

关键字相对于数据来说，关键字的数据量要小的多

关键字应该是有序的，二分查找可以快速确定位置

MySQL中的索引类型可以分为普通索引、唯一索引、主键索引、全文索引和空间索引（针对空间类型的数据）

普通索引：对关键字没有限制

唯一索引：用于实现关键字不允许重复的约束

主键索引：要求主键唯一且不允许为空

创建索引

创建索引有2种方式：

创建表的同时建立对应索引，例如主键、唯一性约束

创建表之后建立索引

删除索引

可以根据索引名称执行删除操作，对应的是普通索引、唯一索引、全文索引

语法：alter table 表名称 drop key 索引名

执行计划

当执行SQL语句时，首先会分析、优化，形成执行计划，最后按照执行计划执行操作，然会操作结果集。可以通过explain来分析sql执行前的执行计划。

创建索引

建立索引是以浪费空间为代价，换取高查询效率。建立索引会对修改操作的性能造成影响

1、建立基础索引：在使用where、order by、join字段上建立索引

2、优化组合索引：基于业务规则和业务逻辑

                如果条件经常性的出现在一起，可以考虑将多个字段的索引升级为复合索引

                如果添加个别字段的索引就可能出现索引覆盖，可以考虑为该字段建立索引

3、如果查询时不常出现的索引应该删除掉

4、建立索引后注意sql语句的使用

                %开头的模糊查询导致索引失效

                索引列最好非空，null值不会出现在索引中。 sex boolean default 1

                少用not in或者!=查询，not in可以使用not exists替代

                不要针对列进行计算，针对列建议的索引无效

5、使用explain查看执行计划，判断索引是否生效