MYSQL索引学习笔记

什么是索引
----官方定义：索引是一种帮助mysql提高查询效率的数据结构
—索引数据结构

----索引的优点：
1.大大加快数据查询速度

–索引的缺点：
1.维护索引需要耗费数据库资源
2.索引需要占用磁盘空间
3.当对表的数据进行增删改时，因为要维护索引，速度会受到影响

2.索引的分类
在InnoDB存储引擎环境下：

• 主键索引：设定为主键后，数据库会自动建立索引，innodb为聚簇索引，主键索引索引列值不能为空

• 单值索引 单列索引 普通索引:
  即一个索引只包含单个列，一个表可以有多个单列索引 id,name,index age…

• 唯一索引：
  索引列的值必须唯一，但是允许有空值 唯一索引索引列值可以存在null,但是只能存在一个null

• 复合索引：
  即一个索引包含多个列 id(name,age) index bir
  场景：有些时候，在大数据量的情况下我们的业务需要通过多个字段联合起来进行查询，这个时候通过单列索引来查找就不行了，这时候就要考虑使用复合索引来进行查询

MyISAM
-e.FuLL Text 全文检索 (mysql5.7版本之前，只能用于MYISAM引擎)
全文索引类型为Full Text,在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文检索可以在CHAR、VARCHAR、 TEXT类型列上面创建，mysql只有MYISAM存储引擎支持全文索引

3.索引的基本操作
1.主键索引 自动创建
—主键索引是在创建表的时候自动创建的
—建表 主键自动创建主键索引，如下图所示：

---- 查看索引：
show index from user; 查询的结果如下

2.单列索引(普通索引或单值索引)
----建表时创建
create table t_user(id varchar(20) primary key ,name varchar(20) ,key(name));
注意：随表一起建立的索引索引名同列名一致

----建表后创建
create index nameindex on t_user(name);

----删除索引
drop index 索引名 on 表名

3.唯一索引
----建表时创建
create table t_user(id varchar(20) primary key ,name varchar(20),unique(name));

—建表后创建
create unique index nameindex on t_user(name);

4.复合索引
----建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),age int ,key(name,age));

----建表后创建
create index nameageindex on t_user(name,age);

经典面试题:在表中以name,age,bir这三种排列顺序创建了复合索引，以下哪种组合查询可以使索引生效 name bir age (可以) 、 name age bir(可以) 、age bir (不行) 、bir age name(可以)、 age bir (不行)

原理:mysql官方定义，在使用复合索引的时候，会存在最左前缀匹配原则，那么在这种说法的情况下，按照name来查询，可以走索引，按照name ,age这种组合顺序来查询，可以走索引，按照name,age,bir这种组合顺序来查询可以走索引，其他复合查询顺序都不会走索引，但是mysql除此之外还提供了一个优化器，mysql存储引擎在查询时为了更好的利用索引，在查询过程中会动态的调整查询字段顺序以便利用索引，因此就造成了上述结果

索引底层原理

1. 思考:
   -----建表
   create table t_emp(id int primary key,name varchar(20),age int);

   
   -----查询
   select * from t_emp

查询结果如下：

思考：为什么上面的数据明明没有按照顺序插入，为什么查询时却是有顺序的呢？
原因：mysql底层为主键自动创建索引，一旦创建索引就会进行排序，也就是mysql底层真正存储是这样的，为什么要排序呢？，因为排序之后在查询就相对比较快了，比如查询id=3的我只需要按照顺序找到3就行了（如果没有排序那就是大海捞针，全靠运气）

为了进一步提高效率mysql索引又进行了优化
------就是基于页的形式管理索引
------如 查询id = 4的 ，直接先比较页，先去页目录中查找，再去数据目录中查找，如下图所示

上面这种索引的数据结构称之为B+树数据结构，那摩什么是B+树呢？

B+Tree 是在B-Tree(B树)基础上面的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
B-Tree中每个节点中不仅包含数据的key值，还有data值，而每一个页的存储空间是有限的，如果data数据较大时会将导致每个节点(即一个页)能存储的key的数据量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时磁盘的I/O次数，进而影响查询效率，在B+tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上面，而非叶子节点只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：
1.非叶子节点只存储键值信息
2.所有叶子节点之间都有一个链指针
3.数据记录都存放在叶子节点中

• InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)，或bigint(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree中的一个节点)中大概存储16kb/(8B+8B)=1K个键值（因为是估值，为了方便计算，这里的k取值为10^3）,也就是说一个深度为3的B+Tree索引可以维护100010001000=10亿条记录
• 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般是在2~4层，mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1到3次磁盘I/O操作，注意：顶层页常驻内存

聚簇索引和非聚簇索引
聚簇索引：将数据和索引放到了一块，索引结构的叶子节点保存了行数据
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
注意：在InnoDB中，在聚簇索引之上建立的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找

1.在InnoDB中

• InnoDB使用的是聚簇索引，将主键组织到一颗B+树中，而行数据就存储在叶子节点上，若使用“where id = 14” 这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获取行数据。
• 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name,达到其叶子节点获取对应的主键，第二步使用主键在主索引B+树中再执行一次B+树的检索操作，最终到达叶子节点即可获取整行数据（重点在于通过其他键需要建立辅助索引）
• 聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引替代，如果没有这样的索引，InnoDB会隐式定义一个主键(类似oracle中的RowId)来作为聚簇索引，如果已经设置了主键为聚簇索引又希望在单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

2.MYISAM

使用聚簇索引的优势
问题： 每次使用辅助索引检索都需要经过两次B+树查找，看上去聚簇索引的效率要明显低于非聚簇索引，这样一来不是多此一举吗？聚簇索引的又是在哪里？
1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中(缓冲器)，再次访问时，会在内存中完成访问，不必访问磁盘，这样主键和行数据是一起被载入到内存中的，找到叶子节点就可以立刻将行数据返回了，如果按照主键ID来组织数据，获得数据更快。

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

聚簇索引需要注意什么

• 当使用主键为聚簇索引时，主键最好不要使用uuid,因为uuid的值太过于离散，不适合排序且可能出现新增加记录的uuid,会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源
• 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小，而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量

为什么主键通常建议使用自增id
—聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那麽对应的数据一定也是相邻的存放在磁盘上的，如果主键不是自增id,那摩可以想象，它会干些什么，不断的调整数据的物理地址，分页，当然也有一些其他措施来减少这些操作，但是无法彻底避免，但，如果是自增的，那就是简单了，他只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高。

什么情况下无法利用索引

• 查询语句中使用like关键字
  在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%” ，索引不会被使用，如果“%”不是在第一个位置，索引就会被使用
• 查询语句中使用多列索引
  多列索引是在表的多个字段上面创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。
• 查询语句中使用or关键字
  查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那摩查询中将会使用索引，如果or前后有一个条件的列不是索引，那摩查询中将不使用索引