MySQL----索引

数据库的索引是一种数据结构 

--主键索引，是自动创建
create table t_user(id varchar(20) primary key,name varchar(20));
 
 show index from t_user;
 
 --普通索引创建的两种方式：建表时创建，建表后创建
 create index name_index on t_user(name);
 
 show index from t_user;
 
 create table t_user1(id varchar(20) primary key,name varchar(20),key(name));
 
 show index from t_user1;
 
 --唯一索引创建的两种方式： 建表前创建，建表后创建
 
 create table t_user2(id varchar(20) primary key,name varchar(20),unique(name));
 
 create unique index name_index on t_user(name);
 
 show index from t_user2;
 
 --复合索引
 create table t_user3(id varchar(20) primary key,
 name varchar(20),age int(3),bir varchar(20),key(name,age,bir));
 
 show index from t_user3;

主键索引会自己排序

mysql采用InnoDb引擎在设计时是将根节点常驻内存

深度为3的B+Tree可以维护约10亿条数据

索引的底层是采用B+Tree分页来存储信息的

每一页16kb

数据只存在于叶子节点上

叶子结点由主键值，数据，指针point组成

其他节点上只存储主键值和指针

B+Tree和B-Tree的几点不同：

1.非叶子节点只存储键值信息

2.所有叶子结点之间都有一个链指针

3.数据记录都存放在叶子结点中

聚簇索引和非聚簇索引

聚簇索引：将数据和索引放到了一块，索引的叶子结点保存了行数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子结点指向数据对应的位置

在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引的叶子结点存储的不在是行的物理位置，而是主键值，辅助索引访问数据总是要二次查找。

在InnoDB中使用的是聚簇索引，将主键组织到一棵B+_树中，而行数据就存储在叶子节点上，若使用“where id=14”这样的条件查找主键，则按照B+树的检索方法，即可查找到对应的叶子结点，之后获得行数据。

若对Name列进行条件搜索，则需要两个步骤：第一步：在辅助索引B+数中检索Name，到达其叶子结点获取对应的主键，第二步：使用主键索引在B+树再执行一次B+树检索操作，最终到达叶子节点即可获得行数据。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个非空且唯一的索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键（类似于ORACLE中的Rowld）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，然后恢复设置主键就行。

使用聚簇索引的优势：

1.由于行数据和聚簇索引的叶子结点存储在一起，同一页中会有多条数据，访问同一数据页不同数据时，已经把页加载到Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点接可以立即将数据返回，如果按照主键id组织数据，获得数据更快。

2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址，好处是当数据发生变化时，索引树的节点也要分裂变化，或者是我们需要查找数据时，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免辅助索引的维护工作，只需要维护聚簇索引就好了，另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

什么情况下无法利用索引

1.查询语句中使用LIKE关键字

在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为"%"，索引不会被使用。如果"%"不是在第一个位置，索引就会被使用。

2.查询语句中使用多列索引

多列索引是在表的多个字段上创建一个索引，只查询条件中使用了这些字段中的第一个字段，索引才会被使用。

3.查询语句中使用了OR关键字

查询语句只有or关键字时，如果or前后的两个条件的列都是索引，那么查询中将使用索引，如果前后有一个条件列不是索引，那么查询中将不使用索引。