mysql 索引

目录

1.什么是索引？ 

2.索引的优缺点

3.索引分类

4.索引原理：

5.B+树与B树的几点不同

6.聚簇索引和非聚簇索引

7.使用聚簇索引的优势

8.使用聚簇索引需要注意点

---

1.什么是索引？ 

官方定义：一种帮助mysql提高查询效率的数据结构

2.索引的优缺点

优点：大大提高了数据查询速度

索引缺点

        1.维护索引需要消耗数据库资源 //无论是性能还是磁盘上都会占用

        2.索引需要占用内存空间

        3.当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

3.索引分类

--a.主键索引

设定为主键后会自动建立索引,innodb为聚簇索引，主键索引值不能为空

创建主键索引 ：创建一个rev_cost_line表，设置 line_id 为主键索引

create table rev_cost_line(line_id varchar(30) primary key, header_id) ;

查看索引： show index from rev_cost_line;

--b.普通索引

即一个索引只包含单个列,一个表有多个单列索引

普通索引建立方式：1.建立表的时候创建 2.建表后时创建

建表前：创建一个rev_cost_event表，其主键索引的名称为event_id,普通索引为distribution，其中key内的名称为要创建列索引的名称

create table rev_cost_event(event_id varchar(20) primary key ,distribution varchar(20), key(distribution));

建表后：创建一个索引，索引的名称为index_header_id ，其作用的表为header_id

create index index_header_id on rev_cost_line(header_id);

查看索引： show index from rev_cost_line;

--c.唯一索引

索引列的值必须为唯一,但允许空值，空值只能有一个空值

# 建表前的索引
create table company(company_id int,company_name varchar(20),company_month varchar(20),unique(company_month ));

# 建表后索引
create unique index index_company_name on company(company_name);

--d.复合索引

一个索引包含多个列 1. 遵循最左前缀原则 2.mysql引擎为了更好利用索引，会在查询过程中动态查询字段顺序以便利用索引

# 实例创建一个 name、age联合索引 dir 为非索引
​
​
name bir age  # 可以
name bir  # 不可用该联合索引
age bir # 不可用该联合索引
bir age name # 可以

# 创建时添加联合索引
create table salers(salers_id int,salers_code varchar(10),key(salers_id,salers_code));
#创建后添加联合索引
create index index_salers on salers(salers_id,salers_code);

4.索引原理：

首先mysql是一个B+树,我们造数据的时候，它会对数据进行一个排序，排序后数据会以一个指针的方式将数据链接起来，mysql在底层为了进一步优化基于B+树的数据结构去进行存储，对数据进行一页一页的存储，每一页的数据为16KB，站在B+树的数据结构上来看，一个三层的B+树数据结构近10E左右，一般项目使用是3层，如果是基于主键上来讲，它一般是查询2次磁盘I\O因为顶层是常驻内存的

# 创建表 user ，user表含id、age、name两个用户
​
create table user(id int,name varchar(20),age int,key(id))
​
# 插入无需id数据
​
insert into user values(1,'a',17);
​
insert into user values(3,'c',19);
​
insert into user values(2,'d',15);
​
insert into user values(5,'e',16);
​
# 通过查询语句查询添加索引的字段与无索引的字段对比， 有索引的字段是进行了数据排序的，无索引的数据未进行数据排序
# 主键索引为什么要排序？ 方便快速查询
​

数据库存储计算：

 

1page = 16*1024 = 16384字节

以上图为例，id为int类型 name为16字节，age 为4字节 指针为4-8字节假设指针为8字节

那么一个page可以存储512条数据

-B+Tree 是在B-Tree（B树）基础上的一种优化,更适合现在外存储索引结构，innoDB存储引擎用的就是B+树实现索引结构

B-Tree（B树）结构图中可以看到每个节点中不仅包含数据的key值,还有data值,每一个页的存储空间是有限的,如果data数据比较大时,将会导致每个节点（即一个页）能存储的key的数据值很小，当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时磁盘I/O次数，进而影响查询效率，在B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层节点上,而非叶子节点上值存储key值信息，这样可以加大每个节点存储的key值数数量，降低B+Tree的高度。

5.B+树与B树的几点不同

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

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

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

• InnoDB存储引擎页的大小为16KB每页,一般表的主键为int(占用4个字节)或者BIGIN(占用8个字节)，指针类型占用4-8个字节，也就是 说一个页（B+树中的一个节点）中大概存储16KB/(8B+8B)=1K的数据存储，也就是说一个深度为3的B+树索引可以维护10亿条记录。
  

  
  

• 实际情况每个节点不可能填满，因此在数据库中，B+树的高度一般都在2-4层，mysql的innoDB存储引擎在设计时根节点常驻内存，也就是说查找某一键值的行记录时最多只需要查找某一键值的行记录时最多只需要1-3次的磁盘I/O *** 作，顶层为常驻内存

6.聚簇索引和非聚簇索引

• 聚簇索引:行存储与索引放到一块，索引结构的叶子节点保存了行数据 （//聚簇索引：1.主键索引 2.无主键索引那么聚簇索引为生成数据时候的任务id）

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

  PS：在innoDB中，在聚簇索引之上称为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。
  

  
  

  辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找

 

InnoDB中

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

  
  

• 若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name，达到其叶子节点获取对应的主键。
  

  
  

  第二步使用主键在主索引B+树中再执行一次B+树检索 *** 作，最终达到叶子节点即可以获取整行数据

• 聚簇索引默认是主键 如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引进行替代，如果没有这样的索引，InnoDB会建立一个隐式定义一个主键（类似Oracle的rowId）来作为聚簇索引。
  

  
  

  如果已经设置了主键为聚簇索引又希望在单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可

MYISAM:

• MYIASM使用的是非聚簇索引，非聚簇索引的两颗B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了辅助键，表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址向真正的表数据，对于表数据来说，这2个键没有任何差别。
  

  
  

  由于索引树是独立的，通过辅助键检索无需访问主键的索引树

7.使用聚簇索引的优势

问题：每次使用辅助索引检索都要经过2次B+树查找，看上聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

1. 由于行数据和聚簇索引的叶子节点在一起，同一页中会有多条行数据，访问同一页数据不同记录时，已经加载到buff（缓存器中），再次访问时，会在内存中完成访问，不必访问磁盘。
   

   
   

   这样主键和行数据是一起载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键id来组织数据，获取数据更快。
   

   
   

2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址，好处是当行数据发生变化的时候，索引树的节点也会发送变化，或者我们查找数据，在上一次I/O读写的缓冲中没有，需要发生一次新的IO *** 作时，可以避免对辅助系统的维护工作，只需要维护聚簇索引树就好了。

另外一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

8.使用聚簇索引需要注意点

• 使用主键为聚簇索引时，主键最好不用uuid，因为uuid的值太过离散，不适合排序，且可能出现新增加的uuid，会插入索引树的中间位置，导致索引树调整复杂度变大，消耗更多的时间和资源

• 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键，对所以树的结构影响最小，而且占用的存储空间越大，辅助索引中保存的主键值也越大，占用空间也会影响到I/O读写到的数据量

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

• 聚簇索引的数据物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据也是放在相邻的存放在磁盘上，如果主键不是自增id，那么可以想象，它会不断的调换数据的物理地址，分页，当然也由于其他一些措施来减少这些 *** 作，但是却无法避免，如果是自增的，那就简单了，它只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高

什么时候无法利用索引？

• 查询语句中使用like关键字

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

• 查询语句中使用多列索引

  • 使用联合索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用

• 索引语句中使用了or关键字

  • 查询语句中含有OR关键字时，如果or前后两个列均为索引那么查询中将使用索引，如果or前后有一个非索引，那么查询中将不使用索引