java复习 数据库 索引

索引是啥？ index

索引是一种能帮助MYSQL提高查询效率的数据结构
索引是一种特殊的文件，包含这对数据库表里所有记录的引用指针
索引是一种数据结构。数据库的索引是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库表中的数据。
索引的通常实现使用B树或者B+树
通俗的说索引就是一个目录，表相当于书，数据相当于书里的内容，通过内容建立索引形成目录
索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的使用场景

1. 数据量大，并且常对这些列进行条件查询
2. 索引的使用会降低插入，删除的效率，所以适用于该数据库的插入操作以及对这些列的修改操作频率低的场景
3. 索引会占用额外的磁盘空间

索引的优势

大大的加快数据的查询速度

索引的缺点

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

索引的分类

InnoDB
1 主键索引 ： 设置主键的后数据库会自动建立索引 主键索引不能为空 建表时候自动建立 聚簇索引
2 单值索引 单列索引 普通索引 ： 即一个索引值包含单个列，给表里的某一个列加入索引
3 唯一索引： 索引列的值必须唯一，但允许有空值 但是只能又一个 建表时或者建表之后可以给这个列建立一个唯一索引但是创建这个索引的时候必须保证这个列要唯一，可以有null但是必须只能又一个null
4 复合索引： 一个索引包含多个列 根据这个表的多个列共同创mary造一个索引
MYISAM
全文索引：FULL TEXT

索引的基本操作

主键索引：
查看索引的SQL语句 ： show index from 表命

普通索引的创建 建表的时候创建 建表之后创建
建表之后创建 ： create index name_index（索引名字） on 表名字**(表中的那个列)**
建表的时候创建： create table t_user（id varchar(20) primary key ,name varchar(20), key(name））

唯一索引 建表的时候创建 建表之后创建
建表之后创建 ： create unique index nameindex on t_user (name);
建表的时候创建：create table t_user（d varchar(20) primary key ,name varchar(20),unique(name)*）

复合索引
建表的时候创建 ： create table t_user（d varchar(20) primary key ,name varchar(20)，age int,key(name，age)*）
建表之后创建 ** create index** nameindex on t_user (name，age);

复合索引面试题
*
1 最左前缀原则
2 mysql在查询为了更好的利用索引，会在查询的过程中动态的调整查询字段以便利用索引*

索引的底层原理

插入的数据是无序的得到的结果竟然排序了
基于B+树存储的

B+树 是一种数据结构 是在B 树的基础上的一种优化 使其更适合实现外存储索引结构，innoDB存储引擎就是使用的B+Tree实现其索引结构。

B 树中每个节点都要存储数据的key值，还有data值，每一页的存储空间是有限的，如果data数据过大时候就会导致每个节点（即一个页）能存储的key 的数量很小，当存储数量大的时候会导致B树的深度较大，增加查询时候磁盘的I/O次数从而影响查询效率。

在B+树中所有的数据记录节点都是按照键值的大小顺序存放在同一个层的叶子节点上，而非叶子节点只存放KeY值的信息，这样就大大的加大了每个节点存储的key值数量，降低了B+树的高度。
最顶层的是常驻内存，最多 1 ~3次查询
非主键索引 要先找到主键索引在跟着主键索引去查

B+树和B树的区别

B+树只有叶子节点才存数据 所有的叶子节点都有一个链指针 非叶子节点不存数据 只存储索引和指针 用来辅助进行查找
B树非叶子结点也要存储数据

InnoBD存储引擎页的大小为16kb

索引的底层实现？

mysql索引底层其实是一个b+树 首先在放入数据的时候会基于数据进行一个排序 排序之后会把数据以一个指针的形式吧这些数据连接起来 同时mysql在底层为了进一步优化它，是基于B+树的数据结构对数据进行存储就是对我们的数据进行一页一页的存储 默认页的大小为16kb
站在整个B+树的数据结构上来讲的话，一个三层的B+树的存储结构可以存储8亿到10亿左右，所以一般在用的话我们的项目两层就够了
如果站在两层B+树来讲的话它所查询时候如果是基于主键去查最多进行一次磁盘I/O 因为他的顶层是常驻内存的。

聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块儿，索引的叶子节点保存了行数据，也就是说找到索引也就是找到了数据
主键索引就是聚簇索引；
如果数据存储和索引放到了一起，叶子节点存放的是整行数据 这就是聚簇索引；
非聚簇索引： 将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
**注意：**在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，想复合索引，前缀索引，唯一索引。辅助索引的叶子节点存储不在是行的物理位置，而是主键值，辅助索引访问数总是需要二次查找。

为什么辅助索引不存放地址，而放主键 是因为在增删改的时候树上的地址会发生变化

InnoDB使用的是聚簇索引，

将主键组织到一棵B+树上，而行数据就存在叶子节点上，

• 假如使用where id=14（主键列）这样的条件来查询主键；则按照B+树的检索算法，即找到对应的叶子节点，之后获得行数据
• 若对非主键列name列进行条件搜索，则需要两个步骤： 1 在辅助索引B+树上检索name，到达其叶子节点所对应的主键。 2 使用主键在主索引B+树再执行一次检索操作，最终到达叶子节点就可以获取整行数据了。（重点在于通过键需要建立辅助索引）
• 聚簇索引默认是主键，如果表中没有定义主键，innoDB就会选择一个非空的索引代替，如果没有这样的索引，InnoDB就会隐式的定义一个主键来做聚簇索引。
• 如果你不想要主键为聚簇索引，那么你就先删除主键，然后再添加想要的聚簇索引，再把主键恢复

MYISAM使用的是非聚簇索引

使用聚簇索引的优势

每次使用辅助索引都要经过两次B+树查找，看上去聚簇索引的效率要低于非聚簇索引，这不是多此一举吗？优势在哪儿

1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时候，已经把页加载到了缓存器，再次访问的时候，会在内存中访问不会再磁盘中访问。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将数据返回了，如果按照主键ID来组织数据，获取数据更快。
2. 辅助索引的叶子节点，存储主键值，而不是数据的存放地址。这个好处就是当行数据发生变化时候，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次的I/O读写的缓存中没有，需要发生一次新的I/O操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。
3. 辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

聚簇索引需要注意什么

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

为啥主键通常建议使用自增id

聚簇索引的数据的物理存放与索引顺序是一致的，即只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的。如果主键是不是自增ID的话就要不断的调整数据的物理地址，分页，所以如果是自增的话就只需要一页一页的写，索引结构相对紧促，磁盘碎片化小。

什么情况下无法使用索引

1 查询的时候使用like关键字
如果匹配字符串的第一个字符为“%”，索引就不会被使用，如果“%”不在第一个位置索引就会被使用
2 查询语句中使用多列索引
只有查询语句使用了字段中的第一个字段，索引才会被使用
3 查询关键字中使用OR关键字
如果OR前后的两个条件列都是索引，那么查询将可以使用索引。如果or中又一个不是索引，那么查询就不能使用索引。