MySQL之索引

1.索引概述
索引是帮助MySQL进行高效查询的数据结构。

2.索引的优缺点
优势：1）类似于书籍的目录索引，提高数据检索的效率，降低数据库的成本
2）通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
缺点1）实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所有索引列也是要占用空间的。
2）虽然提高了查询效率，却降低了更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。

3.索引结构
索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的索引引擎都支持所有的索引类型的。MySQL提供4种索引：
BTREE索引：最常见的索引类型，大部分索引都支持B数索引
HASH索引：只有Memory引擎支持，使用场景简单
R-tree索引（空间索引）：是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
Full-text（全文索引）：是MyISAM引擎的一个特殊索引类型，主要用于全文索引，（默认的存储引擎）InnoDB从MySQL5.6版本开始支持。
InnoDB默认支持的索引就是BTREE索引。

平常所说的索引一般是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，复合索引，前缀索引，唯一索引默认都是使用B+tree树索引，统称为索引。

（一）BTREE结构
又叫多路平衡搜索树，一般m叉的BTREE特性如下：

• 树中每个节点最多包含m个孩子
• 除根节点与叶子节点以外，每个节点至少有[ceil(m/2)]个孩子—m/2,然后向上取整
• 若根节点不是叶子节点，则至少有两个孩子
• 所有的叶子节点都在同一层
• 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1]<=n<=m-1
  

如：5叉BTree为例，key:2<=n<=4。当n>4时，中间节点分裂到父节点，两边节点分裂。
插入数据C N G A H E K Q M F W L T Z D P R X Y S数据为例
-１插入前４个节点

• ２插入H，ｎ＞４，中间元素G字母向上分裂到新的节点。
  
  

• ３插入E，K,Q不需要分裂
  

• ４插入M
  

• ５.插入FWLT，不需要分裂
  

• ６.插入Z，中间元素向上分裂到父节点中。
  

• ７.插入D，中间元素向上分裂，
  PRXY不需要分裂

• ８插入S
  结论：Btree树和二叉树相比，查询数据效率更高，因为对于相同的数据量来说。Btree层数更小，因此搜索速度更快。
  （二）B＋TREE结构

B＋TREE是BTREE的变种，区别为：
１）ｎ叉B＋TREE最多含有ｎ个ｋｅｙ，而BTREE最多含有ｎ－１个ｋｅｙ
２）B＋TREE的叶子节点保存所有的ｋｅｙ信息，以ｋｅｙ大小顺序排列
３）所有的非叶子节点都可以看作是ｋｅｙ的索引部分

由于Ｂ＋Ｔｒｅｅ只有叶子节点保存ｋｅｙ信息，查询任何ｋｅｙ都要从ｒｏｏｔ走到叶子，所有查询效率更稳定。
（三）ＭｙSQL中的B＋Tree
MySQL索引数据结构对经典的B＋Tree进行了优化。在B＋Tree原基础上，添加了一个指向相邻叶子节点的链表指针（范围搜索），就形成了带顺序指针的B＋Tree，提高区间访问的性能。

（四）索引分类
（五）索引语法
＞创建索引

create　［UNIQUE唯一索引｜FULLTEXT全文索引｜SPATILAL］　index　index＿name
［USING　index＿type］//指定类型，默认为B+树类型
ON　tal_name(index_col_name)
index_col_name字段:column_name[(length)][ASC|DESC]

示例：
普通索引：
create index index_name on student(id);
查看索引
show index from student;

删除索引
drop index index_name on student;

ALTER命令
1.主键索引
alter table tb_name add primary key(column_list);
添加一个主键，意味着索引值必须是唯一的，且不能为空。
2.唯一索引
alter table tb_name add unique index_name(column_list);
创建索引的值必须是唯一的，可以为NULL（NULL可能出现多次）
3.普通索引
alter table tb_name add index index_name(volumn_list);
索引值可以出现多次
4.全文索引
alter table tb_name add fulltext index_name(column_list);

(六)索引设计原则

1.对什么样的表建立索引？
查询频次高，数据量比较大

2.对什么字段建立索引？
针对查询条件的字段

3.尽量使用唯一索引，区分度越高，效率越高

4.索引不是多多益善，维护索引数据结构代价高

5.尽可能使用段字段索引，占用磁盘空间
6.复合索引要利用最左索引
create index index_name on table_name(id,name,status);
相当于
对id创建索引
对id,name创建索引
对id,name,status创建索引